/ 
DBIO-ClickHouse-0.900004
/ DBIO::ClickHouse::DDL

NAME

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

VERSION

version 0.900004

DESCRIPTION

DBIO::ClickHouse::DDL generates a ClickHouse DDL script from a DBIO::Schema class hierarchy. It is the desired-state side of the test-deploy-and-compare strategy the other DBIO drivers use.

my $ddl = DBIO::ClickHouse::DDL->install_ddl($schema_class_or_instance);

The output is plain SQL, suitable for executing one statement at a time against a fresh ClickHouse database. Every table is emitted with an explicit engine and sorting key -- ENGINE = MergeTree plus ORDER BY defaulting to the DBIO primary key -- because ClickHouse has no engine default that DBIO could rely on. Per-table overrides come from DBIO::ClickHouse::Result's clickhouse_table.

All statement shape lives in DBIO::ClickHouse::DDL::Emit so that this module and the later diff renderers agree char-for-char.

What is deliberately NOT emitted

Foreign keys and unique constraints are not left out silently: a declaration that asks for one is refused, see "What install_ddl refuses".

  • Foreign keys. ClickHouse has no foreign key concept at all -- not even a parsed-and-ignored one. See docs/adr/0004-no-foreign-keys.md.

  • Unique constraints. Likewise absent; add_unique_constraint is schema metadata DBIO uses for lookups, and ClickHouse cannot enforce it. Use ReplacingMergeTree with the key in ORDER BY if you want last-write-wins deduplication.

  • Auto-increment. There are no sequences and no identity columns. An is_auto_increment column of a UUID-family type gets DEFAULT generateUUIDv4(); for any other type the flag is refused (see "What install_ddl refuses") -- it used to be ignored silently, which built a table without the behaviour the declaration promised.

  • Standalone indexes. There is no CREATE INDEX. ClickHouse's data-skipping indexes are declared inline in CREATE TABLE (INDEX name expr TYPE ... GRANULARITY n); see "Data-skipping indexes".

Data-skipping indexes

The canonical index declarations of a result source ("idx" in DBIO::Cake, indices, "add_index_declaration" in DBIO::ResultSource; ADR 0061 in core) are read with index_declarations_for('clickhouse', [qw(type granularity expression)]) and rendered inline in the CREATE TABLE body, after the columns, sorted by name, through "index_def" in DBIO::ClickHouse::DDL::Emit -- the very function the diff renders an added index with.

$source->add_index_declaration({
  name    => 'score_idx',
  columns => ['score'],
  driver  => { clickhouse => { type => 'minmax', granularity => 4 } },
});
# INDEX `score_idx` `score` TYPE minmax GRANULARITY 4

  • type is required and passed verbatim (minmax, set(100), bloom_filter(0.01), ...). There is no plain index in ClickHouse, so a declaration without a clickhouse block, with an empty one or without type is refused: the type cannot be made up.

  • granularity is optional, a positive integer; omitted, the server default stands.

  • expression is optional and emitted verbatim; without it the indexed expression is the declared column (several columns become the tuple (`a`, `b`)).

Refused loudly, naming the source and the index, never rendered as a simpler index: unique (ClickHouse has no unique index), a block holding only another driver's keys, an unknown key in the block, a non-empty top-level options or a top-level type other than unique.

No hand-declared source

Unlike the other drivers, this one has no per-driver hand-written index accessor (clickhouse_table carries engine clauses only), so there is nothing for a canonical declaration to win against.

Statement order

Tables are emitted in sorted-name order, before the views. Because no foreign keys are emitted, there are no inter-table dependencies to satisfy, so the alphabetical order is both sufficient and deterministic -- the diff layer depends on that stability. Views come last, each after the views its SELECT reads, otherwise by name ("view_order").

Type mapping

