Added a semicolon at the end of the create trigger definition because SQLite seems...

[dbsrgits/SQL-Translator.git] / t / 15oracle-parser.t

diff --git a/t/15oracle-parser.t b/t/15oracle-parser.t
index 635313e..533ac72 100644 (file)
--- a/t/15oracle-parser.t
+++ b/t/15oracle-parser.t
@@ -1,10 +1,14 @@
 #!/usr/bin/perl
+# vim: set ft=perl:
 
 use strict;
-use Test::More tests => 73;
+use Test::More;
 use SQL::Translator;
 use SQL::Translator::Schema::Constants;
-use SQL::Translator::Parser::Oracle qw(parse);
+use Test::SQL::Translator qw(maybe_plan);
+
+maybe_plan(85, 'SQL::Translator::Parser::Oracle');
+SQL::Translator::Parser::Oracle->import('parse');
 
 my $t   = SQL::Translator->new( trace => 0 );
 my $sql = q[
@@ -15,7 +19,8 @@ my $sql = q[
         trait_category              VARCHAR2(100)   NOT NULL,
         UNIQUE ( trait_category )
     );
-    comment on table qtl_trait_category is 'hey, hey, hey, hey';
+    COMMENT ON TABLE qtl_trait_category IS 
+    'hey, hey, hey, hey';
     comment on column qtl_trait_category.qtl_trait_category_id 
         is 'the primary key!';
 
@@ -24,29 +29,33 @@ my $sql = q[
     (
         qtl_trait_id            NUMBER(11)      NOT NULL    
             CONSTRAINT pk_qtl_trait PRIMARY KEY,
-        trait_symbol            VARCHAR2(100)   NOT NULL,
-        trait_name              VARCHAR2(200)   NOT NULL,
+        trait_symbol            VARCHAR2(100 BYTE)   NOT NULL,
+        trait_name              VARCHAR2(200 CHAR)   NOT NULL,
         qtl_trait_category_id   NUMBER(11)      NOT NULL,
         UNIQUE ( trait_symbol ),
         UNIQUE ( trait_name ),
         FOREIGN KEY ( qtl_trait_category_id ) REFERENCES qtl_trait_category
     );
 
+    /* qtl table comment */
     CREATE TABLE qtl
     (
+        /* qtl_id comment */
         qtl_id              NUMBER(11)      NOT NULL    
             CONSTRAINT pk_qtl PRIMARY KEY,
-        qtl_accession_id    VARCHAR2(20)    NOT NULL,
+        qtl_accession_id    VARCHAR2(20)    NOT NULL /* accession comment */,
         published_symbol    VARCHAR2(100),
         qtl_trait_id        NUMBER(11)      NOT NULL,
         linkage_group       VARCHAR2(32)    NOT NULL,
         start_position      NUMBER(11,2)    NOT NULL,
         stop_position       NUMBER(11,2)    NOT NULL,
         comments            long,
-        UNIQUE ( qtl_accession_id ),
         FOREIGN KEY ( qtl_trait_id ) REFERENCES qtl_trait
     );
 
+    CREATE UNIQUE INDEX qtl_accession ON qtl ( qtl_accession_id );
+    CREATE UNIQUE INDEX qtl_accession_upper ON qtl ( UPPER(qtl_accession_id) );
+
     CREATE TABLE qtl_trait_synonym
     (
         qtl_trait_synonym_id    NUMBER(11)      NOT NULL    
@@ -54,7 +63,7 @@ my $sql = q[
         trait_synonym           VARCHAR2(200)   NOT NULL,
         qtl_trait_id            NUMBER(11)      NOT NULL,
         UNIQUE( qtl_trait_id, trait_synonym ),
-        FOREIGN KEY ( qtl_trait_id ) REFERENCES qtl_trait
+        FOREIGN KEY ( qtl_trait_id ) REFERENCES qtl_trait ON DELETE SET NULL
     );
 ];
 
@@ -198,7 +207,20 @@ my @t3_fields = $t3->get_fields;
 is( scalar @t3_fields, 8, '8 fields in table' );
 
 my @t3_constraints = $t3->get_constraints;
-is( scalar @t3_constraints, 3, '3 constraints on table' );
+is( scalar @t3_constraints, 4, '4 constraints on table' );
+my $t3_c4 = $t3_constraints[3];
+is( $t3_c4->type, UNIQUE, 'Fourth constraint is unique' );
+is( $t3_c4->name, 'qtl_accession_upper', 'Name = "qtl_accession_upper"' );
+is( join(',', $t3_c4->fields), 'UPPER(qtl_accession_id)', 'Fields = "UPPER(qtl_accession_id)"' );
+
+is( $t3->comments, 'qtl table comment', 'Comment "qtl table comment" exists' );
+
+my $t3_f1     = shift @t3_fields;
+is( $t3_f1->comments, 'qtl_id comment', 'Comment "qtl_id comment" exists' );
+
+my $t3_f2     = shift @t3_fields;
+is( $t3_f2->comments, 'accession comment', 
+    'Comment "accession comment" exists' );
 
 #
 # qtl_trait_synonym
@@ -211,3 +233,14 @@ is( scalar @t4_fields, 3, '3 fields in table' );
 
 my @t4_constraints = $t4->get_constraints;
 is( scalar @t4_constraints, 3, '3 constraints on table' );
+my $t4_c3 = $t4_constraints[2];
+is( $t4_c3->type, FOREIGN_KEY, 'Third constraint is FK' );
+is( $t4_c3->name, '', 'No name' );
+is( join(',', $t4_c3->fields), 'qtl_trait_id', 
+    'Fields = "qtl_trait_id"' );
+is( $t4_c3->reference_table, 'qtl_trait', 
+    'Reference table = "qtl_trait"' );
+is( join(',', $t4_c3->reference_fields), 'qtl_trait_id', 
+    'Reference fields = "qtl_trait_id"' );
+is( $t4_c3->on_delete, 'SET NULL', 
+    'on_delete = "SET NULL"' );