bz2redmine.rb

Alexander Zhovnuvaty, 2010-04-06 23:17

Download (16.7 KB)

 
1
#!/usr/local/bin/ruby
2
#
3
# Copyright (c) 2009, Ralph Juhnke
4
# All rights reserved.
5
#
6
# Redistribution and use in source and binary forms, with or without modification, 
7
# are permitted provided that the following conditions are met:
8
#
9
#    1. Redistributions of source code must retain the above copyright notice, 
10
#       this list of conditions and the following disclaimer.
11
#    2. Redistributions in binary form must reproduce the above copyright notice, 
12
#       this list of conditions and the following disclaimer in the documentation and/or other
13
#       materials provided with the distribution.
14
# 
15
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND 
16
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
17
# MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO 
18
# EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, 
19
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
20
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
21
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON 
22
# ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 
23
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, 
24
# EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
25
# 
26

    
27
require "rubygems"
28
require "mysql"
29
require "settings"
30

    
31
class ConnectionInfo
32
  attr_accessor :host
33
  attr_accessor :user
34
  attr_accessor :password
35
  attr_accessor :dbname
36

    
37
  def initialize(host, user, password, dbname)
38
    @host = host
39
    @user = user
40
    @password = password
41
    @dbname = dbname
42
  end
43
end
44

    
45
class BugzillaToRedmine
46
  def initialize
47
    @bugzillainfo =  ConnectionInfo.new(BUGZILLA_HOST, BUGZILLA_USER, BUGZILLA_PASSWORD, BUGZILLA_DB)
48
    @redmineinfo = ConnectionInfo.new(REDMINE_HOST, REDMINE_USER, REDMINE_PASSWORD, REDMINE_DB)
49

    
50
    # Bugzilla priority to Redmine priority map
51
    @issuePriorities = ISSUE_PRIORITIES
52

    
53
    # Bugzilla severity to Redmine tracker map
54
    @issueTrackers = ISSUE_TRACKERS
55

    
56
    # Bugzilla status to Redmine status map
57
    @issueStatus = ISSUE_STATUS 
58
  end
59

    
60
  def migrate
61
    self.open_connections
62
    self.clear_redmine_tables
63
    self.migrate_projects
64
    self.migrate_versions
65
    self.migrate_users
66
        self.migrate_groups
67
    self.migrate_members
68
        self.migrate_member_roles
69
        self.migrate_groups_users
70
        self.migrate_categories
71
    self.migrate_issues
72
        self.migrate_watchers
73
    self.migrate_issue_relations
74
    self.migrate_attachments
75
    self.close_connections
76
  end
77

    
78
  def open_connections
79
    @bugzilladb = self.open_connection(@bugzillainfo)
80
    @redminedb = self.open_connection(@redmineinfo)
81
  end
82

    
83
  def close_connections
84
    self.log "closing database connections"
85
    @bugzilladb.close
86
    @redminedb.close
87
  end
88

    
89
  def         open_connection(info)
90
    self.log "opening #{info.inspect}"
91
    return Mysql::new(info.host, info.user, info.password, info.dbname)
92
  end
93

    
94
  def clear_redmine_tables
95
    sqls = [
96
      "DELETE FROM projects",
97
      "DELETE FROM projects_trackers",
98
      "DELETE FROM enabled_modules",
99
      "DELETE FROM boards",
100
      "DELETE FROM custom_fields_projects",
101
      "DELETE FROM documents",
102
      "DELETE FROM news",
103
      "DELETE FROM queries",
104
      "DELETE FROM repositories",
105
      "DELETE FROM time_entries",
106
      "DELETE FROM wiki_content_versions",
107
      "DELETE FROM wiki_contents",
108
      "DELETE FROM wiki_pages",
109
      "DELETE FROM wiki_redirects",
110
      "DELETE FROM wikis",
111
    ]
112
    sqls.each do |sql|
113
      self.red_exec_sql(sql)
114
    end
115
  end
116

    
117
  def log(s)
118
    puts s
119
  end
120

    
121
  def migrate_projects
122
    self.bz_select_sql("SELECT products.id, products.name, products.description, products.classification_id, products.disallownew, classifications.name as classification_name FROM products, classifications WHERE products.classification_id = classifications.id") do |row|
123
      identifier = row[1].downcase
124
      status = row[3] == 1 ? 9 : 1
125
      created_at = self.find_min_created_at_for_product(row[0])
126
      updated_at = self.find_max_bug_when_for_product(row[0])
127
      self.red_exec_sql("INSERT INTO projects (id, name, description, is_public, identifier, created_on, updated_on, status) values (?, ?, ?, ?, ?, ?, ?, ?)", row[0], row[1], row[2], 1, identifier,
128
        created_at, updated_at, status)
129
      self.insert_project_trackers(row[0])
130
      self.insert_project_modules(row[0])
131
    end
132
  end
133

    
134
  def find_min_created_at_for_product(product_id)
135
    bug_when = '1970-01-01 10:22:25'
136
    sql = "select min(b.creation_ts) from products p join bugs b on b.product_id = p.id where product_id=?"
137
    self.bz_select_sql(sql, product_id) do |row|
138
      bug_when = row[0]
139
    end
140
    return bug_when
141
  end
142

    
143
  def find_max_bug_when_for_product(product_id)
144
    bug_when = '1970-01-01 10:22:25'
145
    sql = "select max(l.bug_when) from products p join bugs b on b.product_id = p.id join longdescs l on l.bug_id = b.bug_id where b.product_id=?"
146
    self.bz_select_sql(sql, product_id) do |row|
147
      bug_when = row[0]
148
    end
149
    return bug_when
150
  end
151

    
152
  def migrate_versions
153
    self.red_exec_sql("delete from versions")
154
    self.bz_select_sql("SELECT id, product_id AS project_id, value AS name FROM milestones") do |row|
155
      self.red_exec_sql("INSERT INTO versions (id, project_id, name) VALUES (?, ?, ?)", row[0], row[1], row[2])
156
    end
157
  end
158

    
159
  def migrate_users
160
    ["DELETE FROM users",
161
      "DELETE FROM user_preferences",
162
      "DELETE FROM members",
163
      "DELETE FROM member_roles",          
164
      "DELETE FROM groups_users",                    
165
      "DELETE FROM messages",     
166
      "DELETE FROM tokens",
167
      "DELETE FROM watchers"].each do |sql|
168
      self.red_exec_sql(sql)
169
    end
170
    self.bz_select_sql("SELECT userid, login_name, realname, disabledtext FROM profiles") do |row|
171
      user_id = row[0]
172
      login_name = row[1]
173
      real_name = row[2]
174
      disabled_text = row[3]
175
      if real_name.nil? 
176
        (last_name, first_name) = ['empty', 'empty']
177
      else
178
        (last_name, first_name) = real_name.split(/[ ]+/)
179
        if first_name.to_s.strip.empty?
180
          first_name = 'empty'
181
        end
182
      end
183
      status = disabled_text.to_s.strip.empty? ? 1 : 3
184
      self.red_exec_sql("INSERT INTO users (id, login, mail, firstname, lastname, language, mail_notification, status, hashed_password, type) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
185
        user_id, login_name, login_name, last_name, first_name, 'en', 0, status, 'd033e22ae348aeb5660fc2140aec35850c4da997', 'User')
186
      other = """---
187
:comments_sorting: asc
188
:no_self_notified: true
189
      """
190
      self.red_exec_sql("INSERT INTO user_preferences (user_id,others) values (?, ?)", user_id, other)
191
    end
192
  end
193

    
194
  def migrate_groups
195
    self.bz_select_sql("select name from groups") do |row|
196
      name = row[0]
197
      self.red_exec_sql("insert into users (lastname, mail_notification, admin, status, type, language) values (?, ?, ?, ?, ?, ?)",
198
        name, 1, 0, 1, 'Group', 'en')
199
    end
200
  end
201
  
202
  def find_version_id(project_id, version)
