Postgres compatibility

Also the insert statement in nodequeue_arrange_subqueue_form_submit needs to be broken up into individual insert statements. I"m using postgresql 7.4.x.

I missed a couple of the other issues you dug up and did the field list slightly differently. I did do basic testing of these changes on both mysql and postgres.

Cheers

Cal

PS I've included an ed style diff and the full file can be found at http://placeofdreams.org/nodequeue.module

@cwoodruf, or another Postgres user, can you confirm that the revised patch in #2 is RTBC?

As a postgres user I confirm that these changes work.
Rerolled patch from #2 against CVS HEAD. This reroll also applies cleanly to nodequeue-6.x-2.0-rc3 (offset of -1).

The changes to nodequeue.actions.inc are already in HEAD, as well as hunk 4 of nodequeue.module in the patch from #2, so these are not included in the reroll.

Changing priority to critical as the module is unusable for postgres users without this patch.

subscribing

Does this patch make #269459: PgSQL unnecessary?

It would be great if someone with postgres familiarity could comment on the relation between this patch and the following additional postgres issues:
#286918: Schema mismatch errors reported by schema.module
#269541: PgSQL for insert statements.

Should these other issues be marked as dupes? Does this patch need work? Leaving as CNR.

It looks like all three are related. There seems to be two separate issues, but some mix e two in the same issue. The first is just the syntax of SQL statements, which I think my patch addresses completely (at least in the main module).

The second issue looks like the nodequeue_subqueue table is missing from D5 for pgsql. The code supplied in #269459: PgSQL looks like it address (only) this issue.

Between a proper patch for #269459, and a reroll or rework of this patch for D5, all of these issues can probably be resolved.

It looks like this patch changes the insert for saving new values into the nodequeue_nodes table from a single insert per-queue to one insert per-node. On a site with a lot of queues, that could add up to many additional queries. Is there any alternative to that?

Seems like the INSERT INTO {nodequeue_nodes}... would be better done via drupal_write_record()...

the rest of the query changes look correct to me.

The above patch worked for me however the node names were not getting displayed as a query was failing

* warning: pg_query() [function.pg-query]: Query failed: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list in database.pgsql.inc on line 139.
* user warning: query: SELECT DISTINCT(n.nid) FROM node n LEFT JOIN nodequeue_nodes nq ON nq.nid = n.nid WHERE nq.sqid = 2 ORDER BY nq.position ASC in nodequeue.module on line 1046.

Changing line 1046 in nodequeue.module fixed this issue:
$query_restricted = db_query(db_rewrite_sql("SELECT n.nid FROM {node} n LEFT JOIN {nodequeue_nodes} nq ON nq.nid = n.nid WHERE nq.sqid = %d$node_status_sql GROUP BY n.nid, nq.position ORDER BY nq.position $order"), $sqid);

Thanks for pointing this out. Could you submit this change as a patch?

The patch in this thread breaks latest version of nodequeue with postgres. Here is a patch that should work with postgres and nodequeue 2.3

Just rolled my own patch using subqueries. Seems fairly similar to the others here otherwise. I am pretty sure this should work in MySQL 4.1 and up.

Does anyone know why PostgreSQL support has not been merged into nodequeue proper? This thread seems to have been going for ages.

This no longer applies.

Marked http://drupal.org/node/269459 as a duplicate.

I struck an issue with nodequeue 2.3 and postgres 8.1 on line 1345 there is a db_query that attempts to do multiple inserts in a single statement. Postgres 8.1 fails on this (8.3 is ok).

Here is a patch that should fix this issue with pg8.1

@ezra-g what do you mean by this no longer applies? latest (v2.4 ) nodequeue did not fix the postgres issues in my testing...

No longer applies means a patch no longer applies to the HEAD version of a module.

Also, please roll all of the postgres fixes into a single patch. Thanks!

to be more clear, it means the patch cannot be applied, not that the issue is no longer relevant.

Hello.

I applied the modifications outlined in #14 and I generated the patch for the version 6.x-2.x-dev.
I hope it was helpful.

Greetings

Leandro

Hello.

I added some corrections to my previous patch. I replaced "| |" for "OR" for some queries.

Thanks for the patch. Unfortunately it patch is unreviewable because it was rolled without the cvs diff -up options. Please see http://drupal.org/patch/create.

Also, it doesn't seem to take into account any of the changes in the previous patches.

Hi,

subscibe

Please keep on this task the good work! If you need a person to test the patches, let me know. I really would like to get rid of mysql.

Cheers,
xmarket

Hi,

Here is the nodequeue.module postgresql compatible patch. I use PostgreSQL 8.4.2. Patch based on #21. Patch works good with Nodequeue and Generate nodequeue assignments module according to the postmaster.log and my tests. I didn't test Smartqueue module. Querys remained compatible with mysql too, tested with mysql 5.0.90.

I noticed, that you use the COUNT so much in the querys. It would be nice, if you could rewrite the querys to use LIMIT, instead of COUNT.
Here is a nice topic about COUNT vs LIMIT:
http://drupal.org/node/196862#comment-649928
http://drupal.org/node/196862#comment-1595084

Cheers,
xmarket

Patch in #24 is wrong! Please ignore it!

Here is the FIXED patch! Sorry...

Please do not neglect this topic!

Here is a patch against latest dev. Still works! Please test it!

Hi,

I stumbled upon a few problems with nodequeue-6.x-2.9 while trying to get it running for a site powered by postgresql in the backend and I think I fixed them so far, so I attach a patch (made with svn but should work with any version of patch) addressing all the issues that I found. Maybe these changes could be incorporated into the next release ...

More detailed Explanations:

* use curly braces in function 'nodequeue_generate_rehash' (nodequeue/nodequeue_generate.module) so table prefixes are honored as well

* alter naming for creating indexes

CODE EXAMPLE:
'indexes' => array('{nodequeue_subqueue}_qid_idx' => array('qid'));

EXPECTED INDEX NAME:
'staging_nodequeue_subqueue_qid_idx'

CREATED INDEX NAME:
'staging_nodequeue_subqueue_staging_nodequeue_subqueue_qid_idx_idx' (which in turn may become problematic if you run into the postgresql-specific issue of max 63 allowed characters for keywords and identifiers ... @see http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html)

* create primary key for 'nodequeue_types' (nodequeue/nodequeue.install)

* regarding line 150/151 in patch file, postgresql will throw the following error:
"ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list"

* postgresql is also very picky about "group by" clauses omitting all selected columns:

QUERY:
SELECT s.*, COUNT(n.position) AS count FROM staging_nodequeue_subqueue s LEFT JOIN staging_nodequeue_nodes n ON n.sqid = s.sqid WHERE s.sqid IN (1,2,3,5,6) GROUP BY s.sqid

ERROR:
column "s.qid" must appear in the GROUP BY clause or be used in an aggregate function

* regarding : @@ -1867,10 +1867,12 @@ in patch file:
Maybe I misinterpret the intended logic here, but I made the OR conditional more explicit

cheers,
matthias

#27 fixed it for me, tnx

mmh, seems like the automated tests expect patch indexes to be relative to a modules root directory, so i changed to filepath related sections of the patch file (applying reverse patch worked locally)

#30 worked for me,

Im upgrading from D5, i tried 6.x-2.9 had errors, then tried 6.x-2.x-dev still had errors, removed the files put back 6.x-2.9 update.php ran from Version 1. Errored again

so i patched the files, & ran update again, still had the error relating to columns not being in group by, the column was q.name.

Was weird not sure why this was there as it does not seem to be referenced anywhere in the code, so i deleted that column from the table. and all is well i can see me data again :)

Thanks heaps guys!!

Go testbot :)

It seems that some hunks from nodequeue.install were fixed in #286918: Schema mismatch errors reported by schema.module. Can you roll a patch with all the other changes?

Posptoning until someone with postgres experience can re-roll the patch from #30.

Moving to 7.x-2.x.

Issues I see as of 6.x-2.11:

• IFNULL() is non-standard.. use COALESCE() instead.
• ORDER BY columns must be selected too (nq.position).
• All columns not used in an aggregate function must appear in the GROUP BY clause (this hasn't changed since my patch in #4).
• Alternatively if we want to retain 'q.*' and 's.*' we could do a count in a subselect instead of using GROUP BY (nodequeue-290969-37b.patch).
• The indices are not fixed. PG doesn't support the non-standard SHOW INDEX FROM. The schema module has code in engines/schema_pgsql.inc (look for This query is derived from phpPgAdmin's getIndexes() function). Proper indices were created by nodequeue_update_6006() so I just dropped the duplicates manually. These are what I dropped:
  

  DROP INDEX nodequeue_roles_nodequeue_roles_qid_idx_idx;
  DROP INDEX nodequeue_roles_nodequeue_roles_rid_idx_idx;
  DROP INDEX nodequeue_types_nodequeue_types_qid_idx_idx;
  DROP INDEX nodequeue_types_nodequeue_types_type_idx_idx;
  DROP INDEX nodequeue_subqueue_nodequeue_subqueue_qid_idx_idx;
  DROP INDEX nodequeue_subqueue_nodequeue_subqueue_reference_idx_idx;
  DROP INDEX nodequeue_subqueue_nodequeue_subqueue_title_idx_idx;
  DROP INDEX nodequeue_nodes_nodequeue_nodes_qid_nid_idx_idx;
  DROP INDEX nodequeue_nodes_nodequeue_nodes_sqid_idx_idx;
  DROP INDEX nodequeue_nodes_nodequeue_subqueue_nid_idx_idx;
  

I'm just keeping a D6 site running..

Committed #37b to 6.x-2.x and 7.x-2.x. Finally! Thanks for all the work on this :)

http://drupalcode.org/project/nodequeue.git/commit/dd45955
http://drupalcode.org/project/nodequeue.git/commit/2d396db

Tagging