From 5af90fb735d357a729d02a661c92af6e865b6d3a Mon Sep 17 00:00:00 2001 From: Jon Bergli Heier Date: Mon, 15 Nov 2010 23:39:05 +0100 Subject: Implemented most database queries. --- main.c | 9 ++++ parsing.c | 22 +++++++++ pg.h | 9 ++++ pg.pgc | 157 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 4 files changed, 193 insertions(+), 4 deletions(-) diff --git a/main.c b/main.c index 72a8848..dbc3688 100644 --- a/main.c +++ b/main.c @@ -38,6 +38,15 @@ int main(int argc, char **argv) { pg_upgrade(); goto disconnect; } + if(argc == 2 && strcmp(argv[1], "-d") == 0) { + pg_dropall(); + goto disconnect; + } + if(argc == 2 && strcmp(argv[1], "-c") == 0) { + pg_dropall(); + pg_init(); + goto disconnect; + } process(); diff --git a/parsing.c b/parsing.c index aeec3e6..f0e4fb5 100644 --- a/parsing.c +++ b/parsing.c @@ -321,12 +321,27 @@ static void process_file(FILE *f, struct channel_t *channel, struct regexset_t * } } +static void save_users(struct channel_t *channel) { + for(int i = 0; i < USERS_MAX; i++) { + struct user_t *user = &users[i]; + if(!user->nick) + continue; + + while(user) { + if(!user->real_user) + pg_user_set(channel, user); + user = user->next; + } + } +} + void process() { /* Parsing stuff goes here. */ for(int chan_i = 0; chan_i < channel_get_count(); chan_i++) { user_init(); word_init(); struct channel_t *channel = channel_get(chan_i); + int channel_id = pg_channel_get(channel); printf("Channel %s\n", channel->name); struct channel_file_t *file = channel->files; while(file) { @@ -339,15 +354,22 @@ void process() { } else printf("\tParsing %s\n", file->path); + long pos = pg_channel_file_get(channel_id, file); + fseek(f, pos, SEEK_SET); + last_user = NULL; in_monolog = monolog_len = 0; process_file(f, channel, rs); + pg_channel_file_set(channel_id, file, ftell(f)); + fclose(f); file = file->next; } + save_users(channel_id); + user_free(); word_free(); } diff --git a/pg.h b/pg.h index 150b5ef..26fbe54 100644 --- a/pg.h +++ b/pg.h @@ -1,9 +1,18 @@ #ifndef PG_H #define PG_H +#include "channel.h" +#include "user.h" + int pg_connect(const char *string); void pg_disconnect(); int pg_init(); int pg_upgrade(); +void pg_dropall(); +int pg_channel_get(struct channel_t *channel); +long pg_channel_file_get(int channel_id, struct channel_file_t *file); +void pg_channel_file_set(int channel_id, struct channel_file_t *file, long pos); +void pg_user_set(int channel_id, struct user_t *user); +void pg_user_get(int channel_id, struct user_t *user); #endif 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: */ -- cgit v1.2.3