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

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

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

Modified Files:
	helpserv-pgsql.diff 
Log Message:
Queue up HelpServ SQL commands, so that a vacuum doesn't hose it either


Index: helpserv-pgsql.diff
===================================================================
RCS file: /cvsroot/srvx/services/patches/helpserv-pgsql.diff,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -r1.1 -r1.2
*** helpserv-pgsql.diff	19 Oct 2003 17:18:53 -0000	1.1
--- helpserv-pgsql.diff	26 Oct 2003 14:01:39 -0000	1.2
***************
*** 5,9 ****
  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 > $@
--- 5,9 ----
  diff -u -r1.59 Makefile.am
  --- src/Makefile.am	9 Sep 2003 01:56:55 -0000	1.59
! +++ src/Makefile.am	26 Oct 2003 13:15:45 -0000
  @@ -9,7 +9,7 @@
   	./expnhelp < $(srcdir)/nickserv.help.m4 > $@
***************
*** 21,34 ****
  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"
--- 21,35 ----
  diff -u -r1.83 helpserv.c
  --- src/helpserv.c	19 Oct 2003 04:16:10 -0000	1.83
! +++ src/helpserv.c	26 Oct 2003 13:15:47 -0000
! @@ -46,6 +46,8 @@
   #include "opserv.h"
   #include "saxdb.h"
   #include "timeq.h"
+ +#include "ioset.h"
  +#include <postgresql/libpq-fe.h>
   
   #define HELPSERV_CONF_NAME "services/helpserv"
   #define HELPSERV_HELPFILE_NAME "helpserv.help"
! @@ -94,6 +96,7 @@
   #define KEY_REQ_ON_JOIN "req_on_join"
   #define KEY_AUTO_VOICE "auto_voice"
***************
*** 38,42 ****
   /* 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;
--- 39,43 ----
   /* General */
   #define HSMSG_WRITE_SUCCESS      "HelpServ db write completed (in "FMT_TIME_T".%03lu seconds)."
! @@ -401,6 +404,7 @@
       const char *description;
       unsigned long db_backup_frequency;
***************
*** 46,50 ****
   
   static int helpserv_enabled;
! @@ -443,6 +446,7 @@
       unsigned int req_on_join : 1;
       unsigned int auto_voice : 1;
--- 47,51 ----
   
   static int helpserv_enabled;
! @@ -443,6 +447,7 @@
       unsigned int req_on_join : 1;
       unsigned int auto_voice : 1;
***************
*** 54,58 ****
       unsigned int helpchan_empty : 1;
   
! @@ -608,36 +612,106 @@
       return dict_find(hs->users, hi->handle, NULL);
   }
--- 55,59 ----
       unsigned int helpchan_empty : 1;
   
! @@ -608,36 +613,153 @@
       return dict_find(hs->users, hi->handle, NULL);
   }
***************
*** 61,64 ****
--- 62,118 ----
  -    char key[27+NICKLEN];
  -    char userhost[USERLEN+HOSTLEN+2];
