/ 
DBIO-MySQL-0.900004
/ DBIO::MySQL::DDL

NAME

DBIO::MySQL::DDL - Generate MySQL/MariaDB DDL from DBIO Result classes

VERSION

version 0.900004

DESCRIPTION

DBIO::MySQL::DDL generates a MySQL DDL script from a DBIO::Schema class hierarchy. It is the desired-state side of the test-deploy-and-compare strategy used by DBIO::MySQL::Deploy.

my $ddl = DBIO::MySQL::DDL->install_ddl($schema);

The generated DDL is plain SQL, one CREATE TABLE per source. The tables come in foreign key order, a referenced table before the one that references it, and among tables free to go the one of the first source name first, so the script is the same on every run. A foreign key that points to itself is no dependency. Tables that reference each other in a cycle are created with the foreign keys that close the cycle left out of their CREATE TABLE; those are added with ALTER TABLE ... ADD FOREIGN KEY after every table and index; the cycle is broken at the first table on it by source name. A foreign key is written CONSTRAINT `name` FOREIGN KEY ... with the fixed name "foreign_key_name" gives. Identical foreign keys (two belongs_to to one target over the same columns) are written once; different ones that would get the same name all get a _<hash8> suffix ("foreign_key_names"), nothing throws. The columns of a composite foreign key stand in the order of the key of the target (primary key, then unique constraints by name), the order InnoDB takes. A foreign key from a belongs_to carries ON UPDATE and ON DELETE from the on_update and on_delete attributes of the relationship (NO ACTION, RESTRICT, CASCADE, SET NULL, any case); NO ACTION, an unset attribute and '' (the database's default) write nothing, any other word throws. SET DEFAULT throws as well: both servers accept it and never run it (MySQL acts as RESTRICT, MariaDB stores RESTRICT), so it is refused loudly rather than deployed as a promise the database does not keep. A relationship whose target is a view source (virtual or not) writes no foreign key, in the install and in the diff alike: neither server lets a key reference a view (error 1824), and the relationship itself stays valid, so a join over it works. The introspection then sees none. Tables default to ENGINE=InnoDB and CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci. Result classes can override per-table via mysql_engine, mysql_charset, mysql_collate attributes.

Indexes come from two declarations: mysql_index on the result class and the canonical index declaration of the result source (idx, indices; "index_declarations_for" in DBIO::ResultSource), whose own mysql block may hold using. On the same name the canonical declaration wins. An entry that carries only another driver's block, or an unknown key in its mysql block, throws.

An index over exactly the columns of a unique constraint of the same table, in the same order, is not written: the UNIQUE KEY is that index already, and a second one would only be a duplicate. Another column order, a subset and a FULLTEXT or SPATIAL index are other objects and are written.

An enum or set column lists its members from extra => { list => [...] }, each as a string literal (apostrophe and backslash doubled), and a bit column keeps its size (bit(8); without one the server makes bit(1)).

Every name the DDL carries -- of a table, a view, a column, a unique key, an index, and the table and columns a foreign key references -- is written in backticks, a backtick inside it doubled (DBIO::MySQL::Identifier). So a table may be called order and a column key or my-col. A name is one identifier: a dot in it is part of the name, not the separator of a database-qualified one. Because of that a table or view name with a dot (table('db.t'), table(\'db.t')) is refused with a DBIO::Exception and nothing is rendered, instead of creating a table literally called db.t that no query reaches; creating a table in another database is not supported yet. A dot in a column name is fine. The definition of a view is the SQL the result source declares and is written as it stands; the names inside it are quoted by whoever wrote it.

METHODS

default_charset

The character set of a table that names none: utf8mb4. The CREATE TABLE of DBIO::MySQL::Diff::Table takes it from here as well, so a table an upgrade creates is the table install_ddl creates.

default_collation

The collation of a table that names none: utf8mb4_unicode_ci, the same source as "default_charset".

foreign_key_name

my $name = DBIO::MySQL::DDL::foreign_key_name('child', 'parent_id');   # child_parent_id_fk

The name of a foreign key: <table>_<columns>_fk, the columns in the order of the key. One function for "install_ddl" and for both ways the diff writes a foreign key (DBIO::MySQL::Diff::Table, DBIO::MySQL::Diff::ForeignKey), so an install and an upgrade end with the same constraint names and the diff after the upgrade is empty. A name over 64 characters, the limit of the servers, is cut and ends in 8 hex digits of the SHA-1 of the full name, so two long names that start alike stay apart.

foreign_key_names

my @names = DBIO::MySQL::DDL::foreign_key_names(
  { table => 'a_b', from_columns => ['c'],   to_table => 'p', to_columns => ['id'] },
  { table => 'a',   from_columns => ['b_c'], to_table => 'p', to_columns => ['id'] },
);   # a_b_c_fk_1f2e3d4c, a_b_c_fk_9a8b7c6d

The names of a whole set of foreign keys (hashrefs with table, from_columns, to_table, to_columns and, if there are any, on_delete / on_update as written), one name per foreign key in the order given. This is the one place that decides which foreign keys share a name, for "install_ddl" and for the diff (DBIO::MySQL::Diff, for a model that carries no names), so both agree.

Foreign keys that are identical (table, columns, target, target columns, actions) are one foreign key and get the same name; the caller writes it once. Different foreign keys that would get the same "foreign_key_name" (table a_b column c and table a column b_c; one column that references two targets) ALL get the fixed suffix _<hash8>: 8 hex digits of the SHA-1 of table, columns, target table and target columns; a name that grew over 64 characters is cut before the suffix. The comparison ignores case, as the server does. Nothing throws. Two foreign keys that differ only in their actions (same table, columns and target) get the actions into the hash as well, else they would stay alike.

order_by_foreign_keys

my @steps = DBIO::MySQL::DDL::order_by_foreign_keys(\@tables, sub { $_[0]{to_table} });

The order in which a set of new tables is created, shared by the install and by the upgrade out of an empty database (DBIO::MySQL::Diff). @tables are hashrefs with a name and a list fks of foreign keys of any shape; the code ref names the table a foreign key references. Of the tables whose parents are all out, the first in the order given goes next. A foreign key to a table outside the set is no dependency, and neither is one to the table itself. When every table left waits on another one (a cycle), the first of them that lies on a cycle (not one that merely waits for a cycle) goes with the foreign keys to tables not yet out held back. Returns one hash per table in creation order: table, inline (the foreign keys its CREATE TABLE carries) and held (those to be added by ALTER TABLE after every table and index).

view_reads

my @names = DBIO::MySQL::DDL::view_reads($view_definition);

The names a view definition reads as a table or view: every name that stands where a FROM or a JOIN (or a comma in a FROM list) expects a table, in a subquery, a derived table or a join group as much as at the top, in order of appearance, without repeats. A database qualifier (db.name) is dropped and only the name is returned. The caller keeps those that are views of its own set ("order_views").

This is a reading of the text, not of the server, because "install_ddl" has no database to ask. It is the one detection for the install and for the diff: the diff reads the definition the server hands back (DBIO::MySQL::Introspect::Tables), which is the same text in a fixed spelling. It was measured (MySQL 8.0.30, MariaDB 11.8.9) against information_schema.view_table_usage, which MySQL 8 has and MariaDB does not have at all (Unknown table 'view_table_usage'), so it would have meant two detections.

The text is split into names, words, strings and comments first: a name inside a string literal ('from b') or a comment (/* from b */, -- b, # b) reads nothing; a column, an alias or a function that bears the name of a view reads nothing, because only the place after FROM/JOIN/the comma counts; a name in backticks may be a keyword-like word or hold a dot (a dot inside backticks is part of the name); the FROM of TRIM, EXTRACT and SUBSTRING opens no table list, since it stands inside a call and no SELECT stands in that frame. A name followed by ( is a call (JSON_TABLE(...)), not a table. Keywords are matched without regard to case.

What it does not recognise, so that a view which reads another one through any of these is still created in name order and the server may refuse the statement:

  • a view of another database. The qualifier is dropped, so other.b counts as b when the set has a view b. (It is a false edge, not a missed one.)

  • a CTE that bears the name of a view (WITH b AS (...) SELECT ... FROM b) is read as a read of the view b.

  • a double-quoted word is a string, as the server reads it without ANSI_QUOTES; with that mode a view that names its tables in double quotes is not recognised. The server rewrites to backticks, so the diff is not affected.

  • an executable comment (/*! ... */) is a comment here, whatever the server makes of it.

  • a view read in a statement the definition does not hold at the top (a view body is one SELECT; this is no limit).

order_views

my @names = DBIO::MySQL::DDL::order_views(\%definitions);

The order in which a set of views is created, shared by the install ("install_ddl") and by the diff (DBIO::MySQL::Diff::View). %definitions maps a view name to its definition (SQL text, or undef for a view that reads nothing). A view goes after the views of the set it reads ("view_reads"); of the views free to go the first by name is next, so views free of each other stay in name order. A read of the view itself, or of a name that is no view of the set, is no dependency. The drop order is this one reversed: a view goes before the views it reads.

When the views read each other in a cycle no order creates them, and the call throws a DBIO::Exception naming the views on the cycle, before any statement has been written. A name is matched as written, else without regard to case when exactly one view of the set has that name that way.

drop_ddl

my $ddl = DBIO::MySQL::DDL->drop_ddl($schema);

The statements that take away what "install_ddl" creates, for the add_drop_table re-deploy: DROP VIEW IF EXISTS for every view the install creates, a reader before the view it reads (the reverse of "order_views"), then DROP TABLE IF EXISTS for every table, all names in backticks as the install writes them. One string, the statements separated by a blank line. A virtual view, a source with a computed name and a view without a definition are no object of the database and stay out. The views have to go first: the install says CREATE VIEW, never CREATE OR REPLACE, so a view left behind fails the next install.

qualified_tables

my @pairs = DBIO::MySQL::DDL->qualified_tables($schema);   # ([ 'other.t', 'other' ], ...)

The tables of the schema whose name is database.table, with their database, in source order and once each. A view, a virtual source and a computed name are not among them.

install_ddl

my $ddl = DBIO::MySQL::DDL->install_ddl($schema);
my $ddl = DBIO::MySQL::DDL->install_ddl($schema, { local_only => 1 });
my $ddl = DBIO::MySQL::DDL->install_ddl($schema, { database => 'other' });

Returns the full installation DDL as a single string. local_only leaves out the tables of other databases (the temporary database of a diff gets the tables of the current database only). database returns the tables of that one database and nothing else, under the names they have in it: other.t is made as `t`, for the temporary database a diff compares that database's tables in; views are not among them.

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

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