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