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