Project

General

Profile

Paste
Download (7.46 KB) Statistics
| Branch: | Revision:

root / other-scripts / migrate-issues-to-redmine / fix-db.rb @ 63ffb014

1
#!/usr/bin/ruby -w
2
#encoding: UTF-8
3

    
4
require "dbi"
5
require "uri"
6

    
7
###### Definition of functions
8
def custom_chomp(string)
9
  # Chomp line completely
10
  previous_string = ''
11
  while string != previous_string
12
    previous_string = string.dup # = makes a reference to the original string
13
    string.chomp!
14
  end
15
  return string
16
end
17

    
18

    
19
def fix_issues_comment_link(name, cid2iid, cid2post_nb)
20
  # Fix links like : task-name#comment-789
21
  scan_regex = /.*#comment-(\d+)/
22
  cid = name.scan(scan_regex).flatten[0]
23
  post_nb = custom_chomp(cid2post_nb[cid])
24
  return "##{cid2iid[cid]}#note-#{post_nb}"
25
end
26

    
27

    
28
def fix_issues_link(text, name2iid, cid2iid, cid2post_nb)
29
  # Find all links in text
30
  scan_regex =  /:http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content(?:\/t(?:%C3%A2|â)che)?|node)\/([^ \n.,\/)]*)/
31

    
32
  # Non flattened output is [['some'], ['text']]
33
  # flattened output is ['some', 'text']
34
  names = text.scan(scan_regex).flatten
35

    
36
  if not names.empty?
37
    names.each do |name|
38
      name = custom_chomp(name)
39
      if name.include? 'é' or name.include? 'è' or name.include? 'ê' or name.include? 'à' or name.include? 'ô' or name.include? 'â' or name.include? 'ù' or name.include? 'û'
40
        name = URI.escape(name)
41
      end
42
      unescaped_name = URI.unescape(name)
43
      gsub_regex = /"http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content(?:\/t(?:%C3%A2|â)che)?|node)\/[^ ]*":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content(?:\/t(?:%C3%A2|â)che)?|node)\/#{name}/
44
      # Some link may have a description between "…"
45
      if (text =~ gsub_regex)
46
        # Some comments are like this : task-name#comment-nb.
47
        # We fix them here
48
        if name.include? '#comment-'
49
          remplace_regex = fix_issues_comment_link(name, cid2iid, cid2post_nb)
50
        else
51
          remplace_regex = "#{unescaped_name} : ##{name2iid[name]}"
52
        end
53
        text.gsub!(gsub_regex, remplace_regex)
54
      else
55
        scan_regex = /"([^"]*)":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content(?:\/t(?:%C3%A2|â)che)?|node)\/(?:[^ \n)]*)/
56
        description = text.scan(scan_regex).flatten[0]
57
        gsub_regex = /"#{description}":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)(?:content(?:\/t(?:%C3%A2|â)che)?|node)\/#{name}/
58
        if name.include? '#comment-'
59
          remplace_regex = fix_issues_comment_link(name, cid2iid, cid2post_nb)
60
          remplace_regex = "#{description} (" + remplace_regex + ')'
61
        else
62
          remplace_regex = "#{description} (##{name2iid[name]})"
63
        end
64
        text.gsub!(gsub_regex, remplace_regex)
65
      end
66
    end
67
  end
68
  return text
69
end
70

    
71

    
72
def fix_comments_link(text, cid2iid, cid2post_nb)
73
  scan_regex = /:http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/|\/portail\/)comment\/(\d+)/
74

    
75
  cids = text.scan(scan_regex).flatten
76

    
77
  if not cids.empty?
78
    cids.each do |cid|
79
      cid = custom_chomp(cid)
80
      gsub_regex = /"http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)comment\/.*":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)comment\/#{cid}#comment-\d+/
81
      post_nb = custom_chomp(cid2post_nb[cid])
82
      # Some link may have a description between "…"
83
      if (text =~ gsub_regex)
84
        remplace_regex = "##{cid2iid[cid]}#note-#{post_nb}"
85
        text.gsub!(gsub_regex, remplace_regex)
86
      else
87
        scan_regex = /"([^"]*)":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/|\/portail\/)comment\/(?:\d+)/
88
        description = text.scan(scan_regex).flatten[0]
89
        gsub_regex = /"#{description}":http:\/\/assos\.centrale-marseille\.fr(?:\/|\/lessive\/)comment\/#{cid}#comment-\d+/
90
        remplace_regex = "#{description} (##{cid2iid[cid]}#note-#{post_nb})"
91
        text.gsub!(gsub_regex, remplace_regex)
92
      end
93
    end
94
  end
95
  return text
96
end
97

    
98

    
99

    
100
###### DATABASE
101
begin
102
  ##### Create DB connection
103
  db = 'redmine_default'
104
  dbuser = 'root'
105
  dbpwd = 'tata'
106
  host = 'localhost'
107
  dbh = DBI.connect("DBI:Mysql:#{db}:#{host}", dbuser, dbpwd)
108
  dbh.do("SET NAMES 'UTF8'")
109

    
110
  ###### Initialisation of hashes needed to fix URL
111
  name2iid = Hash.new
112
  cid2iid = Hash.new
113
  cid2post_nb = Hash.new
114

    
115
  fix_url_issues_csv = File.open("fix_url_issues.csv", "r")
116
  fix_url_issues_csv.each do |line|
117
    line = line.chomp
118
    nid, name, iid = line.split(',')
119
    name2iid[name] = iid
120
    name2iid[nid] = iid
121
  end
122
  fix_url_issues_csv.close
123

    
124
  fix_url_comments_csv = File.open("fix_url_comments.csv", "r")
125
  fix_url_comments_csv.each do |line|
126
    cid, iid, post_nb = line.split(',')
127
    cid2iid[cid] = iid
128
    cid2post_nb[cid] = post_nb
129
  end
130
  fix_url_comments_csv.close
131

    
132

    
133

    
134
  ###### Update issues
135
  sql_update_issue = "UPDATE issues SET author_id = ?, created_on = ?, is_private = 1 WHERE  id = ?"
136
  sql_fix_links_issue = "UPDATE issues SET description = ? WHERE id = ?"
137
  select_description = "SELECT description FROM issues WHERE id = ?"
138

    
139
  req_update_issue = dbh.prepare(sql_update_issue)
140
  req_fix_links_issue = dbh.prepare(sql_fix_links_issue)
141
  req_select_description = dbh.prepare(select_description)
142

    
143
  # Reading file
144
  issues = File.open('issues.csv', 'r')
145
  issues.each do |line|
146
    # Update author and date of creation
147
    line = line.chomp
148
    iid, author_id, created_on, nid, name = line.split(',')
149
    req_update_issue.execute(author_id, created_on, iid)
150

    
151
    ## Fix links
152
    # issues
153
    req_select_description.execute(iid)
154

    
155
    description = req_select_description.fetch[0]
156
    description = fix_issues_link(description, name2iid, cid2iid, cid2post_nb)
157

    
158
    # Comments
159
    description = fix_comments_link(description, cid2iid, cid2post_nb)
160

    
161
    # We update
162
    req_fix_links_issue.execute(description, iid)
163
  end
164

    
165
  req_select_description.finish
166
  req_update_issue.finish
167
  req_fix_links_issue.finish
168

    
169

    
170

    
171
  ###### Update journals
172
  sql_get_ids = "SELECT id FROM journals WHERE journalized_id = ? ORDER BY id ASC"
173
  sql_update_journals = "UPDATE journals SET created_on = ?, user_id = ? WHERE id = ?"
174
  sql_updated_on = "UPDATE issues SET  updated_on = ? WHERE id = ?"
175
  sql_fix_links_comment = "UPDATE journals SET notes = ? WHERE id = ?"
176

    
177
  req_get_ids = dbh.prepare(sql_get_ids)
178
  req_update_journals = dbh.prepare(sql_update_journals)
179
  req_updated_on = dbh.prepare(sql_updated_on)
180
  req_fix_links_comment = dbh.prepare(sql_fix_links_comment)
181

    
182
  # Reading file
183
  comments = File.open('comments.csv', 'r')
184

    
185
  # We must remember current iid in order to fetch result or execute query: req_get_id return
186
  # more than one value
187
  current_iid = -1
188
  select_notes = "SELECT notes FROM journals WHERE id = ?"
189
  req_select_notes = dbh.prepare(select_notes)
190

    
191
  comments.each do |line|
192
    line = line.chomp
193
    iid, user_id, created_on = line.split(',')
194

    
195
    # We get the id of the comment
196
    if iid != current_iid
197
      current_iid = iid
198
      req_get_ids.execute(iid)
199
    end
200

    
201
    id = req_get_ids.fetch[0]
202

    
203
    # We do the update
204
    req_update_journals.execute(created_on, user_id, id)
205
    req_updated_on.execute(created_on, iid)
206

    
207
    ## Fix links
208
    # issues
209
    req_select_notes.execute(id)
210
    notes = req_select_notes.fetch[0]
211
    notes = fix_issues_link(notes, name2iid, cid2iid, cid2post_nb)
212

    
213
    # Comments
214
    notes = fix_comments_link(notes, cid2iid, cid2post_nb)
215

    
216
    req_fix_links_comment.execute(notes, id)
217
  end
218
  comments.close
219

    
220
  req_select_notes.finish
221
  req_get_ids.finish
222
  req_update_journals.finish
223
  req_updated_on.finish
224
rescue DBI::DatabaseError => e
225
     puts "An error occurred"
226
     puts "Error code:    #{e.err}"
227
     puts "Error message: #{e.errstr}"
228
ensure
229
     # disconnect from server
230
     dbh.disconnect if dbh
231
end