Schema
Methods for defining, loading and creating DB schemas
Methodology
1) Create tables with primary keys 2) Create indexes 3) Create foreign key constraints
my $schema = {
table => {
columns => [
{
name => 'COLUMN',
type => 'ALLOWED TYPE',
unique => 0|1,
null => 0|1,
size => [0-9]*,
default => '',
}
],
primary_key => [
'COLUMN',
],
indexes => [
{
name => 'INDEX NAME',
unique => 0|1,
columns => [
'COLUMN',
],
},
],
constraints => {
foreign => [
{
name => 'CONSTRAINT NAME',
columns => [
'COLUMN',
],
references => {
table => 'TABLE',
columns => [
'COLUMN',
],
},
cascade => 0|1,
},
],
},
},
};
Allowed types
Numeric
All numbers are signed (apart from some serials). Numbers are of fixed size, i.e int(10) is invalid.
- int
-
Safe range -2147483648 to 2147483647
- smallint
-
Safe range -32768 to 32767
- bigint
-
Safe range -9223372036854775808 to 9223372036854775807
- real
-
Safe range - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
- double
-
Safe range - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
Dates and times
Always store dates in the database as GMT (UTC), you shouldn't be storing timezones in the DB (and we don't plan to support it). Date implementations vary a lot, you might be better off just using integers, such as 20100101 for 1st of January 2010.
- date
-
Safe range '1000-01-01' to '9999-12-31'
- time
-
Safe range 00:00:00 to 23:59:59
- timezone
-
Safe range '1000-01-01 00:00:00' to '9999-12-31 23:59:59' Format YYYY-MM-DD HH:MM:SS
Strings
By default all strings are created unicode compatible. You should be planning for wide characters in the first place, it'll save you much pain later. It is possible to use the standard ASCII string types by TODO
- char
-
Safe range 1 to 255 Default 1
- varchar
-
Safe range 1 to 2000
- text
-
Safe storage up to 2GB
Special
- serial
-
Safe range 1 to 2147483647 (int based)
Beware oracle warning below
- bigserial
-
Safe range 1 to 9223372036854775807 (bigint based)
FAQ
- Why no ON UPDATE CASCADE?
-
Oracle doesn't support it, and for good reason. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
Known issues
- MySQL floats
-
MySQL uses doubles for internal calculations, so floats may not calculate the same as the other dbs
- Oracle unicode
-
SELECT translated_description FROM product_descriptions WHERE translated_name = N'LCD Monitor 11/PM';
- Oracle auto increment
-
Much more complicated. We automatically create a sequence and trigger
# additional code is needed:- #create sequence test_seq #start with 1 #increment by 1 #nomaxvalue; # #create trigger test_trigger #before insert on my_test #for each row #begin #select test_seq.nextval into :new.id from dual; #end;
- Postgres index names must be unique across tables
-
It appears so. Should we automatically append table name to postgres indexes???
- Oracle dates and times
-
Oracle date fields are different, they include a time and must be formatted. The helper method TODO is provided to eleviate this problem. Oracle also has no native time, we use a timestamp field with date 1000-01-01 to simulate.
- SQL Server serials
-
To explicitly insert a serial, you must first run
SET IDENTITY_INSERT "I<TABLE>" ON
The helper method TODO is provided to eliviate this problem.
methods
- new
-
$db_schema = new Cosmic::DB::Schema( $dbh );
- load
-
$db_schema->load( $schema );
- create
-
my $ddl = $db_schema->create;
13 POD Errors
The following errors were encountered while parsing the POD:
- Around line 69:
'=item' outside of any '=over'
- Around line 89:
You forgot a '=back' before '=head2'
- Around line 96:
'=item' outside of any '=over'
- Around line 109:
You forgot a '=back' before '=head2'
- Around line 115:
'=item' outside of any '=over'
- Around line 128:
You forgot a '=back' before '=head2'
- Around line 130:
'=item' outside of any '=over'
- Around line 140:
You forgot a '=back' before '=head1'
- Around line 142:
'=item' outside of any '=over'
- Around line 147:
You forgot a '=back' before '=head1'
- Around line 149:
'=item' outside of any '=over'
- Around line 194:
You forgot a '=back' before '=head1'
- Around line 198:
'=item' outside of any '=over'
=over without closing =back