private bool IsDbPartitioned()
{
_logger.Log(ClassTitle, "Checking database is partitioned", Category.Info);
var query = TransactionConnectionManager.RunSqlNoConex(_server, "SELECT count(*) FROM information_schema.TABLES WHERE(TABLE_SCHEMA = 'c4pssdb') AND(TABLE_NAME = 'log_actions')", QueryType.Scalar);
return(((Int64)query == 1) ? true : false);
}
/// <summary>
/// this is the condition to upgrade db
/// this happens only when upgrading versions prior to V 2.1.0
/// </summary>
/// <param name="transMon"></param>
/// <returns></returns>
private bool DBVersionTblExists()
{
_logger.Log(ClassTitle, "Checking if dbversion table exists", Category.Info);
var query = TransactionConnectionManager.RunSqlNoConex(_server, "select count(*) from information_schema.tables where table_name = 'dbversion'", QueryType.Scalar);
return(((Int64)query == 0) ? false : true);
}
/// <summary>
/// Executes the ScriptContent on the sql server
/// </summary>
public virtual void Execute()
{
using (TransactionConnectionManager tcm = new TransactionConnectionManager())
{
tcm.ExecuteMySQLCmd(ScriptContent, 3600);
tcm.Commit();
}
}
private static void ValidateRegistryValues(TransactionConnectionManager transMon, DataRow dataRow)
{
var item = dataRow.ItemArray[1];
if (item.ToString() != RegistryHelper.RegistryHelper.GetRegistryValue("TRANS1_DOMAIN"))
{
RegistryHelper.RegistryHelper.SetRegistryKey("TRANS1_DOMAIN", item.ToString());
}
_logger.Log(ClassTitle, $"Failed to Update TS Value with exception: ", Category.Exception);
}
/// <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>
/// Drops Databse
/// </summary>
private void DropDB()
{
using (TransactionConnectionManager transMon = new TransactionConnectionManager())
{
try
{
_logger.Log(ClassTitle, "Dropping DB", Category.Info);
transMon.ExecuteMySqlScript("DROP SCHEMA IF EXISTS c4pssdb");
_logger.Log(ClassTitle, "c4pssdb schema dropped succesfully", Category.Info);
}
catch (Exception ex)
{
_logger.Log(ClassTitle, "failed dropping DB", Category.Exception);
throw new Exception("failed dropping DB " + ex.Message);
}
}
}
public override void Execute()
{
MySqlConnection conn = null;
MySqlCommand command = null;
try
{
conn = DBManager.GetNewConnection();
command = new MySqlCommand(ScriptContent, conn);
TransactionConnectionManager.ExecuteMySQLCmd(ScriptContent, 3600, command);
}
finally
{
command.Dispose();
conn.Dispose();
}
}
/// <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);
}
}
/// <summary>
/// imports data from backup dump to current schema
/// </summary>
public void ImportDump(string path)
{
try
{
_logger.Log(ClassTitle, "Importing Dump to c4pssdb", Category.Info);
using (TransactionConnectionManager transMon = new TransactionConnectionManager())
{
transMon.ExecuteMySqlScript("SET GLOBAL FOREIGN_KEY_CHECKS=0;");
RunCmdProcess(Path.GetDirectoryName(@"C:\Program Files\MySQL\MySQL Server " + _mySqlVersion + @"\bin\mysql.exe"), @"/c mysql --protocol=tcp --host=localhost --user=root --password=1234 --port=3306 --default-character-set=utf8 --comments --database=c4pssdb < " + "\"" + path + "\"");
transMon.ExecuteMySqlScript("SET GLOBAL FOREIGN_KEY_CHECKS=1;");
}
_logger.Log(ClassTitle, "Imported Dump to c4pssdb succesfully ", Category.Info);
}
catch (Exception ex)
{
_logger.Log(ClassTitle, "Failed Importing Dump c4pssdb: " + ex.Message, Category.Exception);
}
}
public bool CheckIfDBExists()
{
try
{
var query = TransactionConnectionManager.RunSqlNoConex(_server, "SHOW DATABASES LIKE '" + _db + "';", QueryType.Scalar).ToString();
if ((string)query == "c4pssdb")
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
_logger.Log(ClassTitle, "Failed Checking if database exists: " + ex.Message, Category.Info);
return(false);
}
}