[srvx-commits] CVS: services/patches helpserv-pgsql.diff,NONE,1.1 helpserv-pgsql.txt,NONE,1.1

[Entrope <en...@us...>]

Update of /cvsroot/srvx/services/patches
In directory sc8-pr-cvs1:/tmp/cvs-serv17391/patches

Added Files:
	helpserv-pgsql.diff helpserv-pgsql.txt 
Log Message:
log HelpServ requests and statistics to PostgreSQL


--- NEW FILE ---
Index: src/Makefile.am
===================================================================
RCS file: /cvsroot/srvx/services/src/Makefile.am,v
retrieving revision 1.59
diff -u -r1.59 Makefile.am
--- src/Makefile.am	9 Sep 2003 01:56:55 -0000	1.59
+++ src/Makefile.am	19 Oct 2003 17:17:28 -0000
@@ -9,7 +9,7 @@
 	./expnhelp < $(srcdir)/nickserv.help.m4 > $@
 
 EXTRA_srvx_SOURCES = proto-bahamut.c proto-common.c proto-p10.c mod-snoop.c mod-memoserv.c
-srvx_LDADD = @MODULE_OBJS@
+srvx_LDADD = @MODULE_OBJS@ -lpq
 srvx_DEPENDENCIES = @MODULE_OBJS@
 srvx_SOURCES = \
 	chanserv.c chanserv.h \
Index: src/helpserv.c
===================================================================
RCS file: /cvsroot/srvx/services/src/helpserv.c,v
retrieving revision 1.83
diff -u -r1.83 helpserv.c
--- src/helpserv.c	19 Oct 2003 04:16:10 -0000	1.83
+++ src/helpserv.c	19 Oct 2003 17:17:30 -0000
@@ -46,6 +46,7 @@
 #include "opserv.h"
 #include "saxdb.h"
 #include "timeq.h"
+#include <postgresql/libpq-fe.h>
 
 #define HELPSERV_CONF_NAME "services/helpserv"
 #define HELPSERV_HELPFILE_NAME "helpserv.help"
@@ -94,6 +95,7 @@
 #define KEY_REQ_ON_JOIN "req_on_join"
 #define KEY_AUTO_VOICE "auto_voice"
 #define KEY_AUTO_DEVOICE "auto_devoice"
+#define KEY_LOG_SQL "log_sql"
 
 /* General */
 #define HSMSG_WRITE_SUCCESS      "HelpServ db write completed (in "FMT_TIME_T".%03lu seconds)."
@@ -401,6 +403,7 @@
     const char *description;
     unsigned long db_backup_frequency;
     const char *reqlogfile;
+    PGconn *sql_log;
 } helpserv_conf;
 
 static int helpserv_enabled;
@@ -443,6 +446,7 @@
     unsigned int req_on_join : 1;
     unsigned int auto_voice : 1;
     unsigned int auto_devoice : 1;
+    unsigned int log_sql : 1;
 
     unsigned int helpchan_empty : 1;
 
@@ -608,36 +612,106 @@
     return dict_find(hs->users, hi->handle, NULL);
 }
 
