Support for DB2 databases?

Folks,

I'm brand new to Gallery, just downloaded G2.0. Haven't been able to install it yet because I don't have a currently-supported database. I was hoping I could make it work with a DB2 database, but the install program hard-codes the choices.. Any chance it could be made to work with a DB2 database? I'm not a hardcore developer, but I have years of experience with DB2 and would be happy to help G2 developers implement support for DB2.

I'm running Windows XP SP2, Internet Explorer 6.0.2900.2180.xpsp_sp2_gdr.050301-1519, PHP 4.3.7, Apache 2.0.49, and DB2 V8.2.2.

Thanks.

Thanks for the response, Valiant.

> i'd prefer if we could guide you to do the necessary steps

That's the *only* way this would happen. ;-)

OK, will follow your instructions ASAP (maybe even tomorrow) and get back to you. Note that I can only test this on my Windows environment, I don't currently have a Linux/UNIX environment at my disposal (never mind one with DB2 installed ;-) ).

Let me know (larry.menard@rogers.com) if you want to continue this discussion offline as opposed to via the forum.

@IRC:
IRC is internet relay chat. so i offered you to use a irc client to join our chat channel called #gallery on this irc server irc.freenode.net.
most people use Gaim or mIRC to join chat rooms, i use opera, because it's so easy with opera. Gaim is also quite easy, mIRC is very powerful, but IMO, not very intuitive.

@file integrity:
that's fine. since you changed now some files, the checksums we've computed before don't match anymore. you can ignore this warning.

@database setup step:
i didn't expect you to be so fast, so my instructions were incomplete

what you still have to do:
- installer: install/steps/DatabaseSetupStep.class
after:

	    case 'mysqlt':
		if (!function_exists('mysql_connect')) {
		    $templateData['error']['phpDbMissing'] =
			_('You must have the MySQL PHP module installed');
		}
		$dbPlatformType = 'mysql';
		break;

add an analogous block for db2. function_exists('db2_connect') should be fine as a test.
in

foreach (array('mysql'  => _('MySQL (all versions)'),
			   'mysqlt' => _('MySQL with Transactions (v3.23.34a and newer)'),
			   'postgres7' => _('PostgreSQL v7.x'),
			   'postgres' => _('PostgreSQL v6.x (not well tested)'),
			   'oci8po' => _('Oracle (9i and newer)'))

add something like 'db2' => _('DB2 v...'),

- not important, but nice: in install/config.php-template add a comment about db2 in

 * The possible database types are:
 *  mysql        Standard MySQL
 *  mysqlt       MySQL with transactions (3.23.34a and newer)
 *  postgres     PostgreSQL 6.x (not rigorously tested)
 *  postgres7    PostgreSQL 7.x
 *  oci8po       Oracle (9i and newer)
 */

- in modules/core/classes/GalleryStorage.class
add after

case 'postgres':
	case 'postgres7':
	    GalleryCoreApi::relativeRequireOnce(
		'modules/core/classes' . $base . 'DatabaseStorage/PostgreSqlDatabaseStorage.class');
	    $this->_impl = new PostgreSqlDatabaseStorage($config);
	    break;

an analogous case for db2.

- finally add the adodb db2 driver to G2. adodb supports a lot of dbs, we only included the drivers in G2 that we actually need.
get the latest php adodb from http://adodb.sourceforge.net/
and copy the adodb-db2.inc.php (or whatever its name is) driver from this downloaded archive (drivers/ folder) to gallery2/lib/adodb/drivers/adodb-db2.inc.php.

i guess that's it.

@email:
actually, i prefer the forums. plus if someone else wants to add ms sql server support, the person could just read our discussion here.
but i'm moving the topic to the development forums

If it helps, I put a couple of debug prontf()s in the code, and I can see that it seems to go away in the call to ADONewConnection():

printf("I get here, config type = %s\n", $this->_config['type']);
$this->_db =& ADONewConnection($this->_config['type']);
printf("But I never get here\n");

error_reporting is set to E_ALL, and nothing in the Apache logs in the last 2 days.

I plugged a few more printf()s in it, and in "adodb.inc.php" it seems to never return from:

printf("<br>In ADOLoadCode, db = %s, class = %s, file = %s.\n", $db, $class, $file);
@include_once($file);
printf("<br>In ADOLoadCode, I do not get here.\n");

Result:

In file DatabaseSetupStep.class, I get here, config type = db2.
In ADONewConnection, db = db2.
In ADOLoadCode, db = db2, class = db2, file = C:\My Server\gallery2\lib\adodb/drivers/adodb-db2.inc.php.
In adodb-db2.inc.php, I get here.

But that's all I get. It seems to never return to ADOLoadCode() from include_once().

remove the @ in front of the include once, or even replace @include_once with require_once

@ will suppress any errors. and require is even more restrictive = better for debugging.
maybe spaces are a problem in dirnames on windows, no idea.

Well, that didn't take long, did it?

The database connect is failing. Messages are:

----------------------------
For odbc Connect(), gallery2 is not used. Place dsn in 1st parameter.

Warning: odbc_connect(): SQL error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, SQL state IM002 in SQLConnect in C:\My Server\gallery2\lib\adodb\drivers\adodb-odbc.inc.php on line 61
localhost: Connection error to server 'localhost' with user 'lmenard'
----------------------------

Looking at that code in "adodb-odbc.inc.php", I presume the "not used" message is informational, not fatal (because debug is enabled). And on the odbc_connect() call, $argDSN *is* specified first.

So I printed out the value of $argDSN, and for some reason it is "localhost".

Shouldn't the DSN be the same as the database name?

it has been some time since i last worked with ODBC on windows.
but as far as i remember, i had to setup all ODBC connections in a windows odbc manager. e.g. like http://www.databasejournal.com/features/mssql/article.php/2238221

so instead of db host you'd use the DSN in the g2 db host field in the database setup step.
try entering your dsn there instead of localhost.

does that make sense? or should the db name be used as dsn?

well, first get it working, then let's discuss about usability issues
we can change the html form / add notices etc of course.

Just for completeness... yes I did run gmake in modules/core/classes as you said originally. Here's the optput of that command (it doesn't say anything about the db2 directory):

