1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (http://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database;
  9: 
 10: use Nette;
 11: use Tracy;
 12: 
 13: 
 14: /**
 15:  * Database helpers.
 16:  *
 17:  * @author     David Grudl
 18:  */
 19: class Helpers
 20: {
 21:     /** @var int maximum SQL length */
 22:     public static $maxLength = 100;
 23: 
 24:     /** @var array */
 25:     public static $typePatterns = array(
 26:         '^_' => IReflection::FIELD_TEXT, // PostgreSQL arrays
 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, // DATETIME, TIMESTAMP
 33:         'DATE' => IReflection::FIELD_DATE,
 34:         'BOOL' => IReflection::FIELD_BOOL,
 35:     );
 36: 
 37: 
 38:     /**
 39:      * Displays complete result set as HTML table for debug purposes.
 40:      * @return void
 41:      */
 42:     public static function dumpResult(ResultSet $result)
 43:     {
 44:         echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($result->getQueryString(), ENT_IGNORE, 'UTF-8') . "</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, ENT_NOQUOTES, 'UTF-8') . "</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, ENT_NOQUOTES, 'UTF-8'), "</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:      * Returns syntax highlighted SQL command.
 76:      * @param  string
 77:      * @return string
 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:         // insert new lines
 85:         $sql = " $sql ";
 86:         $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
 87: 
 88:         // reduce spaces
 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:         // syntax highlight
 95:         $sql = htmlSpecialChars($sql, ENT_IGNORE, 'UTF-8');
 96:         $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function ($matches) {
 97:             if (!empty($matches[1])) { // comment
 98:                 return '<em style="color:gray">' . $matches[1] . '</em>';
 99: 
100:             } elseif (!empty($matches[2])) { // error
101:                 return '<strong style="color:red">' . $matches[2] . '</strong>';
102: 
103:             } elseif (!empty($matches[3])) { // most important keywords
104:                 return '<strong style="color:blue">' . $matches[3] . '</strong>';
105: 
106:             } elseif (!empty($matches[4])) { // other keywords
107:                 return '<strong style="color:green">' . $matches[4] . '</strong>';
108:             }
109:         }, $sql);
110: 
111:         // parameters
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">&lt;binary&gt;</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 . '\'', ENT_NOQUOTES, 'UTF-8');
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'], ENT_NOQUOTES, 'UTF-8') . '"' : NULL)
133:                     . '>&lt;' . htmlSpecialChars($type, ENT_NOQUOTES, 'UTF-8') . ' resource&gt;</i> ';
134: 
135:             } else {
136:                 return htmlspecialchars($param, ENT_NOQUOTES, 'UTF-8');
137:             }
138:         }, $sql);
139: 
140:         return '<pre class="dump">' . trim($sql) . "</pre>\n";
141:     }
142: 
143: 
144:     /**
145:      * Common column type detection.
146:      * @return array
147:      */
148:     public static function detectTypes(\PDOStatement $statement)
149:     {
150:         $types = array();
151:         $count = $statement->columnCount(); // driver must be meta-aware, see PHP bugs #53782, #54695
152:         for ($col = 0; $col < $count; $col++) {
153:             $meta = $statement->getColumnMeta($col);
154:             if (isset($meta['native_type'])) {
155:                 $types[$meta['name']] = self::detectType($meta['native_type']);
156:             }
157:         }
158:         return $types;
159:     }
160: 
161: 
162:     /**
163:      * Heuristic column type detection.
164:      * @param  string
165:      * @return string
166:      * @internal
167:      */
168:     public static function detectType($type)
169:     {
170:         static $cache;
171:         if (!isset($cache[$type])) {
172:             $cache[$type] = 'string';
173:             foreach (self::$typePatterns as $s => $val) {
174:                 if (preg_match("#$s#i", $type)) {
175:                     return $cache[$type] = $val;
176:                 }
177:             }
178:         }
179:         return $cache[$type];
180:     }
181: 
182: 
183:     /**
184:      * Import SQL dump from file - extremely fast.
185:      * @return int  count of commands
186:      */
187:     public static function loadFromFile(Connection $connection, $file)
188:     {
189:         @set_time_limit(0); // intentionally @
190: 
191:         $handle = @fopen($file, 'r'); // intentionally @
192:         if (!$handle) {
193:             throw new Nette\FileNotFoundException("Cannot open file '$file'.");
194:         }
195: 
196:         $count = 0;
197:         $delimiter = ';';
198:         $sql = '';
199:         $pdo = $connection->getPdo(); // native query without logging
200:         while (!feof($handle)) {
201:             $s = rtrim(fgets($handle));
202:             if (!strncasecmp($s, 'DELIMITER ', 10)) {
203:                 $delimiter = substr($s, 10);
204: 
205:             } elseif (substr($s, -strlen($delimiter)) === $delimiter) {
206:                 $sql .= substr($s, 0, -strlen($delimiter));
207:                 $pdo->exec($sql);
208:                 $sql = '';
209:                 $count++;
210: 
211:             } else {
212:                 $sql .= $s . "\n";
213:             }
214:         }
215:         if (trim($sql) !== '') {
216:             $pdo->exec($sql);
217:             $count++;
218:         }
219:         fclose($handle);
220:         return $count;
221:     }
222: 
223: 
224:     public static function createDebugPanel($connection, $explain = TRUE, $name = NULL)
225:     {
226:         $panel = new Nette\Bridges\DatabaseTracy\ConnectionPanel($connection);
227:         $panel->explain = $explain;
228:         $panel->name = $name;
229:         Tracy\Debugger::getBar()->addPanel($panel);
230:         return $panel;
231:     }
232: 
233: 
234:     /**
235:      * Reformat source to key -> value pairs.
236:      * @return array
237:      */
238:     public static function toPairs(array $rows, $key = NULL, $value = NULL)
239:     {
240:         if (!$rows) {
241:             return array();
242:         }
243: 
244:         $keys = array_keys((array) reset($rows));
245:         if (!count($keys)) {
246:             throw new \LogicException('Result set does not contain any column.');
247: 
248:         } elseif ($key === NULL && $value === NULL) {
249:             if (count($keys) === 1) {
250:                 list($value) = $keys;
251:             } else {
252:                 list($key, $value) = $keys;
253:             }
254:         }
255: 
256:         $return = array();
257:         if ($key === NULL) {
258:             foreach ($rows as $row) {
259:                 $return[] = ($value === NULL ? $row : $row[$value]);
260:             }
261:         } else {
262:             foreach ($rows as $row) {
263:                 $return[is_object($row[$key]) ? (string) $row[$key] : $row[$key]] = ($value === NULL ? $row : $row[$value]);
264:             }
265:         }
266: 
267:         return $return;
268:     }
269: 
270: }
271: