You have 2 bugs in version 2.2.

You are creating a table with a column named 'user' which does not work at all with PostgreSQL. Shouldn't it be 'uid' anyway?

Then you use a primary key entry with an array. That breaks. Probably because you used a comma instead of an arrow.

array('qid' => 255)

At this time, your version appears as Array in the SQL command.

That happens when running the update.php but I'm sure you'd get the same with a fresh install since the schema is the same.

Thank you.
Alexis

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

AlexisWilke’s picture

Also, you have a SELECT that says state='1', which won't work because state is an integer, not a string.

And as I was testing some more, it looks like the array('quid',255) will NOT work for primary keys. Instead, you'd need to use a unique key. Although, I did not test that, but it is used by another project that way (quotes) which as far as I know functions properly.

Thank you.
Alexis Wilke

AlexisWilke’s picture

Version: 6.x-2.2 » 6.x-3.0
Status: Active » Needs review
FileSize
3.89 KB

Posting a patch for 3.0 which has NOT been fixed!

Oops! I named the patch using version "1.x". It really is for version "3.0".

ij’s picture

The patch breaks brilliant_gallery for mysql. After applying the patch, everything is fine for sites with PostgreSQL as backend db, but sites with MySQL are showing this error:

user warning: Unknown column 'uid' in 'where clause' query: select qid,state from brilliant_gallery_checklist where nid=9999999 and qid not like 'user-%' and uid=0 union select qid,state from brilliant_gallery_checklist where nid=9999999 and qid like 'user-%' and uid=3 in /usr/local/drupal6/modules/brilliant_gallery/brilliant_gallery.module on line 110.

My ugly workaround would be to create a brilliant_gallery_pgsql module, but I hope the bug can be fixed in a much saner way... ;-)

AlexisWilke’s picture

ij,

Well! Either the patch failed to install properly or I missed something on line 110.

The "normal" name for the user identifier in Brilliant Gallery is 'user'.

Also, if you only install the patch, it won't work. You need to also update the corresponding column in the database. That could be done automatically with an update which I did not write because I don't even know whether the author cares about making his system work for PostgreSQL or not.

So far, he did not even make it work properly for D6 even though I posted a solution months ago! (before he published the latest version)

So... here we are.

Alexis Wilke

ij’s picture

No, the patch applied successfully, but it breaks existing MySQL installation using the brilliant_gallery module. At least on my system. YMMV. Upgrading or even re-installing other, working MySQL installations/galleries is not an option, of course. Well, yes, I could alter the tables of course for MySQL as well, but I'd rather not like to do that at this point.

The use of "user" in brilliant_gallery is causing problems because "user" is a reserved word in PostgreSQL. Instead I'm often using "usr" in my own PG databases.

Anyway, I agree with you that the module maintainer should act and find a solution for both (mysql and pgsql) with an update to the appropriate tables to make both database backends happy - and the users, too! Maybe he's just busy with real life issues? I'll give him a notice via his contact form on his website... we'll see... ;-)

Ingo

ij’s picture

No, the patch applied successfully, but it breaks existing MySQL installation using the brilliant_gallery module. At least on my system. YMMV. Upgrading or even re-installing other, working MySQL installations/galleries is not an option, of course. Well, yes, I could alter the tables of course for MySQL as well, but I'd rather not like to do that at this point.

The use of "user" in brilliant_gallery is causing problems because "user" is a reserved word in PostgreSQL. Instead I'm often using "usr" in my own PG databases.

Anyway, I agree with you that the module maintainer should act and find a solution for both (mysql and pgsql) with an update to the appropriate tables to make both database backends happy - and the users, too! Maybe he's just busy with real life issues? I'll give him a notice via his contact form on his website... we'll see... ;-)

Ingo

AlexisWilke’s picture

ij,

Since I did not put the necessary code to alter the column, it won't work unless you do the ALTER TABLE.

Thank you.
Alexis

Pieter Janssens’s picture

"user" is not only a reserved word in PostgreSQL, but also in all versions of the SQL standard and therefore _must not_ be used. see http://drupal.org/node/371 and http://drupal.org/node/330983

freggy’s picture

This problem still exists in the module for Drupal 7:

PDOException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "user" LINE 3: user int NOT NULL default 0, ^: CREATE TABLE {brilliant_gallery_checklist} ( nid int NOT NULL default 0, user int NOT NULL default 0, qid text NOT NULL, state int NOT NULL default 0, PRIMARY KEY (nid, user, Array) ); Array ( ) in db_create_table() (line 2684 of /var/www/ai.vub.ac.be/drupal/includes/database/database.inc).

_walko_’s picture

Hi,

same thing for me, postgres doesn't work even in D7 version... And the 6.x patch can't fit with the new version of the Gallery...

Ben Coleman’s picture

Ran into this when trying to install Brilliant Gallery in a Drupal 6 site running PostgreSQL. Yeah, the field 'user' needs to be changed, preferably to the standard 'uid'. And the "array('qid' => 255)" bit caters to a MySQLism where you have to give it a length when using a text field as an index key. From Drupal 6 through 8 (!), this syntax is not handled by the Drupal PostgreSQL drivers. There's a patch, at least, for Drupal 8 (See #1148856: Postgres schema doesn't support keylength on a unique index), and I expect it to make it at least into D8 at some time, as the core Comment module in Drupal 8 is using the same syntax (which means that the current version of Drupal 8 won't install on PostgreSQL, alas). I haven't yet looked at porting that patch to D7, let alone D6. And getting that patch into core would be required before BG would work on PostgreSQL.

abrahamdsl’s picture

Issue summary: View changes

Ugh, this is still not fixed in Drupal 7 version, at this time. :/ Using PostgreSQL.

nathanweeks’s picture

Attached is a patch that seems to allow brilliant gallery 7.x-1.x to work with PostgreSQL. I haven't tested it on MySQ; it would at least need an update hook to rename the relevant columns in the MySQL tables of an existing installation.

Ben Coleman’s picture

I can verify that #13 installs without errors on PostgreSQL 9.3.

abrahamdsl’s picture

Status: Needs review » Reviewed & tested by the community

I can confirm the fix to enable use in PostgreSQL works. However, at least for Picasa users there should be at least three entries, i.e.

[bg|<address to Picasa>]

won't work.

[bg|<address to Picasa>|sort]

works.

Maybe it has something to do with how it is parsed and is another issue to be filed.

Ben Coleman’s picture

Version: 6.x-3.0 » 7.x-1.x-dev

Changing the version, as the RTBC is for a 7.x-1.x patch. Just installed BG 7.x-1.8 on a Drupal 7 PostgreSQL, and it did install (rather than throw over 10 Postgres errors as it does without the patch).

nathanweeks’s picture

I've updated the patch so it applies cleanly against 7.x-1.11.

Is there a chance that this could be reviewed and some version (e.g., with an update hook that renames the "user" column to "uid" for existing installations, or perhaps just additional logic that retains the "user" column for MySQL, and uses "uid" for PostgreSQL) incorporated into the upstream?