[rt-users] Oddity within the RT database

[Tim Cutts]

On 20 Mar 2012, at 10:52, Ruslan Zakirov wrote:

> 
> 
> Ruslan from phone.
> 20.03.2012 14:31 пользователь "Tim Cutts" <tjrc at sanger.ac.uk> написал:
> >
> >
> > On 20 Mar 2012, at 08:26, Ruslan Zakirov wrote:
> >
> > > Have you tried to delete them in ui?
> >
> > Is that possible?  These don't show up in the query builder is loadable saved searches - they're the RT::System standard ones, not user or group saved searches.
> >
> > > Use rt-attributes viewer that is shipped with rt and installed into sbin directory.
> >
> > I've been using that to investigate them, yes.
> >
> > > Decide which one you need and just delete an attribute.
> >
> > From what I can see, both are in use by the various dashboards and things that users have set.  I think I may have to write a script to go through all the Attributes blob, and for these duplicated things set them to one particular one, and only once I've done that can I safely delete the redundant saved search.
> 
> This is better way to go.

Yep.  After all that care, it turned out only one dashboard needed updating before I deleted the duplicate rows, but for the record here's what I did.  Up front information:

Two system saved searches were duplicated, so I wanted to replace references to search 495 with 492, and search 496 with 493.

Step 1, update Dashboard references to the redundant searches, for which I used the script attached at the end of this mail.

Step 2, with straightforwards SQL, rename preferences associated with the redundant searches:

UPDATE Attributes SET Name = 'Pref-RT::Attribute-492' WHERE Name = 'Pref-RT::Attribute-495';
UPDATE Attributes SET Name = 'Pref-RT::Attribute-493' WHERE Name = 'Pref-RT::Attribute-496';

Step 3, delete the redundant searches:

DELETE FROM Attributes WHERE id = 495;
DELETE FROM Attributes WHERE id = 496;

I think that's about all that's required.  Certainly no-one's screaming...

I don't think anything's needed for homepage portlet preferences, since those appear to be store by search name rather than by id.  It's possible some users might have saved graphs, but I think I'll cross that bridge if someone complains, because the numbers will be single digits.

I'd always assumed that the home page was simply a special case of a dashboard, and was rather surprised to see that the implementation is completely different.  Is that for historical reasons?  I'm trying to remember - I suspect the customisable homepage was written first (3.6?) and dashboards came later (3.8?).

Regards,

Tim

PS.  Here's my little script:

#!/usr/bin/perl
use strict;
use warnings;

use lib qw(/opt/rt4/lib /opt/rt4/local/lib);

use Data::Dumper;
use RT;
use RT::Attributes;
use RT::Dashboard;
use RT::User;

RT::LoadConfig();
RT::Init();

# These are the system search id's I want to map across
my %attr_map = ( 495 => 492,
		 496 => 493 );

my $attributes = RT::Attributes->new(RT->SystemUser);
my $dashboard = RT::Dashboard->new(RT->SystemUser);
my $user = RT::User->new(RT->SystemUser);

$attributes->Limit( FIELD => 'Name', VALUE => 'Dashboard');

my ($res, $err);

while (my $attr = $attributes->Next) {
    # RT::SystemUser has no rights to load user's
    # dashboards, so we load the creating user's object to
    # use when loading the dashboard.
    ($res, $err) = $user->LoadById($attr->Creator);
    unless ($res) {
	warn "Could not load user: ", $attr->Creator, $err;
	next;
    }

    my $dashboard = RT::Dashboard->new($user);
    ($res, $err) = $dashboard->LoadById($attr->Id);
    unless ($res) {
	warn "Could not load dashboard ", $attr->Id,
             " as user ", $user->Name, ":", $err;
        next;
    }

    my ($pane, $portlets);
    my $panes = $dashboard->Panes;
    my $before = Dumper($panes);
    while (($pane, $portlets) = each %$panes) {
	foreach my $portlet (@$portlets) {
	    next unless ($portlet->{portlet_type} eq 'search');
	    if (exists($attr_map{$portlet->{id}})) {
		$portlet->{id} = $attr_map{$portlet->{id}};
	    }
	}
    }

    my $after = Dumper($panes);
    if ($before ne $after) {
	($res, $err) = $dashboard->Update(Panes => $panes);
	if ($res) {
	    printf q(
User: %s
Dashboard: %s (%d)
Before: %s
After: %s
), $user->Name, $dashboard->Name, $dashboard->Id, $before, $after;
	} else {
	    warn "Failed to update dashboard ",  $dashboard->Id,
	    ': ', $err;
	}
    }
}





-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.