summaryrefslogtreecommitdiff
path: root/pg.pgc
diff options
context:
space:
mode:
authorJon Bergli Heier <snakebite@jvnv.net>2010-11-15 23:39:05 +0100
committerJon Bergli Heier <snakebite@jvnv.net>2010-11-15 23:39:05 +0100
commit5af90fb735d357a729d02a661c92af6e865b6d3a (patch)
treea9af27348dacab4c5e21be4434e077f9550ebe3f /pg.pgc
parent4ec55cb9cfcdc8e64fa8f121e49422f8afbf960d (diff)
Implemented most database queries.
Diffstat (limited to 'pg.pgc')
-rw-r--r--pg.pgc157
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: */