ConfigurationFile

Configuration file

By default the current configuration file is SqlDatabase.dll.config. The file is located in the installation folder. It can be changed in CLI

$ SqlDatabase ... -configuration=path\to\sql-database.config

File example

<configuration>
  <configSections>
    <section name="sqlDatabase"
             type="SqlDatabase.Configuration.AppConfiguration, SqlDatabase"/>
  </configSections>

  <sqlDatabase>
               
    <assemblyScript className="SqlDatabaseScript"
                    methodName="Execute" />

    <!-- global variables, by default the list is empty -->
    <variables>
      <add name="Variable1"
           value="value1" />
      <add name="Variable2"
           value="value 2" />
    </variables>

    <!-- mssql configuration:
    - default scripts to read and update database version
    - few predefined variables, applicable only for mssql  -->
    <mssql getCurrentVersion="SELECT value from sys.fn_listextendedproperty('version', default, default, default, default, default, default)"
           setCurrentVersion="EXEC sys.sp_updateextendedproperty @name=N'version', @value=N'{{TargetVersion}}'">
      <variables>
        <add name="MsSqlVariable1"
             value="value1" />
        <add name="MsSqllVariable2"
             value="value 2" />
      </variables>
    </mssql>

    <!-- pgsql configuration:
    - default scripts to read and update database version
    - few predefined variables, applicable only for pgsql  -->
    <pgsql getCurrentVersion="SELECT version FROM public.version WHERE module_name = 'database'"
           setCurrentVersion="UPDATE public.version SET version='{{TargetVersion}}' WHERE module_name = 'database'">
      <variables>
        <add name="PgSqlVariable1"
             value="value1" />
        <add name="PgSqllVariable2"
             value="value 2" />
      </variables>
    </pgsql>

    <!-- 
      mysql configuration:
      - default scripts to read and update database version
      - few predefined variables, applicable only for mysql, by defult is empty
      -->
    <mysql getCurrentVersion="SELECT version FROM version WHERE module_name = 'database'"
           setCurrentVersion="UPDATE version SET version='{{TargetVersion}}' WHERE module_name = 'database'">
      <variables>
        <add name="MySqlVariable1"
             value="value1" />
        <add name="MySqlVariable2"
             value="value 2" />
      </variables>
    </pgsql>
  </sqlDatabase>
</configuration>

getCurrentVersion

An sql script to determine the current version of database, see database upgrade.

Default value for MSSQL Server (sqlDatabase/mssql/@getCurrentVersion):

SELECT value from sys.fn_listextendedproperty('version', default, default, default, default, default, default)

Default value for PostgreSQL (sqlDatabase/pgsql/@getCurrentVersion):

SELECT version FROM public.version WHERE module_name = 'database'

Default value for MySQL (sqlDatabase/mysql/@getCurrentVersion):

SELECT version FROM version WHERE module_name = 'database'

Warn: SqlDatabase does not validate the provided script, please make sure that script is working before running SqlDatabase.

setCurrentVersion

An sql script to update the current version of database, see database upgrade.

Default value for MSSQL Server (sqlDatabase/mssql/@setCurrentVersion):

EXEC sys.sp_updateextendedproperty @name=N'version', @value=N'{{TargetVersion}}'

Default value for PostgreSQL (sqlDatabase/pgsql/@setCurrentVersion):

UPDATE public.version SET version='{{TargetVersion}}' WHERE module_name = 'database'

Default value for MySQL (sqlDatabase/mysql/@setCurrentVersion):

UPDATE version SET version='{{TargetVersion}}' WHERE module_name = 'database'

Warn: SqlDatabase does not validate the provided script, please make sure that script is working before running SqlDatabase.

assemblyScript

A configuration of .NET Assembly scripts.

• className - a script class name, default value is SqlDatabaseScript
• methodName - a method, entry point of SqlDatabaseScript, default value is Execute

example

namespace <any namespace>
{
    public sealed class MyScript
    {
        public void MyEntryPoint(IDbCommand command, IReadOnlyDictionary<string, string> variables)
        {
            // write a message to a log
            Console.WriteLine("hello from my assembly script");
        }
    }
}

<sqlDatabase>
  <assemblyScript className="MyScript"
                  methodName="MyEntryPoint" />
</sqlDatabase>

variables

sections

• sqlDatabase/variables
• sqlDatabase/mssql/variables
• sqlDatabase/pgsql/variables
• sqlDatabase/mysql/variables

A list of variables in format

<add name="a name of variable" value="a value of variable" />
<add name="a name of variable" value="a value of variable" />
<add name="a name of variable" value="a value of variable" />

by default all lists are empty.

example

<sqlDatabase>
  <variables>
    <add name="SchemaName"
          value="demo" />
    <add name="TableName"
          value="Table1" />
  </variables>
</sqlDatabase>

-- script file *.sql
PRINT 'drop table {{SchemaName}}.{{TableName}}'
DROP TABLE [{{SchemaName}}].[{{TableName}}]

-- script at runtime
PRINT 'drop table demo.Table1'
DROP TABLE [demo].[Table1]