<?
$backup=new mysql_backup(dirname(__FILE__));

class mysql_backup {
	private $my;
	private $archivedir;
	private $backupdir;
	private $schema='';
	private $dry_run=0;
	private $database='';

	public function __construct($basedir='/backup') {
		set_time_limit(1800);
		ini_set('memory_limit','60M');
		date_default_timezone_set('UTC');
		$args=array();
		if($_SERVER['argv']) {
        		foreach($_SERVER['argv'] as $x) {
                		if(!preg_match("/^(.+?)=(.*)$/",$x,$match)) continue;
                		$args[$match[1]]=$match[2];
        		}
		}
		$this->site=$args['site'];
		if(!$this->site) {
			print "Please specify a site!\n";
			print "To Analyse: {$_SERVER['argv'][0]} analyse=1 site=<sitename> database=<dbname> [dry-run=1]\n";
			print "To Backup: {$_SERVER['argv'][0]} backup=1 site=<sitename> database=<dbname> [dry-run=1]\n";
			print "To Verify: {$_SERVER['argv'][0]} verify=1 site=<sitename> database=<dbname>\n";
			print "To Restore: {$_SERVER['argv'][0]} restore=1 site=<sitename> database=<dbname> target=<dbname> [dry-run=1]\n";
			exit;
		}
		if(!$this->connect($args['hostname'], $args['username'], $args['password'])) {
			exit;
		}
		$this->database=$args['database'];
		if(!$this->database) {
			print "Please specify a database!\n";
			exit;
		}
		if(!$this->change_database($this->database)) {
			exit;
		}

		$this->backupdir="$basedir/{$this->site}/db-{$this->database}";
		$this->archivedir="$basedir/{$this->site}/dbarchive-{$this->database}";
		if(isset($args['dry-run'])) $this->dry_run=$args['dry-run'];
		if(!$this->dry_run) {
			if(!is_dir(dirname($this->backupdir))) mkdir(dirname($this->backupdir));
			if(!is_dir($this->backupdir)) mkdir($this->backupdir);
			chmod($this->backupdir,0777);
			if(!is_dir(dirname($this->archivedir))) mkdir(dirname($this->archivedir));
			if(!is_dir($this->archivedir)) mkdir($this->archivedir);
			chmod($this->archivedir,0777);
		}
		if($args['analyse']) {
			$this->analyse_database();
			exit;
		}
		$groups=array();
		$groupsfile=dirname(__FILE__)."/backup-{$this->site}-{$this->database}.php";
		if(!file_exists($groupsfile)) {
			$this->logit("$groupsfile doesn't exist! Run an analyse first");
			exit;
		}
		include($groupsfile);
		if(!$groups) {
			$this->logit("There doesn't appear to be a \$groups array in $groupsfile");
			exit;
		}
		if($args['backup']) {
			foreach($groups as $group => $tables) {
				$this->backup_group($group,$tables);
			}
			$sql=$this->backup_schema();
			exit;
		}
		if($args['verify']) {
			$target_database='test'.date('Ymd').sprintf("%05d",rand(0,10000));
			if($this->dry_run) {
				$this->logit("Would restore data to $target_database");
			}
			else {
				if(!$this->create_database($target_database)) {
					exit;
				}
				if(!$this->change_database($target_database)) {
					exit;
				}
			}
			if(!$this->restore_schema()) {
				exit;
			}
			foreach($groups as $group => $tables) {
				$this->restore_group($group,$tables);
			}
			if(!$this->dry_run) {
				$this->logit("Have a look at the tables in $target_database, and DROP it when you're done!");
			}
			exit;
		}
		if($args['restore']) {
			$target_database=$args['target'];
			if(!$target_database) {
				print "Please specify a target database!\n";
				exit;
			}
			if(!$this->change_database($target_database)) {
				exit;
			}
			if(!$this->restore_schema()) {
				exit;
			}
			foreach($groups as $group => $tables) {
				$this->restore_group($group,$tables);
			}
			exit;
		}
	}

	private function logit($msg) {
		print "[".date('H:i:s')."] $msg\n";
		flush();ob_flush();
	}

	public function connect($host,$user,$pass) {
		$this->my=new mysqli($host,$user,$pass);
		if($err=mysqli_connect_error()) {
			$this->logit("Couldn't connect to $host as $user: $err");
			return false;
		}
		return true;
	}

	public function create_database($db) {
		$query="CREATE DATABASE `$db`";
		if(!$this->my->query($query)) {
			$this->logit("Failed to create $db database: ".$this->my->error);
			return false;
		}
		return true;
	}

	public function drop_database($db) {
		$query="DROP DATABASE `$db`";
		if(!$this->my->query($query)) {
			$this->logit("Failed to drop $db database: ".$this->my->error);
			return false;
		}
		return true;
	}

	public function change_database($db) {
		if (!$this->my->select_db($db)) {
			$this->logit("Unable to select $db database");
			return false;
		}
		$actual_db='';
		$query="SELECT DATABASE()";
		if($res=$this->my->query($query)) {
			list($actual_db)=$res->fetch_row();
		}
		if($actual_db != $db) {
			$this->logit("Unable to select $db database");
			return false;
		}
		$this->logit("Selected $actual_db database");
		return true;
	}

