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( |
23 | no_comments => 1, |
ee91ab6f |
24 | show_warnings => 0, |
8c4efd11 |
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 => 'SQLite', |
33 | filename => $xmlfile, |
34 | ) or die $sqlt->error; |
35 | |
4d438549 |
36 | eq_or_diff($sql, << "SQL"); |
8c4efd11 |
37 | BEGIN TRANSACTION; |
38 | |
d8cf2279 |
39 | DROP TABLE 'Basic'; |
40 | |
41 | CREATE TABLE 'Basic' ( |
42 | 'id' INTEGER PRIMARY KEY NOT NULL, |
43 | 'title' varchar(100) NOT NULL DEFAULT 'hello', |
44 | 'description' text DEFAULT '', |
45 | 'email' varchar(500), |
46 | 'explicitnulldef' varchar, |
47 | 'explicitemptystring' varchar DEFAULT '', |
8c4efd11 |
48 | -- Hello emptytagdef |
d8cf2279 |
49 | 'emptytagdef' varchar DEFAULT '', |
50 | 'another_id' int(10) DEFAULT 2, |
51 | 'timest' timestamp, |
ea4a3ecc |
52 | FOREIGN KEY ('another_id') REFERENCES 'Another'('id') |
8c4efd11 |
53 | ); |
54 | |
d8cf2279 |
55 | CREATE INDEX 'titleindex' ON 'Basic' ('title'); |
24d9fe69 |
56 | |
d8cf2279 |
57 | CREATE UNIQUE INDEX 'emailuniqueindex' ON 'Basic' ('email'); |
8c4efd11 |
58 | |
d8cf2279 |
59 | CREATE UNIQUE INDEX 'very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms' ON 'Basic' ('title'); |
3b9249fb |
60 | |
d8cf2279 |
61 | DROP TABLE 'Another'; |
24d9fe69 |
62 | |
d8cf2279 |
63 | CREATE TABLE 'Another' ( |
64 | 'id' INTEGER PRIMARY KEY NOT NULL, |
65 | 'num' numeric(10,2) |
b08b5416 |
66 | ); |
67 | |
d8cf2279 |
68 | DROP VIEW IF EXISTS 'email_list'; |
4c0d31c1 |
69 | |
d8cf2279 |
70 | CREATE VIEW 'email_list' AS |
3910f248 |
71 | SELECT email FROM Basic WHERE (email IS NOT NULL); |
ec59a597 |
72 | |
d8cf2279 |
73 | DROP TRIGGER IF EXISTS 'foo_trigger'; |
f9c96971 |
74 | |
d8cf2279 |
75 | CREATE TRIGGER 'foo_trigger' after insert on 'Basic' BEGIN update modified=timestamp(); END; |
f9c96971 |
76 | |
d8cf2279 |
77 | DROP TRIGGER IF EXISTS 'bar_trigger_insert'; |
410d4a42 |
78 | |
d8cf2279 |
79 | CREATE TRIGGER 'bar_trigger_insert' before insert on 'Basic' BEGIN update modified2=timestamp(); END; |
410d4a42 |
80 | |
d8cf2279 |
81 | DROP TRIGGER IF EXISTS 'bar_trigger_update'; |
410d4a42 |
82 | |
d8cf2279 |
83 | CREATE TRIGGER 'bar_trigger_update' before update on 'Basic' BEGIN update modified2=timestamp(); END; |
410d4a42 |
84 | |
8c4efd11 |
85 | COMMIT; |
86 | SQL |
24d9fe69 |
87 | |
88 | # Test in list context |
89 | my @sql = $sqlt->translate( |
90 | from => 'XML-SQLFairy', |
91 | to => 'SQLite', |
92 | filename => $xmlfile, |
93 | ) or die $sqlt->error; |
94 | |
aee4b66e |
95 | eq_or_diff(\@sql, |
24d9fe69 |
96 | [ |
b6b0696f |
97 | 'BEGIN TRANSACTION', |
d8cf2279 |
98 | q<DROP TABLE 'Basic'>, |
99 | q<CREATE TABLE 'Basic' ( |
100 | 'id' INTEGER PRIMARY KEY NOT NULL, |
101 | 'title' varchar(100) NOT NULL DEFAULT 'hello', |
102 | 'description' text DEFAULT '', |
103 | 'email' varchar(500), |
104 | 'explicitnulldef' varchar, |
105 | 'explicitemptystring' varchar DEFAULT '', |
24d9fe69 |
106 | -- Hello emptytagdef |
d8cf2279 |
107 | 'emptytagdef' varchar DEFAULT '', |
108 | 'another_id' int(10) DEFAULT 2, |
109 | 'timest' timestamp, |
ea4a3ecc |
110 | FOREIGN KEY ('another_id') REFERENCES 'Another'('id') |
d8cf2279 |
111 | )>, |
112 | q<CREATE INDEX 'titleindex' ON 'Basic' ('title')>, |
113 | q<CREATE UNIQUE INDEX 'emailuniqueindex' ON 'Basic' ('email')>, |
114 | q<CREATE UNIQUE INDEX 'very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms' ON 'Basic' ('title')>, |
115 | q<DROP TABLE 'Another'>, |
116 | q<CREATE TABLE 'Another' ( |
117 | 'id' INTEGER PRIMARY KEY NOT NULL, |
118 | 'num' numeric(10,2) |
119 | )>, |
120 | q<DROP VIEW IF EXISTS 'email_list'>, |
121 | q<CREATE VIEW 'email_list' AS |
122 | SELECT email FROM Basic WHERE (email IS NOT NULL)>, |
123 | q<DROP TRIGGER IF EXISTS 'foo_trigger'>, |
124 | q<CREATE TRIGGER 'foo_trigger' after insert on 'Basic' BEGIN update modified=timestamp(); END>, |
125 | q<DROP TRIGGER IF EXISTS 'bar_trigger_insert'>, |
126 | q<CREATE TRIGGER 'bar_trigger_insert' before insert on 'Basic' BEGIN update modified2=timestamp(); END>, |
127 | q<DROP TRIGGER IF EXISTS 'bar_trigger_update'>, |
128 | q<CREATE TRIGGER 'bar_trigger_update' before update on 'Basic' BEGIN update modified2=timestamp(); END>, |
410d4a42 |
129 | 'COMMIT', |
130 | |
24d9fe69 |
131 | ], 'SQLite translate in list context matches'); |
132 | |
133 | |