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: static public $maxLength = 100;
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(ResultSet $result)
42: {
43: echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($result->getQueryString()) . "</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) . "</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: echo "\t\t<td>", htmlSpecialChars($col), "</td>\n";
60: }
61: echo "\t</tr>\n";
62: $i++;
63: }
64:
65: if ($i === 0) {
66: echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
67: } else {
68: echo "</tbody>\n</table>\n";
69: }
70: }
71:
72:
73: 74: 75: 76: 77:
78: public static function dumpSql($sql, array $params = NULL)
79: {
80: 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';
81: static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
82:
83:
84: $sql = " $sql ";
85: $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
86:
87:
88: $sql = preg_replace('#[ \t]{2,}#', ' ', $sql);
89:
90: $sql = wordwrap($sql, 100);
91: $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
92:
93:
94: $sql = htmlSpecialChars($sql);
95: $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function($matches) {
96: if (!empty($matches[1])) {
97: return '<em style="color:gray">' . $matches[1] . '</em>';
98:
99: } elseif (!empty($matches[2])) {
100: return '<strong style="color:red">' . $matches[2] . '</strong>';
101:
102: } elseif (!empty($matches[3])) {
103: return '<strong style="color:blue">' . $matches[3] . '</strong>';
104:
105: } elseif (!empty($matches[4])) {
106: return '<strong style="color:green">' . $matches[4] . '</strong>';
107: }
108: }, $sql);
109:
110:
111: $sql = preg_replace_callback('#\?#', function() use ($params) {
112: static $i = 0;
113: if (!isset($params[$i])) {
114: return '?';
115: }
116: $param = $params[$i++];
117: if (is_string($param) && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $param) || preg_last_error())) {
118: return '<i title="Length ' . strlen($param) . ' bytes"><binary></i>';
119:
120: } elseif (is_string($param)) {
121: return '<span title="Length ' . Nette\Utils\Strings::length($param) . ' characters">\'' . htmlspecialchars(Nette\Utils\Strings::truncate($param, Helpers::$maxLength)) . "'</span>";
122:
123: } elseif (is_resource($param)) {
124: $type = get_resource_type($param);
125: if ($type === 'stream') {
126: $info = stream_get_meta_data($param);
127: }
128: return '<i' . (isset($info['uri']) ? ' title="' . htmlspecialchars($info['uri']) . '"' : NULL) . '><' . htmlSpecialChars($type) . ' resource></i> ';
129:
130: } else {
131: return htmlspecialchars($param);
132: }
133: }, $sql);
134:
135: return '<pre class="dump">' . trim($sql) . "</pre>\n";
136: }
137:
138:
139: 140: 141: 142:
143: public static function detectTypes(\PDOStatement $statement)
144: {
145: $types = array();
146: $count = $statement->columnCount();
147: for ($col = 0; $col < $count; $col++) {
148: $meta = $statement->getColumnMeta($col);
149: if (isset($meta['native_type'])) {
150: $types[$meta['name']] = self::detectType($meta['native_type']);
151: }
152: }
153: return $types;
154: }
155:
156:
157: 158: 159: 160: 161: 162:
163: public static function detectType($type)
164: {
165: static $cache;
166: if (!isset($cache[$type])) {
167: $cache[$type] = 'string';
168: foreach (self::$typePatterns as $s => $val) {
169: if (preg_match("#$s#i", $type)) {
170: return $cache[$type] = $val;
171: }
172: }
173: }
174: return $cache[$type];
175: }
176:
177:
178: 179: 180: 181:
182: public static function loadFromFile(Connection $connection, $file)
183: {
184: @set_time_limit(0);
185:
186: $handle = @fopen($file, 'r');
187: if (!$handle) {
188: throw new Nette\FileNotFoundException("Cannot open file '$file'.");
189: }
190:
191: $count = 0;
192: $sql = '';
193: while (!feof($handle)) {
194: $s = fgets($handle);
195: $sql .= $s;
196: if (substr(rtrim($s), -1) === ';') {
197: $connection->query($sql);
198: $sql = '';
199: $count++;
200: }
201: }
202: if (trim($sql) !== '') {
203: $connection->query($sql);
204: $count++;
205: }
206: fclose($handle);
207: return $count;
208: }
209:
210:
211: public static function createDebugPanel($connection, $explain = TRUE, $name = NULL)
212: {
213: $panel = new Nette\Database\Diagnostics\ConnectionPanel($connection);
214: $panel->explain = $explain;
215: $panel->name = $name;
216: Nette\Diagnostics\Debugger::getBar()->addPanel($panel);
217: return $panel;
218: }
219:
220:
221: 222: 223: 224:
225: public static function toPairs(array $rows, $key = NULL, $value = NULL)
226: {
227: if (!$rows) {
228: return array();
229: }
230:
231: $keys = array_keys((array) reset($rows));
232: if (!count($keys)) {
233: throw new \LogicException('Result set does not contain any column.');
234:
235: } elseif ($key === NULL && $value === NULL) {
236: if (count($keys) === 1) {
237: list($value) = $keys;
238: } else {
239: list($key, $value) = $keys;
240: }
241: }
242:
243: $return = array();
244: if ($key === NULL) {
245: foreach ($rows as $row) {
246: $return[] = ($value === NULL ? $row : $row[$value]);
247: }
248: } else {
249: foreach ($rows as $row) {
250: $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value === NULL ? $row : $row[$value]);
251: }
252: }
253:
254: return $return;
255: }
256:
257: }
258: