Firefox PHP

Phorum - IBM DB2 9.5.2 port

Posted by Radim Kolar 
Phorum - IBM DB2 9.5.2 port
December 25, 2010 03:24PM
I ported phorum bulletin board to IBM DB2 9.5.2 (with fulltext). You can get free ibm db2 express c edition here and PHP DB2 driver here. DB2 specific installation instructions are included in archive. Message size is limited to 32KB, file attachments are limited by PHP settings, column for storing files is defined as 16MB.

Download: DB2 Phorum



Edited 1 time(s). Last edit at 01/22/2011 02:42PM by Radim Kolar.
Re: Phorum - IBM DB2 9.5.2 port
January 21, 2011 03:55PM
you guys have plans to merge this? if not its fine with me, i will put it on launchpad as separate project.
Re: Phorum - IBM DB2 9.5.2 port
January 22, 2011 01:43AM
Do you think your work would be a good start point for a postgresql effort?

I've always wanted postgresql support but it has never really happened.

Given a good and more general SQL code base (than the current postgresql code) I'd be willing to transmorgrify it to postgresql.
Re: Phorum - IBM DB2 9.5.2 port
January 22, 2011 02:17AM
yes

db2 specific statements are:

- datatypes in create table
- lob support
- fulltext search
- limit/ofset emulation (needs oracle-like rownum)
- temporary table creation used by search function

rest should be pgsql9 compatible.
Re: Phorum - IBM DB2 9.5.2 port
January 24, 2011 01:20PM
We are not really interested in having a 5.2 port for db2. The port is already out-of-date when looking at the schema of the current development tree. Developments other than bug fixing and really important features can't be part of a stable branch. Your patch typically doesn't fall in those categories. Sorry for that.

I have written a message on ideas that I had for the database layers in Phorum, to be able to switch to an OO method, without having to drop the current functional layer. Today, I have committed my work on the new layer code for Phorum 5.3. If you want to see what's going on in this field, take a look at changeset 5205.

Some more work will be done to add some cleanness to parts of the new MySQL code, but you'll probably be happy with the progress so far, since it is now possible to override single methods, instead of having to copy the fully db layer.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Phorum - IBM DB2 9.5.2 port
January 24, 2011 01:34PM
My two cents, might be worth less to you guys, is that an OO approach is too big a change. In my opinion use of MySql has caused "non-standard SQL" to be used. Had postgresql, or something like it, been the starting point then more "generic" sql would have been used. This would have lead to it being easier to port to other databases.

Why do I say this, well for one thing I have almost 30 years experience in developing software and have found that SQL is typically not too difficult to port. This assumes two things, one that a port is likely and two standard SQL constructs are used.

Like I said, my opinions are just that opinions, make of them what you will.

I will say that the overhead, both run time and programmer understanding, of going to an OO layer seem to be overkill in my opinion. I fantasize about an elegant, thin SQL layer. I think you guys original approach was spot on, you just got snookered into making decisions in favor of MySQL.
Re: Phorum - IBM DB2 9.5.2 port
January 24, 2011 02:19PM
There's nothing in the OO layer "in favor of MySQL" just in favor of "versatility".


Thomas Seifert
Re: Phorum - IBM DB2 9.5.2 port
January 24, 2011 04:06PM
You are missing the point of the switch to the OO layer completely. The whole idea is that the PhorumDB object can now contain the perfectly clean standard ANSI SQL that you long for, while we have the possibility to implement specific non-standard code in the PhorumMysqlDB layer code. The MySQL specific code can now be fully stripped from the base layer. We like having non-standard SQL when that speeds up the database interaction. Why settle for anything less? With this OO layer, this is now possible in a clean way.

In short: we do not see the use for what you call elegant: a one SQL fits all approach.

Run time was a big issue for OO code in PHP in the past, but we have done tests that have shown that the big speed issues have been fixed. If OO coding had been as efficient as it is nowadays, we most likely would have had an OO DB layer from the start.

As for using PostgreSQL as the database system to start out with: back in 1998, PostgreSQL was not yet really a well-known wide-spead database system. Neither was it yet matured at that time to support SQL92 syntax. MySQL was the logical choice.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Phorum - IBM DB2 9.5.2 port
January 26, 2011 09:08PM
If you guys are interested in the 5.3 db layer code, here's the PostgreSQL layer that I worked on today.

PhorumPostgresqlDB
  |
  +--- PhorumDB

In the main PhorumDB layer, some properties were added for certain features, which can be used to influence some SQL constructs, without having to override the full functions in derived classes. Something like the ROWNUM usage instead of LIMIT/OFFSET would be a candidate for a special property too IMO.

The code is mostly functional. The only known open task is the implementation of the search code in a PostgreSQL compatible way. The search method is a good candidate for overriding, since PostgreSQL does support full text search, but in a totally different way than MySQL.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Phorum - IBM DB2 9.5.2 port
January 27, 2011 04:25AM
LIMIT/OFFSET should be done as parameters to $this->interact . I use this method in Oracle / DB2 port and it turned to be best because you need to do complicated query rewrite [nested queries or cursors] to emulate LIMIT / OFFSET.

Another annoying stuff is emulating SQL_CALC_ROWS, luckily it is not used in phorum much, but it is very heavily used feature in wordpress

