Support for DB2 databases?

@BIT_OR:

i realized yesterday that the - (COUNT(*) - CONSTANT) is NOT necessary.
since BIT_OR will only increase the value of a sequence of 0s and 1s, MAX(BIT_OR(column)) is already fine.

your UDF BIT_OR() function will nevertheless have to be a UDF that outputs the intermediate result after each step and puts it also on the scratchpad.

- just to be sure: you did this counter just to check if your UDF works (does something correctly for each row, and prints out the result)
- cool that this works
- but we don't need a counter (as explained in my last post). so now you can replace the counter with the AND operation... (i guess you had this in mind anyway, just wanted to make sure that we're going into the same direction)

@MAX:
we'll replace

	$query = '
	SELECT
	    [GalleryAccessSubscriberMap::itemId],
	    BIT_OR([GalleryAccessMap::permission])
	FROM
	    [GalleryAccessMap], [GalleryAccessSubscriberMap]
	WHERE
	    [GalleryAccessSubscriberMap::itemId] IN (' . $itemIdMarkers . ')
	    AND
	    [GalleryAccessSubscriberMap::accessListId] = [GalleryAccessMap::accessListId]
	    AND
	    ([GalleryAccessMap::userId] = ?
	    OR
	    [GalleryAccessMap::groupId] IN (' . $groupIdMarkers . '))
	GROUP BY
	    [GalleryAccessSubscriberMap::itemId]
	';

with

$storage =& $gallery->getStorage();
	list ($ret, $bitor) = $storage->getFunctionSql(
	    'BIT_OR',
	    array('[GalleryAccessMap::permission]'));
	if ($ret->isError()) {
	    return array($ret->wrap(__FILE__, __LINE__), null);
	}

	$query = '
	SELECT
	    [GalleryAccessSubscriberMap::itemId],
	    ' . $bitor . '
	FROM
	    [GalleryAccessMap], [GalleryAccessSubscriberMap]
	WHERE
	    [GalleryAccessSubscriberMap::itemId] IN (' . $itemIdMarkers . ')
	    AND
	    [GalleryAccessSubscriberMap::accessListId] = [GalleryAccessMap::accessListId]
	    AND
	    ([GalleryAccessMap::userId] = ?
	    OR
	    [GalleryAccessMap::groupId] IN (' . $groupIdMarkers . '))
	GROUP BY
	    [GalleryAccessSubscriberMap::itemId]
	';

and in Db2DatabaseStorage.class we'll add:

	case 'BIT_OR':
	    $sql = 'MAX(BIT_OR(' . $args[0] . '))'
	    break;

while other DBs will have:

	case 'BIT_OR':
	    $sql = 'BIT_OR(' . $args[0] . ')'
	    break;

your BIT_OR is different from other BIT_OR functions, since it returns COUNT(*) results, and not a single end result. thus we need MAX to select the end result.
as explained, MAX() will work for a BIT_ORed intermediate result list, since OR only adds something, so the intermediate result with the max value is the last intermediate result anyway. lucky us.

how can you control to only output the last intermediate result?

Ah, on second thought, I guess not. I see your point.

Sorry, never mind... I had forgotten to do the "gmake" in "modules/cor/classes/GalleryStorage/DatabaseStorage/schema".

However, In Step 8 I get error:

Thanks, Valiant.

> did gmake change anything for you?

Yes, it got me past step 5 all the way to Step 8, where I hit the _executeSqlFile() problem.

> remove all blank lines after a ; within CreateDb2Udfs.sql

Not sure what you mean by that. Remember that I created that SQL on Windows, so there might be an extra LF in it when you look at it in a unix editor... it looks fine to me.

I've replaced the code in db2DatabaseStorage as you described (you're missing a ';') and I'm now getting a different error, still in Step 8.

Thanks.

Also, I don't know if this is related, but I now get another warning in Step 2:

I enabled gettext in my 'php.ini', restarted my web server, and closed all browsers, and still get the warning.

> blank lines after ';'

I see. Thanks.

> missing ';'

> installer Step 5

I presume you mean the "use dsn as arg 1" message. It seems to only show up when there's another more serious error. I'm not sure how to suppress it completely.

