Esempio n. 1
0
        ///<inheritdoc/>
        public override void RunSqlScripts(
            IDbConnection connection,
            IDbTransaction transaction,
            NonTransactionalContext nonTransactionalContext,
            string version,
            string workingPath,
            string scriptDirectory,
            string metaSchemaName,
            string metaTableName,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            int?commandTimeout          = null,
            string environmentCode      = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null
            )
        {
            //extract and filter out scripts when environment code is used
            var sqlScriptFiles = _directoryService.GetFiles(scriptDirectory, "*.sql").ToList();

            sqlScriptFiles = _directoryService.FilterFiles(workingPath, environmentCode, sqlScriptFiles).ToList();
            _traceService.Info($"Found {sqlScriptFiles.Count} script files on {workingPath}" + (sqlScriptFiles.Count > 0 ? Environment.NewLine : string.Empty) +
                               $"{string.Join(Environment.NewLine, sqlScriptFiles.Select(s => "  + " + new FileInfo(s).Name))}");

            //execute all script files in the version folder
            sqlScriptFiles.Sort();
            sqlScriptFiles
            .ForEach(scriptFile =>
            {
                var sqlStatementRaw = _fileService.ReadAllText(scriptFile);
                var sqlStatements   = _dataService.BreakStatements(sqlStatementRaw)
                                      .Where(s => !string.IsNullOrWhiteSpace(s))
                                      .ToList();
                ;
                sqlStatements.ForEach(sqlStatement =>
                {
                    try
                    {
                        sqlStatement = _tokenReplacementService.Replace(tokenKeyPairs, sqlStatement);
                        _traceService.Debug($"Executing sql statement as part of : {scriptFile}");
                        _configurationDataService.ExecuteSql(
                            connection: connection,
                            commandText: sqlStatement,
                            transaction: transaction,
                            commandTimeout: commandTimeout,
                            traceService: _traceService);
                    }
                    catch (Exception)
                    {
                        _traceService.Error($"Failed to execute sql statements in script file {scriptFile}.{Environment.NewLine}" +
                                            $"The failing statement starts here --------------------------{Environment.NewLine}" +
                                            $"{sqlStatement} {Environment.NewLine}" +
                                            $"The failing statement ends here --------------------------");
                        throw;
                    }
                });

                _traceService.Info($"Executed script file {scriptFile}.");
            });
        }
 public abstract void RunSqlScripts(
     IDbConnection connection,
     IDbTransaction transaction,
     NonTransactionalContext nonTransactionalContext,
     string version,
     string workingPath,
     string scriptDirectory,
     string metaSchemaName,
     string metaTableName,
     List <KeyValuePair <string, string> > tokenKeyPairs = null,
     int?commandTimeout          = null,
     string environmentCode      = null,
     string appliedByTool        = null,
     string appliedByToolVersion = null
     );
 public abstract void RunVersionScripts(
     IDbConnection connection,
     IDbTransaction transaction,
     List <string> dbVersions,
     string workingPath,
     string targetVersion,
     NonTransactionalContext nonTransactionalContext,
     List <KeyValuePair <string, string> > tokenKeyPairs = null,
     string bulkSeparator        = null,
     string metaschemaName       = null,
     string metaTableName        = null,
     int?commandTimeout          = null,
     int?bulkBatchSize           = null,
     string appliedByTool        = null,
     string appliedByToolVersion = null,
     string environmentCode      = null
     );
