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