#!/usr/bin/perl -w
use strict;
use File::Spec;
use File::Path;
use FindBin qw($Bin);
use Test::More tests => 1 + (5 * 18);
BEGIN
{
require 't/test-lib.pl';
use_ok('Rose::DB::Object::Loader');
}
our %Have;
our @Tables = qw(vendors products prices colors product_color_map);
our $Include_Tables = join('|', @Tables);
our $Module_Dir = File::Spec->catfile($Bin, 'loader_lib');
SETUP:
{
File::Path::rmtree($Module_Dir) if(-d $Module_Dir);
unless(-d $Module_Dir)
{
mkdir($Module_Dir);
unless(-d $Module_Dir)
{
die "Could not mkdir($Module_Dir) - $!";
}
}
unshift(@INC, $Module_Dir);
}
#
# Tests
#
# We'll need to clear the registry since we're using DSN instead
our $real_registry = Rose::DB->registry;
our $empty_registry = Rose::DB::Registry->new;
my $i = 1;
my $BC_Counter = 1;
foreach my $db_type (qw(mysql pg_with_schema pg informix sqlite))
{
SKIP:
{
skip("$db_type tests", 18) unless($Have{$db_type});
}
next unless($Have{$db_type});
$i++;
Rose::DB->registry($real_registry);
Rose::DB::Object::Metadata->unregister_all_classes;
my $class_prefix = ucfirst($db_type eq 'pg_with_schema' ? 'pgws' : $db_type);
#$Rose::DB::Object::Metadata::Debug = 1;
my $db = Rose::DB->new($db_type);
my $loader =
Rose::DB::Object::Loader->new(
db_dsn => $db->dsn,
db_schema => $db->schema,
db_username => $db->username,
db_password => $db->password,
class_prefix => $class_prefix);
Rose::DB->registry($empty_registry);
my @classes = $loader->make_modules(include_tables => $Include_Tables,
module_dir => $Module_Dir);
if($db_type eq 'pg')
{
is(Pg::Color->meta->column('id')->perl_hash_definition,
q(id => { type => 'bigserial', not_null => 1 }),
"bigserial perl_hash_definition 1 - $db_type");
is(Pg::Price->meta->column('id')->perl_hash_definition,
q(id => { type => 'serial', not_null => 1 }),
"bigserial perl_hash_definition 2 - $db_type");
is(Pg::Product->meta->column('date_created')->type, 'timestamp',
"tough default 1 - $db_type");
is(Pg::Product->meta->column('date_created')->default, 'now',
"tough default 2 - $db_type");
}
elsif($db_type eq 'pg_with_schema')
{
is(Pgws::Color->meta->column('id')->perl_hash_definition,
q(id => { type => 'bigserial', not_null => 1 }),
"bigserial perl_hash_definition 1 - $db_type");
is(Pgws::Price->meta->column('id')->perl_hash_definition,
q(id => { type => 'serial', not_null => 1 }),
"bigserial perl_hash_definition 2 - $db_type");
is(Pgws::Product->meta->column('date_created')->type, 'timestamp',
"tough default 1 - $db_type");
is(Pgws::Product->meta->column('date_created')->default, 'now',
"tough default 2 - $db_type");
}
else
{
SKIP:
{
skip('Pg serial tests', 4);
}
}
foreach my $class (@classes, map { $class_prefix . "::$_" }
('DB::AutoBase' . $BC_Counter,
'DB::Object::AutoBase' . ($BC_Counter + 1)))
{
my @path = split('::', $class);
$path[-1] .= '.pm';
my $file = File::Spec->catfile($Module_Dir, @path);
die "Missing $file" unless(-e $file, "make_modules() $class");
}
$BC_Counter += 2;
my $product_class = $class_prefix . '::Product';
##
## Run tests
##
my $p = $product_class->new(name => "Sled $i");
#ok($p->db->class =~ /^${class_prefix}::DB::AutoBase\d+$/, "db 1 - $db_type");
ok($p->isa('Rose::DB::Object'), "base class 1 - $db_type");
if($db_type eq 'pg_with_schema')
{
is($p->db->schema, lc 'Rose_db_object_private', "schema - $db_type");
}
else
{
ok(1, "schema - $db_type");
}
$p->vendor(name => "Acme $i");
$p->prices({ price => 1.25, region => 'US' },
{ price => 4.25, region => 'UK' });
$p->colors({ name => 'red' },
{ name => 'green' });
$p->save;
system($^X, '-I', $Module_Dir, "-M$product_class", '-e',
"\$p = $product_class->new(id => " . $p->id . ')->load;' .
'die "Wrong id" unless($p->name eq "' . $p->name . '");');
is($? >> 8, 0, "external load - $db_type");
$p = $product_class->new(id => $p->id)->load;
is($p->vendor->name, "Acme $i", "vendor 1 - $db_type");
my @prices = sort { $a->price <=> $b->price } $p->prices;
is(scalar @prices, 2, "prices 1 - $db_type");
is($prices[0]->price, 1.25, "prices 2 - $db_type");
is($prices[1]->price, 4.25, "prices 3 - $db_type");
my @colors = sort { $a->name cmp $b->name } $p->colors;
is(scalar @colors, 2, "colors 1 - $db_type");
is($colors[0]->name, 'green', "colors 2 - $db_type");
is($colors[1]->name, 'red', "colors 3 - $db_type");
my $mgr_class = $class_prefix . '::Product::Manager';
my $prods = $mgr_class->get_products(query => [ id => $p->id ]);
is(ref $prods, 'ARRAY', "get_products 1 - $db_type");
is(@$prods, 1, "get_products 2 - $db_type");
is($prods->[0]->id, $p->id, "get_products 3 - $db_type");
#$DB::single = 1;
#$Rose::DB::Object::Debug = 1;
my $vc = $class_prefix . '::Vendor';
my $sql =
Rose::DB::Object::Manager->get_objects_sql(
object_class => $vc,
with_objects => [ 'products' ],
query => [ 'products.vendor_id' => undef ]);
if($db_type eq 'sqlite')
{
ok($sql =~ /WHERE \s+ t2\.vendor_id \s+ IS \s+ NULL \s+ ORDER \s+ BY/xi,
"spot-check SQL generation - $db_type");
}
else { ok(1, "skip spot-check SQL generation - $db_type") }
}
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 product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.product_color_map CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.products CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.vendors CASCADE');
$dbh->do('DROP SCHEMA Rose_db_object_private CASCADE');
$dbh->do('CREATE SCHEMA Rose_db_object_private');
}
$dbh->do(<<"EOF");
CREATE TABLE vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
vendor_id INT REFERENCES vendors (id),
status VARCHAR(128) NOT NULL DEFAULT 'inactive'
CHECK(status IN ('inactive', 'active', 'defunct')),
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
DEFAULT ('now'::text)::timestamp(0) WITHOUT TIME ZONE,
release_date TIMESTAMP,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE prices
(
id SERIAL NOT NULL PRIMARY KEY,
product_id INT NOT NULL REFERENCES products (id),
region CHAR(2) NOT NULL DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE colors
(
id SERIAL8 NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE product_color_map
(
product_id INT NOT NULL REFERENCES products (id),
color_id INT NOT NULL REFERENCES colors (id),
PRIMARY KEY(product_id, color_id)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
vendor_id INT REFERENCES Rose_db_object_private.vendors (id),
status VARCHAR(128) NOT NULL DEFAULT 'inactive'
CHECK(status IN ('inactive', 'active', 'defunct')),
date_created TIMESTAMP WITHOUT TIME ZONE NOT NULL
DEFAULT ('now'::text)::timestamp(0) WITHOUT TIME ZONE,
release_date TIMESTAMP,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.prices
(
id SERIAL NOT NULL PRIMARY KEY,
product_id INT NOT NULL REFERENCES Rose_db_object_private.products (id),
region CHAR(2) NOT NULL DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.colors
(
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE Rose_db_object_private.product_color_map
(
product_id INT NOT NULL REFERENCES Rose_db_object_private.products (id),
color_id INT NOT NULL REFERENCES Rose_db_object_private.colors (id),
PRIMARY KEY(product_id, color_id)
)
EOF
$dbh->disconnect;
}
#
# MySQL
#
eval
{
my $db = Rose::DB->new('mysql_admin');
$dbh = $db->retain_dbh or die Rose::DB->error;
die "MySQL version too old" unless($db->database_version >= 4_000_000);
# Drop existing tables, ignoring errors
{
local $dbh->{'RaiseError'} = 0;
local $dbh->{'PrintError'} = 0;
$dbh->do('DROP TABLE product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
}
# Foreign key stuff requires InnoDB support
$dbh->do(<<"EOF");
CREATE TABLE vendors
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
ENGINE=InnoDB
EOF
# MySQL will silently ignore the "ENGINE=InnoDB" part and create
# a MyISAM table instead. MySQL is evil! Now we have to manually
# check to make sure an InnoDB table was really created.
my $db_name = $db->database;
my $sth = $dbh->prepare("SHOW TABLE STATUS FROM `$db_name` LIKE ?");
$sth->execute('vendors');
my $info = $sth->fetchrow_hashref;
no warnings 'uninitialized';
unless(lc $info->{'Type'} eq 'innodb' || lc $info->{'Engine'} eq 'innodb')
{
die "Missing InnoDB support";
}
};
if(!$@ && $dbh)
{
$Have{'mysql'} = 1;
$dbh->do(<<"EOF");
CREATE TABLE products
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
vendor_id INT,
status VARCHAR(128) NOT NULL DEFAULT 'inactive'
CHECK(status IN ('inactive', 'active', 'defunct')),
date_created TIMESTAMP,
release_date TIMESTAMP,
UNIQUE(name),
INDEX(vendor_id),
FOREIGN KEY (vendor_id) REFERENCES vendors (id) ON DELETE NO ACTION ON UPDATE SET NULL
)
ENGINE=InnoDB
EOF
$dbh->do(<<"EOF");
CREATE TABLE prices
(
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
region CHAR(2) NOT NULL DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region),
INDEX(product_id),
FOREIGN KEY (product_id) REFERENCES products (id)
)
ENGINE=InnoDB
EOF
$dbh->do(<<"EOF");
CREATE TABLE colors
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
ENGINE=InnoDB
EOF
$dbh->do(<<"EOF");
CREATE TABLE product_color_map
(
product_id INT NOT NULL,
color_id INT NOT NULL,
PRIMARY KEY(product_id, color_id),
INDEX(color_id),
INDEX(product_id),
FOREIGN KEY (product_id) REFERENCES products (id),
FOREIGN KEY (color_id) REFERENCES colors (id)
)
ENGINE=InnoDB
EOF
$dbh->disconnect;
}
#
# Informix
#
eval
{
$dbh = Rose::DB->new('informix_admin')->retain_dbh()
or die Rose::DB->error;
};
if(!$@ && $dbh)
{
$Have{'informix'} = 1;
# Drop existing tables, ignoring errors
{
local $dbh->{'RaiseError'} = 0;
local $dbh->{'PrintError'} = 0;
$dbh->do('DROP TABLE product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
}
$dbh->do(<<"EOF");
CREATE TABLE vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
vendor_id INT REFERENCES vendors (id),
status VARCHAR(128) DEFAULT 'inactive' NOT NULL
CHECK(status IN ('inactive', 'active', 'defunct')),
date_created DATETIME YEAR TO SECOND,
release_date DATETIME YEAR TO SECOND,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE prices
(
id SERIAL NOT NULL PRIMARY KEY,
product_id INT NOT NULL REFERENCES products (id),
region CHAR(2) DEFAULT 'US' NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
UNIQUE(product_id, region)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE colors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE product_color_map
(
product_id INT NOT NULL REFERENCES products (id),
color_id INT NOT NULL REFERENCES colors (id),
PRIMARY KEY(product_id, color_id)
)
EOF
$dbh->disconnect;
}
#
# SQLite
#
eval
{
$dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
or die Rose::DB->error;
};
if(!$@ && $dbh)
{
$Have{'sqlite'} = 1;
# Drop existing tables, ignoring errors
{
local $dbh->{'RaiseError'} = 0;
local $dbh->{'PrintError'} = 0;
$dbh->do('DROP TABLE product_color_map');
$dbh->do('DROP TABLE colors');
$dbh->do('DROP TABLE prices');
$dbh->do('DROP TABLE products');
$dbh->do('DROP TABLE vendors');
}
$dbh->do(<<"EOF");
CREATE TABLE vendors
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE products
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
vendor_id INT REFERENCES vendors (id),
status VARCHAR(128) DEFAULT 'inactive' NOT NULL
CHECK(status IN ('inactive', 'active', 'defunct')),
date_created DATETIME,
release_date DATETIME,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE prices
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INT NOT NULL REFERENCES products (id),
region CHAR(2) DEFAULT 'US' NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
UNIQUE(product_id, region)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE colors
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh->do(<<"EOF");
CREATE TABLE product_color_map
(
product_id INT NOT NULL REFERENCES products (id),
color_id INT NOT NULL REFERENCES colors (id),
PRIMARY KEY(product_id, color_id)
)
EOF
$dbh->disconnect;
}
}
END
{
File::Path::rmtree($Module_Dir) if(-d $Module_Dir);
# Delete test tables
Rose::DB->registry($real_registry);
if($Have{'pg'})
{
# PostgreSQL
my $dbh = Rose::DB->new('pg_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.product_color_map CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.colors CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.prices CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.products CASCADE');
$dbh->do('DROP TABLE Rose_db_object_private.vendors 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 product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
$dbh->disconnect;
}
if($Have{'informix'})
{
# Informix
my $dbh = Rose::DB->new('informix_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE product_color_map CASCADE');
$dbh->do('DROP TABLE colors CASCADE');
$dbh->do('DROP TABLE prices CASCADE');
$dbh->do('DROP TABLE products CASCADE');
$dbh->do('DROP TABLE vendors CASCADE');
$dbh->disconnect;
}
if($Have{'sqlite'})
{
# Informix
my $dbh = Rose::DB->new('sqlite_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE product_color_map');
$dbh->do('DROP TABLE colors');
$dbh->do('DROP TABLE prices');
$dbh->do('DROP TABLE products');
$dbh->do('DROP TABLE vendors');
$dbh->disconnect;
}
}