Commit | Line | Data |
---|---|---|
f8beb284 MW |
1 | #!/bin/sh |
2 | # Simple script to generate input to psql to generate tables for a list | |
3 | # All tables are created, even though it is not advisable to put e.g. the | |
4 | # moderator table in the SQL database nor is it very useful to put the | |
5 | # blacklist/deny table there. The subscriber lists for the main and digest | |
6 | # lists should be there, and it's reasonable to put the "extra" list there | |
7 | # if used. | |
8 | ECHO='echo' | |
9 | CAT='cat' | |
10 | CUT='cut' | |
11 | ||
12 | CREATE='y' | |
13 | DROP='n' | |
14 | TROOT='list' | |
15 | # size of std cookie | |
16 | COOKIE='20' | |
17 | ||
18 | # not everyone has getopt :-( | |
19 | while [ "`${ECHO} "$1" | ${CUT} -c1`" = "-" ]; do | |
20 | case "$1" in | |
21 | -c) CREATE='y'; shift;; | |
22 | -C) CREATE='n'; shift;; | |
23 | -d) DROP='y'; shift;; | |
24 | -D) DROP='n'; shift;; | |
25 | -cd|-dc) CREATE='y'; DROP='y'; shift;; | |
26 | -cD|-Dc) CREATE='y'; DROP='n'; shift;; | |
27 | -Cd|-dC) CREATE='n'; DROP='y'; shift;; | |
28 | -CD|-DC) CREATE='n'; DROP='n'; shift;; | |
29 | --) shift; break;; | |
30 | *) echo "usage: emzlm-mktab [-cCdD] table_toot"; exit 100;; | |
31 | esac | |
32 | done | |
33 | ||
34 | [ ! -z "$1" ] && TROOT="$1"; | |
35 | ||
36 | if [ "$DROP" = "y" ]; then | |
37 | cat <<EOF | |
38 | ||
39 | /* drop old tables. */ | |
40 | /* Usage: */ | |
41 | /* ezmlm-mktab [-d] troot | psql -h host -u userid -d database */ | |
42 | ||
43 | DROP TABLE ${TROOT}; | |
44 | DROP TABLE ${TROOT}_slog; | |
45 | DROP TABLE ${TROOT}_digest; | |
46 | DROP TABLE ${TROOT}_digest_slog; | |
47 | DROP TABLE ${TROOT}_mod; | |
48 | DROP TABLE ${TROOT}_mod_slog; | |
49 | DROP TABLE ${TROOT}_allow; | |
50 | DROP TABLE ${TROOT}_allow_slog; | |
51 | DROP TABLE ${TROOT}_deny; | |
52 | DROP TABLE ${TROOT}_deny_slog; | |
53 | DROP TABLE ${TROOT}_name; | |
54 | DROP TABLE ${TROOT}_cookie; | |
55 | DROP TABLE ${TROOT}_mlog; | |
56 | DROP TABLE ${TROOT}_digest_name; | |
57 | DROP TABLE ${TROOT}_digest_cookie; | |
58 | DROP TABLE ${TROOT}_digest_mlog; | |
59 | DROP SEQUENCE ${TROOT}_name_listno_seq; | |
60 | DROP SEQUENCE ${TROOT}_digest_name_listno_seq; | |
61 | ||
62 | EOF | |
63 | ||
64 | fi | |
65 | ||
66 | if [ $CREATE = 'y' ]; then | |
67 | cat << EOF | |
68 | ||
69 | /* Main address table */ | |
70 | create TABLE ${TROOT} ( | |
71 | hash INT4 NOT NULL, | |
72 | address VARCHAR(255) PRIMARY KEY ); | |
73 | ||
74 | /* Subscription log table. No addr idx to make insertion fast, since that is */ | |
75 | /* almost the only thing we do with this table */ | |
76 | create TABLE ${TROOT}_slog ( | |
77 | tai TIMESTAMP DEFAULT now(), | |
78 | address VARCHAR(255) NOT NULL, | |
79 | fromline VARCHAR(255) NOT NULL, | |
80 | edir CHAR NOT NULL, | |
81 | etype CHAR NOT NULL | |
82 | ); | |
83 | ||
84 | /* digest list table */ | |
85 | create TABLE ${TROOT}_digest ( | |
86 | hash INT4 NOT NULL, | |
87 | address TEXT NOT NULL | |
88 | ); | |
89 | ||
90 | /* digest list subscription log */ | |
91 | create TABLE ${TROOT}_digest_slog ( | |
92 | tai TIMESTAMP DEFAULT now(), | |
93 | address VARCHAR(255) NOT NULL, | |
94 | fromline VARCHAR(255) NOT NULL, | |
95 | edir CHAR NOT NULL, | |
96 | etype CHAR NOT NULL | |
97 | ); | |
98 | ||
99 | /* moderator addresses */ | |
100 | create TABLE ${TROOT}_mod ( | |
101 | hash INT4 NOT NULL, | |
102 | address TEXT NOT NULL | |
103 | ); | |
104 | ||
105 | /* moderator subscription log */ | |
106 | create TABLE ${TROOT}_mod_slog ( | |
107 | tai TIMESTAMP DEFAULT now(), | |
108 | address VARCHAR(255) NOT NULL, | |
109 | fromline VARCHAR(255) NOT NULL, | |
110 | edir CHAR NOT NULL, | |
111 | etype CHAR NOT NULL | |
112 | ); | |
113 | ||
114 | /* "allow" address table */ | |
115 | create TABLE ${TROOT}_allow ( | |
116 | hash INT4 NOT NULL, | |
117 | address VARCHAR(255) NOT NULL | |
118 | ); | |
119 | ||
120 | /* extra address table log */ | |
121 | create TABLE ${TROOT}_allow_slog ( | |
122 | tai TIMESTAMP DEFAULT now(), | |
123 | address VARCHAR(255) NOT NULL, | |
124 | fromline VARCHAR(255) NOT NULL, | |
125 | edir CHAR NOT NULL, | |
126 | etype CHAR NOT NULL | |
127 | ); | |
128 | ||
129 | /* blacklist address table */ | |
130 | create TABLE ${TROOT}_deny ( | |
131 | hash INT4 NOT NULL, | |
132 | address VARCHAR(255) NOT NULL | |
133 | ); | |
134 | ||
135 | /* blacklist subscription log */ | |
136 | create TABLE ${TROOT}_deny_slog ( | |
137 | tai TIMESTAMP, | |
138 | address VARCHAR(255) NOT NULL, | |
139 | fromline VARCHAR(255) NOT NULL, | |
140 | edir CHAR NOT NULL, | |
141 | etype CHAR NOT NULL | |
142 | ); | |
143 | ||
144 | /* sublist restriction table */ | |
145 | /* notuse != 0 => defer message. = 0 => process message */ | |
146 | /* no reason for index - will always be small */ | |
147 | create TABLE ${TROOT}_name ( | |
148 | listno SERIAL, | |
149 | name VARCHAR(255) NOT NULL, | |
150 | notuse INT4 NOT NULL DEFAULT 0, | |
151 | msgnum_lo INT8 NOT NULL DEFAULT 0, | |
152 | msgnum_hi INT8 NOT NULL DEFAULT 4294967295, | |
153 | hash_lo INT4 NOT NULL DEFAULT 0, | |
154 | hash_hi INT4 NOT NULL DEFAULT 52, | |
155 | domain CHAR(3) NOT NULL DEFAULT '', | |
156 | PRIMARY KEY (listno)); | |
157 | ||
158 | /* main list inserts a cookie here. Sublists check it */ | |
159 | CREATE TABLE ${TROOT}_cookie ( | |
160 | msgnum INT4 NOT NULL, | |
161 | tai TIMESTAMP NOT NULL DEFAULT now(), | |
162 | cookie CHAR(20) NOT NULL, | |
163 | chunk INT4 NOT NULL DEFAULT 0, | |
164 | bodysize INT4 NOT NULL DEFAULT 0, | |
165 | PRIMARY KEY (msgnum)); | |
166 | ||
167 | /* main and sublist log here when the message is done */ | |
168 | /* done=0 for arrived, done=1 for sent. tai reflects last change, as e.g. */ | |
169 | /* done=0 may be overwritten in case first delivery to the list fails. */ | |
170 | CREATE TABLE ${TROOT}_mlog ( | |
171 | msgnum INT4 NOT NULL, | |
172 | listno INT4 NOT NULL, | |
173 | tai TIMESTAMP DEFAULT now(), | |
174 | subs INT4 NOT NULL DEFAULT 0, | |
175 | done INT4 NOT NULL DEFAULT 0, | |
176 | PRIMARY KEY (listno,msgnum,done)); | |
177 | ||
178 | /* digest sublist restriction table */ | |
179 | /* notuse != 0 => defer message. = 0 => process message */ | |
180 | /* no index, since table unlikely to have >30 or so rows */ | |
181 | create TABLE ${TROOT}_digest_name ( | |
182 | listno SERIAL, | |
183 | name VARCHAR(255) NOT NULL, | |
184 | notuse INT4 NOT NULL DEFAULT 0, | |
185 | msgnum_lo INT8 NOT NULL DEFAULT 0, | |
186 | msgnum_hi INT8 NOT NULL DEFAULT 4294967295, | |
187 | hash_lo INT4 NOT NULL DEFAULT 0, | |
188 | hash_hi INT4 NOT NULL DEFAULT 52, | |
189 | domain CHAR(3) NOT NULL DEFAULT '', | |
190 | PRIMARY KEY (listno)); | |
191 | ||
192 | /* ezmlm-get when creating a digests inserts a cookie here. Sublists check it*/ | |
193 | CREATE TABLE ${TROOT}_digest_cookie ( | |
194 | msgnum INT4 NOT NULL, | |
195 | tai TIMESTAMP NOT NULL DEFAULT now(), | |
196 | cookie CHAR(20) NOT NULL, | |
197 | chunk INT4 NOT NULL DEFAULT 0, | |
198 | bodysize INT4 NOT NULL DEFAULT 0, | |
199 | PRIMARY KEY (msgnum)); | |
200 | ||
201 | /* ezmlm-get and digest sublists log here when the message is done */ | |
202 | /* done=0 for arrived, done=1 for sent. tai reflects last change */ | |
203 | CREATE TABLE ${TROOT}_digest_mlog ( | |
204 | msgnum INT4 NOT NULL, | |
205 | listno INT4 NOT NULL, | |
206 | tai TIMESTAMP DEFAULT now(), | |
207 | subs INT4 NOT NULL DEFAULT 0, | |
208 | done INT4 NOT NULL DEFAULT 0, | |
209 | PRIMARY KEY (listno,msgnum,done)); | |
210 | ||
211 | EOF | |
212 | ||
213 | fi | |
214 | exit 0 | |
215 | ||
216 |