6 use Test::SQL::Translator;
10 use SQL::Translator::Schema::Constants;
13 maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
14 'SQL::Translator::Producer::Oracle');
17 my $xmlfile = "$Bin/data/xml/schema.xml";
20 $sqlt = SQL::Translator->new(
22 quote_table_names => 1,
23 quote_field_names => 1,
28 die "Can't find test schema $xmlfile" unless -e $xmlfile;
30 my @sql = $sqlt->translate(
31 from => 'XML-SQLFairy',
34 ) or die $sqlt->error;
36 my $sql_string = $sqlt->translate(
37 from => 'XML-SQLFairy',
40 ) or die $sqlt->error;
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,
49 "description" clob DEFAULT \'\',
50 "email" varchar2(500),
51 "explicitnulldef" varchar2(4000),
52 "explicitemptystring" varchar2(4000) DEFAULT \'\',
53 "emptytagdef" varchar2(4000) DEFAULT \'\',
54 "another_id" number(10) DEFAULT \'2\',
57 CONSTRAINT "Basic_emailuniqueindex" UNIQUE ("email")
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,
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"
74 new."id" IS NULL OR new."id" = 0
77 SELECT "sq_Basic_id".nextval
82 'CREATE OR REPLACE TRIGGER "ts_Basic_timest"
83 BEFORE INSERT OR UPDATE ON "Basic"
84 FOR EACH ROW WHEN (new."timest" IS NULL)
86 SELECT sysdate INTO :new."timest" FROM dual;
89 'CREATE OR REPLACE TRIGGER "ai_Another_id"
90 BEFORE INSERT ON "Another"
92 new."id" IS NULL OR new."id" = 0
95 SELECT "sq_Another_id".nextval
100 'CREATE INDEX "titleindex" on "Basic" ("title")'];
102 is_deeply(\@sql, $want, 'Got correct Oracle statements in list context');
104 is($sql_string, q|DROP TABLE "Basic" CASCADE CONSTRAINTS;
106 DROP SEQUENCE "sq_Basic_id01";
108 CREATE SEQUENCE "sq_Basic_id01";
110 CREATE TABLE "Basic" (
111 "id" number(10) NOT NULL,
112 "title" varchar2(100) DEFAULT 'hello' NOT NULL,
113 "description" clob DEFAULT '',
114 "email" varchar2(500),
115 "explicitnulldef" varchar2(4000),
116 "explicitemptystring" varchar2(4000) DEFAULT '',
117 "emptytagdef" varchar2(4000) DEFAULT '',
118 "another_id" number(10) DEFAULT '2',
121 CONSTRAINT "Basic_emailuniqueindex" UNIQUE ("email")
124 DROP TABLE "Another" CASCADE CONSTRAINTS;
126 DROP SEQUENCE "sq_Another_id01";
128 CREATE SEQUENCE "sq_Another_id01";
130 CREATE TABLE "Another" (
131 "id" number(10) NOT NULL,
136 DROP VIEW "email_list";
138 CREATE VIEW "email_list" AS
139 SELECT email FROM Basic WHERE (email IS NOT NULL);
141 ALTER TABLE "Basic" ADD CONSTRAINT "Basic_another_id_fk01" FOREIGN KEY ("another_id") REFERENCES "Another" ("id");
143 CREATE INDEX "titleindex01" on "Basic" ("title");
145 CREATE OR REPLACE TRIGGER "ai_Basic_id01"
146 BEFORE INSERT ON "Basic"
148 new."id" IS NULL OR new."id" = 0
151 SELECT "sq_Basic_id01".nextval
157 CREATE OR REPLACE TRIGGER "ts_Basic_timest01"
158 BEFORE INSERT OR UPDATE ON "Basic"
159 FOR EACH ROW WHEN (new."timest" IS NULL)
161 SELECT sysdate INTO :new."timest" FROM dual;
165 CREATE OR REPLACE TRIGGER "ai_Another_id01"
166 BEFORE INSERT ON "Another"
168 new."id" IS NULL OR new."id" = 0
171 SELECT "sq_Another_id01".nextval