03/03: Handle NULL better insert-missing-data-and-return-all-ids queries

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 8d109bccb5f90afe449b3bad1b26818e5637464c
Author: Christopher Baines <address@hidden>
AuthorDate: Sun Mar 29 10:29:46 2020 +0100

    Handle NULL better insert-missing-data-and-return-all-ids queries
    
    Actually check if fields can be NULL, and if they can be then include some
    extra conditions for the comparison.
    
    This will at least make the queries smaller, I'm not sure if it will have an
    effect on performance.
---
 guix-data-service/model/utils.scm | 45 ++++++++++++++++++++++++++++++++-------
 1 file changed, 37 insertions(+), 8 deletions(-)

diff --git a/guix-data-service/model/utils.scm 
b/guix-data-service/model/utils.scm
index 3b256f1..5676c23 100644
--- a/guix-data-service/model/utils.scm
+++ b/guix-data-service/model/utils.scm
@@ -162,6 +162,23 @@ WHERE table_name = $1"
        (error
         (simple-format #f "error: unknown type for value: ~A" v)))))
 
+  (define schema-details
+    (table-schema conn table-name))
+
+  (define (field-can-be-null? field)
+    (match (find (lambda (column-data)
+                   (string=? field
+                             (car column-data)))
+                 schema-details)
+      ((column-name data-type is-nullable?) is-nullable?)
+      (#f
+       (simple-format
+        (current-error-port)
+        "error: couldn't find data for ~A in ~A\n"
+        field
+        schema-details)
+       (error "error: field-can-be-null?"))))
+
   (define select-query
     (string-append
      "SELECT id, "
@@ -187,10 +204,15 @@ WHERE table_name = $1"
      "ON "
      (string-join
       (map (lambda (field)
-             (string-append
-              "(" table-name "." field " = vals." field
-              " OR (" table-name "." field " IS NULL AND"
-              " vals." field " IS NULL))"))
+             (string-concatenate
+              `("("
+                ,table-name "." ,field " = vals." ,field
+                ,@(if (field-can-be-null? field)
+                      `(" OR (" ,table-name "." ,field " IS NULL AND"
+                        " vals." ,field " IS NULL"
+                        ")")
+                      '())
+                ")")))
            field-strings)
       " AND ")))
 
@@ -206,10 +228,17 @@ WHERE table_name = $1"
      " ON "
      (string-join
       (map (lambda (field)
-             (string-append
-              "(" table-name "." field " = " temp-table-name "." field
-              " OR (" table-name "." field " IS NULL AND"
-              " " temp-table-name "." field " IS NULL))"))
+             (string-concatenate
+              `("("
+                ,table-name "." ,field " = " ,temp-table-name "." ,field
+                ,@(if (field-can-be-null? field)
+                      `(" OR ("
+                        ,table-name "." ,field " IS NULL"
+                        " AND "
+                        ,temp-table-name "." ,field " IS NULL"
+                        ")")
+                      '())
+                ")")))
            field-strings)
       " AND ")))