public void LoadUI(InstallSetup setup)
{
chkIgnoreWarnings.Checked = setup.AcceptVersionWarningsChangedScripts && setup.AcceptVersionWarningsNewScripts;
chkSkipNormalize.Checked = setup.SkipNormalize;
chkUseTransaction.Checked = setup.UseTransaction;
chkUseHash.Checked = setup.UseHash;
}
public void LoadUI(InstallSetup setup)
{
chkIgnoreWarnings.Checked = setup.AcceptVersionWarningsChangedScripts && setup.AcceptVersionWarningsNewScripts;
chkSkipNormalize.Checked = setup.SkipNormalize;
chkUseTransaction.Checked = setup.UseTransaction;
chkUseHash.Checked = setup.UseHash;
}
/// <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;
}
}
private bool CreateDatabase()
{
var error = false;
var connectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, string.Empty, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text);
if (SqlServers.TestConnectionString(connectString) && SqlServers.HasCreatePermissions(connectString))
{
try
{
var setup = new InstallSetup()
{
MasterConnectionString = connectString,
NewDatabaseName = txtCreationDatabaseName.Text,
};
SqlServers.CreateDatabase(setup);
}
catch (Exception ex)
{
error = true;
System.Diagnostics.Debug.WriteLine(ex.ToString());
MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message);
}
}
else
{
error = true;
MessageBox.Show("The account does not have permissions to create a database on this server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(error);
}
public void SaveUI(InstallSetup setup)
{
setup.AcceptVersionWarningsChangedScripts = chkIgnoreWarnings.Checked;
setup.AcceptVersionWarningsNewScripts = chkIgnoreWarnings.Checked;
setup.SkipNormalize = chkSkipNormalize.Checked;
setup.UseTransaction = chkUseTransaction.Checked;
setup.UseHash = chkUseHash.Checked;
}
public void SaveUI(InstallSetup setup)
{
setup.AcceptVersionWarningsChangedScripts = chkIgnoreWarnings.Checked;
setup.AcceptVersionWarningsNewScripts = chkIgnoreWarnings.Checked;
setup.SkipNormalize = chkSkipNormalize.Checked;
setup.UseTransaction = chkUseTransaction.Checked;
setup.UseHash = chkUseHash.Checked;
}
/// <summary>
/// Performs an install of a database
/// </summary>
public void Install(InstallSetup setup)
{
if (setup.InstallStatus == InstallStatusConstants.Create)
{
//Conection cannot reference an existing database
if (SqlServers.TestConnectionString(setup.ConnectionString))
{
throw new Exception("The connection string references an existing database.");
}
//The new database name must be specified
if (string.IsNullOrEmpty(setup.NewDatabaseName))
{
throw new Exception("A new database name was not specified.");
}
//The connection string and the new database name must be the same
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(setup.ConnectionString);
if (builder.InitialCatalog.ToLower() != setup.NewDatabaseName.ToLower())
{
throw new Exception("A new database name does not match the specified connection string.");
}
SqlServers.CreateDatabase(setup);
}
else if (setup.InstallStatus == InstallStatusConstants.Upgrade)
{
//The connection string must reference an existing database
if (!SqlServers.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();
Console.WriteLine(sb.ToString());
UpgradeInstaller.LogError(ex, sb.ToString());
throw;
}
catch (Exception ex)
{
throw;
}
}
/// <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));
}
private bool IdentifyDatabaseConnectionString(InstallSetup setup)
{
var F = new IdentifyDatabaseForm(setup);
if (F.ShowDialog() == DialogResult.OK)
{
this.Action = F.Action;
this.Settings = F.Settings;
return(true);
}
return(false);
}
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);
}
public IdentifyDatabaseForm(InstallSetup setup)
: this()
{
this.Settings = new InstallSettings();
_setup = setup;
this.InstallSettingsUI1.LoadUI(setup);
cmdCancel.Click += new EventHandler(cmdCancel_Click);
cmdOK.Click += new EventHandler(cmdOK_Click);
cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown;
cboConnectionServerName.DropDown += ServerName_DropDown;
cboCreationServerName.DropDown += ServerName_DropDown;
buttonCreationRefresh.Click += buttonCreationRefresh_Click;
optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged;
radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged;
optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged;
cmdViewHistory.Click += cmdViewHistory_Click;
buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click;
optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged;
buttonConnectionRefresh.Click += buttonConnectionRefresh_Click;
//Turn off features for Azure
//if (SqlServers.DatabaseVersion != SqlServers.SQLServerTypeConstants.SQLAzure)
// tabControlChooseDatabase.TabPages.Remove(tabPageAzureCopy);
this.Settings.Load();
if (this.Settings.IsLoaded)
{
chkSaveSettings.Checked = true;
//Tab Upgrade
cboConnectionServerName.Text = this.Settings.PrimaryServer;
optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked;
txtConnectionUserName.Text = this.Settings.PrimaryUserName;
txtConnectionPassword.Text = this.Settings.PrimaryPassword;
cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase;
//Tab Create
cboCreationServerName.Text = this.Settings.PrimaryServer;
txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase;
optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked;
txtCreationUserName.Text = this.Settings.PrimaryUserName;
txtCreationPassword.Text = this.Settings.PrimaryPassword;
//Tab Azure Copy
azureCopyControl1.LoadSettings(this.Settings);
}
this.UpdateLogin();
}
public IdentifyDatabaseForm(InstallSetup setup)
: this()
{
this.Settings = new InstallSettings();
_setup = setup;
this.InstallSettingsUI1.LoadUI(setup);
cmdCancel.Click += new EventHandler(cmdCancel_Click);
cmdOK.Click += new EventHandler(cmdOK_Click);
cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown;
cboConnectionServerName.DropDown += ServerName_DropDown;
cboCreationServerName.DropDown += ServerName_DropDown;
buttonCreationRefresh.Click += buttonCreationRefresh_Click;
optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged;
radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged;
optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged;
cmdViewHistory.Click += cmdViewHistory_Click;
buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click;
optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged;
buttonConnectionRefresh.Click += buttonConnectionRefresh_Click;
//Turn off features for Azure
//if (SqlServers.DatabaseVersion != SqlServers.SQLServerTypeConstants.SQLAzure)
// tabControlChooseDatabase.TabPages.Remove(tabPageAzureCopy);
this.Settings.Load();
if (this.Settings.IsLoaded)
{
chkSaveSettings.Checked = true;
//Tab Upgrade
cboConnectionServerName.Text = this.Settings.PrimaryServer;
optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked;
txtConnectionUserName.Text = this.Settings.PrimaryUserName;
txtConnectionPassword.Text = this.Settings.PrimaryPassword;
cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase;
//Tab Create
cboCreationServerName.Text = this.Settings.PrimaryServer;
txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase;
optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked;
txtCreationUserName.Text = this.Settings.PrimaryUserName;
txtCreationPassword.Text = this.Settings.PrimaryPassword;
txtDiskPath.Text = this.Settings.DiskPath;
//Tab Azure Copy
azureCopyControl1.LoadSettings(this.Settings);
}
this.UpdateLogin();
}
public IdentifyDatabaseForm(InstallSetup setup)
: this()
{
this.Settings = new InstallSettings();
_setup = setup;
this.InstallSettingsUI1.LoadUI(setup);
cmdCancel.Click += new EventHandler(cmdCancel_Click);
cmdOK.Click += new EventHandler(cmdOK_Click);
cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown;
cboConnectionServerName.DropDown += ServerName_DropDown;
cboCreationServerName.DropDown += ServerName_DropDown;
buttonCreationRefresh.Click += buttonCreationRefresh_Click;
optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged;
radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged;
optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged;
cmdViewHistory.Click += cmdViewHistory_Click;
buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click;
optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged;
buttonConnectionRefresh.Click += buttonConnectionRefresh_Click;
this.Settings.Load();
if (this.Settings.IsLoaded)
{
chkSaveSettings.Checked = true;
//Tab Upgrade
cboConnectionServerName.Text = this.Settings.PrimaryServer;
optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked;
txtConnectionUserName.Text = this.Settings.PrimaryUserName;
txtConnectionPassword.Text = this.Settings.PrimarySecurityPhrase;
cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase;
//Tab Create
cboCreationServerName.Text = this.Settings.PrimaryServer;
txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase;
optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity;
radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked;
txtCreationUserName.Text = this.Settings.PrimaryUserName;
txtCreationPassword.Text = this.Settings.PrimarySecurityPhrase;
txtDiskPath.Text = this.Settings.DiskPath;
}
this.UpdateLogin();
}
/// <summary />
private void UIInstall(InstallSetup setup)
{
if (IdentifyDatabaseConnectionString(setup))
{
setup.ConnectionString = this.Settings.GetPrimaryConnectionString();
setup.InstallStatus = InstallStatusConstants.Upgrade;
if (this.Action == ActionTypeConstants.Create)
{
setup.InstallStatus = InstallStatusConstants.Create;
UpgradeInstaller.UpgradeDatabase(setup);
}
else if (this.Action == ActionTypeConstants.Upgrade)
{
UpgradeInstaller.UpgradeDatabase(setup);
}
}
}
internal static void CreateDatabase(InstallSetup setup)
{
try
{
using (var conn = new NpgsqlConnection(setup.MasterConnectionString))
{
conn.Open();
var cmdCreateDb = new NpgsqlCommand();
var fileInfo = string.Empty;
if (!string.IsNullOrEmpty(setup.DiskPath))
{
fileInfo = " WITH LOCATION '" + Path.Combine(setup.DiskPath, setup.NewDatabaseName) + "')";
}
cmdCreateDb.CommandText = $"CREATE DATABASE \"{setup.NewDatabaseName}\"" + fileInfo;
cmdCreateDb.CommandType = System.Data.CommandType.Text;
cmdCreateDb.Connection = conn;
DatabaseServer.ExecuteCommand(cmdCreateDb);
}
using (var conn = new NpgsqlConnection(setup.ConnectionString))
{
conn.Open();
//Add UUID generator
var command = new NpgsqlCommand();
command.CommandText = "CREATE EXTENSION \"uuid-ossp\";";
command.CommandType = System.Data.CommandType.Text;
command.Connection = conn;
DatabaseServer.ExecuteCommand(command);
}
}
catch { throw; }
finally
{
System.Threading.Thread.Sleep(1000);
}
}
public void Run(InstallSettings settings)
{
//STEPS TO COPY DATABASE TO AZURE
//1. Verify that target is a blank database
//2. Execute only tables schemas with no defaults, relations, etc
//3. Copy data with BCP one table at a time
//4. Run full installer on the target database
//1. Verify that target is a blank database
if (!this.TargetIsBlank(settings))
{
throw new Exception("The target database must be empty!");
}
//2. Execute only tables schemas and PK with no defaults, relations, etc
Assembly assem = Assembly.GetExecutingAssembly();
string[] resourceNames = assem.GetManifestResourceNames();
var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql"));
if (string.IsNullOrEmpty(resourceName))
{
throw new Exception("Could not find the 'CreateSchema.sql' resource!");
}
var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, new InstallSetup());
SqlConnection connection = null;
try
{
connection = new SqlConnection(settings.GetCloudConnectionString());
connection.Open();
////Create version table
//var sb = new StringBuilder();
//sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')");
//sb.AppendLine("BEGIN");
//sb.AppendLine("CREATE TABLE [__nhydrateschema] (");
//sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,");
//sb.AppendLine("[LastUpdate] [datetime] NOT NULL,");
//sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,");
//sb.AppendLine("[History] [text] NOT NULL");
//sb.AppendLine(")");
//sb.AppendLine("--PRIMARY KEY FOR TABLE");
//sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')");
//sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])");
//sb.AppendLine("END");
//var command2 = new SqlCommand(sb.ToString(), connection);
//command2.ExecuteNonQuery();
foreach (string sql in scripts)
{
if (
sql.Contains("--CREATE TABLE") ||
sql.Contains("--CREATE AUDIT TABLE") ||
sql.StartsWith("--APPEND AUDIT") ||
sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
var command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
//3. Copy data with BCP one table at a time
this.CopyData(settings);
//4. Run full installer on the target database
var setup = new InstallSetup()
{
ConnectionString = settings.GetCloudConnectionString(),
InstallStatus = InstallStatusConstants.Upgrade,
};
UpgradeInstaller.UpgradeDatabase(setup);
}
/// <summary>
/// Performs an install of a database
/// </summary>
public void Install(InstallSetup setup)
{
if (setup.InstallStatus == InstallStatusConstants.Create)
{
//Conection cannot reference an existing database
if (SqlServers.TestConnectionString(setup.ConnectionString))
throw new Exception("The connection string references an existing database.");
//The new database name must be specified
if (string.IsNullOrEmpty(setup.NewDatabaseName))
throw new Exception("A new database name was not specified.");
//The connection string and the new database name must be the same
var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(setup.ConnectionString);
if (builder.InitialCatalog.ToLower() != setup.NewDatabaseName.ToLower())
throw new Exception("A new database name does not match the specified connection string.");
SqlServers.CreateDatabase(setup);
}
else if (setup.InstallStatus == InstallStatusConstants.Upgrade)
{
//The connection string must reference an existing database
if (!SqlServers.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();
Console.WriteLine(sb.ToString());
UpgradeInstaller.LogError(ex, sb.ToString());
throw;
}
catch (Exception ex)
{
throw;
}
}
/// <summary>
/// Performs an install of a database
/// </summary>
public override void Install(System.Collections.IDictionary stateSaver)
{
base.Install(stateSaver);
var commandParams = GetCommandLineParameters();
var paramUICount = 0;
var setup = new InstallSetup();
if (commandParams.Count > 0)
{
if (commandParams.ContainsKey(PARAMKEYS_SHOWSQL))
{
if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "true" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "1" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == string.Empty)
{
setup.ShowSql = true;
}
else if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "false" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "0")
{
setup.ShowSql = false;
}
else
{
throw new Exception("The /" + PARAMKEYS_SHOWSQL + " parameter must be set to 'true or false'.");
}
paramUICount++;
}
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_QUIET))
{
setup.SuppressUI = 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;
}
//Try to drop database
if (commandParams.Any(x => PARAMKEYS_DROP.Contains(x.Key)))
{
var masterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty);
var dbname = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault();
if (commandParams.Count == 3 && !string.IsNullOrEmpty(masterConnectionString))
{
if (!DropDatabase(dbname, masterConnectionString))
{
throw new Exception("The database '" + dbname + "' could not dropped.");
}
Console.WriteLine("Database successfully dropped.");
return;
}
throw new Exception("Invalid drop database configuration.");
}
setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty);
setup.MasterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty);
if (GetSetting(commandParams, PARAMKEYS_UPGRADE, setup.IsUpgrade))
{
setup.InstallStatus = InstallStatusConstants.Upgrade;
}
if (commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key)))
{
setup.InstallStatus = InstallStatusConstants.Create;
}
if (commandParams.Any(x => PARAMKEYS_UPGRADE.Contains(x.Key)) && commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key)))
{
throw new Exception("You cannot specify both the create and update action.");
}
if (commandParams.Count(x => PARAMKEYS_NEWNAME.Contains(x.Key)) > 1)
{
throw new Exception("The new database name was specified more than once.");
}
if (commandParams.Count(x => PARAMKEYS_MASTERDB.Contains(x.Key)) > 1)
{
throw new Exception("The master database connection string was specified more than once.");
}
if (commandParams.Count(x => PARAMKEYS_APPDB.Contains(x.Key)) > 1)
{
throw new Exception("The connection string was specified more than once.");
}
//Determine if calling as a script generator
if (commandParams.ContainsKey(PARAMKEYS_SCRIPT))
{
var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower();
switch (scriptAction)
{
case "versioned":
case "unversioned":
case "create":
break;
default:
throw new Exception("The script action must be 'create', '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);
}
}
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Upgrade;
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
case "unversioned":
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Upgrade;
setup.Version = UpgradeInstaller._def_Version;
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
case "create":
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Create;
setup.Version = new GeneratedVersion(-1, -1, -1, -1, -1);
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
}
return;
}
//If we processed all parameters and they were UI then we need to show UI
if ((paramUICount < commandParams.Count) || setup.SuppressUI)
{
setup.NewDatabaseName = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault();
Install(setup);
return;
}
}
UIInstall(setup);
}
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup, List<KeyValuePair<Guid, string>> failedScripts, List<Guid> successOrderScripts)
{
if (sql.StartsWith("--##METHODCALL"))
{
CallMethod(sql, connection, transaction, setup);
return;
}
var originalSQL = sql.Trim();
sql = originalSQL;
if (string.IsNullOrEmpty(sql)) return;
#region Get Script Key
var isBody = false;
var lines = sql.Split(new char[] { '\n' }, StringSplitOptions.None).ToList();
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 System.Data.SqlClient.SqlCommand(dropSQL, connection);
dropCommand.Transaction = transaction;
dropCommand.CommandTimeout = 300;
SqlServers.ExecuteCommand(dropCommand);
}
}
catch (Exception ex)
{
//Ignore. The scripts should not need this. It has been added for convenience
}
}
#endregion
var command = new System.Data.SqlClient.SqlCommand(sql, connection);
command.Transaction = transaction;
command.CommandTimeout = Math.Max(300, connection.ConnectionTimeout);
try
{
if (!setup.CheckOnly)
{
if (setup.ShowSql && !string.IsNullOrEmpty(sql))
{
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";
Console.WriteLine(debugText);
}
_timer.Restart();
SqlServers.ExecuteCommand(command);
_timer.Stop();
//System.Diagnostics.Debug.WriteLine("Elapsed: " + _timer.ElapsedMilliseconds + " / " + sql.Split('\n').First()); //Alert user of what is running
if (successOrderScripts != null && isBody)
successOrderScripts.Add(key);
}
}
catch (System.Data.SqlClient.SqlException sqlexp)
{
if ((sqlexp.Number == 1779) && sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
//Ignore this error
return;
}
else if ((sqlexp.Number == 1781) && sql.StartsWith("--DEFAULTS FOR TABLE"))
{
//Ignore this error
return;
}
else if (failedScripts != null)
{
//Ignore this error, we will re-process it
failedScripts.Add(new KeyValuePair<Guid, string>(key, originalSQL));
return;
}
else
{
if (setup.SuppressUI)
throw new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName };
else if (!SkipScriptPrompt(new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName }))
throw new HandledSQLException(sqlexp.Message, sqlexp);
}
}
catch (Exception ex) { throw; }
}
/// <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 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);
}
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup, List<KeyValuePair<Guid, string>> failedScripts)
{
ExecuteSQL(connection, transaction, sql, setup, failedScripts, null);
}
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<KeyValuePair<Guid, string>> failedScripts, List<nHydrateDbObject> _databaseItems, InstallSetup setup)
{
RunEmbeddedFile(connection, transaction, resourceFileName, failedScripts, null, _databaseItems, setup);
}
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<KeyValuePair<Guid, string>> failedScripts, List<Guid> successOrderScripts, List<nHydrateDbObject> _databaseItems, InstallSetup setup)
{
var tempFolder = string.Empty;
var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup);
System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("HH:mm:ss.ff") + " - Run embedded file: " + resourceFileName);
#region Load script hashes
var runScript = !setup.UseHash;
var current = _databaseItems.FirstOrDefault(x => x.name.ToLower() == resourceFileName.ToLower());
var hashValue = string.Join("\r\nGO\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);
}
}
}
/// <summary />
private void UIInstall(InstallSetup setup)
{
if (IdentifyDatabaseConnectionString(setup))
{
setup.ConnectionString = this.Settings.GetPrimaryConnectionString();
setup.InstallStatus = InstallStatusConstants.Upgrade;
if (this.Action == ActionTypeConstants.Create)
{
setup.InstallStatus = InstallStatusConstants.Create;
UpgradeInstaller.UpgradeDatabase(setup);
}
else if (this.Action == ActionTypeConstants.Upgrade)
{
UpgradeInstaller.UpgradeDatabase(setup);
}
else if (this.Action == ActionTypeConstants.AzureCopy)
{
UpgradeInstaller.AzureCopyDatabase(this.Settings);
}
}
}
private bool IdentifyDatabaseConnectionString(InstallSetup setup)
{
var F = new IdentifyDatabaseForm(setup);
if (F.ShowDialog() == DialogResult.OK)
{
this.Action = F.Action;
this.Settings = F.Settings;
return true;
}
return false;
}
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)));
}
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)
{
ExecuteSQL(connection, transaction, sql, setup, null);
}
public void Run(InstallSettings settings)
{
//STEPS TO COPY DATABASE TO AZURE
//1. Verify that target is a blank database
//2. Execute only tables schemas with no defaults, relations, etc
//3. Copy data with BCP one table at a time
//4. Run full installer on the target database
//1. Verify that target is a blank database
if (!this.TargetIsBlank(settings))
throw new Exception("The target database must be empty!");
//2. Execute only tables schemas and PK with no defaults, relations, etc
Assembly assem = Assembly.GetExecutingAssembly();
string[] resourceNames = assem.GetManifestResourceNames();
var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql"));
if (string.IsNullOrEmpty(resourceName)) throw new Exception("Could not find the 'CreateSchema.sql' resource!");
var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, new InstallSetup());
SqlConnection connection = null;
try
{
connection = new SqlConnection(settings.GetCloudConnectionString());
connection.Open();
////Create version table
//var sb = new StringBuilder();
//sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')");
//sb.AppendLine("BEGIN");
//sb.AppendLine("CREATE TABLE [__nhydrateschema] (");
//sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,");
//sb.AppendLine("[LastUpdate] [datetime] NOT NULL,");
//sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,");
//sb.AppendLine("[History] [text] NOT NULL");
//sb.AppendLine(")");
//sb.AppendLine("--PRIMARY KEY FOR TABLE");
//sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')");
//sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])");
//sb.AppendLine("END");
//var command2 = new SqlCommand(sb.ToString(), connection);
//command2.ExecuteNonQuery();
foreach (string sql in scripts)
{
if (
sql.Contains("--CREATE TABLE") ||
sql.Contains("--CREATE AUDIT TABLE") ||
sql.StartsWith("--APPEND AUDIT") ||
sql.StartsWith("--PRIMARY KEY FOR TABLE"))
{
var command = new SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
if (connection != null)
connection.Close();
}
//3. Copy data with BCP one table at a time
this.CopyData(settings);
//4. Run full installer on the target database
var setup = new InstallSetup()
{
ConnectionString = settings.GetCloudConnectionString(),
InstallStatus = InstallStatusConstants.Upgrade,
};
UpgradeInstaller.UpgradeDatabase(setup);
}
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup)
{
ExecuteSQL(connection, transaction, sql, setup, null);
}
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 = GetCommandLineParameters();
var paramUICount = 0;
var setup = new InstallSetup();
if (commandParams.Count > 0)
{
if (commandParams.ContainsKey(PARAMKEYS_SHOWSQL))
{
if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "true" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "1" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == string.Empty)
setup.ShowSql = true;
else if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "false" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "0")
setup.ShowSql = false;
else
throw new Exception("The /" + PARAMKEYS_SHOWSQL + " parameter must be set to 'true or false'.");
paramUICount++;
}
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_QUIET))
{
setup.SuppressUI = 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;
}
//Try to drop database
if (commandParams.Any(x => PARAMKEYS_DROP.Contains(x.Key)))
{
var masterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty);
var dbname = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault();
if (commandParams.Count == 3 && !string.IsNullOrEmpty(masterConnectionString))
{
if (!DropDatabase(dbname, masterConnectionString))
throw new Exception("The database '" + dbname + "' could not dropped.");
Console.WriteLine("Database successfully dropped.");
return;
}
throw new Exception("Invalid drop database configuration.");
}
setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty);
setup.MasterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty);
if (GetSetting(commandParams, PARAMKEYS_UPGRADE, setup.IsUpgrade))
setup.InstallStatus = InstallStatusConstants.Upgrade;
if (commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key)))
setup.InstallStatus = InstallStatusConstants.Create;
if (commandParams.Any(x => PARAMKEYS_UPGRADE.Contains(x.Key)) && commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key)))
throw new Exception("You cannot specify both the create and update action.");
if (commandParams.Count(x => PARAMKEYS_NEWNAME.Contains(x.Key)) > 1)
throw new Exception("The new database name was specified more than once.");
if (commandParams.Count(x => PARAMKEYS_MASTERDB.Contains(x.Key)) > 1)
throw new Exception("The master database connection string was specified more than once.");
if (commandParams.Count(x => PARAMKEYS_APPDB.Contains(x.Key)) > 1)
throw new Exception("The connection string was specified more than once.");
//Determine if calling as a script generator
if (commandParams.ContainsKey(PARAMKEYS_SCRIPT))
{
var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower();
switch (scriptAction)
{
case "versioned":
case "unversioned":
case "create":
break;
default:
throw new Exception("The script action must be 'create', '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);
}
}
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Upgrade;
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
case "unversioned":
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Upgrade;
setup.Version = UpgradeInstaller._def_Version;
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
case "create":
Console.WriteLine("Generate Script Started");
setup.InstallStatus = InstallStatusConstants.Create;
setup.Version = new GeneratedVersion(-1, -1, -1, -1, -1);
File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup));
Console.WriteLine("Generated Create Script");
break;
}
return;
}
//If we processed all parameters and they were UI then we need to show UI
if ((paramUICount < commandParams.Count) || setup.SuppressUI)
{
setup.NewDatabaseName = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault();
Install(setup);
return;
}
}
UIInstall(setup);
}
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());
}
private static void CallMethod(string text, SqlConnection connection, SqlTransaction transaction, InstallSetup setup)
{
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");
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.");
}
}
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)
{
ExecuteSQL(connection, transaction, sql, setup, failedScripts, null);
}
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(".sql"))
{
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\nGO\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 CreateDatabase(InstallSetup setup)
{
var conn = new System.Data.SqlClient.SqlConnection();
try
{
conn.ConnectionString = setup.MasterConnectionString;
conn.Open();
var cmdCreateDb = new SqlCommand();
var collate = string.Empty;
if (!string.IsNullOrEmpty(collate)) collate = " COLLATE " + collate;
cmdCreateDb.CommandText = "CREATE DATABASE [" + setup.NewDatabaseName + "]" + collate;
cmdCreateDb.CommandType = System.Data.CommandType.Text;
cmdCreateDb.Connection = conn;
SqlServers.ExecuteCommand(cmdCreateDb);
var sb = new StringBuilder();
sb.AppendLine("declare @databasename nvarchar(500)");
sb.AppendLine("set @databasename = (select [name] from master.sys.master_files where database_id = (select top 1 [dbid] from master.sys.sysdatabases where name = '" + setup.NewDatabaseName + "' and [type] = 0))");
sb.AppendLine("exec('ALTER DATABASE [" + setup.NewDatabaseName + "] MODIFY FILE (NAME = [' + @databasename + '], MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)')");
sb.AppendLine("set @databasename = (select [name] from master.sys.master_files where database_id = (select top 1 [dbid] from master.sys.sysdatabases where name = '" + setup.NewDatabaseName + "' and [type] = 1))");
sb.AppendLine("exec('ALTER DATABASE [" + setup.NewDatabaseName + "] MODIFY FILE (NAME = [' + @databasename + '], MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)')");
cmdCreateDb.CommandText = sb.ToString();
SqlServers.ExecuteCommand(cmdCreateDb);
}
catch { throw; }
finally
{
if (conn != null)
conn.Close();
System.Threading.Thread.Sleep(1000);
}
}
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;
}
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<nHydrateDbObject> _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");
}
private bool CreateDatabase()
{
var error = false;
var connectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, string.Empty, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text);
if (SqlServers.TestConnectionString(connectString) && SqlServers.HasCreatePermissions(connectString))
{
try
{
var setup = new InstallSetup()
{
MasterConnectionString = connectString,
NewDatabaseName = txtCreationDatabaseName.Text,
};
SqlServers.CreateDatabase(setup);
}
catch (Exception ex)
{
error = true;
System.Diagnostics.Debug.WriteLine(ex.ToString());
MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message);
}
}
else
{
error = true;
MessageBox.Show("The account does not have permissions to create a database on this server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return error;
}
