File Coverage

blib/lib/Cosmic/DB/Schema.pm

Criterion	Covered	Total	%
statement	14	36	38.8
branch	1	14	7.1
condition	0	3	0.0
subroutine	4	6	66.6
pod	3	3	100.0
total	22	62	35.4

package Cosmic::DB::Schema;
use strict;
use warnings;
use Carp;

=pod

=head1 Schema

Methods for defining, loading and creating DB schemas

=head1 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,
                },
            ],
        },
    },
};

=head1 Allowed types

=head2 Numeric

All numbers are signed (apart from some serials). Numbers are of fixed size,
i.e int(10) is invalid.

=item int

Safe range -2147483648 to 2147483647

=item smallint

Safe range -32768 to 32767

=item bigint

Safe range -9223372036854775808 to 9223372036854775807

=item real

Safe range - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

=item double

Safe range - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

=head2 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.

=item date

Safe range '1000-01-01' to '9999-12-31'

=item time

Safe range 00:00:00 to 23:59:59

=item timezone

Safe range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
Format YYYY-MM-DD HH:MM:SS

=head2 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

=item char

Safe range 1 to 255
Default 1

=item varchar

Safe range 1 to 2000

=item text

Safe storage up to 2GB

=head2 Special

=item serial

Safe range 1 to 2147483647 (int based)

Beware oracle warning below

=item bigserial

Safe range 1 to 9223372036854775807 (bigint based)

=head1 FAQ

=item 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

=head1 Known issues

=item MySQL floats

MySQL uses doubles for internal calculations, so floats may not calculate the
same as the other dbs

=item Oracle unicode

SELECT translated_description FROM product_descriptions
   WHERE translated_name = N'LCD Monitor 11/PM';

=item 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;

=item Postgres index names must be unique across tables

It appears so. Should we automatically append table name to postgres indexes???

=item 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.

=item SQL Server serials

To explicitly insert a serial, you must first run

    SET IDENTITY_INSERT "I" ON 




191




 





 





 





 





 





 



   




192




 





 





 





 





 





 



 The helper method TODO is provided to eliviate this problem. 




193




 





 





 





 





 





 



   




194




 





 





 





 





 





 



 =head1 methods 




195




 





 





 





 





 





 



   




196




 





 





 





 





 





 



 =cut 




197




 





 





 





 





 





 



   




198




 





 





 





 





 





 



 =item new 




199




 





 





 





 





 





 



   




200




 





 





 





 





 





 



     $db_schema = new Cosmic::DB::Schema( $dbh ); 




201




 





 





 





 





 





 



   




202




 





 





 





 





 





 



 =cut 




203




 





 





 





 





 





 



   




204




 





 





 





 





 





 



 sub new { 




205




1





 





 




  
1
  



  
1
  




529



     my $class = shift; 




206




1





 





 





 





 





4



     my ( $dbh ) = @_; 




207




1




  
 50
  




 





 





 





5



     croak( 'No database handle passed' ) unless ref( $dbh ) eq 'DBI::db'; 




208




1





 





 





 





 





4



     my $self = bless { dbh => $dbh }, $class; 




209




1





 





 





 





 





3



     return $self; 




210




 





 





 





 





 





 



 }#sub 




211




 





 





 





 





 





 



   




212




 





 





 





 





 





 



   




213




 





 





 





 





 





 



 =item load 




214




 





 





 





 





 





 



   




215




 





 





 





 





 





 



     $db_schema->load( $schema ); 




216




 





 





 





 





 





 



   




217




 





 





 





 





 





 



 =cut 




218




 





 





 





 





 





 



   




219




 





 





 





 





 





 



 sub load { 




220




0





 





 




  
0
  



  
1
  




 



     my $self = shift; 




221




0





 





 





 





 





 



     $self->{schema} = $_[0]; 




222




 





 





 





 





 





 



     # TODO load from JSON, YAML, XML 




223




 





 





 





 





 





 



     # TODO validation 




224




0





 





 





 





 





 



     return $self; 




225




 





 





 





 





 





 



 }#sub 




226




 





 





 





 





 





 



   




227




 





 





 





 





 





 



   




228




 





 





 





 





 





 



 =item create 




229




 





 





 





 





 





 



   




230




 





 





 





 





 





 



     my $ddl = $db_schema->create; 




231




 





 





 





 





 





 



   




232




 





 





 





 





 





 



 =cut 




233




 





 





 





 





 





 



   




