[Dbix-class] update_or_insert from hash reference

Matt S Trout dbix-class at trout.me.uk
Tue May 23 20:51:06 CEST 2006


Fernan Aguero wrote:
> +----[ Matt S Trout <dbix-class at trout.me.uk> (23.May.2006 13:50):
> |
> | Fernan Aguero wrote:
> | > Hi!
> | > 
> | > I'm new to DBIx::Class and after setting up a mysql database
> | > I'm trying to test DBIx::Class by defining a minimal
> | > object-relational mapping for a single table:
> | > 
> | > TABLE users (
> | >   user_id (PK, auto_increment),
> | >   username,
> | >   firstname,
> | >   lastname.
> | >   is_allowed_to_edit_database,
> | >   date_modified,
> | >   date_added )
> | > 	
> | > What I want to do to populate the DB is something like this:
> | > 
> | > my $userHashRef = {
> | >   username  => $username,
> | >   firstname => $firstname,
> | >   lastname  => $lastname,
> | >   ... ...
> | >   };
> | > 
> | > And then 
> | > 
> | > # get an object
> | > my $appUser = MyApp::DB::User->new( $userHashRef );
> | > 
> | > # update or insert
> | > $appUser->update_or_insert;
>
> [snipped]
>
> | > What am I missing? What am I doing wrong? Thanks in advance,
> | 
> | Read the Manual - you've not loaded the components you need, your new() 
> | method is both unnecessary and broken, and there's no update_or_insert 
> | method - you want $rs->update_or_create ...
> |
> +----]
>
> Matt,
>
> thanks for sending me to RTFM :) 
>
> It's not that I haven't read it ... but some things don't
> make sense to me (yet) ... perhaps I haven't fully entered
> the ORM or DBIx::Class orbit. 
>
> Now, I have my insertdb test script working (but read
> below). I have not changed the loading of components ... so
> what do you mean by "you've not loaded the components you
> need"?
>
> I got rid of the new() method, my Users.pm now looks like
> this:
>
> package MyApp::DB::Users;
>
> use base qw/DBIx::Class/;
>
> __PACKAGE__->load_components( qw/Core PK::Auto/ );
>
> # set the table name
> __PACKAGE__->table('users');
>
> # set columns in the table
> __PACKAGE__->add_columns( qw/
> 	user_id username password date_modified date_added firstname
> 	lastname is_allowed_to_edit_database / );
> 	
> # set the primary key
> __PACKAGE__->set_primary_key( qw/ user_id /);
>
> 1;
>
>
> and in my script I use it like this:
>
> my $userHashRef = { 
>   user_id   => $user_id,
>   username  => $username, 
>   firstname => $firstname,
>   ... };
>   
> my $dbix = MyApp::DB->connect( 'dbi:mysql:dbname=... );
>
> my $resultSetObj = $dbix->resultset('Users')->search( $userHashRef );
>
> $resultSetObj->update_or_create;
>
>
> Now the first time I ran the script, I filled the hash ref
> with data for a user but without setting the user_id
> (primary key), as this will be automatically generated (and
> incremented) by mysql (the same goes for the date columns).
>
> This worked fine and I got the following inserted into the
> DB:
>
> user_id username firstname lastname ... is_allowed...
> -----------------------------------------------------
> 1       fernan   Fernn    Aguero   ...  Y
>
> On a second run, I set the user_id to '1' and in the hash
> ref I changed the typo in the firstname and changed the
> is_allowed_to_edit_database from Y to N.
>
> When running the script I stepped into this:
> DBIx::Class::ResultSet::update_or_create(): Error executing
> 'INSERT INTO users (firstname, ..., user_id, username)
> VALUES (?, ?, ...) ('Fernan', ..., '1', 'fernan'):
> DBD::mysql::st execute failed: Duplicate entry '1' for key 1
> at /usr/local/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI.pm
> line 525.
>
> Why is it trying to INSERT when it should have UPDATED the
> row? 
>
> I've read TFM :) and so far the documentation for the
> update_or_create method in DBIx::Class::ResultSet confirms
> what the method name suggests: that it will first search
> "for an existing row matching one of the unique constraints
> (including the primary key) on the source of this resultset.
> If a row is found, updates it with the other given column
> values.  Otherwise, creates a new row."
>
> Will it use the primary key as declared in Users.pm? Or
> should I have to pass it as an extra attribute to the search
> call? (i.e. as in ->search( hashRef, { key => user_id } ) )
> I've already tried this and it didn't work ... but maybe I'm
> doing something wrong (again).
>   
my $user = $dbix->resultset('Users')->update_or_create($hashref);

:)



More information about the Dbix-class mailing list