/ 
DBIO-0.900004
/ DBIO::Storage::DBI::Capabilities

NAME

DBIO::Storage::DBI::Capabilities - Three-tier capability probing for DBI storage drivers

VERSION

version 0.900004

DESCRIPTION

Capability detection for DBIO::Storage::DBI. Every capability X is answered by asking three tiers in order and taking the first defined answer. The tiers are separate slots, and which one a value lands in is decided by how it was written, not by which accessor was used:

1. The per-storage force -- $storage->_use_X(0|1)

Written on an instance. This is the caller's slot: one application, one storage object, overriding whatever the driver believes. It lives in the storage's own hash and dies with it.

Clear it with $storage->_use_X(undef), which hands the question back to tier 2 (see "Clearing a force" -- undef removes the force, it does not mean "off"; "off" is 0).

2. The class-level declaration -- __PACKAGE__->_use_X(0|1)

Written on a class, in a driver's storage class body. This is the driver's slot for a fact that is a static property of the driver. It lives in a package variable and is inherited down @ISA, so a subclass of the declaring storage gets it too.

It is not the same slot as tier 1 -- set_inherited writes the instance hash for an instance invocant and the $Class::__cag_* package variable for a class one -- which is why a caller can force over a class declaration and get the declaration back afterwards.

3. The determination -- sub _determine_supports_X

The driver's slot for a fact that has to be probed: an answer that depends on the server version, on which DBD is loaded, on anything only the live $dbh knows. Called through _supports_X, a simple-style accessor that caches the answer in a slot wiped on every $dbh reconnection (a reconnect is not guaranteed to land on the same RDBMS version).

_determine_supports_X does not need to exist on a driver -- the runtime ->can-checks for it before calling, and a capability with neither a declaration nor a determiner reads as 0.

$storage->_use_X is the accessor to read: it is the only one that consults all three tiers, and it is what every consumer in core asks ("upsert" in DBIO::ResultSet, "results_exist" in DBIO::ResultSet, "should_emit_if_exists" in DBIO::Diff::Op, "_execute_ddl" in DBIO::Deploy::Base, DBIO::DeploymentHandler). $storage->_supports_X is tier 3's own accessor and answers for tier 3 alone -- on a storage whose driver declared in tier 2 it will report the default while _use_X reports the declaration, and that is not a disagreement to fix but the difference between "what did the probe find" and "what is the answer".

Which tier a driver writes to

Tiers 2 and 3 are both the driver's. Pick by what is being declared, not by what the caller might want to do with it -- the caller's slot is tier 1 and neither declaration form takes it away:

  • A static fact about this driver -- "this storage renders the standard CASE WHEN EXISTS form", "this engine commits DDL implicitly" -- goes in tier 2, __PACKAGE__->_use_X(0|1). It answers as a class method, before any connection and before any storage object exists.

  • A probed fact -- "PostgreSQL 9.5 and up", "MariaDB 10.5 and up" -- goes in tier 3, sub _determine_supports_X, where it can read _server_info.

Declaring in both tiers with the same constant is harmless but says nothing extra; one is enough.

Clearing a force

$storage->_use_X(undef) removes the tier-1 force, and the question falls to tier 2 and then tier 3 -- so a caller who forces a capability off and later changes their mind gets the driver's answer back, not core's default.

This is not what Class::Accessor::Grouped does on its own, and the difference is the reason set_use_dbms_capability exists as an override rather than a straight call to set_inherited: get_inherited reads an instance slot with exists, not defined, so a plain undef assignment would leave a tombstone shadowing the class declaration for the life of the storage instead of clearing anything. Assigning undef to an instance therefore deletes the slot. On a class invocant nothing special happens -- get_inherited already tests defined when it walks classes, so an undef declaration falls through by itself.

Driver-declared capabilities and the per-name accessor

A driver may add capabilities of its own:

__PACKAGE__->mk_group_accessors( dbms_capability     => '_supports_X' );
__PACKAGE__->mk_group_accessors( use_dbms_capability => '_use_X' );

The three tiers then work exactly as above -- but the two per-name accessors are installed in the class that declared them, and core reblesses a storage into its driver class lazily, on first use. On a freshly connected storage that has not been reblessed yet, $storage->_use_X(...) is therefore not a method at all:

Can't locate object method "_use_X" via package "DBIO::Storage::DBI"

Core cannot install it in advance: it does not know the name, and the driver's storage class is not even loaded until determination runs. Two forms work regardless, and a driver's POD should name one of them:

  • DBIO::Yourdb::Storage->_use_X(0|1) -- the class form. Valid from the moment the storage class is loaded, needs no storage object, and is tier 2, so it is the right form for a deployment-wide setting.

  • $storage->set_use_dbms_capability('_use_X', 0|1) and $storage->get_use_dbms_capability('_use_X') -- the two-argument form underneath every _use_ accessor. Name-independent, so it works before the rebless: the getter is on DBIO::Storage::DBI's driver-determination list and reblesses first, the setter writes the tier-1 slot the driver's accessor reads afterwards. undef clears there too.

Both accessors want a storage instance for anything they have to determine. A class-method call only ever succeeds where the value was declared outright in tier 2, because everything else has to be determined from the connected $dbh. Asking a class for an undeclared capability therefore throws, naming the capability.

CONTRACT VERSION

This class exposes an independent compatibility version, distinct from $VERSION (the dist version injected by Dist::Zilla's VersionFromMainModule):

my $v = $class->contract_version;

$CONTRACT_VERSION bumps when the capability-probing interface (the _supports_* / _use_* accessors, the default capability list, or the contract surface that drivers extend with new entries) changes. The dist $VERSION bumps on every release, but two core releases at the same contract version remain wire-compatible. Out-of-tree drivers should record the contract version they were last tested against and compare it against core's at load time, warning (or strict-failing under DBIO_STRICT_CONTRACT) when the shapes have drifted. See docs/adr/ for the contract-version policy.

Version history of this class: 1.3 adds _adjust_bind_values, the one seam for adjusting values before they are bound, on both bind paths.

CAPABILITY LIST

The default capability set is the following. Drivers add their own with __PACKAGE__->mk_group_accessors(dbms_capability => '_supports_X').

insert_returning / insert_returning_bound

INSERT ... RETURNING support.

multicolumn_in

Multi-column IN list optimisation.

placeholders / typeless_placeholders

Driver placeholder style.

join_optimizer

Join-order hinting. On by default; not strictly a capability.

transactional_ddl -- F02 / F10

True iff the engine runs DDL inside a normal transaction (i.e. txn_do { do_ddl() } is atomic). False if the engine forces an implicit COMMIT on DDL (MySQL, Oracle, Sybase, Informix), depends on AutoCommit=on for in-place rebuilds (SQLite), or is capable but deliberately declines for driver-specific reasons (DB2). DBIO::Deploy::Base uses this to decide whether to wrap a multi-statement DDL loop in $storage->txn_do; DBIO::DeploymentHandler uses it to decide whether the whole upgrade can run atomically. Drivers register their engine value via __PACKAGE__->_use_transactional_ddl(0|1) (in the driver's storage class) or by defining _determine_supports_transactional_ddl.

Its per-statement companion is the _ddl_statement_is_transactional hook below: the capability answers for the connection, the hook for the odd statement that cannot be in a transaction anyway.

supports_if_exists -- F12

True iff the engine / diff emitter can use IF [NOT] EXISTS in DDL (CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS, ALTER TABLE ... ADD COLUMN IF NOT EXISTS). False on engines that do not parse the syntax (older MySQL / MariaDB before 10.0.2, some SQLite builds, others). DBIO::Diff::Op::should_emit_if_exists probes this to decide whether to emit guarded DDL. Drivers register their engine value via __PACKAGE__->_use_supports_if_exists(0|1) or by defining _determine_supports_supports_if_exists.

supports_upsert -- ADR 0036

True iff the engine has a single-statement, server-side atomic upsert that rides on INSERT (PostgreSQL / SQLite ON CONFLICT ... DO UPDATE|NOTHING, MySQL ON DUPLICATE KEY UPDATE) and the driver's DBIO::SQLMaker subclass implements the on_conflict option of sql_maker->insert. False everywhere by default -- including on engines whose upsert is a MERGE statement (Oracle, DB2, SQL Server), which is a different statement shape and not covered by this capability.

"upsert" in DBIO::ResultSet probes this and throws when it is false; it never degrades silently to a client-side read-then-write. Drivers register their engine value via __PACKAGE__->_use_supports_upsert(0|1) or by defining _determine_supports_supports_upsert (version-gated, as PostgreSQL does for insert_returning).

MERGE-based upsert is a deliberate, permanent non-goal -- not an oversight. The exclusion of the MERGE engines (Oracle, DB2, SQL Server) above is a decision, not an accident of the default 0: they stay without $rs->upsert under this capability for good, because MERGE is a different feature rather than a driver nobody has written yet. Two structural facts say so:

  • Two correlation names, one vocabulary. This API's condition vocabulary ("upsert" in DBIO::ResultSet's update_where) names the stored row with a bare column and the attempted row with { -attempted => $col }, and that is unambiguous only because an INSERT ... ON CONFLICT has exactly one target correlation plus the one EXCLUDED pseudo-row. A MERGE has two named sides -- source and target -- so the stored side comes out unqualified and ambiguous. Covering it would need a second, symmetric qualifier this vocabulary does not have.

  • The identity guarantee does not carry. ADR 0036's rule that a driver may declare supports_upsert only if it can prove which row it wrote rests, for the INSERT-borne engines, on an insert having happened (RETURNING, or the engine's insert-identity channel). When a MERGE takes its UPDATE branch there was no insert and no generated key -- verbatim the SQLite-below-3.35 failure mode ADR 0036 rejects as "silent bad data, worse than undef". The guarantee must be re-derived for MERGE, not inherited.

A future MERGE upsert therefore needs its own capability (supports_merge_upsert or the like) with guarantees reasoned from scratch -- never a widening of this flag or a second alias bolted onto this vocabulary. See ADR 0036 for the full rationale.

supports_upsert_where -- ADR 0036

True iff the driver renders the conditional conflict branch -- DO UPDATE SET ... WHERE ..., the where key of the on_conflict structure, reachable as "upsert" in DBIO::ResultSet's update_where option. PostgreSQL and SQLite have the grammar; MySQL has no equivalent (ON DUPLICATE KEY UPDATE takes no WHERE, and a per-column IF() does not suppress the write). Implies nothing on its own: it is only ever consulted together with supports_upsert.

This is the one dialect difference that gets a flag of its own, and the reason is that it is the only one a driver can ignore silently. Dropping the conflict target emits invalid SQL on PostgreSQL, ignoring action => 'nothing' leaves no set to write -- those announce themselves. Dropping the WHERE produces a perfectly valid unconditional UPDATE, i.e. the last-write-wins overwrite the condition was written to prevent. Default 0 therefore keeps every driver that has never heard of the option correct, and core throws instead of handing it one.

Answer about the branch itself; never derive the answer from supports_upsert. The two flags earn their keep by answering two different questions, so a flag that always repeats the other one carries no information at all -- and deriving it also imports whatever the other gate happens to require for its own reasons. PostgreSQL's supports_upsert, for one, additionally demands INSERT ... RETURNING, because that is what proves the row handed back is the row that was written; a condition that does not hold writes no row by design, so folding that requirement in here would make this flag report a limitation the conditional branch does not have. Nor is a second 0 any help diagnostically: core probes supports_upsert first and throws there, so a driver gated off for an unrelated reason is already named by the right gate, with that gate's message.

The legitimate uses of _determine_supports_supports_upsert_where are the ones that still measure the branch: an engine whose own support for the conditional clause is version-dependent, which is the same fact, probed. What is not an input to this answer is anything the branch does not depend on -- RETURNING, the identity transport, connection state. Drivers register their engine value via __PACKAGE__->_use_supports_upsert_where(0|1) where it is a static fact, or as a _determine_supports_supports_upsert_where where it has to be probed; both leave the _use_ force to the caller, because the class-level declaration and the per-storage force are different slots ("Clearing a force"). For PostgreSQL and SQLite the rule yields an unconditional yes: ON CONFLICT arrived complete on either engine (PostgreSQL 9.5, libsqlite 3.24), WHERE included, so neither has a version that speaks the conflict grammar but not the condition.

supports_exists_query -- ADR 0037

True iff the storage can render a boolean-valued EXISTS query this engine accepts -- either because the base rendering of "_exists_value_sql" in DBIO::Storage::DBI is right for it (SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END, correct on PostgreSQL, MySQL/MariaDB, SQLite, DuckDB, SQL Server and Sybase), or because the driver overrode that hook with the spelling its engine needs (Oracle FROM DUAL, DB2 FROM SYSIBM.SYSDUMMY1, Firebird FROM RDB$DATABASE).

"results_exist" in DBIO::ResultSet, "results_exist_async" in DBIO::ResultSet and "results_exist_as_query" in DBIO::ResultSet probe this and throw when it is false; they never degrade silently to "has_rows" in DBIO::ResultSet, which is the portable alternative the exception names. False everywhere by default: the flag is not vacuous for a driver that does not override the hook, because it asserts that somebody checked -- and the engines nobody has checked are exactly the ones a default-on would ship broken SQL to. Drivers register their engine value via __PACKAGE__->_use_supports_exists_query(0|1) or by defining _determine_supports_supports_exists_query.

PER-STATEMENT DDL SAFETY

_ddl_statement_is_transactional

sub _ddl_statement_is_transactional {
  my ($self, $stmt) = @_;
  return 0 if $stmt =~ /^\s*ALTER \s+ TYPE \b .* \b ADD \s+ VALUE \b/xi;
  return 1;
}

Asked once per DDL statement by "_execute_ddl" in DBIO::Deploy::Base, but only on a storage that already reports transactional_ddl. Answer false for a statement this engine refuses to run inside a transaction, or runs but leaves unusable until commit.

_execute_ddl groups consecutive statements by this answer and keeps the original order: each run of transaction-safe statements gets one $storage->txn_do, and a statement that answered false runs on its own, outside any transaction. A batch where everything answers true produces a single run and is therefore identical to bracketing the whole loop, which is why a driver that never defines this hook sees no change at all.

The default is true, and overriding it is a deliberate act with a cost: the batch is no longer one transaction, so a failure does not take the whole upgrade back, and core announces the split once. Only define it where the engine leaves no choice. The known case is PostgreSQL's ALTER TYPE ... ADD VALUE, rejected outright in a transaction block before server 12 and, from 12 on, accepted while the new value stays unusable until that transaction commits.

Match on the statement text with care — it is the DDL your own driver emits, not arbitrary user SQL, so anchoring on the forms your DDL and diff classes actually produce is both sufficient and safer than a broad pattern.

DDL SPLITTING

_ddl_sql_dialect

sub _ddl_sql_dialect { 'postgresql' }

Names the SQL dialect the engine's DDL is written in, for the one reader that must know it: the splitter "_split_statements" in DBIO::SQL::Util that "_execute_ddl" in DBIO::Deploy::Base (and, for the async deploy, DBIO::Storage::Async) cuts install and upgrade DDL with. The text of a statement does not say which dialect it is in, and one place -- the backslash in a string literal -- reads differently: a character in standard SQL (PostgreSQL, SQLite), an escape in MySQL and MariaDB. Left to guess, the splitter takes both readings, which cuts a literal that ends on a backslash in front of a later literal with a semicolon. The engine knows; this hook is how it says so.

Allowed values: postgresql, sqlite, mysql (MariaDB declares mysql), oracle (a PL/SQL unit is one statement that keeps the semicolon behind its END). The default is undef, "not declared", which leaves the splitter exactly as it was: both readings. An unknown name makes the splitter throw, so a typo is loud instead of silently reading the wrong way.

It is a fact about the engine like transactional_ddl and "_ddl_statement_is_transactional", which is why it lives on the storage and not on a deploy class: the sync deploy reaches it through $schema->storage, the async deploy through its owner storage, and both end in the same answer. Define it once on the driver's base storage class.

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.