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