commands: db_row_json and db_exec_bound helpers

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

1 files changed, 23 insertions, 87 deletions

diff --git a/src/cmd_users.c b/src/cmd_users.c
index 7fae2cc..04ee42c 100644
--- a/src/cmd_users.c
+++ b/src/cmd_users.c
@@ -50,35 +50,12 @@ static yyjson_mut_val *h_user_create(struct req *r)
 
 static yyjson_mut_val *h_user_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT id,username,display_name,is_admin,created_at,disabled_at"
-            " FROM users ORDER BY id",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "username",
-                               sq(sqlite3_column_text(st, 1)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "display_name",
-                               sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_bool(r->rdoc, o, "is_admin",
-                                sqlite3_column_int(st, 3) != 0);
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                               sq(sqlite3_column_text(st, 4)));
-        if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "disabled_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "disabled_at",
-                                   sq(sqlite3_column_text(st, 5)));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r,
+                    "SELECT id,username,display_name,is_admin,created_at,disabled_at"
+                    " FROM users ORDER BY id",
+                    "id:i,username:s,display_name:s,is_admin:b,created_at:s,"
+                    "disabled_at:n",
+                    "");
 }
 
 static int scope_allowed(const char *role, const char *scope)
@@ -173,46 +150,13 @@ static yyjson_mut_val *h_token_create(struct req *r)
 
 static yyjson_mut_val *h_token_list(struct req *r)
 {
-    yyjson_mut_val *items = yyjson_mut_arr(r->rdoc);
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            "SELECT id,label,scopes,created_at,expires_at,last_used_at,"
-            "revoked_at FROM api_tokens WHERE org_id=?1 AND user_id=?2"
-            " ORDER BY id",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_int64(st, 1, r->org_id);
-    sqlite3_bind_int64(st, 2, r->sess->user_id);
-    while (sqlite3_step(st) == SQLITE_ROW) {
-        yyjson_mut_val *o = yyjson_mut_arr_add_obj(r->rdoc, items);
-        yyjson_mut_obj_add_int(r->rdoc, o, "id", sqlite3_column_int64(st, 0));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "label",
-                               sq(sqlite3_column_text(st, 1)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "scopes",
-                               sq(sqlite3_column_text(st, 2)));
-        yyjson_mut_obj_add_strcpy(r->rdoc, o, "created_at",
-                               sq(sqlite3_column_text(st, 3)));
-        if (sqlite3_column_type(st, 4) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "expires_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "expires_at",
-                                   sq(sqlite3_column_text(st, 4)));
-        if (sqlite3_column_type(st, 5) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "last_used_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "last_used_at",
-                                   sq(sqlite3_column_text(st, 5)));
-        if (sqlite3_column_type(st, 6) == SQLITE_NULL)
-            yyjson_mut_obj_add_null(r->rdoc, o, "revoked_at");
-        else
-            yyjson_mut_obj_add_strcpy(r->rdoc, o, "revoked_at",
-                                   sq(sqlite3_column_text(st, 6)));
-    }
-    sqlite3_finalize(st);
-    yyjson_mut_val *out = yyjson_mut_obj(r->rdoc);
-    yyjson_mut_obj_add_val(r->rdoc, out, "items", items);
-    return out;
+    return req_list(r,
+                    "SELECT id,label,scopes,created_at,expires_at,last_used_at,"
+                    "revoked_at FROM api_tokens WHERE org_id=?1 AND user_id=?2"
+                    " ORDER BY id",
+                    "id:i,label:s,scopes:s,created_at:s,expires_at:n,"
+                    "last_used_at:n,revoked_at:n",
+                    "ii", r->org_id, r->sess->user_id);
 }
 
 static yyjson_mut_val *h_token_revoke(struct req *r)
@@ -223,24 +167,16 @@ static yyjson_mut_val *h_token_revoke(struct req *r)
     char ts[32];
     util_iso8601(util_now(), ts, sizeof ts);
     int is_owner = strcmp(r->role, "owner") == 0;
-    sqlite3_stmt *st = NULL;
-    if (sqlite3_prepare_v2(
-            r->db,
-            is_owner
-                ? "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
-                : "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
-                  " AND user_id=?4",
-            -1, &st, NULL) != SQLITE_OK)
-        return db_error(r);
-    sqlite3_bind_text(st, 1, ts, -1, SQLITE_TRANSIENT);
-    sqlite3_bind_int64(st, 2, id);
-    sqlite3_bind_int64(st, 3, r->org_id);
-    if (!is_owner)
-        sqlite3_bind_int64(st, 4, r->sess->user_id);
-    int rc = sqlite3_step(st);
-    sqlite3_finalize(st);
-    if (rc != SQLITE_DONE)
-        return db_sqlite_error(r);
+    int rc = is_owner
+        ? req_exec(r,
+                   "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3",
+                   "sii", ts, id, r->org_id)
+        : req_exec(r,
+                   "UPDATE api_tokens SET revoked_at=?1 WHERE id=?2 AND org_id=?3"
+                   " AND user_id=?4",
+                   "siii", ts, id, r->org_id, r->sess->user_id);
+    if (rc != 0)
+        return NULL;
     if (sqlite3_changes(r->db) == 0)
         return fail(r, "NOT_FOUND", "token not found");
     audit_append(r->db, r->org_id, r->sess->user_id, id, "token.revoke", "{}",