	public function analyse_database() {
		$query = "SELECT TABLE_NAME,TABLE_ROWS,DATA_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA='{$this->database}'";
		$res=$this->my->query($query);
		if(!$res) {
			$this->logit("Couldn't get list of tables!");
			return false;
		}
		$total=$res->num_rows;
		$this->logit("$total tables");
		$groups=array();
		$this->my->store_result();
		while(list($table,$rows,$databytes)=$res->fetch_row()) {
			$large=($rows > 100000 or $databytes > 50000000);
			$incr=preg_match("/(logs?|streams?)\b/",$table);
			$backup=preg_match("/cache|^tmp|^old_|_old$/",$table)?0:1;
			if($incr) {
				$key='';
				$query="SHOW CREATE TABLE `$table`";
				if($res2=$this->my->query($query)) {
					$inf=$res2->fetch_assoc();
					$res2->free();
					if(preg_match("/`(\w+)` .+?AUTO_INCREMENT/i",$inf['Create Table'],$match)) {
						$key=$match[1];
					}
				}
			}
			if($incr and !$key) $incr=0;
			$type=$incr ? 'incremental' : 'full';
			$group=$large ? 'large' : 'default';
			if(!$key) $key=1;
			$groups[$group][$table]=array('type'=>$type, 'key'=>$key,'backup'=>$backup,);
		}
		$res->free();
		$php = "<?\n\$groups=array(\n";
		foreach($groups as $group => $tables) {
			$php .= "\t'$group' => array(\n";
			foreach($tables as $table => $config) {
				$php .= "\t\t'$table' => array('{$config['type']}'=>'{$config['key']}','backup'=>'{$config['backup']}'),\n";
			}
			$php .= "\t),\n";

		}
		$php .= ");\n";
		$groupsfile=dirname(__FILE__)."/backup-{$this->site}-{$this->database}.php";
		if($this->dry_run) {
			$this->logit("Would create file $groupsfile with this content:\n$php");
		}
		else {
			if(file_exists($groupsfile)) {
				$this->logit("Won't over-write existing file $groupsfile; move it out of the way first");
				return false;
			}
			file_put_contents($groupsfile,$php);
		}
		return true;
	}

	public function backup_schema() {
		$file="{$this->backupdir}/schema.sql";
		if(file_exists($file)) {
			$archive_file="{$this->archivedir}/schema-".date('Y-m-d',filemtime($file)).".sql";
			if($this->dry_run) {
				$this->logit("Would rename($file,$archive_file)");
			}
			else {
				rename($file,$archive_file);
			}
		}
		if($this->dry_run) {
			$this->logit("Would file_put_contents($file,".strlen($this->schema)." chars)");
		}
		else {
			file_put_contents($file,$this->schema);
		}
		return $file;
	}

	public function restore_schema() {
		$file="{$this->backupdir}/schema.sql";
		if(!file_exists($file)) {
			$this->logit("No schema file $file!");
			return false;
		}
		$this->schema=file_get_contents($file);
		if($this->dry_run) {
			$this->logit("Would mysql_query(".strlen($this->schema)." chars)");
		}
		else {
			if(!$this->my->multi_query($this->schema)) {
				$this->logit("restore_schema failed:".$this->my->error);
				return false;
			}
			do {
				$result = $this->my->store_result();
			} while ($this->my->next_result());
			
		}
		return $file;
	}

