wiki:AdoptingTrac

Version 1 (modified by hauma, 15 years ago) (diff)

--

Adopting Trac

The move towards Trac required some harmonization of fuzzy naming used in the tracking system used previously.

Manual changes to the database

# List all components used in the ticket table that are not listed in
# component table.
#
SELECT DISTINCT component FROM ticket WHERE component NOT IN (SELECT name FROM component) ;


# Update old-fashioned spelling of components.
#
UPDATE ticket SET component="jpc" WHERE component="JPC" ;
UPDATE ticket SET component="gjc" WHERE component="GJC" ;
UPDATE ticket SET component="uka.karmi" WHERE component="KaRMI" ;
UPDATE ticket SET component="karmic" WHERE component="KaRMIc" ;
UPDATE ticket SET component="JP trafo" WHERE component="JP-TRAFO" ;


# Update components for tickets previously classified as "JP"
#
UPDATE ticket SET component="JP trafo" WHERE id=27;
UPDATE ticket SET component="JP environment" WHERE id=31;
UPDATE ticket SET component="JP trafo" WHERE id=37;
UPDATE ticket SET component="JP environment" WHERE id=39;
UPDATE ticket SET component="JP trafo" WHERE id=40;
UPDATE ticket SET component="build process" WHERE id=41;
UPDATE ticket SET component="JP trafo" WHERE id=42;
UPDATE ticket SET component="JP environment" WHERE id=43;
UPDATE ticket SET component="jpc" WHERE id=44;
UPDATE ticket SET component="JP trafo" WHERE id=48;
UPDATE ticket SET component="JP environment" WHERE id=49;
UPDATE ticket SET component="jpc" WHERE id=50;
UPDATE ticket SET component="JP environment" WHERE id=51;
UPDATE ticket SET component="JP trafo" WHERE id=52;
UPDATE ticket SET component="jpc" WHERE id=53;
UPDATE ticket SET component="JP environment" WHERE id=57;
UPDATE ticket SET component="JP trafo" WHERE id=59;
UPDATE ticket SET component="JP trafo" WHERE id=61;
UPDATE ticket SET component="JP environment" WHERE id=67;
UPDATE ticket SET component="jpc" WHERE id=68;
UPDATE ticket SET component="build process" WHERE id=73;
UPDATE ticket SET component="JP environment" WHERE id=77;
UPDATE ticket SET component="JP trafo" WHERE id=78;
UPDATE ticket SET component="JP trafo" WHERE id=80;
UPDATE ticket SET component="JP trafo" WHERE id=86;
UPDATE ticket SET component="JP environment" WHERE id=108;
UPDATE ticket SET component="JP trafo" WHERE id=114;
UPDATE ticket SET component="JP environment" WHERE id=116;
UPDATE ticket SET component="JP trafo" WHERE id=117;
UPDATE ticket SET component="JP trafo" WHERE id=118;
UPDATE ticket SET component="JP trafo" WHERE id=122;
UPDATE ticket SET component="JP environment" WHERE id=125;
UPDATE ticket SET component="JP environment" WHERE id=128;
UPDATE ticket SET component="JP environment" WHERE id=129;
UPDATE ticket SET component="JP environment" WHERE id=130;
UPDATE ticket SET component="JP environment" WHERE id=131;
UPDATE ticket SET component="JP environment" WHERE id=132;
UPDATE ticket SET component="JP environment" WHERE id=135;
UPDATE ticket SET component="JP trafo" WHERE id=136;
UPDATE ticket SET component="JP environment" WHERE id=138;
UPDATE ticket SET component="JP trafo" WHERE id=139;
UPDATE ticket SET component="JP trafo" WHERE id=140;
UPDATE ticket SET component="JP environment" WHERE id=142;
UPDATE ticket SET component="JP environment" WHERE id=146;
UPDATE ticket SET component="JP trafo" WHERE id=158;
UPDATE ticket SET component="uka.transport" WHERE id=170;
UPDATE ticket SET component="jpc" WHERE id=196;
UPDATE ticket SET component="JP trafo" WHERE id=197;
UPDATE ticket SET component="JP environment" WHERE id=198;
UPDATE ticket SET component="JP environment" WHERE id=199;
UPDATE ticket SET component="JP trafo" WHERE id=204;


# Move uka.karo tickets to uka.karo.
#
UPDATE ticket SET component="uka.karo" WHERE id=157;
UPDATE ticket SET component="uka.karo" WHERE id=165;
UPDATE ticket SET component="uka.karo" WHERE id=177;
UPDATE ticket SET component="uka.karo" WHERE id=178;
UPDATE ticket SET component="uka.karo" WHERE id=180;
UPDATE ticket SET component="uka.karo" WHERE id=188;
UPDATE ticket SET component="uka.karo" WHERE id=213;
UPDATE ticket SET component="uka.karo" WHERE id=219;
UPDATE ticket SET component="uka.karo" WHERE id=167;
UPDATE ticket SET component="uka.karo" WHERE id=205;
UPDATE ticket SET component="uka.karo" WHERE id=206;
UPDATE ticket SET component="uka.karo" WHERE id=207;
UPDATE ticket SET component="uka.karo" WHERE id=208;
UPDATE ticket SET component="uka.karo" WHERE id=171;
UPDATE ticket SET component="uka.karo" WHERE id=172;
UPDATE ticket SET component="uka.karo" WHERE id=181;
UPDATE ticket SET component="uka.karo" WHERE id=182;
UPDATE ticket SET component="uka.karo" WHERE id=183;
UPDATE ticket SET component="uka.karo" WHERE id=220;


# Delete ticket change history, where the change refers to a
# non-existing component.
#
DELETE FROM ticket_change WHERE field = "component" AND (oldvalue NOT IN (SELECT name FROM component)) ;


# A fancy query. Is a more simple formulation possible (without that
# much aliasing)?
#
SELECT tid, tsummary, tcomponent, coldvalue, cnewvalue FROM (SELECT t.id AS tid, t.summary AS tsummary, t.component AS tcomponent, c.field AS cfield, c.oldvalue AS coldvalue, c.newvalue AS cnewvalue FROM ticket AS t, ticket_change AS c WHERE t.id = c.ticket) WHERE cfield = "component" AND tcomponent != cnewvalue;