[wp-hackers] GSoC Proposal: Multiple RDBMS support

[Olexandr Melnyk]

Hey,

On 3/31/08, Chris Poteet <cpoteet at siolon.com> wrote:
>
> Yes, even though this has been talked about before but for good
> reason.  Like my new plugin which uses SimpleXML which is much easier
> than the PHP4 way of parsing XML.  I was in Barnes and Noble and saw a
> book for PHP6!  Now, we're really lagging behind.


I am a proponent of PHP5 as well. WIth the wide use of WordPress, if it
becomes PHP5-only, pretty much all hosts will be supporting it soon :)

But let's get back to the topic, this discussion is about something
a little bit different here.

On 3/31/08, Jacob Santos <wordpress at santosj.name> wrote:
>
> Do you propose an adapter, factory, or registry pattern for
> accomplishing this? The way your code looks, it looks like what can
> already be done now with replacing the WPDB class with one of your own.
> You would have to do replacements of the SQL.


We could use a factory for instantiating an obect of
proper database-specific WPDB ancestor class. Registry could work for
storing "wpdb" object, but changing to
this approach from storing wpdb object in a global variable, like it
is done now, would break (almost)
all existing plugins, and that is not a point here.

Also, it appears you are talking about Database *Access* Abstraction
> Layer and SQL Abstraction Layer. The Access Abstraction is already
> accomplished and while pretty damn crappy (you have to support PHP4 you
> know), I do think PDO would kick WPDB ass and probably be a lot faster
> too.


I was going to suggest PDO for database abstraction too, but realised
that it won't work for the same reason.

However, PDO would not solve your SQL abstraction and your Access
> abstraction is already solved, so there would be no need to do this.
> Unless you wanted to remove the global variable usage and allow for one
> of the above patterns to have the database replacement in a plugin
> instead of in the wp-content folder with a specific name in order to be
> included and replace the current WPDB class.
>
> SQL is not that difficult to learn, therefore it would logical assume
> that if one knows one SQL language, one can also learn another. Given
> that, I think handling RAW SQL instead of arrays would work better.
> However you go about doing so would be interesting. However, I don't
> suggest your current mockup, because I'll hate you for creating
> something so overly complex.


There are a number of approaches for this, one is to use a "clauses  chain"
like this: $wpdb->select('user_login')->from('users')->where('ID = 1');

Despite this could be a "new" way of writing SQL, we'd still have to support
the old interface.

Well, technically it would range from extremely easy but redundant to
> extremely difficult, depending on which method you choose. The plugin
> would just say, this plugin is MySQL only like in other systems I've
> seen. If the user wants another database support, the user can add it
> themselves.
>
> It is a fact, that not coding in strict SQL 92 format and manually
> targetting one RDBMS, allows for better support, quicker development,
> and better optimization.


Agreed on both, but as I said, with the MySQL
feature set used by WordPress, it shouldn't make a huge difference
whether one or multiple databases should be targeted (as
long as all of them understand an SQL dialect).

It is not a fact that it won't meet most target audiences, want about
> companies who already use Oracle and don't want to have to buy a MySQL
> server just to run WordPress? Force them to write their own WPDB class
> and replace the SQL by regular expressions?


Replacing SQL by regex would be a quick and dirty solution. It could
also make a some of plugins work
on number databases, but it wouldn't be a good idea in the long run.

It is not a fact that it will require plugin authors to support RDBMS,
> unless it was made a requirement. It would be completely up to the
> plugin developer. If that plugin developer only wanted to support SQLite
> or Oracle, than that would be their priority and audience. If another
> person wanted to add MySQL or another, then it would be added.


Agreed, if developer finds particular database functionality more useful
for his/her plugin than database abstraction, s/he can use it.

Another fact is that it is not very hard depending on how you implement
> it, the current implementations only make it seem that way because they
> abstract everything and hold your hand. That implementation would be
> extremely difficult and not worth the time it would take. An
> implementation that is just an array that WordPress looks for the key
> for the database and pulls that SQL statement out, would be easy,
> however hackish.


What I would suggest to use for now is to split WPDB into two parts. One
would provide database abstraction (query, fetch row, get error etc) and
will be implemented for every supported database.
Another will have the same interface as current WPDB (so it won't break any
plugins) and will be based on calls to the abstraction layer (so it will
have to be implemented just once), instead of
mysql_* functions, as it is done now.

We'd also have to expand WPDB class with a couple of methods,
used for the SQL statements/clauses that have varying syntax among different
databases. As far as I'm aware, WordPress makes use of two such
features: one is fulltext search and another is limit
clause.FOr the former we could have a method fulltext_matches(), which would
return an appropriate WHERE condition.
For the latter, we could use something like:

$result = $wpdb->query(limit('SELECT user_login FROM ' . $wpdb->users . '
ORDER BY user_login', 0, 10));

The implementation for MySQL, PostgreSQL and SQLite would be somewhat
straightforward, and would require some more work for SQL Server and Oracle.


Thanks for the replies

-- 
Sincerely yours,
Olexandr Melnyk <><
http://omelnyk.net/