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