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