NAME

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

VERSION

version 0.15

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 types (e.g. SQL_TIMESTAMP )
  use CXC::DB::DDL::Util -types;

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

DESCRIPTION

CXC::DB::DDL::Util provides a DSL 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.

The heart of system is "xFIELDS", which takes pairs of ($field_name, $type_generator) and returns a fields => \%attr pair suitable to be passed to CXC::DB::DDL's constructor.

The type generators accept any of the CXC::DB::DDL::Field attribute specifications.

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

Type constants

"Bare" type "constants" are used by "xTYPE"; these are made available either via explicit export or via the "-types" option passed during import. The constants' values are specific to this package; do not use them in place of the standard constants when working directly with DBI.

The standard SQL types (e.g. those exported by DBI) are available under the same names (e.g. SQL_INTEGER). The DBD specific types are available with an added prefix of DBD_TYPE_, e.g. the DBD::Pg's PG_JSON is made available as DBD_TYPE_PG_JSON.

SUBROUTINES

SQL_TYPE_NAMES

SQL_TYPE_VALUES

  @type_names = SQL_TYPE_NAMES;
  @type_codes = SQL_TYPE_VALUES;

returns (in collated order) names and values of all of the DBI supported types (without the SQL_ prefix)

DBD_TYPE_NAMES

DBD_TYPE_VALUES

  @type_names = DBD_TYPE_NAMES;
  @type_codes = DBD_TYPE_VALUES;

returns (in collated order) names and values of all of the DBD supported types.

TYPE_NAMES

TYPE_VALUES

  @type_names = DBD_TYPE_NAMES;
  @type_codes = DBD_TYPE_VALUES;

returns (in collated order) names and values of all of the supported types

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 type constant exported by this module (not by DBI or a DBD driver). It is important to use the types provided by this package, e.g. do this:

  use CXC::DB::DDL::Util 'DBD_TYPE_PG_JSONB';

  xTYPE( DBD_TYPE_PG_JSONB, ... );

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

• CXC::DB::DDL::Field::Pg

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