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