Prune Improvement

[patrickdight]

I recently turned on pruning in a copy of our system and found there were a number of problems.

1) It would time out as I have a lot of spam emails to prune (need to increase your php.ini max execution time setting temporarily)
2) it would run out of memory as it creates a single array of all changes and then writes it out to a file. It acutally deletes one or more tables but does not write the backup file !
3) It does not prune uploaded attachments
4) The file can not be easily loaded back into mysql

Here is a new version of the pruning code which can be updated to your modules/Schedulers/_AddJobsHere.php. It is supplied AS IS in the hope people may find it usefull - test it yourself before you run it in production. Currently the log lines are set to fatal so I can keep an eye on it. Remember in order to actually save server space you need to purge these files off the server too.

Patrick


====================================

 function pruneDatabase() {
    $GLOBALS['log']->fatal('----->Scheduler fired job of type pruneDatabase() '. date('Y-m-d H:i:s'));
    $backupDir    = 'cache/backups';
    $backupFile    = 'backup-GMT0_'.gmdate('Y_m_d-H_i_s', strtotime('now')).'.sql';
 
    global $sugar_config;
    $backupNotes=rtrim($backupFile, ".sql");
 
    $db = PearDatabase::getInstance();
    $tables = $db->getTablesArray();
 
    if(!function_exists('mkdir_recursive')) {
        require_once('include/dir_inc.php');
    }
    if(!function_exists('write_array_to_file')) {
        require_once('include/utils/file_utils.php');
    }
    if(!file_exists($backupDir) || !file_exists($backupDir.'/'.$backupFile)) {
        // create directory if not existent
        mkdir_recursive($backupDir, false);
    }
 
    // write cache file
    $the_file=$backupDir.'/'.$backupFile;
 
    if( !$fh = @fopen( $the_file, "w" ) ) {
        $GLOBALS['log']->fatal('pruneDatabase() stopped - could not create '.$the_file);
        return false;
    }
 
    $query='# started: ' . date('Y-m-d H:i:s') . "\n";
    fputs( $fh, $query, strlen($query));
 
 
//_ppd($tables);
    if(!empty($tables)) {
        foreach($tables as $kTable => $table) {
            // find tables with deleted=1
            $qDel = 'SELECT * FROM '.$table.' WHERE deleted = 1';
            $rDel = $db->query($qDel);// OR continue; // continue if no 'deleted' column
            $pdcount=0;
 
            if($db->getRowCount($rDel)==0) continue;
 
            // make a backup INSERT query if we are deleting.
            while($aDel = $db->fetchByAssoc($rDel)) {
                $pdcount++;
                // build column names
                $rCols = $db->query('SHOW COLUMNS FROM '.$table);
                $colName = array();
 
                while($aCols = $db->fetchByAssoc($rCols)) {
                    $colName[] = $aCols['Field'];
                }
 
                $query = 'INSERT INTO '.$table.' (';
                $values = '';
                foreach($colName as $kC => $column) {
                    $query .= $column.', ';
                    $values .= '"'.$aDel[$column].'", ';
                }
 
                $query  = substr($query, 0, (strlen($query) - 2));
                $values = substr($values, 0, (strlen($values) - 2));
                $query .= ') VALUES ('.str_replace("'", "'", $values).');';
 
                if(empty($colName)) {
                    $GLOBALS['log']->fatal('pruneDatabase() could not get the columns for table ('.$table.')');
                }
 
 
                //justcrms start
                //and move any note attachment too
                if($table=='notes' && $aDel['filename']!="") {
                    $backupFileName=html_entity_decode($aDel['id'].$aDel['filename'], ENT_QUOTES);
 
                    if(!file_exists($backupDir) || !file_exists($backupDir.'/'.$backupNotes)) {
                        // create directory if not existent
                        mkdir_recursive($backupDir.'/'.$backupNotes, false);
                    }
                    if(!file_exists($sugar_config['upload_dir'].$backupFileName)) {
                        $GLOBALS['log']->fatal('pruneDatabase() could not find attachment '.$backupFileName.' but kept going...');
                    }
                    else {
                        if(!rename($sugar_config['upload_dir'].$backupFileName, $backupDir.'/'.$backupNotes.'/'.$backupFileName)) {
                            $GLOBALS['log']->fatal('pruneDatabase() halted could not move attachment '.$backupFileName);
                            return false;
                        }
                        else {
                            $GLOBALS['log']->fatal('pruneDatabase() moved attachment '.$backupFileName);
                        }
                    }
                }
                $query.="\n";
                fputs( $fh, $query, strlen($query));
                //justcrms end
 
            } // end aDel while()
            $GLOBALS['log']->fatal("backed up $pdcount $table to sql file");
 
            // now do the actual delete
            $db->query('DELETE FROM '.$table.' WHERE deleted = 1');
 
            $GLOBALS['log']->fatal("deleted $pdcount from $table");
 
        } // foreach() tables
 
        $query='# finished: ' . date('Y-m-d H:i:s') . "\n";
            fputs( $fh, $query, strlen($query));
 
        fclose( $fh );
 
        $GLOBALS['log']->fatal("pruneDatabase complete ". date('Y-m-d H:i:s'));
        return true;
    }
    return false;
}