Esempio n. 4
0
        private void RunSqlScripts(
            IDbConnection connection,
            IDbTransaction transaction,
            NonTransactionalContext nonTransactionalContext,
            string version,
            string workingPath,
            string scriptDirectory,
            string schemaName,
            string tableName,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            int?commandTimeout          = null,
            string environmentCode      = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null
            )
        {
            string currentScriptFile = null;

            try
            {
                //TODO: Filter out scripts when environment code is used
                var sqlScriptFiles = _directoryService.GetFiles(scriptDirectory, "*.sql").ToList();
                sqlScriptFiles = _directoryService.FilterFiles(workingPath, environmentCode, sqlScriptFiles).ToList();
                _traceService.Info($"Found the {sqlScriptFiles.Count} script files on {scriptDirectory}");
                _traceService.Info($"{string.Join(@"\r\n\t", sqlScriptFiles.Select(s => new FileInfo(s).Name))}");

                //execute all script files in the version folder
                sqlScriptFiles.Sort();
                sqlScriptFiles
                .ForEach(scriptFile =>
                {
                    currentScriptFile = scriptFile;

                    //In case the non-transactional failure is resolved, skip scripts
                    if (nonTransactionalContext?.ResolvingOption == NonTransactionalResolvingOption.ContinueAfterFailure &&
                        !nonTransactionalContext.IsFailedScriptPathMatched)
                    {
                        //Set failed script file as matched
                        if (string.Equals(scriptFile, nonTransactionalContext.FailedScriptPath, StringComparison.InvariantCultureIgnoreCase))
                        {
                            nonTransactionalContext.SetFailedScriptPathMatch();
                        }
                        _traceService.Info($"Skipping script file {scriptFile} ...");
                    }
                    else //otherwise execute them
                    {
                        var sqlStatementRaw = _fileService.ReadAllText(scriptFile);
                        var sqlStatements   = _dataService.BreakStatements(sqlStatementRaw)
                                              .Where(s => !string.IsNullOrWhiteSpace(s))
                                              .ToList();
                        ;
                        sqlStatements.ForEach(sqlStatement =>
                        {
                            sqlStatement = _tokenReplacementService.Replace(tokenKeyPairs, sqlStatement);

                            _traceService.Debug($"Executing sql statement as part of : {scriptFile}{Environment.NewLine}{sqlStatement}");
                            _configurationDataService.ExecuteSql(
                                connection: connection,
                                commandText: sqlStatement,
                                transaction: transaction,
                                commandTimeout: commandTimeout,
                                traceService: _traceService);
                        });

                        _traceService.Info($"Executed script file {scriptFile}.");
                    }
                });
            }
            catch (Exception exc)
            {
                //Try parse the known sql error
                string sqlError;
                if (!_dataService.TryParseErrorFromException(exc, out sqlError))
                {
                    //if not sucesfull, use the whole exception
                    sqlError = exc.ToString();
                }

                //in case scripts are not executed within transaction, mark version as failed in database
                if (transaction == null)
                {
                    //update db version to failed
                    _configurationDataService.InsertVersion(connection, transaction, version,
                                                            schemaName,
                                                            tableName,
                                                            commandTimeout: commandTimeout,
                                                            appliedByTool: appliedByTool,
                                                            appliedByToolVersion: appliedByToolVersion,
                                                            currentScriptFile,
                                                            sqlError);

                    _traceService.Error(@$ "Migration of " "{version}" " version was not running in transaction and has failed when executing of script file " "{currentScriptFile}" " with following error:
{sqlError}
{ManualResolvingAfterFailureMessage}");
                }
                else
                {
                    _traceService.Error(@$ "Migration of " "{version}" " version was running in transaction and has failed when executing of script file " "{currentScriptFile}" " with following error:
{sqlError}
");
                }

                throw exc;
            }
        }
Esempio n. 5
0
        private void RunVersionScripts(
            IDbConnection connection,
            IDbTransaction transaction,
            List <string> dbVersions,
            string workingPath,
            string targetVersion,
            NonTransactionalContext nonTransactionalContext,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            string delimiter            = null,
            string schemaName           = null,
            string tableName            = null,
            int?commandTimeout          = null,
            int?batchSize               = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null,
            string environmentCode      = null
            )
        {
            //excludes all versions already executed
            var versionDirectories = _directoryService.GetDirectories(workingPath, "v*.*")
                                     .Where(v => !dbVersions.Contains(new DirectoryInfo(v).Name))
                                     .ToList();

            //exclude all versions greater than the target version
            if (!string.IsNullOrEmpty(targetVersion))
            {
                versionDirectories.RemoveAll(v =>
                {
                    var cv = new LocalVersion(new DirectoryInfo(v).Name);
                    var tv = new LocalVersion(targetVersion);

                    return(string.Compare(cv.SemVersion, tv.SemVersion) == 1);
                });
            }

            //execute all sql scripts in the version folders
            if (versionDirectories.Any())
            {
                versionDirectories.Sort();
                versionDirectories.ForEach(versionDirectory =>
                {
                    try
                    {
                        //run scripts in all sub-directories
                        List <string> scriptSubDirectories = null;

                        //check for transaction directory
                        bool isExplicitTransactionDefined = false;
                        string transactionDirectory       = Path.Combine(versionDirectory, "_transaction");
                        if (_directoryService.Exists(transactionDirectory))
                        {
                            if (_dataService.IsAtomicDDLSupported)
                            {
                                throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " can't contain " "_transaction" " subdirectory for selected target platform, because the whole migration is already running in single transaction.");
                            }
                            if (_directoryService.GetDirectories(versionDirectory, "*").Count() > 1)
                            {
                                throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " containing " "_transaction" " subdirectory can't contain other subdirectories");
                            }
                            else if (_directoryService.GetFiles(versionDirectory, "*.*").Count() > 0)
                            {
                                throw new YuniqlMigrationException(@$ "The version directory " "{versionDirectory}" " containing " "_transaction" " subdirectory can't contain files");
                            }

                            isExplicitTransactionDefined = true;
                            scriptSubDirectories         = _directoryService.GetAllDirectories(transactionDirectory, "*").ToList();
                        }
                        else
                        {
                            scriptSubDirectories = _directoryService.GetAllDirectories(versionDirectory, "*").ToList();
                        }

                        if (isExplicitTransactionDefined)
                        {
                            string versionName = new DirectoryInfo(versionDirectory).Name;

                            //run scripts within a single transaction
                            _traceService.Info(@$ "The " "_transaction" " directory has been detected and therefore " "{versionName}" " version scripts will run in single transaction. The rollback will not be reliable in case the version scripts contain commands causing implicit commit (e.g. DDL)!");

                            using (var transaction = connection.BeginTransaction())
                            {
                                try
                                {
                                    RunVersionScriptsInternal(transaction, scriptSubDirectories, transactionDirectory, versionDirectory, schemaName, tableName);

                                    transaction.Commit();

                                    _traceService.Info(@$ "Target database has been commited after running " "{versionName}" " version scripts.");
                                }
                                catch (Exception)
                                {
                                    _traceService.Error(@$ "Target database will be rolled back to the state before running " "{versionName}" " version scripts.");
                                    transaction.Rollback();
                                    throw;
                                }
                            }
                        }
                        else //run scripts without transaction
                        {
                            RunVersionScriptsInternal(transaction, scriptSubDirectories, versionDirectory, versionDirectory, schemaName, tableName);
                        }
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                });
            }
            else
            {
                var connectionInfo = _dataService.GetConnectionInfo();
                _traceService.Info($"Target database is updated. No migration step executed at {connectionInfo.Database} on {connectionInfo.DataSource}.");
            }

            void RunVersionScriptsInternal(IDbTransaction transaction, List <string> scriptSubDirectories, string scriptDirectory, string versionDirectory, string schemaName, string tableName)
            {
                try
                {
                    string versionName = new DirectoryInfo(versionDirectory).Name;

                    scriptSubDirectories.Sort();
                    scriptSubDirectories.ForEach(scriptSubDirectory =>
                    {
                        //run all scripts in the current version folder
                        RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptSubDirectory, schemaName, tableName, tokenKeyPairs, commandTimeout, environmentCode, appliedByTool, appliedByToolVersion);

                        //import csv files into tables of the the same filename as the csv
                        RunBulkImport(connection, transaction, workingPath, scriptSubDirectory, delimiter, batchSize, commandTimeout, environmentCode);
                    });

                    //run all scripts in the current version folder
                    RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptDirectory, schemaName, tableName, tokenKeyPairs, commandTimeout, environmentCode, appliedByTool, appliedByToolVersion);

                    //import csv files into tables of the the same filename as the csv
                    RunBulkImport(connection, transaction, workingPath, scriptDirectory, delimiter, batchSize, commandTimeout, environmentCode);

                    //update db version
                    _configurationDataService.InsertVersion(connection, transaction, versionName,
                                                            schemaName: schemaName,
                                                            tableName: tableName,
                                                            commandTimeout: commandTimeout,
                                                            appliedByTool: appliedByTool,
                                                            appliedByToolVersion: appliedByToolVersion);

                    _traceService.Info($"Completed migration to version {versionDirectory}");
                }
                finally
                {
                    //Clear nontransactional context to ensure it is not applied on next version
                    nonTransactionalContext = null;
                }
            }
        }
