private static Version SelectSchemaVersion(SqlTextAdapter adapter)
{
StringBuilder stringVersion = null;
Version version = new Version(0, 0, 0);
try
{
// Select the current schema version from the Extended Property of the database
string sqlText = "SELECT value FROM fn_listextendedproperty(N'STF Revision', NULL, NULL, NULL, NULL, NULL, NULL)";
var reader = adapter.ExecuteReader(sqlText);
if (reader != null && reader.Read())
{
stringVersion = new StringBuilder((string)reader["value"]);
Match match = Regex.Match(stringVersion.ToString(), @"([0-9]+)\.([0-9]+)\.*([0-9]*)", RegexOptions.IgnoreCase);
stringVersion.Clear();
stringVersion.Append(match.Groups[1].Value);
stringVersion.Append(".");
stringVersion.Append(match.Groups[2].Length == 0 ? "0" : match.Groups[2].Value);
stringVersion.Append(".");
stringVersion.Append(match.Groups[3].Length == 0 ? "0" : match.Groups[3].Value);
version = new Version(stringVersion.ToString());
}
}
catch (Exception ex)
{
SystemTrace.Instance.Error(ex.ToString());
}
return(version);
}
/// <summary>
/// Gets the database schema version.
/// </summary>
/// <param name="hostname"></param>
/// <returns></returns>
public static Version GetSchemaVersion(string hostname)
{
using (SqlTextAdapter adapter = new SqlTextAdapter(hostname, "Master"))
{
return(SelectSchemaVersion(adapter));
}
}
private bool ExecuteSql(string sqlText, SqlTextAdapter adapter, bool useTransaction = true)
{
if (CancelPending())
{
return(false);
}
string[] sqlCommands = sqlText.Split(_delimiter, StringSplitOptions.RemoveEmptyEntries);
int count = 1;
int length = sqlCommands.Length;
string statement = string.Empty;
SendProgressStart(sqlCommands.Length);
foreach (string command in sqlCommands)
{
try
{
SendProgressUpdate(count++);
statement = command.Trim();
if (!string.IsNullOrEmpty(statement))
{
if (useTransaction)
{
adapter.BeginTransaction();
adapter.ExecuteSql(statement);
adapter.CommitTransaction();
}
else
{
adapter.ExecuteSql(statement);
}
}
if (CancelPending())
{
return(false);
}
}
catch (Exception ex)
{
if (useTransaction)
{
adapter.RollbackTransaction();
}
SendError(ex);
SystemTrace.Instance.Error(statement);
System.Diagnostics.Debug.WriteLine(ex.ToString());
System.Diagnostics.Debug.WriteLine(statement);
throw;
}
}
Thread.Sleep(1000);
SendProgressEnd();
return(true);
}
private void RollBackSTBInstall()
{
using (SqlTextAdapter adapter = new SqlTextAdapter(Environment.MachineName, "Master"))
{
DropDatabaseInstances(GetSTBDatabaseNames(), adapter);
}
CleanUpFileShare();
}
/// <summary>
/// Runs the specified update script against the database.
/// Note: Assumes transactions are being handled outside of this method.
/// </summary>
/// <param name="adapter">The SQL adapter</param>
/// <param name="script">The databse update script</param>
/// <returns>True if the script was executed successfully. False otherwise.</returns>
private bool RunDatabaseUpdate(string databaseHost, SqlUpdateScript script)
{
// Execute the SQL update script against the defined database host
using (SqlTextAdapter adapter = new SqlTextAdapter(databaseHost, "Master"))
{
UpdateStatus("Updating {0} to version {1}".FormatWith(script.Database, script.Version));
if (!ExecuteSql(script.SqlText, adapter, true))
{
return(false);
}
}
return(true);
}
private bool UpdateVersion(string databaseHost, Version version)
{
// Update the STB/STF version on the defined database host.
string updateVersionSql = new EmbeddedResource("HP.SolutionTest.DatabaseCreation").Read("ins_SchemaVersion.sql");
using (var adapter = new SqlTextAdapter(databaseHost, "Master"))
{
adapter.BeginTransaction();
if (!ExecuteSql(updateVersionSql.FormatWith(version.ToString()), adapter, false))
{
adapter.RollbackTransaction();
return(false);
}
adapter.CommitTransaction();
_ticket.CurrentVersion = version;
}
return(true);
}
/// <summary>
/// Drop all database instances that were created during this install.
/// </summary>
/// <param name="creationSQL">The SQL used to create the databases.</param>
/// <param name="adapter">The SQL Text Adapter.</param>
private void DropDatabaseInstances(IEnumerable <string> databaseNames, SqlTextAdapter adapter)
{
try
{
foreach (string databaseName in databaseNames)
{
UpdateStatus(string.Format("Killing {0} database session.", databaseName));
//UpdateStatus(statement);
adapter.ExecuteNonQuery(Resources.UseMaster);
adapter.ExecuteSql(Resources.KillDatabaseProcessSql.FormatWith(databaseName));
UpdateStatus(string.Format("Dropping {0}.", databaseName));
adapter.ExecuteSql(Resources.UseMaster);
adapter.ExecuteSql(Resources.CloseConnectionSql.FormatWith(databaseName));
adapter.ExecuteSql(Resources.DropDatabaseSql.FormatWith(databaseName));
}
}
catch (Exception ex)
{
UpdateStatus(ex.Message);
Thread.Sleep(5000);
SendError(ex);
}
}
private bool CreateDatabases()
{
SystemTrace.Instance.Debug("Creating databases...");
string creationSQL = string.Empty; //Cache this in case we need to roll back.
StringBuilder sqlText = new StringBuilder();
// Create tables, insert data, etc. for each database
using (SqlTextAdapter adapter = new SqlTextAdapter(Environment.MachineName, "Master"))
{
try
{
UpdateStatus("Creating Solution Test Bench database instances...");
EmbeddedResource stream = new EmbeddedResource("HP.SolutionTest.DatabaseCreation");
creationSQL = stream.Read("create_DatabaseInstances.sql");
sqlText.Append(creationSQL);
sqlText.Replace("{DATABASE_PATH}", _ticket.DatabaseFilesPath);
if (!ExecuteSql(sqlText.ToString(), adapter, false))
{
return(false);
}
UpdateStatus("Creating Solution Test Bench database users...");
if (!ExecuteSql(stream.Read("create_DatabaseUsers.sql"), adapter, true))
{
return(false);
}
UpdateStatus("Creating Solution Test Bench primary configuration database schema...");
if (!ExecuteSql(stream.Read("create_SchemaEnterpriseTest.sql"), adapter, true))
{
return(false);
}
UpdateStatus("Creating Solution Test Bench asset inventory database schema...");
if (!ExecuteSql(stream.Read("create_SchemaAssetInventory.sql"), adapter, true))
{
return(false);
}
UpdateStatus("Creating Solution Test Bench test document database schema...");
if (!ExecuteSql(stream.Read("create_SchemaTestDocumentLibrary.sql"), adapter, true))
{
return(false);
}
UpdateStatus("Creating Solution Test Bench data log database schema...");
if (!ExecuteSql(stream.Read("create_SchemaScalableTestDatalog.sql"), adapter, true))
{
return(false);
}
UpdateStatus("Populating Solution Test Bench databases with base configuration data...");
sqlText.Clear();
sqlText.Append(stream.Read("ins_BaseConfigurationData.sql"));
sqlText.Replace("{FILE_SHARE}", _ticket.FileShareName);
sqlText.Replace("{SERVER_ADDRESS}", _ticket.ServerHostname);
sqlText.Replace("{ADMIN_USER}", _ticket.AdminUserName);
sqlText.Replace("{ADMIN_DOMAIN}", _ticket.AdminDomain);
sqlText.Replace("{ORGANIZATION}", _ticket.OrganizationName);
sqlText.Replace("{ADMIN_EMAIL}", _ticket.AdminEmail);
sqlText.Replace("{DNS_DOMAIN}", _dnsDomainName);
sqlText.Replace("{AD_DOMAIN}", Environment.UserDomainName);
if (!ExecuteSql(sqlText.ToString(), adapter, true))
{
return(false);
}
Version version = new Version(stream.Read("SchemaVersion.txt"));
SystemTrace.Instance.Debug($"Schema version: {version}");
UpdateStatus("Updating Solution Test Bench database to initial version");
sqlText.Clear();
sqlText.Append(stream.Read("ins_SchemaVersion.sql"));
sqlText = sqlText.Replace("{0}", version.ToString());
if (!ExecuteSql(sqlText.ToString(), adapter, true))
{
return(false);
}
// Update the ticket to the current version
_ticket.CurrentVersion = version;
}
catch (Exception ex)
{
SendError(ex);
DropDatabaseInstances(GetSTBDatabaseNames(ref creationSQL), adapter);
return(false);
}
}
return(true);
}
