Importing to Redmine with SQLAlchemy

#!/usr/bin/env python
# -*- coding: utf-8 -*-

# Redmine database URL
DB = "postgres://USERNAME:PASSWORD@localhost:5432/DATABASE"

# Source (directory with .be subdir)
SOURCE_DIR = '/home/marcin/mercurial_repos/watchbot'

# Destination Redmine project (a name)
BUG_PROJECT = 'WatchBot'
# Owner of all imported issues (login name)
BUG_OWNER = 'marcin'
# Redmine tracker type (Task, Error, ...)
BUG_TRACKER = 'Task'
# Status given to non-resolved issues
BUG_NEW_STATUS = 'New'
# Status given to fixed issues
BUG_FIXED_STATUS = u'Fixed'

############################################################

from sqlalchemy import create_engine, MetaData
from sqlalchemy import Column, Table, \
     DateTime, UnicodeText, Integer, String, Unicode
from sqlalchemy.sql import func
from sqlalchemy.orm import mapper, sessionmaker

############################################################
# Reflect the Redmine database
############################################################

engine = create_engine(DB, echo=True)
metadata = MetaData()

# Load the tables definitions. I force some columns
# to be Unicode instead of String to avoid problems
# with my Polish characters ("Can't decode...")

issues_table = Table("issues", metadata, 
                     Column('subject', Unicode()),
                     Column('description', Unicode()),
                     autoload = True, autoload_with = engine)
comments_table = Table("comments", metadata, 
                       Column('comments', Unicode()),
                       autoload = True, autoload_with = engine)
journals_table = Table("journals", metadata, 
                       Column('notes', Unicode()),
                       autoload = True, autoload_with = engine)
projects_table = Table("projects", metadata,
                       autoload = True, autoload_with = engine)
users_table = Table("users", metadata,
                    autoload = True, autoload_with = engine)
trackers_table = Table("trackers", metadata,
                       autoload = True, autoload_with = engine)
issue_statuses_table = Table("issue_statuses", metadata,
                       autoload = True, autoload_with = engine)

# ORM mapper classes and mappings

class Issue(object):
    pass
class Comment(object):
    pass
class Journal(object):
    pass
class Project(object):
    pass
class User(object):
    pass
class Tracker(object):
    pass
class IssueStatus(object):
    pass

mapper(Issue, issues_table)
mapper(Comment, comments_table)
mapper(Journal, journals_table)
mapper(Project, projects_table)
mapper(User, users_table)
mapper(Tracker, trackers_table)
mapper(IssueStatus, issue_statuses_table)

############################################################

# Connect to the database

Session = sessionmaker(autoflush = True, transactional = True)
Session.configure(bind = engine)

session = Session()


############################################################
# Redmine-specific part (creating issues)
############################################################

# Find numeric identifiers for the configuration variables
# specified on the very beginning.

my_user = session.query(User).filter(User.login == BUG_OWNER).one()
my_project = session.query(Project).filter(Project.name == BUG_PROJECT).one()
my_tracker = session.query(Tracker).filter(Tracker.name == BUG_TRACKER).one()
my_open_status = session.query(IssueStatus).filter(IssueStatus.name == BUG_NEW_STATUS).one()
my_fixed_status = session.query(IssueStatus).filter(IssueStatus.name == BUG_FIXED_STATUS).one()

#print "%s %s %s" % (my_user.id, my_user.login, my_user.firstname)
#print "%s %s %s" % (my_project.id, my_project.name, my_project.description)
#print "%s %s" % (my_tracker.id, my_tracker.name)
#print "%s %s" % (my_status.id, my_status.name)

def create_issue(subject, description, created_on, is_fixed):
    issue = Issue()

    issue.subject = subject
    issue.description = description
    issue.assigned_to_id = my_user.id

    issue.tracker_id = my_tracker.id
    issue.project_id = my_project.id
    if is_fixed:
        issue.status_id = my_fixed_status.id
    else:
        issue.status_id = my_open_status.id
    issue.priority_id = 1   ### Too lazy here
    issue.author_id = my_user.id
    issue.lock_version = 0  ### Lazy too
    issue.done_ratio = 0

    #issue.category_id = ...let's categorize after import...
    issue.created_on = created_on
    issue.updated_on = datetime.datetime.now()

    session.save(issue)

############################################################
# Be specific part (reading issues)
############################################################

# Note: I manually read Be database structures. I initially
# tried popen-ing `be list` and such, but faced some be
# bug due to which it crashed on non-ascii characters
# when piped or even redirected to a file.

import dircache
import os.path
import codecs
import re
import datetime

def be_issues():
    """
    Generator, yields issue_identifiers
    """
    be_dir = os.path.join(SOURCE_DIR, '.be', 'bugs')
    data = dircache.listdir(be_dir)
    for d in data:
        yield os.path.join(be_dir, d)

re_val = re.compile("^(\w+)=(.*)$")
def be_parse_values_file(filename):
    f = codecs.open(filename, "r", "utf-8")
    d = {}
    for l in f:
        m = re_val.match(l)
        if m:
            name = m.group(1).lower()
            value = m.group(2)
            if name == 'time' or name == 'date':
                value = datetime.datetime.strptime(value, "%a, %d %b %Y %H:%M:%S +0000")
            d[ name ] = value
    return d

def be_issue_detail(issue_id):
    """
    Returns issue details - dictionary with 'creator', 'severity', 'status',
    'summary' and 'time' fields
    """
    return be_parse_values_file(os.path.join(issue_id, 'values'))

def be_issue_comments(issue_id):
    """
    Yields all comments on issue. Each comment is dict 'date' and 'body'.
    Comments are sorted by date.
    """
    comments = []
    comment_dir = os.path.join(issue_id, 'comments')
    if not os.path.isdir(comment_dir):
        return []
    for cdir in dircache.listdir(comment_dir):
        here_dir = os.path.join(comment_dir, cdir)
        body = "".join(codecs.open(os.path.join(here_dir, "body"), "r", "utf-8").readlines())
        val = be_parse_values_file(os.path.join(here_dir, "values"))
        comments.append(dict(date = val['date'], body = body, id = cdir))
    comments.sort(key = lambda x: (x['date'], x['id']))
    return comments

# Here is part of initial popen attempt:
#
#    from subprocess import Popen, PIPE
#
#    os.chdir(SOURCE_DIR)
#    data = Popen(
#        ["be", "list"],
#        stdout = PIPE,
#        env = os.environ,
#        ).communicate()[0]
#    for x in data.split("\n"):
#        # ....

############################################################
# Main loop
############################################################

for issue_id in be_issues():


    print "--- %s ---" % issue_id
    detail = be_issue_detail(issue_id)
    comments = be_issue_comments(issue_id)

    if comments:
        desc = "\n\n".join([ c['body'] for c in comments ])
    else:
        desc = '.'

    create_issue(subject = detail['summary'], 
                 description = desc, 
                 created_on = detail['time'], 
                 is_fixed = (detail['status'] == 'closed'))

session.flush()
#session.rollback()
session.commit()