database: use foreign keys for rule and object group attributes

the change is small, reduces code and lets the task of referential
integrity to PostgreSQL.

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
Tested-by: Dominik Csapak <d.csapak@proxmox.com>
Reviewed-by: Dominik Csapak <d.csapak@proxmox.com>

diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
index f46861873e583fff1800e5a51e5dee40a0a6e799..17ba44d4a0294b0131ccc72898c2b312565923e1 100644 (file)
--- a/src/PMG/Cluster.pm
+++ b/src/PMG/Cluster.pm
@@ -532,8 +532,6 @@ sub sync_ruledb_from_master {
        $ldb->do("DELETE FROM ObjectGroup");
        $ldb->do("DELETE FROM Object");
        $ldb->do("DELETE FROM Attribut");
-       $ldb->do("DELETE FROM Objectgroup_Attributes");
-       $ldb->do("DELETE FROM Rule_Attributes");
 
        eval {
            $rdb->begin_work;

diff --git a/src/PMG/DBTools.pm b/src/PMG/DBTools.pm
index 3e814dc4e01336f0ef1bbe485b0c3b7555275ecb..6112566b414f6d03c90c8889af95bd5d70ac987b 100644 (file)
--- a/src/PMG/DBTools.pm
+++ b/src/PMG/DBTools.pm
@@ -297,7 +297,7 @@ __EOD
 
 my $rule_attributes_cmd = <<__EOD;
     CREATE TABLE Rule_Attributes (
-      Rule_ID INTEGER NOT NULL,
+      Rule_ID INTEGER NOT NULL REFERENCES Rule (ID) ON DELETE CASCADE,
       Name VARCHAR(20) NOT NULL,
       Value BYTEA NULL,
       PRIMARY KEY (Rule_ID, Name)
@@ -309,7 +309,7 @@ __EOD
 
 my $object_group_attributes_cmd = <<__EOD;
     CREATE TABLE Objectgroup_Attributes (
-      Objectgroup_ID INTEGER NOT NULL,
+      Objectgroup_ID INTEGER NOT NULL REFERENCES Objectgroup (ID) ON DELETE CASCADE,
       Name VARCHAR(20) NOT NULL,
       Value BYTEA NULL,
       PRIMARY KEY (Objectgroup_ID, Name)
@@ -635,8 +635,6 @@ sub init_ruledb {
        $dbh->do(
            "DELETE FROM Rule;"
            ." DELETE FROM RuleGroup;"
-           ." DELETE FROM Rule_Attributes;"
-           ." DELETE FROM Objectgroup_Attributes;"
            ." DELETE FROM Attribut WHERE Object_ID NOT IN ($glids);"
            ." DELETE FROM Object WHERE ID NOT IN ($glids);"
            ." DELETE FROM Objectgroup WHERE class != 'greylist';"

diff --git a/src/PMG/RuleDB.pm b/src/PMG/RuleDB.pm
index e5fe56eeea4df025984f313902b2763ce1da5f86..315f79b9c9606ddcfe841807a446b23a5a299622 100644 (file)
--- a/src/PMG/RuleDB.pm
+++ b/src/PMG/RuleDB.pm
@@ -276,9 +276,6 @@ sub delete_group {
        $self->{dbh}->do("DELETE FROM RuleGroup " .
                         "WHERE Objectgroup_ID = ?", undef, $groupid);
 
-       $self->{dbh}->do("DELETE FROM Objectgroup_Attributes " .
-                        "WHERE Objectgroup_ID = ?", undef, $groupid);
-
        $sth = $self->{dbh}->prepare("SELECT * FROM Object " .
                                      "where Objectgroup_ID = ?");
        $sth->execute($groupid);
@@ -772,9 +769,6 @@ sub delete_rule {
                         "WHERE ID = ?", undef, $ruleid);
        $self->{dbh}->do("DELETE FROM RuleGroup " .
                         "WHERE Rule_ID = ?", undef, $ruleid);
-       $self->{dbh}->do("DELETE FROM Rule_Attributes " .
-                        "WHERE Rule_ID = ?", undef, $ruleid);
-
        $self->{dbh}->commit;
     };
     if (my $err = $@) {