[Catalyst] user maintenance

[Toby Corkindale]

Michael Higgins wrote:
> Hello, list --
> 
> I have a catalyst app about to get a bunch of new users. In
> anticipation of that, I'd like advice on how to maintain the
> user/acl tables I set up per the tutorial.
> 
> Basically, I just want to add the user and have a default role
> automagically appear in the corresponding acl table. When I delete the
> user, the user's role(s) go as well.
> 
> Which of the catalyst options would give me the quickest route to
> maintaining this list? And what is the best way to ... create
> related entries and cascade delete? (I'm still new at this.)

Are you using MySQL? If so, stop using it, and start using a relational
database that supports foreign keys and cascaded deletes - and example
of an opensource DB with those is PostgreSQL.

Then define your tables like:
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  username VARCHAR(32)
-- etc
);
CREATE TABLE roles (
  id   SERIAL PRIMARY KEY,
  role VARCHAR(32)
-- note: some people just use the role AS the primary key on its own
);
CREATE TABLE accounts_to_roles (
  account INTEGER NOT NULL REFERENCES accounts(id)
		ON DELETE CASCADE,
  role    INTEGER NOT NULL REFERENCES roles(id)
		ON DELETE CASCADE,
  PRIMARY KEY(account,role)
);

Then setup your DBIx::Class schema with the appropriate relationships -
ie. AccountsToRoles.pm has:
# the usual stuff, then:
__PACKAGE__->belongs_to(account => 'MyApp::Schema::Accounts');
__PACKAGE__->belongs_to(role => 'MyApp::Schema::Roles');

Accounts.pm has
# the usual stuff
__PACKAGE__->has_many(
    account_roles => 'MyApp::Schema::AccountsToRoles', 'account'
);
__PACKAGE__->many_to_many(
    roles => 'account_roles', 'role'
);


After all that, you should be able to use
Catalyst::Plugin::Authentication and C:P:Authorization::Roles with it
all, and you'll find that when you delete accounts, or roles, the
corresponding entry in the linking table deletes as well.

MySQL 4.x and 5.0 will accept the above SQL syntax, but will silently
ignore it, and not actually perform any referential integrity checking,
nor cascading. ie. It all looks like it's working fine, until it breaks
horribly when you get into detailed testing. You have been warned.
(I believe there is a MySQL-specific workaround for this, but like the
horribleness with timestamps, it seems more trouble than just using a DB
that just works, as there are plenty of commercial, and at least one
open-source DBs that support the standard way)

Cheers,
Toby