The portable data_type of a column is mapped to a native ClickHouse type. The interesting cases, and why they were decided that way:

  • Integers map by width, not to one catch-all: tinyint to Int8, smallint to Int16, int/integer to Int32, bigint to Int64. extra => { unsigned => 1 } (the spelling DBIC's MySQL loader emits) selects the UInt* counterpart.

  • Strings. The whole varchar/char/text/blob family collapses onto String, ClickHouse's arbitrary-length byte string. There is no length limit to enforce and no separate binary type, so size is dropped.

  • FixedString(N) is opt-in, via extra => { fixed_string => 1 } on a sized column -- never inferred from char(N). FixedString pads with zero bytes rather than spaces and hands the padding back to the client, so it does not mean what CHAR(N) means in any other engine. It is a storage optimization for genuinely fixed-width binary keys (hashes, country codes), which is a decision only the schema author can make.

  • Decimal(P, S) comes straight from size => [precision, scale]. A sizeless decimal cannot be rendered -- ClickHouse has no bare Decimal -- so it falls back to Decimal(38, 9), the widest precision still inside Decimal128.

  • date maps to Date32, not Date. Date is two bytes and starts at 1970-01-01, so it cannot hold a date of birth or any historical date; Date32 covers 1900-2299. Correctness over two bytes per row. The narrow type is reachable by spelling it natively -- data_type => 'Date', capital D -- where the range is known to be safe and the column is hot.

  • datetime/timestamp map to DateTime64(3) -- millisecond precision. Plain DateTime is second-resolution and capped at 2106; DateTime64(3) matches what an ORM user gets from every other DBIO driver's timestamp. Spelling data_type => 'DateTime' natively asks for the second-resolution type. timestamptz adds the zone the same way ClickHouse does, inside the type: DateTime64(3, 'UTC').

  • time maps to String. ClickHouse gained Time/Time64 types only recently and behind an experimental setting, so there is no portable native target; String is lossless and round-trips. The native data_type => 'Time' is passed through for schemas running with that setting enabled.

  • boolean maps to Bool, not UInt8. Bool is an alias for UInt8 in the engine, but it is what SHOW CREATE TABLE reports, which keeps the desired-state DDL and the introspected model comparable.

  • Enums. data_type => 'enum', extra => { list => [...] } renders Enum8 for up to 127 members and Enum16 beyond, numbering members 1..N in declaration order. Appending a member is then a non-destructive change; reordering is not.

  • Arrays. A data_type ending in [] (text[]) becomes Array(T).

  • Nullable(T) is derived from is_nullable -- ClickHouse columns are NOT NULL by default, and nullability lives in the type rather than in a column constraint. Composite types are left alone: the server rejects Nullable(Array(T)).

  • LowCardinality(T) is opt-in via extra => { low_cardinality => 1 }. It cannot be inferred, because it is a win for low-distinct-count columns and a loss otherwise. It nests outside nullability: LowCardinality(Nullable(String)).

  • Anything else passes through verbatim, so a native ClickHouse type name can be written straight into data_type (IPv6, Point, Map(String, UInt64)). A data_type that already carries parentheses is likewise never rewritten.

  • An exact native spelling is never translated. The portable map is consulted case-insensitively, so VARCHAR and varchar both render String -- but a data_type spelled exactly the way ClickHouse spells the type itself, capitalization included, counts as native and is passed through before the map is consulted at all. That is what makes the narrow data_type => 'Date' and data_type => 'DateTime' above reachable rather than being swallowed by the portable date / datetime entries. The same rule makes data_type => 'JSON' ClickHouse's semi-structured JSON type while data_type => 'json' stays String, and data_type => 'Time' the native Time while data_type => 'time' stays String.

    Only seven names are affected -- String, Bool, UUID, Date, DateTime, Time, JSON -- because every other native name is absent from the portable map and already passes through untouched. Note that the native name wins over the extra flags that pick a type: String with extra => { fixed_string => 1 } is String; ask for FixedString(N) through the portable char spelling, or write FixedString(N) out.

Nullability is not emitted as a NOT NULL column constraint; it is part of the rendered type. See "column_def" in DBIO::ClickHouse::DDL::Emit.

METHODS

install_ddl

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

Returns the full installation DDL as a single string, statements separated by a blank line.

A source whose table is database.table (table('other.t')) is made in that database: CREATE TABLE `other`.`t` ("qualified_tables"). The optional second argument is a hash reference; local_only => 1 leaves those tables out, which is what the scratch database of a diff gets -- the diff reads the current database alone.

qualified_tables

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

The tables and views of the schema whose name is database.table or database.view, with their database, in source order and once each (a view only when it is made, like "install_ddl" does: not a virtual one, and one with a definition). A virtual source and a name that names nothing are not among them.

view_order

my @names = DBIO::ClickHouse::DDL->view_order({ a => 'SELECT ... FROM b', b => '...' });

The names of the views of { view_name => $select_sql } in the order they are written in: each view after the views its SELECT reads, the others by name. Dropping goes the other way round: reverse of this.

The server resolves the tables of a view's SELECT when the view is created, so a view over a view that does not exist yet is UNKNOWN_TABLE (measured, 25.3). Nothing the server records helps: system.tables.dependencies_table is empty for a plain view, and the views of an install do not exist yet anyway. The only source is the text of the definition, read against the names of the views given. That is a reading with limits:

  • a read is a name after FROM or JOIN, bare, in backticks or qualified by a database (db.v); a view whose name carries a database (other.v) is read by that qualified name only. Strings, comments and everything else are not names: a column, an alias or a literal that spells a view is not a read.

  • a comma join (FROM a, v) reads only the first name; write JOIN. A WITH name that shadows a view still counts as a read of that view. A read the text hides (a dynamic remote(), a dictionary) is not seen.

  • what is not seen is not ordered: the install then fails at the server, as it did before, never silently differently.

A cycle (a reads b, b reads a) is a DBIO::Exception naming every view in it, thrown before a statement is written.

declared_views

my $views = DBIO::ClickHouse::DDL->declared_views($schema);

Returns { view_name => $select_sql } for every non-virtual view source with a view_definition -- exactly the views "install_ddl" creates (one selection, shared). The Diff cannot recover a definition from the introspected model, so DBIO::ClickHouse::Deploy hands these to it, which renders the CREATE VIEW of a view the upgrade has to create.

A view named database.view is made in that database. The optional second argument is a hash reference; local_only => 1 leaves those views out, which is what the diff gets: it reads the current database alone, so a view of another database is installed and never compared, replaced or dropped.

declared_settings

my $settings = DBIO::ClickHouse::DDL->declared_settings($schema);

Returns { table_name => \%settings } for every physical table whose result class declares settings through clickhouse_table. The Diff cannot recover them from the scratch database (engine_full there also carries the server's own defaults), so DBIO::ClickHouse::Deploy hands the declared ones to the Diff, which renders them into the CREATE TABLE of a table the upgrade creates.

column_type

my $type = DBIO::ClickHouse::DDL->column_type($source->column_info($name));
# 'LowCardinality(Nullable(String))'

Renders one DBIO column_info hashref as the ClickHouse type string this module would put in a CREATE TABLE, wrappers and all. It is the single public entry to "Type mapping" above; install_ddl and the diff both go through the same code, so there is exactly one answer to "what type is this column" in this distribution.

Exposed because it is useful outside DDL generation: DBIO::ClickHouse::Native needs the identical string to build the column list ClickHouse::Encoder->new takes, and a second type table would be a second truth.

What install_ddl refuses

install_ddl throws, before it renders a statement and so before any is executed, when the declaration asks for something ClickHouse cannot build and the table would otherwise be created without it (conformance DDL list). Every offender is named, with table and column:

This replaces the silent omission of ADR 0004 for the first three; the auto-increment and bit refusals replace a silent ignore and a silent widening.

SEE ALSO

AUTHOR

DBIO Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors

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