#!/usr/bin/perl -w
use strict;
use Test::More tests => 33;
BEGIN
{
require 't/test-lib.pl';
use_ok('Rose::DB::Object');
}
our %Have;
#
# Tests
#
#$Rose::DB::Object::Manager::Debug = 1;
SKIP:
{
skip("migration tests", 32) unless($Have{'pg'} && $Have{'mysql'});
#$DB::single = 1;
#$Rose::DB::Object::Debug = 1;
my $db_pg = Rose::DB->new('pg');
my $db_ws = Rose::DB->new('pg_with_schema');
my $db_my = Rose::DB->new('mysql');
my $a1 = Album->new(id => 1, db => $db_pg, name => 'One', year => 2001, dt => '1/2/2003 4:56:12')->save;
my $a2 = Album->new(id => 2, db => $db_pg, name => 'Two', year => 2002, dt => '2/2/2003 4:56:12')->save;
my $a3 = Album->new(id => 1, db => $db_ws, name => 'OneWS', year => 2003, dt => '3/2/2003 4:56:12')->save;
my $a4 = Album->new(id => 2, db => $db_my, name => 'TwoMy', year => 2004, dt => '4/2/2003 4:56:12')->save;
# pg -> pg with schema
$a2->db($db_ws);
$a2->delete;
$a2->save;
$a2 = Album->new(id => 2, db => $db_ws)->load;
is($a2->name, 'Two', 'pg -> pg with schema');
# pg with schema -> pg
$a3->db($db_pg);
$a3->save;
$a3 = Album->new(id => 1, db => $db_pg)->load;
is($a3->name, 'OneWS', 'pg with schema -> pg');
$a1 = Album->new(id => 1, db => $db_pg)->load;
$a2 = Album->new(id => 2, db => $db_pg)->load;
$a3 = Album->new(id => 1, db => $db_ws)->load;
$a4 = Album->new(id => 2, db => $db_my)->load;
# pg -> mysql
$a2->db($db_my);
$a2->delete;
$a2->save;
$a2 = Album->new(id => 2, db => $db_my)->load;
is($a2->name, 'Two', 'pg -> mysql');
# pg with schema -> mysql
$a3->db($db_my);
$a3->delete;
$a3->save;
$a3 = Album->new(id => 1, db => $db_my)->load;
is($a3->name, 'OneWS', 'pg with schema -> mysql 1');
is($a3->dt->month, 3, 'pg with schema -> mysql 2');
$a1 = Album->new(id => 1, db => $db_pg)->load;
$a2 = Album->new(id => 2, db => $db_pg)->load;
$a3 = Album->new(id => 1, db => $db_ws)->load;
$a4 = Album->new(id => 2, db => $db_my)->load;
$a4->name('TwoMy');
$a4->save;
# mysql -> pg
$a4->db($db_pg);
$a4->save;
$a4 = Album->new(id => 2, db => $db_my)->load;
is($a4->name, 'TwoMy', 'mysql -> pg');
# mysql -> pg with schema
$a4 = Album->new(id => 2, db => $db_my)->load;
$a4->db($db_ws);
$a4->save;
$a4 = Album->new(id => 2, db => $db_ws)->load;
is($a4->name, 'TwoMy', 'mysql -> pg with schema');
$a1 = Album->new(id => 1, db => $db_pg)->load;
$a2 = Album->new(id => 2, db => $db_pg)->load;
$a3 = Album->new(id => 1, db => $db_ws)->load;
$a4 = Album->new(id => 2, db => $db_my)->load;
is($a1->dt->month, 3, 'dt check 1');
is($a2->dt->month, 2, 'dt check 2');
is($a3->dt->month, 3, 'dt check 2');
is($a4->dt->month, 2, 'dt check 3');
#
# Test with schema override
#
# Rose::DB::MySQL currently supports schema as a stand-in for database.
# We need to turn that off for this test because we don't control the
# database(s) the test suite runs against.
Rose::DB::MySQL->supports_schema(0);
$a1 = AlbumWS->new(id => 10, db => $db_pg, name => 'Ten', year => 2001, dt => '1/2/2003 4:56:12')->save;
$a2 = AlbumWS->new(id => 20, db => $db_pg, name => 'Twe', year => 2002, dt => '2/2/2003 4:56:12')->save;
$a3 = AlbumWS->new(id => 30, db => $db_ws, name => 'Thi', year => 2003, dt => '3/2/2003 4:56:12')->save;
$a4 = AlbumWS->new(id => 40, db => $db_my, name => 'For', year => 2004, dt => '4/2/2003 4:56:12')->save;
$a1->db($db_my);
$a1->save(insert => 1);
$a1 = AlbumWS->new(id => 10, db => $db_my)->load;
is($a1->name, 'Ten', 'pg forced schema -> mysql 1');
is($a1->dt->month, 1, 'pg forced schema -> mysql 2');
$a2->db($db_my);
$a2->save(insert => 1);
$a2 = AlbumWS->new(id => 20, db => $db_my)->load;
is($a2->name, 'Twe', 'pg forced schema -> mysql 3');
is($a2->dt->month, 2, 'pg forced schema -> mysql 4');
$a3->db($db_my);
$a3->save(insert => 1);
$a3 = AlbumWS->new(id => 30, db => $db_my)->load;
is($a3->name, 'Thi', 'pg forced schema -> mysql 5');
is($a3->dt->month, 3, 'pg forced schema -> mysql 6');
$a4->db($db_pg);
$a4->save(insert => 1);
$a4 = AlbumWS->new(id => 40, db => $db_ws)->load;
is($a4->name, 'For', 'mysql -> pg forced schema 7');
is($a4->dt->month, 4, 'pg forced schema -> mysql 8');
#
# Test multi-pk with sequences
#
$a1 = Code->new(name => 'One', db => $db_pg, id2 => 2)->save;
$a2 = Code->new(name => 'Two', db => $db_ws, id2 => 3)->save;
$a3 = Code->new(name => 'Thr', db => $db_my, id2 => 5, id3 => 6)->save;
is($a1->id1, 1, 'multi-pk check pk 1');
is($a1->id2, 2, 'multi-pk check pk 2');
is($a1->id3, 1, 'multi-pk check pk 3');
is($a2->id1, 1, 'multi-pk check pk 4');
is($a2->id2, 3, 'multi-pk check pk 5');
is($a2->id3, 2, 'multi-pk check pk 6');
is($a3->id1, 1, 'multi-pk check pk 7');
is($a3->id2, 5, 'multi-pk check pk 8');
is($a3->id3, 6, 'multi-pk check pk 9');
# pg -> mysql
$a1->db($db_my);
$a1->delete;
$a1->save;
$a1 = Code->new(id1 => 1, id2 => 2, id3 => 1)->load;
is($a1->name, 'One', 'multi-pk pg -> mysql');
# pg with schema -> mysql
$a2->db($db_my);
$a2->save(insert => 1);
$a2 = Code->new(id1 => 1, id2 => 3, id3 => 2, db => $db_my)->load;
is($a2->name, 'Two', 'multi-pk pg with schema -> mysql');
# mysql -> pg
$a3->db($db_pg);
$a3->save(insert => 1);
$a3 = Code->new(id1 => 1, id2 => 5, id3 => 6, db => $db_pg)->load;
is($a3->name, 'Thr', 'multi-pk mysql -> pg');
# mysql -> pg with schema
$a3->db($db_ws);
$a3->save(insert => 1);
$a3 = Code->new(id1 => 1, id2 => 5, id3 => 6, db => $db_ws)->load;
is($a3->name, 'Thr', 'multi-pk mysql -> pg with schema');
}
BEGIN
{
our %Have;
#
# PostgreSQL
#
my $dbh;
eval
{
$dbh = Rose::DB->new('pg_admin')->retain_dbh()
or die Rose::DB->error;
};
if(!$@ && $dbh)
{
$Have{'pg'} = 1;
$Have{'pg_with_schema'} = 1;
# Drop existing tables and create schema, ignoring errors
{
local $dbh->{'RaiseError'} = 0;
local $dbh->{'PrintError'} = 0;
$dbh->do('DROP TABLE Rose_db_object_private.rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_codes CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.rdbo_codes CASCADE');
$dbh->do('DROP SEQUENCE Rose_db_object_private.rdbo_seq CASCADE');
$dbh->do('CREATE SCHEMA Rose_db_object_private');
}
$dbh->do(<<"EOF");
CREATE TABLE rdbo_albums
(
id SERIAL PRIMARY KEY,
name VARCHAR(32) UNIQUE,
artist VARCHAR(32),
year INTEGER,
dt TIMESTAMP
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.rdbo_albums
(
id SERIAL PRIMARY KEY,
name VARCHAR(32) UNIQUE,
artist VARCHAR(32),
year INTEGER,
dt TIMESTAMP
)
EOF
$dbh->do('CREATE SEQUENCE Rose_db_object_private.rdbo_seq');
$dbh->do(<<"EOF");
CREATE TABLE rdbo_codes
(
id1 SERIAL NOT NULL,
id2 INT NOT NULL,
id3 INT NOT NULL DEFAULT nextval('Rose_db_object_private.rdbo_seq'),
name VARCHAR(32) UNIQUE,
PRIMARY KEY(id1, id2, id3)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.rdbo_codes
(
id1 SERIAL NOT NULL,
id2 INT NOT NULL,
id3 INT NOT NULL DEFAULT nextval('Rose_db_object_private.rdbo_seq'),
name VARCHAR(32) UNIQUE,
PRIMARY KEY(id1, id2, id3)
)
EOF
$dbh->disconnect;
Rose::DB->default_type('pg');
package Album;
our @ISA = qw(Rose::DB::Object);
Album->meta->table('rdbo_albums');
Album->meta->auto_initialize;
package AlbumWS;
our @ISA = qw(Rose::DB::Object);
AlbumWS->meta->table('rdbo_albums');
AlbumWS->meta->schema('Rose_db_object_private');
AlbumWS->meta->auto_initialize;
package Code;
our @ISA = qw(Rose::DB::Object);
Code->meta->table('rdbo_codes');
Code->meta->auto_initialize;
}
#
# MySQL
#
eval
{
$dbh = Rose::DB->new('mysql_admin')->retain_dbh()
or die Rose::DB->error;
};
if(!$@ && $dbh)
{
$Have{'mysql'} = 1;
# Drop existing tables and create schema, ignoring errors
{
local $dbh->{'RaiseError'} = 0;
local $dbh->{'PrintError'} = 0;
$dbh->do('DROP TABLE rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_codes CASCADE');
}
$dbh->do(<<"EOF");
CREATE TABLE rdbo_albums
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) UNIQUE,
artist VARCHAR(32),
year INTEGER,
dt TIMESTAMP
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE rdbo_codes
(
id1 INT NOT NULL AUTO_INCREMENT,
id2 INT NOT NULL,
id3 INT NOT NULL,
name VARCHAR(32) UNIQUE,
PRIMARY KEY(id1, id2, id3)
)
EOF
$dbh->disconnect;
}
}
END
{
# Delete test tables
if($Have{'pg'})
{
# PostgreSQL
my $dbh = Rose::DB->new('pg_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE Rose_db_object_private.rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_codes CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.rdbo_codes CASCADE');
$dbh->do('DROP SEQUENCE Rose_db_object_private.rdbo_seq CASCADE');
$dbh->do('DROP SCHEMA Rose_db_object_private CASCADE');
$dbh->disconnect;
}
if($Have{'mysql'})
{
# MySQL
my $dbh = Rose::DB->new('mysql_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE rdbo_albums CASCADE');
$dbh->do('DROP TABLE rdbo_codes CASCADE');
$dbh->disconnect;
}
}