[Dbix-class] how to avoid adding duplicate row to table containing unique field/column

[Trevor Leffler]

Hi Rajeev,

See the docs on adding unique constraints [at the DBIC layer]:
http://search.cpan.org/dist/DBIx-Class-0.08121/lib/DBIx/Class/ResultSource.pm#add_unique_constraint

If 'userid' is already your primary key, you may omit the second 
argument to find_or_create().

Cheers,
--Trevor

Rajeev Prasad wrote:
> thx Rob,
> 
> I tried to use it like this:
> 
> for my $aref ( @newusers ) { 
>     print "adding [@$aref], or say $aref->[0]  \n";
>     $schema->resultset('Testdbuser')->find_or_create(
>         {
>           userid => $aref->[0],
>           password  => $aref->[1],
>         },
>         { key => 'userid' }
>       );
>     }
> 
> getting error:
> 
> DBIx::Class::ResultSet::find_or_create(): Unknown unique constraint 
> userid on 'testdbusers' at ./insertdb.pl line 25
> 
> 
> see below that my table does have a unique constraint on this column.
> 
> 
> ------------------------------------------------------------------------
> *From:* Rob Kinyon <rob.kinyon at gmail.com>
> *To:* Rajeev Prasad <rp.neuli at yahoo.com>; DBIx::Class user and developer 
> list <dbix-class at lists.scsys.co.uk>
> *Sent:* Wednesday, October 12, 2011 11:41 AM
> *Subject:* Re: [Dbix-class] how to avoid adding duplicate row to table 
> containing unique field/column
> 
> find_or_create is a method on DBIx::Class::ResultSet
> 
> On Wed, Oct 12, 2011 at 12:06, Rajeev Prasad <rp.neuli at yahoo.com 
> <mailto:rp.neuli at yahoo.com>> wrote:
> 
> 
> 
> 
>     there is a find_and_create function which can do the job, i am not
>     able to find help on it. does anyone know?
> 
> 
> 
> 
>     ------------------------------------------------------------------------
>     *From:* Rajeev Prasad <rp.neuli at yahoo.com <mailto:rp.neuli at yahoo.com>>
>     *To:* "dbix-class at lists.scsys.co.uk
>     <mailto:dbix-class at lists.scsys.co.uk>" <dbix-class at lists.scsys.co.uk
>     <mailto:dbix-class at lists.scsys.co.uk>>
>     *Sent:* Wednesday, October 12, 2011 10:33 AM
>     *Subject:* [Dbix-class] how to avoid adding duplicate row to table
>     containing unique field/column
> 
>     Hello,
> 
>     In my test script below, i am trying to figure out how to suppress
>     the error mesg when the the script try to add a previously presen
>     tentry into the table.
> 
>     OR better even, to make sure that the script should not even try to
>     update the table, if the record is already present in table. (table
>     may grow very big in future).
> 
> 
>     table:
>     Action	Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null
>     Comment
>     Edit Edit
>     <http://www.sking.att/phpMyAdmin/tbl_indexes.php?db=mysqltestdb&table=testdbusers&index=PRIMARY&token=6f24cb5736aa4533f388248e941da70f>
>     	Drop Drop
>     <http://www.sking.att/phpMyAdmin/sql.php?db=mysqltestdb&table=testdbusers&sql_query=ALTER+TABLE+%60testdbusers%60+DROP+PRIMARY+KEY&message_to_show=The+primary+key+has+been+dropped&token=6f24cb5736aa4533f388248e941da70f>
>     	PRIMARY	BTREE	Yes	No	userid	3	A	
>     	
>     Edit Edit
>     <http://www.sking.att/phpMyAdmin/tbl_indexes.php?db=mysqltestdb&table=testdbusers&index=userid&token=6f24cb5736aa4533f388248e941da70f>
>     	Drop Drop
>     <http://www.sking.att/phpMyAdmin/sql.php?db=mysqltestdb&table=testdbusers&sql_query=ALTER+TABLE+%60testdbusers%60+DROP+INDEX+%60userid%60&message_to_show=Index+userid+has+been+dropped&token=6f24cb5736aa4533f388248e941da70f>
>     	userid	BTREE	Yes	No	userid	3	A	
>     	
> 
> 
>     mysql> desc testdbusers;
>     +----------+-------------+------+-----+---------+-------+
>     | Field    | Type        | Null | Key | Default | Extra |
>     +----------+-------------+------+-----+---------+-------+
>     | userid   | char(6)     | NO   | PRI | NULL    |       |
>     | password | varchar(10) | YES  |     | NULL    |       |
>     +----------+-------------+------+-----+---------+-------+
>     2 rows in set (0.00 sec)
> 
>     mysql>
> 
>     mysql> show index from testdbusers;
>     +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>     | Table       | Non_unique | Key_name | Seq_in_index | Column_name |
>     Collation | Cardinality | Sub_part | Packed | Null | Index_type |
>     Comment |
>     +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>     | testdbusers |          0 | PRIMARY  |            1 | userid      |
>     A         |           3 |     NULL | NULL   |      | BTREE     
>     |         |
>     | testdbusers |          0 | userid   |            1 | userid      |
>     A         |           3 |     NULL | NULL   |      | BTREE     
>     |         |
>     +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
>     2 rows in set (0.00 sec)
> 
>     mysql>
> 
> 
> 
> 
>     script:
> 
>     #!/usr/bin/perl
>     use strict;
>     use warnings;
>     use lib '../testdb';
>     use Mysqltestdb::Schema;
>     my $schema;
>     my @newusers;
>     $schema =
>     Mysqltestdb::Schema->connect("dbi:mysql:dbname=mysqltestdb:localhost:3306",
>     'root', 'mypassword');
>     @newusers = (['te1234', 'pass'], ['te4567', 'pass']);
>     $schema->populate('Testdbuser', [
>        [qw/userid password/],
>        @newusers,
>     ]);
> 
> 
> 
> 
>     I get this info message out from script(error):
> 
>     DBIx::Class::Schema::populate(): Duplicate entry 'te1234' for key
>     'PRIMARY' for populate slice:
>     {
>       password => "pass",
>       userid => "te1234"
>     }
> 
> 
> 
>     _______________________________________________
>     List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>     IRC: irc.perl.org#dbix-class <http://irc.perl.org#dbix-class>
>     SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>     Searchable Archive:
>     http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
> 
> 
>     _______________________________________________
>     List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>     IRC: irc.perl.org#dbix-class <http://irc.perl.org#dbix-class>
>     SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>     Searchable Archive:
>     http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
> 
> 
> 
> 
> -- 
> Thanks,
> Rob Kinyon
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk