import pg
import re

def quote(s):
    return re.sub("'", r"\'", s)

class QueryResult:
    def __init__(self, result):
        self.__result = result
        self.__tuple = None

    def getresult(self):
        if self.__tuple is None:
            self.__tuple = self.__result.getresult()
        return self.__tuple

    def __len__(self):
        return len(self.getresult())

    def __getattr__(self, attr):
        return getattr(self.__result, attr)

class SQLMapping:
    """Decode a simple mapping from an SQL table

    An interface for a simple SQL table of the following sort:
    CREATE TABLE bug_group_t (
       bug_group_id int PRIMARY KEY,
       name text NOT NULL
       );

    The chief requirements are that the types of the two fields match
    the example above. 

    Assumes that the keys and values are disjoint, so that a single
    interface can resolve in either direction.
    """

    def __init__(self, db, table, fields="*"):
        self.dict1 = {}
        self.dict2 = {}
        self.__db = db
        self.__table = table
        r = db.query("SELECT %s FROM %s" % (fields, table)).getresult()
        for key, val in r:
            assert None not in (key, val)
            self.dict1[key] = val
            self.dict2[val] = key

    def lookup(self, kv):
        r = self.dict1.get(kv)
        if r is None:
            r = self.dict2.get(kv)
        return r

    def get_dict(self):
        """Return dict mapping key to value"""
        return self.dict2

    __insert_q = "INSERT INTO %(table)s VALUES (%(key)s, '%(value)s')"
    
    def insert(self, key, value):
        table = self.__table
        value = quote(value)
        query = self.__insert_q % locals()
        self.__db.query(query)
        self.dict1[key] = value
        self.dict2[value] = key

class PatchDBInterface:
    """Interface between the PatchManager and the SQL database

    Scheme for the patches table is:
    CREATE TABLE patches_t (
       patch_id int PRIMARY KEY,
       summary text,
       status int REFERENCES status_t,
       category text,
       date text,
       submitted_by int REFERENCES users_t,
       assigned_to int REFERENCES users_t,
       summary_url text
    );
    """

    def __init__(self, db=None):
        db = db or pg.connect()
        self.db = db
        self.users = SQLMapping(db, 'users_t')
        self.status = SQLMapping(db, 'status_t')
        self.num_deletes = 0

    def update(self, patch_id, attrs):
        # resolve REFERENCES
        status = self.status.lookup(attrs['Status'])
        submitted_by = self.users.lookup(attrs['Submitted By'])
        if submitted_by is None:
            submitted_by = 0
        assigned_to = self.users.lookup(attrs['Assigned To'])
        if assigned_to is None:
            assigned_to = 100

        # delete old version if necessary
        if self.has_patch(patch_id):
            q = "DELETE FROM patches_t WHERE patch_id = %(patch_id)d"
            self.db.query(q % locals())
            self.num_deletes = self.num_deletes + 1

        d = locals()
        del d['attrs'] # just to make debugging prints clearer
        for k, v in attrs.items():
            d[k] = pg._quote(v, 0)
        
        q = "INSERT INTO patches_t VALUES (%(patch_id)d," \
            " %(Summary)s, %(status)d, %(Category)s, %(Date)s," \
            " %(submitted_by)d, %(assigned_to)d)"
        self.db.query(q % d)

    def has_patch(self, patch_id):
        r = self.db.query("SELECT * FROM patches_t" \
                          " WHERE patch_id = %d" % patch_id).getresult()
        if r:
            return 1
        else:
            return 0

    def query(self, query):
        return self.db.query(query)

