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:

Code for exportdb.php (put this in actions/ )
 

<?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/) :
 

<?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
There are no comments on this page.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki