1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
|
# 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::Sqlite;
use 5.10.1;
use Moo;
extends qw(Bugzilla::DB);
use Bugzilla::Constants;
use Bugzilla::Error;
use Bugzilla::Install::Util qw(install_string);
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)';
# For some reason, dropping the related FKs causes the index to
# disappear early, which causes all sorts of problems.
use constant INDEX_DROPS_REQUIRE_FK_DROPS => 0;
####################################
# 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_mod { $_[0] % $_[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 BUILDARGS {
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'},
};
return { dsn => $dsn, user => '', pass => '', attrs => $attrs };
}
sub on_dbi_connected {
my ($class, $dbh) = @_;
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) {
$dbh->do("PRAGMA $name = $value");
}
$dbh->sqlite_create_collation('bugzilla', \&_sqlite_collate_ci);
$dbh->sqlite_create_function('position', 2, \&_sqlite_position);
$dbh->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".
$dbh->sqlite_create_function('substring', 3, \&CORE::substr);
$dbh->sqlite_create_function('mod', 2, \&_sqlite_mod);
$dbh->sqlite_create_function('now', 0, \&_sqlite_now);
$dbh->sqlite_create_function('localtimestamp', 1, \&_sqlite_now);
$dbh->sqlite_create_function('floor', 1, \&POSIX::floor);
}
###############
# 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')";
}
###############
# bz_ methods #
###############
sub bz_setup_database {
my $self = shift;
$self->SUPER::bz_setup_database(@_);
# If we created TheSchwartz tables with COLLATE bugzilla (during the
# 4.1.x development series) re-create them without it.
my @tables = $self->bz_table_list();
my @ts_tables = grep { /^ts_/ } @tables;
my $drop_ok;
foreach my $table (@ts_tables) {
my $create_table =
$self->_bz_real_schema->_sqlite_create_table($table);
if ($create_table =~ /COLLATE bugzilla/) {
if (!$drop_ok) {
_sqlite_jobqueue_drop_message();
$drop_ok = 1;
}
$self->bz_drop_table($table);
$self->bz_add_table($table);
}
}
}
sub _sqlite_jobqueue_drop_message {
# This is not translated because this situation will only happen if
# you are updating from a 4.1.x development version of Bugzilla using
# SQLite, and we don't want to maintain this string in strings.txt.pl
# forever for just this one uncommon circumstance.
print <<END;
WARNING: We have to re-create all the database tables used by jobqueue.pl.
If there are any pending jobs in the database (that is, emails that
haven't been sent), they will be deleted.
END
unless (Bugzilla->installation_answers->{NO_PAUSE}) {
print install_string('enter_or_ctrl_c');
getc;
}
}
# XXX This needs to be implemented.
sub bz_explain { }
sub bz_table_list_real {
my $self = shift;
my @tables = $self->SUPER::bz_table_list_real(@_);
# SQLite includes a sqlite_sequence table in every database that isn't
# one of our real tables. We exclude any table that starts with sqlite_,
# just to be safe.
@tables = grep { $_ !~ /^sqlite_/ } @tables;
return @tables;
}
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>.
|