1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database;
  9: 
 10: use Nette;
 11: use Tracy;
 12: 
 13: 
 14: /**
 15:  * Database helpers.
 16:  */
 17: class Helpers
 18: {
 19:     /** @var int maximum SQL length */
 20:     public static $maxLength = 100;
 21: 
 22:     /** @var array */
 23:     public static $typePatterns = array(
 24:         '^_' => IStructure::FIELD_TEXT, // PostgreSQL arrays
 25:         'BYTEA|BLOB|BIN' => IStructure::FIELD_BINARY,
 26:         'TEXT|CHAR|POINT|INTERVAL' => IStructure::FIELD_TEXT,
 27:         'YEAR|BYTE|COUNTER|SERIAL|INT|LONG|SHORT|^TINY$' => IStructure::FIELD_INTEGER,
 28:         'CURRENCY|REAL|MONEY|FLOAT|DOUBLE|DECIMAL|NUMERIC|NUMBER' => IStructure::FIELD_FLOAT,
 29:         '^TIME$' => IStructure::FIELD_TIME,
 30:         'TIME' => IStructure::FIELD_DATETIME, // DATETIME, TIMESTAMP
 31:         'DATE(?!RANGE)' => IStructure::FIELD_DATE,
 32:         'BOOL' => IStructure::FIELD_BOOL,
 33:     );
 34: 
 35: 
 36:     /**
 37:      * Displays complete result set as HTML table for debug purposes.
 38:      * @return void
 39:      */
 40:     public static function dumpResult(ResultSet $result)
 41:     {
 42:         echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($result->getQueryString(), ENT_IGNORE, 'UTF-8') . "</caption>\n";
 43:         if (!$result->getColumnCount()) {
 44:             echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $result->getRowCount(), "</td>\n\t</tr>\n</table>\n";
 45:             return;
 46:         }
 47:         $i = 0;
 48:         foreach ($result as $row) {
 49:             if ($i === 0) {
 50:                 echo "<thead>\n\t<tr>\n\t\t<th>#row</th>\n";
 51:                 foreach ($row as $col => $foo) {
 52:                     echo "\t\t<th>" . htmlSpecialChars($col, ENT_NOQUOTES, 'UTF-8') . "</th>\n";
 53:                 }
 54:                 echo "\t</tr>\n</thead>\n<tbody>\n";
 55:             }
 56:             echo "\t<tr>\n\t\t<th>", $i, "</th>\n";
 57:             foreach ($row as $col) {
 58:                 echo "\t\t<td>", htmlSpecialChars($col, ENT_NOQUOTES, 'UTF-8'), "</td>\n";
 59:             }
 60:             echo "\t</tr>\n";
 61:             $i++;
 62:         }
 63: 
 64:         if ($i === 0) {
 65:             echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
 66:         } else {
 67:             echo "</tbody>\n</table>\n";
 68:         }
 69:     }
 70: 
 71: 
 72:     /**
 73:      * Returns syntax highlighted SQL command.
 74:      * @param  string
 75:      * @return string
 76:      */
 77:     public static function dumpSql($sql, array $params = NULL, Connection $connection = NULL)
 78:     {
 79:         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';
 80:         static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
 81: 
 82:         // insert new lines
 83:         $sql = " $sql ";
 84:         $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
 85: 
 86:         // reduce spaces
 87:         $sql = preg_replace('#[ \t]{2,}#', ' ', $sql);
 88: 
 89:         $sql = wordwrap($sql, 100);
 90:         $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
 91: 
 92:         // syntax highlight
 93:         $sql = htmlSpecialChars($sql, ENT_IGNORE, 'UTF-8');
 94:         $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function ($matches) {
 95:             if (!empty($matches[1])) { // comment
 96:                 return '<em style="color:gray">' . $matches[1] . '</em>';
 97: 
 98:             } elseif (!empty($matches[2])) { // error
 99:                 return '<strong style="color:red">' . $matches[2] . '</strong>';
100: 
101:             } elseif (!empty($matches[3])) { // most important keywords
102:                 return '<strong style="color:blue">' . $matches[3] . '</strong>';
103: 
104:             } elseif (!empty($matches[4])) { // other keywords
105:                 return '<strong style="color:green">' . $matches[4] . '</strong>';
106:             }
107:         }, $sql);
108: 
109:         // parameters
110:         $sql = preg_replace_callback('#\?#', function () use ($params, $connection) {
111:             static $i = 0;
112:             if (!isset($params[$i])) {
113:                 return '?';
114:             }
115:             $param = $params[$i++];
116:             if (is_string($param) && (preg_match('#[^\x09\x0A\x0D\x20-\x7E\xA0-\x{10FFFF}]#u', $param) || preg_last_error())) {
117:                 return '<i title="Length ' . strlen($param) . ' bytes">&lt;binary&gt;</i>';
118: 
119:             } elseif (is_string($param)) {
120:                 $length = Nette\Utils\Strings::length($param);
121:                 $truncated = Nette\Utils\Strings::truncate($param, Helpers::$maxLength);
122:                 $text = htmlspecialchars($connection ? $connection->quote($truncated) : '\'' . $truncated . '\'', ENT_NOQUOTES, 'UTF-8');
123:                 return '<span title="Length ' . $length . ' characters">' . $text . '</span>';
124: 
125:             } elseif (is_resource($param)) {
126:                 $type = get_resource_type($param);
127:                 if ($type === 'stream') {
128:                     $info = stream_get_meta_data($param);
129:                 }
130:                 return '<i' . (isset($info['uri']) ? ' title="' . htmlspecialchars($info['uri'], ENT_NOQUOTES, 'UTF-8') . '"' : NULL)
131:                     . '>&lt;' . htmlSpecialChars($type, ENT_NOQUOTES, 'UTF-8') . ' resource&gt;</i> ';
132: 
133:             } else {
134:                 return htmlspecialchars($param, ENT_NOQUOTES, 'UTF-8');
135:             }
136:         }, $sql);
137: 
138:         return '<pre class="dump">' . trim($sql) . "</pre>\n";
139:     }
140: 
141: 
142:     /**
143:      * Common column type detection.
144:      * @return array
145:      */
146:     public static function detectTypes(\PDOStatement $statement)
147:     {
148:         $types = array();
149:         $count = $statement->columnCount(); // driver must be meta-aware, see PHP bugs #53782, #54695
150:         for ($col = 0; $col < $count; $col++) {
151:             $meta = $statement->getColumnMeta($col);
152:             if (isset($meta['native_type'])) {
153:                 $types[$meta['name']] = self::detectType($meta['native_type']);
154:             }
155:         }
156:         return $types;
157:     }
158: 
159: 
160:     /**
161:      * Heuristic column type detection.
162:      * @param  string
163:      * @return string
164:      * @internal
165:      */
166:     public static function detectType($type)
167:     {
168:         static $cache;
169:         if (!isset($cache[$type])) {
170:             $cache[$type] = 'string';
171:             foreach (self::$typePatterns as $s => $val) {
172:                 if (preg_match("#$s#i", $type)) {
173:                     return $cache[$type] = $val;
174:                 }
175:             }
176:         }
177:         return $cache[$type];
178:     }
179: 
180: 
181:     /**
182:      * Import SQL dump from file - extremely fast.
183:      * @return int  count of commands
184:      */
185:     public static function loadFromFile(Connection $connection, $file)
186:     {
187:         @set_time_limit(0); // @ function may be disabled
188: 
189:         $handle = @fopen($file, 'r'); // @ is escalated to exception
190:         if (!$handle) {
191:             throw new Nette\FileNotFoundException("Cannot open file '$file'.");
192:         }
193: 
194:         $count = 0;
195:         $delimiter = ';';
196:         $sql = '';
197:         $pdo = $connection->getPdo(); // native query without logging
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:                 $pdo->exec($sql);
206:                 $sql = '';
207:                 $count++;
208: 
209:             } else {
210:                 $sql .= $s . "\n";
211:             }
212:         }
213:         if (trim($sql) !== '') {
214:             $pdo->exec($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:      * Reformat source to key -> value pairs.
234:      * @return array
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: