Wiki Navigation

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 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 what the syntax will be run as on the specified database. Inconsistencies between Flourish SQL and the specific databases are highlighted in red.

Supported Database Versions

The information on this page is targeted at the following database versions, or newer editions:

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	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
smallint	smallint	smallint	smallint	smallint	smallint
integer	integer	integer	integer	integer	integer
bigint	bigint	bigint	integer	bigint	bigint
integer autoincrement primary key	integer identity(1) primary key	integer auto_increment primary key	integer + sequence + trigger	serial primary key	integer primary key autoincrement
float	float	float	float	float	float
real	real	real	real	real	real
decimal	decimal	decimal	decimal	decimal	decimal
char	nchar	char	char	char	char
varchar	nvarchar	varchar	varchar2	varchar	varchar
text	ntext	mediumtext	clob	text	text
blob	image	longblob	blob	bytea	blob
timestamp	datetime	datetime	timestamp	timestamp	timestamp
date	date for 2008, datetime for 2005	date	date	date	date
time	time for 2008, datetime for 2005	time	timestamp	time	time
boolean	bit	boolean	number(1)	boolean	boolean

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	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
Mathematical Operators
+	+	+	+	+	+
-	-	-	-	-	-
/	/	/	/	/	/
*	*	*	*	*	*
%	%	%	mod()	%	%
String Operators
\|\| (concatenation)	+	\|\|	\|\|	\|\|	\|\|
Comparison Operators
<	<	<	<	<	<
>	>	>	>	>	>
<=	<=	<=	<=	<=	<=
>=	>=	>=	>=	>=	>=
<> (inequality)	<>	<>	<>	<>	<>
!=	!=	!=	!=	!=	!=
=	=	=	=	=	=
IN	IN	IN	IN	IN	IN
NOT IN	NOT IN	NOT IN	NOT IN	NOT IN	NOT IN
IS (equality with NULL)	IS	IS	IS	IS	IS
IS NOT (inequality with NULL)	IS NOT	IS NOT	IS NOT	IS NOT	IS NOT
LIKE (case insensitive)	LIKE	LIKE	lower(value) LIKE lower(pattern)	ILIKE	LIKE
Boolean Operators
AND	AND	AND	AND	AND	AND
OR	OR	OR	OR	OR	OR

Functions

There a quite a few functions that are consistent across the different databases:

Flourish SQL	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
Mathematical Functions
abs(x)	abs(x)	abs(x)	abs(x)	abs(x)	abs(x)
acos(x)	acos(x)	acos(x)	acos(x)	acos(x)	PHP Callback
asin(x)	asin(x)	asin(x)	asin(x)	asin(x)	PHP Callback
atan(x)	atan(x)	atan(x)	atan(x)	atan(x)	PHP Callback
atan2(x, y)	atn2(x, y)	atan2(x, y)	atan2(x, y)	atan2(x, y)	PHP Callback
ceil(x)	ceiling(x)	ceil(x)	ceil(x)	ceil(x)	PHP Callback
ceiling(x)	ceiling(x)	ceiling(x)	ceil(x)	ceiling(x)	PHP Callback
cos(x)	cos(x)	cos(x)	cos(x)	cos(x)	PHP Callback
cot(x)	cot(x)	cot(x)	(1/tan(x))	cot(x)	PHP Callback
degrees(x)	degrees(x)	degrees(x)	(x * 57.295779513083)	degrees(x)	PHP Callback
exp(x)	exp(x)	exp(x)	exp(x)	exp(x)	PHP Callback
floor(x)	floor(x)	floor(x)	floor(x)	floor(x)	PHP Callback
ln(x)	log(x)	ln(x)	ln(x)	ln(x)	PHP Callback
log(b, x)	(log(x)/log(b))	log(b, x)	log(b, x)	log(b, x)	PHP Callback
pi()	pi()	(pi()+0.0000000000000)	3.14159265358979	pi()	PHP Callback
power(x, y)	power(x, y)	power(x, y)	power(x, y)	power(x, y)	PHP Callback
radians(x)	radians(x)	radians(x)	(x * 0.017453292519943)	radians(x)	PHP Callback
random()	rand()	rand()	(abs( dbms_random.random ) / 2147483647)	random()	(abs( random()) / 9223372036854775807)
round(x)	round(x)	round(x)	round(x)	round(x)	round(x)
sign(x)	sign(x)	sign(x)	sign(x)	sign(x)	PHP Callback
sqrt(x)	sqrt(x)	sqrt(x)	sqrt(x)	sqrt(x)	PHP Callback
sin(x)	sin(x)	sin(x)	sin(x)	sin(x)	PHP Callback
tan(x)	tan(x)	tan(x)	tan(x)	tan(x)	PHP Callback
Aggregate Functions
avg(l)	avg(l)	avg(l)	avg(l)	avg(l)	avg(l)
count(l)	count(l)	count(l)	count(l)	count(l)	count(l)
max(l)	max(l)	max(l)	max(l)	max(l)	max(l)
min(l)	min(l)	min(l)	min(l)	min(l)	min(l)
sum(l)	sum(l)	sum(l)	sum(l)	sum(l)	sum(l)
String Functions
trim(s)	rtrim(ltrim(s))	trim(s)	trim(s)	trim(s)	trim(s)
rtrim(s)	rtrim(s)	rtrim(s)	rtrim(s)	rtrim(s)	rtrim(s)
ltrim(s)	ltrim(s)	ltrim(s)	ltrim(s)	ltrim(s)	ltrim(s)
upper(s)	upper(s)	upper(s)	upper(s)	upper(s)	upper(s)
lower(s)	lower(s)	lower(s)	lower(s)	lower(s)	lower(s)
substr(s, start, length)	substring(s, start, length)	substr(s, start, length)	substr(s, start, length)	substr(s, start, length)	substr(s, start, length)
replace(s, find, replace)	replace(s, find, replace)	replace(s, find, replace)	replace(s, find, replace)	replace(s, find, replace)	replace(s, find, replace)
length(s)	len(s)	length(s)	length(s)	length(s)	length(s)
coalesce(a, b,...)	coalesce(a, b,...)	coalesce(a, b,...)	coalesce(a, b,...)	coalesce(a, b,...)	coalesce(a, b,...)
Date/Time Functions
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	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	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
TRUE	'1'	TRUE	1	TRUE	'1'
FALSE	'0'	FALSE	0	FALSE	'0'

