patches/helpserv-pgsql.txt

   1 -- This patch assumes a database prepared with the script below.
   2 -- Once this is set up, you tell the HelpServ code to log stats
   3 -- by adding an entry to the "helpserv" section of srvx.conf:
   4 --   "sql_log" "host=postgres.testnet.com port=5432 dbname=srvx user=srvx password=TeStNeT requiressl=1";
   5 -- some of those options may be omitted, in which case the PostgreSQL
   6 -- client library will use defaults.  You may use hostaddr=10.0.0.7
   7 -- instead of host=postgres.testnet.com, if the database server does
   8 -- not have a name in DNS or in /etc/hosts.
   9
  10 CREATE TABLE srvx_helpserv_reqs (
  11    c_bot VARCHAR(32) NOT NULL,
  12    t_opened TIMESTAMP NOT NULL,
  13    t_assigned TIMESTAMP,
  14    t_closed TIMESTAMP NOT NULL,
  15    i_id INTEGER NOT NULL,
  16    c_helper VARCHAR(32),
  17    c_user_account VARCHAR(32),
  18    c_user_nick VARCHAR(32),
  19    c_user_host VARCHAR(80),
  20    c_close_reason TEXT NOT NULL,
  21    c_text TEXT);
  22
  23 CREATE TABLE srvx_helpserv_stats (
  24    c_bot VARCHAR(32) NOT NULL,
  25    t_weekstart TIMESTAMP NOT NULL,
  26    c_helper VARCHAR(32) NOT NULL,
  27    i_time INTEGER NOT NULL,
  28    i_picked_up INTEGER NOT NULL,
  29    i_closed INTEGER NOT NULL,
  30    i_reassigned_from INTEGER NOT NULL,
  31    i_reassigned_to INTEGER NOT NULL);