diff options
author | Mathieu Othacehe <othacehe@gnu.org> | 2020-10-05 14:46:31 +0200 |
---|---|---|
committer | Mathieu Othacehe <othacehe@gnu.org> | 2020-10-05 14:54:06 +0200 |
commit | 930c2f315c6a9768d31a80d35d2a2550b588deaa (patch) | |
tree | c8e029cc1e01509f36e230eb1d1c1039886ca680 /src | |
parent | 71aac24f3fe8cb396c9d232453d8721519cae914 (diff) | |
download | cuirass-930c2f315c6a9768d31a80d35d2a2550b588deaa.tar cuirass-930c2f315c6a9768d31a80d35d2a2550b588deaa.tar.gz |
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.
Diffstat (limited to 'src')
-rw-r--r-- | src/cuirass/database.scm | 31 | ||||
-rw-r--r-- | src/cuirass/templates.scm | 4 | ||||
-rw-r--r-- | src/schema.sql | 1 | ||||
-rw-r--r-- | src/sql/upgrade-13.sql | 5 |
4 files changed, 18 insertions, 23 deletions
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; |