| 1 | """
|
|---|
| 2 | Import tickets into a Trac database from a correctly formatted comma delimited file.
|
|---|
| 3 |
|
|---|
| 4 | Requires: Trac 0.9 or greater from http://trac.edgewall.com/
|
|---|
| 5 | Python 2.3 from http://www.python.org/
|
|---|
| 6 |
|
|---|
| 7 | Author: Felix Collins felix at keyghost.com
|
|---|
| 8 | Copyright: Felix Collins 2005 - 2006
|
|---|
| 9 |
|
|---|
| 10 | Acknowledgement : This script borrows heavily from sourceforge2trac.py
|
|---|
| 11 |
|
|---|
| 12 | Each 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 |
|
|---|
| 31 | Any of the fields may be left blank except for type, status, severity and priority.
|
|---|
| 32 | By default a blank field will be left blank in the ticket
|
|---|
| 33 | If the summary field is blank then no ticket will be entered
|
|---|
| 34 | If the time field is blank then the current time will be used
|
|---|
| 35 |
|
|---|
| 36 | The component, version and milestone fields can use any value.
|
|---|
| 37 | These values will be entered into the component, version and milestone tables respectively.
|
|---|
| 38 |
|
|---|
| 39 | type must be one of: task, defect, enhancement
|
|---|
| 40 | status must be one of: new, assigned, reopened, closed
|
|---|
| 41 | severity must be one of: blocker, critical, major, normal, minor, trivial, enhancement
|
|---|
| 42 | priority must be one of: highest, high, normal, low, lowest
|
|---|
| 43 |
|
|---|
| 44 | In addition to the above entries, custom fields are supported.
|
|---|
| 45 | The name of the custom field should appear in the first row.
|
|---|
| 46 | To enable processing of the custom fields by Trac you will need to put an entry in your trac.ini file
|
|---|
| 47 | The following creates an entry for a field called "estimate".
|
|---|
| 48 | "estimate = text" defines estimate as a single line text entry. See the Trac documentation
|
|---|
| 49 | on custom fields for more help.
|
|---|
| 50 |
|
|---|
| 51 | [ticket-custom]
|
|---|
| 52 | estimate = text
|
|---|
| 53 | estimate.label = First Estimate (hours)
|
|---|
| 54 |
|
|---|
| 55 | Issues
|
|---|
| 56 | This script does not do any checking of character formats. It is therefore
|
|---|
| 57 | quite easy to get illegal characters into the database which will prevent Trac
|
|---|
| 58 | from displaying the ticket. To avoid this stick to alphanumerics if possible.
|
|---|
| 59 | Fixing problems of this nature requires manually removing the tickets from the
|
|---|
| 60 | database with the sqlite3 tool.
|
|---|
| 61 |
|
|---|
| 62 | """
|
|---|
| 63 |
|
|---|
| 64 | from datetime import datetime
|
|---|
| 65 | import trac.env
|
|---|
| 66 | import csv
|
|---|
| 67 | import time
|
|---|
| 68 | import sys
|
|---|
| 69 |
|
|---|
| 70 | #This function allow the script to be called from command line or programatically
|
|---|
| 71 | def Output(Content):
|
|---|
| 72 | if Output.CALLEDFROMCMD == True:
|
|---|
| 73 | print Content
|
|---|
| 74 | else:
|
|---|
| 75 | Output.outputstring = Output.outputstring + Content + "<br>"
|
|---|
| 76 | Output.outputstring = ""
|
|---|
| 77 | Output.CALLEDFROMCMD = False
|
|---|
| 78 |
|
|---|
| 79 | class 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 |
|
|---|
| 156 | class 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 |
|
|---|
| 282 | def 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 |
|
|---|
| 295 | def 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 |
|
|---|
| 353 | if __name__ == '__main__':
|
|---|
| 354 | main()
|
|---|
