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:  * @package Nette\Database
  7:  */
  8: 
  9: 
 10: 
 11: /**
 12:  * Database helpers.
 13:  *
 14:  * @author     David Grudl
 15:  * @package Nette\Database
 16:  */
 17: class NDatabaseHelpers
 18: {
 19:     /** @var array */
 20:     public static $typePatterns = array(
 21:         '^_' => IReflection::FIELD_TEXT, // PostgreSQL arrays
 22:         'BYTEA|BLOB|BIN' => IReflection::FIELD_BINARY,
 23:         'TEXT|CHAR|POINT|INTERVAL' => IReflection::FIELD_TEXT,
 24:         'YEAR|BYTE|COUNTER|SERIAL|INT|LONG|SHORT|^TINY$' => IReflection::FIELD_INTEGER,
 25:         'CURRENCY|REAL|MONEY|FLOAT|DOUBLE|DECIMAL|NUMERIC|NUMBER' => IReflection::FIELD_FLOAT,
 26:         '^TIME$' => IReflection::FIELD_TIME,
 27:         'TIME' => IReflection::FIELD_DATETIME, // DATETIME, TIMESTAMP
 28:         'DATE' => IReflection::FIELD_DATE,
 29:         'BOOL' => IReflection::FIELD_BOOL,
 30:     );
 31: 
 32: 
 33:     /**
 34:      * Displays complete result set as HTML table for debug purposes.
 35:      * @return void
 36:      */
 37:     public static function dumpResult(NStatement $statement)
 38:     {
 39:         echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($statement->queryString) . "</caption>\n";
 40:         if (!$statement->columnCount()) {
 41:             echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $statement->rowCount(), "</td>\n\t</tr>\n</table>\n";
 42:             return;
 43:         }
 44:         $i = 0;
 45:         foreach ($statement as $row) {
 46:             if ($i === 0) {
 47:                 echo "<thead>\n\t<tr>\n\t\t<th>#row</th>\n";
 48:                 foreach ($row as $col => $foo) {
 49:                     echo "\t\t<th>" . htmlSpecialChars($col) . "</th>\n";
 50:                 }
 51:                 echo "\t</tr>\n</thead>\n<tbody>\n";
 52:             }
 53:             echo "\t<tr>\n\t\t<th>", $i, "</th>\n";
 54:             foreach ($row as $col) {
 55:                 echo "\t\t<td>", htmlSpecialChars($col), "</td>\n";
 56:             }
 57:             echo "\t</tr>\n";
 58:             $i++;
 59:         }
 60: 
 61:         if ($i === 0) {
 62:             echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
 63:         } else {
 64:             echo "</tbody>\n</table>\n";
 65:         }
 66:     }
 67: 
 68: 
 69:     /**
 70:      * Returns syntax highlighted SQL command.
 71:      * @param  string
 72:      * @return string
 73:      */
 74:     public static function dumpSql($sql)
 75:     {
 76:         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';
 77:         static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|[RI]?LIKE|REGEXP|TRUE|FALSE';
 78: 
 79:         // insert new lines
 80:         $sql = " $sql ";
 81:         $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
 82: 
 83:         // reduce spaces
 84:         $sql = preg_replace('#[ \t]{2,}#', ' ', $sql);
 85: 
 86:         $sql = wordwrap($sql, 100);
 87:         $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
 88: 
 89:         // syntax highlight
 90:         $sql = htmlSpecialChars($sql);
 91:         $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", create_function('$matches', '
 92:             if (!empty($matches[1])) { // comment
 93:                 return \'<em style="color:gray">\' . $matches[1] . \'</em>\';
 94: 
 95:             } elseif (!empty($matches[2])) { // error
 96:                 return \'<strong style="color:red">\' . $matches[2] . \'</strong>\';
 97: 
 98:             } elseif (!empty($matches[3])) { // most important keywords
 99:                 return \'<strong style="color:blue">\' . $matches[3] . \'</strong>\';
100: 
101:             } elseif (!empty($matches[4])) { // other keywords
102:                 return \'<strong style="color:green">\' . $matches[4] . \'</strong>\';
103:             }
104:         '), $sql);
105: 
106:         return '<pre class="dump">' . trim($sql) . "</pre>\n";
107:     }
108: 
109: 
110:     /**
111:      * Heuristic type detection.
112:      * @param  string
113:      * @return string
114:      * @internal
115:      */
116:     public static function detectType($type)
117:     {
118:         static $cache;
119:         if (!isset($cache[$type])) {
120:             $cache[$type] = 'string';
121:             foreach (self::$typePatterns as $s => $val) {
122:                 if (preg_match("#$s#i", $type)) {
123:                     return $cache[$type] = $val;
124:                 }
125:             }
126:         }
127:         return $cache[$type];
128:     }
129: 
130: 
131:     /**
132:      * Import SQL dump from file - extremely fast.
133:      * @return int  count of commands
134:      */
135:     public static function loadFromFile(NConnection $connection, $file)
136:     {
137:         @set_time_limit(0); // intentionally @
138: 
139:         $handle = @fopen($file, 'r'); // intentionally @
140:         if (!$handle) {
141:             throw new FileNotFoundException("Cannot open file '$file'.");
142:         }
143: 
144:         $count = 0;
145:         $sql = '';
146:         while (!feof($handle)) {
147:             $s = fgets($handle);
148:             $sql .= $s;
149:             if (substr(rtrim($s), -1) === ';') {
150:                 $connection->query($sql); // native query without logging
151:                 $sql = '';
152:                 $count++;
153:             }
154:         }
155:         if (trim($sql) !== '') {
156:             $connection->query($sql);
157:             $count++;
158:         }
159:         fclose($handle);
160:         return $count;
161:     }
162: 
163: }
164: