DBIx::Class Error: syntax error at or near "NOT" [Solved]

[three18ti]

Hello Monks,

I am looking at developing a website that will use an ACL. At the moment I am working on writing the DBIx Schema so I can deploy the database via script.
The fill source code can be found on github: https://github.com/three18ti/dbix-schema-test

The full error is:

DBIx::Class::Schema::deploy(): DBIx::Class::Schema::deploy(): 
DBI Exception: DBD::Pg::db do failed: ERROR:  syntax error at or near 
+"NOT"
LINE 5:   "user_password"  NOT NULL,
                           ^ at deploy.pl line 32
 (running "DROP TABLE IF EXISTS "users" CASCADE;
CREATE TABLE "users" (
  "user_id" bigserial NOT NULL,
  "username" character varying(256) NOT NULL,
  "user_password"  NOT NULL,
  "email_address" character varying(256),
  "last_name" character varying(100),
  "active" smallint,
  PRIMARY KEY ("user_id")
);

") at deploy.pl line 32

however line 32 is just the invocation of the DBIx deployment method, lines 30, 31, 32 of deploy.pl:

# deploy Schema
my $schema = My::Schema->connect($dsn, $user_name, $password);
$schema->deploy({ add_drop_table => 1});

I think the real problem is in My::Schema::Result::User:

package My::Schema::Result::User;

use strict;
use warnings;

use Moose;
use MooseX::NonMoose;
use namespace::autoclean;
#use base qw/DBIx::Class::Core/;

extends 'DBIx::Class::Core';

__PACKAGE__->load_components(qw/ InflateColumn::DateTime Ordered TimeS
+tamp PassphraseColumn /);
__PACKAGE__->position_column('user_id');

__PACKAGE__->table('users');

__PACKAGE__->add_columns(
                        user_id =>
                          { accessor  => 'userid',
                            data_type => 'integer',
                            size      => 16,
                            is_nullable => 0,
                            is_auto_increment => 1,
                          },
                        username =>
                          { accessor  => 'username',
                            data_type => 'varchar',
                            size      => 256,
                            is_nullable => 0,
                            is_auto_increment => 0,
                          },
                        user_password =>
                          { data_type => 'varchar', 
                            size      => 256,
                            is_nullable => 0,
                            is_auto_increment => 0,
                          },
                        email_address =>
                          { data_type   => "varchar",
                            size        => 256,
                            is_nullable => 1,
                          },
                        last_name   =>
                          { data_type   => "varchar",
                            size        => 100,
                            is_nullable => 1,
                          },
                        active      =>
                          { data_type   => "integer",
                            size        => 1,
                            is_nullable => 1,
                          },
                       );

__PACKAGE__->set_primary_key('user_id');

__PACKAGE__->has_many(
    user_roles  => 'My::Schema::Result::UserRole',
    { "foreign.user_id" => "self.id" },
    { cascade_copy => 0, cascade_delete => 0 },
);

# many_to_many():
#   args:
#     1) Name of relationship, 
# DBIC will create accessor with this name
#     2) Name of has_many() 
# relationship this many_to_many() is shortcut for
#     3) Name of belongs_to() 
# relationship in model class of has_many() above
#   You must already have the has_many() 
# defined to use a many_to_many().
__PACKAGE__->many_to_many(roles => 'user_roles', 'role');

# Have the 'password' column use a SHA-1 hash and 20-byte salt
# with RFC 2307 encoding; Generate the 'check_password" method
__PACKAGE__->add_columns(
    'user_password' => {
        passphrase       => 'rfc2307',
        passphrase_class => 'SaltedDigest',
        passphrase_args  => {
            algorithm   => 'SHA-1',
            salt_random => 20.
        },
        passphrase_check_method => 'check_password',
    },
);

=head2 has_role

Check if a user has the specified role

=cut

use Perl6::Junction qw/any/;
sub has_role {
    my ($self, $role) = @_;

    # Does this user posses the required role?
    return any(map { $_->role } $self->roles) eq $role;
}

__PACKAGE__->meta->make_immutable;
1;
__END__

I'm not sure why the password field is causing me pain. This is essentially borrowed from the Catalyst tutorial on user authorization. Any insight would be greatly appreciated.

Edit: I did find this page in the DBIx Manual, : http://search.cpan.org/~mstrout/DBIx-Class-0.08100/lib/DBIx/Class/Manual/Troubleshooting.pod#syntax_error_at_or_near_"<something>"_...
I changed the field name from password to user_password and the change produced the same error (the name change was reflected in the error message)

Solution: Looks like I was redefining my user_password field, here is the updated version. Thanks to tobyink for the pointer below.

Here's the updated class:

package My::Schema::Result::User;

use strict;
use warnings;

use Moose;
use MooseX::NonMoose;
use namespace::autoclean;
#use base qw/DBIx::Class::Core/;

extends 'DBIx::Class::Core';

__PACKAGE__->load_components(qw/ InflateColumn::DateTime Ordered TimeS
+tamp PassphraseColumn /);
__PACKAGE__->position_column('user_id');

__PACKAGE__->table('users');

__PACKAGE__->add_columns(
                        user_id =>
                          { accessor  => 'userid',
                            data_type => 'integer',
                            size      => 16,
                            is_nullable => 0,
                            is_auto_increment => 1,
                          },
                        username =>
                          { accessor  => 'username',
                            data_type => 'varchar',
                            size      => 256,
                            is_nullable => 0,
                            is_auto_increment => 0,
                          },

                        # Have the 'password' column use a SHA-1 hash 
+and 20-byte salt
                        # with RFC 2307 encoding; Generate the 'check_
+password" method
                        user_password =>
                          { data_type => 'varchar', 
                            size      => 256,
                            is_nullable => 0,
                            is_auto_increment => 0,
                            passphrase       => 'rfc2307',
                            passphrase_class => 'SaltedDigest',
                            passphrase_args  => {
                                algorithm   => 'SHA-1',
                                salt_random => 20.
                            },
                            passphrase_check_method => 'check_password
+',
                          },
                        email_address =>
                          { data_type   => "varchar",
                            size        => 256,
                            is_nullable => 1,
                          },
                        last_name   =>
                          { data_type   => "varchar",
                            size        => 100,
                            is_nullable => 1,
                          },
                        active      =>
                          { data_type   => "integer",
                            size        => 1,
                            is_nullable => 1,
                          },
                       );

__PACKAGE__->set_primary_key('user_id');

__PACKAGE__->has_many(
    user_roles  => 'My::Schema::Result::UserRole',
    { "foreign.user_id" => "self.id" },
    { cascade_copy => 0, cascade_delete => 0 },
);

# many_to_many():
#   args:
#     1) Name of relationship, DBIC will create accessor with this nam
+e
#     2) Name of has_many() relationship this many_to_many() is shortc
+ut for
#     3) Name of belongs_to() relationship in model class of has_many(
+) above
#   You must already have the has_many() defined to use a many_to_many
+().
__PACKAGE__->many_to_many(roles => 'user_roles', 'role');


=head2 has_role

Check if a user has the specified role

=cut

use Perl6::Junction qw/any/;
sub has_role {
    my ($self, $role) = @_;

    # Does this user posses the required role?
    return any(map { $_->role } $self->roles) eq $role;
}

__PACKAGE__->meta->make_immutable;
1;
__END__

[Your Mother]

"user_password" NOT NULL, seems to be missing a data type :) Also, DBIx is a wide namespace which is not actually synonymous with DBIC, aka DBIx::Class.

[three18ti]

I had no idea, thanks for clarifying that. The fact that it goes by DBIC and DBIx::Class is kinda confusing too (now I understand though, one of those ligtbulb moments ;) )

Also, thanks for the pointer, I couldn;t figure out for the life of my why it was missing (since I had -previously- defined the data type), but it appears that I was overwriting the password field.

[tobyink]

Now, I've never used DBIC, but I'm guessing the problem is this... You call add_columns for the password column twice. The first time you supply a SQL data type; the second time you do not. I suppose what happens is that when a column already exists (the password column already exists the second time you add it), then add_columns overwrites the original definition for the column rather than updating it. So the password column's initial definition which includes a data type gets overwritten with a new definition which does not.

Thus you should be able to fix your issue by moving all the details for the password column from the second add_columns call up to where the password column is initially defined; and then ditching the second add_columns call.