#!/usr/bin/python2.6

###
# Fix trac sqlite3 db for import into JIRA
# Author: Henry Hughes <hhughes@acunu.com>
###

# JIRA trac import script creates an account for every email
# address it comes across. this means we get lots of bogus accounts
# for email aliases (eg henry@ and hhughes@).

# requires users.json contains a map from email address in trac to
# JIRA users eg
# { "hhughes" : "hhughes", "henry@acunu.com": "hhughes", ...}

# script will fail if an email in the db is not in the users map

# also fix links which use the format [[link|name]] -- these are
# apparently in WikiCreole format
# (http://trac.edgewall.org/wiki/WikiCreole) and JIRA gets them wrong.
# Transform to [link name] instead

# this script will in all likelyhood break your trac db so please back
# it up! 

import sqlite3
import json
import re

f = open('users.json')
usermap = json.load(f)

# in table ticket clean all owner, reporter and cc fields
# in table ticket_change clean all oldvalue, newvalue where status is owner/cc

conn = sqlite3.connect('trac/db/trac.db')
c = conn.cursor()

# update table `ticket`

ticket_fields = ["owner", "reporter", "cc"]

for f in ticket_fields:
    rows = c.execute('select %s from ticket group by %s' % (f,f)).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        olduser = row[0]
        newuser = ', '.join(map(lambda u: usermap[u.strip()], olduser.split(',')))
        if olduser == newuser:
            continue

        sql = "update ticket set %s='%s' where %s='%s'" % (f, newuser, f, olduser)
        print sql
        c.execute(sql)

# update table ticket (authors)

author_fields = ["author"]

for f in author_fields:
    rows = c.execute('select %s from ticket_change group by %s' % (f,f)).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        olduser = row[0]
        newuser = ', '.join(map(lambda u: usermap[u.strip()], olduser.split(',')))
        if olduser == newuser:
            continue

        sql = "update ticket_change set %s='%s' where %s='%s'" % (f, newuser, f, olduser)
        print sql
        c.execute(sql)

# update table attachment (authors)

author_fields = ["author"]

for f in author_fields:
    rows = c.execute('select %s from attachment group by %s' % (f,f)).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        olduser = row[0]
        newuser = ', '.join(map(lambda u: usermap[u.strip()], olduser.split(',')))
        if olduser == newuser:
            continue

        sql = "update attachment set %s='%s' where %s='%s'" % (f, newuser, f, olduser)
        print sql
        c.execute(sql)

# update table permission (username)

author_fields = ["username"]

for f in author_fields:
    rows = c.execute('select %s from permission group by %s' % (f,f)).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        olduser = row[0]
        newuser = ', '.join(map(lambda u: usermap[u.strip()], olduser.split(',')))
        if olduser == newuser:
            continue

        sql = "update permission set %s='%s' where %s='%s'" % (f, newuser, f, olduser)
        print sql
        c.execute(sql)


# update table ticket (authors)

change_fields = ["oldvalue", "newvalue"]

for f in change_fields:
    rows = c.execute("select %s from ticket_change where field='owner' or field='cc' group by %s" % (f,f)).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        olduser = row[0]
        newuser = ', '.join(map(lambda u: usermap[u.strip()], olduser.split(',')))
        if olduser == newuser:
            continue

        sql = "update ticket_change set %s='%s' where %s='%s'" % (f, newuser, f, olduser)
        print sql
        c.execute(sql)

broken_link_re = re.compile('\[\[([^\|]+)\|([^\]]*)\]\]')
link_fields = ["oldvalue", "newvalue"]
for f in link_fields:
    rows = c.execute("select %s from ticket_change where field='comment' or field='description'" % f).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        if not broken_link_re.search(row[0]):
            continue

        oldstr = row[0]
        newstr = broken_link_re.sub('[\g<1> \g<2>]', oldstr)

        sql = "update ticket_change set %s=? where %s=?" % (f, f)
        print sql
        print oldstr
        print newstr
        c.execute(sql, (newstr, oldstr))

ticket_fields = ["description"]
for f in ticket_fields:
    rows = c.execute("select %s from ticket" % f).fetchall()
    for row in rows:
        if not len(row) or not row[0]:
            continue

        if not broken_link_re.search(row[0]):
            continue

        oldstr = row[0]
        newstr = broken_link_re.sub('[\g<1> \g<2>]', oldstr)

        sql = "update ticket set %s=? where %s=?" % (f, f)
        print sql
        print oldstr
        print newstr
        c.execute(sql, (newstr, oldstr))

conn.commit()
conn.close()