[PATCH 0/2] sharedkv: cleanup + bugfixes

[PATCH 0/2] sharedkv: cleanup + bugfixes

[Eric Wong]

Well, working on another "canary" project helped me notice some
faults for this one.

Eric Wong (2):
  sharedkv: remove unused subs
  sharedkv: avoid ambiguity for numeric-like string keys

 lib/PublicInbox/SharedKV.pm | 52 ++++++++++++++++---------------------
 t/shared_kv.t               | 16 ++++--------
 2 files changed, 28 insertions(+), 40 deletions(-)

[PATCH 1/2] sharedkv: remove unused subs

[Eric Wong]

Some features didn't get used, and they're just getting in the
way of upcoming bugfixes.
---
 lib/PublicInbox/SharedKV.pm | 22 ----------------------
 t/shared_kv.t               | 13 ++-----------
 2 files changed, 2 insertions(+), 33 deletions(-)

diff --git a/lib/PublicInbox/SharedKV.pm b/lib/PublicInbox/SharedKV.pm
index 95a3cb14..d49a39c1 100644
--- a/lib/PublicInbox/SharedKV.pm
+++ b/lib/PublicInbox/SharedKV.pm
@@ -56,12 +56,6 @@ sub new {
 	$self;
 }
 
-sub index_values {
-	my ($self) = @_;
-	my $lock = $self->lock_for_scope_fast;
-	$self->dbh($lock)->do('CREATE INDEX IF NOT EXISTS idx_v ON kv (v)');
-}
-
 sub set_maybe {
 	my ($self, $key, $val, $lock) = @_;
 	$lock //= $self->lock_for_scope_fast;
@@ -97,22 +91,6 @@ sub keys {
 	map { $_->[0] } @{$self->dbh->selectall_arrayref($sql, undef, @pfx)};
 }
 
-sub delete_by_val {
-	my ($self, $val, $lock) = @_;
-	$lock //= $self->lock_for_scope_fast;
-	$self->{dbh}->prepare_cached(<<'')->execute($val) + 0;
-DELETE FROM kv WHERE v = ?
-
-}
-
-sub replace_values {
-	my ($self, $oldval, $newval, $lock) = @_;
-	$lock //= $self->lock_for_scope_fast;
-	$self->{dbh}->prepare_cached(<<'')->execute($newval, $oldval) + 0;
-UPDATE kv SET v = ? WHERE v = ?
-
-}
-
 sub set {
 	my ($self, $key, $val) = @_;
 	if (defined $val) {
diff --git a/t/shared_kv.t b/t/shared_kv.t
index 251b7f39..8b4f9c29 100644
--- a/t/shared_kv.t
+++ b/t/shared_kv.t
@@ -1,5 +1,5 @@
 #!perl -w
-# Copyright (C) 2020-2021 all contributors <meta@public-inbox.org>
+# Copyright (C) all contributors <meta@public-inbox.org>
 # License: AGPL-3.0+ <https://www.gnu.org/licenses/agpl-3.0.txt>
 use strict;
 use v5.10.1;
@@ -26,10 +26,6 @@ is($skv->get($dead), $cafe, 'get after xchg');
 is($skv->xchg($dead, undef), $cafe, 'xchg to undef');
 is($skv->get($dead), undef, 'get after xchg to undef');
 is($skv->get($cafe), $dead, 'get after set_maybe');
-ok($skv->index_values, 'index_values works');
-is($skv->replace_values($dead, $cafe), 1, 'replaced one by value');
-is($skv->get($cafe), $cafe, 'value updated');
-is($skv->replace_values($dead, $cafe), 0, 'replaced none by value');
 is($skv->xchg($dead, $cafe), undef, 'xchg from undef');
 is($skv->count, 2, 'count works');
 
@@ -39,14 +35,9 @@ while (my ($k, $v) = $sth->fetchrow_array) {
 	$seen{$k} = $v;
 }
 is($seen{$dead}, $cafe, '$dead has expected value');
-is($seen{$cafe}, $cafe, '$cafe has expected value');
+is($seen{$cafe}, $dead, '$cafe has expected value');
 is(scalar keys %seen, 2, 'iterated through all');
 
-is($skv->replace_values($cafe, $dead), 2, 'replaced 2 by value');
-is($skv->delete_by_val('bogus'), 0, 'delete_by_val misses');
-is($skv->delete_by_val($dead), 2, 'delete_by_val hits');
-is($skv->delete_by_val($dead), 0, 'delete_by_val misses again');
-
 undef $skv;
 ok(!-d $skv_tmpdir, 'temporary dir gone');
 $skv = PublicInbox::SharedKV->new("$tmpdir/dir", 'base');

[PATCH 2/2] sharedkv: avoid ambiguity for numeric-like string keys

[Eric Wong]

While we only store URLs and binary SHA-1/SHA-256 values in skv
at the moment, we may store potentially ambiguous keys/values in
the future.  It's possible to store "02" and have it treated as
`2' unless explicitly binding parameters as SQL_BLOB.  This
behavior was independent of the sqlite_unicode parameter as
evidenced by the new tests.

I only noticed this bug while hacking on another project using
DBD::SQLite, and not while hacking on public-inbox itself.
---
 lib/PublicInbox/SharedKV.pm | 30 +++++++++++++++++++++++-------
 t/shared_kv.t               |  3 +++
 2 files changed, 26 insertions(+), 7 deletions(-)

diff --git a/lib/PublicInbox/SharedKV.pm b/lib/PublicInbox/SharedKV.pm
index d49a39c1..90ccf2b4 100644
--- a/lib/PublicInbox/SharedKV.pm
+++ b/lib/PublicInbox/SharedKV.pm
@@ -9,7 +9,7 @@ use strict;
 use v5.10.1;
 use parent qw(PublicInbox::Lock);
 use File::Temp qw(tempdir);
-use DBI ();
+use DBI qw(:sql_types); # SQL_BLOB
 use PublicInbox::Spawn;
 use File::Path qw(rmtree make_path);
 
@@ -59,9 +59,12 @@ sub new {
 sub set_maybe {
 	my ($self, $key, $val, $lock) = @_;
 	$lock //= $self->lock_for_scope_fast;
-	my $e = $self->{dbh}->prepare_cached(<<'')->execute($key, $val);
+	my $sth = $self->{dbh}->prepare_cached(<<'');
 INSERT OR IGNORE INTO kv (k,v) VALUES (?, ?)
 
+	$sth->bind_param(1, $key, SQL_BLOB);
+	$sth->bind_param(2, $val, SQL_BLOB);
+	my $e = $sth->execute;
 	$e == 0 ? undef : $e;
 }
 
@@ -88,20 +91,30 @@ sub keys {
 	} else {
 		@pfx = (); # [0] may've been undef
 	}
-	map { $_->[0] } @{$self->dbh->selectall_arrayref($sql, undef, @pfx)};
+	my $sth = $self->dbh->prepare($sql);
+	if (@pfx) {
+		$sth->bind_param(1, $pfx[0], SQL_BLOB);
+		$sth->bind_param(2, $pfx[1]);
+	}
+	$sth->execute;
+	map { $_->[0] } @{$sth->fetchall_arrayref};
 }
 
 sub set {
 	my ($self, $key, $val) = @_;
 	if (defined $val) {
-		my $e = $self->{dbh}->prepare_cached(<<'')->execute($key, $val);
+		my $sth = $self->{dbh}->prepare_cached(<<'');
 INSERT OR REPLACE INTO kv (k,v) VALUES (?,?)
 
+		$sth->bind_param(1, $key, SQL_BLOB);
+		$sth->bind_param(2, $val, SQL_BLOB);
+		my $e = $sth->execute;
 		$e == 0 ? undef : $e;
 	} else {
-		$self->{dbh}->prepare_cached(<<'')->execute($key);
+		my $sth = $self->{dbh}->prepare_cached(<<'');
 DELETE FROM kv WHERE k = ?
 
+		$sth->bind_param(1, $key, SQL_BLOB);
 	}
 }
 
@@ -110,7 +123,8 @@ sub get {
 	my $sth = $self->{dbh}->prepare_cached(<<'', undef, 1);
 SELECT v FROM kv WHERE k = ?
 
-	$sth->execute($key);
+	$sth->bind_param(1, $key, SQL_BLOB);
+	$sth->execute;
 	$sth->fetchrow_array;
 }
 
@@ -121,9 +135,11 @@ sub xchg {
 	if (defined $newval) {
 		set($self, $key, $newval);
 	} else {
-		$self->{dbh}->prepare_cached(<<'')->execute($key);
+		my $sth = $self->{dbh}->prepare_cached(<<'');
 DELETE FROM kv WHERE k = ?
 
+		$sth->bind_param(1, $key, SQL_BLOB);
+		$sth->execute;
 	}
 	$oldval;
 }
diff --git a/t/shared_kv.t b/t/shared_kv.t
index 8b4f9c29..8dfd3b25 100644
--- a/t/shared_kv.t
+++ b/t/shared_kv.t
@@ -42,5 +42,8 @@ undef $skv;
 ok(!-d $skv_tmpdir, 'temporary dir gone');
 $skv = PublicInbox::SharedKV->new("$tmpdir/dir", 'base');
 ok(-e "$tmpdir/dir/base.sqlite3", 'file created');
+$skv->dbh;
+ok($skv->set_maybe('02', '2'), "`02' set");
+ok($skv->set_maybe('2', '2'), "`2' set (no match on `02')");
 
 done_testing;