Handling Gypsy Fields in an EE1 to EE2 Upgrade

One of the bigger hassles in updating an ExpressionEngine 1.x site to ExpressionEngine 2.x can be dealing with a site that has a lot of Gypsy fields. There is no denying that Gypsy was a huge time (and tedium) saver. But with no EE2 upgrade path, one is stuck paying double for all that convenience.

After having updated a few EE1 sites that made use of Gypsy to EE2, I thought I'd document the process that I use.

1. Manually create a new custom field that is a copy of the Gypsy field. Do this in any field group used by a weblog that relied on the Gypsy field. So for example if the original Gypsy field was a textarea, create a new textarea field in the appropriate field group(s).
2. With SQL, copy over the data from the original gypsy field to the new replacement field (code below).
3. Update the templates to use the new custom field name.
4. Repeat as necessary for each Gypsy field.

If you have a lot of Gypsy fields being used across a bunch of weblogs then it would be worthwhile to manage Step 1 above with SQL, or deal with your Gypsy fields after updating to EE2 so that you can take advantage of the time-saving Field Editor add-on.

Here is the SQL for Step 2 above to copy over the data from the original gypsy field to the new replacement field:

UPDATE exp_weblog_data
SET field_id_XX = field_id_YY
WHERE weblog_id = ZZ

Notes: Replace XX with the field_id of the new field you created to replace the Gypsy field. Replace YY with the field_id of the Gypsy field. Replace ZZ with the weblog_id of any applicable weblog(s) using the new field.

Just substitute "channel" for "weblog" if you do this on the EE2 end of the upgrade process.

Once the query has been run (I recommend using Sequel Pro), you can check the result by running the following Query. If everything is good, you should see two fields with identical data for each record. One is the original Gypsy field and the other is your new copy.

SELECT entry_id, field_id_YY, field_id_XX
FROM exp_weblog_data
WHERE weblog_id = ZZ

Don't forget to update your templates to use the new field and you're good to go.

For Matrix Gypsy Fields

If you've made any of your Matrix fields a Gypsy field the query you'll need to run is a bit more detailed.

UPDATE exp_weblog_data

JOIN exp_matrix_data ON exp_weblog_data.entry_id = exp_matrix_data.entry_id

SET 
/* the new Matrix field_id */
exp_matrix_data.field_id = 'XX'

/* new_col_id = old_col_id */
, col_id_24 = col_id_6
, col_id_25 = col_id_7

/* the gypsy field id */
WHERE exp_matrix_data.field_id = 'YY'

AND exp_weblog_data.weblog_id = 'ZZ'

Notes: Replace XX with the field_id of the new Matrix field you created to replace the Gypsy field. Replace each Matrix column_id as appopriate for your situation, mapping the new col_id to the old col_id. Replace YY with the field_id of the Gypsy field. Replace ZZ with the weblog_id of the applicable weblog using the new field.