jodastephen edited this page Nov 5, 2014 · 9 revisions

Sadly, although there is a standard for SQL, different databases do not always agree with the standard. As such, part of the goal of ElSql is to provide a very thin veneer over some of the key differences that affect developers. The goal is not to provide a general database abstraction layer.

Database differences

The database, and its differences, is encapsulated in the ElSqlConfig class.

Each instance has a name, such as Postgres, HSQL, or MySql. It also has methods that provide the rest of ElSql the ability to manage differences in the LIKE syntax and how to do paging. The following database names are supported:

Name Desciption
Default Follows standard ANSI SQL
Postgres Follows standard ANSI SQL
HSQL Escapes the LIKE clause with ESCAPE '\\'
MySql Uses LIMIT...OFFSET instead of FETCH...OFFSET
Oracle Handles paging using rownum
SqlServer2008 Handles paging using SELECT TOP and ROW_NUMBER()
Vertica Follows standard ANSI SQL

If a piece of SQL that must be generated is even more different, then the .elsql file can be supplemented with a database specific one. If the main filename is FooBar.elsql, then the override file for MySql database would be FooBar-MySql.elsql. With this mechanism, the differences between databases can be limited and managed. Simply write ANSI SQL in the base file and database specific SQL in the database specific file as necessary.

Additional databases can be added by subclassing ElSqlConfig. See the source code for more info.