[Sommaire] [Imprimer]

   1  <?php
   2  // $Id: sqlutility.php 2 2005-11-02 18:23:29Z skalpa $

   3  // sqlutility.php - defines utility class for MySQL database

   4  /**

   5   * @package     kernel

   6   * @subpackage  database

   7   * 

   8   * @author        Kazumi Ono    <onokazu@xoops.org>

   9   * @copyright    copyright (c) 2000-2003 XOOPS.org

  10   */
  11  
  12  /**

  13   * provide some utility methods for databases

  14   * 

  15   * @author Kazumi Ono <onokazu@xoops.org>

  16   * @copyright copyright (c) 2000-2003 XOOPS.org

  17   * 

  18   * @package kernel

  19   * @subpackage  database

  20   */
  21  class SqlUtility
  22  {
  23      /**

  24      * Function from phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/)

  25      *

  26       * Removes comment and splits large sql files into individual queries

  27       *

  28      * Last revision: September 23, 2001 - gandon

  29       *

  30       * @param   array    the splitted sql commands

  31       * @param   string   the sql commands

  32       * @return  boolean  always true

  33       * @access  public

  34       */
  35  	function splitMySqlFile(&$ret, $sql)
  36      {
  37          $sql               = trim($sql);
  38          $sql_len           = strlen($sql);
  39          $char              = '';
  40          $string_start      = '';
  41          $in_string         = false;
  42  
  43          for ($i = 0; $i < $sql_len; ++$i) {
  44              $char = $sql[$i];
  45  
  46             // We are in a string, check for not escaped end of

  47             // strings except for backquotes that can't be escaped

  48             if ($in_string) {
  49                     for (;;) {
  50                         $i         = strpos($sql, $string_start, $i);
  51                      // No end of string found -> add the current

  52                      // substring to the returned array

  53                      if (!$i) {
  54                          $ret[] = $sql;
  55                          return true;
  56                      }
  57                      // Backquotes or no backslashes before 

  58                      // quotes: it's indeed the end of the 

  59                      // string -> exit the loop

  60                      else if ($string_start == '`' || $sql[$i-1] != '\\') {
  61                          $string_start      = '';
  62                             $in_string         = false;
  63                          break;
  64                      }
  65                      // one or more Backslashes before the presumed 

  66                      // end of string...

  67                      else {
  68                          // first checks for escaped backslashes

  69                          $j                     = 2;
  70                          $escaped_backslash     = false;
  71                          while ($i-$j > 0 && $sql[$i-$j] == '\\') {
  72                              $escaped_backslash = !$escaped_backslash;
  73                              $j++;
  74                          }
  75                          // ... if escaped backslashes: it's really the 

  76                          // end of the string -> exit the loop

  77                          if ($escaped_backslash) {
  78                              $string_start  = '';
  79                              $in_string     = false;
  80                              break;
  81                          }
  82                          // ... else loop

  83                          else {
  84                              $i++;
  85                          }
  86                      } // end if...elseif...else

  87                  } // end for

  88              } // end if (in string)

  89              // We are not in a string, first check for delimiter...

  90              else if ($char == ';') {
  91                  // if delimiter found, add the parsed part to the returned array

  92                  $ret[]    = substr($sql, 0, $i);
  93                  $sql      = ltrim(substr($sql, min($i + 1, $sql_len)));
  94                     $sql_len  = strlen($sql);
  95                  if ($sql_len) {
  96                      $i      = -1;
  97                  } else {
  98                      // The submited statement(s) end(s) here

  99                      return true;
 100                  }
 101              } // end else if (is delimiter)

 102              // ... then check for start of a string,...

 103              else if (($char == '"') || ($char == '\'') || ($char == '`')) {
 104                  $in_string    = true;
 105                  $string_start = $char;
 106              } // end else if (is start of string)

 107  
 108              // for start of a comment (and remove this comment if found)...

 109              else if ($char == '#' || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
 110                  // starting position of the comment depends on the comment type

 111                     $start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
 112                  // if no "\n" exits in the remaining string, checks for "\r"

 113                  // (Mac eol style)

 114                     $end_of_comment   = (strpos(' ' . $sql, "\012", $i+2))
 115                                ? strpos(' ' . $sql, "\012", $i+2)
 116                                : strpos(' ' . $sql, "\015", $i+2);
 117                     if (!$end_of_comment) {
 118                  // no eol found after '#', add the parsed part to the returned

 119                  // array and exit

 120                      // RMV fix for comments at end of file

 121                         $last = trim(substr($sql, 0, $i-1));
 122                      if (!empty($last)) {
 123                          $ret[] = $last;
 124                      }
 125                         return true;
 126                  } else {
 127                      $sql     = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
 128                      $sql_len = strlen($sql);
 129                      $i--;
 130                  } // end if...else

 131              } // end else if (is comment)

 132          } // end for

 133  
 134          // add any rest to the returned array

 135          if (!empty($sql) && trim($sql) != '') {
 136              $ret[] = $sql;
 137          }
 138          return true;
 139      }
 140  
 141      /**

 142       * add a prefix.'_' to all tablenames in a query

 143       * 

 144       * @param   string  $query  valid SQL query string

 145       * @param   string  $prefix prefix to add to all table names

 146       * @return  mixed   FALSE on failure

 147       */
 148  	function prefixQuery($query, $prefix)
 149      {
 150          $pattern = "/^(INSERT INTO|CREATE TABLE|ALTER TABLE|UPDATE)(\s)+([`]?)([^`\s]+)\\3(\s)+/siU";
 151          $pattern2 = "/^(DROP TABLE)(\s)+([`]?)([^`\s]+)\\3(\s)?$/siU";
 152          if (preg_match($pattern, $query, $matches) || preg_match($pattern2, $query, $matches)) {
 153              $replace = "\\1 ".$prefix."_\\4\\5";
 154              $matches[0] = preg_replace($pattern, $replace, $query);
 155              return $matches;
 156          }
 157          return false;
 158      }
 159  }
 160  ?>