2 # BEGIN BPS TAGGED BLOCK {{{
6 # This software is Copyright (c) 1996-2013 Best Practical Solutions, LLC
7 # <sales@bestpractical.com>
9 # (Except where explicitly superseded by other copyright notices)
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
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.
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.
31 # CONTRIBUTION SUBMISSION POLICY:
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.)
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.
48 # END BPS TAGGED BLOCK }}}
52 # fix lib paths, some may be relative
55 my @libs = ("@RT_LIB_PATH@", "@LOCAL_LIB_PATH@");
59 unless ( File::Spec->file_name_is_absolute($lib) ) {
61 if ( File::Spec->file_name_is_absolute(__FILE__) ) {
62 $bin_path = ( File::Spec->splitpath(__FILE__) )[1];
67 $bin_path = $FindBin::Bin;
70 $lib = File::Spec->catfile( $bin_path, File::Spec->updir, $lib );
87 usage() unless $opt{'check'};
88 usage_warning() if $opt{'resolve'} && !$opt{'force'};
98 $0 --check --verbose --resolve
99 $0 --check --verbose --resolve --force
101 --check - is mandatory argument, you can use -c, as well.
102 --verbose - print additional info to STDOUT
103 --resolve - enable resolver that can delete or create some records
104 --force - resolve without asking questions
108 This script checks integrity of records in RT's DB. May delete some invalid
109 records or ressurect accidentally deleted.
117 This utility can fix some issues with DB by creating or updating. In some
118 cases there is no enough data to resurect a missing record, but records which
119 refers to a missing can be deleted. It's up to you to decide what to do.
121 In any case it's highly recommended to have a backup before resolving anything.
123 Press enter to continue.
125 # Read a line of text, any line of text
133 my $dbh = $RT::Handle->dbh;
134 my $db_type = RT->Config->Get('DatabaseType');
137 'Transactions.Field' => 'text',
138 'Transactions.OldValue' => 'text',
139 'Transactions.NewValue' => 'text',
153 ObjectCustomFieldValue
166 $redo_on{'Delete'} = {
174 CustomFieldValues => [],
175 ObjectCustomFields => [],
176 ObjectCustomFieldValues => [],
182 ScripConditions => [],
185 Tickets => [ 'Tickets -> other', 'Tickets <-> Role Groups' ],
186 Transactions => [ 'Attachments -> other' ],
188 Principals => ['User <-> ACL equivalence group', 'GMs -> Groups, Members' ],
189 Users => ['User <-> ACL equivalence group', 'GMs -> Groups, Members', 'Principals -> Users' ],
190 Groups => ['User <-> ACL equivalence group', 'GMs -> Groups, Members', 'CGM vs. GM', 'Principals -> Groups' ],
192 GroupMembers => [ 'CGM vs. GM' ],
193 CachedGroupMembers => [ 'CGM vs. GM' ],
195 $redo_on{'Create'} = {
196 Principals => ['User <-> ACL equivalence group', 'GMs -> Groups, Members' ],
197 Groups => ['User <-> ACL equivalence group', 'GMs -> Groups, Members', 'CGM vs. GM' ],
198 GroupMembers => [ 'CGM vs. GM' ],
199 CachedGroupMembers => [ 'CGM vs. GM' ],
206 my $txn_id = $row->{transactionid};
207 my $res = 'Attachment #'. $row->{id} .' -> Txn #'. $txn_id;
208 return $res .', '. describe( 'Transactions', $txn_id );
210 Transactions => sub {
212 return 'Transaction #'. $row->{id} .' -> object '. $row->{objecttype} .' #'. $row->{objectid};
219 return $cache{$model} if $cache{$model};
220 my $class = "RT::$model";
221 my $object = $class->new( $RT::SystemUser );
222 return $cache{$model} = $object->Table;
225 my (@do_check, %redo_check);
228 foreach my $table ( qw(Users Groups) ) {
229 push @CHECKS, "$table -> Principals" => sub {
230 my $msg = "A record in $table refers not existing record in Principals."
231 ." The script can either create missing record in Principals"
232 ." or delete record in $table.";
233 my ($type) = ($table =~ /^(.*)s$/);
235 $table, 'id' => 'Principals', 'id',
236 join_condition => 't.PrincipalType = ?',
237 bind_values => [ $type ],
240 return unless my $a = prompt_action( ['Delete', 'create'], $msg );
243 delete_record( $table, $id );
245 elsif ( $a eq 'c' ) {
246 my $principal_id = create_record( 'Principals',
247 id => $id, PrincipalType => $type, ObjectId => $id, Disabled => 0
251 die "Unknown action '$a'";
257 push @CHECKS, "Principals -> $table" => sub {
258 my $msg = "A record in Principals refers not existing record in $table."
259 ." In some cases it's possible to resurrect manually such records,"
260 ." but this utility can only delete";
263 'Principals', 'id' => $table, 'id',
264 condition => 's.PrincipalType = ?',
265 bind_values => [ $table =~ /^(.*)s$/ ],
268 return unless prompt( 'Delete', $msg );
270 delete_record( 'Principals', $id );
276 push @CHECKS, 'User <-> ACL equivalence group' => sub {
279 'Users', 'id' => 'Groups', 'Instance',
280 join_condition => 't.Domain = ? AND t.Type = ?',
281 bind_values => [ 'ACLEquivalence', 'UserEquiv' ],
284 return unless prompt(
285 'Create', "Found an user that has no ACL equivalence group."
288 my $gid = create_record( 'Groups',
289 Domain => 'ACLEquivalence', Type => 'UserEquiv', Instance => $id,
295 'Groups', 'Instance' => 'Users', 'id',
296 condition => 's.Domain = ? AND s.Type = ?',
297 bind_values => [ 'ACLEquivalence', 'UserEquiv' ],
300 return unless prompt(
301 'Delete', "Found an user ACL equivalence group, but there is no user."
304 delete_record( 'Groups', $id );
307 # one ACL equiv group for each user
310 columns => ['Instance'],
311 condition => '.Domain = ? AND .Type = ?',
312 bind_values => [ 'ACLEquivalence', 'UserEquiv' ],
316 # check integrity of Queue role groups
317 push @CHECKS, 'Queues <-> Role Groups' => sub {
318 # XXX: we check only that there is at least one group for a queue
319 # from queue to group
321 'Queues', 'id' => 'Groups', 'Instance',
322 join_condition => 't.Domain = ?',
323 bind_values => [ 'RT::Queue-Role' ],
325 # from group to queue
327 'Groups', 'Instance' => 'Queues', 'id',
328 condition => 's.Domain = ?',
329 bind_values => [ 'RT::Queue-Role' ],
332 return unless prompt(
333 'Delete', "Found role group of not existant queue."
336 delete_record( 'Groups', $id );
341 # check integrity of Ticket role groups
342 push @CHECKS, 'Tickets <-> Role Groups' => sub {
343 # XXX: we check only that there is at least one group for a queue
344 # from queue to group
346 'Tickets', 'id' => 'Groups', 'Instance',
347 join_condition => 't.Domain = ?',
348 bind_values => [ 'RT::Ticket-Role' ],
350 # from group to ticket
352 'Groups', 'Instance' => 'Tickets', 'id',
353 condition => 's.Domain = ?',
354 bind_values => [ 'RT::Ticket-Role' ],
357 return unless prompt(
358 'Delete', "Found a role group of not existant ticket."
361 delete_record( 'Groups', $id );
366 # additional CHECKS on groups
367 push @CHECKS, 'Role Groups (Instance, Type) uniqueness' => sub {
368 # Check that Domain, Instance and Type are unique
371 columns => ['Domain', 'Instance', 'Type'],
372 condition => '.Domain LIKE ?',
373 bind_values => [ '%-Role' ],
378 push @CHECKS, 'GMs -> Groups, Members' => sub {
379 my $msg = "A record in GroupMembers references an object that doesn't exist."
380 ." May be you deleted a group or principal directly from DB?"
381 ." Usually it's ok to delete such records.";
383 'GroupMembers', 'GroupId' => 'Groups', 'id',
386 return unless prompt( 'Delete', $msg );
388 delete_record( 'GroupMembers', $id );
392 'GroupMembers', 'MemberId' => 'Principals', 'id',
395 return unless prompt( 'Delete', $msg );
397 delete_record( 'GroupMembers', $id );
403 push @CHECKS, 'CGM vs. GM' => sub {
404 # all GM record should be duplicated in CGM
406 GroupMembers => ['GroupId', 'MemberId'],
407 CachedGroupMembers => ['GroupId', 'MemberId'],
408 join_condition => 't.ImmediateParentId = t.GroupId AND t.Via = t.id',
411 return unless prompt(
413 "Found a record in GroupMembers that has no direct duplicate in CachedGroupMembers table."
416 my $gm = RT::GroupMember->new( $RT::SystemUser );
418 die "Couldn't load GM record #$id" unless $gm->id;
419 my $cgm = create_record( 'CachedGroupMembers',
420 GroupId => $gm->GroupId, MemberId => $gm->MemberId,
421 ImmediateParentId => $gm->GroupId, Via => undef,
422 Disabled => 0, # XXX: we should check integrity of Disabled field
424 update_records( "CachedGroupMembers", { id => $cgm }, { Via => $cgm } );
427 # all first level CGM records should have a GM record
429 CachedGroupMembers => ['GroupId', 'MemberId'],
430 GroupMembers => ['GroupId', 'MemberId'],
431 condition => 's.ImmediateParentId = s.GroupId AND s.Via = s.id AND s.GroupId != s.MemberId',
434 return unless prompt(
436 "Found a record in CachedGroupMembers for a (Group, Member) pair"
437 ." that doesn't exist in GroupMembers table."
440 delete_record( 'CachedGroupMembers', $id );
443 # each group should have a CGM record where MemberId == GroupId
445 Groups => ['id', 'id'],
446 CachedGroupMembers => ['GroupId', 'MemberId'],
447 join_condition => 't.ImmediateParentId = t.GroupId AND t.Via = t.id',
450 return unless prompt(
452 "Found a record in Groups that has no direct"
453 ." duplicate in CachedGroupMembers table."
456 my $g = RT::Group->new( $RT::SystemUser );
458 die "Couldn't load group #$id" unless $g->id;
459 die "Loaded group by $id has id ". $g->id unless $g->id == $id;
460 my $cgm = create_record( 'CachedGroupMembers',
461 GroupId => $id, MemberId => $id,
462 ImmediateParentId => $id, Via => undef,
463 Disabled => $g->Disabled,
465 update_records( "CachedGroupMembers", { id => $cgm }, { Via => $cgm } );
469 # and back, each record in CGM with MemberId == GroupId without exceptions
470 # should reference a group
472 CachedGroupMembers => ['GroupId', 'MemberId'],
473 Groups => ['id', 'id'],
474 condition => "s.GroupId = s.MemberId",
477 return unless prompt(
479 "Found a record in CachedGroupMembers for a group that doesn't exist."
482 delete_record( 'CachedGroupMembers', $id );
487 CachedGroupMembers => 'Via',
488 CachedGroupMembers => 'id',
491 return unless prompt(
493 "Found a record in CachedGroupMembers with Via referencing not existing record."
496 delete_record( 'CachedGroupMembers', $id );
500 # for every CGM where ImmediateParentId != GroupId there should be
501 # matching parent record (first level)
503 CachedGroupMembers => ['ImmediateParentId', 'MemberId'],
504 CachedGroupMembers => ['GroupId', 'MemberId'],
505 join_condition => 't.Via = t.id',
506 condition => 's.ImmediateParentId != s.GroupId',
509 return unless prompt(
511 "Found a record in CachedGroupMembers that referencing not existant record in CachedGroupMembers table."
514 delete_record( 'CachedGroupMembers', $id );
518 # for every CGM where ImmediateParentId != GroupId there should be
519 # matching "grand" parent record
521 CachedGroupMembers => ['GroupId', 'ImmediateParentId', 'Via'],
522 CachedGroupMembers => ['GroupId', 'MemberId', 'id'],
523 condition => 's.ImmediateParentId != s.GroupId',
526 return unless prompt(
528 "Found a record in CachedGroupMembers that referencing not existant record in CachedGroupMembers table."
531 delete_record( 'CachedGroupMembers', $id );
535 # CHECK recursive records:
536 # if we have CGM1 (G1,M1,V1,IP1) then for every GM2(G2, M2), where G2 == M1,
537 # we should have CGM3 where G3 = G1, M3 = M2, V3 = ID1, IP3 = M1
540 SELECT cgm1.GroupId, gm2.MemberId, cgm1.id AS Via,
541 cgm1.MemberId AS ImmediateParentId, cgm1.Disabled
543 CachedGroupMembers cgm1
544 CROSS JOIN GroupMembers gm2
545 LEFT JOIN CachedGroupMembers cgm3 ON (
546 cgm3.GroupId = cgm1.GroupId
547 AND cgm3.MemberId = gm2.MemberId
548 AND cgm3.Via = cgm1.id
549 AND cgm3.ImmediateParentId = cgm1.MemberId )
550 WHERE cgm1.GroupId != cgm1.MemberId
551 AND gm2.GroupId = cgm1.MemberId
557 return unless prompt(
559 "Found records in CachedGroupMembers table without recursive duplicates."
561 my $cgm = create_record( 'CachedGroupMembers', %props );
564 my $sth = execute_query( $query );
565 while ( my ($g, $m, $via, $ip, $dis) = $sth->fetchrow_array ) {
566 print STDERR "Principal #$m is member of #$ip when #$ip is member of #$g,";
567 print STDERR " but there is no cached GM record that $m is member of #$g.\n";
569 GroupId => $g, MemberId => $m, Via => $via,
570 ImmediateParentId => $ip, Disabled => $dis,
577 push @CHECKS, 'Tickets -> other' => sub {
579 'Tickets', 'EffectiveId' => 'Tickets', 'id',
582 return unless prompt(
584 "Found a ticket that's been merged into a ticket that don't exist anymore."
587 delete_record( 'Tickets', $id );
591 'Tickets', 'Queue' => 'Queues', 'id',
594 'Tickets', 'Owner' => 'Users', 'id',
596 # XXX: check that owner is only member of owner role group
600 push @CHECKS, 'Transactions -> other' => sub {
601 foreach my $model ( @models ) {
603 'Transactions', 'ObjectId' => m2t($model), 'id',
604 condition => 's.ObjectType = ?',
605 bind_values => [ "RT::$model" ],
608 return unless prompt(
609 'Delete', "Found a transaction without object."
612 delete_record( 'Transactions', $id );
618 'Transactions', 'Field' => 'CustomFields', 'id',
619 condition => 's.Type = ?',
620 bind_values => [ 'CustomField' ],
622 # type = Take, Untake, Force, Steal or Give
624 'Transactions', 'OldValue' => 'Users', 'id',
625 condition => 's.Type IN (?, ?, ?, ?, ?)',
626 bind_values => [ qw(Take Untake Force Steal Give) ],
629 return unless prompt(
630 'Delete', "Found a transaction regarding changes of Owner,"
631 ." but User with id stored in OldValue column doesn't exist anymore."
634 delete_record( 'Transactions', $id );
638 'Transactions', 'NewValue' => 'Users', 'id',
639 condition => 's.Type IN (?, ?, ?, ?, ?)',
640 bind_values => [ qw(Take Untake Force Steal Give) ],
643 return unless prompt(
644 'Delete', "Found a transaction regarding changes of Owner,"
645 ." but User with id stored in NewValue column doesn't exist anymore."
648 delete_record( 'Transactions', $id );
653 'Transactions', 'OldValue' => 'Principals', 'id',
654 condition => 's.Type = ?',
655 bind_values => [ 'DelWatcher' ],
658 return unless prompt(
659 'Delete', "Found a transaction describing watchers change,"
660 ." but User with id stored in OldValue column doesn't exist anymore."
663 delete_record( 'Transactions', $id );
668 'Transactions', 'NewValue' => 'Principals', 'id',
669 condition => 's.Type = ?',
670 bind_values => [ 'AddWatcher' ],
673 return unless prompt(
674 'Delete', "Found a transaction describing watchers change,"
675 ." but User with id stored in NewValue column doesn't exist anymore."
678 delete_record( 'Transactions', $id );
682 # XXX: Links need more love, uri is stored instead of id
683 # # type = DeleteLink
685 # 'Transactions', 'OldValue' => 'Links', 'id',
686 # condition => 's.Type = ?',
687 # bind_values => [ 'DeleteLink' ],
691 # 'Transactions', 'NewValue' => 'Links', 'id',
692 # condition => 's.Type = ?',
693 # bind_values => [ 'AddLink' ],
696 # type = Set, Field = Queue
698 'Transactions', 'NewValue' => 'Queues', 'id',
699 condition => 's.Type = ? AND s.Field = ?',
700 bind_values => [ 'Set', 'Queue' ],
703 return unless prompt(
704 'Delete', "Found a transaction describing queue change,"
705 ." but Queue with id stored in NewValue column doesn't exist anymore."
708 delete_record( 'Transactions', $id );
712 'Transactions', 'OldValue' => 'Queues', 'id',
713 condition => 's.Type = ? AND s.Field = ?',
714 bind_values => [ 'Set', 'Queue' ],
717 return unless prompt(
718 'Delete', "Found a transaction describing queue change,"
719 ." but Queue with id stored in OldValue column doesn't exist anymore."
722 delete_record( 'Transactions', $id );
727 'Transactions', 'NewValue' => 'Tickets', 'id',
728 join_condition => 't.Type = ?',
729 condition => 's.Type IN (?, ?, ?)',
730 bind_values => [ 'reminder', 'AddReminder', 'OpenReminder', 'ResolveReminder' ],
735 push @CHECKS, 'Attachments -> other' => sub {
737 Attachments => 'TransactionId', Transactions => 'id',
740 return unless prompt(
741 'Delete', "Found an attachment without a transaction."
743 delete_record( 'Attachments', $id );
747 Attachments => 'Parent', Attachments => 'id',
750 return unless prompt(
751 'Delete', "Found an sub-attachment without its parent attachment."
753 delete_record( 'Attachments', $id );
757 Attachments => 'Parent',
759 join_condition => 's.TransactionId = t.TransactionId',
763 push @CHECKS, 'CustomFields and friends' => sub {
764 #XXX: ObjectCustomFields needs more love
766 'CustomFieldValues', 'CustomField' => 'CustomFields', 'id',
769 'ObjectCustomFieldValues', 'CustomField' => 'CustomFields', 'id',
771 foreach my $model ( @models ) {
773 'ObjectCustomFieldValues', 'ObjectId' => m2t($model), 'id',
774 condition => 's.ObjectType = ?',
775 bind_values => [ "RT::$model" ],
780 push @CHECKS, Templates => sub {
782 'Templates', 'Queue' => 'Queues', 'id',
786 push @CHECKS, Scrips => sub {
788 'Scrips', 'Queue' => 'Queues', 'id',
791 'Scrips', 'ScripCondition' => 'ScripConditions', 'id',
794 'Scrips', 'ScripAction' => 'ScripActions', 'id',
797 'Scrips', 'Template' => 'Templates', 'id',
801 push @CHECKS, Attributes => sub {
802 foreach my $model ( @models ) {
804 'Attributes', 'ObjectId' => m2t($model), 'id',
805 condition => 's.ObjectType = ?',
806 bind_values => [ "RT::$model" ],
811 # Fix situations when Creator or LastUpdatedBy references ACL equivalence
812 # group of a user instead of user
813 push @CHECKS, 'FIX: LastUpdatedBy and Creator' => sub {
815 foreach my $model ( @models ) {
816 my $class = "RT::$model";
817 my $object = $class->new( $RT::SystemUser );
818 foreach my $column ( qw(LastUpdatedBy Creator) ) {
819 next unless $object->_Accessible( $column, 'auto' );
821 my $table = m2t($model);
823 SELECT m.id, g.id, g.Instance
825 Groups g JOIN $table m ON g.id = m.$column
831 my ($gid, $uid) = @_;
832 return unless prompt(
834 "Looks like there were a bug in old versions of RT back in 2006\n"
835 ."that has been fixed. If other checks are ok then it's ok to update\n"
836 ."these records to point them to users instead of groups"
838 $fix{ $table }{ $column }{ $gid } = $uid;
841 my $sth = execute_query( $query, 'ACLEquivalence', 'UserEquiv' );
842 while ( my ($rid, $gid, $uid) = $sth->fetchrow_array ) {
843 print STDERR "Record #$rid in $table refers to ACL equivalence group #$gid of user #$uid";
844 print STDERR " when must reference user.\n";
845 $action->( $gid, $uid );
846 if ( keys( %fix ) > 1000 ) {
855 foreach my $table ( keys %fix ) {
856 foreach my $column ( keys %{ $fix{ $table } } ) {
857 my $query = "UPDATE $table SET $column = ? WHERE $column = ?";
858 while ( my ($gid, $uid) = each %{ $fix{ $table }{ $column } } ) {
859 update_records( $table, { $column => $gid }, { $column => $uid } );
863 $redo_check{'FIX: LastUpdatedBy and Creator'} = 1;
867 push @CHECKS, 'LastUpdatedBy and Creator' => sub {
868 foreach my $model ( @models ) {
869 my $class = "RT::$model";
870 my $object = $class->new( $RT::SystemUser );
871 my $table = $object->Table;
872 foreach my $column ( qw(LastUpdatedBy Creator) ) {
873 next unless $object->_Accessible( $column, 'auto' );
875 $table, $column => 'Users', 'id',
877 my ($id, %prop) = @_;
878 return unless my $replace_with = prompt_integer(
880 "Column $column should point to a user, but there is record #$id in table $table\n"
881 ."where it's not true. It's ok to replace these wrong references with id of any user.\n"
882 ."Note that id you enter is not checked. You can peak any user from your DB, but it's\n"
883 ."may be better to create a special user for this, for example 'user_that_has_been_deleted'\n"
884 ."or something like that.",
885 "$table.$column -> user #$prop{$column}"
887 update_records( $table, { id => $id, $column => $prop{$column} }, { $column => $replace_with } );
893 my %CHECKS = @CHECKS;
895 @do_check = do { my $i = 1; grep $i++%2, @CHECKS };
897 while ( my $check = shift @do_check ) {
898 $CHECKS{ $check }->();
900 foreach my $redo ( keys %redo_check ) {
901 die "check $redo doesn't exist" unless $CHECKS{ $redo };
902 delete $redo_check{ $redo };
903 next if grep $_ eq $redo, @do_check; # don't do twice
904 push @do_check, $redo;
908 sub check_integrity {
909 my ($stable, @scols) = (shift, shift);
910 my ($ttable, @tcols) = (shift, shift);
913 @scols = @{ $scols[0] } if ref $scols[0];
914 @tcols = @{ $tcols[0] } if ref $tcols[0];
916 print "Checking integrity of $stable.{". join(', ', @scols) ."} => $ttable.{". join(', ', @tcols) ."}\n"
919 my $query = "SELECT s.id, ". join(', ', map "s.$_", @scols)
920 ." FROM $stable s LEFT JOIN $ttable t"
922 ' AND ', map columns_eq_cond('s', $stable, $scols[$_] => 't', $ttable, $tcols[$_]), (0..(@scols-1))
924 . ($args{'join_condition'}? " AND ( $args{'join_condition'} )": "")
925 ." WHERE t.id IS NULL"
926 ." AND ". join(' AND ', map "s.$_ IS NOT NULL", @scols);
928 $query .= " AND ( $args{'condition'} )" if $args{'condition'};
930 my @binds = @{ $args{'bind_values'} || [] };
931 if ( $tcols[0] eq 'id' && @tcols == 1 ) {
932 my $type = $TYPE{"$stable.$scols[0]"} || 'number';
933 if ( $type eq 'number' ) {
934 $query .= " AND s.$scols[0] != ?"
936 elsif ( $type eq 'text' ) {
937 $query .= " AND s.$scols[0] NOT LIKE ?"
942 my $sth = execute_query( $query, @binds );
943 while ( my ($sid, @set) = $sth->fetchrow_array ) {
944 print STDERR "Record #$sid in $stable references not existent record in $ttable\n";
945 for ( my $i = 0; $i < @scols; $i++ ) {
946 print STDERR "\t$scols[$i] => '$set[$i]' => $tcols[$i]\n";
948 print STDERR "\t". describe( $stable, $sid ) ."\n";
949 $args{'action'}->( $sid, map { $scols[$_] => $set[$_] } (0 .. (@scols-1)) ) if $args{'action'};
954 my ($table, $id) = @_;
955 return '' unless my $cb = $describe_cb{ $table };
957 my $row = load_record( $table, $id );
958 unless ( $row->{id} ) {
960 return "$table doesn't exist";
962 return $cb->( $row );
965 sub columns_eq_cond {
966 my ($la, $lt, $lc, $ra, $rt, $rc) = @_;
967 my $ltype = $TYPE{"$lt.$lc"} || 'number';
968 my $rtype = $TYPE{"$rt.$rc"} || 'number';
969 return "$la.$lc = $ra.$rc" if $db_type ne 'Pg' || $ltype eq $rtype;
971 if ( $rtype eq 'text' ) {
972 return "$ra.$rc LIKE CAST($la.$lc AS text)";
974 elsif ( $ltype eq 'text' ) {
975 return "$la.$lc LIKE CAST($ra.$rc AS text)";
977 else { die "don't know how to cast" }
980 sub check_uniqueness {
984 my @columns = @{ $args{'columns'} };
986 print "Checking uniqueness of ( ", join(', ', map "'$_'", @columns )," ) in table '$on'\n"
990 if ( $scond = $tcond = $args{'condition'} ) {
991 $scond =~ s/(\s|^)\./$1s./g;
992 $tcond =~ s/(\s|^)\./$1t./g;
995 my $query = "SELECT s.id, t.id, ". join(', ', map "s.$_", @columns)
996 ." FROM $on s LEFT JOIN $on t "
997 ." ON s.id != t.id AND ". join(' AND ', map "s.$_ = t.$_", @columns)
998 . ($tcond? " AND ( $tcond )": "")
999 ." WHERE t.id IS NOT NULL "
1000 ." AND ". join(' AND ', map "s.$_ IS NOT NULL", @columns);
1001 $query .= " AND ( $scond )" if $scond;
1003 my $sth = execute_query(
1005 $args{'bind_values'}? (@{ $args{'bind_values'} }, @{ $args{'bind_values'} }): ()
1007 while ( my ($sid, $tid, @set) = $sth->fetchrow_array ) {
1008 print STDERR "Record #$tid in $on has the same set of values as $sid\n";
1009 for ( my $i = 0; $i < @columns; $i++ ) {
1010 print STDERR "\t$columns[$i] => '$set[$i]'\n";
1016 my ($table, $id) = @_;
1017 my $sth = execute_query( "SELECT * FROM $table WHERE id = ?", $id );
1018 return $sth->fetchrow_hashref('NAME_lc');
1022 my ($table, $id) = (@_);
1023 print "Deleting record #$id in $table\n" if $opt{'verbose'};
1024 my $query = "DELETE FROM $table WHERE id = ?";
1025 $redo_check{ $_ } = 1 foreach @{ $redo_on{'Delete'}{ $table } || [] };
1026 return execute_query( $query, $id );
1030 print "Creating a record in $_[0]\n" if $opt{'verbose'};
1031 $redo_check{ $_ } = 1 foreach @{ $redo_on{'Create'}{ $_[0] } || [] };
1032 return $RT::Handle->Insert( @_ );
1035 sub update_records {
1040 my (@where_cols, @where_binds);
1041 while ( my ($k, $v) = each %$where ) { push @where_cols, $k; push @where_binds, $v; }
1043 my (@what_cols, @what_binds);
1044 while ( my ($k, $v) = each %$what ) { push @what_cols, $k; push @what_binds, $v; }
1046 print "Updating record(s) in $table\n" if $opt{'verbose'};
1047 my $query = "UPDATE $table SET ". join(', ', map "$_ = ?", @what_cols)
1048 ." WHERE ". join(' AND ', map "$_ = ?", @where_cols);
1049 $redo_check{ $_ } = 1 foreach @{ $redo_on{'Update'}{ $table } || [] };
1050 return execute_query( $query, @what_binds, @where_binds );
1054 my ($query, @binds) = @_;
1056 print "Executing query: $query\n\n" if $opt{'verbose'};
1058 my $sth = $dbh->prepare( $query ) or die "couldn't prepare $query\n\tError: ". $dbh->errstr;
1059 $sth->execute( @binds ) or die "couldn't execute $query\n\tError: ". $sth->errstr;
1063 { my %cached_answer;
1067 my $token = shift || join ':', caller;
1069 return 0 unless $opt{'resolve'};
1070 return 1 if $opt{'force'};
1072 return $cached_answer{ $token } if exists $cached_answer{ $token };
1075 print "$action ALL records with the same defect? [N]: ";
1077 return $cached_answer{ $token } = 1 if $a =~ /^(y|yes)$/i;
1078 return $cached_answer{ $token } = 0;
1081 { my %cached_answer;
1083 my $actions = shift;
1085 my $token = shift || join ':', caller;
1087 return '' unless $opt{'resolve'};
1088 return '' if $opt{'force'};
1089 return $cached_answer{ $token } if exists $cached_answer{ $token };
1092 print join( ' or ', @$actions ) ." ALL records with the same defect? [do nothing]: ";
1095 return $cached_answer{ $token } = '' unless $a;
1096 foreach ( grep rindex(lc $_, lc $a, 0) == 0, @$actions ) {
1097 return $cached_answer{ $token } = lc substr $a, 0, 1;
1099 return $cached_answer{ $token } = '';
1102 { my %cached_answer;
1103 sub prompt_integer {
1106 my $token = shift || join ':', caller;
1108 return 0 unless $opt{'resolve'};
1109 return 0 if $opt{'force'};
1111 return $cached_answer{ $token } if exists $cached_answer{ $token };
1114 print "$action ALL records with the same defect? [0]: ";
1115 my $a = <STDIN>; chomp $a; $a = int($a);
1116 return $cached_answer{ $token } = $a;