diff options
| -rw-r--r-- | Makefile.am | 3 | ||||
| -rw-r--r-- | src/cuirass/database.scm | 29 | ||||
| -rw-r--r-- | src/schema.sql | 6 | ||||
| -rw-r--r-- | src/sql/upgrade-12.sql | 7 |
4 files changed, 23 insertions, 22 deletions
diff --git a/Makefile.am b/Makefile.am index 0e4d3c8..60b1e24 100644 --- a/Makefile.am +++ b/Makefile.am @@ -79,7 +79,8 @@ dist_sql_DATA = \ src/sql/upgrade-8.sql \ src/sql/upgrade-9.sql \ src/sql/upgrade-10.sql \ - src/sql/upgrade-11.sql + src/sql/upgrade-11.sql \ + src/sql/upgrade-12.sql dist_css_DATA = \ src/static/css/cuirass.css \ diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index 666a20b..c67a234 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -1032,21 +1032,17 @@ FROM Evaluations ORDER BY id DESC LIMIT " limit ";")) (define (db-get-evaluations-build-summary spec limit border-low border-high) (with-db-worker-thread db (let loop ((rows (sqlite-exec db " -SELECT E.id, E.status, B.succeeded, B.failed, B.scheduled -FROM -(SELECT id, status -FROM Evaluations +SELECT E.id, E.status, SUM(B.status=0) as succeeded, +SUM(B.status>0) as failed, SUM(B.status<0) as scheduled FROM +(SELECT id, status FROM Evaluations WHERE (specification=" spec ") AND (" border-low "IS NULL OR (id >" border-low ")) AND (" border-high "IS NULL OR (id <" border-high ")) ORDER BY CASE WHEN " border-low "IS NULL THEN id ELSE -id END DESC LIMIT " limit ") E -LEFT JOIN -(SELECT rowid, evaluation, SUM(status=0) as succeeded, -SUM(status>0) as failed, SUM(status<0) as scheduled -FROM Builds -GROUP BY evaluation) B +LEFT JOIN Builds as B ON B.evaluation=E.id +GROUP BY E.id ORDER BY E.id ASC;")) (evaluations '())) (match rows @@ -1081,16 +1077,11 @@ WHERE specification=" spec))) (with-db-worker-thread db (let ((rows (sqlite-exec db " SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime, -B.total, B.succeeded, B.failed, B.scheduled -FROM - (SELECT id, status, timestamp, checkouttime, evaltime -FROM Evaluations -WHERE (id=" id ")) E -LEFT JOIN - (SELECT rowid, evaluation, SUM(status=0) as succeeded, -SUM(status>0) as failed, SUM(status<0) as scheduled, SUM(status>-100) as total -FROM Builds -GROUP BY evaluation) B +SUM(B.status>-100) as total, SUM(B.status=0) as succeeded, +SUM(B.status>0) as failed, SUM(B.status<0) as scheduled FROM +(SELECT id, status, timestamp, checkouttime, evaltime FROM + Evaluations WHERE (id=" id ")) E +LEFT JOIN Builds as B ON B.evaluation=E.id ORDER BY E.id ASC;"))) (and=> (expect-one-row rows) diff --git a/src/schema.sql b/src/schema.sql index ed5893e..cc9ad24 100644 --- a/src/schema.sql +++ b/src/schema.sql @@ -95,9 +95,11 @@ CREATE TABLE Events ( event_json TEXT NOT NULL ); --- Create indexes to speed up common queries, in particular those --- corresponding to /api/latestbuilds and /api/queue HTTP requests. +-- 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 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); CREATE INDEX Inputs_index ON Inputs(specification, name, branch); diff --git a/src/sql/upgrade-12.sql b/src/sql/upgrade-12.sql new file mode 100644 index 0000000..06aaffe --- /dev/null +++ b/src/sql/upgrade-12.sql @@ -0,0 +1,7 @@ +BEGIN TRANSACTION; + +CREATE INDEX Builds_evaluation_index ON Builds (evaluation, status); +CREATE INDEX Evaluations_status_index ON Evaluations (id, status); +CREATE INDEX Evaluations_specification_index ON Evaluations (specification, id DESC); + +COMMIT; |