Microsoft SQL Support

great! best of luck on this project, we'll gladly provide guidance.

this isn't java; *.class files in G2 are source files, just open it up

another thing you'll need to edit is lib/tools/bin/generate-sql.php (I don't think there are xsl files anymore.. are you looking at current CVS?)

Hello

Thanks for the responses. I checked out the postings for the db2 port and they have helped.

I’ve got eclipse running with the php plugin and got all the code from CVS.

I’ve added the necessary code to generate-sql.php for mssql. I can compile it and can see the generated sql in schema.tpl

How does gallery 2 use this schema.tpl when running the sql against the db? How does it resolve dependencies in the file, I see that the sql is not ordered by dependency? E.g. an alter table exists for a table before the create table sql.

I’ve created a MsSqlStorage class and have modified it. I just need to understand what the following functions are used for
encodeBlob
decodeBlob
_getOptimizeStatement
convertIntToBits
convertBitsToInt

If I know what they are used for, I can check if they are necessary for MS SQL.

Once I’ve done those functions, I can start testing.

in the db2 port notes there is a step as follows:

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;

I cannot seem to find this, has this changed since the db2 port?

Vince

Ah, I didn't realize this too had been moved after I implemented the DB2 change.

Hi Vince.

The only thing you need to 'build' is the 'gallery2/modules/core/classes/GalleryStorage/schema.tpl' file, which will contain the generated SQL based on your other changes.

To build this file, go to 'gallery2/modules/core/classes/GalleryStorage' and run 'gmake'.

