Firefox PHP

More database independent phorum - idea for 5.3

Posted by Radim Kolar 
More database independent phorum - idea for 5.3
December 07, 2010 02:57AM
Making phorum more database independent is quite easy task, it will require mininal changes. Basic idea is simple: Split 2-way database layer into 3-way.
  1. low level db driver, doing phorum_db_interact and phorum_db_fetch_row. Because LIMIT / OFFSET feature is highly database dependent. You need to add 2 more parameters to low level driver, so it can do query rewrite:
    function phorum_db_interact($return, $sql = NULL, $keyfield = NULL, $flags = 0, $limit = 0, $offset = 0)
  2. general sql driver sending database independant SQL commands. Most of commands are database independent already. You have about 200 SQL statements used in phorum and about 15 needs rewrite to avoid MySQL specific stuff (JOIN USING and incorrect number quoting) and metaquery compile needs to be fixed too to do proper string escaping and dont quote numbers.
  3. database dependant sql commands - this will be very small. Only database dependent is file save/load, table creation, full text search and db sanity check and db schema updates. This is minimal amount of code, very easily portable.
Your current SQL commands contains Mysql query hints, you can probably keep them in general-sql section because low level db driver can filter them out very easily using regexps. Also pass binary files to file load/save routines. If you want them to be saved in base64, let low level db driver to do so.
My current Oracle/DB2/PGSQL(not tested) is using this 3-way structure, but i removed mysql query hints, so for mysql compatibility they needs to be retuned back. It is synced with 5.2.15a, i will upload DB2 drivers as far i know it is fully working, i just need to write little install documentation.
Its not big code change, i ported it to oracle and db2 in about 4 days. After this change you can get more db drivers very easily for example sqlite would be interesting to try, because its fairly trivial to write a new one.
Re: More database independent phorum - idea for 5.3
December 07, 2010 05:11AM
Hi Radim

Quote
Radim Kolar
Making phorum more database independent is quite easy task, it will require mininal changes. Basic idea is simple: Split 2-way database layer into 3-way.

Looks interesting.


Quote
Radim Kolar
... to do proper string escaping ...

string escaping depends on the database, or?

Regards
Oliver


Using Phorum since 7/2000: forum.langzeittest.de (actual version 5.2.23)
Modules "Made in Germany" for version 5.2: Author_as_Sender, CarCost, Close_Topic, Conceal_Message_Timestamp,
Format_Email, Index_Structure, Mailing_List, Pervasive_Forum, Spritmonitor, Terms_of_Service and German_Language_Files_Package.
Re: More database independent phorum - idea for 5.3
December 07, 2010 06:30AM
Quote
Oliver Riesen
Quote
Radim Kolar
... to do proper string escaping ...
string escaping depends on the database, or?

it does but it is currently handled well by DB_RETURN_QUOTED . What is not done is to have and correctly use list of fields which are supposed to be number and which are supposed to be string, so you will not compare numeric field with string constant like forum_id = '2'

for example you need to have correct definitions of field types in:
$GLOBALS['PHORUM']['string_fields_message']
$GLOBALS['PHORUM']['string_fields_user'];

and use them in phorum_db_metaquery_compile($metaquery)
Re: More database independent phorum - idea for 5.3
December 07, 2010 06:48AM
For deriving database layers, it would be better to have an object oriented layer. I have thought about that. It would keep us from doing stuff like regular expression replacements on queries, which sounds quite hacky (but I agree that it would keep the changes to a minimum). A direction that I have thought about, is to create a new OO-based layer, which can be used to derive other layers more easily. For backward compatibility, we can create a stub db layer that relays all functions from the current layer to the OO code. Within Phorum, I would of course change all code to make use of the new OO layer code. The backward compatibility layer code would be there for helping third party code (e.g. modules) to keep working.

With OO, we can create a fully SQL ISO compatible base layer and extend it the other way around: for MySQL, we can then implement specific methods that handle key hints and other speed improvement constructions.

This would be the way to go IMO, when we want the DB layer to be more independent.

There has never been a big drive on other layers in the past, since we only had the PostgreSQL layer, which was quite unmaintained. But if we are going to provide more maintained layers (I for example have plans for supporting PostgreSQL myself), then I agree that a better, but still light weight abstraction would be good to have.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: More database independent phorum - idea for 5.3
December 07, 2010 11:53AM
Quote
Maurice Makaay
For deriving database layers, it would be better to have an object oriented layer. This would be the way to go IMO, when we want the DB layer to be more independent.

Yes, this is preferred way to do that. Drupal and wordpress are doing that. They even pull column definitions from database to check what type are they, so it can avoid hardcoded column types (string, numeric, LOB). Problem with these apps is that they change their OO layer quite a lot with every major version and DB drivers needs to be rewritten.

Here is DB2 Phorum, installation instructions inside: I merged 3 files driver back to 2 file, it will make diffs easier to read.

[www.phorum.org]
Sorry, only registered users may post in this forum.

Click here to login