<<toc></toc>>
One of the aims of Flourish is to make a portable PHP library so that applications can be deployed on different servers with little to no changes. With the multitude of database servers available and different platforms, one of the main hurdles for portability is the different dialects of SQL.
Flourish SQL has grown a common subset of the SQL dialects supported by IBM DB2, Microsoft SQL Server, MySQL, PostgreSQL and SQLite, with a few additions for ease-of-use. This document describes what features and syntax are supported across all databases.
In the Flourish SQL column you will find the supported syntax for a function, data type, etc. The other columns contain a `✓` if the syntax is the same, or the database-specific syntax highlighted in red.
The information on this page is targeted at the following database versions, or newer editions:
- '''DB2:''' 9.x - '''MSSQL:''' Server 2005 - '''MySQL:''' 5.0 - '''Oracle:''' 10g - '''PostgreSQL:''' 8.0 - '''SQLite:''' 2.8.17 ''(v2)'', 3.2.8 ''(v3)''
If targeting MSSQL or DB2, please be aware that `UNIQUE` constraints only allow a single `NULL` value to be present in all rows. To work around this, split the column in to a separate table and use a foreign key to associate it to the original table.
Oracle does not support the `ON UPDATE` clause for foreign keys. MSSQL does not allow `ON UPDATE CASCADE` or `ON DELETE CASCADE` clauses for foreign keys that could cause cycles during handling. This is often caused by multiple columns in a table referencing the same foreign table.
MySQL, DB2, Oracle and MSSQL all have various constraints on `TEXT` and `BLOB` data types. To achieve cross-database support, be sure not to do the following with such columns:
- Set a `DEFAULT` value - Use them in a `GROUP BY` clause - Use them as a `PRIMARY KEY` or `FOREIGN KEY` - Use them in a `UNIQUE` constraint
Since fRecordSet uses `GROUP BY` clauses to allow for selection of records by values in related tables, it is highly recommended that `TEXT` and `BLOB` columns always be split out into a separate table and to use a foreign key to associate it with the original table. The Flourish ORM has strong support to easily retrieving such related records.
The following Flourish SQL is supported when calling the fDatabase::translatedQuery() and fDatabase::unbufferedTranslatedQuery() methods. The Cross-Database SQL section of the fDatabase page has more information.
Most dialects of SQL support a large number of common data types, however a things like booleans, binary data and dates and times are often different. Here is an outline of the data types supported by Flourish SQL:
In all cases except for the `date` and `time` are the data types consistent across the databases. MSSQL does not support `date` or `time`, so the more precise `datetime` is used instead. Oracle does not support `time` and uses `timestamp` instead. In both cases, the unnecessary portions are removed by the `unescape()` method of fDatabase.
One other thing to note is the apparent perfect compatibility for SQLite data types. This is not because the SQLite data types were chosen as the basis for Flourish SQL, but rather that SQLite is loosely-typed and allows anything to be entered for a data type.
Since boolean fields are implemented quite differently across databases, it comes as no surprise that there are different values to use for boolean fields:
Transaction control differs slightly among the supported databases:
The following constructs are are used with the data manipulation language (DML) statements in SQL. Such statements include `SELECT`, `INSERT`, `UPDATE` and `DELETE`.
Operators are fairly consistent across the different databases, with the exception of the concatenation operator. Please note that `||` works for concatenation in MySQL only when MySQL is in ANSI mode (Flourish automatically switches into ANSI mode when a connection to a MySQL database is initiated).
There a quite a few functions that are consistent across the different databases:
A quick scan of the SQLite column indicates that the trigonometric functions are all implemented as PHP callbacks. SQLite made the decision to include only specific functions in the supported SQL dialect, however they added support for hooks into other programming languages. Flourish uses this ability to provide support for trigonometric functions.
There are a few expressions that are consistent across databases:
There are quite a few different types of join supported by the four different databases, however the common join functionality is a small subset. Note that all joins except `CROSS` and `,` require use of an `ON` clause, while `CROSS` and `,` can not use an `ON` clause.
The brackets indicate the words `OUTER` and `INNER` are optional.
You may notice the lack of a `FULL [OUTER] JOIN`, `RIGHT [OUTER] JOIN`, the keyword `NATURAL` and `USING` clauses. Unfortunately DB2, MySQL and SQLite do not support `FULL` joins, SQLite does not support `RIGHT` joins and DB2 and MSSQL do not support `NATURAL` joins. `USING (col, ...)` clauses are not supported by MSSQL.
`FULL` joins can be achieved by a somewhat complex combination of a `SELECT` from the first table with extra columns `UNION`ed with an `INNER JOIN`, `UNION`ed with a `SELECT` from the second table with extra columns.
`RIGHT` joins can be performed by switching the order of the tables in the `FROM` clause.
`NATURAL` joins are just shorthand for the common columns between tables, so all that needs to be done is an `INNER` join with manually specified columns in the `ON` condition.
`USING` clauses can be approximated with a `ON` condition, however the `USING` operator ensures that only one copy of each column is included in the returned rows, whereas `ON` does not.
There are a few clauses that are supported across all databases:
The following are the supported data definition language (DDL) statements in Flourish SQL. Some examples of DDL statements include `CREATE TABLE`, `ALTER TABLE` and `CREATE INDEX`.
While DML statements between the supported databases tend to be fairly consistent, DDL statements tend to be much more varied. For instance, all of the supported databases use different constructs to achieve auto-incrementing primary keys.
The following grammars will show the supported Flourish SQL syntax, but do not cover exactly what is run for each type of database. To see what processing takes place, please see the fSQLSchemaTranslation source code, or call fDatabase::enableDebugging() before executing DDL statements.
The following syntax is supported for `CREATE TABLE` statements. By strictly following this syntax, and using the [#DataTypes], `CREATE TABLE` statements should work identically across all six supported database systems.
where column_constraint is:
table_constraint is:
and action is:
The `ALTER TABLE` statements are some of the most powerful aspects of Flourish SQL since the native support for `ALTER TABLE` varies wildly between databases. In addition to varying syntax, many databases require explicit constraint names for dropping `UNIQUE`, `CHECK`, `FOREIGN KEY` and `PRIMARY KEY` constraints. These constraint names are usually generated by the system and are not consistent across different databases.
To solve these problems, fSQLSchemaTranslation introspects the database and alters the SQL statements to work with the different database engines. In some cases multiple SQL statements must be executed. fSQLSchemaTranslation is written in such a way that each of the statements listen below should be individually atomic. Thus, if the statement fails, the database should remain at the state before the statement was executed.
PostgreSQL, SQLite and MSSQL all fully support transactions for DDL statements, which makes this easy. For DB2, most DDL statements run within a transaction, and the one that doesn't (`REORG TABLE`) is only executed once the primary statement succeeds. For Oracle, all of these statement correspond to a single SQL statement, so the statement will either succeed or fail. MySQL requires storing rollback statements for all database operations, and such statements are run if a statement fails. The tests for Flourish include a bunch of tests to ensure that statements are run atomically.
The syntax is strongly based on PostgreSQL's `ALTER TABLE` statements, since they are powerful and succinct. Users who are more familiar with other database systems, especially SQLite and MySQL, will hopefully find these statement significantly easier to use than native commands.
Of special note among the supported database systems is SQLite, since it only natively supports renaming tables and adding columns for version 3.x and nothing for version 2.x. Flourish does a fairly significant amount of work for SQLite, which includes:
1. parsing the existing table structure 2. modifying it into a new temporary table 3. transferring table contents to the temporary table 4. dropping the original table 5. renaming the temporary table to the original table name
Some of these steps require significant sub-steps, such as in SQLite 2, where table renaming does not exists. Luckily SQLite allows DDL statements within transactions, so everything is safe, but `ALTER TABLE` statements may run more slowly because of the extensive amount of work required.
An fSQLException will be thrown if a table with the new name already exists.
The `data_type`, `default_value` and `column_constraint` values are all exactly the same as those for [#CreateTable] statements. An fSQLException will be thrown if a column with the same name already exists or there is an error in the column definition.
An fSQLException will be thrown if a column with the new name already exists.
Any `UNIQUE`, `FOREIGN KEY` or `PRIMARY KEY` constraints that involve this column will also be dropped. An fSQLException will be thrown if the column does not exist.
For many of the supported databases, there are restrictions about what data types can be automatically converted. In general it is safe to change the data type to increase the size, such as making a `VARCHAR` longer, or changing from an `INTEGER` to a `BIGINT`.
For more drastic data type changes, it is normally necessary to:
1. Create a new column
2. Assign values to the new column from the old column, using a `CAST()`
statement
3. Drop the old column
4. Rename the new column to the old column
5. Re-create `UNIQUE`, `FOREIGN KEY` and `PRIMARY KEY` constraints that
existed on the old column
Sets the default value for a column, overriding any previous value. Some databases only support constant values for `default_value`. For example, not all databases allow setting `CURRENT_TIMESTAMP()` to be a default value since it is a function.
Drops the default value for a column. This statement will succeed even if no default value exists.
Sets a column to not allow `NULL` values, and optionally sets the default value for a column. This statement will succeed even if the column already restricts `NULL` values.
This combined form of `SET NOT NULL` and `SET DEFAULT` is useful to support `NOT NULL DEFAULT ` configurations on Oracle databases, where blank strings are automatically converted to `NULL`. `NOT NULL DEFAULT ` is useful for writing simpler SQL statements since it is no longer necessary to specially test for `NULL` values via `(column_name IS NULL OR column_name = )`.
Changes a column to allow `NULL` values. For most databases this will not succeed on columns that are part of a `PRIMARY KEY` constraint. This statement will succeed even if the column already allows `NULL`s.
Creates a `CHECK(column_name IN ('string value', 'string value'))` check constraint for the column. This check constraint will override any existing check constraint.
MySQL doesn't support `CHECK` constraints, but the `ENUM()` data type is basically a check constraint for a set of string values. Thus for MySQL, this statement will turn a `VARCHAR` column into an `ENUM()` column with the strings provided.
Drop the `CHECK` constraint for a column. An fSQLException will be thrown if no check constraint exists.
MySQL doesn't support `CHECK` constraints, but the `ENUM()` data type is basically a check constraint for a set of string values. Thus the only cross- database compatible `CHECK` constraint is `CHECK(column_name IN ('string value', 'string value 2'))`, which will be automatically converted to an `ENUM()` for MySQL. Consequently, when `DROP CHECK` is called for MySQL, an `ENUM()` column will be coverted to a `VARCHAR`.
Creates a primary key for `table_name` with the columns specified. An fSQLException will be thrown if a primary key already exists.
If only one column is specified and the `AUTOINCREMENT` keyword is provided, the column will be configured to automatically created auto-incrementing integer values when no value is provided. The actual implementation of `AUTOINCREMENT` varies widely between databases. Please see [#DataTypes] for details.
Drops the primary key for `table_name`. Any foreign keys that reference this primary key will also be dropped. An fSQLException will be thrown if a primary key does not exist.
Adds a foreign key to the column specified. Currently, Flourish as a whole, only supports single-column foreign key constraints. This restriction is also present for this `ALTER TABLE` statement. The valid `action` values are the same as those supported by [#CreateTable]. An fSQLException will be thrown if a foreign key already exist.
Since DB2 and Oracle do not fully support `ON UPDATE` clauses, such a clause will be automatically removed for those databases.
Drops the foreign key for the column specified. Currently, Flourish as a whole, only supports single-column foreign key constraints. An fSQLException will be thrown if a foreign key does not exist.
Adds a unique constraint to the column(s) specified. Some databases, such as MSSQL and DB2, treat a `NULL` value as a distinct value, only allowing one per column. PostgreSQL, SQLite, Oracle and MySQL treat `NULL` specially and allow any number of `NULL`s in a unique column.
Drop the unique constraint that exists for the column(s) specified. Any foreign keys that reference this unique constraint will also be dropped. An fSQLException will be thrown if a `UNIQUE` constraint does not exist for the column(s) specified.
For SQLite, this statement adds an inline SQL comment at the end of the line that defines the column. All other databases have a native system for storing comments.
The following syntax is supported for `CREATE INDEX` statements.
All of the databases supported by Flourish support foreign key constraints through some method. By using `translatedQuery()` to execute `CREATE TABLE` statements, you can be sure that foreign keys will be enforced.
PostgreSQL, MSSQL, Oracle and DB2 support foreign keys completely natively. MySQL supports them natively as long as the InnoDB engine type is specified in the `CREATE TABLE` statement. This is automatically added when using `translatedQuery()`.
SQLite supports the syntax, however enforcement before version 3.6.19 has to be done through triggers. `translatedQuery()` will create the necessary triggers to enforce foreign key constraints on an SQLite database.
Please note that Oracle does not support `ON UPDATE` clauses, and DB2 only supports the `NO ACTION` and `RESTRICT` actions for `ON UPDATE` clauses. Because of these limitations, it is best to avoid `ON UPDATE` clauses for cross-database applications.
In addition, Flourish is designed to support only single-column foreign keys. While most databases support multi-column foreign keys, Flourish's SQLite foreign key trigger generation only supports a single column. In addition, the `ALTER TABLE` through Flourish SQL is only designed and tested against single- column foreign keys.