Flourish SQL
Quick Links
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)
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:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| smallint | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| integer | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| bigint | ✓ | ✓ | ✓ | integer | ✓ | ✓ |
| integer autoincrement primary key | integer generated by default as identity primary key | integer identity(1) primary key | integer auto_increment primary key | integer + sequence + trigger | serial primary key | integer primary key autoincrement |
| float | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| real | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| decimal | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| char | ✓ | nchar | ✓ | ✓ | ✓ | ✓ |
| varchar | ✓ | nvarchar | ✓ | varchar2 | ✓ | ✓ |
| text | clob | ntext | mediumtext | clob | ✓ | ✓ |
| blob | ✓ | image | longblob | ✓ | bytea | ✓ |
| timestamp | ✓ | datetime | datetime | ✓ | ✓ | ✓ |
| date | ✓ | date for 2008, datetime for 2005 | ✓ | ✓ | ✓ | ✓ |
| time | ✓ | time for 2008, datetime for 2005 | ✓ | timestamp | ✓ | ✓ |
| boolean | char(1) | bit | ✓ | number(1) | ✓ | ✓ |
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.
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).
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| Mathematical Operators | ||||||
| + | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| - | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| / | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| * | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| % | mod() | ✓ | ✓ | mod() | ✓ | ✓ |
| String Operators | ||||||
| || (concatenation) | ✓ | + | ✓ | ✓ | ✓ | ✓ |
| Comparison Operators | ||||||
| < | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| > | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| <= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| >= | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| <> (inequality) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| != | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| = | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| IN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| NOT IN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| IS (equality with NULL) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| IS NOT (inequality with NULL) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| LIKE (case insensitive) | lower(value) LIKE lower(pattern) | ✓ | ✓ | lower(value) LIKE lower(pattern) | ILIKE | ✓ |
| Boolean Operators | ||||||
| AND | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| OR | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
Functions
There a quite a few functions that are consistent across the different databases:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| Mathematical Functions | ||||||
| abs(x) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| acos(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| asin(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| atan(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| atan2(x, y) | ✓ | atn2(x, y) | ✓ | ✓ | ✓ | PHP Callback |
| ceil(x) | ceiling(x) | ceiling(x) | ✓ | ✓ | ✓ | PHP Callback |
| ceiling(x) | ✓ | ✓ | ✓ | ceil(x) | ✓ | PHP Callback |
| cos(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| cot(x) | (1/tan(x)) | ✓ | ✓ | (1/tan(x)) | ✓ | PHP Callback |
| degrees(x) | ✓ | ✓ | ✓ | (x * 57.295779513083) | ✓ | PHP Callback |
| exp(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| floor(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| ln(x) | ✓ | log(x) | ✓ | ✓ | ✓ | PHP Callback |
| log(b, x) | (ln(x)/ln(b)) | (log(x)/log(b)) | ✓ | ✓ | ✓ | PHP Callback |
| pi() | 3.14159265358979 | ✓ | (pi()+0.0000000000000) | 3.14159265358979 | ✓ | PHP Callback |
| power(x, y) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| radians(x) | ✓ | ✓ | ✓ | (x * 0.017453292519943) | ✓ | PHP Callback |
| random() | rand() | rand() | rand() | (abs( dbms_random.random ) / 2147483647) | ✓ | (abs( random()) / 9223372036854775807) |
| round(x) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| sign(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| sqrt(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| sin(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| tan(x) | ✓ | ✓ | ✓ | ✓ | ✓ | PHP Callback |
| Aggregate Functions | ||||||
| avg(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| count(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| max(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| min(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| sum(l) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| String Functions | ||||||
| trim(s) | ✓ | rtrim(ltrim(s)) | ✓ | ✓ | ✓ | ✓ |
| rtrim(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| ltrim(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| upper(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| lower(s) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| substr(s, start, length) | ✓ | substring(s, start, length) | ✓ | ✓ | ✓ | ✓ |
| replace(s, find, replace) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| length(s) | ✓ | len(s) | ✓ | ✓ | ✓ | ✓ |
| coalesce(a, b,...) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| Date/Time Functions | ||||||
| CURRENT_TIMESTAMP | ✓ | ✓ | ✓ | ✓ | ✓ | datetime( CURRENT_TIMESTAMP, 'localtime') |
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.
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:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| TRUE | '1' | '1' | ✓ | 1 | ✓ | '1' |
| FALSE | '0' | '0' | ✓ | 0 | ✓ | '0' |
Expressions
There are a few expressions that are consistent across databases:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| BETWEEN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| CASE (simple) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| CASE (complex) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
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.
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| , (cross join) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| CROSS JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| [INNER] JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| LEFT [OUTER] JOIN | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
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:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| DISTINCT (follows SELECT) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| ALL (follows SELECT) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| LIMIT (without an offset) | FETCH FIRST n ROWS ONLY | TOP | ✓ | subquery with rownum clause | ✓ | ✓ |
| LIMIT/OFFSET | subquery with row_number() clause and removal of extra column via PHP | subquery with row_number() clause and removal of extra column via PHP | ✓ | nested subqueries with rownum clauses and removal of extra column via PHP | ✓ | ✓ |
| UNION | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| UNION ALL | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
Transactions
Transaction control differs slightly among the supported databases:
| Flourish SQL | DB2 | MSSQL | MySQL | Oracle | PostgreSQL | SQLite |
|---|---|---|---|---|---|---|
| BEGIN | auto-commit disabled | BEGIN TRANSACTION | ✓ | auto-commit disabled | ✓ | ✓ |
| COMMIT | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| ROLLBACK | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
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.
SQLite supports the syntax, however enforcement has to be done through triggers. translatedQuery() will create the necessary triggers to enforce foreign key constraints on an SQLite database.
Create Table/Index Syntax
Since each database supported has quite a few proprietary extensions to the SQL standard, below is a grammar that can be used with Flourish to ensure that CREATE TABLE and CREATE INDEX statements will work on all databases. Element in [] are optional, ... indicates that an element can be repeated, and | inside of {} indicates that any of the pipe-separated elements can be chosen.
Create Table
CREATE TABLE table_name (
{
column_name data_type [ DEFAULT default_value ] [ column_constraint [ ... ] ] |
table_constraint
}, ...
)
where table_constraint is:
{
UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( expression ) |
FOREIGN KEY ( column_name ) REFERENCES referenced_table ( referenced_column )
[ ON DELETE action ] [ ON UPDATE action ]
}
and column_constraint is:
{
NOT NULL |
NULL |
UNIQUE |
PRIMARY KEY |
CHECK ( expression ) |
REFERENCES referenced_table ( referenced_column ) [ ON DELETE action ]
}
and action is:
{ RESTRICT | CASCADE | SET NULL | NO ACTION }
Create Index
CREATE [ UNIQUE ] INDEX index_name ON table_name ( column_name [, ... ] )
