NAME

DBIO::Sybase::DDL - Generate Sybase ASE DDL from DBIO Result classes

VERSION

version 0.900007

DESCRIPTION

DBIO::Sybase::DDL generates Sybase ASE DDL from the DBIO schema class hierarchy. It is the desired-state side of the test-deploy-and-compare strategy used by DBIO::Sybase::Deploy.

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

Every name goes through DBIO::Sybase::Quote: bare when it is a simple identifier and no ASE keyword, in square brackets otherwise -- [dummy], [key], [my col]. The same holds for the DBIO::Sybase::Diff operations.

NOTE: Sybase ASE does not support CHECK constraints in the same way as other databases. Unique constraints -- a column's is_unique flag as well as the source's add_unique_constraints -- are emitted as unique indexes instead, a constraint's index under the constraint's name.

Indexes

CREATE [UNIQUE] INDEX also comes from the index declaration of the result source that idx of DBIO::Cake, indices and add_index_declaration write (ADR 0061 in core, index_declarations_for of DBIO::ResultSource). It is written after the table's unique indexes and before the foreign keys. The driver has no attribute of its own for hand-written indexes.

ASE has no partial and no function index, so the sybase block of a declaration takes no key: where, expression and any other key, a declaration that carries only another driver's block (pg => {...} without sybase => {}), a type other than unique, any options, and more than 31 key columns are refused with an exception naming the index -- never written as a plainer one. An empty sybase => {} is the plain index. The diff builds its desired model from this DDL and refuses the same.

An index over exactly the columns, in the same order, of a unique index already written (a column's is_unique, a unique constraint, the primary key) is not written a second time. The same name over other columns is refused: ASE index names are per table. An identifier's length is not checked here.

METHODS

install_ddl

my $ddl = DBIO::Sybase::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.

The foreign keys come last, one ALTER TABLE ... ADD CONSTRAINT fk_<table>_<cols> FOREIGN KEY (...) REFERENCES ... each, after every CREATE TABLE -- references may form a cycle. They are read from the relationships marked is_foreign_key_constraint (belongs_to sets it by default), without ON DELETE / ON UPDATE, which ASE does not have (Msg 156, for NO ACTION as well). A relationship that becomes a constraint and declares an on_delete or on_update other than NO ACTION (undef and the empty string mean none) -- CASCADE, SET NULL, SET DEFAULT, RESTRICT or any other word -- is refused with a DBIO::Exception naming relationship, table, event and value, never installed as a plain reference. The diff builds its desired model from this DDL and refuses the same. A relationship that yields no constraint is not looked at. A relationship whose condition is not plain column pairs, whose target table is not part of the deploy, or whose target columns are neither the target's primary key nor one of its unique constraints (ASE refuses a reference without a unique index behind it) yields no constraint.

A column marked is_auto_increment is rendered <type> IDENTITY, the attribute alone. ASE takes it on the integer types (INT, SMALLINT, BIGINT, the unsigned variants) and on NUMERIC(p,0) only -- Msg 2764 "Identity field must be a numeric with a scale of 0" for the rest. So a column whose type is known not to take it is refused with a croak naming table, column and type: NUMERIC with a scale (and so numeric / decimal without a size, which is NUMERIC(18,6)), FLOAT, DOUBLE PRECISION, BIT, MONEY, SMALLMONEY, and everything "sybase_column_type" renders as VARCHAR, CHAR, TEXT, DATETIME, SMALLDATETIME or IMAGE (uniqueidentifier among them), plus the ASE character, binary and date/time names that pass through it (unichar, univarchar, unitext, binary, varbinary, time, bigdatetime, bigtime). There is no statement to fall back to: without the attribute the column would deploy and every insert relying on the server for its value would fail. A column whose value comes from somewhere else (a GUID) is not an identity column: the refusal points at auto_nextval, the attribute for a generated value, which renders no DDL here. A type name this module does not know -- a user-defined datatype, which may well be numeric -- is not known to refuse it and keeps its IDENTITY. DBIO::Sybase::Diff::Table applies the same rule to a created table.

What ASE cannot hold at all is refused by install_ddl as well, before the server is asked, with a DBIO::Exception naming table and column: a column of type enum (no such type, Msg 2715), a bit or boolean with a size (it has no length, and the size would be dropped without a word) and a nullable one (ASE keeps no NULL flag for a bit, Msg 2718; declare is_nullable => 0). double is DOUBLE PRECISION.

sybase_column_type

my $sql_type = sybase_column_type($data_type, $size);

Maps a logical DBIO data_type (+ optional size) to its Sybase ASE column type. $size follows the DBIO convention: a scalar length for varchar/nvarchar/char/nchar, or a [precision, scale] arrayref for numeric/decimal. When omitted, the historical fixed defaults (255/1/18,6) apply. An arrayref handed to a character type is read for its first member -- the single length those types take -- rather than numified to its address. Shared by the DDL emitter and DBIO::Sybase::Diff::Table so both sides of the test-deploy-and-compare agree on type rendering. Exportable on request.

uniqueidentifier is VARCHAR(36), whatever $size says: ASE has no GUID type, and the column holds the string form -- 36 characters with hyphens, which also takes the 32 of ASE's newid(). The catalog reads it back as varchar with size 36, which renders the same.

character is CHAR, char varying and character varying are VARCHAR, with the length rules of those; mediumtext is TEXT.

Any other name this map does not know is passed through uppercased, with the $size it was given -- binary 16 is BINARY(16), an arrayref is written comma-separated -- except the ASE datatypes that take no length (int, money, bit, date, time, ...), which stay bare: it may be an ASE datatype not listed here or a user-defined one, and only the server can tell (Msg 2715 if it is neither).

sybase_default_clause

my $clause = sybase_default_clause($default_value, $data_type, $column_name);

Renders the DEFAULT fragment for a column definition (with a leading space), or the empty string when there is no default. A SCALAR ref is emitted verbatim (SQL expression), except the bare current_timestamp, which ASE refuses in a DEFAULT (Msg 128) and which is written getdate(); the literal string 'null' yields no clause.

Any other value is a literal, and the column's $data_type decides how it is spelled:

  • On a numeric column -- every type "sybase_column_type" renders as INT, SMALLINT, BIGINT, NUMERIC, FLOAT, DOUBLE PRECISION or BIT, plus the ASE names int, tinyint, real, money, smallmoney, bit and the unsigned integers (unsigned int as written in DDL, uint / usmallint / ubigint as systypes reports them) -- a value that looks like a number (optional sign, digits with an optional fraction, optional exponent) is emitted unquoted, exactly as written. Quoted, ASE accepts the CREATE TABLE but keeps the default as a VARCHAR constant, and every INSERT that leaves the column out fails: Msg 257 "Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed" (live, ASE 16.0 SP02; the same for BIT).

    A value on a numeric column that is not a number -- 'abc', '', 'true', '$5', ' 0' -- is refused with a croak naming column, value and type. There is no correct rendering to fall back to: quoted it is the Msg 257 above, deployed without complaint and failing at the first defaulted INSERT; unquoted it is a syntax error or an identifier. Guessing a conversion ('true' to 1) would put a value in the schema nobody wrote. An expression the schema author means -- \'$5.00' -- is written as a SCALAR ref and passes verbatim.

  • On every other column (and with no $data_type, which "sybase_column_type" reads as varchar) the value is single-quoted, an embedded ' doubled to '' -- the only escape a Transact-SQL string literal has. '0' on a character column is the string '0'.

$column_name is used only in the refusal message. DBIO::Sybase::Introspect::Columns undoes exactly this rendering when it reads the default back from syscomments, so a literal round-trips unchanged. Shared by the DDL emitter and the Diff table/column emitters so all of them render defaults identically. Exportable on request.

SEE ALSO

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.