| Subject: | parse mysql triggers (patch attached) |
The mysql parser currently silently ignores triggers.
The attached patch parses mysql's CREATE TRIGGER syntax per
http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html
Tests updated and pass
| Subject: | mysql-trigger-parse.diff |
diff -ru SQL-Translator-0.11007.orig/lib/SQL/Translator/Parser/MySQL.pm SQL-Translator-0.11007/lib/SQL/Translator/Parser/MySQL.pm
--- SQL-Translator-0.11007.orig/lib/SQL/Translator/Parser/MySQL.pm 2010-11-30 12:26:37.000000000 -0500
+++ SQL-Translator-0.11007/lib/SQL/Translator/Parser/MySQL.pm 2011-04-11 17:20:22.514193801 -0400
@@ -174,7 +174,7 @@
{
my ( $database_name, %tables, $table_order, @table_comments, %views,
- $view_order, %procedures, $proc_order );
+ $view_order, %procedures, %triggers, $proc_order, $trigger_order );
my $delimiter = ';';
}
@@ -190,6 +190,7 @@
tables => \%tables,
views => \%views,
procedures => \%procedures,
+ triggers => \%triggers,
}
}
@@ -322,9 +323,21 @@
;
}
-create : CREATE /trigger/i NAME not_delimiter "$delimiter"
+create : CREATE /trigger/i NAME /before|after/i /insert|update|delete/i /on/i NAME /for each row/i not_delimiter "$delimiter"
{
@table_comments = ();
+ my $trigger_name = $item[3];
+ my $perform_action_when = lc($item[4]);
+ my $database_events = lc($item[5]);
+ my $table = $item[7];
+ my $action = $item[9];
+
+ $triggers{ $trigger_name }{'order'} = ++$trigger_order;
+ $triggers{ $trigger_name }{'name'} = $trigger_name;
+ $triggers{ $trigger_name }{'perform_action_when'} = $perform_action_when;
+ $triggers{ $trigger_name }{'database_events'} = $database_events;
+ $triggers{ $trigger_name }{'on_table'} = $table;
+ $triggers{ $trigger_name }{'action'} = $action;
}
create : CREATE PROCEDURE NAME not_delimiter "$delimiter"
@@ -942,6 +955,24 @@
sql => $result->{procedures}->{$proc_name}->{sql},
);
}
+
+ my @triggers = sort {
+ $result->{triggers}->{ $a }->{'order'}
+ <=>
+ $result->{triggers}->{ $b }->{'order'}
+ } keys %{ $result->{triggers} };
+
+ for my $trigger_name ( @triggers ) {
+ my $trigger = $result->{triggers}{$trigger_name};
+ $schema->add_trigger(
+ name => $trigger_name,
+ perform_action_when => $trigger->{perform_action_when},
+ database_events => $trigger->{database_events},
+ on_table => $trigger->{on_table},
+ action => $trigger->{action},
+ );
+ }
+
my @views = sort {
$result->{views}->{ $a }->{'order'}
<=>
diff -ru SQL-Translator-0.11007.orig/t/02mysql-parser.t SQL-Translator-0.11007/t/02mysql-parser.t
--- SQL-Translator-0.11007.orig/t/02mysql-parser.t 2010-11-30 12:26:34.000000000 -0500
+++ SQL-Translator-0.11007/t/02mysql-parser.t 2011-04-11 17:49:46.403721097 -0400
@@ -11,7 +11,7 @@
use Test::SQL::Translator qw(maybe_plan);
BEGIN {
- maybe_plan(317, "SQL::Translator::Parser::MySQL");
+ maybe_plan(323, "SQL::Translator::Parser::MySQL");
SQL::Translator::Parser::MySQL->import('parse');
}
@@ -486,7 +486,7 @@
DELIMITER ;;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
/*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
- /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
+ /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `one`
FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
@@ -636,6 +636,19 @@
my $proc2 = shift @procs;
is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
+
+ my @triggers = $schema->get_triggers;
+ is( scalar @triggers, 1, 'Right number of triggers (1)' );
+ my $trigger1 = shift @triggers;
+ is($trigger1->name, 'acl_entry_insert', 'Found "acl_entry_insert" trigger');
+ is($trigger1->perform_action_when, 'before', '"acl_entry_insert" trigger is before');
+ is_deeply([$trigger1->database_events], ['insert'], '"acl_entry_insert" fires on insert');
+ is($trigger1->on_table, 'one', '"acl_entry_insert" for table one');
+ my $trigger1_action = $trigger1->action;
+ $trigger1_action =~ s/\s+/ /g;
+ $trigger1_action =~ s/\s+$//;
+ is($trigger1_action, q!SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'), NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00')!,
+ '"acl_entry_insert" action set');
}
# Tests for collate table option