如何将版本从Bugzilla迁移到Redmine中新的自定义“源版本"字段(我需要哪种SQL语法)? [英] How can I migrate versions from bugzilla into a new custom 'source version' field in redmine (what SQL syntax do I need)?
问题描述
我正在将Bugzilla安装迁移到Redmine.
I'm in the process of migrating a bugzilla installation over to redmine.
在bugzilla中，总是记录导致该问题出现的软件版本的错误.
In bugzilla bugs were always logged against the version of software which caused the issue to be raised.
由于redmine支持路线图，因此我想做一些迁移工作.我想做的第一部分是将所有当前版本设置移至新的自定义源版本"字段.
Since redmine supports roadmaps I'd like to do a few things to migrate. The first part I'd like to do is to move all the current version settings over to a new custom 'source version' field.
我尝试创建一个名为源版本"的新自定义字段，并手动更改一些错误报告以使用该字段.一些有限的测试表明，为该字段指定的每个值都会在custom_values表中获得一个新条目.下面显示了三个错误，其中两个错误具有相同的自定义版本值1.2060:
I've tried making a new custom field called 'source version' and manually altering a few bug reports to use that field. Some limited testing suggests that each value specified for that field gets a new entry in the custom_values table. The following shows three bugs and two of those had the same custom version value of 1.2060:
--------------
SELECT * FROM custom_values WHERE custom_field_id=4
--------------
+------+-----------------+---------------+-----------------+--------+
| id | customized_type | customized_id | custom_field_id | value |
+------+-----------------+---------------+-----------------+--------+
| 4821 | Issue | 765 | 4 | 1.1098 |
| 4822 | Issue | 802 | 4 | 1.2060 |
| 4823 | Issue | 801 | 4 | 1.2060 |
+------+-----------------+---------------+-----------------+--------+
......其中custom_field_id与我添加的新的源版本"字段相关.
...where custom_field_id relates to the new 'source version' field I added.
所以我认为要填充表格，我需要为每个需要从当前版本复制的问题创建一个新条目.
So I think to populate the table I need to create a new entry for every issue which I need to copy from the current version.
如果有帮助，版本表具有以下结构:
If it helps, the versions table has the following structure:
--------------
SHOW COLUMNS FROM versions
--------------
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| project_id | int(11) | NO | MUL | 0 | |
| name | varchar(255) | NO | | | |
| description | varchar(255) | YES | | | |
| effective_date | date | YES | | NULL | |
| created_on | datetime | YES | | NULL | |
| updated_on | datetime | YES | | NULL | |
| wiki_page_title | varchar(255) | YES | | NULL | |
| status | varchar(255) | YES | | open | |
| sharing | varchar(255) | NO | MUL | none | |
+-----------------+--------------+------+-----+---------+----------------+
...并且问题表具有以下结构:
...and the issues table has the following structure:
--------------
SHOW COLUMNS FROM issues
--------------
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tracker_id | int(11) | NO | MUL | 0 | |
| project_id | int(11) | NO | MUL | 0 | |
| subject | varchar(255) | NO | | | |
| description | text | YES | | NULL | |
| due_date | date | YES | | NULL | |
| category_id | int(11) | YES | MUL | NULL | |
| status_id | int(11) | NO | MUL | 0 | |
| assigned_to_id | int(11) | YES | MUL | NULL | |
| priority_id | int(11) | NO | MUL | 0 | |
| fixed_version_id | int(11) | YES | MUL | NULL | |
| author_id | int(11) | NO | MUL | 0 | |
| lock_version | int(11) | NO | | 0 | |
| created_on | datetime | YES | MUL | NULL | |
| updated_on | datetime | YES | | NULL | |
| start_date | date | YES | | NULL | |
| done_ratio | int(11) | NO | | 0 | |
| estimated_hours | float | YES | | NULL | |
| parent_id | int(11) | YES | | NULL | |
| root_id | int(11) | YES | MUL | NULL | |
| lft | int(11) | YES | | NULL | |
| rgt | int(11) | YES | | NULL | |
| is_private | tinyint(1) | NO | | 0 | |
+------------------+--------------+------+-----+---------+----------------+
问题是，我需要执行什么神奇的SQL查询才能将版本复制到新字段并进行清理?我从来都不是SQL专家，所以将不胜感激地收到任何帮助！
The question is, what magical SQL query do I need to do to copy the versions over to the new fields and clean up? I've never been much of an SQL whizz so any help would be gratefully received!
编辑-我尝试了以下操作:
Edit - I tried the following:
INSERT INTO custom_values
(Issue, issues.id, 4, versions.name)
SELECT * FROM issues INNER JOIN versions ON issues.fixed_version_id=versions.id;
...但是它给了我一个错误:
...but it gives me an error:
--------------
INSERT INTO custom_values (Issue, issues.id, 4, versions.name) SELECT * FROM issues INNER JOIN versions ON issues.fixed_version_id=versions.id
--------------
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4, versions.name) SELECT * FROM issues INNER JOIN versions ON issues.fixed_version_id=ve' at line 1
推荐答案
您的查询
INSERT INTO custom_values
(Issue, issues.id, 4, versions.name)
SELECT * FROM issues INNER JOIN versions ON issues.fixed_version_id=versions.id;
应对此进行修改
INSERT INTO custom_values
(customized_type, customized_id, custom_field_id, value)
SELECT 'Issue', issues.id, 4, versions.name
FROM issues INNER JOIN versions ON issues.fixed_version_id=versions.id;
是有效的SQL.
但是我还是要备份数据库...:-)
But I'd backup the db anyway... :-)
这篇关于如何将版本从Bugzilla迁移到Redmine中新的自定义“源版本"字段(我需要哪种SQL语法)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
