Tuesday 19 February 2013

Install MongoDB for PHP on Mac OS X

MongoDB is a document-oriented database (among other things), and it’s especially convenient that the native document format is JSON. For various ongoing pet projects, I figured I’d give it a try to avoid the overhead of creating/maintaining schemas and having to flatten down my JSON objects to fit a relational model.

These instructions work for 10.7 Lion, 10.6 Snow Leopard and 10.5 Leopard. The steps outlined here ensure that your MongoDB installation integrates well with the rest of the system (Apache, PHP & `launchd`), is started automatically when the machine starts up, and makes it easy to upgrade later.

There are a lot of conflicting instructions on the Web about how to install MongoDB, and they’re either incomplete (most of them skip the part about making MongoDB run automatically at startup), and none of them satisfied the following requirements I had:

I should not need to install any software that’s already present on a stock Mac OS X installation (Lion, Snow Leopard or Leopard, at the very least.)

It should be relatively easy to upgrade the base version of MongoDB, so installing it via a package manager would be preferable to installing it from source.

The MongoDB server should be started up automatically through system-standard tools such as `launchd` — not manually every time I need to test something.

This tutorial assumes a virgin Mac OS X installation. If you already have a component installed, simply skip that step.

Install XCode from the Mac App Store. It’s needed for MacPorts.

Install MacPorts from macports.org. MacPorts is a package manager for Mac OS X that makes it easy to install and configure a lot of open-source software.

Although MongoDB binaries are available from the MongoDB web site, I strongly recommend using the MacPorts MongoDB port. MacPorts ensures dependencies are installed correctly, and provides an easy way to upgrade all outdated packages at once.

Install and configure MongoDB

Install MongoDB. Open a Terminal, ensure you’re using an account with Administrator privileges, and type:
```
sudo port install mongodb
```
That’s it, MongoDB is now installed. But there’s lots more to do to get it working.

Create directories required by MongoDB.

# The data directory. sudo mkdir -p /var/lib/mongodb/ # The logs directory. sudo mkdir -p /var/log/mongodb/

Create a config file. Put this in a new file at /etc/mongodb.conf:

# This is an example config file for MongoDB. # Place it at /etc/mongodb.conf # Based on a sample provided at # http://www.mongodb.org/display/DOCS/File+Based+Configuration dbpath = /var/lib/mongodb bind_ip = 127.0.0.1 noauth = true

Configure a launchd LaunchDaemon. Put this in a new file at/Library/LaunchDaemons/org.mongo.mongod.plist.

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd"> <plist version="1.0"> <dict> <key>Label</key> <string>org.mongodb.mongod</string> <key>ProgramArguments</key> <array> <string>/opt/local/bin/mongod</string> <string>run</string> <string>--config</string> <string>/etc/mongodb.conf</string> </array> <key>RunAtLoad</key> <true/> <key>KeepAlive</key> <true/> <key>WorkingDirectory</key> <string>/var/log/mongodb/</string> <key>StandardErrorPath</key> <string>/var/log/mongodb/output.log</string> <key>StandardOutPath</key> <string>/var/log/mongodb/output.log</string> </dict> </plist>

Start MongoDB via launchd. Type in a Terminal window:

sudo launchctl load /Library/LaunchDaemons/org.mongo.mongod.plist

You can stop reading now if all you need is MongoDB and don’t plan to use PHP with it. The MongoDB server will be automatically launched at startup.

Enable Apache and PHP5

Start Apache. In System Preferences, go to Sharing, and then check the box next to “Web Sharing”.

Enable PHP5 support in Apache. Open the Apache config file, /etc/apache2/httpd.conf, and locate the following line.
```
# LoadModule php5_module
```
Uncomment that line, so it reads:
```
LoadModule php5_module
```
Restart Apache. Type in a Terminal window:
```
sudo apachectl graceful
```

Test if PHP is working correctly. Create a new PHP file at/Library/WebServer/Documents/phpinfo.php and put in it:

<?php phpinfo(); ?>

Browse to http://localhost/phpinfo.php, and you should see a long page showing your PHP configuration.

Apache and PHP5 are now configured.

Install and Configure the PHP5 MongoDB driver.

Install PEAR. PEAR is the PHP Extension and Application Repository, from which we’ll install the PHP5 MongoDB driver.

$ cd /tmp $ wget http://pear.php.net/go-pear.phar $ sudo php -d detect_unicode=0 go-pear.phar

