Zeta Components - high quality PHP components

• Tutorial
• API
• Oracle_setup

eZ Components - Database

Introduction

Before attempting to use the Database component, you should familiarize yourself with the PHP Data Objects (PDO) documentation. The Database component builds upon PDO and we do not provide examples or explanations of the PDO basics.

The Database component consists of two main parts:

1. Database handlers derived from PDO with some added functionality. A database handler provides a common API for all databases to execute queries on a database. An introduction can be found in the PHP PDO documentation. Most importantly, the handlers in the components add support for nested ezcDbHandler::beginTransaction() and ezcDbHandler::commit() calls. The handlers also provide factory methods for the query abstraction layer.

2. The query abstraction layer. This layer provides an object-oriented API for creating SELECT, INSERT, UPDATE and DELETE queries. Using a single interface, you can create syntactically equal queries for the supported databases. This layer removes all need to do string processing in order to build your queries and helps to avoid syntax errors. Note that the query layer does not remove semantic/logical differences between databases.

Supported databases

The Database component currently supports:

• MSSQL

• MySQL

• PostgreSQL

• Oracle

• SQLite

Class overview

This section gives you an overview of the main classes of the Database component.

Handlers

ezcDbHandler

ezcDbHandler extends PDO and provides the common interface for all the component's database handlers. The handlers should be instantiated using ezcDbFactory.

ezcDbFactory

ezcDbFactory is exactly that: a factory for database handlers. It should always be used when instantiating a database handler.

ezcDbInstance

Usually, the database is used in several different places throughout your application. It is inconvenient to pass the handler around and insecure to store it in a global variable. The singleton ezcDbInstance allows you to store any number of database handlers and use these everywhere in your application.

Query abstraction

ezcQuerySelect

Interface to create SELECT queries. Instances of ezcQuerySelect should be retrieved from the database handler factory method ezcDbHandler::createSelectQuery().

ezcQueryInsert

Interface to create INSERT queries. Instances of ezcQueryInsert should be retrieved from the database handler factory method ezcDbHandler::createInsertQuery().

ezcQueryUpdate

Interface to create UPDATE queries. Instances of ezcQueryUpdate should be retrieved from the database handler factory method ezcDbHandler::createUpdateQuery().

ezcQueryDelete

Interface to create DELETE queries. Instances of ezcQueryDelete should be retrieved from the database handler factory method ezcDbHandler::createDeleteQuery().

ezcQueryExpression

ezcQueryExpression provides the interface to create SQL statements common to SELECT, INSERT, UPDATE and DELETE queries. Examples are methods like ezcQueryExpression::add() to add two or more numbers and ezcQueryExpression::now() to create the current time. Each query has an expression object available through the variable $expr.

Handler usage

This section shows how to use the factory and the instance as well as how to execute some typical queries. For more information on how to perform queries using the handlers, we recommend reading the PHP PDO documentation.

In order to get started, you need a database handler. The first example shows how to create one using ezcDbFactory and how to store the handler in ezcDbInstance so it can be easily retrieved later:

1. <?php
3. $db = ezcDbFactory::create( 'mysql://user:password@host/database' );
4. ezcDbInstance::set( $db );
6. // anywhere later in your program you can retrieve the db instance again using
7. $db = ezcDbInstance::get();
9. ?>

Executing a simple query and returning the result right away can be done with the PDO::query() method:

1. <?php
3. $db = ezcDbInstance::get();
5. $rows = $db->query( 'SELECT * FROM quotes' );
7. // Iterate over the rows and print the information from each result.
8. foreach( $rows as $row )
9. {
10.     print_r( $row );
11. }
12. ?>

Next, we show a simple example with statements and the use of bind. Binding values can be very valuable both in terms of efficiency and security. The main difference with normal queries is that the bound value will be transferred to the SQL server independent of the main query. See the section 'Avoiding SQL injection' below.

1. <?php
3. $db = ezcDbInstance::get();
4. $stmt = $db->prepare( 'SELECT * FROM quotes where author = :author' );
5. $stmt->bindValue( ':author', 'Robert Foster' );
7. $stmt->execute();
8. $rows = $stmt->fetchAll();
10. ?>

Lazy initialization

Lazy initialization is a mechanism to load and configure a component, only when it is really used in your application. This mechanism saves time for parsing the classes and configuration, when the component is not used at all during one request. You can find a description how you can use it for your own components and how it works in the ezcBase tutorial. The keyword for the database component is ezcInitDatabaseInstance.

1. <?php
2. require_once 'tutorial_autoload.php';
4. class customLazyDatabaseConfiguration implements ezcBaseConfigurationInitializer
5. {
6.     public static function configureObject( $instance )
7.     {
8.         switch ( $instance )
9.         {
10.             case false: // Default instance
11.                 return ezcDbFactory::create( 'mysql://user:password@host/database' );
12.             case 'sqlite':
13.                 return ezcDbFactory::create( 'sqlite://:memory:' );
14.         }
15.     }
16. }
18. ezcBaseInit::setCallback( 
19.     'ezcInitDatabaseInstance', 
20.     'customLazyDatabaseConfiguration'
21. );
23. // Create and configure default mysql connection
24. $db = ezcDbInstance::get();
26. // Create and configure additional sqlite connection
27. $sb = ezcDbInstance::get( 'sqlite' );
28. ?>

ezcBaseInit::setCallback accepts as a first parameter a component specific key, which lets the component later request the right configuration callback. The second parameter is the name of the class to perform the static callback on. This class must implement the ezcBaseConfigurationInitializer class. Each component's lazy initialization calls the static method configureObject() on the referenced class.

This example shows a way to configure multiple database handlers, only when they are really requested in your application. The example does basicaly the same like the first example in this tutorial, but creates the connection not before it is really required.

In line 24 the default database handler is first requested in this example, which does not exist yet, so that the configuration class earlier referenced through the setCallback() call will be asked for a new instance for the current instance name, which is (bool) false for the default instance.

In the configureObject() method in line 8 we switch on the instance name and create and return the right newly created database handler. Line 27 shows, that this will also work with multiple database instances, creating an additional SQLite connection.

Query abstraction usage

This section gives you a basic introduction on how to build queries using the query abstraction layer.

We will start out by recreating the first query example:

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
6. $q->select( '*' )->from( 'quotes' );
8. $stmt = $q->prepare();
9. $stmt->execute();
11. ?>

As you can see, building the query itself follows the build-up of a normal query and is rather straightforward. The rest of the example is a bit more verbose; this is mainly due to the fact that you need to fetch the query object from the handler and that you are required to use prepared statements with the query abstraction layer. The factory methods in the handler to fetch the query object ensure that you get a query of the correct type, regardless of what database you use. Please note that if you want to use table/column names that are SQL keywords, such as group, you need to quote the table/column names manually with ezcDbHandler::quoteIdentifier().

Bind parameters

The next example is based on the previous one, but builds a more complex query and introduces the usage of bind parameters in the query:

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
6. $e = $q->expr; // fetch the expression object
7. $q->select( '*' )->from( 'quotes' )
8.     ->where( $e->eq( 'author', $q->bindValue( 'Robert Foster' ) ) )
9.     ->orderBy( 'quote' )
10.     ->limit( 10, 0 );
12. $stmt = $q->prepare();
13. $stmt->execute();
15. ?>

The query will fetch the first ten quotes by Robert Foster, sorted by the quote itself. Note that string parameters must either be bound using ezcQuery::bindParam()/ezcQuery::BindValue() or escaped and quoted manually.

As you can see, logical expressions are built up using the expression object of the type ezcQueryExpression. Note that the methods for logical or and and are named lOr and lAnd, respectively. This is because and and or are reserved names in PHP and cannot be used in method names.

The next example shows that you can insert, update and delete rows from a table using the query abstraction layer, in a similar way to the SELECT query.

The example below shows how to create and use basic INSERT, UPDATE and DELETE query objects.

1. <?php
3. $db = ezcDbInstance::get();
5. // Insert
6. $q = $db->createInsertQuery();
7. $q->insertInto( 'quotes' )
8.   ->set( 'id', 1 )
9.   ->set( 'name', $q->bindValue( 'Robert Foster' ) )
10.   ->set( 'quote', $q->bindValue( "It doesn't look as if it's ever used!" ) );
11. $stmt = $q->prepare();
12. $stmt->execute();
14. // update
15. $q = $db->createUpdateQuery();
16. $q->update( 'quotes' )
17.   ->set( 'quote', 'His skin is cold... Like plastic...' )
18.   ->where( $q->expr->eq( 'id', 1 ) );
19. $stmt = $q->prepare();
20. $stmt->execute();
22. // delete
23. $q = $db->createDeleteQuery();
24. $q->deleteFrom( 'quotes' )
25.   ->where( $q->expr->eq( 'name', $q->bindValue( 'Robert Foster' ) ) );
26. $stmt = $q->prepare();
27. $stmt->execute();
28. ?>

Multi-join syntax

The next examples show how to use multi-join syntax to build queries with several joined tables using inner, right or left join.

The innerJoin(), rightJoin() and leftJoin() methods can be used in three forms:

1. The first form takes two string arguments (table name and join condition) and returns an ezcQuery object. Each invocation joins one table. You can invoke the *Join() method several times.

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
7. // Right join of two tables. Will produce SQL:
8. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id".
9. $q->select( 'id' )->from( 'table1' )->rightJoin( 'table2', $q->expr->eq( 'table1.id', 'table2.id' ) );
11. $stmt = $q->prepare();
12. $stmt->execute();
14. // Right join of three tables. Will produce SQL:
15. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id < table2.id RIGHT JOIN table3 ON table2.id > table3.id".
16. $q->select( 'id' )
17.         ->from( 'table1' )
18.             ->rightJoin( 'table2', $q->expr->lt( 'table1.id', 'table2.id' ) )
19.             ->rightJoin( 'table3', $q->expr->gt( 'table2.id', 'table3.id' ) );
21. $stmt = $q->prepare();
22. $stmt->execute();
23. ?>

1. This is a simplified version of form 1, where the join condition is always set to "equal".

   rightJoin( 'table1', 'table1.id', 'table2.id' ) is a shorter equivalent of rightJoin( 'table1', $this->expr->eq('table1.id', 'table2.id' ) );

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
7. // Right join of three tables. Will produce SQL:
8. // "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id RIGHT JOIN table3 ON table2.id = table3.id".
9. $q->select( 'id' )
10.         ->from( 'table1' )
11.             ->rightJoin( 'table2', 'table1.id', 'table2.id' )
12.             ->rightJoin( 'table3', 'table2.id', 'table3.id' );
14. $stmt = $q->prepare();
15. $stmt->execute();
18. ?>

1. This is a simple form that can join only two tables. It takes four string arguments and returns an SQL string. This exists mainly for backwards compatibility reasons.

1. <?php
3. $db = ezcDbInstance::get();
5. $q = $db->createSelectQuery();
7. // $q->rightJoin( 'table1', 'table2', 'table1.id', 'table2.id' ) will produce
8. // string "table1 RIGHT JOIN table2 ON table1.id = table2.id"
9. // that should be added to FROM clause of query.
10. // resulting query is "SELECT id FROM table1 RIGHT JOIN table2 ON table1.id = table2.id".
11. $q->select( 'id' )->from( $q->rightJoin( 'table1', 'table2', 'table1.id', 'table2.id' ) );
12. $stmt = $q->prepare();
13. $stmt->execute();
15. ?>

This final example shows how to build subselect queries inside SELECT:

1. <?php
3. $name = 'IBM';
4. $q = new ezcQuerySelect( ezcDbInstance::get() );
6. // Creating subselect object
7. $q2 = $q->subSelect();
9. // $q2 will build the subquery "SELECT company FROM query_test WHERE
10. // company = :ezcValue1 AND id > 2". This query will be used inside the SQL for
11. // $q.
12. $q2->select( 'company' )
13.    ->from( 'query_test' )
14.    ->where( $q2->expr->eq( 'company', $q2->bindParam( $name ) ), 'id > 2' );
16. // $q the resulting query. It produces the following SQL:
17. // SELECT * FROM query_test
18. // WHERE  id >= 1  AND
19. //     company IN ( (
20. //         SELECT company FROM query_test
21. //         WHERE company = :ezcValue1 AND id > 2
22. //     ) )
23. $q->select('*')
24.   ->from( 'query_test' )
25.   ->where( ' id >= 1 ', $q->expr->in( 'company', $q2 ) );
27. $stmt = $q->prepare();
28. $stmt->execute();
30. ?>

Avoiding SQL injection

SQL injection is possibly the biggest single cause of major security problems in web applications. SQL injections are caused when building SQL statements that include untrusted data. If the untrusted data is not escaped properly or checked for proper input, you are susceptible to SQL injections.

With the introduction of bound values, it is possible to avoid SQL injection altogether. Simply use bind to insert untrusted data into a query. This is usually more efficient as well, since you do not need to escape the data and the SQL server does not have to parse it as part of the query string.

Character set issues

This component currently does not deal with character sets support. It relies on the different databases' own mechanisms to deal with this. For MySQL that means that you might have to set the character set yourself on the ezcDatabaseHandler instance with something like:

Oracle treats character sets totally different, and their driver needs to know which character set to use before connecting to the database, or starting the database. For this you need to make an environment setting. An example of such setting could be:

The value of the NLS_LANG environment variable differs according to locale and character set, please see the oracle website for some default values and a FAQ.

Notable differences between databases

Even though the query abstraction layer creates syntactically equal queries for the supported databases, the results may still differ. This is due to a large number of differences between the databases.

For example, SQLite does not support the rebinding of values. If you have an insert query and want to reuse it as outlined below, an error will be returned:

Instead, you should use bindParameter() to achieve the same effect:

Restrictions

To keep the compability between different relational database management systems, there are restrictions you have to cope with:

• Lowercase column names

  As some RDBMS always do not store column names case sensitive, but return and strore them completely as upper case letters, ezcDatabase converts all column names to lower case to offer a uniform interface.

Adding support for a new database

This section explains the basic steps to take to create support for a new database. The following steps are rudimentary but should help you along the way.

1. Check out the Database component from the eZ Systems SVN server. This is necessary in order to use the testing system. This allows you to easily see if your code works as it should.

2. Create a handler for the new database. The handler must inherit from ezcDbHandler. Do not reimplement the methods for the query abstraction layer; they will then default to MySQL syntax.

3. Run the test system and check whether any of them fail. If any tests fail, you have to extend the class and method in question and make sure that the generated query is correct for your database.