Commit | Line | Data |
8c4efd11 |
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; |
4d438549 |
8 | use Test::Differences; |
8c4efd11 |
9 | use Data::Dumper; |
10 | use SQL::Translator; |
11 | use SQL::Translator::Schema::Constants; |
12 | |
13 | |
14 | BEGIN { |
24d9fe69 |
15 | maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy', |
8c4efd11 |
16 | 'SQL::Translator::Producer::SQLite'); |
17 | } |
18 | |
19 | my $xmlfile = "$Bin/data/xml/schema.xml"; |
20 | |
21 | my $sqlt; |
22 | $sqlt = SQL::Translator->new( |
2d23c1e1 |
23 | quote_identifiers => 1, |
8c4efd11 |
24 | no_comments => 1, |
ee91ab6f |
25 | show_warnings => 0, |
8c4efd11 |
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 => 'SQLite', |
34 | filename => $xmlfile, |
35 | ) or die $sqlt->error; |
36 | |
4d438549 |
37 | eq_or_diff($sql, << "SQL"); |
8c4efd11 |
38 | BEGIN TRANSACTION; |
39 | |
f6e49d34 |
40 | DROP TABLE "Basic"; |
41 | |
42 | CREATE TABLE "Basic" ( |
43 | "id" INTEGER PRIMARY KEY NOT NULL, |
44 | "title" varchar(100) NOT NULL DEFAULT 'hello', |
45 | "description" text DEFAULT '', |
46 | "email" varchar(500), |
47 | "explicitnulldef" varchar, |
48 | "explicitemptystring" varchar DEFAULT '', |
8c4efd11 |
49 | -- Hello emptytagdef |
f6e49d34 |
50 | "emptytagdef" varchar DEFAULT '', |
51 | "another_id" int(10) DEFAULT 2, |
52 | "timest" timestamp, |
53 | FOREIGN KEY ("another_id") REFERENCES "Another"("id") |
8c4efd11 |
54 | ); |
55 | |
f6e49d34 |
56 | CREATE INDEX "titleindex" ON "Basic" ("title"); |
24d9fe69 |
57 | |
f6e49d34 |
58 | CREATE UNIQUE INDEX "emailuniqueindex" ON "Basic" ("email"); |
8c4efd11 |
59 | |
f6e49d34 |
60 | CREATE UNIQUE INDEX "very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms" ON "Basic" ("title"); |
3b9249fb |
61 | |
f6e49d34 |
62 | DROP TABLE "Another"; |
24d9fe69 |
63 | |
f6e49d34 |
64 | CREATE TABLE "Another" ( |
65 | "id" INTEGER PRIMARY KEY NOT NULL, |
66 | "num" numeric(10,2) |
b08b5416 |
67 | ); |
68 | |
f6e49d34 |
69 | DROP VIEW IF EXISTS "email_list"; |
4c0d31c1 |
70 | |
f6e49d34 |
71 | CREATE VIEW "email_list" AS |
3910f248 |
72 | SELECT email FROM Basic WHERE (email IS NOT NULL); |
ec59a597 |
73 | |
f6e49d34 |
74 | DROP TRIGGER IF EXISTS "foo_trigger"; |
f9c96971 |
75 | |
f6e49d34 |
76 | CREATE TRIGGER "foo_trigger" after insert on "Basic" BEGIN update modified=timestamp(); END; |
f9c96971 |
77 | |
f6e49d34 |
78 | DROP TRIGGER IF EXISTS "bar_trigger_insert"; |
410d4a42 |
79 | |
f6e49d34 |
80 | CREATE TRIGGER "bar_trigger_insert" before insert on "Basic" BEGIN update modified2=timestamp(); END; |
410d4a42 |
81 | |
f6e49d34 |
82 | DROP TRIGGER IF EXISTS "bar_trigger_update"; |
410d4a42 |
83 | |
f6e49d34 |
84 | CREATE TRIGGER "bar_trigger_update" before update on "Basic" BEGIN update modified2=timestamp(); END; |
410d4a42 |
85 | |
8c4efd11 |
86 | COMMIT; |
87 | SQL |
24d9fe69 |
88 | |
89 | # Test in list context |
90 | my @sql = $sqlt->translate( |
91 | from => 'XML-SQLFairy', |
92 | to => 'SQLite', |
93 | filename => $xmlfile, |
94 | ) or die $sqlt->error; |
95 | |
aee4b66e |
96 | eq_or_diff(\@sql, |
24d9fe69 |
97 | [ |
b6b0696f |
98 | 'BEGIN TRANSACTION', |
f6e49d34 |
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 '', |
24d9fe69 |
107 | -- Hello emptytagdef |
f6e49d34 |
108 | "emptytagdef" varchar DEFAULT '', |
109 | "another_id" int(10) DEFAULT 2, |
110 | "timest" timestamp, |
111 | FOREIGN KEY ("another_id") REFERENCES "Another"("id") |
d8cf2279 |
112 | )>, |
f6e49d34 |
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, |
119 | "num" numeric(10,2) |
d8cf2279 |
120 | )>, |
f6e49d34 |
121 | q<DROP VIEW IF EXISTS "email_list">, |
122 | q<CREATE VIEW "email_list" AS |
d8cf2279 |
123 | SELECT email FROM Basic WHERE (email IS NOT NULL)>, |
f6e49d34 |
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>, |
410d4a42 |
130 | 'COMMIT', |
131 | |
24d9fe69 |
132 | ], 'SQLite translate in list context matches'); |
133 | |
134 | |