NAME

CXC::DB::DDL::Util - CXC::DB::DDL utilities

VERSION

version 0.14

SYNOPSIS

use CXC::DB::DDL::Util -all;

# import xFIELDS, xCHECK, xTYPE
use CXC::DB::DDL::Util -schema_funcs;

# import type function generators (e.g. INTEGER, DOUBLE )
use CXC::DB::DDL::Util -type_funcs;

# import type function generators with a prefix
# (e.g. MK_INTEGER, MK_DOUBLE )
use CXC::DB::DDL::Util { prefix => 'MK_' }, -type_funcs;


use DBI;
$ddl = CXC::DB::DDL->new( [ {
            name => 'observation',
            xFIELDS(
                segment     => MK_INTEGER,
                obsid       => INTEGER( is_primary_key => 1 ),
                cohort      => TEXT,
                date        => xTYPE( [DBI::SQL_TIMESTAMP] ),
                dec         => TEXT,
                object      => TEXT,
                event_count => INTEGER,
                exposure    => REAL,
                instrument  => TEXT,
                obs_cycle   => INTEGER,
                prop_cycle  => INTEGER,
                ra          => TEXT,
            ),
        },
    ] );

DESCRIPTION

CXC::DB::DDL::Util provides generators to ease creation of, amongst, others, CXC::DB::DDL::Field objects. It uses Exporter::Tiny as its base exporter, allowing renaming of exported symbols and other things.

DBD Specific Types

Some database drivers (e.g. DBD::Pg) provide additional types. For the generic mechanism to add these see "ADVANCED USES".

To access the PostgreSQL types, first load the DBD::Pg specific subclass of CXC::DB::DDL::Field, then pass the global dbd => 'Pg' to CXC::DB::DDL::Util:

use CXC::DB::DDL::Field::Pg;
use CXC::DB::DDL::Util { dbd => 'Pg' }, -type_funcs;

The PostgreSQL specific type function generators are now available as e.g., PG_JSONB (the PG_ prefix is not removed):

@fields = xFIELDS(
    segment        => INTEGER,
    pars           => PG_JSONB,
);

The generated field objects will be in the CXC::DB::DDL::Field::Pg class.

SUBROUTINES

SQL_TYPE_NAMES

@typenames = SQL_TYPE_NAMES;

returns all of the DBI supported types (without the SQL_ prefix)

TYPENAME

TYPENAME is one of the SQL types recognized by DBI or by a particular DBD driver (see "DBD Specific Types").

See "sql_type_constants" in CXC::DB::DDL::Constants for more information.

Called as, e.g.

INTEGER( %attr )

these are generators which return subroutines with the following signature:

sub ( $field_name )

which return a CXC::DB::DDL::Field object with the specified SQL datatype (in this example INTEGER), field name ($field_name) and attributes (%attr).

These are available for individual export or in entirety via the type_funcs tag.

They are typically used in conjunction with the "xFIELDS" subroutine, e.g.

xFIELDS(
    segment        => INTEGER,
    obsid          => INTEGER( is_primary_key => 1 ),
    target_type    => VARCHAR( is_nullable => 1 ),
)

"xFIELDS" essentially turns this into:

fields => [
    INTEGER()->('segment'),
    INTEGER(is_primary_key => 1 )->('obsid'),
    VARCHAR(is_nullable => 1 )->( 'target_type' ),
]

which is more painful to write and look at. So don't.

xTYPE

xTYPE ( $type, %attr )

A generic form of e.g., "INTEGER". Type is a numeric DATA_TYPE made available from DBI or a DBD driver (e.g. DBD::Pg).

Be careful when importing the type functions from CXC::DB::DDL::Util for driver specific types.

For example, the type constants provided by DBI all begin with SQL_; the DBI type functions provided by CXC::DB::DDL::Util match those, but with the SQL_ prefix removed, so there is no collision between DBI type constant names and CXC::DB::DDL::Util type function names, e.g.:

use DBI 'SQL_INTEGER';
use CXC::DB::DDL:Util -type_funcs, 'xTYPE';

xTYPE( SQL_INTEGER, ... );
INTEGER( ... )

However, because it is possible that a driver specific type might have a similar name to a DBI provided type (e.g. for the fictitious DBD::MYDB driver, MYDB_INTEGER vs SQL_INTEGER), when constructing the type functions for driver specific types, the leading prefix is not removed.

So,

# DONT DO THIS
use DBD::MYDB 'MYDB_INTEGER';
use CXC::DB::DDL:Util { dbd => 'MYDB' }, -type_funcs, 'xTYPE';

xTYPE( MYDB_INTEGER, ... );
MYDB_INTEGER( ... )

will cause a collision between MYDB_INTEGER, the type constant, and MYDB_INTEGER, the type function. To avoid this, either rename the type function generators upon import (see Exporter::Tiny::Manual::Importing):

# DO THIS
use DBD::MYDB 'MYDB_INTEGER';
use CXC::DB::DDL:Util
  { as => sub { $_[0] =~ s/MYDB_/MK_/r },
    dbd => 'MYDB',
  },
  -type_funcs, 'xTYPE';

xTYPE( MYDB_INTEGER, ... );
MK_INTEGER( ... )

or use the fully qualified type constant name:

# DO THIS
use DBD::MYDB ();
use CXC::DB::DDL:Util { dbd => 'MYDB'}, -type_funcs, 'xTYPE';

xTYPE( DBD::MYDB::MYDB_INTEGER, ... );
INTEGER( ... )

xFIELDS

@field_spec = xFIELDS( array of  Tuple[ NonEmptyStr, CodeRef ] );

returns a list of

fields => \@spec,

where @spec generated by running

CodeRef->(NonEmptyStr)

for each tuple.

xCHECK

DEPRECATED; use a table constraint type set to the constant CHECK_C, as follows:

my $table = CXC::DB::DDL::Table_>new( ...,
    constraints => [
        {
            expression => '"type" in ( "a", "b", "c" )',
            type       => CHECK_C,
        },
   ] );

DEPRECATED USE BELOW:

$string = xCHECK( $field, @values )

generates a check constraint as a string which looks like

$field in ( $value[0], $value[1], ...  )

sqlt_producer_map

$sqlt_producer = sqlt_producer_map( $dbd );

Map $dbd (typically from $dbh-{Driver}{NAME}>) to what SQL::Translator wants. Actually, this just checks for known (to me) deviations; everything else is returned as.

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.

• CXC::DB::DDL

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

cpanm CXC::DB::DDL

perl -MCPAN -e shell
install CXC::DB::DDL