| Subject: | patch - parse MySQL/Oracle ALTER TABLE ADD CONSTRAINT/INDEX |
This patch allows the MySQL and Oracle parsers to interpret ALTER TABLE
ADD CONSTRAINT and ALTER TABLE ADD INDEX as can already be specified in
the CREATE TABLE. The MySQL parser already accepted foreign key
constraints in ALTER. This change adds support for other constraints and
index ALTERations, and makes them available also for the Oracle parser.
The patch also adds GRANT to the list of ignored SQL statements for the
MySQL and Oracle parsers.
Also includes a fix to the Oracle parser to allow non-alphanumeric
default values if they are quoted.
(Linux 2.4.21-47.EL, perl 5.0.8, SQL-Translator-0.08_04)
| Subject: | SQL-Translator-0.08-1.patch |
diff -ur SQL-Translator-0.08-orig/lib/SQL/Translator/Parser/MySQL.pm SQL-Translator-0.08/lib/SQL/Translator/Parser/MySQL.pm
--- SQL-Translator-0.08-orig/lib/SQL/Translator/Parser/MySQL.pm 2006-12-07 14:05:17.000000000 +0000
+++ SQL-Translator-0.08/lib/SQL/Translator/Parser/MySQL.pm 2007-01-08 18:15:31.000000000 +0000
@@ -117,14 +117,19 @@
or DATA DIRECTORY="absolute path to directory"
or INDEX DIRECTORY="absolute path to directory"
-A subset of the ALTER TABLE syntax that allows addition of foreign keys:
+A subset of the ALTER TABLE syntax that allows addition of foreign keys,
+constraints, and indices:
ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ...
alter_specification:
- ADD [CONSTRAINT [symbol]]
- FOREIGN KEY [index_name] (index_col_name,...)
- [reference_definition]
+ ADD PRIMARY KEY (index_col_name,...)
+ or ADD KEY [index_name] (index_col_name,...)
+ or ADD INDEX [index_name] (index_col_name,...)
+ or ADD UNIQUE [INDEX] [index_name] (index_col_name,...)
+ or ADD FULLTEXT [INDEX] [index_name] (index_col_name,...)
+ or ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)
+ [reference_definition]
A subset of INSERT that we ignore:
@@ -175,6 +180,7 @@
| create
| alter
| insert
+ | grant
| delimiter
| empty_statement
| <error>
@@ -208,25 +214,38 @@
insert : /insert/i statement_body "$delimiter"
+grant : /grant/i statement_body "$delimiter"
+
delimiter : /delimiter/i /[\S]+/
{ $delimiter = $item[2] }
empty_statement : "$delimiter"
-alter : ALTER TABLE table_name alter_specification(s /,/) "$delimiter"
- {
+alter : ALTER IGNORE(?) TABLE table_name alter_specification(s /,/) "$delimiter"
+ {
my $table_name = $item{'table_name'};
- die "Cannot ALTER table '$table_name'; it does not exist"
- unless $tables{ $table_name };
- for my $definition ( @{ $item[4] } ) {
- $definition->{'extra'}->{'alter'} = 1;
- push @{ $tables{ $table_name }{'constraints'} }, $definition;
- }
+ die "Cannot ALTER table '$table_name'; it does not exist"
+ unless $tables{ $table_name };
+ for my $definition ( @{ $item[5] } ) {
+ $definition->{'extra'}->{'alter'} = 1;
+ if ( $definition->{'supertype'} eq 'constraint' ) {
+ push @{ $tables{ $table_name }{'constraints'} }, $definition;
+ }
+ elsif ( $definition->{'supertype'} eq 'index' ) {
+ push @{ $tables{ $table_name }{'indices'} }, $definition;
+ }
+ }
+ 1;
}
-alter_specification : ADD foreign_key_def
+alter_specification : ADD alter_definition
{ $return = $item[2] }
+alter_definition : constraint
+ | index
+ | comment
+ | <error>
+
create : CREATE /database/i WORD "$delimiter"
{ @table_comments = () }
@@ -678,6 +697,8 @@
ALTER : /alter/i
+IGNORE : /ignore/i
+
CREATE : /create/i
TEMPORARY : /temporary/i
diff -ur SQL-Translator-0.08-orig/lib/SQL/Translator/Parser/Oracle.pm SQL-Translator-0.08/lib/SQL/Translator/Parser/Oracle.pm
--- SQL-Translator-0.08-orig/lib/SQL/Translator/Parser/Oracle.pm 2006-12-07 14:05:17.000000000 +0000
+++ SQL-Translator-0.08/lib/SQL/Translator/Parser/Oracle.pm 2007-01-04 22:25:54.000000000 +0000
@@ -72,6 +72,14 @@
partitioning_options:
Partition_clause {ENABLE|DISABLE} ROW MOVEMENT
+From http://www.ss64.com/ora/table_a_cons.html:
+
+ ALTER TABLE [schema.]table
+ constraint_clause,...
+
+constraint_clause:
+ ADD table_ref_constraint
+
Column Constraints
(http://www.ss64.com/ora/clause_constraint_col.html)
@@ -141,17 +149,33 @@
| comment_on_table
| comment_on_column
| alter
+ | grant
| drop
| <error>
-alter : /alter/i WORD /[^;]+/ ';'
- { @table_comments = () }
+alter : alter_table table_name alter_specification(s /,/) ';'
+ {
+ my $table_name = $item{'table_name'};
+ die "Cannot ALTER table '$table_name'; it does not exist"
+ unless $tables{ $table_name };
+ for my $definition ( @{ $item[3] } ) {
+ $definition->{'extra'}->{'alter'} = 1;
+ $definition->{'type'} = $definition->{'constraint_type'};
+ push @{ $tables{ $table_name }{'constraints'} }, $definition;
+ }
+ }
+
+alter_specification : /add/i table_constraint
+ { $return = $item[2] }
drop : /drop/i TABLE ';'
drop : /drop/i WORD(s) ';'
{ @table_comments = () }
+grant : /grant/i TERM(s) ';'
+ { @table_comments = () }
+
create : create_table table_name '(' create_definition(s /,/) ')' table_option(s?) ';'
{
my $table_name = $item{'table_name'};
@@ -437,7 +461,7 @@
field_meta : default_val
| column_constraint
-default_val : /default/i /(?:')?[\w\d.-]*(?:')?/
+default_val : /default/i /'.*?'|[\w\d.-]/
{
my $val = $item[2];
$val =~ s/'//g if defined $val;
@@ -457,6 +481,7 @@
}
create_table : /create/i global_temporary(?) /table/i
+alter_table : /alter/i /table/i
table_option : /organization/i WORD
{
@@ -549,6 +574,8 @@
WORD : /\w+/
+TERM : /[^;\s]+/
+
NAME : /\w+/ { $item[1] }
TABLE : /table/i