Flourish SQL

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.

Supported Database Versions

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)

Limitations

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.

Usage

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.

Data Types

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.

Boolean Values

Since boolean fields are implemented quite differently across databases, it comes as no surprise that there are different values to use for boolean fields:

Transactions

Transaction control differs slightly among the supported databases:

Data Manipulation Statements

The following constructs are are used with the data manipulation language (DML) statements in SQL. Such statements include SELECT, INSERT, UPDATE and DELETE.

Operators

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).

Functions

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.

Expressions

There are a few expressions that are consistent across databases:

Joins

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.

Other Join Types

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 UNIONed with an INNER JOIN, UNIONed 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.

Clauses

There are a few clauses that are supported across all databases:

Data Definition Statements

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.

Create Table

The following syntax is supported for CREATE TABLE statements. By strictly following this syntax, and using the data types previously defined, CREATE TABLE statements should work identically across all six supported database systems.

CREATE TABLE table_name ( 
    {
        column_name data_type [ DEFAULT default_value ] [ column_constraint [ ... ] ] |
        table_constraint
    }, ...
)

where column_constraint is:

{
    NOT NULL | 
    NULL | 
    UNIQUE |
    [ AUTOINCREMENT ] PRIMARY KEY |
    CHECK ( column_name IN ( value1 [, value2 [, ... ] ] ) ) |
    REFERENCES referenced_table ( referenced_column ) [ ON DELETE action ]
}

table_constraint is:

{
    UNIQUE ( column_name [, ... ] ) |
    PRIMARY KEY ( column_name [, ... ] ) |
    CHECK ( column_name IN ( value1 [, value2 [, ... ] ] ) ) |
    FOREIGN KEY ( column_name ) REFERENCES referenced_table ( referenced_column )
        [ ON DELETE action ]
}

and action is:

{ RESTRICT | CASCADE | SET NULL | NO ACTION }

Alter Table

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.

Rename Table

ALTER TABLE table_name RENAME TO new_table_name

An fSQLException will be thrown if a table with the new name already exists.

Add Column

ALTER TABLE table_name ADD COLUMN column_name data_type [ DEFAULT default_value ] [ column_constraint [ ... ] ]

The data_type, default_value and column_constraint values are all exactly the same as those for Create Table statements. An fSQLException will be thrown if a column with the same name already exists or there is an error in the column definition.

Rename Column

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name

An fSQLException will be thrown if a column with the new name already exists.

Drop Column

ALTER TABLE table_name DROP COLUMN column_name

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.

Set Column Type

ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type

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

Set Default

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value

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.

Drop Default

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT

Drops the default value for a column. This statement will succeed even if no default value exists.

Set Not Null

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL [ DEFAULT default_value ]

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 = '').

Drop Not Null

ALTER TABLE table_name ALTER COLUMN column_name DROP NOT NULL

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 NULLs.

Set Check Constraint

ALTER TABLE table_name ALTER COLUMN column_name SET CHECK IN (string_value [, ... ] )

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 Check Constraint

ALTER TABLE table_name ALTER COLUMN column_name DROP CHECK

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.

Add Primary Key

ALTER TABLE table_name ADD PRIMARY KEY (column_name [, ... ] ) [ AUTOINCREMENT ]

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 Data Types for details.

Drop Primary Key

ALTER TABLE table_name DROP PRIMARY KEY

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.

Add Foreign Key

ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES foreign_table(foreign_column) [ ON DELETE action ]

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 Create Table. 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.

Drop Foreign Key

ALTER TABLE table_name DROP FOREIGN KEY (column_name)

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.

Add Unique

ALTER TABLE table_name ADD UNIQUE (column_name [, ... ] )

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 NULLs in a unique column.

Drop Unique

ALTER TABLE table_name DROP UNIQUE (column_name [, ... ] )

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.

Comment on Column

COMMENT ON COLUMN table_name.column_name IS 'Comment value'

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.

Drop Table

DROP TABLE table_name

Create Index

The following syntax is supported for CREATE INDEX statements.

CREATE [ UNIQUE ] INDEX index_name ON table_name ( column_name [, ... ] ) 

Drop Index

DROP INDEX index_name

Foreign Key Support

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.