#!/usr/bin/perl -w
use strict;
use Test::More tests => 2 + (6 * 1);
BEGIN
{
require 't/test-lib.pl';
use_ok('Rose::DB::Object::Loader');
use_ok('Rose::DB::Object::Helpers');
}
our %Have;
#
# Tests
#
#$Rose::DB::Object::Manager::Debug = 1;
foreach my $db_type (qw(pg))
{
SKIP:
{
skip("$db_type tests", 6) unless($Have{$db_type});
}
next unless($Have{$db_type});
Rose::DB->default_type($db_type);
my $class_prefix = ucfirst($db_type);
my $loader =
Rose::DB::Object::Loader->new(
db => Rose::DB->new,
class_prefix => $class_prefix);
my @classes =
$loader->make_classes(include_tables =>
[ qw(offering_levels offering_sequences offerings employee employer) ],
include_map_class_relationships => 1);
#foreach my $class (@classes)
#{
# print $class->meta->perl_class_definition if($class->can('meta'));
#}
my $employer_class = $class_prefix . '::Employer';
my $offering_class = $class_prefix . '::Offering';
my $offering_sequence_class = $class_prefix . '::OfferingSequence';
my $offering_level_class = $class_prefix . '::OfferingLevel';
$employer_class->meta->column('data')->lazy(1);
$employer_class->meta->column('data')->make_methods(replace_existing => 1);
Rose::DB::Object::Manager->update_objects(
set => { name => { sql => 'upper(name)' } },
where => [ name => 'Default Employer' ],
object_class => $employer_class);
Rose::DB::Object::Manager->update_objects(
set => { name => \q(name || 'x') },
where => [ name => 'DEFAULT EMPLOYER' ],
object_class => $employer_class);
my $employer = $employer_class->new(company_code => 'TEST', data => "\0\1x\2\3");
my @offerings =
(
{
sort_order => 1,
years => 05,
offering_sequences =>
[
{
offering_levels =>
[
{
catalog_level => 'C',
catalog_code => 'HZNO04',
}
],
sequence_number => 0,
years => 05,
eid => ''
}
]
},
{
sort_order => 2,
years => 10,
offering_sequences =>
[
{
offering_levels =>
[
{
catalog_level => 'E',
catalog_code => 'HZNO04',
}
],
sequence_number => 0,
years => 10,
eid => ''
}
]
},
{
sort_order => 3,
years => 15,
offering_sequences =>
[
{
offering_levels =>
[
{
catalog_level => 'H',
catalog_code => 'HZNO04',
}
],
sequence_number => 0,
years => 15,
eid => '',
}
]
},
{
sort_order => 5,
years => 25,
offering_sequences =>
[
{
offering_levels =>
[
{
catalog_level => 'P',
catalog_code => 'HZNO04',
}
],
sequence_number => 0,
years => 25,
eid => '',
}
]
}
);
$employer->add_offerings(\@offerings);
#$Rose::DB::Object::Debug = 1;
#$Rose::DB::Object::Manager::Debug = 1;
#$DB::single = 1;
$employer->save;
$employer = $employer_class->new(company_code => 'TEST')->load;
is($employer->{'data'}, undef, "lazy bytea 1 - $db_type");
is($employer->data, "\0\1x\2\3", "lazy bytea 2 - $db_type");
$employer->data("\0\4x\3\1");
$employer->save;
my $employers =
Rose::DB::Object::Manager->get_objects(
object_class => $employer_class,
sort_by => 'name');
is_deeply([ map { scalar $_->Rose::DB::Object::Helpers::column_value_pairs } @$employers ],
[
{
'name' => '',
'company_code' => 'TEST',
'data' => "\0\4x\3\1",
},
{
'name' => 'DEFAULT EMPLOYERx',
'company_code' => '',
'data' => undef,
}
],
"employer check - $db_type");
my $offerings =
Rose::DB::Object::Manager->get_objects(
object_class => $offering_class,
sort_by => [ 'company_code', 'sort_order', 'years' ]);
is_deeply([ map { scalar $_->Rose::DB::Object::Helpers::column_value_pairs } @$offerings ],
[
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '0',
'years' => '05',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '0',
'years' => '10',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '0',
'years' => '15',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '1',
'years' => '5',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '2',
'years' => '10',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '3',
'years' => '15',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'discrete_sequences' => 1,
'sort_order' => '5',
'years' => '25',
'eid' => '',
'company_code' => 'TEST'
}
],
"offering check - $db_type");
my $offering_sequences =
Rose::DB::Object::Manager->get_objects(
object_class => $offering_sequence_class,
sort_by => [ 'company_code', 'years' ]);
is_deeply([ map { scalar $_->Rose::DB::Object::Helpers::column_value_pairs } @$offering_sequences ],
[
{
'browse' => 1,
'sequence_number' => '0',
'years' => '05',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '10',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '15',
'eid' => '',
'company_code' => ''
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '10',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '15',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '25',
'eid' => '',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'years' => '5',
'eid' => '',
'company_code' => 'TEST'
}
],
"offering sequence check - $db_type");
my $offering_levels =
Rose::DB::Object::Manager->get_objects(
object_class => $offering_level_class,
sort_by => [ 'company_code', 'years' ]);
is_deeply([ map { scalar $_->Rose::DB::Object::Helpers::column_value_pairs } @$offering_levels ],
[
{
'browse' => 1,
'sequence_number' => '0',
'catalog_level' => 'E',
'years' => '10',
'eid' => '',
'catalog_code' => 'HZNO04',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'catalog_level' => 'H',
'years' => '15',
'eid' => '',
'catalog_code' => 'HZNO04',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'catalog_level' => 'P',
'years' => '25',
'eid' => '',
'catalog_code' => 'HZNO04',
'company_code' => 'TEST'
},
{
'browse' => 1,
'sequence_number' => '0',
'catalog_level' => 'C',
'years' => '5',
'eid' => '',
'catalog_code' => 'HZNO04',
'company_code' => 'TEST'
}
],
"offering level check - $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 offering_levels CASCADE');
$dbh->do('DROP TABLE offering_sequences CASCADE');
$dbh->do('DROP TABLE offerings CASCADE');
$dbh->do('DROP TABLE employee CASCADE');
$dbh->do('DROP TABLE employer CASCADE');
}
my @sql =
(
<<"EOF",
CREATE OR REPLACE FUNCTION add_default_employee() RETURNS "trigger"
AS '
BEGIN
IF NEW.company_code IS NOT NULL THEN
INSERT INTO
employee (company_code, eid)
VALUES (
NEW.company_code,
''''
);
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
EOF
<<"EOF",
CREATE TABLE employer
(
company_code VARCHAR(6) DEFAULT '' NOT NULL PRIMARY KEY,
name VARCHAR(128) DEFAULT '' NOT NULL,
data BYTEA
)
EOF
<<"EOF",
CREATE TRIGGER employer_default_employee
AFTER INSERT ON employer
FOR EACH ROW
EXECUTE PROCEDURE add_default_employee()
EOF
<<"EOF",
CREATE TABLE employee
(
company_code VARCHAR(6) DEFAULT '' NOT NULL,
eid VARCHAR(9) DEFAULT '' NOT NULL,
first_name VARCHAR(15) DEFAULT '' NOT NULL,
last_name VARCHAR(25) DEFAULT '' NOT NULL,
PRIMARY KEY (company_code, eid)
);
EOF
<<"EOF",
ALTER TABLE employee ADD CONSTRAINT fk_employee_company_code
FOREIGN KEY (company_code) REFERENCES employer(company_code)
ON UPDATE CASCADE ON DELETE RESTRICT
EOF
<<"EOF",
INSERT INTO employer (company_code, name) VALUES ('', 'Default Employer')
EOF
<<"EOF",
CREATE TABLE offerings
(
company_code VARCHAR(6) DEFAULT '' NOT NULL,
eid VARCHAR(9) DEFAULT '' NOT NULL,
years VARCHAR(2) DEFAULT '' NOT NULL,
sort_order SMALLINT DEFAULT 0 NOT NULL,
browse BOOLEAN DEFAULT true NOT NULL,
discrete_sequences BOOLEAN DEFAULT true NOT NULL,
PRIMARY KEY (company_code, eid, years)
)
EOF
<<"EOF",
ALTER TABLE offerings ADD CONSTRAINT fk_offering_employee
FOREIGN KEY (company_code, eid) REFERENCES employee(company_code, eid)
ON UPDATE CASCADE ON DELETE CASCADE
EOF
<<"EOF",
ALTER TABLE offerings ADD CONSTRAINT fk_offering_company_code
FOREIGN KEY (company_code) REFERENCES employer(company_code)
ON UPDATE CASCADE ON DELETE CASCADE
EOF
<<"EOF",
INSERT INTO offerings (company_code, eid, years) VALUES ('','','05')
EOF
<<"EOF",
INSERT INTO offerings (company_code, eid, years) VALUES ('','','10')
EOF
<<"EOF",
INSERT INTO offerings (company_code, eid, years) VALUES ('','','15')
EOF
<<"EOF",
CREATE TABLE offering_sequences
(
company_code VARCHAR(6) DEFAULT '' NOT NULL,
eid VARCHAR(9) DEFAULT '' NOT NULL,
years VARCHAR(2) DEFAULT '' NOT NULL,
sequence_number SMALLINT DEFAULT 0 NOT NULL,
browse BOOLEAN DEFAULT true NOT NULL,
PRIMARY KEY (company_code, eid, years, sequence_number)
)
EOF
<<"EOF",
ALTER TABLE offering_sequences ADD CONSTRAINT fk_offering_sequences
FOREIGN KEY (company_code, eid, years) REFERENCES offerings(company_code, eid, years)
ON UPDATE CASCADE ON DELETE CASCADE
EOF
<<"EOF",
INSERT INTO offering_sequences (company_code, eid, years, sequence_number) VALUES ('','','05',0)
EOF
<<"EOF",
INSERT INTO offering_sequences (company_code, eid, years, sequence_number) VALUES ('','','10',0)
EOF
<<"EOF",
INSERT INTO offering_sequences (company_code, eid, years, sequence_number) VALUES ('','','15',0)
EOF
<<"EOF",
CREATE TABLE offering_levels
(
company_code VARCHAR(6) DEFAULT '' NOT NULL,
eid VARCHAR(9) DEFAULT '' NOT NULL,
years VARCHAR(2) DEFAULT '' NOT NULL,
sequence_number SMALLINT DEFAULT 0 NOT NULL,
catalog_code VARCHAR(6) DEFAULT '' NOT NULL,
catalog_level VARCHAR(2) DEFAULT '' NOT NULL,
browse BOOLEAN DEFAULT true NOT NULL,
PRIMARY KEY (company_code, eid, years, sequence_number, catalog_code, catalog_level)
)
EOF
<<"EOF",
ALTER TABLE offering_levels ADD CONSTRAINT fk_offering_levels
FOREIGN KEY (company_code, eid, years, sequence_number)
REFERENCES offering_sequences(company_code, eid, years, sequence_number)
ON UPDATE CASCADE ON DELETE CASCADE
EOF
);
foreach my $sql (@sql)
{
local $dbh->{'PrintError'} = 0;
eval { $dbh->do($sql) };
if($@)
{
warn $@ unless($@ =~ /language "plpgsql" does not exist/);
$Have{'pg'} = 0;
$Have{'pg_with_schema'} = 0;
last;
}
}
$dbh->disconnect;
}
}
END
{
# Delete test table
if($Have{'pg'})
{
# PostgreSQL
my $dbh = Rose::DB->new('pg_admin')->retain_dbh()
or die Rose::DB->error;
$dbh->do('DROP TABLE offering_levels CASCADE');
$dbh->do('DROP TABLE offering_sequences CASCADE');
$dbh->do('DROP TABLE offerings CASCADE');
$dbh->do('DROP TABLE employee CASCADE');
$dbh->do('DROP TABLE employer CASCADE');
$dbh->disconnect;
}
}