Add support for SQLCMD mode scripts

[arturas-vitkauskas]

It would be awesome if this super database migration tool supported SQL Server scripts in SQLCMD mode. The Visual Studio Data Tools generate scripts in SQLCMD mode. It would be nice to add these SQLCMD mode scripts to the RoundHouse migrations without any modifications.

[arturas-vitkauskas]

Below is the proposed implementation of the SQLCMD mode support.

namespace roundhouse.databases.sqlserver
{
    using System;
    using System.Data;
    using System.Text;
    using infrastructure.app;
    using infrastructure.extensions;
    using System.Diagnostics;
    using System.IO;

    public class SqlServerSqlCmdDatabase : SqlServerDatabase
    {
        public override bool supports_ddl_transactions{ get { return false; } }
        public override bool split_batch_statements{ get { return false; } set { } }

        private string user_id = "";
        private string password = "";
        private string admin_user_id = "";
        private string admin_password = "";

        public override void initialize_connections(ConfigurationPropertyHolder configuration_property_holder)
        {
            base.initialize_connections(configuration_property_holder);

            string[] parts = connection_string.Split(';');
            foreach (string part in parts) {
                if (part.to_lower().Contains("user id")) {
                    user_id = part.Substring(part.IndexOf("=") + 1);
                }

                if (part.to_lower().Contains("password")) {
                    password = part.Substring(part.IndexOf("=") + 1);
                }
            }

            parts = admin_connection_string.Split(';');
            foreach (string part in parts) {
                if (part.to_lower().Contains("user id")) {
                    admin_user_id = part.Substring(part.IndexOf("=") + 1);
                }

                if (part.to_lower().Contains("password")) {
                    admin_password = part.Substring(part.IndexOf("=") + 1);
                }
            }
        }

        private bool database_specific_tasks_running = false;

        public override void run_database_specific_tasks()
        {
            database_specific_tasks_running = true;

            base.run_database_specific_tasks();
        }

        protected override void run_sql(string sql_to_run, ConnectionType connection_type, System.Collections.Generic.IList<parameters.IParameter<IDbDataParameter>> parameters)
        {
            if (parameters != null) {
                base.run_sql(sql_to_run, connection_type, parameters);
                return;
            }

            if (database_specific_tasks_running) {
                database_specific_tasks_running = false;
                base.run_sql(sql_to_run, connection_type, parameters);
                return;
            }

            if (string.IsNullOrEmpty(sql_to_run)) return;

            string tempScriptFile = Path.GetTempFileName();
            using (var writer = new StreamWriter(new FileStream(tempScriptFile, FileMode.Open, FileAccess.ReadWrite), Encoding.Unicode))
            {
                writer.WriteLine(":setvar {0} \"{1}\"", "DatabaseName", this.database_name);              
                writer.WriteLine("GO");
                writer.WriteLine();
                writer.WriteLine(":on error exit");
                writer.WriteLine("GO");
                writer.WriteLine();
                if (connection_type == ConnectionType.Default)
                {
                    writer.WriteLine("SET IMPLICIT_TRANSACTIONS OFF;");
                    writer.WriteLine("GO");
                    writer.WriteLine("BEGIN TRAN;");
                    writer.WriteLine("GO");
                    writer.WriteLine();                   
                }

                writer.WriteLine(sql_to_run);
                writer.WriteLine("GO");
                writer.WriteLine();
                if (connection_type == ConnectionType.Default)
                {
                    writer.WriteLine("IF @@TRANCOUNT > 1 RAISERROR(N'@@TRANCOUNT > 1 (BEGIN TRAN count greater than COMMIT TRAN count)', 18, 1) ");
                    writer.WriteLine("ELSE IF @@TRANCOUNT < 1 RAISERROR(N'@@TRANCOUNT < 1 (COMMIT TRAN count greater than BEGIN TRAN count)', 18, 1) ");
                    writer.WriteLine("ELSE COMMIT TRAN; ");
                    writer.WriteLine("GO");
                    writer.WriteLine();                   
                }
            }

            string sqlCmdArguments = string.Format("-S \"{0}\" -b -i \"{1}\" ", this.server_name, tempScriptFile);

            if (connection_type == ConnectionType.Default) {
                sqlCmdArguments += string.Format("-d \"{0}\" ", this.database_name);

                if (string.IsNullOrEmpty(this.user_id))
                    sqlCmdArguments += "-E ";
                else
                    sqlCmdArguments += string.Format("-U \"{0}\" -P \"{1}\" ", this.user_id, this.password);
            }
            else {
                sqlCmdArguments += "-d master ";

                if (string.IsNullOrEmpty(this.admin_user_id))
                    sqlCmdArguments += "-E ";
                else
                    sqlCmdArguments += string.Format("-U \"{0}\" -P \"{1}\" ", this.admin_user_id, this.admin_password);
            }

            Process sqlCmdProcess = new Process();
            sqlCmdProcess.StartInfo.FileName = "sqlcmd.exe";
            sqlCmdProcess.StartInfo.Arguments = sqlCmdArguments;
            sqlCmdProcess.StartInfo.UseShellExecute = false;
            sqlCmdProcess.Start();

            sqlCmdProcess.WaitForExit();

            File.Delete(tempScriptFile);

            if (sqlCmdProcess.ExitCode != 0)
                throw new ApplicationException("SQLCMD returned an error.");

        }
    }
}

[BiggerNoise]

This has been sitting for a while, let's drill into the use case:

You want to use Visual Studio to generate a SQLCMD file and then use RH to run it? I am trying to understand why you would want to do this. I always saw those files as very situational and very transient.

If you can elaborate on how you'd use this, we can consider it.

[arturas-vitkauskas]

Sure, I can elaborate on this next week as currently on vacations.

On Oct 11, 2017 20:06, "Andy Davis" notifications@github.com wrote:

This has been sitting for a while, let's drill into the use case:

You want to use Visual Studio to generate a SQLCMD file and then use RH to run it? I am trying to understand why you would want to do this. I always saw those files as very situational and very transient.

If you can elaborate on how you'd use this, we can consider it.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/chucknorris/roundhouse/issues/228#issuecomment-335897941, or mute the thread https://github.com/notifications/unsubscribe-auth/AOzJucHh-UpW9suh4UZPg6wg9CR_bDvEks5srQOMgaJpZM4GZdOE .

[arturas-vitkauskas]

So the use case is a combination of Visual Studio Database Project (*.sqlproj) and RoundhousE. Visual Studio Database Project is great for its SQL Server database visual design tools, comprehensive schema and data comparison tools, change script generation by comparing two databases or a database against a database project. RoundhousE is a super tool for organizing and running incremental change scripts.

Suppose, in development environment, we maintain the database project MyAppDb.sqlproj and the corresponding RoundhousE directory structure with *.sql files. In local SQL Server, we create two databases using RoundHousE: MyAppDb (database for local aplication development) and MyAppDb_CMP (database for schema and data comparison via Visual Studio Database Tools and generating SQLCMD compatible change scripts. Generated SQL delta files (sometimes with minor modifications) go to the "up" folder.

The proposed support for SQLCMD mode gives a few advantages: 1) SQL delta files generated by Visual Studio Database Tools can go to the "up" folder often without any modification (just a quick review) 2) SQLCMD variable references like $(DatabaseName) are automatically replaced with correct values 3) Automatic transaction handling. Each SQL change script is started in a transaction, if success, automatic commit, if error, automatic rollback only of the failed script. No need to care about transactions in SQL delta files. 4) SQLCMD mode has various features, which can be used to run certain SQL delta files conditionally, or even let one SQL file to dynamically generate another temporary SQL script and then execute it.

IMHO, Visual Studio Database Tools and RoundhousE is a perfect match to increase developer productivity in authoring and refactoring SQL Server database.

[BiggerNoise]

Thank you for getting back to me. I want to touch base with @ferventcoder on this one. I've not worked this way, but some of what I was reading in the wiki suggests that it was a desired feature at one point.

At the moment, we're still engaged in some fairly significant rework of the project structure so we don't have a lot of bandwidth to consider this, but give us a couple of weeks and we'll get back to you with a determination of what we want to do with this.

[chrisedebo]

Any news on this? I could benefit from the variable reference stuff. A post deployment script could be updated with a block of variables from our deployment system which could then be run by roundhouse. This would mean generic SQL Server agent job deployment scripts in source control with variables added to customise per environment.

:setVar ManagementDatabase "EnvironmentManagement"
:setvar JobCategory "Data Synchronisation (Local)"
:setvar OwnerLoginName "sa"

:setVar TargetDatabase "test_baseimage"
:setvar JobName "prd data sync - testbaseimage"
:setvar JobDescription "Synchronises data from production database to test_baseimage database"

:r .\SqlAgentJobs\PRD-DataSyncJob.sql