From 72fbf9ff67b82d8576bdd2633e3227230ca83ad0 Mon Sep 17 00:00:00 2001 From: Tristan Riehs Date: Fri, 14 Nov 2025 20:14:02 +0100 Subject: Progress on ftag query Query works for a single tag. Multi-tag SQL query looks fine but does not work. --- src/main.c | 39 ++++++++++++++++++++++++++++----------- 1 file changed, 28 insertions(+), 11 deletions(-) (limited to 'src') diff --git a/src/main.c b/src/main.c index 6bdc28e..2f85c44 100644 --- a/src/main.c +++ b/src/main.c @@ -393,6 +393,29 @@ static int print_all_callback(void *_print_header, int count, char **cols, char return 0; } +/* Build the "join" part of the SQL query for the "ftag query" command. */ +static void ftag_query_join(sqlite3 *db, char *sql, int max_len, + char **tags, int tag_count) +{ + assert(tag_count >= 0); + if (tag_count == 0) { + strncat(sql, "\t(SELECT id,canonical_name,full_name FROM files)\n", + max_len - strlen(sql)); + assert(strlen(sql) <= max_len); + return; + } + char *prefix = "\t(SELECT id,canonical_name,full_name FROM file_tags JOIN\n"; + strncat(sql, prefix, max_len - strlen(sql)); + assert(strlen(sql) <= max_len); + ftag_query_join(db, sql, max_len, tags, tag_count-1); + char suffix[128]; + memset(suffix, 0, sizeof(suffix)); + snprintf(suffix, sizeof(suffix)-1, + "\t\tON id = file\n\t\tWHERE tag = %d)\n", tag_count-1); + strncat(sql, suffix, max_len - strlen(sql)); + assert(strlen(sql) <= max_len); +} + static void ftag_query(int argc, char **argv) { if (argc == 0) { @@ -427,22 +450,16 @@ static void ftag_query(int argc, char **argv) argv += 2; } char sql[4096]; - char sql_join[2048]; sqlite3 *db; int rc; rc = sqlite3_open(DATABASE_PATH, &db); sqlite3_check(rc, db); - for (int join_idx = 0; join_idx < tag_count; join_idx++) { - int tag_id = get_id_by_col(db, "tags", "name", tags[join_idx]); - memset(sql_join, 0, sizeof(sql_join)); - snprintf(sql_join, sizeof(sql_join)-1, - "\t(SELECT id,canonical_name,full_name\n" - " FROM (files JOIN file_tags ON id = file)\n" - " WHERE tag = %d)\n", tag_id); - strcat(sql, sql_join); - } + char sql_join[2048]; + memset(sql_join, 0, sizeof(sql_join)); + ftag_query_join(db, sql_join, sizeof(sql_join)-1, tags, tag_count); memset(sql, 0, sizeof(sql)); - snprintf(sql, sizeof(sql)-1, "SELECT id,canonical_name,full_name FROM (\n%s);\n", sql_join); + snprintf(sql, sizeof(sql)-1, + "SELECT id,canonical_name,full_name FROM (\n%s);\n", sql_join); printf("%s: debug: SQL query is:\n%s", __func__, sql); int print_header = 1; rc = sqlite3_exec(db, sql, print_all_callback, &print_header, NULL); -- cgit v1.2.3