Support for DB2 databases?

Well, the one-line fix might be to change adodb-db2.php.inc as follows:

This seems to get past the immediate problem. Unfortunately there are more after that.

Does that change to adodb-db2.php.inc sound reasonable to you folks?

Yes I did see the comments. But as you said, it doesn't work for me, and it's 10 times faster... than what? It doesn't discuss SQL_CUR_USE_IF_NEEDED.

I suspect the performance issue is moot anyway... we're talking about a single row result set, I suspect the case described in the file comments was considerably more than that.

Yes, the setting of $this->curmode in the constructor is the exact line I'm talking about. So there are two ways to make this change:

1) Set it to SQL_CUR_USE_IF_NEEDED regardless of platform
2) Set it to SQL_CUR_USE_IF_NEEDED only on Windows

For now, I'm going to set it to SQL_CUR_USE_IF_NEEDED only on Windows, unless you have any violent objections about it.

The new constructor I'm using is:

Until such time as I know if it's required for other platforms, I'd rather leave it as Windows-only for now.

The next problem I hit looks like a BIT AND issue.

I think I've found the statement code in "modules/core/classes/helpers/GalleryPermissionHelper_simple.class":

and in "modules/core/classes/GalleryStorage/DatabaseStorage/Db2DatabaseStorage.class":

Obviously the "&" isn't valid in DB2, so I'll need to implement some sort of BITWISE AND functionality, not a problem. But I'm not clear on exactly what it's trying to do overall. Specifically, I don't understand:

1) The "B". Is that supposed to be a way of casting the value of the subsequent parameter marker?

2) Why the same value of bits is pushed onto the $data[] array twice? We're comparing the 'B-casted' bit string to the 'un-B-casted' verion of itself? Why?

The dump of the $data[] array is:

Yes, I wrote that BIT_OR function (actually, I shamelessly 'borrowed' it from a DB2 developerWorks article) because there was already something similar there. There was nothing similar to a BIT_AND. I can easily create one though. Like I said, that's not a problem.

I've found some info about the "B"... it's a funky way of casting a char string to PostgreSQL's BIT type. But I'm still confused as to why we're comparing the 'bit-casted' bit string to the original version of itself. What's the purpose of doing that? DB2 does not have a BIT datatype, so I need to understand what we're doing in order to come up with a DB2 equivalent.

I already understand what a bitwise AND is... what I don't understand is: Why is the same series of bits being pushed onto the $data[] array twice? What is the point of comparing the 'B-casted' bit string to the 'un-B-casted' version of itself?

From what I see in the code, this will always be the case:

Larry Menard, I tried to explain it, I'll try it with other words....
this is one of our functions that use the BITAND:

    function fetchAccessListIds($permission, $userId) {
	global $gallery;

	$cacheKey = "GalleryPermissionHelper::fetchAccessListIds($permission, $userId)";
	if (!GalleryDataCache::containsKey($cacheKey)) {
	    list ($ret, $groupIds) = GalleryCoreApi::fetchGroupsForUser($userId);
	    if ($ret->isError()) {
		return array($ret->wrap(__FILE__, __LINE__), null);
	    }
	    $groupIds = array_keys($groupIds);
	    $groupIdMarkers = GalleryUtilities::makeMarkers($groupIds);

	    list ($ret, $bits) = GalleryCoreApi::convertPermissionIdsToBits($permission);
	    if ($ret->isError()) {
		return array($ret->wrap(__FILE__, __LINE__), null);
	    }

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

	    $query = sprintf('
	    SELECT DISTINCT
	      [GalleryAccessMap::accessListId]
	    FROM
	      [GalleryAccessMap]
	    WHERE
	      ([GalleryAccessMap::userId] = ?
	      OR
	      [GalleryAccessMap::groupId] IN (%s))
	      AND
	      ' . $bitAndPermission . ' = ?
	    ', $groupIdMarkers);

	    $storage =& $gallery->getStorage();

	    $data = array($userId);
	    $data = array_merge($data, $groupIds);
	    $data[] = $storage->convertIntToBits($bits);
	    $data[] = $storage->convertIntToBits($bits);

	    list ($ret, $results) = $gallery->search($query, $data);
	    if ($ret->isError()) {
		return array($ret->wrap(__FILE__, __LINE__), null);
	    }

	    $aclIds = array();
	    while ($result = $results->nextResult()) {
		$aclIds[] = (int)$result[0];
	    }
	    GalleryDataCache::put($cacheKey, $aclIds);
	} else {
	    $aclIds = GalleryDataCache::get($cacheKey);
	}

	return array(GalleryStatus::success(), $aclIds);
    }

let's say we call it with
fetchAccessListIds('core.viewResizes', '8'),
so we want to know to which access list ids the user has permission to viewResizes (and access list ids are mapped one to many to entities in G2). so with another simple query we'll get the complete list of entityIds for which the user has core.viewResize permissions.

so first we get the id for core.viewResizes from PermissionSetMap which is 2.
we also get all the groupIds the user is a member of. let's say he's a member of group everybody and registered users, 2 and 4.
so we can build our query:

	    SELECT DISTINCT
	      g2_GalleryAccessMap.g_accessListId
	    FROM
	      g2_GalleryAccessMap
	    WHERE
	      (g2_GalleryAccessMap.g_userId = 8
	      OR
	      g2_GalleryAccessMap.g_groupId IN (2, 4))
	      AND
	      g2_GalleryAccessMap.g_permission & 2 = 2;

I guess you understand everything but the last line. We could also write it as:
BITAND(g2_GalleryAccessMap.g_permission, 2) = 2; , to note that we first execute the AND operation and then compare the result with 2.
The two 2's in the last line will always be the same number, a power of 2 (well, we also have combinations of permissions, which are the sum of other permissions).
When you AND an arbitrary number with a power of 2 then the result is always 0 or again the same power of 2.

examples:

nr in ( ) is decimal, other numbers are in binary format
g2_GalleryAccessMap.g_permission          input         result
( 3) 00000011                     ( 1) 00000001  ( 1) 00000001  -> user has permission 
( 3) 00000011                     ( 4) 00000100  ( 0) 00000000  -> user has no permission
( 3) 00000011                     (16) 00010000  ( 0) 00000000  -> user has no permission
(81) 01010001                     (64) 01000000  (64) 01000000  -> user has permission

as you see, the result of the AND operation is always equal to the one of the AND operators or 0. if it's 0, we don't have the permission for this ACL id.

this should already be enough to explain our system. just for completenesss, i'll add a more complicated case too, which is when the permission we're checking for is a permission set of other permissions. e.g. core.viewAll is the sum of core.view (1 = 0001) + core.viewResizes (2 = 0010) and core.viewSource (4 = 0100) so core.viewAll is 7 = 0111.
examples:

g2_GalleryAccessMap.g_permission          input         result
(  3) 00000011                     ( 7) 00000111  ( 3) 00000011  -> result != input, we only have permission to viewResizes and view, but not viewSource -> not what we're looking for
(  7) 00000111                     ( 7) 00000111  ( 7) 00000111  -> result == input, we have core.viewAll for this ACL id
( 38) 00100110                     ( 7) 00000111  ( 6) 00000110  -> result != input, we don't have core.view -> not what we're looking for
(247) 11110111                     ( 7) 00000111  ( 7) 00000111  -> result == input, we have core.viewAll for this ACL id

as you see, we don't get 0 or the input sequence as result, the result of the AND operation is equal to the input or equal to the sum of 1 or more members of the permission set or 0. but we're only interested if it's again the input or something else.

Thanks!

My DB2 is mainly on Linux, Although I used to have a few on Windows as well. But recently managed to get them all over to Linux. I also hope to have a few AIX ones soon though. But that's currently only in the planning stage at this time.

My webserving is split across Linux and Windows. However, with Websphere on Linux, and Lotus Domino on Windows, we have plans to migrate Domino away, but can't decide on migrating to Linux, AIX, or OS/400 yet. (Too many choices :-p)

I also would like to commend the G2 developers for their assistance and open minds about DB2 support. As only a few open source projects currently have DB2 support, and many of the ones that do not, IMO are very entrenched in the "MySQL is the only way" mindset, while I agree that MySQL does have its uses, and some of the things it does are all well and good. I find that there is NO DBMS solution that is the absolute greatest at doing everything. As I am sure everyone can agree, that although the DB vendors try very hard, there are still areas where each DBMS pales in comparison compared to others. Combined with the impracticality of running a seperate DBMS solution for every single app, I think multi-DBMS support is something more developers need to think about, both open and closed source.

If you need help with anything let me know. I have access to both new and archive DB2 code, If for some reason you need old code, I can help with old code down until DB2 v5.x. Also if the G2 devs need access to a DB2 box, I should be able to help. However, I am in Australia, and I only have a 512/512kbps link which is fairly generous for Australia .

So if you need anything just give us a yell. We would be happy to help wherever possible.

cool

yeah. it's a lot of work mostly because db2 isn't used that often in the php world. else we wouldn't have had the adodb / ODBC issues etc.

BTW, it seems there's only one 0-byte long entry in the g2_ItemAttributesMap.g_parentSequence column. (I believe this is not a NULL, it's a 0-byte string.)

Is that to be expected?

DB2 does support concatenation ("||" and "CONCAT(..., ...)", but apparently its use in a "WHERE ... LIKE ..." clause is limited.

The answer I got in comp.databases.ibm-db2:

So we need to either find a portable way to do it, or implement a DB2-specific override here.

(I probably shouldn't say this, but... since undertaking this project, I'm now beginning to develop more of an appreciation for the criticism I've seen reported about DB2's shortcomings.)

UDFs don't work in WHERE predicates either... only in SELECT clauses.

A further response to my newsgroup posting added the following:

I'm going to think about this for a while... will let you know if/when I think of anything.

Sorry, you're right. (I only got about 2 hours sleep last night, not thinking very well at the moment.)

Let me chew on the SQL for a while, but I will need your input as to how to implement the SQL difference. For example, should we implement another CASE in getFunctionSql() in Db2DatabaseStorage.class?

Hmm... it's not going to be as easy as that.

The problem is not the concatenation, and it's not the datatype of the columns involved. The problem is simply that the fact that column names cannot be specified in a LIKE predicate.

The only things that can be contained in the LIKE predicate are:

- A constant
- A special register
- A host variable
- A scalar function whose operands are any of the above
- An expression concatenating any of the above

It appears to me that the only way to do this in DB2 is to split this query into two separate queries:

- the first one putting the values of iam0.g_parentSequence and iam0.g_itemId into host variables,
- the second one doing the actual "SELECT iam0.g_itemId FROM g2_ItemAttributesMap AS iam0, g2_ItemAttributesMap AS iam1 WHERE iam1.g_parentSequence LIKE hostvar || '/%'".

Your thoughts?

That's about what I thought you'd say.

I've been trying to come up with a UDF solution, but it keeps complaining about it being an invalid LIKE predicate.

I already told you above what DB2 newsgroup I'm working with.

The DB2 support in comp.databases.ibm-db2 is very good. That's where I'm getting the DB2 help I've got so far.

I have some bad news, and some good news.

First the bad news.

It has been made very clear to me in the newsgroup that:

- column names are not allowed in LIKE predicates

- UDFs written in SQL/PL (or PL/SQL) are not allowed in LIKE predicates
(this particular little gem is undocumented )

Now the hopefully good news.

How about if I replace:

with:

?

I haven't tried it in the actual G2 code yet, but as a standalone statement in my simplified testcase it seems to work. ;-)

And a much less attractive possibility: In order to keep the LIKE predicate I could make the UDF 'external' by writing it in Java, but that could be messy because:

- that would mean either shipping a binary ".class" library file and/or building it from source as part of the G2 build step

- during the install, the ".class" library file would have to be copied to a directory in which the DB2 runtime will be able to find it (like '.../sqllib/function').

And I haven't done a quick prototype of that, so I don't even know for sure that it would work.

Could you please review my suggested change above, and let me know if you have any concerns about it?

phew, a solution
your suggestion:

WHERE iam1.g_parentSequence LIKE iam0.g_parentSequence || iam0.g_itemId || '/%'
with
WHERE SUBSTR(iam1.g_parentSequence, 1, LENGTH(iam0.g_parentSequence) + 5) = iam0.g_parentSequence || CAST(iam0.g_itemId AS CHAR(4)) || '/%'

1. are you sure strings start at 1 and not at 0 in DB2 / substr?
2. CAST(iam0.g_itemId AS CHAR(4)) ? 4 chars only? 4 chars = max 9999 items. itemId can be much larger what's the limit of TYPE integer in DB2? do you have to specify as CHAR(4) ? can't you just use CAST(iam0.g_itemId as CHAR) ?
3. this explains your +5, which i don't like. exactly because itemId isn't limited to 4 chars in the real world.
4. we can leave away the /% since the % has only a meaning in LIKE statements
5. - iam1.g_parentSequence could be shorther than LENGTH(iam0.g_parentSequence) + LENGTH(CHAR(iam0.g_itemId) + 1), do we then get a DB2 error, because we tried to get a substring which is longer than the original?

so here's what i think would be better, if it works:

WHERE LENGTH(iam1.g_parentSequence) >= (LENGTH(iam0.g_parentSequence) + LENGTH(CHAR(iam0.g_itemId) + 1) AND
SUBSTR(iam1.g_parentSequence, 1, LENGTH(iam0.g_parentSequence) + LENGTH(CHAR(iam0.g_itemId) + 1) = iam0.g_parentSequence || CHAR(iam0.g_itemId) || '/'

now, the question whether this is a correct replacement:
- yes! great job. that's really excellent!
- if this CHAR casting needs an argument / needs to know how long it should make the string, then we have to find a solution for this hopefully small problem

@java:
we'd of course ship g2 with a compiled java file. but it seems we don't need it.

you may have overlooked my answer since we were posting almost at the same time. please check my answer.

Sure not a problem, but if you do want the MTK to try it I'm more than happy to put it up, or send it to you.
As if I understand what they said correctly, and they wrote a new LIKE predicate, then it could work. As it sounds like the existing LIKE is not fucntional enough, so they replaced the LIKE with one more similar to other DBMS's?

Just some guessing.

- INTEGER: 2,147,483,647 is certainly better than 32,767. else we'd have had a problem, since our sequence for IDs in G2 reaches a few thousands quite fast.
- LOCATE is also fine. i don't know if it is faster than the other approach since it doesn't stop comparing the two strings after the LENGTH(iam0.g_parentSequence || CHAR(iam0.g_itemId) || '/'), but i guess it could still be faster...

a slight correction:
AND LOCATE(iam0.g_parentSequence || CHAR(iam0.g_itemId) || '/', iam1.g_parentSequence) = 1
i removed the 3rd argument since it defaults to 1 anyway and i require a result of 1 not just > 0 since our original statement was a LIKE statement which doesn't have % at the beginning.

so now we have a replacement for
OP1 LIKE CONCAT(OP2, OP3)
constructs. we're using concat in very few places, but we'd have to change those a little and we'd have to change the DB abstraction classes a little, i.e. add LIKECONCAT or something like that.

of course we'd prefer if we didn't have to do this.
how much work would this UDF LIKE replacement be? if you give me the choice between a UDF LIKE and the LIKE CONCAT replacement, i'd choose the UDF LIKE.
then we could just add a DB abstraction LIKE instead of a weird LIKECONCAT and we wouldn't have to deal with additional issues and it would be more flexible.

I can understand that you're quite happy with the LOCATE() solution and that you don't want to spend even more work on this.

sphericus
could you maybe create this LIKE UDF?
it sounds like the algorithm was already posted somewhere, right?

of course, if it's too complex, we'll go with the LIKE CONCAT replacement...

The MTK is 38MB and it exceeds the maximum size for this forum. Do you have any preference for distribution?

I've started up my FTP server ("ftp cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com"), and created userid "sphericus" with password "gallery2". You can put the file there (remember to specify binary if it's compressed).

I don't like to leave the ftp server running, so I'll shut it down when I see the file arrive.

Thanks.

Got it. I'm shutting down my server now. Thanks, Sphericus.

valiant: I would write a LIKE UDF if, I had the expertise, which I lack.
However, I believe that Larry may be able to come up with something, as there are LIKE UDF's in the MTK, four infact that I was able to find. Two for MS SQL migration, and two from a Sybase migration. Hopefully one of those will be functional.