[Catalyst] Pulling My Hair Out with DBIx::Schema For Authentication and Authorization

[hkclark at gmail.com]

Based on Matt's suggestion to my email yesterday
(http://www.gossamer-threads.com/lists/catalyst/users/5951), I have
been hard at work trying to convert from "Simple" DBIx::Class to the
"Schema" (DBIx::Class::Schema) approach.  I must be missing something
because I can't get it to work.  I have searched through the archives
and found other people talking about aspects of the problem I'm trying
to solve, but I haven't found a close or complete enough example to
put all the pieces together.  If I can get this working, I would be
glad to post all of the code to the wiki as a self-contained example
for the benefit of others.

I have been starting with the example Carl Franks recently added to
the Cookbook (http://dev.catalyst.perl.org/docs/Catalyst/Manual/Cookbook.html#authentication_with_catalyst__plugin__authentication
-- note: on cpan yet, use this link), but:

a) I'm trying to do it with DBIC::Schema vs. DBIC::SchemaLoader
b) I'm also trying to incorporate the "Role-based Authorization" at
the bottom of the Cookbook.

My Root.pm, Login.pm, and Logout.pm controllers look the same as in
Carl's Authentication example in the Cookbook.  My root/index.tt and
login.tt look the same as well.  All of my changes are included at the
bottom of this email.

I get to the /login page fine, but when I put in a username & password
and hit the Login button, I get (note that I'm using "export
DBIX_CLASS_STORAGE_DBI_DEBUG=1" before I run "script/myapp_server.pl"
to get that first line of SQL dump info):

  SELECT me.user_id, me.role_id FROM user_roles me WHERE ( username =
? ): test01
  DBD::SQLite::db prepare_cached failed: no such table: user_roles(1)
at dbdimp.c line 269 at
/usr/lib/perl5/site_perl/5.8.5/DBIx/Class/Storage/DBI.pm line 448.
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug]
**********************************
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug] * Request 4 (0.133/s) [11102]
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug]
**********************************
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug] Body Parameters are:
  .---------------------------------------+-------------------.
  | Key                                   | Value             |
  +---------------------------------------+-------------------+
  | password                              | mypass            |
  | username                              | test01            |
  '---------------------------------------+-------------------'

  [Tue Mar 21 12:25:52 2006] [catalyst] [debug] "POST" request for
"login" from "127.0.0.1"
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug] Arguments are "login"
  [Tue Mar 21 12:25:52 2006] [catalyst] [debug] Rendering template
"login/default"
  [Tue Mar 21 12:25:52 2006] [catalyst] [error] Couldn't render
template "file error - login/default: not found"
  [Tue Mar 21 12:25:52 2006] [catalyst] [error] Caught exception in
MyApp::Controller::Login->default "DBIx::Class::ResultSet::first(): no
sth generated via sql: SELECT me.user_id, me.role_id FROM user_roles
me WHERE ( username = ? ) at
/usr/lib/perl5/site_perl/5.8.5/Catalyst/Plugin/Authentication/Store/DBIC/User.pm
line 15"
  [Tue Mar 21 12:25:52 2006] [catalyst] [error] Couldn't render
template "file error - login/default: not found"
  [Tue Mar 21 12:25:52 2006] [catalyst] [info] Request took 0.131348s (7.613/s)
  .------------------------------------------------+-----------.
  | Action                                         | Time      |
  +------------------------------------------------+-----------+
  | /login/default                                 | 0.034019s |
  | /end                                           | 0.009470s |
  |  -> MyApp::View::TT->process                   | 0.003893s |
  '------------------------------------------------+-----------'


Also, I can use my MyApp::Db::Schema fine in a standalone script...
I'm only running into issue getting it to work inside Catalyst.

Thanks & regards,
HC

~~~~~~

PS -- Here are my changes to what's in the Cookbook:

1) I used this helper command:
script/myapp_create.pl model DBIC DBIC::Schema MyApp::Db::Schema
dbi:SQLite:spadmin.db '' '' '{ AutoCommit => 1 }'

2) I created this "base schema class" in lib/MyApp/Db/Schema.pm:
  package MyApp::Db::Schema;
  use base qw/DBIx::Class::Schema/;
  __PACKAGE__->load_classes(
    {
      'MyApp::Db::Schema' => [ qw/User UserRole Role/]
    }
  );
  1;

3) I created these table classes (I guess they are called result
sources in DBIC) in lib/MyApp/Db/Schema/:
  lib/MyApp/Db/Schema/Role.pm
  ===========================
  package MyApp::Db::Schema::Role;
  use base qw/DBIx::Class/;
  __PACKAGE__->load_components(qw/PK::Auto::SQLite Core/);
  __PACKAGE__->table('roles');
  __PACKAGE__->add_columns(qw/id name/);
  __PACKAGE__->set_primary_key('id');
  __PACKAGE__->has_many(map_user_role => 'MyApp::Db::Schema::UserRole'
=> 'role_id');
  1;

  lib/MyApp/Db/Schema/User.pm
  ===========================
  package MyApp::Db::Schema::User;
  use base qw/DBIx::Class/;
  __PACKAGE__->load_components(qw/PK::Auto::SQLite Core/);
  __PACKAGE__->table('users');
  __PACKAGE__->add_columns(qw/id user_name password/);
  __PACKAGE__->set_primary_key('id');
  __PACKAGE__->has_many(map_user_role => 'MyApp::Db::Schema::UserRole'
=> 'user_id');
  1;

  lib/MyApp/Db/Schema/UserRole.pm
  ===============================
  package MyApp::Db::Schema::UserRole;
  use base qw/DBIx::Class/;
  __PACKAGE__->load_components(qw/PK::Auto::SQLite Core/);
  __PACKAGE__->table('user_roles');
  __PACKAGE__->add_columns(qw/user_id role_id/);
  __PACKAGE__->set_primary_key(qw/user_id role_id/);
  1;

4) Here is my myapp.yml:
  ---
  name: MyApp
  authentication:
      dbic:
          user_class: MyApp::Model::DBIC::User
          user_field: username
          password_field: password
  authorization:
      dbic:
          role_class: MyApp::Model::DBIC::Role
          role_field: role
          role_rel: map_user_role
          user_role_user_field: user

5) Here is my SQL to create myapp.db in SQLite:
  CREATE TABLE user (
          id       INTEGER PRIMARY KEY,
          username TEXT,
          password TEXT
  );
  CREATE TABLE role (
          id INTEGER PRIMARY KEY,
          role TEXT
  );
  CREATE TABLE user_role (
          user INTEGER,
          role INTEGER,
          PRIMARY KEY (user, role)
  );

  INSERT INTO user (id, username, password) VALUES (1, 'test01', 'mypass');
  INSERT INTO user (id, username, password) VALUES (2, 'test02', 'mypass');
  INSERT INTO role (id, role) VALUES (1, 'user');
  INSERT INTO role (id, role) VALUES (2, 'admin');
  INSERT INTO user_role (user, role) VALUES (1, 1);
  INSERT INTO user_role (user, role) VALUES (1, 2);
  INSERT INTO user_role (user, role) VALUES (2, 2);