X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FOracle.pm;h=e1c79c3af106df867f221bc5d6d928428afbd972;hb=13db351bd115c9272ca5a5cf5d0197e372e02c0e;hp=63e55ec84bcc9270cb684be1bd290800f37bb22f;hpb=f92d79aad4f104af6eeb16d0e2660574b633fdf2;p=dbsrgits%2FSQL-Translator.git

diff --git a/lib/SQL/Translator/Producer/Oracle.pm b/lib/SQL/Translator/Producer/Oracle.pm
index 63e55ec..e1c79c3 100644
--- a/lib/SQL/Translator/Producer/Oracle.pm
+++ b/lib/SQL/Translator/Producer/Oracle.pm
@@ -46,6 +46,56 @@ 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;
@@ -195,13 +245,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 +682,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 +693,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 +741,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);