1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database;
9:
10: use Nette;
11: use Tracy;
12:
13:
14: 15: 16:
17: class Helpers
18: {
19: use Nette\StaticClass;
20:
21:
22: public static $maxLength = 100;
23:
24:
25: public static $typePatterns = [
26: '^_' => IStructure::FIELD_TEXT,
27: '(TINY|SMALL|SHORT|MEDIUM|BIG|LONG)(INT)?|INT(EGER|\d+| IDENTITY)?|(SMALL|BIG|)SERIAL\d*|COUNTER|YEAR|BYTE|LONGLONG|UNSIGNED BIG INT' => IStructure::FIELD_INTEGER,
28: '(NEW)?DEC(IMAL)?(\(.*)?|NUMERIC|REAL|DOUBLE( PRECISION)?|FLOAT\d*|(SMALL)?MONEY|CURRENCY|NUMBER' => IStructure::FIELD_FLOAT,
29: 'BOOL(EAN)?' => IStructure::FIELD_BOOL,
30: 'TIME' => IStructure::FIELD_TIME,
31: 'DATE' => IStructure::FIELD_DATE,
32: '(SMALL)?DATETIME(OFFSET)?\d*|TIME(STAMP.*)?' => IStructure::FIELD_DATETIME,
33: 'BYTEA|(TINY|MEDIUM|LONG|)BLOB|(LONG )?(VAR)?BINARY|IMAGE' => IStructure::FIELD_BINARY,
34: ];
35:
36:
37: 38: 39: 40:
41: public static function dumpResult(ResultSet $result)
42: {
43: echo "\n<table class=\"dump\">\n<caption>" . htmlspecialchars($result->getQueryString(), ENT_IGNORE, 'UTF-8') . "</caption>\n";
44: if (!$result->getColumnCount()) {
45: echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $result->getRowCount(), "</td>\n\t</tr>\n</table>\n";
46: return;
47: }
48: $i = 0;
49: foreach ($result 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, ENT_NOQUOTES, 'UTF-8') . "</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: if (is_bool($col)) {
60: $s = $col ? 'TRUE' : 'FALSE';
61: } elseif ($col === null) {
62: $s = 'NULL';
63: } else {
64: $s = (string) $col;
65: }
66: echo "\t\t<td>", htmlspecialchars($s, ENT_NOQUOTES, 'UTF-8'), "</td>\n";
67: }
68: echo "\t</tr>\n";
69: $i++;
70: }
71:
72: if ($i === 0) {
73: echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
74: } else {
75: echo "</tbody>\n</table>\n";
76: }
77: }
78:
79:
80: 81: 82: 83: 84:
85: public static function dumpSql($sql, array $params = null, Connection $connection = null)
86: {
87: 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';
88: static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
89:
90:
91: $sql = " $sql ";
92: $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
93:
94:
95: $sql = preg_replace('#[ \t]{2,}#', ' ', $sql);
96:
97: $sql = wordwrap($sql, 100);
98: $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
99:
100:
101: $sql = htmlspecialchars($sql, ENT_IGNORE, 'UTF-8');
102: $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function (array $matches) {
103: if (!empty($matches[1])) {
104: return '<em style="color:gray">' . $matches[1] . '</em>';
105:
106: } elseif (!empty($matches[2])) {
107: return '<strong style="color:red">' . $matches[2] . '</strong>';
108:
109: } elseif (!empty($matches[3])) {
110: return '<strong style="color:blue">' . $matches[3] . '</strong>';
111:
112: } elseif (!empty($matches[4])) {
113: return '<strong style="color:green">' . $matches[4] . '</strong>';
114: }
115: }, $sql);
116:
117:
118: $sql = preg_replace_callback('#\?#', function () use ($params, $connection) {
119: static $i = 0;
120: if (!isset($params[$i])) {
121: return '?';
122: }
123: $param = $params[$i++];
124: if (is_string($param) && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $param) || preg_last_error())) {
125: return '<i title="Length ' . strlen($param) . ' bytes"><binary></i>';
126:
127: } elseif (is_string($param)) {
128: $length = Nette\Utils\Strings::length($param);
129: $truncated = Nette\Utils\Strings::truncate($param, self::$maxLength);
130: $text = htmlspecialchars($connection ? $connection->quote($truncated) : '\'' . $truncated . '\'', ENT_NOQUOTES, 'UTF-8');
131: return '<span title="Length ' . $length . ' characters">' . $text . '</span>';
132:
133: } elseif (is_resource($param)) {
134: $type = get_resource_type($param);
135: if ($type === 'stream') {
136: $info = stream_get_meta_data($param);
137: }
138: return '<i' . (isset($info['uri']) ? ' title="' . htmlspecialchars($info['uri'], ENT_NOQUOTES, 'UTF-8') . '"' : null)
139: . '><' . htmlspecialchars($type, ENT_NOQUOTES, 'UTF-8') . ' resource></i> ';
140:
141: } else {
142: return htmlspecialchars($param, ENT_NOQUOTES, 'UTF-8');
143: }
144: }, $sql);
145:
146: return '<pre class="dump">' . trim($sql) . "</pre>\n";
147: }
148:
149:
150: 151: 152: 153:
154: public static function detectTypes(\PDOStatement $statement)
155: {
156: $types = [];
157: $count = $statement->columnCount();
158: for ($col = 0; $col < $count; $col++) {
159: $meta = $statement->getColumnMeta($col);
160: if (isset($meta['native_type'])) {
161: $types[$meta['name']] = self::detectType($meta['native_type']);
162: }
163: }
164: return $types;
165: }
166:
167:
168: 169: 170: 171: 172: 173:
174: public static function detectType($type)
175: {
176: static $cache;
177: if (!isset($cache[$type])) {
178: $cache[$type] = 'string';
179: foreach (self::$typePatterns as $s => $val) {
180: if (preg_match("#^($s)$#i", $type)) {
181: return $cache[$type] = $val;
182: }
183: }
184: }
185: return $cache[$type];
186: }
187:
188:
189: 190: 191: 192:
193: public static function loadFromFile(Connection $connection, $file)
194: {
195: @set_time_limit(0);
196:
197: $handle = @fopen($file, 'r');
198: if (!$handle) {
199: throw new Nette\FileNotFoundException("Cannot open file '$file'.");
200: }
201:
202: $count = 0;
203: $delimiter = ';';
204: $sql = '';
205: $pdo = $connection->getPdo();
206: while (!feof($handle)) {
207: $s = rtrim(fgets($handle));
208: if (!strncasecmp($s, 'DELIMITER ', 10)) {
209: $delimiter = substr($s, 10);
210:
211: } elseif (substr($s, -strlen($delimiter)) === $delimiter) {
212: $sql .= substr($s, 0, -strlen($delimiter));
213: $pdo->exec($sql);
214: $sql = '';
215: $count++;
216:
217: } else {
218: $sql .= $s . "\n";
219: }
220: }
221: if (trim($sql) !== '') {
222: $pdo->exec($sql);
223: $count++;
224: }
225: fclose($handle);
226: return $count;
227: }
228:
229:
230: public static function createDebugPanel($connection, $explain = true, $name = null)
231: {
232: $panel = new Nette\Bridges\DatabaseTracy\ConnectionPanel($connection);
233: $panel->explain = $explain;
234: $panel->name = $name;
235: Tracy\Debugger::getBar()->addPanel($panel);
236: return $panel;
237: }
238:
239:
240: 241: 242: 243:
244: public static function toPairs(array $rows, $key = null, $value = null)
245: {
246: if (!$rows) {
247: return [];
248: }
249:
250: $keys = array_keys((array) reset($rows));
251: if (!count($keys)) {
252: throw new \LogicException('Result set does not contain any column.');
253:
254: } elseif ($key === null && $value === null) {
255: if (count($keys) === 1) {
256: list($value) = $keys;
257: } else {
258: list($key, $value) = $keys;
259: }
260: }
261:
262: $return = [];
263: if ($key === null) {
264: foreach ($rows as $row) {
265: $return[] = ($value === null ? $row : $row[$value]);
266: }
267: } else {
268: foreach ($rows as $row) {
269: $return[(string) $row[$key]] = ($value === null ? $row : $row[$value]);
270: }
271: }
272:
273: return $return;
274: }
275:
276:
277: 278: 279: 280: 281:
282: public static function findDuplicates(\PDOStatement $statement)
283: {
284: $cols = [];
285: for ($i = 0; $i < $statement->columnCount(); $i++) {
286: $meta = $statement->getColumnMeta($i);
287: $cols[$meta['name']][] = isset($meta['table']) ? $meta['table'] : '';
288: }
289: $duplicates = [];
290: foreach ($cols as $name => $tables) {
291: if (count($tables) > 1) {
292: $tables = array_filter(array_unique($tables));
293: $duplicates[] = "'$name'" . ($tables ? ' (from ' . implode(', ', $tables) . ')' : '');
294: }
295: }
296: return implode(', ', $duplicates);
297: }
298: }
299: