unofficial mirror of guix-patches@gnu.org 
 help / color / mirror / code / Atom feed
From: Christopher Baines <mail@cbaines.net>
To: 69292@debbugs.gnu.org
Cc: "Christopher Baines" <guix@cbaines.net>,
	"Josselin Poiret" <dev@jpoiret.xyz>,
	"Ludovic Courtès" <ludo@gnu.org>,
	"Mathieu Othacehe" <othacehe@gnu.org>,
	"Ricardo Wurmus" <rekado@elephly.net>,
	"Simon Tournier" <zimon.toutoune@gmail.com>,
	"Tobias Geerinckx-Rice" <me@tobias.gr>
Subject: [bug#69292] [PATCH 4/6] store: database: Stop finalizing prepared statements.
Date: Tue, 20 Feb 2024 19:39:04 +0000	[thread overview]
Message-ID: <c4bf41b5c91623ee93285f35516265cfdb0ab401.1708457946.git.mail@cbaines.net> (raw)
In-Reply-To: <4b6a268daab5e0b307dff2229d551a47c9fe1ebc.1708457946.git.mail@cbaines.net>

Especially since we're asking for these to be cached.

Management of prepared statements isn't trivial, since you don't want to keep
them forever as this can lead to poor query performance, but I don't think
that finalizing them immediately is the right solution.

Change-Id: I61706b4d09d771835bb8f074b8f6a6ee871f5e2d

* guix/store/database.scm (sqlite-step-and-reset): New procedure.
(last-insert-row, path-id, update-or-insert, add-references): Don't finalize
prepared statements.

Change-Id: I2a2c6deb43935d67df9e43000a5105343d72b3e6
---
 guix/store/database.scm | 40 ++++++++++++++++++----------------------
 1 file changed, 18 insertions(+), 22 deletions(-)

diff --git a/guix/store/database.scm b/guix/store/database.scm
index 7e3a2873ce..8d8b7346e0 100644
--- a/guix/store/database.scm
+++ b/guix/store/database.scm
@@ -167,16 +167,19 @@ (define-syntax with-database
     ((_ file db exp ...)
      (call-with-database file (lambda (db) exp ...)))))
 
