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