From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from firstgate.proxmox.com (firstgate.proxmox.com [212.224.123.68]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by lists.proxmox.com (Postfix) with ESMTPS id ED5A1945D1 for ; Thu, 22 Feb 2024 10:47:49 +0100 (CET) Received: from firstgate.proxmox.com (localhost [127.0.0.1]) by firstgate.proxmox.com (Proxmox) with ESMTP id C5A965806 for ; Thu, 22 Feb 2024 10:47:19 +0100 (CET) Received: from proxmox-new.maurer-it.com (proxmox-new.maurer-it.com [94.136.29.106]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by firstgate.proxmox.com (Proxmox) with ESMTPS for ; Thu, 22 Feb 2024 10:47:19 +0100 (CET) Received: from proxmox-new.maurer-it.com (localhost.localdomain [127.0.0.1]) by proxmox-new.maurer-it.com (Proxmox) with ESMTP id CA9A844817 for ; Thu, 22 Feb 2024 10:47:18 +0100 (CET) Message-ID: Date: Thu, 22 Feb 2024 10:47:17 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird To: Stoiko Ivanov , pmg-devel@lists.proxmox.com References: <20240221210814.274027-1-s.ivanov@proxmox.com> Content-Language: en-US From: Stefan Hanreich In-Reply-To: <20240221210814.274027-1-s.ivanov@proxmox.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-SPAM-LEVEL: Spam detection results: 0 AWL 0.496 Adjusted score from AWL reputation of From: address BAYES_00 -1.9 Bayes spam probability is 0 to 1% DMARC_MISSING 0.1 Missing DMARC policy KAM_DMARC_STATUS 0.01 Test Rule for DKIM or SPF Failure with Strict Alignment SPF_HELO_NONE 0.001 SPF: HELO does not publish an SPF Record SPF_PASS -0.001 SPF: sender matches SPF record T_SCC_BODY_TEXT_LINE -0.01 - URIBL_BLOCKED 0.001 ADMINISTRATOR NOTICE: The query to URIBL was blocked. See http://wiki.apache.org/spamassassin/DnsBlocklists#dnsbl-block for more information. [cluster.pm, ruledb.pm, dbtools.pm] Subject: Re: [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes X-BeenThere: pmg-devel@lists.proxmox.com X-Mailman-Version: 2.1.29 Precedence: list List-Id: Proxmox Mail Gateway development discussion List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 22 Feb 2024 09:47:50 -0000 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 > --- > 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 = $@) {