lmenard@larryhome /cygdrive/c/temp/gallery2/modules/core/classes/GalleryStorage
$ gmake 2>&1 | tee gmake.out
perl ../../../../lib/tools/bin/extractClassXml.pl --dtd=../../../../lib/tools/dt
d/GalleryClass2.0.dtd --quiet --out-dir=tmp/classxml ../*.class
php -f ../../../../lib/tools/bin/generate-dbxml.php
php -f ../../../../lib/tools/bin/generate-sql.php

After this, you should have a new 'schema.tpl':

lmenard@larryhome /cygdrive/c/temp/gallery2/modules/core/classes/GalleryStorage
$ ls -l schema.tpl
-rw-r--r--  1 lmenard None 115269 Mar 22 09:24 schema.tpl

And it should contain a section for your MS SQL. (Look for '##', that's how the db platform sections are delimited.)

Then, just move your changed files into the 'gallery2' tree on your server, and fire up the installer by pointing your browser to 'http://localhost/gallery2/install/index.php' and see how it goes.

Edit: I hope you have a Cygwin environment on your Windows system. If not, you'll need it for doing this build.

this is GREAT. ive been waiting for sql support. I'd be more than happy to help test it. (hopefully we can move the gallery data from mysql to mssql without any problems)

I'm not 100% sure, but I think that's normal. The Database Setup Step only creates a couple of test tables, just as a 'sanity check'. The majority of the tables and other objects are created later during the Core Module Install step.

Yeah, now you're getting to the fun part.

After the 'gallery2/config.php' file is created in Step 7, edit that file and enable debugging by changing line:

$gallery->setDebug(false);

to

$gallery->setDebug('buffered');

(Note that this file is recreated everytime you run Step 7, so you might have to re-enable debugging whenever you run Step 7.)

When debugging is enabled, look in your g2data directory for a file called 'install_*'. In that file, look for a few lines of version information being dumped. Ignore everything up to that point... the error you're looking for is somewhere below that.

Happy hunting.

The last line in the trace very clearly says it was not succesful.

I see one error in your trace...

b>Warning</b>:  mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: message: Cannot insert the value NULL into column 'g_bits', table 'gallery3.dbo.g2_PermissionSetMap'; column does not allow nulls. INSERT fails. (severity 16) in <b>C:\Inetpub\wwwroot\gallerymssql\lib\adodb\drivers\adodb-mssql.inc.php</b> on line <b>690</b><br />
<br />
<b>Warning</b>:  mssql_query() [<a href='function.mssql-query'>function.mssql-query</a>]: Query failed in <b>C:\Inetpub\wwwroot\gallerymssql\lib\adodb\drivers\adodb-mssql.inc.php</b> on line <b>690</b><br />
515: The statement has been terminated.<br>

It says "Warning", but I don't trust that.

ok ... I figured there is "other" file needed to be deleted. I can now install all modules
succesfully but when I go to the main page I get the following...

Configuration Error: Missing Theme
Missing Theme
This album is configured to use the theme, but it is either inactive, not installed, or incompatible. To fix this problem you can login as a site administrator and then install or activate this theme or select another default theme

If I try logon as admin I get the following
Database Error
An error has occurred while interacting with the database.

Back to the Gallery

There is no debug output...

Any ideas?

> existing database

In Installer Step 5 (Database Setup), if Gallery detects that there is an existing set of tables, there should be a button that gives you the option to remove and recreate those database objects. I forget the actual wording of this button, but the code that it fires is in the "GalleryStorage/mssqlDatabase.class" file you created, in a function called "cleanStore()". To be honest, that's the one thing i haven't tested really well yet in DB2.

It's very important that the database remains in sync with the contents of the g2data directory. I created a "recreate_db.bat" file that drops and recreates the database, and removes and recreates an empty "g2data". Before starting any Install, I run that script.

> Missing theme

Not sure how much I can help with this specific problem, but here's some general guidance based on my own experience:

1) Review the "install*.log" file to make sure there are no errors that were missed. If debug was not enabled, wipe the db and "g2data", re-run the Install with debug enabled.

2) You can enable debugging at any time, not just during the Install. For example, if you're having difficulty bringing up the main "gallery2/index.php" page, enable debugging and try it. You should see a lot of debug info. Note that the debug info is visible in your browser, it is not sent to a file. Again, there should be no errors in this debug output.

Note: When you enable debugging for runtime (non-Install) activity, it opens a separate browser running something called Smarty. I find it to be more annoying than useful. If you close that window, it just keeps coming back every time you refresh. So rather than close it, I minimize it, so at least it stays out of my way.

3) Look at the stack trace, and don't be afraid to put your own debug code into the source at those locations. For example, here's one I'm currently using:

printf("<br><br>In GalleryStorageExtras::_executeSql(), \$buffer = \"$buffer\".\n<br>");

4) When looking at the source code, watch for methods like "$this->_gs->_traceStart();" and "$this->_gs->_traceStop();" enclosing some execution statement. In non-Install cases, they have the effect of suppressing errors. You might find it helpful to temporarily comment them out.

\gallery2\modules\comment\classes\GalleryStorage

has a tpl file, after I run gmake it has an entry for mssql

thanks for the other info, I will keep at it...

> tpl file

Hmm, I wonder if that's because you're using code that was pulled directly from cvs, as opposed to from a nightly build? In my case (DB2), Valiant was checking in my changes frequently, so by the time I got to the additional modules, the base DB2 support was already there. Once your base changes go into cvs, you shouldn't have to do builds anywhere unless you change your SQL source.

> GROUP BY

See function getFunctionSql() in your "GalleryStorage/mssqlStorage.class". It is basically yours to do with as you please, though the lead developers are somewhat picky about what they allow for performance reasons. I think I had this same problem in DB2, so check out the getFunctionSql() in "GalleryStorage/Db2Storage.class".

You should also be able to modify the "MsSqlGenerator" block of code in "generate-sql.php" without impacting anyone else.

There is also SQL in "moodules/core/classes/helpers/*.class", but those are shared files, so changing those is not done easily.

where can I get more info on the structure of the arrays that are passed into
getfunctionsql() and generate-sql.php? None of the existing code generates the GROUP BY part of the
sql. I would imagine that the sql mentioned above is generated on the fly by gallery or is this
sql pre-generated and if so where is it?

Never mind the stack trace, using 'grep' commands I was able to find the code that I think is stopping you. It's in "GalleryPermissionHelper_Medium.class", function "fetchPermissionsForItems()".

The GROUP BY clause *is* there, so I don't understand what you mean by "None of the existing code generates the GROUP BY part of the sql". Here's the code:

	$query = '
	SELECT
	    [GalleryAccessSubscriberMap::itemId],
	    ' . $bitOr . '
	FROM
	    [GalleryAccessMap], [GalleryAccessSubscriberMap]
	WHERE
	    [GalleryAccessSubscriberMap::itemId] IN (' . $itemIdMarkers . ')
	    AND
	    [GalleryAccessSubscriberMap::accessListId] = [GalleryAccessMap::accessListId]
	    AND
	    [GalleryAccessMap::userOrGroupId] IN (' . $entityIdMarkers . ')
	GROUP BY
	    [GalleryAccessSubscriberMap::itemId]
	';

I think it would be safe to just add the required column(s) to the existing GROUP BY.

	GROUP BY
	    [GalleryAccessSubscriberMap::itemId],[GalleryAccessMap::permission],[GalleryAccessSubscriberMap::itemId]

Try it and see how it goes.

Mindless (or any other lead developers), any concern with that change?

Ah, I'll bet Vicente copied the "Db2Storage.class" file. (In DB2, the 'G2_BIT_OR' aggregate function requires both columns to be passed into it. Chances are MS SQL's BIT_OR does not.)

Vicente, try copying a "case 'BIT_OR':" block from another database, e.g. PostGreSql.

I think you have at least two separate issues here... the BIT_OR and the GROUP BY.

BIT_OR:

The additional passing of G_ITEMID into the BIT_OR is probably very specific to DB2. For now, forget you ever saw that. I suspect you don't need it.

The purpose of this BIT_OR is not to compare two columns. It is supposed to take the set of returned G_PERMISSIONS values and do bitwise ORing of those values against each other, then return the final BIT_ORed value.

For example, if the result set that satisfies the WHERE clause is:

G_ITEMID G_PERMISSIONS
-------- --------------------------------
1        00001110101001011000110101100111
1        00000001111100000101011111100000
1        10100000001000000000000000010000
2        00000001111011100000000001000000
2        01110000101000000000000000000101
2        00000000000000000000010000000000

Then what the query is supposed to do is return the BITOR of all the permissions for G_ITEMID "1", then the BITOR of all the permissions for G_ITEMID "2", etc. So, it should return:

G_ITEMID 2
-------- --------------------------------
1        10101111111101011101111111110111 // <- BITOR of all G_PERMISSION values where G_ITEMID = "1"
2        01110001111011100000000001000101 // <- BITOR of all G_PERMISSION values where G_ITEMID = "2"

DB2 unfortunately does not have built-in BIT functions, so I had to write my own (using a sample from the IBM DeveloperWorks site), but also unfortunately, aggregation using DB2 User-Defined Functions (UDFs) like that do not honour the GROUP BY clause in the original SQL, so the UDF needs to also know the G_ITEMID value for each row in order to do its own grouping. Otherwise it would just BITOR the entire set of G_PERMISSIONS values regardless of the G_ITEMID values. In the case of the above example, that would be:

G_ITEMID 2
-------- --------------------------------
1        11111111111111111101111111110111 // <- BITOR of *all* G_PERMISSION values
2        11111111111111111101111111110111 // <- BITOR of *all* G_PERMISSION values

GROUP BY:

So we might still need to add those columns to the GROUP BY clause from what I can see. It's Mindless that you need to convince.

Sorry for the multiple postings, I keep noticing things I haven't addressed yet.

For the hang... do you really mean "hang", or do you mean "exit"?

I've noticed that when I have a syntax error in a class file, the error might never be evident in the browser or the log file. Sometimes it just exits with no error msg.

I should also have mentioned... in DB2 I also wrap a MAX() around the BIT_OR, so it doesn't actually print the intermediate results, only the max. My exact query is:

SELECT g2_AccessSubscriberMap.g_itemId,
 MAX(G2_BIT_OR (g2_AccessSubscriberMap.g_itemId, g2_AccessMap.g_permission))
 FROM g2_AccessMap, g2_AccessSubscriberMap
 WHERE g2_AccessSubscriberMap.g_itemId IN (7, 12)
 AND g2_AccessSubscriberMap.g_accessListId = g2_AccessMap.g_accessListId
 AND g2_AccessMap.g_userOrGroupId IN (5,4)
 GROUP BY g2_AccessSubscriberMap.g_itemId

It returns:

G_ITEMID    2
----------- -------------------------------------------------------------------
          7 x'3030303030303030303030303030303030303030303030303030303030313131'
         12 x'3030303030303030303030303030303030303030303030303130303030313131'

  2 record(s) selected.

(Don't get hung up on the hex representation... that's just DB2's way of saying it is FOR BIT DATA.)

When I remove the MAX(), I get the same error as Vicente.

SQL0119N  An expression starting with "G_PERMISSION" specified in a SELECT
clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY
clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a
column function and no GROUP BY clause is specified.  SQLSTATE=42803

Vicente, try wrapping a MAX() around your BITOR?

Sorry, I have no idea why you're getting the "Missing Theme" error. I can't even find string "Missing Theme" in my entire Gallery2 tree, nor can I find any occurrence of "Missing" that looks like it might be followed by a variable that could be "Theme".

In "Site Admin -> Themes -> All Themes", what themes are installed (indicated by green circle)? On my system, "Matrix" is the only one installed, and it is therefore the default theme. Do you actually have a complete "gallery2/themes/matrix" directory? Mine is 99 files (172,022 bytes), in 206 directories.

I don't know anything about the session suport either. What is the value of $pSession[5] at that point when it fails?

the error page is modules/core/templates/ShowItemError.tpl

C:\MyServer\Gallery2\themes\matrix>dir /s | tail -n4

     Total Files Listed:
              99 File(s)        172,022 bytes
             206 Dir(s)   2,283,507,712 bytes free

If your matrix directory is 306 files then something is obviously very different between your setup and mine.

I still suggest checking your changes into CVS. That way your code is sure to go through a complete build process, and also then our two environments would be more comparable.

I have attached the debug file...

Mindless may want to review your changes first. Wait for his advice.

FYI, I have just upgraded CVS to use ADODB 4.80 and included the mssql files.