/ 
CXC-DB-DDL-0.18
River stage one • 1 direct dependent • 1 total dependent
/ CXC::DB::DDL::Manual::Intro

NAME

CXC::DB::DDL::Manual::Intro - Introduction to CXC::DB::DDL

VERSION

version 0.18

SYNOPSIS

use CXC::DB::DDL;
use CXC::DB::DDL::Util 'INTEGER', 'VARCHAR', 'REAL', 'DATETIME', 'xFIELDS', 'xTYPE';
use CXC::DB::DDL::Constants qw( UNIQUE
  SQL_VARCHAR
  SQL_LONGVARCHAR
  SQL_TIMESTAMP
  CASCADE
);

# define some table names
use constant DB_TABLE_OBS      => 'observations';
use constant DB_TABLE_TAGS     => 'tags';
use constant DB_TABLE_PRODUCTS => 'products';

# add some DSL keywords of our own
sub TAG_ID ( $name = 'tag_id' ) {
    $name => INTEGER(
        foreign_key => {
            table     => DB_TABLE_TAGS,
            field     => 'id',
            on_delete => CASCADE,
        } );
}

sub AUTO_ID ( $name = 'id' ) {
    $name => INTEGER( is_primary_key => 1, is_auto_increment => 1 );
}

DESCRIPTION

The heart of using CXC::DB::DDL is the DSL routines provided by CXC::DB::DDL::Util, which make it easier to construct the data structures that "new" in CXC::DB::DDL accepts.

my %table_obs = (
    name => DB_TABLE_OBS,
    xFIELDS(
        obsid       => INTEGER( is_primary_key => 1 ),
        cohort      => INTEGER,
        date        => xTYPE( [ SQL_TIMESTAMP, ] ),
        dec         => TEXT,
        event_count => INTEGER,
        exposure    => REAL,
        instrument  => TEXT,
        obs_cycle   => INTEGER,
        prop_cycle  => INTEGER,
        ra          => TEXT,
    ),
);

The upper-cased type functions (e.g. INTEGER) are factories for the CXC::DB::DDL::Field class; and accept its attributes. The xTYPE factory works on any type; here it is used to pass an arrayref of types rather than a single type; this is used to limit the return values of "type_info" in DBI.

Here we're using our own DSL keyword (AUTO_ID, defined in the "SYNOPSIS") and have added a constraint (with an automatically generated name) that requires the tuple of tag and system be unique in the table.

my %table_tags = (
    name => DB_TABLE_TAGS,
    xFIELDS(
        AUTO_ID,
        tag    => TEXT,
        system => TEXT,
        pars   => TEXT,
    ),
    constraints => [ {
            fields => [ 'tag', 'system' ],
            type   => UNIQUE,
        },
    ],
);

Introducing foreign keys and default values is straightforward (also using another custom DSL field; see "SYNOPSIS" ):

my %table_products = (
    name => DB_TABLE_PRODUCTS,
    xFIELDS(
        AUTO_ID,
        obsid  => INTEGER( foreign_key   => DB_TABLE_OBS ),
        sim_id => INTEGER( default_value => 0 ),
        TAG_ID,
        table   => TEXT,
        dep_ids => TEXT( is_nullable => !!1),
    ),
    constraints => [ {
            fields => [ 'tag_id', 'obsid', 'sim_id' ],
            type   => UNIQUE,
        },
    ],
);

And finally, creating the CXC::DB::DDL object:

$ddl = CXC::DB::DDL->new( [ \%table_obs, \%table_tags, \%table_products ] );

At this point, given a handle to a DBI object, $dbh, we can create the tables:

$ddl->create( $dbh );

Drop them:

$ddl->drop( $dbh );

Etc. Note that CXC::DB::DDL does not work out inter-table foreign key dependencies. It drops tables in reverse order passed to the constructor, so make sure those are in the proper order.

Another benefit of using this approach is that we can query a CXC::DB::DSL object for the table names and field names, and so automate some of the drudgery of interactions with databases.

SUPPORT

Bugs

Please report any bugs or feature requests to bug-cxc-db-ddl@rt.cpan.org or through the web interface at: https://rt.cpan.org/Public/Dist/Display.html?Name=CXC-DB-DDL

Source

Source is available at

https://gitlab.com/djerius/cxc-db-ddl

and may be cloned from

https://gitlab.com/djerius/cxc-db-ddl.git

SEE ALSO

Please see those modules/websites for more information related to this module.

AUTHOR

Diab Jerius <djerius@cpan.org>

COPYRIGHT AND LICENSE

This software is Copyright (c) 2022 by Smithsonian Astrophysical Observatory.

This is free software, licensed under:

The GNU General Public License, Version 3, June 2007