And of course LOB object support via variable->field binding is last important feature in db portability layer. Lobs should be used for message body to allow them grow more than maximum varchar size (4-32k in most databases).

If you want to see best db portability code go to look at latest drupal, you are trying to create something like slightly improved wordpress which in reality does not helps much because it does not have LOB support.
Re: Phorum - IBM DB2 9.5.2 port
January 27, 2011 05:48PM
LIMIT/OFFSET has been moved to interact() parameters and I updated all LIMIT/OFFSET constructions in the PhorumDB class to follow this new route.

SQL_CALC_ROWS is only used for the search interface. Changes that I did in this field already have cleared the SQL_CALC_ROWS from the PhorumDB class. The original construction is still available in the search() method for PhorumMysqlDB.

"LOB object support via variable->field binding" does not mean anything to me. I have no experience in DB2, so domain-specific words will not stick. I will take a look at the db2 code to see what it's about and what the db layer would need for supporting it.
Edit: I cannot really find something interesting about this. If this is important, please let me know what I'm looking at and where you support this in your db2 Phorum code.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce



Edited 1 time(s). Last edit at 01/27/2011 05:52PM by Maurice Makaay.
Re: Phorum - IBM DB2 9.5.2 port
January 27, 2011 06:58PM
You are making good progress.

What is UPDATE IGNORE, it is same as firing UPDATE statement and ignoring its return code? If yes, you can use some flag added to db->interact. Wordpress is doing same thing.

In DB2 you can bind filename directly to query parameter with [www.php.net] and do all data transfer on server side. but as i look at db2 code, in phorum it is not used/needed. Binding to query parameters should be enough look at phorum_db_file_save as example how to save file passed as string into LOB object.

Another thing which you need to consider for database compatibility is to avoid using USING keyword in JOIN. Rewrite it as JOIN ON
Re: Phorum - IBM DB2 9.5.2 port
January 27, 2011 07:24PM
I don't want interact() to use a looooong list of parameters (that's asking for problems because of forgotten parameters in calls). With limit/offset it already feels a bit too long. Since "UPDATE IGNORE" is only used in one place in the original MySQL layer, the OO concept allows us to override the query, solving the issue already.

UPDATE IGNORE will ignore errors during update. It is used for updating newflags when moving a message to another forum. Since there might be bad data in the newflags table or since there might be race conditions between moving the message and updating the newflags, errors could occur with duplicate indexes because of the update. Let's say that 200 newflags are moved and that for 1 of the users a newflag already exists for the target forum_id to move to. UPDATE IGNORE will handle the 199 updates that do work and it will ignore the 1 that doesn't (leaving that record as-is).

For PostgreSQL (and possibly for other db systems if the syntax matches), I have written a few queries that circumvent the possibility of running into duplicate indexes. Technically, that feels like a better solution to me, since the code is aware of the race conditions that can occur in this process. The UPDATE IGNORE is almost as good, but only since left-behind records won't hurt the system.

Parameter binding is not needed indeed. The Phorum db layer is a bit more old school in that respect (i.e. creating full queries and taking care of the necessary quoting yourself.) A db layer could of course implement full parameter binding (there's no reason why a layer should follow the exact format of the other layers), but it will be a lot of werk to get that done.

JOIN .. USING .. is a MySQLism indeed. I overlooked that one. Apparently PostgreSQL allows the same syntax. I'll take care of that.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Phorum - IBM DB2 9.5.2 port
January 28, 2011 07:34PM
JOIN ... USING constructions have been resolved in the code.

I also started an experimental Sqlite layer, to check how hard it would be to create new layers on top of the new system. This went quite well. The layer uses PDO to access the sqlite3 database. In the process, I fixed a few more incompatibilities in the PhorumDB layer.


Maurice Makaay
Phorum Development Team
my blog linkedin profile secret sauce
Re: Phorum - IBM DB2 9.5.2 port
January 31, 2011 05:56AM
About argument quoting. If you diff db2 vs mysql code you will see that i have tables which arguments are string because db2 9.5 do not likes to compare numeric field to quoted argument like a='23' if a is number.

This argument passing method still causes troubles with LOB support because maximum string is about 4-32K depending on database. pgsql and mysql can probably have longer stings because they have weak LOB support. Commercial databases supports LOBs well, so they are often used. You can not insert longer data into database. This for example on DB2/Oracle limits post size to 32K which is still good enough for daily use. Problem might be in some modules which are saving longer user specified data, such as avatar pictures.

Best solution is to use prepared statements with parameter markers. They have different syntax for arguments. MySQL and DB2 are using JDBC style arguments, Oracle is using embedded sql style arguments and of course postgresql guys invented their own standard as always. I suggest to follow mysql/db2 style because it can be easily transformed with regular expressions into other syntaxes.

During parameter binding database often needs to know what argument type it is. For start it will be enough to mark argument as lob or not lob. At least oracle will be happy with this. DB2 can bind any argument type into statement, no need to mark if argument is LOB or not. MySQL and PGSQL will be probably happy with bind all as string method as well.

For oracle we can use comments such as INSERT INTO XX (a,b,c) VALUES (?,?,/*lob*/ ?)
Sorry, only registered users may post in this forum.

Click here to login