+(define (sqlite-step-and-reset statement)
+  (let ((val (sqlite-step statement)))
+    (sqlite-reset statement)
+    val))
+
 (define (last-insert-row-id db)
   ;; XXX: (sqlite3) currently lacks bindings for 'sqlite3_last_insert_rowid'.
   ;; Work around that.
-  (let* ((stmt   (sqlite-prepare db "SELECT last_insert_rowid();"
-                                 #:cache? #t))
-         (result (sqlite-fold cons '() stmt)))
-    (sqlite-finalize stmt)
-    (match result
-      ((#(id)) id)
-      (_ #f))))
+  (let ((stmt (sqlite-prepare db
+                              "SELECT last_insert_rowid();"
+                              #:cache? #t)))
+    (vector-ref (sqlite-step-and-reset stmt)
+                0)))
 
 (define* (path-id db path)
   "If PATH exists in the 'ValidPaths' table, return its numerical
@@ -187,11 +190,9 @@ (define* (path-id db path)
 SELECT id FROM ValidPaths WHERE path = :path"
                #:cache? #t)))
     (sqlite-bind-arguments stmt #:path path)
-    (let ((result (sqlite-fold cons '() stmt)))
-      (sqlite-finalize stmt)
-      (match result
-        ((#(id) . _) id)
-        (_ #f)))))
+    (match (sqlite-step-and-reset stmt)
+      (#(id) id)
+      (#f #f))))
 
 (define-inlinable (assert-integer proc in-range? key number)
   (unless (integer? number)
@@ -228,9 +229,8 @@ (define* (update-or-insert db #:key path deriver hash nar-size time)
           (sqlite-bind-arguments stmt #:id id
                                  #:deriver deriver
                                  #:hash hash #:size nar-size #:time time)
-          (sqlite-fold cons '() stmt)
-          (sqlite-finalize stmt)
-          (last-insert-row-id db))
+          (sqlite-step-and-reset stmt)
+          id)
         (let ((stmt (sqlite-prepare
                      db
                      "
@@ -240,8 +240,7 @@ (define* (update-or-insert db #:key path deriver hash nar-size time)
           (sqlite-bind-arguments stmt
                                  #:path path #:deriver deriver
                                  #:hash hash #:size nar-size #:time time)
-          (sqlite-fold cons '() stmt)             ;execute it
-          (sqlite-finalize stmt)
+          (sqlite-step-and-reset stmt)
           (last-insert-row-id db)))))
 
 (define (add-references db referrer references)
@@ -254,13 +253,10 @@ (define (add-references db referrer references)
 VALUES (:referrer, :reference)"
                #:cache? #t)))
     (for-each (lambda (reference)
-                (sqlite-reset stmt)
                 (sqlite-bind-arguments stmt #:referrer referrer
                                        #:reference reference)
-                (sqlite-fold cons '() stmt)       ;execute it
-                (last-insert-row-id db))
-              references)
-    (sqlite-finalize stmt)))
+                (sqlite-step-and-reset stmt))
+              references)))
 
 (define (timestamp)
   "Return a timestamp, either the current time of SOURCE_DATE_EPOCH."
-- 
2.41.0





  parent reply	other threads:[~2024-02-20 19:40 UTC|newest]

Thread overview: 20+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2024-02-20 19:20 [bug#69292] [PATCH 0/6] Prepare the database code for use in the daemon Christopher Baines
2024-02-20 19:39 ` [bug#69292] [PATCH 1/6] store: database: Remove call-with-savepoint and associated code Christopher Baines
2024-02-20 19:39   ` [bug#69292] [PATCH 2/6] store: database: Remove with-statement " Christopher Baines
2024-02-23 16:35     ` Ludovic Courtès
2024-02-23 18:32       ` Reepca Russelstein via Guix-patches via
2024-03-05 11:05         ` Ludovic Courtès
2024-02-20 19:39   ` [bug#69292] [PATCH 3/6] store: database: Inline SQL to where it's used Christopher Baines
2024-02-23 16:40     ` Ludovic Courtès
2024-02-20 19:39   ` Christopher Baines [this message]
2024-02-22 12:39     ` [bug#69292] [PATCH 4/6] store: database: Stop finalizing prepared statements reepca--- via Guix-patches via
2024-02-26 10:50       ` Christopher Baines
2024-02-20 19:39   ` [bug#69292] [PATCH 5/6] store: database: Refactor sqlite-register Christopher Baines
2024-02-23 16:33     ` Ludovic Courtès
2024-02-20 19:39   ` [bug#69292] [PATCH 6/6] store: database: Rename a couple of procedures Christopher Baines
2024-02-23 16:43     ` Ludovic Courtès
2024-02-26 11:03       ` Christopher Baines
2024-02-27  9:24         ` Ludovic Courtès
2024-04-03 17:35           ` Christopher Baines
2024-02-23 16:31   ` [bug#69292] [PATCH 1/6] store: database: Remove call-with-savepoint and associated code Ludovic Courtès
2024-04-03 17:36 ` bug#69292: [PATCH 0/6] Prepare the database code for use in the daemon Christopher Baines

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://guix.gnu.org/

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

  git send-email \
    --in-reply-to=c4bf41b5c91623ee93285f35516265cfdb0ab401.1708457946.git.mail@cbaines.net \
    --to=mail@cbaines.net \
    --cc=69292@debbugs.gnu.org \
    --cc=dev@jpoiret.xyz \
    --cc=guix@cbaines.net \
    --cc=ludo@gnu.org \
    --cc=me@tobias.gr \
    --cc=othacehe@gnu.org \
    --cc=rekado@elephly.net \
    --cc=zimon.toutoune@gmail.com \
    /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
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/guix.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).