base

base copied to clipboard

PDO does allow you to use a number of database engines like Postgres, MSSQL, MySQL, SQLite etc.

However it won't automagically convert your SQL syntax for the engine used.

The SQL syntax produced by Base is MySQL-only (except quite generic queries).

Nowhere in the readme or composer.json is specified it is MySQL-only.

I guess it would be easy to create adapters for more database engines and this could be a nice improvement if there is enough demand.

The SQL statements that Base generates are quite simple. I wonder if, apart from the INSERT discussed in #3, there are other statements that might be incompatible with the other engines that PDO supports.

PDO does support a big list of engines. Each with their own differences - subtle or not.

Some differences from the top of my head:

• MSSQL does not have LIMIT or OFFSET, instead it uses TOP and subqueries.
• Some differences in ORDER BY.
• I don't even want to start with the Oracle implementation.
• And of course PostgreSQL uses double quotes to quote identifiers. It supports only single quotes for quoting strings. MySQL on the other hand uses backticks to quote identifiers and support both single and double quotes for strings.

Some resources:

• http://troels.arvin.dk/db/rdbms/
• https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL

Thanks! Then, perhaps, we should see which engines are compatible (or could become compatible after minor changes) with the current implementation and list them in the readme. SQLite might be one of them. We should also think about running the test suites on these engine.

p.s. Idiorm states that it supports SQLite, MySQL, Firebird and PostgreSQL and it has no drivers. Perhaps base should be able to support these as well.

Idiorm has things such as:

• Using different quoting for different engines - https://github.com/j4mie/idiorm/blob/1db83c9fee8a6418d92fd6fa87447d31faf88842/idiorm.php#L324-L344
• Using either LIMIT or TOP - https://github.com/j4mie/idiorm/blob/1db83c9fee8a6418d92fd6fa87447d31faf88842/idiorm.php#L346-L361

Also it has options for these: https://github.com/j4mie/idiorm/blob/1db83c9fee8a6418d92fd6fa87447d31faf88842/idiorm.php#L70-L71

Another approach is having a syntax agnostic codebase for the query builder which does not build SQL strings, but just composes the syntax in objects/arrays and then adapters for the different SQL implementations carry out the syntax building. Examples:

• Doctrine DBAL drivers https://github.com/doctrine/dbal/tree/master/lib/Doctrine/DBAL/Driver
• Phinx adapters https://github.com/robmorgan/phinx/tree/0.4.x-dev/src/Phinx/Db/Adapter
• Kohana database drivers https://github.com/kohana/database/tree/3.3/master/classes/Kohana/Database