From mboxrd@z Thu Jan 1 00:00:00 1970
Return-Path: <s.hanreich@proxmox.com>
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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; 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 <pmg-devel@lists.proxmox.com>; Thu, 22 Feb 2024 10:47:18 +0100 (CET)
Message-ID: <b1973b05-2767-4979-8319-28d49af681a5@proxmox.com>
Date: Thu, 22 Feb 2024 10:47:17 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: Stoiko Ivanov <s.ivanov@proxmox.com>, pmg-devel@lists.proxmox.com
References: <20240221210814.274027-1-s.ivanov@proxmox.com>
Content-Language: en-US
From: Stefan Hanreich <s.hanreich@proxmox.com>
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
<pmg-devel.lists.proxmox.com>
List-Unsubscribe: <https://lists.proxmox.com/cgi-bin/mailman/options/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=unsubscribe>
List-Archive: <http://lists.proxmox.com/pipermail/pmg-devel/>
List-Post: <mailto:pmg-devel@lists.proxmox.com>
List-Help: <mailto:pmg-devel-request@lists.proxmox.com?subject=help>
List-Subscribe: <https://lists.proxmox.com/cgi-bin/mailman/listinfo/pmg-devel>,
<mailto:pmg-devel-request@lists.proxmox.com?subject=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 <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 = $@) {