#!/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; } }