6 use Test::SQL::Translator;
11 use SQL::Translator::Schema::Constants;
15 maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
16 'SQL::Translator::Producer::SQLite');
19 my $xmlfile = "$Bin/data/xml/schema.xml";
22 $sqlt = SQL::Translator->new(
23 quote_identifiers => 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 eq_or_diff($sql, << "SQL");
42 CREATE TABLE "Basic" (
43 "id" INTEGER PRIMARY KEY NOT NULL,
44 "title" varchar(100) NOT NULL DEFAULT 'hello',
45 "description" text DEFAULT '',
47 "explicitnulldef" varchar,
48 "explicitemptystring" varchar DEFAULT '',
50 "emptytagdef" varchar DEFAULT '',
51 "another_id" int(10) DEFAULT 2,
53 FOREIGN KEY ("another_id") REFERENCES "Another"("id")
56 CREATE INDEX "titleindex" ON "Basic" ("title");
58 CREATE UNIQUE INDEX "emailuniqueindex" ON "Basic" ("email");
60 CREATE UNIQUE INDEX "very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms" ON "Basic" ("title");
64 CREATE TABLE "Another" (
65 "id" INTEGER PRIMARY KEY NOT NULL,
69 DROP VIEW IF EXISTS "email_list";
71 CREATE VIEW "email_list" AS
72 SELECT email FROM Basic WHERE (email IS NOT NULL);
74 DROP TRIGGER IF EXISTS "foo_trigger";
76 CREATE TRIGGER "foo_trigger" after insert on "Basic" BEGIN update modified=timestamp(); END;
78 DROP TRIGGER IF EXISTS "bar_trigger_insert";
80 CREATE TRIGGER "bar_trigger_insert" before insert on "Basic" BEGIN update modified2=timestamp(); END;
82 DROP TRIGGER IF EXISTS "bar_trigger_update";
84 CREATE TRIGGER "bar_trigger_update" before update on "Basic" BEGIN update modified2=timestamp(); END;
89 # Test in list context
90 my @sql = $sqlt->translate(
91 from => 'XML-SQLFairy',
94 ) or die $sqlt->error;
99 q<DROP TABLE "Basic">,
100 q<CREATE TABLE "Basic" (
101 "id" INTEGER PRIMARY KEY NOT NULL,
102 "title" varchar(100) NOT NULL DEFAULT 'hello',
103 "description" text DEFAULT '',
104 "email" varchar(500),
105 "explicitnulldef" varchar,
106 "explicitemptystring" varchar DEFAULT '',
108 "emptytagdef" varchar DEFAULT '',
109 "another_id" int(10) DEFAULT 2,
111 FOREIGN KEY ("another_id") REFERENCES "Another"("id")
113 q<CREATE INDEX "titleindex" ON "Basic" ("title")>,
114 q<CREATE UNIQUE INDEX "emailuniqueindex" ON "Basic" ("email")>,
115 q<CREATE UNIQUE INDEX "very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms" ON "Basic" ("title")>,
116 q<DROP TABLE "Another">,
117 q<CREATE TABLE "Another" (
118 "id" INTEGER PRIMARY KEY NOT NULL,
121 q<DROP VIEW IF EXISTS "email_list">,
122 q<CREATE VIEW "email_list" AS
123 SELECT email FROM Basic WHERE (email IS NOT NULL)>,
124 q<DROP TRIGGER IF EXISTS "foo_trigger">,
125 q<CREATE TRIGGER "foo_trigger" after insert on "Basic" BEGIN update modified=timestamp(); END>,
126 q<DROP TRIGGER IF EXISTS "bar_trigger_insert">,
127 q<CREATE TRIGGER "bar_trigger_insert" before insert on "Basic" BEGIN update modified2=timestamp(); END>,
128 q<DROP TRIGGER IF EXISTS "bar_trigger_update">,
129 q<CREATE TRIGGER "bar_trigger_update" before update on "Basic" BEGIN update modified2=timestamp(); END>,
132 ], 'SQLite translate in list context matches');