| Subject: | Patch for CREATE TABLE parsing |
My previous patch failed to allow for primary & foreign keys involving
three or more columns. This patch also allows data types with a space
inside them "CHARACTER VARYING" and allows column precision to be
specified, as in NUMERIC(4,2)
| Subject: | SQL_Statement_CREATE_TABLE2.patch |
diff -Naur old/lib/SQL/Parser.pm new/lib/SQL/Parser.pm
--- old/lib/SQL/Parser.pm 2009-03-05 18:34:42.000000000 +1100
+++ new/lib/SQL/Parser.pm 2009-03-24 22:06:10.000000000 +1100
@@ -1013,14 +1013,14 @@
return undef unless $self->TABLE_NAME($table_name);
$table_element_def =~ s/\s+\(/(/g;
my $primary_defined;
- $table_element_def =~ s/( # start of grouping 1
+ while($table_element_def =~ s/( # start of grouping 1
\( # match a bracket; vi compatible bracket -> \)(
[^)]+ # everything up to but not including the comma, no nesting of brackets is required
) # end of grouping 1
, # the comma to be removed to allow splitting on commas
( # start of grouping 2; vi compatible bracket -> \(
.*?\) # everything up to and including the end bracket
- )/$1?COMMA?$2/sgx;
+ )/$1?COMMA?$2/sgx) { }
for my $col ( split ',', $table_element_def )
{
if ($col =~ /^\s*(?:CONSTRAINT\s+(\S+)\s*)? # optional name of foreign key
@@ -1081,8 +1081,14 @@
$self->{"struct"}->{"table_defs"}->{"$name"}->{"local_cols"} = \@local_cols;
next;
}
+ my ($data_types_regex) = join('|', keys %{$self->{opts}->{valid_data_types}});
+ $data_types_regex =~ s/ /\\ /g; # backslash spaces to allow the /x modifier below
my ( $name, $type, $constraints ) =
- ( $col =~ /\s*(\S+)\s+(\S+)\s*(.*)/ );
+ ( $col =~ /\s*(\S+)\s+ # capture the column name
+ ((?:$data_types_regex|\S+) # check for all allowed data types OR anything that looks like a bad data type to give a good error
+ (?:\s*\(\d+(?:\?COMMA\?\d+)?\))?) # allow the data type to have a precision specifier such as NUMERIC(4,6) on it
+ \s*(.*) # capture the constraints if any
+ /ix );
if ( !$type )
{
return $self->do_err("Column definition is missing a data type!");
diff -Naur old/t/01prepare.t new/t/01prepare.t
--- old/t/01prepare.t 2009-03-05 18:34:42.000000000 +1100
+++ new/t/01prepare.t 2009-03-24 21:56:26.000000000 +1100
@@ -3,7 +3,7 @@
$|=1;
use lib qw' ./ ./t ';
use SQLtest;
-use Test::More tests => 102;
+use Test::More tests => 105;
$parser = new_parser();
$parser->{PrintError}=0;
@@ -48,8 +48,11 @@
CREATE TABLE foo ( id INTEGER PRIMARY KEY, phrase VARCHAR(40) NOT NULL )
CREATE TABLE foo ( id INTEGER NOT NULL, phrase VARCHAR(40) NOT NULL )
CREATE TABLE foo ( id INTEGER UNIQUE NOT NULL, phrase VARCHAR(40) )
+CREATE TABLE foo ( phrase CHARACTER VARYING(255) )
+CREATE TABLE foo ( phrase NUMERIC(4,6) )
CREATE TABLE foo ( id INTEGER, phrase VARCHAR(40), CONSTRAINT "foo_pkey" PRIMARY KEY ( "id", "phrase" ), CONSTRAINT "foo_fkey" FOREIGN KEY ( "id" ) REFERENCES "bar" ( "bar_id" ))
CREATE TABLE foo ( id INTEGER, phrase VARCHAR(40), PRIMARY KEY ( "id" ), FOREIGN KEY ("id", "phrase") REFERENCES "bar" ("id2", "phrase2"))
+CREATE TABLE foo ( id INTEGER, phrase CHAR(255), phrase2 VARCHAR(40), CONSTRAINT "foo_pkey" PRIMARY KEY ( "id", phrase, "phrase2" ), CONSTRAINT "foo_fkey" FOREIGN KEY ("id", "phrase", "phrase2") REFERENCES "bar" ("id2", "phrase2", "phase10"))
/* JOINS */
SELECT Lnum,Llet,Ulet FROM zLower NATURAL INNER JOIN zUpper
SELECT Lnum,Llet,Ulet FROM zLower NATURAL LEFT JOIN zUpper