Downgrade global version - highest version in 9002 on cpan is 1.58 - thus go with...

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / Oracle.pm

diff --git a/lib/SQL/Translator/Producer/Oracle.pm b/lib/SQL/Translator/Producer/Oracle.pm
index 63e55ec..e2ea448 100644 (file)
--- 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<DBI/do>, 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<DBI> like
+L<DBIx::Class::Schema/deploy> does.
+
+To get this working we removed the slash in those statements in version
+0.09002 of L<SQL::Translator> 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;
@@ -631,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" .
@@ -642,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;
@@ -675,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);