Ticket #5874: webpositive-export.py

# Export existing tickets from the database to XML

# Constants

TRAC_ENV = "/srv/trac/dev.haiku-os.org"
OUTPUT= "webpositive-export/"

#
# 1. set up the XML Elementtree thingie
#

from xml.etree.ElementTree import Element, SubElement

rootelement = Element("Tickets")

#
# 2. Open the Trac Env
#

from trac.env import Environment
env = Environment(TRAC_ENV)
env.verify()

#
# 3. Get open tickets
#

db = env.get_db_cnx()
ticketcursor = db.cursor()
ticketcursor.execute ("SELECT id,time,component,owner,reporter,summary,description FROM ticket WHERE status='accepted' OR status='assigned' OR status='new' OR status='reopened' ORDER BY id LIMIT 20")

#
# 4. Export the ticket details, and the comments to XML
#

attachmentslist = [] # for all attachments that need to be copied

for id, time, component, owner, reporter, summary, description in ticketcursor:
    ticketelement = SubElement(rootelement, "Ticket")
    e = SubElement(ticketelement, "id")
    e.text = str(id)
    e = SubElement(ticketelement, "time")
    e.text = str(time)
    e = SubElement(ticketelement, "component")
    e.text = component
    e = SubElement(ticketelement, "owner")
    e.text = owner
    e = SubElement(ticketelement, "reporter")
    e.text = reporter
    e = SubElement(ticketelement, "summary")
    e.text = summary
    e = SubElement(ticketelement, "description")
    e.text = description

    # Retrieve comments
    commentscursor = db.cursor()
    commentscursor.execute("SELECT time,author,oldvalue,newvalue FROM ticket_change WHERE ticket=%s AND field='comment' ORDER BY oldvalue", (id,))

    commentselement = SubElement(ticketelement, "Comments")
    for time, author, comment_num, comment in commentscursor:
        e = SubElement(commentselement, "Comment")
        e.set("time", str(time))
        e.set("author", author)
        e.set("num", comment_num)
        e.text = comment

    # Retrieve attachments
    attachmentscursor = db.cursor()
    attachmentscursor.execute("SELECT filename,time,description,author,size FROM attachment WHERE id=%s", (str(id),))

    attachmentselement = SubElement(ticketelement, "Attachments")
    for filename, time, description, author, size in attachmentscursor:
        e = SubElement(attachmentselement, "Attachment")
        e.set("filename", filename)
        e.set("time", str(time))
        e.set("author", author)
        e.set("size", str(size))
        e.text = description
        path = TRAC_ENV + "/attachments/ticket/" + str(id) + "/" + filename
        attachmentslist.append(path)

#
# 5. Copy the attachments
#

import os, shutil
os.mkdir(OUTPUT)

for file in attachmentslist:
    shutil.copy(file,OUTPUT)

#
# 6. Dump the output
#

import xml.etree
f = open(OUTPUT + "webpositive-export.xml", "w")
f.write(xml.etree.ElementTree.tostring(rootelement))
f.close()