X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=rt%2Fsbin%2Frt-validator.in;h=2ce2930e0b966679be06e9d70c75fa73ac82c822;hp=fafc85e1027e4413f0bbc3fe8c45bfd31a289022;hb=HEAD;hpb=919e930aa9279b3c5cd12b593889cd6de79d67bf diff --git a/rt/sbin/rt-validator.in b/rt/sbin/rt-validator.in index fafc85e10..080a05ac4 100644 --- a/rt/sbin/rt-validator.in +++ b/rt/sbin/rt-validator.in @@ -3,7 +3,7 @@ # # COPYRIGHT: # -# This software is Copyright (c) 1996-2015 Best Practical Solutions, LLC +# This software is Copyright (c) 1996-2019 Best Practical Solutions, LLC # # # (Except where explicitly superseded by other copyright notices) @@ -50,23 +50,15 @@ use strict; use warnings; # fix lib paths, some may be relative -BEGIN { +BEGIN { # BEGIN RT CMD BOILERPLATE require File::Spec; + require Cwd; my @libs = ("@RT_LIB_PATH@", "@LOCAL_LIB_PATH@"); my $bin_path; for my $lib (@libs) { unless ( File::Spec->file_name_is_absolute($lib) ) { - unless ($bin_path) { - if ( File::Spec->file_name_is_absolute(__FILE__) ) { - $bin_path = ( File::Spec->splitpath(__FILE__) )[1]; - } - else { - require FindBin; - no warnings "once"; - $bin_path = $FindBin::Bin; - } - } + $bin_path ||= ( File::Spec->splitpath(Cwd::abs_path(__FILE__)) )[1]; $lib = File::Spec->catfile( $bin_path, File::Spec->updir, $lib ); } unshift @INC, $lib; @@ -74,31 +66,26 @@ BEGIN { } -use Getopt::Long; +use RT::Interface::CLI qw(Init); my %opt = (); -GetOptions( +Init( \%opt, 'check|c', 'resolve', 'force', 'verbose|v', - 'help|h', + 'links-only', ); -if ( $opt{help} || !$opt{check} ) { - require Pod::Usage; - print Pod::Usage::pod2usage( { verbose => 2 } ); - exit; -} +Pod::Usage::pod2usage( { verbose => 2 } ) unless $opt{check}; usage_warning() if $opt{'resolve'} && !$opt{'force'}; - sub usage_warning { print <; } -use RT; -RT::LoadConfig(); -RT::Init(); - my $dbh = $RT::Handle->dbh; my $db_type = RT->Config->Get('DatabaseType'); @@ -123,6 +106,7 @@ my %TYPE = ( my @models = qw( ACE + Article Attachment Attribute CachedGroupMember @@ -138,6 +122,7 @@ my @models = qw( ScripAction ScripCondition Scrip + ObjectScrip Template Ticket Transaction @@ -160,6 +145,7 @@ $redo_on{'Delete'} = { Queues => [], Scrips => [], + ObjectScrips => [], ScripActions => [], ScripConditions => [], Templates => [], @@ -216,7 +202,7 @@ foreach my $table ( qw(Users Groups) ) { ." The script can either create the missing record in Principals" ." or delete the record in $table."; my ($type) = ($table =~ /^(.*)s$/); - check_integrity( + return check_integrity( $table, 'id' => 'Principals', 'id', join_condition => 't.PrincipalType = ?', bind_values => [ $type ], @@ -244,7 +230,7 @@ foreach my $table ( qw(Users Groups) ) { ." In some cases it's possible to manually resurrect such records," ." but this utility can only delete records."; - check_integrity( + return check_integrity( 'Principals', 'id' => $table, 'id', condition => 's.PrincipalType = ?', bind_values => [ $table =~ /^(.*)s$/ ], @@ -259,8 +245,9 @@ foreach my $table ( qw(Users Groups) ) { } push @CHECKS, 'User <-> ACL equivalence group' => sub { + my $res = 1; # from user to group - check_integrity( + $res *= check_integrity( 'Users', 'id' => 'Groups', 'Instance', join_condition => 't.Domain = ? AND t.Type = ?', bind_values => [ 'ACLEquivalence', 'UserEquiv' ], @@ -276,7 +263,7 @@ push @CHECKS, 'User <-> ACL equivalence group' => sub { }, ); # from group to user - check_integrity( + $res *= check_integrity( 'Groups', 'Instance' => 'Users', 'id', condition => 's.Domain = ? AND s.Type = ?', bind_values => [ 'ACLEquivalence', 'UserEquiv' ], @@ -290,25 +277,27 @@ push @CHECKS, 'User <-> ACL equivalence group' => sub { }, ); # one ACL equiv group for each user - check_uniqueness( + $res *= check_uniqueness( 'Groups', columns => ['Instance'], condition => '.Domain = ? AND .Type = ?', bind_values => [ 'ACLEquivalence', 'UserEquiv' ], ); + return $res; }; # check integrity of Queue role groups push @CHECKS, 'Queues <-> Role Groups' => sub { # XXX: we check only that there is at least one group for a queue # from queue to group - check_integrity( + my $res = 1; + $res *= check_integrity( 'Queues', 'id' => 'Groups', 'Instance', join_condition => 't.Domain = ?', bind_values => [ 'RT::Queue-Role' ], ); # from group to queue - check_integrity( + $res *= check_integrity( 'Groups', 'Instance' => 'Queues', 'id', condition => 's.Domain = ?', bind_values => [ 'RT::Queue-Role' ], @@ -321,19 +310,21 @@ push @CHECKS, 'Queues <-> Role Groups' => sub { delete_record( 'Groups', $id ); }, ); + return $res; }; # check integrity of Ticket role groups push @CHECKS, 'Tickets <-> Role Groups' => sub { # XXX: we check only that there is at least one group for a queue # from queue to group - check_integrity( + my $res = 1; + $res *= check_integrity( 'Tickets', 'id' => 'Groups', 'Instance', join_condition => 't.Domain = ?', bind_values => [ 'RT::Ticket-Role' ], ); # from group to ticket - check_integrity( + $res *= check_integrity( 'Groups', 'Instance' => 'Tickets', 'id', condition => 's.Domain = ?', bind_values => [ 'RT::Ticket-Role' ], @@ -346,12 +337,13 @@ push @CHECKS, 'Tickets <-> Role Groups' => sub { delete_record( 'Groups', $id ); }, ); + return $res; }; # additional CHECKS on groups push @CHECKS, 'Role Groups (Instance, Type) uniqueness' => sub { # Check that Domain, Instance and Type are unique - check_uniqueness( + return check_uniqueness( 'Groups', columns => ['Domain', 'Instance', 'Type'], condition => '.Domain LIKE ?', @@ -360,7 +352,7 @@ push @CHECKS, 'Role Groups (Instance, Type) uniqueness' => sub { }; push @CHECKS, 'System internal group uniqueness' => sub { - check_uniqueness( + return check_uniqueness( 'Groups', columns => ['Instance', 'Type'], condition => '.Domain = ?', @@ -370,7 +362,7 @@ push @CHECKS, 'System internal group uniqueness' => sub { # CHECK that user defined group names are unique push @CHECKS, 'User Defined Group Name uniqueness' => sub { - check_uniqueness( + return check_uniqueness( 'Groups', columns => ['Name'], condition => '.Domain = ?', @@ -394,7 +386,8 @@ push @CHECKS, 'GMs -> Groups, Members' => sub { my $msg = "A record in GroupMembers references an object that doesn't exist." ." Maybe you deleted a group or principal directly from the database?" ." Usually it's OK to delete such records."; - check_integrity( + my $res = 1; + $res *= check_integrity( 'GroupMembers', 'GroupId' => 'Groups', 'id', action => sub { my $id = shift; @@ -403,7 +396,7 @@ push @CHECKS, 'GMs -> Groups, Members' => sub { delete_record( 'GroupMembers', $id ); }, ); - check_integrity( + $res *= check_integrity( 'GroupMembers', 'MemberId' => 'Principals', 'id', action => sub { my $id = shift; @@ -412,12 +405,14 @@ push @CHECKS, 'GMs -> Groups, Members' => sub { delete_record( 'GroupMembers', $id ); }, ); + return $res; }; # CGM and GM push @CHECKS, 'CGM vs. GM' => sub { + my $res = 1; # all GM record should be duplicated in CGM - check_integrity( + $res *= check_integrity( GroupMembers => ['GroupId', 'MemberId'], CachedGroupMembers => ['GroupId', 'MemberId'], join_condition => 't.ImmediateParentId = t.GroupId AND t.Via = t.id', @@ -440,7 +435,7 @@ push @CHECKS, 'CGM vs. GM' => sub { }, ); # all first level CGM records should have a GM record - check_integrity( + $res *= check_integrity( CachedGroupMembers => ['GroupId', 'MemberId'], GroupMembers => ['GroupId', 'MemberId'], condition => 's.ImmediateParentId = s.GroupId AND s.Via = s.id AND s.GroupId != s.MemberId', @@ -456,7 +451,7 @@ push @CHECKS, 'CGM vs. GM' => sub { }, ); # each group should have a CGM record where MemberId == GroupId - check_integrity( + $res *= check_integrity( Groups => ['id', 'id'], CachedGroupMembers => ['GroupId', 'MemberId'], join_condition => 't.ImmediateParentId = t.GroupId AND t.Via = t.id', @@ -483,7 +478,7 @@ push @CHECKS, 'CGM vs. GM' => sub { # and back, each record in CGM with MemberId == GroupId without exceptions # should reference a group - check_integrity( + $res *= check_integrity( CachedGroupMembers => ['GroupId', 'MemberId'], Groups => ['id', 'id'], condition => "s.GroupId = s.MemberId", @@ -498,7 +493,7 @@ push @CHECKS, 'CGM vs. GM' => sub { }, ); # Via - check_integrity( + $res *= check_integrity( CachedGroupMembers => 'Via', CachedGroupMembers => 'id', action => sub { @@ -514,7 +509,7 @@ push @CHECKS, 'CGM vs. GM' => sub { # for every CGM where ImmediateParentId != GroupId there should be # matching parent record (first level) - check_integrity( + $res *= check_integrity( CachedGroupMembers => ['ImmediateParentId', 'MemberId'], CachedGroupMembers => ['GroupId', 'MemberId'], join_condition => 't.Via = t.id', @@ -532,7 +527,7 @@ push @CHECKS, 'CGM vs. GM' => sub { # for every CGM where ImmediateParentId != GroupId there should be # matching "grand" parent record - check_integrity( + $res *= check_integrity( CachedGroupMembers => ['GroupId', 'ImmediateParentId', 'Via'], CachedGroupMembers => ['GroupId', 'MemberId', 'id'], condition => 's.ImmediateParentId != s.GroupId', @@ -578,6 +573,7 @@ END my $sth = execute_query( $query ); while ( my ($g, $m, $via, $ip, $dis) = $sth->fetchrow_array ) { + $res = 0; print STDERR "Principal #$m is member of #$ip when #$ip is member of #$g,"; print STDERR " but there is no cached GM record that $m is member of #$g.\n"; $action->( @@ -586,11 +582,14 @@ END ); } } + + return $res; }; # Tickets push @CHECKS, 'Tickets -> other' => sub { - check_integrity( + my $res = 1; + $res *= check_integrity( 'Tickets', 'EffectiveId' => 'Tickets', 'id', action => sub { my $id = shift; @@ -602,19 +601,34 @@ push @CHECKS, 'Tickets -> other' => sub { delete_record( 'Tickets', $id ); }, ); - check_integrity( + $res *= check_integrity( 'Tickets', 'Queue' => 'Queues', 'id', ); - check_integrity( + $res *= check_integrity( 'Tickets', 'Owner' => 'Users', 'id', + action => sub { + my ($id, %prop) = @_; + return unless my $replace_with = prompt_integer( + 'Replace', + "Column Owner should point to a user, but there is record #$id in Tickets\n" + ."where it's not true. It's ok to replace these wrong references with id of any user.\n" + ."Note that id you enter is not checked. You can pick any user from your DB, but it's\n" + ."may be better to create a special user for this, for example 'user_that_has_been_deleted'\n" + ."or something like that.", + "Tickets.Owner -> user #$prop{Owner}" + ); + update_records( 'Tickets', { id => $id, Owner => $prop{Owner} }, { Owner => $replace_with } ); + }, ); # XXX: check that owner is only member of owner role group + return $res; }; push @CHECKS, 'Transactions -> other' => sub { + my $res = 1; foreach my $model ( @models ) { - check_integrity( + $res *= check_integrity( 'Transactions', 'ObjectId' => m2t($model), 'id', condition => 's.ObjectType = ?', bind_values => [ "RT::$model" ], @@ -629,13 +643,13 @@ push @CHECKS, 'Transactions -> other' => sub { ); } # type = CustomField - check_integrity( + $res *= check_integrity( 'Transactions', 'Field' => 'CustomFields', 'id', condition => 's.Type = ?', bind_values => [ 'CustomField' ], ); # type = Take, Untake, Force, Steal or Give - check_integrity( + $res *= check_integrity( 'Transactions', 'OldValue' => 'Users', 'id', condition => 's.Type IN (?, ?, ?, ?, ?)', bind_values => [ qw(Take Untake Force Steal Give) ], @@ -649,7 +663,7 @@ push @CHECKS, 'Transactions -> other' => sub { delete_record( 'Transactions', $id ); }, ); - check_integrity( + $res *= check_integrity( 'Transactions', 'NewValue' => 'Users', 'id', condition => 's.Type IN (?, ?, ?, ?, ?)', bind_values => [ qw(Take Untake Force Steal Give) ], @@ -664,7 +678,7 @@ push @CHECKS, 'Transactions -> other' => sub { }, ); # type = DelWatcher - check_integrity( + $res *= check_integrity( 'Transactions', 'OldValue' => 'Principals', 'id', condition => 's.Type = ?', bind_values => [ 'DelWatcher' ], @@ -679,7 +693,7 @@ push @CHECKS, 'Transactions -> other' => sub { }, ); # type = AddWatcher - check_integrity( + $res *= check_integrity( 'Transactions', 'NewValue' => 'Principals', 'id', condition => 's.Type = ?', bind_values => [ 'AddWatcher' ], @@ -694,22 +708,11 @@ push @CHECKS, 'Transactions -> other' => sub { }, ); -# XXX: Links need more love, uri is stored instead of id -# # type = DeleteLink -# check_integrity( -# 'Transactions', 'OldValue' => 'Links', 'id', -# condition => 's.Type = ?', -# bind_values => [ 'DeleteLink' ], -# ); -# # type = AddLink -# check_integrity( -# 'Transactions', 'NewValue' => 'Links', 'id', -# condition => 's.Type = ?', -# bind_values => [ 'AddLink' ], -# ); +# type = DeleteLink or AddLink +# handled in 'Links: *' checks as {New,Old}Value store URIs # type = Set, Field = Queue - check_integrity( + $res *= check_integrity( 'Transactions', 'NewValue' => 'Queues', 'id', condition => 's.Type = ? AND s.Field = ?', bind_values => [ 'Set', 'Queue' ], @@ -723,7 +726,7 @@ push @CHECKS, 'Transactions -> other' => sub { delete_record( 'Transactions', $id ); }, ); - check_integrity( + $res *= check_integrity( 'Transactions', 'OldValue' => 'Queues', 'id', condition => 's.Type = ? AND s.Field = ?', bind_values => [ 'Set', 'Queue' ], @@ -738,17 +741,19 @@ push @CHECKS, 'Transactions -> other' => sub { }, ); # Reminders - check_integrity( + $res *= check_integrity( 'Transactions', 'NewValue' => 'Tickets', 'id', join_condition => 't.Type = ?', condition => 's.Type IN (?, ?, ?)', bind_values => [ 'reminder', 'AddReminder', 'OpenReminder', 'ResolveReminder' ], ); + return $res; }; # Attachments push @CHECKS, 'Attachments -> other' => sub { - check_integrity( + my $res = 1; + $res *= check_integrity( Attachments => 'TransactionId', Transactions => 'id', action => sub { my $id = shift; @@ -758,7 +763,7 @@ push @CHECKS, 'Attachments -> other' => sub { delete_record( 'Attachments', $id ); }, ); - check_integrity( + $res *= check_integrity( Attachments => 'Parent', Attachments => 'id', action => sub { my $id = shift; @@ -768,64 +773,75 @@ push @CHECKS, 'Attachments -> other' => sub { delete_record( 'Attachments', $id ); }, ); - check_integrity( + $res *= check_integrity( Attachments => 'Parent', Attachments => 'id', join_condition => 's.TransactionId = t.TransactionId', ); + return $res; }; push @CHECKS, 'CustomFields and friends' => sub { + my $res = 1; #XXX: ObjectCustomFields needs more love - check_integrity( + $res *= check_integrity( 'CustomFieldValues', 'CustomField' => 'CustomFields', 'id', ); - check_integrity( + $res *= check_integrity( 'ObjectCustomFieldValues', 'CustomField' => 'CustomFields', 'id', ); foreach my $model ( @models ) { - check_integrity( + $res *= check_integrity( 'ObjectCustomFieldValues', 'ObjectId' => m2t($model), 'id', condition => 's.ObjectType = ?', bind_values => [ "RT::$model" ], ); } + return $res; }; push @CHECKS, Templates => sub { - check_integrity( + return check_integrity( 'Templates', 'Queue' => 'Queues', 'id', ); }; push @CHECKS, Scrips => sub { - check_integrity( - 'Scrips', 'Queue' => 'Queues', 'id', - ); - check_integrity( + my $res = 1; + $res *= check_integrity( 'Scrips', 'ScripCondition' => 'ScripConditions', 'id', ); - check_integrity( + $res *= check_integrity( 'Scrips', 'ScripAction' => 'ScripActions', 'id', ); - check_integrity( - 'Scrips', 'Template' => 'Templates', 'id', + $res *= check_integrity( + 'Scrips', 'Template' => 'Templates', 'Name', + ); + $res *= check_integrity( + 'ObjectScrips', 'Scrip' => 'Scrips', 'id', ); + $res *= check_integrity( + 'ObjectScrips', 'ObjectId' => 'Queues', 'id', + ); + return $res; }; push @CHECKS, Attributes => sub { + my $res = 1; foreach my $model ( @models ) { - check_integrity( + $res *= check_integrity( 'Attributes', 'ObjectId' => m2t($model), 'id', condition => 's.ObjectType = ?', bind_values => [ "RT::$model" ], ); } + return $res; }; # Fix situations when Creator or LastUpdatedBy references ACL equivalence # group of a user instead of user push @CHECKS, 'FIX: LastUpdatedBy and Creator' => sub { + my $res = 1; my %fix = (); foreach my $model ( @models ) { my $class = "RT::$model"; @@ -855,6 +871,7 @@ END my $sth = execute_query( $query, 'ACLEquivalence', 'UserEquiv' ); while ( my ($rid, $gid, $uid) = $sth->fetchrow_array ) { + $res = 0; print STDERR "Record #$rid in $table refers to ACL equivalence group #$gid of user #$uid"; print STDERR " when must reference user.\n"; $action->( $gid, $uid ); @@ -877,16 +894,18 @@ END } $redo_check{'FIX: LastUpdatedBy and Creator'} = 1; } + return $res; }; push @CHECKS, 'LastUpdatedBy and Creator' => sub { + my $res = 1; foreach my $model ( @models ) { my $class = "RT::$model"; my $object = $class->new( RT->SystemUser ); my $table = $object->Table; foreach my $column ( qw(LastUpdatedBy Creator) ) { next unless $object->_Accessible( $column, 'auto' ); - check_integrity( + $res *= check_integrity( $table, $column => 'Users', 'id', action => sub { my ($id, %prop) = @_; @@ -904,13 +923,210 @@ push @CHECKS, 'LastUpdatedBy and Creator' => sub { ); } } + return $res; +}; + +push @CHECKS, 'Links: wrong organization' => sub { + my $res = 1; + my @URI_USES = ( + { model => 'Transaction', column => 'OldValue', Additional => { Type => 'DeleteLink' } }, + { model => 'Transaction', column => 'NewValue', Additional => { Type => 'AddLink' } }, + { model => 'Link', column => 'Target' }, + { model => 'Link', column => 'Base' }, + ); + + my $right_org = RT->Config->Get('Organization'); + my @rt_uris = rt_uri_modules(); + foreach my $package (@rt_uris) { + + my $rt_uri = $package->new( $RT::SystemUser ); + my $scheme = $rt_uri->Scheme; + my $prefix = $rt_uri->LocalURIPrefix; + + foreach my $use ( @URI_USES ) { + my $table = m2t( $use->{'model'} ); + my $column = $use->{'column'}; + + my $query = "SELECT id, $column FROM $table WHERE" + . " $column LIKE ? AND $column NOT LIKE ?"; + my @binds = (sql_escape_like($scheme) ."://%", sql_escape_like($prefix) ."%"); + + while ( my ($k, $v) = each %{ $use->{'Additional'} || {} } ) { + $query .= " AND $k = ?"; + push @binds, $v; + } + my $sth = execute_query( $query, @binds ); + while ( my ($id, $value) = $sth->fetchrow_array ) { + $res = 0; + print STDERR "Record #$id in $table. Value of $column column most probably is an incorrect link\n"; + my ($wrong_org) = ( $value =~ m{^\Q$scheme\E://(.+)/[^/]+/[0-9]*$} ); + next unless my $replace_with = prompt( + 'Replace', + "Column $column in $table is a link. There is record #$id that has a" + ." local scheme of '$scheme', but its organization is '$wrong_org'" + ." instead of '$right_org'. Most probably the Organization was" + ." changed from '$wrong_org' to '$right_org' at some point. It is" + ." generally a good idea to replace these wrong links.\n", + "Links: wrong organization $wrong_org" + ); + + print "Updating record(s) in $table\n" if $opt{'verbose'}; + my $wrong_prefix = $scheme . '://'. $wrong_org; + my $query = "UPDATE $table SET $column = ". sql_concat('?', "SUBSTR($column, ?)") + ." WHERE $column LIKE ?"; + execute_query( $query, $prefix, length($wrong_prefix)+1, sql_escape_like($wrong_prefix) .'/%' ); + + $redo_check{'Links: wrong organization'} = 1; + $redo_check{'Links: LocalX for non-ticket'} = 1; + last; # plenty of chances we covered all cases with one update + } + } + } # end foreach my $package (@rt_uris) + return $res; +}; + +push @CHECKS, 'Links: LocalX for non-ticket' => sub { + my $res = 1; + my $rt_uri = RT::URI::fsck_com_rt->new( $RT::SystemUser ); + my $scheme = $rt_uri->Scheme; + my $prefix = $rt_uri->LocalURIPrefix; + my $table = m2t('Link'); + + foreach my $dir ( 'Target', 'Base' ) { + # we look only at links with correct organization, previouse check deals + # with incorrect orgs + my $where = "Local$dir > 0 AND $dir LIKE ? AND $dir NOT LIKE ?"; + my @binds = (sql_escape_like($prefix) ."/%", sql_escape_like($prefix) ."/ticket/%"); + + my $sth = execute_query( "SELECT id FROM $table WHERE $where", @binds ); + while ( my ($id, $value) = $sth->fetchrow_array ) { + $res = 0; + print STDERR "Record #$id in $table. Value of Local$dir is not 0\n"; + next unless my $replace_with = prompt( + 'Replace', + "Column Local$dir in $table should be 0 if $dir column is not link" + ." to a ticket. It's ok to replace with 0.\n", + ); + + print "Updating record(s) in $table\n" if $opt{'verbose'}; + execute_query( "UPDATE $table SET Local$dir = 0 WHERE $where", @binds ); + $redo_check{'Links: wrong organization'} = 1; + + last; # we covered all cases with one update + } + } + return $res; +}; + +push @CHECKS, 'Links: LocalX != X' => sub { + my $res = 1; + my $rt_uri = RT::URI::fsck_com_rt->new( $RT::SystemUser ); + my $scheme = $rt_uri->Scheme; + my $prefix = $rt_uri->LocalURIPrefix .'/ticket/'; + my $table = m2t('Link'); + + foreach my $dir ( 'Target', 'Base' ) { + # we limit to $dir = */ticket/* so it doesn't conflict with previouse check + # previouse check is more important as there was a bug in RT when Local$dir + # was set for not tickets + # XXX: we have issue with MergedInto links - "LocalX !~ X" + my $where = "Local$dir > 0 AND $dir LIKE ? AND $dir != ". sql_concat('?', "Local$dir") + ." AND Type != ?"; + my @binds = (sql_escape_like($prefix) ."%", $prefix, 'MergedInto'); + + my $sth = execute_query( "SELECT id FROM $table WHERE $where", @binds ); + while ( my ($id, $value) = $sth->fetchrow_array ) { + $res = 0; + print STDERR "Record #$id in $table. Value of $dir doesn't match ticket id in Local$dir\n"; + next unless my $replace_with = prompt( + 'Replace', + "For ticket links column $dir in $table table should end with" + ." ticket id from Local$dir. It's probably ok to fix $dir column.\n", + ); + + print "Updating record(s) in $table\n" if $opt{'verbose'}; + execute_query( + "UPDATE $table SET $dir = ". sql_concat('?', "Local$dir") ." WHERE $where", + $prefix, @binds + ); + + last; # we covered all cases with one update + } + } + return $res; }; + +push @CHECKS, 'Links: missing object' => sub { + my $res = 1; + my @URI_USES = ( + { model => 'Transaction', column => 'OldValue', Additional => { Type => 'DeleteLink' } }, + { model => 'Transaction', column => 'NewValue', Additional => { Type => 'AddLink' } }, + { model => 'Link', column => 'Target' }, + { model => 'Link', column => 'Base' }, + ); + + my @rt_uris = rt_uri_modules(); + foreach my $package (@rt_uris) { + + my $rt_uri = $package->new( $RT::SystemUser ); + my $scheme = $rt_uri->Scheme; + my $prefix = $rt_uri->LocalURIPrefix; + + foreach my $use ( @URI_USES ) { + my $stable = m2t( $use->{'model'} ); + my $scolumn = $use->{'column'}; + + foreach my $tmodel ( @models ) { + my $tclass = 'RT::'. $tmodel; + my $ttable = m2t($tmodel); + + my $tprefix = $prefix .'/'. ($tclass eq 'RT::Ticket'? 'ticket' : $tclass) .'/'; + + $tprefix = $prefix . '/article/' if $tclass eq 'RT::Article'; + + my $query = "SELECT s.id FROM $stable s LEFT JOIN $ttable t " + ." ON t.id = ". sql_str2int("SUBSTR(s.$scolumn, ?)") + ." WHERE s.$scolumn LIKE ? AND t.id IS NULL"; + my @binds = (length($tprefix) + 1, sql_escape_like($tprefix).'%'); + + while ( my ($k, $v) = each %{ $use->{'Additional'} || {} } ) { + $query .= " AND s.$k = ?"; + push @binds, $v; + } + + my $sth = execute_query( $query, @binds ); + while ( my ($sid) = $sth->fetchrow_array ) { + $res = 0; + print STDERR "Link in $scolumn column in record #$sid in $stable table points" + ." to not existing object.\n"; + next unless prompt( + 'Delete', + "Column $scolumn in $stable table is a link to an object that doesn't exist." + ." You can delete such records, however make sure there is no other" + ." errors with links.\n", + 'Link to a missing object in $ttable' + ); + + delete_record($stable, $sid); + } + } + } + } # end foreach my $package (@rt_uris) + return $res; +}; + + my %CHECKS = @CHECKS; @do_check = do { my $i = 1; grep $i++%2, @CHECKS }; +if ($opt{'links-only'}) { + @do_check = grep { /^Links:/ } @do_check; +} + +my $status = 1; while ( my $check = shift @do_check ) { - $CHECKS{ $check }->(); + $status *= $CHECKS{ $check }->(); foreach my $redo ( keys %redo_check ) { die "check $redo doesn't exist" unless $CHECKS{ $redo }; @@ -919,6 +1135,19 @@ while ( my $check = shift @do_check ) { push @do_check, $redo; } } +exit 1 unless $status; +exit 0; + +=head2 check_integrity + +Takes two (table name, column(s)) pairs. First pair +is reference we check and second is destination that +must exist. Array reference can be used for multiple +columns. + +Returns 0 if a record is missing or 1 otherwise. + +=cut sub check_integrity { my ($stable, @scols) = (shift, shift); @@ -954,15 +1183,21 @@ sub check_integrity { push @binds, 0; } + my $res = 1; + my $sth = execute_query( $query, @binds ); while ( my ($sid, @set) = $sth->fetchrow_array ) { + $res = 0; + print STDERR "Record #$sid in $stable references a nonexistent record in $ttable\n"; for ( my $i = 0; $i < @scols; $i++ ) { print STDERR "\t$scols[$i] => '$set[$i]' => $tcols[$i]\n"; } print STDERR "\t". describe( $stable, $sid ) ."\n"; - $args{'action'}->( $sid, map { $scols[$_] => $set[$_] } (0 .. (@scols-1)) ) if $args{'action'}; + $args{'action'}->( $sid, map { $scols[$_] => $set[$_] } (0 .. (@scols-1)) ) + if $args{'action'}; } + return $res; } sub describe { @@ -1022,13 +1257,16 @@ sub check_uniqueness { $args{'bind_values'}? (@{ $args{'bind_values'} }, @{ $args{'bind_values'} }): (), $args{'extra_values'}? (@{ $args{'extra_values'} }): () ); + my $res = 1; while ( my ($sid, $tid, @set) = $sth->fetchrow_array ) { + $res = 0; print STDERR "Record #$tid in $on has the same set of values as $sid\n"; for ( my $i = 0; $i < @columns; $i++ ) { print STDERR "\t$columns[$i] => '$set[$i]'\n"; } $args{'action'}->( $tid, map { $columns[$_] => $set[$_] } (0 .. (@columns-1)) ) if $args{'action'}; } + return $res; } sub load_record { @@ -1079,6 +1317,30 @@ sub execute_query { return $sth; } +sub sql_concat { + return $_[0] if @_ <= 1; + + my $db_type = RT->Config->Get('DatabaseType'); + if ( $db_type eq 'Pg' || $db_type eq 'SQLite' ) { + return '('. join( ' || ', @_ ) .')'; + } + return sql_concat('CONCAT('. join( ', ', splice @_, 0, 2 ).')', @_); +} + +sub sql_str2int { + my $db_type = RT->Config->Get('DatabaseType'); + if ( $db_type eq 'Pg' ) { + return "($_[0])::integer"; + } + return $_[0]; +} + +sub sql_escape_like { + my ($string) = @_; + $string =~ s/([%_\\])/\\$1/g; + return $string; +} + { my %cached_answer; sub prompt { my $action = shift; @@ -1135,6 +1397,20 @@ sub prompt_integer { return $cached_answer{ $token } = $a; } } +# Find all RT::URI modules RT has loaded + +sub rt_uri_modules { + my @uris = grep /^RT\/URI\/.+\.pm$/, keys %INC; + my @uri_modules; + foreach my $uri_path (@uris){ + next if $uri_path =~ /base\.pm$/; # Skip base RT::URI object + $uri_path = substr $uri_path, 0, -3; # chop off .pm + push @uri_modules, join '::', split '/', $uri_path; + } + + return @uri_modules; +} + 1; __END__ @@ -1178,5 +1454,9 @@ records or ressurect accidentally deleted. resolve without asking questions +=item links-only + + only run the Link validation routines, useful if you changed your Organization + =back