6 use Test::SQL::Translator;
11 use SQL::Translator::Schema::Constants;
14 maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
15 'SQL::Translator::Producer::Oracle');
18 my $xmlfile = "$Bin/data/xml/schema.xml";
21 $sqlt = SQL::Translator->new(
23 quote_table_names => 1,
24 quote_field_names => 1,
29 die "Can't find test schema $xmlfile" unless -e $xmlfile;
31 my @sql = $sqlt->translate(
32 from => 'XML-SQLFairy',
35 ) or die $sqlt->error;
37 my $sql_string = $sqlt->translate(
38 from => 'XML-SQLFairy',
41 ) or die $sqlt->error;
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,
50 "description" clob DEFAULT \'\',
51 "email" varchar2(500),
52 "explicitnulldef" varchar2(4000),
53 "explicitemptystring" varchar2(4000) DEFAULT \'\',
54 "emptytagdef" varchar2(4000) DEFAULT \'\',
55 "another_id" number(10) DEFAULT \'2\',
58 CONSTRAINT "u_Basic_emailuniqueindex" UNIQUE ("email"),
59 CONSTRAINT "u_Basic_very_long_index_name_o" UNIQUE ("title")
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,
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"
76 new."id" IS NULL OR new."id" = 0
79 SELECT "sq_Basic_id".nextval
84 'CREATE OR REPLACE TRIGGER "ts_Basic_timest"
85 BEFORE INSERT OR UPDATE ON "Basic"
86 FOR EACH ROW WHEN (new."timest" IS NULL)
88 SELECT sysdate INTO :new."timest" FROM dual;
91 'CREATE OR REPLACE TRIGGER "ai_Another_id"
92 BEFORE INSERT ON "Another"
94 new."id" IS NULL OR new."id" = 0
97 SELECT "sq_Another_id".nextval
102 'CREATE INDEX "titleindex" on "Basic" ("title")'];
104 is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
106 eq_or_diff($sql_string, q|DROP TABLE "Basic" CASCADE CONSTRAINTS;
108 DROP SEQUENCE "sq_Basic_id01";
110 CREATE SEQUENCE "sq_Basic_id01";
112 CREATE TABLE "Basic" (
113 "id" number(10) NOT NULL,
114 "title" varchar2(100) DEFAULT 'hello' NOT NULL,
115 "description" clob DEFAULT '',
116 "email" varchar2(500),
117 "explicitnulldef" varchar2(4000),
118 "explicitemptystring" varchar2(4000) DEFAULT '',
119 "emptytagdef" varchar2(4000) DEFAULT '',
120 "another_id" number(10) DEFAULT '2',
123 CONSTRAINT "u_Basic_emailuniqueindex01" UNIQUE ("email"),
124 CONSTRAINT "u_Basic_very_long_index_name01" UNIQUE ("title")
127 DROP TABLE "Another" CASCADE CONSTRAINTS;
129 DROP SEQUENCE "sq_Another_id01";
131 CREATE SEQUENCE "sq_Another_id01";
133 CREATE TABLE "Another" (
134 "id" number(10) NOT NULL,
139 DROP VIEW "email_list";
141 CREATE VIEW "email_list" AS
142 SELECT email FROM Basic WHERE (email IS NOT NULL);
144 ALTER TABLE "Basic" ADD CONSTRAINT "Basic_another_id_fk01" FOREIGN KEY ("another_id") REFERENCES "Another" ("id");
146 CREATE INDEX "titleindex01" on "Basic" ("title");
148 CREATE OR REPLACE TRIGGER "ai_Basic_id01"
149 BEFORE INSERT ON "Basic"
151 new."id" IS NULL OR new."id" = 0
154 SELECT "sq_Basic_id01".nextval
160 CREATE OR REPLACE TRIGGER "ts_Basic_timest01"
161 BEFORE INSERT OR UPDATE ON "Basic"
162 FOR EACH ROW WHEN (new."timest" IS NULL)
164 SELECT sysdate INTO :new."timest" FROM dual;
168 CREATE OR REPLACE TRIGGER "ai_Another_id01"
169 BEFORE INSERT ON "Another"
171 new."id" IS NULL OR new."id" = 0
174 SELECT "sq_Another_id01".nextval