| 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() |
|---|
