BabelQL: Single SQL source for multiple RDBMSs

Introduction

I write web applications. My web applications (usually) talk to databases via JDBC. But before the web application can talk to the database, the database has to be initialised. Well, the easiest way to set up a database is to generate a SQL script file. Unfortunately, not all my customers run the same RDBMS; some use Oracle, some use Postgres, some use SQL Server. In the future I expect at least some will use Firebird.

I've had endless fun tweaking SQL scripts to work with different database engines, and maintaining different verions of the SQL scripts had become a major chore. I therefore wrote BabelQL as an attempt to get around the problem, by isolating the SQL syntax differences in a single file, and writing my database initialisation scripts in a special 'engine neutral' SQL dialect.

This is far from perfect but is now good enough to be useful, and I offer it to others in the hope that if more people use it it will get more useful faster.

About BabelQL

babelql.pp is C pre-processor file intended to allow you to generate many different SQL dialects from the same source file. To use it, write your source file (I call it .sql.pp, to indicate pre-processed SQL) using the macros defined in this file, and include this file into it at the start. Then run your c preprocessor (I use GPP) on the file with the argument -Dtarget, where target is one of the supported SQL dialects.

Extending BabelQL

Obviously, BabelQL corrects for the variances in SQL dialects that I personally have tripped over. If you find and trip over other variances, please feel free to add them to the file and copy your changes back to me.

The Macros

Obviously the name of the game is to generate the widest possible range of SQL dialects with the minimum possible number of macros. These are the macros known in BabelQL $Revision$:

Lanquage Features

HASVIEWS

defined if the target language has the concept of 'views', i.e. stored queries which can be selected from as if they were tables; undefined otherwise.

HASVIEWORDER

defined if the target language allows 'order by' clauses in views; undefined otherwise.

Data types

UNLIM

A very large text field, for example a 'memo' field.

UNIQINT

A system generated integer value guaranteed to be unique in its column, typically for abstract primary keys. Example:

create table ARC
(
  arc UNIQINT primary key, ...

System values

TODAY

The current date, for example 'sysdate' or 'now()'.

NOW

The current time, for example 'sysdate' or 'now()'.

Syntax variances

ADDFOREIGN( ctable, cfield, ftable, ondelete)

Add a foreign key constraint to table ctable such that the value of cfield in each row must match the primary key field of a row in ftable; if the row in ftable is deleted, observe the behaviour ondelete, where ondelete is one of

• cascade
• no action

The value set null for ondelete will work for some but not all of the databases which have HASCONSTRAINTS defined, so I would advise against using it.

ADDTOROLE( user, group)

Add the user named user to the role (group) named group.

CREATEROLE( name)

Create a new role (group) with the name name.

CREATEUSER( user, password)

Create a new user account with the username user and the password password.

GRANTTOROLE( role, resource, access)

Grant access on resource to role where role is a role previously defined by CREATEROLE; resource is the name of a previously defined table or view; access is one of

ACCESS_SELECT

ACCESS_INSERT

ACCESS_UPDATE

ACCESS_DELETE

ACCESS_ALL

select, insert, update, delete

ACCESS_NOEDIT

select, insert

ACCESS_EDIT

select, insert, update

GRANTTOUSER( user, resource, access)

Grant access on resource to user where user is a user previously defined by CREATEUSER; resource is the name of a previously defined table or view; access is one of

ACCESS_SELECT

ACCESS_INSERT

ACCESS_UPDATE

ACCESS_DELETE

ACCESS_ALL

select, insert, update, delete

ACCESS_NOEDIT

select, insert

ACCESS_EDIT

select, insert, update

Using BabelQL

To use BabelQL, just write your SQL script using the BabelQL macros where appropriate. At the beginning of your script, include the babelql script:

#include babelql.pp

Then pass the script through a preprocessor such as gpp, defining the relevent macro for your database. For example:

gpp -Doracle8 dealersys2.sql.pp > oracle8.sql

Then run the generated script against your RDBMS to create your database. Couldn't be simpler. There is, however, a minor mess when generating code for Postgres 7: my technique for creating unique names for foreign key constraints is a kluge and means that to generate the script you need do:

gpp -Dpgsql7 dealersys2.sql.pp |\
	  sed 's/ *::fixthisconcat:: */_/' > pgsql7.sql

Getting BabelQL

You can download BabelQL from here.