cd interfaces && gmake - --unix
gmake[1]: Entering directory `/cygdrive/c/My Server/gallery2/modules/core/classes/interfaces'
perl ../../../../lib/tools/bin/extractClassXml.pl --dtd=../../../../../lib/tools/dtd/GalleryClass2.0.dtd --stub-ok --quiet --out-dir=tmp ../*.class
php ../../../../lib/tools/bin/generate-interfaces.php GalleryCore
Content-type: text/html
X-Powered-By: PHP/4.3.7

gmake[1]: Leaving directory `/cygdrive/c/My Server/gallery2/modules/core/classes/interfaces'
cd GalleryStorage/DatabaseStorage/schema && gmake - --unix
gmake[1]: Entering directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema'
cd xml-src && gmake - --unix -w xml
gmake[2]: Entering directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema/xml-src'
perl ../../../../../../../lib/tools/bin/extractClassXml.pl --dtd=../../../../../../../../lib/tools/dtd/GalleryClass2.0.dtd --quiet --out-dir=tmp ../../../../*.class
cp *.xml ../xml-out;
php -f ../../../../../../../lib/tools/bin/generate-dbxml.php
gmake[2]: Leaving directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema/xml-src'
cd platform && gmake - --unix -w sql
gmake[2]: Entering directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema/platform'
php -f ../../../../../../../lib/tools/bin/generate-sql.php ../xml-out
gmake[2]: Leaving directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema/platform'
gmake[1]: Leaving directory `/cygdrive/c/My Server/gallery2/modules/core/classes/GalleryStorage/DatabaseStorage/schema'

Nope, there is nothing in the "mysql" after running gmake.

I now believe I may have grabbed the wrong build. I couldn't find an obvious link to nightly build images from the Gallery home page. I found a bunch of nightly builds on http://galleryupdates.jpmullan.com, but wasn't sure if they were developer builds. So I grabbed the developer build from the home page, which seems to be from Sept. 12th.

I'll try grabbing the latest build from http://galleryupdates.jpmullan.com... hopefully they are developer builds.

OK, I got "gallery-2.0+.zip" from http://galleryupdates.jpmullan.com.

Now I'm unable to get the gmake to work. First it squawked about:

but I resolved that by hard-coding the entire path to that file. Now it gets past that, but fails with:

I'm at a loss to explain that one.

I figured this one out too. Needed to set "register_argc_argv = On" in my php.ini.

It then squawked about the unix-style 'find' command. It was finding the Windows version of the command. I pointed it to my copy of the unix-style 'find.exe' and it got past that.

It then squawked again about a relative path "../../../../../../../lib/smarty/Smarty.class.php" in "generate-dbxml.php", so I again hard-coded the entire path.

The gmake is now successful,

but I still have no files in "gallery2\modules\core\classes\GalleryStorage\DatabaseStorage\schema\platform\db2". I then renamed the "mysql" directory and re-created an empty one, and after re-running gmake there is nothing in there either. So somebody somewhere is apparently not reporting an error.

I'm trying to attach my patched files here, but am getting errors about "invalid extension". It seems to insist on adding ".txt" to the file name.

So screw that, here's a link to my updated files.

and in generate-sql.php, don't forget to replace
foreach (array('mysql', 'postgres', 'oracle') as $db) {
with
foreach (array('mysql', 'postgres', 'oracle', 'db2') as $db) {

yep, works for me. the db2 sql is generated too. but i don't send it to you, since as you said, it's just the oracle sql at the moment.

what about your gmake flags? is the unix thing required?

as i said, the db2/*.sql will look like the oracle/*.sql files if you just have copied the oracle class in generate-sql.php.

maybe bharat can tell you why gmake doesn't work for you correctly.
but you should definitely try cygwin, if you haven't already.

Hi, Larry. I've been reworking this framework quite a bit in the past week so things are in a state of flux right now. But we'll sort all of this out. Sorry for the hassle! Also, the "generate-sql.php" script that I recently created is really a tangled mess right now. It was my first attempt, and I improved the model with the generate-dbxml and generate-interfaces scripts, but haven't gone back and fixed generate-sql yet.

The first thing I do is to stop doing builds in modules/core/classes and instead do them in modules/core/classes/GalleryStorage/DatabaseStorage/schema. That'll cut out the step where we build interfaces, which you don't care about now.

I've tweaked generate-sql.php a little bit so that it generates db2 files that are exactly like Oracle. Here's my version:
http://www.menalto.com/.outgoing/gallery/generate-sql.php.txt

I haven't tested this on Windows yet (my windows box is at home) but if you replace your generate-sql.php script with this one and then run make in the schema directory, you should get the appropriate DB2 SQL files, which you can then tweak. If that doesn't work, can you provide me with a link to a text file with the output from your make run?

I'm pretty sure you want the --unix flag. That's generating the correct output, so theoretically it should have worked. I installed cygwin on a new windows box, set up xampp, installed g2, and ran my modified script and it generated the db2 files for me. It had issues with missing make/gmake/xmllint/rxp and some other issues, for which I've improved the scripts a little bit, but none of those things should be affecting you.

Let's break it down further. Go to the core schema directory and do a "make" and verify that it creates an xml-out directory with a bunch of xml files in it. This is stage 1 -- if that doesn't work then there's a problem with generate-dbxml.php. Then if that works go to the platform directory and look to see if you have a db2 directory. If you don't, then try:

• Verify that you've got my tweaked script in lib/tools/bin/generate-sql.php
• Delete the contents of the mysql directory and run the script again -- does it recreate the .sql files there?
• put some debug statements in generate-sql.php and see if/where it's going wrong

something in there should work!

but you used gmake in 'modules\core\classes\GalleryStorage\DatabaseStorage\schema' right?
maybe add
var_dump(getcwd());
var_dump(glob('../xml-out/*.xml'));

right before the foreach statement for debugging.

Yup, confirmed it with a little test script. The current directory in a given script is the directory in which the script exists. I gather this is not the case in php on Linux/UNIX?

Perhaps it would be wise to avoid using relative paths. How about using a variable called something like GALLERY_ROOT to qualify all path references?

Ah-haa, I just found the following:

http://ca3.php.net/getcwd

There are 2 copies of "php.exe" in the distribution I have:

If I prepend '/cygdrive/c/Program Files/php/cli' to my PATH, it now reports the correct current directory, but seems it still can't find the xml files.

FYI, my php version is:

I'll try upgrading and we'll see what happens.

What a pain! So even the 4.3.7 CLI version of PHP doens't work? I'll investigate that in more detail and see if I can figure out what's going on there. In the meantime I'm glad to see that you've got it working and can move forward! I'll be very happy to see a DB2 version of the SQL!

Hm .. the missing InstallerTest_sql files indicates that you're using a build from last week (or older). The latest stuff no longer builds that file, which is why when you deleted the mysql dir it didn't come back. Really the best thing to do is to get the latest code from CVS ( see http://gallery.menalto.com/wiki/DownloadingGalleryUsingCVS ) and then use my tweaked script and see if that gets you by the problem. Then when we make fixes to help you along, you can get them quickly by just updating your code.

Great... something else I have to learn, install, configure, and debug. Sigh...

Well I've already taken a bite of the CVS apple, so I may as well finish it.

I found a web page that describes how to install & use CVS on Windows
(http://www.ifm-services.com/people/jamesk/papers/cms/cvs-win32-client.html).
I've now installed CVSNT, WINCVS, and Python, but then the web page goes into detail
that I suspect may not be necessary.

Any chance anyone can provide a bare-minimum 'CVS For Windows For Dummies'?