1# --
2# Copyright (C) 2001-2020 OTRS AG, https://otrs.com/
3# --
4# This software comes with ABSOLUTELY NO WARRANTY. For details, see
5# the enclosed file COPYING for license information (GPL). If you
6# did not receive this file, see https://www.gnu.org/licenses/gpl-3.0.txt.
7# --
8
9package Kernel::System::SupportDataCollector::Plugin::Database::oracle::PrimaryKeySequencesAndTriggers;
10
11use strict;
12use warnings;
13
14use parent qw(Kernel::System::SupportDataCollector::PluginBase);
15
16use Kernel::Language qw(Translatable);
17
18our @ObjectDependencies = (
19    'Kernel::System::DB',
20);
21
22sub GetDisplayPath {
23    return Translatable('Database');
24}
25
26sub Run {
27    my $Self = shift;
28
29    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
30
31    if ( $DBObject->GetDatabaseFunction('Type') ne 'oracle' ) {
32        return $Self->GetResults();
33    }
34
35    # Get all table names.
36    my @Tables = $DBObject->ListTables();
37
38    my %SequenceNameFromTableName;
39    for my $TableName (@Tables) {
40
41        my $Sequence = $DBObject->{Backend}->_SequenceName(
42            TableName => $TableName,
43        );
44
45        # Convert to lower case.
46        $Sequence = lc $Sequence;
47
48        $SequenceNameFromTableName{$Sequence} = 1;
49    }
50
51    # Get all sequence names.
52    $DBObject->Prepare(
53        SQL => 'SELECT sequence_name FROM user_sequences',
54    );
55
56    my @SequenceNames;
57    while ( my @Row = $DBObject->FetchrowArray() ) {
58
59        # Convert to lower case.
60        push @SequenceNames, lc $Row[0];
61    }
62
63    my @WrongSequenceNames;
64    SEQUENCE:
65    for my $SequenceName (@SequenceNames) {
66
67        next SEQUENCE if $SequenceNameFromTableName{$SequenceName};
68
69        # Remember wrong sequence name.
70        push @WrongSequenceNames, $SequenceName;
71    }
72
73    # Get all trigger names.
74    $DBObject->Prepare(
75        SQL => 'SELECT trigger_name FROM user_triggers',
76    );
77
78    my @TriggerNames;
79    while ( my @Row = $DBObject->FetchrowArray() ) {
80
81        # Convert to lower case.
82        push @TriggerNames, lc $Row[0];
83    }
84
85    my @WrongTriggerNames;
86    TRIGGER:
87    for my $TriggerName (@TriggerNames) {
88
89        my $SequenceName = $TriggerName;
90
91        # Remove the last part of the sequence name.
92        $SequenceName =~ s{ _t \z }{}xms;
93
94        next TRIGGER if $SequenceNameFromTableName{$SequenceName};
95
96        # Remember wrong trigger name.
97        push @WrongTriggerNames, $TriggerName;
98    }
99
100    my $Error;
101    if (@WrongSequenceNames) {
102
103        $Error .= "Seqences:\n";
104        $Error .= join "\n", @WrongSequenceNames;
105        $Error .= "\n\n";
106    }
107
108    if (@WrongTriggerNames) {
109        $Error .= "Triggers:\n";
110        $Error .= join "\n", @WrongTriggerNames;
111        $Error .= "\n\n";
112    }
113
114    if ($Error) {
115        $Self->AddResultProblem(
116            Identifier => 'PrimaryKeySequencesAndTriggers',
117            Label      => Translatable('Primary Key Sequences and Triggers'),
118            Value      => $Error,
119            Message    => Translatable(
120                'The following sequences and/or triggers with possible wrong names have been found. Please rename them manually.'
121            ),
122        );
123    }
124    else {
125        $Self->AddResultOk(
126            Identifier => 'PrimaryKeySequencesAndTriggers',
127            Label      => Translatable('Primary Key Sequences and Triggers'),
128            Value      => '',
129        );
130    }
131
132    return $Self->GetResults();
133}
134
1351;
136