[pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes

[pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes

[Stoiko Ivanov]

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

Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
---
Thought I send the patch now, as once the version without the constraints
is made public adding them later is not feasible (as users might already
have inconsistent data)
gave it a minimal spin - and think it should work - but would be grateful
for a second set of eyes, from someone more experienced with postgresql

 src/PMG/Cluster.pm | 2 --
 src/PMG/DBTools.pm | 6 ++----
 src/PMG/RuleDB.pm  | 6 ------
 3 files changed, 2 insertions(+), 12 deletions(-)

diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
index f468618..17ba44d 100644
--- 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 3e814dc..6112566 100644
--- 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 e5fe56e..315f79b 100644
--- 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 = $@) {
-- 
2.39.2

Re: [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes

[Stefan Hanreich]

As discussed, looks good afaict - might want to add an index later for
faster joins since postgres doesn't add them automatically for FKs. I'd
assume the rule DB gets read quite often when processing mail but might
still be overkill for a couple hundred rows.

On 2/21/24 22:08, Stoiko Ivanov wrote:
> the change is small, reduces code and lets the task of referential
> integrity to postgresql
> 
> Signed-off-by: Stoiko Ivanov <s.ivanov@proxmox.com>
> ---
> Thought I send the patch now, as once the version without the constraints
> is made public adding them later is not feasible (as users might already
> have inconsistent data)
> gave it a minimal spin - and think it should work - but would be grateful
> for a second set of eyes, from someone more experienced with postgresql
> 
>  src/PMG/Cluster.pm | 2 --
>  src/PMG/DBTools.pm | 6 ++----
>  src/PMG/RuleDB.pm  | 6 ------
>  3 files changed, 2 insertions(+), 12 deletions(-)
> 
> diff --git a/src/PMG/Cluster.pm b/src/PMG/Cluster.pm
> index f468618..17ba44d 100644
> --- 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 3e814dc..6112566 100644
> --- 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 e5fe56e..315f79b 100644
> --- 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 = $@) {

Re: [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attribute

[Dominik Csapak]

On 2/22/24 10:47, Stefan Hanreich wrote:
> As discussed, looks good afaict - might want to add an index later for
> faster joins since postgres doesn't add them automatically for FKs. I'd
> assume the rule DB gets read quite often when processing mail but might
> still be overkill for a couple hundred rows.
> 

just fyi, the rule system only gets loaded once per smtp-filter startup
(via RuleCache) and then only when it's signaled to reload which should
only happen when the actual rule system changes

(i actually just sent a fix for the groups since writing you reminded me 
that if forgot that ^^)

Re: [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attribute

[Stefan Hanreich]

> (i actually just sent a fix for the groups since writing you reminded me
> that if forgot that ^^)

Always happy to help. My point with the indices is also wrong since I
overlooked the PRIMARY KEY statement, which should take care of creating
an INDEX anyway..

Re: [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes

[Dominik Csapak]

gave this a short test, but something seems wrong with backup and restore


as soon as i want to restore a backup with some attributes, the 
resulting rule system is empty...