Edgewall Software

TracSynchronize: csv2trac.2.py

File csv2trac.2.py, 13.3 KB (added by Felix Collins, 11 years ago)

Added more comments to file

Line 
1"""
2Import tickets into a Trac database from a correctly formatted comma delimited file.
3
4Requires: Trac 0.9 or greater from http://trac.edgewall.com/
5 Python 2.3 from http://www.python.org/
6
7Author: Felix Collins felix at keyghost.com
8Copyright: Felix Collins 2005 - 2006
9
10Acknowledgement : This script borrows heavily from sourceforge2trac.py
11
12Each line in the CSV file needs to have the following entries
13 type text -- the ticket purpose
14 time integer -- the time it was created
15 changetime integer
16 component text
17 severity text
18 priority text
19 owner text -- who is this ticket assigned to
20 reporter text
21 cc text -- email addresses to notify
22 url text -- url related to this ticket
23 version text --
24 milestone text --
25 status text
26 resolution text
27 summary text -- one-line summary
28 description text -- problem description (long)
29 keywords text
30
31Any of the fields may be left blank except for type, status, severity and priority.
32By default a blank field will be left blank in the ticket
33If the summary field is blank then no ticket will be entered
34If the time field is blank then the current time will be used
35
36The component, version and milestone fields can use any value.
37These values will be entered into the component, version and milestone tables respectively.
38
39type must be one of: task, defect, enhancement
40status must be one of: new, assigned, reopened, closed
41severity must be one of: blocker, critical, major, normal, minor, trivial, enhancement
42priority must be one of: highest, high, normal, low, lowest
43
44In addition to the above entries, custom fields are supported.
45The name of the custom field should appear in the first row.
46To enable processing of the custom fields by Trac you will need to put an entry in your trac.ini file
47The following creates an entry for a field called "estimate".
48"estimate = text" defines estimate as a single line text entry. See the Trac documentation
49on custom fields for more help.
50
51[ticket-custom]
52estimate = text
53estimate.label = First Estimate (hours)
54
55Issues
56This script does not do any checking of character formats. It is therefore
57quite easy to get illegal characters into the database which will prevent Trac
58from displaying the ticket. To avoid this stick to alphanumerics if possible.
59Fixing problems of this nature requires manually removing the tickets from the
60database with the sqlite3 tool.
61
62"""
63
64from datetime import datetime
65import trac.env
66import csv
67import time
68import sys
69
70#This function allow the script to be called from command line or programatically
71def Output(Content):
72 if Output.CALLEDFROMCMD == True:
73 print Content
74 else:
75 Output.outputstring = Output.outputstring + Content + "<br>"
76Output.outputstring = ""
77Output.CALLEDFROMCMD = False
78
79class ImportedCSVData(object):
80 def __init__(self, filename):
81
82 #This code reads the file and just gets the field names
83 #This is done to support python 2.3
84 self.fileobject = open(filename, "rb")
85 read = csv.reader(self.fileobject)
86 fieldnames = read.next()
87
88 #must rewind the file object after each use to a known location
89 self.fileobject.seek(0)
90
91 #work out what the custom fields are
92 standardfields = ["type", "time", "changetime", "component", "severity", "priority", "owner", "reporter",
93 "cc", "url", "version", "milestone", "status", "resolution", "summary",
94 "description", "keywords"]
95 self.customfields = []
96 readstandardfields = []
97 missingfields = []
98 for field in fieldnames:
99 if field not in standardfields:
100 self.customfields.append(field)
101 else:
102 readstandardfields.append(field)
103
104 if readstandardfields != standardfields:
105 for field in standardfields:
106 if field not in fieldnames:
107 missingfields.append(field)
108 Output("Warning: missing fields in the csv file. Missing: %s" %missingfields)
109
110 self.reader = csv.DictReader(self.fileobject, fieldnames + missingfields, restval="")
111
112 #returns the DictReader with the tickets in it
113 #remember that the reader can only be iterated through once without
114 #rewinding the file pointer (ImportedCSVData.reader.seek(0))
115 def getnewtickets(self):
116 return(self.reader)
117
118 #returns a list of the custom fields defined in the csv file
119 def getcustomfields(self):
120 return(self.customfields)
121
122 #returns a list of the component types needed for the import
123 def getcomponents(self):
124 components = []
125 for row in self.reader:
126 if row["summary"] != "summary":
127 if row["component"] != "":
128 if row["component"] not in components:
129 components.append(row["component"])
130 self.fileobject.seek(0)
131 return(components)
132
133 #returns a list of the versions needed for the import
134 def getversions(self):
135 versions = []
136 for row in self.reader:
137 if row["summary"] != "summary":
138 if row["version"] != "":
139 if row["version"] not in versions:
140 versions.append(row["version"])
141 self.fileobject.seek(0)
142 return(versions)
143
144 #returns a list of the milestones needed for the import
145 def getmilestones(self):
146 milestones = []
147 for row in self.reader:
148 if row["summary"] != "summary":
149 if row["milestone"] != "":
150 if row["milestone"] not in milestones:
151 milestones.append(row["milestone"])
152 self.fileobject.seek(0)
153 return(milestones)
154
155
156class TracDatabase(object):
157 def __init__(self, path):
158 self.env = trac.env.Environment(path)
159 self._db = self.env.get_db_cnx()
160 self._db.autocommit = False
161
162 def db(self):
163 return self._db
164
165 #checks whether there are any existing tickets returns a boolean
166 def hasTickets(self):
167 c = self.db().cursor()
168 c.execute('''SELECT count(*) FROM Ticket''')
169 return int(c.fetchall()[0][0]) > 0
170
171 def setComponentList(self, componentlist):
172 """Remove all components, make new components from the entries in componentlist"""
173 c = self.db().cursor()
174 if self.hasTickets():
175 #if we have tickets then we are importing into a working db
176 #so get the existing components and only create new ones that don't already exist
177 c.execute('SELECT (name) FROM component')
178 rows = c.fetchall()
179 if rows:
180 for r in rows:
181 if r[0] in componentlist:
182 componentlist.remove(r[0])
183 Output("Existing component detected: %s\n" %r[0])
184
185 else: # no existing tickets therefore start from scratch
186 c.execute("""DELETE FROM component""")
187
188 for value in componentlist:
189 c.execute("""INSERT INTO component (name) VALUES ("%s")""" % value)
190 Output("New component: %s" % value)
191 self.db().commit()
192
193 def setVersionList(self, versionlist):
194 """Remove all versions, make new versions from the entries in versionlist"""
195 c = self.db().cursor()
196 if self.hasTickets():
197 #if we have tickets then we are importing into a working db
198 #so get the existing versions and only create new ones that don't already exist
199 c.execute('SELECT (name) FROM version')
200 rows = c.fetchall()
201 if rows:
202 for r in rows:
203 if r[0] in versionlist:
204 versionlist.remove(r[0])
205 Output("Existing version detected: %s\n" %r[0])
206
207 else: # no existing tickets therefore start from scratch
208 c.execute("""DELETE FROM version""")
209
210
211 for value in versionlist:
212 c.execute("""INSERT INTO version (name) VALUES ("%s")""" % value)
213 Output("New version: %s" % value)
214 self.db().commit()
215
216 def setMilestoneList(self, milestonelist):
217 """Remove all milestones, make new milestones from the entries in milestonelist"""
218 c = self.db().cursor()
219
220 if self.hasTickets():
221 #if we have tickets then we are importing into a working db
222 #so get the existing milestones and only create new ones that don't already exist
223 c.execute('SELECT (name) FROM milestone')
224 rows = c.fetchall()
225 if rows:
226 for r in rows:
227 if r[0] in milestonelist:
228 milestonelist.remove(r[0])
229 Output("Existing milestone detected: %s\n" %r[0])
230
231 else: # no existing tickets therefore start from scratch
232 c.execute("""DELETE FROM milestone""")
233
234
235 for value in milestonelist:
236 c.execute("""INSERT INTO milestone (name) VALUES ("%s")""" % value)
237 Output("New milestone: %s" % value)
238 self.db().commit()
239
240 def addTicket(self, ttype, time, changetime, component,
241 severity, priority, owner, reporter, cc,
242 version, milestone, status, resolution,
243 summary, description, keywords):
244 c = self.db().cursor()
245 if status.lower() == 'open':
246 if owner != '':
247 status = 'assigned'
248 else:
249 status = 'new'
250
251 c.execute('SELECT summary FROM ticket WHERE summary="%s"' %summary)
252 rows = c.fetchall()
253 if rows:
254 Output("Existing ticket with matching summary exists - not overwriting %s" %summary)
255 return 0
256 else:
257
258 c.execute('INSERT INTO ticket (type, time, changetime, component,\
259 severity, priority, owner, reporter, cc,\
260 version, milestone, status, resolution,\
261 summary, description, keywords)\
262 VALUES ("%s", "%s", "%s", "%s", "%s",\
263 "%s", "%s", "%s", "%s", "%s",\
264 "%s", "%s", "%s", "%s", "%s",\
265 "%s")' %(
266 ttype, time, changetime, component,
267 severity, priority, owner, reporter,
268 cc, version, milestone, status.lower(),
269 resolution, summary, description, keywords))
270 self.db().commit()
271 return self.db().get_last_id(c, 'ticket')
272
273
274 def addTicketCustom(self, ticket, name, value):
275 c = self.db().cursor()
276 c.execute("""INSERT INTO ticket_custom (ticket, name, value)
277 VALUES ("%s", "%s", "%s")""" %(
278 ticket, name, value))
279 self.db().commit()
280
281
282def main():
283 import optparse
284 Output.CALLEDFROMCMD = True
285 p = optparse.OptionParser('usage: %prog newtickets.csv /path/to/trac/environment')
286 opt, args = p.parse_args()
287 if len(args) != 2:
288 p.error("Incorrect number of arguments")
289
290 try:
291 importCSV(args[0], args[1])
292 except Exception, e:
293 Output('Error:%s' %e)
294
295def importCSV(f, env):
296
297 project = ImportedCSVData(f)
298
299 db = TracDatabase(env)
300 db.setComponentList(project.getcomponents())
301 Output("Imported components")
302 db.setVersionList(project.getversions())
303 Output("Imported versions")
304 db.setMilestoneList(project.getmilestones())
305 Output("Imported milestones")
306
307 tickets = project.getnewtickets()
308
309 newid = 1
310 linecount = 0
311 for t in tickets:
312 if t["summary"] != "summary":
313 #Output("processing ticket %s" % t["summary"])
314 linecount += 1
315
316 #put current time in rows with no time
317 tickettime = t["time"]
318 if tickettime == "":
319 tickettime = int(time.time())
320
321 #Don't import tickets with no summary
322 if t["summary"] != "":
323
324 newid = db.addTicket(ttype=t["type"],
325 time=tickettime,
326 changetime=tickettime,
327 component=t["component"],
328 severity=t["severity"],
329 priority=t["priority"],
330 owner=t["owner"],
331 reporter=t["reporter"],
332 cc=t["cc"],
333 version=t["version"],
334 milestone=t["milestone"],
335 status=t["status"],
336 resolution=t["resolution"],
337 summary=t["summary"],
338 description=t["description"],
339 keywords=t["keywords"])
340
341 if newid != 0:
342 Output('Imported "%s" as ticket #%d' % (t["summary"], newid))
343 #do the custom fields
344 customfields = project.getcustomfields()
345 for field in customfields:
346 if t[field] != "":
347 db.addTicketCustom(newid, field, t[field])
348 else:
349 Output("Warning: Ignored ticket with no summary at line %s" %linecount)
350 Output('Finished importing')
351 return Output.outputstring
352
353if __name__ == '__main__':
354 main()