[rt-users] migration scripts lose keywords and priorities

Jamie Wilkinson jaq at spacepants.org
Tue Apr 8 22:25:01 EDT 2003


This one time, at band camp, Jamie Wilkinson wrote:
>This one time, at band camp, Jesse Vincent wrote:
>>What version of the import tool?
>
>rt2-to-rt3-1.7 as per my email yesterday.

What I found was that the priority field in the tickets table wasn't
correct, though the transactions table had the fields listed correctly.

So, here's some psql crack that fixed up the priorities.  Our resident pg
guru sends his apologies for the sql you are about to see :-)

-- first, grab a list of tickets and their latest priorities.

select ticket, (select newvalue from transactions where transactions.ticket
= t.ticket and field = 'Priority' order by created desc limit 1) as
newvalue from transactions t group by t.ticket, t.field having field =
'Priority' ;

-- then in a transaction:

begin transaction;

-- 10 points to whoever can decode this
update tickets set priority = (select int4(newvalue) from transactions where
transactions.ticket = tickets.id and field = 'Priority' and newvalue
is not null order by created desc limit 1) where tickets.id =
transactions.ticket and (select int4(newvalue) from transactions where
transactions.ticket = tickets.id and field = 'Priority' and newvalue
is not null order by created desc limit 1) is not null;

-- and compare to your original results
select distinct tickets.id, priority from tickets inner join transactions
on transactions.ticket = tickets.id where transactions.field = 'Priority';

-- if it's okay, otherwise rollback
commit;

-- 
jaq at spacepants.org                           http://spacepants.org/jaq.gpg



More information about the rt-users mailing list