	public function backup_group($group,$tables) {
		$this->logit("Backup $group (".count($tables)." tables) ...");
		$tablestr='`'.implode('`,`',$tables).'`';

		$now_time=date('Y-m-d H:i:s');
		//$this->my->query("LOCK TABLES $tablestr");
		foreach($tables as $table => $config) {
			# Find the last file
			$previous_update_time=$previous_update_date=$the_file=$previous_max='';
			$files=glob("{$this->backupdir}/$table*");
			foreach($files as $file) {
				if(!preg_match("@/$table(--([\d\-: ]+))?\.txt\.gz$@",$file,$match)) continue;
				$max=$match[2];
				$the_file=$file;
				$previous_update_time=date('Y-m-d H:i:s',filemtime($the_file));
				$previous_update_date=date('Y-m-d',filemtime($the_file));
				$previous_max=max($max,$previous_max);
			}
			$update_time='';
			$query = "SELECT UPDATE_TIME FROM information_schema.TABLES WHERE TABLE_SCHEMA='{$this->database}' AND TABLE_NAME='$table'";
			if($res=$this->my->query($query)) {
				list($update_time)=$res->fetch_row();
				$res->free();
			}
			else {
				$this->logit("No info for $table:".$this->my->error);
				return;
			}
			$query = "SHOW CREATE TABLE `$table`";
			if($res=$this->my->query($query)) {
				$inf=$res->fetch_assoc();
				$res->free();
				$query=preg_replace("/^(CREATE TABLE)/","$1 IF NOT EXISTS",$inf['Create Table']);
				$this->schema.="$query;\n\n";
			}

			if(isset($config['backup']) and !$config['backup']) continue;

			if($update_time <= $previous_update_time) {
				$this->logit("$table hasn't changed since $previous_update_time");
				continue;
			}

			if($config['full']) {
				# Do a full backup!
				$file="{$this->backupdir}/{$table}.txt";
				if(file_exists("$file.gz")) {
					if($this->dry_run) {
						$this->logit("Would move $file.gz to {$this->archivedir}/{$table}-$previous_update_date.txt.gz");
					}
					else {
						rename("$file.gz","{$this->archivedir}/{$table}-$previous_update_date.txt.gz");
					}
				}
				$query = "SELECT * INTO OUTFILE '$file' FROM `$table`";
				$this->logit("FULL query: $query");
				if(!$this->dry_run) {
					if($this->my->query($query)) {
						$this->compress_file($file);
					}
					else {
						$this->logit("Error: ".$this->my->error);
					}
				}
			}
			else {
				# Incremental.  Figure out the last place we got to first!
				$key=$config['incremental'];
				if(!$key) {
					$this->logit("No key for $table");
					continue;
				}
				$query = "SELECT MAX(`$key`) FROM `$table`";
				$max=0;
				if($res=$this->my->query($query)) {
					list($max)=$res->fetch_row();
					$res->free();
				}
				if(!max or $max==$previous_max) continue;
				$file="{$this->backupdir}/{$table}--$max.txt";
				$where=$previous_max ? "WHERE `{$config['incremental']}` > '$previous_max'" : '';
				$query = "SELECT * INTO OUTFILE '$file' FROM `$table` $where";
				$this->logit("INCR query: $query");
				if(!$this->dry_run) {
					if($this->my->query($query)) {
						$this->compress_file($file);
					}
					else {
						$this->logit("Error: ".$this->my->error);
					}
				}
			}
		}
		//$this->my->query("UNLOCK TABLES $tablestr");
	}

	function restore_group($group,$tables) {
		$this->logit("Restore $group (".count($tables)." tables) ...");
		$tablestr='`'.implode('`,`',$tables).'`';

		$now_time=date('Y-m-d H:i:s');
		//$this->my->query("LOCK TABLES $tablestr");
		foreach($tables as $table => $config) {
			if($config['full']) {
				# Do a full restore!
				$file="{$this->backupdir}/{$table}.txt";
				if(!file_exists("$file.gz")) {
					$this->logit("$file.gz doesn't exist!");
					continue;
				}
				$query = "LOAD DATA INFILE '$file' INTO TABLE `$table`";
				$this->logit("FULL query: $query");
				if(!$this->dry_run) {
					$this->uncompress_file($file);
					if(!$this->my->query($query)) {
						$this->logit("Error: ".$this->my->error);
					}
					unlink($file);
				}
			}
			else {
				# Incremental.  Restore each file in turn
				$key=$config['incremental'];
				if(!$key) {
					$this->logit("No key for $table");
					continue;
				}
				# Find previous files
				$files=glob("{$this->backupdir}/$table*");
				foreach($files as $file) {
					if(!preg_match("@/$table(--([\d\-: ]+))?\.txt\.gz$@",$file,$match)) continue;
					$max=$match[2];
					$query = "SELECT MAX(`$key`) FROM `$table`";
					$previous_max=0;
					if($res=$this->my->query($query)) {
						list($previous_max)=$res->fetch_row();
						$res->free();
					}
					if($max<=$previous_max) {
						$this->logit("Ignoring file $file ($max <= $previous_max)");
						continue;
					}
					$file=preg_replace("/\.gz$/",'',$file);
					$query = "LOAD DATA INFILE '$file' INTO TABLE `$table`";
					$this->logit("FULL query: $query");
					if(!$this->dry_run) {
						$this->uncompress_file($file);
						if(!$this->my->query($query)) {
							$this->logit("Error: ".$this->my->error);
						}
						unlink($file);
					}
				}
			}
		}
		//$this->my->query("UNLOCK TABLES $tablestr");
	}

	private function compress_file($file) {
		$ifh = fopen ($file,'r');
		$ofh = fopen ("compress.zlib://$file.gz",'a');
		if(!$ofh) return false;
		while(($line=fgets($ifh)) !== false) {
			if(!fwrite($ofh, $line)) {
				$this->logit("Error writing to $file.gz");
				fclose($ifh);
				fclose($ofh);
				return false;
			}
		}
		unlink($file);
		fclose($ifh);
		fclose($ofh);
		return true;
	}
		
	private function uncompress_file($file) {
		$ifh = fopen ("compress.zlib://$file.gz",'r');
		$ofh = fopen ($file,'w');
		if(!$ofh) return false;
		while(($line=fgets($ifh)) !== false) {
			if(!fwrite($ofh, $line)) {
				$this->logit("Error writing to $file");
				fclose($ifh);
				fclose($ofh);
				return false;
			}
		}
		fclose($ifh);
		fclose($ofh);
		return true;
	}
		
}