3 * Scripts to create/restore a backup of the Nucleus database
\r
5 * Based on code in phpBB (http://phpBB.sourceforge.net)
\r
10 * This function creates an sql dump of the database and sends it to
\r
11 * the user as a file (can be gzipped if they want)
\r
14 * no output may have preceded (new headers are sent)
\r
16 * 1 = compress backup file, 0 = no compression (default)
\r
18 function do_backup($gzip = 0) {
\r
21 // tables of which backup is needed
\r
23 sql_table('actionlog'),
\r
26 sql_table('comment'),
\r
27 sql_table('config'),
\r
30 sql_table('member'),
\r
32 sql_table('skin_desc'),
\r
34 sql_table('template'),
\r
35 sql_table('template_desc'),
\r
36 sql_table('plugin'),
\r
37 sql_table('plugin_event'),
\r
38 sql_table('plugin_option'),
\r
39 sql_table('plugin_option_desc'),
\r
40 sql_table('category')
\r
43 // add tables that plugins want to backup to the list
\r
44 // catch all output generated by plugins
\r
46 $res = sql_query('SELECT pfile FROM '.sql_table('plugin'));
\r
47 while ($plugName = mysql_fetch_object($res)) {
\r
48 $plug =& $manager->getPlugin($plugName->pfile);
\r
49 if ($plug) $tables = array_merge($tables, $plug->getTableList());
\r
53 // remove duplicates
\r
54 $tables = array_unique($tables);
\r
56 // make sure browsers don't cache the backup
\r
57 header("Pragma: no-cache");
\r
59 // don't allow gzip compression when extension is not loaded
\r
60 if (($gzip != 0) && !extension_loaded("zlib"))
\r
66 // use an output buffer
\r
68 @ob_implicit_flush(0);
\r
71 $filename = 'nucleus_db_backup_'.strftime("%Y%m%d", time()).".sql.gz";
\r
73 $filename = 'nucleus_db_backup_'.strftime("%Y%m%d", time()).".sql";
\r
77 // send headers that tell the browser a file is coming
\r
78 header("Content-Type: text/x-delimtext; name=\"$filename\"");
\r
79 header("Content-disposition: attachment; filename=$filename");
\r
83 echo "# This is a backup file generated by Nucleus \n";
\r
84 echo "# http://www.nucleuscms.org/\n";
\r
86 echo "# backup-date: " . gmdate("d-m-Y H:i:s", time()) . " GMT\n";
\r
88 echo "# nucleus version: " . $nucleus['version'] . "\n";
\r
90 echo "# WARNING: Only try to restore on servers running the exact same version of Nucleus\n";
\r
95 array_walk($tables, '_backup_dump_table');
\r
99 $Size = ob_get_length();
\r
100 $Crc = crc32(ob_get_contents());
\r
101 $contents = gzcompress(ob_get_contents());
\r
103 echo "\x1f\x8b\x08\x00\x00\x00\x00\x00".substr($contents, 0, strlen($contents) - 4).gzip_PrintFourChars($Crc).gzip_PrintFourChars($Size);
\r
112 * Creates a dump for a single table
\r
113 * ($tablename and $key are filled in by array_walk)
\r
115 function _backup_dump_table($tablename, $key) {
\r
118 echo "# TABLE: " . $tablename . "\n";
\r
121 // dump table structure
\r
122 _backup_dump_structure($tablename);
\r
124 // dump table contents
\r
125 _backup_dump_contents($tablename);
\r
128 function _backup_dump_structure($tablename) {
\r
130 // add command to drop table on restore
\r
131 echo "DROP TABLE IF EXISTS $tablename;\n";
\r
132 echo "CREATE TABLE $tablename(\n";
\r
135 // Ok lets grab the fields...
\r
137 $result = mysql_query("SHOW FIELDS FROM $tablename");
\r
138 $row = mysql_fetch_array($result);
\r
141 echo ' ' . $row['Field'] . ' ' . $row['Type'];
\r
143 if(!empty($row['Default']))
\r
144 echo ' DEFAULT \'' . $row['Default'] . '\'';
\r
146 if($row['Null'] != "YES")
\r
149 if($row['Extra'] != "")
\r
150 echo ' ' . $row['Extra'];
\r
152 $row = mysql_fetch_array($result);
\r
154 // add comma's except for last one
\r
160 // Get any Indexed fields from the database...
\r
162 $result = mysql_query("SHOW KEYS FROM $tablename");
\r
163 while($row = mysql_fetch_array($result)) {
\r
164 $kname = $row['Key_name'];
\r
166 if(($kname != 'PRIMARY') && ($row['Non_unique'] == 0))
\r
167 $kname = "UNIQUE|$kname";
\r
168 if(($kname != 'PRIMARY') && ($row['Index_type'] == 'FULLTEXT'))
\r
169 $kname = "FULLTEXT|$kname";
\r
171 if(!is_array($index[$kname]))
\r
172 $index[$kname] = array();
\r
174 $index[$kname][] = $row['Column_name'];
\r
177 while(list($x, $columns) = @each($index)) {
\r
180 if($x == 'PRIMARY')
\r
181 echo ' PRIMARY KEY (' . implode($columns, ', ') . ')';
\r
182 elseif (substr($x,0,6) == 'UNIQUE')
\r
183 echo ' UNIQUE KEY ' . substr($x,7) . ' (' . implode($columns, ', ') . ')';
\r
184 elseif (substr($x,0,8) == 'FULLTEXT')
\r
185 echo ' FULLTEXT KEY ' . substr($x,9) . ' (' . implode($columns, ', ') . ')';
\r
186 elseif (($x == 'ibody') || ($x == 'cbody')) // karma 2004-05-30 quick and dirty fix. fulltext keys were not in SQL correctly.
\r
187 echo ' FULLTEXT KEY ' . substr($x,9) . ' (' . implode($columns, ', ') . ')';
\r
189 echo " KEY $x (" . implode($columns, ', ') . ')';
\r
195 function _backup_dump_contents($tablename) {
\r
197 // Grab the data from the table.
\r
199 $result = mysql_query("SELECT * FROM $tablename");
\r
201 if(mysql_numrows($result) > 0)
\r
202 echo "\n#\n# Table Data for $tablename\n#\n";
\r
205 // Loop through the resulting rows and build the sql statement.
\r
207 while ($row = mysql_fetch_array($result))
\r
209 $tablename_list = '(';
\r
210 $num_fields = mysql_num_fields($result);
\r
213 // Grab the list of field names.
\r
215 for ($j = 0; $j < $num_fields; $j++)
\r
216 $tablename_list .= mysql_field_name($result, $j) . ', ';
\r
219 // Get rid of the last comma
\r
221 $tablename_list = ereg_replace(', $', '', $tablename_list);
\r
222 $tablename_list .= ')';
\r
224 // Start building the SQL statement.
\r
226 echo "INSERT INTO $tablename $tablename_list VALUES(";
\r
228 // Loop through the rows and fill in data for each column
\r
229 for ($j = 0; $j < $num_fields; $j++) {
\r
230 if(!isset($row[$j])) {
\r
231 // no data for column
\r
233 } elseif ($row[$j] != '') {
\r
235 echo " '" . addslashes($row[$j]) . "'";
\r
237 // empty column (!= no data!)
\r
241 // only add comma when not last column
\r
242 if ($j != ($num_fields - 1))
\r
255 // copied from phpBB
\r
256 function gzip_PrintFourChars($Val)
\r
258 for ($i = 0; $i < 4; $i ++)
\r
260 $return .= chr($Val % 256);
\r
261 $Val = floor($Val / 256);
\r
266 function do_restore() {
\r
268 $uploadInfo = postFileInfo('backup_file');
\r
270 // first of all: get uploaded file:
\r
271 if (empty($uploadInfo['name']))
\r
272 return 'No file uploaded';
\r
273 if (!is_uploaded_file($uploadInfo['tmp_name']))
\r
274 return 'No file uploaded';
\r
276 $backup_file_name = $uploadInfo['name'];
\r
277 $backup_file_tmpname = $uploadInfo['tmp_name'];
\r
278 $backup_file_type = $uploadInfo['backup_file']['type'];
\r
280 if (!file_exists($backup_file_tmpname))
\r
281 return 'File Upload Error';
\r
283 if (!preg_match("/^(text\/[a-zA-Z]+)|(application\/(x\-)?gzip(\-compressed)?)|(application\/octet-stream)$/is", $backup_file_type) )
\r
284 return 'The uploaded file is not of the correct type';
\r
286 if (preg_match("/\.gz/is",$backup_file_name))
\r
291 if (!extension_loaded("zlib") && $gzip)
\r
292 return "Cannot decompress gzipped backup (zlib package not installed)";
\r
294 // get sql query according to gzip setting (either decompress, or not)
\r
297 // decompress and read
\r
298 $gz_ptr = gzopen($backup_file_tmpname, 'rb');
\r
300 while( !gzeof($gz_ptr) )
\r
301 $sql_query .= gzgets($gz_ptr, 100000);
\r
304 $fsize = filesize($backup_file_tmpname);
\r
308 $sql_query = fread(fopen($backup_file_tmpname, 'r'), $fsize);
\r
311 // time to execute the query
\r
312 _execute_queries($sql_query);
\r
315 function _execute_queries($sql_query) {
\r
316 if (!$sql_query) return;
\r
318 // Strip out sql comments...
\r
319 $sql_query = remove_remarks($sql_query);
\r
320 $pieces = split_sql_file($sql_query);
\r
322 $sql_count = count($pieces);
\r
323 for($i = 0; $i < $sql_count; $i++)
\r
325 $sql = trim($pieces[$i]);
\r
327 if(!empty($sql) and $sql[0] != "#")
\r
330 // debug("Executing: " . htmlspecialchars($sql) . "\n");
\r
332 $result = mysql_query($sql);
\r
333 if (!$result) debug("SQL Error: " + mysql_error());
\r
341 // remove_remarks will strip the sql comment lines out of an uploaded sql file
\r
343 function remove_remarks($sql)
\r
345 $lines = explode("\n", $sql);
\r
347 // try to keep mem. use down
\r
350 $linecount = count($lines);
\r
353 for ($i = 0; $i < $linecount; $i++)
\r
355 if (($i != ($linecount - 1)) || (strlen($lines[$i]) > 0))
\r
357 if ($lines[$i][0] != "#")
\r
359 $output .= $lines[$i] . "\n";
\r
365 // Trading a bit of speed for lower mem. use here.
\r
376 // split_sql_file will split an uploaded sql file into single sql statements.
\r
377 // Note: expects trim() to have already been run on $sql.
\r
379 // taken from phpBB
\r
381 function split_sql_file($sql)
\r
383 // Split up our string into "possible" SQL statements.
\r
384 $tokens = explode( ";", $sql);
\r
386 // try to save mem.
\r
390 // we don't actually care about the matches preg gives us.
\r
391 $matches = array();
\r
393 // this is faster than calling count($tokens) every time thru the loop.
\r
394 $token_count = count($tokens);
\r
395 for ($i = 0; $i < $token_count; $i++)
\r
397 // Don't wanna add an empty string as the last thing in the array.
\r
398 if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0)))
\r
401 // even number of quotes means a complete SQL statement
\r
402 if (_evenNumberOfQuotes($tokens[$i]))
\r
404 $output[] = $tokens[$i];
\r
405 $tokens[$i] = ""; // save memory.
\r
409 // incomplete sql statement. keep adding tokens until we have a complete one.
\r
410 // $temp will hold what we have so far.
\r
411 $temp = $tokens[$i] . ";";
\r
412 $tokens[$i] = ""; // save memory..
\r
414 // Do we have a complete statement yet?
\r
415 $complete_stmt = false;
\r
417 for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++)
\r
419 // odd number of quotes means a completed statement
\r
420 // (in combination with the odd number we had already)
\r
421 if (!_evenNumberOfQuotes($tokens[$j]))
\r
423 $output[] = $temp . $tokens[$j];
\r
430 $complete_stmt = true;
\r
431 // make sure the outer loop continues at the right point.
\r
436 // even number of unescaped quotes. We still don't have a complete statement.
\r
437 // (1 odd and 1 even always make an odd)
\r
438 $temp .= $tokens[$j] . ";";
\r
452 function _evenNumberOfQuotes($text) {
\r
453 // This is the total number of single quotes in the token.
\r
454 $total_quotes = preg_match_all("/'/", $text, $matches);
\r
455 // Counts single quotes that are preceded by an odd number of backslashes,
\r
456 // which means they're escaped quotes.
\r
457 $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $text, $matches);
\r
459 $unescaped_quotes = $total_quotes - $escaped_quotes;
\r
460 // debug($total_quotes . "-" . $escaped_quotes . "-" . $unescaped_quotes);
\r
461 return (($unescaped_quotes % 2) == 0);
\r