203
    result = -1
204
    self.red_select_sql("select id from versions where project_id=? and name=?", project_id, version) do |row|
205
      result = row[0]
206
    end
207
    return result
208
  end
209

    
210
  def find_max_bug_when(bug_id)
211
    bug_when = '1970-01-01 10:22:25'
212
    self.bz_select_sql("select max(bug_when) from longdescs where bug_id=?", bug_id) do |row|
213
      bug_when = row[0]
214
    end
215
    return bug_when
216
  end
217
  
218
  def migrate_categories
219
    self.red_exec_sql("delete from issue_categories")
220
    self.bz_select_sql("SELECT id, name, product_id AS project_id, initialowner AS assigned_to_id FROM components") do |row|
221
      self.red_exec_sql("INSERT INTO issue_categories (id, name, project_id, assigned_to_id) VALUES (?, ?, ?, ?)", row[0], row[1], row[2], row[3])
222
    end
223
  end
224

    
225
  def migrate_watchers
226
    self.red_exec_sql("delete from watchers")
227
    self.bz_select_sql("select bug_id, who FROM cc") do |row|
228
      self.red_exec_sql("insert into watchers (watchable_type, watchable_id, user_id) values (?, ?, ?)", 'Issue', row[0], row[1])
229
    end
230
  end
231

    
232
  def insert_custom_fields
233
        self.red_exec_sql("delete from custom_fields")
234
        self.red_exec_sql("delete from custom_fields_trackers")
235
        self.red_exec_sql("delete from custom_values")        
236
        self.red_exec_sql("INSERT INTO custom_fields (id, type, name, field_format, possible_values, max_length, is_for_all, is_filter, searchable, default_value) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", 1, 'IssueCustomField', 'URL', 'string', '--- []/n/n', 255, 1, 1, 1, '')
237
        [1,2,3].each do |tracker_id|
238
      self.red_exec_sql("INSERT INTO custom_fields_trackers (custom_field_id, tracker_id) VALUES (?, ?)", 1, tracker_id)
239
    end
240
  end
241
  
242
  def migrate_issues
243
    self.red_exec_sql("delete from issues")
244
    self.red_exec_sql("delete from journals")
245
        self.insert_custom_fields
246
    sql = "SELECT bugs.bug_id,
247
                       bugs.assigned_to,
248
                       bugs.bug_status,
249
                       bugs.creation_ts,
250
                       bugs.short_desc,
251
                       bugs.product_id,
252
                       bugs.reporter,
253
                       bugs.version,
254
                       bugs.resolution,
255
                       bugs.estimated_time,
256
                       bugs.remaining_time,
257
                       bugs.deadline,
258
                           bugs.target_milestone,
259
                       bugs.bug_severity,
260
                       bugs.priority,
261
                       bugs.component_id,
262
                       bugs.status_whiteboard AS whiteboard,
263
                       bugs.bug_file_loc AS url,
264
                       longdescs.comment_id,
265
                       longdescs.thetext,
266
                       longdescs.bug_when,
267
                       longdescs.who,
268
                       longdescs.isprivate
269
                   FROM bugs, longdescs
270
                   WHERE bugs.bug_id = longdescs.bug_id
271
                   ORDER BY bugs.bug_id, longdescs.bug_when"
272
    current_bug_id = -1
273
    self.bz_select_sql(sql) do |row|
274
      ( bug_id,
275
        assigned_to,
276
        bug_status,
277
        creation_ts,
278
        short_desc,
279
        product_id,
280
        reporter,
281
        version,
282
        resolution,
283
        estimated_time,
284
        remaining_time,
285
        deadline,
286
                target_milestone,
287
        bug_severity,
288
        priority,
289
        component_id,
290
        whiteboard,
291
        url,
292
        comment_id,
293
        thetext,
294
        bug_when,
295
        who,
296
        isprivate) = row
297
      if(current_bug_id != bug_id)
