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