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