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_id01;',
43 'CREATE SEQUENCE sq_Basic_id01;',
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 emailuniqueindex UNIQUE (email)
57 'DROP TABLE Another CASCADE CONSTRAINTS;',
58 'DROP SEQUENCE sq_Another_id01;',
59 'CREATE SEQUENCE sq_Another_id01;',
60 'CREATE TABLE Another (
61 id number(10) NOT NULL,
64 'CREATE VIEW email_list AS
65 SELECT email FROM Basic WHERE email IS NOT NULL;',
66 'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);',
67 'CREATE OR REPLACE TRIGGER ai_Basic_id01
68 BEFORE INSERT ON Basic
70 new.id IS NULL OR new.id = 0
73 SELECT sq_Basic_id01.nextval
78 'CREATE OR REPLACE TRIGGER ts_Basic_timest01
79 BEFORE INSERT OR UPDATE ON Basic
80 FOR EACH ROW WHEN (new.timest IS NULL)
82 SELECT sysdate INTO :new.timest FROM dual;
85 'CREATE OR REPLACE TRIGGER ai_Another_id01
86 BEFORE INSERT ON Another
88 new.id IS NULL OR new.id = 0
91 SELECT sq_Another_id01.nextval
97 is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
99 is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;
101 DROP SEQUENCE sq_Basic_id02;
103 CREATE SEQUENCE sq_Basic_id02;
106 id number(10) NOT NULL,
107 title varchar2(100) DEFAULT 'hello' NOT NULL,
108 description clob DEFAULT '',
110 explicitnulldef varchar2,
111 explicitemptystring varchar2 DEFAULT '',
112 emptytagdef varchar2 DEFAULT '',
113 another_id number(10) DEFAULT '2',
116 CONSTRAINT emailuniqueindex UNIQUE (email)
119 DROP TABLE Another CASCADE CONSTRAINTS;
121 DROP SEQUENCE sq_Another_id02;
123 CREATE SEQUENCE sq_Another_id02;
125 CREATE TABLE Another (
126 id number(10) NOT NULL,
130 CREATE VIEW email_list AS
131 SELECT email FROM Basic WHERE email IS NOT NULL;
133 ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);
135 CREATE OR REPLACE TRIGGER ai_Basic_id02
136 BEFORE INSERT ON Basic
138 new.id IS NULL OR new.id = 0
141 SELECT sq_Basic_id02.nextval
147 CREATE OR REPLACE TRIGGER ts_Basic_timest02
148 BEFORE INSERT OR UPDATE ON Basic
149 FOR EACH ROW WHEN (new.timest IS NULL)
151 SELECT sysdate INTO :new.timest FROM dual;
155 CREATE OR REPLACE TRIGGER ai_Another_id02
156 BEFORE INSERT ON Another
158 new.id IS NULL OR new.id = 0
161 SELECT sq_Another_id02.nextval
169 use FindBin qw/$Bin/;
171 use Test::SQL::Translator;
175 use SQL::Translator::Schema::Constants;
178 maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
179 'SQL::Translator::Producer::Oracle');
182 my $xmlfile = "$Bin/data/xml/schema.xml";
185 $sqlt = SQL::Translator->new(
191 die "Can't find test schema $xmlfile" unless -e $xmlfile;
193 my @sql = $sqlt->translate(
194 from => 'XML-SQLFairy',
196 filename => $xmlfile,
197 ) or die $sqlt->error;
199 my $sql_string = $sqlt->translate(
200 from => 'XML-SQLFairy',
202 filename => $xmlfile,
203 ) or die $sqlt->error;
206 'DROP TABLE Basic CASCADE CONSTRAINTS;',
207 'DROP SEQUENCE sq_Basic_id01;',
208 'CREATE SEQUENCE sq_Basic_id01;',
209 'CREATE TABLE Basic (
210 id number(10) NOT NULL,
211 title varchar2(100) DEFAULT \'hello\' NOT NULL,
212 description clob DEFAULT \'\',
214 explicitnulldef varchar2,
215 explicitemptystring varchar2 DEFAULT \'\',
216 emptytagdef varchar2 DEFAULT \'\',
217 another_id number(10) DEFAULT \'2\',
220 CONSTRAINT emailuniqueindex UNIQUE (email)
222 'DROP TABLE Another CASCADE CONSTRAINTS;',
223 'DROP SEQUENCE sq_Another_id01;',
224 'CREATE SEQUENCE sq_Another_id01;',
225 'CREATE TABLE Another (
226 id number(10) NOT NULL,
229 'CREATE VIEW email_list AS
230 SELECT email FROM Basic WHERE email IS NOT NULL;',
231 'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id);',
232 'CREATE OR REPLACE TRIGGER ai_Basic_id01
233 BEFORE INSERT ON Basic
235 new.id IS NULL OR new.id = 0
238 SELECT sq_Basic_id01.nextval
243 'CREATE OR REPLACE TRIGGER ts_Basic_timest01
244 BEFORE INSERT OR UPDATE ON Basic
245 FOR EACH ROW WHEN (new.timest IS NULL)
247 SELECT sysdate INTO :new.timest FROM dual;
250 'CREATE OR REPLACE TRIGGER ai_Another_id01
251 BEFORE INSERT ON Another
253 new.id IS NULL OR new.id = 0
256 SELECT sq_Another_id01.nextval
262 is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
264 is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS;
266 DROP SEQUENCE sq_Basic_id02;
268 CREATE SEQUENCE sq_Basic_id02;
271 id number(10) NOT NULL,
272 title varchar2(100) DEFAULT 'hello' NOT NULL,
273 description clob DEFAULT '',
275 explicitnulldef varchar2,
276 explicitemptystring varchar2 DEFAULT '',
277 emptytagdef varchar2 DEFAULT '',
278 another_id number(10) DEFAULT '2',
281 CONSTRAINT emailuniqueindex UNIQUE (email)
284 DROP TABLE Another CASCADE CONSTRAINTS;
286 DROP SEQUENCE sq_Another_id02;
288 CREATE SEQUENCE sq_Another_id02;
290 CREATE TABLE Another (
291 id number(10) NOT NULL,
295 CREATE VIEW email_list AS
296 SELECT email FROM Basic WHERE email IS NOT NULL;
298 ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk02 FOREIGN KEY (another_id) REFERENCES Another (id);
300 CREATE OR REPLACE TRIGGER ai_Basic_id02
301 BEFORE INSERT ON Basic
303 new.id IS NULL OR new.id = 0
306 SELECT sq_Basic_id02.nextval
312 CREATE OR REPLACE TRIGGER ts_Basic_timest02
313 BEFORE INSERT OR UPDATE ON Basic
314 FOR EACH ROW WHEN (new.timest IS NULL)
316 SELECT sysdate INTO :new.timest FROM dual;
320 CREATE OR REPLACE TRIGGER ai_Another_id02
321 BEFORE INSERT ON Another
323 new.id IS NULL OR new.id = 0
326 SELECT sq_Another_id02.nextval