class Bug:
    """Interface to bug_t row"""
    def __init__(self, bug_info, db):
        self.__db = db
        self.__dict = bug_info
        self.__clean = 1
        # Keep this one around solely for the benefit of the SF Web
        # interface; the local database ignores it.
        self.__details = None

    def get(self, attr):
        """Get an attribute of the bug

        This method understands a few different kinds of keys:
        names of bug_t columns, e.g. summary, status_id, etc.
        names of bug_t references, e.g. status returns the value
            referred to by status_id
        """
        if self.__dict.has_key(attr):
            return self.__dict[attr]
        ref = attr + "_id"
        if self.__dict.has_key(ref):
            return self.__get_ref(attr, ref)
        if attr == "details":
            return self.__details
        raise KeyError, "no attribute: %s" % attr

    def __get_mapping(self, table):
        try:
            mapping = getattr(self.__db, table)
        except AttributeError:
            raise KeyError, "no table for attribute: %s" % table
        return mapping
    
    def __get_ref(self, table, id):
        mapping = self.__get_mapping(table)
        return mapping.lookup(self.__dict[id])

    def set(self, attr, value):
        if attr == 'details':
            self.__details = value
            return
        if self.__dict.has_key(attr):
            self.__clean = 0
            self.__dict[attr] = value
            return
        ref = attr + "_id"
        if self.__dict.has_key(ref):
            self.__set_ref(attr, ref, value)
            return
        raise KeyError, "no attribute: %s" % attr

    def __set_ref(self, table, id, value):
        mapping = self.__get_mapping(table)
        key = mapping.lookup(value)
        if key is None:
            raise ValueError, "invalid attribute for table %s: %s" % \
                  (table, value)
        # __dict holds keys to values in the mapping
        self.__clean = 0
        self.__dict[id] = key

    def assign(self, username):
        # the set interface does not work well here
        self.__set_ref("users", "assigned_to", username)

    _update_ids = ('bug_group_id', 'status_id', 'category_id',
                   'resolution_id') 

    def get_update_data(self):
        """Return data in the format expected by db update method"""
        d = {}
        for attr in Bug._update_ids: 
            attr_value = attr[:-3]
            key = self.get(attr_value)
            if key is not None:
                d[attr] = key, self.get(attr)
        d['priority'] = self.get('priority'), self.get('priority')
        user = self.__get_ref('users', 'assigned_to')
        if user is not None:
            d['assigned_to'] = user, self.get('assigned_to')
        for attr, (k, v) in d.items():
            d[attr] = k, str(v)
        
        return self.__dict['bug_id'], self.__dict['summary'], d

    _form_keys = ('bug_id', 'summary', 'category_id', 'priority',
                  'bug_group_id', 'resolution_id', 'assigned_to',
                  'status_id')

    def get_form_data(self):
        d = {}
        for name in Bug._form_keys:
            val = self.get(name)
            if val is None:
                val = 100
            s = str(val)
            d[name] = s
        if self.__details is not None:
            d['details'] = self.__details
        return d

    def flush_sf(self):
        # XXX not sure what to do here...
        pass
    
    def flush_local(self):
        args = self.get_update_data()
        self.__db.update(*args)

    def flush(self):
        if not self.__clean:
            self.flush_sf()
            self.flush_local()
            self.__clean = 1

class BugDBInterface:
    """Interface to bug_t"""

    def __init__(self, db=None):
        db = db or pg.connect()
        self.__db = db
        self.bug_group = SQLMapping(db, "bug_group_t")
        self.category = SQLMapping(db, "category_t")
        self.status = SQLMapping(db, "status_t")
        self.users = SQLMapping(db, "users_t")
        self.resolution = SQLMapping(db, "resolution_t")

    def __query(self, query):
        print query
        return QueryResult(self.__db.query(query))

    def get(self, bug_id):
        r = self.__query('select * from bug_t where bug_id = %s' % bug_id)
        if len(r) == 0:
            return None
        assert len(r) == 1
        return Bug(r.dictresult()[0], self)

    def insert(self, bug_id, summary, attrs):
        """Load bug using the info from the buglig BugParser"""
        self._do_bug_query(bug_id, summary, attrs, self.__insert_q)

    def update(self, bug_id, summary, attrs):
        """Update existing bug using info from buglib BugParser"""
        self.__query("BEGIN WORK")
        sql = 'DELETE FROM bug_t WHERE bug_id = %(bug_id)s' % locals()
        self.__query(sql)
        self.insert(bug_id, summary, attrs)
        self.__query("COMMIT WORK")

    __insert_q = "INSERT INTO bug_t (bug_id, summary, " \
                 "%(optional_keys)s, priority) VALUES " \
                 "(%(bug_id)s, '%(summary)s', %(optional_values)s,  " \
                 "%(priority)s)"

    def _do_bug_query(self, bug_id, summary, attrs, query):
        summary = quote(summary)
        priority = int(attrs["priority"][1])
        optional_keys, optional_values = self._prep_query_refs(attrs)

        sql = self.__insert_q % locals()
        self.__query(sql)

    def _prep_query_refs(self, attrs):
        self._new_query()
        self._lookup_ref(self.bug_group, attrs, "bug_group_id")
        self._lookup_ref(self.category, attrs, "category_id")
        self._lookup_ref(self.resolution, attrs, "resolution_id")
        self._lookup_ref(self.status, attrs, "status_id")
        self._lookup_ref(self.users, attrs, "assigned_to")

        # now figure out which of the optional fields have values (yuck)
        for k, v in self.refs.items():
            if v is None:
                del self.refs[k]
        optional_keys = ", ".join(self.refs.keys())
        optional_values = ", ".join(map(str, self.refs.values()))
        return optional_keys, optional_values

    __exists_q = "SELECT * FROM bug_t WHERE bug_id = %(bug_id)s"
    
    def exists(self, bug_id):
        if self.__query(self.__exists_q % locals()):
            return 1
        else:
            return 0

    def _new_query(self):
        self.refs = {}

    def _lookup_ref(self, table, attrs, attrname):
        pair = attrs.get(attrname)
        if pair is None:
            self.refs[attrname] = None
            return None
        value, key = pair
        id = table.lookup(value)
        if id is None:
            id = int(key)
            table.insert(id, value)
        self.refs[attrname] = id
        return id