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