| # This Source Code Form is subject to the terms of the Mozilla Public |
| # License, v. 2.0. If a copy of the MPL was not distributed with this |
| # file, You can obtain one at http://mozilla.org/MPL/2.0/. |
| # |
| # This Source Code Form is "Incompatible With Secondary Licenses", as |
| # defined by the Mozilla Public License, v. 2.0. |
| |
| package Bugzilla::DB::Schema::Sqlite; |
| |
| use 5.10.1; |
| use strict; |
| use warnings; |
| |
| use parent qw(Bugzilla::DB::Schema); |
| |
| use Bugzilla::Error; |
| use Bugzilla::Util qw(generate_random_password); |
| |
| use Storable qw(dclone); |
| |
| use constant FK_ON_CREATE => 1; |
| |
| sub _initialize { |
| |
| my $self = shift; |
| |
| $self = $self->SUPER::_initialize(@_); |
| |
| $self->{db_specific} = { |
| BOOLEAN => 'integer', |
| FALSE => '0', |
| TRUE => '1', |
| |
| INT1 => 'integer', |
| INT2 => 'integer', |
| INT3 => 'integer', |
| INT4 => 'integer', |
| |
| SMALLSERIAL => 'SERIAL', |
| MEDIUMSERIAL => 'SERIAL', |
| INTSERIAL => 'SERIAL', |
| |
| TINYTEXT => 'text', |
| MEDIUMTEXT => 'text', |
| LONGTEXT => 'text', |
| |
| LONGBLOB => 'blob', |
| |
| DATETIME => 'DATETIME', |
| DATE => 'DATETIME', |
| }; |
| |
| $self->_adjust_schema; |
| |
| return $self; |
| |
| } |
| |
| ################################# |
| # General SQLite Schema Helpers # |
| ################################# |
| |
| sub _sqlite_create_table { |
| my ($self, $table) = @_; |
| return scalar Bugzilla->dbh->selectrow_array( |
| "SELECT sql FROM sqlite_master WHERE name = ? AND type = 'table'", |
| undef, $table); |
| } |
| |
| sub _sqlite_table_lines { |
| my $self = shift; |
| my $table_sql = $self->_sqlite_create_table(@_); |
| $table_sql =~ s/\n*\)$//s; |
| # The $ makes this work even if people some day add crazy stuff to their |
| # schema like multi-column foreign keys. |
| return split(/,\s*$/m, $table_sql); |
| } |
| |
| # This does most of the "heavy lifting" of the schema-altering functions. |
| sub _sqlite_alter_schema { |
| my ($self, $table, $create_table, $options) = @_; |
| |
| # $create_table is sometimes an array in the form that _sqlite_table_lines |
| # returns. |
| if (ref $create_table) { |
| $create_table = join(',', @$create_table) . "\n)"; |
| } |
| |
| my $dbh = Bugzilla->dbh; |
| |
| my $random = generate_random_password(5); |
| my $rename_to = "${table}_$random"; |
| |
| my @columns = $dbh->bz_table_columns_real($table); |
| push(@columns, $options->{extra_column}) if $options->{extra_column}; |
| if (my $exclude = $options->{exclude_column}) { |
| @columns = grep { $_ ne $exclude } @columns; |
| } |
| my @insert_cols = @columns; |
| my @select_cols = @columns; |
| if (my $rename = $options->{rename}) { |
| foreach my $from (keys %$rename) { |
| my $to = $rename->{$from}; |
| @insert_cols = map { $_ eq $from ? $to : $_ } @insert_cols; |
| } |
| } |
| |
| my $insert_str = join(',', @insert_cols); |
| my $select_str = join(',', @select_cols); |
| my $copy_sql = "INSERT INTO $table ($insert_str)" |
| . " SELECT $select_str FROM $rename_to"; |
| |
| # We have to turn FKs off before doing this. Otherwise, when we rename |
| # the table, all of the FKs in the other tables will be automatically |
| # updated to point to the renamed table. Note that PRAGMA foreign_keys |
| # can only be set outside of a transaction--otherwise it is a no-op. |
| if ($dbh->bz_in_transaction) { |
| die "can't alter the schema inside of a transaction"; |
| } |
| my @sql = ( |
| 'PRAGMA foreign_keys = OFF', |
| 'BEGIN EXCLUSIVE TRANSACTION', |
| @{ $options->{pre_sql} || [] }, |
| "ALTER TABLE $table RENAME TO $rename_to", |
| $create_table, |
| $copy_sql, |
| "DROP TABLE $rename_to", |
| 'COMMIT TRANSACTION', |
| 'PRAGMA foreign_keys = ON', |
| ); |
| } |
| |
| # For finding a particular column's definition in a CREATE TABLE statement. |
| sub _sqlite_column_regex { |
| my ($column) = @_; |
| # 1 = Comma at start |
| # 2 = Column name + Space |
| # 3 = Definition |
| # 4 = Ending comma |
| return qr/(^|,)(\s\Q$column\E\s+)(.*?)(,|$)/m; |
| } |
| |
| ############################# |
| # Schema Setup & Alteration # |
| ############################# |
| |
| sub get_create_database_sql { |
| # If we get here, it means there was some error creating the |
| # database file during bz_create_database in Bugzilla::DB, |
| # and we just want to display that error instead of doing |
| # anything else. |
| Bugzilla->dbh; |
| die "Reached an unreachable point"; |
| } |
| |
| sub _get_create_table_ddl { |
| my $self = shift; |
| my ($table) = @_; |
| my $ddl = $self->SUPER::_get_create_table_ddl(@_); |
| |
| # TheSchwartz uses its own driver to access its tables, meaning |
| # that it doesn't understand "COLLATE bugzilla" and in fact |
| # SQLite throws an error when TheSchwartz tries to access its |
| # own tables, if COLLATE bugzilla is on them. We don't have |
| # to fix this elsewhere currently, because we only create |
| # TheSchwartz's tables, we never modify them. |
| if ($table =~ /^ts_/) { |
| $ddl =~ s/ COLLATE bugzilla//g; |
| } |
| return $ddl; |
| } |
| |
| sub get_type_ddl { |
| my $self = shift; |
| my $def = dclone($_[0]); |
| |
| my $ddl = $self->SUPER::get_type_ddl(@_); |
| if ($def->{PRIMARYKEY} and $def->{TYPE} =~ /SERIAL/i) { |
| $ddl =~ s/\bSERIAL\b/integer/; |
| $ddl =~ s/\bPRIMARY KEY\b/PRIMARY KEY AUTOINCREMENT/; |
| } |
| if ($def->{TYPE} =~ /text/i or $def->{TYPE} =~ /char/i) { |
| $ddl .= " COLLATE bugzilla"; |
| } |
| # Don't collate DATETIME fields. |
| if ($def->{TYPE} eq 'DATETIME') { |
| $ddl =~ s/\bDATETIME\b/text COLLATE BINARY/; |
| } |
| return $ddl; |
| } |
| |
| sub get_alter_column_ddl { |
| my $self = shift; |
| my ($table, $column, $new_def, $set_nulls_to) = @_; |
| my $dbh = Bugzilla->dbh; |
| |
| my $table_sql = $self->_sqlite_create_table($table); |
| my $new_ddl = $self->get_type_ddl($new_def); |
| # When we do ADD COLUMN, columns can show up all on one line separated |
| # by commas, so we have to account for that. |
| my $column_regex = _sqlite_column_regex($column); |
| $table_sql =~ s/$column_regex/$1$2$new_ddl$4/ |
| || die "couldn't find $column in $table:\n$table_sql"; |
| my @pre_sql = $self->_set_nulls_sql(@_); |
| return $self->_sqlite_alter_schema($table, $table_sql, |
| { pre_sql => \@pre_sql }); |
| } |
| |
| sub get_add_column_ddl { |
| my $self = shift; |
| my ($table, $column, $definition, $init_value) = @_; |
| # SQLite can use the normal ADD COLUMN when: |
| # * The column isn't a PK |
| if ($definition->{PRIMARYKEY}) { |
| if ($definition->{NOTNULL} and $definition->{TYPE} !~ /SERIAL/i) { |
| die "You can only add new SERIAL type PKs with SQLite"; |
| } |
| my $table_sql = $self->_sqlite_new_column_sql(@_); |
| # This works because _sqlite_alter_schema will exclude the new column |
| # in its INSERT ... SELECT statement, meaning that when the "new" |
| # table is populated, it will have AUTOINCREMENT values generated |
| # for it. |
| return $self->_sqlite_alter_schema($table, $table_sql); |
| } |
| # * The column has a default one way or another. Either it |
| # defaults to NULL (it lacks NOT NULL) or it has a DEFAULT |
| # clause. Since we also require this when doing bz_add_column (in |
| # the way of forcing an init_value for NOT NULL columns with no |
| # default), we first set the init_value as the default and then |
| # alter the column. |
| if ($definition->{NOTNULL} and !defined $definition->{DEFAULT}) { |
| my %with_default = %$definition; |
| $with_default{DEFAULT} = $init_value; |
| my @pre_sql = |
| $self->SUPER::get_add_column_ddl($table, $column, \%with_default); |
| my $table_sql = $self->_sqlite_new_column_sql(@_); |
| return $self->_sqlite_alter_schema($table, $table_sql, |
| { pre_sql => \@pre_sql, extra_column => $column }); |
| } |
| |
| return $self->SUPER::get_add_column_ddl(@_); |
| } |
| |
| sub _sqlite_new_column_sql { |
| my ($self, $table, $column, $def) = @_; |
| my $table_sql = $self->_sqlite_create_table($table); |
| my $new_ddl = $self->get_type_ddl($def); |
| my $new_line = "\t$column\t$new_ddl"; |
| $table_sql =~ s/^(CREATE TABLE \w+ \()/$1\n$new_line,/s |
| || die "Can't find start of CREATE TABLE:\n$table_sql"; |
| return $table_sql; |
| } |
| |
| sub get_drop_column_ddl { |
| my ($self, $table, $column) = @_; |
| my $table_sql = $self->_sqlite_create_table($table); |
| my $column_regex = _sqlite_column_regex($column); |
| $table_sql =~ s/$column_regex/$1/ |
| || die "Can't find column $column: $table_sql"; |
| # Make sure we don't end up with a comma at the end of the definition. |
| $table_sql =~ s/,\s+\)$/\n)/s; |
| return $self->_sqlite_alter_schema($table, $table_sql, |
| { exclude_column => $column }); |
| } |
| |
| sub get_rename_column_ddl { |
| my ($self, $table, $old_name, $new_name) = @_; |
| my $table_sql = $self->_sqlite_create_table($table); |
| my $column_regex = _sqlite_column_regex($old_name); |
| $table_sql =~ s/$column_regex/$1\t$new_name\t$3$4/ |
| || die "Can't find $old_name: $table_sql"; |
| my %rename = ($old_name => $new_name); |
| return $self->_sqlite_alter_schema($table, $table_sql, |
| { rename => \%rename }); |
| } |
| |
| ################ |
| # Foreign Keys # |
| ################ |
| |
| sub get_add_fks_sql { |
| my ($self, $table, $column_fks) = @_; |
| my @clauses = $self->_sqlite_table_lines($table); |
| my @add = $self->_column_fks_to_ddl($table, $column_fks); |
| push(@clauses, @add); |
| return $self->_sqlite_alter_schema($table, \@clauses); |
| } |
| |
| sub get_drop_fk_sql { |
| my ($self, $table, $column, $references) = @_; |
| my @clauses = $self->_sqlite_table_lines($table); |
| my $fk_name = $self->_get_fk_name($table, $column, $references); |
| |
| my $line_re = qr/^\s+CONSTRAINT $fk_name /s; |
| grep { $line_re } @clauses |
| or die "Can't find $fk_name: " . join(',', @clauses); |
| @clauses = grep { $_ !~ $line_re } @clauses; |
| |
| return $self->_sqlite_alter_schema($table, \@clauses); |
| } |
| |
| |
| 1; |
| |
| =head1 B<Methods in need of POD> |
| |
| =over |
| |
| =item get_rename_column_ddl |
| |
| =item get_add_fks_sql |
| |
| =item get_drop_fk_sql |
| |
| =item get_create_database_sql |
| |
| =item get_alter_column_ddl |
| |
| =item get_add_column_ddl |
| |
| =item get_type_ddl |
| |
| =item get_drop_column_ddl |
| |
| =back |
