Namespaces

  • Latte
    • Loaders
    • Macros
    • Runtime
  • Nette
    • Application
      • Responses
      • Routers
      • UI
    • Bridges
      • ApplicationDI
      • ApplicationLatte
      • ApplicationTracy
      • CacheDI
      • CacheLatte
      • DatabaseDI
      • DatabaseTracy
      • DITracy
      • FormsDI
      • FormsLatte
      • Framework
      • HttpDI
      • HttpTracy
      • MailDI
      • ReflectionDI
      • SecurityDI
      • SecurityTracy
    • Caching
      • Storages
    • ComponentModel
    • Database
      • Conventions
      • Drivers
      • Table
    • DI
      • Config
        • Adapters
      • Extensions
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Loaders
    • Localization
    • Mail
    • Neon
    • PhpGenerator
      • Traits
    • Reflection
    • Security
    • Tokenizer
    • Utils
  • Tracy
    • Bridges
      • Nette
  • none

Classes

  • Connection
  • Context
  • Helpers
  • ResultSet
  • Row
  • SqlLiteral
  • SqlPreprocessor
  • Structure

Interfaces

  • IConventions
  • IRow
  • IRowContainer
  • IStructure
  • ISupplementalDriver

Exceptions

  • ConnectionException
  • ConstraintViolationException
  • DriverException
  • ForeignKeyConstraintViolationException
  • NotNullConstraintViolationException
  • UniqueConstraintViolationException
  • Overview
  • Namespace
  • Class
  • Tree
  • Deprecated
  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:     use Nette\StaticClass;
 20: 
 21:     /** @var int maximum SQL length */
 22:     public static $maxLength = 100;
 23: 
 24:     /** @var array */
 25:     public static $typePatterns = [
 26:         '^_' => IStructure::FIELD_TEXT, // PostgreSQL arrays
 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:      * Displays complete result set as HTML table for debug purposes.
 39:      * @return void
 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:      * Returns syntax highlighted SQL command.
 82:      * @param  string
 83:      * @return string
 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:         // insert new lines
 91:         $sql = " $sql ";
 92:         $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
 93: 
 94:         // reduce spaces
 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:         // syntax highlight
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])) { // comment
104:                 return '<em style="color:gray">' . $matches[1] . '</em>';
105: 
106:             } elseif (!empty($matches[2])) { // error
107:                 return '<strong style="color:red">' . $matches[2] . '</strong>';
108: 
109:             } elseif (!empty($matches[3])) { // most important keywords
110:                 return '<strong style="color:blue">' . $matches[3] . '</strong>';
111: 
112:             } elseif (!empty($matches[4])) { // other keywords
113:                 return '<strong style="color:green">' . $matches[4] . '</strong>';
114:             }
115:         }, $sql);
116: 
117:         // parameters
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">&lt;binary&gt;</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:                     . '>&lt;' . htmlspecialchars($type, ENT_NOQUOTES, 'UTF-8') . ' resource&gt;</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:      * Common column type detection.
152:      * @return array
153:      */
154:     public static function detectTypes(\PDOStatement $statement)
155:     {
156:         $types = [];
157:         $count = $statement->columnCount(); // driver must be meta-aware, see PHP bugs #53782, #54695
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:      * Heuristic column type detection.
170:      * @param  string
171:      * @return string
172:      * @internal
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:      * Import SQL dump from file - extremely fast.
191:      * @return int  count of commands
192:      */
193:     public static function loadFromFile(Connection $connection, $file)
194:     {
195:         @set_time_limit(0); // @ function may be disabled
196: 
197:         $handle = @fopen($file, 'r'); // @ is escalated to exception
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(); // native query without logging
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:      * Reformat source to key -> value pairs.
242:      * @return array
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:      * Finds duplicate columns in select statement
279:      * @param  \PDOStatement
280:      * @return string
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: 
Nette 2.4-20191120 API API documentation generated by ApiGen 2.8.0