bo/Console/Commands/Install/InstallDbCommand.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
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.Value;
using Quadarax.Foundation.Core.Resource.Extensions;
using Quadarax.Foundation.Core.Value;

namespace BO.Console.Commands.Install
{
    internal class InstallDbCommand : DbBaseCommand
    {
        #region *** Constants ***
        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 %";

        #endregion

        #region *** Properties ***

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

        #endregion

        #region *** Constructor ***
        public InstallDbCommand(Engine engine) : base(engine)
        {
        }
        #endregion

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

            var result = new Result();
            using (var conn = OpenMasterConnection())
            {
                if (!CheckIfDbExists(conn, DatabseName))
                {
                    // database not exists
                    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 = $"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 = conn.Execute(query);
                    WriteDebugInfo($"Create script returns: {resultExecute}");
                    if (resultExecute == 0)
                        throw new Exception($"Database '{DatabseName}' creating FAILED.");
                    WriteCaption($"Database '{DatabseName}' CREATED");


                    using (var connDb = OpenConnection())
                    {
                        var script = Assembly.GetExecutingAssembly().GetEmbeddedFileContentAsString("BO.Console.Commands.Install.bo_create_schema.sql");
                        WriteCaption($"Creating latest DB structure in '{DatabseName}'");
                        resultExecute = conn.BatchExecute(script);
                        WriteDebugInfo($"Deploy script returns: {resultExecute}");
                        if (resultExecute == 0)
                            throw new Exception($"Database '{DatabseName}' deploying FAILED.");

                        var version = connDb.ExecuteScalar<string>(CommandType.Text, "select value from sys.extended_properties where NAME = 'BO_Version'");
                        WriteCaption($"Database '{DatabseName}' updated to version '{version}'");
                    }
                }
                else
                {
                    // database exists
                    WriteCaption($"Database '{DatabseName}' exists. SKIPPING");
                }
            }
            return result;
        }
        #endregion

        #region *** Private operations ***


        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));
            return arguments;
        }

        #endregion
    }
}