docs/install/include/classes/sqlutility.php

   1 <?php
   2 /************************************************************************/
   3 /* AContent                                                             */
   4 /************************************************************************/
   5 /* Copyright (c) 2010                                                   */
   6 /* Inclusive Design Institute                                           */
   7 /*                                                                      */
   8 /* This program is free software. You can redistribute it and/or        */
   9 /* modify it under the terms of the GNU General Public License          */
  10 /* as published by the Free Software Foundation.                        */
  11 /************************************************************************/
  12
  13
  14 class SqlUtility
  15 {
  16         /**
  17         * Function from phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/)
  18         *
  19         * Removes comment and splits large sql files into individual queries
  20         *
  21         * Last revision: September 23, 2001 - gandon
  22         *
  23         * @param   array    the splitted sql commands
  24         * @param   string   the sql commands
  25         * @return  boolean  always true
  26         * @access  public
  27         */
  28         function splitSqlFile(&$ret, $sql)
  29         {
  30                 $sql               = trim($sql);
  31                 $sql_len           = strlen($sql);
  32                 $char              = '';
  33         $string_start      = '';
  34         $in_string         = false;
  35
  36         for ($i = 0; $i < $sql_len; ++$i) {
  37                 $char = $sql[$i];
  38
  39            // We are in a string, check for not escaped end of
  40                    // strings except for backquotes that can't be escaped
  41            if ($in_string) {
  42                         for (;;) {
  43                         $i         = strpos($sql, $string_start, $i);
  44                                         // No end of string found -> add the current
  45                                         // substring to the returned array
  46                         if (!$i) {
  47                                                 $ret[] = $sql;
  48                         return true;
  49                         }
  50                                         // Backquotes or no backslashes before
  51                                         // quotes: it's indeed the end of the
  52                                         // string -> exit the loop
  53                         else if ($string_start == '`' || $sql[$i-1] != '\\') {
  54                                                 $string_start      = '';
  55                                 $in_string         = false;
  56                         break;
  57                         }
  58                         // one or more Backslashes before the presumed
  59                                         // end of string...
  60                         else {
  61                                                 // first checks for escaped backslashes
  62                         $j                     = 2;
  63                         $escaped_backslash     = false;
  64                                                 while ($i-$j > 0 && $sql[$i-$j] == '\\') {
  65                                                         $escaped_backslash = !$escaped_backslash;
  66                                 $j++;
  67                         }
  68                         // ... if escaped backslashes: it's really the
  69                                                 // end of the string -> exit the loop
  70                         if ($escaped_backslash) {
  71                                                         $string_start  = '';
  72                                 $in_string     = false;
  73                                                         break;
  74                         }
  75                         // ... else loop
  76                         else {
  77                                                         $i++;
  78                         }
  79                         } // end if...elseif...else
  80                 } // end for
  81                 } // end if (in string)
  82                 // We are not in a string, first check for delimiter...
  83                 else if ($char == ';') {
  84                                 // if delimiter found, add the parsed part to the returned array
  85                 $ret[]    = substr($sql, 0, $i);
  86                 $sql      = ltrim(substr($sql, min($i + 1, $sql_len)));
  87                         $sql_len  = strlen($sql);
  88                 if ($sql_len) {
  89                                         $i      = -1;
  90                 } else {
  91                         // The submited statement(s) end(s) here
  92                         return true;
  93                                 }
  94                 } // end else if (is delimiter)
  95                 // ... then check for start of a string,...
  96                 else if (($char == '"') || ($char == '\'') || ($char == '`')) {
  97                                 $in_string    = true;
  98                                 $string_start = $char;
  99                 } // end else if (is start of string)
 100
 101                 // for start of a comment (and remove this comment if found)...
 102                 else if ($char == '#' || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
 103                 // starting position of the comment depends on the comment type
 104                         $start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
 105                 // if no "\n" exits in the remaining string, checks for "\r"
 106                 // (Mac eol style)
 107                         $end_of_comment   = (strpos(' ' . $sql, "\012", $i+2))
 108                               ? strpos(' ' . $sql, "\012", $i+2)
 109                               : strpos(' ' . $sql, "\015", $i+2);
 110                         if (!$end_of_comment) {
 111                 // no eol found after '#', add the parsed part to the returned
 112                 // array and exit
 113                         $ret[]   = trim(substr($sql, 0, $i-1));
 114                         return true;
 115                                 } else {
 116                         $sql     = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
 117                         $sql_len = strlen($sql);
 118                         $i--;
 119                 } // end if...else
 120                 } // end else if (is comment)
 121         } // end for
 122
 123         // add any rest to the returned array
 124         if (!empty($sql) && trim($sql) != '') {
 125                         $ret[] = $sql;
 126         }
 127         return true;
 128         }
 129
 130         /**
 131          * add a prefix.'_' to all tablenames in a query
 132      *
 133      * @param   string  $query  valid MySQL query string
 134      * @param   string  $prefix prefix to add to all table names
 135          * @return  mixed   FALSE on failure
 136          */
 137         function prefixQuery($query, $prefix)
 138         {
 139                 $pattern = "/^(INSERT INTO|REPLACE INTO|CREATE TABLE|ALTER TABLE|UPDATE)(\s)+([`]?)([^`\s]+)\\3(\s)+/siU";
 140                 $pattern2 = "/^(DROP TABLE)(\s)+([`]?)([^`\s]+)\\3(\s)?$/siU";
 141                 if (preg_match($pattern, $query, $matches) || preg_match($pattern2, $query, $matches)) {
 142                         $replace = "\\1 ".$prefix."\\4\\5";
 143                         $matches[0] = preg_replace($pattern, $replace, $query);
 144
 145                         if ($matches[1] == 'INSERT INTO') {
 146                                 $parts = explode(' ', $matches[0]);
 147                                 $size_of_parts = count($parts);
 148                                 if ($parts[$size_of_parts-2] == 'FROM') {
 149                                         $parts[$size_of_parts-1] = $prefix . str_replace('`', '', $parts[$size_of_parts-1]);
 150                                         $matches[0] = implode(' ', $parts);
 151                                 }
 152                         } else if ($matches[1] == 'ALTER TABLE') {
 153                                 $parts = explode(' ', $matches[0]);
 154                                 if ($parts[3] == 'RENAME') {
 155                                         $parts[4] = $prefix . str_replace('`', '', $parts[4]);
 156                                         $matches[0] = implode(' ', $parts);
 157                                 }
 158                         }
 159
 160                         return $matches;
 161                 }
 162                 return false;
 163         }
 164 }
 165 ?>