comment on

hi all,

i have been trying without much success to use the CPAN module DBIx-Class. at the moment i'm trying to use the find_or_create() to insert data into my database.

I have provided two examples:

dbix-class-library.pl

#!/usr/bin/perl -w
use strict;
use Library::Schema;
use Data::Dump qw(dump);


# Connection parameters
my $dsn = 'dbi:mysql:library';
my $usr = 'nelo';
my $psw = 'n';

# Create a database connection
my $schema = Library::Schema->connect( $dsn, $usr, $psw );

# Insert a new person into table names (represented by class Names)
my ( $artist, $cd );
do
{
    # Provide a artist name
    print 'Enter artist name: ';
    chomp ( my $input_name = <STDIN> );
    
    # Insert new and unique artists
    my $new_artist = $schema->resultset('Artist')->find_or_create(
    {
     name => $input_name,
    },
    {
     key => 'artist_name'
    } );
    
    # SELECT * FROM artist
    my $rs_artist = $schema->resultset('Artist')->search();
    
    # Save artist { name => id } in a hash
    while ( my $artist_name = $rs_artist->next )
    {
        $artist->{$artist_name->name} = $artist_name->artistid;
    }
    
    # Provide a cd name
    print 'Enter cd name: ';
    chomp ( my $input_cd = <STDIN> );
    
    # Insert new and unique cds
    my $new_cd = $schema->resultset('Cd')->find_or_create(
    {
     title  =>  $input_cd,
     artist =>  $artist->{$input_name}, # Retrieve the artist ids for 
+the cd
    },
    {
     key    =>  'cd_title'
    } );
    
    # SELECT * FROM cd
    my $rs_cd = $schema->resultset('Cd')->search();
    
    # Save cd { title => id } in a hash
    while ( my $cd_name = $rs_cd->next )
    {
        $cd->{$cd_name->title} = $cd_name->cdid;
    }
    
    # Provide a cd name
    print 'Enter track name: ';
    chomp ( my $input_track = <STDIN> );
    
    # Insert new and unique tracks
    my $new_track = $schema->resultset('Track')->find_or_create(
    {
     title  =>  $input_track,
     cd     =>  $cd->{$input_cd},
    },
    {
     key    =>  'track_title'
    } );
    
    print 'done', "\n" if $new_track;
    
}
unless! ( $schema );

exit;
[download]

try-loading.pl

#!/usr/bin/perl -w
use strict;
use Germinate::Schema;
use Data::Dump qw(dump);
use Time::Format qw(%time %strftime %manip);

print "testing loading:\n";

# Connection parameters
my $dsn = 'dbi:mysql:cpc';
my $usr = 'nelo';
my $psw = 'n';

# Create a database connection
my $schema = Germinate::Schema->connect( $dsn, $usr, $psw );

do
{
    print "connected to cpc database:\n";
    
    # Provide phenotype name, short name and description
    my $unit  = 1;
    my $name  = 'phenotype test name';
    my $short = 'PTN';
    my $desc  = 'a fictional phenotype to test loading the database.';
    my $time  = $time{'yyyy-mm-dd hh:mm:ss'};

    # Insert new and unique phenotypes
    my $phenotype = $schema->resultset('Phenotypes')->find_or_create(
    {
     unit_id                =>  $unit,
     phenotype_name         =>  $name,
     phenotype_short_name   =>  $short,
     description            =>  $desc,
     created_on             =>  $time,
     updated_on             =>  $time,
    },
    {
     key => 'my_key'
    }
    );

    # SELECT * FROM phenotypes
    my $rs = $schema->resultset('Phenotypes')->search();
    
    # Save phenotypes { name => id } in a hash
    my $phenotypes;
    while ( my $phe_name = $rs->next )
    {
        $phenotypes->{$phe_name->phenotype_uid} = $phe_name->phenotype
+_name;
    }
    
    # Test print
    print dump( $phenotypes ), "\n";
}
if ($schema);

exit;
[download]

in dbix-class-library i have no problems as i'm only inserting new data into one column. i am also able to insert into a second column using a foreign key. however, i cannot seem to update multiple columns in the manner i require.

can any one spot any obvious reasons why try-loading does not work? it keeps adding to the table even though i have specified a unique_constraint. according to the instructions it should check if the column(s) specified in the unique key constraint exist and return the id. or have i read that wrong?

please have a look. i'm still working on it but would appreciate any suggestions. i have also provided the scripts to create both mysql databases you would require:

CREATE DATABASE CPC;

USE CPC;

CREATE TABLE phenotypes (
  phenotype_uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  unit_id INTEGER UNSIGNED NOT NULL,
  phenotype_name VARCHAR(255) NULL,
  phenotype_short_name VARCHAR(10) NULL,
  description TEXT NULL,
  datatype INTEGER NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(phenotype_uid)
)
TYPE=InnoDB;

CREATE TABLE phenotype_data (
  phenotype_data_uid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  dataset_uid INT NOT NULL,
  phenotype_uid INTEGER UNSIGNED NOT NULL,
  germinate_base_uid INTEGER(8) NOT NULL,
  value VARCHAR(255) NULL,
  recording_date DATETIME NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(phenotype_data_uid)
)
TYPE=InnoDB;

CREATE TABLE units (
  unit_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  unit_name VARCHAR(255) NOT NULL,
  unit_abbreviation VARCHAR(255) NOT NULL,
  unit_description VARCHAR(255) NOT NULL,
  created_on DATETIME NULL,
  updated_on DATETIME NULL,
  PRIMARY KEY(unit_id)
);

CREATE DATABASE Library;

USE Library;

CREATE TABLE artist (
  artistid INTEGER PRIMARY KEY,
  name TEXT NOT NULL 
);

CREATE TABLE cd (
  cdid INTEGER PRIMARY KEY,
  artist INTEGER NOT NULL REFERENCES artist(artistid),
  title TEXT NOT NULL
);

CREATE TABLE track (
  trackid INTEGER PRIMARY KEY,
  cd INTEGER NOT NULL REFERENCES cd(cdid),
  title TEXT NOT NULL
);
[download]

and the modules you would need as well:

package Germinate::Schema;
use strict;
use base qw/DBIx::Class::Schema/;

#__PACKAGE__->load_classes(qw/ Phenotypes Phenotype_data /);
__PACKAGE__->load_classes(qw/ Phenotypes /);

1;

package Germinate::Schema::Phenotypes;
use strict;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/ PK::Auto Core /);
__PACKAGE__->table('phenotypes');
__PACKAGE__->add_columns(
                         qw/
                         phenotype_uid
                         unit_id
                         phenotype_name
                         phenotype_short_name
                         description
                         datatype
                         created_on
                         updated_on
                         /
                        );
__PACKAGE__->set_primary_key('phenotype_uid');

# here needs work!
#__PACKAGE__->has_many('phenotype_datas' => 'Germinate::Schema::Phenot
+ype_data');
__PACKAGE__->add_unique_constraint(my_key => [ qw/ phenotype_short_nam
+e / ]);

1;

package Library::Schema;
use strict;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes(qw/Artist Cd Track/);

1;

package Library::Schema::Artist;
use strict;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(qw/ artistid name /);
__PACKAGE__->set_primary_key('artistid');
__PACKAGE__->has_many('cds' => 'Library::Schema::Cd');
__PACKAGE__->add_unique_constraint( artist_name => [ qw/ name / ], );

1;

package Library::Schema::Cd;
use strict;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('cd');
__PACKAGE__->add_columns(qw/ cdid artist title/);
__PACKAGE__->set_primary_key('cdid');
__PACKAGE__->belongs_to('artist' => 'Library::Schema::Artist');
__PACKAGE__->has_many('tracks' => 'Library::Schema::Track');
__PACKAGE__->add_unique_constraint( cd_title => [ qw/ title / ], );

1;

package Library::Schema::Track;
use strict;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('track');
__PACKAGE__->add_columns(qw/ trackid cd title/);
__PACKAGE__->set_primary_key('trackid');
__PACKAGE__->belongs_to('cd' => 'Library::Schema::Cd');
__PACKAGE__->add_unique_constraint( track_title => [ qw/ title / ], );

1;
[download]

my apologies for being so extensive. i wrongly assumed i could upload a zipped file here. thanks in advance.

nelo

In reply to DBIx-Class: find_or_create() duplicating entries. by j1n3l0

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		`&`
	<		`<`
	>		`>`
	[		`[`
	]		`]`

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.