234




 





 





 





 





 





 



 sub create { 




235




0





 





 




  
0
  



  
1
  




 



     my $self = shift; 




236




0





 





 





 





 





 



     my %options = @_; 




237




0





 





 





 





 





 



     my $schema; 




238




0




  
  0
  



  
  0
  




 





 





 



     if ( $self->{dbh}->{Driver}->{Name} eq 'mysql' ||  $self->{dbh}->{Driver}->{Name} eq 'mysqlPP' ) { 




239




0





 





 





 





 





 



         require Cosmic::DB::Schema::MySQL; 




240




0





 





 





 





 





 



         $schema = new Cosmic::DB::Schema::MySQL( $self->{dbh} ); 




241




 





 





 





 





 





 



     }#if 




242




0




  
  0
  




 





 





 





 



     if ( $self->{dbh}->{Driver}->{Name} eq 'Pg' ) { 




243




0





 





 





 





 





 



         require Cosmic::DB::Schema::Postgres; 




244




0





 





 





 





 





 



         $schema = new Cosmic::DB::Schema::Postgres( $self->{dbh} ); 




245




 





 





 





 





 





 



     }#if 




246




0




  
  0
  




 





 





 





 



     if ( $self->{dbh}->{Driver}->{Name} eq 'Oracle' ) { 




247




0





 





 





 





 





 



         require Cosmic::DB::Schema::Oracle; 




248




0





 





 





 





 





 



         $schema = new Cosmic::DB::Schema::Oracle( $self->{dbh} ); 




249




 





 





 





 





 





 



     }#if 




250




0




  
  0
  




 





 





 





 



     if ( $self->{dbh}->{Driver}->{Name} eq 'ODBC' ) { 




251




 





 





 





 





 





 



         # TODO extra ODBC check for SQL Server 




252




0





 





 





 





 





 



         require Cosmic::DB::Schema::SQLServer; 




253




0





 





 





 





 





 



         $schema = new Cosmic::DB::Schema::SQLServer( $self->{dbh} ); 




254




 





 





 





 





 





 



     }#if 




255




0




  
  0
  




 





 





 





 



     if ( $schema ) { 




256




0





 





 





 





 





 



         $options{schema} = $self->{schema}; 




257




0




  
  0
  




 





 





 





 



         return wantarray ? ( $schema->producer( %options ) ) : $schema->producer( %options ); 




258




 





 





 





 





 





 



     }#if 




259




 





 





 





 





 





 



     else { 




260




0





 





 





 





 





 



         croak( "DB $self->{dbh}->{Driver}->{Name} not supported" ); 




261




 





 





 





 





 





 



     }#else 




262




 





 





 





 





 





 



 }#sub 




263




 





 





 





 





 





 



   




264




 





 





 





 





 





 



   




265




 





 





 





 





 





 



 1;

The helper method TODO is provided to eliviate this problem.

=head1 methods

=cut

=item new

    $db_schema = new Cosmic::DB::Schema( $dbh );

=cut

sub new {
    my $class = shift;
    my ( $dbh ) = @_;
    croak( 'No database handle passed' ) unless ref( $dbh ) eq 'DBI::db';
    my $self = bless { dbh => $dbh }, $class;
    return $self;
}#sub


=item load

    $db_schema->load( $schema );

=cut

sub load {
    my $self = shift;
    $self->{schema} = $_[0];
    # TODO load from JSON, YAML, XML
    # TODO validation
    return $self;
}#sub


=item create

    my $ddl = $db_schema->create;

=cut

sub create {
    my $self = shift;
    my %options = @_;
    my $schema;
    if ( $self->{dbh}->{Driver}->{Name} eq 'mysql' ||  $self->{dbh}->{Driver}->{Name} eq 'mysqlPP' ) {
        require Cosmic::DB::Schema::MySQL;
        $schema = new Cosmic::DB::Schema::MySQL( $self->{dbh} );
    }#if
    if ( $self->{dbh}->{Driver}->{Name} eq 'Pg' ) {
        require Cosmic::DB::Schema::Postgres;
        $schema = new Cosmic::DB::Schema::Postgres( $self->{dbh} );
    }#if
    if ( $self->{dbh}->{Driver}->{Name} eq 'Oracle' ) {
        require Cosmic::DB::Schema::Oracle;
        $schema = new Cosmic::DB::Schema::Oracle( $self->{dbh} );
    }#if
    if ( $self->{dbh}->{Driver}->{Name} eq 'ODBC' ) {
        # TODO extra ODBC check for SQL Server
        require Cosmic::DB::Schema::SQLServer;
        $schema = new Cosmic::DB::Schema::SQLServer( $self->{dbh} );
    }#if
    if ( $schema ) {
        $options{schema} = $self->{schema};
        return wantarray ? ( $schema->producer( %options ) ) : $schema->producer( %options );
    }#if
    else {
        croak( "DB $self->{dbh}->{Driver}->{Name} not supported" );
    }#else
}#sub


1;