sub dbh_new {
my ($self) = @_;
my $ro = ref($self) eq 'PublicInbox::Over';
- my $dbh = DBI->connect("dbi:SQLite:dbname=$self->{filename}",'','', {
+ my $f = $self->{filename};
+ if (!$ro && !-f $f) { # SQLite defaults mode to 0644, we want 0666
+ open my $fh, '+>>', $f or die "failed to open $f: $!";
+ }
+ my $dbh = DBI->connect("dbi:SQLite:dbname=$f",'','', {
AutoCommit => 1,
RaiseError => 1,
PrintError => 0,
sub query_xover {
my ($self, $beg, $end) = @_;
do_get($self, <<'', {}, $beg, $end);
-SELECT * FROM over WHERE num >= ? AND num <= ?
+SELECT num,ts,ds,ddd FROM over WHERE num >= ? AND num <= ?
ORDER BY num ASC
}
SELECT tid,sid FROM over WHERE num = ? LIMIT 1
defined $tid or return nothing; # $sid may be undef
-
- $prev ||= 0;
- my $cond = 'FROM over WHERE (tid = ? OR sid = ?) AND num > ?';
- my $msgs = do_get($self, <<"", {}, $tid, $sid, $prev);
-SELECT * $cond ORDER BY num ASC
+ my $sort_col = 'ds';
+ $num = 0;
+ if ($prev) {
+ $num = $prev->{num} || 0;
+ $sort_col = 'num';
+ }
+ my $cond = '(tid = ? OR sid = ?) AND num > ?';
+ my $msgs = do_get($self, <<"", {}, $tid, $sid, $num);
+SELECT num,ts,ds,ddd FROM over WHERE $cond ORDER BY $sort_col ASC
return $msgs unless wantarray;
- my $nr = $dbh->selectrow_array(<<"", undef, $tid, $sid, $prev);
-SELECT COUNT(num) $cond
+ my $nr = $dbh->selectrow_array(<<"", undef, $tid, $sid, $num);
+SELECT COUNT(num) FROM over WHERE $cond
($nr, $msgs);
}
}
}
my $msgs = do_get($self, <<"", $opts, @v);
-SELECT * FROM over WHERE $s
+SELECT ts,ds,ddd FROM over WHERE $s
return $msgs unless wantarray;
my ($self, $num) = @_;
my $dbh = $self->connect;
my $smsg = $dbh->selectrow_hashref(<<'', undef, $num);
-SELECT * from OVER where num = ? LIMIT 1
+SELECT num,ds,ts,ddd FROM over WHERE num = ? LIMIT 1
return load_from_row($smsg) if $smsg;
undef;