Getting Started with Drizzle and PHP

When It Rains, It Drizzles

Unless you’ve been completely out of touch with the open source world, you’ll have heard of Drizzle, a new database server that’s derived from MySQL. The Drizzle project aims to build a “smaller, lighter, faster” version of MySQL that offers similar stability and ease of use, but strips out certain features from the core system in order to improve performance and scalability.

Now, while Drizzle is still relatively new, it is quickly gaining attention among open source developers who are interested in experimenting with it, contributing to it, and using it in their projects. And that’s where this article comes in. Over the next few pages, I’ll guide you through the process of getting started with Drizzle, showing you how to compile and install it, set up a Drizzle database, and connect to it using PHP. Let’s get started!

Different Strokes

In the words of its official Web site, Drizzle is “a database optimized for Cloud and Net applications… designed for massive concurrency on modern multi-CPU/core architecture”. It is licensed under the GNU GPL v2, and the project lead is Brian Aker of MySQL.

Although Drizzle is a fork of the MySQL code base, it is not intended to replace MySQL; rather, it’s designed to appeal to users who want MySQL-like reliability, ACID-compliance and ease of use, but don’t need some of the newer MySQL features like stored procedures, triggers or views.

Drizzle also differs from MySQL in a few other areas:

  • Its default storage engine is InnoDB rather than MyISAM;
  • It supports fewer datatypes than MySQL;
  • It uses a different client/server communication protocol;
  • It supports an extensible, plug-in architecture that allows developers to compile in support for only those modules that are needed (much like Apache or, in fact, PHP).

More information on the differences between MySQL and Drizzle can be found in Michael “Monty” Widenius’ blog post on the topic, as well as in the Drizzle FAQ.

It’s important to note that Drizzle is still under development and the Drizzle developers themselves state that the codebase is not yet suitable for production environments. So, if you’re looking for something to recommend to your boss or customer, you should probably stick with MySQL, PostgreSQL or SQLite for the moment.

An Apple A Day

Drizzle is currently available for Mac OS X, Linux and Solaris Express platforms and requires GCC v4.1 or better; Windows is not yet supported. To begin, download the source code archives for both the Drizzle database server (drizzle) and the Drizzle protocol library (libdrizzle), decompress them, and then compile and install them to your system, as below:

This article uses Drizzle v2009.06.1063 with libdrizzle v0.3.

Note that Drizzle makes use of the Google Protocol Buffers library, so if you don’t already have that on your system, you should download and compile it for your system. You’ll find detailed installation instructions and source code archives on the Protocol Buffers Web site. This article uses v2.0.3 of the Protocol Buffers library instead of the more recent v2.1.0, to avoid a bug related to pthreads.

By default, the Drizzle server will be installed to /usr/local/sbin/drizzled, and the Drizzle client to /usr/local/bin/drizzle.

Once you’ve got it installed, create a non-privileged group and user for the database server, as below:

Also create a directory for the database files and give it appropriate privileges, as below:

Then, start up the Drizzle server by issuing the following command:

The package compilation process will also create a Drizzle client, which is very similar to the MySQL client. Start it up and access the Drizzle server as below:

You can now issue SQL commands to the server, just as you would with the MySQL client.

Let’s get things rolling by creating a new database and adding a table:

According to the Drizzle FAQ, Drizzle supports TEXT, BLOB, VARCHAR, VARBINARY, TIMESTAMP, DATETIME, DATE, TIME, ENUM, INT, DOUBLE and DECIMAL field types. You’re probably already familiar with many of these from MySQL.

Continue building the example database by populating the table with some records:

Run a fast SELECT to make sure everything is in order:

Next, let’s try doing the same thing with PHP!

Getting Breakfast

Drizzle support in PHP comes through the Drizzle PHP extension, which provides a full-fledged API for accessing a Drizzle database server. This extension is currently maintained by Eric Day, James Luedke and others, and is freely available from PECL under the PHP license. Although the extension is currently in beta, it still allows you to perform most of the common tasks related to accessing and using a Drizzle database from within a PHP application.

To get started with the Drizzle PHP extension (v0.4.0), install it using the automated PECL installer, as below:

Alternatively, you can download the source code archive and compile it into a loadable PHP module, as below:

At this point, you should have a loadable PHP module named in your PHP modules directory. Enable this extension in the php.ini configuration file, restart the Web server, and check that the extension is active with a quick call to phpinfo():

Tool Tips

Here’s an example of using the Drizzle PHP extension to execute a SELECT query on the database server and format the results as an HTML table:

This script begins by specifying the information needed to establish a connection to the database server. This information includes the server name, the server port, the username and password required to gain access to it, and the name of the database to query. These values are all set up in regular PHP variables. A Drizzle object is then initialized with the drizzle_create() function, and the connection parameters are added to it with the drizzle_con_add_tcp() function. This function returns a link identifier, which is used for subsequent communication with the database server.

The next step is to create the query and execute it. This is accomplished with the drizzle_query() function, which needs two parameters: the query string and the link identifier for the connection. The return value of the drizzle_query() function is a result object, which represents the result of the query. If you wish to, you can buffer this result set using the drizzle_result_buffer() function; note that this can consume significant amounts of memory when used with queries that return large result sets.

The drizzle_result_row_count() function returns the number of records in the buffered result set. These records can be retrieved with the drizzle_row_next() function, which can be used to iterate over the result set and fetch each record as an array. Individual fields of the record can then be accessed using array notation.

Once you’re done processing the result set, it’s a good idea to use the drizzle_result_free() function to free up the used memory. To clean things up completely, you can close the connection to the Drizzle server with drizzle_con_close().

Here’s an example of what the output of the script looks like:

Object Lesson

You can also use the Drizzle API in an object-oriented way, wherein each of the functions above maps to an object method. Take a look at this next listing, which is equivalent to the one above:

Here, the new keyword is used to instantiate a new Drizzle object, and pass the object’s addTcp() method the relevant connection information. The resulting DrizzleCon object offers a query() method that can be used to execute queries on the database server, and there’s also a DrizzleResult object that exposes methods to perform the tasks of fetching, buffering and processing result sets.

If you look closely at the two scripts above, you’ll see the numerous similarities between the function and method names, and the structure of the script.

How To Eat Less And Run Faster

If your SQL query is likely to return a large number of records, you might be better off using an unbuffered query instead of a buffered query. An unbuffered query consumes less memory; it also lets you begin working with the result set before the query has finished executing. On the downside, you can’t use functions like drizzle_result_row_count() with unbuffered queries, because PHP doesn’t have the complete result set available in a buffer and so can’t calculate how many records it contains.

Here’s an example of using an unbuffered query with the Drizzle extension:

The first half of this script is identical to what you’ve seen earlier. However, once the query has been executed, the drizzle_result_buffer() is not called and instead, the drizzle_row_read() and drizzle_field_read() methods are used to iterate over the unbuffered query result set and print individual field values. Note also the drizzle_column_skip() function, which is used to skip over the column names in the returned data packet.

Here’s the same script using the object interface:

Adding And Subtracting

You can also use the drizzle_query() function, or the query() method, to perform INSERT, UPDATE and DELETE queries. The next example demonstrates, by performing an INSERT query and returning the ID of the new record using the insertId() method:

Here’s what the output looks like:

Here’s another example, this one using a DELETE query and returning the number of affected rows using the affectedRows() method:

The Drizzle PHP extension also includes the drizzle_escape_string() function and the escape() method, which allows you to escape user input before interpolating it into a query string. This is an important security practice to sanitize user-submitted data and thereby reduce the risk of SQL injection attacks. The next example illustrates the escape() method in action, by building a Web form that allows users to directly add new items to the database.

Here’s what the initial form looks like:

And here’s what happens after you submit a record:

Notice that the script uses the escape() method on each input value before interpolating it into the SQL query; this takes care of escaping special characters in the user’s input.

Version Control

The Drizzle extension comes with a bunch of functions designed to provide information about the current versions of the Drizzle client, server and protocol. Combining this with information from Drizzle’s information_schema database provides a big-picture view of what’s going on inside your Drizzle server. Here’s an example:

This script queries the special information_schema database to retrieve information about the databases and tables currently on the server, and to retrieve global status variables such as the server uptime, the number of connected threads, the number of open tables, and so on. It also demonstrates the version(), serverVersion() and protocolVersion() methods, which return the version number of the Drizzle client, server and protocol respectively.

Here are a couple of screenshots illustrating the output of this script:

The Drizzle extension also includes a couple of functions to help you handle connection and query errors. The drizzle_error() and drizzle_error_code() functions return the last connection error message and error code, while the drizzle_con_error() and drizzle_con_error_code() functions return the last query error message and numeric code. Needless to say, these functions have equivalents in the object interface too; look at some of the earlier examples in this article to see them in action.

And that’s about it for this article. Over the last few pages, I introduced you to Drizzle, a new, lightweight fork of MySQL specifically designed for cloud applications, and showed you how easy it is to begin using it in a PHP application. Although the Drizzle PHP extension (not to mention Drizzle itself) is still under development, it nevertheless provides a full-fledged API to performing queries, retrieving result sets and handling errors. Try it out the next time you have some time to spare, and let me know what you think!

Copyright Melonfire, 2009. All rights reserved.