path: root/pg.pgc

#include "pg.h"

EXEC SQL WHENEVER SQLERROR CONTINUE;

static inline int iserror() {
	return !(SQLSTATE[0] == '0' && SQLSTATE[1] == '0');
}

static inline void printerror() {
	printf("%c%c%c%c%c\n", SQLSTATE[0], SQLSTATE[1], SQLSTATE[2], SQLSTATE[3], SQLSTATE[4]);
}

static inline int testerror(const char *code) {
	for(int i = 0; i < 5; i++) {
		if(code[i] != SQLSTATE[i])
			return 0;
	}
	return 1;
}

int pg_connect(const char *string) {
	EXEC SQL BEGIN DECLARE SECTION;
	const char *target = string;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL CONNECT TO :target;

	if(iserror()) {
		sqlprint();
		printerror();
	}

	return !iserror();
}

void pg_disconnect() {
	EXEC SQL DISCONNECT;
}

int pg_init() {
	EXEC SQL WHENEVER SQLERROR GOTO pg_init_error;
	EXEC SQL CREATE TABLE schema (version INTEGER);
	EXEC SQL INSERT INTO schema (version) VALUES (1);

	EXEC SQL CREATE TABLE channel (
		id serial,
		name varchar,
		PRIMARY KEY (id),
		UNIQUE (name)
	);
	EXEC SQL CREATE TABLE channel_file (
		id serial,
		channel_id integer,
		path varchar,
		lastpos integer default 0,
		lasttime integer,
		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,
		kicks integer,
		kicked integer,
		monolog_lines integer,
		monologs integer,
		seen_first integer,
		seen_last integer,
		PRIMARY KEY (id),
		FOREIGN KEY (channel_id) REFERENCES channel (id)
	);
	EXEC SQL CREATE TABLE user_hours (
		user_id integer,
		time_index integer,
		lines integer,
		PRIMARY KEY (user_id, time_index),
		FOREIGN KEY (user_id) REFERENCES users (id)
	);

	EXEC SQL CREATE TABLE word (
		id serial,
		channel_id integer,
		name varchar,
		count integer,
		PRIMARY KEY (id),
		FOREIGN KEY (channel_id) REFERENCES channel (id)
	);

	EXEC SQL COMMIT;

	return 1;

pg_init_error:

	sqlprint();
	return 0;
}

EXEC SQL WHENEVER SQLERROR CONTINUE;
int pg_upgrade() {
	EXEC SQL BEGIN DECLARE SECTION;
	int version = 0;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL SELECT version INTO :version FROM schema;
	if(testerror("02000")) {
		EXEC SQL INSERT INTO schema (version) VALUES (1);
	} else if(iserror()) {
		sqlprint();
		return 0;
	}

	printf("current schema version: %d\n", version);

	EXEC SQL COMMIT;

	return !iserror();
}

EXEC SQL WHENEVER SQLERROR SQLPRINT;
void pg_dropall() {
	EXEC SQL DROP TABLE channel CASCADE;
	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;
}

void pg_channel_file_get(int channel_id, struct channel_file_t *file, long *pos, time_t *time) {
	EXEC SQL BEGIN DECLARE SECTION;
	int id = channel_id;
	const char *path = file->path;
	long lastpos = 0;
	int lasttime = 0;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL SELECT lastpos, lasttime INTO :lastpos, :lasttime 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;
	} else {
		*pos = lastpos;
		*time = lasttime;
	}
}

void pg_channel_file_set(int channel_id, struct channel_file_t *file, long pos, time_t time) {
	EXEC SQL BEGIN DECLARE SECTION;
	int id = channel_id;
	const char *path = file->path;
	long lastpos = pos;
	int lasttime = time;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL UPDATE channel_file SET lastpos = :lastpos, lasttime = :lasttime 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 id = user->id,
		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;

	if(id) {
		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 id = :id AND channel_id = :channel_id;

		for(int ti = 0; ti < 24*4; ti++) {
			EXEC SQL BEGIN DECLARE SECTION;
			int lines = user->lines[ti],
				index = ti;
			EXEC SQL END DECLARE SECTION;
			EXEC SQL UPDATE user_hours SET lines = :lines WHERE user_id = :id AND time_index = :index;
		}
	} else {
		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)
			RETURNING id INTO :id;

		for(int ti = 0; ti < 24*4; ti++) {
			EXEC SQL BEGIN DECLARE SECTION;
			int index = ti,
				lines = user->lines[ti];
			EXEC SQL END DECLARE SECTION;
			EXEC SQL INSERT INTO user_hours (user_id, time_index, lines) VALUES (:id, :index, :lines);
		}
	}

	EXEC SQL COMMIT;
}

