summaryrefslogtreecommitdiffstats
path: root/Bugzilla
diff options
context:
space:
mode:
authorMax Kanat-Alexander <mkanat@bugzilla.org>2010-10-27 09:56:15 +0200
committerMax Kanat-Alexander <mkanat@bugzilla.org>2010-10-27 09:56:15 +0200
commitdf0f3430e1592503dd0ca6a8dd4f2da1da67eb5c (patch)
tree630556e022313ab770aa883db7fef9fd5ff2d3ec /Bugzilla
parent3cefbefe84dca3ae78449aefc80c1b90eaf22395 (diff)
downloadbugzilla-df0f3430e1592503dd0ca6a8dd4f2da1da67eb5c.tar.gz
bugzilla-df0f3430e1592503dd0ca6a8dd4f2da1da67eb5c.tar.xz
Bug 337776: Basic SQLite Support for Bugzilla
r=LpSolit, a=mkanat
Diffstat (limited to 'Bugzilla')
-rw-r--r--Bugzilla/Constants.pm9
-rw-r--r--Bugzilla/DB.pm14
-rw-r--r--Bugzilla/DB/Schema.pm35
-rw-r--r--Bugzilla/DB/Schema/Sqlite.pm86
-rw-r--r--Bugzilla/DB/Sqlite.pm255
-rw-r--r--Bugzilla/Install/Filesystem.pm3
-rw-r--r--Bugzilla/Search.pm2
7 files changed, 385 insertions, 19 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',