# XML-database conversion

import re

from xml.dom import Node

import db_util

import xml_util

def name_of(node): return re.sub(r'^.*\.', r'', xml_util.name_of(node))

ptr_suffix = '_id'

def is_ptr(node_name): return node_name.endswith(ptr_suffix)

def ptr_type(node_name):

    assert is_ptr(node_name)

    return node_name[:-len(ptr_suffix)]

def ptr_target(node):

    assert is_ptr(name_of(node))

    return xml_util.first_elem(node)

def find_by_name(node, name):

    for parent in xml_util.NodeParentIter(node):

        if name_of(parent) == name: return parent

        else:

            for child in xml_util.NodeElemIter(parent):

                child_name = name_of(child)

                if is_ptr(child_name):

                    if ptr_type(child_name) == name: return ptr_target(child)

                elif child_name == name: return child

    return None

def get(db, node, create=False, store_ids=False, row_ct_ref=None, pkeys=None):

    # store_ids enables searching the tree for missing fields

    if pkeys == None: pkeys = {}

    def pkey(table):

        if table not in pkeys: pkeys[table] = db_util.pkey(db, table)

        return pkeys[table]

    def obj(node, parent_id=None):

        table = name_of(node)

        pkey_ = pkey(table)

        row = {}

        children = []

        # Divide children into fields and children with fkeys to parent

        for child in xml_util.NodeElemIter(node):

            child_name = name_of(child)

            if xml_util.is_text(child): row[child_name] = xml_util.value(child)

            elif is_ptr(child_name): row[child_name] = obj(ptr_target(child))

            else: children.append(child)

        try: del row[pkey_]

        except KeyError: pass

        # Add fkey to parent

        if parent_id != None: row[pkey(name_of(node.parentNode))] = parent_id

        # Insert node

        for try_num in range(2):

            try:

                id_ = db_util.get(db, table, row, pkey_, create, row_ct_ref)

                if store_ids: xml_util.set_id(node, id_)

                break

            except db_util.NullValueException, ex:

                if try_num > 0: raise # exception still raised after retry

                # Search for required column in ancestors and their children

                target = find_by_name(node, ptr_type(ex.col))

                if target == None: raise

                row[ex.col] = xml_util.get_id(target)

        # Insert children with fkeys to parent

        for child in children: obj(child, id_)

        return id_

    return obj(node)

def xml2db(db, node, row_ct_ref=None):

    for child in xml_util.NodeElemIter(node):

        if not xml_util.is_text(child): # not XML metadata

            get(db, child, True, True, row_ct_ref)

# Database access

import random

import re

import sys

import ex_util

def _add_cursor_info(ex, cur): ex_util.add_msg(ex, 'query: '+cur.query)

class NameException(Exception): pass

class DbException(ex_util.ExceptionWithCause):

    def __init__(self, msg, cause=None, cur=None):

        ex_util.ExceptionWithCause.__init__(self, msg, cause)

        if cur != None: _add_cursor_info(self, cur)

class ExceptionWithColumn(DbException):

    def __init__(self, col, cause=None):

        DbException.__init__(self, 'column: '+col, cause)

        self.col = col

class DuplicateKeyException(ExceptionWithColumn): pass

class NullValueException(ExceptionWithColumn): pass

def check_name(name):

    if re.search(r'\W', name) != None: raise NameException('Name "'+name

        +'" may contain only alphanumeric characters and _')

def run_query(db, query, params=None):

    cur = db.cursor()

    try: cur.execute(query, params)

    except Exception, ex:

        _add_cursor_info(ex, cur)

        raise

    return cur

def col(cur, idx): return cur.description[idx][0]

def row(cur): return iter(lambda: cur.fetchone(), None).next()

def value(cur): return row(cur)[0]

def with_savepoint(db, func):

    savepoint = 'savepoint_'+str(random.randint(0, sys.maxint)) # must be unique

    run_query(db, 'SAVEPOINT '+savepoint)

    try: return_val = func()

    except:

        run_query(db, 'ROLLBACK TO SAVEPOINT '+savepoint)

        raise

    else:

        run_query(db, 'RELEASE SAVEPOINT '+savepoint)

        return return_val

def select(db, table, fields, conds):

    check_name(table)

    map(check_name, fields)

    map(check_name, conds.keys())

    def cond(entry):

        col, value = entry

        cond_ = col+' '

        if value == None: cond_ += 'IS'

        else: cond_ += '='

        cond_ += ' %s'

        return cond_

    return run_query(db, 'SELECT '+', '.join(fields)+' FROM '+table+' WHERE '

        +' AND '.join(map(cond, conds.iteritems())), conds.values())

def insert(db, table, row):

    check_name(table)

    cols = row.keys()

    map(check_name, cols)

    return run_query(db, 'INSERT INTO '+table+' ('+', '.join(cols)

        +') VALUES ('+', '.join(['%s']*len(cols))+')', row.values())

