Skip to content

MS SQL Server: Timestamp{} emits TIMESTAMP, a rowversion synonym — column is binary and non-writable #587

Description

@Yaraslaut

Surfaced by the column-type coverage added in #579, where it is asserted as-is and labelled KNOWN DEFECT so it does not go unnoticed.

Symptom

On MS SQL Server, a column declared through the migration query builder as

migration.CreateTable("Example")
    .PrimaryKeyWithAutoIncrement("id")
    .RequiredColumn("timestampColumn", Timestamp {});

comes back out of the catalog as an 8-byte binary column, and ddl2cpp generates Light::SqlDynamicBinary<8> for it instead of Light::SqlDateTime. SQLite and PostgreSQL produce Light::SqlDateTime as expected.

Cause

SqlServerFormatter::ColumnType emits the type name verbatim:

// src/Lightweight/QueryFormatter/SqlServerFormatter.hpp:356
[](Timestamp const&) -> std::string { return "TIMESTAMP"; },

On SQL Server, TIMESTAMP is not a point in time. It is a deprecated synonym for rowversion: an 8-byte, server-generated, non-writable binary counter that changes on every update of the row. So the user asks for a timestamp and gets a read-only binary column.

Impact

  • A Timestamp {} column on SQL Server cannot be written to at all — inserts and updates targeting it are rejected by the server.
  • Round-tripping such a schema through ddl2cpp produces a binary record member, so the C++ side inherits the wrong type too.
  • The type is deprecated by Microsoft, so the spelling is on borrowed time regardless.

Suggested fix

Emit DATETIME2 for Timestamp {} in SqlServerFormatter::ColumnType. That is the natural counterpart to what the other two backends store, and it is what DateTime {} already maps to.

Worth deciding as part of the fix: whether existing SQL Server schemas created with the current spelling need a migration path, and whether the schema reader should keep recognising rowversion/timestamp columns as binary when reading third-party schemas (it should — that is a correct reading of what the column actually is).

Verification

src/tests/Ddl2CppColumnTypeTests.cpp (from #579) has a dedicated ColumnTypeTimestamp case that currently branches on SqlServerType::MICROSOFT_SQL to expect Light::SqlDynamicBinary<8>. Once fixed, that branch should collapse to the single unconditional Light::SqlDateTime expectation.

Related: #191, #579

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions