include/classes/sqlutility.class.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 class SqlUtility
  14 {
  15         /**
  16         * Function from phpMyAdmin (http://phpwizard.net/projects/phpMyAdmin/)
  17         *
  18         * Removes comment and splits large sql files into individual queries
  19         *
  20         * Last revision: September 23, 2001 - gandon
  21         *
  22         * @param   array    the splitted sql commands
  23         * @param   string   the sql commands
  24         * @return  boolean  always true
  25         * @access  public
  26         */
  27         function splitSqlFile(&$ret, $sql)
  28         {
  29                 $sql               = trim($sql);
  30                 $sql_len           = strlen($sql);
  31                 $char              = '';
  32         $string_start      = '';
  33         $in_string         = false;
  34
  35         for ($i = 0; $i < $sql_len; ++$i) {
  36                 $char = $sql[$i];
  37
  38            // We are in a string, check for not escaped end of
  39                    // strings except for backquotes that can't be escaped
  40            if ($in_string) {
  41                         for (;;) {
  42                         $i         = strpos($sql, $string_start, $i);
  43                                         // No end of string found -> add the current
  44                                         // substring to the returned array
  45                         if (!$i) {
  46                                                 $ret[] = $sql;
  47                         return true;
  48                         }
  49                                         // Backquotes or no backslashes before
  50                                         // quotes: it's indeed the end of the
  51                                         // string -> exit the loop
  52                         else if ($string_start == '`' || $sql[$i-1] != '\\') {
  53                                                 $string_start      = '';
  54                                 $in_string         = false;
  55                         break;
  56                         }
  57                         // one or more Backslashes before the presumed
  58                                         // end of string...
  59                         else {
  60                                                 // first checks for escaped backslashes
  61                         $j                     = 2;
  62                         $escaped_backslash     = false;
  63                                                 while ($i-$j > 0 && $sql[$i-$j] == '\\') {
  64                                                         $escaped_backslash = !$escaped_backslash;
  65                                 $j++;
  66                         }
  67                         // ... if escaped backslashes: it's really the
  68                                                 // end of the string -> exit the loop
  69                         if ($escaped_backslash) {
  70                                                         $string_start  = '';
  71                                 $in_string     = false;
  72                                                         break;
  73                         }
  74                         // ... else loop
  75                         else {
  76                                                         $i++;
  77                         }
  78                         } // end if...elseif...else
  79                 } // end for
  80                 } // end if (in string)
  81                 // We are not in a string, first check for delimiter...
  82                 else if ($char == ';') {
  83                                 // if delimiter found, add the parsed part to the returned array
  84                 $ret[]    = substr($sql, 0, $i);
  85                 $sql      = ltrim(substr($sql, min($i + 1, $sql_len)));
  86                         $sql_len  = strlen($sql);
  87                 if ($sql_len) {
  88                                         $i      = -1;
  89                 } else {
  90                         // The submited statement(s) end(s) here
  91                         return true;
  92                                 }
  93                 } // end else if (is delimiter)
  94                 // ... then check for start of a string,...
  95                 else if (($char == '"') || ($char == '\'') || ($char == '`')) {
  96                                 $in_string    = true;
  97                                 $string_start = $char;
  98                 } // end else if (is start of string)
  99
 100                 // for start of a comment (and remove this comment if found)...
 101                 else if ($char == '#' || ($i > 1 && $sql[$i-2] . $sql[$i-1] == '--')) {
 102                 // starting position of the comment depends on the comment type
 103                         $start_of_comment = (($sql[$i] == '#') ? $i : $i-2);
 104                 // if no "\n" exits in the remaining string, checks for "\r"
 105                 // (Mac eol style)
 106                         $end_of_comment   = (strpos(' ' . $sql, "\012", $i+2))
 107                               ? strpos(' ' . $sql, "\012", $i+2)
 108                               : strpos(' ' . $sql, "\015", $i+2);
 109                         if (!$end_of_comment) {
 110                 // no eol found after '#', add the parsed part to the returned
 111                 // array and exit
 112                         $ret[]   = trim(substr($sql, 0, $i-1));
 113                         return true;
 114                                 } else {
 115                         $sql     = substr($sql, 0, $start_of_comment) . ltrim(substr($sql, $end_of_comment));
 116                         $sql_len = strlen($sql);
 117                         $i--;
 118                 } // end if...else
 119                 } // end else if (is comment)
 120         } // end for
 121
 122         // add any rest to the returned array
 123         if (!empty($sql) && trim($sql) != '') {
 124                         $ret[] = $sql;
 125         }
 126         return true;
 127         }
 128
 129         /**
 130          * add a prefix.'_' to all tablenames in a query
 131      *
 132      * @param   string  $query  valid MySQL query string
 133      * @param   string  $prefix prefix to add to all table names
 134          * @return  mixed   FALSE on failure
 135          */
 136         function prefixQuery($query, $prefix)
 137         {
 138                 $pattern = "/^(INSERT INTO|CREATE TABLE|ALTER TABLE|UPDATE)(\s)+([`]?)([^`\s]+)\\3(\s)+/siU";
 139                 $pattern2 = "/^(DROP TABLE)(\s)+([`]?)([^`\s]+)\\3(\s)?$/siU";
 140                 if (preg_match($pattern, $query, $matches) || preg_match($pattern2, $query, $matches)) {
 141                         $replace = "\\1 ".$prefix."\\4\\5";
 142                         $matches[0] = preg_replace($pattern, $replace, $query);
 143                         return $matches;
 144                 }
 145                 return false;
 146         }
 147
 148         function queryFromFile($sql_file_path, $table_prefix){
 149                 global $db, $progress, $errors;
 150
 151                 include_once(TR_INCLUDE_PATH.'classes/DAO/DAO.class.php');
 152                 $dao = new DAO();
 153
 154                 $tables = array();
 155
 156         if (!file_exists($sql_file_path)) {
 157             return false;
 158         }
 159
 160         $sql_query = trim(fread(fopen($sql_file_path, 'r'), filesize($sql_file_path)));
 161         SqlUtility::splitSqlFile($pieces, $sql_query);
 162
 163             foreach ($pieces as $piece) {
 164                 $piece = trim($piece);
 165             // [0] contains the prefixed query
 166             // [4] contains unprefixed table name
 167
 168                         if ($table_prefix || ($table_prefix == '')) {
 169                     $prefixed_query = SqlUtility::prefixQuery($piece, $table_prefix);
 170                         } else {
 171                                 $prefixed_query = $piece;
 172                         }
 173
 174                         if ($prefixed_query != false ) {
 175                 $table = $table_prefix.$prefixed_query[4];
 176                 $prefixed_query[1] = strtoupper($prefixed_query[1]);
 177
 178                 if(strtoupper($prefixed_query[1]) == 'CREATE TABLE'){
 179                     if ($dao->execute($prefixed_query[0]) !== false) {
 180                                                 $progress[] = 'Table <b>'.$table . '</b> created successfully.';
 181                     } else {
 182                                                 if (mysql_errno($db) == 1050) {
 183                                                         $progress[] = 'Table <b>'.$table . '</b> already exists. Skipping.';
 184                                                 } else {
 185                                                         $errors[] = 'Table <b>' . $table . '</b> creation failed.';
 186                                                 }
 187                     }
 188                 }
 189                 elseif($prefixed_query[1] == 'INSERT INTO' ||
 190                        $prefixed_query[1] == 'ALTER TABLE' ||
 191                        $prefixed_query[1] == 'DROP TABLE' ||
 192                        $prefixed_query[1] == 'UPDATE'){
 193                                         $dao->execute($prefixed_query[0]);
 194                 }
 195             }
 196                 }
 197         return TRUE;
 198     }
 199 }
 200 ?>