1: <?php
2:
3: 4: 5: 6: 7: 8: 9: 10:
11:
12: namespace Nette\Database;
13:
14: use Nette;
15:
16:
17: 18: 19: 20: 21:
22: class Helpers
23: {
24:
25: public static $typePatterns = array(
26: '^_' => IReflection::FIELD_TEXT,
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,
33: 'DATE' => IReflection::FIELD_DATE,
34: 'BOOL' => IReflection::FIELD_BOOL,
35: );
36:
37:
38: 39: 40: 41:
42: public static function dumpResult(Statement $statement)
43: {
44: echo "\n<table class=\"dump\">\n<caption>" . htmlSpecialChars($statement->queryString) . "</caption>\n";
45: if (!$statement->columnCount()) {
46: echo "\t<tr>\n\t\t<th>Affected rows:</th>\n\t\t<td>", $statement->rowCount(), "</td>\n\t</tr>\n</table>\n";
47: return;
48: }
49: $i = 0;
50: foreach ($statement 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) . "</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:
61: echo "\t\t<td>", htmlSpecialChars($col), "</td>\n";
62: }
63: echo "\t</tr>\n";
64: $i++;
65: }
66:
67: if ($i === 0) {
68: echo "\t<tr>\n\t\t<td><em>empty result set</em></td>\n\t</tr>\n</table>\n";
69: } else {
70: echo "</tbody>\n</table>\n";
71: }
72: }
73:
74:
75: 76: 77: 78: 79:
80: public static function dumpSql($sql)
81: {
82: 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';
83: static $keywords2 = 'ALL|DISTINCT|DISTINCTROW|IGNORE|AS|USING|ON|AND|OR|IN|IS|NOT|NULL|LIKE|RLIKE|REGEXP|TRUE|FALSE';
84:
85:
86: $sql = " $sql ";
87: $sql = preg_replace("#(?<=[\\s,(])($keywords1)(?=[\\s,)])#i", "\n\$1", $sql);
88:
89:
90: $sql = preg_replace('#[ \t]{2,}#', " ", $sql);
91:
92: $sql = wordwrap($sql, 100);
93: $sql = preg_replace('#([ \t]*\r?\n){2,}#', "\n", $sql);
94:
95:
96: $sql = htmlSpecialChars($sql);
97: $sql = preg_replace_callback("#(/\\*.+?\\*/)|(\\*\\*.+?\\*\\*)|(?<=[\\s,(])($keywords1)(?=[\\s,)])|(?<=[\\s,(=])($keywords2)(?=[\\s,)=])#is", function($matches) {
98: if (!empty($matches[1])) {
99: return '<em style="color:gray">' . $matches[1] . '</em>';
100:
101: } elseif (!empty($matches[2])) {
102: return '<strong style="color:red">' . $matches[2] . '</strong>';
103:
104: } elseif (!empty($matches[3])) {
105: return '<strong style="color:blue">' . $matches[3] . '</strong>';
106:
107: } elseif (!empty($matches[4])) {
108: return '<strong style="color:green">' . $matches[4] . '</strong>';
109: }
110: }, $sql);
111:
112: return '<pre class="dump">' . trim($sql) . "</pre>\n";
113: }
114:
115:
116: 117: 118: 119: 120: 121:
122: public static function detectType($type)
123: {
124: static $cache;
125: if (!isset($cache[$type])) {
126: $cache[$type] = 'string';
127: foreach (self::$typePatterns as $s => $val) {
128: if (preg_match("#$s#i", $type)) {
129: return $cache[$type] = $val;
130: }
131: }
132: }
133: return $cache[$type];
134: }
135:
136:
137: 138: 139: 140:
141: public static function loadFromFile(Connection $connection, $file)
142: {
143: @set_time_limit(0);
144:
145: $handle = @fopen($file, 'r');
146: if (!$handle) {
147: throw new Nette\FileNotFoundException("Cannot open file '$file'.");
148: }
149:
150: $count = 0;
151: $sql = '';
152: while (!feof($handle)) {
153: $s = fgets($handle);
154: $sql .= $s;
155: if (substr(rtrim($s), -1) === ';') {
156: $connection->query($sql);
157: $sql = '';
158: $count++;
159: }
160: }
161: if (trim($sql) !== '') {
162: $connection->query($sql);
163: $count++;
164: }
165: fclose($handle);
166: return $count;
167: }
168:
169: }
170: