User:Jack Phoenix/Porting to alternative DBMSes

Quick and dirty guide on how to convert MySQL-specific SQL files to be compatible with alternative DBMSes. Well, not exactly convert, since you have to create a new .sql file for each alternative DBMS, but you get the idea anyway.
 * We should review Manual:Hooks/LoadExtensionSchemaUpdates which lists some ways to create schemas, which is MySQL-only. For example, separate schemas for other DBs are not foreseen. Also for PostgreSQL we don't use small SQL patchfiles, since  gives us methods to create/modify/delete fields/indexes directly.

This is not and will never be an exhaustive guide; this is just a glimpse at creating schemas for non-MySQL/MariaDB backends for developers like myself who have absolutely no experience with (nor interest in) PostgreSQL/Oracle/MSSQL.

We should update:
 * Manual:Database layout (reminder to use updater functions, two tables have different names, using )
 * Manual:Database access
 * PostgreSQL config examples
 * Info on PRIMARY KEYS in description of wrappers, more on INSERT IGNORE and REPLACE
 * Review Database field prefixes, maybe mention sequence naming schemes there for all DBMS
 * Check Database optimization
 * Review Database testing (forgotten doc?)
 * Check Database transactions (seems quite current)

See also r45764 by Tim Starling, committed on 15 January 2009. Diff on ViewVC

Dealing with automatic ID assignement
For MySQL and SQLite, use  type.

Do not assume that INSERTing a row with omitted primary key autogenerates the value (as it does for MySQL and sometimes for PostgreSQL).

For PostgreSQL and ORACLE you have to create a sequence object. Please observe naming conventions - for PostgreSQL it all can be simplified by using  pseudo-datatype, which automagically creates a sequence and assigns the default value to the column. For Microsoft SQL, those objects are called  and contain   data type column called.

Use this database-agnostic pattern in PHP to deal with primary key values: