Microsoft SQL Support

valiant worked on this issue with adodb/postgres. maybe email -devel to get his input on this.. it's possible a fix in adodb will be needed.

> ModuleTest.testModuleCycle and ModuleTest.testPermissionsCleanup

These are new errors, and are happening because of the lack of BITOR support in MSSQL. The error is ERROR_UNSUPPORTED_FUNCTION from the BITOR case in getFunctionSql(). I haven't checked mindless' change yet, but why are we going into this code? How does G2 determine whether to do the BITOR in the database or PHP?

Update: I found the problem. Apparently these two tests call getFunctionSql() and have their own copy of _fetchPermissionBitsForItem(). I modified it as follows:

    function _fetchPermissionBitsForItem($itemId) {
	global $gallery;

	$userId = $gallery->getActiveUserId();
	list ($ret, $groupIds) = GalleryCoreApi::fetchGroupsForUser($userId);
	if ($ret) {
	    return array($ret, null);
	}

	$groupIds = array_keys($groupIds);
	$userOrGroupIdMarkers = GalleryUtilities::makeMarkers(count($groupIds) + 1);

	$storage =& $gallery->getStorage();
	list ($ret, $bitOr) = $storage->getFunctionSql(
				'BIT_OR', array('[GalleryAccessMap::permission]',
						'[GalleryAccessSubscriberMap::itemId]'));
	if ($ret) {
//<LJM>
	    /* Try to OR permissions using aggregate function in DB; fallback to OR in PHP */
	    if ($ret->getErrorCode() & ERROR_UNSUPPORTED_OPERATION) {
		$bitOr = '[GalleryAccessMap::permission]';
		$groupBy = '';
	    } else {
//</LJM>
	    return array($ret, null);
	}
//<LJM>
}
//</LJM>

	$query = '
	SELECT
	  ' . $bitOr . '
	FROM
	  [GalleryAccessMap], [GalleryAccessSubscriberMap]
	WHERE
	  [GalleryAccessSubscriberMap::itemId] = ?
	  AND
	  [GalleryAccessSubscriberMap::accessListId] = [GalleryAccessMap::accessListId]
	  AND
	  [GalleryAccessMap::userOrGroupId] IN (' . $userOrGroupIdMarkers . ')
	';

and both these tests now run clean. Mindless, if you could please check this in too?

> RatingHelperTest.testFetchRatingsForGuestWithoutPersistentSession

We had this same problem in DB2, and it was because DB2's AVG() function was returning an INTEGER datatype when the subject column was of type INTEGER. We fixed it by casting to type double. According to the MSSQL doc, MSSQL's AVG() function behaves similarly, but that same change doesn't seem to be valid on MSSQL (SQL syntax error). Then I tried using CAST(x AS y):

$sql = sprintf('cast(AVG(%s) as decimal(4,3))', $args[0]);

This seems to be syntactically OK, but it still hasn't fixed the original problem... it's applying the CAST to the output of the AVG() call, which already has lost the decimal remainder. I can't see how to apply the CAST() to the base column before the AVG() is applied.

Correction... I've found the solution:

$sql = sprintf('AVG(CAST(%s AS FLOAT))', $args[0]);

Unit test RatingHelperTest.testFetchRatingsForGuestWithoutPersistentSession now runs clean. Mindless, could you please checkin this fix?

> StorageTest.testSqlInjectionWithNullCharacter

Still no responses to this in the MSSQL forum. I've now also sent it to a couple of PHP mailing lists.

fixes above committed.. sorry about the BIT_OR one.. I scanned all G2 code for uses of BIT_OR but didn't look at unit tests.

Multiroot module is a setup assistanat and cannot be activated.

would be nice to figure that last one out, but no i don't think it will hold up support for mssql.

test matrix is for current svn. i'm just putting "2005 or newer" for mssql support. thanks!

We don't have any tool / script to help you in this transition.
Also, note that MS SQL support in G2 does not support international characters yet.

It's much too early to consider switching from MySQL to MS SQL when it comes to G2.