/// <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); }