[Dbix-class] sqlite vs mysql
shawn wilson
ag4ve.us at gmail.com
Fri Jul 27 04:10:47 GMT 2012
I made this script to operate with mysql. however, I also need it to
work with sqlite. I am having issues with primary keys with sqlite.
what am i doing wrong? and can i make this schema work with both db
types?
the error is:
err: DBI Exception: DBD::SQLite::st execute failed: org.org_pk may not
be NULL [for Statement "INSERT INTO org ( agency, symbol, web_ag)
VALUES ( ?, ?, ? )"] at
/usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 1071.
DBIC_TRACE stuff:
SELECT COUNT( * ) FROM text me JOIN dates dates ON dates.dates_pk =
me.dates_fk JOIN org org ON org.org_pk = me.org_fk JOIN batch batch
ON batch.batch_pk = me.batch_fk WHERE ( ( dates.pub_date = ? AND
org.symbol = ? ) ): '2012-07-23 00:00:00', 'DOE'
DOE Education Department
BEGIN WORK
SELECT me.org_pk, me.symbol, me.agency, me.web_ag FROM org me WHERE (
( me.agency = ? AND me.symbol = ? AND me.web_ag = ? ) ): 'Education
Department', 'DOE', 'U.S. Department of Education, National Assessment
Governing Board.'
SELECT me.dates_pk, me.dates, me.pub_date FROM dates me WHERE ( (
me.dates = ? AND me.pub_date = ? ) ): 'August 2-4, 2012. Times:',
'2012-07-23'
SELECT me.batch_pk, me.run FROM batch me WHERE ( me.run = ? ): '1'
SELECT me.org_pk, me.symbol, me.agency, me.web_ag FROM org me WHERE (
( me.agency = ? AND me.symbol = ? AND me.web_ag = ? ) ): 'Education
Department', 'DOE', 'U.S. Department of Education, National Assessment
Governing Board.'
INSERT INTO org ( agency, symbol, web_ag) VALUES ( ?, ?, ? ):
'Education Department', 'DOE', 'U.S. Department of Education, National
Assessment Governing Board.'
ROLLBACK
err: DBI Exception: DBD::SQLite::st execute failed: org.org_pk may not
be NULL [for Statement "INSERT INTO org ( agency, symbol, web_ag)
VALUES ( ?, ?, ? )"] at
/usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 1071.
DBIx::Class::Schema::throw_exception('FR::Schema=HASH(0x336e150)',
'DBI Exception: DBD::SQLite::st execute failed: org.org_pk may...')
called at /usr/local/share/perl/5.10.1/DBIx/Class/Storage.pm line 111
DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'DBI Exception: DBD::SQLite::st execute failed: org.org_pk may...')
called at /usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line
1288
DBIx::Class::Storage::DBI::__ANON__('DBD::SQLite::st execute
failed: org.org_pk may not be NULL [f...', 'DBI::st=HASH(0x4167370)',
undef) called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 1586
DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'DBI::db=HASH(0x3c159e0)', 'INSERT INTO org ( agency, symbol, web_ag)
VALUES ( ?, ?, ? )', 'ARRAY(0x41544b0)', 'ARRAY(0x41735a8)') called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 779
DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'_dbh_execute', 'INSERT INTO org ( agency, symbol, web_ag) VALUES ( ?,
?, ? )', 'ARRAY(0x41544b0)', 'ARRAY(0x41735a8)') called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 1551
DBIx::Class::Storage::DBI::_execute('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'insert', 'DBIx::Class::ResultSource::Table=HASH(0x33a2888)',
'HASH(0x417beb8)', undef) called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 1664
DBIx::Class::Storage::DBI::insert('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'DBIx::Class::ResultSource::Table=HASH(0x33a2888)', 'HASH(0x4184ea0)')
called at /usr/local/share/perl/5.10.1/DBIx/Class/Row.pm line 349
DBIx::Class::Row::insert('FR::Schema::Result::Org=HASH(0x41f7a90)')
called at /usr/local/share/perl/5.10.1/DBIx/Class/Row.pm line 325
DBIx::Class::Row::insert('FR::Schema::Result::Text=HASH(0x419d588)')
called at /usr/local/share/perl/5.10.1/DBIx/Class/ResultSet.pm line
2640
DBIx::Class::ResultSet::create('DBIx::Class::ResultSet=HASH(0x3788250)',
'HASH(0x40a1a48)', 'HASH(0x419da38)') called at ./res-get.pl line 131
main::__ANON__() called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage.pm line 211
DBIx::Class::Storage::__ANON__() called at
/usr/local/share/perl/5.10.1/Try/Tiny.pm line 76
eval {...} called at /usr/local/share/perl/5.10.1/Try/Tiny.pm line 67
Try::Tiny::try('CODE(0x3e95480)',
'Try::Tiny::Catch=REF(0x40843f0)',
'Try::Tiny::Finally=REF(0x3bc87f8)') called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage.pm line 258
DBIx::Class::Storage::txn_do(undef, undef) called at
/usr/local/share/perl/5.10.1/DBIx/Class/Storage/DBI.pm line 806
DBIx::Class::Storage::DBI::txn_do('DBIx::Class::Storage::DBI::SQLite=HASH(0x3368950)',
'CODE(0x40a22e8)') called at
/usr/local/share/perl/5.10.1/DBIx/Class/Schema.pm line 662
DBIx::Class::Schema::txn_do('FR::Schema=HASH(0x336e150)',
'CODE(0x40a22e8)') called at ./res-get.pl line 153
main::__ANON__() called at
/usr/local/share/perl/5.10.1/Try/Tiny.pm line 76
eval {...} called at /usr/local/share/perl/5.10.1/Try/Tiny.pm line 67
Try::Tiny::try('CODE(0x4084948)',
'Try::Tiny::Catch=REF(0x3bc8690)') called at ./res-get.pl line 157
# Text.pm:
package FR::Schema::Result::Text;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('text');
__PACKAGE__->load_components(qw/InflateColumn::DateTime/);
__PACKAGE__->add_columns(
'text_pk',
{
data_type => "integer",
extra => { unsigned => 1 },
is_auto_increment => 1,
is_nullable => 0,
},
"stamp",
{ data_type => 'datetime', is_nullable => 0 },
"org_fk",
{ data_type => "integer", is_foreign_key => 1, extra => { unsigned
=> 1 }, is_nullable => 0 },
"dates_fk",
{ data_type => "integer", is_foreign_key => 1, extra => { unsigned
=> 1 }, is_nullable => 0 },
"batch_fk",
{ data_type => "integer", is_foreign_key => 1, extra => { unsigned
=> 1 }, is_nullable => 0 },
"description",
{ data_type => "text", is_nullable => 1 },
"summary",
{ data_type => "text", is_nullable => 1 },
"address",
{ data_type => "text", is_nullable => 1 },
"page",
{ data_type => "text", is_nullable => 1 },
"action",
{ data_type => "text", is_nullable => 1 },
"text_uri",
{ data_type => "text", is_nullable => 1 },
"pdf_uri",
{ data_type => "text", is_nullable => 1 },
);
__PACKAGE__->set_primary_key("text_pk");
__PACKAGE__->belongs_to(
org =>
"FR::Schema::Result::Org",
{ "foreign.org_pk" => "self.org_fk" }
);
__PACKAGE__->belongs_to(
dates =>
"FR::Schema::Result::Dates",
{ "foreign.dates_pk" => "self.dates_fk" }
);
__PACKAGE__->belongs_to(
batch =>
"FR::Schema::Result::Batch",
{ "foreign.batch_pk" => "self.batch_fk" }
);
1;
# Org.pm:
package FR::Schema::Result::Org;
use strict;
use warnings;
use base 'DBIx::Class::Core';
__PACKAGE__->table('org');
__PACKAGE__->add_columns(
"org_pk",
{
data_type => "integer",
extra => { unsigned => 1 },
is_auto_increment => 1,
# is_nullable => 0,
},
"symbol",
{ data_type => "varchar", size => 8, is_nullable => 1 },
"agency",
{ data_type => "text", is_nullable => 1 },
"web_ag",
{ data_type => "text", is_nullable => 1 },
);
__PACKAGE__->set_primary_key("org_pk");
__PACKAGE__->has_many(
"text",
"FR::Schema::Result::Text",
{ "foreign.org_fk" => "self.org_pk" },
{ is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);
1;
# script create method:
try {
$schema->txn_do( sub {
$text->create( {
stamp => $dtnow,
description => $part->{DESC} // "",
summary => $part->{SUMMARY} // "",
address => $part->{ADDRESS} // "",
action => $part->{ACTION} // "",
page => $part->{PAGE} // "",
text_uri => $pagetext->{text}{link},
pdf_uri => $pagetext->{pdf},
org => {
symbol => $org,
web_ag => $part->{AGENCY} // "",
agency => $orgs->{$org}
}, dates => {
dates => $part->{DATES} // "",
pub_date => $curdt
}, batch => {
run => ($last_run + 1)
}
}, {
key => 'text_pk'
} );
} );
} catch {
push @errs, { date => $curdt->ymd("/"), org => $org,
page => $pagetext };
print "err: " . $_ . "\n";
};
More information about the DBIx-Class
mailing list