EXEC SQL WHENEVER SQLERROR SQLPRINT;
void pg_users_get(int channel) {
	EXEC SQL BEGIN DECLARE SECTION;
	int channel_id = channel;
	VARCHAR nick[0x100];
	int id = 0, words, characters, kicks, kicked, monolog_lines, monologs, seen_first, seen_last, index, lines;
	EXEC SQL END DECLARE SECTION;

	EXEC SQL DECLARE usercursor CURSOR FOR SELECT id, nick, words, characters, kicks, kicked, monolog_lines, monologs, seen_first, seen_last
		FROM users WHERE channel_id = :channel_id;

	EXEC SQL OPEN usercursor;

	EXEC SQL FETCH NEXT FROM usercursor INTO :id, :nick, :words, :characters, :kicks, :kicked, :monolog_lines, :monologs, :seen_first, :seen_last;
	if(iserror())
		goto pg_users_get_close;

	do {
		struct user_t *user = user_get(nick.arr);
		user->id = id;
		user->words = words;
		user->characters = characters;
		user->kicks = kicks;
		user->kicked = kicked;
		user->monolog_lines = monolog_lines;
		user->monologs = monologs;
		user->seen_first = seen_first;
		user->seen_last = seen_last;

		EXEC SQL DECLARE userlinescursor CURSOR FOR SELECT time_index, lines FROM user_hours WHERE user_id = :id;
		EXEC SQL OPEN userlinescursor;
		EXEC SQL FETCH NEXT FROM userlinescursor INTO :index, :lines;

		if(iserror())
			goto pg_userlines_error;

		do {
			user->lines[index] = lines;
			EXEC SQL FETCH NEXT FROM userlinescursor INTO :index, :lines;
		} while(!iserror());

pg_userlines_error:
		EXEC SQL CLOSE userlinescursor;

		EXEC SQL FETCH NEXT FROM usercursor INTO :id, :nick, :words, :characters, :kicks, :kicked, :monolog_lines, :monologs, :seen_first, :seen_last;
	} while(!iserror());

pg_users_get_close:

	EXEC SQL CLOSE usercursor;
}

EXEC SQL WHENEVER SQLERROR CONTINUE;
void pg_word_set(int channel, struct word_t *word) {
	EXEC SQL BEGIN DECLARE SECTION;
	int channel_id = channel,
		id = word->id,
		count = word->count;
	const char *name = word->name;
	EXEC SQL END DECLARE SECTION;

	if(id) {
		EXEC SQL UPDATE word SET count = :count WHERE channel_id = :channel_id AND id = :id;
	} else {
		EXEC SQL INSERT INTO word (channel_id, name, count) VALUES (:channel_id, :name, :count);
	}

	EXEC SQL COMMIT;
}

void pg_words_get(int channel) {
	EXEC SQL BEGIN DECLARE SECTION;
	int channel_id = channel, id = 0, count;
	VARCHAR name[0x400];
	EXEC SQL END DECLARE SECTION;

	EXEC SQL DECLARE wordcursor CURSOR FOR SELECT id, name, count FROM word WHERE channel_id = :channel_id;

	EXEC SQL OPEN wordcursor;

	EXEC SQL FETCH NEXT FROM wordcursor INTO :id, :name, :count;
	if(iserror())
		goto pg_word_get_close;

	do {
		struct word_t *word = word_get(name.arr);
		word->id = id;
		word->count = count;
		EXEC SQL FETCH NEXT FROM wordcursor INTO :id, :name, :count;
	} while(iserror());

pg_word_get_close:

	EXEC SQL CLOSE wordcursor;
}

/* vim: set syn=c: */