You’ll be prompted with a default file layout. I changed this to install it in /usr/local/pear instead of under /Users/Admin.

Below is a suggested file layout for your new PEAR installation. To change individual locations, type the number in front of the directory. Type 'all' to change all of them or simply press Enter to accept these locations. 1. Installation base ($prefix) : /Users/Admin/pear 2. Temporary directory for processing : /tmp/pear/install 3. Temporary directory for downloads : /tmp/pear/install 4. Binaries directory : /Users/Admin/pear/bin 5. PHP code directory ($php_dir) : /Users/Admin/pear/share/pear 6. Documentation directory : /Users/Admin/pear/docs 7. Data directory : /Users/Admin/pear/data 8. User-modifiable configuration files directory : /Users/Admin/pear/cfg 9. Public Web Files directory : /Users/Admin/pear/www 10. Tests directory : /Users/Admin/pear/tests 11. Name of configuration file : /Users/Admin/.pearrc 1-11, 'all' or Enter to continue:

At this prompt, type 1

(Use $prefix as a shortcut for '/Users/Admin/pear', etc.) Installation base ($prefix) [/Users/Admin/pear] :

At this prompt, type /usr/local/pear

Below is a suggested file layout for your new PEAR installation. To change individual locations, type the number in front of the directory. Type 'all' to change all of them or simply press Enter to accept these locations. 1. Installation base ($prefix) : /usr/local/pear 2. Temporary directory for processing : /tmp/pear/install 3. Temporary directory for downloads : /tmp/pear/install 4. Binaries directory : /usr/local/pear/bin 5. PHP code directory ($php_dir) : /usr/local/pear/share/pear 6. Documentation directory : /usr/local/pear/docs 7. Data directory : /usr/local/pear/data 8. User-modifiable configuration files directory : /usr/local/pear/cfg 9. Public Web Files directory : /usr/local/pear/www 10. Tests directory : /usr/local/pear/tests 11. Name of configuration file : /Users/Admin/.pearrc 1-11, 'all' or Enter to continue:

At this prompt, type 11

(Use $prefix as a shortcut for '/usr/local/pear', etc.) Name of configuration file [/Users/Admin/.pearrc] :

At this prompt, type /etc/pearrc

Below is a suggested file layout for your new PEAR installation. To change individual locations, type the number in front of the directory. Type 'all' to change all of them or simply press Enter to accept these locations. 1. Installation base ($prefix) : /usr/local/pear 2. Temporary directory for processing : /tmp/pear/install 3. Temporary directory for downloads : /tmp/pear/install 4. Binaries directory : /usr/local/pear/bin 5. PHP code directory ($php_dir) : /usr/local/pear/share/pear 6. Documentation directory : /usr/local/pear/docs 7. Data directory : /usr/local/pear/data 8. User-modifiable configuration files directory : /usr/local/pear/cfg 9. Public Web Files directory : /usr/local/pear/www 10. Tests directory : /usr/local/pear/tests 11. Name of configuration file : /etc/pearrc 1-11, 'all' or Enter to continue:

At this prompt, type <Enter>

Would you like to alter php.ini </private/etc/php.ini>? [Y/n] :

At this prompt, type Y.

Install the PHP MongoDB driver.

sudo /usr/local/pear/bin/pecl install mongo

Configure the system PHP to load that extension. Add this line to your php.ini. (If you don’t know where your php.ini file is located, go back to the phpinfo page that you created earlier, and you’ll find it on that page.)
```
extension=mongo.so
```
Restart Apache. To enable PHP5 with MongoDB support, restart Apache.
```
sudo apachectl graceful
```

Test your installation.

Create a simple test script. Create a PHP file at /Library/WebServer/Documents/mongodb.php

<?php $mongoDB = new Mongo(); $database = $mongoDB->selectDB("example"); $collection = $database->createCollection('TestCollection'); $collection->insert(array(test => 'Test OK')); $retrieved = $collection->find(); foreach ($retrieved as $obj) { echo($obj['test']); } ?>

Test the installation. Browse to http://localhost/mongodb.php. If you see the following output, then congratulations, MongoDB is properly installed and configured to work with PHP on your Mac. Happy developing!
```
Test OK
```

Tuesday 12 February 2013

Firebug include() command

Firebug Command Line is probably one of the most important Firebug features and its value is yet extended by set of built-in commands.

One of the new commands, introduced in Firebug 1.11 is include() and this post is intended to explain how to use it .

