Adding support for T-SQL's temporal table

[Rik-de-Kort]

I would like to have a go at implementing support for T-SQL temporal table syntax, but I'm new to the codebase so I was hoping I could get some feedback whether I'm on the right path.

In effect, the objective is to be able to parse create table statements like this:

CREATE TABLE test (
    [data] CHAR(7),
    [valid_from] DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [valid_to] DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN   NOT NULL,
    PERIOD FOR SYSTEM_TIME ([valid_from], [valid_to])
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE=[dbo].[benchmark_history], DATA_CONSISTENCY_CHECK=ON));'''

Currently parsing gets stuck on "ROW" as the presence of "GENERATED" makes sqlglot assume we're looking at an identity column. To preserve backwards compatibility, I propose adding GeneratedAsRowColumnConstraint to expressions.py and then modify Parser._parse_generated_as_identity to be able to return this type. Does that sound about right?

[georgesittas]

@Rik-de-Kort sorry for the delayed response, probably missed the notification of this thread being created.

To answer your question - yes, your suggestion sounds about right. For your particular example there may be a couple more problems, though, as we currently don't parse PERIOD FOR SYSTEM_TIME and probably the same holds for the SYSTEM_VERSIONING = ON ( ... ) property. The former should need a new constraint expression, whereas the latter a new property and some additional modifications to the parser.

Feel free to join our slack community if you wanna get in touch and chat about their implementation and more.