fix acl db scheme (primary keys, utf8)

yep, you are right.

I had a look at the db scheme. The mysql scheme doesn't even include primary keys. Furthermore the db scheme doesn't use utf8 for mysql > 4.1.
So I've gone over this issues and created a patch. It also adds an uninstallation routine.

I've tested the mysql upgrade path, but not the postgre sql, because I have no postgre sql test environment. It would be nice if someone could test it.

merlinofchaos, are the scheme changes ok for you?

Hi fago

Thanks for looking into this. Merlinofchaos has just turned ACL and Forum Access over to me.

About the patch:

• I don't think you're allowed to specify a name for the primary key.
• Wouldn't acl_node_grants() benefit from retaining the uid key on the acl_user table?
  The CREATE INDEX seems to have been redundant.

Anyone care to test the PostgreSQL?

P.S. I'd be happy to keep you aboard.

ah well, so hopefully this brings new life to the project.. :)

@name: yep, copy/paste mistake. My mysql hasn't complained, but anyway let's remove them.

@INDEX: as to my knowledge, for a primary key is anyway an index maintained, so there is no need to specify a further one.

acl_user now has an index (the primary key) on (acl_id, uid) — this helps for queries about acl_id alone as well as about acl_id and uid. It does not help for queries about plain uid. That's what the index on uid does.

Feel free to commit #2 if you agree.

indeed, thanks for clarifying this :)

from the mysql docs:
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

however, I think the patch needs some more love now.
* the upgrade path still removes the old indexes
* postgre sql is missing the indexes

Yes, the upgrade path removed one index too many. The others should be ok, but I've put the removal ahead of the recreation to make sure we only do what we want to do.

As for pgsql, if no one cares, we'll commit anyway — I did massage it a little though, but I don't know whether it works...

fine. I've only replaced the index name to be {acl_user}_uid_index, so that the acl_user table prefix is correctly applied.

Otherwise, the code looks good so I've committed it to 5.x-dev. PostgreSQL could still need a tester..

testing on postgres - what's the current state? the patch won't apply cleanly to the end of the DRUPAL-5 branch. Is it already in there?

Yes, the patch is already committed to acl 5.x-1.x-dev, but I'm not sure whether the pgsql part really works, so I'd really appreciate your feedback...

It all installs the schema cleanly - looks correct.
will test with forum access next.

Great — thank you for checking/testing!