/// <summary>
/// Run external pre and post script
/// </summary>
/// <param name="dbEngine">DB Engine object.</param>
/// <param name="databaseName">The database Name.</param>
/// <param name="externalUpdateScriptPath">The external Update Script Path.</param>
private static void RunExternalScript(
DatabaseEngine dbEngine,
string databaseName,
string externalUpdateScriptPath)
{
Trace.TraceInformation(
"Execute external script {0} for the database {1}...\r\n",
externalUpdateScriptPath,
databaseName);
using (var sqlScriptFile = new StreamReader(externalUpdateScriptPath))
{
string externalSqlScript = string.Format(
"USE {0}\r\nGO\r\n",
databaseName);
externalSqlScript += sqlScriptFile.ReadToEnd();
externalSqlScript = externalSqlScript.Replace(
"$__CURRENT_DATABASE_NAME__",
databaseName);
externalSqlScript = externalSqlScript.Replace("$__IS_DEFAULT_DATABASE__", string.Compare(databaseName, Configuration.Default.DefaultDatabaseName, true) == 0 ? "1" : "0");
dbEngine.ExecuteBatch(
externalSqlScript);
}
}
private static void GenerateBackup()
{
//
// Prepare SQL Script
//
var generatedSqlScript = new StringBuilder();
foreach (string sqlScriptPath in Configuration.Default.BackupContent_SqlScriptFiles)
{
using (var sqlScriptFile = new StreamReader(Environment.ExpandEnvironmentVariables(sqlScriptPath)))
{
string sqlScript = sqlScriptFile.ReadToEnd();
generatedSqlScript.Append(
sqlScript.Replace(
"#SURVEYSID#",
""));
generatedSqlScript.Append("\r\nGO\r\n");
}
}
//
// Script created, we're save it to the file.
// Just to have a copy.
//
string outSqlScriptFilePath =
PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Path.ChangeExtension(
Environment.ExpandEnvironmentVariables(
Configuration.Default.SourceDatabaseBackupFilePath),
"sql"));
using (var outSqlScriptFile = new StreamWriter(outSqlScriptFilePath))
{
outSqlScriptFile.Write(generatedSqlScript.ToString());
}
var databaseEngine = new DatabaseEngine();
databaseEngine.CreateDatabaseFromScriptFile(
Configuration.Default.SourceDatabaseName,
outSqlScriptFilePath,
Configuration.Default.BackupContent_ClrAssembyFiles);
//
// Now lets generate backup
//
string outSqlBackupFilePath =
PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Environment.ExpandEnvironmentVariables(
Configuration.Default.SourceDatabaseBackupFilePath));
databaseEngine.CreateBackup(
Configuration.Default.SourceDatabaseName,
outSqlBackupFilePath);
}
public string[] GetDatabasesForUpgrade(
string productionDatabaseName)
{
if (productionDatabaseName != "*")
{
return(new[] { productionDatabaseName });
}
var databaseEngine = new DatabaseEngine();
using (var cn = new SqlConnection(databaseEngine.ConnectionString))
{
cn.Open();
var sc = new ServerConnection(cn);
var srv = new Server(sc);
var databasesForUpgrade = new List <string>();
foreach (Microsoft.SqlServer.Management.Smo.Database database in srv.Databases)
{
//
// Update all databases by specefied regular expression mode
//
var regEx = new Regex(Configuration.Default.DatabaseNamePattern);
if (regEx.IsMatch(database.Name))
{
databasesForUpgrade.Add(database.Name);
}
}
return(databasesForUpgrade.ToArray());
}
}
private static int Main(string[] args)
{
try
{
//
// Check input parameters.
//
if (args.Length == 0)
{
PrintHelp();
return(1);
}
string command = args[0];
if (string.Compare(command, "addnonsplitobjects", true) == 0)
{
if (args.Length != 2)
{
PrintHelp();
return(1);
}
string productionDatabaseName = args[1];
AddNonSplitObjects(productionDatabaseName);
}
else
if (string.Compare(command, "generatebackup", true) == 0)
{
GenerateBackup();
}
else if (string.Compare(command, "generateupdate", true) == 0)
{
if (args.Length != 2)
{
PrintHelp();
return(1);
}
string productionDatabaseName = args[1];
var dbEngine = new DatabaseEngine();
//dbEngine.CreateBackup("ConfirmitCATI", @"C:\_Grigoriy\ConfirmitCATI.bak");
//dbEngine.CreateDatabaseFromBackupFile("ConfirmitCATI", @"c:\_Grigoriy\UpdateDatabaseProject\ConfirmitCATI for Restore.bak");
//dbEngine.CreateDatabaseFromBackupFile("PtBvDb1", @"c:\_Grigoriy\PtBvDb1_10");
if (dbEngine.IsDatabaseExists(Configuration.Default.DefaultDatabaseName) == false)
{
Console.WriteLine(
"Default database {0} does not exists or access denied",
Configuration.Default.DefaultDatabaseName);
return(1);
}
ConsoleKeyInfo key;
if (productionDatabaseName == "*")
{
//
// Update all databases by name pattern mode.
//
if (Configuration.Default.IsTestModeEnabled)
{
Console.WriteLine(
"Test mode cannot be used with '*' as database name.");
return(1);
}
}
else
{
//
// Update single database mode.
//
if (Configuration.Default.IsTestModeEnabled)
{
Console.WriteLine(
"UpdateDatabase.exe started in the test mode.\r\n" +
"Test mode drops and creates production database ({0}) from the backup file.\r\n" +
"Are you sure you want use test mode and drop database {0}?\r\n" +
"Press Y to continue or N to exit",
productionDatabaseName);
key = Console.ReadKey();
if ((key.KeyChar != 'y') && (key.KeyChar != 'Y'))
{
return(1);
}
Console.WriteLine();
}
if (dbEngine.IsDatabaseExists(productionDatabaseName) == false)
{
Console.WriteLine(
"Database {0} does not exists or access denied",
productionDatabaseName);
return(1);
}
} // if (productionDatabaseName == "*")
var confirmation = new StringBuilder();
confirmation.Append(
"Following databases will be updated, are you sure?\r\n" +
"Press Y to continue or N to exit\r\n");
foreach (string databaseFoUpdate in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
{
confirmation.AppendFormat("* {0}\r\n", databaseFoUpdate);
}
Console.Write(confirmation.ToString());
key = Console.ReadKey();
if ((key.KeyChar != 'y') && (key.KeyChar != 'Y'))
{
return(1);
}
Console.WriteLine();
if (Configuration.Default.UpdateDefaultDatabaseBackup)
{
Console.Write(
"\r\n" +
"Database utility will update default database backup file. " +
"So you don't need update it manually. " +
"All newly created instances will use new, updated database.\r\n" +
"Default database name: {0}\r\n" +
"Default database backup file path: {1}\r\n" +
"\r\n" +
"Please check settings are correct.\r\n" +
"If something is wrong press N, correct configuration file and restart utility.\r\n" +
"\r\n" +
"Would you like to continue?\r\n" +
"Press Y to continue or N to exit\r\n",
Configuration.Default.DefaultDatabaseName,
Configuration.Default.DefaultDatabaseBackupFilePath);
key = Console.ReadKey();
if ((key.KeyChar != 'y') && (key.KeyChar != 'Y'))
{
return(1);
}
Console.WriteLine();
}
else
{
Console.Write(
"\r\n" +
"Default database name: {0}\r\n" +
"\r\n" +
"Please check settings are correct.\r\n" +
"If something is wrong press N, correct configuration file and restart utility.\r\n" +
"\r\n" +
"Would you like to continue?\r\n" +
"Press Y to continue or N to exit\r\n",
Configuration.Default.DefaultDatabaseName);
key = Console.ReadKey();
if ((key.KeyChar != 'y') && (key.KeyChar != 'Y'))
{
return(1);
}
Console.WriteLine();
}
GenerateUpdate(productionDatabaseName);
}
else
{
Console.WriteLine("Unknown command {0}", command);
PrintHelp();
return(1);
}
return(0);
}
catch (Exception ex)
{
Trace.TraceError(
ex.ToString());
return(1);
}
}
/// <summary>
/// Add non split objects to databases
/// </summary>
private static void AddNonSplitObjects(string productionDatabaseName)
{
//
// Restore so called source database.
//
var dbEngine = new DatabaseEngine();
DateTime now = DateTime.Now;
foreach (string databaseName in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
{
var currentDBEngine = new DatabaseEngine(databaseName);
string outputFolder = string.Format(
"Output\\{0}\\{1}",
now.ToString("yyyy.MM.dd HH.mm.ss"),
databaseName);
Configuration.Default.OutputFolder = Path.Combine(
Configuration.Default.Path,
outputFolder);
//
// Backup production database. For the case,
// If update will do something wrong, then we can restore
// original database from the backup.
//
Trace.TraceInformation("Backup production database {0}...\r\n", databaseName);
currentDBEngine.CreateBackup(
databaseName,
PathsEngine.CorrectBackupPathAccordingToConfig(
Path.Combine(
Configuration.Default.OutputFolder,
databaseName + ".bak")));
string proceduresPath = Environment.ExpandEnvironmentVariables(@"%PROJPATH%\UNITS\bv7\SQL\PROCEDURES\");
foreach (string currentOriginalProcedureName in Configuration.Default.SplitProceduresList)
{
string procedurePath = proceduresPath + currentOriginalProcedureName + ".sql";
Trace.TraceInformation("Create procedure {0} for the database {1}...\r\n", currentOriginalProcedureName, databaseName);
using (var sr = new StreamReader(procedurePath))
{
try
{
currentDBEngine.ExecuteBatch(sr.ReadToEnd().Replace("#SURVEYSID#", ""));
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadKey();
}
}
}
string tablesPath = Environment.ExpandEnvironmentVariables(@"%PROJPATH%\UNITS\bv7\SQL\TABLES\");
foreach (string currentTableNames in Configuration.Default.SplitTablesList)
{
string currentOriginalTableName = currentTableNames.Split(new[] { ';' })[0];
string tablePath = tablesPath + currentOriginalTableName + ".sql";
Trace.TraceInformation("Create table {0} for the database {1}...\r\n", currentOriginalTableName, databaseName);
using (var sr = new StreamReader(tablePath))
{
try
{
currentDBEngine.ExecuteBatch(sr.ReadToEnd().Replace("#SURVEYSID#", ""));
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadKey();
}
}
}
// Add three triggers for BvInterview
var triggersPath = new[] {
Environment.ExpandEnvironmentVariables(@"%PROJPATH%\UNITS\bv7\SQL\PROCEDURES\BvTrInterview_InterviewsDelete.sql"),
Environment.ExpandEnvironmentVariables(@"%PROJPATH%\UNITS\bv7\SQL\PROCEDURES\BvTrInterview_InterviewsInsert.sql"),
Environment.ExpandEnvironmentVariables(@"%PROJPATH%\UNITS\bv7\SQL\PROCEDURES\BvTrInterview_InterviewsUpdate.sql")
};
foreach (string triggerPath in triggersPath)
{
if (!File.Exists(triggerPath))
{
continue;
}
Trace.TraceInformation("Create trigger {0} for table BvInterview...\r\n", triggerPath);
using (var sr = new StreamReader(triggerPath))
{
try
{
currentDBEngine.ExecuteBatch(sr.ReadToEnd().Replace("#SURVEYSID#", ""));
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
Console.ReadKey();
}
}
}
}
}
private static void GenerateUpdate(
string productionDatabaseName)
{
//
// Restore so called source database.
//
var dbEngine = new DatabaseEngine();
Trace.TraceInformation("Restore {0} database...\r\n", Configuration.Default.SourceDatabaseName);
string sourceDatabaseBackupFilePath =
PathsEngine.CorrectBackupPathAccordingToConfig(
PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Configuration.Default.SourceDatabaseBackupFilePath));
dbEngine.CreateDatabaseFromBackupFile(
Configuration.Default.SourceDatabaseName,
sourceDatabaseBackupFilePath);
if ((Configuration.Default.IsTestModeEnabled) && (productionDatabaseName != "*"))
{
//
// Restore production database from the backup file.
// Needed just to simplify testing.
//
Trace.TraceInformation("Restore {0} database...\r\n", productionDatabaseName);
string productionDatabaseBackupFilePath =
PathsEngine.CorrectBackupPathAccordingToConfig(
PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Configuration.Default.ProductionDatabaseBackupFilePath));
dbEngine.CreateDatabaseFromBackupFile(
productionDatabaseName,
productionDatabaseBackupFilePath);
}
DateTime now = DateTime.Now;
foreach (string databaseName in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
{
string outputFolder = string.Format(
"Output\\{0}\\{1}",
now.ToString("yyyy.MM.dd HH.mm.ss"),
databaseName);
Configuration.Default.OutputFolder = Path.Combine(
Configuration.Default.Path,
outputFolder);
//
// Backup production database. For the case,
// If update will do something wrong, then we can restore
// original database from the backup.
//
Trace.TraceInformation("Backup production database {0}...\r\n", databaseName);
dbEngine.CreateBackup(
databaseName,
PathsEngine.CorrectBackupPathAccordingToConfig(
Path.Combine(
Configuration.Default.OutputFolder,
databaseName + ".bak")));
var scriptGenerator = new UpgradeScriptGenerator(
databaseName);
string dropScriptText;
string createScriptText;
string alterScriptText;
scriptGenerator.GenerateUpgradeScript(
databaseName,
Configuration.Default.SourceDatabaseName,
out dropScriptText,
out createScriptText,
out alterScriptText);
SaveScriptText(
dropScriptText,
"DROP.sql");
SaveScriptText(
createScriptText,
"CREATE.sql");
SaveScriptText(
alterScriptText,
"UPDATE.sql");
}//foreach (string databaseName in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
if (Configuration.Default.ExecuteUpdateScript)
{
Console.WriteLine(
"All scripts for update production databases was generated.\r\n" +
"Press Y to continue or N to exit");
ConsoleKeyInfo key = Console.ReadKey();
Console.WriteLine();
if (key.KeyChar != 'y' && key.KeyChar != 'Y')
{
return;
}
foreach (string databaseName in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
{
if (string.IsNullOrEmpty(Configuration.Default.ExternalPreUpdateScriptPath) == false)
{
Trace.TraceInformation("Execute PRE script for the database {0}...\r\n", databaseName);
string externalUpdateScriptPath = PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Configuration.Default.ExternalPreUpdateScriptPath);
RunExternalScript(dbEngine, databaseName, externalUpdateScriptPath);
}
string outputFolder = string.Format(
"Output\\{0}\\{1}",
now.ToString("yyyy.MM.dd HH.mm.ss"),
databaseName);
string dropScriptText;
using (var sr = new StreamReader(outputFolder + @"\DROP.sql"))
{
dropScriptText = sr.ReadToEnd();
}
Trace.TraceInformation("Execute DROP script for the database {0}...\r\n", databaseName);
dbEngine.ExecuteBatch(
dropScriptText);
string alterScriptText;
using (var sr = new StreamReader(outputFolder + @"\UPDATE.sql"))
{
alterScriptText = sr.ReadToEnd();
}
Trace.TraceInformation("Execute ALTER script for the database {0}...\r\n", databaseName);
dbEngine.ExecuteBatch(
alterScriptText);
string createScriptText;
using (var sr = new StreamReader(outputFolder + @"\CREATE.sql"))
{
createScriptText = sr.ReadToEnd();
}
Trace.TraceInformation("Execute CREATE script for the database {0}...\r\n", databaseName);
dbEngine.ExecuteBatch(
createScriptText);
if (string.IsNullOrEmpty(Configuration.Default.ExternalPostUpdateScriptPath) == false)
{
Trace.TraceInformation("Execute POST script for the database {0}...\r\n", databaseName);
string externalUpdateScriptPath = PathsEngine.ConvertRelativePath2RootedPathIfNeeded(
Configuration.Default.ExternalPostUpdateScriptPath);
RunExternalScript(dbEngine, databaseName, externalUpdateScriptPath);
}
} //foreach (string databaseName in dbEngine.GetDatabasesForUpgrade(productionDatabaseName))
} //if (Configuration.Default.ExecuteUpdateScript == true)
//
// All databases successfully upgraded, now we need:
// 1. Create new backup file if needed
// 2. Drop source "upgrade" database created by utility
//
if (Configuration.Default.UpdateDefaultDatabaseBackup)
{
Trace.TraceInformation("Backuping default database {0}, path {1}...\r\n",
Configuration.Default.DefaultDatabaseName,
Configuration.Default.DefaultDatabaseBackupFilePath);
dbEngine.CreateBackup(
Configuration.Default.DefaultDatabaseName,
Configuration.Default.DefaultDatabaseBackupFilePath);
}
dbEngine.DropDatabase(
Configuration.Default.SourceDatabaseName);
}
public Database(string databaseName)
{
m_DatabaseEngine = new DatabaseEngine(databaseName);
SurveyIds = GetSurveyIds();
}
