Attempting to make Serendipity work with DB2 Database

73blazer · Post by **73blazer** » Wed Sep 13, 2006 1:02 am

Hello.
I'm new to serendipity and I was trying to see if I could make it work with a DB2 backend.
Back earlier this year a guy started to create a DB2 patch. I found it at
http://coffeecode.net/s9y/ibm_db2.diff

Anyhow, I made some changes, and managed to use a large portion of that patch . I did get through the installation, and it looks as if serendipity installed. All the tables are there and the basic initial data got inserted into some of them. I was very proud of myself.
However, I can't seem to get the main page to load. I believe the query's aren't returning a proper array. I'm still working on it.
Here's what I get right now: http://www.snyderworld.org/serendipity

Anyhow, I'll be picking at this to see if I can get it to work properly. I may be asking questions, I hope this is the right place. If anyone has any pointers, I'll take anything I can get. If someone already has a DB2 port

, I'll take it.

Thanks.

Ken :

73blazer · Post by **73blazer** » Wed Sep 13, 2006 1:04 am

Well, my first post, and I already screwed up. I meant to post in the development forum. Guess I wasn't paying attention. Sorry.

Post by **jhermanns** » Wed Sep 13, 2006 1:25 am

no problem, we like to hangl loose here...

Post by **garvinhicking** » Wed Sep 13, 2006 10:28 am

Hi!

That's great, cool work on the DB2 support. Finally someone took on that - I personally have no DB2 knowledge at all.

It seems that at least some of the queries to not properly return a valid array structure, like to fill the global $serendipity array with all configuration values of the serendipity_config DB table. For instance the "lang" variable seems to be not set, thus leading to display constants instead of translated items...

Best regards,
Garvin

73blazer · Post by **73blazer** » Wed Sep 13, 2006 6:36 pm

Ok, making progress.
Just needed to format the array properly.
Anyhow, then I ran into this line in include/functions_config.inc.php
Line 93
"VALUES ('" . serendipity_db_escape_string($name) . "', ' " .

Notice the space in the last part of the line single quote,space,double quote
Is that supposed to be there? If it's there on my installation it inserts
' /www/mypathtoserendipity/'
in s9y_config table and all operations wanting to use that value fail because obviously <space>/www does not exist.
I removed the space.
I would think that would be an error for everyone upon a fresh installation.

Anyhow, moving on...I'm getting closer....

Post by **garvinhicking** » Wed Sep 13, 2006 9:15 pm

Hi!

Please post some surrounding; in the functions_config.inc.php of current SVN I cannot find what you quoted? Are you sure its in serendipity and not a bug that was introduced by you editing the files? Because if that space were in there it would have caused major problems in official serendipity versions!

Best regards,
Garvin

73blazer · Post by **73blazer** » Wed Sep 13, 2006 10:28 pm

Yeah, your right. It was from the original guy's code which I started out with. Sorry about that.

Anyhow...I got it somewhat working. I can load the main page and make new entries.

I'm afraid to hit any more buttons. Every new link is like 10 things to fix.

Basic things so far are:
- integers always begin used as '1', DB2 doesn't accept that. (doens't like the single quotes around an integer)
- DB2 doesn't have a LIMIT function. If your using LIMIT with OFFSET then it's a problem, other wise in the db2 driver I just put to use FETCH FIRST x ROWS ONLY. If an offset is needed, I'll have to write something for that. I think some people already got a UDF I found on the web a couple of years ago.
- DB2 doesn't allow a 'SELECT DISTINCT', an ORDER BY or a GROUP BY clause on columns more than 255bytes. There's a couple of these I've found. FOr now I just said in your already existing clauses in several files (looks like mostly for postgre, one had a comment to the effect of why does postgres keep doing this) $group='' and $distinct='', so not sure what that will effect. I'm sure something important. I'll have to find a way around that.
- DB2 timestamp columns don't go by the unix_timestamp format. I made a UDF for this, but there's might be a better way. I'm still thinking about it.

I think it's pretty workable though. A couple of things to re-write, and some things to case out and it should be good.

http://www.snyderworld.org/serendipity

Post by **garvinhicking** » Thu Sep 14, 2006 9:36 am

Hi!

- integers always begin used as '1', DB2 doesn't accept that. (doens't like the single quotes around an integer)

Yes, sadly s9y does have some places where this is done. s9y was developed mainly with mysql and pgsql in mind, which can both cope with those, I believe. But changing it officially in the code would be very fine.

- DB2 doesn't have a LIMIT function. If your using LIMIT with OFFSET then it's a problem, other wise in the db2 driver I just put to use FETCH FIRST x ROWS ONLY. If an offset is needed, I'll have to write something for that. I think some people already got a UDF I found on the web a couple of years ago.

