/// <summary>
/// Executes the ScriptContent on the sql server
/// </summary>
public virtual void Execute()
{
using (TransactionConnectionManager tcm = new TransactionConnectionManager())
{
tcm.ExecuteMySQLCmd(ScriptContent, 3600);
tcm.Commit();
}
}
/// <summary>
/// writes current assembly version to dbversion table
/// </summary>
/// <param name="version"></param>
private void UpdateDBVersion(string version)
{
using (TransactionConnectionManager transMon = new TransactionConnectionManager())
{
_logger.Log(ClassTitle, "Updating DBVersion", Category.Info);
if ((transMon.ExecuteScalar($"SELECT COUNT(*) FROM c4pssdb.dbversion WHERE Version = '{version}'")) != 1)
{
transMon.ExecuteMySqlScript("INSERT INTO c4pssdb.dbversion (version) VALUES ('" + version + "');");
}
transMon.Commit();
_logger.Log(ClassTitle, "Updated DBVersion", Category.Info);
}
}
/// <summary>
/// Executes all script elements on the current database
/// </summary>
/// <param name="scripts"></param>
private void UpdateDatabase(IScriptElement[] scripts)
{
bool updatedDb = false;
_logger.Log(ClassTitle, "updating Database ", Category.Info);
//make sure scripts are executed by date / order number
var orderedScripts = scripts.OrderBy(se => se.Date.Date).ThenBy(se => se.OrderNumber);
try
{
using (TransactionConnectionManager transMon = new TransactionConnectionManager())
{
// VerifyUpgradeTablesExist(transMon);
UpdateTSinDb(transMon);
}
foreach (IScriptElement element in orderedScripts)
{
using (TransactionConnectionManager transMon = new TransactionConnectionManager())
{
// check whether this script has been run before using the filename as the key
if (transMon.ExecuteScalar("select count(*) from DatabaseScript where Filename = @Filename", new MySqlParameter("@Filename", element.Filename)) == 0)
{
_logger.Log(ClassTitle, "Executing " + element.Filename, Category.Info);
element.Execute();
transMon.ExecuteMySQLCmd("INSERT INTO DatabaseScript (Filename, Description, DateRun) VALUES (@Filename, @Description, @DateRun)", 0,
new MySqlParameter("@Filename", element.Filename),
new MySqlParameter("@Description", ""), new MySqlParameter("@DateRun", DateTime.UtcNow));
updatedDb = true;
}
transMon.Commit();
}
}
}
catch (Exception e)
{
_logger.Log(ClassTitle, "Exception while performing update script", Category.Exception);
throw new Exception("Failed to Update Existing Schema " + e.Message);
}
if (updatedDb)
{
_logger.Log(ClassTitle, "DB Upgraded succesfully", Category.Info);
}
}
/// <summary>
/// waits for partitioning script to end
/// </summary>
/// <param name="connection"></param>
private static void UpdateTSinDb(TransactionConnectionManager transMon)
{
var tsInfo = new List <string>();
try
{
DataRowCollection queryResults = transMon.GetAllResultMySqlCmd("SELECT id,domain FROM c4pssdb.transaction_servers where domain is not null;").Rows;
if (queryResults.Count < 1)
{
tsInfo = ReadTsInfoFromRegistry();
string updated = null;
int idx = 0;
//write ts info retrieved from registry to DB
foreach (string str in tsInfo)
{
transMon.ExecuteMySqlScript($"INSERT INTO `c4pssdb`.`transaction_servers` (`id`, `description`, `domain`, `uri`, `active`, `secure`) " +
$"VALUES ('{idx + 1}', 'TS #{idx + 1}','{tsInfo[idx]}', '/', '1','1')");
updated = updated + tsInfo[idx];
idx++;
_logger.Log(ClassTitle, $"TS Table Value updated successfully: " + str, Category.Info);
}
transMon.Commit();
}
else
{
ValidateRegistryValues(transMon, queryResults[0]);
}
}
catch (Exception ex)
{
_logger.Log(ClassTitle, $"Failed to Update TS Value with exception:\n {ex.Message}", Category.Exception);
}
}