src/PMG/RuleDB.pm

   1 package PMG::RuleDB;
   2
   3 use strict;
   4 use warnings;
   5 use DBI;
   6 use HTML::Entities;
   7 use Data::Dumper;
   8 use Encode qw(encode);
   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;
  27 use PMG::RuleDB::LDAP;
  28 use PMG::RuleDB::LDAPUser;
  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;
  38 use PMG::RuleDB::MatchArchiveFilename;
  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;
  48 use PMG::RuleDB::ArchiveFilter;
  49
  50 sub new {
  51     my ($type, $dbh) = @_;
  52
  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
  66 sub create_group_with_obj {
  67     my ($self, $obj, $name, $info) = @_;
  68
  69     my $og;
  70     my $id;
  71
  72     defined($obj) || die "proxmox: undefined object";
  73
  74     $name = encode('UTF-8', $name // '');
  75     $info = encode('UTF-8', $info // '');
  76
  77     eval {
  78
  79         $self->{dbh}->begin_work;
  80
  81         $self->{dbh}->do("INSERT INTO Objectgroup (Name, Info, Class) " .
  82                          "VALUES (?, ?, ?)", undef,
  83                          $name, $info, $obj->oclass());
  84
  85         my $lid = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq');
  86
  87         $og = PMG::RuleDB::Group->new($name, $info, $obj->oclass());
  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
 103 sub load_groups {
 104     my ($self, $rule) = @_;
 105
 106     defined($rule->{id}) || die "undefined rule id: ERROR";
 107
 108     my $sth = $self->{dbh}->prepare(
 109         "SELECT RuleGroup.Grouptype, Objectgroup.ID, " .
 110         "Objectgroup.Name, Objectgroup.Info " .
 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
 120     my ($from, $to, $when, $what, $action) = ([], [], [], [], []);
 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];
 137             defined($obj) || die "undefined action object: ERROR";
 138             $og->{action} = $obj;
 139             push @$action, $og;
 140         }
 141     }
 142
 143     $sth->finish();
 144
 145     return ($from, $to, $when, $what, $action);
 146 }
 147
 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
 163 sub update_group_attributes {
 164     my ($self, $og) = @_;
 165
 166     my $attributes = [qw(and invert)];
 167
 168     for my $attribute ($attributes->@*) {
 169         # only save the values if they're set to 1
 170         if ($og->{$attribute}) {
 171             $self->{dbh}->do(
 172                 "INSERT INTO Objectgroup_Attributes (Objectgroup_ID, Name, Value) " .
 173                 "VALUES (?, ?, ?) ".
 174                 "ON CONFLICT (Objectgroup_ID, Name) DO UPDATE SET Value = ?", undef,
 175                 $og->{id}, $attribute, $og->{$attribute}, $og->{$attribute},
 176             );
 177         } else {
 178             $self->{dbh}->do(
 179                 "DELETE FROM Objectgroup_Attributes " .
 180                 "WHERE Objectgroup_ID = ? AND Name = ?", undef,
 181                 $og->{id}, $attribute,
 182             );
 183         }
 184     }
 185 }
 186
 187 sub save_group {
 188     my ($self, $og) = @_;
 189
 190     defined($og->{name}) ||
 191         die "undefined group attribute - name: ERROR";
 192     defined($og->{info}) ||
 193         die "undefined group attribute - info: ERROR";
 194     defined($og->{class}) ||
 195         die "undefined group attribute - class: ERROR";
 196
 197     if (defined($og->{id})) {
 198         $self->{dbh}->begin_work;
 199
 200         eval {
 201             $self->{dbh}->do("UPDATE Objectgroup " .
 202                              "SET Name = ?, Info = ? " .
 203                              "WHERE ID = ?", undef,
 204                              encode('UTF-8', $og->{name}),
 205                              encode('UTF-8', $og->{info}),
 206                              $og->{id});
 207
 208             $self->update_group_attributes($og);
 209
 210             $self->{dbh}->commit;
 211         };
 212
 213         if (my $err = $@) {
 214             $self->{dbh}->rollback;
 215             syslog('err', $err);
 216             return undef;
 217         }
 218     } else {
 219         $self->{dbh}->begin_work;
 220
 221         eval {
 222             my $sth = $self->{dbh}->prepare(
 223                 "INSERT INTO Objectgroup (Name, Info, Class) " .
 224                 "VALUES (?, ?, ?);");
 225
 226             $sth->execute(encode('UTF-8', $og->name), encode('UTF-8', $og->info), $og->class);
 227
 228             $og->{id} = PMG::Utils::lastid($self->{dbh}, 'objectgroup_id_seq');
 229
 230             $self->update_group_attributes($og);
 231
 232             $self->{dbh}->commit;
 233         };
 234
 235         if (my $err = $@) {
 236             $self->{dbh}->rollback;
 237             syslog('err', $err);
 238             return undef;
 239         }
 240     }
 241
 242     return $og->{id};
 243 }
 244
 245 sub delete_group {
 246     my ($self, $groupid) = @_;
 247
 248     defined($groupid) || die "undefined group id: ERROR";
 249
 250     eval {
 251
 252         $self->{dbh}->begin_work;
 253
 254         # test if group is used in rules
 255         $self->{dbh}->do("LOCK TABLE RuleGroup IN EXCLUSIVE MODE");
 256
 257         my $sth = $self->{dbh}->prepare(
 258             "SELECT Rule.Name as rulename, ObjectGroup.Name as groupname " .
 259             "FROM RuleGroup, Rule, ObjectGroup WHERE " .
 260             "ObjectGroup.ID = ? AND Objectgroup_ID = ObjectGroup.ID AND " .
 261             "Rule_ID = Rule.ID");
 262
 263         $sth->execute($groupid);
 264
 265         if (my $ref = $sth->fetchrow_hashref()) {
 266             my $groupname = PMG::Utils::try_decode_utf8($ref->{groupname});
 267             my $rulename = PMG::Utils::try_decode_utf8($ref->{rulename});
 268             die "Group '$groupname' is used by rule '$rulename' - unable to delete\n";
 269         }
 270
 271         $sth->finish();
 272
 273         $self->{dbh}->do("DELETE FROM ObjectGroup " .
 274                          "WHERE ID = ?", undef, $groupid);
 275
 276         $self->{dbh}->do("DELETE FROM RuleGroup " .
 277                          "WHERE Objectgroup_ID = ?", undef, $groupid);
 278
 279         $sth = $self->{dbh}->prepare("SELECT * FROM Object " .
 280                                       "where Objectgroup_ID = ?");
 281         $sth->execute($groupid);
 282
 283         while (my $ref = $sth->fetchrow_hashref()) {
 284             $self->{dbh}->do("DELETE FROM Attribut " .
 285                              "WHERE Object_ID = ?", undef, $ref->{id});
 286         }
 287
 288         $sth->finish();
 289
 290         $self->{dbh}->do("DELETE FROM Object " .
 291                          "WHERE Objectgroup_ID = ?", undef, $groupid);
 292
 293         $self->{dbh}->commit;
 294     };
 295     if (my $err = $@) {
 296         $self->{dbh}->rollback;
 297         die $err;
 298     }
 299
 300     return undef;
 301 }
 302
 303 sub load_group_attributes {
 304     my ($self, $og) = @_;
 305
 306     my $attribute_sth = $self->{dbh}->prepare("SELECT * FROM Objectgroup_Attributes WHERE Objectgroup_ID = ?");
 307     $attribute_sth->execute($og->{id});
 308
 309     while (my $ref = $attribute_sth->fetchrow_hashref()) {
 310         $og->{and} = $ref->{value} if $ref->{name} eq 'and';
 311         $og->{invert} = $ref->{value} if $ref->{name} eq 'invert';
 312     }
 313 }
 314
 315 sub load_objectgroups {
 316     my ($self, $class, $id) = @_;
 317
 318     my $sth;
 319
 320     defined($class) || die "undefined object class";
 321
 322     $self->{dbh}->begin_work;
 323
 324     my $arr_og = ();
 325
 326     eval {
 327         if (!(defined($id))) {
 328             $sth = $self->{dbh}->prepare(
 329                 "SELECT * FROM Objectgroup where Class = ? ORDER BY name");
 330             $sth->execute($class);
 331
 332         } else {
 333             $sth = $self->{dbh}->prepare(
 334                 "SELECT * FROM Objectgroup where Class like ? and id = ? " .
 335                 "order by name");
 336             $sth->execute($class,$id);
 337         }
 338
 339         while (my $ref = $sth->fetchrow_hashref()) {
 340             my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info},
 341                                              $ref->{class});
 342             $og->{id} = $ref->{id};
 343
 344             if ($class eq 'action') {
 345                 my $objects = $self->load_group_objects($og->{id});
 346                 my $obj = @$objects[0];
 347                 defined($obj) || die "undefined action object: ERROR";
 348                 $og->{action} = $obj;
 349             } else {
 350                 $self->load_group_attributes($og);
 351             }
 352             push @$arr_og, $og;
 353         }
 354
 355         $sth->finish();
 356     };
 357
 358     my $err = $@;
 359
 360     $self->{dbh}->rollback; # finish transaction
 361
 362     die $err if $err;
 363
 364     return $arr_og;
 365 }
 366
 367 sub get_object {
 368     my ($self, $otype) = @_;
 369
 370     my $obj;
 371
 372     # FIXME: remove deprecated types and files with PMG 8.0
 373     my $deprecated_types = {
 374         4004 => "Attach",
 375         4008 => "ReportSpam",
 376         4999 => "Counter",
 377     };
 378
 379     # WHO OBJECTS
 380     if ($otype == PMG::RuleDB::Domain::otype()) {
 381         $obj = PMG::RuleDB::Domain->new();
 382     }
 383     elsif ($otype == PMG::RuleDB::ReceiverDomain::otype) {
 384         $obj = PMG::RuleDB::ReceiverDomain->new();
 385     }
 386     elsif ($otype == PMG::RuleDB::WhoRegex::otype) {
 387         $obj = PMG::RuleDB::WhoRegex->new();
 388     }
 389     elsif ($otype == PMG::RuleDB::ReceiverRegex::otype) {
 390         $obj = PMG::RuleDB::ReceiverRegex->new();
 391     }
 392     elsif ($otype == PMG::RuleDB::EMail::otype) {
 393         $obj = PMG::RuleDB::EMail->new();
 394     }
 395     elsif ($otype == PMG::RuleDB::Receiver::otype) {
 396         $obj = PMG::RuleDB::Receiver->new();
 397     }
 398     elsif ($otype == PMG::RuleDB::IPAddress::otype) {
 399         $obj = PMG::RuleDB::IPAddress->new();
 400     }
 401     elsif ($otype == PMG::RuleDB::IPNet::otype) {
 402         $obj = PMG::RuleDB::IPNet->new();
 403     }
 404     elsif ($otype == PMG::RuleDB::LDAP::otype) {
 405         $obj = PMG::RuleDB::LDAP->new();
 406     }
 407     elsif ($otype == PMG::RuleDB::LDAPUser::otype) {
 408         $obj = PMG::RuleDB::LDAPUser->new();
 409     }
 410     # WHEN OBJECTS
 411     elsif ($otype == PMG::RuleDB::TimeFrame::otype) {
 412         $obj = PMG::RuleDB::TimeFrame->new();
 413     }
 414     # WHAT OBJECTS
 415     elsif ($otype == PMG::RuleDB::Spam::otype) {
 416         $obj = PMG::RuleDB::Spam->new();
 417     }
 418     elsif ($otype == PMG::RuleDB::Virus::otype) {
 419         $obj = PMG::RuleDB::Virus->new();
 420     }
 421     elsif ($otype == PMG::RuleDB::MatchField::otype) {
 422         $obj = PMG::RuleDB::MatchField->new();
 423     }
 424     elsif ($otype == PMG::RuleDB::MatchFilename::otype) {
 425         $obj = PMG::RuleDB::MatchFilename->new();
 426     }
 427     elsif ($otype == PMG::RuleDB::MatchArchiveFilename::otype) {
 428         $obj = PMG::RuleDB::MatchArchiveFilename->new();
 429     }
 430     elsif ($otype == PMG::RuleDB::ContentTypeFilter::otype) {
 431         $obj = PMG::RuleDB::ContentTypeFilter->new();
 432     }
 433     elsif ($otype == PMG::RuleDB::ArchiveFilter::otype) {
 434         $obj = PMG::RuleDB::ArchiveFilter->new();
 435     }
 436     # ACTION OBJECTS
 437     elsif ($otype == PMG::RuleDB::ModField::otype) {
 438         $obj = PMG::RuleDB::ModField->new();
 439     }
 440     elsif ($otype == PMG::RuleDB::Accept::otype()) {
 441         $obj = PMG::RuleDB::Accept->new();
 442     }
 443     elsif ($otype == PMG::RuleDB::ReportSpam::otype()) {
 444         $obj = PMG::RuleDB::ReportSpam->new();
 445     }
 446     elsif ($otype == PMG::RuleDB::Attach::otype) {
 447         $obj = PMG::RuleDB::Attach->new();
 448     }
 449     elsif ($otype == PMG::RuleDB::Disclaimer::otype) {
 450         $obj = PMG::RuleDB::Disclaimer->new();
 451     }
 452     elsif ($otype == PMG::RuleDB::BCC::otype) {
 453         $obj = PMG::RuleDB::BCC->new();
 454     }
 455     elsif ($otype == PMG::RuleDB::Quarantine::otype) {
 456         $obj = PMG::RuleDB::Quarantine->new();
 457     }
 458     elsif ($otype == PMG::RuleDB::Block::otype) {
 459         $obj = PMG::RuleDB::Block->new();
 460     }
 461     elsif ($otype == PMG::RuleDB::Counter::otype) {
 462         $obj = PMG::RuleDB::Counter->new();
 463     }
 464     elsif ($otype == PMG::RuleDB::Remove::otype) {
 465         $obj = PMG::RuleDB::Remove->new();
 466     }
 467     elsif ($otype == PMG::RuleDB::Notify::otype) {
 468         $obj = PMG::RuleDB::Notify->new();
 469     }
 470     else {
 471             die "proxmox: unknown object type: ERROR";
 472     }
 473
 474     if ( grep( $_ == $otype, keys %$deprecated_types)) {
 475         syslog('warning', "proxmox: deprecated object of type %s found!",
 476             $deprecated_types->{$otype});
 477     }
 478     return $obj;
 479 }
 480
 481 # FIXME: remove with PMG 8.0
 482 sub load_counters_data {
 483     my ($self) = @_;
 484
 485     my $sth = $self->{dbh}->prepare(
 486         "SELECT Object.id, Objectgroup.name, Object.Value, Objectgroup.info " .
 487         "FROM Object, Objectgroup " .
 488         "WHERE objectgroup.id = object.objectgroup_id and ObjectType = ? " .
 489         "order by Objectgroup.name, Value");
 490
 491     my @data;
 492
 493     $sth->execute(PMG::RuleDB::Counter->otype());
 494
 495     while (my $ref = $sth->fetchrow_hashref()) {
 496         my $tmp = [$ref->{id},$ref->{name},$ref->{value},$ref->{info}];
 497         push (@data, $tmp);
 498     }
 499
 500     $sth->finish();
 501
 502     return @data;
 503 }
 504
 505 sub load_object {
 506     my ($self, $objid) = @_;
 507
 508     my $value = '';
 509
 510     defined($objid) || die "undefined object id";
 511
 512     my $sth = $self->{dbh}->prepare("SELECT * FROM Object where ID = ?");
 513     $sth->execute($objid);
 514
 515     my $ref = $sth->fetchrow_hashref();
 516
 517     $sth->finish();
 518
 519     if (defined($ref->{'value'})) {
 520         $value = $ref->{'value'};
 521     }
 522
 523     if (!(defined($ref->{'objecttype'}) &&
 524           defined($ref->{'objectgroup_id'}))) {
 525         return undef;
 526     }
 527
 528     my $ogroup = $ref->{'objectgroup_id'};
 529
 530     my $otype = $ref->{'objecttype'};
 531     my $obj = $self->get_object($otype);
 532
 533     return $obj->load_attr($self, $objid, $ogroup, $value);
 534 }
 535
 536 sub load_object_full {
 537     my ($self, $id, $gid, $exp_otype) = @_;
 538
 539     my $obj = $self->load_object($id);
 540     die "object '$id' does not exists\n" if !defined($obj);
 541
 542     my $otype = $obj->otype();
 543     die "wrong object type ($otype != $exp_otype)\n"
 544         if defined($exp_otype) && $otype != $exp_otype;
 545
 546     die "wrong object group ($obj->{ogroup} != $gid)\n"
 547         if $obj->{ogroup} != $gid;
 548
 549     return $obj;
 550 }
 551
 552 sub load_group_by_name {
 553     my ($self, $name) = @_;
 554
 555     $name = encode('UTF-8', $name);
 556     my $sth = $self->{dbh}->prepare("SELECT * FROM Objectgroup " .
 557                                     "WHERE name = ?");
 558
 559     $sth->execute($name);
 560
 561     while (my $ref = $sth->fetchrow_hashref()) {
 562         my $og = PMG::RuleDB::Group->new($ref->{name}, $ref->{info},
 563                                          $ref->{class});
 564         $og->{id} = $ref->{id};
 565
 566         $sth->finish();
 567
 568         if ($ref->{'class'} eq 'action') {
 569             my $objects = $self->load_group_objects($og->{id});
 570             my $obj = @$objects[0];
 571             defined($obj) || die "undefined action object: ERROR";
 572             $og->{action} = $obj;
 573         }
 574
 575         return $og;
 576     }
 577
 578     $sth->finish();
 579
 580     return undef;
 581 }
 582
 583 sub greylistexclusion_groupid {
 584     my ($self) = @_;
 585
 586     my $sth = $self->{dbh}->prepare(
 587         "select id from objectgroup where class='greylist' limit 1;");
 588
 589     $sth->execute();
 590
 591     my $ref = $sth->fetchrow_hashref();
 592
 593     return $ref->{id};
 594 }
 595
 596 sub load_group_objects {
 597     my ($self, $ogid) = @_;
 598
 599     defined($ogid) || die "undefined group id: ERROR";
 600
 601     my $sth = $self->{dbh}->prepare(
 602         "SELECT * FROM Object " .
 603         "WHERE Objectgroup_ID = ? order by ObjectType,Value");
 604
 605     my $objects = ();
 606
 607     $sth->execute($ogid);
 608
 609     while (my $ref = $sth->fetchrow_hashref()) {
 610         my $obj = $self->load_object($ref->{id});
 611         push @$objects, $obj;
 612     }
 613
 614     $sth->finish();
 615
 616     return $objects;
 617 }
 618
 619
 620 sub save_object {
 621     my ($self, $obj) = @_;
 622
 623     $obj->save($self);
 624
 625     return $obj->{id};
 626 }
 627
 628 sub group_add_object {
 629     my ($self, $group, $obj) = @_;
 630
 631     ($obj->oclass() eq $group->{class}) ||
 632         die "wrong object class: ERROR";
 633
 634     $obj->{ogroup} = $group->{id};
 635
 636     $self->save_object($obj);
 637 }
 638
 639 sub delete_object {
 640     my ($self, $obj) = @_;
 641
 642     defined($obj->{id}) || die "undefined object id";
 643
 644     eval {
 645
 646         $self->{dbh}->begin_work;
 647
 648         $self->{dbh}->do("DELETE FROM Attribut " .
 649                           "WHERE Object_ID = ?", undef, $obj->{id});
 650
 651         $self->{dbh}->do("DELETE FROM Object " .
 652                           "WHERE ID = ?",
 653                           undef, $obj->{id});
 654
 655         $self->{dbh}->commit;
 656     };
 657     if (my $err = $@) {
 658         $self->{dbh}->rollback;
 659         syslog('err', $err);
 660         return undef;
 661     }
 662
 663     $obj->{id} = undef;
 664
 665     return 1;
 666 }
 667
 668 sub update_rule_attributes {
 669     my ($self, $rule) = @_;
 670
 671     my $types = [qw(what when from to)];
 672     my $attributes = [qw(and invert)];
 673
 674     for my $type ($types->@*) {
 675         for my $attribute ($attributes->@*) {
 676             my $prop = "$type-$attribute";
 677
 678             # only save the values if they're set to 1
 679             if ($rule->{$prop}) {
 680                 $self->{dbh}->do(
 681                     "INSERT INTO Rule_Attributes (Rule_ID, Name, Value) " .
 682                     "VALUES (?, ?, ?) ".
 683                     "ON CONFLICT (Rule_ID, Name) DO UPDATE SET Value = ?", undef,
 684                     $rule->{id}, $prop, $rule->{$prop}, $rule->{$prop},
 685                 );
 686             } else {
 687                 $self->{dbh}->do(
 688                     "DELETE FROM Rule_Attributes " .
 689                     "WHERE Rule_ID = ? AND Name = ?", undef,
 690                     $rule->{id}, $prop,
 691                 );
 692             }
 693         }
 694     }
 695 }
 696
 697 sub save_rule {
 698     my ($self, $rule) = @_;
 699
 700     defined($rule->{name}) ||
 701         die "undefined rule attribute - name: ERROR";
 702     defined($rule->{priority}) ||
 703         die "undefined rule attribute - priority: ERROR";
 704     defined($rule->{active}) ||
 705         die "undefined rule attribute - active: ERROR";
 706     defined($rule->{direction}) ||
 707         die "undefined rule attribute - direction: ERROR";
 708
 709     my $rulename = encode('UTF-8', $rule->{name});
 710     if (defined($rule->{id})) {
 711         $self->{dbh}->begin_work;
 712
 713         eval {
 714             $self->{dbh}->do(
 715                 "UPDATE Rule " .
 716                 "SET Name = ?, Priority = ?, Active = ?, Direction = ? " .
 717                 "WHERE ID = ?", undef,
 718                 $rulename, $rule->{priority}, $rule->{active},
 719                 $rule->{direction}, $rule->{id});
 720
 721             $self->update_rule_attributes($rule);
 722
 723             $self->{dbh}->commit;
 724         };
 725
 726         if (my $err = $@) {
 727             $self->{dbh}->rollback;
 728             syslog('err', $err);
 729             return undef;
 730         }
 731     } else {
 732         $self->{dbh}->begin_work;
 733
 734         eval {
 735             my $sth = $self->{dbh}->prepare(
 736                 "INSERT INTO Rule (Name, Priority, Active, Direction) " .
 737                 "VALUES (?, ?, ?, ?);");
 738
 739             $sth->execute($rulename, $rule->priority, $rule->active,
 740                 $rule->direction);
 741
 742
 743             $rule->{id} = PMG::Utils::lastid($self->{dbh}, 'rule_id_seq');
 744
 745             $self->update_rule_attributes($rule);
 746
 747             $self->{dbh}->commit;
 748         };
 749
 750         if (my $err = $@) {
 751             $self->{dbh}->rollback;
 752             syslog('err', $err);
 753             return undef;
 754         }
 755     }
 756
 757     return $rule->{id};
 758 }
 759
 760 sub delete_rule {
 761     my ($self, $ruleid) = @_;
 762
 763     defined($ruleid) || die "undefined rule id: ERROR";
 764
 765     eval {
 766         $self->{dbh}->begin_work;
 767
 768         $self->{dbh}->do("DELETE FROM Rule " .
 769                          "WHERE ID = ?", undef, $ruleid);
 770         $self->{dbh}->do("DELETE FROM RuleGroup " .
 771                          "WHERE Rule_ID = ?", undef, $ruleid);
 772         $self->{dbh}->commit;
 773     };
 774     if (my $err = $@) {
 775         $self->{dbh}->rollback;
 776         syslog('err', $err);
 777         return undef;
 778     }
 779
 780     return 1;
 781 }
 782
 783 sub delete_testrules {
 784     my ($self) = @_;
 785
 786     eval {
 787         $self->{dbh}->begin_work;
 788
 789         my $sth = $self->{dbh}->prepare("Select id FROM Rule " .
 790                                         "WHERE name = 'testrule'");
 791         $sth->execute();
 792
 793         while(my $ref = $sth->fetchrow_hashref()) {
 794             $self->{dbh}->do("DELETE FROM Rule " .
 795                              "WHERE ID = ?", undef, $ref->{id});
 796             $self->{dbh}->do("DELETE FROM RuleGroup " .
 797                              "WHERE Rule_ID = ?", undef, $ref->{id});
 798         }
 799         $sth->finish();
 800
 801         $self->{dbh}->commit;
 802     };
 803     if (my $err = $@) {
 804         $self->{dbh}->rollback;
 805         die $err;
 806     }
 807
 808     return 1;
 809 }
 810
 811 my $grouptype_hash = {
 812     from => 0,
 813     to => 1,
 814     when => 2,
 815     what => 3,
 816     action => 4,
 817 };
 818
 819 sub rule_add_group {
 820     my ($self, $ruleid, $groupid, $gtype_str) = @_;
 821
 822     my $gtype = $grouptype_hash->{$gtype_str} //
 823         die "unknown group type '$gtype_str'\n";
 824
 825     defined($ruleid) || die "undefined rule id: ERROR";
 826     defined($groupid) || die "undefined group id: ERROR";
 827     defined($gtype) || die "undefined group type: ERROR";
 828
 829     $self->{dbh}->do("INSERT INTO RuleGroup " .
 830                      "(Objectgroup_ID, Rule_ID, Grouptype) " .
 831                      "VALUES (?, ?, ?)", undef,
 832                      $groupid, $ruleid, $gtype);
 833     return 1;
 834 }
 835
 836 sub rule_add_from_group {
 837     my ($self, $rule, $group) = @_;
 838
 839     $self->rule_add_group($rule->{id}, $group->{id}, 'from');
 840 }
 841
 842 sub rule_add_to_group {
 843     my ($self, $rule, $group) = @_;
 844
 845     $self->rule_add_group($rule->{id}, $group->{id}, 'to');
 846 }
 847
 848 sub rule_add_when_group {
 849     my ($self, $rule, $group) = @_;
 850
 851     $self->rule_add_group($rule->{id}, $group->{id}, 'when');
 852 }
 853
 854 sub rule_add_what_group {
 855     my ($self, $rule, $group) = @_;
 856
 857     $self->rule_add_group($rule->{id}, $group->{id}, 'what');
 858 }
 859
 860 sub rule_add_action {
 861     my ($self, $rule, $group) = @_;
 862
 863     $self->rule_add_group($rule->{id}, $group->{id}, 'action');
 864 }
 865
 866 sub rule_remove_group {
 867     my ($self, $ruleid, $groupid, $gtype_str) = @_;
 868
 869     my $gtype = $grouptype_hash->{$gtype_str} //
 870         die "unknown group type '$gtype_str'\n";
 871
 872     defined($ruleid) || die "undefined rule id: ERROR";
 873     defined($groupid) || die "undefined group id: ERROR";
 874     defined($gtype) || die "undefined group type: ERROR";
 875
 876     $self->{dbh}->do("DELETE FROM RuleGroup WHERE " .
 877                      "Objectgroup_ID = ? and Rule_ID = ? and Grouptype = ?",
 878                      undef, $groupid, $ruleid, $gtype);
 879     return 1;
 880 }
 881
 882 sub load_rule_attributes {
 883     my ($self, $rule) = @_;
 884
 885     my $types = [qw(what when from to)];
 886     my $attributes = [qw(and invert)];
 887
 888     my $attribute_sth = $self->{dbh}->prepare("SELECT * FROM Rule_Attributes WHERE Rule_ID = ?");
 889     $attribute_sth->execute($rule->{id});
 890
 891     while (my $ref = $attribute_sth->fetchrow_hashref()) {
 892         if ($ref->{name} =~ m/^((?:what|when|from|to)-(?:and|invert))$/) {
 893             my $prop = $1;
 894             $rule->{$prop} = $ref->{value};
 895         }
 896     }
 897 }
 898
 899 sub load_rule {
 900     my ($self, $id) = @_;
 901
 902     defined($id) || die "undefined id: ERROR";
 903
 904     $self->{dbh}->begin_work;
 905
 906     my $rule;
 907
 908     eval {
 909         my $sth = $self->{dbh}->prepare(
 910             "SELECT * FROM Rule where id = ? ORDER BY Priority DESC");
 911
 912         $sth->execute($id);
 913
 914         my $ref = $sth->fetchrow_hashref();
 915         die "rule '$id' does not exist\n" if !defined($ref);
 916
 917         $rule = PMG::RuleDB::Rule->new($ref->{name}, $ref->{priority},
 918                                           $ref->{active}, $ref->{direction});
 919         $rule->{id} = $ref->{id};
 920
 921         $self->load_rule_attributes($rule);
 922     };
 923     my $err = $@;
 924
 925     $self->{dbh}->rollback; # finish transaction
 926
 927     die $err if $err;
 928
 929     return $rule;
 930 }
 931
 932 sub load_rules {
 933     my ($self) = @_;
 934
 935     my $sth = $self->{dbh}->prepare(
 936         "SELECT * FROM Rule ORDER BY Priority DESC");
 937
 938     my $rules = ();
 939
 940     $sth->execute();
 941
 942     while (my $ref = $sth->fetchrow_hashref()) {
 943         my $rulename = PMG::Utils::try_decode_utf8($ref->{name});
 944         my $rule = PMG::RuleDB::Rule->new($rulename, $ref->{priority},
 945                                           $ref->{active}, $ref->{direction});
 946         $rule->{id} = $ref->{id};
 947         push @$rules, $rule;
 948     }
 949
 950     $sth->finish();
 951
 952     return $rules;
 953 }
 954
 955
 956
 957 1;
 958
 959 __END__
 960
 961 =head1 PMG::RuleDB
 962
 963 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:
 964
 965     use PMG::RuleDB;
 966
 967     $ruledb = PMG::RuleDB->new();
 968
 969 =head2 Database Overview
 970
 971 =head3 Rules
 972
 973 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.
 974
 975 =head3 Groups
 976
 977 A Group is a set of Objects.
 978
 979 =head3 Objects
 980
 981 Objects contains the filter data.
 982
 983 =head3 Rule Semantics
 984
 985 The classes have 'and' semantics. A rule matches if the checks in FROM, TO, WHEN and WHAT classes returns TRUE.
 986
 987 Within a class the objects are or'ed together.
 988
 989 =head2 Managing Rules
 990
 991 =head3 $ruledb->load_rules()
 992
 993     Returns an array of Rules containing all rules in the database.
 994
 995 =head3 $ruledb->save_rule ($rule)
 996
 997 One can use the following code to add a new rule to the database:
 998
 999     my $rule = PMG::RuleDB::Rule->new ($name, $priority, $active);
1000     $ruledb->save_rule ($rule);
1001
1002 You can also use save_rule() to commit changes back to the database.
1003
1004 =head3 $ruledb->delete_rule ($ruleid)
1005
1006 Removes the rule from the database.
1007
1008 =head3  $ruledb->rule_add_group ($rule, $og, $gtype)
1009
1010 Add an object group to the rule.
1011
1012 Possible values for $gtype are:
1013
1014     'from' 'to', 'when', 'what', 'action'
1015
1016 =head3  $ruledb->rule_remove_group ($rule, $og, $gtype)
1017
1018 Removes an object group from the rule.
1019
1020 =head2 Managing Objects and Groups
1021
1022 =head3 $ruledb->load_groups ($rule)
1023
1024 Return all object groups belonging to a rule. Data is divided into separate arrays:
1025
1026     my ($from, $to, $when, $what, $action) =
1027         $ruledb->load_groups($rule);
1028
1029 =head3 $ruledb->save_group ($og)
1030
1031 This can be used to add or modify an Group. This code segemnt creates
1032 a new object group:
1033
1034     $og = PMG::RuleDB::Group->new ($name, $desc);
1035     $ruledb->save_group ($og);
1036
1037
1038 =head3 $ruledb->delete_group ($groupid)
1039
1040 Deletes the object group, all reference to the group and all objects
1041 belonging to this group from the Database.
1042
1043 =head3 $ruledb->group_add_object ($og, $obj)
1044
1045 Attach an object to an object group.
1046
1047 =head3 $ruledb->save_object ($obj)
1048
1049 Save or update an object. This can be used to add new objects
1050 to the database (although group_add_object() is the preferred way):
1051
1052     $obj =  PMG::RuleDB::EMail->new ('.*@mydomain.com');
1053     # we need to set the object group manually
1054     $obj->ogroup ($group->id);
1055     $ruledb->save_object ($obj);
1056
1057
1058 =head3 $ruledb->delete_object ($obj)
1059
1060 Deletes the object, all references to the object  and all object
1061 attributes from the database.