[pmg-devel] applied: [PATCH pmg-api] fix #2071: RuleDB: ignore duplicate entries for Who objects

[Stoiko Ivanov <s.ivanov@proxmox.com>]

Huge thanks for the patch - tested and applied!

On Fri, 24 Sep 2021 13:17:46 +0200
Dominik Csapak <d.csapak@proxmox.com> wrote:

> if we detect an entry with a value that is identical, return that id
> instead of adding it again to the db.
> 
> Signed-off-by: Dominik Csapak <d.csapak@proxmox.com>
> ---
>  src/PMG/RuleDB/LDAP.pm     | 10 ++++++++++
>  src/PMG/RuleDB/LDAPUser.pm | 10 ++++++++++
>  src/PMG/RuleDB/WhoRegex.pm | 10 ++++++++++
>  src/PMG/Utils.pm           | 17 +++++++++++++++++
>  4 files changed, 47 insertions(+)
> 
> diff --git a/src/PMG/RuleDB/LDAP.pm b/src/PMG/RuleDB/LDAP.pm
> index e17441b..a132499 100644
> --- a/src/PMG/RuleDB/LDAP.pm
> +++ b/src/PMG/RuleDB/LDAP.pm
> @@ -81,6 +81,16 @@ sub save {
>      } else {
>  	# insert
>  
> +	# check if it exists first
> +	if (my $id = PMG::Utils::get_existing_object_id(
> +	    $ruledb->{dbh},
> +	    $self->{ogroup},
> +	    $self->otype(),
> +	    $confdata
> +	)) {
> +	    return $id;
> +	}
> +
>  	my $sth = $ruledb->{dbh}->prepare(
>  	    "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " .
>  	    "VALUES (?, ?, ?);");
> diff --git a/src/PMG/RuleDB/LDAPUser.pm b/src/PMG/RuleDB/LDAPUser.pm
> index f550870..022d784 100644
> --- a/src/PMG/RuleDB/LDAPUser.pm
> +++ b/src/PMG/RuleDB/LDAPUser.pm
> @@ -83,6 +83,16 @@ sub save {
>      } else {
>  	# insert
>  
> +	# check if it exists first
> +	if (my $id = PMG::Utils::get_existing_object_id(
> +	    $ruledb->{dbh},
> +	    $self->{ogroup},
> +	    $self->otype(),
> +	    $confdata
> +	)) {
> +	    return $id;
> +	}
> +
>  	my $sth = $ruledb->{dbh}->prepare(
>  	    "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " .
>  	    "VALUES (?, ?, ?);");
> diff --git a/src/PMG/RuleDB/WhoRegex.pm b/src/PMG/RuleDB/WhoRegex.pm
> index b9519ad..37ec3aa 100644
> --- a/src/PMG/RuleDB/WhoRegex.pm
> +++ b/src/PMG/RuleDB/WhoRegex.pm
> @@ -70,6 +70,16 @@ sub save {
>      } else {
>  	# insert
>  
> +	# check if it exists first
> +	if (my $id = PMG::Utils::get_existing_object_id(
> +	    $ruledb->{dbh},
> +	    $self->{ogroup},
> +	    $self->otype(),
> +	    $adr
> +	)) {
> +	    return $id;
> +	}
> +
>  	my $sth = $ruledb->{dbh}->prepare (
>  	    "INSERT INTO Object (Objectgroup_ID, ObjectType, Value) " .
>  	    "VALUES (?, ?, ?);");
> diff --git a/src/PMG/Utils.pm b/src/PMG/Utils.pm
> index 20686ad..92c3a7a 100644
> --- a/src/PMG/Utils.pm
> +++ b/src/PMG/Utils.pm
> @@ -1505,4 +1505,21 @@ sub update_local_spamassassin_channels {
>      return $fresh_updates
>  }
>  
> +sub get_existing_object_id {
> +    my ($dbh, $obj_id, $obj_type, $value) = @_;
> +
> +    my $sth = $dbh->prepare("SELECT id FROM Object WHERE ".
> +	"Objectgroup_ID = ? AND ".
> +	"ObjectType = ? AND ".
> +	"Value = ?"
> +    );
> +    $sth->execute($obj_id, $obj_type, $value);
> +
> +    if (my $ref = $sth->fetchrow_hashref()) {
> +	return $ref->{id};
> +    }
> +
> +    return;
> +}
> +
>  1;