Commit | Line | Data |
9522798b |
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; |
9522798b |
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 => 1, |
24 | quote_field_names => 1, |
9522798b |
25 | show_warnings => 0, |
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 = [ |
9522798b |
44 | 'DROP TABLE "Basic" CASCADE CONSTRAINTS', |
45 | 'DROP SEQUENCE "sq_Basic_id"', |
46 | 'CREATE SEQUENCE "sq_Basic_id"', |
47 | 'CREATE TABLE "Basic" ( |
48 | "id" number(10) NOT NULL, |
49 | "title" varchar2(100) DEFAULT \'hello\' NOT NULL, |
d570aec7 |
50 | "description" clob DEFAULT \'\', |
9522798b |
51 | "email" varchar2(500), |
e19efc15 |
52 | "explicitnulldef" varchar2(4000), |
53 | "explicitemptystring" varchar2(4000) DEFAULT \'\', |
54 | "emptytagdef" varchar2(4000) DEFAULT \'\', |
9522798b |
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") |
9522798b |
60 | )', |
61 | 'DROP TABLE "Another" CASCADE CONSTRAINTS', |
62 | 'DROP SEQUENCE "sq_Another_id"', |
63 | 'CREATE SEQUENCE "sq_Another_id"', |
64 | 'CREATE TABLE "Another" ( |
65 | "id" number(10) NOT NULL, |
66 | "num" number(10,2), |
67 | PRIMARY KEY ("id") |
68 | )', |
69 | 'DROP VIEW "email_list"', |
70 | 'CREATE VIEW "email_list" AS |
71 | SELECT email FROM Basic WHERE (email IS NOT NULL)', |
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" |
74 | BEFORE INSERT ON "Basic" |
75 | FOR EACH ROW WHEN ( |
76 | new."id" IS NULL OR new."id" = 0 |
77 | ) |
78 | BEGIN |
79 | SELECT "sq_Basic_id".nextval |
80 | INTO :new."id" |
81 | FROM dual; |
82 | END; |
83 | ', |
84 | 'CREATE OR REPLACE TRIGGER "ts_Basic_timest" |
85 | BEFORE INSERT OR UPDATE ON "Basic" |
86 | FOR EACH ROW WHEN (new."timest" IS NULL) |
aee4b66e |
87 | BEGIN |
9522798b |
88 | SELECT sysdate INTO :new."timest" FROM dual; |
89 | END; |
90 | ', |
91 | 'CREATE OR REPLACE TRIGGER "ai_Another_id" |
92 | BEFORE INSERT ON "Another" |
93 | FOR EACH ROW WHEN ( |
94 | new."id" IS NULL OR new."id" = 0 |
95 | ) |
96 | BEGIN |
97 | SELECT "sq_Another_id".nextval |
98 | INTO :new."id" |
99 | FROM dual; |
100 | END; |
101 | ', |
102 | 'CREATE INDEX "titleindex" on "Basic" ("title")']; |
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; |
9522798b |
107 | |
108 | DROP SEQUENCE "sq_Basic_id01"; |
109 | |
110 | CREATE SEQUENCE "sq_Basic_id01"; |
111 | |
112 | CREATE TABLE "Basic" ( |
113 | "id" number(10) NOT NULL, |
114 | "title" varchar2(100) DEFAULT 'hello' NOT NULL, |
d570aec7 |
115 | "description" clob DEFAULT '', |
9522798b |
116 | "email" varchar2(500), |
e19efc15 |
117 | "explicitnulldef" varchar2(4000), |
118 | "explicitemptystring" varchar2(4000) DEFAULT '', |
119 | "emptytagdef" varchar2(4000) DEFAULT '', |
9522798b |
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") |
9522798b |
125 | ); |
126 | |
127 | DROP TABLE "Another" CASCADE CONSTRAINTS; |
128 | |
129 | DROP SEQUENCE "sq_Another_id01"; |
130 | |
131 | CREATE SEQUENCE "sq_Another_id01"; |
132 | |
133 | CREATE TABLE "Another" ( |
134 | "id" number(10) NOT NULL, |
135 | "num" number(10,2), |
136 | PRIMARY KEY ("id") |
137 | ); |
138 | |
139 | DROP VIEW "email_list"; |
140 | |
141 | CREATE VIEW "email_list" AS |
142 | SELECT email FROM Basic WHERE (email IS NOT NULL); |
143 | |
144 | ALTER TABLE "Basic" ADD CONSTRAINT "Basic_another_id_fk01" FOREIGN KEY ("another_id") REFERENCES "Another" ("id"); |
145 | |
146 | CREATE INDEX "titleindex01" on "Basic" ("title"); |
147 | |
148 | CREATE OR REPLACE TRIGGER "ai_Basic_id01" |
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; |
158 | / |
159 | |
160 | CREATE OR REPLACE TRIGGER "ts_Basic_timest01" |
161 | BEFORE INSERT OR UPDATE ON "Basic" |
162 | FOR EACH ROW WHEN (new."timest" IS NULL) |
aee4b66e |
163 | BEGIN |
9522798b |
164 | SELECT sysdate INTO :new."timest" FROM dual; |
165 | END; |
166 | / |
167 | |
168 | CREATE OR REPLACE TRIGGER "ai_Another_id01" |
169 | BEFORE INSERT ON "Another" |
170 | FOR EACH ROW WHEN ( |
171 | new."id" IS NULL OR new."id" = 0 |
172 | ) |
173 | BEGIN |
174 | SELECT "sq_Another_id01".nextval |
175 | INTO :new."id" |
176 | FROM dual; |
177 | END; |
178 | / |
179 | |
180 | |); |