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)) {
334 $rest = substr($rest, 1);
336 $state = $states['SQL_SCAN'];
338 } elseif (preg_match("/^\\\'/", $rest)) {
340 $rest = substr($rest, 2);
341 $state = $states['IN_QUOTE'];
343 } elseif (preg_match("/^\\\[^\\\]/", $rest)) {
345 $rest = substr($rest, 1);
346 $state = $states['IN_QUOTE'];
348 } elseif (preg_match('/^\\\$/', $rest)) {
350 $rest = substr($rest, 1);
351 $state = $states['IN_QUOTE'];
355 $state = $states['QUOTE_SCAN'];
358 break; // End of IN_QUOTE
360 case $states['IN_DOLDOL']:
361 // error_log('IN_DOLDOL');
362 $cur = $doldolstack[0];
363 if (preg_match(",^(.*?)\\$([\w]*)\\$,", $l, $matches)) {
364 $sql .= $matches[1].'$'.$matches[2].'$';
365 $found = $matches[2];
366 if ($found == $cur) {
367 array_pop($doldolstack);
368 if (count($doldolstack) > 0) {
369 $state = $states['IN_DOLDOL'];
372 $rest = preg_replace(",^.*?\\$[\w]*\\$,",'',$l, 1);
373 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
374 $state = $states['SQL_SCAN'];
378 array_push($doldolstack, $found);
379 $state = $states['IN_DOLDOL'];
382 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
383 $state = $states['IN_DOLDOL'];
387 $l = get_next_line($f);
389 error_log("End of file reached during a dollar-quoted string");
390 $state = $states['ERROR'];
393 $state = $states['IN_DOLDOL'];
396 break; // End of IN_DOLDOL
398 case $states['START_COPY']:
399 // error_log('START_COPY');
400 if (preg_match('/\s*copy\s+\"([\w_]+)\"\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
401 $copy_table = $matches[1];
402 $copy_field_list = trim($matches[2]);
403 if ($copy_field_list != '') {
404 $copy_field_list = ' '.$copy_field_list;
406 $copy_rest = $matches[3];
407 $l = get_next_line($f);
409 error_log("End of file reached during a COPY statement");
410 $state = $states['ERROR'];
413 $state = $states['IN_COPY'];
415 } elseif (preg_match('/\s*copy\s+([\w_]+)\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
416 $copy_table = $matches[1];
417 $copy_field_list = trim($matches[2]);
418 if ($copy_field_list != '') {
419 $copy_field_list = ' '.$copy_field_list;
421 $copy_rest = $matches[3];
422 $l = get_next_line($f);
424 error_log("End of file reached during a COPY statement");
425 $state = $states['ERROR'];
428 $state = $states['IN_COPY'];
431 error_log("Unknown event in START_COPY state");
434 break; // End of START_COPY
436 case $states['IN_COPY']:
437 // error_log('IN_COPY');
440 $state = $states['SCAN'];
443 $copy_data = array();
444 $copy_data_tmp = explode ("\t", $l);
445 foreach ($copy_data_tmp as $copy_field) {
446 if ($copy_field == '\N') {
447 $copy_field = 'NULL';
449 $copy_field = preg_replace('/\'/','\'\'', $copy_field);
450 $copy_field = "'".$copy_field."'";
452 array_push($copy_data, $copy_field);
454 $sql = "INSERT INTO \"$copy_table\"$copy_field_list VALUES (";
455 $sql .= implode (', ', $copy_data);
457 array_push($sql_list, $sql);
458 // error_log(RED."---->".$sql.NORMAL);
459 $l = get_next_line($f);
461 error_log("End of file reached during a COPY statement");
462 $state = $states['ERROR'];
465 $state = $states['IN_COPY'];
468 break; // End of IN_COPY
470 case $states['DONE']:
471 // error_log('DONE');
473 break; // End of DONE
475 case $states['ERROR']:
476 // error_log('ERROR');
477 error_log("Reached the ERROR state, dying. State machine is buggy.");
479 break; // End of ERROR
482 error_log("State machine went to an unknown state, redirecting to ERROR");
483 $state = $states['ERROR'];
486 // error_log("State=$names[$state] after switch");
495 // c-file-style: "bsd"