Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
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 (Chancellor) 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 pondering the Monastery: (6)
As of 2015-07-01 23:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (25 votes), past polls