How do I manipulate a VARCHAR field in SQL to simplify a collection of roadmaps in a Bugzilla to Redmine migration?

Question

As per an earlier question, I'm migrating from Bugzilla to Redmine and in doing so, I'd like to make use of the road-maps which Redmine offers.

In bugzilla, bugs were always logged against the version of software which caused the issue to be raised and although I've now preserved this information in a custom field (see the earlier question mentioned above), I now need to reduce the roadmaps down to something more manageable i.e:

1. Change versions of all bugs which are closed to a simplified equivalent which fits with the roadmap (e.g 0.1234 becomes 0.1 and 2.9876 becomes 2.9). This allows any one road-mapped version to have up to 999 sub-versions which is what we tended to do with Bugzilla already.
2. Change all bugs which are open to a new 'Unplanned' version.
3. Remove all of the current unused version numbers

I imagine this could be achieved with the following steps:

1. Getting all current versions available
2. For each version retrieved, strip off all but the first three characters
3. Check whether a version number for that product already exists.
4. If the version number is new, add it as a new version.
5. Running through every issue in the database and (a) if the issue is closed, assign it to the shortened version number or (b) if the issue is still open, assign it to a 'Unplanned' version.

...but alas, my lack of SQL knowledge is letting me down. Does anyone have any idea how to solve this?

Answer 1

This query gets you all the 3-character version names of all existing versions, by project:

SELECT DISTINCT v.project_id, Left(v.name, 3) newversionname
    FROM issues i
        INNER JOIN versions v ON i.fixed_version_id=v.id

You'll need a list of all 3-character version names which do not yet exist:

SELECT f.project_id, f.newversionname
    FROM (
        SELECT DISTINCT v.project_id, Left(v.name, 3) newversionname
            FROM issues i INNER JOIN versions v ON i.fixed_version_id=v.id
    ) f
    LEFT OUTER JOIN versions v2 ON f.project_id = v2.project_id and f.newversionname=v2.name
    WHERE v2.project_id is null

You'll need to insert new version for each result of the above query (I'll leave the adaptation of the above query as an INSERT query to you...).

---

EDIT: Updated query to add version details

SELECT f.project_id, f.newversionname, v3.description, v3.created_on, v3.updated_on
    FROM (
        SELECT v.project_id, Left(v.name, 3) newversionname, MIN(v.id) minversionid
            FROM issues i INNER JOIN versions v ON i.fixed_version_id=v.id
            GROUP BY v.project_id
    ) f
    LEFT OUTER JOIN versions v2 ON f.project_id = v2.project_id and f.newversionname=v2.name
    INNER JOIN versions v3 ON f.minversionid=v3.id
    WHERE v2.project_id is null

This will simply select the details of the version with the lowest id for each new version.

---

Note: This will break if you have version bigger than 9 (ie 11.234 becomes 11.).

Now we now that for every issue associatted with an old version, there exists a new 3-character version. The following query shows which one:

SELECT DISTINCT i.id, v.id oldversionid, v.name oldversionname, v2.id newversionid, v2.name newversionname
    FROM issues i
        INNER JOIN versions v ON i.fixed_version_id=v.id
        INNER JOIN versions v2 ON LEFT(v.name, 3) = v2.name and v.project_id = v2.project_id
    WHERE v.id <> v2.id

You may use this query and adapt it as an UPDATE query after a sanity check. You'll still need to add the criteria to distinguish between the open and closed issues (status_id).

Hope this helps with your migration, have fun with redmine!