Edgewall Software

TracSynchronize: csv2trac.2.py

File csv2trac.2.py, 13.3 KB (added by Felix Collins, 9 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()