Namespaces

  • Nette
    • Application
      • Diagnostics
      • Responses
      • Routers
      • UI
    • Caching
      • Storages
    • ComponentModel
    • Database
      • Diagnostics
      • Drivers
      • Reflection
      • Table
    • DI
      • Config
        • Adapters
      • Diagnostics
      • Extensions
    • Diagnostics
    • Forms
      • Controls
      • Rendering
    • Http
      • Diagnostics
    • Iterators
    • Latte
      • Macros
    • Loaders
    • Localization
    • Mail
    • PhpGenerator
    • Reflection
    • Security
      • Diagnostics
    • Templating
    • Utils
  • NetteModule
  • none

Classes

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

Interfaces

  • IReflection
  • IRow
  • IRowContainer
  • ISupplementalDriver
  • Overview
  • Namespace
  • Class
  • Tree
  • Deprecated
  1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (http://nette.org)
  5:  * Copyright (c) 2004 David Grudl (http://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database;
  9: 
 10: use Nette;
 11: 
 12: 
 13: /**
 14:  * Database helpers.
 15:  *
 16:  * @author     David Grudl
 17:  */
 18: class Helpers
 19: {
 20:     /** @var int maximum SQL length */
 21:     static public $maxLength = 100;
 22: 
 23:     /** @var array */
 24:     public static $typePatterns = array(
 25:         '^_' => IReflection::FIELD_TEXT, // PostgreSQL arrays
 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, // DATETIME, TIMESTAMP
 32:         'DATE' => IReflection::FIELD_DATE,
 33:         'BOOL' => IReflection::FIELD_BOOL,
 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()) . "</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:      * Returns syntax highlighted SQL command.
 75:      * @param  string
 76:      * @return string
 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:         // insert new lines
 84:         $sql = " $sql ";
 85:         $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
 86: 
 87:         // reduce spaces
 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:         // syntax highlight
 94:         $sql = htmlSpecialChars($sql);
 95:         $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function($matches) {
 96:             if (!empty($matches[1])) { // comment
 97:                 return '<em style="color:gray">' . $matches[1] . '</em>';
 98: 
 99:             } elseif (!empty($matches[2])) { // error
100:                 return '<strong style="color:red">' . $matches[2] . '</strong>';
101: 
102:             } elseif (!empty($matches[3])) { // most important keywords
103:                 return '<strong style="color:blue">' . $matches[3] . '</strong>';
104: 
105:             } elseif (!empty($matches[4])) { // other keywords
106:                 return '<strong style="color:green">' . $matches[4] . '</strong>';
107:             }
108:         }, $sql);
109: 
110:         // parameters
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">&lt;binary&gt;</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) . '>&lt;' . htmlSpecialChars($type) . ' resource&gt;</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:      * Common column type detection.
141:      * @return array
142:      */
143:     public static function detectTypes(\PDOStatement $statement)
144:     {
145:         $types = array();
146:         $count = $statement->columnCount(); // driver must be meta-aware, see PHP bugs #53782, #54695
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:      * Heuristic column type detection.
159:      * @param  string
160:      * @return string
161:      * @internal
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:      * Import SQL dump from file - extremely fast.
180:      * @return int  count of commands
181:      */
182:     public static function loadFromFile(Connection $connection, $file)
183:     {
184:         @set_time_limit(0); // intentionally @
185: 
186:         $handle = @fopen($file, 'r'); // intentionally @
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); // native query without logging
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:      * Reformat source to key -> value pairs.
223:      * @return array
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: 
Nette Framework 2.1.8 API API documentation generated by ApiGen 2.8.0