1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10: 11:
12:
13:
14:
15: 16: 17: 18: 19: 20:
21: class DatabaseHelpers
22: {
23:
24: public static $typePatterns = array(
25: '^_' => IReflection::FIELD_TEXT,
26: 'BYTEA|BLOB|BIN' => IReflection::FIELD_BINARY,
27: 'TEXT|CHAR|POINT|INTERVAL' => IReflection::FIELD_TEXT,
28: 'YEAR|BYTE|COUNTER|SERIAL|INT|LONG|SHORT|^TINY$' => IReflection::FIELD_INTEGER,
29: 'CURRENCY|REAL|MONEY|FLOAT|DOUBLE|DECIMAL|NUMERIC|NUMBER' => IReflection::FIELD_FLOAT,
30: '^TIME$' => IReflection::FIELD_TIME,
31: 'TIME' => IReflection::FIELD_DATETIME,
32: 'DATE' => IReflection::FIELD_DATE,
33: 'BOOL' => IReflection::FIELD_BOOL,
34: );
35:
36:
37: 38: 39: 40:
41: public static function dumpResult(Statement $statement)
42: {
43: echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($statement->queryString) . "</caption>\n";
44: if (!$statement->columnCount()) {
45: echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $statement->rowCount(), "</td>\n\t</tr>\n</table>\n";
46: return;
47: }
48: $i = 0;
49: foreach ($statement as $row) {
50: if ($i === 0) {
51: echo "<thead>\n\t<tr>\n\t\t<th>#row</th>\n";
52: foreach ($row as $col => $foo) {
53: echo "\t\t<th>" . htmlSpecialChars($col) . "</th>\n";
54: }
55: echo "\t</tr>\n</thead>\n<tbody>\n";
56: }
57: echo "\t<tr>\n\t\t<th>", $i, "</th>\n";
58: foreach ($row as $col) {
59:
60: echo "\t\t<td>", htmlSpecialChars($col), "</td>\n";
61: }
62: echo "\t</tr>\n";
63: $i++;
64: }
65:
66: if ($i === 0) {
67: echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
68: } else {
69: echo "</tbody>\n</table>\n";
70: }
71: }
72:
73:
74: 75: 76: 77: 78:
79: public static function dumpSql($sql)
80: {
81: static $keywords1 = 'SELECT|(?:ON\s+DUPLICATE\s+KEY)?UPDATE|INSERT(?:\s+INTO)?|REPLACE(?:\s+INTO)?|DELETE|CALL|UNION|FROM|WHERE|HAVING|GROUP\s+BY|ORDER\s+BY|LIMIT|OFFSET|SET|VALUES|LEFT\s+JOIN|INNER\s+JOIN|TRUNCATE';
82: static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
83:
84:
85: $sql = " $sql ";
86: $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
87:
88:
89: $sql = preg_replace('#[ \t]{2,}#', " ", $sql);
90:
91: $sql = wordwrap($sql, 100);
92: $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
93:
94:
95: $sql = htmlSpecialChars($sql);
96: $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", create_function('$matches', '
97: if (!empty($matches[1])) { // comment
98: return \'<em style="color:gray">\' . $matches[1] . \'</em>\';
99:
100: } elseif (!empty($matches[2])) { // error
101: return \'<strong style="color:red">\' . $matches[2] . \'</strong>\';
102:
103: } elseif (!empty($matches[3])) { // most important keywords
104: return \'<strong style="color:blue">\' . $matches[3] . \'</strong>\';
105:
106: } elseif (!empty($matches[4])) { // other keywords
107: return \'<strong style="color:green">\' . $matches[4] . \'</strong>\';
108: }
109: '), $sql);
110:
111: return '<pre class="dump">' . trim($sql) . "</pre>\n";
112: }
113:
114:
115: 116: 117: 118: 119: 120:
121: public static function detectType($type)
122: {
123: static $cache;
124: if (!isset($cache[$type])) {
125: $cache[$type] = 'string';
126: foreach (self::$typePatterns as $s => $val) {
127: if (preg_match("#$s#i", $type)) {
128: return $cache[$type] = $val;
129: }
130: }
131: }
132: return $cache[$type];
133: }
134:
135:
136: 137: 138: 139:
140: public static function loadFromFile(Connection $connection, $file)
141: {
142: @set_time_limit(0);
143:
144: $handle = @fopen($file, 'r');
145: if (!$handle) {
146: throw new FileNotFoundException("Cannot open file '$file'.");
147: }
148:
149: $count = 0;
150: $sql = '';
151: while (!feof($handle)) {
152: $s = fgets($handle);
153: $sql .= $s;
154: if (substr(rtrim($s), -1) === ';') {
155: $connection->query($sql);
156: $sql = '';
157: $count++;
158: }
159: }
160: if (trim($sql) !== '') {
161: $connection->query($sql);
162: $count++;
163: }
164: fclose($handle);
165: return $count;
166: }
167:
168: }
169: