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