+ +static struct string_list sql_queue;
+ +static struct io_fd *sql_fd;
+  
+ -    if (!reqlog_ctx || !req)
+ +static void pgsql_send_next_query() {
+ +    int res;
+ +
+ +    res = PQsendQuery(helpserv_conf.sql_log, sql_queue.list[0]);
+ +    if (!res) {
+ +        log_module(MAIN_LOG, LOG_ERROR, "Error sending query \"%s\": %s", sql_queue.list[0], PQerrorMessage(helpserv_conf.sql_log));
+          return;
+ -    if (!reason)
+ -        reason = "";
+ +    }
+ +    res = PQflush(helpserv_conf.sql_log);
+ +    if (res == EOF)
+ +        log_module(MAIN_LOG, LOG_ERROR, "Error flushing PgSql output: %s", PQerrorMessage(helpserv_conf.sql_log));
+ +}
+ +
+ +static void pgsql_readable(UNUSED_ARG(struct io_fd *fd)) {
+ +    PGconn *conn;
+ +    PGresult *pgres;
+ +    unsigned int ii;
+ +    int res;
+ +    ExecStatusType st;
+ +
+ +    conn = helpserv_conf.sql_log;
+ +    res = PQconsumeInput(conn);
+ +    if (!res)
+ +        log_module(MAIN_LOG, LOG_ERROR, "Error consuming PgSql input: %s", PQerrorMessage(conn));
+ +    if (PQisBusy(conn))
+ +        return;
+ +    while ((pgres = PQgetResult(conn))) {
+ +        st = PQresultStatus(pgres);
+ +        if (st != PGRES_COMMAND_OK)
+ +            log_module(MAIN_LOG, LOG_ERROR, "PgSql error in \"%s\": %s", sql_queue.list[0], PQresultErrorMessage(pgres));
+ +        PQclear(pgres);
+ +    }
+ +    if (sql_queue.used == 1)
+ +        sql_queue.list[1] = NULL;
+ +    free(sql_queue.list[0]);
+ +    sql_queue.used--;
+ +    for (ii = 0; ii < sql_queue.used; ++ii)
+ +        sql_queue.list[ii] = sql_queue.list[ii+1];
+ +    if (sql_queue.used)
+ +        pgsql_send_next_query();
+ +}
+  
+ -    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);
  +static void
  +string_buffer_append_quoted(struct string_buffer *dest, const char *src) {
***************
*** 77,84 ****
  +    } else {
  +        string_buffer_append_string(dest, "NULL, ");
! +    }
  +}
!  
! -    if (!reqlog_ctx || !req)
  +static void
  +string_buffer_append_time(struct string_buffer *dest, time_t when) {
--- 131,139 ----
  +    } else {
  +        string_buffer_append_string(dest, "NULL, ");
!      }
! -    if (req->handle) {
! -        saxdb_write_string(reqlog_ctx, KEY_REQUEST_HANDLE, req->handle->handle);
  +}
! +
  +static void
  +string_buffer_append_time(struct string_buffer *dest, time_t when) {
***************
*** 86,101 ****
  +    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) {
--- 141,150 ----
  +    if (!when) {
  +        string_buffer_append_string(dest, "NULL, ");
! +        return;
       }
! -    if (req->user) {
! -        saxdb_write_string(reqlog_ctx, KEY_REQUEST_NICK, req->user->nick);
! -        sprintf(userhost, "%s@%s", req->user->ident, req->user->hostname);
! -        saxdb_write_string(reqlog_ctx, KEY_REQUEST_USERHOST, userhost);
  +    if (dest->size < dest->used + 20) {
  +        if (dest->size < 20) {
***************
*** 106,120 ****
  +        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;
--- 155,179 ----
  +        dest->list = realloc(dest->list, dest->size);
       }
! -    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);
  +    dest->used += strftime(dest->list + dest->used, dest->size - dest->used, "'%Y-%m-%d %H:%M:%S', ", localtime_r(&when, &broken_out));
  +}
  +
+ +static void
+ +pgsql_insert(char *query) {
+ +    string_list_append(&sql_queue, query);
+ +    if (sql_queue.used == 1)
+ +        pgsql_send_next_query();
+ +}
+  
+ -    fflush(reqlog_f);
  +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);
  +    else
  +        userhost[0] = 0;
***************
*** 143,147 ****
  +        struct string_buffer query, sb;
  +        unsigned int ii;
- +        PGresult *res;
  +
  +        sb.used = query.used = 0;
--- 202,205 ----
***************
*** 168,189 ****
  +        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");
   }
--- 226,236 ----
  +        query.used -= 2; /* chop off ", " from append_quoted */
  +        string_buffer_append_string(&query, ");");
! +        pgsql_insert(query.list);
  +        free(sb.list);
