commit 22f3a7ce67aefccfc7d2c6871c9168ec89319e29
from: Alisdair MacLeod <git@alisdairmacleod.co.uk>
date: Tue Mar 31 18:52:18 2026 UTC

Combine bookingform's two queries into a single LEFT JOIN

The booking form page previously ran separate queries for day
bookings and available desks, each requiring its own
prepare/bind/step/finalize cycle. A single LEFT JOIN query
returns all desks with their booking status in one pass,
halving the SQLite overhead for this route.

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>

commit - 77f906b3cf1c549a28c0cd767e2244e470f598bf
commit + 22f3a7ce67aefccfc7d2c6871c9168ec89319e29
blob - 8652d6fb89573981c8b033c155824147cea4c5f8
blob + 1603bc07aeb8c87cea5da5a9983e6e2c55737b5e
--- bookingform.c
+++ bookingform.c
@@ -37,11 +37,10 @@ static const char html_head[] = {
 void
 handle_bookingform(const char *day_param)
 {
-	struct booking_list	 bl;
-	struct desk_list	 dl;
+	struct booking_list	 all;
 	struct tm		 tm;
 	char			 display[16], datestr[16];
-	int			 i;
+	int			 i, has_booked, has_available;
 
 	const char *user = getenv("REMOTE_USER");
 	if (user == NULL || *user == '\0') {
@@ -64,28 +63,18 @@ handle_bookingform(const char *day_param)
 		return;
 	}
 
-	if (db_query_day_bookings(db, datestr, &bl) != 0) {
-		fprintf(stderr, "Error listing booked desks for day "
+	if (db_query_desks_with_bookings(db, datestr, &all) != 0) {
+		fprintf(stderr, "Error listing desks for day "
 		    "\"%s\"\n", datestr);
 		db_close(db);
 		cgi_error(500);
 		return;
 	}
-
-	if (db_query_available_desks(db, datestr, &dl) != 0) {
-		fprintf(stderr, "Error listing available desks for day "
-		    "\"%s\"\n", datestr);
-		booking_list_free(&bl);
-		db_close(db);
-		cgi_error(500);
-		return;
-	}
 	db_close(db);
 
 	char *csrf = cgi_csrf_generate();
 	if (csrf == NULL) {
-		booking_list_free(&bl);
-		desk_list_free(&dl);
+		booking_list_free(&all);
 		cgi_error(500);
 		return;
 	}
@@ -101,7 +90,17 @@ handle_bookingform(const char *day_param)
 	printf("    <img src=\"/static/floorplan.png\" "
 	    "alt=\"Floor plan\">\n");
 
-	if (bl.count > 0) {
+	/* Check which sections are needed. */
+	has_booked = 0;
+	has_available = 0;
+	for (i = 0; i < all.count; i++) {
+		if (*all.items[i].user != '\0')
+			has_booked = 1;
+		else
+			has_available = 1;
+	}
+
+	if (has_booked) {
 		printf("    <h2>Current bookings for ");
 		cgi_html_escape(display);
 		printf("</h2>\n");
@@ -113,13 +112,15 @@ handle_bookingform(const char *day_param)
 		printf("        </tr>\n");
 		printf("        </thead>\n");
 		printf("        <tbody>\n");
-		for (i = 0; i < bl.count; i++) {
+		for (i = 0; i < all.count; i++) {
+			if (*all.items[i].user == '\0')
+				continue;
 			printf("        <tr>\n");
 			printf("            <td>");
-			cgi_html_escape(bl.items[i].desk);
+			cgi_html_escape(all.items[i].desk);
 			printf("</td>\n");
 			printf("            <td>");
-			cgi_html_escape(bl.items[i].user);
+			cgi_html_escape(all.items[i].user);
 			printf("</td>\n");
 			printf("        </tr>\n");
 		}
@@ -127,23 +128,25 @@ handle_bookingform(const char *day_param)
 		printf("    </table>\n");
 	}
 
-	if (dl.count > 0) {
+	if (has_available) {
 		printf("    <h2>Book a desk for ");
 		cgi_html_escape(display);
 		printf("</h2>\n");
 		printf("    <form method=\"POST\">\n");
 		printf("        <input type=\"hidden\" name=\"%s\" "
 		    "value=\"%s\">\n", CSRF_KEY, csrf);
-		for (i = 0; i < dl.count; i++) {
+		for (i = 0; i < all.count; i++) {
+			if (*all.items[i].user != '\0')
+				continue;
 			printf("        <input type=\"radio\" id=\"");
-			cgi_html_escape(dl.items[i]);
+			cgi_html_escape(all.items[i].desk);
 			printf("\" name=\"%s\" value=\"", DESK_KEY);
-			cgi_html_escape(dl.items[i]);
+			cgi_html_escape(all.items[i].desk);
 			printf("\">\n");
 			printf("        <label for=\"");
-			cgi_html_escape(dl.items[i]);
+			cgi_html_escape(all.items[i].desk);
 			printf("\">");
-			cgi_html_escape(dl.items[i]);
+			cgi_html_escape(all.items[i].desk);
 			printf("</label>\n");
 			printf("        <br/>\n");
 		}
@@ -162,6 +165,5 @@ handle_bookingform(const char *day_param)
 	printf("</html>\n");
 
 	free(csrf);
-	booking_list_free(&bl);
-	desk_list_free(&dl);
+	booking_list_free(&all);
 }
blob - 6965fc60f248b87956a53fc2097f3c0a477cb72d
blob + 1e5f76636cf06ad3a70eead626f995e49af9bbde
--- db.c
+++ db.c
@@ -51,6 +51,11 @@ static const char sql_select_available_desks[] = {
 	, '\0'
 };
 
+static const char sql_select_desks_with_bookings[] = {
+#embed "sql/select_desks_with_bookings.sql"
+	, '\0'
+};
+
 static const char sql_insert_booking[] = {
 #embed "sql/insert_booking.sql"
 	, '\0'
@@ -399,6 +404,56 @@ db_query_available_desks(sqlite3 *db, const char *day,
 }
 
 /*
+ * Query all desks with their booking status for a given day.
+ * Each desk appears exactly once: booked desks have a non-empty
+ * user field, available desks have an empty user string. Results
+ * are ordered by desk name using natural sort collation. Replaces
+ * the need for separate day_bookings and available_desks queries.
+ * The caller must call booking_list_free when done. Returns 0 on
+ * success, -1 on failure.
+ */
+int
+db_query_desks_with_bookings(sqlite3 *db, const char *day,
+    struct booking_list *bl)
+{
+	sqlite3_stmt	*stmt;
+	int		 rc;
+
+	memset(bl, 0, sizeof(*bl));
+
+	if (sqlite3_prepare_v2(db, sql_select_desks_with_bookings, -1,
+	    &stmt, NULL) != SQLITE_OK) {
+		fprintf(stderr, "prepare select desks with bookings: %s\n",
+		    sqlite3_errmsg(db));
+		return -1;
+	}
+
+	sqlite3_bind_text(stmt, 1, day, -1, SQLITE_STATIC);
+
+	while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
+		const char *desk = (const char *)sqlite3_column_text(stmt, 0);
+		const char *user = "";
+		if (sqlite3_column_type(stmt, 1) != SQLITE_NULL)
+			user = (const char *)sqlite3_column_text(stmt, 1);
+		if (booking_list_add(bl, user, desk, day) != 0) {
+			sqlite3_finalize(stmt);
+			booking_list_free(bl);
+			return -1;
+		}
+	}
+
+	sqlite3_finalize(stmt);
+	if (rc != SQLITE_DONE) {
+		fprintf(stderr, "step select desks with bookings: %s\n",
+		    sqlite3_errmsg(db));
+		booking_list_free(bl);
+		return -1;
+	}
+
+	return 0;
+}
+
+/*
  * Insert a booking for the given user, desk, and day. Foreign key
  * constraints (enabled at connection open) reject unknown desks.
  * Returns 0 on success, or the SQLite extended error code on
blob - e5fb88c036b13412cac5b0b9cc66a11b4fbae1f4
blob + 64e97bac5c093cc1a50f1b48b114f6b4267d24d3
--- db.h
+++ db.h
@@ -57,6 +57,8 @@ int		 db_query_day_bookings(sqlite3 *, const char *,
 		    struct booking_list *);
 int		 db_query_available_desks(sqlite3 *, const char *,
 		    struct desk_list *);
+int		 db_query_desks_with_bookings(sqlite3 *, const char *,
+		    struct booking_list *);
 int		 db_insert_booking(sqlite3 *, const char *, const char *,
 		    const char *);
 int		 db_delete_booking(sqlite3 *, const char *, const char *,
blob - /dev/null
blob + fe2bd69dabc977c43cf0bd7436b46a5ba565e3d8 (mode 644)
--- /dev/null
+++ sql/select_desks_with_bookings.sql
@@ -0,0 +1 @@
+SELECT d.desk, b.user FROM desks d LEFT JOIN bookings b ON d.desk = b.desk AND b.day = ? ORDER BY d.desk COLLATE "natural"
\ No newline at end of file