NAME

DBIO::Cake - DDL-like DSL for defining DBIO result classes

VERSION

version 0.900004

SYNOPSIS

package MyApp::Schema::Result::Artist;
use DBIO::Cake;

table 'artists';

col id         => integer auto_inc;
col name       => varchar(100);
col bio        => text null;
col active     => boolean default(1);
col created_at => timestamp;
col updated_at => timestamp on_update;

primary_key 'id';
unique artist_name => ['name'];

has_many cds => 'MyApp::Schema::Result::CD', 'artist_id';

1;

PostgreSQL-specific example:

package MyApp::Schema::Result::User;
use DBIO::Cake -inflate_json;

table 'users';

col id         => uuid;
col name       => varchar(100);
col role       => enum(qw( admin moderator user guest )), null;
col metadata   => jsonb \"{}";
col embedding  => vector(1536);
col tags       => array(text), null;
col tsv        => tsvector null;
col created_at => timestamp;
col updated_at => timestamp on_update;
col deleted_at => timestamp null;

primary_key 'id';
idx user_tags => ['tags'], pg => { using => 'gin' };

1;

See t/cake_comprehensive.t for a runnable example.

DESCRIPTION

DBIO::Cake is the most concise way to define DBIO result classes. It keeps the same underlying schema metadata as vanilla DBIO::Core, but replaces verbose hashref-heavy declarations with a DDL-like DSL.

Use Cake when you want result classes to read like schema definitions without changing how the rest of DBIO behaves.

When you use DBIO::Cake, it automatically:

Enables strict and warnings
Sets the calling class to inherit from DBIO::Core
Exports all DSL functions into the calling package
Cleans up exported symbols after the scope ends (via namespace::clean)

METHODS

type_function_names

my @names = DBIO::Cake->type_function_names;

The names of the column type functions Cake exports (integer, varchar, timestamp, ...), the words that stand for a type in col NAME => TYPE. Column modifiers, relationship functions and the table functions are not among them. DBIO::Generate::Style::Cake writes a column's type as a bare word for exactly these names and as a string for every other type.

COMMA-FREE SYNTAX

Cake supports a DDL-like comma-free syntax. All type functions and modifiers pass @_ through, so Perl chains them via nested function calls:

col id => integer auto_inc;     # parsed as: integer(auto_inc())
col bio => text null;           # parsed as: text(null())
col active => boolean default(1); # parsed as: boolean(default(1))

When you need a comma: after a number or closing parenthesis, Perl needs a comma before the next bareword:

col name => varchar(100), null;   # comma after (100)
col name => varchar 100, null;    # comma after 100

This matches DBIx::Class::ResultDDL conventions.

IMPORT OPTIONS

use DBIO::Cake;                            # defaults
use DBIO::Cake -inflate_datetime;          # load InflateColumn::DateTime
use DBIO::Cake -inflate_json;              # auto-inflate json/jsonb columns
use DBIO::Cake -retrieve_defaults;         # set retrieve_on_insert for columns with defaults
use DBIO::Cake -no_autoclean;              # don't clean up symbols

Multiple options can be combined:

use DBIO::Cake -inflate_datetime, -inflate_json;

SMART DEFAULTS

Cake automatically sets sensible defaults based on column type and nullability.

Timestamp columns

The behavior depends on nullability and the on_update modifier:

col created_at => timestamp;                  # NOT NULL -> set_on_create
col updated_at => timestamp on_update;        # NOT NULL -> set_on_create + set_on_update
col deleted_at => timestamp null;             # nullable -> no auto-set
col last_login => timestamp null, on_update;  # nullable -> only set_on_update

The logic: NOT NULL timestamp columns must have a value on create, so set_on_create is implied. Nullable columns don't need a value on create, so only explicit on_update is applied.

This integrates with the DBIO::Timestamp component built into DBIO core.

UUID columns

