X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FOracle.pm;h=e2ea44886fa10ee5bd698a82aa704c47cdca333d;hb=4ab3763d2ad756c236b757306989cafa08e7f35e;hp=4a3a8e0798effad4dbb932a481c854c27de1af83;hpb=e3aac687ba2633584ce0265998b3c17c8a42d63a;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/Oracle.pm b/lib/SQL/Translator/Producer/Oracle.pm index 4a3a8e0..e2ea448 100644 --- a/lib/SQL/Translator/Producer/Oracle.pm +++ b/lib/SQL/Translator/Producer/Oracle.pm @@ -1,9 +1,7 @@ package SQL::Translator::Producer::Oracle; # ------------------------------------------------------------------- -# $Id: Oracle.pm,v 1.34 2005-08-10 16:33:39 duality72 Exp $ -# ------------------------------------------------------------------- -# Copyright (C) 2002-4 SQLFairy Authors +# Copyright (C) 2002-2009 SQLFairy Authors # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as @@ -46,11 +44,61 @@ CREATE TABLE statement and adds ALTER TABLEs at the end with it. =back +=head1 NOTES + +=head2 Autoincremental primary keys + +This producer uses sequences and triggers to autoincrement primary key +columns, if necessary. SQLPlus and DBI expect a slightly different syntax +of CREATE TRIGGER statement. You might have noticed that this +producer returns a scalar containing all statements concatenated by +newlines or an array of single statements depending on the context +(scalar, array) it has been called in. + +SQLPlus expects following trigger syntax: + + CREATE OR REPLACE TRIGGER ai_person_id + BEFORE INSERT ON person + FOR EACH ROW WHEN ( + new.id IS NULL OR new.id = 0 + ) + BEGIN + SELECT sq_person_id.nextval + INTO :new.id + FROM dual; + END; + / + +Whereas if you want to create the same trigger using L, you need +to omit the last slash: + + my $dbh = DBI->connect('dbi:Oracle:mysid', 'scott', 'tiger'); + $dbh->do(" + CREATE OR REPLACE TRIGGER ai_person_id + BEFORE INSERT ON person + FOR EACH ROW WHEN ( + new.id IS NULL OR new.id = 0 + ) + BEGIN + SELECT sq_person_id.nextval + INTO :new.id + FROM dual; + END; + "); + +If you call this producer in array context, we expect you want to process +the returned array of statements using L like +L does. + +To get this working we removed the slash in those statements in version +0.09002 of L when called in array context. In scalar +context the slash will be still there to ensure compatibility with SQLPlus. + =cut use strict; use vars qw[ $VERSION $DEBUG $WARN ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.34 $ =~ /(\d+)\.(\d+)/; +$VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; use SQL::Translator::Schema::Constants; @@ -195,13 +243,14 @@ sub produce { } for my $table ( $schema->get_tables ) { - my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def) = create_table( + my ( $table_def, $fk_def, $trigger_def, $index_def, $constraint_def ) = create_table( $table, { - add_drop_table => $add_drop_table, - show_warnings => $WARN, - no_comments => $no_comments, - delay_constraints => $delay_constraints + add_drop_table => $add_drop_table, + show_warnings => $WARN, + no_comments => $no_comments, + delay_constraints => $delay_constraints, + wantarray => wantarray ? 1 : 0, } ); push @table_defs, @$table_def; @@ -216,7 +265,15 @@ sub produce { push @view_defs, create_view($view); } - return wantarray ? (defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs) : $create . join ('', map { $_ ? "$_;\n\n" : () } @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs); + if (wantarray) { + return defined $create ? $create : (), @table_defs, @view_defs, @fk_defs, @trigger_defs, @index_defs, @constraint_defs; + } + else { + $create .= join ('', map { $_ ? "$_;\n\n" : () } @table_defs, @view_defs, @fk_defs, @index_defs, @constraint_defs); + # triggers may NOT end with a semicolon + $create .= join "\n\n", @trigger_defs; + return $create; + } } sub create_table { @@ -311,7 +368,7 @@ sub create_table { push @constraint_defs, "CONSTRAINT $name CHECK ($expression)"; } elsif ( $c->type eq FOREIGN_KEY ) { - $name = mk_name( join('_', $table_name, $c->fields). '_fk' ); + $name = mk_name( join('_', $table_name, $c->fields). '_fk' ); my $def = "CONSTRAINT $name FOREIGN KEY "; if ( @fields ) { @@ -335,12 +392,12 @@ sub create_table { $def .= ' ON DELETE '.join( ' ', $c->on_delete ); } - # disabled by plu 2007-12-29 - doesn't exist for oracle - #if ( $c->on_update ) { - # $def .= ' ON UPDATE '.join( ' ', $c->on_update ); - #} + # disabled by plu 2007-12-29 - doesn't exist for oracle + #if ( $c->on_update ) { + # $def .= ' ON UPDATE '.join( ' ', $c->on_update ); + #} - push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table, $def); + push @fk_defs, sprintf("ALTER TABLE %s ADD %s", $table_name_ur, $def); } } @@ -623,7 +680,7 @@ sub create_field { push @create, qq[DROP SEQUENCE $seq_name] if $options->{add_drop_table}; push @create, "CREATE SEQUENCE $seq_name"; - push @trigger_defs, + my $trigger = "CREATE OR REPLACE TRIGGER $trigger_name\n" . "BEFORE INSERT ON $table_name_ur\n" . "FOR EACH ROW WHEN (\n" . @@ -634,20 +691,35 @@ sub create_field { " SELECT $seq_name.nextval\n" . " INTO :new." . $field->name."\n" . " FROM dual;\n" . - "END\n/"; - ; + "END;\n"; + + # + # If wantarray is set we have to omit the last "/" in this statement so it + # can be executed by DBI->do() directly. + # + $trigger .= "/" unless $options->{wantarray}; + + push @trigger_defs, $trigger; } if ( lc $field->data_type eq 'timestamp' ) { my $base_name = $table_name_ur . "_". $field_name_ur; my $trig_name = mk_name( $base_name, 'ts' ); - push @trigger_defs, + my $trigger = "CREATE OR REPLACE TRIGGER $trig_name\n". "BEFORE INSERT OR UPDATE ON $table_name_ur\n". "FOR EACH ROW WHEN (new.$field_name_ur IS NULL)\n". "BEGIN \n". " SELECT sysdate INTO :new.$field_name_ur FROM dual;\n". - "END\n/"; + "END;\n"; + + # + # If wantarray is set we have to omit the last "/" in this statement so it + # can be executed by DBI->do() directly. + # + $trigger .= "/" unless $options->{wantarray}; + + push @trigger_defs, $trigger; } push @field_defs, $field_def; @@ -667,7 +739,7 @@ sub create_field { sub create_view { my ($view) = @_; - my $out = sprintf("CREATE VIEW %s AS\n%s;", + my $out = sprintf("CREATE VIEW %s AS\n%s", $view->name, $view->sql);