bo/Console/Commands/Install/InstallDbCommand.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using BO.Console.Commands.Base;
using Quadarax.Foundation.Core.Data.Extensions;
using Quadarax.Foundation.Core.QConsole;
using Quadarax.Foundation.Core.QConsole.Argument;
using Quadarax.Foundation.Core.QConsole.Attributes;
using Quadarax.Foundation.Core.QConsole.Value;
using Quadarax.Foundation.Core.Resource.Extensions;
using Quadarax.Foundation.Core.Value;

namespace BO.Console.Commands.Install
{
    [CommandDefinition]
    internal class InstallDbCommand : DbBaseCommand
    {
        #region *** Constants ***

        private const string DB_VERSION = "1.0.10";
        private const string DB_PROP_NAME = "BO_Version";


        private const string CS_CMD_INSTALL_NAME = "dbinstall";
        private const string CS_CMD_INSTALL_DESCR = "install BO database from scratch (supports SqlServer, LocalDB).";
        
        
        private const string ARG_PATH_CODE = "dpath";
        private const string ARG_PATH_HINT = "data_file_path";
        private const string ARG_PATH_DESC = "Absolute path to SQL Server datafile directory (ends without \\ character). Use '|' instead of ':'";
        private const string ARG_ISIZE_CODE = "isize";
        private const string ARG_ISIZE_HINT = "initial_size";
        private const string ARG_ISIZE_DESC = "Initial size in MB for Data and Log file";
        private const string ARG_GROWTH_CODE = "growth";
        private const string ARG_GROWTH_HINT = "growth";
        private const string ARG_GROWTH_DESC = "Data file growth in %";

        private const string ARG_FORCE_DESC = "Flag if defined override (if exists) existing database (drop and create) with loosing data";


        private const string VAL_LOCALDB = "(localdb)";
        private const string VAL_NOFILE = "nofile";

        #endregion

        #region *** Properties ***

        public override string Name => CS_CMD_INSTALL_NAME;
        public override string Description => CS_CMD_INSTALL_DESCR;

        public override string Notes => "For localDB (local db engine) use argument -srv:" + VAL_LOCALDB + "\\mssqllocaldb";

        private Version LatestVersion { get; }

        #endregion

        #region *** Constructor ***
        public InstallDbCommand(Engine engine) : base(engine)
        {
            LatestVersion = new Version(DB_VERSION);
        }
        #endregion

        #region *** EXECUTE ***
        protected override Result OnExecute()
        {

            var result = new Result();
            using (var conn = OpenMasterConnection())
            {
                if (!CheckIfDbExists(conn, DatabseName))
                {
                    // database not exists
                    CreateDatabase(conn);

                    using (var connDb = OpenConnection())
                    {
                        UpdateModel(connDb);    
                    }
                }
                else
                {
                    // database exists
                    using (var connDb = OpenConnection())
                    {
                        var version = GetDbVersion(connDb);
                        WriteCaption($"Database '{DatabseName}' exists (v.{version}).");
                    }

                    if (IsForce.GetValueOrDefault())
                    {
                        DeleteDatabase(conn);
                        CreateDatabase(conn);
                        using (var connDb = OpenConnection())
                        {
                            UpdateModel(connDb);    
                        }
                    }
                    else
                    {
                        WriteInfo($"Cancel. For force reinstall database use {Engine.Configuration.CharacterNamedArgumentSeparator}{CS_ARG_NAME_FORCE} flag.");
                    }
                }
            }
            return result;
        }

        
        #endregion

        #region *** Private overrides ***

        protected override void OnValidateArguments()
        {
            if (ServerName.Contains(VAL_LOCALDB))
            {
                var pathArg = GetArgument(ARG_PATH_CODE);
                pathArg.Value.Set(VAL_NOFILE);
            }
            base.OnValidateArguments();
        }

        protected override IEnumerable<AbstractArgument> OnSetupArguments()
        {
            var arguments = new List<AbstractArgument>(base.OnSetupArguments());
            arguments.Add(new NamedArgument(ARG_PATH_CODE, ARG_PATH_DESC, ARG_PATH_HINT, TypeValuesEnum.String, string.Empty, true));
            arguments.Add(new NamedArgument(ARG_ISIZE_CODE, ARG_ISIZE_DESC, ARG_ISIZE_HINT, TypeValuesEnum.Integer, "10", false));
            arguments.Add(new NamedArgument(ARG_GROWTH_CODE, ARG_GROWTH_DESC, ARG_GROWTH_HINT, TypeValuesEnum.Integer, "10", false));
            AppendForceArgument(arguments,ARG_FORCE_DESC);
            return arguments;
        }

        #endregion

        #region *** Private Operations ***

        private void CreateDatabase(SqlConnection masterConnection)
        {
            WriteInfo($"Database '{DatabseName}' not exists creating ...");
            var dataPath = GetArgumentValueOrDefault<string>(ARG_PATH_CODE).Replace("|", ":");
            var initSize = GetArgumentValueOrDefault<int>(ARG_ISIZE_CODE);
            var growth = GetArgumentValueOrDefault<int>(ARG_GROWTH_CODE);
            var query = dataPath == VAL_NOFILE ? $"CREATE DATABASE {DatabseName} " : ($"CREATE DATABASE {DatabseName} ON PRIMARY " +
                $"(NAME = {DatabseName}_Data, " +
                $"FILENAME = '{dataPath}\\{DatabseName}.mdf', " +
                $"SIZE = {initSize}MB, FILEGROWTH = {growth}%) " +
                $"LOG ON (NAME = {DatabseName}_Log, " +
                $"FILENAME = '{dataPath}\\{DatabseName}.ldf', " +
                $"SIZE = {initSize}MB, " +
                $"FILEGROWTH = {growth}%)");
            WriteDebugInfo("Using query: " + query);
            var resultExecute = masterConnection.Execute(query);
            WriteDebugInfo($"Create script returns: {resultExecute}");
            if (resultExecute == 0)
                throw new Exception($"Database '{DatabseName}' creating FAILED.");
            WriteCaption($"Database '{DatabseName}' CREATED");
        }
        private void DeleteDatabase(SqlConnection masterConnection)
        {
            masterConnection.BatchExecute($"ALTER DATABASE [{DatabseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\nGO\nDROP DATABASE [{DatabseName}];\nGO");
            WriteInfo($"Database '{DatabseName}' dropped.");
        }

        private Version GetDbVersion(SqlConnection dbConnection)
        {
            var version = dbConnection.DatabasePropertyGet(DB_PROP_NAME);
            WriteDebugInfo($"DB version get: {version}");
            if (!string.IsNullOrEmpty(version))
            {
                return new Version(version);
            }
            return new Version();

        }

        private void SetDbVersion(SqlConnection dbConnection)
        {
            dbConnection.DatabasePropertyAddOrUpdate(DB_PROP_NAME,DB_VERSION);
            WriteDebugInfo($"DB version set: {DB_VERSION}");
        }

        private void UpdateModel(SqlConnection dbConnection)
        {
            var script = Assembly.GetExecutingAssembly().GetEmbeddedFileContentAsString("BO.Console.Scripts.bo_create_schema.sql");
            WriteCaption($"Creating latest DB structure in '{DatabseName}'");
            var resultExecute = dbConnection.BatchExecute(script);
            WriteDebugInfo($"Deploy script returns: {resultExecute}");
            if (resultExecute == 0)
                throw new Exception($"Database '{DatabseName}' deploying FAILED.");
            SetDbVersion(dbConnection);
            var version = GetDbVersion(dbConnection);
            WriteCaption($"Database '{DatabseName}' updated to version '{version}'");
        }

        #endregion
    }
}