Zend Framework Hidden Gems: Zend_Db

Welcome back for another edition of Zend Framework Hidden Gems. Finally we start on the weird and wonderful world of database abstraction with PHP5, PDO and Zend.

Before we get started with the mechanics of how Zend Framework handles database abstraction, we need to cover some database abstraction theory so that we understand why we are jumping through hoops and which hoops we are jumping through.

Database Abstraction Layers

When people talk about database abstraction, they usually think that they will be able to write their database code and SQL once and then rely on the database abstraction layer to make their code portable across all databases. It rarely works this way.

Zend_Db is a layer which sits above the native PDO database extension. Before PDO (pre-PHP5.1) all we had to connect to databases were libraries that were developed in individual cycles and development teams, with little regard to what other database extensions were doing. This created database drivers which utilized specific features of the underlying database very well, however it made writing any cross database code very difficult. Databases were accessed using different syntax, not all had object oriented interfaces, some drivers lacked some basic functionality which others had for years, etc. Because of these multiple independent database extensions we saw the emergence of complex and heavy-weight database abstraction layers such as PEAR::DB, MDB2, ADOdb etc. Each of these DBAL suites solved the various issues with different techniques and different levels of success and simplicity, but one thing was clear across all PHP DBAL solutions. Writing database abstraction code in PHP is slow, most of these solutions clocked in at 5 to 10 times slower than using the underlying extension directly.

PDO solved some of these problems with creating a standard core of database functionality which additional drivers can be built on top of. This was known as PHP Data Objects (PDO), and the drivers were named PDO_Mysql, PDO_Sqlite, etc. PDO is a php extension written in C, so while it doesn’t cover as much ground as full featured DBALs it does what it does very quickly. Taking this speed benefit of using PDO to do a majority of the work, PDO wrapper libraries like Zend_Db can supplement PDO with their own wrapper code leaving you with a fairly lightweight yet feature-rich database abstraction layer.

Connecting to a Database

Database connection abstraction, is using the same code to connect to multiple databases. The following code fragment shows connecting to multiple databases using regular PHP extensions, PDO and Zend_Db

	
    

<?php
	
// ext/sqlite OO connect
	
try{
    $sqlite_db = new SQLiteDatabase('example.sqlite2', 0666);

}catch(Exception $e){
    die($e->getMessage());

}
	
// ext/mysql OO connect
	
$mysql_db = new mysqli('localhost', 'user', 'password', 'example');

if (mysqli_connect_errno()) {
   echo "Error connecting: ". mysqli_connect_error();
   exit;

}
	
// PDO Mysql Connect

try {
    $mysql_db = new PDO('mysql:host=localhost;dbname=example', 'user', 'password');

} catch (PDOException $e){
    die($e->getMessage);

}
	
// PDO SQLite Connect

try {
    $sqlite_db = new PDO('sqlite:/path/to/example.sqlite2');

} catch (PDOException $e) {
    die($e->getMessage);

}
	
?>

As you can see, using the standard extensions that come with PHP5 (ext/sqlite and ext/mysqli) offers no unified interface for connecting to various databases. You must have intimate knowledge of the database extension that you are connecting to and then write your code accordingly. PDO offers the abstraction needed to use the same syntax to create the PDO object, however you still need to know which format the DSN string needs to be in for the specific database driver. In this example, the MySQL DSN and parameters are very different than the SQLite DSN. This is by design, as PDO simply passes the DSN directly to the server driver without any extra processing. This is fast and scalable as PDO doesn’t box itself into a specific DSN format and risk backwards compatibility issues arising if a specific database enables a feature which doesn’t fit into the model they have created. However, if you take a look at the various databases that PDO supports you’ll see a lot of different DSN formats.

Zend_Db::factory() handles the DSN Connection abstraction simply by providing a uniform format to connecting to any database. The Zend_Db code follows.

	


<?php
	
require_once 'Zend.php';
	
Zend::loadClass("Zend_Db");
	
$params = array ('host'     => '127.0.0.1',
                 'username' => 'user',
                 'password' => 'password',
                 'dbname'   => 'example');
	
$db = Zend_Db::factory('PDO_MYSQL', $params);

?>

We are requiring Zend.php because Zend_Db is dependant on the the top level Zend class, so there is no reason why we shouldn’t take advantage of it as well.

