1#!/usr/bin/perl 2# BEGIN BPS TAGGED BLOCK {{{ 3# 4# COPYRIGHT: 5# 6# This software is Copyright (c) 1996-2021 Best Practical Solutions, LLC 7# <sales@bestpractical.com> 8# 9# (Except where explicitly superseded by other copyright notices) 10# 11# 12# LICENSE: 13# 14# This work is made available to you under the terms of Version 2 of 15# the GNU General Public License. A copy of that license should have 16# been provided with this software, but in any event can be snarfed 17# from www.gnu.org. 18# 19# This work is distributed in the hope that it will be useful, but 20# WITHOUT ANY WARRANTY; without even the implied warranty of 21# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 22# General Public License for more details. 23# 24# You should have received a copy of the GNU General Public License 25# along with this program; if not, write to the Free Software 26# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 27# 02110-1301 or visit their web page on the internet at 28# http://www.gnu.org/licenses/old-licenses/gpl-2.0.html. 29# 30# 31# CONTRIBUTION SUBMISSION POLICY: 32# 33# (The following paragraph is not intended to limit the rights granted 34# to you to modify and distribute this software under the terms of 35# the GNU General Public License and is only of importance to you if 36# you choose to contribute your changes and enhancements to the 37# community by submitting them to Best Practical Solutions, LLC.) 38# 39# By intentionally submitting any modifications, corrections or 40# derivatives to this work, or any other work intended for use with 41# Request Tracker, to Best Practical Solutions, LLC, you confirm that 42# you are the copyright holder for those contributions and you grant 43# Best Practical Solutions, LLC a nonexclusive, worldwide, irrevocable, 44# royalty-free, perpetual, license to use, copy, create derivative 45# works based on those contributions, and sublicense and distribute 46# those contributions and any derivatives thereof. 47# 48# END BPS TAGGED BLOCK }}} 49use 5.10.1; 50use strict; 51use warnings; 52 53use lib "local/lib"; 54use lib "lib"; 55 56use Term::ReadKey; 57use Getopt::Long; 58 59$| = 1; # unbuffer all output. 60 61my %args; 62GetOptions( 63 \%args, 64 'dba=s', 'dba-password=s', 'prompt-for-dba-password', 65); 66 67no warnings 'once'; 68use RT::Interface::CLI qw(Init); 69Init(); 70 71my $db_type = RT->Config->Get('DatabaseType') || ''; 72my $db_host = RT->Config->Get('DatabaseHost') || ''; 73my $db_port = RT->Config->Get('DatabasePort') || ''; 74my $db_name = RT->Config->Get('DatabaseName') || ''; 75my $db_user = RT->Config->Get('DatabaseUser') || ''; 76my $db_pass = RT->Config->Get('DatabasePassword') || ''; 77 78my $dba_user = $args{'dba'} || $ENV{'RT_DBA_USER'} || RT->Config->Get('DatabaseAdmin') || ''; 79my $dba_pass = $args{'dba-password'} || $ENV{'RT_DBA_PASSWORD'}; 80 81if ( !$args{force} && ( !defined $dba_pass || $args{'prompt-for-dba-password'} ) ) { 82 $dba_pass = get_dba_password(); 83 chomp $dba_pass if defined($dba_pass); 84} 85 86 87my $dbh = $RT::Handle->dbh; 88 89my $found_assets_tables = {}; 90foreach my $name ( $RT::Handle->_TableNames ) { 91 next unless grep lc($name) eq $_, qw(rtxassets rtxcatalogs); 92 $found_assets_tables->{lc $name}++; 93} 94 95unless ( $found_assets_tables->{rtxassets} && $found_assets_tables->{rtxcatalogs} ) { 96 warn "Could not find RT::Extension::Assets data to migrate"; 97 exit; 98} 99 100print "Working with:\n" 101 ."Type:\t$db_type\nHost:\t$db_host\nPort:\t$db_port\nName:\t$db_name\n" 102 ."User:\t$db_user\nDBA:\t$dba_user" . ($args{'skip-create'} ? ' (No DBA)' : '') . "\n\n"; 103 104print "Upgrading asset tables...\n"; 105 106{ # port over Catalogs 107 my @columns = qw(id Name Lifecycle Description Disabled Creator Created LastUpdatedBy LastUpdated); 108 copy_tables('RTxCatalogs','Catalogs',\@columns); 109 110 fix_id_sequence('Catalogs', { 111 Pg => 'catalogs_id_seq', 112 Oracle => 'Catalogs_seq', 113 }); 114} 115 116 117{ # port over Assets 118 my @columns = qw(id Name Catalog Status Description Creator Created LastUpdatedBy LastUpdated); 119 copy_tables('RTxAssets','Assets',\@columns); 120 121 fix_id_sequence('Assets', { 122 Pg => 'assets_id_seq', 123 Oracle => 'Assets_seq', 124 }); 125} 126 127print "Asset table upgrades complete.\n"; 128 129sub copy_tables { 130 my ($source, $dest, $columns) = @_; 131 my $column_list = join(', ',@$columns); 132 my $sql; 133 # SQLite: http://www.sqlite.org/lang_insert.html 134 if ( $db_type eq 'mysql' || $db_type eq 'SQLite' ) { 135 $sql = "insert into $dest ($column_list) select $column_list from $source"; 136 } 137 # Oracle: http://www.adp-gmbh.ch/ora/sql/insert/select_and_subquery.html 138 elsif ( $db_type eq 'Pg' || $db_type eq 'Oracle' ) { 139 $sql = "insert into $dest ($column_list) (select $column_list from $source)"; 140 } 141 $RT::Logger->debug($sql); 142 $dbh->do($sql); 143} 144 145sub fix_id_sequence { 146 my ($table, $sequence_per_db) = @_; 147 my $sequence = $sequence_per_db->{$db_type} or return; 148 149 my $admin_dbh = get_admin_dbh(); 150 151 my ($max) = $admin_dbh->selectrow_array("SELECT MAX(id) FROM $table;"); 152 my $next_id = ($max || 0) + 1; 153 RT->Logger->info("Resetting $sequence to $next_id\n"); 154 155 my @sql; 156 if ($db_type eq 'Pg') { 157 @sql = "ALTER SEQUENCE $sequence RESTART WITH $next_id;"; 158 } 159 elsif ($db_type eq 'Oracle') { 160 @sql = ( 161 "ALTER SEQUENCE $sequence INCREMENT BY " . ($next_id - 1) . ";", 162 "SELECT $sequence.nextval FROM dual;", 163 "ALTER SEQUENCE $sequence INCREMENT BY 1;", 164 ); 165 } 166 167 $RT::Logger->debug($_) for @sql; 168 $admin_dbh->do($_) for @sql; 169} 170 171sub get_dba_password { 172 return "" if $db_type eq 'SQLite'; 173 print "In order to create or update your RT database," 174 . " this script needs to connect to your " 175 . " $db_type instance on $db_host (port '$db_port') as $dba_user\n"; 176 print "Please specify that user's database password below. If the user has no database\n"; 177 print "password, just press return.\n\n"; 178 print "Password: "; 179 ReadMode('noecho'); 180 my $password = ReadLine(0); 181 ReadMode('normal'); 182 print "\n"; 183 return ($password); 184} 185 186sub get_admin_dbh { 187 return _get_dbh( RT::Handle->DSN, $dba_user, $dba_pass ); 188} 189 190sub _get_dbh { 191 my ($dsn, $user, $pass) = @_; 192 my $dbh = DBI->connect( 193 $dsn, $user, $pass, 194 { RaiseError => 0, PrintError => 0 }, 195 ); 196 unless ( $dbh ) { 197 my $msg = "Failed to connect to $dsn as user '$user': ". $DBI::errstr; 198 if ( $args{'debug'} ) { 199 require Carp; Carp::confess( $msg ); 200 } else { 201 print STDERR $msg; exit -1; 202 } 203 } 204 return $dbh; 205} 206 207