]>
Commit | Line | Data |
---|---|---|
72d8bf49 DM |
1 | package PMG::RuleDB; |
2 | ||
3 | use strict; | |
4 | use warnings; | |
72d8bf49 DM |
5 | use DBI; |
6 | use HTML::Entities; | |
af748e8c | 7 | use Data::Dumper; |
4418cffc | 8 | use Encode qw(encode); |
72d8bf49 DM |
9 | |
10 | use PVE::SafeSyslog; | |
11 | ||
12 | use PMG::Utils; | |
13 | use PMG::DBTools; | |
14 | ||
15 | use PMG::RuleDB::Group; | |
16 | ||
17 | #use Proxmox::Statistic; | |
18 | use PMG::RuleDB::Object; | |
19 | use PMG::RuleDB::WhoRegex; | |
20 | use PMG::RuleDB::ReceiverRegex; | |
21 | use PMG::RuleDB::EMail; | |
22 | use PMG::RuleDB::Receiver; | |
23 | use PMG::RuleDB::IPAddress; | |
24 | use PMG::RuleDB::IPNet; | |
25 | use PMG::RuleDB::Domain; | |
26 | use PMG::RuleDB::ReceiverDomain; | |
10621236 | 27 | use PMG::RuleDB::LDAP; |
c712d3a2 | 28 | use PMG::RuleDB::LDAPUser; |
72d8bf49 DM |
29 | use PMG::RuleDB::TimeFrame; |
30 | use PMG::RuleDB::Spam; | |
31 | use PMG::RuleDB::ReportSpam; | |
32 | use PMG::RuleDB::Virus; | |
33 | use PMG::RuleDB::Accept; | |
34 | use PMG::RuleDB::Remove; | |
35 | use PMG::RuleDB::ModField; | |
36 | use PMG::RuleDB::MatchField; | |
37 | use PMG::RuleDB::MatchFilename; | |
5e809f47 | 38 | use PMG::RuleDB::MatchArchiveFilename; |
72d8bf49 DM |
39 | use PMG::RuleDB::Attach; |
40 | use PMG::RuleDB::Disclaimer; | |
41 | use PMG::RuleDB::BCC; | |
42 | use PMG::RuleDB::Quarantine; | |
43 | use PMG::RuleDB::Block; | |
44 | use PMG::RuleDB::Counter; | |
45 | use PMG::RuleDB::Notify; | |
46 | use PMG::RuleDB::Rule; | |
47 | use PMG::RuleDB::ContentTypeFilter; | |
c4741113 | 48 | use PMG::RuleDB::ArchiveFilter; |
72d8bf49 DM |
49 | |
50 | sub new { | |
51 | my ($type, $dbh) = @_; | |
9ef3f143 | 52 | |
72d8bf49 DM |
53 | $dbh = PMG::DBTools::open_ruledb("Proxmox_ruledb") if !defined ($dbh); |
54 | ||
55 | my $self = bless { dbh => $dbh }, $type; | |
56 | ||
57 | return $self; | |
58 | } | |
59 | ||
60 | sub close { | |
61 | my ($self) = @_; | |
62 | ||
63 | $self->{dbh}->disconnect(); | |
64 | } | |
65 | ||
9973fc66 | 66 | sub create_group_with_obj { |
bdf383f3 | 67 | my ($self, $obj, $name, $info) = @_; |
744483ce DM |
68 | |
69 | my $og; | |
70 | my $id; | |
71 | ||
72 | defined($obj) || die "proxmox: undefined object"; | |
73 | ||
4418cffc SI |
74 | $name = encode('UTF-8', $name // ''); |
75 | $info = encode('UTF-8', $info // ''); | |
bdf383f3 | 76 | |
744483ce DM |
77 | eval { |
78 | ||
79 | $self->{dbh}->begin_work; | |
80 | ||
bdf383f3 DM |
81 | $self->{dbh}->do("INSERT INTO Objectgroup (Name, Info, Class) " . |
82 | "VALUES (?, ?, ?)", undef, | |
83 | $name, $info, $obj->oclass()); | |
744483ce DM |
84 | |
85 | my $lid = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq'); | |
86 | ||
4a574ae2 | 87 | $og = PMG::RuleDB::Group->new($name, $info, $obj->oclass()); |
744483ce DM |
88 | $og->{id} = $lid; |
89 | ||
90 | $obj->{ogroup} = $lid; | |
91 | $id = $obj->save($self, 1); | |
92 | $obj->{id} = $id; # just to be sure | |
93 | ||
94 | $self->{dbh}->commit; | |
95 | }; | |
96 | if (my $err = $@) { | |
97 | $self->{dbh}->rollback; | |
98 | die $err; | |
99 | } | |
100 | return $og; | |
101 | } | |
102 | ||
72d8bf49 DM |
103 | sub load_groups { |
104 | my ($self, $rule) = @_; | |
105 | ||
9ef3f143 | 106 | defined($rule->{id}) || die "undefined rule id: ERROR"; |
72d8bf49 DM |
107 | |
108 | my $sth = $self->{dbh}->prepare( | |
109 | "SELECT RuleGroup.Grouptype, Objectgroup.ID, " . | |
9ef3f143 | 110 | "Objectgroup.Name, Objectgroup.Info " . |
72d8bf49 DM |
111 | "FROM Rulegroup, Objectgroup " . |
112 | "WHERE Rulegroup.Rule_ID = ? and " . | |
113 | "Rulegroup.Objectgroup_ID = Objectgroup.ID " . | |
114 | "ORDER BY RuleGroup.Grouptype"); | |
115 | ||
116 | my $groups = (); | |
117 | ||
118 | $sth->execute($rule->{id}); | |
119 | ||
4108629e | 120 | my ($from, $to, $when, $what, $action) = ([], [], [], [], []); |
72d8bf49 DM |
121 | |
122 | while (my $ref = $sth->fetchrow_hashref()) { | |
123 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}); | |
124 | $og->{id} = $ref->{id}; | |
125 | ||
126 | if ($ref->{'grouptype'} == 0) { #from | |
127 | push @$from, $og; | |
128 | } elsif ($ref->{'grouptype'} == 1) { # to | |
129 | push @$to, $og; | |
130 | } elsif ($ref->{'grouptype'} == 2) { # when | |
131 | push @$when, $og; | |
132 | } elsif ($ref->{'grouptype'} == 3) { # what | |
133 | push @$what, $og; | |
134 | } elsif ($ref->{'grouptype'} == 4) { # action | |
135 | my $objects = $self->load_group_objects($og->{id}); | |
136 | my $obj = @$objects[0]; | |
9ef3f143 | 137 | defined($obj) || die "undefined action object: ERROR"; |
72d8bf49 DM |
138 | $og->{action} = $obj; |
139 | push @$action, $og; | |
140 | } | |
141 | } | |
142 | ||
143 | $sth->finish(); | |
144 | ||
145 | return ($from, $to, $when, $what, $action); | |
146 | } | |
147 | ||
4108629e DM |
148 | sub load_groups_by_name { |
149 | my ($self, $rule) = @_; | |
150 | ||
151 | my ($from, $to, $when, $what, $action) = | |
152 | $self->load_groups($rule); | |
153 | ||
154 | return { | |
155 | from => $from, | |
156 | to => $to, | |
157 | when => $when, | |
158 | what => $what, | |
159 | action => $action, | |
160 | }; | |
161 | } | |
162 | ||
72d8bf49 DM |
163 | sub save_group { |
164 | my ($self, $og) = @_; | |
165 | ||
9ef3f143 DM |
166 | defined($og->{name}) || |
167 | die "undefined group attribute - name: ERROR"; | |
168 | defined($og->{info}) || | |
169 | die "undefined group attribute - info: ERROR"; | |
170 | defined($og->{class}) || | |
171 | die "undefined group attribute - class: ERROR"; | |
72d8bf49 DM |
172 | |
173 | if (defined($og->{id})) { | |
174 | ||
9ef3f143 | 175 | $self->{dbh}->do("UPDATE Objectgroup " . |
72d8bf49 | 176 | "SET Name = ?, Info = ? " . |
9ef3f143 | 177 | "WHERE ID = ?", undef, |
4418cffc SI |
178 | encode('UTF-8', $og->{name}), |
179 | encode('UTF-8', $og->{info}), | |
180 | $og->{id}); | |
9ef3f143 | 181 | |
72d8bf49 DM |
182 | return $og->{id}; |
183 | ||
184 | } else { | |
185 | my $sth = $self->{dbh}->prepare( | |
186 | "INSERT INTO Objectgroup (Name, Info, Class) " . | |
187 | "VALUES (?, ?, ?);"); | |
188 | ||
4418cffc | 189 | $sth->execute(encode('UTF-8', $og->name), encode('UTF-8', $og->info), $og->class); |
72d8bf49 DM |
190 | |
191 | return $og->{id} = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq'); | |
192 | } | |
193 | ||
194 | return undef; | |
195 | } | |
196 | ||
72d8bf49 DM |
197 | sub delete_group { |
198 | my ($self, $groupid) = @_; | |
199 | ||
9ef3f143 | 200 | defined($groupid) || die "undefined group id: ERROR"; |
72d8bf49 DM |
201 | |
202 | eval { | |
203 | ||
204 | $self->{dbh}->begin_work; | |
205 | ||
206 | # test if group is used in rules | |
207 | $self->{dbh}->do("LOCK TABLE RuleGroup IN EXCLUSIVE MODE"); | |
208 | ||
209 | my $sth = $self->{dbh}->prepare( | |
210 | "SELECT Rule.Name as rulename, ObjectGroup.Name as groupname " . | |
211 | "FROM RuleGroup, Rule, ObjectGroup WHERE " . | |
212 | "ObjectGroup.ID = ? AND Objectgroup_ID = ObjectGroup.ID AND " . | |
213 | "Rule_ID = Rule.ID"); | |
214 | ||
215 | $sth->execute($groupid); | |
216 | ||
217 | if (my $ref = $sth->fetchrow_hashref()) { | |
4418cffc SI |
218 | my $groupname = PMG::Utils::try_decode_utf8($ref->{groupname}); |
219 | my $rulename = PMG::Utils::try_decode_utf8($ref->{rulename}); | |
220 | die "Group '$groupname' is used by rule '$rulename' - unable to delete\n"; | |
72d8bf49 DM |
221 | } |
222 | ||
223 | $sth->finish(); | |
224 | ||
9ef3f143 | 225 | $self->{dbh}->do("DELETE FROM ObjectGroup " . |
72d8bf49 DM |
226 | "WHERE ID = ?", undef, $groupid); |
227 | ||
9ef3f143 | 228 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 DM |
229 | "WHERE Objectgroup_ID = ?", undef, $groupid); |
230 | ||
231 | $sth = $self->{dbh}->prepare("SELECT * FROM Object " . | |
232 | "where Objectgroup_ID = ?"); | |
233 | $sth->execute($groupid); | |
9ef3f143 | 234 | |
72d8bf49 | 235 | while (my $ref = $sth->fetchrow_hashref()) { |
9ef3f143 | 236 | $self->{dbh}->do("DELETE FROM Attribut " . |
72d8bf49 DM |
237 | "WHERE Object_ID = ?", undef, $ref->{id}); |
238 | } | |
9ef3f143 | 239 | |
72d8bf49 DM |
240 | $sth->finish(); |
241 | ||
9ef3f143 | 242 | $self->{dbh}->do("DELETE FROM Object " . |
72d8bf49 | 243 | "WHERE Objectgroup_ID = ?", undef, $groupid); |
9ef3f143 | 244 | |
72d8bf49 DM |
245 | $self->{dbh}->commit; |
246 | }; | |
247 | if (my $err = $@) { | |
248 | $self->{dbh}->rollback; | |
9ef3f143 | 249 | die $err; |
72d8bf49 DM |
250 | } |
251 | ||
252 | return undef; | |
253 | } | |
254 | ||
255 | sub load_objectgroups { | |
256 | my ($self, $class, $id) = @_; | |
9ef3f143 | 257 | |
72d8bf49 | 258 | my $sth; |
9ef3f143 DM |
259 | |
260 | defined($class) || die "undefined object class"; | |
261 | ||
72d8bf49 DM |
262 | if (!(defined($id))) { |
263 | $sth = $self->{dbh}->prepare( | |
264 | "SELECT * FROM Objectgroup where Class = ? ORDER BY name"); | |
265 | $sth->execute($class); | |
9ef3f143 | 266 | |
72d8bf49 DM |
267 | } else { |
268 | $sth = $self->{dbh}->prepare( | |
269 | "SELECT * FROM Objectgroup where Class like ? and id = ? " . | |
270 | "order by name"); | |
271 | $sth->execute($class,$id); | |
272 | } | |
9ef3f143 | 273 | |
72d8bf49 DM |
274 | my $arr_og = (); |
275 | while (my $ref = $sth->fetchrow_hashref()) { | |
276 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}, | |
277 | $ref->{class}); | |
278 | $og->{id} = $ref->{id}; | |
279 | ||
280 | if ($class eq 'action') { | |
281 | my $objects = $self->load_group_objects($og->{id}); | |
282 | my $obj = @$objects[0]; | |
9ef3f143 | 283 | defined($obj) || die "undefined action object: ERROR"; |
72d8bf49 DM |
284 | $og->{action} = $obj; |
285 | } | |
286 | push @$arr_og, $og; | |
287 | } | |
9ef3f143 | 288 | |
72d8bf49 DM |
289 | $sth->finish(); |
290 | ||
291 | return $arr_og; | |
292 | } | |
293 | ||
294 | sub get_object { | |
295 | my ($self, $otype) = @_; | |
296 | ||
8671ea1c SI |
297 | my $obj; |
298 | ||
299 | # FIXME: remove deprecated types and files with PMG 8.0 | |
300 | my $deprecated_types = { | |
301 | 4004 => "Attach", | |
302 | 4008 => "ReportSpam", | |
303 | 4999 => "Counter", | |
304 | }; | |
9ef3f143 | 305 | |
72d8bf49 DM |
306 | # WHO OBJECTS |
307 | if ($otype == PMG::RuleDB::Domain::otype()) { | |
308 | $obj = PMG::RuleDB::Domain->new(); | |
9ef3f143 | 309 | } |
72d8bf49 DM |
310 | elsif ($otype == PMG::RuleDB::ReceiverDomain::otype) { |
311 | $obj = PMG::RuleDB::ReceiverDomain->new(); | |
9ef3f143 | 312 | } |
72d8bf49 DM |
313 | elsif ($otype == PMG::RuleDB::WhoRegex::otype) { |
314 | $obj = PMG::RuleDB::WhoRegex->new(); | |
9ef3f143 | 315 | } |
72d8bf49 DM |
316 | elsif ($otype == PMG::RuleDB::ReceiverRegex::otype) { |
317 | $obj = PMG::RuleDB::ReceiverRegex->new(); | |
9ef3f143 | 318 | } |
72d8bf49 DM |
319 | elsif ($otype == PMG::RuleDB::EMail::otype) { |
320 | $obj = PMG::RuleDB::EMail->new(); | |
9ef3f143 | 321 | } |
72d8bf49 DM |
322 | elsif ($otype == PMG::RuleDB::Receiver::otype) { |
323 | $obj = PMG::RuleDB::Receiver->new(); | |
9ef3f143 | 324 | } |
72d8bf49 DM |
325 | elsif ($otype == PMG::RuleDB::IPAddress::otype) { |
326 | $obj = PMG::RuleDB::IPAddress->new(); | |
9ef3f143 | 327 | } |
72d8bf49 DM |
328 | elsif ($otype == PMG::RuleDB::IPNet::otype) { |
329 | $obj = PMG::RuleDB::IPNet->new(); | |
9ef3f143 | 330 | } |
10621236 DM |
331 | elsif ($otype == PMG::RuleDB::LDAP::otype) { |
332 | $obj = PMG::RuleDB::LDAP->new(); | |
333 | } | |
c712d3a2 DM |
334 | elsif ($otype == PMG::RuleDB::LDAPUser::otype) { |
335 | $obj = PMG::RuleDB::LDAPUser->new(); | |
336 | } | |
72d8bf49 DM |
337 | # WHEN OBJECTS |
338 | elsif ($otype == PMG::RuleDB::TimeFrame::otype) { | |
339 | $obj = PMG::RuleDB::TimeFrame->new(); | |
9ef3f143 | 340 | } |
72d8bf49 DM |
341 | # WHAT OBJECTS |
342 | elsif ($otype == PMG::RuleDB::Spam::otype) { | |
343 | $obj = PMG::RuleDB::Spam->new(); | |
344 | } | |
345 | elsif ($otype == PMG::RuleDB::Virus::otype) { | |
346 | $obj = PMG::RuleDB::Virus->new(); | |
347 | } | |
348 | elsif ($otype == PMG::RuleDB::MatchField::otype) { | |
349 | $obj = PMG::RuleDB::MatchField->new(); | |
350 | } | |
351 | elsif ($otype == PMG::RuleDB::MatchFilename::otype) { | |
352 | $obj = PMG::RuleDB::MatchFilename->new(); | |
353 | } | |
5e809f47 DC |
354 | elsif ($otype == PMG::RuleDB::MatchArchiveFilename::otype) { |
355 | $obj = PMG::RuleDB::MatchArchiveFilename->new(); | |
356 | } | |
72d8bf49 DM |
357 | elsif ($otype == PMG::RuleDB::ContentTypeFilter::otype) { |
358 | $obj = PMG::RuleDB::ContentTypeFilter->new(); | |
359 | } | |
c4741113 DM |
360 | elsif ($otype == PMG::RuleDB::ArchiveFilter::otype) { |
361 | $obj = PMG::RuleDB::ArchiveFilter->new(); | |
362 | } | |
72d8bf49 DM |
363 | # ACTION OBJECTS |
364 | elsif ($otype == PMG::RuleDB::ModField::otype) { | |
365 | $obj = PMG::RuleDB::ModField->new(); | |
366 | } | |
367 | elsif ($otype == PMG::RuleDB::Accept::otype()) { | |
368 | $obj = PMG::RuleDB::Accept->new(); | |
369 | } | |
370 | elsif ($otype == PMG::RuleDB::ReportSpam::otype()) { | |
371 | $obj = PMG::RuleDB::ReportSpam->new(); | |
372 | } | |
373 | elsif ($otype == PMG::RuleDB::Attach::otype) { | |
374 | $obj = PMG::RuleDB::Attach->new(); | |
375 | } | |
376 | elsif ($otype == PMG::RuleDB::Disclaimer::otype) { | |
377 | $obj = PMG::RuleDB::Disclaimer->new(); | |
378 | } | |
379 | elsif ($otype == PMG::RuleDB::BCC::otype) { | |
380 | $obj = PMG::RuleDB::BCC->new(); | |
381 | } | |
382 | elsif ($otype == PMG::RuleDB::Quarantine::otype) { | |
383 | $obj = PMG::RuleDB::Quarantine->new(); | |
384 | } | |
385 | elsif ($otype == PMG::RuleDB::Block::otype) { | |
386 | $obj = PMG::RuleDB::Block->new(); | |
387 | } | |
388 | elsif ($otype == PMG::RuleDB::Counter::otype) { | |
389 | $obj = PMG::RuleDB::Counter->new(); | |
390 | } | |
391 | elsif ($otype == PMG::RuleDB::Remove::otype) { | |
392 | $obj = PMG::RuleDB::Remove->new(); | |
393 | } | |
394 | elsif ($otype == PMG::RuleDB::Notify::otype) { | |
395 | $obj = PMG::RuleDB::Notify->new(); | |
396 | } | |
397 | else { | |
9ef3f143 | 398 | die "proxmox: unknown object type: ERROR"; |
72d8bf49 | 399 | } |
9ef3f143 | 400 | |
8671ea1c SI |
401 | if ( grep( $_ == $otype, keys %$deprecated_types)) { |
402 | syslog('warning', "proxmox: deprecated object of type %s found!", | |
403 | $deprecated_types->{$otype}); | |
404 | } | |
72d8bf49 DM |
405 | return $obj; |
406 | } | |
407 | ||
8671ea1c | 408 | # FIXME: remove with PMG 8.0 |
72d8bf49 DM |
409 | sub load_counters_data { |
410 | my ($self) = @_; | |
9ef3f143 | 411 | |
72d8bf49 DM |
412 | my $sth = $self->{dbh}->prepare( |
413 | "SELECT Object.id, Objectgroup.name, Object.Value, Objectgroup.info " . | |
9ef3f143 | 414 | "FROM Object, Objectgroup " . |
72d8bf49 DM |
415 | "WHERE objectgroup.id = object.objectgroup_id and ObjectType = ? " . |
416 | "order by Objectgroup.name, Value"); | |
417 | ||
418 | my @data; | |
419 | ||
420 | $sth->execute(PMG::RuleDB::Counter->otype()); | |
421 | ||
9ef3f143 | 422 | while (my $ref = $sth->fetchrow_hashref()) { |
72d8bf49 DM |
423 | my $tmp = [$ref->{id},$ref->{name},$ref->{value},$ref->{info}]; |
424 | push (@data, $tmp); | |
425 | } | |
9ef3f143 | 426 | |
72d8bf49 | 427 | $sth->finish(); |
9ef3f143 DM |
428 | |
429 | return @data; | |
72d8bf49 DM |
430 | } |
431 | ||
432 | sub load_object { | |
433 | my ($self, $objid) = @_; | |
9ef3f143 | 434 | |
72d8bf49 DM |
435 | my $value = ''; |
436 | ||
9ef3f143 | 437 | defined($objid) || die "undefined object id"; |
72d8bf49 DM |
438 | |
439 | my $sth = $self->{dbh}->prepare("SELECT * FROM Object where ID = ?"); | |
440 | $sth->execute($objid); | |
9ef3f143 | 441 | |
72d8bf49 DM |
442 | my $ref = $sth->fetchrow_hashref(); |
443 | ||
444 | $sth->finish(); | |
445 | ||
9ef3f143 DM |
446 | if (defined($ref->{'value'})) { |
447 | $value = $ref->{'value'}; | |
72d8bf49 DM |
448 | } |
449 | ||
9ef3f143 | 450 | if (!(defined($ref->{'objecttype'}) && |
72d8bf49 DM |
451 | defined($ref->{'objectgroup_id'}))) { |
452 | return undef; | |
453 | } | |
454 | ||
455 | my $ogroup = $ref->{'objectgroup_id'}; | |
9ef3f143 | 456 | |
72d8bf49 DM |
457 | my $otype = $ref->{'objecttype'}; |
458 | my $obj = $self->get_object($otype); | |
459 | ||
13a96624 | 460 | return $obj->load_attr($self, $objid, $ogroup, $value); |
72d8bf49 DM |
461 | } |
462 | ||
744483ce DM |
463 | sub load_object_full { |
464 | my ($self, $id, $gid, $exp_otype) = @_; | |
465 | ||
466 | my $obj = $self->load_object($id); | |
467 | die "object '$id' does not exists\n" if !defined($obj); | |
468 | ||
469 | my $otype = $obj->otype(); | |
470 | die "wrong object type ($otype != $exp_otype)\n" | |
471 | if defined($exp_otype) && $otype != $exp_otype; | |
472 | ||
473 | die "wrong object group ($obj->{ogroup} != $gid)\n" | |
474 | if $obj->{ogroup} != $gid; | |
475 | ||
476 | return $obj; | |
477 | } | |
478 | ||
72d8bf49 DM |
479 | sub load_group_by_name { |
480 | my ($self, $name) = @_; | |
481 | ||
4418cffc | 482 | $name = encode('UTF-8', $name); |
9ef3f143 | 483 | my $sth = $self->{dbh}->prepare("SELECT * FROM Objectgroup " . |
72d8bf49 DM |
484 | "WHERE name = ?"); |
485 | ||
486 | $sth->execute($name); | |
9ef3f143 | 487 | |
72d8bf49 DM |
488 | while (my $ref = $sth->fetchrow_hashref()) { |
489 | my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info}, | |
490 | $ref->{class}); | |
491 | $og->{id} = $ref->{id}; | |
492 | ||
493 | $sth->finish(); | |
494 | ||
495 | if ($ref->{'class'} eq 'action') { | |
496 | my $objects = $self->load_group_objects($og->{id}); | |
497 | my $obj = @$objects[0]; | |
9ef3f143 | 498 | defined($obj) || die "undefined action object: ERROR"; |
72d8bf49 DM |
499 | $og->{action} = $obj; |
500 | } | |
9ef3f143 | 501 | |
72d8bf49 DM |
502 | return $og; |
503 | } | |
504 | ||
505 | $sth->finish(); | |
506 | ||
507 | return undef; | |
508 | } | |
509 | ||
510 | sub greylistexclusion_groupid { | |
511 | my ($self) = @_; | |
9ef3f143 | 512 | |
72d8bf49 | 513 | my $sth = $self->{dbh}->prepare( |
9ef3f143 DM |
514 | "select id from objectgroup where class='greylist' limit 1;"); |
515 | ||
72d8bf49 | 516 | $sth->execute(); |
9ef3f143 | 517 | |
72d8bf49 | 518 | my $ref = $sth->fetchrow_hashref(); |
9ef3f143 | 519 | |
72d8bf49 DM |
520 | return $ref->{id}; |
521 | } | |
522 | ||
523 | sub load_group_objects { | |
524 | my ($self, $ogid) = @_; | |
525 | ||
9ef3f143 | 526 | defined($ogid) || die "undefined group id: ERROR"; |
72d8bf49 | 527 | |
9ef3f143 DM |
528 | my $sth = $self->{dbh}->prepare( |
529 | "SELECT * FROM Object " . | |
72d8bf49 DM |
530 | "WHERE Objectgroup_ID = ? order by ObjectType,Value"); |
531 | ||
532 | my $objects = (); | |
533 | ||
534 | $sth->execute($ogid); | |
535 | ||
536 | while (my $ref = $sth->fetchrow_hashref()) { | |
537 | my $obj = $self->load_object($ref->{id}); | |
538 | push @$objects, $obj; | |
539 | } | |
540 | ||
541 | $sth->finish(); | |
542 | ||
543 | return $objects; | |
544 | } | |
545 | ||
546 | ||
547 | sub save_object { | |
548 | my ($self, $obj) = @_; | |
9ef3f143 | 549 | |
72d8bf49 DM |
550 | $obj->save($self); |
551 | ||
552 | return $obj->{id}; | |
553 | } | |
554 | ||
555 | sub group_add_object { | |
556 | my ($self, $group, $obj) = @_; | |
9ef3f143 | 557 | |
72d8bf49 | 558 | ($obj->oclass() eq $group->{class}) || |
9ef3f143 | 559 | die "wrong object class: ERROR"; |
72d8bf49 DM |
560 | |
561 | $obj->{ogroup} = $group->{id}; | |
9ef3f143 | 562 | |
72d8bf49 DM |
563 | $self->save_object($obj); |
564 | } | |
565 | ||
566 | sub delete_object { | |
567 | my ($self, $obj) = @_; | |
568 | ||
9ef3f143 | 569 | defined($obj->{id}) || die "undefined object id"; |
72d8bf49 DM |
570 | |
571 | eval { | |
572 | ||
573 | $self->{dbh}->begin_work; | |
574 | ||
9ef3f143 | 575 | $self->{dbh}->do("DELETE FROM Attribut " . |
72d8bf49 | 576 | "WHERE Object_ID = ?", undef, $obj->{id}); |
9ef3f143 DM |
577 | |
578 | $self->{dbh}->do("DELETE FROM Object " . | |
72d8bf49 DM |
579 | "WHERE ID = ?", |
580 | undef, $obj->{id}); | |
581 | ||
582 | $self->{dbh}->commit; | |
583 | }; | |
584 | if (my $err = $@) { | |
585 | $self->{dbh}->rollback; | |
586 | syslog('err', $err); | |
587 | return undef; | |
588 | } | |
589 | ||
590 | $obj->{id} = undef; | |
591 | ||
592 | return 1; | |
593 | } | |
594 | ||
595 | sub save_rule { | |
596 | my ($self, $rule) = @_; | |
597 | ||
9ef3f143 DM |
598 | defined($rule->{name}) || |
599 | die "undefined rule attribute - name: ERROR"; | |
600 | defined($rule->{priority}) || | |
601 | die "undefined rule attribute - priority: ERROR"; | |
602 | defined($rule->{active}) || | |
603 | die "undefined rule attribute - active: ERROR"; | |
604 | defined($rule->{direction}) || | |
605 | die "undefined rule attribute - direction: ERROR"; | |
72d8bf49 | 606 | |
4418cffc | 607 | my $rulename = encode('UTF-8', $rule->{name}); |
72d8bf49 DM |
608 | if (defined($rule->{id})) { |
609 | ||
610 | $self->{dbh}->do( | |
9ef3f143 | 611 | "UPDATE Rule " . |
72d8bf49 | 612 | "SET Name = ?, Priority = ?, Active = ?, Direction = ? " . |
9ef3f143 | 613 | "WHERE ID = ?", undef, |
4418cffc | 614 | $rulename, $rule->{priority}, $rule->{active}, |
72d8bf49 DM |
615 | $rule->{direction}, $rule->{id}); |
616 | ||
617 | return $rule->{id}; | |
618 | ||
619 | } else { | |
620 | my $sth = $self->{dbh}->prepare( | |
621 | "INSERT INTO Rule (Name, Priority, Active, Direction) " . | |
622 | "VALUES (?, ?, ?, ?);"); | |
623 | ||
4418cffc | 624 | $sth->execute($rulename, $rule->priority, $rule->active, |
9ef3f143 | 625 | $rule->direction); |
72d8bf49 DM |
626 | |
627 | return $rule->{id} = PMG::Utils::lastid($self->{dbh}, 'rule_id_seq'); | |
628 | } | |
629 | ||
630 | return undef; | |
631 | } | |
632 | ||
633 | sub delete_rule { | |
634 | my ($self, $ruleid) = @_; | |
635 | ||
9ef3f143 | 636 | defined($ruleid) || die "undefined rule id: ERROR"; |
72d8bf49 DM |
637 | |
638 | eval { | |
639 | $self->{dbh}->begin_work; | |
640 | ||
9ef3f143 | 641 | $self->{dbh}->do("DELETE FROM Rule " . |
72d8bf49 | 642 | "WHERE ID = ?", undef, $ruleid); |
9ef3f143 | 643 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 DM |
644 | "WHERE Rule_ID = ?", undef, $ruleid); |
645 | ||
646 | $self->{dbh}->commit; | |
647 | }; | |
648 | if (my $err = $@) { | |
649 | $self->{dbh}->rollback; | |
650 | syslog('err', $err); | |
651 | return undef; | |
652 | } | |
653 | ||
654 | return 1; | |
655 | } | |
656 | ||
657 | sub delete_testrules { | |
658 | my ($self) = @_; | |
659 | ||
660 | eval { | |
661 | $self->{dbh}->begin_work; | |
662 | ||
663 | my $sth = $self->{dbh}->prepare("Select id FROM Rule " . | |
664 | "WHERE name = 'testrule'"); | |
665 | $sth->execute(); | |
666 | ||
667 | while(my $ref = $sth->fetchrow_hashref()) { | |
9ef3f143 | 668 | $self->{dbh}->do("DELETE FROM Rule " . |
72d8bf49 | 669 | "WHERE ID = ?", undef, $ref->{id}); |
9ef3f143 | 670 | $self->{dbh}->do("DELETE FROM RuleGroup " . |
72d8bf49 DM |
671 | "WHERE Rule_ID = ?", undef, $ref->{id}); |
672 | } | |
673 | $sth->finish(); | |
674 | ||
675 | $self->{dbh}->commit; | |
676 | }; | |
677 | if (my $err = $@) { | |
678 | $self->{dbh}->rollback; | |
9ef3f143 | 679 | die $err; |
72d8bf49 DM |
680 | } |
681 | ||
9ef3f143 | 682 | return 1; |
72d8bf49 DM |
683 | } |
684 | ||
0e022653 DM |
685 | my $grouptype_hash = { |
686 | from => 0, | |
687 | to => 1, | |
688 | when => 2, | |
689 | what => 3, | |
690 | action => 4, | |
691 | }; | |
692 | ||
72d8bf49 | 693 | sub rule_add_group { |
0e022653 DM |
694 | my ($self, $ruleid, $groupid, $gtype_str) = @_; |
695 | ||
696 | my $gtype = $grouptype_hash->{$gtype_str} // | |
697 | die "unknown group type '$gtype_str'\n"; | |
72d8bf49 | 698 | |
9ef3f143 DM |
699 | defined($ruleid) || die "undefined rule id: ERROR"; |
700 | defined($groupid) || die "undefined group id: ERROR"; | |
701 | defined($gtype) || die "undefined group type: ERROR"; | |
702 | ||
703 | $self->{dbh}->do("INSERT INTO RuleGroup " . | |
72d8bf49 | 704 | "(Objectgroup_ID, Rule_ID, Grouptype) " . |
9ef3f143 | 705 | "VALUES (?, ?, ?)", undef, |
72d8bf49 DM |
706 | $groupid, $ruleid, $gtype); |
707 | return 1; | |
708 | } | |
709 | ||
710 | sub rule_add_from_group { | |
711 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 712 | |
0e022653 | 713 | $self->rule_add_group($rule->{id}, $group->{id}, 'from'); |
72d8bf49 DM |
714 | } |
715 | ||
716 | sub rule_add_to_group { | |
717 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 718 | |
0e022653 | 719 | $self->rule_add_group($rule->{id}, $group->{id}, 'to'); |
72d8bf49 DM |
720 | } |
721 | ||
722 | sub rule_add_when_group { | |
723 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 724 | |
0e022653 | 725 | $self->rule_add_group($rule->{id}, $group->{id}, 'when'); |
72d8bf49 DM |
726 | } |
727 | ||
728 | sub rule_add_what_group { | |
729 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 730 | |
0e022653 | 731 | $self->rule_add_group($rule->{id}, $group->{id}, 'what'); |
72d8bf49 DM |
732 | } |
733 | ||
734 | sub rule_add_action { | |
735 | my ($self, $rule, $group) = @_; | |
9ef3f143 | 736 | |
0e022653 | 737 | $self->rule_add_group($rule->{id}, $group->{id}, 'action'); |
72d8bf49 DM |
738 | } |
739 | ||
740 | sub rule_remove_group { | |
0e022653 DM |
741 | my ($self, $ruleid, $groupid, $gtype_str) = @_; |
742 | ||
743 | my $gtype = $grouptype_hash->{$gtype_str} // | |
744 | die "unknown group type '$gtype_str'\n"; | |
72d8bf49 | 745 | |
9ef3f143 DM |
746 | defined($ruleid) || die "undefined rule id: ERROR"; |
747 | defined($groupid) || die "undefined group id: ERROR"; | |
748 | defined($gtype) || die "undefined group type: ERROR"; | |
72d8bf49 | 749 | |
9ef3f143 | 750 | $self->{dbh}->do("DELETE FROM RuleGroup WHERE " . |
72d8bf49 DM |
751 | "Objectgroup_ID = ? and Rule_ID = ? and Grouptype = ?", |
752 | undef, $groupid, $ruleid, $gtype); | |
753 | return 1; | |
754 | } | |
755 | ||
756 | sub load_rule { | |
757 | my ($self, $id) = @_; | |
9ef3f143 DM |
758 | |
759 | defined($id) || die "undefined id: ERROR"; | |
760 | ||
72d8bf49 DM |
761 | my $sth = $self->{dbh}->prepare( |
762 | "SELECT * FROM Rule where id = ? ORDER BY Priority DESC"); | |
763 | ||
764 | my $rules = (); | |
765 | ||
766 | $sth->execute($id); | |
767 | ||
768 | my $ref = $sth->fetchrow_hashref(); | |
af748e8c | 769 | die "rule '$id' does not exist\n" if !defined($ref); |
9ef3f143 | 770 | |
72d8bf49 DM |
771 | my $rule = PMG::RuleDB::Rule->new($ref->{name}, $ref->{priority}, |
772 | $ref->{active}, $ref->{direction}); | |
773 | $rule->{id} = $ref->{id}; | |
9ef3f143 DM |
774 | |
775 | return $rule; | |
72d8bf49 DM |
776 | } |
777 | ||
778 | sub load_rules { | |
779 | my ($self) = @_; | |
780 | ||
781 | my $sth = $self->{dbh}->prepare( | |
782 | "SELECT * FROM Rule ORDER BY Priority DESC"); | |
783 | ||
784 | my $rules = (); | |
785 | ||
786 | $sth->execute(); | |
9ef3f143 | 787 | |
72d8bf49 | 788 | while (my $ref = $sth->fetchrow_hashref()) { |
4418cffc SI |
789 | my $rulename = PMG::Utils::try_decode_utf8($ref->{name}); |
790 | my $rule = PMG::RuleDB::Rule->new($rulename, $ref->{priority}, | |
72d8bf49 DM |
791 | $ref->{active}, $ref->{direction}); |
792 | $rule->{id} = $ref->{id}; | |
793 | push @$rules, $rule; | |
794 | } | |
795 | ||
796 | $sth->finish(); | |
797 | ||
798 | return $rules; | |
799 | } | |
800 | ||
801 | ||
802 | ||
803 | 1; | |
804 | ||
805 | __END__ | |
806 | ||
807 | =head1 PMG::RuleDB | |
808 | ||
809 | The RuleDB Object manages the database connection and provides an interface to manipulate the database without SQL. A typical application first create a RuleDB object: | |
810 | ||
811 | use PMG::RuleDB; | |
812 | ||
813 | $ruledb = PMG::RuleDB->new(); | |
814 | ||
815 | =head2 Database Overview | |
816 | ||
817 | =head3 Rules | |
818 | ||
819 | Rules contains sets of Groups, grouped by classes (FROM, TO, WHEN, WHAT and ACTION). Each rule has an associated priority and and active/inactive marker. | |
820 | ||
821 | =head3 Groups | |
822 | ||
823 | A Group is a set of Objects. | |
824 | ||
825 | =head3 Objects | |
826 | ||
827 | Objects contains the filter data. | |
828 | ||
829 | =head3 Rule Semantics | |
830 | ||
831 | The classes have 'and' semantics. A rule matches if the checks in FROM, TO, WHEN and WHAT classes returns TRUE. | |
832 | ||
833 | Within a class the objects are or'ed together. | |
834 | ||
835 | =head2 Managing Rules | |
836 | ||
837 | =head3 $ruledb->load_rules() | |
838 | ||
839 | Returns an array of Rules containing all rules in the database. | |
840 | ||
841 | =head3 $ruledb->save_rule ($rule) | |
842 | ||
843 | One can use the following code to add a new rule to the database: | |
844 | ||
845 | my $rule = PMG::RuleDB::Rule->new ($name, $priority, $active); | |
846 | $ruledb->save_rule ($rule); | |
847 | ||
9ef3f143 | 848 | You can also use save_rule() to commit changes back to the database. |
72d8bf49 DM |
849 | |
850 | =head3 $ruledb->delete_rule ($ruleid) | |
851 | ||
852 | Removes the rule from the database. | |
853 | ||
854 | =head3 $ruledb->rule_add_group ($rule, $og, $gtype) | |
855 | ||
856 | Add an object group to the rule. | |
857 | ||
858 | Possible values for $gtype are: | |
859 | ||
0e022653 | 860 | 'from' 'to', 'when', 'what', 'action' |
72d8bf49 DM |
861 | |
862 | =head3 $ruledb->rule_remove_group ($rule, $og, $gtype) | |
863 | ||
864 | Removes an object group from the rule. | |
865 | ||
866 | =head2 Managing Objects and Groups | |
867 | ||
868 | =head3 $ruledb->load_groups ($rule) | |
869 | ||
870 | Return all object groups belonging to a rule. Data is divided into separate arrays: | |
871 | ||
9ef3f143 | 872 | my ($from, $to, $when, $what, $action) = |
72d8bf49 DM |
873 | $ruledb->load_groups($rule); |
874 | ||
875 | =head3 $ruledb->save_group ($og) | |
876 | ||
877 | This can be used to add or modify an Group. This code segemnt creates | |
878 | a new object group: | |
879 | ||
880 | $og = PMG::RuleDB::Group->new ($name, $desc); | |
881 | $ruledb->save_group ($og); | |
882 | ||
883 | ||
884 | =head3 $ruledb->delete_group ($groupid) | |
885 | ||
9ef3f143 DM |
886 | Deletes the object group, all reference to the group and all objects |
887 | belonging to this group from the Database. | |
72d8bf49 DM |
888 | |
889 | =head3 $ruledb->group_add_object ($og, $obj) | |
890 | ||
891 | Attach an object to an object group. | |
892 | ||
893 | =head3 $ruledb->save_object ($obj) | |
894 | ||
9ef3f143 | 895 | Save or update an object. This can be used to add new objects |
1359baef | 896 | to the database (although group_add_object() is the preferred way): |
72d8bf49 DM |
897 | |
898 | $obj = PMG::RuleDB::EMail->new ('.*@mydomain.com'); | |
899 | # we need to set the object group manually | |
900 | $obj->ogroup ($group->id); | |
901 | $ruledb->save_object ($obj); | |
9ef3f143 | 902 | |
72d8bf49 DM |
903 | |
904 | =head3 $ruledb->delete_object ($obj) | |
905 | ||
9ef3f143 | 906 | Deletes the object, all references to the object and all object |
72d8bf49 | 907 | attributes from the database. |