///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first.</summary> public static long MakeABackup() { //This function should always make the backup on the server itself, and since no directories are //referred to (all handled with MySQL), this function will always be referred to the server from //client machines. if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetLong(MethodBase.GetCurrentMethod())); } //only used in two places: upgrading version, and upgrading mysql version. //Both places check first to make sure user is using mysql. //we have to be careful to throw an exception if the backup is failing. DataConnection dcon = new DataConnection(); string command = "SELECT database()"; DataTable table = dcon.GetTable(command); string oldDb = PIn.String(table.Rows[0][0].ToString()); string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy"); command = "SHOW DATABASES"; table = dcon.GetTable(command); string[] databases = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { databases[i] = table.Rows[i][0].ToString(); } if (Contains(databases, newDb)) //if the new database name already exists //find a unique one { int uniqueID = 1; string originalNewDb = newDb; do { newDb = originalNewDb + "_" + uniqueID.ToString(); uniqueID++; }while(Contains(databases, newDb)); } command = "CREATE DATABASE " + newDb + " CHARACTER SET utf8"; dcon.NonQ(command); command = "SHOW TABLES"; table = dcon.GetTable(command); string[] tableName = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { tableName[i] = table.Rows[i][0].ToString(); } //switch to using the new database DataConnection newDcon = new DataConnection(newDb); for (int i = 0; i < tableName.Length; i++) { command = "SHOW CREATE TABLE " + oldDb + "." + tableName[i]; table = newDcon.GetTable(command); command = PIn.ByteArray(table.Rows[0][1]); newDcon.NonQ(command); //this has to be run using connection with new database command = "INSERT INTO " + newDb + "." + tableName[i] + " SELECT * FROM " + oldDb + "." + tableName[i]; newDcon.NonQ(command); } return(0); }
///<summary></summary> public static void ClearDuplicates() { if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod()); return; } //First, create a temp table with operatories for all blockouts string command = "DROP TABLE IF EXISTS tempBlockoutOps"; Db.NonQ(command); command = @"CREATE TABLE tempBlockoutOps SELECT ScheduleNum, (SELECT " + DbHelper.GroupConcat("so1.OperatoryNum", false, true) + @" FROM scheduleop so1 WHERE so1.ScheduleNum=schedule.ScheduleNum) AS ops FROM schedule WHERE SchedType=2 GROUP BY ScheduleNum" ; Db.NonQ(command); command = "ALTER TABLE tempBlockoutOps ADD INDEX (ScheduleNum)"; Db.NonQ(command); //Get a list of scheduleNums that have duplicates //A duplicate is defined as a matching opsList and matching times //The result list will contain one random scheduleNum out of the many duplicates command = @"SELECT SchedDate,MAX(ScheduleNum),StartTime,StopTime," //MAX on id. Cannot GROUP BY id without splitting up duplicates. + @"(SELECT ops FROM tempBlockoutOps WHERE tempBlockoutOps.ScheduleNum=schedule.ScheduleNum) ops_______________ops, COUNT(*) countDups FROM schedule WHERE SchedType=2 GROUP BY SchedDate,ops_______________ops,StartTime,StopTime HAVING countDups > 1" ; DataTable table = Db.GetTable(command); DateTime schedDate; DateTime startTime; DateTime stopTime; string ops; long scheduleNum; for (int i = 0; i < table.Rows.Count; i++) { schedDate = PIn.Date(table.Rows[i][0].ToString()); scheduleNum = PIn.Long(table.Rows[i][1].ToString()); startTime = PIn.DateT(table.Rows[i][2].ToString()); stopTime = PIn.DateT(table.Rows[i][3].ToString()); ops = PIn.ByteArray(table.Rows[i][4]); command = "DELETE FROM schedule WHERE " + "SchedDate = " + POut.Date(schedDate) + " " + "AND ScheduleNum != " + POut.Long(scheduleNum) + " " + "AND StartTime = '" + startTime.ToString("hh:mm", new DateTimeFormatInfo()) + ":00' " + "AND StopTime = '" + stopTime.ToString("hh:mm", new DateTimeFormatInfo()) + ":00' " + "AND (SELECT ops FROM tempBlockoutOps WHERE tempBlockoutOps.ScheduleNum=schedule.ScheduleNum) = '" + POut.String(ops) + "' "; Db.NonQ(command); } command = "DROP TABLE IF EXISTS tempBlockoutOps"; Db.NonQ(command); //clear all the orphaned scheduleops command = "DELETE FROM scheduleop WHERE NOT EXISTS(SELECT * FROM schedule WHERE scheduleop.ScheduleNum=schedule.ScheduleNum)"; long result = Db.NonQ(command); //we can test the result in debug }
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first. ///Does not work for Oracle, due to some MySQL specific commands inside.</summary> public static void MakeABackup(string serverName = "", string user = "", string pass = "", bool doVerify = false) { //This function should always make the backup on the server itself, and since no directories are //referred to (all handled with MySQL), this function will always be referred to the server from //client machines. if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { Meth.GetVoid(MethodBase.GetCurrentMethod(), serverName, user, pass, doVerify); return; } //UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users. //GetStringNoCache() will return blank if pref does not exist. if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra") { return; } string currentServerName = DataConnection.GetServerName().ToLower(); bool useSameServer = string.IsNullOrWhiteSpace(serverName) || currentServerName.Equals(serverName, StringComparison.CurrentCultureIgnoreCase); if (!string.IsNullOrWhiteSpace(serverName) && currentServerName == "localhost" && serverName.ToLower() != "localhost") //there could be a mismatch but technically the same server { useSameServer = serverName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase); } if (serverName.ToLower() == "localhost" && currentServerName != "localhost") //there could be a mismatch but technically the same server { useSameServer = currentServerName.Equals(Environment.MachineName, StringComparison.CurrentCultureIgnoreCase); } //only used in two places: upgrading version, and upgrading mysql version. //Both places check first to make sure user is using mysql. //we have to be careful to throw an exception if the backup is failing. DataConnection dcon = new DataConnection(); //if they provided a different server where they want their backup to be, we need a separate connection for that DataConnection dconBackupServer = useSameServer ? new DataConnection() : new DataConnection(serverName, "", user, pass, DatabaseType.MySql); //Check that the backup server does not already contain this database string command = "SELECT database()"; DataTable table = dcon.GetTable(command); string oldDb = PIn.String(table.Rows[0][0].ToString()); string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy"); command = "SHOW DATABASES"; table = dconBackupServer.GetTable(command); string[] databases = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { databases[i] = table.Rows[i][0].ToString(); } if (Contains(databases, newDb)) //if the new database name already exists //find a unique one { int uniqueID = 1; string originalNewDb = newDb; do { newDb = originalNewDb + "_" + uniqueID.ToString(); uniqueID++; }while(Contains(databases, newDb)); } command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8"; dconBackupServer.NonQ(command); //create the backup db on the backup server //But get the tables from the current, not the backup server command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL. table = dcon.GetTable(command); //Set the connection to the new database now that it has been created dconBackupServer = useSameServer?new DataConnection(newDb):new DataConnection(serverName, newDb, user, pass, DatabaseType.MySql); foreach (DataRow row in table.Rows) { string tableName = row[0].ToString(); //First create the table on the new db MiscDataEvent.Fire(ODEventType.MiscData, $"Backing up table: {tableName}"); //also works with views. Added backticks around table name for unusual characters. command = $"SHOW CREATE TABLE `{oldDb}`.`{tableName}`"; DataTable dtCreate = dcon.GetTable(command); command = PIn.ByteArray(dtCreate.Rows[0][1]); //The backup database tables will be MyISAM because it is significantly faster at doing bulk inserts. command = command.Replace("ENGINE=InnoDB", "ENGINE=MyISAM"); dconBackupServer.NonQ(command); //Then copy the data into the new table if (useSameServer) { //If on the same server we can select into directly, which is faster command = $"INSERT INTO `{newDb}`.`{tableName}` SELECT * FROM `{oldDb}`.`{tableName}`"; //Added backticks around table name for unusual characters. dconBackupServer.NonQ(command); } else { long count = PIn.Long(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`")); int limit = 10000; if (tableName == "documentmisc") //This table can have really large rows so just to be safe, handle the backup one row at a time { limit = 1; } int offset = 0; while (count > offset) { DataTable dtOld = dcon.GetTable($" SELECT * FROM `{oldDb}`.`{tableName}` LIMIT {limit} OFFSET {offset}"); offset += dtOld.Rows.Count; dconBackupServer.BulkCopy(dtOld, tableName); } } } //Verify that the old database and the new backup have the same number of rows if (doVerify) { List <string> listTablesFailed = new List <string>(); foreach (DataRow dbTable in table.Rows) { string tableName = dbTable[0].ToString(); MiscDataEvent.Fire(ODEventType.MiscData, $"Verifying backup: {tableName}"); int ctOld = PIn.Int(dcon.GetCount($"SELECT COUNT(*) FROM `{oldDb}`.`{tableName}`")); int ctNew = PIn.Int(dconBackupServer.GetCount($"SELECT COUNT(*) FROM `{newDb}`.`{tableName}`")); if (ctOld != ctNew) { listTablesFailed.Add(tableName); } } if (listTablesFailed.Count > 0) { throw new Exception($@"Failed to create database backup because the following tables contained a different number of rows than expected: {string.Join(", ",listTablesFailed)}." ); } } }
///<summary>Backs up the database to the same directory as the original just in case the user did not have sense enough to do a backup first. ///Does not work for Oracle, due to some MySQL specific commands inside.</summary> public static long MakeABackup() { //This function should always make the backup on the server itself, and since no directories are //referred to (all handled with MySQL), this function will always be referred to the server from //client machines. if (RemotingClient.RemotingRole == RemotingRole.ClientWeb) { return(Meth.GetLong(MethodBase.GetCurrentMethod())); } //UpdateStreamLinePassword is purposefully named poorly and used in an odd fashion to sort of obfuscate it from our users. //GetStringNoCache() will return blank if pref does not exist. if (PrefC.GetStringNoCache(PrefName.UpdateStreamLinePassword) == "abracadabra") { return(0); } //only used in two places: upgrading version, and upgrading mysql version. //Both places check first to make sure user is using mysql. //we have to be careful to throw an exception if the backup is failing. DataConnection dcon = new DataConnection(); string command = "SELECT database()"; DataTable table = dcon.GetTable(command); string oldDb = PIn.String(table.Rows[0][0].ToString()); string newDb = oldDb + "backup_" + DateTime.Today.ToString("MM_dd_yyyy"); command = "SHOW DATABASES"; table = dcon.GetTable(command); string[] databases = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { databases[i] = table.Rows[i][0].ToString(); } if (Contains(databases, newDb)) //if the new database name already exists //find a unique one { int uniqueID = 1; string originalNewDb = newDb; do { newDb = originalNewDb + "_" + uniqueID.ToString(); uniqueID++; }while(Contains(databases, newDb)); } command = "CREATE DATABASE `" + newDb + "` CHARACTER SET utf8"; dcon.NonQ(command); command = "SHOW FULL TABLES WHERE Table_type='BASE TABLE'"; //Tables, not views. Does not work in MySQL 4.1, however we test for MySQL version >= 5.0 in PrefL. table = dcon.GetTable(command); string[] tableName = new string[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { tableName[i] = table.Rows[i][0].ToString(); } //switch to using the new database DataConnection newDcon = new DataConnection(newDb); for (int i = 0; i < tableName.Length; i++) { //Alert anyone that cares that we are backing up this table. ODEvent.Fire(new ODEventArgs("BackupProgress", Lans.g("MiscData", "Backing up table") + ": " + tableName[i])); command = "SHOW CREATE TABLE `" + oldDb + "`.`" + tableName[i] + "`"; //also works with views. Added backticks around table name for unusual characters. table = newDcon.GetTable(command); command = PIn.ByteArray(table.Rows[0][1]); newDcon.NonQ(command); //this has to be run using connection with new database command = "INSERT INTO `" + newDb + "`.`" + tableName[i] + "` " + "SELECT * FROM `" + oldDb + "`.`" + tableName[i] + "`"; //Added backticks around table name for unusual characters. newDcon.NonQ(command); } return(0); }