Commit | Line | Data |
---|---|---|
f8beb284 MW |
1 | #!/bin/sh |
2 | # Simple script to generate input to mysql 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 | ||
13 | CREATE='y' | |
14 | DROP='n' | |
15 | TROOT='list' | |
16 | # size of std cookie | |
17 | COOKIE='20' | |
18 | ||
19 | # not everyone has getopt :-( | |
20 | while [ "`${ECHO} "$1" | ${CUT} -c1`" = "-" ]; do | |
21 | case "$1" in | |
22 | -c) CREATE='y'; shift;; | |
23 | -C) CREATE='n'; shift;; | |
24 | -d) DROP='y'; shift;; | |
25 | -D) DROP='n'; shift;; | |
26 | -cd|-dc) CREATE='y'; DROP='y'; shift;; | |
27 | -cD|-Dc) CREATE='y'; DROP='n'; shift;; | |
28 | -Cd|-dC) CREATE='n'; DROP='y'; shift;; | |
29 | -CD|-DC) CREATE='n'; DROP='n'; shift;; | |
30 | --) shift; break;; | |
31 | *) echo "usage: emzlm-mktab [-cCdD] table_toot"; exit 100;; | |
32 | esac | |
33 | done | |
34 | ||
35 | [ ! -z "$1" ] && TROOT="$1"; | |
36 | ||
37 | ||
38 | if [ "$DROP" = "y" ]; then | |
39 | cat <<EOF | |
40 | ||
41 | /* drop old tables. This may fail unless you use mysql -f */ | |
42 | /* Usage: */ | |
43 | /* ezmlm-mktab [-d] troot | mysql -hhost -uuserid -ppw datab -f */ | |
44 | ||
45 | DROP TABLE ${TROOT}; | |
46 | DROP TABLE ${TROOT}_slog; | |
47 | DROP TABLE ${TROOT}_digest; | |
48 | DROP TABLE ${TROOT}_digest_slog; | |
49 | DROP TABLE ${TROOT}_mod; | |
50 | DROP TABLE ${TROOT}_mod_slog; | |
51 | DROP TABLE ${TROOT}_allow; | |
52 | DROP TABLE ${TROOT}_allow_slog; | |
53 | DROP TABLE ${TROOT}_deny; | |
54 | DROP TABLE ${TROOT}_deny_slog; | |
55 | /* eliminated name table - no need */ | |
56 | DROP TABLE ${TROOT}_cookie; | |
57 | DROP TABLE ${TROOT}_mlog; | |
58 | DROP TABLE ${TROOT}_digest_cookie; | |
59 | DROP TABLE ${TROOT}_digest_mlog; | |
60 | ||
61 | EOF | |
62 | ||
63 | fi | |
64 | ||
65 | if [ $CREATE = 'y' ]; then | |
66 | cat << EOF | |
67 | ||
68 | /* Main address table */ | |
69 | /* Need varchar. Domain = 3 chars => fixed length, as opposed to varchar */ | |
70 | /* Always select on domain and hash, so that one index should do */ | |
71 | /* primary key(address) is very inefficient for MySQL. */ | |
72 | /* MySQL tables do not need a primary key. Other RDBMS require one. For */ | |
73 | /* the log tables, just add an INT AUTO_INCREMENT. For the address table,*/ | |
74 | /* do that or use address as a primary key. */ | |
75 | ||
76 | create TABLE ${TROOT} ( | |
77 | hash TINYINT UNSIGNED NOT NULL, | |
78 | address VARCHAR(255) NOT NULL, | |
79 | INDEX h (hash), | |
80 | INDEX a (address(12))); | |
81 | ||
82 | /* Subscription log table. No addr idx to make insertion fast, since that is */ | |
83 | /* almost the only thing we do with this table */ | |
84 | create TABLE ${TROOT}_slog ( | |
85 | tai TIMESTAMP, | |
86 | address VARCHAR(255) NOT NULL, | |
87 | fromline VARCHAR(255) NOT NULL, | |
88 | edir CHAR(1) NOT NULL, | |
89 | etype CHAR(1) NOT NULL, | |
90 | INDEX (tai)); | |
91 | ||
92 | /* digest list table */ | |
93 | create TABLE ${TROOT}_digest ( | |
94 | hash TINYINT UNSIGNED NOT NULL, | |
95 | address VARCHAR(255) NOT NULL, | |
96 | INDEX h (hash), | |
97 | INDEX a (address(12))); | |
98 | ||
99 | /* digest list subscription log */ | |
100 | create TABLE ${TROOT}_digest_slog ( | |
101 | tai TIMESTAMP, | |
102 | address VARCHAR(255) NOT NULL, | |
103 | fromline VARCHAR(255) NOT NULL, | |
104 | edir CHAR(1) NOT NULL, | |
105 | etype CHAR(1) NOT NULL, | |
106 | INDEX (tai)); | |
107 | ||
108 | /* moderator addresses */ | |
109 | create TABLE ${TROOT}_mod ( | |
110 | hash TINYINT UNSIGNED NOT NULL, | |
111 | address VARCHAR(255) NOT NULL, | |
112 | INDEX h(hash), | |
113 | INDEX a(address(12))); | |
114 | ||
115 | /* moderator subscription log */ | |
116 | create TABLE ${TROOT}_mod_slog ( | |
117 | tai TIMESTAMP, | |
118 | address VARCHAR(255) NOT NULL, | |
119 | fromline VARCHAR(255) NOT NULL, | |
120 | edir CHAR(1) NOT NULL, | |
121 | etype CHAR(1) NOT NULL, | |
122 | INDEX (tai)); | |
123 | ||
124 | /* "allow" address table */ | |
125 | create TABLE ${TROOT}_allow ( | |
126 | hash TINYINT UNSIGNED NOT NULL, | |
127 | address VARCHAR(255) NOT NULL, | |
128 | INDEX h(hash), | |
129 | INDEX a(address(12))); | |
130 | ||
131 | /* extra address table log */ | |
132 | create TABLE ${TROOT}_allow_slog ( | |
133 | tai TIMESTAMP, | |
134 | address VARCHAR(255) NOT NULL, | |
135 | fromline VARCHAR(255) NOT NULL, | |
136 | edir CHAR(1) NOT NULL, | |
137 | etype CHAR(1) NOT NULL, | |
138 | INDEX (tai)); | |
139 | ||
140 | /* blacklist address table */ | |
141 | create TABLE ${TROOT}_deny ( | |
142 | hash TINYINT UNSIGNED NOT NULL, | |
143 | address VARCHAR(255) NOT NULL, | |
144 | INDEX h(hash), | |
145 | INDEX a(address(12))); | |
146 | ||
147 | /* blacklist subscription log */ | |
148 | create TABLE ${TROOT}_deny_slog ( | |
149 | tai TIMESTAMP, | |
150 | address VARCHAR(255) NOT NULL, | |
151 | fromline VARCHAR(255) NOT NULL, | |
152 | edir CHAR(1) NOT NULL, | |
153 | etype CHAR(1) NOT NULL, | |
154 | INDEX (tai)); | |
155 | ||
156 | /* main list inserts a cookie here. Sublists check it */ | |
157 | CREATE TABLE ${TROOT}_cookie ( | |
158 | msgnum INTEGER UNSIGNED NOT NULL, | |
159 | tai TIMESTAMP NOT NULL, | |
160 | cookie CHAR($COOKIE) NOT NULL, | |
161 | chunk TINYINT UNSIGNED NOT NULL DEFAULT 0, | |
162 | bodysize INTEGER UNSIGNED NOT NULL DEFAULT 0, | |
163 | PRIMARY KEY (msgnum)); | |
164 | ||
165 | /* main and sublist log here when the message is done */ | |
166 | /* done=0 for arrived, done=4 for sent, 5 for receit. */ | |
167 | /* tai reflects last change */ | |
168 | CREATE TABLE ${TROOT}_mlog ( | |
169 | msgnum INTEGER UNSIGNED NOT NULL, | |
170 | listno INTEGER UNSIGNED NOT NULL, | |
171 | tai TIMESTAMP, | |
172 | subs INTEGER UNSIGNED NOT NULL DEFAULT 0, | |
173 | done TINYINT NOT NULL DEFAULT 0, | |
174 | PRIMARY KEY listmsg (listno,msgnum,done)); | |
175 | ||
176 | /* ezmlm-get when creating a digests inserts a cookie here. Sublists check it */ | |
177 | CREATE TABLE ${TROOT}_digest_cookie ( | |
178 | msgnum INTEGER UNSIGNED NOT NULL, | |
179 | tai TIMESTAMP NOT NULL, | |
180 | cookie CHAR($COOKIE) NOT NULL, | |
181 | chunk TINYINT UNSIGNED NOT NULL DEFAULT 0, | |
182 | bodysize INTEGER UNSIGNED NOT NULL DEFAULT 0, | |
183 | PRIMARY KEY (msgnum)); | |
184 | ||
185 | /* ezmlm-get and digest sublists log here when the message is done */ | |
186 | /* done=0 for arrived, done=4 for sent, 5 for receit. */ | |
187 | /* tai reflects last change */ | |
188 | CREATE TABLE ${TROOT}_digest_mlog ( | |
189 | msgnum INTEGER UNSIGNED NOT NULL, | |
190 | listno INTEGER UNSIGNED NOT NULL, | |
191 | tai TIMESTAMP, | |
192 | subs INT UNSIGNED NOT NULL DEFAULT 0, | |
193 | done TINYINT NOT NULL DEFAULT 0, | |
194 | PRIMARY KEY listmsg (listno,msgnum,done)); | |
195 | ||
196 | EOF | |
197 | ||
198 | fi | |
199 | exit 0 | |
200 |