NAME

DBIO::SQLite::DDL - Generate SQLite DDL from DBIO Result classes

VERSION

version 0.900007

DESCRIPTION

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

my $ddl = DBIO::SQLite::DDL->install_ddl($schema_class_or_instance);
# CREATE TABLE ...; CREATE INDEX ...; ...

The output is plain SQL, suitable for executing one statement at a time against a fresh SQLite database.

SQLite has neither schemas nor sequences nor functions/triggers/RLS, so the generated DDL is much smaller than the PostgreSQL equivalent. The only constructs emitted are CREATE TABLE (with inline columns, primary key, unique constraints, foreign keys), CREATE INDEX and CREATE VIEW.

Indexes

CREATE INDEX comes from two declarations: sqlite_index (DBIO::SQLite::Result) and the index declaration of the result source that idx of DBIO::Cake and indices write (index_declarations, ADR 0061 in core). The latter gives name, columns and unique, and its own driver => { sqlite => {...} } block gives expression and where. An empty sqlite => {} is the plain index. Where both declarations use the same name, the canonical declaration wins. An entry that carries only another driver's block, such as pg => { where => ... }, and an unknown key in the own block are refused with an exception naming the index (maintainer decision 2026-10-12, index_declarations_for of DBIO::ResultSource); the diff builds its desired model from this DDL and refuses the same.

An index over exactly the columns of a unique constraint of the same table is not written, plain or declared unique: the UNIQUE line already has the index SQLite keeps for it, and a second one is a duplicate. The columns must be the same list in the same order, and an index with an expression or a where is another object and is written.

Identifiers

Every name is written in double quotes -- the table, its columns, the columns of a key or a unique constraint, the table and the columns a foreign key points at, an index, a view:

CREATE TABLE "order" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "group" VARCHAR(20) NOT NULL,
  "customer_id" INTEGER NOT NULL,
  UNIQUE ("group"),
  FOREIGN KEY ("customer_id") REFERENCES "customer"("id")
);

CREATE INDEX "index" ON "order" ("customer_id");

Always, and not only where a name needs it: a reserved word has the shape of any other name, and written bare CREATE TABLE order (...) is a syntax error. A double quote inside a name is doubled ("quote_identifier" in DBIO::SQLite::Util). SQLite does not fold the letter case of a name, so the quotes change nothing about which table or column is meant: "Item" is the table Item, and a statement that names it item finds it as before.

A table or view name given as a string is one name. SQLite has no schemas to qualify it with, so a string with a dot in it -- table('other.t') -- is refused, naming the table and the scalar-ref form below, before a statement is written; the foreign keys, the indexes and the desired model of a diff go through this method and so refuse the same. A virtual view has no database object and no name to refuse.

Three things are not names and are written as the schema gives them:

  • a table or view name given as a scalar ref, which is literal SQL by the schema's own declaration -- __PACKAGE__->table(\'cd') is written CREATE TABLE cd, __PACKAGE__->table(\'main.cd') is written CREATE TABLE main.cd and creates the table cd. The same text names the table for its indexes and behind the REFERENCES of a foreign key that points at it. Nothing is made of it: SQLite takes no schema prefix behind REFERENCES, so a key cannot point at a table that is named with one;

  • the expression and the where of a sqlite_indexes entry (DBIO::SQLite::Result) and a view's definition, which are SQL;

  • a column's type, and a default given as a scalar ref.

This is about the DDL. The queries of the ORM are written by the SQL maker, and how those quote a name is its business and that of the quote_names connect attribute, not this module's.

A database deployed by an earlier version has its names bare in the statements sqlite_master keeps. They are the same tables, columns and indexes, and "upgrade" in DBIO::Deploy::Base finds nothing to do for them ("What counts as a changed index" in DBIO::SQLite::Diff::Index).

Column sizes

A column's size is written into its type: a length for varchar, char, character and every type the driver has no name of its own for -- bit with size => 8 is BIT(8), tinyint with size => 1 TINYINT(1) -- and precision and scale as size => [ 10, 2 ] for numeric and decimal. An integer alias SQLite maps to INTEGER (bigint, smallint, int) is INTEGER whatever its size, which keeps a key column the rowid alias. DBIO::SQLite::Introspect reads the size back out of the type, so a deploy diffs empty against its own declaration.

