NAME
DBIO::Sybase::Diff::Column - Diff Sybase ASE columns
VERSION
version 0.900007
DESCRIPTION
Compares two column sets and generates column-level diff operations.
METHODS
lossy
my $bool = $op->lossy;
True for the alter of a column whose type change can lose or alter stored values (optional operation method, ADR 0080): "apply" in DBIO::Deploy::Base runs such a diff with allow_destructive => 1 only. The answer is "column_change_lossy" in DBIO::Diff::Compare over data_type and size of the two columns, whose type names are the systypes names DBIO::Sybase::Introspect::Columns reads (int, smallint, tinyint, bigint). A change keeps every value when the type stays and the size does not get smaller (the scale of a numeric equal), or when it is one of the integer widenings: tinyint to smallint, int or bigint, smallint to int or bigint, int to bigint. Every other type change is lossy, the unsigned types included. A change of nullability or default alone is not (NOT NULL over a NULL is refused by "_check_before_apply" in DBIO::Sybase::Deploy), and neither is an added column.
A COLUMN IS RENDERED, NOT NAMED
add and alter emit the rendered type from "sybase_column_type" in DBIO::Sybase::DDL -- the same function DBIO::Sybase::Diff::Table and install_ddl use -- never the bare data_type string. The two sides of test-deploy-and-compare have to agree on type rendering or the diff never converges: a column that CREATE TABLE deploys as VARCHAR(64) but an incremental ADD spells varchar is VARCHAR(255) on the server, so the next introspection reports the size back as changed and the diff reproduces itself forever.
The size is carried in its own model field (size), not inside data_type, because that is the shape DBIO::Sybase::Introspect::Columns produces -- syscolumns.length / prec / scale alongside the type name. Dropping it here is therefore not a cosmetic difference between two spellings of one type; it is a column of the wrong width.
NULLABILITY IS ALWAYS SPELLED OUT
Both an add and an alter write an explicit NULL or NOT NULL on every statement, even when nothing about the nullability moved. Omitting it does not leave the nullability to the column, so "say nothing and the sensible thing happens" is not available in this dialect.
For an add the manual says so in one sentence -- "If you do not specify null or not null, the SAP ASE server uses not null by default. However, you can switch this default using sp_dboption to make the default compatible with the SQL standards" (Reference Manual: Commands, alter table, parameter null | not null). A bare add therefore lands as NOT NULL or NULL depending on a database option this driver cannot see, and a column the schema declared nullable is created either way round. It is worse than a coin toss, because the same DDL produces different columns on two servers.
For an alter the same conclusion needs three facts from the modify production:
The nullability is optional in the production --
modify column_name [ datatype [null | not null] ]-- so a statement without it parses, and there is no error to warn anybody.What it falls back to is not the column's own state but a default: for a user-defined datatype the Transact-SQL User's Guide states it outright ("If you do not specify either NULL or NOT NULL as the default, columns use the default specified by the user-defined datatype"), and for a system datatype the corresponding default is the database's, which is
not nullunlesssp_dboption ... 'allow nulls by default'has been turned on. Nothing in that chain consults the existing column.That default is therefore server configuration this driver cannot see. Which way a bare modify would land is a property of the target database, not of the schema being deployed -- which makes omitting the nullability worse than Transact-SQL's fixed "if not specified, the column is nullable": at least that one is deterministic.
Restating an unchanged nullability is consequently not redundancy, it is the only spelling under which a type change keeps the column it found.
A NEW TABLE'S COLUMNS ARE NOT ADDED TWICE
diff skips a table that is being created by this same diff. Its columns are already in the CREATE TABLE DBIO::Sybase::Diff::Table emits, so adding them again produced a migration that created a column and then added it a second time -- CREATE TABLE fresh (id INT NOT NULL); ALTER TABLE fresh ADD id INT -- which ASE rejects as a duplicate column name. Both table models were already being passed to diff for this guard; they were accepted and never read.
The guard asks whether the table is known new -- absent from the source tables and present in the target tables -- rather than merely absent from the source. A caller that supplies no table model at all knows nothing to be new and so skips nothing, which keeps column-level diffing usable on its own.
THE ADD CLAUSE ORDER IS DEFAULT, THEN NULLABILITY
The add production puts the default before the nullability, which is the reverse of the Transact-SQL order a reader coming from DBIO::MSSQL will expect. Verbatim from the Reference Manual: Commands, alter table:
alter table [[database.][owner].]table_name
{add column_name datatype}
[default {constant_expression | user | null}]
{identity | null | not null [not materialized]}
So this class emits
ALTER TABLE t ADD extra VARCHAR(64) DEFAULT 'x' NOT NULL
and never ... NOT NULL DEFAULT 'x'. The create table production orders its column definition the same way, so the order is the dialect's rather than a property of alter.
A NOT NULL ADD CARRIES ITS DEFAULT
A not-null add whose column names no default is refused -- as_sql croaks naming the table and column -- rather than rendered and sent. The manual makes the default mandatory in that case, unconditionally and not only for a populated table: "If you specify (or imply) not null for the newly added column, a default clause is required. The default value is used for all existing rows of the newly added column, and applies to future inserts as well." The Usage section repeats it and admits exactly one exception: "If you add a not null column, you must also specify a default clause. This rule has one exception: if you add a user-defined type column, and the type has a default bound to it, you need not specify a default clause."
Refusing is the house answer for a statement that cannot be made correct from what the model carries -- DBIO::MSSQL::Diff::Column croaks the same way on a default drop with no default_constraint_name. The alternative is to emit the statement anyway and let the server reject it, which trades a message naming the schema column for an ASE error naming neither.
The one documented exception is out of reach on purpose. Whether a bound default exists is a property of the server, readable only by querying the type's bindings, and the model this class renders from carries no such field; guessing that a passthrough type name is a user-defined type with a default bound to it would turn a loud refusal into a silent gamble. Naming the default in the schema is the fix in that case too, and it is correct on every server rather than on the ones where the binding happens to exist.
The predicate is the rendered default clause, not default_value itself, because "sybase_default_clause" in DBIO::Sybase::DDL renders nothing for the literal string 'null'. That case reaches the server as the very statement it rejects, and it is a contradiction in the model besides -- a column that forbids nulls defaulting to one.
THE DESIRED-STATE CONTRACT APPLIES TO RENDERING TOO
as_sql reads the target the way "changed_fields" in DBIO::Diff::Compare reads it when deciding this op exists at all: a field the target leaves undef was not prescribed, so the live value stands. That is applied per field, not per hashref -- data_type, size and not_null each fall back to the from side on their own.
not_null is the field where it matters most. Core lists it among the desired-state-skippable scalars, so a target that does not prescribe it produces no not_null entry in the changed-field list and the op exists because some other attribute moved. Reading it from the target alone then meant a type-only or default-only alter emitted the database's null default over a live NOT NULL column, silently, on a statement nobody asked to change the nullability at all.
default_value is deliberately not read through that fallback. Re-stating the live default is not the harmless no-op it looks like -- in this dialect it is a second statement, and one ASE can refuse (see "THE ALTER IS A MODIFY, THE DEFAULT IS A SEPARATE REPLACE"). "This column should have no default" is expressible: the comparator reports a default the target dropped (a live DEFAULT NULL counts as none), and the op removes it with alter table t replace c default null, the Reference Manual's own spelling. The live default is never restated.
THE ALTER IS A MODIFY, THE DEFAULT IS A SEPARATE REPLACE
ASE has no ALTER COLUMN. That spelling is Transact-SQL's -- it is what DBIO::MSSQL::Diff::Column emits -- and it appears nowhere in the ASE alter table entry, in any version consulted. The subclause that changes an existing column is modify, and the one that changes an existing column's default is replace. Verbatim from the Reference Manual: Commands, alter table (16.0), with the alternatives not at issue elided:
alter table [[database.][owner].]table_name
...
| modify column_name
[datatype [null | not null]]
...
| replace column_name
default {constant_expression | user | null}
...
Both hang off alter table itself, so nothing stands between the table name and the keyword. The manual's own examples are alter table authors modify city varchar (30) null and alter table authors replace phone default null.
The two lines quoted above are spelled the same way in the 15.5 entry. The 15.0 entry differs in one respect, and only in the direction this class already takes: there the datatype is not bracketed -- modify column_name datatype [null | not null] -- so on that server a modify naming no type does not parse at all.
A DEFAULT written inside the modify is not a second spelling of that second statement; it is not in the production at all. The parameter list keeps the two apart in one sentence each -- modify: "Specifies the name of the column for which you are changing the datatype or nullability"; replace: "Specifies the column for which to replace the default value with the new value specified by a following default clause".
Two statements, not one
alter table does take several subclauses at once, but the sentence that allows it rules out this pairing twice over: "add, drop, modify, and lock subclauses are useful when changing an existing table's schema. A single statement can contain any number of these subclauses, in any order, as long as the same column name is not referenced more than once in the statement" (Reference Manual: Commands, alter table, "Altering Table Schema"). replace is not among the four named, and a combined statement would name one column twice, which is forbidden even for the four that are.
So a default change is rendered as a second statement, separated by ; -- the shape DBIO::Sybase::Diff::Index's alter already emits for its DROP INDEX / CREATE INDEX pair, and the separator the apply path reads: "_execute_ddl" in DBIO::Deploy::Base splits the migration on ;, so two statements sharing a line become two $dbh->do calls.
When the replace is emitted
Only when the target prescribes a default and the rendered clause differs from the live column's -- never as a restatement of the default already there. The predicate is the rendered clause for the same reason the add's refusal uses it: "sybase_default_clause" in DBIO::Sybase::DDL renders nothing for the literal 'null', and comparing default_value directly would compare two SCALAR refs by address rather than by the expression they carry.
Emitting it on every alter of a defaulted column would look like the safer choice and is not: a column whose default was bound with sp_bindefault rejects the statement -- "You cannot replace a user-defined default bound to the column with sp_bindefault", and "First use sp_unbindefault to unbind the default" (Reference Manual: Commands, alter table, "Changing Defaults"). A redundant replace would turn a migration that had nothing to do on that column into a failing one.
The manual's list of what a modified table keeps -- "The modified table retains the existing space management properties (max_rows_per_page, fillfactor, and so on) and indexes of the table" (same entry, "Altering Table Schema") -- does not name a column's default, which left open whether a modify drops it and a type-only alter would then have to put it back. Live on ASE 16.0 SP02 it does not: a column with default 'old', modified to varchar(32) null, still inserted 'old'.
A default-only change is the replace alone
When the comparator ("changed_column_fields" in DBIO::Diff::Compare) reports nothing but default_value as changed, the modify is left out and the alter is the replace statement alone -- ALTER TABLE t REPLACE name DEFAULT 'new', summarised as ALTER TABLE t REPLACE name. The modify would only restate a type, width and nullability that did not move, and it is not free: a data copy "is required ... for most alter table ... modify commands" (same entry, "Altering Table Schema"). Live on ASE 16.0 SP02 a replace changes the default with no modify before it.
as_sql asks the comparator itself, with the same call diff makes, rather than re-deciding which fields moved; the test is a set test ("everything changed except default_value" is empty), not a position in the list. A default-only change that renders no replace -- the literal 'null' -- falls back to the modify it has always emitted, so the op never renders as an empty statement.
An expression default is a SCALAR ref, and diff hands the comparator the SQL behind it rather than the ref, whose string is its address: the catalog fetch of DBIO::Sybase::Introspect::Columns reads every DEFAULT getdate() that way on both sides of a deploy diff, and two equal expressions must not be a change. An expression and a literal of the same text still differ -- one renders unquoted, the other quoted.
Dropping a default is not emitted at all. ASE spells it replace col default null, but core's comparator skips an undef default_value on the target, so the drift never reaches this class -- and the literal 'null', the one value that could stand for it in the model, is exactly what sybase_default_clause renders as no clause.
THE DROP NAMES THE COLUMN AND NOTHING ELSE
A dropped column renders as ALTER TABLE t DROP old, with no COLUMN keyword. The drop subclause has two arms and keywords only one of them -- verbatim from the Reference Manual: Commands, alter table (16.0):
| drop {column_name [, column_name]... | constraint constraint_name}
So drop constraint is spelled out -- which is what DBIO::Sybase::Diff::ForeignKey's drop already emits -- while a column is named bare. The 15.5 entry publishes that line unchanged, and its own worked example drops three columns without the keyword: alter table titles drop advance, notes, contract. The Transact-SQL User's Guide states the column arm on its own, alter table table_name drop column_name [, column_name], with the same example.
DROP COLUMN is Transact-SQL's spelling -- it is what DBIO::MSSQL::Diff::Column emits -- and it is the same import as the ALTER COLUMN corrected above, arriving at a different subclause. Only as_sql carried it: this class's summary has said DROP old since the file was written, so the two methods spelled one statement two ways.
The production takes several columns per statement -- column_name [, column_name]... -- and this class still emits one statement per dropped column. That is a compact form of the grammar left unused, not a form the grammar refuses; whether the diff should batch drops is a question about op granularity and is deliberately not answered here.
DATA-COPYING ALTERS NEED A DATABASE OPTION
Several of the statements this class renders make ASE copy the table: a drop, a modify that changes the stored type (INT to BIGINT), and an add of a NOT NULL column (with the default it needs). In a database where neither option below is in effect, each fails with Msg 11052 (live, ASE 16.0 SP02, a database fresh from CREATE DATABASE, table holding one row):
Neither the 'select into' nor the 'full logging for alter table'
database options are enabled for database 'k50_a'. ALTER TABLE with
data copy cannot be done.
A change that needs no copy -- widening a VARCHAR, a replace of the default, a nullable add -- runs regardless.
What makes them run is select into/bulkcopy/pllsort on the target database. full logging for alter table, the other option the message names, is not a substitute on every database: it applies only to a database whose log is on its own device (log on at CREATE DATABASE), and on a database with mixed data and log segments -- what a plain CREATE DATABASE gives -- ASE accepts the setting, says it "will be ignored while the log and data segments remain mixed", and the alter fails as before. A system administrator turns the option on from master and checkpoints the database for it to take effect:
use master
exec sp_dboption 'mydb', 'select into/bulkcopy/pllsort', true
use mydb
checkpoint
select into/bulkcopy/pllsort permits minimally logged operations, and the transaction log of such a database cannot be dumped until the next full database dump; whether that is acceptable is the administrator's decision. This driver neither sets the option nor checks for it before applying a diff. Because "apply" in DBIO::Sybase::Deploy is not transactional, a diff that meets the error part way stops there, with every statement before it applied -- see "Data-copying ALTER TABLE" in DBIO::Sybase::Deploy.
LIMITS
Every statement here is written against the documented grammar; none of it has been executed against a server -- this driver's live suite exercises storage, not the introspect/diff path. All three forms above are therefore sourced, not verified: the add's clause order and default requirement, the alter's split into modify plus replace, and the drop's bare column name, are quoted from the Reference Manual, and no ASE has confirmed that a server accepts what those quotations were read to mean.
Two facts about the alter have since been checked by hand on ASE 16.0 SP02: a replace alone changes the default, and a modify keeps it. That is a probe, not a suite -- the statements this class renders still are not run by anything automated.
Independent of spelling: "You cannot run alter table from inside a transaction" -- verbatim in the 15.0 and 15.5 entries, which carry the whole command on one page; the 16.0 entry is split into subsections and its "Restrictions for alter table" does not repeat the sentence. That is what DBIO::Sybase::Storage::ASE's _use_transactional_ddl(0) keeps core's deploy path from doing. The data copy above additionally needs select into/bulkcopy/pllsort turned on in the database ("If alter table performs a data copy, select into/bulkcopy/pllsort must be turned on in the database that includes the table whose schema you are changing" -- same section; measured live, see "DATA-COPYING ALTERS NEED A DATABASE OPTION"). A narrowing modify fails outright if any existing row holds NULL where not null is being asked for, or data too wide for a reduced length.
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.