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;