Reviewing extension SQL for every engine #

This page is for database administrators who must approve an extension before it is installed on a site running PostgreSQL, SQLite, Oracle or MongoDB. Exponential's own SQL runs on every supported engine; extension code and hand-written SQL often do not, because most extensions were written against MySQL. This page lists what to look for and where. Source: 9.8 Cross-engine notes.

Contents #

1. Where an extension's SQL lives
2. What to search for
3. Driver helpers
4. Semantics that differ
5. MongoDB
6. Testing
7. Checklist

Where an extension's SQL lives #

• share/db_schema.dba and share/db_data.dba: the engine-neutral schema, the best case; the upgrade check reads it.
• sql/<engine>/*.sql: DDL per engine. An extension with only sql/mysql/ has no tables for other engines yet.
• PHP: eZPersistentObject definitions (portable) and hand-written statements in query() and arrayQuery() calls (the ones to read).
• Update files of the extension's own releases.

What to search for #

grep -rn '`' extension/<name> --include=*.php | grep -i 'select\|insert\|update\|delete'     # backticks
grep -rniE 'FROM [a-z_]+ AS [a-z_]+' extension/<name> --include=*.php                        # AS before a table alias
grep -rniE 'SET (NAMES|FOREIGN_KEY_CHECKS|SQL_MODE|AUTOCOMMIT)' extension/<name>               # MySQL session statements
grep -rniE 'LIMIT [0-9]+ *, *[0-9]+|IFNULL|GROUP_CONCAT|ON DUPLICATE KEY|REPLACE INTO' extension/<name>   # MySQL dialect

Driver helpers #

The drivers implement what differs between engines; code should call them instead of writing the SQL:

Semantics that differ #

• Empty strings: Oracle returns NULL for '' unless OracleEmptyStringForNull=enabled.
• Case: MySQL's default collations ignore case; PostgreSQL and Oracle compare with case; SQLite compares = with case and ignores ASCII case in LIKE. Compare lower(...) on both sides where case must not matter (Character sets and collations).
• Failure versus empty result: a failed statement and an empty result are not reported alike on every engine. To ask whether a table exists, ask the catalog (the driver's eZTableList()), not a SELECT that may fail.
• Index names are database-wide on SQLite and PostgreSQL; the schema handlers create a clashing name as <table>__<name>. Keep names within the lint limits (The .dba schema format).
• Integers: column ranges differ ($db::MAX_INT); $db->clampInteger() keeps a value in range.
• Error codes differ (Database error codes and messages).

MongoDB #

The adapter never runs SQL; it translates what it can and returns an empty result for the rest, logging MONGO TODO. An extension with hand-written SQL needs either SQL the adapter understands or a MongoDB path of its own. Click through every feature of the extension on a MongoDB staging copy and grep the logs (MongoDB: limits and operations).

Testing #

1. Install the extension on a staging copy of the target engine.
2. Run its schema through ezsqlinsertschema.php or its installer; run the upgrade check.
3. Use every feature; watch var/log/error.log and the debug output with SQLOutput=enabled for your address.
4. Run the extension's own tests if it has them.

Checklist #

• To do: The extension has share/db_schema.dba or SQL files for the target engine.
• To do: No backticks, no AS before table aliases, no MySQL-only session statements.
• To do: Driver helpers used for strings, hashes, bits, paging and ids.
• To do: Case-insensitive lookups use lower(...).
• To do: Tested on staging on the target engine without new errors.

Related pages #

• Extension development
• Choosing an engine
• Database support
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.