Commit | Line | Data |
24d9fe69 |
1 | #!/usr/bin/perl |
2 | # vim: set ft=perl: |
3 | |
4 | use strict; |
27f0e868 |
5 | use Test::More; |
24d9fe69 |
6 | use Test::SQL::Translator qw(maybe_plan); |
24d9fe69 |
7 | |
e533bcdd |
8 | use SQL::Translator::Schema; |
24d9fe69 |
9 | use SQL::Translator::Schema::View; |
ea4a3ecc |
10 | use SQL::Translator::Schema::Table; |
24d9fe69 |
11 | use SQL::Translator::Producer::SQLite; |
2d23c1e1 |
12 | $SQL::Translator::Producer::SQLite::NO_QUOTES = 0; |
24d9fe69 |
13 | |
14 | { |
15 | my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo', |
16 | fields => [qw/id name/], |
17 | sql => 'SELECT id, name FROM thing', |
18 | extra => { |
19 | temporary => 1, |
20 | if_not_exists => 1, |
21 | }); |
22 | my $create_opts = { no_comments => 1 }; |
4c0d31c1 |
23 | my $view1_sql1 = [ SQL::Translator::Producer::SQLite::create_view($view1, $create_opts) ]; |
24d9fe69 |
24 | |
f6e49d34 |
25 | my $view_sql_replace = [ 'CREATE TEMPORARY VIEW IF NOT EXISTS "view_foo" AS |
26 | SELECT id, name FROM thing' ]; |
4c0d31c1 |
27 | is_deeply($view1_sql1, $view_sql_replace, 'correct "CREATE TEMPORARY VIEW" SQL'); |
24d9fe69 |
28 | |
29 | |
30 | my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo', |
31 | fields => [qw/id name/], |
32 | sql => 'SELECT id, name FROM thing',); |
33 | |
4c0d31c1 |
34 | my $view1_sql2 = [ SQL::Translator::Producer::SQLite::create_view($view2, $create_opts) ]; |
f6e49d34 |
35 | my $view_sql_noreplace = [ 'CREATE VIEW "view_foo" AS |
36 | SELECT id, name FROM thing' ]; |
4c0d31c1 |
37 | is_deeply($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL'); |
24d9fe69 |
38 | } |
ea4a3ecc |
39 | { |
40 | my $create_opts; |
41 | |
42 | my $table = SQL::Translator::Schema::Table->new( |
43 | name => 'foo_table', |
44 | ); |
45 | $table->add_field( |
46 | name => 'foreign_key', |
47 | data_type => 'integer', |
27f0e868 |
48 | default_value => 1, |
ea4a3ecc |
49 | ); |
50 | my $constraint = SQL::Translator::Schema::Constraint->new( |
51 | table => $table, |
52 | name => 'fk', |
53 | type => 'FOREIGN_KEY', |
54 | fields => ['foreign_key'], |
55 | reference_fields => ['id'], |
56 | reference_table => 'foo', |
57 | on_delete => 'RESTRICT', |
58 | on_update => 'CASCADE', |
59 | ); |
f6e49d34 |
60 | my $expected = [ 'FOREIGN KEY ("foreign_key") REFERENCES "foo"("id") ON DELETE RESTRICT ON UPDATE CASCADE']; |
ea4a3ecc |
61 | my $result = [SQL::Translator::Producer::SQLite::create_foreignkey($constraint,$create_opts)]; |
62 | is_deeply($result, $expected, 'correct "FOREIGN KEY"'); |
63 | } |
27f0e868 |
64 | { |
65 | my $table = SQL::Translator::Schema::Table->new( |
66 | name => 'foo_table', |
67 | ); |
68 | $table->add_field( |
69 | name => 'id', |
70 | data_type => 'integer', |
71 | default_value => 1, |
72 | ); |
73 | my $expected = [ qq<CREATE TABLE "foo_table" (\n "id" integer DEFAULT 1\n)>]; |
74 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
75 | is_deeply($result, $expected, 'correctly unquoted DEFAULT'); |
76 | } |
77 | |
8224e2cf |
78 | { |
79 | my $table = SQL::Translator::Schema::Table->new( |
80 | name => 'foo', |
81 | ); |
82 | $table->add_field( |
83 | name => 'data', |
84 | data_type => 'bytea', |
85 | ); |
86 | $table->add_field( |
87 | name => 'data2', |
88 | data_type => 'set', |
89 | ); |
90 | $table->add_field( |
91 | name => 'data2', |
92 | data_type => 'set', |
93 | ); |
94 | $table->add_field( |
95 | name => 'data3', |
96 | data_type => 'text', |
97 | size => 30, |
98 | ); |
99 | $table->add_field( |
100 | name => 'data4', |
101 | data_type => 'blob', |
102 | size => 30, |
103 | ); |
104 | my $expected = [ qq<CREATE TABLE "foo" ( |
105 | "data" blob, |
106 | "data2" varchar, |
107 | "data3" text, |
108 | "data4" blob |
109 | )>]; |
110 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
111 | is_deeply($result, $expected, 'correctly translated bytea to blob'); |
112 | } |
113 | |
ff6dc6d4 |
114 | { |
115 | my $table = SQL::Translator::Schema::Table->new( |
116 | name => 'foo_table', |
117 | ); |
118 | $table->add_field( |
119 | name => 'id', |
120 | data_type => 'integer', |
121 | default_value => \'gunshow', |
122 | ); |
123 | my $expected = [ qq<CREATE TABLE "foo_table" (\n "id" integer DEFAULT gunshow\n)>]; |
124 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
125 | is_deeply($result, $expected, 'correctly unquoted DEFAULT'); |
126 | } |
127 | |
128 | { |
129 | my $table = SQL::Translator::Schema::Table->new( |
130 | name => 'foo_table', |
131 | ); |
132 | $table->add_field( |
133 | name => 'id', |
134 | data_type => 'integer', |
135 | default_value => 'frew', |
136 | ); |
137 | my $expected = [ qq<CREATE TABLE "foo_table" (\n "id" integer DEFAULT 'frew'\n)>]; |
138 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
139 | is_deeply($result, $expected, 'correctly quoted DEFAULT'); |
140 | } |
141 | |
142 | { |
143 | my $table = SQL::Translator::Schema::Table->new( |
144 | name => 'foo', |
145 | ); |
146 | $table->add_field( |
147 | name => 'id', |
148 | data_type => 'integer', |
149 | default_value => 'NULL', |
150 | ); |
151 | $table->add_field( |
152 | name => 'when', |
153 | default_value => 'now()', |
154 | ); |
155 | $table->add_field( |
156 | name => 'at', |
157 | default_value => 'CURRENT_TIMESTAMP', |
158 | ); |
159 | my $expected = [ qq<CREATE TABLE "foo" ( |
160 | "id" integer DEFAULT NULL, |
161 | "when" DEFAULT now(), |
162 | "at" DEFAULT CURRENT_TIMESTAMP |
163 | )>]; |
164 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
165 | is_deeply($result, $expected, 'correctly unquoted excempted DEFAULTs'); |
166 | } |
167 | |
2491a467 |
168 | { |
7f3f64d7 |
169 | my $table = SQL::Translator::Schema::Table->new( |
170 | name => 'some_table', |
171 | ); |
172 | $table->add_field( |
173 | name => 'id', |
174 | data_type => 'integer', |
175 | is_auto_increment => 1, |
176 | is_nullable => 0, |
177 | extra => { |
178 | auto_increment_type => 'monotonic', |
179 | }, |
180 | ); |
181 | $table->primary_key('id'); |
182 | my $expected = [ qq<CREATE TABLE "some_table" (\n "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL\n)>]; |
183 | my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })]; |
184 | is_deeply($result, $expected, 'correctly built monotonicly autoincremened PK'); |
185 | } |
186 | |
187 | { |
2491a467 |
188 | my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => ['foo'] ); |
189 | |
190 | { |
191 | my $index = $table->add_index(name => 'myindex', fields => ['foo']); |
192 | my ($def) = SQL::Translator::Producer::SQLite::create_index($index); |
193 | is($def, 'CREATE INDEX "myindex" ON "foobar" ("foo")', 'index created'); |
194 | } |
195 | |
196 | { |
197 | my $index = $table->add_index(fields => ['foo']); |
198 | my ($def) = SQL::Translator::Producer::SQLite::create_index($index); |
199 | is($def, 'CREATE INDEX "foobar_idx" ON "foobar" ("foo")', 'index created'); |
200 | } |
201 | |
202 | { |
203 | my $constr = $table->add_constraint(name => 'constr', fields => ['foo']); |
204 | my ($def) = SQL::Translator::Producer::SQLite::create_constraint($constr); |
205 | is($def, 'CREATE UNIQUE INDEX "constr" ON "foobar" ("foo")', 'constraint created'); |
206 | } |
207 | |
208 | { |
209 | my $constr = $table->add_constraint(fields => ['foo']); |
210 | my ($def) = SQL::Translator::Producer::SQLite::create_constraint($constr); |
211 | is($def, 'CREATE UNIQUE INDEX "foobar_idx02" ON "foobar" ("foo")', 'constraint created'); |
212 | } |
213 | } |
214 | |
e533bcdd |
215 | { |
216 | my $schema = SQL::Translator::Schema->new(); |
217 | my $table = $schema->add_table( name => 'foo', fields => ['bar'] ); |
218 | |
219 | { |
220 | my $trigger = $schema->add_trigger( |
221 | name => 'mytrigger', |
222 | perform_action_when => 'before', |
223 | database_events => 'update', |
224 | on_table => 'foo', |
225 | fields => ['bar'], |
226 | action => 'BEGIN baz() END' |
227 | ); |
228 | my ($def) = SQL::Translator::Producer::SQLite::create_trigger($trigger); |
229 | is($def, 'CREATE TRIGGER "mytrigger" before update on "foo" BEGIN baz() END', 'trigger created'); |
230 | } |
231 | |
232 | { |
233 | my $trigger = $schema->add_trigger( |
234 | name => 'mytrigger2', |
235 | perform_action_when => 'after', |
236 | database_events => ['insert'], |
237 | on_table => 'foo', |
238 | fields => ['bar'], |
239 | action => 'baz()' |
240 | ); |
241 | my ($def) = SQL::Translator::Producer::SQLite::create_trigger($trigger); |
242 | is($def, 'CREATE TRIGGER "mytrigger2" after insert on "foo" BEGIN baz() END', 'trigger created'); |
243 | } |
244 | } |
245 | |
27f0e868 |
246 | done_testing; |