3 * FusionForge PostgreSQL file parser
5 * Copyright 2002-2008, Roland Mas (Perl implementation)
6 * Copyright 2011, Roland Mas (PHP rewrite)
8 * This file is part of FusionForge. FusionForge is free software;
9 * you can redistribute it and/or modify it under the terms of the
10 * GNU General Public License as published by the Free Software
11 * Foundation; either version 2 of the Licence, or (at your option)
14 * FusionForge is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17 * GNU General Public License for more details.
19 * You should have received a copy of the GNU General Public License along
20 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
21 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
24 function get_next_line($f) {
29 $l = rtrim($l, "\n\r");
33 define ('GREEN', "\033[01;32m" );
34 define ('NORMAL', "\033[00m" );
35 define ('RED', "\033[01;31m" );
37 function parse_sql_file($filename) {
38 $f = fopen($filename, 'r');
40 error_log("$filename not found");
44 $states = array ('INIT' => 0,
55 'IN_SQL_COMMENT' => 11,
59 $names = array_flip($states);
61 $state = $states['INIT'];
65 $doldolstack = array();
74 $copy_data_tmp = array();
77 while ($state != $states['DONE']) {
78 error_log("STATE_LOOP: state=$names[$state], l=".RED.$l.NORMAL.", chunk=".RED.$chunk.NORMAL.", rest=".RED.$rest.NORMAL);
79 error_log(RED."<".GREEN.$sql.RED.">".NORMAL);
86 $l = get_next_line($f);
88 $state = $states['DONE'];
92 $state = $states['SCAN'];
97 if (($l == '') || preg_match('/^\s*$/', $l) || preg_match('/^\s*--/', $l)) {
98 $l = get_next_line($f);
100 $state = $states['DONE'];
104 } elseif (preg_match('/\s*copy\s+\"[\w_]+\"\s*(\([\w, "]+\))?\s*from\s+stdin\s*;/i', $l)
105 || preg_match('/\s*copy\s+[\w_]+\s*(\([\w, "]+\))?\s*from\s+stdin\s*;/i', $l)) {
106 $state = $states['START_COPY'];
110 $state = $states['SQL_SCAN'];
113 break; // End of SCAN
115 case $states['IN_COMMENT']:
116 // error_log('IN_COMMENT');
117 if (($l == '') || preg_match('/^\s*$/', $l)) {
118 $l = get_next_line($f);
120 error_log("End of file reached during a comment");
121 $state = $states['ERROR'];
124 $state = $states['IN_COMMENT'];
126 } elseif (preg_match(',\*/,', $l) || preg_match(',/\*,', $l)) {
127 $l = preg_replace(',.*?((/\*)|(\*/)),', '$1', $l, 1);
128 $chunk = substr($l,0,2);
129 $rest = substr($l,2);
130 if ($chunk == '/*') {
135 if ($com_level == 0) {
136 $state = $states['SQL_SCAN'];
139 $state = $states['IN_COMMENT'];
143 $l = get_next_line($f);
145 error_log("End of file reached during a comment");
146 $state = $states['ERROR'];
149 $state = $states['IN_COMMENT'];
152 break; // End of IN_COMMENT
154 case $states['IN_SQL_COMMENT']:
155 // error_log('IN_SQL_COMMENT');
156 if (($rest == '') || preg_match('/^\s*$/')) {
157 $rest = get_next_line($f);
158 if ($rest === false) {
159 error_log("End of file reached during a comment");
160 $state = $states['ERROR'];
163 $state = $states['IN_SQL_COMMENT'];
165 } elseif (preg_match(',\*/,', $rest) || preg_match(',/\*,', $rest)) {
166 $rest = preg_replace(',.*?((/\*)|(\*/)),', '$1', $l, 1);
167 $chunk = substr($rest,0,2);
168 $rest = substr($rest,2);
169 if ($chunk == '/*') {
174 if ($com_level == 0) {
175 $state = $states['IN_SQL'];
178 $state = $states['IN_SQL_COMMENT'];
182 $rest = get_next_line($f);
183 if ($rest === false) {
184 error_log("End of file reached during a comment");
185 $state = $states['ERROR'];
188 $state = $states['IN_SQL_COMMENT'];
191 break; // End of IN_SQL_COMMENT
193 case $states['SQL_SCAN']:
194 // error_log('SQL_SCAN');
195 if (($l == '') || preg_match('/^\s*$/', $l) || preg_match('/^--/', $l)) {
196 $l = get_next_line($f);
198 error_log("End of file reached during an SQL statement");
199 $state = $states['ERROR'];
202 $state = $states['SQL_SCAN'];
204 } elseif (preg_match(',^\s*/\*,', $l)) {
205 $l = preg_replace(',^\s*/\*,','',$l, 1);
207 $state = $states['IN_COMMENT'];
209 } elseif (preg_match(',^(.*?)\$([\w]*)\$,', $l, $matches)) {
210 $sql .= $matches[1].'$'.$matches[2].'$';
211 array_push($doldolstack,$matches[2]);
212 $l = preg_replace(',^(.*?)\$[\w]*\$,','',$l, 1);
213 $state = $states['IN_DOLDOL'];
216 preg_match(',^([^()\';-]*)(.*),', $l, $matches);
217 $chunk = $matches[1];
220 $state = $states['IN_SQL'];
224 break; // End of SQL_SCAN
226 case $states['IN_SQL']:
227 // error_log('IN_SQL');
228 if (preg_match(',^\s*/\*,',$rest)) {
229 $rest = preg_replace(',^\s*/\*,','',$rest, 1);
231 $state = $states['IN_SQL_COMMENT'];
233 } elseif (preg_match('/^\(/', $rest)) {
236 $rest = substr($rest, 1);
238 $state = $states['SQL_SCAN'];
240 } elseif (preg_match('/^\)/', $rest) && $par_level > 0) {
243 $rest = substr($rest, 1);
245 $state = $states['SQL_SCAN'];
247 } elseif (preg_match('/^\)/', $rest)) {
248 error_log("Detected ')' without any matching '('");
249 $state = $states['ERROR'];
251 } elseif (preg_match('/^--/', $rest)) {
254 $state = $states['SQL_SCAN'];
256 } elseif (preg_match('/^-/', $rest)) {
258 $rest = substr($rest, 1);
260 $state = $states['SQL_SCAN'];
262 } elseif (preg_match('/^;/', $rest) && ($par_level == 0)) {
264 $rest = substr($rest, 1);
265 $state = $states['END_SQL'];
267 } elseif (preg_match('/^;/', $rest)) {
268 error_log("Detected ';' within a parenthesis");
269 $state = $states['ERROR'];
271 } elseif ($rest == '') {
274 $state = $states['SQL_SCAN'];
276 } elseif (preg_match("/^\\'/", $rest)) {
279 $rest = substr($rest, 1);
281 $state = $states['IN_QUOTE'];
284 error_log("Unknown event in IN_SQL state");
285 $state = $states['ERROR'];
288 break; // End of IN_SQL
290 case $states['END_SQL']:
291 // error_log('END_SQL');
292 if (preg_match('/^\s*$/', $sql)) {
295 $state = $states['SCAN'];
298 array_push($sql_list, $sql);
299 error_log(RED."---->".$sql.NORMAL);
302 $state = $states['SCAN'];
305 break; // End of END_SQL
307 case $states['QUOTE_SCAN']:
308 // error_log('QUOTE_SCAN');
311 $l = get_next_line($f);
313 error_log("End of file reached during a quoted string");
314 $state = $states['ERROR'];
318 $state = $states['QUOTE_SCAN'];
321 preg_match("/^([^\\\']*)(.*)/", $l, $matches);
322 $chunk = $matches[1];
325 $state = $states['IN_QUOTE'];
328 break; // End of QUOTE_SCAN
330 case $states['IN_QUOTE']:
331 // error_log('IN_QUOTE');
332 if (preg_match("/^'/", $rest)) {
335 $rest = substr($rest, 1);
337 $state = $states['SQL_SCAN'];
339 } elseif (preg_match("/^\\\'/", $rest)) {
342 $rest = substr($rest, 2);
343 $state = $states['IN_QUOTE'];
345 } elseif (preg_match("/^\\\[^\\\]/", $rest)) {
348 $rest = substr($rest, 1);
349 $state = $states['IN_QUOTE'];
351 } elseif (preg_match('/^\\\$/', $rest)) {
354 $rest = substr($rest, 1);
355 $state = $states['IN_QUOTE'];
360 $state = $states['QUOTE_SCAN'];
363 break; // End of IN_QUOTE
365 case $states['IN_DOLDOL']:
366 // error_log('IN_DOLDOL');
367 $cur = $doldolstack[0];
368 if (preg_match(",^(.*?)\\$([\w]*)\\$,", $l, $matches)) {
369 $sql .= $matches[1].'$'.$matches[2].'$';
370 $found = $matches[2];
371 if ($found == $cur) {
372 array_pop($doldolstack);
373 if (count($doldolstack) > 0) {
374 $state = $states['IN_DOLDOL'];
377 $rest = preg_replace(",^.*?\\$[\w]*\\$,",'',$l, 1);
378 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
379 $state = $states['SQL_SCAN'];
383 array_push($doldolstack, $found);
384 $state = $states['IN_DOLDOL'];
387 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
388 $state = $states['IN_DOLDOL'];
392 $l = get_next_line($f);
394 error_log("End of file reached during a dollar-quoted string");
395 $state = $states['ERROR'];
398 $state = $states['IN_DOLDOL'];
401 break; // End of IN_DOLDOL
403 case $states['START_COPY']:
404 // error_log('START_COPY');
405 if (preg_match('/\s*copy\s+\"([\w_]+)\"\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
406 $copy_table = $matches[1];
407 $copy_field_list = trim($matches[2]);
408 if ($copy_field_list != '') {
409 $copy_field_list = ' '.$copy_field_list;
411 $copy_rest = $matches[3];
412 $l = get_next_line($f);
414 error_log("End of file reached during a COPY statement");
415 $state = $states['ERROR'];
418 $state = $states['IN_COPY'];
420 } elseif (preg_match('/\s*copy\s+([\w_]+)\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
421 $copy_table = $matches[1];
422 $copy_field_list = trim($matches[2]);
423 if ($copy_field_list != '') {
424 $copy_field_list = ' '.$copy_field_list;
426 $copy_rest = $matches[3];
427 $l = get_next_line($f);
429 error_log("End of file reached during a COPY statement");
430 $state = $states['ERROR'];
433 $state = $states['IN_COPY'];
436 error_log("Unknown event in START_COPY state");
439 break; // End of START_COPY
441 case $states['IN_COPY']:
442 // error_log('IN_COPY');
445 $state = $states['SCAN'];
448 $copy_data = array();
449 $copy_data_tmp = explode ("\t", $l);
450 foreach ($copy_data_tmp as $copy_field) {
451 if ($copy_field == '\N') {
452 $copy_field = 'NULL';
454 $copy_field = preg_replace('/\'/','\'\'', $copy_field);
455 $copy_field = "'".$copy_field."'";
457 array_push($copy_data, $copy_field);
459 $sql = "INSERT INTO \"$copy_table\"$copy_field_list VALUES (";
460 $sql .= implode (', ', $copy_data);
462 array_push($sql_list, $sql);
463 error_log(RED."---->".$sql.NORMAL);
464 $l = get_next_line($f);
466 error_log("End of file reached during a COPY statement");
467 $state = $states['ERROR'];
470 $state = $states['IN_COPY'];
473 break; // End of IN_COPY
475 case $states['DONE']:
476 // error_log('DONE');
478 break; // End of DONE
480 case $states['ERROR']:
481 // error_log('ERROR');
482 error_log("Reached the ERROR state, dying. State machine is buggy.");
484 break; // End of ERROR
487 error_log("State machine went to an unknown state, redirecting to ERROR");
488 $state = $states['ERROR'];
491 // error_log("State=$names[$state] after switch");
500 // c-file-style: "bsd"