all lists on lists.proxmox.com
 help / color / mirror / Atom feed
* [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes
@ 2024-02-21 21:08 Stoiko Ivanov
  2024-02-22  9:47 ` Stefan Hanreich
  2024-02-22 10:29 ` [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes Dominik Csapak
  0 siblings, 2 replies; 5+ messages in thread
From: Stoiko Ivanov @ 2024-02-21 21:08 UTC (permalink / raw)
  To: pmg-devel

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





^ permalink raw reply	[flat|nested] 5+ messages in thread

end of thread, other threads:[~2024-02-22 10:29 UTC | newest]

Thread overview: 5+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2024-02-21 21:08 [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes Stoiko Ivanov
2024-02-22  9:47 ` Stefan Hanreich
2024-02-22 10:05   ` [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attribute Dominik Csapak
2024-02-22 10:23     ` Stefan Hanreich
2024-02-22 10:29 ` [pmg-devel] [RFC pmg-api] database: use foreign keys for rule and object group attributes Dominik Csapak

This is an external index of several public inboxes,
see mirroring instructions on how to clone and mirror
all data and code used by this external index.
Service provided by Proxmox Server Solutions GmbH | Privacy | Legal