def last_insert_id(db): return value(run_query(db, 'SELECT lastval()'))

def try_insert(db, table, row):

    try: return with_savepoint(db, lambda: insert(db, table, row))

    except Exception, ex:

        msg = str(ex)

        match = re.search(r'duplicate key value violates unique constraint "'

            +table+'_(\w+)_index"', msg)

        if match: raise DuplicateKeyException(match.group(1), ex)

        match = re.search(r'null value in column "(\w+)" violates not-null '

            'constraint', msg)

        if match: raise NullValueException(match.group(1), ex)

        raise # no specific exception raised

def pkey(db, table): # Assumed to be first column in table

    check_name(table)

    return col(run_query(db, 'SELECT * FROM '+table+' LIMIT 0'), 0)

def get(db, table, row, pkey, create=False, row_ct_ref=None):

    try: return value(select(db, table, [pkey], row))

    except StopIteration:

        if not create: raise

        # Insert new row

        try:

            row_ct = try_insert(db, table, row).rowcount

            if row_ct_ref != None and row_ct >= 0: row_ct_ref[0] += row_ct

            return last_insert_id(db)

        except DuplicateKeyException, ex:

            return value(select(db, table, [pkey], {ex.col: row[ex.col]}))

# Exception handling

def add_msg(ex, msg): ex.args = (str(ex).rstrip()+'\n'+msg,)

class ExceptionWithCause(Exception):

    def __init__(self, msg, cause=None):

        Exception.__init__(self, msg)

        if cause != None: add_msg(self, 'cause: '+str(cause))

# XML DOM tree manipulation

from xml.dom import Node

import xml.dom.minidom

def name_of(node): return node.tagName.lower()

def get_id(node): return node.getAttribute('id')

def set_id(node, id_): node.setAttribute('id', id_)

class NodeElemIter:

    def __init__(self, node): self.child = node.firstChild

    def __iter__(self): return self

    def curr(self):

        while self.child != None:

            if self.child.nodeType == Node.ELEMENT_NODE: return self.child

            self.child = self.child.nextSibling

        raise StopIteration

    def next(self):

        child = self.curr()

        self.child = self.child.nextSibling

        return child

def first_elem(node): return NodeElemIter(node).next()

class NodeElemReverseIter:

    def __init__(self, node): self.child = node.lastChild

    def __iter__(self): return self

    def curr(self):

        while self.child != None:

            if self.child.nodeType == Node.ELEMENT_NODE: return self.child

            self.child = self.child.previousSibling

        raise StopIteration

    def next(self):

        child = self.curr()

        self.child = self.child.previousSibling

        return child

def last_elem(node): return NodeElemReverseIter(node).next()

class NodeParentIter:

    def __init__(self, node): self.node = node

    def __iter__(self): return self

    def curr(self):

        if self.node != None and self.node.nodeType == Node.ELEMENT_NODE:

            return self.node

        raise StopIteration

    def next(self):

        node = self.curr()

        self.node = self.node.parentNode

        return node

def is_text(node):

    for child in NodeElemIter(node): return False # has an element node

    return True

def value(node):

    if node.firstChild != None: return node.firstChild.nodeValue

    else: return node.nodeValue

def set_value(doc, node, value):

    if node.nodeType == Node.ELEMENT_NODE:

        node.appendChild(doc.createTextNode(value))

    else: node.nodeValue = value

def by_tag_name(node, name, last_only=False):

    # last_only optimization returns last (most recently inserted) matching node

    children = []

    for child in NodeElemReverseIter(node):

        if child.tagName == name:

            children.append(child)

            if last_only: break

    return children

_writexml_orig = xml.dom.minidom.Element.writexml

def _writexml(self, writer, indent="", addindent="", newl=""):

    if self.firstChild != None and self.firstChild.nextSibling == None\

    and self.firstChild.nodeType == Node.TEXT_NODE: # a single text node

        writer.write(indent+'<'+self.tagName)

        for attr_idx in range(self.attributes.length):

            attr = self.attributes.item(attr_idx)

            writer.write(' '+attr.name+'='+attr.value)

        writer.write('>'+value(self)+'</'+self.tagName+'>'+newl)

    else: _writexml_orig(self, writer, indent, addindent, newl)

xml.dom.minidom.Element.writexml = _writexml

import xml_util

        else: children = xml_util.by_tag_name(parent, elem.name, last_only)

            is_match = elem.value == None or xml_util.value(child) == elem.value

            if elem.value != None: xml_util.set_value(doc, node, elem.value)

                set_value(id_elem, xml_util.get_id(node))

                id_ = xml_util.value(node)

                    if last != None: id_ = str(int(xml_util.get_id(last)) + 1)

                    xml_util.set_value(doc, node, id_)

import xml_db

        xml_db.xml2db(db, doc.documentElement, row_ct_ref)