298
        sql = "INSERT INTO issues (id, project_id, subject, description, assigned_to_id, author_id, created_on, updated_on, start_date, estimated_hours, priority_id, fixed_version_id, category_id, tracker_id, status_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
299
        status_id = 1
300
        version_id = self.find_version_id(product_id, version)
301
                target_milestone_id = self.find_version_id(product_id, target_milestone)
302
        updated_at = self.find_max_bug_when(bug_id)
303
        priority_id = @issuePriorities[priority]
304
        tracker_id = @issueTrackers[bug_severity]
305
        status_id = @issueStatus[bug_status]
306
        self.red_exec_sql(sql, bug_id, product_id, short_desc, thetext, assigned_to, reporter, creation_ts,  updated_at, creation_ts, estimated_time, priority_id, target_milestone_id, component_id, tracker_id,  status_id)
307
        current_bug_id = bug_id
308
                sql = "INSERT INTO custom_values (customized_type, customized_id, custom_field_id, value)  VALUES (?, ?, ?, ?)"
309
                self.red_exec_sql(sql, 'Issue', bug_id, 1, url)
310
      else
311
        sql = "INSERT INTO journals (id, journalized_id, journalized_type, user_id, notes, created_on)  VALUES (?, ?, ?, ?, ?, ?)"
312
        self.red_exec_sql(sql, comment_id, bug_id, "Issue", who, thetext, bug_when)
313
      end
314
    end
315
  end
316

    
317
  def migrate_issue_relations
318
    self.red_exec_sql("delete from issue_relations")
319
    sql = "SELECT dependson, blocked FROM dependencies"
320
    self.bz_select_sql(sql) do |row|
321
      self.red_exec_sql("INSERT INTO issue_relations (issue_from_id, issue_to_id, relation_type) values (?, ?, ?)", row[0], row[1], "blocks")
322
    end
323
    sql = "SELECT dupe, dupe_of FROM duplicates"
324
    self.bz_select_sql(sql) do |row|
325
      self.red_exec_sql("INSERT INTO issue_relations (issue_from_id, issue_to_id, relation_type) values (?, ?, ?)", row[0], row[1], "duplicates")
326
    end
327
  end
328

    
329
  def migrate_attachments
330
    self.red_exec_sql("DELETE FROM attachments")
331
    sql = "SELECT attachments.attach_id, attachments.bug_id, attachments.filename, attachments.mimetype, attachments.submitter_id, attachments.creation_ts, attachments.description, attach_data.thedata FROM attachments, attach_data WHERE attachments.attach_id = attach_data.id"
332
    self.bz_select_sql(sql) do |row|
333
      (attach_id, bug_id, filename, mimetype, submitter_id, creation_ts, description, thedata ) = row
334
      disk_filename = self.get_disk_filename(attach_id, filename)
335
      filesize = thedata.size()
336
      sql = "INSERT INTO attachments (id, container_id, container_type, filename, filesize, disk_filename, content_type, digest, downloads, author_id, created_on, description) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
337
      self.red_exec_sql(sql, attach_id, bug_id, 'Issue', filename, filesize, disk_filename, mimetype, '', 0, submitter_id, creation_ts, description)
338
      File.open("#{ATTACHMENT_PATH}/#{disk_filename}", "wb") do |f|
339
        f.write(thedata)
340
      end
341
    end
342
  end
343
  
344
  def get_disk_filename(attach_id, filename)
345
    return "a#{attach_id}.#{self.get_file_extension(filename)}".downcase
346
  end
347
    
348
  def get_file_extension(s)
349
    m = /\.(\w+)$/.match(s)
350
    if(m)
351
      return m[1]
352
    else
353
      return 'dat'
354
    end
355
  end
356

    
357
  def migrate_members
358
    self.log("*** migrate members ***")
359
    self.bz_select_sql("SELECT DISTINCT user_group_map.user_id, group_control_map.product_id AS project_id FROM group_control_map, user_group_map WHERE group_control_map.group_id = user_group_map.group_id") do |row|
360
      user_id = row[0]
361
      product_id = row[1]
362
      role_id = DEFAULT_ROLE_ID
