Optimize build query.

Make sure that db-get-builds only performs one query and add build products to
the result. Also add indexes so that all "order" filters are covered.

Limit the maximum of build objects returned in "/api/latestbuilds" and
"/api/queue" to 1000.

Fixes: <https://issues.guix.gnu.org/43826>.

* src/sql/upgrade-15.sql: New file.
* Makefile (dist_sql_DATA): Add it.
* src/cuirass/database.scm (db-get-builds-by-search): Add "buildproducts" to
the returned build objects.
(db-get-builds): Remove unused filters. Join Outputs and BuildProducts table
in the query. Add "buildproducts" to the returned build objects.
* src/cuirass/http.scm (build->hydra-build): Use "buildproducts" from
"db-get-builds" procedure.
(url-handler): Forbid limit parameters greater than 1000 and use
"buildproducts" from "db-get-builds" procedure.
* src/schema.sql (Builds_stoptime, Builds_stoptime_id, Builds_status_ts_id):
New indexes.

1 files changed, 7 insertions, 0 deletions

diff --git a/src/sql/upgrade-15.sql b/src/sql/upgrade-15.sql
new file mode 100644
index 0000000..1fc38d6
--- /dev/null
+++ b/src/sql/upgrade-15.sql
@@ -0,0 +1,7 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Builds_stoptime on Builds(stoptime DESC);
+CREATE INDEX Builds_stoptime_id on Builds(stoptime DESC, id DESC);
+CREATE INDEX Builds_status_ts_id on Builds(status DESC, timestamp DESC, id ASC);
+
+COMMIT;