Bug #63452 for SQL-Translator: [PATCH 2/2] Add support for triggers in the MySQL producer

--- Changes | 1 + lib/SQL/Translator/Producer/MySQL.pm | 50 ++++++++++++++++++ t/64xml-to-mysql.t | 95 ++++++++++++++++++++++++++++++++++ 3 files changed, 146 insertions(+), 0 deletions(-) create mode 100644 t/64xml-to-mysql.t diff --git a/Changes b/Changes index 7fb442c..fdcbb23 100644 --- a/Changes +++ b/Changes @@ -4,6 +4,7 @@ * Some minor fixes to squash warnings on new perls * Support a custom_type_name hint when creating enum types in PostgreSQL * Fix sqlt options/pod mismatch (RT#58318) +* Add support for triggers in the MySQL producer # ---------------------------------------------------------- # 0.11006 2010-06-03 diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index db7f7c8..bfa1da6 100644 --- a/lib/SQL/Translator/Producer/MySQL.pm +++ b/lib/SQL/Translator/Producer/MySQL.pm @@ -320,6 +320,20 @@ sub produce { } } + if ($mysql_version >= 5.000002) { + for my $trigger ( $schema->get_triggers ) { + push @table_defs, create_trigger($trigger, + { add_drop_trigger => $add_drop_table, + show_warnings => $show_warnings, + no_comments => $no_comments, + quote_table_names => $qt, + quote_field_names => $qf, + max_id_length => $max_id_length, + mysql_version => $mysql_version + }); + } + } + # print "@table_defs\n"; push @table_defs, "SET foreign_key_checks=1"; @@ -327,6 +341,42 @@ sub produce { return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs))); } +sub create_trigger { + my ($trigger, $options) = @_; + my $qt = $options->{quote_table_names} || ''; + my $qf = $options->{quote_field_names} || ''; + + my $trigger_name = $trigger->name; + debug("PKG: Looking at trigger '${trigger_name}'\n"); + + my @statements; + + my $events = $trigger->database_events; + for my $event ( @$events ) { + my $name = $trigger_name; + if (@$events > 1) { + $name .= "_$event"; + + warn "Multiple database events supplied for trigger '${trigger_name}', ", + "creating trigger '${name}' for the '${event}' event\n" + if $options->{show_warnings}; + } + + my $action = $trigger->action; + $action .= ";" unless $action =~ /;\s*\z/; + + push @statements, "DROP TRIGGER IF EXISTS ${qt}${name}${qt}" if $options->{add_drop_trigger}; + push @statements, sprintf( + "CREATE TRIGGER ${qt}%s${qt} %s %s ON ${qt}%s${qt}\n FOR EACH ROW BEGIN %s END", + $name, $trigger->perform_action_when, $event, $trigger->on_table, $action, + ); + + } + # Tack the comment onto the first statement + $statements[0] = "--\n-- Trigger ${qt}${trigger_name}${qt}\n--\n" . $statements[0] unless $options->{no_comments}; + return @statements; +} + sub create_view { my ($view, $options) = @_; my $qt = $options->{quote_table_names} || ''; diff --git a/t/64xml-to-mysql.t b/t/64xml-to-mysql.t new file mode 100644 index 0000000..fea07b0 --- /dev/null +++ b/t/64xml-to-mysql.t @@ -0,0 +1,95 @@ +#!/usr/bin/perl +use strict; + +use FindBin qw/$Bin/; +use Test::More; +use Test::SQL::Translator; +use Test::Exception; +use Test::Differences; +use Data::Dumper; +use SQL::Translator; +use SQL::Translator::Schema::Constants; + + +BEGIN { + maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy', + 'SQL::Translator::Producer::MySQL'); +} + +my $xmlfile = "$Bin/data/xml/schema.xml"; + +my $sqlt; +$sqlt = SQL::Translator->new( + no_comments => 1, + show_warnings => 0, + add_drop_table => 1, + producer_args => { + mysql_version => 5.005, + }, +); + +die "Can't find test schema $xmlfile" unless -e $xmlfile; + +my @want = ( + q[SET foreign_key_checks=0], + + q[DROP TABLE IF EXISTS `Basic`], + q[CREATE TABLE `Basic` ( + `id` integer(10) zerofill NOT NULL auto_increment, + `title` varchar(100) NOT NULL DEFAULT 'hello', + `description` text DEFAULT '', + `email` varchar(500), + `explicitnulldef` varchar(255), + `explicitemptystring` varchar(255) DEFAULT '', + `emptytagdef` varchar(255) DEFAULT '' comment 'Hello emptytagdef', + `another_id` integer(10) DEFAULT 2, + `timest` timestamp, + INDEX `titleindex` (`title`), + INDEX (`another_id`), + PRIMARY KEY (`id`), + UNIQUE `emailuniqueindex` (`email`), + UNIQUE `very_long_index_name_on_title_field_which_should_be_tru_14b59999` (`title`), + CONSTRAINT `Basic_fk` FOREIGN KEY (`another_id`) REFERENCES `Another` (`id`) +) ENGINE=InnoDB], + + q[DROP TABLE IF EXISTS `Another`], + q[CREATE TABLE `Another` ( + `id` integer(10) NOT NULL auto_increment, + `num` numeric(10, 2), + PRIMARY KEY (`id`) +) ENGINE=InnoDB], + q[CREATE OR REPLACE + VIEW `email_list` ( `email` ) AS + SELECT email FROM Basic WHERE (email IS NOT NULL) +], + + q[DROP TRIGGER IF EXISTS `foo_trigger`], + q[CREATE TRIGGER `foo_trigger` after insert ON `Basic` + FOR EACH ROW BEGIN update modified=timestamp(); END], + + q[DROP TRIGGER IF EXISTS `bar_trigger_insert`], + q[CREATE TRIGGER `bar_trigger_insert` before insert ON `Basic` + FOR EACH ROW BEGIN update modified2=timestamp(); END], + + q[DROP TRIGGER IF EXISTS `bar_trigger_update`], + q[CREATE TRIGGER `bar_trigger_update` before update ON `Basic` + FOR EACH ROW BEGIN update modified2=timestamp(); END], + + q[SET foreign_key_checks=1], +); + +my $sql = $sqlt->translate( + from => 'XML-SQLFairy', + to => 'MySQL', + filename => $xmlfile, +) or die $sqlt->error; + +eq_or_diff($sql, join("", map { "$_;\n\n" } @want)); + +my @sql = $sqlt->translate( + from => 'XML-SQLFairy', + to => 'MySQL', + filename => $xmlfile, +) or die $sqlt->error; + +is_deeply(\@sql, \@want); -- 1.7.1