Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

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

by three18ti (Scribe)
on Jun 03, 2012 at 06:08 UTC ( #974088=perlquestion: print w/ replies, xml ) Need Help??
three18ti has asked for the wisdom of the Perl Monks concerning the following question:

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__

Comment on DBIx::Class Error: syntax error at or near "NOT" [Solved]
Select or Download Code
Re: DBIx Error: syntax error at or near "NOT"
by Your Mother (Canon) on Jun 03, 2012 at 06:20 UTC

    "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.

      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.

Re: DBIx Error: syntax error at or near "NOT"
by tobyink (Abbot) on Jun 03, 2012 at 08:03 UTC

    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.

    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://974088]
Approved by davido
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (3)
As of 2014-10-25 03:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (141 votes), past polls