[Dbix-class] update_or_insert from hash reference

[Fernan Aguero]

+----[ 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).

Fernan