Fixing my copy of CreateDb2Udfs.sql, and it didn't help with the current error. I stuck a var_dump($query) in the error block of DatabaseStorage.class at line 305:

	if ($recordSet) {
	    return array(GalleryStatus::success(),
			 new DatabaseSearchResults($recordSet));
	} else {
var_dump ($query);
	    return array(GalleryStatus::error(ERROR_STORAGE_FAILURE, __FILE__, __LINE__), null);
	}

and it says:

I'm not sure why it's printing two different statements. I presume the 2nd is the fatal one.

Attached here.

I got Step 9 to work. There are two problems:

1) Comments are allowed but only inside the PL/SQL blocks (between the BEGIN ATOMIC and END, where DB2 recognizes them).

2) The DROP FUNCTION statements were returning errors because the functions to not exist in a newly-created database.

Attached is the CreateDb2Udfs.sql file that works.

Now the install completes successfully.

When I run gallery2/main.php, I get a new error:

Details:

Looks like a problem with the 3rd arg I'm passing to the G2_LIKE code. I'll look into it.

Ah, of course. Thanks.

I've fixed it, but am now getting error:

I'll look into that. Hope it's not as bad as it sounds.

Ah, that's it. I was just about to post an observation that that this was working fine on the previous build with the updated files that I sent you.

Now I'm getting an error in G2_LIKE:

db2 error: [0: ] in EXECUTE(" 
SELECT iam0.g_itemId, COUNT(iam1.g_itemId)
 FROM g2_ItemAttributesMap AS iam0, g2_ItemAttributesMap AS iam1, g2_AccessSubscriberMap
 WHERE iam0.g_itemId IN (?)
 AND G2_LIKE(iam1.g_parentSequence, char(iam0.g_parentSequence) || char(iam0.g_itemId) || char('/%'))
 AND iam1.g_itemId = g2_AccessSubscriberMap.g_itemId
 AND g2_AccessSubscriberMap.g_accessListId IN (?,?)
 GROUP BY iam0.g_itemId
 ") 
--------------------------------------------------------------------------------
(db2): 
SELECT iam0.g_itemId, COUNT(iam1.g_itemId)
 FROM g2_ItemAttributesMap AS iam0, g2_ItemAttributesMap AS iam1, g2_AccessSubscriberMap
 WHERE iam0.g_itemId IN (?)
 AND G2_LIKE(iam1.g_parentSequence, char(iam0.g_parentSequence) || char(iam0.g_itemId) || char('/%'))
 AND iam1.g_itemId = g2_AccessSubscriberMap.g_itemId
 AND g2_AccessSubscriberMap.g_accessListId IN (?,?)
 GROUP BY iam0.g_itemId
   [ (0=>'7') (1=>'8') (2=>'9') ] 
--------------------------------------------------------------------------------

Warning: odbc_prepare() [function.odbc-prepare]: 
SQL error: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "AND" was found following "emId) || char('/%'))".
 Expected tokens may include: "<interval_qualifier>".
 SQLSTATE=42601 , SQL state 42601 in SQLPrepare in C:\My Server\gallery2\lib\adodb\drivers\adodb-odbc.inc.php on line 502

I don't see anything wrong with this SQL. ??

Yeah, that was it. I added " = 1" to the end of the clause in the G2_LIKE code, and it works. I'm now at the BIT_OR error, as expected.

No prob. Thanks. ;-)

haven't coded java in a while, maybe try (scratchPad[ctr]).intValue() or just (integer)(scratchPad[ctr])

@code:
- you have to to do a == 1 compare operation per bit anyway, but not more
i'd do (pseudo code):
1. scratchPad = data loaded from scratch
2. input = data from current column row
3. iterate over each bit
foreach bitposition i
if (input[i] == 1) {
scratchPad[i] = 1;
}
print scratchPad[i];
}
4. store scratchPad in scratch again for next row

it's almost the same as you already have, just less == operations.

but why not use boolean operators of hava?
load the data from scratchpad into an integer
convert the input row data into an integer
result = input & scratchPad;
and then print out the result as string again...

you've got an input string x "00100010011";
use integer input = Integer.parseInt(x, 2);
do the same for scratchPad, then you can simply call integer result = input & scratchPad;
and you can print Integer.toString(result, 2);
see:
http://javaalmanac.com/egs/java.lang/ConvertNum2.html
http://java.sun.com/j2se/1.4.2/docs/api/java/lang/Integer.html