SQLite does not enforce a declared length: eleven characters are stored in a CHARACTER(10), and the driver writes no CHECK constraint to change that (maintainer decision 2026-10-12). The size is the declared type, and what the column is reported as, nothing more.

Column defaults

A column's default_value is written after DEFAULT in one of two ways:

default_value => 5                       # DEFAULT 5
default_value => "it's"                  # DEFAULT 'it''s'
default_value => '007'                   # DEFAULT '007' in a char, varchar
                                         # or text column, DEFAULT 007 in
                                         # an integer one
default_value => \'CURRENT_TIMESTAMP'    # DEFAULT CURRENT_TIMESTAMP
default_value => \"(datetime('now'))"    # DEFAULT (datetime('now'))

A plain value is a value, and the column says of which kind. In a column with TEXT affinity -- one whose type, as it is written into the CREATE TABLE, holds CHAR, CLOB or TEXT and no INT ("type_affinity" in DBIO::SQLite::Util) -- it is a string and is always written as a string literal, with every apostrophe in it doubled, also when it looks like a number: bare, DEFAULT 007 would give every row the text 7. In any other column a number is written bare and anything else as that string literal. Either way the column's default is the value that was declared ("default_literal_sql" in DBIO::SQLite::Util). A scalar ref is literal SQL and goes into the statement as it is -- an expression needs its own parentheses, as SQLite's grammar asks for them.

A database deployed by an earlier version has the digits of such a default bare in its text columns. Where that is the same default -- an integer without a leading zero, DEFAULT 10 -- the next "upgrade" in DBIO::Deploy::Base finds nothing to do. Where it is not -- DEFAULT 007, DEFAULT 1.50 -- it rebuilds the table with the default that was declared; the rows that are there keep what they hold. "What counts as a changed column" in DBIO::SQLite::Diff::Column has the rule.

Foreign keys and their actions

A belongs_to is written as a FOREIGN KEY (...) REFERENCES ... table constraint, unless it sets is_foreign_key_constraint => 0. The on_delete and on_update attributes of the relationship follow it as ON DELETE / ON UPDATE, as declared:

__PACKAGE__->belongs_to(owner => 'MyApp::DB::Result::Owner', 'owner_id',
  { on_delete => 'CASCADE', on_update => 'CASCADE' });

# FOREIGN KEY ("owner_id") REFERENCES "owner"("id") ON DELETE CASCADE ON UPDATE CASCADE
  • An attribute that is absent, undef or '' writes no clause. The key then has SQLite's default, NO ACTION.

  • The value is checked ("foreign_key_action" in DBIO::SQLite::Util): SET NULL, SET DEFAULT, CASCADE, RESTRICT or NO ACTION, in either letter case and with any white space between the words. It is written as declared (white space collapsed). NO ACTION is the default of a key and writes no clause. Any other word dies with a DBIO::Exception that names the value and the relationship, so a typo is refused when the DDL is generated.

  • Nothing is derived from the other side of the relationship. A has_many has cascade_delete => 1 by default, and that is the ORM deleting the related rows; it does not make the key ON DELETE CASCADE. The autodetection DBIO::Relationship::Base describes for on_delete / on_update is not performed.

The keys of a table are written in the order of the names of their relationships, so the statement is the same every time it is generated.

A key that several relationships declare -- the same columns, the same target table and columns, the same actions in whichever letter case -- is one key and is written once, by the first of them. Relationships over the same columns and target that declare different actions each write their own line, and SQLite keeps a foreign key for each; whether that should be refused instead is open.

SQLite runs these actions only on a connection that enforces foreign keys -- PRAGMA foreign_keys = ON, see "connect_call_use_foreign_keys" in DBIO::SQLite::Storage.

An action reaches a table when the table is created, by an install or by an upgrade that adds it, and it reaches a table that is already deployed by the next "upgrade" in DBIO::Deploy::Base: DBIO::SQLite::Diff compares the foreign keys of a table that exists on both sides -- columns, target and both actions -- and rebuilds the table when they differ ("When a table is rebuilt" in DBIO::SQLite::Diff). So a database deployed before this module wrote ON DELETE / ON UPDATE gets the actions its relationships declare, and adding, changing or removing on_delete / on_update, or is_foreign_key_constraint, is picked up. SQLite has no statement that changes a foreign key in place, so each such change is a rebuild of the table that holds the key.

METHODS

install_ddl

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

Returns the full installation DDL as a single string. $schema may be a connected schema instance or a schema class name.

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.