6 use Test::SQL::Translator;
10 use SQL::Translator::Schema::Constants;
13 maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
14 'SQL::Translator::Producer::Oracle');
17 my $xmlfile = "$Bin/data/xml/schema.xml";
20 $sqlt = SQL::Translator->new(
26 die "Can't find test schema $xmlfile" unless -e $xmlfile;
28 my @sql = $sqlt->translate(
29 from => 'XML-SQLFairy',
32 ) or die $sqlt->error;
34 my $sql_string = $sqlt->translate(
35 from => 'XML-SQLFairy',
38 ) or die $sqlt->error;
41 'DROP TABLE Basic CASCADE CONSTRAINTS',
42 'DROP SEQUENCE sq_Basic_id',
43 'CREATE SEQUENCE sq_Basic_id',
45 id number(10) NOT NULL,
46 title varchar2(100) DEFAULT \'hello\' NOT NULL,
47 description clob DEFAULT \'\',
49 explicitnulldef varchar2,
50 explicitemptystring varchar2 DEFAULT \'\',
51 emptytagdef varchar2 DEFAULT \'\',
52 another_id number(10) DEFAULT \'2\',
55 CONSTRAINT Basic_emailuniqueindex UNIQUE (email)
57 'DROP TABLE Another CASCADE CONSTRAINTS',
58 'DROP SEQUENCE sq_Another_id',
59 'CREATE SEQUENCE sq_Another_id',
60 'CREATE TABLE Another (
61 id number(10) NOT NULL,
65 'DROP VIEW email_list',
66 'CREATE VIEW email_list AS
67 SELECT email FROM Basic WHERE (email IS NOT NULL)',
68 'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk FOREIGN KEY (another_id) REFERENCES Another (id)',
69 'CREATE OR REPLACE TRIGGER ai_Basic_id
70 BEFORE INSERT ON Basic
72 new.id IS NULL OR new.id = 0
75 SELECT sq_Basic_id.nextval
80 'CREATE OR REPLACE TRIGGER ts_Basic_timest
81 BEFORE INSERT OR UPDATE ON Basic
82 FOR EACH ROW WHEN (new.timest IS NULL)
84 SELECT sysdate INTO :new.timest FROM dual;
87 'CREATE OR REPLACE TRIGGER ai_Another_id
88 BEFORE INSERT ON Another
90 new.id IS NULL OR new.id = 0
93 SELECT sq_Another_id.nextval
98 'CREATE INDEX titleindex on Basic (title)'];
100 is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
102 is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;
104 DROP SEQUENCE sq_Basic_id01;
106 CREATE SEQUENCE sq_Basic_id01;
109 id number(10) NOT NULL,
110 title varchar2(100) DEFAULT 'hello' NOT NULL,
111 description clob DEFAULT '',
113 explicitnulldef varchar2,
114 explicitemptystring varchar2 DEFAULT '',
115 emptytagdef varchar2 DEFAULT '',
116 another_id number(10) DEFAULT '2',
119 CONSTRAINT Basic_emailuniqueindex UNIQUE (email)
122 DROP TABLE Another CASCADE CONSTRAINTS;
124 DROP SEQUENCE sq_Another_id01;
126 CREATE SEQUENCE sq_Another_id01;
128 CREATE TABLE Another (
129 id number(10) NOT NULL,
134 DROP VIEW email_list;
136 CREATE VIEW email_list AS
137 SELECT email FROM Basic WHERE (email IS NOT NULL);
139 ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);
141 CREATE INDEX titleindex01 on Basic (title);
143 CREATE OR REPLACE TRIGGER ai_Basic_id01
144 BEFORE INSERT ON Basic
146 new.id IS NULL OR new.id = 0
149 SELECT sq_Basic_id01.nextval
155 CREATE OR REPLACE TRIGGER ts_Basic_timest01
156 BEFORE INSERT OR UPDATE ON Basic
157 FOR EACH ROW WHEN (new.timest IS NULL)
159 SELECT sysdate INTO :new.timest FROM dual;
163 CREATE OR REPLACE TRIGGER ai_Another_id01
164 BEFORE INSERT ON Another
166 new.id IS NULL OR new.id = 0
169 SELECT sq_Another_id01.nextval