Persistence and the database layer #

This page is for engineers who write or review code that reads and writes the database. Exponential has no ORM in the sense of Doctrine or Hibernate: it has a database factory with one driver per engine, and an active-record style mapper, eZPersistentObject, with one PHP class per table. The authoritative rules are in Database and SQL standards.

Contents #

1. The pieces
2. eZPersistentObject
3. Raw queries
4. Transactions
5. The SQL query cache
6. If you come from an ORM

The pieces #

The driver is chosen by site.ini [DatabaseSettings] DatabaseImplementation, resolved through ImplementationAlias[].

eZPersistentObject #

A subclass returns its table description from static function definition(): fields, keys, increment_key, class_name, name (the table) and function_attributes. Reads use eZPersistentObject::fetchObject() and fetchObjectList(); writes use store() and remove(). These methods escape values for you.

New tables should get a persistent class rather than hand-written SQL, and their schema must reach share/db_schema.dba, the update files of every driver, and every supported driver (Schema changes and migrations). Worked examples: Persistent objects and custom tables and the eZPersistentObject reference. The pattern is described in the catalogue as Active record.

Raw queries #

Statements are built as strings, so two rules are mandatory for any value from a request, a setting or the database:

1. Escape every string with $db->escapeString( $value ), inside single quotes.
2. Cast every number with (int) before it goes into SQL.

$db = eZDB::instance();
$rows = $db->arrayQuery(
    "SELECT id, name FROM ezsection WHERE identifier = '" . $db->escapeString( $identifier ) . "'"
    . " AND id <> " . (int)$excludeId,
    array( 'offset' => 0, 'limit' => 10 )
);

Pass offset and limit as parameters, as above, so each driver writes its own LIMIT syntax. The dialect differences are listed on Writing portable SQL.

Transactions #

• $db->begin() and $db->commit() nest by counting: only the outermost pair sends BEGIN and COMMIT. $db->rollback() rolls back the whole transaction.
• Multi-statement writes in kernel code must be wrapped in begin() and commit().
• No driver sets an isolation level; each engine runs at its own default. Code that depends on one is engine-dependent.
• SQLite serializes writers: the driver takes a lock file and runs BEGIN IMMEDIATE, waiting up to [DatabaseSettings] SQLiteTransactionWait seconds (SQLite transactions).
• MongoDB writes are not transactional: each operation commits on its own.

The SQL query cache #

querycache.ini can keep SELECT results in memory until a write to one of the tables read makes them stale. It is off by default (Mode=off), and the MongoDB driver does not use it. Once it is on, anything that writes to the database from outside Exponential (a SQL client, a restore, another program) must clear it afterwards:

php bin/php/ezcache.php --clear-id=querycache --allow-root-user

See The SQL query cache.

If you come from an ORM #

Related pages #

• Database access
• eZDB reference
• Writing portable SQL
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.