Add database helper functions

9 files changed, 147 insertions, 26 deletions

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 <stdio.h>
+#include <stdlib.h>
 #include <string.h>
+#include <time.h>
 #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 *)&timestamp);
+	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 <err.h>
 #include <stdlib.h>
+#include <string.h>
 #include <sqlite3.h>
 #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.h>
 
+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 <err.h>
+#include <time.h>
+#include <stdio.h>
+#include "misc.h"
+
+char *
+date(int timestamp)
+{
+	char *buf;
+	time_t *t;
+	
+	if(!(buf = malloc(20)))
+		err(1, "malloc");
+	
+	t = localtime(&timestamp);
+	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 @@
-<h1>Post <% printf("%d", id); %></h1>
+<h1>Post <% printf("%d", id); %>: <%= post->subject %></h1>
+<p>From: <%= user->full %>
+&lt;<a href="?user=<% printf("%d", user->id); %>"><%= user->name %></a>&gt;
+<br>Date: <% PFREE(date(post->created)); %>
+<pre>
+<%= post->text %>
+</pre>
\ 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("<h1>Post ");
 printf("%d", id); 
-printf("</h1>\n");
+printf(": ");
+printf("%s",  post->subject );
+printf("</h1>\n<p>From: ");
+printf("%s",  user->full );
+printf("\n&lt;<a href=\"?user=");
+printf("%d", user->id); 
+printf("\">");
+printf("%s",  user->name );
+printf("</a>&gt;\n<br>Date: ");
+PFREE(date(post->created)); 
+printf("\n<pre>\n");
+printf("%s",  post->text );
+printf("\n</pre>");