| eZ Components - Database |
| ~~~~~~~~~~~~~~~~~~~~~~~~ |
| |
| .. contents:: Table of Contents |
| |
| 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: |
| |
| .. include:: tutorial_example_01.php |
| :literal: |
| |
| Executing a simple query and returning the result right away can be done with the |
| PDO::query() method: |
| |
| .. include:: tutorial_example_02.php |
| :literal: |
| |
| 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. |
| |
| .. include:: tutorial_example_03.php |
| :literal: |
| |
| .. _documentation: http://www.php.net/pdo |
| |
| 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*. |
| |
| __ introduction_Base.html#lazy-initialization |
| |
| .. include:: tutorial_lazy_initialization.php |
| :literal: |
| |
| 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: |
| |
| .. include:: tutorial_example_05.php |
| :literal: |
| |
| 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: |
| |
| .. include:: tutorial_example_04.php |
| :literal: |
| |
| 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. |
| |
| .. include:: tutorial_example_06.php |
| :literal: |
| |
| 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. |
| |
| .. include:: tutorial_example_07a.php |
| :literal: |
| |
| 2. 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' ) ); |
| |
| .. include:: tutorial_example_07b.php |
| :literal: |
| |
| 3. 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. |
| |
| .. include:: tutorial_example_08.php |
| :literal: |
| |
| This final example shows how to build subselect queries inside SELECT: |
| |
| .. include:: tutorial_example_09.php |
| :literal: |
| |
| |
| 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:: |
| |
| $db->query("SET NAMES utf8"); |
| |
| 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:: |
| |
| export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 |
| |
| 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__. |
| |
| __ http://download-uk.oracle.com/docs/cd/B19306_01/install.102/b14317/gblsupp.htm#BCEEEJGF |
| __ http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm |
| |
| |
| 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: :: |
| |
| $q->insertInto( 'query_test' ) |
| ->set( 'id', 1 ) |
| ->set( 'company', $q->bindValue( 'eZ systems' ) ) |
| ->set( 'section', $q->bindValue( 'Norway' ) ) |
| ->set( 'employees', 20 ); |
| $stmt = $q->prepare(); |
| $stmt->execute(); |
| |
| $q->insertInto( 'query_test' ); |
| $q->set( 'id', 2 ); |
| $q->set( 'company', $q->bindValue( 'Trolltech' ) ); |
| $q->set( 'section', $q->bindValue( 'Norway' ) ); |
| $q->set( 'employees', 70 ); |
| $stmt = $q->prepare(); |
| $stmt->execute(); |
| |
| Instead, you should use bindParameter() to achieve the same effect: :: |
| |
| $company = 'eZ systems'; |
| $section = 'Norway'; |
| $q->insertInto( 'query_test' ) |
| ->set( 'id', 1 ) |
| ->set( 'company', $q->bindParam( $company ) ) |
| ->set( 'section', $q->bindParam( $section ) ) |
| ->set( 'employees', 20 ); |
| $stmt = $q->prepare(); |
| $stmt->execute(); |
| |
| $q->insertInto( 'query_test' ); |
| $q->set( 'id', 2 ); |
| $q->set( 'employees', 70 ); |
| $company = 'Trolltech'; |
| $section = 'Norway'; |
| $stmt = $q->prepare(); |
| $stmt->execute(); |
| |
| .. |
| - difference in case sensitivity |
| - difference in allowed field length |
| - difference in what you can compare (e.g TRUE =! 0) |
| |
| 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. |
| |
| |
| .. |
| Local Variables: |
| mode: rst |
| fill-column: 79 |
| End: |
| vim: et syn=rst tw=79 |