-static void helpserv_log_request(struct helpserv_request *req, const char *reason) {
-    char key[27+NICKLEN];
-    char userhost[USERLEN+HOSTLEN+2];
+static void
+string_buffer_append_quoted(struct string_buffer *dest, const char *src) {
+    if (src) {
+        size_t len = strlen(src);
+        string_buffer_append(dest, '\'');
+        if (dest->size < (dest->used + len * 2)) {
+            if (dest->size < len * 2)
+                dest->size = len * 4;
+            else
+                dest->size = dest->size * 2;
+            dest->list = realloc(dest->list, dest->size);
+        }
+        dest->used += PQescapeString(dest->list + dest->used, src, len);
+        string_buffer_append_string(dest, "', ");
+    } else {
+        string_buffer_append_string(dest, "NULL, ");
+    }
+}
 
-    if (!reqlog_ctx || !req)
+static void
+string_buffer_append_time(struct string_buffer *dest, time_t when) {
+    struct tm broken_out;
+    if (!when) {
+        string_buffer_append_string(dest, "NULL, ");
         return;
-    if (!reason)
-        reason = "";
-
-    sprintf(key, "%s-" FMT_TIME_T "-%lu", req->hs->helpserv->nick, req->opened, req->id);
-    saxdb_start_record(reqlog_ctx, key, 1);
-    if (req->helper) {
-        saxdb_write_string(reqlog_ctx, KEY_REQUEST_HELPER, req->helper->handle->handle);
-        saxdb_write_int(reqlog_ctx, KEY_REQUEST_ASSIGNED, req->assigned);
     }
-    if (req->handle) {
-        saxdb_write_string(reqlog_ctx, KEY_REQUEST_HANDLE, req->handle->handle);
+    if (dest->size < dest->used + 20) {
+        if (dest->size < 20) {
+            dest->size = 40;
+        } else {
+            dest->size = dest->size * 2;
+        }
+        dest->list = realloc(dest->list, dest->size);
     }
-    if (req->user) {
-        saxdb_write_string(reqlog_ctx, KEY_REQUEST_NICK, req->user->nick);
+    dest->used += strftime(dest->list + dest->used, dest->size - dest->used, "'%Y-%m-%d %H:%M:%S', ", localtime_r(&when, &broken_out));
+}
+
+static void helpserv_log_request(struct helpserv_request *req, const char *reason) {
+    char userhost[USERLEN+HOSTLEN+2];
+
+    if (req->user)
         sprintf(userhost, "%s@%s", req->user->ident, req->user->hostname);
-        saxdb_write_string(reqlog_ctx, KEY_REQUEST_USERHOST, userhost);
+    else
+        userhost[0] = 0;
+    if (reqlog_ctx) {
+        char key[27+NICKLEN];
+        sprintf(key, "%s-" FMT_TIME_T "-%lu", req->hs->helpserv->nick, req->opened, req->id);
+        saxdb_start_record(reqlog_ctx, key, 1);
+        if (req->helper) {
+            saxdb_write_string(reqlog_ctx, KEY_REQUEST_HELPER, req->helper->handle->handle);
+            saxdb_write_int(reqlog_ctx, KEY_REQUEST_ASSIGNED, req->assigned);
+        }
+        if (req->handle)
+            saxdb_write_string(reqlog_ctx, KEY_REQUEST_HANDLE, req->handle->handle);
+        if (req->user) {
+            saxdb_write_string(reqlog_ctx, KEY_REQUEST_NICK, req->user->nick);
+            saxdb_write_string(reqlog_ctx, KEY_REQUEST_USERHOST, userhost);
+        }
+        saxdb_write_int(reqlog_ctx, KEY_REQUEST_OPENED, req->opened);
+        saxdb_write_int(reqlog_ctx, KEY_REQUEST_CLOSED, now);
+        saxdb_write_string(reqlog_ctx, KEY_REQUEST_CLOSEREASON, reason);
+        saxdb_write_string_list(reqlog_ctx, KEY_REQUEST_TEXT, req->text);
+        saxdb_end_record(reqlog_ctx);
+        fflush(reqlog_f);
+    }
+    if (helpserv_conf.sql_log && req->hs->log_sql) {
+        struct string_buffer query, sb;
+        unsigned int ii;
+        PGresult *res;
+
+        sb.used = query.used = 0;
+        sb.size = query.size = 512;
+        query.list = malloc(query.size);
+        sb.list = malloc(sb.size);
+        string_buffer_append_string(&query, "INSERT INTO srvx_helpserv_reqs (c_bot, t_opened, t_assigned, t_closed, i_id, c_helper, c_user_account, c_user_nick, c_user_host, c_close_reason, c_text) VALUES (");
+        string_buffer_append_quoted(&query, req->hs->helpserv->nick);
+        string_buffer_append_time(&query, req->opened);
+        string_buffer_append_time(&query, req->assigned);
+        string_buffer_append_time(&query, now);
+        string_buffer_append_printf(&query, "%lu, ", req->id);
+        string_buffer_append_quoted(&query, req->helper ? req->helper->handle->handle : NULL);
+        string_buffer_append_quoted(&query, req->handle ? req->handle->handle : NULL);
+        string_buffer_append_quoted(&query, req->user ? req->user->nick : NULL);
+        string_buffer_append_quoted(&query, req->user ? userhost : NULL);
+        string_buffer_append_quoted(&query, reason);
+        for (ii = 0; ii < req->text->used; ++ii) {
+            string_buffer_append_string(&sb, req->text->list[ii]);
+            string_buffer_append(&sb, '\n');
+        }
+        string_buffer_append(&sb, 0);
+        string_buffer_append_quoted(&query, sb.list);
+        query.used -= 2; /* chop off ", " from append_quoted */
+        string_buffer_append_string(&query, ");");
+        res = PQexec(helpserv_conf.sql_log, query.list);
+        if (PQresultStatus(res) != PGRES_COMMAND_OK)
+            log_module(HS_LOG, LOG_ERROR, "Unable to SQL-store request %lu on bot %s: %s", req->id, req->hs->helpserv->nick, PQerrorMessage(helpserv_conf.sql_log));
+        PQclear(res);
+        free(query.list);
+        free(sb.list);
     }
-    saxdb_write_int(reqlog_ctx, KEY_REQUEST_OPENED, req->opened);
-    saxdb_write_int(reqlog_ctx, KEY_REQUEST_CLOSED, now);
-    saxdb_write_string(reqlog_ctx, KEY_REQUEST_CLOSEREASON, reason);
-    saxdb_write_string_list(reqlog_ctx, KEY_REQUEST_TEXT, req->text);
-    saxdb_end_record(reqlog_ctx);
-
-    fflush(reqlog_f);
 }
 
 /* Searches for a request by number, nick, or account (num|nick|*account).
@@ -2931,6 +3005,28 @@
     OPTION_BINARY(hs->auto_devoice, "AutoDeVoice");
 }
 
+static HELPSERV_OPTION(opt_log_sql) {
+    int changed = 0;
+    if (argc > 0) {
+        if (!from_opserv) {
+            helpserv_notice(user, HSMSG_SET_NEED_OPER);
+            return 0;
+        }
+        if (enabled_string(argv[0])) {
+            hs->log_sql = 1;
+            changed = 1;
+        } else if (disabled_string(argv[0])) {
+            hs->log_sql = 0;
+            changed = 1;
+        } else {
+            helpserv_notice(user, MSG_INVALID_BINARY, argv[0]);
+            return 0;
+        }
+    }
+    helpserv_notice(user, HSMSG_STRING_VALUE, "LogSql", hs->log_sql ? "Enabled" : "Disabled");
+    return changed;
+}
+
 static HELPSERV_FUNC(cmd_set) {
     helpserv_option_func_t *opt;
 
@@ -2944,7 +3040,7 @@
             opt_empty_interval, opt_stale_delay, opt_request_persistence,
             opt_helper_persistence, opt_notification, opt_id_wrap,
             opt_req_maxlen, opt_privmsg_only, opt_req_on_join, opt_auto_voice,
-            opt_auto_devoice
+            opt_auto_devoice, opt_log_sql
         };
 
         helpserv_notice(user, HSMSG_QUEUE_OPTIONS);
@@ -3267,6 +3363,7 @@
     saxdb_write_int(ctx, KEY_REQ_ON_JOIN, hs->req_on_join);
     saxdb_write_int(ctx, KEY_AUTO_VOICE, hs->auto_voice);
     saxdb_write_int(ctx, KEY_AUTO_DEVOICE, hs->auto_devoice);
+    saxdb_write_int(ctx, KEY_LOG_SQL, hs->log_sql);
 
     /* End bot record */
     saxdb_end_record(ctx);
@@ -3376,6 +3473,8 @@
     hs->auto_voice = str ? enabled_string(str) : 0;
     str = database_get_data(GET_RECORD_OBJECT(br), KEY_AUTO_DEVOICE, RECDB_QSTRING);
     hs->auto_devoice = str ? enabled_string(str) : 0;
+    str = database_get_data(GET_RECORD_OBJECT(br), KEY_LOG_SQL, RECDB_QSTRING);
+    hs->log_sql = str ? enabled_string(str) : 0;
 
     dict_foreach(users, user_read_helper, hs);
 
@@ -3422,6 +3521,23 @@
         helpserv_conf.reqlogfile = NULL;
     }
 
+    if (helpserv_conf.sql_log) {
+        PQfinish(helpserv_conf.sql_log);
+        helpserv_conf.sql_log = NULL;
+    }
+    str = database_get_data(conf_node, "sql_log", RECDB_QSTRING);
+    if (str) {
+        PGconn *conn = PQconnectdb(str);
+        if (!conn) {
+            log_module(HS_LOG, LOG_ERROR, "Unable to allocate pgsql connection");
+        } else if (PQstatus(conn) == CONNECTION_BAD) {
+            log_module(HS_LOG, LOG_ERROR, "Pgsql connection failed: %s", PQerrorMessage(conn));
+            PQfinish(conn);
+        } else {
+            helpserv_conf.sql_log = conn;
+        }
+    }
+
     if (reqlog_ctx) {
         saxdb_close_context(reqlog_ctx);
         reqlog_ctx = NULL;
@@ -4159,25 +4275,31 @@
     return mktime(timeinfo);
 }
 
-/* If data != NULL, then don't add to the timeq */
 static void helpserv_run_stats(time_t when) {
     struct tm when_s;
+    struct string_buffer query;
     struct helpserv_bot *hs;
     struct helpserv_user *hs_user;
-    int i;
     dict_iterator_t it, it2;
+    int i;
+    char timestamp[64];
 
     last_stats_update = when;
     localtime_r(&when, &when_s);
+    strftime(timestamp, sizeof(timestamp), "'%Y-%m-%d %H:%M:%S', ", &when_s);
+    query.size = 512;
+    query.list = malloc(query.size);
     for (it=dict_first(helpserv_bots_dict); it; it=iter_next(it)) {
         hs = iter_data(it);
 
         for (it2=dict_first(hs->users); it2; it2=iter_next(it2)) {
             hs_user = iter_data(it2);
 
+            /* Skip the helper if it's not their week-start day. */
             if (hs_user->week_start != when_s.tm_wday)
                 continue;
 
+            /* Adjust their credit if they are in-channel at rollover. */
             if (hs_user->join_time) {
                 hs_user->time_per_week[0] += when - hs_user->join_time;
                 hs_user->time_per_week[4] += when - hs_user->join_time;
@@ -4193,10 +4315,26 @@
                 hs_user->reassigned_to[i] = hs_user->reassigned_to[i-1];
             }
 
+            /* Log to SQL */
+            if (helpserv_conf.sql_log && hs->log_sql) {
+                PGresult *res;
+                query.used = 0;
+                string_buffer_append_string(&query, "INSERT INTO srvx_helpserv_stats (c_bot, t_weekstart, c_helper, i_time, i_picked_up, i_closed, i_reassigned_from, i_reassigned_to) VALUES(");
+                string_buffer_append_quoted(&query, hs->helpserv->nick);
+                string_buffer_append_quoted(&query, timestamp);
+                string_buffer_append_quoted(&query, hs_user->handle->handle);
+                string_buffer_append_printf(&query, "%d, %d, %d, %d, %d);", hs_user->time_per_week[0], hs_user->picked_up[0], hs_user->closed[0], hs_user->reassigned_from[0], hs_user->reassigned_to[0]);
+                res = PQexec(helpserv_conf.sql_log, query.list);
+                if (PQresultStatus(res) != PGRES_COMMAND_OK)
+                    log_module(HS_LOG, LOG_ERROR, "Unable to SQL-store stats for helper %s on bot %s: %s", hs_user->handle->handle, hs->helpserv->nick, PQerrorMessage(helpserv_conf.sql_log));
+                PQclear(res);
+            }
+
             /* Reset it for this week */
             hs_user->time_per_week[0] = hs_user->picked_up[0] = hs_user->closed[0] = hs_user->reassigned_from[0] = hs_user->reassigned_to[0] = 0;
         }
     }
+    free(query.list);
 }
 
 static void helpserv_timed_run_stats(UNUSED_ARG(void *data)) {
@@ -4226,6 +4364,10 @@
         saxdb_close_context(reqlog_ctx);
     if (reqlog_f)
         fclose(reqlog_f);
+    if (helpserv_conf.sql_log) {
+        PQfinish(helpserv_conf.sql_log);
+        helpserv_conf.sql_log = NULL;
+    }
 }
 
 void init_helpserv() {
@@ -4291,6 +4433,7 @@
     helpserv_define_option("REQONJOIN", opt_req_on_join);
     helpserv_define_option("AUTOVOICE", opt_auto_voice);
     helpserv_define_option("AUTODEVOICE", opt_auto_devoice);
+    helpserv_define_option("LOGSQL", opt_log_sql);
 
     helpserv_bots_dict = dict_new();
     dict_set_free_data(helpserv_bots_dict, helpserv_free_bot);

--- NEW FILE ---
-- This patch assumes a database prepared with the script below.
-- Once this is set up, you tell the HelpServ code to log stats
-- by adding an entry to the "helpserv" section of srvx.conf:
--   "sql_log" "host=postgres.testnet.com port=5432 dbname=srvx user=srvx password=TeStNeT requiressl=1";
-- some of those options may be omitted, in which case the PostgreSQL
-- client library will use defaults.  You may use hostaddr=10.0.0.7
-- instead of host=postgres.testnet.com, if the database server does
-- not have a name in DNS or in /etc/hosts.

CREATE TABLE srvx_helpserv_reqs (
   c_bot VARCHAR(32) NOT NULL,
   t_opened TIMESTAMP NOT NULL,
   t_assigned TIMESTAMP,
   t_closed TIMESTAMP NOT NULL,
   i_id INTEGER NOT NULL,
   c_helper VARCHAR(32),
   c_user_account VARCHAR(32),
   c_user_nick VARCHAR(32),
   c_user_host VARCHAR(80),
   c_close_reason TEXT NOT NULL,
   c_text TEXT);

CREATE TABLE srvx_helpserv_stats (
   c_bot VARCHAR(32) NOT NULL,
   t_weekstart TIMESTAMP NOT NULL,
   c_helper VARCHAR(32) NOT NULL,
   i_time INTEGER NOT NULL,
   i_picked_up INTEGER NOT NULL,
   i_closed INTEGER NOT NULL,
   i_reassigned_from INTEGER NOT NULL,
   i_reassigned_to INTEGER NOT NULL);