user/dev discussion of public-inbox itself
 help / color / mirror / code / Atom feed
From: Eric Wong <e@80x24.org>
To: meta@public-inbox.org
Subject: [PATCH 5/9] msgmap: use DBI->prepare_cached
Date: Tue, 12 Oct 2021 11:47:01 +0000	[thread overview]
Message-ID: <20211012114705.383-6-e@80x24.org> (raw)
In-Reply-To: <20211012114705.383-1-e@80x24.org>

msgmap is not performance-critical enough to justify doing our
own prepared statement caching.  Just rely on the functionality
of DBI here so future changes will be easier.

There's also minor style changes to avoid dirtying refcount
cache lines bumping by repeating hash lookups rather than attempting
to store them as locals.
---
 lib/PublicInbox/Msgmap.pm | 54 +++++++++++++++------------------------
 1 file changed, 20 insertions(+), 34 deletions(-)

diff --git a/lib/PublicInbox/Msgmap.pm b/lib/PublicInbox/Msgmap.pm
index 3887a9e6..de9fd989 100644
--- a/lib/PublicInbox/Msgmap.pm
+++ b/lib/PublicInbox/Msgmap.pm
@@ -61,18 +61,15 @@ sub meta_accessor {
 	my ($self, $key, $value) = @_;
 
 	my $sql = 'SELECT val FROM meta WHERE key = ? LIMIT 1';
-	my $dbh = $self->{dbh};
-	my $prev;
-	defined $value or return $dbh->selectrow_array($sql, undef, $key);
-
-	$prev = $dbh->selectrow_array($sql, undef, $key);
+	my $prev = $self->{dbh}->selectrow_array($sql, undef, $key);
+	$value // return $prev;
 
 	if (defined $prev) {
 		$sql = 'UPDATE meta SET val = ? WHERE key = ?';
-		$dbh->do($sql, undef, $value, $key);
+		$self->{dbh}->do($sql, undef, $value, $key);
 	} else {
 		$sql = 'INSERT INTO meta (key,val) VALUES (?,?)';
-		$dbh->do($sql, undef, $key, $value);
+		$self->{dbh}->do($sql, undef, $key, $value);
 	}
 	$prev;
 }