Expressions

There are a few expressions that are consistent across databases:

Flourish SQL	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
BETWEEN	BETWEEN	BETWEEN	BETWEEN	BETWEEN	BETWEEN
CASE (simple)	CASE	CASE	CASE	CASE	CASE
CASE (complex)	CASE	CASE	CASE	CASE	CASE

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	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
, (cross join)	,	,	,	,	,
CROSS JOIN	CROSS JOIN	CROSS JOIN	CROSS JOIN	CROSS JOIN	CROSS JOIN
[INNER] JOIN	[INNER] JOIN	[INNER] JOIN	[INNER] JOIN	[INNER] JOIN	[INNER] JOIN
LEFT [OUTER] JOIN	LEFT [OUTER] JOIN	LEFT [OUTER] JOIN	LEFT [OUTER] JOIN	LEFT [OUTER] 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 MySQL and SQLite do not support FULL joins, SQLite does not support RIGHT joins and MSSQL does 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	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
DISTINCT (follows SELECT)	DISTINCT	DISTINCT	DISTINCT	DISTINCT	DISTINCT
ALL (follows SELECT)	ALL	ALL	ALL	ALL	ALL
LIMIT (without an offset)	TOP	LIMIT	subquery with rownum clause	LIMIT	LIMIT
LIMIT/OFFSET	subquery with row_number() clause and removal of extra column via PHP	LIMIT/OFFSET	nested subqueries with rownum clauses and removal of extra column via PHP	LIMIT/OFFSET	LIMIT/OFFSET
UNION	UNION	UNION	UNION	UNION	UNION
UNION ALL	UNION ALL	UNION ALL	UNION ALL	UNION ALL	UNION ALL

Transactions

Transaction control differs slightly among the supported databases:

Flourish SQL	MSSQL	MySQL	Oracle	PostgreSQL	SQLite
BEGIN	BEGIN TRANSACTION	BEGIN	auto-commit disabled	BEGIN	BEGIN
COMMIT	COMMIT	COMMIT	COMMIT	COMMIT	COMMIT
ROLLBACK	ROLLBACK	ROLLBACK	ROLLBACK	ROLLBACK	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.

Both PostgreSQL and MSSQL 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 ] [ ON UPDATE action ]
}

and action is:

{ RESTRICT | CASCADE | SET NULL | NO ACTION }

Create Index

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

Download in other formats:

Plain Text