! +    }
   }
   
   /* Searches for a request by number, nick, or account (num|nick|*account).
! @@ -2931,6 +3053,28 @@
       OPTION_BINARY(hs->auto_devoice, "AutoDeVoice");
   }
***************
*** 214,218 ****
       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,
--- 261,265 ----
       helpserv_option_func_t *opt;
   
! @@ -2944,7 +3088,7 @@
               opt_empty_interval, opt_stale_delay, opt_request_persistence,
               opt_helper_persistence, opt_notification, opt_id_wrap,
***************
*** 223,227 ****
   
           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);
--- 270,274 ----
   
           helpserv_notice(user, HSMSG_QUEUE_OPTIONS);
! @@ -3267,6 +3411,7 @@
       saxdb_write_int(ctx, KEY_REQ_ON_JOIN, hs->req_on_join);
       saxdb_write_int(ctx, KEY_AUTO_VOICE, hs->auto_voice);
***************
*** 231,235 ****
       /* 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);
--- 278,282 ----
       /* End bot record */
       saxdb_end_record(ctx);
! @@ -3376,6 +3521,8 @@
       hs->auto_voice = str ? enabled_string(str) : 0;
       str = database_get_data(GET_RECORD_OBJECT(br), KEY_AUTO_DEVOICE, RECDB_QSTRING);
***************
*** 240,244 ****
       dict_foreach(users, user_read_helper, hs);
   
! @@ -3422,6 +3521,23 @@
           helpserv_conf.reqlogfile = NULL;
       }
--- 287,291 ----
       dict_foreach(users, user_read_helper, hs);
   
! @@ -3422,6 +3569,31 @@
           helpserv_conf.reqlogfile = NULL;
       }
***************
*** 256,261 ****
--- 303,316 ----
  +            log_module(HS_LOG, LOG_ERROR, "Pgsql connection failed: %s", PQerrorMessage(conn));
  +            PQfinish(conn);
+ +        } else if (PQsetnonblocking(conn, 1) == -1) {
+ +            log_module(HS_LOG, LOG_ERROR, "Unable to make pgsql non-blocking");
+ +            PQfinish(conn);
  +        } else {
  +            helpserv_conf.sql_log = conn;
+ +            sql_fd = ioset_add(PQsocket(conn));
+ +            sql_fd->connected = 1;
+ +            sql_fd->wants_reads = 1;
+ +            sql_fd->readable_cb = pgsql_readable;
+ +            while (PQflush(conn)) ;
  +        }
  +    }
***************
*** 264,268 ****
           saxdb_close_context(reqlog_ctx);
           reqlog_ctx = NULL;
! @@ -4159,25 +4275,31 @@
       return mktime(timeinfo);
   }
--- 319,323 ----
           saxdb_close_context(reqlog_ctx);
           reqlog_ctx = NULL;
! @@ -4159,25 +4331,31 @@
       return mktime(timeinfo);
   }
***************
*** 298,302 ****
                   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];
               }
--- 353,357 ----
                   hs_user->time_per_week[0] += when - hs_user->join_time;
                   hs_user->time_per_week[4] += when - hs_user->join_time;
! @@ -4193,10 +4371,22 @@
                   hs_user->reassigned_to[i] = hs_user->reassigned_to[i-1];
               }
***************
*** 304,308 ****
  +            /* 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(");
--- 359,362 ----
***************
*** 311,318 ****
  +                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);
  +            }
  +
--- 365,369 ----
  +                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]);
! +                pgsql_insert(query.list);
  +            }
  +
***************
*** 325,329 ****
   
   static void helpserv_timed_run_stats(UNUSED_ARG(void *data)) {
! @@ -4226,6 +4364,10 @@
           saxdb_close_context(reqlog_ctx);
       if (reqlog_f)
--- 376,380 ----
   
   static void helpserv_timed_run_stats(UNUSED_ARG(void *data)) {
! @@ -4226,6 +4416,10 @@
           saxdb_close_context(reqlog_ctx);
       if (reqlog_f)
***************
*** 336,340 ****
   
   void init_helpserv() {
! @@ -4291,6 +4433,7 @@
       helpserv_define_option("REQONJOIN", opt_req_on_join);
       helpserv_define_option("AUTOVOICE", opt_auto_voice);
--- 387,391 ----
   
   void init_helpserv() {
! @@ -4291,6 +4485,7 @@
       helpserv_define_option("REQONJOIN", opt_req_on_join);
       helpserv_define_option("AUTOVOICE", opt_auto_voice);