<?php 
/**
 * This file contains the Backup_Database class wich performs
 * a partial or complete backup of any given MySQL database
 * @author Daniel López Azaña <daniloaz@gmail.com>
 * @version 1.0
 */

/**
 * Define database parameters here
 */
define("DB_USER", 'pondercf_dental0');
define("DB_PASSWORD", 'ponder00723');
define("DB_NAME", 'pondercf_elindelc_dental');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", './myphp-backup-files'); // Comment this line to use same script's directory ('.')
//define("TABLES", '*'); // Full backup
define("TABLES", 'precios'); // Partial backup
define("CHARSET", 'utf8');
define("GZIP_BACKUP_FILE", false);  // Set to false if you want plain SQL backup files (not gzipped)

define('BACKUP_BASE_DIR', '/home2/pondercf/public_html/dental/imagenes/backup/');
$hostname_odonto_connection = "localhost";
$database_odonto_connection = "";
$username_odonto_connection = "";
$password_odonto_connection = "";
/**
 * The Backup_Database class
 */
class Backup_Database {
    /**
     * Host where the database is located
     */
    var $host;

    /**
     * Username used to connect to database
     */
    var $username;

    /**
     * Password used to connect to database
     */
    var $passwd;

    /**
     * Database to backup
     */
    var $dbName;

    /**
     * Database charset
     */
    var $charset;

    /**
     * Database connection
     */
    var $conn;

    /**
     * Backup directory where backup files are stored 
     */
    var $backupDir;

    /**
     * Output backup file
     */
    var $backupFile;

    /**
     * Use gzip compression on backup file
     */
    var $gzipBackupFile;
	
	var $file_id;
	var $zip_id;
	var $zip;
	var $cantidad;
	var $filename;
	var $id;

    /**
     * Constructor initializes database
     */
    public function __construct($host, $username, $passwd, $dbName, $charset = 'utf8') {
        $this->host            = $host;
        $this->username        = $username;
        $this->passwd          = $passwd;
        $this->dbName          = $dbName;
        $this->charset         = $charset;
        $this->conn            = $this->initializeDatabase();
        $this->backupDir       = BACKUP_BASE_DIR;
        $this->backupFile      = 'myphp-backup-'.$this->dbName.'-'.date("Ymd_His", time()).'.sql';
        $this->gzipBackupFile  = defined('GZIP_BACKUP_FILE') ? GZIP_BACKUP_FILE : true;
		$this->zip_id		= 0;
		$this->file_id		= 0;
        $this->zip = null;
		$this->cantidad		= 0;
    }

    protected function initializeDatabase() {
        try {
            $conn = mysqli_connect($this->host, $this->username, $this->passwd, $this->dbName);
            if (mysqli_connect_errno()) {
                throw new Exception('ERROR connecting database: ' . mysqli_connect_error());
                die();
            }
            if (!mysqli_set_charset($conn, $this->charset)) {
                mysqli_query($conn, 'SET NAMES '.$this->charset);
            }
        } catch (Exception $e) {
            print_r($e->getMessage());
            die();
        }

        return $conn;
    }

    /**
     * Backup the whole database or just some tables
     * Use '*' for whole database or 'table1 table2 table3...'
     * @param string $tables
     */
    public function backupTables($tables = '*') {
        
        try {
            /**
            * Tables to export
            */
            if($tables == '*') {
                $tables = array();
                $result = mysqli_query($this->conn, 'SHOW TABLES');
                while($row = mysqli_fetch_row($result)) {
                    $tables[] = $row[0];
                    //break;
                }
			    mysqli_free_result($result); //AP 
            } else {
                $tables = is_array($tables) ? $tables : explode(',', str_replace(' ', '', $tables));
            }
			$sql = '';
            //$sql = 'CREATE DATABASE IF NOT EXISTS `'.$this->dbName."`;\n\n";
            //$sql .= 'USE `'.$this->dbName."`;\n\n";

            /**
            * Iterate tables
            */
            foreach($tables as $table) {
                $this->obfPrint("Backing up `".$table."` table...".str_repeat('.', 50-strlen($table)), 0, 0);

                /**
                 * CREATE TABLE
                 */
                $sql .= 'DROP TABLE IF EXISTS `'.$table.'`;';
                
                
                    $this->saveFile($sql, true);
                    $sql = '';
                    
                $row = mysqli_fetch_row(mysqli_query($this->conn, 'SHOW CREATE TABLE `'.$table.'`'));
                
                //$sql .= "\n\n".$row[1].";\n\n";
                $sql = $row[1] . ";";
                
                $sql = str_replace('utf8mb4_unicode_ci','utf8_unicode_ci',$sql);
                $sql = str_replace(' CHARACTER SET utf8mb4','',$sql);
                $sql = str_replace('DEFAULT CHARSET=utf8mb4','DEFAULT CHARSET=utf8', $sql);
                $this->saveFile($sql, true);
                $sql = '';
                /**
                 * INSERT INTO
                 */

                $row = mysqli_fetch_row(mysqli_query($this->conn, 'SELECT COUNT(*) FROM `'.$table.'`'));
                $numRows = $row[0];

                // Split table in batches in order to not exhaust system memory 
                $batchSize = 100; // Number of rows per batch
                $numBatches = intval($numRows / $batchSize) + 1; // Number of while-loop calls to perform
/*
                for ($b = 1; $b <= $numBatches; $b++) {
                    
                    $query = 'SELECT * FROM `'.$table.'` LIMIT '.($b*$batchSize-$batchSize).','.$batchSize;
                    $result = mysqli_query($this->conn, $query);
                    $numFields = mysqli_num_fields($result);

                    $a_sql = array();

                    for ($i = 0; $i < $numFields; $i++) {
                        $rowCount = 0;
                        while($row = mysqli_fetch_row($result)) {
                            //$sql .= 'INSERT INTO `'.$table.'` VALUES(';
                            $sql='(';
                            for($j=0; $j<$numFields; $j++) {
                                if (isset($row[$j])) {
                                    $row[$j] = addslashes($row[$j]);
                                    $row[$j] = str_replace("\n","\\n",$row[$j]);
                                    $sql .= '"'.$row[$j].'"' ;
                                } else {
                                    $sql.= 'NULL';
                                }

                                if ($j < ($numFields-1)) {
                                    $sql .= ',';
                                }
                            }

                            //$sql.= ");\n";
                            $sql.= ")";
                            $a_sql[] = $sql;
                        }
                    }
                    $sql = 'INSERT INTO `'.$table.'` VALUES ' . implode(",\n", $a_sql) . ';';
                    $this->saveFile($sql);
                    $sql = '';
*/
                $offset = 0;
                while(1) {
                    
                    $query = "SELECT * FROM `$table` LIMIT $offset,$batchSize";
                    $offset+=$batchSize;
                    $result = mysqli_query($this->conn, $query);
                    $numFields = mysqli_num_fields($result);

                    $a_sql = array();

                    $rowCount = 0;
                    while($row = mysqli_fetch_row($result)) {
                        $rowCount++;
                        $sql='(';
                        for($j=0; $j<$numFields; $j++) {
                            if (isset($row[$j])) {
                                $row[$j] = addslashes($row[$j]);
                                $row[$j] = str_replace("\n","\\n",$row[$j]);
                                $sql .= '"'.$row[$j].'"' ;
                            } else {
                                $sql.= 'NULL';
                            }

                            if ($j < ($numFields-1)) {
                                $sql .= ',';
                            }
                        }

                        $sql.= ")";
                        $a_sql[] = $sql;
                    }
                    
			        mysqli_free_result($result); //AP 
                    if($rowCount==0)
                        break;

                    $sql = 'INSERT INTO `'.$table.'` VALUES ' . implode(",\n", $a_sql) . ';';
                    $this->saveFile($sql);
                    $sql = '';


                }

                $sql.="\n\n\n";

                $this->obfPrint(" OK");
            }

            if ($this->gzipBackupFile) {
                $this->gzipBackupFile();
            } else {
                $this->obfPrint('Backup file succesfully saved to ' . $this->backupDir.'/'.$this->backupFile, 1, 1);
            }
        } catch (Exception $e) {
            print_r($e->getMessage());
            return false;
        }

$this->zip->close();
        return true;
    }

    /**
     * Save SQL to file
     * @param string $sql
     */
    protected function saveFile(&$sql, $only=false) {
        if (!$sql) return false;

        try {

            if( is_null( $this->zip ))
            {
                $this->zip = new ZipArchive();
                
                $this->filename = sprintf(BACKUP_BASE_DIR . 'backup-%010d.zip', $this->zip_id++);

                if ($this->zip->open($this->filename, ZipArchive::CREATE)!==TRUE)
                    exit("cannot open <$this->filename>\n");
                    $this->cantidad = 0;
                    $this->id = 0;
            }

            $this->file_id++;
            
            
            $this->zip->addFromString(sprintf( '%020d.sql', $this->file_id), $sql);
            
           // echo $this->zip->count.'<br>';
            //echo $this->zip->statIndex( $this->zip->count-1 )['comp_size'] . '<>' . strlen($sql) . '<br>';
 /*           
            if( ($data=$this->zip->statIndex( $this->id ) ) )
            {
                echo $data['size'] . '=>' . $data['comp_size'] . '<br>';
                
                $this->cantidad+=$data['comp_size'];
            }
            $this->id++;
*/
            $this->cantidad += strlen($sql);
    
            if( $this->cantidad >= 15000000)
            //if( filesize ( $this->filename )>500000 )
            {
                $this->zip->close();
                $this->zip = null;
                $this->cantidad =0;
                $this->id = 0;
            }

            if (!file_exists($this->backupDir)) {
                mkdir($this->backupDir, 0777, true);
            }
/*
            //file_put_contents($this->backupDir.'/'.$this->backupFile, $sql, FILE_APPEND | LOCK_EX);
			$file = $this->backupDir . '/' . sprintf( '%020d', $this->file_id) . '.sql';
			
            //    echo "<pre>$file</pre><br>";
            file_put_contents($file, $sql, FILE_APPEND | LOCK_EX);

            if( ($this->file_id % 100000) == 0)
            {
                $file=sprintf(BACKUP_DIR.'/sql%020d', $this->file_id) . '.gz';
                $command = "tar -czvf $file " . $this->backupDir . '/*.sql';
                echo "<pre>$command</pre><br>";
                system( $command );
                system( "rm " . $this->backupDir . '/*.sql');
            }
*/
                echo '.';
        } catch (Exception $e) {
            print_r($e->getMessage());
            return false;
        }

        return true;
    }

    /*
     * Gzip backup file
     *
     * @param integer $level GZIP compression level (default: 9)
     * @return string New filename (with .gz appended) if success, or false if operation fails
     */
    protected function gzipBackupFile($level = 9) {
        if (!$this->gzipBackupFile) {
            return true;
        }

        $source = $this->backupDir . '/' . $this->backupFile;
        $dest =  $source . '.gz';

        $this->obfPrint('Gzipping backup file to ' . $dest . '... ', 1, 0);

        $mode = 'wb' . $level;
        if ($fpOut = gzopen($dest, $mode)) {
            if ($fpIn = fopen($source,'rb')) {
                while (!feof($fpIn)) {
                    gzwrite($fpOut, fread($fpIn, 1024 * 256));
                }
                fclose($fpIn);
            } else {
                return false;
            }
            gzclose($fpOut);
            if(!unlink($source)) {
                return false;
            }
        } else {
            return false;
        }
        
        $this->obfPrint('OK');
        return $dest;
    }

    /**
     * Prints message forcing output buffer flush
     *
     */
    public function obfPrint ($msg = '', $lineBreaksBefore = 0, $lineBreaksAfter = 1) {
        if (!$msg) {
            return false;
        }

        $output = '';

        if (php_sapi_name() != "cli") {
            $lineBreak = "<br />";
        } else {
            $lineBreak = "\n";
        }

        if ($lineBreaksBefore > 0) {
            for ($i = 1; $i <= $lineBreaksBefore; $i++) {
                $output .= $lineBreak;
            }                
        }

        $output .= $msg;

        if ($lineBreaksAfter > 0) {
            for ($i = 1; $i <= $lineBreaksAfter; $i++) {
                $output .= $lineBreak;
            }                
        }

        echo $output;
/*
        if (php_sapi_name() != "cli") {
            ob_flush();
        }
*/
        flush();
    }
}

/**
 * Instantiate Backup_Database and perform backup
 */

// Report all errors
error_reporting(E_ALL);
// Set script max execution time
set_time_limit(900); // 15 minutes

if (php_sapi_name() != "cli") {
    echo '<div style="font-family: monospace;">';
}

$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$result = $backupDatabase->backupTables(TABLES, BACKUP_DIR) ? 'OK' : 'KO';
$backupDatabase->obfPrint('Backup result: ' . $result, 1);

if (php_sapi_name() != "cli") {
    echo '</div>';
}
