branch master updated: Do not allow full search on nix_name field.

[Mathieu Othacehe]

This is an automated email from the git hooks/post-receive script.

mothacehe pushed a commit to branch master
in repository guix-cuirass.

The following commit(s) were added to refs/heads/master by this push:
     new 930c2f3  Do not allow full search on nix_name field.
930c2f3 is described below

commit 930c2f315c6a9768d31a80d35d2a2550b588deaa
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Mon Oct 5 14:46:31 2020 +0200

    Do not allow full search on nix_name field.
    
    Searching for something like "%a%" can take up to ten minutes. Remove the
    usage of special characters "^" and "$" in queries. Instead, always add a 
"%"
    character at the end of the query. Also add an index on the nix_name field.
    
    Fixes: <https://issues.guix.gnu.org/43791>.
    
    * src/sql/upgrade-13.sql: New file.
    * Makefile.am (dist_sql_DATA): Add it.
    * src/schema.sql (Builds_nix_name): New index.
    * src/cuirass/database.scm (query->bind-arguments): Remove support for "^" 
and
    "$" special characters. Instead make sure that the query does not contain 
any
    "%" character and prefix the query by "%".
    (db-get-builds-by-search): Remove an useless query nesting level.
    * src/cuirass/templates.scm (search-form): Adapt the search help message
    accordingly.
---
 Makefile.am               |  3 ++-
 src/cuirass/database.scm  | 31 +++++++++++--------------------
 src/cuirass/templates.scm |  4 +---
 src/schema.sql            |  1 +
 src/sql/upgrade-13.sql    |  5 +++++
 5 files changed, 20 insertions(+), 24 deletions(-)

diff --git a/Makefile.am b/Makefile.am
index a575755..9f7d411 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -81,7 +81,8 @@ dist_sql_DATA =                               \
   src/sql/upgrade-9.sql                                \
   src/sql/upgrade-10.sql                       \
   src/sql/upgrade-11.sql                       \
-  src/sql/upgrade-12.sql
+  src/sql/upgrade-12.sql                       \
+  src/sql/upgrade-13.sql
 
 dist_css_DATA =                                        \
   src/static/css/cuirass.css                   \
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index cc705cb..fbb5ecc 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -808,20 +808,13 @@ WHERE derivation =" derivation ";"))
                     `(#:status ,(assoc-ref status-values status)))
                    ((_ invalid) '())    ; ignore
                    ((query)
-                    `(#:query
-                      ,(fold
-                        (lambda (transform val)
-                          (match transform
-                            ((pred modify-true modify-false)
-                             ((if (pred val) modify-true modify-false) val))))
-                        query
-                        ;; Process special characters ^ and $.
-                        (list (list (cut string-prefix? "^" <>)
-                                    (cut string-drop <> 1)
-                                    (cut string-append "%" <>))
-                              (list (cut string-suffix? "$" <>)
-                                    (cut string-drop-right <> 1)
-                                    (cut string-append <> "%"))))))))
+                    ;; Remove any '%' that could make the search too slow and
+                    ;; add one at the end of the query.
+                    `(#:query ,(string-append
+                                (string-join
+                                 (string-split query #\%)
+                                 "")
+                                "%")))))
                (string-tokenize query-string))))
     ;; Normalize arguments
     (fold (lambda (key acc)
@@ -835,10 +828,9 @@ WHERE derivation =" derivation ";"))
 FILTERS is an assoc list whose possible keys are the symbols query,
 border-low-id, border-high-id, and nr."
   (with-db-worker-thread db
-    (let* ((stmt-text (format #f "SELECT * FROM (
-SELECT Builds.rowid, Builds.timestamp, Builds.starttime,
-Builds.stoptime, Builds.log, Builds.status, Builds.job_name, Builds.system,
-Builds.nix_name, Specifications.name
+    (let* ((stmt-text (format #f "SELECT Builds.rowid, Builds.timestamp,
+Builds.starttime,Builds.stoptime, Builds.log, Builds.status,
+Builds.job_name, Builds.system, Builds.nix_name, Specifications.name
 FROM Builds
 INNER JOIN Evaluations ON Builds.evaluation = Evaluations.id
 INNER JOIN Specifications ON Evaluations.specification = Specifications.name
@@ -857,8 +849,7 @@ ORDER BY
 CASE WHEN :borderlowid IS NULL THEN Builds.rowid
                                ELSE -Builds.rowid
 END DESC
-LIMIT :nr)
-ORDER BY rowid DESC;"))
+LIMIT :nr;"))
            (stmt (sqlite-prepare db stmt-text #:cache? #t)))
       (apply sqlite-bind-arguments
              stmt
diff --git a/src/cuirass/templates.scm b/src/cuirass/templates.scm
index d6abc05..70737fc 100644
--- a/src/cuirass/templates.scm
+++ b/src/cuirass/templates.scm
@@ -89,12 +89,10 @@
                (code "failed-dependency") ", "
                (code "failed-other") ", or "
                (code "canceled") "."))
-          (p "You can also use the anchors " (code "^") " and " (code "$") "
-for matching the beginning and the end of a name, respectively.")
           (p "For example, the following query will list successful builds of
 the " (code "guix-master") " specification for the " (code "i686-linux") "
 system whose names start with " (code "guile-") ":" (br)
-(code "spec:guix-master system:i686-linux status:success ^guile-")))))
+(code "spec:guix-master system:i686-linux status:success guile-")))))
 
 (define* (html-page title body navigation #:optional query)
   "Return HTML page with given TITLE and BODY."
diff --git a/src/schema.sql b/src/schema.sql
index cc9ad24..f98d430 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -98,6 +98,7 @@ CREATE TABLE Events (
 -- Create indexes to speed up common queries.
 CREATE INDEX Builds_status_index ON Builds (status);
 CREATE INDEX Builds_evaluation_index ON Builds (evaluation, status);
+CREATE INDEX Builds_nix_name ON Builds (nix_name COLLATE NOCASE);
 CREATE INDEX Evaluations_status_index ON Evaluations (id, status);
 CREATE INDEX Evaluations_specification_index ON Evaluations (specification, id 
DESC);
 CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
diff --git a/src/sql/upgrade-13.sql b/src/sql/upgrade-13.sql
new file mode 100644
index 0000000..b7a0cb5
--- /dev/null
+++ b/src/sql/upgrade-13.sql
@@ -0,0 +1,5 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Builds_nix_name ON Builds (nix_name COLLATE NOCASE);
+
+COMMIT;