# bugzilla.py -- Bugzilla interface.
# Nicholas Barnes, Ravenbrook Limited, 2000-11-21.
# $Id: //info.ravenbrook.com/project/p4dti/version/0.4/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:

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

    def __init__(self,
                 connect, host, port, database, user, password,
                 rid, sid):
        self.db = connect(host = host,
                          port = port,
                          db = database,
                          user = user,
                          passwd = 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_bugs_activity')) :
            self.execute("create table p4dti_bugs_activity "
                         "  ( bug_id mediumint not null, "
                         "    who mediumint not null, "
                         "    bug_when datetime not null, "
                         "    fieldid mediumint not null, "
                         "    oldvalue tinytext, "
                         "    newvalue tinytext, "
                         "    rid varchar(32) not null, "
                         "    sid varchar(32) not null, "

                         "    index(bug_id), "
                         "    index(bug_when), "
                         "    index(fieldid) "
                         "  );")

        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_activity;")
        if (self.table_present('p4dti_bugs_activity')) :
            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)

    # For fields which should get timestamps.

    def if_empty_then_now(self,v) :
        if v == '':
            return 'now()'
        else:
            return self.sqlquote(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,
          },

        'bugs_activity' :
        { 'bug_id' : self_str,
          'who' : self_str,
          'fieldid' : self_str,
          },
        
        'p4dti_bugs_activity' :
        { 'bug_id' : self_str,
          'who' : self_str,
          'fieldid' : 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,
          'end' : if_empty_then_now
          }
        }

    # 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)


    ### 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 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',
                                 ('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 add_filespec(self, filespec):
        filespec['replication'] = self.replication
        self.insert_row_rid_sid('p4dti_filespecs', filespec)

    def delete_filespec(self, filespec):
        self.delete_rows_rid_sid('p4dti_filespecs',
                                 ('bug_id = %d and filespec = %s'
                                  % (filespec['bug_id'],
                                     self.sqlquote(fix['filespec']))))

    def update_fix(self, fix, bug_id, number):
        fix['replication'] = self.replication
        self.update_row_rid_sid('p4dti_filespecs', 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 rid = %s and "
             "        sid = %s and "
             "        bug_id = %d" % (self.sqlquote(self.rid),
                                      self.sqlquote(self.sid),
                                      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 using (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, date):
        changed = self.fetch_rows_as_list_of_dictionaries(
            # find bugs which were changed since the given date
            # by someone other than this replicator.
            ("select bugs.* "
             "    from bugs, "
             "         bugs_activity ba "
             "    left join p4dti_bugs_activity pba "
             "      on (ba.bug_id = pba.bug_id and "
             "          ba.bug_when = pba.bug_when and "
             "          ba.who = pba.who and "
             "          ba.oldvalue = pba.oldvalue and "
             "          ba.newvalue = pba.newvalue and "
             "          pba.rid = %s and "
             "          pba.sid = %s) "
             "    where pba.bug_id is null and "
             "          ba.bug_when >= %s and "
             "           bugs.bug_id = ba.bug_id "
             "    group by bugs.bug_id;" %
             (self.sqlquote(self.rid),
              self.sqlquote(self.sid),
              self.sqlquote(date))),
            "changed bugs since '%s'" % date)
        # new bugs don't get entries in bugs_activity, so
        # require a separate select.
        new = self.fetch_rows_as_list_of_dictionaries(
            ("select bugs.* from bugs "
             "    left join bugs_activity "
             "      on (bugs.bug_id = bugs_activity.bug_id) "
             "    where bugs_activity.bug_id is null and "
             "          bugs.delta_ts >= %s" %
             (self.sqlquote(date))),
            "new bugs since '%s'" % date)
        return changed + new
            

    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 rid = %s and "
             "        sid = %s and "
             "        bug_id = %d" % (self.sqlquote(self.rid),
                                      self.sqlquote(self.sid),
                                      bug_id)),
            "fixes for bug %d" % bug_id)

    def filespecs_from_bug_id(self, bug_id):
        return self.fetch_rows_as_list_of_dictionaries (
            ("select * from p4dti_filespecs "
             "  where rid = %s and "
             "        sid = %s and "
             "        bug_id = %d" % (self.sqlquote(self.rid),
                                      self.sqlquote(self.sid),
                                      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)

    def groupset_from_userid(self, user):
        row = self.select_one_row("select groupset from profiles where"
                                  " userid = %d;" % user,
                                  "groupset for user %d" % user)
        return row[0]

    def groups(self):
        return self.fetch_rows_as_list_of_dictionaries(
            'select * from groups', 'all groups');

    def fielddefs(self):
        return self.fetch_rows_as_list_of_dictionaries(
            'select * from fielddefs', 'all fielddefs');

    def add_activity(self, dict):
        self.insert_row('bugs_activity', dict)
        # copy the dictionary so the rid and sid columns don't get
        # passed back out.
        p4dti_dict = dict.copy()
        self.insert_row_rid_sid('p4dti_bugs_activity', p4dti_dict)


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

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

    def end_replication(self):
        if self.replication != None:
            self.update_row_rid_sid('p4dti_replications', {'end' : ''}, 
                            '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

    # Locking
    def lock_tables(self) :
        self.execute("lock tables "
                     "  bugs write, "
                     "  bugs_activity write, "
                     "  p4dti_bugs write, "
                     "  p4dti_bugs_activity write, "
                     "  p4dti_changelists write, "
                     "  p4dti_fixes write, "
                     "  p4dti_filespecs write, "
                     "  p4dti_replications write, "
                     "  profiles read, "
                     "  groups read, "
                     "  fielddefs read, "
                     "  bugs_activity as ba read, "
                     "  p4dti_bugs_activity as pba read;")

    def unlock_tables(self) :
        self.execute("unlock tables;")

# B. Document History
# 
# 2000-12-05 NB Fixes for job job000089 and job000118.  We update bugs_activity
# and have a new table p4dti_bugs_activity which duplicates bugs_activity rows
# added by this replicator.  A complicated select then identifies bugs which
# have been changed other than by the replicator.  Locking added.  Fixes,
# filespecs, and changelists now work.
# 
# 2000-12-07 RB Abolished "config" in favour of explicit parameters so that
# this is callable from the configurator (which doesn't have a config when it
# needs to call).