1 # @tag: cvars_remove_duplicate_entries
2 # @description: Doppelte Einträge für gleiche benutzerdefinierte Variablen entfernen (behalte den Neusten).
3 # @depends: release_3_4_1
5 package SL::DBUpgrade2::cvars_remove_duplicate_entries;
10 use parent qw(SL::DBUpgrade2::Base);
18 my $query_all_dups = qq|
19 SELECT trans_id, config_id, sub_module FROM custom_variables
20 GROUP BY trans_id, config_id, sub_module
24 my $refs = selectall_hashref_query($::form, $self->dbh, $query_all_dups);
26 # remove all but the newest one (order by itime descending)
27 my $query_delete = qq|
28 DELETE FROM custom_variables WHERE id = ?;
30 my $sth_delete = $self->dbh->prepare($query_delete);
32 my $query_all_but_newest = qq|
33 SELECT id FROM custom_variables WHERE trans_id = ? AND config_id = ? AND sub_module = ? ORDER BY itime DESC OFFSET 1
35 my $sth_all_but_newest = $self->dbh->prepare($query_all_but_newest);
37 foreach my $ref (@$refs) {
39 $sth_all_but_newest->execute($ref->{trans_id}, $ref->{config_id}, $ref->{sub_module}) || $::form->dberror($query_all_but_newest);
40 while (my ($row) = $sth_all_but_newest->fetchrow_array()) {
41 push(@to_delete_ids, $row);
43 ($sth_delete->execute($_) || $::form->dberror($query_delete)) for @to_delete_ids;
46 $sth_all_but_newest->finish;