3 * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
4 * Copyright (C) 2002-2007 The Nucleus Group
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
10 * (see nucleus/documentation/index.html#license for more info)
13 * Scripts to create/restore a backup of the Nucleus database
15 * Based on code in phpBB (http://phpBB.sourceforge.net)
17 * @license http://nucleuscms.org/license.txt GNU General Public License
18 * @copyright Copyright (C) 2002-2007 The Nucleus Group
19 * @version $Id: backup.php,v 1.9 2008-02-08 09:31:22 kimitake Exp $
20 * $NucleusJP: backup.php,v 1.8.2.1 2007/08/08 05:23:31 kimitake Exp $
25 * This function creates an sql dump of the database and sends it to
26 * the user as a file (can be gzipped if they want)
29 * no output may have preceded (new headers are sent)
31 * 1 = compress backup file, 0 = no compression (default)
33 function do_backup($gzip = 0) {
36 // tables of which backup is needed
38 sql_table('actionlog'),
47 sql_table('skin_desc'),
49 sql_table('template'),
50 sql_table('template_desc'),
52 sql_table('plugin_event'),
53 sql_table('plugin_option'),
54 sql_table('plugin_option_desc'),
55 sql_table('category'),
56 sql_table('activation'),
60 // add tables that plugins want to backup to the list
61 // catch all output generated by plugins
63 $res = sql_query('SELECT pfile FROM '.sql_table('plugin'));
64 while ($plugName = mysql_fetch_object($res)) {
65 $plug =& $manager->getPlugin($plugName->pfile);
66 if ($plug) $tables = array_merge($tables, (array) $plug->getTableList());
71 $tables = array_unique($tables);
73 // make sure browsers don't cache the backup
74 header("Pragma: no-cache");
76 // don't allow gzip compression when extension is not loaded
77 if (($gzip != 0) && !extension_loaded("zlib"))
83 // use an output buffer
85 @ob_implicit_flush(0);
88 $filename = 'nucleus_db_backup_'.strftime("%Y%m%d", time()).".sql.gz";
90 $filename = 'nucleus_db_backup_'.strftime("%Y%m%d", time()).".sql";
94 // send headers that tell the browser a file is coming
95 header("Content-Type: text/x-delimtext; name=\"$filename\"");
96 header("Content-disposition: attachment; filename=$filename");
100 echo "# This is a backup file generated by Nucleus \n";
101 echo "# http://www.nucleuscms.org/\n";
103 echo "# backup-date: " . gmdate("d-m-Y H:i:s", time()) . " GMT\n";
105 echo "# Nucleus CMS version: " . $nucleus['version'] . "\n";
107 echo "# WARNING: Only try to restore on servers running the exact same version of Nucleus\n";
112 array_walk($tables, '_backup_dump_table');
116 $Size = ob_get_length();
117 $Crc = crc32(ob_get_contents());
118 $contents = gzcompress(ob_get_contents());
120 echo "\x1f\x8b\x08\x00\x00\x00\x00\x00".substr($contents, 0, strlen($contents) - 4).gzip_PrintFourChars($Crc).gzip_PrintFourChars($Size);
129 * Creates a dump for a single table
130 * ($tablename and $key are filled in by array_walk)
132 function _backup_dump_table($tablename, $key) {
135 echo "# TABLE: " . $tablename . "\n";
138 // dump table structure
139 _backup_dump_structure($tablename);
141 // dump table contents
142 _backup_dump_contents($tablename);
145 function _backup_dump_structure($tablename) {
147 // add command to drop table on restore
148 echo "DROP TABLE IF EXISTS $tablename;\n";
149 echo "CREATE TABLE $tablename(\n";
152 // Ok lets grab the fields...
154 $result = mysql_query("SHOW FIELDS FROM $tablename");
155 $row = mysql_fetch_array($result);
158 echo ' ' . $row['Field'] . ' ' . $row['Type'];
160 if(isset($row['Default']))
161 echo ' DEFAULT \'' . $row['Default'] . '\'';
163 if($row['Null'] != "YES")
166 if($row['Extra'] != "")
167 echo ' ' . $row['Extra'];
169 $row = mysql_fetch_array($result);
171 // add comma's except for last one
177 // Get any Indexed fields from the database...
179 $result = mysql_query("SHOW KEYS FROM $tablename");
180 while($row = mysql_fetch_array($result)) {
181 $kname = $row['Key_name'];
183 if(($kname != 'PRIMARY') && ($row['Non_unique'] == 0))
184 $kname = "UNIQUE|$kname";
185 if(($kname != 'PRIMARY') && ($row['Index_type'] == 'FULLTEXT'))
186 $kname = "FULLTEXT|$kname";
188 if(!is_array($index[$kname]))
189 $index[$kname] = array();
191 $index[$kname][] = $row['Column_name'] . ( ($row['Sub_part']) ? ' (' . $row['Sub_part'] . ')' : '');
194 while(list($x, $columns) = @each($index)) {
198 echo ' PRIMARY KEY (' . implode($columns, ', ') . ')';
199 elseif (substr($x,0,6) == 'UNIQUE')
200 echo ' UNIQUE KEY ' . substr($x,7) . ' (' . implode($columns, ', ') . ')';
201 elseif (substr($x,0,8) == 'FULLTEXT')
202 echo ' FULLTEXT KEY ' . substr($x,9) . ' (' . implode($columns, ', ') . ')';
203 elseif (($x == 'ibody') || ($x == 'cbody')) // karma 2004-05-30 quick and dirty fix. fulltext keys were not in SQL correctly.
204 echo ' FULLTEXT KEY ' . substr($x,9) . ' (' . implode($columns, ', ') . ')';
206 echo " KEY $x (" . implode($columns, ', ') . ')';
213 * Returns the field named for the given table in the
216 * (column1, column2, ..., columnn)
218 function _backup_get_field_names($result, $num_fields) {
220 /* if (function_exists('mysqli_fetch_fields') ) {
222 $fields = mysqli_fetch_fields($result);
223 for ($j = 0; $j < $num_fields; $j++)
224 $fields[$j] = $fields[$j]->name;
229 for ($j = 0; $j < $num_fields; $j++) {
230 $fields[] = mysql_field_name($result, $j);
235 return '(' . implode(', ', $fields) . ')';
238 function _backup_dump_contents($tablename) {
240 // Grab the data from the table.
242 $result = mysql_query("SELECT * FROM $tablename");
244 if(mysql_num_rows($result) > 0)
245 echo "\n#\n# Table Data for $tablename\n#\n";
247 $num_fields = mysql_num_fields($result);
250 // Compose fieldname list
252 $tablename_list = _backup_get_field_names($result, $num_fields);
255 // Loop through the resulting rows and build the sql statement.
257 while ($row = mysql_fetch_array($result))
259 // Start building the SQL statement.
261 echo "INSERT INTO $tablename $tablename_list VALUES(";
263 // Loop through the rows and fill in data for each column
264 for ($j = 0; $j < $num_fields; $j++) {
265 if(!isset($row[$j])) {
266 // no data for column
268 } elseif ($row[$j] != '') {
270 echo " '" . addslashes($row[$j]) . "'";
272 // empty column (!= no data!)
276 // only add comma when not last column
277 if ($j != ($num_fields - 1))
291 function gzip_PrintFourChars($Val)
293 for ($i = 0; $i < 4; $i ++)
295 $return .= chr($Val % 256);
296 $Val = floor($Val / 256);
301 function do_restore() {
303 $uploadInfo = postFileInfo('backup_file');
305 // first of all: get uploaded file:
306 if (empty($uploadInfo['name']))
307 return 'No file uploaded';
308 if (!is_uploaded_file($uploadInfo['tmp_name']))
309 return 'No file uploaded';
311 $backup_file_name = $uploadInfo['name'];
312 $backup_file_tmpname = $uploadInfo['tmp_name'];
313 $backup_file_type = $uploadInfo['type'];
315 if (!file_exists($backup_file_tmpname))
316 return 'File Upload Error';
318 if (!preg_match("/^(text\/[a-zA-Z]+)|(application\/(x\-)?gzip(\-compressed)?)|(application\/octet-stream)$/is", $backup_file_type) )
319 return 'The uploaded file is not of the correct type';
323 if (preg_match("/\.gz/is",$backup_file_name))
328 if (!extension_loaded("zlib") && $gzip)
329 return "Cannot decompress gzipped backup (zlib package not installed)";
331 // get sql query according to gzip setting (either decompress, or not)
334 // decompress and read
335 $gz_ptr = gzopen($backup_file_tmpname, 'rb');
337 while( !gzeof($gz_ptr) )
338 $sql_query .= gzgets($gz_ptr, 100000);
341 $fsize = filesize($backup_file_tmpname);
345 $sql_query = fread(fopen($backup_file_tmpname, 'r'), $fsize);
348 // time to execute the query
349 _execute_queries($sql_query);
352 function _execute_queries($sql_query) {
353 if (!$sql_query) return;
355 // Strip out sql comments...
356 $sql_query = remove_remarks($sql_query);
357 $pieces = split_sql_file($sql_query);
359 $sql_count = count($pieces);
360 for($i = 0; $i < $sql_count; $i++)
362 $sql = trim($pieces[$i]);
364 if(!empty($sql) and $sql[0] != "#")
367 // debug("Executing: " . htmlspecialchars($sql) . "\n");
369 $result = mysql_query($sql);
370 if (!$result) debug('SQL Error: ' . mysql_error());
378 // remove_remarks will strip the sql comment lines out of an uploaded sql file
380 function remove_remarks($sql)
382 $lines = explode("\n", $sql);
384 // try to keep mem. use down
387 $linecount = count($lines);
390 for ($i = 0; $i < $linecount; $i++)
392 if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
394 if ($lines[$i][0] != "#")
396 $output .= $lines[$i] . "\n";
402 // Trading a bit of speed for lower mem. use here.
413 // split_sql_file will split an uploaded sql file into single sql statements.
414 // Note: expects trim() to have already been run on $sql.
418 function split_sql_file($sql)
420 // Split up our string into "possible" SQL statements.
421 $tokens = explode( ";", $sql);
427 // we don't actually care about the matches preg gives us.
430 // this is faster than calling count($tokens) every time thru the loop.
431 $token_count = count($tokens);
432 for ($i = 0; $i < $token_count; $i++)
434 // Don't wanna add an empty string as the last thing in the array.
435 if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
438 // even number of quotes means a complete SQL statement
439 if (_evenNumberOfQuotes($tokens[$i]))
441 $output[] = $tokens[$i];
442 $tokens[$i] = ""; // save memory.
446 // incomplete sql statement. keep adding tokens until we have a complete one.
447 // $temp will hold what we have so far.
448 $temp = $tokens[$i] . ";";
449 $tokens[$i] = ""; // save memory..
451 // Do we have a complete statement yet?
452 $complete_stmt = false;
454 for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
456 // odd number of quotes means a completed statement
457 // (in combination with the odd number we had already)
458 if (!_evenNumberOfQuotes($tokens[$j]))
460 $output[] = $temp . $tokens[$j];
467 $complete_stmt = true;
468 // make sure the outer loop continues at the right point.
473 // even number of unescaped quotes. We still don't have a complete statement.
474 // (1 odd and 1 even always make an odd)
475 $temp .= $tokens[$j] . ";";
489 function _evenNumberOfQuotes($text) {
490 // This is the total number of single quotes in the token.
491 $total_quotes = preg_match_all("/'/", $text, $matches);
492 // Counts single quotes that are preceded by an odd number of backslashes,
493 // which means they're escaped quotes.
494 $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $text, $matches);
496 $unescaped_quotes = $total_quotes - $escaped_quotes;
497 // debug($total_quotes . "-" . $escaped_quotes . "-" . $unescaped_quotes);
498 return (($unescaped_quotes % 2) == 0);