Re: [CDBI] Class::DBI::Loader question

[prev] [thread] [next] [Date index for 2004/10/14]

Perrin Harkins writes:
 > On Tue, 2004-10-12 at 14:43, Kingsley Kerce wrote:
 > > I've just run the latest SQL::Translator's Class::DBI producer against
 > > a PostgreSQL schema of a simple many-to-many relation.  I can't see
 > > how the resulting Class::DBI code would be helpful to anyone.
 > 
 > It doesn't look so bad to me.  There are a couple of bits that reflect
 > one person's taste and that I would probably do differently.

Disagreed.  The code generated by SQL::Translator's Class::DBI
producer is abysmal.  I've (re)appended the code in its entirety at
the end of this message.

Read the code in the a_user package.  The following is acceptable:

a_user->set_up_table('a_user');
a_user->has_many(
    'user_role_user_id', 'user_role' => 'user_id'
);

But the rest of the package is crap.  The "Primary key accessor" sub
is redundant and useless:

sub a_user {
    shift->id
}

Then there's the _redefinition_ of the sub user_roles.
WTF?!?!

sub user_roles {
    return shift->user_role_user_id
}

sub user_roles { my $self = shift; return map $_->id, $self->user_role_user_id }

Besides, the user_roles sub is unnecessary.  In my opinion, the
producer should have created something like the following:

UserRole->has_a(user_id => 'AUser');
UserRole->has_a(role_id => 'Role');
AUser->has_many(roles => [ 'UserRole' => 'role_id' ]);
Role->has_many(a_users => [ 'UserRole' => 'user_id' ]);

Which is a common CDBI idiom, and would automatically provide you with
methods like:

$user->roles
and
$role->a_users

which obviate any need of a user_roles sub.

(BTW, "user" is a reserved word in PostgreSQL, thus "a_user".)

Folks should know that SQL::Translator's Class::DBI producer is in
need of a complete overhaul.

Kingsley

# -------------------------------------------------------------------

$ cat schema.sql

CREATE TABLE a_user (
  id SERIAL PRIMARY KEY,
  username VARCHAR(20) NOT NULL,
  password VARCHAR(20) NOT NULL
);

CREATE TABLE role (
  id SERIAL PRIMARY KEY,
  description VARCHAR(30) NOT NULL
);

CREATE TABLE user_role (
  user_id INTEGER NOT NULL REFERENCES a_user(id),
  role_id INTEGER NOT NULL REFERENCES role(id),
  PRIMARY KEY (user_id, role_id)
);

# -------------------------------------------------------------------

$ sqlt -f PostgreSQL -t ClassDBI < schema.sql

package DBI;

# 
# Created by SQL::Translator::Producer::ClassDBI
# Created on Tue Oct 12 14:15:17 2004
# 

use strict;
use base 'Class::DBI::Pg';

DBI->set_db('Main', 'dbi:Pg:_', '', '');

# -------------------------------------------------------------------
package a_user;
use base 'DBI';
use Class::DBI::Pager;

a_user->set_up_table('a_user');


#
# Primary key accessor
#
sub a_user {
    shift->id
}

sub user_roles {
    return shift->user_role_user_id
}

a_user->has_many(
    'user_role_user_id', 'user_role' => 'user_id'
);

sub user_roles { my $self = shift; return map $_->id, $self->user_role_user_id }

# -------------------------------------------------------------------
package role;
use base 'DBI';
use Class::DBI::Pager;

role->set_up_table('role');


#
# Primary key accessor
#
sub role {
    shift->id
}

sub user_roles {
    return shift->user_role_role_id
}

role->has_many(
    'user_role_role_id', 'user_role' => 'role_id'
);

sub user_roles { my $self = shift; return map $_->id, $self->user_role_role_id }

# -------------------------------------------------------------------
package user_role;
use base 'DBI';
use Class::DBI::Pager;

user_role->set_up_table('user_role');


#
# Primary key accessor
#
sub user_role {
    shift->user_id
}

user_role->has_a(
    user_id => 'a_user'
);

sub user_role {
    return shift->user_id
}

user_role->has_a(
    role_id => 'role'
);

sub user_role {
    return shift->role_id
}

1;

# -------------------------------------------------------------------