override default script for initialization of flyway metadata table

Question

I'm working with the Microsoft Parallel Data Warehouse appliance and attempting to use flyway to handle table migrations in that environment. The issue I'm running into is that the default script for establishing the schema_version table fails.

Here is the default script as far as I can tell that is being executed upon calling baseline().

CREATE TABLE [dbo].[dbresult_migration] (
    [installed_rank] INT NOT NULL,
    [version] NVARCHAR(50),
    [description] NVARCHAR(200),
    [type] NVARCHAR(20) NOT NULL,
    [script] NVARCHAR(1000) NOT NULL,
    [checksum] INT,
    [installed_by] NVARCHAR(100) NOT NULL,
    [installed_on] DATETIME NOT NULL DEFAULT GETDATE(),
    [execution_time] INT NOT NULL,
    [success] BIT NOT NULL
);

ALTER TABLE [dbo].[dbresult_migration] ADD CONSTRAINT [dbresult_migration_pk] PRIMARY KEY ([installed_rank]);
CREATE INDEX [dbresult_migration_s_idx] ON [dbo].[dbresult_migration] ([success]);

Specifically the Microsoft Parallel Data Warehouse (MS PDW or APS as it is now known) doesn't support expressions with default constraints.

Msg 104338, Level 16, State 1, Line 1 An expression cannot be used with a default constraint. Specify only constants for a default constraint.

Which causes an error when GETDATE() is used as the default for the installed_on column.

The ALTER TABLE statement will also fail as PRIMARY KEYs and INDICES are managed differently in the environment.

Is there a way to override the default initialization script for the schema_version?

UPDATE

Further investigation reveals that the next failure occurs when attempting to insert records into the schema_version table. Specifically the current implementation attempts to identify the current user based on a call to dbSupport.getCurrentUserFunction(). For SQL Server this function is SUSER_SNAME(). While this function is available on both the standard SQL Server and the Parallel Data Warehouse the current implementation of the Parallel Data Warehouse does not allow for function calls within the values portion of an insert statement. As such, the following error is returned:

Insert values statement can contain only constant literal values or variable references.

When the query that is attempted is logged as:

INSERT INTO [dbo].[dbresult_migration] ([installed_rank],[version],[description],[type],[script],[checksum],[installed_by],[execution_time],[success]) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, SUSER_SNAME(), @P6, @P7)

UPDATE 2

I now have a fork of flyway-core that correctly identifies if you are connecting to SQL Server vs SQL Server parallel data warehouse. Another issue that I have identified is that SQL Server PDW does not allow DDL within transactions and so an attempt to baseline fails as this appears to be attempted from within a transaction template. Ultimately this is evolving from a question of understanding how to modify an initialization script to a need for support of a new database platform. I've submitted this as a new issue on the flyway repo on github here.

override default script for initialization of flyway metadata table

0 Answers0