[rt-users] Re: BUG in rt2->rt3 conversion re: keywords

[Nicolae P. Costescu]

Jamie
I'm sorry, I don't have a list of statements for you. But the basic outline 
I gave is correct I think...

You can do most of it from inside of Rt3.

First, check and make sure your global keywords did not get created in Rt3. 
You can look in RT3 or you can look at the CustomFields table, and look for 
rows with queue = 0.

If you have the same problem I did, what you'll see is one set of rows of 
CustomFields for each queue, instead of just one global set.

Now add the custom field values (to CustomFieldValues) for your new global 
custom fields. You can do it in RT3 (be careful to use the exact same 
spelling as your existing values) or you can just do it with sql statements 
(unfortunately since mysql has no views, no functions, no subselects, you 
have to make some temp tables). If you're using postgres it's much easier, 
you just do it in one statement with a subselect.

Now that you have that set up, here's the part you can't do in Rt3. You 
must do it in the database.

You have to remap all TicketCustomFieldValues from the queue specific 
CustomFields to the  global CustomFields. This is a many to one mapping.

Say you have a global custom field  which now has id Kg in the CustomFields 
table. Say the upgrade script created  identical custom fields based on 
this one, with id fields K1...Kn, one per queue. You want to upate any 
TicketCustomFieldValue rows with CustomField in (k1...Kn) to Kg. Repeat 
this for each global keyword with its corresponding queue specific clones.

Then you'll want to check and make sure you haven't missed any by looking 
at TicketCustomFieldValues that have CustomFields that do not map to your 
global CustomFields (again harder in mysql thanks to no subselects).

Then disable all the customfields that were queue specific clones of the 
global ones. Can do this in Rt3, or quicker in sql.

If users have used your system while you had those queue spec. clones, 
tickets may now have more than one value even for single select keywords - 
remove duplicates (I wrote  a program, was easier to do that than to 
struggle w/mysql limits  - again in postgres you could do it with a subselect).

At this point I also deleted TicketCustomFieldValues rows that had Content 
fields that no longer existed in the CustomFieldValues table.

Sorry I can't be more specific, I made the queries up as I went along and 
didn't save them. I also didn't find a 3.0 schema anywhere in the docs 
(there was one for 2.0).

Someone could write a fairly simple program based on the outline above to 
"fix up" botched 2->3 upgrades.

Good luck,
Nick



>Date: Tue, 15 Apr 2003 09:23:12 +1000
>From: Jamie Wilkinson <jaq at spacepants.org>
>To: rt-users at lists.fsck.com
>Subject: Re: [rt-users] Re: BUG in rt2->rt3 conversion re: keywords
>
>This one time, at band camp, Nicolae P. Costescu wrote:
> >I was able to straighten the keywords out with a lot of SQL gymnastics.
> >Basically I had to create new CustomFields, then new CustomFieldValues,
> >then map all the customfields from the TicketCustomFieldValues to the new
> >CustomFields. Then I disabled the queue specific custom fields for every
> >queue except 0 (global), except for the queues that really were supposed to
> >have queue specific custom fields (whew).
>
>Can you post the specific sql statements you used?  I've also been hit by
>this, and it's the last thing I need to fix for our RT3 migration.

****************************************************
Nicolae P. Costescu, Ph.D.  / Senior Developer
Stronghold Technologies
46040 Center Oak Plaza, Suite 160 / Sterling, Va 20166
Tel: 571-434-1472 / Fax: 571-434-1478