363
      created_on = "2007-01-01 12:00:00"
364
      mail_notification = 0
365
      self.red_exec_sql("INSERT INTO members (user_id, project_id, created_on, mail_notification) VALUES (?,?,?,?)", user_id, product_id, created_on, mail_notification)
366
    end
367
  end
368

    
369
  def migrate_member_roles
370
    self.log("*** migrate member roles ***")
371
    self.bz_select_sql("SELECT DISTINCT groups.name, group_control_map.product_id AS project_id FROM group_control_map, groups WHERE groups.id = group_control_map.group_id") do |row|
372
      group_name = row[0]
373
      product_id = row[1]
374
      role_id = DEFAULT_ROLE_ID
375
      created_on = "2007-01-01 12:00:00"
376
      mail_notification = 0
377
      self.red_exec_sql("INSERT INTO members (user_id, project_id, created_on, mail_notification) select (select id from users where lastname = ?),?,?,?", group_name, product_id, created_on, mail_notification)
378
          self.red_exec_sql("INSERT INTO member_roles (member_id, role_id, inherited_from) select (select members.id from members, users where members.user_id = users.id and users.lastname = ?),?,?", group_name, role_id, 0)
379
          self.red_exec_sql("INSERT INTO member_roles (member_id, role_id, inherited_from) select members.id, ?, (select members.id from members, users where members.user_id = users.id and users.lastname = ?) FROM members,users where members.project_id = ? and members.user_id = users.id and users.type = ?", role_id, group_name, product_id, 'User')
380
    end
381
  end
382

    
383
  def migrate_groups_users
384
    self.log("*** migrate groups users ***")
385
    self.red_select_sql("select (select members.user_id from members where members.id = mr.inherited_from) as group_id, m.user_id FROM member_roles as mr, members as m where mr.inherited_from is not null and mr.inherited_from <> 0 and mr.member_id = m.id") do |row|
386
      group_id = row[0]
387
      user_id = row[1]
388
      self.red_exec_sql("INSERT INTO groups_users (group_id, user_id) values (?, ?)", group_id, user_id)
389
    end
390
  end
391
  
392
  def insert_project_trackers(project_id)
393
    [1,2,3].each do |tracker_id|
394
      self.red_exec_sql("INSERT INTO projects_trackers (project_id, tracker_id) VALUES (?, ?)", project_id, tracker_id)
395
    end
396
  end
397

    
398
  def insert_project_modules(project_id)
399
    ['issue_tracking',
400
      'time_tracking',
401
      'news',
402
      'documents',
403
      'files',
404
      'wiki',
405
      'repository',
406
      'boards',].each do |m|
407
      self.red_exec_sql("INSERT INTO enabled_modules (project_id, name) VALUES (?, ?)", project_id, m)
408
    end
409
  end
410

    
411
  def bz_exec_sql(sql)
412
    self.log("bugzilla: #{sql}")
413
  end
414

    
415
  def bz_select_sql(sql, *args, &block)
416
    self.log("bugzilla: #{sql} args=#{args.join(',')}")
417
    statement = @bugzilladb.prepare(sql)
418
    statement.execute(*args)
419
    while row = statement.fetch do
420
      yield row
421
    end
422
    statement.close()
423
  end
424

    
425
  def red_exec_sql(sql, *args)
426
    self.log("redmine: #{sql} args=#{args.join(',')}")
427
    statement = @redminedb.prepare(sql)
428
    statement.execute(*args)
429
    statement.close()
430
  end
431

    
432
  def red_select_sql(sql, *args, &block)
433
    self.log("redmine: #{sql} args=#{args.join(',')}")
434
    statement = @redminedb.prepare(sql)
435
    statement.execute(*args)
436
    while row = statement.fetch do
437
      yield row
438
    end
439
    statement.close()
440
  end
441
end
442

    
443
begin
444
  bzred = BugzillaToRedmine.new
445
  bzred.migrate
446
rescue => e
447
  puts e.inspect
448
  puts e.backtrace
449
end