with my suggested code, you receveive a String in your function, and you convert it to integer and back to a string.

what do you need byte for? for storing it in the scratchpad?
ok, i see getScratchPad returns a byte[] array:
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/r0008987.htm

an int = 4 bytes

hacky code (maybe there are simple methods):
int fromScratch = 0;
for (int i = scratchPadByteArray.length - 1; i >= 0; i--) {
fromScratch |= scratchPadByteArray[i] & 0xFF;
if (i != 0) {
fromScratch <<= 8; // leftshirt by 8 bits
}
}

maybe you can use the same methods as Kurt...

i'm sure this could done easier... but i myself haven't coded too much in java and it's also been a few years ago since i last coded in java.

but your string -> byte[] -> Byte conversion could at least be made one step shorter.

just use
1. no result array is needed since we can write the new result into the scratchpad variable.
2.
in for { use:
if (columnValueArray[ctr] == 49) { // 49 is the ascii value for "1" see www.asciitable.com
scratchPad[ctr] == 49;
}
System.out.println(scratchPad[ctr]); // cast byte to string or so to get 1 and 0 instead of 49 and 48
} // end for

sorry, should have been:
scratchPad[ctr] = 49;

basic programing doh!
== is for comparison
= for assignment

SQL is different of course, but SQL is SQL.

Ah, excellent
Btw: We can test whether DB2/G2 works correctly with the 1500+ unit tests of G2, which should trigger all code of G2 at least once.
you can start them with yourgallery/lib/tools/phpunit/index.php

but before, you need to be able to login
and you should put your php memory_limit to 64M.

i will commit your changes tomorrow, it's already night here.

I'm curious about the performance since we've got here quite slow implementations of BIT operation (using varchar in SQL instead of int, and the UDF calls...i don't know. i'm just curious

@installing:
we'll certainly have to at least create installation notes for DB2. thanks for already thinking about that and writing
i guess we should show these instructions in the db step when the user selects DB2.
of course we could ask the user to enter the path to sqllib (if we can't get it from the path ENV or from db2) and make it writable, but asking the user to copy a file is fine i guess.

@hostname / db name field:
right, will need to change the installer for that. do you only need the hostname field for DB2? should i only show a single field for hostname/db name and tag it "Database Name"?

no, you don't need to add anything to your g2.
but it only tests active modules.
but testing core only is already a huge thing

no, it doesn't. you can add items, it just doesn't generate thumbnails.
you can activate other modules in site admin -> modules, if you want.

if the first steps seem to be a success, you can try to run the unit steps.
i'm sure there will be errors. they don't even have to be related to your DB2 stuff, G2 is in development right now and sometimes a few tests fail. then we have to fix things.. _

other modules don't need any special SQL, no. some of the modules have external dependencies, e.g. imagemagick binaries etc.

@makefiles:
the the db2 java file won't be part of our make scripts.
we'll deal with it exactly like we deal with the other java binary files we have in g2 (slideshow, upload applet, crop applet). they are just in a data/ directory in the specific module as .jar file.
you don't have to build a jar file.
once we're finished, we'll have to make sure that we have the source code etc. just in case someone wants the source or if we need to recompile the java class because we need to change something.

there's no need to put it in a jar, absolutely not. the jar makes just things easier for the multi class files applets.

so you say we can install the udf with g2, no need to move the file elsewhere? excellent!!
then we just put the class file in the DatabaseStorage/ dir

i'll be back in a few hours.

that would be awesome. installation would be as simple as for every other DBMS. i hope it works

what's the exact DB2 SQL you want to execute? i can help you with the absolute path stuff etc.

e.g. in Db2DatabaseStorage.class we could do:

$path = dirname(__FILE__) . $platform->getDirectorySeparator() . 'g2_bit_or.jar';
you could then use $path in your SQL query:
execute("call sqlj.install_jar(file://$path, myJarId);

on windows, $path could be:
c:\www\gallery2\modules\core\classes\GalleryStorage\DatabaeStorage\g2_bit_or.jar
on linux/unix:
/var/www/gallery2/modules/core/classes/GalleryStorage/DatabaeStorage/g2_bit_or.jar