1: <?php
2: // $Id: sqlutility.php,v 1.1 2007/05/15 02:35:14 minahito Exp $
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: public static 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: public static 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: ?>
161: