3 * FusionForge PostgreSQL file parser
5 * Copyright 2002-2008, Roland Mas (Perl implementation)
6 * Copyright 2011, Roland Mas (PHP rewrite)
7 * Copyright 2019, Franck Villaume - TrvialDev
9 * This file is part of FusionForge. FusionForge is free software;
10 * you can redistribute it and/or modify it under the terms of the
11 * GNU General Public License as published by the Free Software
12 * Foundation; either version 2 of the Licence, or (at your option)
15 * FusionForge is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
20 * You should have received a copy of the GNU General Public License along
21 * with FusionForge; if not, write to the Free Software Foundation, Inc.,
22 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
31 function get_next_line($f) {
36 $l = rtrim($l, "\n\r");
40 define ('GREEN', "\033[01;32m" );
41 define ('NORMAL', "\033[00m" );
42 define ('RED', "\033[01;31m" );
47 * @param string $filename
50 function parse_sql_file($filename) {
51 $f = fopen($filename, 'r');
53 error_log("$filename not found");
57 $states = array ('INIT' => 0,
68 'IN_SQL_COMMENT' => 11,
72 $names = array_flip($states);
74 $state = $states['INIT'];
78 $doldolstack = array();
87 $copy_data_tmp = array();
90 while ($state != $states['DONE']) {
91 // error_log("STATE_LOOP: state=$names[$state], l=".RED.$l.NORMAL.", chunk=".RED.$chunk.NORMAL.", rest=".RED.$rest.NORMAL);
92 // error_log(RED."<".GREEN.$sql.RED.">".NORMAL);
99 $l = get_next_line($f);
101 $state = $states['DONE'];
105 $state = $states['SCAN'];
106 break; // End of INIT
108 case $states['SCAN']:
109 // error_log('SCAN');
110 if (($l == '') || preg_match('/^\s*$/', $l) || preg_match('/^\s*--/', $l)) {
111 $l = get_next_line($f);
113 $state = $states['DONE'];
116 } elseif (preg_match('/\s*copy\s+\"[\w_]+\"\s*(\([\w, "]+\))?\s*from\s+stdin\s*;/i', $l)
117 || preg_match('/\s*copy\s+[\w_]+\s*(\([\w, "]+\))?\s*from\s+stdin\s*;/i', $l)) {
118 $state = $states['START_COPY'];
121 $state = $states['SQL_SCAN'];
123 break; // End of SCAN
125 case $states['IN_COMMENT']:
126 // error_log('IN_COMMENT');
127 if (preg_match(',\*/,', $l) || preg_match(',/\*,', $l)) {
128 $l = preg_replace(',.*?((/\*)|(\*/)),', '$1', $l, 1);
129 $chunk = substr($l,0,2);
130 $rest = substr($l,2);
131 if ($chunk == '/*') {
136 if ($com_level == 0) {
137 $state = $states['SQL_SCAN'];
140 $state = $states['IN_COMMENT'];
144 $l = get_next_line($f);
146 error_log("End of file reached during a comment");
147 $state = $states['ERROR'];
150 $state = $states['IN_COMMENT'];
152 break; // End of IN_COMMENT
154 case $states['IN_SQL_COMMENT']:
155 // error_log('IN_SQL_COMMENT');
156 if (preg_match(',\*/,', $rest) || preg_match(',/\*,', $rest)) {
157 $rest = preg_replace(',.*?((/\*)|(\*/)),', '$1', $l, 1);
158 $chunk = substr($rest,0,2);
159 $rest = substr($rest,2);
160 if ($chunk == '/*') {
165 if ($com_level == 0) {
166 $state = $states['IN_SQL'];
169 $state = $states['IN_SQL_COMMENT'];
173 $rest = get_next_line($f);
174 if ($rest === false) {
175 error_log("End of file reached during a comment");
176 $state = $states['ERROR'];
179 $state = $states['IN_SQL_COMMENT'];
181 break; // End of IN_SQL_COMMENT
183 case $states['SQL_SCAN']:
184 // error_log('SQL_SCAN');
185 if (($l == '') || preg_match('/^\s*$/', $l) || preg_match('/^--/', $l)) {
186 $l = get_next_line($f);
188 error_log("End of file reached during an SQL statement");
189 $state = $states['ERROR'];
192 $state = $states['SQL_SCAN'];
193 } elseif (preg_match(',^\s*/\*,', $l)) {
194 $l = preg_replace(',^\s*/\*,','',$l, 1);
196 $state = $states['IN_COMMENT'];
197 } elseif (preg_match(',^(.*?)\$([\w]*)\$,', $l, $matches)) {
198 $sql .= $matches[1].'$'.$matches[2].'$';
199 array_push($doldolstack,$matches[2]);
200 $l = preg_replace(',^(.*?)\$[\w]*\$,','',$l, 1);
201 $state = $states['IN_DOLDOL'];
203 preg_match(',^([^()\';-]*)(.*),', $l, $matches);
204 $chunk = $matches[1];
207 $state = $states['IN_SQL'];
210 break; // End of SQL_SCAN
212 case $states['IN_SQL']:
213 // error_log('IN_SQL');
214 if (preg_match(',^\s*/\*,',$rest)) {
215 $rest = preg_replace(',^\s*/\*,','',$rest, 1);
217 $state = $states['IN_SQL_COMMENT'];
218 } elseif (preg_match('/^\(/', $rest)) {
221 $rest = substr($rest, 1);
223 $state = $states['SQL_SCAN'];
224 } elseif (preg_match('/^\)/', $rest) && $par_level > 0) {
227 $rest = substr($rest, 1);
229 $state = $states['SQL_SCAN'];
230 } elseif (preg_match('/^\)/', $rest)) {
231 error_log("Detected ')' without any matching '('");
232 $state = $states['ERROR'];
233 } elseif (preg_match('/^--/', $rest)) {
236 $state = $states['SQL_SCAN'];
237 } elseif (preg_match('/^-/', $rest)) {
239 $rest = substr($rest, 1);
241 $state = $states['SQL_SCAN'];
242 } elseif (preg_match('/^;/', $rest) && ($par_level == 0)) {
244 $rest = substr($rest, 1);
245 $state = $states['END_SQL'];
246 } elseif (preg_match('/^;/', $rest)) {
247 error_log("Detected ';' within a parenthesis");
248 $state = $states['ERROR'];
249 } elseif ($rest == '') {
252 $state = $states['SQL_SCAN'];
253 } elseif (preg_match("/^\\'/", $rest)) {
256 $rest = substr($rest, 1);
258 $state = $states['IN_QUOTE'];
260 error_log("Unknown event in IN_SQL state");
261 $state = $states['ERROR'];
263 break; // End of IN_SQL
265 case $states['END_SQL']:
266 // error_log('END_SQL');
267 if (preg_match('/^\s*$/', $sql)) {
270 $state = $states['SCAN'];
272 array_push($sql_list, $sql);
273 // error_log(RED."---->".$sql.NORMAL);
276 $state = $states['SCAN'];
278 break; // End of END_SQL
280 case $states['QUOTE_SCAN']:
281 // error_log('QUOTE_SCAN');
284 $l = get_next_line($f);
286 error_log("End of file reached during a quoted string");
287 $state = $states['ERROR'];
291 $state = $states['QUOTE_SCAN'];
293 preg_match("/^([^\\\']*)(.*)/", $l, $matches);
294 $chunk = $matches[1];
297 $state = $states['IN_QUOTE'];
299 break; // End of QUOTE_SCAN
301 case $states['IN_QUOTE']:
302 // error_log('IN_QUOTE');
303 if (preg_match("/^'/", $rest)) {
305 $rest = substr($rest, 1);
307 $state = $states['SQL_SCAN'];
308 } elseif (preg_match("/^\\\'/", $rest)) {
310 $rest = substr($rest, 2);
311 $state = $states['IN_QUOTE'];
312 } elseif (preg_match("/^\\\[^\\\]/", $rest)) {
314 $rest = substr($rest, 1);
315 $state = $states['IN_QUOTE'];
316 } elseif (preg_match('/^\\\$/', $rest)) {
318 $rest = substr($rest, 1);
319 $state = $states['IN_QUOTE'];
322 $state = $states['QUOTE_SCAN'];
324 break; // End of IN_QUOTE
326 case $states['IN_DOLDOL']:
327 // error_log('IN_DOLDOL');
328 $cur = $doldolstack[0];
329 if (preg_match(",^(.*?)\\$([\w]*)\\$,", $l, $matches)) {
330 $sql .= $matches[1].'$'.$matches[2].'$';
331 $found = $matches[2];
332 if ($found == $cur) {
333 array_pop($doldolstack);
334 if (count($doldolstack) > 0) {
335 $state = $states['IN_DOLDOL'];
338 $rest = preg_replace(",^.*?\\$[\w]*\\$,",'',$l, 1);
339 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
340 $state = $states['SQL_SCAN'];
344 array_push($doldolstack, $found);
345 $state = $states['IN_DOLDOL'];
348 $l = preg_replace(",^.*?\\\$[\w]*\\\$,",'',$l,1);
349 $state = $states['IN_DOLDOL'];
352 $l = get_next_line($f);
354 error_log("End of file reached during a dollar-quoted string");
355 $state = $states['ERROR'];
358 $state = $states['IN_DOLDOL'];
360 break; // End of IN_DOLDOL
362 case $states['START_COPY']:
363 // error_log('START_COPY');
364 if (preg_match('/\s*copy\s+\"([\w_]+)\"\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
365 $copy_table = $matches[1];
366 $copy_field_list = trim($matches[2]);
367 if ($copy_field_list != '') {
368 $copy_field_list = ' '.$copy_field_list;
370 $copy_rest = $matches[3];
371 $l = get_next_line($f);
373 error_log("End of file reached during a COPY statement");
374 $state = $states['ERROR'];
377 $state = $states['IN_COPY'];
378 } elseif (preg_match('/\s*copy\s+([\w_]+)\s*(\\([\w, "]+\\))?\s*from\s+stdin\s*;(.*)/i', $l, $matches)) {
379 $copy_table = $matches[1];
380 $copy_field_list = trim($matches[2]);
381 if ($copy_field_list != '') {
382 $copy_field_list = ' '.$copy_field_list;
384 $copy_rest = $matches[3];
385 $l = get_next_line($f);
387 error_log("End of file reached during a COPY statement");
388 $state = $states['ERROR'];
391 $state = $states['IN_COPY'];
393 error_log("Unknown event in START_COPY state");
395 break; // End of START_COPY
397 case $states['IN_COPY']:
398 // error_log('IN_COPY');
401 $state = $states['SCAN'];
403 $copy_data = array();
404 $copy_data_tmp = explode ("\t", $l);
405 foreach ($copy_data_tmp as $copy_field) {
406 if ($copy_field == '\N') {
407 $copy_field = 'NULL';
409 $copy_field = preg_replace('/\'/','\'\'', $copy_field);
410 $copy_field = "'".$copy_field."'";
412 array_push($copy_data, $copy_field);
414 $sql = "INSERT INTO \"$copy_table\"$copy_field_list VALUES (";
415 $sql .= implode (', ', $copy_data);
417 array_push($sql_list, $sql);
418 // error_log(RED."---->".$sql.NORMAL);
419 $l = get_next_line($f);
421 error_log("End of file reached during a COPY statement");
422 $state = $states['ERROR'];
425 $state = $states['IN_COPY'];
427 break; // End of IN_COPY
429 case $states['DONE']:
430 // error_log('DONE');
432 break; // End of DONE
434 case $states['ERROR']:
435 // error_log('ERROR');
436 error_log("Reached the ERROR state, dying. State machine is buggy.");
438 break; // End of ERROR
441 error_log("State machine went to an unknown state, redirecting to ERROR");
442 $state = $states['ERROR'];
445 // error_log("State=$names[$state] after switch");
454 // c-file-style: "bsd"