Ejemplo n.º 1
0
        protected override void ExecuteScriptTask()
        {
            var sqlScript = $@"
{SqlSnippets.ManufacturerHeader}

PRINT '******************* {ScriptTypeName} scripts *******************'

-- Start transaction for all {ScriptTypeName} scripts
BEGIN TRANSACTION;
BEGIN TRY

    DECLARE @DBEXISTS bit;
    SET @DBEXISTS = 0;

    DECLARE @DBNAME NVARCHAR(128);
    SET @DBNAME = DB_NAME();

    DECLARE @DBID INT;
    SET @DBID = DB_ID();

    IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME)
    BEGIN
        PRINT 'The database ' +  @DBNAME + ' does not yet exist. All {ScriptTypeName} deployment scripts will be skipped.'
        SET @DBEXISTS = 0
    END
    ELSE
    BEGIN
        PRINT 'The database ' +  @DBNAME + ' already exists. {ScriptTypeName} deployment scripts will be executed.'
        SET @DBEXISTS = 1
    END";

            var scripts = GetScripts();

            foreach (var script in scripts)
            {
                var hashBase64 = script.ScriptHash;

                sqlScript += $@"

    -- Only run scripts of type '{ScriptTypes.PreScript}' if the database exists, otherwise skip. {ScriptTypes.PostScript} scripts are always run.
    IF @DBEXISTS=1 AND '{ScriptTypeName}'='{ScriptTypes.PreScript}' AND OBJECT_ID(N'dbo._MigrationScriptsHistory', N'U') IS NOT NULL OR '{ScriptTypeName}'='{ScriptTypes.PostScript}'
    BEGIN
        -- Check if the script was already run in previous migrations
        IF NOT EXISTS(SELECT *
                        FROM dbo.[_MigrationScriptsHistory]
                        WHERE [ScriptNameId] = '{script.UniqueScriptId}')
        BEGIN
            -- Run the new migration script
            PRINT '------------------ RUN --------------------------'
            PRINT 'Script Id:      {script.UniqueScriptId}'
            PRINT 'Script Name:    {script.Name}'
            PRINT 'Order Criteria: {script.OrderCriteria}'
            PRINT 'Script Type:    {script.MigrationType}'
            PRINT 'Script Path:    {script.FullFilePath}'
            PRINT 'Script Hash:    {hashBase64}'
        
            PRINT ' > Start {ScriptTypeName}-script run....'
            {TSqlHelper.GetScopedTsqlByFile(script.FullFilePath)}
            PRINT ' > Finished {ScriptTypeName}-script run....'
        
            -- Register the script in the migration script history table to prevent duplicate runs
            PRINT ' > Register script in migration history table.'
            INSERT INTO dbo.[_MigrationScriptsHistory]
            VALUES('{script.UniqueScriptId}', GETDATE(), '{hashBase64}')

            PRINT '----------------- END RUN ------------------------'
            PRINT '|'
        END
        ELSE
        BEGIN
            -- The script was already run. Check if script hash has changed meanwhile.
            IF NOT EXISTS(SELECT *
                            FROM dbo.[_MigrationScriptsHistory]
                            WHERE [ScriptHash] = '{hashBase64}')
            BEGIN
                IF {Convert.ToInt32(ScriptSettings.TreatHashMismatchAsError)}!=0
                    RAISERROR ('ERROR: The hash value for the {ScriptTypeName} migration script {script.Name} does not match with the origin registered and executed script.', 18, 1);
                ELSE
                    -- SEVERITY 0-9 is treated as warning
                    RAISERROR ('WARNING: The hash value for the {ScriptTypeName} migration script {script.Name} does not match with the past registered and executed script.', 5, 1);
            END
        
            PRINT 'Skip {ScriptTypeName}-script {script.Name} (already executed)'
        END
    END";
            }

            // Add transaction handling and catch errors
            sqlScript += $@"
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Rollback all transactions if any of the {ScriptTypeName} scripts failed.
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

-- Commit transaction if all {ScriptTypeName} scripts have been successfully run.
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO";

            File.WriteAllText(ScriptSettings.GeneratedScriptPath, sqlScript, Encoding.UTF8);
            Logger.LogMessage($@"Script execution file '{ScriptSettings.GeneratedScriptPath}' has been generated.");
        }
Ejemplo n.º 2
0
        protected override void ExecuteScriptTask()
        {
            var sqlScript = $@"
{SqlSnippets.ManufacturerHeader}

PRINT '******************* {ScriptTypeName} scripts *******************'

-- Start transaction for all {ScriptTypeName} scripts
BEGIN TRANSACTION;
BEGIN TRY

    DECLARE @DBEXISTS bit;
    SET @DBEXISTS = 0;

    DECLARE @DBNAME NVARCHAR(128);
    SET @DBNAME = DB_NAME();

    DECLARE @DBID INT;
    SET @DBID = DB_ID();

    IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME)
    BEGIN
        PRINT 'The database ' +  @DBNAME + ' does not yet exist. All {ScriptTypeName} deployment scripts will be skipped.'
        SET @DBEXISTS = 0
    END
    ELSE
    BEGIN
        PRINT 'The database ' +  @DBNAME + ' already exists. {ScriptTypeName} deployment scripts will be executed.'
        SET @DBEXISTS = 1
    END";

            sqlScript += $@"
    
    -- Prefill the _MigrationScriptsHistory table with the existing migration scripts when applying the first time
    IF @DBEXISTS=1 AND '{ScriptTypeName}'='{ScriptTypes.ReferenceDataScript}'
    BEGIN    
        IF OBJECT_ID(N'dbo._MigrationScriptsHistory', N'U') IS NOT NULL
        BEGIN
            IF NOT EXISTS (SELECT * FROM [dbo].[_MigrationScriptsHistory])
            BEGIN";

            var existingScripts = new List <Script>();

            // add all PreScripts
            var preScriptTask = new PreScriptTask();

            preScriptTask.ProjectRootDirectory = ProjectRootDirectory;
            preScriptTask.Initialize(false);
            existingScripts.AddRange(GetScripts(preScriptTask.ScriptSettings));

            // add all PostScripts
            var postScriptTask = new PostScriptTask();

            postScriptTask.ProjectRootDirectory = ProjectRootDirectory;
            postScriptTask.Initialize(false);
            existingScripts.AddRange(GetScripts(postScriptTask.ScriptSettings));

            if (existingScripts.Any())
            {
                sqlScript += $@"
                PRINT N'Set default values for MigrationScriptsHistory';

                INSERT INTO [dbo].[_MigrationScriptsHistory] 
                VALUES";

                var separator = string.Empty;
                foreach (var script in existingScripts)
                {
                    sqlScript += $@"
                    {separator}('{script.UniqueScriptId}', GETUTCDATE(),'{script.ScriptHash}')";

                    separator = ",";
                }

                // add entry into MigrationScriptsHistory table to set initialization status which blocks adding upcoming scripts
                sqlScript += $@"
                    {separator}('initialization\_migrationscriptshistory', GETUTCDATE(),'ZmI3OWI5ZThiOWU2ZGU5MTYwODFkYmJmN2M3ZjFlYzM=')";
            }
            else
            {
                sqlScript += $@"
                PRINT N'No scripts found to add to MigrationScriptsHistory';";
            }

            sqlScript += $@"
            END
            ELSE
            BEGIN
                PRINT N'MigrationScriptsHistory already has entries, skipping.';
            END
        END
        ELSE
        BEGIN
            RAISERROR ('ERROR: The migration script history table _MigrationScriptsHistory does not exists.', 18, 1);
        END
    END";

            var scripts = GetScripts();

            foreach (var script in scripts)
            {
                var hashBase64 = script.ScriptHash;

                sqlScript += $@"

    -- Only run scripts of type '{ScriptTypes.ReferenceDataScript}' if the database exists, otherwise skip.
    IF @DBEXISTS=1 AND '{ScriptTypeName}'='{ScriptTypes.ReferenceDataScript}'
    BEGIN    
        -- Run the new migration script
        PRINT '------------------ RUN --------------------------'
        PRINT 'Script Id:      {script.UniqueScriptId}'
        PRINT 'Script Name:    {script.Name}'
        PRINT 'Order Criteria: {script.OrderCriteria}'
        PRINT 'Script Type:    {script.MigrationType}'
        PRINT 'Script Path:    {script.FullFilePath}'
        PRINT 'Script Hash:    {hashBase64}'
        
        PRINT ' > Start {ScriptTypeName}-script run....'
        {TSqlHelper.GetScopedTsqlByFile(script.FullFilePath)}
        PRINT ' > Finished {ScriptTypeName}-script run....'
        
        PRINT '----------------- END RUN ------------------------'
        PRINT '|'
    END";
            }

            // Add transaction handling and catch errors
            sqlScript += $@"
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();

    -- Rollback all transactions if any of the {ScriptTypeName} scripts failed.
    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

-- Commit transaction if all {ScriptTypeName} scripts have been successfully run.
IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO";

            File.WriteAllText(ScriptSettings.GeneratedScriptPath, sqlScript, Encoding.UTF8);
            Logger.LogMessage($@"Script execution file '{ScriptSettings.GeneratedScriptPath}' has been generated.");
        }