private static void CheckSchemaForUpgradeTables()
{
Logger.Log("Checking for UpgradeScript tables in database");
if (!TableExists(UpgradeScriptRunTableName))
{
Logger.Log($"Creating table {UpgradeScriptRunTableName}");
string createTable =
$"{UseDatabase} " +
$"CREATE TABLE {UpgradeScriptRunTableName} ( " +
$"{UpgradeScriptRunTableName}Id INTEGER NOT NULL UNIQUE IDENTITY(1, 1), " +
"ScriptFile VARCHAR(255) NOT NULL, " +
"ExecutedTime DATETIME NOT NULL DEFAULT GETUTCDATE(), " +
"IsSuccessful BIT NOT NULL DEFAULT 0 " +
"PRIMARY KEY (UpgradeScriptRunId) )";
SQLInterface.ExecuteNonQuery(createTable);
Logger.LogVerbose($"Table {UpgradeScriptRunTableName} created");
}
if (!TableExists(UpgradeScriptRunErrorTableName))
{
Logger.Log($"Creating table {UpgradeScriptRunErrorTableName}");
string createTable =
$"{UseDatabase} " +
$"CREATE TABLE {UpgradeScriptRunErrorTableName} ( " +
$"{UpgradeScriptRunErrorTableName}Id INTEGER NOT NULL UNIQUE IDENTITY(1, 1), " +
$"{UpgradeScriptRunTableName}Id INTEGER NOT NULL, " +
"ErrorMessage NTEXT, " +
$"PRIMARY KEY ({UpgradeScriptRunErrorTableName}Id), " +
$"FOREIGN KEY ({UpgradeScriptRunTableName}Id) REFERENCES {UpgradeScriptRunTableName}({UpgradeScriptRunTableName}Id) )";
SQLInterface.ExecuteNonQuery(createTable);
Logger.LogVerbose($"Table {UpgradeScriptRunErrorTableName} created");
}
}
private static void CheckDatabase()
{
string databaseQuery =
$"SELECT COUNT(*) FROM sys.databases WHERE [name] = '{Database}'";
var queryResults = SQLInterface.ExecuteQueryIntoDataTable(databaseQuery);
int count = (int)queryResults.Rows[0].ItemArray[0];
if (count == 0)
{
string createDatabase = $"CREATE DATABASE [{Database}]";
SQLInterface.ExecuteNonQuery(createDatabase);
}
}
private static bool TableExists(string tableName)
{
Logger.LogVerbose($"Checking for table {tableName} in database");
SQLInterface.ExecuteNonQuery(UseDatabase);
string tableQuery = $"SELECT COUNT(*) FROM sys.tables WHERE [name] = '{tableName}'";
var queryResult = SQLInterface.ExecuteQueryIntoDataTable(tableQuery);
bool result = (int)queryResult.Rows[0].ItemArray[0] == 1;
if (result)
{
Logger.LogVerbose($"Table {tableName} found");
}
else
{
Logger.LogVerbose($"Table {tableName} not found");
}
return(result);
}
private static void LogUpgradeScriptRun(string filePath, Exception exception = null)
{
Logger.LogVerbose($"Logging upgrade script for script {filePath}");
int isSuccessful = (exception == null ? 1 : 0);
filePath = Path.GetFileName(filePath);
string query =
$"INSERT INTO UpgradeScriptRun (ScriptFile, IsSuccessful) VALUES " +
$"('{filePath}', {isSuccessful})";
SQLInterface.ExecuteNonQuery(query);
if (isSuccessful == 0)
{
string message = exception.Message.Replace("'", "''");
query =
"INSERT INTO UpgradeScriptRunError (UpgradeScriptRunId, ErrorMessage) " +
$"SELECT TOP 1 UpgradeScriptRunId, '{message}' FROM UpgradeScriptRun WHERE ScriptFile = '{filePath}' " +
"ORDER BY ExecutedTime DESC";
SQLInterface.ExecuteNonQuery(query);
}
}
private static void RunNewDatabaseUpgradeScripts()
{
if (NewUpgradeScripts.Count == 0)
{
Logger.Log("No new upgrade scripts to run");
return;
}
SQLInterface.ExecuteNonQuery(UseDatabase);
string fileContent;
string[] statements;
var goSplit = new Regex("\r\n[ \t]{0,}GO([ \t]{0,}\r\n{0,}){1,}");
foreach (string file in NewUpgradeScripts)
{
Logger.Log($"Executing script {file}");
fileContent = $"{UseDatabase}\r\n GO\r\n {File.ReadAllText(file)}";
statements = goSplit.Split(fileContent);
try
{
foreach (string statement in statements)
{
if (!string.IsNullOrEmpty(statement))
{
SQLInterface.ExecuteNonQuery(statement);
}
}
LogUpgradeScriptRun(file);
}
catch (Exception e)
{
Logger.Log($"Error when running script: {e.Message}");
LogUpgradeScriptRun(file, e);
}
}
}