6 use Test::SQL::Translator qw(maybe_plan);
8 use SQL::Translator::Schema;
9 use SQL::Translator::Schema::View;
10 use SQL::Translator::Schema::Table;
11 use SQL::Translator::Producer::SQLite;
12 $SQL::Translator::Producer::SQLite::NO_QUOTES = 0;
15 my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
16 fields => [qw/id name/],
17 sql => 'SELECT id, name FROM thing',
22 my $create_opts = { no_comments => 1 };
23 my $view1_sql1 = [ SQL::Translator::Producer::SQLite::create_view($view1, $create_opts) ];
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');
30 my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
31 fields => [qw/id name/],
32 sql => 'SELECT id, name FROM thing',);
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');
42 my $table = SQL::Translator::Schema::Table->new(
46 name => 'foreign_key',
47 data_type => 'integer',
50 my $constraint = SQL::Translator::Schema::Constraint->new(
53 type => 'FOREIGN_KEY',
54 fields => ['foreign_key'],
55 reference_fields => ['id'],
56 reference_table => 'foo',
57 on_delete => 'RESTRICT',
58 on_update => 'CASCADE',
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"');
65 my $table = SQL::Translator::Schema::Table->new(
70 data_type => 'integer',
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');
79 my $table = SQL::Translator::Schema::Table->new(
104 my $expected = [ qq<CREATE TABLE "foo" (
110 my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })];
111 is_deeply($result, $expected, 'correctly translated bytea to blob');
115 my $table = SQL::Translator::Schema::Table->new(
120 data_type => 'integer',
121 default_value => \'gunshow',
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');
129 my $table = SQL::Translator::Schema::Table->new(
134 data_type => 'integer',
135 default_value => 'frew',
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');
143 my $table = SQL::Translator::Schema::Table->new(
148 data_type => 'integer',
149 default_value => 'NULL',
153 default_value => 'now()',
157 default_value => 'CURRENT_TIMESTAMP',
159 my $expected = [ qq<CREATE TABLE "foo" (
160 "id" integer DEFAULT NULL,
161 "when" DEFAULT now(),
162 "at" DEFAULT CURRENT_TIMESTAMP
164 my $result = [SQL::Translator::Producer::SQLite::create_table($table, { no_comments => 1 })];
165 is_deeply($result, $expected, 'correctly unquoted excempted DEFAULTs');
169 my $table = SQL::Translator::Schema::Table->new(
170 name => 'some_table',
174 data_type => 'integer',
175 is_auto_increment => 1,
178 auto_increment_type => 'monotonic',
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');
188 my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => ['foo'] );
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');
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');
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');
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');
216 my $schema = SQL::Translator::Schema->new();
217 my $table = $schema->add_table( name => 'foo', fields => ['bar'] );
220 my $trigger = $schema->add_trigger(
222 perform_action_when => 'before',
223 database_events => 'update',
226 action => 'BEGIN baz() END'
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');
233 my $trigger = $schema->add_trigger(
234 name => 'mytrigger2',
235 perform_action_when => 'after',
236 database_events => ['insert'],
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');