1: <?php
2:
3: 4: 5: 6:
7:
8: namespace Nette\Database;
9:
10: use Nette,
11: Tracy;
12:
13:
14: 15: 16: 17: 18:
19: class Helpers
20: {
21:
22: static public $maxLength = 100;
23:
24:
25: public static $typePatterns = array(
26: '^_' => IReflection::FIELD_TEXT,
27: 'BYTEA|BLOB|BIN' => IReflection::FIELD_BINARY,
28: 'TEXT|CHAR|POINT|INTERVAL' => IReflection::FIELD_TEXT,
29: 'YEAR|BYTE|COUNTER|SERIAL|INT|LONG|SHORT|^TINY$' => IReflection::FIELD_INTEGER,
30: 'CURRENCY|REAL|MONEY|FLOAT|DOUBLE|DECIMAL|NUMERIC|NUMBER' => IReflection::FIELD_FLOAT,
31: '^TIME$' => IReflection::FIELD_TIME,
32: 'TIME' => IReflection::FIELD_DATETIME,
33: 'DATE' => IReflection::FIELD_DATE,
34: 'BOOL' => IReflection::FIELD_BOOL,
35: );
36:
37:
38: 39: 40: 41:
42: public static function dumpResult(ResultSet $result)
43: {
44: echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($result->getQueryString()) . "</caption>\n";
45: if (!$result->getColumnCount()) {
46: echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $result->getRowCount(), "</td>\n\t</tr>\n</table>\n";
47: return;
48: }
49: $i = 0;
50: foreach ($result as $row) {
51: if ($i === 0) {
52: echo "<thead>\n\t<tr>\n\t\t<th>#row</th>\n";
53: foreach ($row as $col => $foo) {
54: echo "\t\t<th>" . htmlSpecialChars($col) . "</th>\n";
55: }
56: echo "\t</tr>\n</thead>\n<tbody>\n";
57: }
58: echo "\t<tr>\n\t\t<th>", $i, "</th>\n";
59: foreach ($row as $col) {
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, array $params = NULL, Connection $connection = NULL)
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", function($matches) {
97: if (!empty($matches[1])) {
98: return '<em style="color:gray">' . $matches[1] . '</em>';
99:
100: } elseif (!empty($matches[2])) {
101: return '<strong style="color:red">' . $matches[2] . '</strong>';
102:
103: } elseif (!empty($matches[3])) {
104: return '<strong style="color:blue">' . $matches[3] . '</strong>';
105:
106: } elseif (!empty($matches[4])) {
107: return '<strong style="color:green">' . $matches[4] . '</strong>';
108: }
109: }, $sql);
110:
111:
112: $sql = preg_replace_callback('#\?#', function() use ($params, $connection) {
113: static $i = 0;
114: if (!isset($params[$i])) {
115: return '?';
116: }
117: $param = $params[$i++];
118: if (is_string($param) && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $param) || preg_last_error())) {
119: return '<i title="Length ' . strlen($param) . ' bytes"><binary></i>';
120:
121: } elseif (is_string($param)) {
122: $length = Nette\Utils\Strings::length($param);
123: $truncated = Nette\Utils\Strings::truncate($param, Helpers::$maxLength);
124: $text = htmlspecialchars($connection ? $connection->quote($truncated) : '\'' . $truncated . '\'');
125: return '<span title="Length ' . $length . ' characters">' . $text . '</span>';
126:
127: } elseif (is_resource($param)) {
128: $type = get_resource_type($param);
129: if ($type === 'stream') {
130: $info = stream_get_meta_data($param);
131: }
132: return '<i' . (isset($info['uri']) ? ' title="' . htmlspecialchars($info['uri']) . '"' : NULL) . '><' . htmlSpecialChars($type) . ' resource></i> ';
133:
134: } else {
135: return htmlspecialchars($param);
136: }
137: }, $sql);
138:
139: return '<pre class="dump">' . trim($sql) . "</pre>\n";
140: }
141:
142:
143: 144: 145: 146:
147: public static function detectTypes(\PDOStatement $statement)
148: {
149: $types = array();
150: $count = $statement->columnCount();
151: for ($col = 0; $col < $count; $col++) {
152: $meta = $statement->getColumnMeta($col);
153: if (isset($meta['native_type'])) {
154: $types[$meta['name']] = self::detectType($meta['native_type']);
155: }
156: }
157: return $types;
158: }
159:
160:
161: 162: 163: 164: 165: 166:
167: public static function detectType($type)
168: {
169: static $cache;
170: if (!isset($cache[$type])) {
171: $cache[$type] = 'string';
172: foreach (self::$typePatterns as $s => $val) {
173: if (preg_match("#$s#i", $type)) {
174: return $cache[$type] = $val;
175: }
176: }
177: }
178: return $cache[$type];
179: }
180:
181:
182: 183: 184: 185:
186: public static function loadFromFile(Connection $connection, $file)
187: {
188: @set_time_limit(0);
189:
190: $handle = @fopen($file, 'r');
191: if (!$handle) {
192: throw new Nette\FileNotFoundException("Cannot open file '$file'.");
193: }
194:
195: $count = 0;
196: $delimiter = ';';
197: $sql = '';
198: while (!feof($handle)) {
199: $s = rtrim(fgets($handle));
200: if (!strncasecmp($s, 'DELIMITER ', 10)) {
201: $delimiter = substr($s, 10);
202:
203: } elseif (substr($s, -strlen($delimiter)) === $delimiter) {
204: $sql .= substr($s, 0, -strlen($delimiter));
205: $connection->query($sql);
206: $sql = '';
207: $count++;
208:
209: } else {
210: $sql .= $s . "\n";
211: }
212: }
213: if (trim($sql) !== '') {
214: $connection->query($sql);
215: $count++;
216: }
217: fclose($handle);
218: return $count;
219: }
220:
221:
222: public static function createDebugPanel($connection, $explain = TRUE, $name = NULL)
223: {
224: $panel = new Nette\Bridges\DatabaseTracy\ConnectionPanel($connection);
225: $panel->explain = $explain;
226: $panel->name = $name;
227: Tracy\Debugger::getBar()->addPanel($panel);
228: return $panel;
229: }
230:
231:
232: 233: 234: 235:
236: public static function toPairs(array $rows, $key = NULL, $value = NULL)
237: {
238: if (!$rows) {
239: return array();
240: }
241:
242: $keys = array_keys((array) reset($rows));
243: if (!count($keys)) {
244: throw new \LogicException('Result set does not contain any column.');
245:
246: } elseif ($key === NULL && $value === NULL) {
247: if (count($keys) === 1) {
248: list($value) = $keys;
249: } else {
250: list($key, $value) = $keys;
251: }
252: }
253:
254: $return = array();
255: if ($key === NULL) {
256: foreach ($rows as $row) {
257: $return[] = ($value === NULL ? $row : $row[$value]);
258: }
259: } else {
260: foreach ($rows as $row) {
261: $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value === NULL ? $row : $row[$value]);
262: }
263: }
264:
265: return $return;
266: }
267:
268: }
269: