Support for DB2 databases?

I found the problem with SessionTest.testSessionCookieHeaderAndPersistence... apparently it assumes that the ImageFrame and Migration modules are installed and activated.

I thought these tests were supposed to only run of their required modules were installed?

I'll install all modules and re-run the whole set of tests again.

Yeah I saw your response after I posted mine. ;-)

Next problem... when installing *all* modules, I now get a database error. There's an "UPDATE g2_SessionMap" statement that is squawking, but it doesn't seem to be fatal... it doesn't cause the module install/activation to fail. I just happened to notice it at the bottom of the page.

The statement is very long, but starts off with:

UPDATE g2_SessionMap SET
 g_userId=6,
 g_remoteIdentifier='a:2:i:0;s:11:"192.168.1.1";i:1;s:32:"2581833420733f29e9446ab1312d0ac0";}',
 g_creationTimestamp=1139677980,
 g_modificationTimestamp=1139678138,
 g_data='...'
 ...

It's the 'g_data' value that is apparently too long (the DB2 message is truncating it). That column is currently defined as VARCHAR(8000). What do you think a safe length for it should be?

In DB2 the max VARCHAR is 32K, so I'll try using a CLOB(64K). Will let you know how it goes. Thanks.

In Gallery::getLockSystem(), I'm printing $which, and it is "database". I don't know why the switch() is failing. ???

I'm printing it immediately before the which(), so it is printed in all cases, success and failure.

I updated the error message, but don't see it in the output?

Ah, but when I var_dump() it (as opposed to just printf()), $which is a string(9). Why 9?

Even when I dump it in the default:, it is 9. By d displaying the ascii value of each byte in the string, the last character is a null (x'0').

How would that be caused by the TEXT-MEDIUM changing to CLOB(64K)?

no idea what that change in db2 implies for the returned data etc. can't help you there.
there sure shouldn't be added a terminating ascii 0 character. at least not as part of the string.

Bug opened. http://pecl.php.net/bugs/bug.php?id=6755

Thanks. Did you check in the CLOB(64000) change too, or are you waiting for resolution to the 'ibm_db2' bug for that?

i guess it's a good idea to wait for the db fix / notice before we use CLOB for the session data. else sessions won't work at all.
please add everything i missed to commit on the codex page.
thanks!

Dan has acknowledged the bug. Hoping for a fix today.

ok, let me know if the session stuff etc works with CLOB(64K) and if so, i'll commit the change to g2.

Brand new build, same problem.

I've chosen AdminCoreControllerTest.testChangeCookiePath to debug. I see that it loops around an array of 6 different ['path'] keys.

When I run each of those 6 paths in isolation (comment out the rest), each one of them runs clean.

When I uncomment the first 2 paths, the test reports "Success", but it does generate STORAGE_ERROR_FAILUREs (no DB2 errors).

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/Gallery2/Gallery%20Unit%20Tests%20-%20testChangeCookiePath%20-%202.htm

When I uncomment the first 3 paths, the test now reports "Failure", I get the same STORAGE_ERROR_FAILUREs, and it now has some DB2 errors (db2_fetch_array()).

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/Gallery2/Gallery%20Unit%20Tests%20-%20testChangeCookiePath%20-%203.htm

Any words of wisdom as to how to tackle this?

Yes, it is also CLOB(64K):

C:\MyServer>db2 describe table g2_pluginparametermap

Column                         Type      Type
name                           schema    name               Length   Scale Nulls

------------------------------ --------- ------------------ -------- ----- -----
-
G_PLUGINTYPE                   SYSIBM    VARCHAR                  32     0 No

G_PLUGINID                     SYSIBM    VARCHAR                  32     0 No

G_ITEMID                       SYSIBM    INTEGER                   4     0 No

G_PARAMETERNAME                SYSIBM    VARCHAR                 128     0 No

G_PARAMETERVALUE               SYSIBM    CLOB                  65536     0 No


  5 record(s) selected.


C:\MyServer>db2 describe table g2_sessionmap

Column                         Type      Type
name                           schema    name               Length   Scale Nulls

------------------------------ --------- ------------------ -------- ----- -----
-
G_ID                           SYSIBM    VARCHAR                  32     0 No

G_USERID                       SYSIBM    INTEGER                   4     0 No

G_REMOTEIDENTIFIER             SYSIBM    VARCHAR                 128     0 No

G_CREATIONTIMESTAMP            SYSIBM    INTEGER                   4     0 No

G_MODIFICATIONTIMESTAMP        SYSIBM    INTEGER                   4     0 No

G_DATA                         SYSIBM    CLOB                  65536     0 Yes


  6 record(s) selected.

But if an INSERT is failing, why do I not see an SQL error message to that effect?

And even if I put a debug print() in the Execute() function, I do not see it in the output of the "2 path" scenario. In fact, when I run the "2 path" scenario, I don't see any database activity at all in the trace. All I see are the STORAGE_ERROR_FAILUREs. I see it in the "3 path" scenario, but not the "2 path" scenario.

Ah-HAA! When I remove the "$this->_gs->_traceStart();" and "$this->_gs->_traceStop();" calls, I can finally see the information I need.

And yes, the problem we're getting *is* constraint violations. I've updated the URLs with the latest trace info:

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/Gallery2/Gallery%20Unit%20Tests%20-%20testChangeCookiePath%20-%202.htm
http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/Gallery2/Gallery%20Unit%20Tests%20-%20testChangeCookiePath%20-%203.htm

So why are we getting constraint violations? Is there perhaps a DELETE from g2_PluginParameterMap that is not working (and that is not being caught/reported)?

@ traceStart / traceStop:
enable immediate debug mode to see the output anyway

@duplicate rows:
if we had duplicate OK / UK it would of course be a problem for other DBMS too. we'd get the same error from mysql, pg etc too. but we don't get errors there. maybe you need to do a fresh install.

@ using LIKE instead of = for PluginParameterMap.parameterValue
1. where do we compare its value to something else?
2. generally: replacing = with LIKE "just for DB2" is not an option. maybe we can make an exception somewhere, but there should be very good reasons
3. if we use IN somewhere, replacing it with x times LIKE a OR LIKE b OR ... is obviously also not an option.

oh, i see. we use = and IN in GalleryCoreApi::removeParameterByValue(). darn.
so this means we can't use CLOB for TEXT at least not for PluginParameterMap.

an alternative would be to override GalleryStorage::removeMapEntry in Db2Storage. but duplicating all the logic seems a little expensive and theoretically, there could also be function calls of updateMapEntry() where parameterValue is set. darn.

@bharat:
the curent problem:
In G2 we need a few TEXT columns, that is, columns with up to 64kbyte size, e.g. for the session data and for plugin parameters.
DB2's varchar() type can't be that large. DB2's CLOB(64K) could fill the void but you cannot use a CLOB column in a = or IN(,,,,) comparison. you can use LIKE with CLOB though.
we need the IN and = comparison for PluginParameterMap.parameterValue e.g. for removePluginParameterByValue() but generally it could be used in any update / removeMapEntry call.
we don't need to compare the session data at any time.

so what should we do?
options:
1. in GalleryStorage::removeMapEntry, use LIKE instead of = and LIKE OR LIKE OR LIKE ... instead of IN. i'm -1 for that option. it's slower, and we'd do that just 'cause of DB2 and it affects all DBMS.
2. use DB2's VARCHAR(8000) (or max varchar size) for pluginParameterMap.parameterValue and use CLOB(64K) for SessionMap.data (8000 chars is too short for serialized session data). that would require that we change e.g. the memberType of GallerySession->data to something else than TEXT. or that we introduce other generate-sql changes. also, we'd assume that the PluginParameterMap.parameterValue is always shorter than 8000 characters.
3. duplicate / override removeMapEntry and updateMapEntry in Db2Storage.
4. add a few switches in updateMapEntry / removeMapEntry to give the Db2Storage a chance to replace LIKE with = and IN with a LIKE .. OR LIKE ..OR .. construct

After some trial-and-error, I found that I was able install core and all modules with TEST-MEDIUM of VARCHAR(32001). Unit Testing started off pretty well but eventually it blew up too.

I really hope that last schema change of yours was worth it for the product as a whole, because it has really screwed DB2 support. There's no way a change of that magnitude should be done so close to the scheduled ship date. I was soooo close, but now I'm set waaaay back.

I don't want to debate project management with you, but suffice it to say that I disagree.

While waiting for Bharat's input, I've started investigating the latest unit test failures errors (with VARCHAR(32001)).

First of all, the only way I can get the SQL error is if I remove the $this->_gs->trace*() calls and use setDebug('buffered').

The actual error is:

db2 error: [22001: [IBM][CLI Driver][DB2/NT] SQL0433N Value "Western European (ISO) Hello World Norwegian Norsk Hallo" is too long. SQLSTATE=22001 SQLCODE=-433] in 
EXECUTE("INSERT INTO g2_Item (g_canContainChildren, g_description, g_ownerId, g_summary, g_title, g_viewedSinceTimestamp, g_originationTimestamp, g_id)
VALUES (0,NULL,6,NULL,'Western European (ISO) Hello World Norwegian Norsk Hallo Verden Gallery er så utrolig bra at jeg har satt det opp til flere',1139938452,1139938452,1171)") 

But the length of that string is only 123 bytes, so it should work (the g_title column is VARCHAR(128)).

I can't reproduce this error outside of G2.

C:\MyServer>php testDb2Api.php ibm_db2

API in use = 'db2'.

Processing statement "CREATE TABLE mytable (g_id INTEGER NOT NULL,
                                                     g_cancontainchildren SMALLINT NOT NULL,
                                                     g_description VARCHAR(32001),
                                                     g_keywords VARCHAR(255),
                                                     g_ownerid INTEGER NOT NULL,
                                                     g_summary VARCHAR(255),
                                                     g_title VARCHAR(128),
                                                     g_viewedsincetimestamp INTEGER NOT NULL,
                                                     g_originationtimestamp INTEGER NOT NULL
                                                    )".

Processing statement "INSERT INTO mytable (g_canContainChildren, g_description,
g_ownerId, g_summary, g_title, g_viewedSinceTimestamp, g_originationTimestamp, g_id)
                               VALUES (0,NULL,6,NULL,'Western European (ISO) Hello World Norwegian Norsk Hallo Verden Gallery er så utrolig bra at jeg har sattdet opp til flere',1139938452,1139938452,1171)".

No errors.

Is Gallery modifying the string somehow?

the string is 123 utf8 multi-byte characters long.
are you sure
- your db is utf8,
- the connection is utf8 (i don't know if the db2 connection needs to be configured) and
- that db2 can store 128 multybyte characters in a varchar(128) field?

as test string, you should use:

$string =                         "Western European (ISO)\r\n" .
				  "Hello World \r\n" .
				  "Norwegian Norsk\r\n" .
				  "Hallo Verden\r\n" .
				  "Gallery er s\xc3\xa5 utrolig bra at jeg " .
				  "har satt det opp til flere"

that's the string from the unit test.

Ah, OK, that reproduces the problem. (Lesson learned: read the code, not the output.)

Next I doubled the size of g_item (and decreased g_description accordingly), and it worked successfully. Then I queried the length of the string in the database:

C:\MyServer>db2 select length(g_title) from mytable

1
-----------
        131

  1 record(s) selected.

Does that look reasonable to you? If so, then are we looking at simply making g_item larger? If so, how large? (Remember, whatever we add to g_item has to come off g_description because of the 32k row length limit).

Thanks.

Hi, Bharat.

I understand that you have requirements, and I know the Gallery code is very nicely modular. But I would think that the database schema would be one of the first things stabilized in a release. That's just based on my own experience in IBM... there were very strict rules regarding when 'database-breaking' changes could be made. Maybe in the 'real' world we just have to take our lumps for the team. ;-)

Sorry but I'm afraid I don't quite follow your suggestion. (I'm not very familiar with XML in general, never mind how G2 uses it.)

@Norwegian utf8 string:
yes, these are 131 characters.
no, we should not increase the size of the column since the test passes for MySQL and pg and both give a warning when the string that you insert / update is too long (MySQL since version 5 in strict mode).
and our GalleryStorage.class _truncateString function which is called in GalleryStorage::_normalizeValue() should truncate the 131 characters to 128. which works fine, else we'd get errors for pg / mysql 5 too.

so the problem lies somewhere else. maybe you can do a strlen() before and after the truncation in GalleryStorage.class function _normalizeValue(). note that you have to remove traceStart / traceStop in the query to see output generated in normalizeValue.

hmm, maybe better copy the truncate code from GalleryStorge and GalleryUtilities.class to your test script.

@XML changes:
why not try it for now with varchar(3200) for all TEXT columns, ok? i'll gladly change the xml if it is required.

@project management etc:
i agree that we changed a little too much too close before the release this time. we did a much better job at stabilizing before the first release (2.0). but you should not underestimate a) the level of test coverage we get from our unit tests (we require all tests to pass or at least not introduce further failures when committing new code, tested on multiple platforms), and b) the feedback we get during development from our users.

yeah, maybe there's another output cache or so.
what about copying the normalize / truncate functions to your test script where you have everything under control?

OK, I have a script that does not use any database (basically just calls the truncate code from G2). So you can run it too to see if you get different output than I do.

http://cpe0013102da23b-cm0f0079804905.cpe.net.cable.rogers.com/misc/Gallery2/test_utf8Substr.php.txt

I don't really understand some of the truncation though, so I don't know what to expect at any given point in the process.

For example, basically I'm dumping the value of $string after every step of the truncation. I can see how it is changed after calling utf8ToUnicodeEntities(), but after calling the entitySubstr() function, $string is still the same. Does that mean this is where the problem is?