Commit | Line | Data |
e56dabb7 |
1 | #!/usr/bin/perl |
2 | use strict; |
3 | |
4 | use FindBin qw/$Bin/; |
5 | use Test::More; |
6 | use Test::SQL::Translator; |
7 | use Test::Exception; |
8 | use Data::Dumper; |
9 | use SQL::Translator; |
10 | use SQL::Translator::Schema::Constants; |
11 | |
12 | BEGIN { |
13 | maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy', |
14 | 'SQL::Translator::Producer::Oracle'); |
15 | } |
16 | |
17 | my $xmlfile = "$Bin/data/xml/schema.xml"; |
18 | |
19 | my $sqlt; |
20 | $sqlt = SQL::Translator->new( |
21 | no_comments => 1, |
22 | show_warnings => 1, |
23 | add_drop_table => 1, |
24 | ); |
25 | |
26 | die "Can't find test schema $xmlfile" unless -e $xmlfile; |
27 | |
28 | my @sql = $sqlt->translate( |
29 | from => 'XML-SQLFairy', |
30 | to => 'Oracle', |
31 | filename => $xmlfile, |
32 | ) or die $sqlt->error; |
33 | |
34 | my $sql_string = $sqlt->translate( |
35 | from => 'XML-SQLFairy', |
36 | to => 'Oracle', |
37 | filename => $xmlfile, |
38 | ) or die $sqlt->error; |
39 | |
40 | my $want = [ |
d6828ad7 |
41 | 'DROP TABLE Basic CASCADE CONSTRAINTS', |
42 | 'DROP SEQUENCE sq_Basic_id', |
43 | 'CREATE SEQUENCE sq_Basic_id', |
e56dabb7 |
44 | 'CREATE TABLE Basic ( |
45 | id number(10) NOT NULL, |
46 | title varchar2(100) DEFAULT \'hello\' NOT NULL, |
47 | description clob DEFAULT \'\', |
48 | email varchar2(255), |
49 | explicitnulldef varchar2, |
50 | explicitemptystring varchar2 DEFAULT \'\', |
51 | emptytagdef varchar2 DEFAULT \'\', |
52 | another_id number(10) DEFAULT \'2\', |
53 | timest date, |
54 | PRIMARY KEY (id), |
55 | CONSTRAINT emailuniqueindex UNIQUE (email) |
d6828ad7 |
56 | )', |
57 | 'DROP TABLE Another CASCADE CONSTRAINTS', |
58 | 'DROP SEQUENCE sq_Another_id', |
59 | 'CREATE SEQUENCE sq_Another_id', |
e56dabb7 |
60 | 'CREATE TABLE Another ( |
61 | id number(10) NOT NULL, |
62 | PRIMARY KEY (id) |
d6828ad7 |
63 | )', |
e56dabb7 |
64 | 'CREATE VIEW email_list AS |
d6828ad7 |
65 | SELECT email FROM Basic WHERE email IS NOT NULL', |
66 | 'ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk FOREIGN KEY (another_id) REFERENCES Another (id)', |
67 | 'CREATE OR REPLACE TRIGGER ai_Basic_id |
e56dabb7 |
68 | BEFORE INSERT ON Basic |
69 | FOR EACH ROW WHEN ( |
70 | new.id IS NULL OR new.id = 0 |
71 | ) |
72 | BEGIN |
d6828ad7 |
73 | SELECT sq_Basic_id.nextval |
e56dabb7 |
74 | INTO :new.id |
75 | FROM dual; |
76 | END; |
d6828ad7 |
77 | ', |
78 | 'CREATE OR REPLACE TRIGGER ts_Basic_timest |
e56dabb7 |
79 | BEFORE INSERT OR UPDATE ON Basic |
80 | FOR EACH ROW WHEN (new.timest IS NULL) |
81 | BEGIN |
82 | SELECT sysdate INTO :new.timest FROM dual; |
83 | END; |
d6828ad7 |
84 | ', |
85 | 'CREATE OR REPLACE TRIGGER ai_Another_id |
e56dabb7 |
86 | BEFORE INSERT ON Another |
87 | FOR EACH ROW WHEN ( |
88 | new.id IS NULL OR new.id = 0 |
89 | ) |
90 | BEGIN |
d6828ad7 |
91 | SELECT sq_Another_id.nextval |
e56dabb7 |
92 | INTO :new.id |
93 | FROM dual; |
94 | END; |
d6828ad7 |
95 | ', |
96 | 'CREATE INDEX titleindex on Basic (title)']; |
e56dabb7 |
97 | |
98 | is_deeply(\@sql, $want, 'Got correct Oracle statements in list context'); |
99 | |
100 | is($sql_string, q|DROP TABLE Basic CASCADE CONSTRAINTS; |
101 | |
d6828ad7 |
102 | DROP SEQUENCE sq_Basic_id01; |
e56dabb7 |
103 | |
d6828ad7 |
104 | CREATE SEQUENCE sq_Basic_id01; |
e56dabb7 |
105 | |
106 | CREATE TABLE Basic ( |
107 | id number(10) NOT NULL, |
108 | title varchar2(100) DEFAULT 'hello' NOT NULL, |
109 | description clob DEFAULT '', |
110 | email varchar2(255), |
111 | explicitnulldef varchar2, |
112 | explicitemptystring varchar2 DEFAULT '', |
113 | emptytagdef varchar2 DEFAULT '', |
114 | another_id number(10) DEFAULT '2', |
115 | timest date, |
116 | PRIMARY KEY (id), |
117 | CONSTRAINT emailuniqueindex UNIQUE (email) |
118 | ); |
119 | |
120 | DROP TABLE Another CASCADE CONSTRAINTS; |
121 | |
d6828ad7 |
122 | DROP SEQUENCE sq_Another_id01; |
e56dabb7 |
123 | |
d6828ad7 |
124 | CREATE SEQUENCE sq_Another_id01; |
e56dabb7 |
125 | |
126 | CREATE TABLE Another ( |
127 | id number(10) NOT NULL, |
128 | PRIMARY KEY (id) |
129 | ); |
130 | |
131 | CREATE VIEW email_list AS |
132 | SELECT email FROM Basic WHERE email IS NOT NULL; |
133 | |
d6828ad7 |
134 | ALTER TABLE Basic ADD CONSTRAINT Basic_another_id_fk01 FOREIGN KEY (another_id) REFERENCES Another (id); |
e56dabb7 |
135 | |
d6828ad7 |
136 | CREATE INDEX titleindex01 on Basic (title); |
e56dabb7 |
137 | |
d6828ad7 |
138 | CREATE OR REPLACE TRIGGER ai_Basic_id01 |
e56dabb7 |
139 | BEFORE INSERT ON Basic |
140 | FOR EACH ROW WHEN ( |
141 | new.id IS NULL OR new.id = 0 |
142 | ) |
143 | BEGIN |
144 | SELECT sq_Basic_id01.nextval |
145 | INTO :new.id |
146 | FROM dual; |
147 | END; |
e56dabb7 |
148 | / |
149 | |
d6828ad7 |
150 | CREATE OR REPLACE TRIGGER ts_Basic_timest01 |
e56dabb7 |
151 | BEFORE INSERT OR UPDATE ON Basic |
152 | FOR EACH ROW WHEN (new.timest IS NULL) |
153 | BEGIN |
154 | SELECT sysdate INTO :new.timest FROM dual; |
155 | END; |
156 | / |
157 | |
d6828ad7 |
158 | CREATE OR REPLACE TRIGGER ai_Another_id01 |
e56dabb7 |
159 | BEFORE INSERT ON Another |
160 | FOR EACH ROW WHEN ( |
161 | new.id IS NULL OR new.id = 0 |
162 | ) |
163 | BEGIN |
d6828ad7 |
164 | SELECT sq_Another_id01.nextval |
e56dabb7 |
165 | INTO :new.id |
166 | FROM dual; |
167 | END; |
168 | /|); |