2 role SQL::Translator::Producer::SQL::SQLite {
3 use MooseX::Types::Moose qw(HashRef);
4 use SQL::Translator::Constants qw(:sqlt_types :sqlt_constants);
5 use SQL::Translator::Types qw(Column Constraint Index Trigger Table View);
7 around _build_data_type_mapping {
8 my $data_type_mapping = $self->$orig;
9 $data_type_mapping->{SQL_FLOAT()} = 'real';
10 $data_type_mapping->{SQL_BIGINT()} = 'integer';
12 return $data_type_mapping;
15 method header_comment(Str $comment_char? = '-- ') {
16 my $producer = __PACKAGE__;
17 my $now = scalar localtime;
19 my $header_comment = <<"HEADER_COMMENT";
21 ${comment_char}Created by $producer
22 ${comment_char}Created on $now
26 # Any additional stuff passed in
27 for my $additional_comment (@_) {
28 $header_comment .= "${comment_char}${additional_comment}\n";
31 return $header_comment;
35 my $translator = $self->translator;
36 # local $DEBUG = $translator->debug;
37 # local $WARN = $translator->show_warnings;
38 my $no_comments = $translator->no_comments;
39 my $add_drop_table = $translator->add_drop_table;
40 my $schema = $translator->schema;
41 my $producer_args = $translator->producer_args;
42 my $sqlite_version = $producer_args->{sqlite_version} || 0;
43 my $no_txn = $producer_args->{no_transaction};
45 # %global_names = (); #reset
48 push @create, $self->header_comment unless ($no_comments);
49 $create[0] .= "\n\nBEGIN TRANSACTION" unless $no_txn;
51 for my $table ( $schema->get_tables ) {
52 push @create, $self->create_table($table, { no_comments => $no_comments,
53 sqlite_version => $sqlite_version,
54 add_drop_table => $add_drop_table,});
57 for my $view ( $schema->get_views ) {
58 push @create, $self->create_view($view, {
59 add_drop_view => $add_drop_table,
60 no_comments => $no_comments,
64 for my $trigger ( $schema->get_triggers ) {
65 push @create, $self->create_trigger($trigger, {
66 add_drop_trigger => $add_drop_table,
67 no_comments => $no_comments,
72 push @create, "COMMIT" unless $no_txn;
75 push @create, "COMMIT;\n" unless $no_txn;
76 return join(";\n\n", @create );
80 # -------------------------------------------------------------------
82 my ($name, $scope, $critical) = @_;
83 my $max_id_length = 30;
84 # $scope ||= \%global_names;
85 if ( my $prev = $scope->{ $name } ) {
86 my $name_orig = $name;
87 $name .= sprintf( "%02d", ++$prev );
88 substr($name, $max_id_length - 3) = "00"
89 if length( $name ) > $max_id_length;
91 # warn "The name '$name_orig' has been changed to ",
92 # "'$name' to make it unique.\n" if $WARN;
94 $scope->{ $name_orig }++;
101 method create_view(View $view, $options?) {
102 my $add_drop_view = $options->{add_drop_view};
104 my $view_name = $view->name;
105 # debug("PKG: Looking at view '${view_name}'\n");
107 # Header. Should this look like what mysqldump produces?
108 my $extra = $view->extra;
110 $create .= "--\n-- View: ${view_name}\n--\n" unless $options->{no_comments};
111 $create .= "DROP VIEW IF EXISTS $view_name;\n" if $add_drop_view;
113 $create .= " TEMPORARY" if exists($extra->{temporary}) && $extra->{temporary};
115 $create .= " IF NOT EXISTS" if exists($extra->{if_not_exists}) && $extra->{if_not_exists};
116 $create .= " ${view_name}";
118 if( my $sql = $view->sql ){
119 $create .= " AS\n ${sql}";
124 method create_table(Table $table, HashRef $options) {
125 my $table_name = $table->name;
126 my $no_comments = $options->{no_comments};
127 my $add_drop_table = $options->{add_drop_table};
128 my $sqlite_version = $options->{sqlite_version} || 0;
130 # debug("PKG: Looking at table '$table_name'\n");
132 my ( @index_defs, @constraint_defs );
133 my @fields = $table->get_fields or die "No fields in $table_name";
135 my $temp = $options->{temporary_table} ? 'TEMPORARY ' : '';
139 my $exists = ($sqlite_version >= 3.3) ? ' IF EXISTS' : '';
141 my ($comment, $create_table) = "";
142 $comment = "--\n-- Table: $table_name\n--\n" unless $no_comments;
143 if ($add_drop_table) {
144 push @create, $comment . qq[DROP TABLE$exists $table_name];
146 $create_table = $comment;
149 $create_table .= "CREATE ${temp}TABLE $table_name (\n";
154 if ( $table->comments and !$no_comments ){
155 $create_table .= "-- Comments: \n-- ";
156 $create_table .= join "\n-- ", $table->comments;
157 $create_table .= "\n--\n\n";
161 # How many fields in PK?
163 my $pk = $table->primary_key;
164 my @pk_fields = $pk ? $pk->fields : ();
169 my ( @field_defs, $pk_set );
170 for my $field ( @fields ) {
171 push @field_defs, $self->create_field($field);
175 scalar @pk_fields > 1
177 ( @pk_fields && !grep /INTEGER PRIMARY KEY/, @field_defs )
179 push @field_defs, 'PRIMARY KEY (' . join(', ', @pk_fields ) . ')';
185 my $idx_name_default = 'A';
186 for my $index ( $table->get_indices ) {
187 push @index_defs, $self->create_index($index);
193 my $c_name_default = 'A';
194 for my $c ( $table->get_constraints ) {
195 next unless $c->type eq UNIQUE;
196 push @constraint_defs, $self->create_constraint($c);
199 $create_table .= join(",\n", map { " $_" } @field_defs ) . "\n)";
201 return (@create, $create_table, @index_defs, @constraint_defs );
204 method create_field(Column $field, $options?) {
205 my $field_name = $field->name;
206 my $field_comments = $field->comments
207 ? "-- " . $field->comments . "\n "
210 my $field_def = $field_comments.$field_name;
213 my $size = $field->size;
214 my $data_type = $field->data_type;
215 $data_type = 'varchar' if lc $data_type eq 'set';
216 $data_type = 'blob' if lc $data_type eq 'bytea';
218 if ( lc $data_type =~ /(text|blob)/i ) {
222 # if ( $data_type =~ /timestamp/i ) {
223 # push @trigger_defs,
224 # "CREATE TRIGGER ts_${table_name} ".
225 # "after insert on $table_name\n".
227 # " update $table_name set $field_name=timestamp() ".
228 # "where id=new.id;\n".
235 # SQLite is generally typeless, but newer versions will
236 # make a field autoincrement if it is declared as (and
237 # *only* as) INTEGER PRIMARY KEY
239 my $pk = $field->table->primary_key;
240 my @pk_fields = $pk ? $pk->fields : ();
243 $field->is_primary_key &&
244 scalar @pk_fields == 1 &&
246 $data_type =~ /int(eger)?$/i
248 ( $data_type =~ /^number?$/i && $size !~ /,/ )
251 $data_type = 'INTEGER PRIMARY KEY';
256 $field_def .= sprintf " %s%s", $data_type,
257 ( !$field->is_auto_increment && $size ) ? "($size)" : '';
260 $field_def .= ' NOT NULL' unless $field->is_nullable;
262 # Default? XXX Need better quoting!
263 my $default = $field->default_value;
265 # if (defined $default) {
266 # SQL::Translator::Producer->_apply_default_value(
271 # 'now()' => 'now()',
272 # 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP',
279 method create_index(Index $index, $options?) {
280 my $name = $index->name;
281 $name = mk_name($name);
283 my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : '';
285 # strip any field size qualifiers as SQLite doesn't like these
286 my @fields = map { s/\(\d+\)$//; $_ } $index->fields;
287 (my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema
288 # warn "removing schema name from '" . $index->table->name . "' to make '$index_table_name'\n" if $WARN;
290 "CREATE ${type}INDEX $name ON " . $index_table_name .
291 ' (' . join( ', ', @fields ) . ')';
296 method create_constraint(Constraint $c, $options?) {
298 $name = mk_name($name);
299 my @fields = $c->fields;
300 (my $index_table_name = $c->table->name) =~ s/^.+?\.//; # table name may not specify schema
301 # warn "removing schema name from '" . $c->table->name . "' to make '$index_table_name'\n" if $WARN;
304 "CREATE UNIQUE INDEX $name ON " . $index_table_name .
305 ' (' . join( ', ', @fields ) . ')';
309 method create_trigger(Trigger $trigger, $options?) {
310 my $add_drop = $options->{add_drop_trigger};
314 my $trigger_name = $trigger->name;
315 my @events = $trigger->database_events;
316 for my $evt ( @events ) {
318 my $trig_name = $trigger_name;
320 $trig_name .= "_$evt";
322 # warn "Multiple database events supplied for trigger '$trigger_name', ",
323 # "creating trigger '$trig_name' for the '$evt' event.\n" if $WARN;
326 push @statements, "DROP TRIGGER IF EXISTS $trig_name" if $add_drop;
331 if (not ref $trigger->action) {
332 $action .= "BEGIN " . $trigger->action . " END";
335 $action = $trigger->action->{for_each} . " "
336 if $trigger->action->{for_each};
338 $action = $trigger->action->{when} . " "
339 if $trigger->action->{when};
341 my $steps = $trigger->action->{steps} || [];
344 $action .= $_ . "; " for (@$steps);
348 push @statements, sprintf (
349 'CREATE TRIGGER %s %s %s on %s %s',
351 $trigger->perform_action_when,
361 method alter_table(@) { } # Noop
363 method add_field(Column $field) {
364 return sprintf("ALTER TABLE %s ADD COLUMN %s",
365 $field->table->name, $self->create_field($field))
368 method alter_create_index(Index $index) {
369 # This might cause name collisions
370 return $self->create_index($index);
373 method alter_create_constraint(Constraint $constraint) {
374 return $self->create_constraint($constraint) if $constraint->type eq 'UNIQUE';
377 method alter_drop_constraint(@args) { $self->alter_drop_index(@args) }
379 method alter_drop_index(Constraint $constraint) {
380 return sprintf("DROP INDEX %s",
384 method batch_alter_table(Table $table, $diffs) {
385 # If we have any of the following
391 # we need to do the following <http://www.sqlite.org/faq.html#q11>
394 # CREATE TEMPORARY TABLE t1_backup(a,b);
395 # INSERT INTO t1_backup SELECT a,b FROM t1;
397 # CREATE TABLE t1(a,b);
398 # INSERT INTO t1 SELECT a,b FROM t1_backup;
399 # DROP TABLE t1_backup;
404 # If we have rename_field we do similarly.
406 my $table_name = $table->name;
407 my $renaming = $diffs->{rename_table} && @{$diffs->{rename_table}};
409 if ( @{$diffs->{rename_field}} == 0 &&
410 @{$diffs->{alter_field}} == 0 &&
411 @{$diffs->{drop_field}} == 0
413 # return join("\n", map {
415 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
416 map { my $sql = $meth->(ref $_ eq 'ARRAY' ? @$_ : $_); $sql ? ("$sql") : () } @{ $diffs->{$_} }
418 } grep { @{$diffs->{$_}} }
420 alter_drop_constraint
427 alter_create_constraint
432 my $old_table = $renaming ? $diffs->{rename_table}[0][0] : $table;
435 local $table->{name} = $table_name . '_temp_alter';
436 # We only want the table - dont care about indexes on tmp table
437 my ($table_sql) = $self->create_table($table, {no_comments => 1, temporary_table => 1});
438 push @sql,$table_sql;
441 push @sql, "INSERT INTO @{[$table_name]}_temp_alter SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$old_table]}",
442 "DROP TABLE @{[$old_table]}",
443 $self->create_table($table, { no_comments => 1 }),
444 "INSERT INTO @{[$table_name]} SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$table_name]}_temp_alter",
445 "DROP TABLE @{[$table_name]}_temp_alter";
448 # return join("", @sql, "");
451 method drop_table(Str $table) {
452 return "DROP TABLE $table";
455 method rename_table(Str $old_table, Str $new_table, $options?) {
456 my $qt = $options->{quote_table_names} || '';
458 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
462 # No-op. Just here to signify that we are a new style parser.
463 method preproces_schema(@) { }