It would work perfectly well without Cat, with, as a command line tool, in CGI::Application, as the data source of a standalone CGI service. Anything. This is the best practice way of doing schemas in Cat precisely because the schema should not be tied to the web application. It's entirely reusable this way.

I don't have time right now to cook up a full tutorial so this isn't complete (a better example would have working code to do it all in SQLite or something) but it's pretty close. I know DBIC can be hard to hash out but the info is out there and the list is friendly as long as you don't ask about "DBIx" when MST's around. :)

1. You need a user table, a role table, and user_role join table to work with this particular approach; there are other ways to do it but this is flexible and what I'd call best practice (compared to a role column in the user table for example which is easy to implement but has huge limitations you'll hit quickly and require spaghetti to solve).
2. The minimum necessary table layout is approximately-

   CREATE TABLE `user` (
     `id` int unsigned NOT NULL auto_increment,
     `username` VARCHAR(100) NOT NULL,
     PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   CREATE TABLE `role` (
     `id` int unsigned NOT NULL auto_increment,
     `name` VARCHAR(25) NOT NULL UNIQUE,
     PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   CREATE TABLE `user_role` (
     `user` int unsigned NOT NULL,
     `role` int unsigned NOT NULL,
     PRIMARY KEY  (`user`,`role`),
     FOREIGN KEY (`user`) REFERENCES user(id),
     FOREIGN KEY (`role`) REFERENCES role(id)
   ) ENGINE=InnoDB;
   [download]

3. Create your DBIC schema. There is a good stub here: Re: Switch from DBI to DBIx::Class: thoughts?
4. In your DBIC schema files, if you followed the above example, you will find these classes/pms were created-

   MyApp::Schema::Result::User
   MyApp::Schema::Result::Role
   MyApp::Schema::Result::UserRole
   [download]

5. Add to MyApp/Schema/Result/User.pm beneath the "DO NOT MODIFY this" line. If you do so, you can regenerate your schema files automatically while preserving any code you've added-

   # Created by DBIx::Class::Schema::Loader v0.04999_06 @ 2009-02-28 19:1
   +4:54
   # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:iS0oJWk28gjL7QD50cdRqQ
   
   no warnings "uninitialized";
   use Scalar::Util "blessed";
   use List::Util "first";
   
   __PACKAGE__->many_to_many( roles => "user_roles", "role" );
   
   sub has_role : method {
       my $self = shift;
       my $role = shift || return;
   
       if ( blessed $role )
       {
           return first { $role->id == $_->id } $self->roles;
       }
       elsif ( $role =~ /\A\d+\z/ )
       {
           return first { $role == $_->id } $self->roles;
       }
       else
       {
           return first { $role eq $_->name } $self->roles;
       }
       return;
   }
   [download]

6. (Optional, I think, but makes some things much more DWIM) Add to MyApp/Schema/Result/Role.pm beneath the "DO NOT MODIFY this" line-

   use overload '""' => sub {
       return +shift->name;
   }, fallback => 1;
   [download]

7. Now, you connect the schema like you would anywhere. Catalyst uses its Model space to abstract this a bit. Since we're not in Cat, we'll do it manually-

   my $attr = { RaiseError => 1,
                AutoCommit => 1,
                ChopBlanks => 1,
               };
   
   my $schema = MyApp::Schema->connect
       (
        "dbi:mysql:db_name",
        $user,
        $pass,
        $attr
       );
   
   # Just grab one-
   my $user = $schema->resultset("User")
           ->search({},{ order_by => 'RAND()' })->first;
   
   print
      "User ", $user->username,
       " has these roles: ",
        join(", ", $user->roles) || "none!", "\n";
   [download]

And you're good to go. Wherever. :)

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.