@@ -109,33 +106,30 @@ sub num_highwater {
 
 sub mid_insert {
 	my ($self, $mid) = @_;
-	my $dbh = $self->{dbh};
-	my $sth = $dbh->prepare_cached(<<'');
+	my $sth = $self->{dbh}->prepare_cached(<<'');
 INSERT INTO msgmap (mid) VALUES (?)
 
 	return unless eval { $sth->execute($mid) };
-	my $num = $dbh->last_insert_id(undef, undef, 'msgmap', 'num');
+	my $num = $self->{dbh}->last_insert_id(undef, undef, 'msgmap', 'num');
 	$self->num_highwater($num) if defined($num);
 	$num;
 }
 
 sub mid_for {
 	my ($self, $num) = @_;
-	my $dbh = $self->{dbh};
-	my $sth = $self->{mid_for} ||=
-		$dbh->prepare('SELECT mid FROM msgmap WHERE num = ? LIMIT 1');
-	$sth->bind_param(1, $num);
-	$sth->execute;
+	my $sth = $self->{dbh}->prepare_cached(<<"", undef, 1);
+SELECT mid FROM msgmap WHERE num = ? LIMIT 1
+
+	$sth->execute($num);
 	$sth->fetchrow_array;
 }
 
 sub num_for {
 	my ($self, $mid) = @_;
-	my $dbh = $self->{dbh};
-	my $sth = $self->{num_for} ||=
-		$dbh->prepare('SELECT num FROM msgmap WHERE mid = ? LIMIT 1');
-	$sth->bind_param(1, $mid);
-	$sth->execute;
+	my $sth = $self->{dbh}->prepare_cached(<<"", undef, 1);
+SELECT num FROM msgmap WHERE mid = ? LIMIT 1
+
+	$sth->execute($mid);
 	$sth->fetchrow_array;
 }
 
@@ -157,18 +151,12 @@ sub minmax {
 
 sub mid_delete {
 	my ($self, $mid) = @_;
-	my $dbh = $self->{dbh};
-	my $sth = $dbh->prepare('DELETE FROM msgmap WHERE mid = ?');
-	$sth->bind_param(1, $mid);
-	$sth->execute;
+	$self->{dbh}->do('DELETE FROM msgmap WHERE mid = ?', undef, $mid);
 }
 
 sub num_delete {
 	my ($self, $num) = @_;
-	my $dbh = $self->{dbh};
-	my $sth = $dbh->prepare('DELETE FROM msgmap WHERE num = ?');
-	$sth->bind_param(1, $num);
-	$sth->execute;
+	$self->{dbh}->do('DELETE FROM msgmap WHERE num = ?', undef, $num);
 }
 
 sub create_tables {
@@ -192,9 +180,8 @@ CREATE TABLE IF NOT EXISTS meta (
 sub msg_range {
 	my ($self, $beg, $end, $cols) = @_;
 	$cols //= 'num,mid';
-	my $dbh = $self->{dbh};
 	my $attr = { Columns => [] };
-	my $mids = $dbh->selectall_arrayref(<<"", $attr, $$beg, $end);
+	my $mids = $self->{dbh}->selectall_arrayref(<<"", $attr, $$beg, $end);
 SELECT $cols FROM msgmap WHERE num >= ? AND num <= ?
 ORDER BY num ASC LIMIT 1000
 
@@ -206,10 +193,9 @@ ORDER BY num ASC LIMIT 1000
 # see scripts/xhdr-num2mid or PublicInbox::Filter::RubyLang for usage
 sub mid_set {
 	my ($self, $num, $mid) = @_;
-	my $sth = $self->{mid_set} ||= do {
-		$self->{dbh}->prepare(
-			'INSERT OR IGNORE INTO msgmap (num,mid) VALUES (?,?)');
-	};
+	my $sth = $self->{dbh}->prepare_cached(<<"");
+INSERT OR IGNORE INTO msgmap (num,mid) VALUES (?,?)
+
 	my $result = $sth->execute($num, $mid);
 	$self->num_highwater($num) if (defined($result) && $result == 1);
 	$result;

  parent reply	other threads:[~2021-10-12 11:47 UTC|newest]

Thread overview: 10+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2021-10-12 11:46 [PATCH 0/9] various read-only daemon and WWW things Eric Wong
2021-10-12 11:46 ` [PATCH 1/9] daemon: use v5.10.1, disable local warnings Eric Wong
2021-10-12 11:46 ` [PATCH 2/9] daemon: quiet down Eml-related warnings Eric Wong
2021-10-12 11:46 ` [PATCH 3/9] search: delete QueryParser along with DB handle Eric Wong
2021-10-12 11:47 ` [PATCH 4/9] nntp: use defined-OR from Perl 5.10 for msgid check Eric Wong
2021-10-12 11:47 ` Eric Wong [this message]
2021-10-12 11:47 ` [PATCH 6/9] msgmap: share most of check_inodes w/ over Eric Wong
2021-10-12 11:47 ` [PATCH 7/9] daemon: unconditionally close Xapian shards on cleanup Eric Wong
2021-10-12 11:47 ` [PATCH 8/9] msgmap: ->new_file to supports $ibx arg, drop ->new Eric Wong
2021-10-12 11:47 ` [PATCH 9/9] www: _/text/config/raw Last-Modified: is mm->created_at Eric Wong

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://public-inbox.org/README

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20211012114705.383-6-e@80x24.org \
    --to=e@80x24.org \
    --cc=meta@public-inbox.org \
    --subject='Re: [PATCH 5/9] msgmap: use DBI->prepare_cached' \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link

Code repositories for project(s) associated with this inbox:

	https://80x24.org/public-inbox.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).