1#!/usr/bin/perl
2# vim: set ft=perl:
3
4use strict;
5use Test::More;
6use Test::SQL::Translator qw(maybe_plan);
7
8use SQL::Translator::Schema;
9use SQL::Translator::Schema::View;
10use SQL::Translator::Schema::Table;
11use SQL::Translator::Producer::SQLite;
12$SQL::Translator::Producer::SQLite::NO_QUOTES = 0;
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 };
23  my $view1_sql1 = [ SQL::Translator::Producer::SQLite::create_view($view1, $create_opts) ];
24
25  my $view_sql_replace = [ 'CREATE TEMPORARY VIEW IF NOT EXISTS "view_foo" AS
26    SELECT id, name FROM thing' ];
27  is_deeply($view1_sql1, $view_sql_replace, 'correct "CREATE TEMPORARY VIEW" SQL');
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
34  my $view1_sql2 = [ SQL::Translator::Producer::SQLite::create_view($view2, $create_opts) ];
35  my $view_sql_noreplace = [ 'CREATE VIEW "view_foo" AS
36    SELECT id, name FROM thing' ];
37  is_deeply($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
38}
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',
48        default_value => 1,
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    );
60    my $expected = [ 'FOREIGN KEY ("foreign_key") REFERENCES "foo"("id") ON DELETE RESTRICT ON UPDATE CASCADE'];
61    my $result =  [SQL::Translator::Producer::SQLite::create_foreignkey($constraint,$create_opts)];
62    is_deeply($result, $expected, 'correct "FOREIGN KEY"');
63}
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
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
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
168{
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{
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
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
246{
247    my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => ['foo'] );
248    my $constr = $table->add_constraint(name => 'constr', expression => "foo != 'baz'");
249    my ($def) = SQL::Translator::Producer::SQLite::create_check_constraint($constr);
250
251    is($def, q{CONSTRAINT "constr" CHECK(foo != 'baz')}, 'check constraint created');
252}
253
254done_testing;
255