Revision history for DBIO-MSSQL

0.900007  2026-11-17
    * Fixed
        - Columns of type datetime2, datetimeoffset and time with DateTime
          inflation (inflate_datetime) have a channel of their own each.
          datetime2 is written and read with all seven fractional digits;
          datetimeoffset is written with the offset of the value (a value in
          an Olson zone: the offset it has at that moment) and read with it;
          time goes through parse_time/format_time with up to seven digits.
          All three are written in forms no language or DATEFORMAT of the
          session changes (YYYYMMDD hh:mm:ss.fffffff [+HH:MM]). What is read
          is what the DBD hands over: DBD::ODBC the full text of the server;
          DBD::Sybase over TDS 7.3 and 7.4 the value the client library has
          already cut to milliseconds (a datetimeoffset as the UTC instant,
          without its offset), over TDS 7.2 and below the full text.
        - A date column with DateTime inflation (InflateColumn::DateTime)
          is written and read over DBD::ODBC and DBD::Sybase. DBIO writes
          'YYYYMMDD', which reads the same under every language and
          DATEFORMAT, and reads what the DBD hands over ('YYYY-MM-DD' over
          DBD::ODBC, 'YYYY-MM-DDT00:00:00.000Z' over DBD::Sybase).
        - A datetime or smalldatetime column keeps its date under any
          language or DATEFORMAT of the session (SET LANGUAGE German or
          French, a login's default language): DBIO writes it as
          'YYYYMMDD hh:mm:ss[.mmm]', which SQL Server reads the same under
          every setting, and leaves the session of the user as it is.
        - Error texts name no values whatever language the server reports
          in. SQL Server writes its messages in the language of the session
          (SET LANGUAGE, the login's or the server's default) and quotes
          the offending value in them; the English wording the masking
          knew did not hold there, and a short bound value or a value the
          server quotes (in a German conversion error, in a French
          duplicate-key message) stood in the exception. The
          masking now goes by the number of the message (conversion and
          overflow, syntax, JSON, duplicate key, truncation) and the marks
          the server quotes with; where the language writes the value
          without a mark, the text of that message falls away and its
          number stays. Over DBD::ODBC, which has no number in the text,
          the storage reads it from the diagnostic records of the failing
          handle. English texts are masked as before.
    * Documentation
        - The dbio-mssql and dbio-mssql-database skills describe the
          limit dialect, the type maps, the declared capabilities, the
          -ms shortcut and the live test path as the code has them.
    * Tests
        - DBIO_TEST_SERVER_LANGUAGE (de, fr) runs the live suite with the
          server reporting in that language; the tests that read an English
          wording of the server say us_english for their own session.
        - The test suite passes on an install with only the required
          modules: the DBD::Sybase half of the server data type test skips
          when DBIO::Sybase is not installed.
        - The live tests that write or read text beyond ASCII state the
          client character set they need (ClientCharset=UTF-8 in the ODBC
          DSN, a UTF-8 LC_CTYPE for DBD::Sybase) instead of taking it from
          the locale of the process, so the live suite also passes under a
          non-UTF-8 locale such as de_DE.iso88591. The refusal of such text
          under a client character set that is not UTF-8 is tested with an
          explicit ClientCharset=ISO-8859-1 in the DSN.
        - The live test files that need a client character set also load
          and skip without a DSN on a perl without I18N::Langinfo
          (Strawberry Perl 5.20 for Windows has none; it works on every
          platform from perl 5.28). The helper then reads the code set from
          the name of the locale; where that does not say UTF-8 the code set
          is unknown, nothing dies, and only the DBD::Sybase half of the
          tests that write or read text beyond ASCII skips, with the reason.

0.900004  2026-11-08
    * Storage
        - A schema that declares DBIO::MSSQL::Storage gets, on first use,
          the class for the DBD of its DSN (DBD::ODBC or DBD::Sybase,
          including the FreeTDS class), without a "Driver mismatch" warning
          and without asking the server. A private subclass pinned over
          DBD::Sybase, or an ODBC class pinned over a dbi:Sybase: DSN, is
          refused with a message that names the class to derive from (ADR
          0008).
        - Connection parameters without a DSN ({dbname, host, port}) give a
          DBD::Sybase DSN; host and port come as a pair. A host bound by a
          Replicated view goes into a DBD::ODBC DSN as Server=host[,port].
        - supports_exists_query and transactional_ddl are declared:
          results_exist works, and a deploy runs in one transaction.
        - explain drives SET SHOWPLAN_ALL around the query over DBD::Sybase
          and is refused over DBD::ODBC, which returns no plan.
        - except_all and intersect_all are refused, SQL Server has neither.
        - Binary columns carry their bytes over both access paths. Over
          DBD::Sybase they are read back as bytes, and a bind value of more
          than 4000 bytes arrives whole.
        - Over DBD::Sybase a bound string beyond ASCII, a value over the
          session's TEXTSIZE (LongReadLen) and a SQL_VARIANT result are
          refused instead of stored padded or cut, and a bound empty string
          is stored as ''. The POD of DBIO::MSSQL::Storage::Sybase carries
          the support matrix of DBD::ODBC against DBD::Sybase.
        - Over DBD::Sybase and FreeTDS the key of a new row is read with
          SCOPE_IDENTITY(), populate on the NoBindVars class inserts row by
          row, and populate stops at the first tuple the server refuses.
        - A transaction with an open cursor over DBD::Sybase and FreeTDS
          stays on its connection (DBIO::Sybase::Storage::FreeTDS).
        - The text SQL Server writes into an error carries no values: the
          value of a failed conversion or overflow, the key values of a
          duplicate key, the start of a truncated value and the token of a
          syntax error are replaced by ***, bound values of four characters
          or more everywhere in the text. Table, column and constraint
          names, the error position and "with N bind values" stay
          (DBIO::MSSQL::Util::mask_error_details, ADR 0009).
        - Placeholders are found with split_at_placeholders, so a ? in a
          literal or a quoted name is text; error texts carry neither
          password, DSN nor bind values.

    * ODBC
        - Text beyond the database code page arrives in NCHAR, NVARCHAR and
          NTEXT columns (CJK, emoji); a client character set that is not
          UTF-8 is refused. A text column is read back as characters.
        - LongReadLen defaults to 1 GiB, so LOB columns are not cut at 80
          bytes; the POD names the memory price.

    * DDL
        - install_ddl writes FOREIGN KEY constraints from belongs_to as
          named ALTER TABLE statements after all tables, named unique
          constraints (UQ_<table>_<columns>) and PK_<table>. No second
          index is written over the columns of a key.
        - Indexes follow the canonical declaration (idx, indices) with an
          mssql block for the kind, and CREATE [UNIQUE] [CLUSTERED |
          NONCLUSTERED] INDEX is spelled as the server expects. Filtered
          indexes (a where key) are introspected, written and diffed.
        - schema.table is made in its schema, with CREATE SCHEMA; schemas =>
          [...] reads further schemas.
        - One function quotes every name (reserved words, [ ] brackets),
          string defaults are literals, and foreign key columns follow the
          order of the key they reference.
        - Decimal precision and scale, character lengths and (max) are kept;
          a column without is_nullable deploys as NOT NULL.
        - What SQL Server cannot hold is refused with an exception naming
          table and column: enum, a sized bit, dotted names, cascade paths
          (Msg 1785), name clashes, a second clustered index, bad type
          parameters and string defaults on numeric or date types.

    * Diff
        - Statements are ordered for the server: foreign key drops first,
          new tables without inline keys, foreign key adds last, so
          referencing tables in any order upgrade.
        - Primary keys are added, replaced and dropped on existing tables,
          CLUSTERED or NONCLUSTERED included; foreign keys, indexes and
          unique constraints around a changing key or column are dropped and
          created again. Unique constraints are constraints, not indexes.
        - ALTER COLUMN is one statement with type and nullability, and a
          removed default drops its DEFAULT constraint.
        - Views are created, replaced and dropped in dependency order;
          cycles are refused before the first statement. Index kind is
          compared.
        - Names follow the collation of the live database, and a missing
          schema is created.

    * Deploy
        - apply and upgrade are the base class's: allow_destructive and
          dry_run hold here, lossy column type changes need
          allow_destructive, and a NOT NULL column without default over rows
          or NOT NULL over a NULL is refused before the first statement.
        - The temporary database takes the collation of the live one and its
          connection from the connect info; a handle with AutoCommit off is
          refused, and DBIO::MSSQL::Storage::Sybase deploys with
          DBIO::MSSQL::Deploy.

    * Introspect
        - Every catalog column is aliased in lower case, independent of the
          handle's FetchHashKeyName. Columns carry the DEFAULT constraint
          name and the primary key's name and kind; views carry their
          definition.

    * SQLMaker
        - order_by with -nulls => 'first' | 'last' is emulated with a CASE
          sort key, also in the ROW_NUMBER limit.

    * Test Modules
        - DBIO::MSSQL::Test::Conformance answers the shared DDL, Diff and
          Bind case tables of core, live against a server with a database
          per case.

    * Documentation
        - README states the async stance of this sync-only driver and the
          Perl 5.20 floor; the POD describes limit dialect, ordered
          subselects and MONEY as the code has them.
        - ADR 0007 records the storage shape (a transport-neutral base plus
          one class per DBD); ADR 0036 proposes a MERGE-based upsert, and
          $rs->upsert throws on SQL Server until it lands.

    * Packaging
        - Requires DBIO 0.900004. The cpanfile names the modules Storage
          loads (Context::Preserve, Scope::Guard, Try::Tiny), and DBD::ODBC
          is recommended.
        - Every module carries $VERSION in step with the distribution.

0.900001  2026-07-12
    * Diff
        - Fix DROP INDEX / DROP TABLE collision in full-schema sync (karr #15,
          from core #77 / dbio-postgresql #32). When diff reconciled a live
          schema containing a table absent from the target (removed from the
          app's DBIO classes, or an unmanaged leftover), it emitted both
          DROP TABLE <t> and a later standalone DROP INDEX <idx> ON <t>; since
          MSSQL's DROP TABLE already removes the table's own indexes, the
          standalone DROP INDEX failed once the table was gone ("Invalid object
          name") and aborted apply()/upgrade(). DBIO::MSSQL::Diff::Index now
          receives the tables sections (threaded in by DBIO::MSSQL::Diff) and
          suppresses a standalone DROP INDEX for any index whose owning table is
          itself being dropped in the same pass. Indexes of surviving tables are
          still dropped as before; the two-argument diff() call stays backward
          compatible (absent table sections suppress nothing).

    * Tests
        - Add t/51-diff-drop-table-index-cascade.t and extend t/50-diff.t:
          full-diff regression for the DROP INDEX / DROP TABLE collision
          (karr #15) plus Diff::Index unit cases for the tables-aware drop
          suppression and its over-suppression guard.

    * Storage
        - new DBIO::MSSQL::Storage::ODBC (DBD::ODBC transport on top of the
          transport-neutral DBIO::MSSQL::Storage base). ODBC connections are now
          reblessed into this class instead of DBIO::MSSQL::Storage::Sybase, so
          the ODBC path no longer pulls in DBIO::Sybase. The Sybase class stays
          for the DBD::Sybase path. Needs DBIO core with the matching connector
          mapping.

    * Tests
        - skip the optional-dependency tests instead of failing when
          DBIO::Sybase (DBD::Sybase path) or DBIO::SQLite (SQL-comparison
          test helper) is not installed (Codeberg #1)
        - t/10-mssql.t and t/20-mssql-core.t: port leftover DBIx::Class names
          to the DBIO storage classes (DBIO::MSSQL::Storage::Sybase /
          ::NoBindVars) and the surviving PK::Auto compat shim, so the
          DBD::Sybase path reblesses and runs against a live server; skip the
          multiple-active-statements sections (need dynamic-cursor/MARS support
          not ported to DBIO) consistently instead of dying mid-file; and
          replace the removed-API "RNO detection" block (sql_limit_dialect /
          sql_maker->{limit_dialect}) with an assertion that the storage is
          wired to DBIO::MSSQL::SQLMaker (apply_limit). Both files now run
          green to exit 0 over a named-server dbi:Sybase: DSN.

    * Fixes
        - DBIO::MSSQL::Storage::Sybase::_init compared the FreeTDS version
          with a numeric '>', but modern versions like "1.3.17" aren't valid
          numbers, warning "Argument ... isn't numeric" on every connect over
          the Sybase/FreeTDS transport. Now compared via version->parse
          (dotted-version, matching the ASE sibling); behaviour unchanged
          (1.3.17 > 0.82 still disables statement caching, the ||999
          sentinel for an undeterminable version is preserved). MARS
          (multiple-active-statements) support was investigated and
          confirmed infeasible on this transport -- documented as a
          permanent limitation, not a gap to close. karr #13, #14.

    * Dependencies
        - Requires core DBIO >= 0.900001 (family-wide version alignment)

    * Project
        - Added SECURITY.md (CPAN Security Group guidelines v1.5.0)

0.900000  2026-06-23
    First release. Microsoft SQL Server driver for DBIO.

    * Storage
        - SCOPE_IDENTITY() identity retrieval, MONEY column casting,
          ordered-subselect safety, SAVE TRANSACTION savepoints, deferred
          FK checks via sp_msforeachtable, and NEWID() GUID/random support
        - DBD::ODBC and DBD::MSSQL connection paths
        - DBD::Sybase/FreeTDS path with placeholder detection (NoBindVars
          rebless) and statement-cache workarounds

    * SQL generation
        - MSSQL SQLMaker with LIMIT/OFFSET via ROW_NUMBER() OVER() (with a
          (SELECT(1)) default order) and the TOP dialect for older servers
        - [ ] identifier quoting and SQLServer SQL::Translator type

    * Schema management
        - Native DDL generation, including CREATE VIEW for non-virtual
          views from view_definition
        - Native introspection via INFORMATION_SCHEMA and the sys.* catalog
        - Native diff of tables, columns, indexes, and foreign keys
        - test-deploy-and-compare install and upgrade via Deploy

    * Integration
        - DBIO::MSSQL schema component and the `use DBIO -ms` shortcut
        - Result component for MSSQL-specific indexes
        - base->native type Adapter
        - datetime parsing for the MSSQL and Sybase paths
