#!/usr/bin/perl -w
my
$Have_Test_Differences
= $@ ? 0 : 1;
BEGIN
{
require
't/test-lib.pl'
;
use_ok(
'Rose::DB::Object::Loader'
);
use_ok(
'Rose::DB::Object::Helpers'
);
}
$Data::Dumper::Sortkeys
= 1;
our
(
%Have
,
$Have_YAML
,
$Have_JSON
);
if
(
defined
$ENV
{
'RDBO_NESTED_JOINS'
} && Rose::DB::Object::Manager->can(
'default_nested_joins'
))
{
Rose::DB::Object::Manager->default_nested_joins(
$ENV
{
'RDBO_NESTED_JOINS'
});
}
my
$Include
=
'^(?:'
.
join
(
'|'
,
qw(colors descriptions authors nicknames
description_author_map product_color_map
prices products vendors regions)
) .
')$'
;
$Include
=
qr($Include)
;
foreach
my
$db_type
(
qw(sqlite mysql pg pg_with_schema informix)
)
{
SKIP:
{
skip(
"$db_type tests"
, 28)
unless
(
$Have
{
$db_type
});
}
next
unless
(
$Have
{
$db_type
});
if
(
$Have_Test_Differences
)
{
if
(
$db_type
eq
'sqlite'
||
$db_type
=~ /^pg/)
{
no
warnings;
*is_deeply
= \
&Test::More::is_deeply
;
}
else
{
no
warnings;
*is_deeply
= \
&Test::Differences::eq_or_diff
;
}
}
Rose::DB->default_type(
$db_type
);
Rose::DB::Object::Metadata->unregister_all_classes;
my
$db
= Rose::DB->new;
my
$class_prefix
=
ucfirst
(
$db_type
eq
'pg_with_schema'
?
'pgws'
:
$db_type
);
my
$loader
=
Rose::DB::Object::Loader->new(
db
=>
$db
,
class_prefix
=>
$class_prefix
);
my
@classes
=
$loader
->make_classes(
include_tables
=>
$Include
);
foreach
my
$class
(
@classes
)
{
next
unless
(
$class
->isa(
'Rose::DB::Object'
));
if
(
my
@rels
=
grep
{ !
$_
->is_singular }
$class
->meta->relationships)
{
foreach
my
$rel
(
@rels
)
{
if
(
$rel
->type eq
'many to many'
)
{
$rel
->manager_args({
sort_by
=>
't2.id'
});
}
else
{
$rel
->manager_args({
sort_by
=>
'id'
});
}
}
$class
->meta->make_relationship_methods(
replace_existing
=> 1);
}
}
my
$product_class
=
$class_prefix
.
'::Product'
;
my
$manager_class
=
$product_class
.
'::Manager'
;
Rose::DB::Object::Helpers->
import
(
-target_class
=>
$product_class
,
qw(as_tree new_from_tree new_from_deflated_tree init_with_tree traverse_depth_first)
);
if
(
$Have_JSON
)
{
Rose::DB::Object::Helpers->
import
(
-target_class
=>
$product_class
,
qw(as_json new_from_json init_with_json)
);
}
if
(
$Have_YAML
)
{
Rose::DB::Object::Helpers->
import
(
-target_class
=>
$product_class
,
qw(as_yaml new_from_yaml init_with_yaml)
);
}
my
$p1
=
$product_class
->new(
id
=> 1,
name
=>
'Kite'
,
sale_date
=>
'1/2/2005'
,
vendor
=> {
id
=> 1,
name
=>
'V1'
,
region
=> {
id
=>
'DE'
,
name
=>
'Germany'
} },
prices
=>
[
{
price
=> 1.25,
region
=> {
id
=>
'US'
,
name
=>
'America'
} },
{
price
=> 4.25,
region
=> {
id
=>
'DE'
,
name
=>
'Germany'
} },
],
colors
=>
[
{
name
=>
'red'
,
description
=>
{
text
=>
'desc 1'
,
authors
=>
[
{
name
=>
'john'
,
nicknames
=> [ {
nick
=>
'jack'
}, {
nick
=>
'sir'
} ],
},
{
name
=>
'sue'
,
nicknames
=> [ {
nick
=>
'sioux'
} ],
},
],
},
},
{
name
=>
'blue'
,
description
=>
{
text
=>
'desc 2'
,
authors
=>
[
{
name
=>
'john'
},
{
name
=>
'jane'
,
nicknames
=> [ {
nick
=>
'blub'
} ],
},
],
}
}
]);
$p1
->save;
my
$p2
=
$product_class
->new(
id
=> 2,
name
=>
'Sled'
,
sale_date
=>
'2/2/2005'
,
vendor
=> {
id
=> 2,
name
=>
'V2'
,
region_id
=>
'US'
,
vendor_id
=> 1 },
prices
=> [ {
price
=>
'5.25'
} ],
colors
=>
[
{
name
=>
'red'
},
{
name
=>
'green'
,
description
=>
{
text
=>
'desc 3'
,
authors
=> [ {
name
=>
'tim'
} ],
}
}
]);
$p2
->save;
my
$tree
=
$p2
->as_tree;
my
$from_tree
=
$product_class
->new_from_deflated_tree(
$tree
);
is_deeply(
$tree
,
$from_tree
->as_tree,
"as_tree -> new_from_deflated_tree -> as_tree 1 - $db_type"
);
is_deeply(
$tree
,
$from_tree
->as_tree,
"as_tree -> new_from_deflated_tree -> as_tree 2 - $db_type"
);
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 0);
my
$check_tree
=
{
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'vendor_id'
=>
'2'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 0 - $db_type"
);
my
$new_from_deflated_tree
=
$product_class
->new_from_deflated_tree(
$tree
);
is_deeply(
$new_from_deflated_tree
->as_tree,
$check_tree
,
"new_from_deflated_tree 1 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 0);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree,
"new_from_json 1 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 0);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree,
"new_from_yaml 1 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 1);
$check_tree
=
{
'colors'
=>
[
{
'description_id'
=>
'1'
,
'id'
=>
'1'
,
'name'
=>
'red'
},
{
'description_id'
=>
'3'
,
'id'
=>
'3'
,
'name'
=>
'green'
}
],
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'prices'
=>
[
{
'id'
=>
'3'
,
'price'
=>
'5.25'
,
'product_id'
=>
'2'
,
'region_id'
=>
'US'
}
],
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor'
=>
{
'id'
=>
'2'
,
'name'
=>
'V2'
,
'region_id'
=>
'US'
,
'vendor_id'
=>
'1'
},
'vendor_id'
=>
'2'
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 1 - $db_type"
);
$new_from_deflated_tree
=
$product_class
->new_from_deflated_tree(
$tree
);
is_deeply(
$new_from_deflated_tree
->as_tree,
$check_tree
,
"new_from_deflated_tree 2 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 1);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree,
"new_from_json 2 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 1);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree,
"new_from_yaml 2 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 2);
$check_tree
=
{
'colors'
=>
[
{
'description'
=>
{
'id'
=>
'1'
,
'text'
=>
'desc 1'
},
'description_id'
=>
'1'
,
'id'
=>
'1'
,
'name'
=>
'red'
},
{
'description'
=>
{
'id'
=>
'3'
,
'text'
=>
'desc 3'
},
'description_id'
=>
'3'
,
'id'
=>
'3'
,
'name'
=>
'green'
}
],
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'prices'
=>
[
{
'id'
=>
'3'
,
'price'
=>
'5.25'
,
'product_id'
=>
'2'
,
'region'
=>
{
'id'
=>
'US'
,
'name'
=>
'America'
},
'region_id'
=>
'US'
}
],
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor'
=>
{
'id'
=>
'2'
,
'name'
=>
'V2'
,
'region_id'
=>
'US'
,
'vendor'
=>
{
'id'
=>
'1'
,
'name'
=>
'V1'
,
'region_id'
=>
'DE'
,
'vendor_id'
=>
undef
},
'vendor_id'
=>
'1'
,
'vendors'
=> []
},
'vendor_id'
=>
'2'
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 2 - $db_type"
);
my
$new_from_tree
=
$product_class
->new_from_deflated_tree(
$tree
);
is_deeply(
$new_from_tree
->as_tree,
$check_tree
,
"new_from_tree 3 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 2);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree,
"new_from_json 3 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 2);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree,
"new_from_yaml 3 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
$check_tree
=
{
'colors'
=>
[
{
'description'
=>
{
'id'
=>
'1'
,
'text'
=>
'desc 1'
},
'description_id'
=>
'1'
,
'id'
=>
'1'
,
'name'
=>
'red'
,
'products'
=>
[
{
'id'
=>
'1'
,
'name'
=>
'Kite'
,
'sale_date'
=>
'2005-01-02 00:00:00'
,
'vendor_id'
=>
'1'
},
{
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor_id'
=>
'2'
}
]
},
{
'description'
=>
{
'id'
=>
'3'
,
'text'
=>
'desc 3'
},
'description_id'
=>
'3'
,
'id'
=>
'3'
,
'name'
=>
'green'
,
'products'
=>
[
{
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor_id'
=>
'2'
}
]
}
],
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'prices'
=>
[
{
'id'
=>
'3'
,
'price'
=>
'5.25'
,
'product'
=>
{
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor_id'
=>
'2'
},
'product_id'
=>
'2'
,
'region'
=>
{
'id'
=>
'US'
,
'name'
=>
'America'
},
'region_id'
=>
'US'
}
],
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor'
=>
{
'id'
=>
'2'
,
'name'
=>
'V2'
,
'products'
=>
[
{
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor_id'
=>
'2'
}
],
'region'
=>
{
'id'
=>
'US'
,
'name'
=>
'America'
},
'region_id'
=>
'US'
,
'vendor'
=>
{
'id'
=>
'1'
,
'name'
=>
'V1'
,
'region_id'
=>
'DE'
,
'vendor_id'
=>
undef
},
'vendor_id'
=>
'1'
,
'vendors'
=> []
},
'vendor_id'
=>
'2'
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 2, allow_loops => 1 - $db_type"
);
$new_from_tree
=
$product_class
->new_from_tree(
$tree
);
is_deeply(
$new_from_tree
->as_tree(
allow_loops
=> 1),
$check_tree
,
"new_from_tree 4 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree(
allow_loops
=> 1),
"new_from_json 4 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree(
allow_loops
=> 1),
"new_from_yaml 4 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ });
$check_tree
=
{
'colors'
=>
[
{
'description'
=>
{
'id'
=>
'1'
,
'text'
=>
'desc 1'
},
'description_id'
=>
'1'
,
'id'
=>
'1'
,
'name'
=>
'red'
,
},
{
'description'
=>
{
'id'
=>
'3'
,
'text'
=>
'desc 3'
},
'description_id'
=>
'3'
,
'id'
=>
'3'
,
'name'
=>
'green'
,
}
],
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor'
=>
{
'id'
=>
'2'
,
'name'
=>
'V2'
,
'region'
=>
{
'id'
=>
'US'
,
'name'
=>
'America'
},
'region_id'
=>
'US'
,
'vendor'
=>
{
'id'
=>
'1'
,
'name'
=>
'V1'
,
'region_id'
=>
'DE'
,
'vendor_id'
=>
undef
},
'vendor_id'
=>
'1'
,
'vendors'
=> []
},
'vendor_id'
=>
'2'
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 2, allow_loops => 1, /^p/ - $db_type"
);
$new_from_tree
=
$product_class
->new_from_tree(
$tree
);
is_deeply(
$new_from_tree
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ }),
$check_tree
,
"new_from_tree 5 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ }),
"new_from_json 5 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ }),
"new_from_yaml 5 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$tree
=
$product_class
->new(
id
=> 2)->as_tree(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ },
exclude
=>
sub
{
no
warnings;
shift
->id > 2 });
$check_tree
=
{
'colors'
=>
[
{
'description'
=>
{
'id'
=>
'1'
,
'text'
=>
'desc 1'
},
'description_id'
=>
'1'
,
'id'
=>
'1'
,
'name'
=>
'red'
,
},
],
'id'
=>
'2'
,
'name'
=>
'Sled'
,
'sale_date'
=>
'2005-02-02 00:00:00'
,
'vendor'
=>
{
'id'
=>
'2'
,
'name'
=>
'V2'
,
'region'
=>
{
'id'
=>
'US'
,
'name'
=>
'America'
},
'region_id'
=>
'US'
,
'vendor'
=>
{
'id'
=>
'1'
,
'name'
=>
'V1'
,
'region_id'
=>
'DE'
,
'vendor_id'
=>
undef
},
'vendor_id'
=>
'1'
,
'vendors'
=> []
},
'vendor_id'
=>
'2'
};
is_deeply(
$tree
,
$check_tree
,
"as_tree force, depth 2, allow_loops => 1, /^p/,id > 2 - $db_type"
);
$new_from_tree
=
$product_class
->new_from_tree(
$tree
);
is_deeply(
$new_from_tree
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ },
exclude
=>
sub
{
no
warnings;
shift
->id > 2 }),
$check_tree
,
"new_from_tree 6 - $db_type"
);
if
(
$Have_JSON
)
{
my
$json
=
$product_class
->new(
id
=> 2)->as_json(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_json
=
$product_class
->new_from_json(
$json
);
is_deeply(
$check_tree
,
$new_from_json
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ },
exclude
=>
sub
{
no
warnings;
shift
->id > 2 }),
"new_from_json 6 - $db_type"
);
}
else
{ SKIP: { skip(
'JSON tests'
, 1) } }
if
(
$Have_YAML
)
{
my
$yaml
=
$product_class
->new(
id
=> 2)->as_yaml(
force_load
=> 1,
max_depth
=> 2,
allow_loops
=> 1);
my
$new_from_yaml
=
$product_class
->new_from_yaml(
$yaml
);
is_deeply(
$check_tree
,
$new_from_yaml
->as_tree(
allow_loops
=> 1,
prune
=>
sub
{
shift
->name =~ /^p/ },
exclude
=>
sub
{
no
warnings;
shift
->id > 2 }),
"new_from_yaml 6 - $db_type"
);
}
else
{ SKIP: { skip(
'YAML tests'
, 1) } }
$product_class
->meta->add_nonpersistent_column(
other_date
=> {
type
=>
'datetime'
,
default
=> DateTime->new(
year
=> 2008,
month
=> 12,
day
=> 31) });
$product_class
->meta->make_nonpersistent_column_methods;
my
$p3
=
$product_class
->new(
id
=> 3,
name
=>
'Barn'
,
other_date
=>
'12/31/2007'
);
$check_tree
=
{
'id'
=> 3,
'name'
=>
'Barn'
,
'other_date'
=>
'2007-12-31 00:00:00'
,
'sale_date'
=>
undef
,
'vendor_id'
=>
undef
};
is_deeply(
$p3
->as_tree,
$check_tree
,
"nonpersistent columns 1 - $db_type"
);
$check_tree
=
{
'id'
=> 3,
'name'
=>
'Barn'
,
'sale_date'
=>
undef
,
'vendor_id'
=>
undef
};
is_deeply(
$p3
->as_tree(
persistent_columns_only
=> 1),
$check_tree
,
"nonpersistent columns 2 - $db_type"
);
}
INIT_WITH_TREE_BUG:
{
SKIP:
{
skip(
"init_with_tree() bug tests"
, 4)
unless
(
%Have
);
}
next
unless
(
%Have
);
__PACKAGE__->meta->setup
(
table
=>
'user'
,
columns
=>
[
id
=> {
type
=>
'bigserial'
,
not_null
=> 1 },
name
=> {
type
=>
'varchar'
,
length
=> 100,
not_null
=> 1 },
password
=> {
type
=>
'varchar'
,
length
=> 100,
not_null
=> 1 },
name_prefix
=> {
type
=>
'varchar'
,
length
=> 20 },
first_name
=> {
type
=>
'varchar'
,
length
=> 255 },
last_name
=> {
type
=>
'varchar'
,
length
=> 255 },
reseller_id
=> {
type
=>
'integer'
,
default
=> 0,
not_null
=> 1 },
created_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
updated_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
parent_user_id
=> {
type
=>
'bigint'
},
user_company_id
=> {
type
=>
'bigint'
},
company_name
=> {
type
=>
'varchar'
,
length
=> 255 },
owner_user_id
=> {
type
=>
'bigint'
},
user_title_id
=> {
type
=>
'bigint'
},
job_title
=> {
type
=>
'varchar'
,
length
=> 255 },
primary_user_company_id
=> {
type
=>
'bigint'
},
primary_user_title_id
=> {
type
=>
'bigint'
},
primary_user_phone_id
=> {
type
=>
'bigint'
},
primary_user_email_id
=> {
type
=>
'bigint'
},
primary_user_address_id
=> {
type
=>
'bigint'
},
commission_user_address_id
=> {
type
=>
'bigint'
},
user_source_id
=> {
type
=>
'integer'
},
updated_by_user_id
=> {
type
=>
'bigint'
},
locale_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
spoken_lang
=> {
type
=>
'enum'
,
check_in
=> [
'English'
,
'Mandarin'
,
'Cantonese'
],
default
=>
'English'
,
not_null
=> 1 },
encryption_key
=> {
type
=>
'character'
,
length
=> 32 },
timezone_id
=> {
type
=>
'integer'
,
default
=> 513,
not_null
=> 1 },
user_type_id
=> {
type
=>
'integer'
,
default
=> 0,
not_null
=> 1 },
primary_billing_method_id
=> {
type
=>
'bigint'
},
autodetect_timezone
=> {
type
=>
'integer'
,
default
=> 0,
not_null
=> 1 },
is_login_disabled
=> {
type
=>
'integer'
,
default
=> 0 },
security_question_id
=> {
type
=>
'integer'
,
default
=> 1,
not_null
=> 1 },
security_question_custom
=> {
type
=>
'varchar'
,
length
=> 255 },
security_answer
=> {
type
=>
'varchar'
,
length
=> 255 },
has_temporary_password
=> {
type
=>
'integer'
,
default
=> 0 },
email_id
=> {
type
=>
'bigint'
},
payment_failure_status
=> {
type
=>
'integer'
,
default
=> 0,
not_null
=> 1 },
notes
=> {
type
=>
'text'
,
length
=> 65535 },
],
primary_key_columns
=> [
'id'
],
unique_keys
=> [[
'email_id'
], [
'name'
],],
relationships
=>
[
user_addresses
=>
{
class
=>
'Project::Model::UserAddress'
,
column_map
=> {
id
=>
'user_id'
},
type
=>
'one to many'
,
},
user_emails
=>
{
class
=>
'Project::Model::UserEmail'
,
column_map
=> {
id
=>
'user_id'
},
type
=>
'one to many'
,
},
user_phones
=>
{
class
=>
'Project::Model::UserPhone'
,
column_map
=> {
id
=>
'user_id'
},
type
=>
'one to many'
,
},
],
);
__PACKAGE__->meta->setup
(
table
=>
'user_address'
,
columns
=>
[
id
=> {
type
=>
'bigserial'
,
not_null
=> 1 },
user_id
=> {
type
=>
'bigint'
,
not_null
=> 1 },
user_address_type_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
geo_country_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
address1
=> {
type
=>
'varchar'
,
length
=> 255 },
address2
=> {
type
=>
'varchar'
,
length
=> 255 },
address3
=> {
type
=>
'varchar'
,
length
=> 255 },
geo_subregion
=> {
type
=>
'varchar'
,
length
=> 255 },
geo_region_id
=> {
type
=>
'integer'
},
postal_code1
=> {
type
=>
'varchar'
,
length
=> 5 },
postal_code2
=> {
type
=>
'varchar'
,
length
=> 5 },
created_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
updated_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
],
primary_key_columns
=> [
'id'
],
relationships
=>
[
users
=>
{
class
=>
'IV::Model::User'
,
column_map
=> {
id
=>
'commission_user_address_id'
},
type
=>
'one to many'
,
},
],
);
__PACKAGE__->meta->setup
(
table
=>
'user_email'
,
columns
=>
[
id
=> {
type
=>
'bigserial'
,
not_null
=> 1 },
email
=> {
type
=>
'varchar'
,
length
=> 255,
not_null
=> 1 },
user_id
=> {
type
=>
'bigint'
,
not_null
=> 1 },
user_email_type_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
created_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
updated_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
],
primary_key_columns
=> [
'id'
],
unique_key
=> [
'user_id'
,
'email'
],
foreign_keys
=>
[
user
=>
{
class
=>
'Project::Model::User'
,
key_columns
=> {
user_id
=>
'id'
},
},
],
);
__PACKAGE__->meta->setup
(
table
=>
'user_phone'
,
columns
=>
[
id
=> {
type
=>
'bigserial'
,
not_null
=> 1 },
user_id
=> {
type
=>
'bigint'
,
not_null
=> 1 },
geo_country_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
area_code
=> {
type
=>
'varchar'
,
length
=> 4 },
number1
=> {
type
=>
'varchar'
,
length
=> 10 },
number2
=> {
type
=>
'varchar'
,
length
=> 10 },
extension
=> {
type
=>
'varchar'
,
length
=> 50 },
user_phone_type_id
=> {
type
=>
'integer'
,
not_null
=> 1 },
created_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
updated_at
=> {
type
=>
'datetime'
,
not_null
=> 1 },
],
primary_key_columns
=> [
'id'
],
foreign_keys
=>
[
user
=>
{
class
=>
'Project::Model::User'
,
key_columns
=> {
user_id
=>
'id'
},
},
],
);
my
$tree
=
{
'user_titles'
=> [],
'billing_invoices'
=> [],
'incident_external_departments'
=> [],
'salescalendar_user_calendars'
=> [],
'salescalendar_appointment_notes'
=> [],
'password'
=>
'$1$068F9leP$8jfRI43HMUS2/jxUsQTme.'
,
'incident_internal_departments'
=> [],
'user_title_id'
=>
undef
,
'reseller_id'
=>
'4'
,
'incidents_external_owned_by'
=> [],
'primary_billing_method_id'
=>
undef
,
'name'
=>
'sego03'
,
'timezone_id'
=>
'513'
,
'user_login_logs'
=> [],
'primary_user_email_id'
=>
'8061'
,
'updated_at'
=>
'2008-08-27 22:39:40'
,
'encryption_key'
=>
'37dcd1d8fc4555fd46f063fbb8e4f55b'
,
'security_answer'
=>
undef
,
'commission_user_address_id'
=>
undef
,
'job_title'
=>
''
,
'updated_by_user_id'
=>
undef
,
'salescalendar_appointments'
=> [],
'notes_entered_by'
=> [],
'created_at'
=>
'2008-07-10 00:31:58'
,
'owner_user_id'
=>
'5343'
,
'billing_methods'
=> [],
'autodetect_timezone'
=> 0,
'domains'
=> [],
'notes'
=>
undef
,
'user_company_id'
=>
undef
,
'user_source_id'
=>
'1'
,
'website'
=> {},
'primary_user_company_id'
=>
undef
,
'company_name'
=>
'myCompany'
,
'user_phones'
=>
[
{
'area_code'
=>
'888'
,
'extension'
=>
''
,
'created_at'
=>
'2008-07-10 00:31:58'
,
'number1'
=>
'8888'
,
'geo_country_id'
=>
'4'
,
'user_phone_type_id'
=>
'1'
,
'number2'
=>
'8888'
,
'id'
=>
'8399'
,
'user_id'
=>
'11647'
}
],
'primary_user_address_id'
=>
undef
,
'salescalendar_appointments_cancelled_by'
=> [],
'user_type_id'
=>
'6'
,
'id'
=>
'11647'
,
'password_confirm'
=>
'$1$068F9leP$8jfRI43HMUS2/jxUsQTme.'
,
'salescalendar_appointments_salesrep'
=> [],
'roles'
=> [],
'user_emails'
=>
[
{
'email'
=>
'test@test.com'
,
'created_at'
=>
'2008-07-10 00:31:58'
,
'user_email_type_id'
=>
'1'
,
'id'
=>
'8061'
,
'user_id'
=>
'11647'
}
],
'salescalendar_lockouts'
=> [],
'name_prefix'
=>
''
,
'user_companies'
=> [],
'payment_failure_status'
=> 0,
'locale_id'
=>
'8'
,
'parent_user_id'
=> 1,
'has_temporary_password'
=> 0,
'email_id'
=>
undef
,
'incidents_entered_by'
=> [],
'contact_website'
=> {},
'last_name'
=>
'sego03'
,
'is_login_disabled'
=> 0,
'security_question_id'
=>
'1'
,
'billing_schedules'
=> [],
'primary_user_title_id'
=>
undef
,
'updated_users'
=> [],
'primary_user_phone_id'
=>
'8399'
,
'spoken_lang'
=>
'English'
,
'incidents'
=> [],
'security_question_custom'
=>
undef
,
'incidents_internal_owned_by'
=> [],
'user_addresses'
=> [],
'child_users'
=> [],
'first_name'
=>
''
};
my
$user_archive
= init_with_tree(Project::Model::User->new,
$tree
);
is(
$user_archive
->id, 11647,
'init_with_tree() columns first bug 1'
);
is(
$user_archive
->user_emails->[0]->user_id, 11647,
'init_with_tree() columns first bug 2'
);
is(
$user_archive
->user_phones->[0]->user_id, 11647,
'init_with_tree() columns first bug 3'
);
$tree
= as_tree(
$user_archive
);
is(
$tree
->{
'user_phones'
}[0]{
'user_id'
}, 11647,
'as_tree() traverse fks'
);
}
BEGIN
{
our
(
$Have_YAML
,
$Have_JSON
);
$Have_YAML
= $@ ? 0 : 1;
eval
{
die
"JSON $JSON::VERSION too old"
unless
(
$JSON::VERSION
>= 2.00);
};
$Have_JSON
= $@ ? 0 : 1;
}
BEGIN
{
our
%Have
;
my
$dbh
;
eval
{
$dbh
= Rose::DB->new(
'pg_admin'
)->retain_dbh()
or
die
Rose::DB->error;
};
if
(!$@ &&
$dbh
&&
$DBD::Pg::VERSION
ge
'2.15.1'
)
{
$Have
{
'pg'
} = 1;
$Have
{
'pg_with_schema'
} = 1;
{
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 description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions 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.description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.descriptions 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 TABLE Rose_db_object_private.regions CASCADE'
);
$dbh
->
do
(
'CREATE SCHEMA Rose_db_object_private'
);
}
$dbh
->
do
(
<<"EOF");
CREATE TABLE regions
(
id CHAR(2) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
region_id CHAR(2) REFERENCES regions (id),
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
sale_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_id CHAR(2) NOT NULL REFERENCES regions (id) DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE descriptions
(
id SERIAL NOT NULL PRIMARY KEY,
text VARCHAR(255) NOT NULL,
UNIQUE(text)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE authors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE nicknames
(
id SERIAL NOT NULL PRIMARY KEY,
nick VARCHAR(255) NOT NULL,
author_id INT REFERENCES authors (id),
UNIQUE(nick, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE description_author_map
(
description_id INT NOT NULL REFERENCES descriptions (id),
author_id INT NOT NULL REFERENCES authors (id),
PRIMARY KEY(description_id, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE colors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description_id INT REFERENCES descriptions (id),
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.regions
(
id CHAR(2) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES Rose_db_object_private.vendors (id),
region_id CHAR(2) REFERENCES Rose_db_object_private.regions (id),
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES Rose_db_object_private.vendors (id),
sale_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_id CHAR(2) NOT NULL REFERENCES Rose_db_object_private.regions (id) DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.descriptions
(
id SERIAL NOT NULL PRIMARY KEY,
text VARCHAR(255) NOT NULL,
UNIQUE(text)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.authors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.nicknames
(
id SERIAL NOT NULL PRIMARY KEY,
nick VARCHAR(255) NOT NULL,
author_id INT REFERENCES Rose_db_object_private.authors (id),
UNIQUE(nick, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.description_author_map
(
description_id INT NOT NULL REFERENCES Rose_db_object_private.descriptions (id),
author_id INT NOT NULL REFERENCES Rose_db_object_private.authors (id),
PRIMARY KEY(description_id, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE Rose_db_object_private.colors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description_id INT REFERENCES Rose_db_object_private.descriptions (id),
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;
}
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);
{
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 descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions CASCADE'
);
}
$dbh
->
do
(
<<"EOF");
CREATE TABLE regions
(
id CHAR(2) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
UNIQUE(name)
)
ENGINE=InnoDB
EOF
my
$db_name
=
$db
->database;
my
$sth
=
$dbh
->prepare(
"SHOW TABLE STATUS FROM `$db_name` LIKE ?"
);
$sth
->execute(
'regions'
);
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 vendors
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT,
region_id CHAR(2),
INDEX(vendor_id),
INDEX(region_id),
FOREIGN KEY (vendor_id) REFERENCES vendors (id),
FOREIGN KEY (region_id) REFERENCES regions (id),
UNIQUE(name)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE products
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT,
sale_date DATETIME,
INDEX(vendor_id),
FOREIGN KEY (vendor_id) REFERENCES vendors (id),
UNIQUE(name)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE prices
(
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
region_id CHAR(2) NOT NULL DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
INDEX(product_id),
INDEX(region_id),
FOREIGN KEY (product_id) REFERENCES products (id),
FOREIGN KEY (region_id) REFERENCES regions (id),
UNIQUE(product_id, region_id)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE descriptions
(
id INT AUTO_INCREMENT PRIMARY KEY,
text VARCHAR(255) NOT NULL,
UNIQUE(text)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE authors
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE nicknames
(
id INT AUTO_INCREMENT PRIMARY KEY,
nick VARCHAR(255) NOT NULL,
author_id INT,
INDEX(author_id),
FOREIGN KEY (author_id) REFERENCES authors (id),
UNIQUE(nick, author_id)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE description_author_map
(
description_id INT NOT NULL,
author_id INT NOT NULL,
INDEX(description_id),
INDEX(author_id),
FOREIGN KEY (description_id) REFERENCES descriptions (id),
FOREIGN KEY (author_id) REFERENCES authors (id),
PRIMARY KEY(description_id, author_id)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE colors
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description_id INT,
INDEX(description_id),
FOREIGN KEY (description_id) REFERENCES descriptions (id),
UNIQUE(name)
)
ENGINE=InnoDB
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE product_color_map
(
product_id INT NOT NULL,
color_id INT NOT NULL,
INDEX(product_id),
INDEX(color_id),
FOREIGN KEY (product_id) REFERENCES products (id),
FOREIGN KEY (color_id) REFERENCES colors (id),
PRIMARY KEY(product_id, color_id)
)
ENGINE=InnoDB
EOF
$dbh
->disconnect;
}
eval
{
$dbh
= Rose::DB->new(
'informix_admin'
)->retain_dbh()
or
die
Rose::DB->error;
};
if
(!$@ &&
$dbh
)
{
$Have
{
'informix'
} = 1;
{
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 description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions CASCADE'
);
}
$dbh
->
do
(
<<"EOF");
CREATE TABLE regions
(
id CHAR(2) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE vendors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
region_id CHAR(2) REFERENCES regions (id),
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE products
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
sale_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_id CHAR(2) DEFAULT 'US' NOT NULL REFERENCES regions (id),
price DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
UNIQUE(product_id, region_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE descriptions
(
id SERIAL NOT NULL PRIMARY KEY,
text VARCHAR(255) NOT NULL,
UNIQUE(text)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE authors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE nicknames
(
id SERIAL NOT NULL PRIMARY KEY,
nick VARCHAR(255) NOT NULL,
author_id INT REFERENCES authors (id),
UNIQUE(nick, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE description_author_map
(
description_id INT NOT NULL REFERENCES descriptions (id),
author_id INT NOT NULL REFERENCES authors (id),
PRIMARY KEY(description_id, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE colors
(
id SERIAL NOT NULL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description_id INT REFERENCES descriptions (id),
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;
}
eval
{
$dbh
= Rose::DB->new(
'sqlite_admin'
)->retain_dbh()
or
die
Rose::DB->error;
};
if
(!$@ &&
$dbh
)
{
$Have
{
'sqlite'
} = 1;
{
local
$dbh
->{
'RaiseError'
} = 0;
local
$dbh
->{
'PrintError'
} = 0;
$dbh
->
do
(
'DROP TABLE colors'
);
$dbh
->
do
(
'DROP TABLE descriptions'
);
$dbh
->
do
(
'DROP TABLE authors'
);
$dbh
->
do
(
'DROP TABLE nicknames'
);
$dbh
->
do
(
'DROP TABLE description_author_map'
);
$dbh
->
do
(
'DROP TABLE product_color_map'
);
$dbh
->
do
(
'DROP TABLE prices'
);
$dbh
->
do
(
'DROP TABLE products'
);
$dbh
->
do
(
'DROP TABLE vendors'
);
$dbh
->
do
(
'DROP TABLE regions'
);
}
$dbh
->
do
(
<<"EOF");
CREATE TABLE regions
(
id CHAR(2) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE vendors
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
region_id CHAR(2) REFERENCES regions (id),
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE products
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
vendor_id INT REFERENCES vendors (id),
sale_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_id CHAR(2) NOT NULL REFERENCES regions (id) DEFAULT 'US',
price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
UNIQUE(product_id, region_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE descriptions
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
text VARCHAR(255) NOT NULL,
UNIQUE(text)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE authors
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
UNIQUE(name)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE nicknames
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
nick VARCHAR(255) NOT NULL,
author_id INT REFERENCES authors (id),
UNIQUE(nick, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE description_author_map
(
description_id INT NOT NULL REFERENCES descriptions (id),
author_id INT NOT NULL REFERENCES authors (id),
PRIMARY KEY(description_id, author_id)
)
EOF
$dbh
->
do
(
<<"EOF");
CREATE TABLE colors
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255) NOT NULL,
description_id INT REFERENCES descriptions (id),
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
{
if
(
$Have
{
'pg'
})
{
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 description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions 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.description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE Rose_db_object_private.descriptions 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 TABLE Rose_db_object_private.regions CASCADE'
);
$dbh
->
do
(
'DROP SCHEMA Rose_db_object_private CASCADE'
);
$dbh
->disconnect;
}
if
(
$Have
{
'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 description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions CASCADE'
);
$dbh
->disconnect;
}
if
(
$Have
{
'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 description_author_map CASCADE'
);
$dbh
->
do
(
'DROP TABLE nicknames CASCADE'
);
$dbh
->
do
(
'DROP TABLE authors CASCADE'
);
$dbh
->
do
(
'DROP TABLE descriptions CASCADE'
);
$dbh
->
do
(
'DROP TABLE prices CASCADE'
);
$dbh
->
do
(
'DROP TABLE products CASCADE'
);
$dbh
->
do
(
'DROP TABLE vendors CASCADE'
);
$dbh
->
do
(
'DROP TABLE regions CASCADE'
);
$dbh
->disconnect;
}
if
(
$Have
{
'sqlite'
})
{
my
$dbh
= Rose::DB->new(
'sqlite_admin'
)->retain_dbh()
or
die
Rose::DB->error;
$dbh
->
do
(
'DROP TABLE colors'
);
$dbh
->
do
(
'DROP TABLE descriptions'
);
$dbh
->
do
(
'DROP TABLE authors'
);
$dbh
->
do
(
'DROP TABLE nicknames'
);
$dbh
->
do
(
'DROP TABLE description_author_map'
);
$dbh
->
do
(
'DROP TABLE product_color_map'
);
$dbh
->
do
(
'DROP TABLE prices'
);
$dbh
->
do
(
'DROP TABLE products'
);
$dbh
->
do
(
'DROP TABLE vendors'
);
$dbh
->
do
(
'DROP TABLE regions'
);
$dbh
->disconnect;
}
}