Line 0
Link Here
|
|
|
1 |
=head1 NAME |
2 |
|
3 |
Mail::SpamAssassin::ConfStoreSQL - store prefs in an SQL database |
4 |
|
5 |
=head1 DESCRIPTION |
6 |
|
7 |
This module uses Perl's DBI module to store prefs in an SQL database. In |
8 |
particular, it supports any database DBI supports with placeholders. |
9 |
|
10 |
See below for some table creation commands for various databases. |
11 |
|
12 |
=head1 PUBLIC FUNCTIONS |
13 |
|
14 |
=over 4 |
15 |
|
16 |
=cut |
17 |
|
18 |
package Mail::SpamAssassin::ConfStoreSQL; |
19 |
|
20 |
use Carp; |
21 |
use DBI; |
22 |
use Mail::SpamAssassin::Conf; |
23 |
use strict; |
24 |
|
25 |
use vars qw{ |
26 |
@ISA |
27 |
}; |
28 |
|
29 |
use Mail::SpamAssassin::ConfStoreGeneric; |
30 |
@ISA = qw(Mail::SpamAssassin::ConfStoreGeneric); |
31 |
|
32 |
########################################################################### |
33 |
|
34 |
=item Mail::SpamAssassin::ConfStoreSQL->new($dsn, $username, $password, $tablename) |
35 |
|
36 |
Create a new object. $dsn is the Perl DBI data source name. $username and |
37 |
$password are the username and password, respectively. $tablename is the |
38 |
name of the table to read from. Note that no database connection is |
39 |
attempted at this time. |
40 |
|
41 |
=cut |
42 |
|
43 |
sub new { |
44 |
my $class = shift; |
45 |
$class = ref($class) || $class; |
46 |
my ($dsn, $username, $password, $tablename)=@_; |
47 |
|
48 |
sa_die("Invalid table name '$tablename'") unless $tablename=~/^[a-zA-Z0-9_]+$/o; |
49 |
my $self = { |
50 |
'dsn' => $dsn, |
51 |
'user' => $username, |
52 |
'pass' => $password, |
53 |
'table' => $tablename, |
54 |
'dbi' => [0, undef], |
55 |
}; |
56 |
|
57 |
bless ($self, $class); |
58 |
$self; |
59 |
} |
60 |
|
61 |
########################################################################### |
62 |
|
63 |
=item $store->load_modules() |
64 |
|
65 |
If this is called as shown instead of statically as |
66 |
Mail::SpamAssassin::ConfStoreSQL->load_modules(), a connection will be made |
67 |
to the database, and a true value will be returned if the connect seemed to |
68 |
succeed. This has the side effect of loading the appropriate DBD modules, |
69 |
which cannot be easily determined beforehand. |
70 |
|
71 |
=cut |
72 |
|
73 |
sub load_modules { # static |
74 |
my ($self)=@_; |
75 |
Mail::SpamAssassin::ConfStoreGeneric->load_modules(@_); |
76 |
eval { |
77 |
$self->do_connect() if ref($self); |
78 |
}; |
79 |
dbg($@) if $@; |
80 |
return $@?0:1; |
81 |
# do any preloading that will speed up operation |
82 |
} |
83 |
|
84 |
########################################################################### |
85 |
|
86 |
=item $f->save_prefs($user, $prefs) |
87 |
|
88 |
Note that leading and trailing whitespace and comments are stripped from the |
89 |
saved data. Whitespace within the "lang xx " preference modifier or within |
90 |
the preference itself is collapsed to a single space. |
91 |
|
92 |
For those looking at the actual database, two extra pseudo-prefs are |
93 |
inserted with negative line numbers, to store the original length and checksum. |
94 |
|
95 |
=cut |
96 |
|
97 |
sub do_connect { |
98 |
my ($self)=@_; |
99 |
|
100 |
if($self->{'dbi'}->[0]!=$$){ |
101 |
# If we have an old dbh from a fork, set InactiveDestroy so we don't |
102 |
# disconnect our parent. |
103 |
$self->{'dbi'}->[1]->{InactiveDestroy}=1 if defined($self->{'dbi'}->[1]); |
104 |
$self->{'dbi'}->[0]=$$; |
105 |
dbg("Connecting to database..."); |
106 |
$self->{'dbi'}->[1]=DBI->connect($self->{'dsn'}, $self->{'user'}, $self->{'pass'}, { PrintError => 0, RaiseError => 1, AutoCommit => 0}) or die $DBI::errstr; |
107 |
} |
108 |
die "Our dbh is unsuspectedly undef" unless defined($self->{'dbi'}->[1]); |
109 |
return $self->{'dbi'}->[1]; |
110 |
} |
111 |
|
112 |
sub save_prefs { |
113 |
my ($self, $user, $prefs)=@_; |
114 |
my $dbh=undef; |
115 |
my $ins=undef; |
116 |
|
117 |
dbg("Saving prefs for $user to the SQL database"); |
118 |
eval { |
119 |
$dbh=$self->do_connect(); |
120 |
my $table=$self->{'table'}; |
121 |
# RaiseError is set, so we have no need to check for errors. |
122 |
# AutoCommit is off, so nothing should happen until we $dbh->commit(). |
123 |
|
124 |
$dbh->do("DELETE FROM $table WHERE username=?", {}, $user); |
125 |
$ins=$dbh->prepare("INSERT INTO $table (username,preference,value,line) VALUES (?,?,?,?)"); |
126 |
$ins->execute($user, "saved length", "".length($prefs), -2); |
127 |
$ins->execute($user, "saved checksum", "".$self->calc_checksum($prefs), -1); |
128 |
my ($line, $num, $pref, $value); |
129 |
$num=0; |
130 |
while(defined($line=Mail::SpamAssassin::Conf::extract_line($prefs))){ |
131 |
$num++; |
132 |
next unless($line); # skip empty lines |
133 |
$line=~s/^lang\s+(\S+)\s+/lang $1 /o; |
134 |
die "Invalid input at line $num\n" unless $line=~/^((?:lang \S+ )?\S+)\s*(.*)$/o; |
135 |
($pref, $value)=($1, $2); |
136 |
$ins->execute($user, $pref, $value, $num); |
137 |
} |
138 |
$ins->finish(); |
139 |
$dbh->commit(); |
140 |
}; |
141 |
if($@){ |
142 |
my $err=$@; |
143 |
eval { |
144 |
$ins->finish() if $ins; |
145 |
$dbh->rollback() if $dbh; |
146 |
}; |
147 |
return ret("EX_IOERR", $err); |
148 |
} |
149 |
return ret("EX_OK", "Success"); |
150 |
} |
151 |
|
152 |
=item $f->get_prefs($user) |
153 |
|
154 |
Note that the return isn't exactly what was given to save_prefs(), see the |
155 |
note there for details. |
156 |
|
157 |
=cut |
158 |
|
159 |
sub get_prefs { |
160 |
my ($self, $user)=@_; |
161 |
my $prefs=undef; |
162 |
|
163 |
dbg("Reading prefs for $user from the SQL database"); |
164 |
eval { |
165 |
my $dbh=$self->do_connect(); |
166 |
my $table=$self->{'table'}; |
167 |
# RaiseError is set, so we have no need to check for errors. |
168 |
# AutoCommit is off, so nothing should happen until we $dbh->commit(). |
169 |
|
170 |
my $ref=$dbh->selectall_arrayref("SELECT preference, value FROM $table WHERE username=? AND line>=0 ORDER BY line ASC", {}, $user); |
171 |
$prefs=join("\n", map { join(" ", @$_) } @$ref)."\n" if @$ref; |
172 |
}; |
173 |
return ret("EX_IOERR", undef, $@) if($@); |
174 |
return ret("EX_OK", undef, "No user prefs") unless defined($prefs); |
175 |
return ret("EX_OK", $prefs, "Success"); |
176 |
} |
177 |
|
178 |
sub get_length { |
179 |
my ($self, $user)=@_; |
180 |
my $len=undef; |
181 |
my $exists=0; |
182 |
|
183 |
eval { |
184 |
my $dbh=$self->do_connect(); |
185 |
my $table=$self->{'table'}; |
186 |
# RaiseError is set, so we have no need to check for errors. |
187 |
# AutoCommit is off, so nothing should happen until we $dbh->commit(). |
188 |
|
189 |
$len=$dbh->selectrow_array("SELECT value FROM $table WHERE username=? AND line=-2 AND preference=?", {}, $user, "saved length"); |
190 |
$exists=defined($len) || $dbh->selectrow_array("SELECT COUNT(*) FROM $table WHERE username=?", {}, $user); |
191 |
}; |
192 |
return ret("EX_IOERR", undef, $@) if($@); |
193 |
return ret("EX_DATAERR", undef, "No user prefs") unless($exists); |
194 |
return ret("EX_OK", $len, "Success") if defined($len); |
195 |
return $self->SUPER::get_length($user); |
196 |
} |
197 |
|
198 |
sub get_checksum { |
199 |
my ($self, $user)=@_; |
200 |
my $sum=undef; |
201 |
my $exists=0; |
202 |
|
203 |
eval { |
204 |
my $dbh=$self->do_connect(); |
205 |
my $table=$self->{'table'}; |
206 |
# RaiseError is set, so we have no need to check for errors. |
207 |
# AutoCommit is off, so nothing should happen until we $dbh->commit(). |
208 |
|
209 |
$sum=$dbh->selectrow_array("SELECT value FROM $table WHERE username=? AND line=-1 AND preference=?", {}, $user, "saved checksum"); |
210 |
$exists=defined($sum) || $dbh->selectrow_array("SELECT COUNT(*) FROM $table WHERE username=?", {}, $user); |
211 |
}; |
212 |
return ret("EX_IOERR", undef, $@) if($@); |
213 |
return ret("EX_DATAERR", undef, "No user prefs") unless($exists); |
214 |
return ret("EX_OK", $sum, "Success") if defined($sum); |
215 |
return $self->SUPER::get_checksum($user); |
216 |
} |
217 |
|
218 |
sub apply_prefs { |
219 |
my ($self, $conf, $user)=@_; |
220 |
my $sth=undef; |
221 |
|
222 |
dbg("Applying prefs for $user from the SQL database"); |
223 |
eval { |
224 |
my $dbh=$self->do_connect(); |
225 |
my $table=$self->{'table'}; |
226 |
# RaiseError is set, so we have no need to check for errors. |
227 |
# AutoCommit is off, so nothing should happen until we $dbh->commit(). |
228 |
|
229 |
my $sth=$dbh->prepare("SELECT preference, value FROM $table WHERE username=? AND line>=0 ORDER BY line ASC"); |
230 |
$sth->execute($user); |
231 |
my ($pref, $val); |
232 |
$sth->bind_columns(\$pref, \$val); |
233 |
|
234 |
$conf->parse_scores_only("$pref $val") while($sth->fetch); |
235 |
$sth->finish(); |
236 |
}; |
237 |
if($@){ |
238 |
my $err=$@; |
239 |
return ret("EX_IOERR", $err); |
240 |
} |
241 |
return ret("EX_OK", "Success"); |
242 |
} |
243 |
|
244 |
########################################################################### |
245 |
|
246 |
sub ret { Mail::SpamAssassin::ConfStoreGeneric::ret(@_); } |
247 |
sub dbg { Mail::SpamAssassin::ConfStoreGeneric::dbg (@_); } |
248 |
sub sa_die { Mail::SpamAssassin::ConfStoreGeneric::sa_die (@_); } |
249 |
|
250 |
########################################################################### |
251 |
|
252 |
1; |
253 |
__END__ |
254 |
|
255 |
=back |
256 |
|
257 |
=head1 DATABASE TABLES |
258 |
|
259 |
=head2 In general |
260 |
|
261 |
The table schema has changed slightly from the ConfSourceSQL version: |
262 |
|
263 |
username varchar |
264 |
preference varchar |
265 |
value varchar |
266 |
line integer |
267 |
|
268 |
The sizes of the varchars don't matter too much, as long as they're big |
269 |
enough to hold whatever possible values you'll be putting in them. The line |
270 |
column is new, it avoids the problem of ConfSourceSQL where the rows are not |
271 |
guaranteed to be returned in any particular order. Thus, you could end up |
272 |
with your report template all mixed up, or you could end up with |
273 |
clear-report-template after your report lines! |
274 |
|
275 |
Selects are performed mainly using username and line, so an appropriate |
276 |
index would be on those two columns. The combination of the two should be |
277 |
unique. This module has little need for an index on preference, and none for |
278 |
an index on value. |
279 |
|
280 |
=head2 PostgreSQL |
281 |
|
282 |
Table creation: |
283 |
|
284 |
CREATE TABLE userpref ( |
285 |
username VARCHAR(32) NOT NULL, |
286 |
preference VARCHAR(50) NOT NULL, |
287 |
value VARCHAR(100) NOT NULL, |
288 |
line INTEGER NOT NULL, |
289 |
PRIMARY KEY (username, line) |
290 |
); |
291 |
|
292 |
Note that the lengths of the varchars may be adjusted to suit your |
293 |
situation. Note that 'preference' may contain "lang xx pref" as well as just |
294 |
"pref". The primary key setting isn't strictly necessary, but it speeds |
295 |
queries slightly. |
296 |
|
297 |
Granting permissions: |
298 |
|
299 |
GRANT SELECT, INSERT, UPDATE, DELETE ON userpref TO spamassassin; |
300 |
|
301 |
If you only want to use get_prefs, only SELECT is necessary. UPDATE probably |
302 |
isn't necessary for most cases either. |
303 |
|
304 |
If you need to copy prefs from an old-style table (without the 'line' |
305 |
column), something like this should do it unless you created the old table |
306 |
without oids: |
307 |
|
308 |
INSERT INTO new_userpref SELECT *, oid AS line FROM old_userpref; |
309 |
|
310 |
=head2 MySQL |
311 |
|
312 |
Table creation: |
313 |
|
314 |
CREATE TABLE userpref ( |
315 |
username varchar(32) NOT NULL, |
316 |
preference varchar(50) NOT NULL, |
317 |
value varchar(100) NOT NULL, |
318 |
prefid int(11) NOT NULL auto_increment, |
319 |
line int(11) NOT NULL, |
320 |
PRIMARY KEY (prefid), |
321 |
INDEX (username) |
322 |
) TYPE=MyISAM; |
323 |
|
324 |
The lines containing 'prefid' are not strictly necessary, i suspect they're |
325 |
there to provide a unique column for the primary key. |
326 |
|
327 |
Since I don't use MySQL, I can't give any suggestions for permissions or |
328 |
copying (although, if you have the prefid column in your old table you can |
329 |
copy that to line). |
330 |
|
331 |
=head1 SEE ALSO |
332 |
|
333 |
C<Mail::SpamAssassin> |
334 |
C<Mail::SpamAssassin::Conf> |
335 |
C<Mail::SpamAssassin::ConfSourceGeneric> |
336 |
C<Mail::SpamAssassin::ConfStoreGeneric> |
337 |
C<spamassassin> |
338 |
C<spamd> |
339 |
|