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