PostgreSQL Compatability: updated patch

Attached is the sql script to create the tables. Not perfect, tho. The sequences created for the primary keys are not named correctly. You'll have to experience the errors and change the sequence names appropriately. I used pgadmin to change the names, so no SQL for you.

The functionality of this module exactly matches my needs, so I was very pleased to find it. I've taken the PG version ken-ichi did and fixed the problems he refers to (that claim is of course suspect until thorough testing is completed). I'm using it with CVS version of Drupal with no apparent problems so far. Please find it attached.

And yes - I do know what day it is, but my wife's asleep on the floor and I'm stuffed full of Xmas lunch and bored out of my skull, so I'm working. OK?!!

This thread seemed to be the best place for me to try and get some answers.
I'm new to Drupal, but not new to Unix or programming (25+ years).
What I want to do is as follows:

I want members to be able to set access privs on their content based on the roles
of the users. For instance Unauthenticated users would be able to read blogs that
some users would like them to see but not able to read forums period. The basic idea is that some content is open to members only and some content is only open to particular roles (or particular users in particular roles). It seems that the Organic Groups Module is what I need. Now...

Is it best for me to attempt this with Drupal-current (from the CVS tree?
Reading the install instructions, installing the module is obvious to me but not installing
the og.mysql. I am running mySQL (sorry I know this is a Postgres thread), do I merge this code into database/database.mysql?

Any other tips would help or If you think there is a better module for my needs let me know.

the most recent patch is empty. if a pgsql schema and patch are supplied, I will accept them. Please do some testing before marking this as 'patch'. I don't plan to do any pgsql testing.

Oops - sorry - should have included schema for postgres version. Here it is:

CREATE TABLE og (
nid int NOT NULL default '0',
selective int NOT NULL default '0',
PRIMARY KEY (nid)
)

thanks. would be especially nice if someone could condense the changes into a patch file. i intend to support postgres so this issue is getting us close but not quite there yet.

thanks ken. sorry took me so long to review this:

is this needed? seems like the old way was just as valid:

$nodes .= node_view(node_load($onenode), TRUE, FALSE);
+ $conditions = array('nid' => $onenode['nid']);
+ $nodes .= node_view(node_load($conditions), FALSE, FALSE);

also, id like for the new setting about 'age' to be either removed or to be placed in the new section for block specific settings which is availablew in HEAD

I tried my best to implement the (query) changes submitted in on this page to the 4.6.0 version of the og.module file. Can anyone make a patch and test/review the code?

And the database tables for psql...

I would appreciate if people could test and refine these patches if needed.

works great for me so far

sometime this week i hope to have a postgres test machine up and running. once i do, i'll try to update these patches, test on postgres + mysql, and report my findings. just posting here so i don't forget. ;)

I hope there will be also a patch for OG 4.7 like this!!

definitely. if we're going to support it, it'll be for 4.7 and HEAD, too.

I've coded a working og module for postgresql, being careful to keep the mysql code intact. This includes a working og.install for setting it up. I've configured it but haven't started making groups for it yet. You can get it from here.

http://download.farbot.com/index.php?path=php%2FDrupal/

I haven't tested mysql with it since my modifications, and if I find problems with it going forward I will post the updates at that location.

Here's a patch to the og directory that patches the current release on the main page to get postgresql support working.

I've fixed a couple of bugs in the postgresql patch, and recreated it from the module's .tar.gz file.

looks like you were working with an older version of og when you generated this. it reverts some wanted improvements. please reroll against latest HEAD and assure that only the minimum required are shown as changed.

I've recreated the patch with the latest CVS code from today. This includes new logic and SQL to add the two new columns to a postgresql database.

i'll take a look at this and test on both pgsql and mysql, but it won't happen for a few days. we're about to do a major stable release at my day job.

setting the version to "cvs" to reflect what this is targetted for. however, moshe, can we please backport this to 4.7? i'd be willing to work on that if necessary...

sure. i'm planning to keep 4.7 and HEAD identical until feature freeze ... thanks for testing this, derek.

Here's an updated patch to go against yesterday's cvs version of og.

Thanks much

1. there is 1 REPLACE INTO statement in _og_update_db(). This needs changing, right?

2. delete if present seems a bit cumbersome since it will often generate 3 queries (select, delete, insert). a ebtter algorithm does either 2 queries (select => update or insert depending on presence of that record) or even 1 query (Update, and if that fails to affect anything do an insert). i recognize that these might be hard to implement in a function though. i think it is OK if we don't convert the old updates to pgsql compatible.

lets talk this through and then i will commit.

- the change to add the notification field to the {og} table is ported in og_update_11(), but not created in the initial install hook
- the patch adds a ton of ^M characters
maybe more, this is just the first i've found. i'll keep testing right now, and roll a new patch.

and when i try the same patched workspace on my MySQL test site, i get this:

Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 query: SELECT COUNT(*) FROM og og INNER JOIN node n ON og.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON n.uid = u.uid INNER JOIN og_uid ou ON ou.uid = u.uid WHERE og.nid IN () in /Users/wright/drupal/cvs/4.7/core-4.7.x/includes/database.mysql.inc on line 120

Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ORDER BY og.nid DESC LIMIT 0, 50' at line 1 query: SELECT og.nid, n.title, r.body, n.uid, u.name, og.description FROM og og INNER JOIN node n ON og.nid = n.nid INNER JOIN node_revisions r ON r.vid = n.vid INNER JOIN users u ON n.uid = u.uid INNER JOIN og_uid ou ON ou.uid = u.uid WHERE og.nid IN () ORDER BY og.nid DESC LIMIT 0, 50 in /Users/wright/drupal/cvs/4.7/core-4.7.x/includes/database.mysql.inc on line 120

not sure i'll be able to solve everything tonight... i can't really stay up much later tonight. i'll see what i can do in the next 1/2 hour or so. maybe i can get it most of the way there. ;)

ok, turns out #30 is just a bug in OG if you don't enable OG access control. i get the same stuff with or without the patch...

however, both of the cases for _og_update_db() are broken on pgsql...

1. AND ISNULL(na.gid) is wrong. it works as AND na.gid IS NULL on both mysql and pgsql
2. REPLACE INFO is indeed unavailable in pgsql.

furthermore, the "Group authors" block is broken on pgsql. mysql is forgiving, and allows you to SELECT a bunch of stuff you don't include in a GROUP BY clause, and just throws it out for you. pgsql, considers this an error. this is kind of tricky, since we're currently using the og_get_home_nodes_sql() method to SELECT a ton of fields, but we then ignore everything excetp the fields from {users}. to make this work right in pgsql, we'd probably need to add a 3rd optional argument to og_get_home_nodes_sql(), the columns to SELECT, and then in the case of og_block_subscribers() (kind of a poorly named function, IMHO, if it's displaying recent authors in the current group) where we're specifying a GROUP BY, we'd also specify SELECT. i'm not even sure this would work, to be honest, since pgsql also doesn't like it when you do ORDER BY on something that's not in the SELECT. :(

i almost think that since the needs of the (fairly unimportant) OG authors block are such a tiny subset of the complex query we need for the group home page itself, perhaps we should just resign ourselves to having a separate query directly in the block. yes, some of the query would be duplicated logic, and if we ever change it, we'd have to remember to change it in both places. but i think trying to make og_get_home_nodes_sql() generic enough to work for both cases might make things even more complicated and confusing than just having a separate query.

that said, the attached patch does the following:

1. thorough audit of og.install. fixed a bunch of bugs where the pgsql schema didn't match the latest mysql schema (even though some of the updates were ported)
2. took out bogus 'pgsql' cases from switch statements in the earlier og_update_N() functions that do not work on pgsql. better to just acknowledge that we're not supporting those earlier updates on pgsql, than to claim some of them work when they clearly don't.
3. removed all ^M chars from previous patch, and fixed some other whitespace/indentation problems.
4. fixed _og_update_db() as described above
5. SELECT includes 'n.sticky' in a place where we're trying to ORDER BY it (which pgsql requries)
6. (needs work): conditionally disables the 'Group authors' block on pgsql.

i can't promise i tested everything, but this is a lot closer. every change i made i tried on both pgsql and mysql (it's nice to have 2 sites running on my machine, pointing to the same set of scripts, but different DB backends), so i know this patch doesn't break mysql at all, at least. it still needs work (Group authors block), and there might be a few other rare cases that aren't right, but i'd almost be willing to just commit this as-is, and then work on these other, more minor issues down the road.

moshe, can you please take a look, comment on the whole GROUP BY mess, and set to 'needs work' or RTBC? thanks,
-derek

please add a condition around the 'view' op in og_block() also - just worried about any existing sites that switch to postgres. you may commit this to 4.7 and HEAD (currently identical - please keep it that way for now)

also, please watch this queue for any issues that arise from these changes.

i ran the simpletests under my patched workspace (on both pgsql and mysql) and only 1 test failed (but that failed on mysql w/o the patch, too). see http://drupal.org/node/75692 for more on this.

therefore, w/ moshe's slight modification to conditionally not try to *display* the OG authors block under pgsql, either, i committed to HEAD and 4.7.

webchick and i briefly discussed a backport to 4.6 on IRC, but we both agree it's not worth doing, especially since pgsql support in 4.6 core isn't all that great to begin with.