# bugzilla.py -- Bugzilla interface.
# Nicholas Barnes, Ravenbrook Limited, 2000-11-21.
# $Id: //info.ravenbrook.com/project/p4dti/branch/2000-11-29/bugzilla-resolution/code/replicator/bugzilla.py#2 $
#
# Copyright 2000 Ravenbrook Limited.  This document is provided "as is",
# without any express or implied warranty. In no event will the authors
# be held liable for any damages arising from the use of this document.
# You may make and distribute copies and derivative works of this
# document provided that (1) you do not charge a fee for this document or
# for its distribution, and (2) you retain as they appear all copyright
# and licence notices and document history entries, and (3) you append
# descriptions of your modifications to the document history.

import types

error = 'P4DTI TeamTrack interface error'

class bugzilla:
    config = {}

    db = None
    cursor = None
    rid = None
    sid = None
    replication = None

    def __init__(self, config, rid, sid):
        # Merge the supplied config with the default config (the former takes
        # precedence).
        for k in config.keys():
            self.config[k] = config[k]
        self.db = self.config['db'](host=self.config['dbms-host'],
                                    port=self.config['dbms-port'],
                                    db=self.config['dbms-database'],
                                    user=self.config['dbms-user'],
                                    passwd=self.config['dbms-password'])
        self.cursor = self.db.cursor()
        self.rid = rid
        self.sid = sid

    def execute(self, sql):
        print ("SQL execute '%s'" % sql),
        rows = self.cursor.execute(sql)
        print "->", rows
        return rows

    def fetchone(self):
        row = self.cursor.fetchone()
        print "fetchone() ->", row
        return row

    def fetchall(self):
        rows = self.cursor.fetchall()
        print "fetchall() ->", rows
        return rows

    ### Tables.

    # is this table present?
    def table_present(self, tablename):
        rows = self.execute("show tables from bugs like %s;"
                            % self.sqlquote(tablename))
        return (rows == 1)

    # create the new tables that we need
    def create_p4dti_tables(self):
        if (not self.table_present('p4dti_bugs')) :
            self.execute("create table p4dti_bugs "
                         "  ( bug_id mediumint not null primary key, "
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "
                         "    jobname text not null, "
                         "    replication datetime not null, "
                         "    action text not null "
                         "  );")

        if (not self.table_present('p4dti_changelists')) :
            self.execute("create table p4dti_changelists "
                         "  ( changelist int not null, "
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "
                         "    user mediumint not null, "
                         "    flags int not null, "
                         "    description longtext not null, "
                         "    client text not null, "
                         "    p4date text not null, "
                         "    replication datetime not null, "
                         
                         "    unique (changelist, rid, sid) "
                         "  );")

        if (not self.table_present('p4dti_fixes')) :
            self.execute("create table p4dti_fixes "
                         "  ( changelist int not null, "
                         "    bug_id mediumint not null, "
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "
                         "    user mediumint not null, "
                         "    client text not null, "
                         "    status text not null, "
                         "    p4date text not null, "
                         "    replication datetime not null "
                         "  );")

        if (not self.table_present('p4dti_filespecs')) :
            self.execute("create table p4dti_filespecs "
                         "  ( bug_id mediumint not null, "
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "
                         "    replication datetime not null, "
                         "    filespec longtext not null "
                         "  );")
            
        if (not self.table_present('p4dti_replications')) :
            self.execute("create table p4dti_replications "
                         "  ( id int auto_increment not null "
                         "       primary key," # for uniqueness
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "
                         "    start datetime not null, "
                         "    end datetime not null "
                         "  );")

    # drop all our tables
    def drop_p4dti_tables(self):
        if (self.table_present('p4dti_bugs')) :
            self.execute("drop table p4dti_bugs;")
        if (self.table_present('p4dti_changelists')) :
            self.execute("drop table p4dti_changelists;")
        if (self.table_present('p4dti_fixes')) :
            self.execute("drop table p4dti_fixes;")
        if (self.table_present('p4dti_filespecs')) :
            self.execute("drop table p4dti_filespecs;")
        if (self.table_present('p4dti_replications')) :
            self.execute("drop table p4dti_replications;")

    ### Quotation.

    # Given a string, return an SQL representation
    # (e.g. "foo" -> "'foo'", "O'Malley" -> "'O\\'Malley'")

    def sqlquote(self, value):
        if (type(value) != types.StringType):
            raise error, ("sqlquote given non-string %s." % str(value))
        return "'" + self.db.escape_string(value) + "'"

    # A version of str which takes self.

    def self_str(self,v) :
        return str(v)

    # A table of functions to translate from
    # Python values into SQL textual
    # representations.  We don't have to
    # go the other way because SQL gives us Python
    # values.  We have a default of sqlquote,
    # which handles string values.  Any other
    # column which we might update has to
    # go in this table.

    quote_table = {
        'bugs' :
        {
        'assigned_to' : self_str,
        },
        'p4dti_bugs' :
        {
        'bug_id' : self_str
        },
        'p4dti_changelists' :
        {
        'changelist' : self_str,
        'user' : self_str,
        'flags' : self_str
        },
        'p4dti_fixes' :
        {
        'changelist' : self_str,
        'bug_id' : self_str,
        'user' : self_str,
        },
        'p4dti_filespecs' :
        {
        'bug_id' : self_str,
        },
        'p4dti_replications' :
        {
        'id' : self_str,
        }
        }

    # quote a single value
    def quote(self, table, field, value) :
        if self.quote_table[table].has_key(field):
            return self.quote_table[table][field](self, value)
        else:
            return self.sqlquote(value)

    # quote all values in a dictionary
    def quote_dict(self, table, dict) :
        new_dict = {}
        for key in dict.keys() :
            new_dict[key] = self.quote(table, key, dict[key])
        return new_dict


    ### Basic selects.

    # select a row and do error-checking.

    def select_one_row(self, select, description):
        rows = self.execute(select)

        if rows == 0 :
            raise error, ("select '%s' of %s returns no rows."
                          % (select, description))
        
        if rows > 1 :
            raise error, ("select '%s' of %s returns %d rows."
                          % (select, description, rows))

        if self.cursor.description == None :
            raise error, ("trying to fetch a row from non-select '%s'"
                          % select)

        # get values
        values = self.fetchone()

        if values == None :
            raise error, ("Database error: select '%s'"
                          " returned an unfetchable row." % select)

        return values

    # select many rows and do some error-checking.

    def select_rows(self, select, description):
        rows = self.execute(select)

        if self.cursor.description == None :
            raise error, ("trying to fetch rows from non-select '%s'"
                          % select)

        # get values
        values = self.fetchall()

        if values == None :
            raise error, ("Database error: select '%s'"
                          " returned unfetchable rows." % select)

        return values

    def select_at_most_one_row(self, select, description):
        rows = self.select_rows(select, description)
        if len(rows) == 0 :
            return None
        elif len(rows) == 1 :
            return rows[0]
        else :
            raise error, ("select '%s' of %s returns %d rows."
                          % (select, description, rows))

    def column_names(self):
        keys = []
        for i in range(len(self.cursor.description)):
            keys.append(self.cursor.description[i][0])
        return keys
            

    # select a row and return the result as a dictionary (column -> value)

    def fetch_one_row_as_dictionary(self, select, description):
        dict = {}

        values = self.select_one_row(select, description)
        keys = self.column_names()

        if len(keys) != len(values) :
            raise error, ("Database error: select '%s'"
                          " returns %d keys but %d values."
                         % (select, len(keys), len(values)))

        for i in range(len(keys)):
                dict[keys[i]] = values[i]
        return dict

    def fetch_at_most_one_row_as_dictionary(self, select, description):
        dict = {}

        values = self.select_at_most_one_row(select, description)

        if values == None :
            return None

        keys = self.column_names()

        if len(keys) != len(values) :
            raise error, ("Database error: select '%s'"
                          " returns %d keys but %d values."
                         % (select, len(keys), len(values)))

        for i in range(len(keys)):
                dict[keys[i]] = values[i]
        return dict

    # select a row and return the result as a list.

    def fetch_one_row_as_list(self, select, description):
        return self.select_one_row(select, description)
    
    # select many rows and return the result as a list of dictionaries

    def fetch_rows_as_list_of_dictionaries(self, select, description):
        results = []

        values = self.select_rows(select, description)

        keys = self.column_names()

        for value in values:
            result={}
            if len(keys) != len(value) :
                raise error, ("Database error: select '%s'"
                              " returns %d keys but %d columns."
                              % (select, len(keys), len(value)))
            for j in range(len(keys)):
                result[keys[j]] = value[j]
            results.append(result)

        return results

    # select many rows and return the result as a list of lists

    def fetch_rows_as_list_of_lists(self, select, description):
        return self.select_rows(select, description)

    ### Other basic operations.

    # Insert a row.
    def insert_row(self, table, dict):
        columns = ""
        values = ""
        for key in dict.keys():
            value = self.quote(table, key, dict[key])
            columns = columns + key + ", "
            values = values + value + ', '
        columns = columns[:-2]
        values = values[:-2]
        command = "insert %s ( %s ) values ( %s );" % (table, columns, values)
        rows = self.execute(command)
        if rows != 1 :
            raise error, ("Couldn't insert row in %s" % table)

    # Insert a row in a table with rid and sid columns.
    def insert_row_rid_sid(self, table, dict):
        dict['rid'] = self.rid
        dict['sid'] = self.sid
        self.insert_row(table, dict)

    # Update a row.
    def update_row(self, table, dict, where):
        updates = ""
        for key in dict.keys():
            value = self.quote(table, key, dict[key])
            updates = updates + key + ' = ' + value + ', '
        updates = updates[:-2]
        command = "update %s set %s where %s;" % (table, updates, where)
        rows = self.execute(command)
        if rows != 1 :
            raise error, ("Couldn't update row in %s where %s"
                          % (table, where))

    # Update a row in a table with rid and sid columns
    def update_row_rid_sid(self, table, dict, where):
        self.update_row(table, dict, where +
                        (' and rid = %s and sid = %s' %
                         (self.sqlquote(self.rid), self.sqlquote(self.sid))))

    # Delete rows
    def delete_rows(self, table, where):
        self.execute('delete from %s where %s;' % (table, where))

    # Delete rows in a table with rid and sid columns
    def delete_rows_rid_sid(self, table, dict, where):
        self.delete_rows(table, where +
                         (' and rid = %s and sid = %s' %
                          (self.sqlquote(self.rid), self.sqlquote(self.sid))))
        
        
    ### Particular operations which we use.

    # Note that update operations take a key argument.  This means we can
    # restrict our update to a part-record by passing a small dictionary.
    
    def add_p4dti_bug(self, dict):
        dict['replication'] = self.replication
        self.insert_row_rid_sid('p4dti_bugs', dict)

    def update_p4dti_bug(self, dict, bug_id):
        dict['replication'] = self.replication
        self.update_row_rid_sid('p4dti_bugs', dict,
                                'bug_id = %d' % bug_id)

    def update_bug(self, dict, bug_id):
        self.update_row('bugs', dict, 'bug_id = %d' % bug_id)

    def add_changelist(self, dict):
        dict['replication'] = self.replication
        self.insert_row_rid_sid('p4dti_changelists', dict)

    def update_changelist(self, dict, number):
        dict['replication'] = self.replication
        self.update_row_rid_sid('p4dti_changelists', dict,
                                'changelist = %d' % number)

    def add_fix(self, fix):
        fix['replication'] = self.replication
        self.insert_row_rid_sid('p4dti_fixes', fix)

    def delete_fix(self, fix):
        self.delete_rows_rid_sid('p4dti_fixes',
                                 ('where bug_id = %d and changelist = %d '
                                  % (fix['bug_id'], fix['changelist'])))

    def update_fix(self, fix, bug_id, number):
        fix['replication'] = self.replication
        self.update_row_rid_sid('p4dti_fixes', fix, 
                                ('bug_id = %d and changelist = %d '
                                 % (bug_id, changelist)))

    def bug_p4dti_bug(self, bug):
        bug_id = bug['bug_id']
        p4dti_bug = self.fetch_at_most_one_row_as_dictionary(
            'select * from p4dti_bugs where bug_id = %d' % bug_id,
            'p4dti_bug %d' % bug_id)
        return p4dti_bug

    def all_bugs(self):
        unmatched = self.fetch_rows_as_list_of_dictionaries(
            "select bugs.* from bugs"
            "  left join p4dti_bugs on bugs.bug_id = p4dti_bugs.bug_id"
            "  where p4dti_bugs.bug_id is null;",
            "non-matched bugs")
        matched = self.fetch_rows_as_list_of_dictionaries(
            ("select bugs.* from bugs, p4dti_bugs"
             "  where bugs.bug_id = p4dti_bugs.bug_id "
             "  and p4dti_bugs.rid = %s "
             "  and p4dti_bugs.sid = %s " % (self.sqlquote(self.rid),
                                             self.sqlquote(self.sid))),
            "matched bugs")
        return unmatched + matched

    def all_bugs_since(self, replication):
        return self.fetch_rows_as_list_of_dictionaries(
            ("select * from bugs where delta_ts >= %s;" %
             self.sqlquote(replication)),
            "recent bugs")

    # some functions which get and return single rows as
    # dictionaries
    
    def bug_from_bug_id(self, bug_id):
        return self.fetch_one_row_as_dictionary(
            "select * from bugs where bug_id = %d;" % bug_id,
            "bug id %d" % bug_id)

    def fixes_from_bug_id(self, bug_id):
        return self.fetch_rows_as_list_of_dictionaries (
            "select * from p4dti_fixes where bug_id = %d;" % bug_id,
            "fixes for bug %d" % bug_id)

    def user_id_and_email_list(self):
        return self.fetch_rows_as_list_of_lists (
            "select userid, login_name from profiles;",
            "all user ids and emails")

    def changelists(self, number):
        return self.fetch_rows_as_list_of_dictionaries(
            "select * from p4dti_changelists "
            "  where changelist = %d and "
            "        rid = %s and "
            "        sid = %s;" % (number,
                                   self.sqlquote(self.rid),
                                   self.sqlquote(self.sid)),
            "changelist %d" % number)


    ### Operations on replications (table p4dti_replications)
    
    def now(self):
        self.execute("select now();")
        value = self.fetchall()
        return value[0][0]

    def new_replication(self):
        now = self.now()
        self.insert_row_rid_sid('p4dti_replications', { 'start': now,
                                                        'end' : '0' } )
        self.replication = now
        return now

    def end_replication(self):
        if self.replication != None:
            now = self.now()
            self.update_row_rid_sid('p4dti_replications', {'end' : now}, 
                            'start = %s' % self.sqlquote(self.replication))

    def delete_complete_replications(self) :
        self.delete_rows_rid_sid('p4dti_replications',
                                 ' end != 0')

    def incomplete_replications(self) :
        return self.fetch_rows_as_list_of_dictionaries(
            ("select * from p4dti_replications where "
             " rid = %s and "
             " sid = %s and "
             " end = 0;"
             % (self.sqlquote(self.rid),
                self.sqlquote(self.sid))),
            ("incomplete replications for (%s, %s)" % (self.rid, self.sid)))
    
    def latest_complete_replication(self) :
        self.execute("select max(start) from p4dti_replications where "
                     " rid = %s and "
                     " sid=  %s and "
                     " end != 0;"
                     % (self.sqlquote(self.rid),
                        self.sqlquote(self.sid)))
        result = self.fetchall()[0][0]
        if result == None :
            return '0'
        else :
            return result