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_hours (
		channel_id integer,
		time_index integer,
		lines integer,
		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,
		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,
		name varchar,
		count integer,
		PRIMARY KEY (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_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: */