From 021c604080f178d5dcb261e9659f8a59cb8581a0 Mon Sep 17 00:00:00 2001 From: Olaf Wintermann Date: Fri, 2 May 2025 19:29:13 +0200 Subject: [PATCH] new notes are also added to the notes table the notes list only contains resources that also have a notes entry --- application/store.c | 17 ++++++++++++++--- application/store_sqlite.c | 9 +++++++++ 2 files changed, 23 insertions(+), 3 deletions(-) diff --git a/application/store.c b/application/store.c index ad88c97..6b92ff6 100644 --- a/application/store.c +++ b/application/store.c @@ -56,11 +56,13 @@ "select * from cols\n" \ "order by path;" -#define SQL_NOTEBOOK_GET_NOTES "select resource_id, parent_id, nodename, displayname, lastmodified, creationdate, iscollection, contenttype from resources where parent_id = ? ;" +#define SQL_NOTEBOOK_GET_NOTES "select r.resource_id, r.parent_id, r.nodename, r.displayname, r.lastmodified, r.creationdate, r.iscollection, r.contenttype from resources r inner join notes n on r.resource_id = n.resource_id where parent_id = ? ;" #define SQL_NOTE_GET_CONTENT "select content from resources where resource_id = ? ;" -#define SQL_NOTE_NEW "insert into resources(parent_id, nodename, displayname, lastmodified, creationdate, content) values (?, ?, ?, unixepoch(), unixepoch(), ?) returning resource_id;" +#define SQL_NOTE_RESOURCE_NEW "insert into resources(parent_id, nodename, displayname, lastmodified, creationdate, content) values (?, ?, ?, unixepoch(), unixepoch(), ?) returning resource_id;" + +#define SQL_NOTE_NEW "insert into notes(resource_id, type) values (?, ?);" #define SQL_NOTE_SAVE "update resources set nodename = ? ," \ "displayname = ? ," \ @@ -501,7 +503,7 @@ static void uithr_save_note_finished(UiEvent *event, SaveNoteJob *job) { static int qthr_new_note(SaveNoteJob *job) { Resource *n = job->note; DBUQuery *q = connection->createQuery(connection, NULL); - dbuQuerySetSQL(q, SQL_NOTE_NEW); + dbuQuerySetSQL(q, SQL_NOTE_RESOURCE_NEW); dbuQuerySetParamInt64(q, 1, n->parent_id); dbuQuerySetParamString(q, 2, cx_str(n->displayname)); dbuQuerySetParamString(q, 3, cx_str(n->displayname)); @@ -516,6 +518,15 @@ static int qthr_new_note(SaveNoteJob *job) { long long new_id = 0; cx_strtoll(new_id_str, &new_id, 10); n->resource_id = new_id; + + DBUQuery *q2 = connection->createQuery(connection, NULL); + dbuQuerySetSQL(q2, SQL_NOTE_NEW); + dbuQuerySetParamInt64(q2, 1, new_id); + dbuQuerySetParamInt64(q2, 2, 0); + if(dbuQueryExec(q2)) { + job->error = 3; + } + dbuQueryFree(q2); } } else { job->error = 2; diff --git a/application/store_sqlite.c b/application/store_sqlite.c index 396fa39..fc2cc0d 100644 --- a/application/store_sqlite.c +++ b/application/store_sqlite.c @@ -70,6 +70,14 @@ "foreign key (parent_id) references resources(resource_id), " \ "unique (parent_id, nodename) " \ ");" +#define SQL_CREATE_TABLE_NOTES "create table notes( " \ + "note_id integer primary key, " \ + "resource_id integer, " \ + "type integer, " \ + "status text, " \ + "targetdate integer, " \ + "foreign key (resource_id) references resources(resource_id) " \ + ");" #define SQL_CREATE_TABLE_ATTACHMENTS "create table attachments( " \ "attachment_id integer primary key, " \ "attachment_resource_id integer, " \ @@ -85,6 +93,7 @@ int store_sqlite_init_db(DBUConnection *connection) { SQL_CREATE_TABLE_REPOSITORIES, SQL_CREATE_TABLE_USER_SETTINGS, SQL_CREATE_TABLE_RESOURCES, + SQL_CREATE_TABLE_NOTES, SQL_CREATE_TABLE_ATTACHMENTS }; int nsql = sizeof(sql) / sizeof(char*); -- 2.43.5