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

[Stefan Hanreich <s.hanreich@proxmox.com>]

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 = $@) {