Beispiel #1
0
        public static void DeleteBeforeDateInclusive(DateTime date)
        {
            if (RemotingClient.RemotingRole == RemotingRole.ClientWeb)
            {
                Meth.GetVoid(MethodBase.GetCurrentMethod(), date);
                return;
            }
            int         countDeleted = 0;
            List <long> listSecurityLogNums;

            do
            {
                //Delete the hashes
                MiscDataEvent.Fire(CodeBase.ODEventType.MiscData,
                                   Lans.g("FormBackup", "Removing old data from securityloghash table. Rows deleted so far:") + " " + countDeleted);
                //limiting to 500,000 to avoid out of memory exceptions
                string command = $"SELECT SecurityLogNum FROM securitylog WHERE DATE(LogDateTime) <= {POut.DateT(date.Date)} LIMIT 500000";
                listSecurityLogNums = Db.GetListLong(command);
                if (listSecurityLogNums.Count < 1)
                {
                    break;
                }
                SecurityLogHashes.DeleteForSecurityLogEntries(listSecurityLogNums);
                //Then delete the securitylog entries themselves
                MiscDataEvent.Fire(CodeBase.ODEventType.MiscData,
                                   Lans.g("FormBackup", "Removing old data from securitylog table. Rows deleted so far:") + " " + countDeleted);
                command = $"DELETE FROM securitylog WHERE SecurityLogNum IN ({string.Join(",",listSecurityLogNums)})";
                Db.NonQ(command);
                countDeleted += listSecurityLogNums.Count;
            }while(listSecurityLogNums.Count > 0);
        }
Beispiel #2
0
        ///<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)}."                    );
                }
            }
        }