NOT NULL uuid columns automatically get retrieve_on_insert so the database-generated default (e.g. PostgreSQL's gen_random_uuid()) is retrieved after INSERT:

col id => uuid;
# -> retrieve_on_insert => 1

Scalar references as defaults

A scalar reference anywhere in a col declaration is treated as a default_value. This is a shorthand for default(\...):

col id      => uuid \"gen_random_uuid()";
col active  => boolean \1;
col created => timestamp \"now()";

# equivalent to:
col id      => uuid default(\"gen_random_uuid()");

For literal SQL defaults, use a reference to a string. For Perl-side defaults, use default($value) without a reference.

COLUMN DECLARATION

col

col $name => @type_and_modifiers;

Adds one column to the result class. The list after the name is what the type functions and modifiers below return; col turns it into the column info hashref:

a scalar reference anywhere in the list becomes default_value (see "Scalar references as defaults")
dotted keys are nested: 'extra.unsigned' => 1 becomes extra => { unsigned => 1 }
is_nullable defaults to 0 -- columns are NOT NULL unless marked null
with -retrieve_defaults, a column with a default_value gets retrieve_on_insert (unless set explicitly)
an active inflation import option (e.g. -inflate_json) adds the column attributes the storage's type registry declares for that data type, without overriding ones given explicitly
the timestamp and UUID defaults of "SMART DEFAULTS" are applied

COLUMN TYPES

All type functions return flat key-value lists and pass through @_, enabling the comma-free syntax. Each entry names the data_type it sets. Where a function takes a size-like argument, it is recognised only when it looks like a number, so varchar null still works.

Integer types

col id    => integer auto_inc;
col count => bigint;

integer: integer.
tinyint: tinyint.
smallint: smallint.
bigint: bigint.

Serial types (auto-increment shortcuts)

col id => serial;   # integer + auto_inc in one

serial: serial, plus is_auto_increment => 1.
bigserial: bigserial, plus is_auto_increment => 1.
smallserial: smallserial, plus is_auto_increment => 1.

Numeric types

col price => numeric(10, 2);

numeric: numeric. numeric($precision) sets size => [$precision], numeric($precision, $scale) sets size => [$precision, $scale].
decimal: decimal, with the same optional precision and scale as "numeric".

Floating point types

real
float4: real (float4 is an alias).
double
float8: double precision (float8 is an alias).
float: float; float($bits) also sets size.

String types

col code => char(3);
col name => varchar(100), null;

char: char, size from the argument (default 1).
varchar: varchar, size from the argument (default 255).

Text types

col bio => text null;

text
tinytext
mediumtext
longtext: text, tinytext, mediumtext, longtext.

Binary types

blob
tinyblob
mediumblob
longblob
bytea: blob, tinyblob, mediumblob, longblob, bytea.

Boolean

col active => boolean default(1);

boolean
bool: boolean (bool is an alias).

Date/Time types

col created_at => timestamp;             # auto set_on_create
col updated_at => timestamp on_update;   # auto set_on_create + set_on_update
col deleted_at => timestamp null;        # no auto-set
col birthday   => date null;

date: date.
datetime
timestamp
time: datetime, timestamp, time. An optional first argument that is a plain string and not a lowercase word (e.g. timestamp 'UTC') sets timezone.
timetz: time with time zone.
timestamptz: timestamp with time zone.
interval: interval.

Enum and set

col role => enum(qw( admin moderator user guest ));

enum
set: enum / set, with the arguments as the list of allowed values in extra => { list => [...] }. Unlike the other type functions these take no further options: every argument is a value, so modifiers must be separated by a comma (enum(qw(a b)), null).

UUID

col id => uuid;   # auto retrieve_on_insert

uuid: uuid.

JSON

col metadata => jsonb null;

With -inflate_json, json/jsonb columns are automatically serialized.

json
jsonb: json, jsonb.

Array (PostgreSQL)

col tags => array(text), null;

array: array($type) sets data_type to $type followed by [] (text[] above); the element type may be given as a type function call or as a type name. array(\%info) instead sets data_type => 'ARRAY' and merges %info in.

Vector / AI (pgvector)

col embedding => vector(1536);

vector
halfvec
sparsevec: vector, halfvec, sparsevec; the optional dimension argument sets size.

Full-text search (PostgreSQL)

tsvector
tsquery: tsvector, tsquery.

Network types (PostgreSQL)

inet
cidr
macaddr
macaddr8: inet, cidr, macaddr, macaddr8.

Geometric types (PostgreSQL)

point
line
lseg
box
path
polygon
circle: point, line, lseg, box, path, polygon, circle.

Range types (PostgreSQL)

int4range
int8range
numrange
tsrange
tstzrange
daterange: int4range, int8range, numrange, tsrange, tstzrange, daterange.

Other

money
xml
hstore: money, xml, hstore.
bit
varbit: bit, varbit; the optional size argument sets size.

COLUMN MODIFIERS

All modifiers return flat key-value lists and pass through @_.

null

Marks the column as nullable.

col bio => text null;

auto_inc

Marks the column as auto-increment.

col id => integer auto_inc;

fk

Marks the column as a foreign key.

col author_id => integer fk;

unsigned

Marks the column as unsigned (MySQL).

col count => integer unsigned;

default($value)

Sets the default value.

col active => boolean default(1);
col created => timestamp default(\"now()");

on_create

Explicitly set set_on_create. Normally not needed -- NOT NULL timestamp columns get this automatically.

on_update

Set set_on_update -- the column value is refreshed on every row update.

col updated_at => timestamp on_update;

TABLE AND CONSTRAINT FUNCTIONS

table

table 'my_table';

Sets the table name for this result class.

primary_key

primary_key 'id';
primary_key 'artist_id', 'cd_id';

Sets the primary key column(s).

unique

unique \@cols;
unique $name => \@cols;

Adds a unique constraint.

RELATIONSHIP FUNCTIONS

belongs_to author => 'MyApp::Schema::Result::Author', 'author_id';
has_one    isbn   => 'MyApp::Schema::Result::ISBN', 'book_id';
has_many   books  => 'MyApp::Schema::Result::Book', 'author_id';
might_have bio    => 'MyApp::Schema::Result::Bio', 'author_id';
many_to_many roles => 'actor_roles', 'role';

belongs_to
has_one
has_many
might_have
many_to_many: Call the class method of the same name (see DBIO::Relationship) on the result class, with all arguments unchanged.

rel_one

Like belongs_to but forces join_type => 'left'.

rel_many

Alias for has_many.

CASCADE HELPERS

ddl_cascade

Returns on_delete => 'CASCADE', on_update => 'CASCADE' for use in relationship attribute hashes.

dbic_cascade

Returns cascade_delete => 1, cascade_copy => 1.

VIEW SUPPORT

view

view 'my_view', 'SELECT * FROM artists WHERE active = 1';

Declares a view-based result source.

TIMESTAMP HELPERS

Shortcut functions for the most common timestamp column patterns.

col_created

col_created;               # creates 'created_at' column
col_created 'born_at';     # custom column name

Equivalent to col created_at => timestamp.

col_updated

col_updated;               # creates 'updated_at' column
col_updated 'modified_at'; # custom column name

Equivalent to col updated_at => timestamp on_update.

cols_updated_created

cols_updated_created;      # creates both created_at + updated_at

Creates both timestamp columns in one call. The most common pattern -- just add this one line and you're done.

INDEX SUPPORT

idx

idx name_idx => ['name'];
idx composite_idx => ['last_name', 'first_name'], type => 'unique';
idx tags_idx => ['tags'], pg => { using => 'gin' };
idx draft_only => ['key'],
    type => 'unique',
    pg   => { where => 'version IS NULL' };

The options are type, options and one block per driver: pg takes a hashref with where, using, with and expression, sqlite takes where and expression, mysql takes using. Any other key, in either place, is rejected when the class loads, naming the index and the key.

Declares an index. The declaration is stored on the result source in its canonical form, { name, columns, unique, type, options, driver }, and every driver reads it from there with index_declarations:

for my $idx (MyApp::Schema::Result::User->result_source_instance
               ->index_declarations) {
  # $idx->{name}, $idx->{columns}, $idx->{unique}, $idx->{driver}{pg}
}

type => 'unique' makes the entry unique, and a block is carried as driver => { pg => { ... } }, the block of the driver that understands it.

A schema is bound to one driver and a block is that driver's: the driver reads its entries with index_declarations_for, which refuses an entry that only carries another driver's block (a partial unique index declared for pg is not a plain unique index on sqlite) and an unknown key in its own block. An empty own block, sqlite => {}, says that the plain index is wanted. A class that has to serve two drivers is two schema classes sharing the source's columns. Declaring the same name twice replaces the earlier declaration. indices from DBIO::ResultSourceProxy fills the same declaration list.

A driver reads the declarations from the source; the Result class gets no index method of its own from them. An index a driver namespace declares by hand (pg_index for PostgreSQL) stays valid beside them.

PostgreSQL partial indexes

idx agent_published => ['key', 'version'],
    type => 'unique',
    pg   => { where => 'version IS NOT NULL' };
idx agent_draft => ['key'],
    type => 'unique',
    pg   => { where => 'version IS NULL' };

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.

To install DBIO, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIO

CPAN shell

perl -MCPAN -e shell
install DBIO

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)