More Databases

One barrier towards supporting new database systems is the lack of functions for database structure alteration (mainly table creating and modification) in drupal's current database abstraction layer.

Therefore, a new database port must not only consist out of a new database.inc, probably some more core sql modification and a translation of the database scheme, but also must ALL contrib modules that want to be available for the new database port their database schemes and update routines.

And as most contrib developers will probably work only with one database system (mostly mysql), they won't be interested or won't even be able to do this port.

So even if a new database system would be supported by core, most contrib modules won't run on it.

There had been a long discussion on the dev mailing list on this started by adrian [1]. He proposed the extension of the database abstraction layer.

I did exactly this, and provided a patch [2]. To be included in core, it currently lacks mainly of some more reviews and a postgres implentation (I'm totally unfamiliar with postgres, so I won't be able to do it).
However, for me the patch works perfectly in mysql.

Hence I think the first important step to ease supporting more database systems is to get this patch into core.

So go ahead and review it! ;)
(see http://drupal.org/node/63049)

[1] http://lists.drupal.org/archives/development/2006-05/msg00456.html
[2] http://drupal.org/node/63049

As you probably all aware the Postgres and MySQL database...inc files use LIMIT for queries requiring a range of results. LIMIT sadly isn't SQL:2003 compliant which specifies the use of syntax like:

SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename

Sadly (and somewhat obviously) SQL Server 2000 doesn't support this or anything approaching the LIMIT functionality and only has the simple TOP command i.e. SELECT TOP n ... So in order to make the MS SQL interface work I need to find a mechanism for replacing LIMIT. I though of using a temporary paging table (which is the normal trick) except I don't know the structure of the original table in the query. So I'm left with the following:

1) Run some kind of query to get the size of the result set likely and then create a paging table to join onto it and then run a third query to get the paged results - yuck!

2) Run the query using SELECT TOP n where n would be the sum or start and count and then remove the rows upto start prior to returning the results - which is messy but doable.

3) Some better suggestions - please

Thanks

Nick

Any updates on this activity. I'm definately interested in MSSQL Support.

Cheers,
Matt

I ran across this thread, i think this could help in the "paging" problem:

We have received lot of feedback on making server-side paging syntax easier and considering it for a future version of SQL Server.

As for ROW_NUMBER, why do you say that it doesn't work when doing JOINs or ORDER BY? The ORDER BY of the outermost SELECT affects only the order in which the rows are returned to the client. ROW_NUMBER() has it's own ORDER BY clause so you can effectively number rows in different order and it doesn't impact the presentation order. With ROW_NUMBER you are just counting rows based on set of column(s) and optional ordering so joins doesn't really matter either. Can you give some specific examples where you have problems converting LIMIT syntax to SQL Server?

And the effect of paging results is often misunderstood. Generating a row number without ordering the rows in the result can produce unpredictable behavior. You can end up retrieving some rows in say page #1 in subsequent pages depending on how the engine executes the query and so on. So application requirements is a key consideration before implementing paging. The use of ROW_NUMBER forces you to think about these issues carefully. Note that Oracle also implements the same and it is effective replacement for ROWNUM pseudo-column.

This seems like the most likely place to pose my question. Is there a resource anywhere of the common problems that people have run into in getting other databases running with Drupal and the common solutions to these problems. I suspect that although not completely identical the other databases may share common restrictions. A summary of problems I have run into:
1) num_rows not supported by all database drivers
2) Liberal use of single quote characters with numeric types in core modules (i.e. using '700' instead 700 on an insert of an integer)
3) DISTINCT, GROUP BY, and comparison on "CLOB" fields somewhat common.
4) Use of the MySQL specific IF(expr1, expr2, expr3) function.
5) Need to use prepared statements to insert CLOBS once they reach a certain size.
6) One shouldn't look at the MySQL CREATE TABLE statements for guidance, use the PostGres ones instead.
7) TEMPORARY tables require a schema name prefixed on DB2
8) Perhaps others I've forgotten.

I now have a simple install of Drupal 5.0 working with DB2 9 although there are probably some bugs lurking around. But I am not too happy with some of the measures I needed to take to get it going (I changed db_query in database.inc and I require the implementation of a couple of extra simple methods by the database specific database..inc files. Are these problems common for new DB adapters? and are there recommended methods of handling them?