Esempio n. 6
0
        /// <summary>
        /// Runs migrations by executing alls scripts in the workspace directory.
        /// When CSV files are present also run bulk import operations to target database table having same file name.
        /// </summary>
        /// <param name="workingPath">The directory path to migration project.</param>
        /// <param name="targetVersion">The maximum version to run to. When NULL, runs migration to the latest version found in the workspace path.</param>
        /// <param name="autoCreateDatabase">When TRUE, creates the database in the target host.</param>
        /// <param name="tokenKeyPairs">Token kev/value pairs to replace tokens in script files.</param>
        /// <param name="verifyOnly">When TRUE, runs the migration in uncommitted mode. No changes are committed to target database. When NULL, runs migration in atomic mode.</param>
        /// <param name="delimiter">Delimeter character in the CSV bulk import files. When NULL, uses comma.</param>
        /// <param name="schemaName">Schema name for schema versions table. When empty, uses the default schema in the target data platform. </param>
        /// <param name="tableName">Table name for schema versions table. When empty, uses __yuniqldbversion.</param>
        /// <param name="commandTimeout">Command timeout in seconds. When NULL, it uses default provider command timeout.</param>
        /// <param name="batchSize">Batch rows to processed when performing bulk import. When NULL, it uses default provider batch size.</param>
        /// <param name="appliedByTool">The source that initiates the migration. This can be yuniql-cli, yuniql-aspnetcore or yuniql-azdevops.</param>
        /// <param name="appliedByToolVersion">The version of the source that initiates the migration.</param>
        /// <param name="environmentCode">Environment code for environment-aware scripts.</param>
        /// <param name="nonTransactionalResolvingOption">The non-transactional resolving option.</param>
        public void Run(
            string workingPath,
            string targetVersion    = null,
            bool?autoCreateDatabase = false,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            bool?verifyOnly             = false,
            string delimiter            = null,
            string schemaName           = null,
            string tableName            = null,
            int?commandTimeout          = null,
            int?batchSize               = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null,
            string environmentCode      = null,
            NonTransactionalResolvingOption?nonTransactionalResolvingOption = null
            )
        {
            if (_dataService.IsAtomicDDLSupported && nonTransactionalResolvingOption != null)
            {
                throw new NotSupportedException(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is not available for this platform.");
            }

            //validate workspace structure
            _localVersionService.Validate(workingPath);

            //when uncomitted run is not supported, fail migration and throw exceptions
            if (verifyOnly.HasValue && verifyOnly == true && !_dataService.IsAtomicDDLSupported)
            {
                throw new NotSupportedException("Yuniql.Verify is not supported in the target platform. " +
                                                "The feature requires support for atomic DDL operations. " +
                                                "An atomic DDL operations ensures creation of tables, views and other objects and data are rolledback in case of error. " +
                                                "For more information see https://yuniql.io/docs/.");
            }

            //when no target version specified, we use the latest local version
            if (string.IsNullOrEmpty(targetVersion))
            {
                targetVersion = _localVersionService.GetLatestVersion(workingPath);
                _traceService.Info($"No explicit target version requested. We'll use latest available locally {targetVersion} on {workingPath}.");
            }

            var connectionInfo       = _dataService.GetConnectionInfo();
            var targetDatabaseName   = connectionInfo.Database;
            var targetDatabaseServer = connectionInfo.DataSource;

            //we try to auto-create the database, we need this to be outside of the transaction scope
            //in an event of failure, users have to manually drop the auto-created database!
            //we only check if the db exists when --auto-create-db is true
            if (autoCreateDatabase.HasValue && autoCreateDatabase == true)
            {
                var targetDatabaseExists = _configurationDataService.IsDatabaseExists();
                if (!targetDatabaseExists)
                {
                    _traceService.Info($"Target database does not exist. Creating database {targetDatabaseName} on {targetDatabaseServer}.");
                    _configurationDataService.CreateDatabase();
                    _traceService.Info($"Created database {targetDatabaseName} on {targetDatabaseServer}.");
                }
            }

            //check if database has been pre-configured to support migration and setup when its not
            var targetDatabaseConfigured = _configurationDataService.IsDatabaseConfigured(schemaName, tableName);

            if (!targetDatabaseConfigured)
            {
                //create custom schema when user supplied and only if platform supports it
                if (_dataService.IsSchemaSupported && null != schemaName && !_dataService.SchemaName.Equals(schemaName))
                {
                    _traceService.Info($"Target schema does not exist. Creating schema {schemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
                    _configurationDataService.CreateSchema(schemaName);
                    _traceService.Info($"Created schema {schemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
                }

                //create empty versions tracking table
                _traceService.Info($"Target database {targetDatabaseName} on {targetDatabaseServer} not yet configured for migration.");
                _configurationDataService.ConfigureDatabase(schemaName, tableName);
                _traceService.Info($"Configured database migration support for {targetDatabaseName} on {targetDatabaseServer}.");
            }
            else
            {
                bool databaseupdated = _configurationDataService.UpdateDatabaseConfiguration();

                if (databaseupdated)
                {
                    _traceService.Info($"The configuration of migration has been updated for {targetDatabaseName} on {targetDatabaseServer}.");
                }
                else
                {
                    _traceService.Debug($"The configuration of migration is up to date for {targetDatabaseName} on {targetDatabaseServer}.");
                }
            }

            IList <DbVersion> allVersions = _configurationDataService.GetAllVersions(schemaName, tableName);

            NonTransactionalContext nonTransactionalContext = null;

            //Check for failed not transactional versions
            if (!_dataService.IsAtomicDDLSupported)
            {
                DbVersion failedDbVersion = allVersions.Where(x => x.StatusId == StatusId.Failed).FirstOrDefault();

                if (failedDbVersion != null)
                {
                    if (nonTransactionalResolvingOption == null)
                    {
                        _traceService.Error(@$ "Previous migration of " "{failedDbVersion.Version}" " version was not running in transaction and has failed when executing of script " "{failedDbVersion.FailedScriptPath}" " with following error:
{failedDbVersion.FailedScriptError}
{ManualResolvingAfterFailureMessage}");

                        //Program should exit with non zero exit code
                        throw new InvalidOperationException();
                    }

                    _traceService.Info($@"The non-transactional failure resolving option ""{nonTransactionalResolvingOption}"" was used. Version scripts already applied by previous migration run will be skipped.");

                    nonTransactionalContext = new NonTransactionalContext(failedDbVersion, nonTransactionalResolvingOption.Value);
                }
                else
                {
                    if (nonTransactionalResolvingOption != null)
                    {
                        _traceService.Error(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is available only if previous migration has failed.");

                        //Program should exit with non zero exit code
                        throw new InvalidOperationException();
                    }
                }
            }

            var dbVersions = _configurationDataService.GetAllAppliedVersions(schemaName, tableName)

                             .Select(dv => dv.Version)
                             .OrderBy(v => v)
                             .ToList();

            //checks if target database already runs the latest version and skips work if it already is
            var targeDatabaseLatest = IsTargetDatabaseLatest(targetVersion, schemaName, tableName);

            if (!targeDatabaseLatest)
            {
                //create a shared open connection to entire migration run
                using (var connection = _dataService.CreateConnection())
                {
                    connection.Open();

                    //enclose all executions in a single transaction in case platform supports it
                    if (_dataService.IsAtomicDDLSupported)
                    {
                        _traceService.Debug(@$ "Target platform fully supports transactions. Migration will run in single transaction.");
                        using (var transaction = connection.BeginTransaction())
                        {
                            try
                            {
                                RunInternal(connection, transaction);

                                //when true, the execution is an uncommitted transaction
                                //and only for purpose of testing if all can go well when it run to the target environment
                                if (verifyOnly.HasValue && verifyOnly == true)
                                {
                                    transaction.Rollback();
                                }
                                else
                                {
                                    transaction.Commit();
                                }
                            }
                            catch (Exception)
                            {
                                _traceService.Error("Target database will be rolled back to its previous state.");
                                transaction.Rollback();
                                throw;
                            }
                        }
                    }
                    else //otherwise don't use transactions
                    {
                        _traceService.Info(@$ "Target platform doesn't reliably support transactions for all commands. Migration will not run in single transaction. Any failure during the migration will require manual resolution.");

                        try
                        {
                            RunInternal(connection, null);
                        }
                        catch (Exception)
                        {
                            _traceService.Error($"Migration was not running in transaction, therefore roll back of target database to the state before the migration is not possible.");
                            throw;
                        }
                    }
                }
            }

            else //runs all scripts files inside _draft on every yuniql run regardless of state of target database
            {
                //enclose all executions in a single transaction
                using (var connection = _dataService.CreateConnection())
                {
                    connection.Open();

                    //enclose all executions in a single transaction in case platform supports it
                    if (_dataService.IsAtomicDDLSupported)
                    {
                        _traceService.Debug(@$ "Target platform fully supports transactions. Migration will run in single transaction.");
                        using (var transaction = connection.BeginTransaction())
                        {
                            try
                            {
                                RunDraftInternal(connection, transaction);
                                transaction.Commit();
                            }
                            catch (Exception)
                            {
                                transaction.Rollback();
                                throw;
                            }
                        }
                    }
                    else //otherwise don't use transactions
                    {
                        try
                        {
                            _traceService.Info($"Target platform doesn't reliably support transactions for all commands. " +
                                               $"Migration will not run in single transaction. " +
                                               $"Any failure during the migration can prevent automatic completing of migration.");
                            RunDraftInternal(connection, null);
                        }
                        catch (Exception)
                        {
                            _traceService.Error("Migration was not running in transaction, therefore roll back of target database to its previous state is not possible. " +
                                                "Migration need to be completed manually! Running of Yuniql again, might cause that some scripts will be executed twice!");
                            throw;
                        }
                    }
                    _traceService.Info($"Target database runs the latest version already. Scripts in _pre, _draft and _post are executed.");
                }
            }

            //local method
            void RunInternal(IDbConnection connection, IDbTransaction transaction)
            {
                //check if database has been pre-configured and execute init scripts
                if (!targetDatabaseConfigured)
                {
                    //runs all scripts in the _init folder
                    RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_init"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                    _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_init")}");
                }

                //checks if target database already runs the latest version and skips work if it already is
                //runs all scripts in the _pre folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");

                //runs all scripts int the vxx.xx folders and subfolders
                RunVersionScripts(connection, transaction, dbVersions, workingPath, targetVersion, nonTransactionalContext, tokenKeyPairs, delimiter: delimiter, schemaName: schemaName, tableName: tableName, commandTimeout: commandTimeout, batchSize: batchSize, appliedByTool: appliedByTool, appliedByToolVersion: appliedByToolVersion, environmentCode: environmentCode);

                //runs all scripts in the _draft folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");

                //runs all scripts in the _post folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
            }

            void RunDraftInternal(IDbConnection connection, IDbTransaction transaction)
            {
                //runs all scripts in the _pre folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");

                //runs all scripts in the _draft folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");

                //runs all scripts in the _post folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, delimiter: delimiter, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
            }
        }
Esempio n. 7
0
        ///<inheritdoc/>
        public override void RunVersionScripts(
            IDbConnection connection,
            IDbTransaction transaction,
            List <string> dbVersions,
            string workingPath,
            string targetVersion,
            NonTransactionalContext nonTransactionalContext,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            string bulkSeparator        = null,
            string metaSchemaName       = null,
            string metaTableName        = null,
            int?commandTimeout          = null,
            int?bulkBatchSize           = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null,
            string environmentCode      = null
            )
        {
            //excludes all versions already executed
            var versionDirectories = _directoryService.GetDirectories(workingPath, "v*.*")
                                     .Where(v => !dbVersions.Contains(new DirectoryInfo(v).Name))
                                     .ToList();

            //exclude all versions greater than the target version
            if (!string.IsNullOrEmpty(targetVersion))
            {
                versionDirectories.RemoveAll(v =>
                {
                    var cv = new LocalVersion(new DirectoryInfo(v).Name);
                    var tv = new LocalVersion(targetVersion);

                    return(string.Compare(cv.SemVersion, tv.SemVersion) == 1);
                });
            }

            //execute all sql scripts in the version folders
            if (versionDirectories.Any())
            {
                versionDirectories.Sort();
                versionDirectories.ForEach(versionDirectory =>
                {
                    var versionName = new DirectoryInfo(versionDirectory).Name;

                    //run scripts in all sub-directories
                    var scriptSubDirectories = _directoryService.GetAllDirectories(versionDirectory, "*").ToList();
                    scriptSubDirectories.Sort();
                    scriptSubDirectories.ForEach(scriptSubDirectory =>
                    {
                        //run all scripts in the current version folder
                        RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, scriptSubDirectory, metaSchemaName, metaTableName, tokenKeyPairs, commandTimeout, environmentCode);

                        //import csv files into tables of the the same filename as the csv
                        RunBulkImport(connection, transaction, workingPath, scriptSubDirectory, bulkSeparator, bulkBatchSize, commandTimeout, environmentCode);
                    });

                    //run all scripts in the current version folder
                    RunSqlScripts(connection, transaction, nonTransactionalContext, versionName, workingPath, versionDirectory, metaSchemaName, metaTableName, tokenKeyPairs, commandTimeout, environmentCode);

                    //import csv files into tables of the the same filename as the csv
                    RunBulkImport(connection, transaction, workingPath, versionDirectory, bulkSeparator, bulkBatchSize, commandTimeout, environmentCode);

                    //update db version
                    _configurationDataService.InsertVersion(connection, transaction, versionName,
                                                            metaSchemaName: metaSchemaName,
                                                            metaTableName: metaTableName,
                                                            commandTimeout: commandTimeout,
                                                            appliedByTool: appliedByTool,
                                                            appliedByToolVersion: appliedByToolVersion);

                    _traceService.Info($"Completed migration to version {versionDirectory}");
                });
            }
            else
            {
                var connectionInfo = _dataService.GetConnectionInfo();
                _traceService.Info($"Target database is updated. No migration step executed at {connectionInfo.Database} on {connectionInfo.DataSource}.");
            }
        }
Esempio n. 8
0
        /// <inheritdoc />
        public override void Run(
            string workingPath,
            string targetVersion    = null,
            bool?autoCreateDatabase = false,
            List <KeyValuePair <string, string> > tokenKeyPairs = null,
            bool?verifyOnly             = false,
            string bulkSeparator        = null,
            string metaSchemaName       = null,
            string metaTableName        = null,
            int?commandTimeout          = null,
            int?bulkBatchSize           = null,
            string appliedByTool        = null,
            string appliedByToolVersion = null,
            string environmentCode      = null,
            NonTransactionalResolvingOption?nonTransactionalResolvingOption = null,
            bool noTransaction = false
            )
        {
            if (_dataService.IsAtomicDDLSupported && nonTransactionalResolvingOption != null)
            {
                throw new NotSupportedException(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is not available for this platform.");
            }

            //check the workspace structure if required directories are present
            _localVersionService.Validate(workingPath);

            //when uncomitted run is not supported, fail migration, throw exceptions and return error exit code
            if (verifyOnly.HasValue && verifyOnly == true && !_dataService.IsAtomicDDLSupported)
            {
                throw new NotSupportedException("Yuniql.Verify is not supported in the target platform. " +
                                                "The feature requires support for atomic DDL operations. " +
                                                "An atomic DDL operations ensures creation of tables, views and other objects and data are rolledback in case of error. " +
                                                "For more information see https://yuniql.io/docs/.");
            }

            //when no target version specified, we use the latest local version
            if (string.IsNullOrEmpty(targetVersion))
            {
                targetVersion = _localVersionService.GetLatestVersion(workingPath);
                _traceService.Info($"No explicit target version requested. We'll use latest available locally {targetVersion} on {workingPath}.");
            }

            var connectionInfo       = _dataService.GetConnectionInfo();
            var targetDatabaseName   = connectionInfo.Database;
            var targetDatabaseServer = connectionInfo.DataSource;

            //we try to auto-create the database, we need this to be outside of the transaction scope
            //in an event of failure, users have to manually drop the auto-created database!
            //we only check if the db exists when --auto-create-db is true
            if (autoCreateDatabase.HasValue && autoCreateDatabase == true)
            {
                var targetDatabaseExists = _configurationDataService.IsDatabaseExists();
                if (!targetDatabaseExists)
                {
                    _traceService.Info($"Target database does not exist. Creating database {targetDatabaseName} on {targetDatabaseServer}.");
                    _configurationDataService.CreateDatabase();
                    _traceService.Info($"Created database {targetDatabaseName} on {targetDatabaseServer}.");
                }
            }

            //check if database has been pre-configured to support migration and setup when its not
            var targetDatabaseConfigured = _configurationDataService.IsDatabaseConfigured(metaSchemaName, metaTableName);

            if (!targetDatabaseConfigured)
            {
                //create custom schema when user supplied and only if platform supports it
                if (_dataService.IsSchemaSupported && null != metaSchemaName && !_dataService.SchemaName.Equals(metaSchemaName))
                {
                    _traceService.Info($"Target schema does not exist. Creating schema {metaSchemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
                    _configurationDataService.CreateSchema(metaSchemaName);
                    _traceService.Info($"Created schema {metaSchemaName} on {targetDatabaseName} on {targetDatabaseServer}.");
                }

                //create empty versions tracking table
                _traceService.Info($"Target database {targetDatabaseName} on {targetDatabaseServer} not yet configured for migration.");
                _configurationDataService.ConfigureDatabase(metaSchemaName, metaTableName);
                _traceService.Info($"Configured database migration support for {targetDatabaseName} on {targetDatabaseServer}.");
            }

            var targetDatabaseUpdated = _configurationDataService.UpdateDatabaseConfiguration(metaSchemaName, metaTableName);

            if (targetDatabaseUpdated)
            {
                _traceService.Info($"The configuration of migration has been updated for {targetDatabaseName} on {targetDatabaseServer}.");
            }
            else
            {
                _traceService.Debug($"The configuration of migration is up to date for {targetDatabaseName} on {targetDatabaseServer}.");
            }

            NonTransactionalContext nonTransactionalContext = null;

            //check for presence of failed no-transactional versions from previous runs
            var allVersions   = _configurationDataService.GetAllVersions(metaSchemaName, metaTableName);
            var failedVersion = allVersions.Where(x => x.Status == Status.Failed).FirstOrDefault();

            if (failedVersion != null)
            {
                //check if user had issue resolving option such as continue on failure
                if (nonTransactionalResolvingOption == null)
                {
                    //program should exit with non zero exit code
                    var message = @$ "Previous migration of " "{failedVersion.Version}" " version was not running in transaction and has failed when executing of script " "{failedVersion.FailedScriptPath}" " with following error: {failedVersion.FailedScriptError} {MESSAGES.ManualResolvingAfterFailureMessage}";
                    _traceService.Error(message);
                    throw new InvalidOperationException(message);
                }

                _traceService.Info($@"The non-transactional failure resolving option ""{nonTransactionalResolvingOption}"" was used. Version scripts already applied by previous migration run will be skipped.");
                nonTransactionalContext = new NonTransactionalContext(failedVersion, nonTransactionalResolvingOption.Value);
            }
            else
            {
                if (nonTransactionalResolvingOption != null)
                {
                    //program should exit with non zero exit code
                    _traceService.Error(@$ "The non-transactional failure resolving option " "{nonTransactionalResolvingOption}" " is available only if previous migration has failed.");
                    throw new InvalidOperationException();
                }
            }

            var appliedVersions = _configurationDataService.GetAllAppliedVersions(metaSchemaName, metaTableName)
                                  .Select(dv => dv.Version)
                                  .OrderBy(v => v)
                                  .ToList();

            //checks if target database already runs the latest version and skips work if it already is
            var targeDatabaseLatest = IsTargetDatabaseLatest(targetVersion, metaSchemaName, metaTableName);

            if (!targeDatabaseLatest)
            {
                //create a shared open connection to entire migration run
                using (var connection = _dataService.CreateConnection())
                {
                    connection.Open();
                    RunAllInternal(connection, null);
                }
            }
            else
            {
                //runs all scripts files inside _draft on every yuniql run regardless of state of target database
                using (var connection = _dataService.CreateConnection())
                {
                    connection.Open();
                    RunDraftInternal(connection, null);
                }
            }

            //local method
            void RunAllInternal(IDbConnection connection, IDbTransaction transaction)
            {
                //check if database has been pre-configured and execute init scripts
                if (!targetDatabaseConfigured)
                {
                    //runs all scripts in the _init folder
                    RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_init"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                    _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_init")}");
                }

                //checks if target database already runs the latest version and skips work if it already is
                //runs all scripts in the _pre folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");

                //runs all scripts int the vxx.xx folders and subfolders
                RunVersionScripts(connection, transaction, appliedVersions, workingPath, targetVersion, nonTransactionalContext, tokenKeyPairs, bulkSeparator: bulkSeparator, metaSchemaName: metaSchemaName, metaTableName: metaTableName, commandTimeout: commandTimeout, bulkBatchSize: bulkBatchSize, appliedByTool: appliedByTool, appliedByToolVersion: appliedByToolVersion, environmentCode: environmentCode);

                //runs all scripts in the _draft folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");

                //runs all scripts in the _post folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
            }

            //local method
            void RunDraftInternal(IDbConnection connection, IDbTransaction transaction)
            {
                //runs all scripts in the _pre folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_pre"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_pre")}");

                //runs all scripts in the _draft folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_draft"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_draft")}");

                //runs all scripts in the _post folder and subfolders
                RunNonVersionScripts(connection, transaction, Path.Combine(workingPath, "_post"), tokenKeyPairs, bulkSeparator: bulkSeparator, commandTimeout: commandTimeout, environmentCode: environmentCode);
                _traceService.Info($"Executed script files on {Path.Combine(workingPath, "_post")}");
            }
        }