include/classes/sqlutility.class.php

   1 <?php
   2 /****************************************************************/
   3 /* ATutor                                                                                                               */
   4 /****************************************************************/
   5 /* Copyright (c) 2002-2010                                      */
   6 /* Inclusive Design Institute                                   */
   7 /* http://atutor.ca                                             */
   8 /*                                                              */
   9 /* This program is free software. You can redistribute it and/or*/
  10 /* modify it under the terms of the GNU General Public License  */
  11 /* as published by the Free Software Foundation.                                */
  12 /****************************************************************/
  13 // $Id:
  14
  15 class SqlUtility
  16 {
  17         /**
  18         * Function from phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/)
  19         *
  20         * Removes comment and splits large sql files into individual queries
  21         *
  22         * Last revision: September 23, 2001 - gandon
  23         *
  24         * @param   array    the splitted sql commands
  25         * @param   string   the sql commands
  26         * @return  boolean  always true
  27         * @access  public
  28         */
  29         function splitSqlFile(&$ret, $sql)
  30         {
  31                 $sql               = trim($sql);
  32                 $sql_len           = strlen($sql);
  33                 $char              = '';
  34         $string_start      = '';
  35         $in_string         = false;
  36
  37         for ($i = 0; $i < $sql_len; ++$i) {
  38                 $char = $sql[$i];
  39
  40            // We are in a string, check for not escaped end of
  41                    // strings except for backquotes that can't be escaped
  42            if ($in_string) {
  43                         for (;;) {
  44                         $i         = strpos($sql, $string_start, $i);
  45                                         // No end of string found -> add the current
  46                                         // substring to the returned array
  47                         if (!$i) {
  48                                                 $ret[] = $sql;
  49                         return true;
  50                         }
  51                                         // Backquotes or no backslashes before
  52                                         // quotes: it's indeed the end of the
  53                                         // string -> exit the loop
  54                         else if ($string_start == '`' || $sql[$i-1] != '\\') {
  55                                                 $string_start      = '';
  56                                 $in_string         = false;
  57                         break;
  58                         }
  59                         // one or more Backslashes before the presumed
  60                                         // end of string...
  61                         else {
  62                                                 // first checks for escaped backslashes
  63                         $j                     = 2;
  64                         $escaped_backslash     = false;
  65                                                 while ($i-$j > 0 && $sql[$i-$j] == '\\') {
  66                                                         $escaped_backslash = !$escaped_backslash;
  67                                 $j++;
  68                         }
  69                         // ... if escaped backslashes: it's really the
  70                                                 // end of the string -> exit the loop
  71                         if ($escaped_backslash) {
  72                                                         $string_start  = '';
  73                                 $in_string     = false;
  74                                                         break;
  75                         }
  76                         // ... else loop
  77                         else {
  78                                                         $i++;
  79                         }
  80                         } // end if...elseif...else
  81                 } // end for
  82                 } // end if (in string)
  83                 // We are not in a string, first check for delimiter...
  84                 else if ($char == ';') {
  85                                 // if delimiter found, add the parsed part to the returned array
  86                 $ret[]    = substr($sql, 0, $i);
  87                 $sql      = ltrim(substr($sql, min($i + 1, $sql_len)));
  88                         $sql_len  = strlen($sql);
  89                 if ($sql_len) {
  90                                         $i      = -1;
  91                 } else {
  92                         // The submited statement(s) end(s) here
  93                         return true;
  94                                 }
  95                 } // end else if (is delimiter)
  96                 // ... then check for start of a string,...
  97                 else if (($char == '"') || ($char == '\'') || ($char == '`')) {
  98                                 $in_string    = true;
  99                                 $string_start = $char;
 100                 } // end else if (is start of string)
 101
 102                 // for start of a comment (and remove this comment if found)...
 103                 // the comment signs can be "# ", "/*", "--". The comment start signs must be at the begining of a line.
 104                 else if ($char == '#' || ($char == '/' && $sql[$i+1] == '*') || ($char == ' ' && $i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
 105                 // starting position of the comment depends on the comment type
 106                         $start_of_comment = (($char == '#' || ($char == '/' && $sql[$i+1] == '*')) ? $i : $i-2);
 107                 // if no "\n" exits in the remaining string, checks for "\r"
 108                 // (Mac eol style)
 109                         if ($char == '/' && $sql[$i+1] == '*')
 110                                 $end_of_comment = strpos($sql, "*/", $i+2) + 2;
 111                         else
 112                                 $end_of_comment   = (strpos(' ' . $sql, "\012", $i+2))
 113                                       ? strpos(' ' . $sql, "\012", $i+2)
 114                                       : strpos(' ' . $sql, "\015", $i+2);
 115                         if (!$end_of_comment) {
 116                 // no eol found after '#', add the parsed part to the returned
 117                 // array and exit
 118                         $ret[]   = trim(substr($sql, 0, $i-1));
 119                         return true;
 120                                 } else {
 121                         $sql     = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
 122                         $sql_len = strlen($sql);
 123                         $i--;
 124                 } // end if...else
 125                 } // end else if (is comment)
 126         } // end for
 127
 128         // add any rest to the returned array
 129         if (!empty($sql) && trim($sql) != '') {
 130                         $ret[] = $sql;
 131         }
 132         return true;
 133         }
 134
 135         /**
 136          * add a prefix.'_' to all tablenames in a query
 137      *
 138      * @param   string  $query  valid MySQL query string
 139      * @param   string  $prefix prefix to add to all table names
 140          * @return  mixed   FALSE on failure
 141          */
 142         function prefixQuery($query, $prefix)
 143         {
 144                 $pattern = "/^(REPLACE INTO|INSERT INTO|CREATE TABLE|ALTER TABLE|UPDATE)(\s)+([`]?)([^`\s]+)\\3(\s)+/siU";
 145                 $pattern2 = "/^(DROP TABLE)(\s)+([`]?)([^`\s]+)\\3(\s)?$/siU";
 146                 if (preg_match($pattern, $query, $matches) || preg_match($pattern2, $query, $matches)) {
 147                         $replace = "\\1 ".$prefix."\\4\\5";
 148                         $matches[0] = preg_replace($pattern, $replace, $query);
 149                         return $matches;
 150                 }
 151                 return false;
 152         }
 153
 154         function queryFromFile($sql_file_path, $table_prefix)
 155         {
 156                 global $db, $progress, $errors;
 157
 158                 $tables = array();
 159
 160                 if (!file_exists($sql_file_path))
 161                         return false;
 162
 163                 $sql_query = trim(fread(fopen($sql_file_path, 'r'), filesize($sql_file_path)));
 164                 SqlUtility::splitSqlFile($pieces, $sql_query);
 165
 166                 foreach ($pieces as $piece)
 167                 {
 168                         $piece = trim($piece);
 169
 170                         // [0] contains the prefixed query
 171                         // [4] contains unprefixed table name
 172                         if ($table_prefix || ($table_prefix == ''))
 173                                 $prefixed_query = SqlUtility::prefixQuery($piece, $table_prefix);
 174                         else
 175                                 $prefixed_query = $piece;
 176
 177                         if ($prefixed_query != false )
 178                         {
 179                                 $table = $table_prefix.$prefixed_query[4];
 180                                 if($prefixed_query[1] == 'CREATE TABLE')
 181                                 {
 182                                         if (mysql_query($prefixed_query[0],$db) !== false)
 183                                                 $progress[] = 'Table <b>'.$table . '</b> created successfully.';
 184                                         else
 185                                         {
 186                                                 if (mysql_errno($db) == 1050)
 187                                                         $progress[] = 'Table <b>'.$table . '</b> already exists. Skipping.';
 188                                                 else
 189                                                                 $errors[] = 'Table <b>' . $table . '</b> creation failed.';
 190                                         }
 191                                 }
 192                                 elseif($prefixed_query[1] == 'INSERT INTO')
 193                                         mysql_query($prefixed_query[0],$db);
 194                                 elseif($prefixed_query[1] == 'REPLACE INTO')
 195                                         mysql_query($prefixed_query[0],$db);
 196                                 elseif($prefixed_query[1] == 'ALTER TABLE')
 197                                         mysql_query($prefixed_query[0],$db);
 198                                 elseif($prefixed_query[1] == 'DROP TABLE')
 199                                         mysql_query($prefixed_query[1] . ' ' .$table,$db);
 200                         }
 201                 }
 202     return TRUE;
 203   }
 204
 205         // This function only revert queries on "CREATE TABLE" and "INSERT INTO language_text"
 206         function revertQueryFromFile($sql_file_path, $table_prefix)
 207         {
 208                 global $db, $progress, $errors;
 209
 210                 $tables = array();
 211
 212                 if (!file_exists($sql_file_path))
 213                         return false;
 214
 215                 $sql_query = trim(fread(fopen($sql_file_path, 'r'), filesize($sql_file_path)));
 216                 SqlUtility::splitSqlFile($pieces, $sql_query);
 217
 218                 foreach ($pieces as $piece)
 219                 {
 220                         $piece = trim($piece);
 221
 222                         $pattern_create_table = "/^CREATE TABLE\s+([`]?)([^`\s]+)\\1(\s)+/siU";
 223                         if (preg_match($pattern_create_table, $piece, $matches))
 224                         {
 225                                 $sql = 'DROP TABLE '. $table_prefix . $matches[2];
 226                                 mysql_query($sql, $db);
 227                         }
 228
 229                         $pattern_insert_lang = "/^INSERT INTO\s+([`]?)language_text\\1\s+.*VALUES.*'.*'.*'(.*)'.*'(.*)'/siU";
 230                         if (preg_match($pattern_insert_lang, $piece, $matches))
 231                         {
 232                                 $sql = "DELETE FROM ".$table_prefix."language_text WHERE variable='".$matches[2]."' AND term='".$matches[3]."'";
 233                                 mysql_query($sql, $db);
 234                         }
 235                 }
 236
 237     return TRUE;
 238   }
 239 }
 240 ?>