Include Remote Script

The basic purpose of include() command is to include a remote script into the current page.
include("http://code.jquery.com/jquery-latest.min.js")

In this particular example we included jQuery script and effectively jQuerified the page.

Create Alias

It would be irritating to type long URLs (and remember them) every time you reload the page and so, it's possible to create an alias.
include("http://code.jquery.com/jquery-latest.min.js", "jquery")
Now, all you need to do to include jQuery on your page is type:
include("jquery")
Aliases are persistent across Firefox restarts and list of existing aliases can be displayed by typing:
include();

To remove an existing alias type:
include(null, "jquery");

~firebug team

Monday 11 February 2013

Class Reloading in Java

The first thing to understand when talking about reloading Java code is the relation between classes and objects. All Java code is associated with methods contained in classes. Simplified, you can think of a class as a collection of methods, that receive "this" as the first argument. The class with all its methods is loaded into memory and receives a unique identity. In the Java API this identity is represented by an instance of java.lang.Class that you can access using theMyObject.class expression.

Every object created gets a reference to this identity accessible through the Object.getClass()method. When a method is called on an object, the JVM consults the class reference and calls the method of that particular class. That is, when you call mo.method() (where mo is an instance ofMyObject), then the JVM will call mo.getClass().getDeclaredMethod("method").invoke(mo)(this is not what the JVM actually does, but the result is the same).

Every Class object is in turn associated with its classloader (MyObject.class.getClassLoader()). The main role of the class loader is to define a class scope -- where the class is visible and where it isn't. This scoping allows for classes with the same name to exist as long as they are loaded in different classloaders. It also allows loading a newer version of the class in a different classloader.

The main problem with code reloading in Java is that although you can load a new version of a class, it will get a completely different identity and the existing objects will keep referring the previous version of the class. So when a method is called on those objects it will execute the old version of the method.

Let's assume that we load a new version of the MyObject class. Let's refer to the old version asMyObject_1 and to the new one as MyObject_2. Let's also assume that MyObject.method()returns "1" in MyObject_1 and "2" in MyObject_2. Now if mo2 is an instance of MyObject_2:

mo.getClass() != mo2.getClass()
mo.getClass().getDeclaredMethod("method").invoke(mo) != mo2.getClass().getDeclaredMethod("method").invoke(mo2)
mo.getClass().getDeclaredMethod("method").invoke(mo2) throws aClassCastException, because the Class identities of mo and mo2 do no match.

This means that any useful solution must create a new instance of mo2 that is an exact copy of moand replace all references to mo with it. To understand how hard it is, remember the last time you had to change your phone number. It's easy enough to change the number itself, but then you have to make sure that everyone you know will use the new number, which is quite a hassle. It's just as difficult with objects (in fact, it's actually impossible, unless you control the object creation yourself), and we're talking about many objects that you must update at the same time.

Down and Dirty

Let's see how this would look in code. Remember, what we're trying to do here is load a newer version of a class, in a different classloader. We'll use an Example class that looks like this:

public class Example implements IExample {
  private int counter;
  public String message() {
    return "Version 1";
  }
  public int plusPlus() {
    return counter++;
  }
  public int counter() {
    return counter;
  }
}

We'll use a main() method that will loop infinitely and print out the information from the Exampleclass. We'll also need two instances of the Example class: example1 that is created once in the beginning and example2 that is recreated on every roll of the loop:

public class Main {
  private static IExample example1;
  private static IExample example2;
 
  public static void main(String[] args)  {
    example1 = ExampleFactory.newInstance();
 
    while (true) {
      example2 = ExampleFactory.newInstance();
 
      System.out.println("1) " +
        example1.message() + " = " + example1.plusPlus());
      System.out.println("2) " +
        example2.message() + " = " + example2.plusPlus());
      System.out.println();
 
      Thread.currentThread().sleep(3000);
    }
  }
}

IExample is an interface with all the methods from Example. This is necessary because we'll be loading Example in an isolated classloader, so Main cannot use it directly (otherwise we'd get aClassCastException).

public interface IExample {
  String message();
  int plusPlus();
}

From this example, you might be surprised to see how easy it is to create a dynamic class loader. If we remove the exception handling it boils down to this:

public class ExampleFactory {
  public static IExample newInstance() {
    URLClassLoader tmp =
      new URLClassLoader(new URL[] {getClassPath()}) {
        public Class loadClass(String name) {
          if ("example.Example".equals(name))
            return findClass(name);
          return super.loadClass(name);
        }
      };
 
    return (IExample)
      tmp.loadClass("example.Example").newInstance();
  }
}

The method getClassPath() for the purposes of this example could return the hardcoded classpath. However, in the full source code (available in the Resources section below) you can see how we can use the ClassLoader.getResource() API to automate that.

Now let's run Main.main and see the output after waiting for a few loop rolls:

1) Version 1 = 3
2) Version 1 = 0

As expected, while the counter in the first instance is updated, the second stays at "0". If we change the Example.message() method to return "Version 2". The output will change as follows:

1) Version 1 = 4
2) Version 2 = 0

As we can see, the first instance continues incrementing the counter, but uses the old version of the class to print out the version. The second instance class was updated, however all of the state is lost.

To remedy this, let's try to reconstruct the state for the second instance. To do that we can just copy it from the previous iteration.

First we add a new copy() method to Example class (and corresponding interface method):

public IExample copy(IExample example) {
  if (example != null)
    counter = example.counter();
  return this;
}

Next we update the line in the Main.main() method that creates the second instance:

9	example2 = ExampleFactory.newInstance().copy(example2);

Now waiting for a few iterations yields:

1) Version 1 = 3
2) Version 1 = 3

And changing Example.message() method to return "Version 2" yields:

1) Version 1 = 4
2) Version 2 = 4

As you can see even though it's possible for the end user to see that the second instance is updated and all its state is preserved, it involves managing that state by hand. Unfortunately, there is no way in the Java API to just update the class of an existing object or even reliably copy its state, so we will always have to resort to complicated workarounds.

~jevgeni

Sunday 10 February 2013

Setting up Homebrew on Macbook Pro

You can find Homebrew on gitub and you can find the recommended installation instructions there which are as follows.

ruby -e "$(curl -fsSkL raw.github.com/mxcl/homebrew/go)"

This will download the “go” ruby script and execute it in one go. If you are unsure or just curious and can read ruby code, then you can see this script here.

In brief, this script changes permissions on various directories, downloads the Homebrew install files and installs them under /usr/local

Before I ran this install, I blew away my MacPorts installation using these instructions, which suggests the following…

sudo port -f uninstall installed

sudo rm -rf \
    /opt/local \
    /Applications/DarwinPorts \
    /Applications/MacPorts \
    /Library/LaunchDaemons/org.macports.* \
    /Library/Receipts/DarwinPorts*.pkg \
    /Library/Receipts/MacPorts*.pkg \
    /Library/StartupItems/DarwinPortsStartup \
    /Library/Tcl/darwinports1.0 \
    /Library/Tcl/macports1.0 \
    ~/.macports

Since Apple does not touch /usr/local I cleaned that out too, since anything in there on my Mac would have been installed by MacPorts. Homebrew installs under /usr/local and I wanted it to be nice and clean. Doing this will depend on what you’ve been up to with your Mac.

Some key files that will be installed by the installer are

/usr/local/bin/brew

This script does all the magic of installing, uninstalling, listing installed packages, showing package information and other tasks.

/usr/local/Cellar/

This directory is where the files are installed for the packages you install. You can see some of the package directories I have installed on this machine.

