diff options
| -rw-r--r-- | Bugzilla/Constants.pm | 9 | ||||
| -rw-r--r-- | Bugzilla/DB.pm | 14 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema.pm | 35 | ||||
| -rw-r--r-- | Bugzilla/DB/Schema/Sqlite.pm | 86 | ||||
| -rw-r--r-- | Bugzilla/DB/Sqlite.pm | 255 | ||||
| -rw-r--r-- | Bugzilla/Install/Filesystem.pm | 3 | ||||
| -rw-r--r-- | Bugzilla/Search.pm | 2 | ||||
| -rw-r--r-- | template/en/default/setup/strings.txt.pl | 6 |
8 files changed, 389 insertions, 21 deletions
diff --git a/Bugzilla/Constants.pm b/Bugzilla/Constants.pm index f4ed56608..4ca04c478 100644 --- a/Bugzilla/Constants.pm +++ b/Bugzilla/Constants.pm @@ -505,6 +505,15 @@ use constant DB_MODULE => { version => '1.19', }, name => 'Oracle'}, + # SQLite 3.6.22 fixes a WHERE clause problem that may affect us. + sqlite => {db => 'Bugzilla::DB::Sqlite', db_version => '3.6.22', + dbd => { + package => 'DBD-SQLite', + module => 'DBD::SQLite', + # 1.29 is the version that contains 3.6.22. + version => '1.29', + }, + name => 'SQLite'}, }; # True if we're on Win32. diff --git a/Bugzilla/DB.pm b/Bugzilla/DB.pm index 97099b54c..ae9fffd3a 100644 --- a/Bugzilla/DB.pm +++ b/Bugzilla/DB.pm @@ -232,7 +232,7 @@ EOT sub bz_create_database { my $dbh; # See if we can connect to the actual Bugzilla database. - my $conn_success = eval { $dbh = connect_main(); }; + my $conn_success = $dbh = connect_main(); my $db_name = Bugzilla->localconfig->{db_name}; if (!$conn_success) { @@ -767,10 +767,11 @@ sub bz_add_table { # initial table creation, because column names have changed # over history and it's impossible to keep track of that info # in ABSTRACT_SCHEMA. - if (exists $fields{$col}->{REFERENCES}) { - $fields{$col}->{REFERENCES}->{created} = 0; - } + next unless exists $fields{$col}->{REFERENCES}; + $fields{$col}->{REFERENCES}->{created} = + $self->_bz_real_schema->FK_ON_CREATE; } + $self->_bz_real_schema->add_table($name, $table_def); $self->_bz_store_real_schema; } @@ -1179,7 +1180,10 @@ sub bz_start_transaction { # what we need in Bugzilla to be safe, for what we do. # Different DBs have different defaults for their isolation # level, so we just set it here manually. - $self->do('SET TRANSACTION ISOLATION LEVEL ' . $self->ISOLATION_LEVEL); + if ($self->ISOLATION_LEVEL) { + $self->do('SET TRANSACTION ISOLATION LEVEL ' + . $self->ISOLATION_LEVEL); + } $self->{private_bz_transaction_count} = 1; } } diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm index a64aa04a2..cfa1608f9 100644 --- a/Bugzilla/DB/Schema.pm +++ b/Bugzilla/DB/Schema.pm @@ -52,6 +52,12 @@ use Storable qw(dclone freeze thaw); # New SCHEMA_VERSIONs (2+) use this use Data::Dumper; +# Whether or not this database can safely create FKs when doing a +# CREATE TABLE statement. This is false for most DBs, because they +# prevent you from creating FKs on tables and columns that don't +# yet exist. (However, in SQLite it's 1 because SQLite allows that.) +use constant FK_ON_CREATE => 0; + =head1 NAME Bugzilla::DB::Schema - Abstract database schema for Bugzilla @@ -1781,8 +1787,9 @@ C<ALTER TABLE> SQL statement # DEFAULT attribute must appear before any column constraints # (e.g., NOT NULL), for Oracle $type_ddl .= " DEFAULT $default" if (defined($default)); - $type_ddl .= " NOT NULL" if ($finfo->{NOTNULL}); + # PRIMARY KEY must appear before NOT NULL for SQLite. $type_ddl .= " PRIMARY KEY" if ($finfo->{PRIMARYKEY}); + $type_ddl .= " NOT NULL" if ($finfo->{NOTNULL}); return($type_ddl); @@ -2016,7 +2023,7 @@ sub get_table_ddl { return @ddl; } #eosub--get_table_ddl -#-------------------------------------------------------------------------- + sub _get_create_table_ddl { =item C<_get_create_table_ddl> @@ -2032,25 +2039,27 @@ sub _get_create_table_ddl { my $thash = $self->{schema}{$table}; die "Table $table does not exist in the database schema." - unless (ref($thash)); - - my $create_table = "CREATE TABLE $table \(\n"; + unless ref $thash; + my (@col_lines, @fk_lines); my @fields = @{ $thash->{FIELDS} }; while (@fields) { my $field = shift(@fields); my $finfo = shift(@fields); - $create_table .= "\t$field\t" . $self->get_type_ddl($finfo); - $create_table .= "," if (@fields); - $create_table .= "\n"; + push(@col_lines, "\t$field\t" . $self->get_type_ddl($finfo)); + if ($self->FK_ON_CREATE and $finfo->{REFERENCES}) { + my $fk = $finfo->{REFERENCES}; + my $fk_ddl = "\t" . $self->get_fk_ddl($table, $field, $fk); + push(@fk_lines, $fk_ddl); + } } + + my $sql = "CREATE TABLE $table (\n" . join(",\n", @col_lines, @fk_lines) + . "\n)"; + return $sql - $create_table .= "\)"; - - return($create_table) +} -} #eosub--_get_create_table_ddl -#-------------------------------------------------------------------------- sub _get_create_index_ddl { =item C<_get_create_index_ddl> diff --git a/Bugzilla/DB/Schema/Sqlite.pm b/Bugzilla/DB/Schema/Sqlite.pm new file mode 100644 index 000000000..8f8af3803 --- /dev/null +++ b/Bugzilla/DB/Schema/Sqlite.pm @@ -0,0 +1,86 @@ +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Everything Solved, Inc. +# Portions created by the Initial Developer are Copyright (C) 2010 the +# Initial Developer. All Rights Reserved. +# +# Contributor(s): +# Max Kanat-Alexander <mkanat@bugzilla.org> + +use strict; +package Bugzilla::DB::Schema::Sqlite; +use base qw(Bugzilla::DB::Schema); + +use Bugzilla::Error; + +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', + }; + + $self->_adjust_schema; + + return $self; + +} + +sub get_type_ddl { + my $self = shift; + my $def = dclone($_[0]); + + my $ddl = $self->SUPER::get_type_ddl(@_); + if ($def->{PRIMARYKEY} and $def->{TYPE} eq 'SERIAL') { + $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; +} + +1; diff --git a/Bugzilla/DB/Sqlite.pm b/Bugzilla/DB/Sqlite.pm new file mode 100644 index 000000000..c518486ae --- /dev/null +++ b/Bugzilla/DB/Sqlite.pm @@ -0,0 +1,255 @@ +# -*- Mode: perl; indent-tabs-mode: nil -*- +# +# The contents of this file are subject to the Mozilla Public +# License Version 1.1 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.mozilla.org/MPL/ +# +# Software distributed under the License is distributed on an "AS +# IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or +# implied. See the License for the specific language governing +# rights and limitations under the License. +# +# The Original Code is the Bugzilla Bug Tracking System. +# +# The Initial Developer of the Original Code is Everything Solved, Inc. +# Portions created by the Initial Developer are Copyright (C) 2010 the +# Initial Developer. All Rights Reserved. +# +# Contributor(s): +# Max Kanat-Alexander <mkanat@bugzilla.org> + +use strict; +package Bugzilla::DB::Sqlite; +use base qw(Bugzilla::DB); + +use Bugzilla::Constants; +use Bugzilla::Error; + +use DateTime; +use POSIX (); + +# SQLite only supports the SERIALIZABLE and READ UNCOMMITTED isolation +# levels. SERIALIZABLE is used by default and SET TRANSACTION ISOLATION +# LEVEL is not implemented. +use constant ISOLATION_LEVEL => undef; + +# Since we're literally using Perl's regexes, we can use something +# simpler and more efficient than what Bugzilla::DB uses. +use constant WORD_START => '(?:^|\W)'; +use constant WORD_END => '(?:$|\W)'; + +#################################### +# Functions Added To SQLite Itself # +#################################### + +# A case-insensitive, Unicode collation for SQLite. This allows us to +# make all comparisons and sorts case-insensitive (though unfortunately +# not accent-insensitive). +sub _sqlite_collate_ci { lc($_[0]) cmp lc($_[1]) } + +sub _sqlite_now { + my $now = DateTime->now(time_zone => Bugzilla->local_timezone); + return $now->ymd . ' ' . $now->hms; +} + +# SQL's POSITION starts its values from 1 instead of 0 (so we add 1). +sub _sqlite_position { + my ($text, $fragment) = @_; + if (!defined $text or !defined $fragment) { + return undef; + } + my $pos = index $text, $fragment; + return $pos + 1; +} + +sub _sqlite_position_ci { + my ($text, $fragment) = @_; + return _sqlite_position(lc($text), lc($fragment)); +} + +############### +# Constructor # +############### + +sub new { + my ($class, $params) = @_; + my $db_name = $params->{db_name}; + + # Let people specify paths intead of data/ for the DB. + if ($db_name and $db_name !~ m{[\\/]}) { + # When the DB is first created, there's a chance that the + # data directory doesn't exist at all, because the Install::Filesystem + # code happens after DB creation. So we create the directory ourselves + # if it doesn't exist. + my $datadir = bz_locations()->{datadir}; + if (!-d $datadir) { + mkdir $datadir or warn "$datadir: $!"; + } + if (!-d "$datadir/db/") { + mkdir "$datadir/db/" or warn "$datadir/db: $!"; + } + $db_name = bz_locations()->{datadir} . "/db/$db_name"; + } + + # construct the DSN from the parameters we got + my $dsn = "dbi:SQLite:dbname=$db_name"; + + my $attrs = { + # XXX Should we just enforce this to be always on? + sqlite_unicode => Bugzilla->params->{'utf8'}, + }; + + my $self = $class->db_new({ dsn => $dsn, user => '', + pass => '', attrs => $attrs }); + # Needed by TheSchwartz + $self->{private_bz_dsn} = $dsn; + + my %pragmas = ( + # Make sure that the sqlite file doesn't grow without bound. + auto_vacuum => 1, + encoding => "'UTF-8'", + foreign_keys => 'ON', + # We want the latest file format. + legacy_file_format => 'OFF', + # This guarantees that we get column names like "foo" + # instead of "table.foo" in selectrow_hashref. + short_column_names => 'ON', + # The write-ahead log mode in SQLite 3.7 gets us better concurrency, + # but breaks backwards-compatibility with older versions of + # SQLite. (Which is important because people may also want to use + # command-line clients to access and back up their DB.) If you need + # better concurrency and don't need 3.6 compatibility, then you can + # uncomment this line. + #journal_mode => "'WAL'", + ); + + while (my ($name, $value) = each %pragmas) { + $self->do("PRAGMA $name = $value"); + } + + $self->sqlite_create_collation('bugzilla', \&_sqlite_collate_ci); + $self->sqlite_create_function('position', 2, \&_sqlite_position); + $self->sqlite_create_function('iposition', 2, \&_sqlite_position_ci); + # SQLite has a "substr" function, but other DBs call it "SUBSTRING" + # so that's what we use, and I don't know of any way in SQLite to + # alias the SQL "substr" function to be called "SUBSTRING". + $self->sqlite_create_function('substring', 3, \&CORE::substr); + $self->sqlite_create_function('now', 0, \&_sqlite_now); + $self->sqlite_create_function('localtimestamp', 1, \&_sqlite_now); + $self->sqlite_create_function('floor', 1, \&POSIX::floor); + + bless ($self, $class); + return $self; +} + +############### +# SQL Methods # +############### + +sub sql_position { + my ($self, $fragment, $text) = @_; + return "POSITION($text, $fragment)"; +} + +sub sql_iposition { + my ($self, $fragment, $text) = @_; + return "IPOSITION($text, $fragment)"; +} + +# SQLite does not have to GROUP BY the optional columns. +sub sql_group_by { + my ($self, $needed_columns, $optional_columns) = @_; + my $expression = "GROUP BY $needed_columns"; + return $expression; +} + +# XXX SQLite does not support sorting a GROUP_CONCAT, so $sort is unimplemented. +sub sql_group_concat { + my ($self, $column, $separator, $sort) = @_; + $separator = $self->quote(', ') if !defined $separator; + # In SQLite, a GROUP_CONCAT call with a DISTINCT argument can't + # specify its separator, and has to accept the default of ",". + if ($column =~ /^DISTINCT/) { + return "GROUP_CONCAT($column)"; + } + return "GROUP_CONCAT($column, $separator)"; +} + +sub sql_istring { + my ($self, $string) = @_; + return $string; +} + +sub sql_regexp { + my ($self, $expr, $pattern, $nocheck, $real_pattern) = @_; + $real_pattern ||= $pattern; + + $self->bz_check_regexp($real_pattern) if !$nocheck; + + return "$expr REGEXP $pattern"; +} + +sub sql_not_regexp { + my $self = shift; + my $re_expression = $self->sql_regexp(@_); + return "NOT($re_expression)"; +} + +sub sql_limit { + my ($self, $limit, $offset) = @_; + + if (defined($offset)) { + return "LIMIT $limit OFFSET $offset"; + } else { + return "LIMIT $limit"; + } +} + +sub sql_from_days { + my ($self, $days) = @_; + return "DATETIME($days)"; +} + +sub sql_to_days { + my ($self, $date) = @_; + return "JULIANDAY($date)"; +} + +sub sql_date_format { + my ($self, $date, $format) = @_; + $format = "%Y.%m.%d %H:%M:%s" if !$format; + $format =~ s/\%i/\%M/g; + return "STRFTIME(" . $self->quote($format) . ", $date)"; +} + +sub sql_date_math { + my ($self, $date, $operator, $interval, $units) = @_; + # We do the || thing (concatenation) so that placeholders work properly. + return "DATETIME($date, '$operator' || $interval || ' $units')"; +} + +sub sql_string_until { + my ($self, $string, $substring) = @_; + my $position = $self->sql_position($substring, $string); + return "SUBSTR($string, 1, $position - 1)" +} + +# XXX This needs to be implemented. +sub bz_explain { } + +1; + +__END__ + +=head1 NAME + +Bugzilla::DB::Sqlite - Bugzilla database compatibility layer for SQLite + +=head1 DESCRIPTION + +This module overrides methods of the Bugzilla::DB module with a +SQLite-specific implementation. It is instantiated by the Bugzilla::DB module +and should never be used directly. + +For interface details see L<Bugzilla::DB> and L<DBI>.
\ No newline at end of file diff --git a/Bugzilla/Install/Filesystem.pm b/Bugzilla/Install/Filesystem.pm index 378a32f63..42f292b6d 100644 --- a/Bugzilla/Install/Filesystem.pm +++ b/Bugzilla/Install/Filesystem.pm @@ -199,6 +199,8 @@ sub FILESYSTEM { dirs => DIR_CGI_WRITE | DIR_ALSO_WS_SERVE }, graphs => { files => WS_SERVE, dirs => DIR_CGI_WRITE | DIR_ALSO_WS_SERVE }, + "$datadir/db" => { files => CGI_WRITE, + dirs => DIR_CGI_WRITE }, # Readable directories "$datadir/mining" => { files => CGI_READ, @@ -265,6 +267,7 @@ sub FILESYSTEM { "$datadir/extensions" => DIR_CGI_READ, $extensionsdir => DIR_CGI_READ, # Directories that cgi scripts can write to. + "$datadir/db" => DIR_CGI_WRITE, $attachdir => DIR_CGI_WRITE, graphs => DIR_CGI_WRITE | DIR_ALSO_WS_SERVE, $webdotdir => DIR_CGI_WRITE | DIR_ALSO_WS_SERVE, diff --git a/Bugzilla/Search.pm b/Bugzilla/Search.pm index 850a63457..bcbe3a2a5 100644 --- a/Bugzilla/Search.pm +++ b/Bugzilla/Search.pm @@ -457,7 +457,7 @@ use constant COLUMN_JOINS => { 'flagtypes.name' => { as => 'map_flags', table => 'flags', - extra => ['attach_id IS NULL'], + extra => ['map_flags.attach_id IS NULL'], then_to => { as => 'map_flagtypes', table => 'flagtypes', diff --git a/template/en/default/setup/strings.txt.pl b/template/en/default/setup/strings.txt.pl index c786ef36b..b7c907346 100644 --- a/template/en/default/setup/strings.txt.pl +++ b/template/en/default/setup/strings.txt.pl @@ -171,8 +171,10 @@ END localconfig_db_host => <<'END', The DNS name or IP address of the host that the database server runs on. END - localconfig_db_name => -"The name of the database. For Oracle, this is the database's SID.", + localconfig_db_name => <<'END', +The name of the database. For Oracle, this is the database's SID. For +SQLite, this is a name (or path) for the DB file. +END localconfig_db_pass => <<'END', Enter your database password here. It's normally advisable to specify a password for your bugzilla database user. |