/// <summary> /// Performs an install of a database /// </summary> public void Install(InstallSetup setup) { //The connection string must reference an existing database if (!DatabaseServer.TestConnectionString(setup.ConnectionString)) { throw new Exception("The connection string does not reference a valid database."); } try { UpgradeInstaller.UpgradeDatabase(setup); } catch (InvalidSQLException ex) { var sb = new StringBuilder(); sb.AppendLine(); sb.AppendLine("BEGIN ERROR SQL"); sb.AppendLine(ex.SQL); sb.AppendLine("END ERROR SQL"); sb.AppendLine(); Log.Verbose(sb.ToString()); UpgradeInstaller.LogError(ex, sb.ToString()); throw; } catch (Exception ex) { throw; } }
public static List <string> GetEmbeddedScripts(string resourceFileName, InstallSetup setup) { var retval = new List <string>(); var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); foreach (var sql in scripts) { retval.Add(sql); } return(retval); }
/// <summary> /// Returns the upgrade script for the specified database /// </summary> public string GetScript(InstallSetup setup) { if (string.IsNullOrEmpty(setup.ConnectionString) && setup.Version == null) { throw new Exception("The connection string must be set."); } if (setup.SkipSections == null) { setup.SkipSections = new List <string>(); } return(UpgradeInstaller.GetScript(setup)); }
public static string[] ReadSQLFileSectionsFromResource(string resourceFileName, InstallSetup setup) { if (string.IsNullOrEmpty(resourceFileName)) { return new string[] { } } ; var skipSectionsLowered = new List <string>(); var skipSections = setup.SkipSections.ToList(); if (skipSections != null) { skipSections.Where(x => x != null).ToList().ForEach(x => skipSectionsLowered.Add(x.ToLower())); } #region Load Full Script var fullScript = string.Empty; if (resourceFileName.ToLower().EndsWith(".pgsql")) { var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var sr = new System.IO.StreamReader(manifestStream)) { fullScript = sr.ReadToEnd(); } } catch { } finally { manifestStream.Close(); } } else if (resourceFileName.ToLower().EndsWith(".zip")) { var tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString()); Directory.CreateDirectory(tempPath); var zipPath = Path.Combine(tempPath, Guid.NewGuid().ToString()); var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var fileStream = File.Create(zipPath)) { manifestStream.CopyTo(fileStream); } } catch { } finally { manifestStream.Close(); } using (var archive = System.IO.Compression.ZipFile.Open(zipPath, System.IO.Compression.ZipArchiveMode.Update)) { archive.ExtractToDirectory(tempPath); } System.Threading.Thread.Sleep(250); File.Delete(zipPath); var files = Directory.GetFiles(tempPath, "*.*").OrderBy(x => x).ToList(); files.ForEach(x => fullScript += (File.ReadAllText(x) + "\r\n--GO\r\n")); System.Threading.Thread.Sleep(250); files.ForEach(x => File.Delete(x)); System.Threading.Thread.Sleep(250); Directory.Delete(tempPath); } else { return(new string[] { }); } #endregion var retval = new ArrayList(); var sb = new StringBuilder(); var allLines = fullScript.Replace("\r\n", "\n").Replace("\r", "\n").Split('\n'); var skippingQueue = new List <string>(); foreach (var lineText in allLines) { //If we are currently NOT skipping script then process the SQL //Check to determine if this is a skip section start if (lineText.ToUpper().StartsWith("--##SECTION BEGIN [")) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skipSectionsLowered.Contains(sectionName)) { //We are now skipping script skippingQueue.Add(sectionName); } } else if (lineText.ToUpper().StartsWith("--##SECTION END [")) { if (skippingQueue.Count > 0) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skippingQueue.Last().ToLower() == sectionName) { //We are now skipping script skippingQueue.RemoveAt(0); } } else { //Do Nothing } } else if (skippingQueue.Count == 0) { if (lineText.ToUpper().Trim() == "--GO") { var s = sb.ToString(); s = s.Trim(); retval.Add(s); sb = new StringBuilder(); } else if (lineText.ToUpper().StartsWith("--##METHODCALL")) { retval.Add(lineText); sb = new StringBuilder(); } else { var s = lineText; if (s.EndsWith("\r")) { s = lineText.Substring(0, lineText.Length - 1); } sb.AppendLine(s); } } } //Last string if (!string.IsNullOrEmpty(sb.ToString())) { retval.Add(sb.ToString()); } return((string[])retval.ToArray(typeof(string))); }
private static void CallMethod(string text, NpgsqlConnection connection, NpgsqlTransaction transaction, InstallSetup setup) { var timer = Stopwatch.StartNew(); var cleaned = string.Empty; try { cleaned = text .Replace("--##METHODCALL", string.Empty) .Replace("[", string.Empty) .Replace("]", string.Empty) .Trim(); var arr = cleaned.Split('.'); var methodName = arr.Last(); var typeName = string.Join(".", arr.Take(arr.Length - 1)); Type type = Type.GetType(typeName); var methodType = type.GetMethod(methodName); if (methodType == null) { throw new Exception("Method: '" + methodName + "' not implemented"); } Log.Verbose(TheDate + " Start CallMethod=" + methodName); if (methodType.GetParameters().Count() == 2) { methodType.Invoke(null, new object[] { connection, transaction }); } else if (methodType.GetParameters().Count() == 3) { methodType.Invoke(null, new object[] { connection, transaction, setup.ConnectionString }); } else { throw new Exception("Method: '" + methodName + "' does not have valid parameters."); } timer.Stop(); Log.Verbose(TheDate + " End CallMethod=" + methodName + ", Elapsed=" + timer.FormattedTime()); } catch (Exception ex) { throw new Exception("The external method call '" + cleaned + "' failed.", ex); } }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts) { if (sql.StartsWith("--##METHODCALL")) { CallMethod(sql, connection, transaction, setup); return; } //Test for empty statements var originalSQL = sql.Trim(); sql = originalSQL; if (string.IsNullOrEmpty(sql)) { return; } //Test for noop statements (all comments/empty strings) var lines = sql.BreakLines().TrimAll(); lines.RemoveAll(x => x.StartsWith("--")); lines.RemoveAll(x => x == ""); if ([email protected]()) { return; } lines = sql.BreakLines().TrimAll(); //Reset #region Get Script Key var isBody = false; var key = Guid.NewGuid(); var l = lines.FirstOrDefault(x => x.StartsWith("--MODELID: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run //if (!Guid.TryParse(l, out key)) key = Guid.NewGuid(); } else { l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID,BODY:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run if (!Guid.TryParse(l, out key)) { key = Guid.NewGuid(); } else { isBody = true; } } } #endregion if (string.IsNullOrEmpty(sql)) { return; } #region Try to remove objects before creation var dropObjectName = string.Empty; var dropSQL = GetSQLDropScript(sql); //Add a bit of convenience for dropping DB objects before creation if (!string.IsNullOrEmpty(dropSQL)) { try { if (!setup.CheckOnly) { var dropCommand = new NpgsqlCommand(dropSQL, connection); dropCommand.Transaction = transaction; dropCommand.CommandTimeout = 0; DatabaseServer.ExecuteCommand(dropCommand); } } catch (Exception ex) { //Ignore. The scripts should not need this. It has been added for convenience } } #endregion var command = new NpgsqlCommand(sql, connection); command.Transaction = transaction; command.CommandTimeout = 0; try { if (!setup.CheckOnly) { var debugText = "[" + DateTime.Now.ToString() + "]\r\n"; const int MAX_SQL = 500; var sqlLength = Math.Min(sql.Length, MAX_SQL); debugText += sql.Substring(0, sqlLength); if (sqlLength == MAX_SQL) { debugText += "..."; } debugText += "\r\n\r\n"; Log.Verbose(debugText); _timer.Restart(); DatabaseServer.ExecuteCommand(command); _timer.Stop(); Log.Debug <long, string>("Time:{Elapsed:000} Sql:{sql}", _timer.ElapsedMilliseconds, sql); if (successOrderScripts != null && isBody) { successOrderScripts.Add(key); } } } catch (NpgsqlException sqlexp) { if (failedScripts != null) { //Ignore this error, we will re-process it failedScripts.Add(new KeyValuePair <Guid, string>(key, originalSQL)); return; } else { throw new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName }; } } catch (Exception ex) { throw; } finally { if (command != null) { command.Dispose(); } } }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, null); }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup) { ExecuteSQL(connection, transaction, sql, setup, null); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts, nHydrateDbObjectList _databaseItems, InstallSetup setup) { var timer = Stopwatch.StartNew(); var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); Log.Verbose(TheDate + " Start File=" + Extensions.StripResourceAssem(resourceFileName)); #region Load script hashes var runScript = !setup.UseHash; var current = _databaseItems.FirstOrDefault(x => x.name.ToLower() == resourceFileName.ToLower()); var hashValue = string.Join("\r\n--GO\r\n", ReadSQLFileSectionsFromResource(resourceFileName, setup)).CalculateMD5Hash(); if (current != null) { //if (current.Hash != hashValue) { runScript = true; current.ModifiedDate = DateTime.Now; current.Hash = hashValue; current.Status = "applied"; } } else { runScript = true; current = new nHydrateDbObject() { name = resourceFileName, Hash = hashValue, ModelKey = new Guid(UpgradeInstaller.MODELKEY), type = "FILE", Status = "applied", Changed = true, }; _databaseItems.Add(current); } #endregion if (runScript && !setup.CheckOnly) { foreach (var sql in scripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, successOrderScripts); } } timer.Start(); Log.Verbose(TheDate + " End File=" + Extensions.StripResourceAssem(resourceFileName) + ", Elapsed=" + timer.FormattedTime()); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, List <KeyValuePair <Guid, string> > failedScripts, nHydrateDbObjectList _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, failedScripts, null, _databaseItems, setup); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, nHydrateDbObjectList _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, null, _databaseItems, setup); }
/// <summary> /// Performs an install of a database /// </summary> public override void Install(System.Collections.IDictionary stateSaver) { //base.Install(stateSaver); var commandParams = stateSaver as Dictionary <string, string>; foreach (var action in GetDatabaseActions() .Select(x => Activator.CreateInstance(x) as IDatabaseAction) .OrderBy(x => x.SortOrder) .ToList()) { action.Execute(commandParams); } var paramUICount = 0; var setup = new InstallSetup(); if (commandParams.Count > 0) { if (commandParams.Any(x => PARAMKEYS_TRAN.Contains(x.Key))) { setup.UseTransaction = GetSetting(commandParams, PARAMKEYS_TRAN, true); paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_SKIPNORMALIZE)) { setup.SkipNormalize = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_HASH)) { if (commandParams[PARAMKEYS_HASH].ToLower() == "true" || commandParams[PARAMKEYS_HASH].ToLower() == "1" || commandParams[PARAMKEYS_HASH].ToLower() == string.Empty) { setup.UseHash = true; } else if (commandParams[PARAMKEYS_HASH].ToLower() == "false" || commandParams[PARAMKEYS_HASH].ToLower() == "0") { setup.UseHash = false; } else { throw new Exception("The /" + PARAMKEYS_HASH + " parameter must be set to 'true or false'."); } paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_CHECKONLY)) { setup.CheckOnly = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_VERSIONWARN)) { if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "all") { setup.AcceptVersionWarningsChangedScripts = true; setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "none") { setup.AcceptVersionWarningsChangedScripts = false; setup.AcceptVersionWarningsNewScripts = false; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "new") { setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "changed") { setup.AcceptVersionWarningsChangedScripts = true; } else { throw new Exception("The /" + PARAMKEYS_VERSIONWARN + " parameter must be set to 'all, none, new, or changed'."); } paramUICount++; } if (GetSetting(commandParams, PARAMKEYS_HELP, false)) { ShowHelp(); return; } setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty); //Determine if calling as a script generator if (commandParams.ContainsKey(PARAMKEYS_SCRIPT)) { var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower(); switch (scriptAction) { case "versioned": break; case "unversioned": break; default: throw new Exception("The script action must be 'versioned' or 'unversioned'."); } if (!commandParams.ContainsKey(PARAMKEYS_SCRIPTFILE)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter must be set for script generation."); } var dumpFile = commandParams[PARAMKEYS_SCRIPTFILE]; if (!IsValidFileName(dumpFile)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter is not valid."); } var fileCreate = true; if (commandParams.ContainsKey(PARAMKEYS_SCRIPTFILEACTION) && (commandParams[PARAMKEYS_SCRIPTFILEACTION] + string.Empty) == "append") { fileCreate = false; } if (File.Exists(dumpFile) && fileCreate) { File.Delete(dumpFile); System.Threading.Thread.Sleep(500); } switch (scriptAction) { case "versioned": if (commandParams.ContainsKey(PARAMKEYS_DBVERSION)) { if (!GeneratedVersion.IsValid(commandParams[PARAMKEYS_DBVERSION])) { throw new Exception("The '" + PARAMKEYS_DBVERSION + "' parameter is not valid."); } setup.Version = new GeneratedVersion(commandParams[PARAMKEYS_DBVERSION]); } else { if (string.IsNullOrEmpty(setup.ConnectionString)) { throw new Exception("Generation of versioned scripts requires a '" + PARAMKEYS_DBVERSION + "' parameter or valid connection string."); } else { var s = new nHydrateSetting(); s.Load(setup.ConnectionString); setup.Version = new GeneratedVersion(s.dbVersion); } } File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); break; case "unversioned": setup.Version = UpgradeInstaller._def_Version; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); break; } return; } //If we processed all parameters and they were UI then we need to show UI if (paramUICount < commandParams.Count) { Install(setup); return; } } Log.Information("Invalid configuration"); }