$ ls -dF1 /usr/local/Cellar/*
/usr/local/Cellar/bash-completion/
/usr/local/Cellar/geos/
/usr/local/Cellar/git/
/usr/local/Cellar/mongodb/
/usr/local/Cellar/ossp-uuid/
/usr/local/Cellar/pidof/
/usr/local/Cellar/postgis/
/usr/local/Cellar/postgresql/
/usr/local/Cellar/proj/
/usr/local/Cellar/readline/
/usr/local/Cellar/wget/

Once, you’re installed you should be able to run brew install to install packages.

Packages are first downloaded to /Library/Caches/Homebrew/, so if you do uninstall and re-install, you will not have to re-download them a second time.

Here’s a dump of the files my cache directory.

ls -1 /Library/Caches/Homebrew/*
/Library/Caches/Homebrew/bash-completion-1.2.tar.bz2
/Library/Caches/Homebrew/geos-3.2.2.tar.bz2
/Library/Caches/Homebrew/git-1.7.3.2.tar.bz2
/Library/Caches/Homebrew/git-htmldocs-1.7.3.2.tar.bz2
/Library/Caches/Homebrew/git-manpages-1.7.3.2.tar.bz2
/Library/Caches/Homebrew/mongodb-1.6.3-x86_64.tgz
/Library/Caches/Homebrew/mysql-5.1.51.tar.gz
/Library/Caches/Homebrew/ossp-uuid-1.6.2.tar.gz
/Library/Caches/Homebrew/pidof-0.1.4.tar.gz
/Library/Caches/Homebrew/postgis-1.5.2.tar.gz
/Library/Caches/Homebrew/postgresql-9.0.1.tar.bz2
/Library/Caches/Homebrew/proj-4.7.0.tar.gz
/Library/Caches/Homebrew/proj-datumgrid-1.5.zip
/Library/Caches/Homebrew/readline-6.1.tar.gz
/Library/Caches/Homebrew/solr-1.4.1.tgz
/Library/Caches/Homebrew/wget-1.12.tar.bz2

I will demonstrate installing Mongodb, which is a “scalable, high-performance, open source, document-oriented database”. Think “NoSQL”.

brew install mongodb

Here’s the output

==> Downloading http://fastdl.mongodb.org/osx/mongodb-osx-x86_64-1.6.3.tgz
######################################################################## 100.0%
==> Caveats
If this is your first install, automatically load on login with:
    cp /usr/local/Cellar/mongodb/1.6.3-x86_64/org.mongodb.mongod.plist ~/Library/LaunchAgents
    launchctl load -w ~/Library/LaunchAgents/org.mongodb.mongod.plist

If this is an upgrade and you already have the org.mongodb.mongod.plist loaded:
    launchctl unload -w ~/Library/LaunchAgents/org.mongodb.mongod.plist
    cp /usr/local/Cellar/mongodb/1.6.3-x86_64/org.mongodb.mongod.plist ~/Library/LaunchAgents
    launchctl load -w ~/Library/LaunchAgents/org.mongodb.mongod.plist

Or start it manually:
    mongod run --config /usr/local/Cellar/mongodb/1.6.3-x86_64/mongod.conf
==> Summary
/usr/local/Cellar/mongodb/1.6.3-x86_64: 16 files, 83M, built in 2 seconds

A command I really like is the info command.

brew info mongodb

It will give you all the information you need for a particular package for starting, stopping, restarting or other actions pertinent the service you installed. If will be similar to the information output when you first install a package. It’s good to know about this command so that you can always find the maintenance commands.

Here is an example of the info command for the postgresql package.

brew info postgresql

postgresql 9.0.1

http://www.postgresql.org/

Depends on: readline, ossp-uuid
/usr/local/Cellar/postgresql/9.0.1 (1229 files, 25M)

If builds of Postgresl 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:

http://github.com/mxcl/homebrew/issues/issue/2510

To build plpython against a specific Python, set PYTHON prior to brewing:
  PYTHON=/usr/local/bin/python  brew install postgresql
See:

http://www.postgresql.org/docs/9.0/static/install-procedure.html

If this is your first install, create a database with:
    initdb /usr/local/var/postgres

If this is your first install, automatically load on login with:
    cp /usr/local/Cellar/postgresql/9.0.1/org.postgresql.postgres.plist ~/Library/LaunchAgents
    launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

If this is an upgrade and you already have the org.postgresql.postgres.plist loaded:
    launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
    cp /usr/local/Cellar/postgresql/9.0.1/org.postgresql.postgres.plist ~/Library/LaunchAgents
    launchctl load -w ~/Library/LaunchAgents/org.postgresql.postgres.plist

Or start manually with:
    pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

And stop with:
    pg_ctl -D /usr/local/var/postgres stop -s -m fast

If you want to install the postgres gem, including ARCHFLAGS is recommended:
    env ARCHFLAGS="-arch x86_64" gem install pg

To install gems without sudo, see the Homebrew wiki.

http://github.com/mxcl/homebrew/commits/master/Library/Formula/postgresql.rb

brew list will tell you which packages are installed.

brew help will give you the following

Usage: brew [-v|--version] [--prefix [formula]] [--cache [formula]]
            [--cellar [formula]] [--config] [--env] [--repository]
            [-h|--help] COMMAND [formula] ...

Principle Commands:
  install formula ... [--ignore-dependencies] [--HEAD]
  list [--unbrewed|--versions] [formula] ...
  search [/regex/] [substring]
  uninstall formula ...
  update

Other Commands:
  info formula [--github]
  options formula
  deps formula
  uses formula [--installed]
  home formula ...
  cleanup [formula]
  link formula ...
  unlink formula ...
  outdated
  missing
  prune
  doctor

Informational:
  --version
  --config
  --prefix [formula]
  --cache [formula]

Commands useful when contributing:
  create URL
  edit [formula]
  audit [formula]
  log formula
  install formula [-vd|-i]

For more information:
  man brew

To visit the Homebrew homepage type:
  brew home

MySQL quick reference.

Let’s start by making a new user within the MySQL shell:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@‘localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

Your changes will now be in effect.

How To Grant Different User Permissions

Here is a short list of other common possible permissions that users can enjoy.

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)

CREATE- allows them to create new tables or databases

DROP- allows them to them to delete tables or databases

DELETE- allows them to delete rows from tables

INSERT- allows them to insert rows into tables

SELECT- allows them to use the Select command to read through databases

UPDATE- allow them to update table rows

GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this framework:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name.

Each time you update or change a permission be sure to use the Flush Privileges command.

If you need to revoke a permission, the structure is almost identical to granting it:

REVOKE [type of permission] ON [database name].[table name] TO ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER ‘demo’@‘localhost’;

To test out your new user, log out by typing

quit

and log back in with this command in terminal:

mysql -u [username]-p


Query
SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
SELECT ... FROM t1 JOIN t2 USING(id) WHERE condition


 Conditionals
field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IN (value1, value2)
field1 NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2


 Data Manipulation
INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
INSERT table1 SET field1=value_1, field2=value_2 ...

DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- join:
DELETE FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition

UPDATE table1 SET field1=new_value1 WHERE condition
-- join:
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ...
WHERE table1.id1 = table2.id2 AND condition


 Browsing
SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / SHOW COLUMNS FROM table / DESCRIBE table / DESC table / EXPLAIN table
SHOW CREATE TABLE table
SHOW CREATE TRIGGER trigger
SHOW TRIGGERS LIKE '%update%'
SHOW PROCESSLIST
KILL process_number
SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '**yourdbname**';
$ mysqlshow
$ mysqlshow database


 Create / delete / select / alter database
CREATE DATABASE [IF NOT EXIST] mabase [CHARACTER SET charset] [COLLATE collation]
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase
USE mabase

ALTER DATABASE mabase CHARACTER SET utf8


 Create/delete/modify table
CREATE TABLE table (field1 type1, field2 type2, ...)
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA)
    [ON UPDATE] [CASCADE|SET NULL|RESTRICT]
    [ON DELETE] [CASCADE|SET NULL|RESTRICT])
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
  FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
CREATE TABLE table IF NOT EXISTS (...)

CREATE TABLE new_tbl_name LIKE tbl_name
  [SELECT ... FROM tbl_name ...]

CREATE TEMPORARY TABLE table (...)

DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
DROP TEMPORARY TABLE table

ALTER TABLE table MODIFY field1 type1 
ALTER TABLE table MODIFY field1 type1 NOT NULL ... 
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD new_name_field1 type1
ALTER TABLE table ADD new_name_field1 type1 FIRST
ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
ALTER TABLE table DROP field1
ALTER TABLE table ADD INDEX (field);
ALTER TABLE table ADD PRIMARY KEY (field);

-- Change field order:
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field

ALTER TABLE old_name RENAME new_name;


 Keys
CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))
ALTER TABLE table ADD PRIMARY KEY (field);


 Privileges
GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')

DROP USER 'user'@'host'


 Main data types
TINYINT (1o: -127+128) SMALLINT (2o: +-65 000)
  MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
  BIGINT (8o: +-9.10^18)
  Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
  /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"

INT NOT NULL auto_increment PRIMARY KEY -- auto-counter for PK

FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
  /!\ 8,3 -> 12345,678 -- NOT 12345678,123!

TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
  TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)

VARCHAR (single-line; explicit size)  TEXT (multi-lines; max size=65535)  BLOB (binary; max size=65535)
  Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
 Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT

ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)


 Forgot root password?
$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Kill mysqld_safe from the terminal, using Control + \
$ /etc/init.d/mysql start


 Repair tables after unclean shutdown
mysqlcheck --all-databases
mysqlcheck --all-databases --fast


 load data from local file
If you are running mysql and using your newly created database use 
the below code to run the script file mysql> SOURCE input_file


from terminal mysql -u root -p database < filename-20120201.tbz