That would be nice, LIMIT emulation is quite important.

- DB2 doesn't allow a 'SELECT DISTINCT', an ORDER BY or a GROUP BY clause on columns more than 255bytes. There's a couple of these I've found. FOr now I just said in your already existing clauses in several files (looks like mostly for postgre, one had a comment to the effect of why does postgres keep doing this) $group='' and $distinct='', so not sure what that will effect. I'm sure something important. I'll have to find a way around that.
Uh. That looks to be like a more serous issue. :-/

- DB2 timestamp columns don't go by the unix_timestamp format. I made a UDF for this, but there's might be a better way. I'm still thinking about it.

I believe we always used simple int(11)s for each timestamp, and did not use native DB-timestamps anywhere...

Best regards and thanks for all your work,
Garvin

73blazer · Post by **73blazer** » Thu Sep 14, 2006 2:22 pm

Great, so I can just remove the single quotes around integers, other databases don't require that? (Sorry, I've only worked with Oracle and DB2) I have just been casing it out, but obviously would be much cleaner to just remove the quotes all together.

I'm thinking about ways to get around the LIMIT|OFFSET problem, and the SELECT DISTINCT, shouldn't be anything too difficult.

THe timestamp thing, I think I put a timestamp column in the schema, so in one place it was wanting to use it. Your right though, most of your time columns are just integers which works just fine.

So, is this something you wish to integrate permenently into your source? At first I just wanted to see if it could be done without major modificatiojns. Now that it looks that is the case, I think it could be integrated fairly easily without much disturbance to the code as it is currently.

Post by **garvinhicking** » Thu Sep 14, 2006 2:59 pm

Hi!

Yes, MySQL, SQLite and PGSql will accept integers if they have no quotes around them. However for security it is important that you use "(int)$phpVaraiable" everywhere where you remote the single quotes - else SQL injection would be possible.

So, is this something you wish to integrate permenently into your source?

Most definitely, yes! I would love to see it officially integrated, once you got it working and sorted out the most central single-quote parts. I'd much appreciate if you could try to build a patch/diff against the most recent s9y trunk/SVN snapshot, this would then be easiest to implement after that.

Thanks a lot,
Garvin

73blazer · Post by **73blazer** » Thu Sep 14, 2006 4:25 pm

Ok. You might have to help me out with the SVN thing. I've never used such a system.
Is that different than the snapshot builds on http://www.s9y.org/12.html or should I be taking directly from the BerliOS repository?

I used that patch comand to install patches, and make them for a single file, but I've never made a pach file for a whole directory structure before. (Like the original ibm_db2 diff file I started with). Hopefully I can just read the man page for patch and diff commands to figure it out. Any pointers here, is there an easy way?

I'm picking through some more advanced functions in s9y, slowing making corrections as I go. Do you have a tried and true testing scenario?

Post by **garvinhicking** » Thu Sep 14, 2006 4:38 pm

Hi!

You could take the snapshots from the URL you mentioned, if you're not that familiar with SVN. However getting familiar with SVN would only do you good, it's a really easy and comfortable system for building patches/diffs. I believe the SVN page on http://developer.berlios.de/projects/serendipity/ has some usefull tutorials...

If you use a SVN client you will be able to just use "svn diff" and see a diff for all changes you made.

I'm picking through some more advanced functions in s9y, slowing making corrections as I go. Do you have a tried and true testing scenario?

Well, not exactly. We always wanted to implement unit tests, but never got down to hook them up in our mixed OO/functional system.

Best regards,
Garvin

judebert · Post by **judebert** » Thu Sep 14, 2006 5:12 pm

I'd really rather see this integrated than lost because SVN or patch became too much of a problem. If you start having so much trouble you lose interest, send me your known-working version. I'll use kdiff3 or something to integrate the changes.

That said, Garvin is wise: learning SVN is good for anyone who hopes to contribute to Open Source projects. CVS is pretty much the same, but it's being replaced by SVN.

73blazer · Post by **73blazer** » Thu Sep 14, 2006 5:21 pm

You won't lose it. I'll have to compile svn for AIX, which, as most open source software goes on AIX, is usally a chore.

I've spent too much time on it now to toss it away or just leave it on one system.

I'll learn SVN, shouldn't be a big problem. It's probably gonna take me a couple weeks to pick through all the functions in s9y and fix anything that causes DB2 errors. After that, I'll make a diff and see what you guys think.

judebert · Post by **judebert** » Thu Sep 14, 2006 5:27 pm

Holy cow, AIX? I remember compiling the first Mosaic for AIX. You're right, it is a chore.

You're a trooper. If there's anything we can do to help, just let us know.