One of the things I really like about Redmine (and Git), compared to Trac and Subversion, is the out-of-the-box support for multiple projects.
The web interface in Redmine makes it pretty easy to bulk move issues to new projects, as I was doing earlier today. Whilst it manages issues, relationships between issues, and time entries against issues, one minor thing it doesn’t seem to let me do through the web interface is move time entries that are not associated with an issue/task to a new project (for example, I have certain project management and deployment activities that are not related to a particular issue or task in Redmine).
In order to do this, I had to get into mysql. This is all some pretty basic SQL, but I thought I’d jot it down incase it’s of use to anyone.
BACKUP!!!
First, and it goes without saying, but I will anyway – take a backup!
lhunt@vm-srv052:~$ rsync -a /var/www/redmine/files /backup
lhunt@vm-srv052:~$ /usr/bin/mysqldump -u redmine -predmine redmine > /backup/db/redmine.sql
Now, get a list of Projects:
mysql> select id, name, parent_id from projects;
+----+-----------------------+-----------+
| id | name | parent_id |
+----+-----------------------+-----------+
| 1 | Project X | NULL |
| 2 | Sub project 1 | 1 |
| 3 | Silverlight migration | 1 |
| 4 | Version 2 | 1 |
+----+-----------------------+-----------+
4 rows in set (0.00 sec)
Then review all those entries without the correct sub-project associated to with them, in this case, everything still associated with the original parent project, Project X.
mysql> select id, project_id, spent_on, comments from time_entries where project_id =1 ;
+-----+------------+------------+-----------------------------------------+
| id | project_id | spent_on | comments |
+-----+------------+------------+-----------------------------------------+
| 22 | 1 | 2010-10-29 | comments related to ticket... |
| 27 | 1 | 2010-11-01 | Some more comments related to ticket... |
| .. | .. | .......... | ....................................... |
| .. | .. | .......... | ....................................... |
+-----+------------+------------+-----------------------------------------+
In my case, I had a minority of entries that should now be associated with the new Silverlight migration sub-project I’m working on….
You could do this by picking individual entries, or by searching the comments…
mysql> update time_entries set project_id = 3 where id in (102, 106, 109, 110);
Query OK, 4 rows affected (0.03 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> update time_entries set project_id = 3 where comments like '%silverlight%';
Query OK, 5 rows affected (0.03 sec)
Rows matched: 9 Changed: 5 Warnings: 0
And the rest should be associated with a new sub-project for a piece of stabilisation work…
mysql> update time_entries set project_id = 2 where project_id = 1;
Query OK, 38 rows affected (0.04 sec)
Rows matched: 38 Changed: 38 Warnings: 0
Done!
Update 24 Dec
Err – I lied – it wasn’t completely done – I had some Documents that were referenced by Title rather than ID, which then failed to link across projects.
Quick fix:
mysql> select id, project_id, title from documents;
+----+------------+----------------+
| id | project_id | title |
+----+------------+----------------+
| 1 | 1 | V1.3.3.3 ..... |
| 2 | 1 | Training Env.. |
| 3 | 1 | V1.3.3.4a .... |
| 4 | 1 | Flow diagram.. |
| 5 | 1 | 20101203...... |
| 6 | 1 | Training Env.. |
| 7 | 1 | V1 3 3 4b UAT..|
+----+------------+----------------+
7 rows in set (0.00 sec)
All the documents needed to be moved to the Stabilisation project, so again a really simple bit of SQL…
mysql> update documents set project_id = 2;
Query OK, 7 rows affected (0.10 sec)
Rows matched: 7 Changed: 7 Warnings: 0
