]>
Commit | Line | Data |
---|---|---|
d76ed9a9 AS |
1 | -- This patch assumes a database prepared with the script below. |
2 | -- Once this is set up, you can create a log destination using the | |
3 | -- "pgsql:" schema followed by a normal PostgreSQL connect string. | |
4 | -- For example: | |
5 | -- "logs" { | |
6 | -- "*.*" "pgsql:host=postgres.testnet.com port=5432 dbname=srvx user=srvx password=TeStNeT requiressl=1"; | |
7 | -- }; | |
8 | -- some of those options may be omitted, in which case the PostgreSQL | |
9 | -- client library will use defaults. You may use hostaddr=10.0.0.7 | |
10 | -- instead of host=postgres.testnet.com, if the database server does | |
11 | -- not have a name in DNS or in /etc/hosts. | |
12 | ||
13 | CREATE TABLE srvx_modules ( | |
14 | i_id SMALLINT PRIMARY KEY, | |
15 | s_name VARCHAR(32) UNIQUE NOT NULL); | |
16 | ||
17 | CREATE TABLE srvx_bots ( | |
18 | i_id SMALLINT PRIMARY KEY, | |
19 | s_name VARCHAR(32) UNIQUE NOT NULL); | |
20 | ||
21 | CREATE TABLE srvx_audit ( | |
22 | i_module SMALLINT NOT NULL REFERENCES srvx_modules(i_id), | |
23 | i_severity SMALLINT NOT NULL, | |
24 | i_bot SMALLINT NOT NULL REFERENCES srvx_bots(i_id), | |
25 | t_when TIMESTAMP NOT NULL, | |
26 | c_channel_name VARCHAR(200), | |
27 | c_user_nick VARCHAR(30) NOT NULL, | |
28 | c_user_account VARCHAR(30), | |
29 | c_user_hostmask VARCHAR(80), | |
30 | c_command VARCHAR(500) NOT NULL); | |
31 | ||
32 | CREATE TABLE srvx_replay ( | |
33 | i_module SMALLINT NOT NULL REFERENCES srvx_modules(i_id), | |
34 | t_when TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
35 | b_is_write BOOLEAN NOT NULL, | |
36 | c_text VARCHAR(510) NOT NULL); | |
37 | ||
38 | CREATE TABLE srvx_log ( | |
39 | i_module SMALLINT NOT NULL REFERENCES srvx_modules(i_id), | |
40 | i_severity SMALLINT NOT NULL, | |
41 | t_when TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
42 | c_message VARCHAR(1024) NOT NULL); | |
43 | ||
1136f709 | 44 | CREATE TABLE srvx_helpserv_reqs ( |
45 | c_bot VARCHAR(30) NOT NULL, | |
46 | t_opened TIMESTAMP NOT NULL, | |
47 | t_assigned TIMESTAMP NOT NULL, | |
48 | t_closed TIMESTAMP NOT NULL, | |
49 | i_id INTEGER NOT NULL PRIMARY KEY, | |
50 | c_helper VARCHAR(30) NOT NULL, | |
51 | c_user_account VARCHAR(30) NOT NULL, | |
52 | c_user_nick VARCHAR(30) NOT NULL, | |
53 | c_user_host VARCHAR(80) NOT NULL, | |
54 | c_close_reason VARCHAR(512) NOT NULL, | |
55 | c_text TEXT NOT NULL); | |
56 | ||
57 | CREATE TABLE srvx_helpserv_stats ( | |
58 | c_bot VARCHAR(30) NOT NULL, | |
59 | t_weekstart TIMESTAMP NOT NULL, | |
60 | c_helper VARCHAR(30) NOT NULL, | |
61 | i_time INTEGER NOT NULL, | |
62 | i_picked_up INTEGER NOT NULL, | |
63 | i_closed INTEGER NOT NULL, | |
64 | i_reassigned_from INTEGER NOT NULL, | |
65 | i_reassigned_to INTEGER NOT NULL); | |
66 | ||
d76ed9a9 AS |
67 | CREATE SEQUENCE srvx_modules_id_seq MINVALUE 0 MAXVALUE 32767; |
68 | CREATE FUNCTION srvx_module_id (VARCHAR(32)) RETURNS SMALLINT | |
69 | LANGUAGE 'plpgsql' STABLE STRICT AS ' | |
70 | DECLARE | |
71 | name ALIAS FOR $1; | |
72 | new_id srvx_modules.i_id%TYPE; | |
73 | BEGIN | |
74 | SELECT INTO new_id i_id FROM srvx_modules WHERE s_name=name; | |
75 | IF NOT FOUND THEN | |
76 | SELECT INTO new_id nextval(''srvx_modules_id_seq''); | |
77 | INSERT INTO srvx_modules (i_id, s_name) VALUES (new_id, name); | |
78 | END IF; | |
79 | RETURN new_id; | |
80 | END;'; | |
81 | ||
82 | CREATE SEQUENCE srvx_bots_id_seq MINVALUE 0 MAXVALUE 32767; | |
83 | CREATE FUNCTION srvx_bot_id (VARCHAR(32)) RETURNS SMALLINT | |
84 | LANGUAGE 'plpgsql' STABLE STRICT AS ' | |
85 | DECLARE | |
86 | name ALIAS FOR $1; | |
87 | new_id srvx_bots.i_id%TYPE; | |
88 | BEGIN | |
89 | SELECT INTO new_id i_id FROM srvx_bots WHERE s_name=name; | |
90 | IF NOT FOUND THEN | |
91 | SELECT INTO new_id nextval(''srvx_bots_id_seq''); | |
92 | INSERT INTO srvx_bots (i_id, s_name) VALUES (new_id, name); | |
93 | END IF; | |
94 | RETURN new_id; | |
95 | END;'; |