use strict;
use warnings;
use File::Basename;
use lib File::Basename::dirname(__FILE__)."/../..";
use URT;
use Test::More tests => 81;
use URT::DataSource::CircFk;
use Data::Dumper;
# This test verifies that sql generation is correct for inserts and deletes
# on tables with nullable foreign key constraints. For a new object, an
# INSERT statement should be returned, with null values in nullable foreign
# key columns, and a corresponding UPDATE statement to set foreign key
# values after the insert. For object deletion, an UPDATE statement
# setting nullable foreign keys to null is expected with the DELETE statement
setup_classes_and_db();
my @circular = URT::Circular->get();
my $sqlite_ds = UR::Context->resolve_data_source_for_object($circular[0]);
is (scalar @circular, 5, 'got circular objects');
for (@circular){
my $id = $_->id;
ok($_->delete, 'deleted object');
my $ghost = URT::Circular::Ghost->get(id=> $id);
my @sql = $sqlite_ds->_default_save_sql_for_object($ghost);
ok(sql_has_update_and_delete(@sql), "got separate update and delete statement for deleting circular item w/ nullable foreign key");
}
eval{
UR::Context->commit();
};
ok(!$@, "circular deletion committed successfully!");
diag($@) if $@;
my @bridges = URT::Bridge->get();
for (@bridges){
my $id = $_->id;
ok($_->delete(), 'deleted bridge');
my $ghost = URT::Bridge::Ghost->get(id => $id);
my @sql = $sqlite_ds->_default_save_sql_for_object($ghost);
ok(sql_has_delete_only(@sql), "didn't update primary key nullable foreign keys on delete");
}
eval{
UR::Context->commit();
};
ok( !$@, 'no commit errors on deleting bridge entries w/ nullable foreign keys primary key' );
diag($@) if $@;
my @bridges_check = URT::Bridge->get();
is (scalar @bridges_check, 0, "couldn't retrieve deleted bridges");
my @left = URT::Left->get(id=>[1..5]);
my @right = URT::Right->get();
while (my $left = shift @left){
my $right = shift @right;
my $bridge = URT::Bridge->create(left_id => $left->id, right_id => $right->id);
my @sql = $sqlite_ds->_default_save_sql_for_object($bridge);
ok(sql_has_insert_only(@sql), "didn't null insert values for bridge entries nullable, no update statement produced)");
}
eval{
UR::Context->commit();
};
ok( !$@, 'no commit errors on recreating bridge entries' );
diag($@) if $@;
my @chain = ( URT::Gamma->get(), URT::Beta->get(), URT::Alpha->get());
ok (@chain, 'got objects from alpha, beta, and gamma tables');
is (scalar @chain, 3, 'got expected number of objects');
my $gamma = shift @chain;
ok ($gamma->delete, 'deleted_object');
for ("URT::Beta", "URT::Alpha"){
my $obj = shift @chain;
my $id = $obj->id;
my $class = $_."::Ghost";
ok($obj->delete, 'deleted object');
my $ghost = $class->get(id => $id);
my @sql = $sqlite_ds->_default_save_sql_for_object($ghost);
ok(sql_has_update_and_delete(@sql), "got separate update and delete statement for deleting bridge items w/ nullable foreign key");
}
eval{
UR::Context->commit();
};
ok(!$@, "no error message on commit: $@");
diag($@) if $@;
my @chain2 = (URT::Alpha->get(), URT::Beta->get(), URT::Gamma->get());
ok(!@chain2, "couldn't get deleted chain objects!");
my ($new_alpha, $new_beta, $new_gamma);
ok($new_alpha = URT::Alpha->create(id => 101, beta_id => 201), 'created new alpha');
my @alpha_sql = $sqlite_ds->_default_save_sql_for_object($new_alpha);
ok($new_beta = URT::Beta->create(id => 201, gamma_id => 301), 'created new beta');
my @beta_sql = $sqlite_ds->_default_save_sql_for_object($new_beta);
ok($new_gamma = URT::Gamma->create(id => 301, type => 'test2'), 'created new gamma');
for (\@alpha_sql, \@beta_sql){
ok(sql_has_insert_and_update(@$_), 'got seperate insert and update statements for recreating chained objects');
}
eval {
UR::Context->commit();
};
ok(!$@, "no error message on commit of new alpha,beta,gamma, would fail due to fk constraints if we weren't using sqlite datasource");
diag($@) if $@;
my $check_alpha = URT::Alpha->get(id => 101);
is ($check_alpha->beta_id, 201, 'initial null value updated correctly for chain object');
my $check_beta = URT::Beta->get(id => 201);
is ($check_beta->gamma_id, 301, 'initial null value updated correctly for chain object');
sub sql_has_delete_only{
my @st = @_;
return undef if grep {$_->{sql} =~ /update|insert/i} @st;
return undef unless grep {$_->{sql} =~/delete/i} @st;
return 1;
}
sub sql_has_insert_only{
my @st = @_;
return undef if grep {$_->{sql} =~ /update|delete/i} @st;
return undef unless grep {$_->{sql} =~/insert/i} @st;
return 1;
}
sub sql_has_insert_and_update{
my @st = @_;
return undef unless grep {$_->{sql} =~ /insert/i} @st;
return undef unless grep {$_->{sql} =~ /update/i} @st;
return 1;
}
sub sql_has_update_and_delete{
my @st = @_;
return undef unless grep {my $val = $_; $val->{sql} =~ /delete/i} @st;
return undef unless grep {my $val = $_; $val->{sql} =~ /update/i} @st;
return 1;
}
sub setup_classes_and_db {
my $dbh = URT::DataSource::CircFk->get_default_handle;
ok($dbh, 'Got DB handle');
ok( $dbh->do("create table circular (id integer primary key, parent_id integer REFERENCES circular(id))"),
'Created circular table');
ok( $dbh->do("create table left (id integer, right_id integer REFERENCES right(id), right_id2 integer REFERENCES right(id), primary key (id, right_id))"),
'Created left table');
ok( $dbh->do("create table right (id integer primary key, left_id integer REFERENCES left(id), left_id2 integer REFERENCES left(id))"),
'Created right table');
ok( $dbh->do("create table alpha (id integer primary key, beta_id integer REFERENCES beta(id))"),
'Created table alpha');
ok( $dbh->do("create table beta (id integer primary key, gamma_id integer REFERENCES gamma(id))"),
'Created table beta');
ok( $dbh->do("create table gamma (id integer primary key, type varchar)"),
'Created table gamma');
ok( $dbh->do("create table bridge (left_id integer REFERENCES left(id), right_id integer REFERENCES right(id), primary key (left_id, right_id))"),
'Created table bridge');
my $ins_circular = $dbh->prepare("insert into circular (id, parent_id) values (?,?)");
foreach my $row ( [1, 5], [2, 1], [3, 2], [4, 3], [5, 4] ) {
ok( $ins_circular->execute(@$row), 'Inserted into circular' );
}
$ins_circular->finish;
my $ins_left = $dbh->prepare("insert into left (id, right_id, right_id2) values (?,?,?)");
my $ins_right = $dbh->prepare("insert into right (id, left_id, left_id2) values (?,?,?)");
foreach my $row ( ( [1,1,2], [2,2,3], [3,3,4], [4,4,5], [5,5,6]) ) {
ok( $ins_left->execute(@$row), 'Inserted into left');
ok( $ins_right->execute(@$row), 'Inserted into right');
}
my $ins_bridge_left = $dbh->prepare("insert into left(id) values (?)");
$ins_bridge_left->execute(10);
my $ins_bridge_right = $dbh->prepare("insert into right(id) values (?)");
my $ins_bridge = $dbh->prepare("insert into bridge(left_id, right_id) values (?, ?)");
for (11..15){
$ins_bridge_right->execute($_);
$ins_bridge->execute(10, $_);
}
$ins_bridge->finish;
$ins_bridge_right->finish;
$ins_bridge_left->finish;
$ins_left->finish;
$ins_right->finish;
my $ins_alpha = $dbh->prepare("insert into alpha(id, beta_id) values(?,?)");
ok($ins_alpha->execute(100,200), 'inserted into alpha');
$ins_alpha->finish;
my $ins_beta = $dbh->prepare("insert into beta(id, gamma_id) values(?,?)");
ok($ins_beta->execute(200, 300), 'inserted into beta');
$ins_beta->finish;
my $ins_gamma = $dbh->prepare("insert into gamma(id, type) values(?,?)");
ok($ins_gamma->execute(300, 'test'), 'inserted into gamma');
$ins_gamma->finish;
ok($dbh->commit(), 'DB commit');
ok(UR::Object::Type->define(
class_name => 'URT::Circular',
id_by => [
id => { is => 'Integer' },
],
has_optional => [
parent_id => { is => 'Integer'},
parent => {is => 'URT::Circular', id_by => 'parent_id'}
],
data_source => 'URT::DataSource::CircFk',
table_name => 'circular',
), 'Defined URT::Circular class');
ok(UR::Object::Type->define(
class_name => 'URT::Left',
id_by => [
id => { is => 'Integer'}
],
has_optional => [
right_id => { is => 'Integer' },
right => { is => 'URT::Right', id_by => 'right_id'},
],
data_source => 'URT::DataSource::CircFk',
table_name => 'left',
), 'Defined URT::Left class');
ok(UR::Object::Type->define(
class_name => 'URT::Right',
id_by => [
id => { is => 'Integer'}
],
has_optional => [
left_id => { is => 'Integer' },
left => { is => 'URT::Left', id_by => 'left_id'},
],
data_source => 'URT::DataSource::CircFk',
table_name => 'right',
), 'Defined URT::Right class');
ok(UR::Object::Type->define(
class_name => 'URT::Alpha',
id_by => [
id => {is => 'Integer'}
],
has_optional => [
beta_id => { is => 'Integer' },
beta => { is => 'URT::Beta', id_by => 'beta_id'},
],
data_source => 'URT::DataSource::CircFk',
table_name => 'alpha',
), 'Defined URT::Alpha class');
ok(UR::Object::Type->define(
class_name => 'URT::Beta',
id_by => [
id => {is => 'Integer'}
],
has_optional => [
gamma_id => { is => 'Integer' },
gamma => { is => 'URT::Gamma', id_by => 'gamma_id'},
],
data_source => 'URT::DataSource::CircFk',
table_name => 'beta',
), 'Defined URT::Beta class');
ok(UR::Object::Type->define(
class_name => 'URT::Gamma',
id_by => [
id => {is => 'Integer'}
],
has => [
type => { is => 'Text' },
],
data_source => 'URT::DataSource::CircFk',
table_name => 'gamma',
), 'Defined URT::Alpha class');
ok(UR::Object::Type->define(
class_name => 'URT::Bridge',
id_by => [
left_id => {is => 'Integer'},
right_id => {is => 'Integer'}
],
data_source => 'URT::DataSource::CircFk',
table_name => 'bridge',
), 'Defined URT::Bridge class');
}