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 GetDatabaseSchema()
{
Logger.Log("Retrieving table schema");
string tableQueryFile = Path.Combine(TemplateDirectory, "TableSchemaQuery.txt");
string tableSchemaQuery = File.ReadAllText(tableQueryFile);
tableSchemaQuery = tableSchemaQuery.Replace("%_DATABASE_%", Database);
tableSchemaQuery = tableSchemaQuery.Replace("%_SCHEMA_%", currentSchema);
DatabaseSchema = SQLInterface.ExecuteQueryIntoDataTable(tableSchemaQuery);
}
private static void BuildFunctionClass()
{
Logger.Log("Retrieving function schema");
string outputFileName = "DbFunctions.cs";
string functionSchemaQuery = ParseTemplate("FunctionSchemaQuery.txt");
functionSchemaQuery = functionSchemaQuery.Replace("%_DATABASE_%", Database);
functionSchemaQuery = functionSchemaQuery.Replace("%_SCHEMA_%", currentSchema);
var functionSchema = SQLInterface.ExecuteQueryIntoDataTable(functionSchemaQuery);
if (functionSchema.Rows.Count == 0)
{
Logger.Log("No functions found");
return;
}
headerLines.Clear();
fieldLines.Clear();
footerLines.Clear();
string fileText = ParseTemplate("FunctionHeader.txt");
int i = 0;
List <DataRow> parameters = new List <DataRow>();
DataRow currentRow;
while (i < functionSchema.Rows.Count)
{
currentRow = functionSchema.Rows[i];
currentFunction = currentRow["FunctionName"].ToString();
parameters.Add(currentRow);
i++;
if (i < functionSchema.Rows.Count)
{
currentRow = functionSchema.Rows[i];
while (i < functionSchema.Rows.Count &&
currentFunction.Equals(currentRow["FunctionName"].ToString()))
{
parameters.Add(currentRow);
i++;
if (i < functionSchema.Rows.Count)
{
currentRow = functionSchema.Rows[i];
}
}
}
fileText += BuildFunctionMethodDefinition(parameters);
}
fileText += ParseTemplate("FunctionFooter.txt");
string outputPath = Path.Combine(OutputDirectory, outputFileName);
Logger.Log($"Writing code to {outputPath}");
File.WriteAllText(outputPath, fileText);
}
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);
}
}
}
private static void GetNewUpgradeScripts()
{
Logger.Log("Determining upgrade scripts to run");
NewUpgradeScripts = Directory.GetFiles(UpgradeScriptDirectory)
.Where(e => CorrectFileNameRegex.IsMatch(Path.GetFileName(e)) &&
UpgradeScriptFilterRegex.IsMatch(Path.GetFileName(e)))
.OrderBy(e => Path.GetFileName(e).Split('_')[0])
.Select(e => Path.GetFileName(e))
.ToList();
Logger.LogVerbose($"{NewUpgradeScripts.Count} valid scripts found in source directory");
string successfulScriptsQuery = $"SELECT DISTINCT ScriptFile FROM {UpgradeScriptRunTableName} WHERE IsSuccessful = 1";
var queryResults = SQLInterface.ExecuteQueryIntoDataTable(successfulScriptsQuery);
Logger.LogVerbose($"{queryResults.Rows.Count} successful scripts found in database records");
foreach (DataRow row in queryResults.Rows)
{
string upgradeScript = row.ItemArray[0].ToString();
Logger.LogVerbose($"Removing script {upgradeScript} from list");
if (NewUpgradeScripts.Contains(upgradeScript))
{
NewUpgradeScripts.Remove(upgradeScript);
Logger.LogVerbose("Script removed");
}
else
{
Logger.LogVerbose("Script does not exist in directory");
}
}
Logger.LogVerbose($"{NewUpgradeScripts.Count} new scripts found in directory");
NewUpgradeScripts = NewUpgradeScripts
.OrderBy(e => int.Parse(e.Split('_')[0]))
.Select(e => Path.Combine(UpgradeScriptDirectory, e))
.ToList();
}
public static void ProcessArguments(string[] args)
{
SetDefaultConfiguration();
try
{
for (int i = 0; i < args.Length; i++)
{
string arg = args[i].ToLower();
switch (arg)
{
case HelpSwitch:
PrintHelpPage();
Environment.Exit((int)ExitCode.OK);
break;
case ConfigFileSwitch:
AppConfigReader.LoadConfigurationFromFile(args[++i]);
ImportFromConfigFile();
break;
// Upgrade Script options
case UpgradeScriptSwitch:
RunUpgradeScripts = true;
break;
case UpgradeScriptDirectorySwitch:
UpgradeScriptDirectory = args[++i];
break;
case UpgradeScriptFileRegexSwitch:
UpgradeScriptFileRegex = args[++i];
break;
case UpgradeScriptTemplateGuidMonikerSwitch:
UpgradeScriptTemplateGuidMonikerPrefix = args[++i];
break;
// Entity code generation options
case EntityCodeGenerationSwitch:
RunEntityCodeGeneration = true;
break;
case TemplateDirectorySwitch:
TemplateDirectory = args[++i];
break;
case DatabaseSchemaListSwitch:
DatabaseSchemas = args[++i].Split(';');
break;
case CodeGenOutputSwitch:
EntityCodeOutputDirectory = args[++i];
break;
// Code build options
case CodeBuildSwitch:
RunCodeBuild = true;
break;
case ProjectNameSwitch:
ProjectName = args[++i];
break;
// Logging options
case LogFileSwitch:
DoLogging = true;
SetLogFile(args[++i]);
break;
case DebugSwitch:
case VerboseLoggingSwitch:
VerboseLogging = true;
break;
case LogMessagePrefixSwitch:
LogMessagePrefix = args[++i];
break;
// Database options
case DatabaseConnectionStringSwitch:
ConnectionString = args[++i];
break;
case DatabaseSwitch:
Database = args[++i];
break;
}
}
CheckDatabase();
if (DatabaseSchemas.Contains("*"))
{
string schemaQuery =
$"USE [{Database}] " +
"SELECT DISTINCT s.[name] FROM sys.tables t " +
"INNER JOIN sys.schemas s ON s.schema_id = t.schema_id " +
"WHERE s.[name] != 'dbo' " +
"UNION ALL SELECT 'dbo'";
var queryResults = SQLInterface.ExecuteQueryIntoDataTable(schemaQuery);
string result = "";
foreach (DataRow row in queryResults.Rows)
{
result += row.ItemArray[0].ToString() + ",";
}
DatabaseSchemas = result.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
}
}
catch (Exception e)
{
Console.WriteLine($"ERROR: {e.Message}");
PrintHelpPage();
Environment.Exit((int)ExitCode.ArgumentError);
}
}
