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