Database access and SQL #

This page is for application creators who need SQL. Exponential runs on SQLite 3, PostgreSQL, MongoDB, MariaDB, Oracle and MySQL (Database support), so portable code matters more than on a single-engine product. Prefer eZPersistentObject (Persistent objects and custom tables); use SQL for reports and bulk work.

Contents #

1. The connection
2. Reading and writing
3. Escaping
4. Transactions
5. Writing portable SQL

The connection #

$db = eZDB::instance();

The instance uses the siteaccess's [DatabaseSettings]; one connection per request (per worker under Velocity).

Reading and writing #

$rows = $db->arrayQuery( 'SELECT id, title FROM expdemo_item WHERE user_id = ' . (int)$userID . ' ORDER BY created DESC',
                         array( 'offset' => 0, 'limit' => 20 ) );

$db->query( "UPDATE expdemo_item SET title = '" . $db->escapeString( $title ) . "' WHERE id = " . (int)$id );

arrayQuery() returns an array of rows (associative arrays); its second argument takes offset and limit, which the driver turns into the engine's syntax. query() returns true or false for statements that change data.

Escaping #

Never build SQL from request input without these.

Transactions #

$db->begin();
try
{
    // several writes
    $db->commit();
}
catch ( Exception $e )
{
    $db->rollback();
    throw $e;
}

Transactions nest: only the outermost commit() writes. On SQLite, writers queue for the write lock; keep transactions short (SQLite transactions).

Writing portable SQL #

• Use arrayQuery() offsets and limits, not LIMIT in the string.
• Avoid engine functions (IFNULL, GROUP_CONCAT, MOD()): the eztags fixes for SQLite replaced MOD() with % (eztags).
• Do not rely on SET @var := ... or multi-statement strings.
• MongoDB has no joins: the kernel's driver translates common statements; for your own tables, prefer eZPersistentObject, which every engine supports (MongoDB support).
• Test on SQLite and on the production engine.
• The SQL query cache can cache read results (querycache.ini) (SQL query cache).

Related pages #

• Persistent objects and custom tables
• Database schema and .dba files
• Databases and SQL standard
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.