eZ publish is database independent and therefore needs to have SQLs that can run on as many databases as possible.
When writing custom SQL statements in eZ publish the following rules and guidelines must be followed to ensure maxium compatability.

SQL standard

In general the SQL92 standard can be followed. Support for this in various databases are in most cases complete. This document will explain in which cases the standard cannot be followed.

Syntax

SQL commands are written in all capital letters.

This example shows two nicely formatted queries with proper syntax.

SELECT * FROM

 ezuser

WHERE

 id = '$user_id'

HAVING

 age > '42'

LIMIT 0,20

 

SELECT table_a, table_b AS c FROM

      ezcontentobject,

      ezcontentobject_attribute,

      ezcontentobject_version

WHERE

      ezcontentobject.id = ezcontentobject_version.contentobject_id AND

      ezcontentobject.id = '42'

LIMIT 0, 20

Identifiers

Identifiers are the general term for names used for tables, columns, triggers and sequences.

The following rules applies to identifiers:

• All identifiers are written in English and must be grammatically correct.
• Identifiers with several words should be separated with underscores, some words may be grouped together e.g contentobject.
• The maximum length for an identifier is 30 characters, this ensures that all identifiers can be used on all supported databases.
• If a word must be abbreviated (for length consideration) try to find an abbreviation that is easy to understands, using synonyms which are shorter may be a good idea.
• All identifiers which are defined as core functionality for eZ publish is prefixed with ez, other systems should pick their own prefix.

Example:

Tables:

ezuser

ezcontentobject_version

ezcontentclass_attribute

 

Columns:

id

first_name

last_name

login

Readability

In general keep in mind that it should be easy for any developer to read the SQL statement and understand what it does.

Some specific guidelines:

• If possible try to indent SQL statements so they match the same column as the previous line, if a statement is started on a separate line indent it with two spaces. Keep keywords like SELECT, FROM and WHERE at the start of the line and the reset of the syntax indented.
• The number of elements on one single line should be limited to one or two elements. Having everything on one line makes it hard to read the code.
• Identifiers and operators should be separated by a space.
• Keep the AND and OR statements at the end of the line after the first expression it binds together

Example of AND and OR usage.

id = 5 AND

depth > 5

 

id = 2 OR

status = 1

AS keyword

The AS keyword must in no cases be used for defining a table alias, while some databases happily allows this others will report errors.

In other words, you should write:

 SELECT t1.a, t2.b FROM table1 t1, table2 t2 WHERE [condition];

instead of:

 SELECT t1.a, t2.b FROM table1 AS t1, table2 AS t2 WHERE [condition];

Text handling

If a column uses a text-based type you must take care when inserting data to it. If there is a chance that the total length of the text exceeds 2000 bytes the code must not insert the text directly in the SQL statement but instead use bound statements (see eZDBInterface:bindVariable() )

In a query like this:

INSERT INTO emp (empno, ename, sal) VALUES (2365, 'VERYVERYLONGNAME', 2000);

you shouldn't explicitly specify a long employee name, instead you should pass that name in a bound variable ( ":ename" in this example):

INSERT INTO emp (empno, ename, sal) VALUES (2365, :ename, 2000);

Also never use columns which are of type CLOB in WHERE statements, some databases will not allow this.

SELECT * FROM ezcontentobject_attribute WHERE data_text = 'some string';

Indexes

Indexes are a great way to speed-up operations on a table and should always considered when creating a new table or adding fields to an existing one. It's hard to give standardised rules of how it should be done, instead it's up to the developer to figure out where to place them. Sometimes the only way to figure out index usage is to fill the tables with thousands or million of entries and then analysing the executed SQLs.

The following rules applies to indeces:

• Never create indexes on columns of type CLOB, some databases cannot handle this.
• The combined length of a key must never exceed 3218 characters. Character sets like UTF-8 may in some databases increase the length so make sure you test the key on several setups.
• Never use length fields on keys in an index, some databases doesn't support this. If needed it must be solved by using transformation rules for the specific databases.
• Do not create too many indexes on a table, too many indexes means that inserts and updates will be slow. The best way is to examine the SQLs that used for the related tables, examining them might give clues on what to index.

Database specifics

Sometimes it can be useful to run SQL statements which only work on one database. Reasons for this can be to improve the speed or to simplify the SQL statement.

For instance say you have to insert lots of elements to one table, in MySQL it is possible to do this with one query but most other databases does not support this.

To allow this optimization on MySQL we would do:

$db =& eZDB::instance();

if ( $db->databaseName() == 'mysql' )

{

   $db->query( "INSERT INTO ..." ); // Do a multi-insert query

}

else

{

   foreach ( $list as $item )

   {

       $db->query( "INSERT INTO ..." ); // Do a normal SQL insert

   }

}

The following database specifics must be considered.

left/right joins

Oracle 8 does not support left and right joins. This means wherever they are used you should either try rewrite the SQL to avoid using it, or write a specific SQL for Oracle.

Left/right joins are handled by placing a (+) adjacent to the column that the join would be performed on.

e.g.

SELECT DISTINCT language_code, name

FROM ezcontentobject_attribute LEFT JOIN ezcontent_translation ON locale = language_code

WHERE contentobject_id = '1' AND version = '1'

ORDER BY name

must be written as:

SELECT DISTINCT language_code, name

FROM ezcontentobject_attribute, ezcontent_translation

WHERE contentobject_id = '58' AND version = '1' AND

     locale(+) = language_code

ORDER BY name