From 973edb08ed5ff35644328c244d5d584f44a70591 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?John=20Ankarstr=C3=B6m?= Date: Sat, 18 Sep 2021 11:58:56 +0200 Subject: Implement `add' functions --- .gitignore | 1 - Makefile | 15 +++---- README | 14 +++---- cforum.c | 35 ++++++++++++---- ctl.c | 37 +++++++++++++---- db.c | 137 ++++++++++++++++++++++++++++++++++++++++++++++++++++--------- db.h | 7 +++- query.c | 6 +-- t/front.t | 18 ++++++-- t/user.t | 10 +++++ 10 files changed, 221 insertions(+), 59 deletions(-) diff --git a/.gitignore b/.gitignore index b14d091..4fcead0 100644 --- a/.gitignore +++ b/.gitignore @@ -1,5 +1,4 @@ .DS_Store -.README db cforum mktpl/mktpl diff --git a/Makefile b/Makefile index 38d7adf..93275cb 100644 --- a/Makefile +++ b/Makefile @@ -15,20 +15,21 @@ clean: cforum: $(C) $(H) $(TPL) $(CC) $(CFLAGS) $(LDFLAGS) $(LDLIBS) -o cforum $(C) -README: .README - cp .README README - -.README: $(C) $(H) $(TPL) - .README awk '/^ / {if(!i++){print;system($$0"|sed s/^/\\ /")}exit} {print}' +README: $(C) $(H) $(TPL) + .README awk '/^ / {if(!i++){print;system($$0"|sed s/^/\\ /")}next} {i=0;print}' + mv .README README .t.tc: mktpl/mktpl <$< mktpl/mktpl >$@ db: + touch db + chown :www db + chmod g+w 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, full, hash NOT NULL, PRIMARY KEY (name));" - sqlite3 db "INSERT INTO users values('john', 'John Ankarström', '123');" + sqlite3 db "CREATE TABLE users(name, full, hash NOT NULL, created INT NOT NULL, PRIMARY KEY (name));" + sqlite3 db "INSERT INTO users values('john', 'John Ankarström', '123', 1462134896);" 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.');" sqlite3 db "INSERT INTO posts values(1, 1, 1462138896, NULL, 'Re: Hello World!', 'This is the second post!');" diff --git a/README b/README index 3b17846..a3352ba 100644 --- a/README +++ b/README @@ -8,21 +8,21 @@ C89, it can be run on practically any UNIX system. It is also rather small: wc -l *.c *.h */*.t */*.lex - 96 cforum.c - 99 ctl.c - 133 db.c + 113 cforum.c + 122 ctl.c + 230 db.c 10 err.c 106 query.c 3 ctl.h - 37 db.h + 40 db.h 0 err.h 14 query.h 2 site.h 3 t/err.t 1 t/foot.t - 14 t/front.t + 26 t/front.t 6 t/head.t 28 t/post.t - 2 t/user.t + 12 t/user.t 87 mktpl/mktpl.lex - 641 total + 803 total diff --git a/cforum.c b/cforum.c index 36782f0..900465a 100644 --- a/cforum.c +++ b/cforum.c @@ -1,22 +1,24 @@ +#include #include #include #include #include #include "ctl.h" /* Controllers. */ -#include "db.h" /* Database. Defines global variable db. */ +#include "db.h" /* Database functions. Defines global variable db. */ #include "err.h" /* HTTP errors. */ #include "site.h" /* Site settings. Defines global struct site. */ #include "query.h" /* Query functions. Defines global struct query. */ -#define MAXERR 300 +#define MAXMSG 300 int main(int argc, char *argv[]) { - char err[MAXERR], *p, *v; + char msg[MAXMSG], *p, *v; int attid, postid, userid; sqlite3_stmt *stmt; + struct att *att; /* * The database is opened or a server error is generated. @@ -24,10 +26,10 @@ main(int argc, char *argv[]) * assumed to be opened. */ if(sqlite3_open("db", &db) != SQLITE_OK){ - snprintf(err, MAXERR, + snprintf(msg, MAXMSG, "The database could not be opened: %s\n", sqlite3_errmsg(db)); - srverr(err); + srverr(msg); sqlite3_close(db); return 1; } @@ -39,24 +41,39 @@ main(int argc, char *argv[]) if(sqlite3_prepare(db, "SELECT value FROM settings WHERE key = 'name'", -1, &stmt, 0) != SQLITE_OK){ - snprintf(err, MAXERR, + snprintf(msg, MAXMSG, "The site name could not be retrieved: %s\n", sqlite3_errmsg(db)); - srverr(err); + srverr(msg); sqlite3_close(db); return 1; } if(sqlite3_step(stmt) == SQLITE_ROW) site.name = strdup((char *)sqlite3_column_text(stmt, 0)); else{ - snprintf(err, MAXERR, "The site name is not set.\n"); - srverr(err); + snprintf(msg, MAXMSG, "The site name is not set.\n"); + srverr(msg); sqlite3_finalize(stmt); sqlite3_close(db); return 1; } sqlite3_finalize(stmt); + /* + if(!(att = malloc(sizeof(struct att)))) + err(1, "malloc"); + att->post = 1; + att->name = strdup("example2"); + att->description = NULL; + att->mime = strdup("text/html"); + att->data = strdup("Hello!"); + att->bytes = strlen(att->data)+1; + + fprintf(stderr, "[add] %d %s\n", + addatt(att), sqlite3_errmsg(db)); + free(att); + */ + /* * The global struct query is set, or the program dies. * From now on, query is assumed to be set. diff --git a/ctl.c b/ctl.c index 757d159..bab653e 100644 --- a/ctl.c +++ b/ctl.c @@ -7,6 +7,7 @@ #include "query.h" #include "site.h" +/* Print UNIX timestamp as written date. */ void printdate(int timestamp) { @@ -24,7 +25,7 @@ showatt(id) { struct att *att; - if(!(att = getatt(byid("atts", id)))){ + if(!(att = getatt(selectbyint("atts", "oid", id)))){ srverr("Could not retrieve att"); return; } @@ -40,15 +41,22 @@ showfront() char *title; struct post *post; struct user *user; - sqlite3_stmt *stmt; + sqlite3_stmt *pstmt, *ustmt; if(sqlite3_prepare(db, "SELECT oid, * from posts ORDER BY created DESC", - -1, &stmt, 0) != SQLITE_OK){ + -1, &pstmt, 0) != SQLITE_OK){ srverr("Could not retrieve posts"); return; } + if(sqlite3_prepare(db, + "SELECT oid, * from users ORDER BY created DESC", + -1, &ustmt, 0) != SQLITE_OK){ + srverr("Could not retrieve users"); + return; + } + title = site.name; printf("Content-Type: text/html\n\n"); #include "t/front.tc" @@ -63,17 +71,17 @@ showpost(int id) struct user *user; sqlite3_stmt *stmt; - if(!(post = getpost(byid("posts", id)))){ + if(!(post = getpost(selectbyint("posts", "oid", id)))){ srverr("Could not retrieve post"); return; } - if(!(user = getuser(byid("users", post->user)))){ + if(!(user = getuser(selectbyint("users", "oid", post->user)))){ srverr("Could not retrieve post author"); return; } - stmt = byid("atts", id); + stmt = selectbyint("atts", "post", id); title = site.name; printf("Content-Type: text/html\n\n"); @@ -86,15 +94,30 @@ void showuser(int id) { char *title; + sqlite3_stmt *stmt; + struct post *post; struct user *user; - if(!(user = getuser(byid("users", id)))){ + if(!(user = getuser(selectbyint("users", "oid", id)))){ srverr("Could not retrieve user"); return; } + if(sqlite3_prepare(db, + "SELECT oid, * from posts WHERE user = ? ORDER BY created DESC", + -1, &stmt, 0) != SQLITE_OK) + goto err; + + if(sqlite3_bind_int(stmt, 1, id) != SQLITE_OK) + goto err; + title = site.name; printf("Content-Type: text/html\n\n"); #include "t/user.tc" free(user); + return; + +err: + srverr("Could not retrieve posts"); + return; } \ No newline at end of file diff --git a/db.c b/db.c index ced02bf..cf75ec6 100644 --- a/db.c +++ b/db.c @@ -3,27 +3,95 @@ #include #include #include +#include #include "db.h" -static char * textdup(const unsigned char *); +static char * strdupn(const unsigned char *); -sqlite3_stmt * -byid(char *table, int id) +/* + * The `add' functions insert an att/post/user struct into the database. + */ +int +addatt(struct att *att) { - char sql[100]; sqlite3_stmt *stmt; + + if(sqlite3_prepare(db, "INSERT INTO atts" + " (post, name, description, mime, data)" + " VALUES (?, ?, ?, ?, ?)", + -1, &stmt, 0) != SQLITE_OK) + goto err; - snprintf(sql, 100, "SELECT oid, * FROM %s WHERE oid = ?", table); + if(sqlite3_bind_int(stmt, 1, att->post) + != SQLITE_OK) + goto err; - 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_bind_text(stmt, 2, att->name, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; - return stmt; + if(sqlite3_bind_text(stmt, 3, att->description, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_bind_text(stmt, 4, att->mime, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_bind_blob(stmt, 5, att->data, att->bytes, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_step(stmt) != SQLITE_DONE) + goto err; + + sqlite3_finalize(stmt); + return 1; +err: + sqlite3_finalize(stmt); + return 0; } +int +adduser(struct user *user) +{ + sqlite3_stmt *stmt; + + if(sqlite3_prepare(db, "INSERT INTO users" + " (name, full, hash, created)" + " VALUES (?, ?, ?, ?)", + -1, &stmt, 0) != SQLITE_OK) + goto err; + + if(sqlite3_bind_text(stmt, 1, user->name, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_bind_text(stmt, 2, user->full, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_bind_text(stmt, 3, user->hash, -1, SQLITE_STATIC) + != SQLITE_OK) + goto err; + + if(sqlite3_bind_int(stmt, 4, time(NULL)) != SQLITE_OK) + goto err; + + if(sqlite3_step(stmt) != SQLITE_DONE) + goto err; + + sqlite3_finalize(stmt); + return 1; +err: + sqlite3_finalize(stmt); + return 0; +} + +/* + * The `get' functions retrieve an att/post/user struct once, + * after which the statement is automatically finalized. + */ struct att * getatt(sqlite3_stmt *stmt) { @@ -51,6 +119,11 @@ getuser(sqlite3_stmt *stmt) return user; } +/* + * The `next' functions create an att/post/user struct by querying + * the database with the given stmt. They may be called multiple times + * with the same stmt to retrieve multiple rows. + */ struct att * nextatt(sqlite3_stmt *stmt) { @@ -67,9 +140,9 @@ nextatt(sqlite3_stmt *stmt) att->id = sqlite3_column_int(stmt, 0); att->post = sqlite3_column_int(stmt, 1); - att->name = textdup(sqlite3_column_text(stmt, 2)); - att->description = textdup(sqlite3_column_text(stmt, 3)); - att->mime = textdup(sqlite3_column_text(stmt, 4)); + att->name = strdupn(sqlite3_column_text(stmt, 2)); + att->description = strdupn(sqlite3_column_text(stmt, 3)); + att->mime = strdupn(sqlite3_column_text(stmt, 4)); att->bytes = sqlite3_column_bytes(stmt, 5); if(!(att->data = malloc(att->bytes))) @@ -99,8 +172,8 @@ nextpost(sqlite3_stmt *stmt) post->user = sqlite3_column_int(stmt, 2); post->created = sqlite3_column_int(stmt, 3); post->edited = sqlite3_column_int(stmt, 4); - post->subject = textdup(sqlite3_column_text(stmt, 5)); - post->text = textdup(sqlite3_column_text(stmt, 6)); + post->subject = strdupn(sqlite3_column_text(stmt, 5)); + post->text = strdupn(sqlite3_column_text(stmt, 6)); return post; } @@ -120,15 +193,39 @@ nextuser(sqlite3_stmt *stmt) err(1, "malloc"); user->id = sqlite3_column_int(stmt, 0); - user->name = textdup(sqlite3_column_text(stmt, 1)); - user->full = textdup(sqlite3_column_text(stmt, 2)); - user->hash = textdup(sqlite3_column_text(stmt, 3)); + user->name = strdupn(sqlite3_column_text(stmt, 1)); + user->full = strdupn(sqlite3_column_text(stmt, 2)); + user->hash = strdupn(sqlite3_column_text(stmt, 3)); + user->created = sqlite3_column_int(stmt, 4); return user; } +/* + * Create a statement that selects from a given table on a given integer. + * The returned statement must eventually be finalized by calling + * sqlite3_finalize(sqlite3_stmt *). + */ +sqlite3_stmt * +selectbyint(char *table, char *field, int i) +{ + char sql[100]; + sqlite3_stmt *stmt; + + snprintf(sql, 100, "SELECT oid, * FROM %s WHERE %s = ?", table, field); + + if(sqlite3_prepare(db, sql, -1, &stmt, 0) != SQLITE_OK) + return NULL; + + if(sqlite3_bind_int(stmt, 1, i) != SQLITE_OK) + return NULL; + + return stmt; +} + +/* Return an allocated copy of string unless NULL. */ char * -textdup(const unsigned char *s) +strdupn(const unsigned char *s) { return s? strdup((char *)s): NULL; } \ No newline at end of file diff --git a/db.h b/db.h index 4d956f9..d6f229d 100644 --- a/db.h +++ b/db.h @@ -24,15 +24,18 @@ struct post{ struct user{ int id; + int created; char *name; char *full; char *hash; }; -sqlite3_stmt *byid(char *, int); +int addatt(struct att *); +int adduser(struct user *); struct att *getatt(sqlite3_stmt *); struct post *getpost(sqlite3_stmt *); struct user *getuser(sqlite3_stmt *); struct att *nextatt(sqlite3_stmt *); struct post *nextpost(sqlite3_stmt *); -struct user *nextuser(sqlite3_stmt *); \ No newline at end of file +struct user *nextuser(sqlite3_stmt *); +sqlite3_stmt *selectbyint(char *, char *, int); \ No newline at end of file diff --git a/query.c b/query.c index 9847f35..7947d59 100644 --- a/query.c +++ b/query.c @@ -5,9 +5,9 @@ #include "query.h" /* - * Return allocated string containing next query string parameter - * ("key=value"). The string is truncated to max characters. If - * truncation occurred, the -1th character of the string is set to 1. + * Return an allocated string containing the next query string parameter + * ("key=value"). The string is truncated to max characters. If truncation + * occurred, the -1th character of the string is set to 1. */ char * nextparam(enum method method, int max) diff --git a/t/front.t b/t/front.t index a12d712..9d39469 100644 --- a/t/front.t +++ b/t/front.t @@ -2,14 +2,26 @@

<%= site.name %>

Latest posts

- <% while(post = nextpost(stmt)){ - user = getuser(byid("users", post->user)); %> + <% while(post = nextpost(pstmt)){ + user = getuser(selectbyint("users", "oid", post->user)); %> - <% free(post); + <% free(user); + free(post); + } %> +
id); %>"><%= post->subject %> user); %>"><%= user->name %> <% printdate(post->created); %>
+

Latest users

+ + <% while(user = nextuser(ustmt)){ %> + + + + + + <% free(user); } %>
id); %>"><%= user->name %><%= user->full %><% printdate(user->created); %>
<% #include "foot.tc" %> \ No newline at end of file diff --git a/t/user.t b/t/user.t index af7c8cd..b69633a 100644 --- a/t/user.t +++ b/t/user.t @@ -1,3 +1,13 @@ <% #include "head.tc" %>

User <% printf("%d", id); %>: <%= user->name %>

+

Latest posts

+ + <% while(post = nextpost(stmt)){ %> + + + + + <% free(post); + } %> +
id); %>"><%= post->subject %><% printdate(post->created); %>
<% #include "foot.tc" %> \ No newline at end of file -- cgit v1.2.3