The factory() method from Zend_Db created an instance of the Zend_Db_Adapter class and returns it for your use. When Zend_Db_Adapter is instantiated it does not create a connection to the database itself just provides the adapter class for your use. Zend_Db_Adapter inherits most of the methods from Abstract classes higher up the object chain.

A quick word about the adapter pattern:

In the above example Zend_Db::factory returns a Zend_Db_Adapter_Pdo_Mysql object, this inherits the methods from Zend_Db_Adapter_Pdo_Abstract which in turn inherits from the generic Zend_Db_Adapter_Abstract class. In this articles as well as future articles, I will try to go over many of the highlights of the database classes, however, there is always additional knowledge to be gleaned from looking at the source, and because we understand Zend Frameworks classname to filename convention, it’s easy for us to find the files in which the classes exist.

Well, enough talk, let’s jump into some code.

	


<?php
	
include "Zend.php";

Zend::loadClass( "Zend_Db" );
	
$params = array ('dbname' => ':memory:');

$db = Zend_Db::factory('PDO_SQLITE', $params);
	
$db->query("CREATE TABLE server (key, value)");
	
$stmt = $db->prepare( "INSERT INTO server (key, value)
                       VALUES (:key, :value)" );
	
$stmt->bindParam('key', $key);

$stmt->bindParam('value', $value);
	
foreach ( $_SERVER as $key => $value ){
    $stmt->execute();

}
	
$data = $db->fetchPairs( 'SELECT key, value
                          FROM server
                          WHERE key LIKE "HTTP%"' );
	
Zend::dump( $data );
	
?>

You should already be familiar with the first two lines, where I load the top-level Zend class, and then use the loadClass() method to load the Zend_Db class.

Because of the simplicity of SQLite and the fact that it is now bundled with PHP, I will be using it for our examples. In the next two lines I create a memory based SQLite database. Instead of passing a file location for the database I use the PDO :memory: flag which is passed directly to PDO as the DSN.

Before we can do anything with our new SQLite in-memory database we need to create a table and populate it with some data. I use the Zend_Db_Adapter object that I created with Zend_Db factory and I run the query() method to create a simple table with 2 columns.

Now we want to populate the table with data. Because I am using a PDO driver, when I use Zend_Db’s prepare() method I create a PDOStatement object ($stmt). This is when Zend_Db hands off control to PDO itself, so when we are working with the $stmt object we are working directly with PDO and can use any method that PDO provides. In this case we bind the two parameters (:key and :value) to variables, and then loop through the $_SERVER array and execute our prepared statement on each loop to populate our database with the data from $_SERVER.

Once we are finished inputting data into our SQLite database, we return to our Zend_Db_Adapter object ($db) and we use the fetchPairs() method which creates an array using the first column as the key and the second column as the value, this essentially re-creates the $_SERVER array.

Lets go back to a bit of theory before we continue on with more examples.

Looking at the above code you will see that there is some very specific SQLite SQL code being used. For example when we create the table, we are taking advantage of the typeless nature of SQLite’s columns to write a very simple ‘CREATE Table’ script. You might think that because we are using a database abstraction layer, you should be able to simply change the DSN to use another database type, and be able to run the same code there. This might be possible using other full featured DBALs, but because PDO doesn’t provide SQL statement abstraction this isn’t the case here. If you want to write scripts that run on multiple database platforms you will have to be sure that you use SQL that is not specific to any single database platform.

Zend_Db provides SQL abstraction in the form of the Zend_Db_Select platform which will be the topic of an upcoming article.

Back to Zend_Db_Adaptor.

Let’s look at our example again and go over the Zend_Db_Adaptor methods used. These are all defined in the Zend_Db_Adapter_Abstract class so pull that file up if you want to follow along with the code.

Querying Zend_Db

In the above code example you saw the use of prepared queries. Prepared queries are built into Zend_Db and provide a very good way to optimize the SQL which is sent between the application and the database server. Essentially the way prepared queries work is that the query data is compiled on the client side, which in our case is the server, and then sent in a binary form to the server which executes it. There are a couple benefits to this:

1. SQL Injection: Because the data is compiled in the sql client there is no way that the data can in any way modify the surrounding SQL statement. This is a much better option than trying to manually escape the code, which can be problematic at best and often the cause of security vulnerabilites.
2. Speed: Once you prepare the statement, you can use it multiple times with different data each time you execute() the statement only the data is compiled rather than compiling the entire SQL string. When you are inserting a lot of data using the same SQL statement, as we are in the above example, prepared statements are much faster.

To utilize prepared queries in the Zend_Db query() function, you can pass two parameters. The first parameter is the SQL string (or Zend_Db_Query object, which we’ll discuss in another article), and the second is the data that you want to bind to the SQL string.

	


<?php
	
...
	
$result = $db->query( 'INSERT INTO server (key, value) VALUES (:key, :value)',
                      array('key' => $foo, 'value' => $bar)

);
	
// as opposed to using Zend_Db quote()
	
$sql = "INSERT INTO server (key, value) VALUES (" . $db->quote($foo, $bar) . ")";

$result = $db->query($sql);
	
// as opposed to the traditional mysql_real_escape_string()
	
$sql = "INSERT INTO server (key, value) VALUES ('" . mysql_real_escape_string($foo) ."','".
                                                                       mysql_real_escape_string($bar) ."')";
	
$result = $db-query($sql);
	
?>

All three of these examples accomplish the same thing. The first example we create our query using oraclesque placeholders, and then send the data as the second parameter to the query() method.

The second example uses Zend_Db’s quote() method which escapes and adds quotes to the parameters passed to it. If multiple parameters are passed to quote() then it quotes the individual parameters and joins them using commas. Quote() uses a database specific quoting implementation, so this is safe and will correctly quote your string. The complete SQL string is then passed to the query() method.

Regardless of whether or not a second parameter exists to the query() method, it will still prepare and execute the query. So there isn’t much gained by using the quote() method on its own.

The third example is a throw-back to the bad-old-days when we had to use functions such as mysql_real_escape_string(), or worse addslashes() to ensure our data was correctly escaped before entering into our database. This obviously should be avoided at all costs.

Transactions

Database transactions provide a way to send a specific set of queries to the database as a set which can be completed (committed) or aborted (rolled back). Transactions are available in all modern database systems (which can be referred to as transactional databases), they help to ensure that the integrity of a database is maintained. A simple example of where you would want to use transactions is where you have an operation which requires several queries to complete. If any one of the queries were to fail it would leave the database with incomplete data, so you would use a transaction to ensure that either all queries are completed, and in the case of a failed query the transaction is “rolled back” and none of the queries are made.

By default Zend_Db is in autocommit mode, this means that all queries that are made are automatically committed. The following example takes Zend_Db out of autocommit mode, runs a transaction, and then returns to autocommit mode when it’s complete.

	


<?php
	
...
	
$db->beginTransaction();
	
try {
    $db->insert('server', array('key' => 'foo', 'value' => 'bar'));
    $db->insert('server', array('key' => 'bar', 'value' => 'baz'));
    $db->insert('server', array('key' => 'baz', 'value' => 'foo'));
    $db->commit();
	
} catch (Exception $e) {
    $db->rollback();
    $e->getMessage();

}
	
...
	
?>

This code can be pasted into our code example where we populate the SQLite database with PHP’s $_SERVER data. Here we start a transaction and add three rows. If any of the rows fail, we roll back the commit, print out the error message and continue as if nothing happened. This transaction is trivial and there is nothing that could go wrong. However, when you have a complex system then a transactional database is a must. Support for transactions were added to MySQL in version 4.1 and have been available in most other databases as a standard feature.

In Conclusion

This segment in our ongoing Zend Frameworks Hidden Gems series merely scratches the surface of what is available in the Zend_Db set of classes, however it’s my hope that it gives you enough information to get a feel for how it can work for you. In the next article in the multi-part Zend_Db segment we will discuss convenience functions that Zend_Db_Adaptor provides. This includes how to simply add/update and delete rows, as well as how to work with the Zend_Db query builder. After that we’ll have articles on how to profile your database using Zend_Db_Profile and how to use Zend_Db_Table which implements the TableModule pattern for easy object oriented database interaction.

Next week however, we’ll move away from databases and look at something entirely different. Until then, Happy Coding!

Vote for this article on dzone.com
Vote for this article on digg.com