Implement `add' functions

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 >.README awk '/^	/ {if(!i++){print;system($$0"|sed s/^/\\	/")}exit} {print}'
+README: $(C) $(H) $(TPL)
+	<README >.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 <err.h>
 #include <sqlite3.h>
 #include <stdio.h>
 #include <stdlib.h>
 #include <string.h>
 
 #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,18 +41,18 @@ 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;
@@ -58,6 +60,21 @@ main(int argc, char *argv[])
 	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 <stdio.h>
 #include <string.h>
 #include <sqlite3.h>
+#include <time.h>
 #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 @@
 <h1><%= site.name %></h1>
 <h3>Latest posts</h3>
 <table border="1">
-	<% while(post = nextpost(stmt)){
-		user = getuser(byid("users", post->user)); %>
+	<% while(post = nextpost(pstmt)){
+		user = getuser(selectbyint("users", "oid", post->user)); %>
 	<tr>
 		<td><a href="?post=<% printf("%d", post->id); %>"><%= post->subject %></a></td>
 		<td><a href="?user=<% printf("%d", post->user); %>"><%= user->name %></a></td>
 		<td><% printdate(post->created); %></td>
 	</tr>
-	<% free(post);
+	<% free(user);
+	free(post);
+	} %>
+</table>
+<h3>Latest users</h3>
+<table border="1">
+	<% while(user = nextuser(ustmt)){ %>
+	<tr>
+		<td><a href="?user=<% printf("%d", user->id); %>"><%= user->name %></a></td>
+		<td><%= user->full %></td>
+		<td><% printdate(user->created); %></td>
+	</tr>
+	<% free(user);
 	} %>
 </table>
 <% #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" %>
 <h1>User <% printf("%d", id); %>: <%= user->name %></h1>
+<h3>Latest posts</h3>
+<table border="1">
+	<% while(post = nextpost(stmt)){ %>
+	<tr>
+		<td><a href="?post=<% printf("%d", post->id); %>"><%= post->subject %></a></td>
+		<td><% printdate(post->created); %></td>
+	</tr>
+	<% free(post);
+	} %>
+</table>
 <% #include "foot.tc" %>
\ No newline at end of file