MySQL REPLACE statement – When to use it

MySQL implements an useful function – REPLACE INTO –  that developers should use to avoid coding logic already implemented at the DB level.

That function works exactly as an INSERT (same syntax), but it also performs a DELETE on the existing record when a duplicate-key occurs (same PRIMARY KEY or UNIQUE KEY on another column) in the same one atomic operation.
Note:  UNIQUE KEYS allows multiple NULL values. In that case there is no key violation and no replacement.
Note: DELETE operations are counted in the number of affected rows.
Note: the DELETE  triggers the innoDb “ON DELETE” .

Example: simple table with settings (containing pairs key->value)

CREATE TABLE IF NOT EXISTS `settings` (

`key` varchar(50) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`key`)
)

If we want to update a setting or create it when not existing, only one query is needed:

REPLACE INTO `settings` (`key`,`value`) VALUES ('version', 1)

Netbeans 7.0 for PHP – Keyboard shortcuts

Netbeans 7.0 PHP IDE EDITORNetbeans PHP 7.0 includes a powerful refactoring tool, support for PHP aliases and PHPDoc generation (not tested yet). I think when xdebug watching feature will become stable, Netbeans  – currently my favourite – will be a fully-featured PHP IDE, probably the best free alternative to Zend Studio.

Netbeans PHP was initially created for Java and afterwards  extended for PHP development, so most of the code OOP features are unknown by PHP community. I haven’t found a page containing a full description of them, so I’ll list here some shortcuts/features I found very useful.

Be smart ! Spend some time learn and experiment them, and you will save lots of time later doing more proper developing, less bug fixing, and less click/search actions. Depending on the tasks, even one of them can considerably increase your performances.

Code navigation
Ctrl+Click on a method/class/function go into the class (if it’s a dependent project/libraries, add those in the project properties)
Alt+Left, Alt+Right navigate back/forward when using the ctrl+click feature
Autocompletition
Ctrl+Space when typing code (variable, method, function, class name,…) => show autocompletition with PHP Docs
Ctrl+ [mouse over] shows the PHP Doc of the method (another reason why it’s necessary to write PHP Docs for our methods/classes)
Ctrl+K/L immediate autocomplete with first match (rotate)
Refactoring
Ctrl+R on a selected variable : automatically highlights all the occurrences in the scope and when typing the changes, it changes all of them
Ctrl+R on a method/class :show a dialog with the new name, and refactors all the occurrences around the code (Implemented in NB 7.0). Of course netbeans does a refactoring using names, it cannot consider dynamic instantiation of classes.
Code generator
Alt+Ins : autcompletition of class constructor, setter and getter methods using existing class fields
Lines editing
The following commands are extremely useful when dealing with zend configuration INI files, chain calls to methods or array options)
Ctrl+E delete line
Alt+Shift+Up / Down move the current line or selection one line up/down
Ctrl+Shift+Down duplicate line
Ctrl+G go to line number
Format
Shift+Tab / Tab on group of line, move tabulation of all the selected line to the left/right (obvious but not everyone knows)
Alt+Shift+F Format/Ident the selected code (not code-sniffer-proof but an order of magnitude better than doing it manually)
Windows Management
Ctrl+w close current window
Menu: Windows->Reset Windows restore windows
Shift+Esc or Double click on a file tab: expand to fullscreen (double click again to go back)
Ctrl+Tab shows opened files list (keep Ctrl button pushed and push Tab to rotate)
Ctrl+Page up/down rotate opened file tabs
Code suggestions
Netbeans highlights syntax errors, and also suggest tips on the line number column, use Alt+Enter to see and select automatic fixes
Bookmark
Ctrl+M set/remove a bookmark on the current line (an icon is displayed)
Ctrl+, move to the next bookmark (rotate)

 

You can see all the shortcut list in Netbeans options, but it’s not clear what they do and most of them are not really useful. An interesting feature is assigning shortcuts for custom commands (e.g. git commit).

I’ll keep this post updated

 

GIT: how to set a private repository on your server

What you need

  • Server side: SSH access to the server + Git installed
  • Client side:Git shell ((Linux shell access, or Gui Bash for Windows)

Set up remote repository

#login to your server
ssh elvis@phpntips.com
# create repository
mkdir ~/myproject.git
# create empty repository
cd ~/myproject.git
git init --bare

Server: Set SSH keys

# only if you do not have an identity (~/.ssh/id_rsa.pub)
ssh-keygen -t rsa
# copy public key to the server
cat ~/.ssh/id_rsa.pub | ssh elvis@phpntips.com "cat >> ~/.ssh/authorized_keys"

in case youv’e specified a password for the RSA keys, and the password is asked all the time, use ssh-agent

eval `ssh-agent`
ssh-add
# If the keys are set properly, you can now login to the server without specifying any password
ssh elvis@phpntips.com

Local machine: Clone the repository

cd ~/myProject
git clone ssh://user@phpntips.com/~/myproject.git
# make changes and commit
git add
git commit -a && git push

Some GIT tutorials / reference
Official Git tutorial
Git tutorial if you already know SVN
Git reference

GIT: first impression, pros and cons for single user projects

I’ve recently moved a personal project of mine from SVN to GIT.
As I’m the only committer, I’m not using a lot the merge/branching and I do not need to navigate into the history so often.
However, my opinion is that GIT is better for those kind of projects too

Advantages

  • speed: GIT compresses the files before sending/receiving and speeds up the network operations by an order of magnitude faster than SVN. It also made unnecessary the use of ZF libs using svn:external, now only one fast repository.
  • only one .git directory at the root level, instead of .svn dirs at each level as SVN does. That made easy the deploy, and make useless the svn export operation.- local repository, fast history navigation

Disadvantages

  • IDE integration: currently there is a stable plugin for Netbeans 7.0, good to commit and show the modified files/lines, but some features are not yet implemented (like the integrated SVN annotations showing the author of each line, and the navigation of the history directly from the IDE) and I have to use the shell for that.
    [update 10/7-11 : the new git plugin (nb dev version, or manual update) implements history navigation and diff, and show annotations too]
  • The process to set up a GIT repository on your server is a bit more complicated than SVN, when done via SSH with keys (and a bit more tricky on Windows). I’ll write a quick guide in the next post.

I’m using it in the office too, and the branch/merge advantages, local history actually speeds up development (and deploy)