== DB Export ==

ExportDB : as i'm discovering Wikki (and WikkaWiki) and playing with it on my main server and on my laptop (which too runs an Apache installation), I
was needing a quickly way of making export of my database. So I wrote a quick (and dirty ...) export page to get all the replace SQL commands to populate an instance.
I too may use this in the future to quickly backup an online Wikki site.

Code is borrowed from phpMyAdmin, but I wanted something as simple as possible (datas are just one link away). It's easier for me to use this page rather than switching on phpMyAdmin.

Just in case someone would need this:
~-You have to add an **exportdb.php** file in your "actions/" directory (with the code above)
~-Add the **dump_database.xml.php** file in your "handlers/page/" directory
~-Add in your wikka.config.php the line %%"language" => "fr",%% anywhere
~-Finally put anywhere the %%{{ExportDB}}%% tag on any page (only admin can view it) ;

<<Code for **exportdb.php** (put this in actions/ )<<::c::
%%(php)
<?php

  // File : actions/exportdb.php
  // Written by SergiO

  $strings['fr']['S_EXPORT_LABEL'] = 'Export de la base de données';
  $strings['fr']['S_DOWNLOAD'] = 'Downloader';
  $strings['fr']['S_VIEW'] = 'Visualiser';
  $strings['fr']['S_NO_ADMIN'] = "Vous n'&ecirc;tes pas autoris&eacute; &agrave; lire cette page";
  

  // === Array set to current strings definitions with the current selected language
  //	(this array is to be used later like this : $lng_strings['STRING_LABEL'])
  $lng_strings = $strings[strtolower($this->GetConfigValue('language'))];

  // === Only admin can access this page
  if ($this->IsAdmin())
  {
	// === Begining of this script ...
	$result = "<form action=\"".$this->href('dump_database.xml',$this->GetPageTag(),"")."\" method=\"post\">\n";
	print "
	  <table cellpadding=\"0\" cellspacing=\"0\">
		<tr>
		  <td>
			<strong>$lng_strings[S_EXPORT_LABEL]</strong>&nbsp;:&nbsp;<br />
		  </td>
		  <td colspan='4' valign='top' align='right' nowrap>
			$result
			<input name=\"submit_download\" type=\"submit\" value=\" $lng_strings[S_DOWNLOAD] \" style=\"padding: 0px; margin: 0px; font-size: 10px\">
			<input name=\"submit_view\" type=\"submit\" value=\" $lng_strings[S_VIEW] \" style=\"padding: 0px; margin: 0px; font-size: 10px\">
			".$this->FormClose()."
		  </td>
		</tr>
	  </table>";
 }
else{
  echo "<i>" . $lng_strings[S_NO_ADMIN] . "</i>" ;
}

?>
%%

