Merge branch 'b-3.6.1' of ../kivitendo-erp_20220811

[kivitendo-erp.git] / sql / Pg-upgrade2 / cvars_remove_dublicate_entries.pl

diff --git a/sql/Pg-upgrade2/cvars_remove_dublicate_entries.pl b/sql/Pg-upgrade2/cvars_remove_dublicate_entries.pl
new file mode 100644 (file)
index 0000000..31e8232
--- /dev/null
+++ b/sql/Pg-upgrade2/cvars_remove_dublicate_entries.pl
@@ -0,0 +1,52 @@
+# @tag: cvars_remove_duplicate_entries
+# @description: Doppelte Einträge für gleiche benutzerdefinierte Variablen entfernen (behalte den Neusten).
+# @depends: release_3_4_1
+
+package SL::DBUpgrade2::cvars_remove_duplicate_entries;
+
+use strict;
+use utf8;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+use SL::DBUtils;
+
+sub run {
+  my ($self) = @_;
+
+  # get all duplicates
+  my $query_all_dups = qq|
+    SELECT trans_id, config_id, sub_module FROM custom_variables
+      GROUP BY trans_id, config_id, sub_module
+      HAVING COUNT(*) > 1
+  |;
+
+  my $refs = selectall_hashref_query($::form, $self->dbh, $query_all_dups);
+
+  # remove all but the newest one (order by itime descending)
+  my $query_delete = qq|
+    DELETE FROM custom_variables WHERE id = ?;
+  |;
+  my $sth_delete = $self->dbh->prepare($query_delete);
+
+  my $query_all_but_newest = qq|
+      SELECT id FROM custom_variables WHERE trans_id = ? AND config_id = ? AND sub_module = ? ORDER BY itime DESC OFFSET 1
+  |;
+  my $sth_all_but_newest = $self->dbh->prepare($query_all_but_newest);
+
+  foreach my $ref (@$refs) {
+    my @to_delete_ids;
+    $sth_all_but_newest->execute($ref->{trans_id}, $ref->{config_id}, $ref->{sub_module}) || $::form->dberror($query_all_but_newest);
+    while (my ($row) = $sth_all_but_newest->fetchrow_array()) {
+      push(@to_delete_ids, $row);
+    }
+    ($sth_delete->execute($_) || $::form->dberror($query_delete)) for @to_delete_ids;
+  }
+
+  $sth_all_but_newest->finish;
+  $sth_delete->finish;
+
+  return 1;
+}
+
+1;