3 * Nucleus: PHP/MySQL Weblog CMS (http://nucleuscms.org/)
\r
4 * Copyright (C) 2002-2013 The Nucleus Group
\r
6 * This program is free software; you can redistribute it and/or
\r
7 * modify it under the terms of the GNU General Public License
\r
8 * as published by the Free Software Foundation; either version 2
\r
9 * of the License, or (at your option) any later version.
\r
10 * (see nucleus/documentation/index.html#license for more info)
\r
12 * Scripts to create/restore a backup of the Nucleus database
\r
14 * Based on code in phpBB (http://phpBB.sourceforge.net)
\r
30 * This function creates an sql dump of the database and sends it to
\r
31 * the user as a file (can be gzipped if they want)
\r
34 * no output may have preceded (new headers are sent)
\r
36 * 1 = compress backup file, 0 = no compression (default)
\r
38 function do_backup($gzip = 0) {
\r
41 // tables of which backup is needed
\r
43 sql_table('actionlog'),
\r
46 sql_table('comment'),
\r
47 sql_table('config'),
\r
50 sql_table('member'),
\r
52 sql_table('skin_desc'),
\r
54 sql_table('template'),
\r
55 sql_table('template_desc'),
\r
56 sql_table('plugin'),
\r
57 sql_table('plugin_event'),
\r
58 sql_table('plugin_option'),
\r
59 sql_table('plugin_option_desc'),
\r
60 sql_table('category'),
\r
61 sql_table('activation'),
\r
62 sql_table('tickets'),
\r
65 // add tables that plugins want to backup to the list
\r
66 // catch all output generated by plugins
\r
68 $res = sql_query('SELECT pfile FROM '.sql_table('plugin'));
\r
69 while ($plugName = sql_fetch_object($res)) {
\r
70 $plug =& $manager->getPlugin($plugName->pfile);
\r
71 if ($plug) $tables = array_merge($tables, (array) $plug->getTableList());
\r
75 // remove duplicates
\r
76 $tables = array_unique($tables);
\r
78 // make sure browsers don't cache the backup
\r
79 header("Pragma: no-cache");
\r
81 // don't allow gzip compression when extension is not loaded
\r
82 if (($gzip != 0) && !extension_loaded("zlib")) {
\r
87 // use an output buffer
\r
89 @ob_implicit_flush(0);
\r
92 $filename = 'nucleus_db_backup_'.strftime("%Y-%m-%d-%H-%M-%S", time()).".sql.gz";
\r
94 $filename = 'nucleus_db_backup_'.strftime("%Y-%m-%d-%H-%M-%S", time()).".sql";
\r
98 // send headers that tell the browser a file is coming
\r
99 header("Content-Type: text/x-delimtext; name=\"$filename\"");
\r
100 header("Content-disposition: attachment; filename=$filename");
\r
104 echo "# " . _BACKUP_BACKUPFILE_TITLE . " \n";
\r
105 echo "# " . _ADMINPAGEFOOT_OFFICIALURL . "\n";
\r
107 echo "# " . _BACKUP_BACKUPFILE_BACKUPDATE . gmdate("d-m-Y H:i:s", time()) . " GMT\n";
\r
109 echo "# " . _BACKUP_BACKUPFILE_NUCLEUSVERSION . $nucleus['version'] . "\n";
\r
111 echo "# " . _BACKUP_WARNING_NUCLEUSVERSION . "\n";
\r
116 array_walk($tables, array(&$this, '_backup_dump_table'));
\r
119 $Size = ob_get_length();
\r
120 $Crc = crc32(ob_get_contents());
\r
121 $contents = gzcompress(ob_get_contents());
\r
123 echo "\x1f\x8b\x08\x00\x00\x00\x00\x00".substr($contents, 0, strlen($contents) - 4).$this->gzip_PrintFourChars($Crc).$this->gzip_PrintFourChars($Size);
\r
132 * Creates a dump for a single table
\r
133 * ($tablename and $key are filled in by array_walk)
\r
135 function _backup_dump_table($tablename, $key) {
\r
138 echo "# " . _BACKUP_BACKUPFILE_TABLE_NAME . $tablename . "\n";
\r
141 // dump table structure
\r
142 $this->_backup_dump_structure($tablename);
\r
144 // dump table contents
\r
145 $this->_backup_dump_contents($tablename);
\r
149 * Creates a dump of the table structure for one table
\r
151 function _backup_dump_structure($tablename) {
\r
153 // add command to drop table on restore
\r
154 echo "DROP TABLE IF EXISTS $tablename;\n";
\r
155 $result = sql_query("SHOW CREATE TABLE $tablename");
\r
156 $create = sql_fetch_assoc($result);
\r
157 echo $create['Create Table'];
\r
162 * Creates a dump of the table structure for one table
\r
164 /* replaced by code above in 3.5
\r
165 function _backup_dump_structure($tablename) {
\r
167 // add command to drop table on restore
\r
168 echo "DROP TABLE IF EXISTS $tablename;\n";
\r
169 echo "CREATE TABLE $tablename(\n";
\r
172 // Ok lets grab the fields...
\r
174 $result = mysql_query("SHOW FIELDS FROM $tablename");
\r
175 $row = mysql_fetch_array($result);
\r
178 echo ' `' . $row['Field'] . '` ' . $row['Type'];
\r
180 if(isset($row['Default']))
\r
181 echo ' DEFAULT \'' . $row['Default'] . '\'';
\r
183 if($row['Null'] != "YES")
\r
186 if($row['Extra'] != "")
\r
187 echo ' ' . $row['Extra'];
\r
189 $row = mysql_fetch_array($result);
\r
191 // add comma's except for last one
\r
197 // Get any Indexed fields from the database...
\r
199 $result = mysql_query("SHOW KEYS FROM $tablename");
\r
200 while($row = mysql_fetch_array($result)) {
\r
201 $kname = $row['Key_name'];
\r
203 if(($kname != 'PRIMARY') && ($row['Non_unique'] == 0))
\r
204 $kname = "UNIQUE|$kname";
\r
205 if(($kname != 'PRIMARY') && ($row['Index_type'] == 'FULLTEXT'))
\r
206 $kname = "FULLTEXT|$kname";
\r
208 if(!is_array($index[$kname]))
\r
209 $index[$kname] = array();
\r
211 $index[$kname][] = $row['Column_name'] . ( ($row['Sub_part']) ? ' (' . $row['Sub_part'] . ')' : '');
\r
214 while(list($x, $columns) = @each($index)) {
\r
217 if($x == 'PRIMARY')
\r
218 echo ' PRIMARY KEY (`' . implode($columns, '`, `') . '`)';
\r
219 elseif (substr($x,0,6) == 'UNIQUE')
\r
220 echo ' UNIQUE KEY ' . substr($x,7) . ' (`' . implode($columns, '`, `') . '`)';
\r
221 elseif (substr($x,0,8) == 'FULLTEXT')
\r
222 echo ' FULLTEXT KEY ' . substr($x,9) . ' (`' . implode($columns, '`, `') . '`)';
\r
223 elseif (($x == 'ibody') || ($x == 'cbody')) // karma 2004-05-30 quick and dirty fix. fulltext keys were not in SQL correctly.
\r
224 echo ' FULLTEXT KEY ' . substr($x,9) . ' (`' . implode($columns, '`, `') . '`)';
\r
226 echo " KEY $x (`" . implode($columns, '`, `') . '`)';
\r
234 * Returns the field named for the given table in the
\r
235 * following format:
\r
237 * (column1, column2, ..., columnn)
\r
239 function _backup_get_field_names($result, $num_fields) {
\r
241 /* if (function_exists('mysqli_fetch_fields') ) {
\r
243 $fields = mysqli_fetch_fields($result);
\r
244 for ($j = 0; $j < $num_fields; $j++)
\r
245 $fields[$j] = $fields[$j]->name;
\r
250 for ($j = 0; $j < $num_fields; $j++) {
\r
251 $fields[] = sql_field_name($result, $j);
\r
256 return '(`' . implode('`, `', $fields) . '`)';
\r
260 * Creates a dump of the table content for one table
\r
262 function _backup_dump_contents($tablename) {
\r
264 // Grab the data from the table.
\r
266 $result = sql_query("SELECT * FROM $tablename");
\r
268 if(sql_num_rows($result) > 0)
\r
269 echo "\n#\n# " . sprintf(_BACKUP_BACKUPFILE_TABLEDATAFOR, $tablename) . "\n#\n";
\r
271 $num_fields = sql_num_fields($result);
\r
274 // Compose fieldname list
\r
276 $tablename_list = $this->_backup_get_field_names($result, $num_fields);
\r
279 // Loop through the resulting rows and build the sql statement.
\r
281 while ($row = sql_fetch_array($result))
\r
283 // Start building the SQL statement.
\r
285 echo "INSERT INTO `".$tablename."` $tablename_list VALUES(";
\r
287 // Loop through the rows and fill in data for each column
\r
288 for ($j = 0; $j < $num_fields; $j++) {
\r
289 if(!isset($row[$j])) {
\r
290 // no data for column
\r
292 } elseif ($row[$j] != '') {
\r
294 echo " '" . sql_real_escape_string($row[$j]) . "'";
\r
296 // empty column (!= no data!)
\r
300 // only add comma when not last column
\r
301 if ($j != ($num_fields - 1))
\r
315 * copied from phpBB
\r
317 function gzip_PrintFourChars($Val)
\r
319 for ($i = 0; $i < 4; $i ++)
\r
321 $return .= chr($Val % 256);
\r
322 $Val = floor($Val / 256);
\r
328 * Restores a database backup
\r
330 function do_restore() {
\r
332 $uploadInfo = postFileInfo('backup_file');
\r
334 // first of all: get uploaded file:
\r
335 if (empty($uploadInfo['name']))
\r
336 return _BACKUP_RESTOR_NOFILEUPLOADED;
\r
337 if (!is_uploaded_file($uploadInfo['tmp_name']))
\r
338 return _BACKUP_RESTOR_NOFILEUPLOADED;
\r
340 $backup_file_name = $uploadInfo['name'];
\r
341 $backup_file_tmpname = $uploadInfo['tmp_name'];
\r
342 $backup_file_type = $uploadInfo['type'];
\r
344 if (!file_exists($backup_file_tmpname))
\r
345 return _BACKUP_RESTOR_UPLOAD_ERROR;
\r
347 if (!preg_match("/^(text\/[a-zA-Z]+)|(application\/(x\-)?gzip(\-compressed)?)|(application\/octet-stream)$/is", $backup_file_type) )
\r
348 return _BACKUP_RESTOR_UPLOAD_NOCORRECTTYPE;
\r
351 if (preg_match("/\.gz/is",$backup_file_name))
\r
356 if (!extension_loaded("zlib") && $gzip)
\r
357 return _BACKUP_RESTOR_UPLOAD_NOZLIB;
\r
359 // get sql query according to gzip setting (either decompress, or not)
\r
362 // decompress and read
\r
363 $gz_ptr = gzopen($backup_file_tmpname, 'rb');
\r
365 while( !gzeof($gz_ptr) )
\r
366 $sql_query .= gzgets($gz_ptr, 100000);
\r
369 $fsize = filesize($backup_file_tmpname);
\r
373 $sql_query = fread(fopen($backup_file_tmpname, 'r'), $fsize);
\r
376 // time to execute the query
\r
377 $this->_execute_queries($sql_query);
\r
381 * Executes a SQL query
\r
383 function _execute_queries($sql_query) {
\r
384 if (!$sql_query) return;
\r
386 // Strip out sql comments...
\r
387 $sql_query = $this->remove_remarks($sql_query);
\r
388 $pieces = $this->split_sql_file($sql_query);
\r
390 $sql_count = count($pieces);
\r
391 for($i = 0; $i < $sql_count; $i++)
\r
393 $sql = trim($pieces[$i]);
\r
395 if(!empty($sql) and $sql[0] != "#")
\r
398 // debug("Executing: " . htmlspecialchars($sql) . "\n");
\r
400 $result = sql_query($sql);
\r
401 if (!$result) debug(_BACKUP_RESTOR_SQL_ERROR . sql_error());
\r
409 * remove_remarks will strip the sql comment lines
\r
410 * out of an uploaded sql file
\r
412 function remove_remarks($sql)
\r
414 $lines = explode("\n", $sql);
\r
416 // try to keep mem. use down
\r
419 $linecount = count($lines);
\r
422 for ($i = 0; $i < $linecount; $i++)
\r
424 if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
\r
426 if ($lines[$i][0] != "#")
\r
428 $output .= $lines[$i] . "\n";
\r
434 // Trading a bit of speed for lower mem. use here.
\r
444 * split_sql_file will split an uploaded sql file
\r
445 * into single sql statements.
\r
447 * Note: expects trim() to have already been run on $sql.
\r
450 function split_sql_file($sql)
\r
452 // Split up our string into "possible" SQL statements.
\r
453 $tokens = explode( ";", $sql);
\r
455 // try to save mem.
\r
459 // we don't actually care about the matches preg gives us.
\r
460 $matches = array();
\r
462 // this is faster than calling count($tokens) every time thru the loop.
\r
463 $token_count = count($tokens);
\r
464 for ($i = 0; $i < $token_count; $i++)
\r
466 // Don't wanna add an empty string as the last thing in the array.
\r
467 if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
\r
470 // even number of quotes means a complete SQL statement
\r
471 if ($this->_evenNumberOfQuotes($tokens[$i]))
\r
473 $output[] = $tokens[$i];
\r
474 $tokens[$i] = ""; // save memory.
\r
478 // incomplete sql statement. keep adding tokens until we have a complete one.
\r
479 // $temp will hold what we have so far.
\r
480 $temp = $tokens[$i] . ";";
\r
481 $tokens[$i] = ""; // save memory..
\r
483 // Do we have a complete statement yet?
\r
484 $complete_stmt = false;
\r
486 for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
\r
488 // odd number of quotes means a completed statement
\r
489 // (in combination with the odd number we had already)
\r
490 if (!$this->_evenNumberOfQuotes($tokens[$j]))
\r
492 $output[] = $temp . $tokens[$j];
\r
499 $complete_stmt = true;
\r
500 // make sure the outer loop continues at the right point.
\r
505 // even number of unescaped quotes. We still don't have a complete statement.
\r
506 // (1 odd and 1 even always make an odd)
\r
507 $temp .= $tokens[$j] . ";";
\r
521 * sub function of split_sql_file
\r
525 function _evenNumberOfQuotes($text) {
\r
526 // This is the total number of single quotes in the token.
\r
527 $total_quotes = preg_match_all("/'/", $text, $matches);
\r
528 // Counts single quotes that are preceded by an odd number of backslashes,
\r
529 // which means they're escaped quotes.
\r
530 $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $text, $matches);
\r
532 $unescaped_quotes = $total_quotes - $escaped_quotes;
\r
533 // debug($total_quotes . "-" . $escaped_quotes . "-" . $unescaped_quotes);
\r
534 return (($unescaped_quotes % 2) == 0);
\r