I am in the process of making SQL::Parser more able to handle a variety
of implementation specific SQL syntaxs and would value suggestions both
in terms of types of variations to handle and in the specifics shown
below.
The three areas I have tackled first are case sensitivity of column
names, style of embedded comments, and style of escaping for single
quote characters. Essentially, for each one there will be a default
behaviour which can be modified by users either with flags to the new()
method or by creating a dialect config file that defines multiple
features. One could implement this either for the Parser in general, or
on a statement by statement basis (e.g. if you are validating multiple
SQL statements meant to be handled by different drivers).
By default column names will be case sensitive, comments will consist of
lines starting with a double hyphen and ending with a newline, and
single quotes will be escaped by doubling them (WHERE name =
'O''REILLY') but all of the defaults can be modified with user flags or
dialect config file options.
So, for example, the SQL statement below containing '/* comment */'
would return an error for test('SQL92') but would return 'ok' for
test('ORACLE') whereas the SQL containing '-- comment' would do the
reverse. Both statements would succeed for test('MySQL').
use SQL::Parser;
test($_) for qw( SQL92 ORACLE MySQL );
sub test {
my $dialect = shift || 'ANSI';
my $parser = SQL::Parser->new( $dialect, {PrintError=>0} );
for my $sql(
"SELECT * FROM foo /* comment */",
"SELECT * FROM foo -- comment",
) {
my $rv = $parser->parse( $sql );
print "$sql\n $dialect : ";
print $rv ? "ok\n" : $parser->errstr;
}
}
Thoughts please?
--
Jeff