<<Code for **dump_database.xml.php** (put this in /handlers/page/) :<<::c::
%%(php)
<?php

  // File : handlers/page/dum_database.xml.php
  // Written by SergiO


  // === ZipFile class 

  /**
   * Zip file creation class.
   * Makes zip files.
   *
   * Based on :
   *
   *  http://www.zend.com/codex.php?id=535&single=1
   *  By Eric Mueller <eric@themepark.com>
   *
   *  http://www.zend.com/codex.php?id=470&single=1
   *  by Denis125 <webmaster@atlant.ru>
   *
   *  a patch from Peter Listiak <mlady@users.sourceforge.net> for last modified
   *  date and time of the compressed file
   *
   * Official ZIP file format: http://www.pkware.com/appnote.txt
   *
   * @access  public
   */
  class zipfile
  {
	  /**
	   * Array to store compressed data
	   *
	   * @var  array	$datasec
	   */
	  var $datasec	  = array();

	  /**
	   * Central directory
	   *
	   * @var  array	$ctrl_dir
	   */
	  var $ctrl_dir	 = array();

	  /**
	   * End of central directory record
	   *
	   * @var  string   $eof_ctrl_dir
	   */
	  var $eof_ctrl_dir = "\x50\x4b\x05\x06\x00\x00\x00\x00";

	  /**
	   * Last offset position
	   *
	   * @var  integer  $old_offset
	   */
	  var $old_offset   = 0;


	  /**
	   * Converts an Unix timestamp to a four byte DOS date and time format (date
	   * in high two bytes, time in low two bytes allowing magnitude comparison).
	   *
	   * @param  integer  the current Unix timestamp
	   *
	   * @return integer  the current date in a four byte DOS format
	   *
	   * @access private
	   */
	  function unix2DosTime($unixtime = 0) {
		  $timearray = ($unixtime == 0) ? getdate() : getdate($unixtime);

		  if ($timearray['year'] < 1980) {
			$timearray['year']	= 1980;
			$timearray['mon']	 = 1;
			$timearray['mday']	= 1;
			$timearray['hours']   = 0;
			$timearray['minutes'] = 0;
			$timearray['seconds'] = 0;
		  } // end if

		  return (($timearray['year'] - 1980) << 25) | ($timearray['mon'] << 21) | ($timearray['mday'] << 16) |
				  ($timearray['hours'] << 11) | ($timearray['minutes'] << 5) | ($timearray['seconds'] >> 1);
	  } // end of the 'unix2DosTime()' method


	  /**
	   * Adds "file" to archive
	   *
	   * @param  string   file contents
	   * @param  string   name of the file in the archive (may contains the path)
	   * @param  integer  the current timestamp
	   *
	   * @access public
	   */
	  function addFile($data, $name, $time = 0)
	  {
		  $name	 = str_replace('\\', '/', $name);

		  $dtime	= dechex($this->unix2DosTime($time));
		  $hexdtime = '\x' . $dtime[6] . $dtime[7]
					. '\x' . $dtime[4] . $dtime[5]
					. '\x' . $dtime[2] . $dtime[3]
					. '\x' . $dtime[0] . $dtime[1];
		  eval('$hexdtime = "' . $hexdtime . '";');

		  $fr   = "\x50\x4b\x03\x04";
		  $fr   .= "\x14\x00";			// ver needed to extract
		  $fr   .= "\x00\x00";			// gen purpose bit flag
		  $fr   .= "\x08\x00";			// compression method
		  $fr   .= $hexdtime;			 // last mod time and date

		  // "local file header" segment
		  $unc_len = strlen($data);
		  $crc	 = crc32($data);
		  $zdata   = gzcompress($data);
		  $zdata   = substr(substr($zdata, 0, strlen($zdata) - 4), 2); // fix crc bug
		  $c_len   = strlen($zdata);
		  $fr	  .= pack('V', $crc);			 // crc32
		  $fr	  .= pack('V', $c_len);		   // compressed filesize
		  $fr	  .= pack('V', $unc_len);		 // uncompressed filesize
		  $fr	  .= pack('v', strlen($name));	// length of filename
		  $fr	  .= pack('v', 0);				// extra field length
		  $fr	  .= $name;

		  // "file data" segment
		  $fr .= $zdata;

		  // "data descriptor" segment (optional but necessary if archive is not
		  // served as file)
		  $fr .= pack('V', $crc);				 // crc32
		  $fr .= pack('V', $c_len);			   // compressed filesize
		  $fr .= pack('V', $unc_len);			 // uncompressed filesize

		  // add this entry to array
		  $this -> datasec[] = $fr;
		  $new_offset		= strlen(implode('', $this->datasec));

		  // now add to central directory record
		  $cdrec = "\x50\x4b\x01\x02";
		  $cdrec .= "\x00\x00";				// version made by
		  $cdrec .= "\x14\x00";				// version needed to extract
		  $cdrec .= "\x00\x00";				// gen purpose bit flag
		  $cdrec .= "\x08\x00";				// compression method
		  $cdrec .= $hexdtime;				 // last mod time & date
		  $cdrec .= pack('V', $crc);		   // crc32
		  $cdrec .= pack('V', $c_len);		 // compressed filesize
		  $cdrec .= pack('V', $unc_len);	   // uncompressed filesize
		  $cdrec .= pack('v', strlen($name) ); // length of filename
		  $cdrec .= pack('v', 0 );			 // extra field length
		  $cdrec .= pack('v', 0 );			 // file comment length
		  $cdrec .= pack('v', 0 );			 // disk number start
		  $cdrec .= pack('v', 0 );			 // internal file attributes
		  $cdrec .= pack('V', 32 );			// external file attributes - 'archive' bit set

		  $cdrec .= pack('V', $this -> old_offset ); // relative offset of local header
		  $this -> old_offset = $new_offset;

		  $cdrec .= $name;

		  // optional extra field, file comment goes here
		  // save to central directory
		  $this -> ctrl_dir[] = $cdrec;
	  } // end of the 'addFile()' method


	  /**
	   * Dumps out file
	   *
	   * @return  string  the zipped file
	   *
	   * @access public
	   */
	  function file()
	  {
		  $data	= implode('', $this -> datasec);
		  $ctrldir = implode('', $this -> ctrl_dir);

		  return
			  $data .
			  $ctrldir .
			  $this -> eof_ctrl_dir .
			  pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries "on this disk"
			  pack('v', sizeof($this -> ctrl_dir)) .  // total # of entries overall
			  pack('V', strlen($ctrldir)) .		   // size of central dir
			  pack('V', strlen($data)) .			  // offset to start of central dir
			  "\x00\x00";							 // .zip file comment length
	  } // end of the 'file()' method

  } // end of the 'zipfile' class


  // === Fonctions borrowed (and adapted) from phpMyAdmin

  function backquote($a_name, $do_it = TRUE)
  {
	  if ($do_it
		  && !empty($a_name) && $a_name != '*') {

		  if (is_array($a_name)) {
			   $result = array();
			   reset($a_name);
			   while(list($key, $val) = each($a_name)) {
				   $result[$key] = '`' . $val . '`';
			   }
			   return $result;
		  } else {
			  return '`' . $a_name . '`';
		  }
	  } else {
		  return $a_name;
	  }
  } // end of the 'PMA_backquote()' function

  function sqlAddslashes($a_string = '', $is_like = FALSE, $crlf = FALSE)
  {
	  if ($is_like) {
		  $a_string = str_replace('\\', '\\\\\\\\', $a_string);
	  } else {
		  $a_string = str_replace('\\', '\\\\', $a_string);
	  }

	  if ($crlf) {
		  $a_string = str_replace("\n", '\n', $a_string);
		  $a_string = str_replace("\r", '\r', $a_string);
		  $a_string = str_replace("\t", '\t', $a_string);
	  }

	  $a_string = str_replace('\'', '\\\'', $a_string);

	  return $a_string;
  } // end of the 'PMA_sqlAddslashes()' function

  function fieldTypes($db_link, $table, $use_backquotes) {
	$table_def = mysql_query('SHOW FIELDS FROM ' . backquote($db) . '.' . backquote($table));
	while($row = @mysql_fetch_array($table_def)) {
		$types[backquote($row['Field'],$use_backquotes)] = ereg_replace('\\(.*', '', $row['Type']);
	}
	return $types;
  }

  function getTableContentFast($db_link, $table, $crlf, $parameter_insert_kind, &$datas)
  {
	$buffer = '';
	$use_backquotes = TRUE;

	$sql_query = 'SELECT * from ' . $table . ' ';

	$result	  = mysql_query($sql_query);
	if ($result != FALSE) {
		$fields_cnt = mysql_num_fields($result);
		$rows_cnt   = mysql_num_rows($result);

		// get the real types of the table's fields (in an array)
		// the key of the array is the backquoted field name
		$field_types = fieldTypes($db,$table,$use_backquotes);

		// Checks whether the field is an integer or not
		for ($j = 0; $j < $fields_cnt; $j++) {
			$field_set[$j] = backquote(mysql_field_name($result, $j), $use_backquotes);
			$type		  = $field_types[$field_set[$j]];

			if ($type == 'tinyint' || $type == 'smallint' || $type == 'mediumint' || $type == 'int' ||
				$type == 'bigint'  || (PMA_MYSQL_INT_VERSION < 40100 && $type == 'timestamp')) {
				$field_num[$j] = TRUE;
			} else {
				$field_num[$j] = FALSE;
			}
			// blob
			if ($type == 'blob' || $type == 'mediumblob' || $type == 'longblob' || $type == 'tinyblob') {
				$field_blob[$j] = TRUE;
			} else {
				$field_blob[$j] = FALSE;
			}
		} // end for

		// Sets the scheme
		if (isset($GLOBALS['showcolumns'])) {
			$fields		= implode(', ', $field_set);
			$schema_insert = $parameter_insert_kind . ' INTO ' . backquote($table, $use_backquotes)
						   . ' (' . $fields . ') VALUES (';
		} else {
			$schema_insert = $parameter_insert_kind . ' INTO ' . backquote($table, $use_backquotes)
						   . ' VALUES (';
		}

		$search	   = array("\x00", "\x0a", "\x0d", "\x1a"); //\x08\\x09, not required
		$replace	  = array('\0', '\n', '\r', '\Z');
		$current_row  = 0;

		while ($row = mysql_fetch_row($result)) {
			$current_row++;
			for ($j = 0; $j < $fields_cnt; $j++) {
				if (!isset($row[$j])) {
					$values[]	 = 'NULL';
				} else if ($row[$j] == '0' || $row[$j] != '') {
					// a number
					if ($field_num[$j]) {
						$values[] = $row[$j];
					// a not empty blob
					} else if ($field_blob[$j] && !empty($row[$j])) {
						$values[] = '0x' . bin2hex($row[$j]);
					// a string
					} else {
						$values[] = "'" . str_replace($search, $replace, sqlAddslashes($row[$j])) . "'";
					}
				} else {
					$values[]	 = "''";
				} // end if
			} // end for

			// Extended inserts case
			if (isset($GLOBALS['extended_ins'])) {
				if ($current_row == 1) {
					$insert_line  = $schema_insert . implode(', ', $values) . ')';
				} else {
					$insert_line  = '(' . implode(', ', $values) . ')';
				}
			}
			// Other inserts case
			else {
				$insert_line	  = $schema_insert . implode(', ', $values) . ')';
			}
			unset($values);
		  $datas[] = $insert_line . ";$crlf";
		  
		} // end while
	} // end if ($result != FALSE)
	mysql_free_result($result);

	return TRUE;
  } // end of the 'PMA_getTableContentFast()' function

  // === Build the dest filename
  function buildDestFilename($s)
  {
	return ( strtolower($s) ); // has to be improved
  }

  // === Parameters of this script
  $parameter_insert_kind = 'REPLACE';  // INSERT | REPLACE
  $parameter_export_table_schema = FALSE;
  $parameter_include_drop_tables  = FALSE;

  $parameter_gzip_result = TRUE;  // Do we want a gzipped file if ouput mode is "download"

  $crlf = "\n";

  // === Language strings global definitions
  $strings['fr']['S_EXPORT_LABEL'] = 'Export de la base de données';
  $strings['fr']['S_STEP1'] = 'Récupération de la liste des tables ...';
  $strings['fr']['S_STEP2'] = 'Récupération de la liste des tables ...';

  $strings['fr']['S_DB_ERROR'] = "Erreur d'accès à la base de données, impossible de récupérer la liste des tables";
  $strings['fr']['S_DB_NO_LINK'] = "Erreur d'accès à la base de données, le lien n'est pas actif";
  $strings['fr']['S_MYSQL_ERROR'] = 'Erreur MySQL';

  // === Array set to current strings definitions with the current selected language 
  //	(this array is to be used later like this : $lng_strings['STRING_LABEL'])
  $lng_strings = $strings[strtolower($this->GetConfigValue('language'))];

  if ($this->IsAdmin())
  {
	// === For each WiKi Tables ...
	if ($this->dblink) 
	{ 
	  $result = mysql_list_tables($this->config["mysql_database"], $this->dblink);

	  if (!$result) {
		  print($lnb_strings['S_DB_ERROR'] . "\n");
		  print($lnb_strings['S_MYSQL_ERROR'] . mysql_error());
		  exit;
	  }
	  
	  while ($row = mysql_fetch_row($result)) {
		  // print("- Table: $row[0] ... ");

		  // Do we have to include drop statements
		  if ( $parameter_include_drop_table == TRUE )
		  {

		  }

		  // Do we have to include schemas
		  if ( $parameter_export_table_schema == TRUE )
		  {
		  }
   
		  // We build all those INSERT statements
		  getTableContentFast($db_link, $row[0], $crlf, $parameter_insert_kind, $datas);
	  }

	  mysql_free_result($result);

	  if ( isset($_POST['submit_download'] ) ) $output = 'download';
	  else $output = 'view';

	  switch ($output)
	  {
		case 'download' :
		  if ( eregi( 'MSIE', $HTTP_USER_AGENT ) || eregi( 'OPERA', $HTTP_USER_AGENT ) ) $mime_type = 'application/octetstream';
		  else 
			$mime_type = 'application/octet-stream';

		  if ( eregi( 'MSIE', $HTTP_USER_AGENT ) ) $content_disp = 'inline';
		  else 
			$content_disp = 'attachment';
		  // Génération du .zip
		  $sql_filename = buildDestFilename($this->GetConfigValue("wakka_name")) . '.sql';
		  if ( $parameter_gzip_result == TRUE ) 
		  {
			$zip = new zipfile;
			$zip->addFile(join("", $datas), $sql_filename); 
			$download_filename = buildDestFilename($this->GetConfigValue("wakka_name")) . '.zip';
		  }
		  else
		  {
			$download_filename = $sql_filename;
		  }

		  //header("Content-Type: application/x-download");
		  header('Content-Type: ' . $mime_type);
		  header('Content-Disposition: '.$content_disp.'; filename="'.$download_filename);
		  header("Content-Transfer-Encoding: binary"); 
		  header("Cache-Control: no-cache, must-revalidate");		   // HTTP/1.1
		  header('Pragma: no-cache');
		  header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // toujours modifié
		  header('Expires: 0');   
		  // header("Content-Length: ".filesize($dest_path.$dest_filename));
		  header("Connection: close");

		  if ( $parameter_gzip_result == TRUE ) 
		  {
			echo $zip->file();
		  }
		  else 
		  {
			foreach ($datas as $line)
			{
			  print("$line\n");
			}
		  }
		  exit;
		  break;
		case 'view' :
		  
		  if ( is_array($datas) && count($datas) > 0 ) 
		  {
			print('<font size="-1">');
			foreach ($datas as $line)
			{
			  print("$line <br />");
			}
			print('</font>');
		  }
		  exit;
		  break;
	  }
	}
	else
	{
	  // No DB connection
	  print($lng_strings['S_DB_NO_LINK'] . "\n");
	}
  }
?>
%%

----
CategoryUserContributions