Implemented most database queries.

1 files changed, 153 insertions, 4 deletions

diff --git a/pg.pgc b/pg.pgc
index e004dc0..fdf223e 100644
--- a/pg.pgc
+++ b/pg.pgc
@@ -1,3 +1,5 @@
+#include "pg.h"
+
 EXEC SQL WHENEVER SQLERROR CONTINUE;
 
 static inline int iserror() {
@@ -43,7 +45,8 @@ int pg_init() {
 	EXEC SQL CREATE TABLE channel (
 		id serial,
 		name varchar,
-		PRIMARY KEY (id)
+		PRIMARY KEY (id),
+		UNIQUE (name)
 	);
 	EXEC SQL CREATE TABLE channel_hours (
 		channel_id integer,
@@ -52,10 +55,19 @@ int pg_init() {
 		PRIMARY KEY (channel_id, time_index),
 		FOREIGN KEY (channel_id) REFERENCES channel (id)
 	);
+	EXEC SQL CREATE TABLE channel_file (
+		id serial,
+		channel_id integer,
+		path varchar,
+		lastpos integer default 0,
+		PRIMARY KEY (id),
+		FOREIGN KEY (channel_id) REFERENCES channel (id)
+	);
 
 	/* "user" is a reserved word */
 	EXEC SQL CREATE TABLE users (
 		id serial,
+		channel_id integer,
 		nick varchar,
 		words integer,
 		characters integer,
@@ -65,7 +77,8 @@ int pg_init() {
 		monologs integer,
 		seen_first integer,
 		seen_last integer,
-		PRIMARY KEY (id)
+		PRIMARY KEY (id),
+		FOREIGN KEY (channel_id) REFERENCES channel (id)
 	);
 	EXEC SQL CREATE TABLE user_hours (
 		user_id integer,
@@ -76,9 +89,10 @@ int pg_init() {
 	);
 
 	EXEC SQL CREATE TABLE word (
-		id serial PRIMARY KEY,
+		id serial,
 		name varchar,
-		count integer
+		count integer,
+		PRIMARY KEY (id)
 	);
 
 	EXEC SQL COMMIT;
@@ -111,4 +125,139 @@ int pg_upgrade() {
 
 	return !iserror();
 }
+
+EXEC SQL WHENEVER SQLERROR SQLPRINT;
+void pg_dropall() {
+	EXEC SQL DROP TABLE channel CASCADE;
+	EXEC SQL DROP TABLE channel_hours;
+	EXEC SQL DROP TABLE channel_file;
+	EXEC SQL DROP TABLE users CASCADE;
+	EXEC SQL DROP TABLE user_hours;
+	EXEC SQL DROP TABLE word;
+	EXEC SQL DROP TABLE schema;
+	EXEC SQL COMMIT;
+}
+
+EXEC SQL WHENEVER SQLERROR CONTINUE;
+int pg_channel_get(struct channel_t *channel) {
+	EXEC SQL BEGIN DECLARE SECTION;
+	const char *name = channel->name;
+	int id = 0;
+	EXEC SQL END DECLARE SECTION;
+
+	EXEC SQL SELECT id INTO :id FROM channel WHERE name = :name;
+
+	int do_insert = iserror() && testerror("02000");
+	if(iserror() && !do_insert) {
+		printerror();
+		sqlprint();
+	}
+
+	EXEC SQL COMMIT;
+
+	if(do_insert) {
+		EXEC SQL INSERT INTO channel (name) VALUES (:name) RETURNING id INTO :id;
+		EXEC SQL COMMIT;
+	}
+
+	return id;
+}
+
+long pg_channel_file_get(int channel_id, struct channel_file_t *file) {
+	EXEC SQL BEGIN DECLARE SECTION;
+	int id = channel_id;
+	const char *path = file->path;
+	long lastpos = 0;
+	EXEC SQL END DECLARE SECTION;
+
+	EXEC SQL SELECT lastpos INTO :lastpos FROM channel_file WHERE channel_id = :id AND path = :path;
+
+	int do_insert = iserror() && testerror("02000");
+	if(iserror() && !do_insert) {
+		printerror();
+		sqlprint();
+	}
+
+	EXEC SQL COMMIT;
+
+	if(do_insert) {
+		EXEC SQL INSERT INTO channel_file (channel_id, path) VALUES (:id, :path);
+		if(iserror()) sqlprint();
+		EXEC SQL COMMIT;
+	}
+
+	return lastpos;
+}
+
+void pg_channel_file_set(int channel_id, struct channel_file_t *file, long pos) {
+	EXEC SQL BEGIN DECLARE SECTION;
+	int id = channel_id;
+	const char *path = file->path;
+	long lastpos = pos;
+	EXEC SQL END DECLARE SECTION;
+
+	EXEC SQL UPDATE channel_file SET lastpos = :lastpos WHERE channel_id = :id AND path = :path;
+
+	EXEC SQL COMMIT;
+}
+
+void pg_user_set(int channel, struct user_t *user) {
+	EXEC SQL BEGIN DECLARE SECTION;
+	const char *nick = user->nick;
+	int channel_id = channel,
+		words = user->words,
+		characters = user->characters,
+		kicks = user->kicks,
+		kicked = user->kicked,
+		monolog_lines = user->monolog_lines,
+		monologs = user->monologs,
+		seen_first = user->seen_first,
+		seen_last = user->seen_last;
+	EXEC SQL END DECLARE SECTION;
+
+	EXEC SQL UPDATE users SET
+		words = :words,
+		characters = :characters,
+		kicks = :kicks,
+		kicked = :kicked,
+		monolog_lines = :monolog_lines,
+		monologs = :monologs,
+		seen_first = :seen_first,
+		seen_last = :seen_last
+		WHERE nick = :nick AND channel_id = :channel_id;
+
+	int do_insert = iserror() && testerror("02000");
+	if(iserror() && !do_insert) {
+		printerror();
+		sqlprint();
+	}
+
+	EXEC SQL COMMIT;
+
+	if(do_insert) {
+		EXEC SQL INSERT INTO users
+			(channel_id, nick, words, characters, kicks, kicked, monolog_lines, monologs, seen_first, seen_last)
+			VALUES (:channel_id, :nick, :words, :characters, :kicks, :kicked, :monolog_lines, :monologs, :seen_first, :seen_last);
+		if(iserror()) sqlprint();
+		EXEC SQL COMMIT;
+	}
+}
+
+void pg_user_get(int channel, struct user_t *user) {
+	EXEC SQL BEGIN DECLARE SECTION;
+	int channel_id = channel;
+	const char *stmt = "SELECT words, characters, kicks, kicked, monolog_lines, monologs, seen_first, seen_last "
+		"FROM users WHERE channel_id = ? AND nick = ?";
+	const char *nick = user->nick;
+	int words, characters, kicks, kicked, monolog_lines, monologs, seen_first, seen_last;
+	EXEC SQL END DECLARE SECTION;
+
+	EXEC SQL PREPARE userget FROM :stmt;
+	EXEC SQL EXECUTE userget INTO :words, :characters, :kicks, :kicked, :monolog_lines, :monologs, :seen_first, :seen_last
+		USING :channel_id, :nick;
+	EXEC SQL DEALLOCATE PREPARE userget;
+
+	if(iserror()) sqlprint();
+}
+
 /* vim: set syn=c: */