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