From 3e400c3df5572524b7ba08b030f45eb3ba6b7734 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?John=20Ankarstr=C3=B6m?= Date: Fri, 17 Sep 2021 22:08:50 +0200 Subject: Add database helper functions --- Makefile | 8 ++++---- cforum.c | 2 +- ctl.c | 39 +++++++++++++++++++++++++++++++++++-- db.c | 66 +++++++++++++++++++++++++++++++++++++++++++++++---------------- db.h | 16 +++++++++++++++- misc.c | 19 ++++++++++++++++++ misc.h | 1 + t/post.t | 8 +++++++- t/post.tc | 14 +++++++++++++- 9 files changed, 147 insertions(+), 26 deletions(-) create mode 100644 misc.c create mode 100644 misc.h diff --git a/Makefile b/Makefile index 4e788a9..f6fdc96 100644 --- a/Makefile +++ b/Makefile @@ -13,7 +13,7 @@ cforum: $(C) $(TPL) db: sqlite3 db "CREATE TABLE settings(key, value, PRIMARY KEY (key));" sqlite3 db "INSERT INTO settings values('name', 'C Forum');" - sqlite3 db "CREATE TABLE users(name, hash NOT NULL, PRIMARY KEY (name));" - sqlite3 db "INSERT INTO users values('john', '123');" - sqlite3 db "CREATE TABLE posts(user INT NOT NULL, created INT NOT NULL, edited INT, subject NOT NULL, text NOT NULL, FOREIGN KEY (user) REFERENCES users(oid));" - sqlite3 db "INSERT INTO posts values(1, 0, NULL, 'Hello World!', 'This is the first post.');" \ No newline at end of file + sqlite3 db "CREATE TABLE users(name, full, hash NOT NULL, PRIMARY KEY (name));" + sqlite3 db "INSERT INTO users values('john', 'John Ankarström', '123');" + sqlite3 db "CREATE TABLE posts(parent INT, user INT NOT NULL, created INT NOT NULL, edited INT, subject NOT NULL, text NOT NULL, FOREIGN KEY (user) REFERENCES users(oid));" + sqlite3 db "INSERT INTO posts values(NULL, 1, 1462137896, NULL, 'Hello World!', 'This is the first post.');" \ No newline at end of file diff --git a/cforum.c b/cforum.c index 80cc424..f29d423 100644 --- a/cforum.c +++ b/cforum.c @@ -47,7 +47,7 @@ main(int argc, char *argv[]) return 1; } if(sqlite3_step(stmt) == SQLITE_ROW) - site.name = strdup(sqlite3_column_text(stmt, 0)); + site.name = strdup((char *)sqlite3_column_text(stmt, 0)); else{ snprintf(err, MAXERR, "The site name is not set.\n"); srverr(err); diff --git a/ctl.c b/ctl.c index 1a3627c..c5f90f4 100644 --- a/ctl.c +++ b/ctl.c @@ -1,9 +1,28 @@ #include +#include #include +#include #include "db.h" #include "query.h" #include "site.h" +#define PFREE(s) do{ printf("%s", s); free(s); }while(0) + +char * +date(int timestamp) +{ + char *buf; + struct tm *t; + + if(!(buf = malloc(20))) + err(1, "malloc"); + + t = localtime((time_t *)×tamp); + strftime(buf, 20, "%Y-%m-%d %H:%M", t); + + return buf; +} + void showfront() { @@ -20,6 +39,18 @@ void showpost(int id) { char *title; + struct post *post; + struct user *user; + + if(!(post = getpost(id))){ + srverr("Could not retrieve post"); + return; + } + + if(!(user = getuser(post->user))){ + srverr("Could not retrieve post author"); + return; + } title = site.name; printf("Content-Type: text/html\n\n"); @@ -31,10 +62,14 @@ showpost(int id) void showuser(int id) { - char *title; + char s[128], *title; struct user *user; - user = getuser(id); + if(!(user = getuser(id))){ + srverr("Could not retrieve user"); + return; + } + title = site.name; printf("Content-Type: text/html\n\n"); #include "t/head.tc" diff --git a/db.c b/db.c index fcb742d..bd2de17 100644 --- a/db.c +++ b/db.c @@ -1,36 +1,70 @@ #include #include +#include #include #include "db.h" +sqlite3_stmt * +byid(int id, char *sql) +{ + sqlite3_stmt *stmt; + + if(sqlite3_prepare(db, sql, -1, &stmt, 0) != SQLITE_OK) + return NULL; + + if(sqlite3_bind_int(stmt, 1, id) != SQLITE_OK) + return NULL; + + if(sqlite3_step(stmt) != SQLITE_ROW) + return NULL; + + return stmt; +} + +struct post * +getpost(int id) +{ + sqlite3_stmt *stmt; + struct post *post; + + if(!(stmt = byid(id, "SELECT parent, user, created, edited, subject, text FROM posts WHERE oid = ?"))){ + sqlite3_finalize(stmt); + return NULL; + } + + if(!(post = malloc(sizeof(struct post)))) + err(1, "malloc"); + + post->parent = sqlite3_column_int(stmt, 0); + post->user = sqlite3_column_int(stmt, 1); + post->created = sqlite3_column_int(stmt, 2); + post->edited = sqlite3_column_int(stmt, 3); + post->subject = strdup((char *)sqlite3_column_text(stmt, 4)); + post->text = strdup((char *)sqlite3_column_text(stmt, 5)); + + sqlite3_finalize(stmt); + return post; +} + struct user * getuser(int id) { sqlite3_stmt *stmt; struct user *user; - - if(sqlite3_prepare(db, - "SELECT name, hash FROM users WHERE oid = ?", - -1, &stmt, 0) != SQLITE_OK) - goto null; - if(sqlite3_bind_int(stmt, 1, id) != SQLITE_OK) - goto null; - - if(sqlite3_step(stmt) != SQLITE_ROW) - goto null; + if(!(stmt = byid(id, "SELECT name, full, hash FROM users WHERE oid = ?"))){ + sqlite3_finalize(stmt); + return NULL; + } if(!(user = malloc(sizeof(struct user)))) err(1, "malloc"); user->id = id; - user->name = sqlite3_column_text(stmt, 0); - user->hash = sqlite3_column_text(stmt, 1); + user->name = strdup((char *)sqlite3_column_text(stmt, 0)); + user->full = strdup((char *)sqlite3_column_text(stmt, 1)); + user->hash = strdup((char *)sqlite3_column_text(stmt, 2)); sqlite3_finalize(stmt); return user; - -null: - sqlite3_finalize(stmt); - return NULL; } \ No newline at end of file diff --git a/db.h b/db.h index 629cb3b..a70e156 100644 --- a/db.h +++ b/db.h @@ -1,9 +1,23 @@ #include +sqlite3 *db; + +struct post{ + int id; + int parent; + int user; + int created; + int edited; + char *subject; + char *text; +}; + struct user{ int id; char *name; + char *full; char *hash; }; -sqlite3 *db; \ No newline at end of file +struct post *getpost(int); +struct user *getuser(int); \ No newline at end of file diff --git a/misc.c b/misc.c new file mode 100644 index 0000000..e5bdeef --- /dev/null +++ b/misc.c @@ -0,0 +1,19 @@ +#include +#include +#include +#include "misc.h" + +char * +date(int timestamp) +{ + char *buf; + time_t *t; + + if(!(buf = malloc(20))) + err(1, "malloc"); + + t = localtime(×tamp); + strftime(buf, 20, "%Y-%m-%d %H:%M", t); + + return buf; +} \ No newline at end of file diff --git a/misc.h b/misc.h new file mode 100644 index 0000000..733c393 --- /dev/null +++ b/misc.h @@ -0,0 +1 @@ +char *date(int); \ No newline at end of file diff --git a/t/post.t b/t/post.t index a74f29a..56d3c37 100644 --- a/t/post.t +++ b/t/post.t @@ -1 +1,7 @@ -

Post <% printf("%d", id); %>

+

Post <% printf("%d", id); %>: <%= post->subject %>

+

From: <%= user->full %> +<id); %>"><%= user->name %>> +
Date: <% PFREE(date(post->created)); %> +

+<%= post->text %>
+
\ No newline at end of file diff --git a/t/post.tc b/t/post.tc index 7c36950..2f81de0 100644 --- a/t/post.tc +++ b/t/post.tc @@ -1,3 +1,15 @@ printf("

Post "); printf("%d", id); -printf("

\n"); +printf(": "); +printf("%s", post->subject ); +printf("\n

From: "); +printf("%s", user->full ); +printf("\n<id); +printf("\">"); +printf("%s", user->name ); +printf(">\n
Date: "); +PFREE(date(post->created)); +printf("\n

\n");
+printf("%s",  post->text );
+printf("\n
"); -- cgit v1.2.3