# bugzilla.py -- Bugzilla interface. # Nicholas Barnes, Ravenbrook Limited, 2000-11-21. # $Id: //info.ravenbrook.com/project/p4dti/branch/2001-04-20/migrate-bugzilla/code/replicator/bugzilla.py#18 $ # # This file is copyright (c) 2001 Perforce Software, Inc. All rights # reserved. # # Redistribution and use in source and binary forms, with or without # modification, are permitted provided that the following conditions are # met: # # 1. Redistributions of source code must retain the above copyright # notice, this list of conditions and the following disclaimer. # # 2. Redistributions in binary form must reproduce the above copyright # notice, this list of conditions and the following disclaimer in # the documentation and/or other materials provided with the # distribution. # # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS # "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT # LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR # A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT # HOLDERS AND CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, # INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, # BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS # OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND # ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR # TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE # USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH # DAMAGE. import catalog import os import re import string import types error = 'Bugzilla database error' class bugzilla: schema_version = '3' db = None cursor = None rid = None sid = None replication = None logger = None processmails=[] def __init__(self, db, config): self.db = db self.logger = config.logger self.cursor = self.db.cursor() self.rid = config.rid self.sid = config.sid self.bugzilla_directory = config.bugzilla_directory self.create_p4dti_tables() # make configuration dictionary c = {} c['replicator_user'] = config.replicator_address c['p4_server_description'] = config.p4_server_description if config.changelist_url is not None: c['changelist_url'] = config.changelist_url if config.job_url is not None: c['job_url'] = config.job_url c['schema_version'] = self.schema_version self.set_config(c) def log(self, id, args): msg = catalog.msg(id, args) msg.text = self.rid + " " + msg.text self.logger.log(msg) def execute(self, sql): assert isinstance(sql, types.StringType) # "Executing SQL command '%s'." self.log(100, sql) rows = self.cursor.execute(sql) # "MySQL returned '%s'." self.log(101, repr(rows)) return rows def fetchone(self): row = self.cursor.fetchone() # "fetchone() returned '%s'." self.log(102, repr(row)) return row def fetchall(self): rows = self.cursor.fetchall() # "fetchall() returned '%s'." self.log(103, repr(rows)) # for some reason, if no rows are returned sometimes one gets () here. if len(rows) == 0: rows = [] return rows ### Tables. # is this table present? def table_present(self, tablename): assert isinstance(tablename, types.StringType) rows = self.execute("show tables 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, " " migrated datetime, " " index(bug_id) " " );") 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) " " );") 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, " " 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, " " unique (bug_id, changelist, rid, sid), " " index (bug_id) " " );") 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, " " filespec longtext not null, " " index(bug_id)" " );") if (not self.table_present('p4dti_config')) : self.execute("create table p4dti_config " " ( rid varchar(32) not null, " " sid varchar(32) not null, " " config_key text not null, " " config_value longtext, " " index(rid, sid)" " );") item = { 'config_key': 'schema_version', 'config_value': self.schema_version } self.insert_row_rid_sid('p4dti_config', item) if (not self.table_present('p4dti_replications')) : self.execute("create table p4dti_replications " " ( rid varchar(32) not null, " " sid varchar(32) not null, " " start datetime not null, " " end datetime not null, " " id int not null auto_increment, " " unique (id), " " unique (start, rid, sid, id), " " index (rid, sid), " " index (end) " " );") # 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_bugs_activity')) : self.execute("drop table p4dti_bugs_activity;") 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_config')) : self.execute("drop table p4dti_config;") if (self.table_present('p4dti_replications')) : self.execute("drop table p4dti_replications;") ### Quotation. # Given a string or integer type, return an SQL representation # (e.g. "foo" -> "'foo'", # "O'Malley" -> "'O\\'Malley'", # 3 -> 3, # 123L -> 123) def sqlquote(self, value): if (type(value) == types.StringType): return "'" + self.db.escape_string(value) + "'" if (type(value) == types.IntType): return str(value) if (type(value) == types.LongType): s = str(value) # note that there's a difference between python 1.5.2 and python 1.6 # here, in whether str of a long ends in an L. NB 2001-01-23 if s[-1:] == 'L': s = s[:-1] return s # "Given '%s' when expecting a string or integer." raise error, catalog.msg(105, str(value)) # For fields which should get timestamps. def if_empty_then_now(self,v) : if v == '': return 'now()' else: return self.sqlquote(v) # For inserting encrypted passwords. def cryptpassword(self,v) : if v == '': return 'encrypt(password)' 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 and integer values. # Any other column which we might update has to # go in this table. quote_table = { 'bugs' : { 'creation_ts': if_empty_then_now, }, 'bugs_activity' : { }, 'longdescs' : { 'bug_when' : if_empty_then_now, }, 'p4dti_bugs_activity' : { }, 'p4dti_bugs' : { 'migrated': if_empty_then_now, }, 'p4dti_changelists' : { }, 'p4dti_fixes' : { }, 'p4dti_filespecs' : { }, 'p4dti_replications' : { 'end' : if_empty_then_now, }, 'profiles' : { 'cryptpassword': cryptpassword, }, } # quote a single value def quote(self, table, field, value) : if (self.quote_table.has_key(table) and 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 : # "Select '%s' of %s returns no rows." raise error, catalog.msg(106, (select, description)) if rows > 1 : # "Select '%s' of %s expecting one row but returns %d." raise error, catalog.msg(107, (select, description, rows)) if self.cursor.description == None : # "Trying to fetch a row from non-select '%s'." raise error, catalog.msg(108, select) # get values values = self.fetchone() if values == None : # "Select '%s' of %s returned an unfetchable row." raise error, catalog.msg(109, (select, description)) 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 : # "Trying to fetch rows from non-select '%s'." raise error, catalog.msg(110, select) # get values values = self.fetchall() if values == None : # "Select '%s' of %s returned unfetchable rows." raise error, catalog.msg(111, (select, description)) 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 : # "Select '%s' of %s expecting no more than one row but returns %d." raise error, catalog.msg(112, (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) : # "Select '%s' of %s returns %d keys but %d values." raise error, catalog.msg(113, (select, description, 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) : # "Select '%s' of %s returns %d keys but %d values." raise error, catalog.msg(113, (select, description, 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) : # "Select '%s' of %s returns %d keys but %d columns." raise error, catalog.msg(115, (select, description, 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): rows = self.select_rows(select, description) # for some reason if one gets no rows this may be () or [] if len(rows) == 0: rows = [] return rows ### Types # A dictionary mapping table name to a list of names of columns containing # user ids. user_fields = { 'bugs' : ['assigned_to', 'reporter', 'qa_contact']} # A dictionary mapping table name to a dictionary of additional columns # faked by this module. fake_fields = { 'bugs' : {'longdesc' : {'type' : 'text', 'length' : 0, 'default': None, 'null': 0} } } # Convert a dictionary describing a column into a 'type' value. # The dictionary includes: # 'Field': column name, # 'Type': SQL type, # 'Null': null allowed ('YES' or '') # 'Key': indexed ('PRI', 'UNI', 'MUL', or '') # 'Default': default value or None # 'Extra': attributes (e.g. auto_increment) def convert_type(self, table, dict): name = dict['Field'] sql_type = dict['Type'] default = dict['Default'] if dict['Null'] == 'YES': null_p = 1 else: null_p = 0 # user fields if self.user_fields.has_key(table) and name in self.user_fields[table]: return {'type': 'user'} # enumerated types match = re.match("^enum\('(.*)'\)$", sql_type) if match != None: enum_values = string.split(match.group(1), "','") return {'type': 'enum', 'length': max(map(len, enum_values)), 'default': default, 'null': null_p, 'values': enum_values} # integral types match = re.match("^(tinyint|smallint|mediumint|int|bigint)\((.*)\)$", sql_type) if match != None: return {'type': 'int', 'length': int(match.group(2)), 'default': default, 'null': null_p} # date types match = re.match("^datetime", sql_type) if match != None: return {'type': 'date', 'default': None, # don't support defaults for dates 'null': null_p} match = re.match("^timestamp", sql_type) if match != None: return {'type': 'timestamp', 'default': None, # don't support defaults for timestamps 'null': null_p} # sized text types match = re.match("^(char|varchar)\((.*)\)$", sql_type) if match != None: return {'type': 'text', 'length': int(match.group(2)), 'default': default, 'null': null_p} # implicit-sized text types match = re.match("^(tinyblob|tinytext)$", sql_type) if match != None: return {'type': 'text', 'length': 255, 'default': default, 'null': null_p} match = re.match("^(blob|text)$", sql_type) if match != None: return {'type': 'text', 'length': 65535, 'default': default, 'null': null_p} match = re.match("^(mediumblob|mediumtext)$", sql_type) if match != None: return {'type': 'text', 'length': 16777215, 'default': default, 'null': null_p} match = re.match("^(longblob|longtext)$", sql_type) if match != None: return {'type': 'text', 'length': 4294967295L, 'default': default, 'null': null_p} # float types match = re.match("^(float|double|decimal)", sql_type) if match != None: return {'type': 'float', 'default': default, 'null': null_p} # types we don't know how to handle. Includes: # date, time, year, set(...) # # Don't raise an exception here; we might not look at this field # so might not care that we don't know what type it is. return {'type': 'other', 'sql_type': sql_type} # Get all field types for a table. def get_types(self, table): results = self.fetch_rows_as_list_of_dictionaries( 'describe %s;' % table, 'describe %s' % table) columns = {} for result in results: columns[result['Field']] = self.convert_type(table, result) if self.fake_fields.has_key(table): for fake_field in self.fake_fields[table].keys(): columns[fake_field] = self.fake_fields[table][fake_field] return columns ### 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 : # "Couldn't insert row in table '%s'." raise error, catalog.msg(116, 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 : # "Couldn't update row in table '%s' where %s." raise error, catalog.msg(117, (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, 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 dict argument. This means we can # restrict our update to a part-record by passing a small dictionary. def add_p4dti_bug(self, dict, created): if created: dict['migrated'] = '' # gets now() self.insert_row_rid_sid('p4dti_bugs', dict) def update_p4dti_bug(self, dict, bug_id): if dict: self.update_row_rid_sid('p4dti_bugs', dict, 'bug_id = %d' % bug_id) def delete_p4dti_bug(self, bug_id): self.delete_rows_rid_sid('p4dti_bugs', 'bug_id = %d' % bug_id) def add_longdesc(self, bug_id, user, comment): longdesc = {} longdesc['bug_id'] = bug_id longdesc['who'] = user longdesc['bug_when'] = '' # gets now() longdesc['thetext'] = string.strip(comment) self.insert_row('longdescs', longdesc) def update_longdesc(self, bug_id, user, old, new): new_comment = string.strip(new[len(old):]) self.add_longdesc(bug_id, user, new_comment) def update_bug(self, dict, bug, user): bug_id = bug['bug_id'] if dict.has_key('longdesc'): self.update_longdesc(bug_id, user, bug['longdesc'], dict['longdesc']) del dict['longdesc'] if dict: self.update_row('bugs', dict, 'bug_id = %d' % bug_id) def delete_bug(self, bug_id): where = 'bug_id = %d' % bug_id self.delete_rows('bugs', where) self.delete_rows('longdescs', where) self.delete_rows('bugs_activity', where) self.delete_rows('p4dti_bugs_activity', where) # Add a dictionary as a bug. def add_bug(self, bug, email): longdesc = bug['longdesc'] del bug['longdesc'] if not bug.has_key('creation_ts'): bug['creation_ts'] = '' # gets now() self.insert_row('bugs', bug) self.execute("select last_insert_id();") bug_id = int(self.fetchall()[0][0]) bug['bug_id'] = bug_id self.add_longdesc(bug_id, bug['reporter'], longdesc) if self.bugzilla_directory != None: self.defer_processmail([self.sqlquote(bug_id), email]) return bug_id def add_user(self, dict): dict['cryptpassword'] = '' # so we get the encryption of the real pwd self.insert_row('profiles', dict) self.execute("select last_insert_id();") userid = int(self.fetchall()[0][0]) return userid def add_changelist(self, dict): self.insert_row_rid_sid('p4dti_changelists', dict) def update_changelist(self, dict, number): if dict: self.update_row_rid_sid('p4dti_changelists', dict, 'changelist = %d' % number) def add_fix(self, fix): 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 delete_fixes_for_bug(self, bug_id): self.delete_rows_rid_sid('p4dti_fixes', 'bug_id = %d' % bug_id) def update_fix(self, dict, bug_id, changelist): if dict: self.update_row_rid_sid('p4dti_fixes', dict, ('bug_id = %d and changelist = %d' % (bug_id, changelist))) def add_filespec(self, filespec): 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 delete_filespecs_for_bug(self, bug_id): self.delete_rows_rid_sid('p4dti_filespecs', 'bug_id = %d' % bug_id) 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 # Regular expression to match a non-empty blank line, i.e. a line # containing space and/or tab characters but nothing else. # See job000375. blank_line_re = re.compile('^[ \t]+$', re.M) def bug_get_longdesc(self, bug): bug_id = bug['bug_id'] longdescs = self.fetch_rows_as_list_of_dictionaries( "select profiles.login_name, profiles.realname, " " longdescs.bug_when, longdescs.thetext " " from longdescs, profiles " " where profiles.userid = longdescs.who " " and longdescs.bug_id = %d" " order by longdescs.bug_when" % bug_id, "long descriptions for bug %d" % bug_id) longdesc = "" first = 1 for record in longdescs: thetext = record['thetext'] # replace blank lines with empty lines. job000375. thetext = self.blank_line_re.sub('', thetext) if first: longdesc = thetext first = 0 else: longdesc = (longdesc + ("\n\n------- %s <%s> at %s -------\n" % (record['realname'], record['login_name'], record['bug_when'])) + thetext) longdesc = (longdesc + "\n\n------- Append additional comments below -------") return longdesc def all_bugs_since(self, date): # Find all bugs replicated by this replicator, and all # unreplicated bugs new, touched, or changed since the given # date. bugs = self.fetch_rows_as_list_of_dictionaries( ("select bugs.* from bugs " " left join p4dti_bugs using (bug_id) " # what replication " where (bugs.delta_ts >= %s " # (recently changed " or bugs.creation_ts >= %s " # or recently created " and p4dti_bugs.rid is null) " # and not replicated) " or (p4dti_bugs.rid = %s " # or replicated by me. " and p4dti_bugs.sid = %s)" % (self.sqlquote(date), self.sqlquote(date), self.sqlquote(self.rid), self.sqlquote(self.sid))), "all bugs since '%s'" % date) for bug in bugs: bug['longdesc'] = self.bug_get_longdesc(bug) return bugs def changed_bugs_since(self, date): # Find bugs new, touched, or changed (by someone other than # this replicator) since the given date, which are not # being replicated by any other replicator. # We exclude changes which have the same timestamp as the # current replication; they will get picked up by the next # replication. This avoids these changes being replicated by # two consecutive replications (which causes an overwrite). # See job000235. NB 2001-03-01. However, it causes # job000337. # We do this by combining the results of three SELECTs. # These results are disjoint. We could almost certainly # do it in a smaller number of SELECTs. # First, bugs which have been created since the date (but not # by migration by me from a new Perforce job), which are not # being replicated by any other replicator. new = self.fetch_rows_as_list_of_dictionaries( ("select bugs.* from bugs " " left join p4dti_bugs using (bug_id) " # what replication " where bugs.creation_ts >= %s " # recent timestamp " and bugs.creation_ts < %s " # NOT just now " and (p4dti_bugs.rid is null " # NOT replicated " or (p4dti_bugs.rid = %s " # or replicated by me. " and p4dti_bugs.sid = %s " " and p4dti_bugs.migrated is null))" % # but not migrated by me. (self.sqlquote(date), self.sqlquote(self.replication), self.sqlquote(self.rid), self.sqlquote(self.sid))), "new bugs since '%s'" % date) # Next, bugs which are not new but have been touched since the # date, but not changed, (no matching rows in bugs_activity), # which are not being replicated by any other replicator. # # Note that we have to specifically exclude bugs which we have # just migrated, as the migration might set creation_ts. touched = self.fetch_rows_as_list_of_dictionaries( ("select bugs.* from bugs " " left join p4dti_bugs using (bug_id) " # what replication " left join bugs_activity " # what activity " on (bugs.delta_ts <= bugs_activity.bug_when and " # recently " bugs.bug_id = bugs_activity.bug_id) " # on this bug " where bugs.delta_ts >= %s " # recent timestamp " and bugs.delta_ts < %s " # NOT just now " and creation_ts < %s " # NOT brand new " and bugs_activity.fieldid is null" # NO recent activity " and (p4dti_bugs.rid is null " # NOT replicated " or (p4dti_bugs.rid = %s " # or replicated by me. " and p4dti_bugs.sid = %s)) " " and (p4dti_bugs.migrated is null " # NOT migrated lately " or p4dti_bugs.migrated < %s) " % (self.sqlquote(date), self.sqlquote(self.replication), self.sqlquote(date), self.sqlquote(self.rid), self.sqlquote(self.sid), self.sqlquote(date))), "bugs touched since '%s'" % date) # Next, bugs which have been changed since the date, by # someone other than me, which are not being replicated by # any other replicator. changed = self.fetch_rows_as_list_of_dictionaries( ("select bugs.* from bugs, bugs_activity ba " # bug activity "left join p4dti_bugs using (bug_id) " # what replication "left join p4dti_bugs_activity pba " # what replication activity " on (ba.bug_id = pba.bug_id and " # by me " ba.bug_when = pba.bug_when and " " ba.who = pba.who and " " ba.fieldid = pba.fieldid and " " ba.oldvalue = pba.oldvalue and " " ba.newvalue = pba.newvalue and " " pba.rid = %s and " " pba.sid = %s) " " where ba.bug_when >= %s " # recent bug activity " and ba.bug_when < %s " # but not too recent " and bugs.bug_id = ba.bug_id " # on this bug " and pba.rid is null " # NO recent activity by me " and (p4dti_bugs.rid is null " # NOT replicated " or (p4dti_bugs.rid = %s " # or replicated by me " and p4dti_bugs.sid = %s))" " and (bugs.creation_ts < %s or " # NOT new, or newly " p4dti_bugs.migrated is not null) " # migrated " group by bugs.bug_id " % # each bug only once (self.sqlquote(self.rid), self.sqlquote(self.sid), self.sqlquote(date), self.sqlquote(self.replication), self.sqlquote(self.rid), self.sqlquote(self.sid), self.sqlquote(date))), "changed bugs since '%s'" % date) bugs = new + touched + changed for bug in bugs: bug['longdesc'] = self.bug_get_longdesc(bug) return bugs def bug_from_bug_id(self, bug_id): bug = self.fetch_one_row_as_dictionary( "select * from bugs where bug_id = %d;" % bug_id, "bug id %d" % bug_id) bug['longdesc'] = self.bug_get_longdesc(bug) return bug 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 products(self): rows = self.fetch_rows_as_list_of_dictionaries( "select * from products;", "list of products") products={} for row in rows: products[row['product']] = row return products def versions_of_product(self, product): rows = self.fetch_rows_as_list_of_lists ( "select value from versions where program=%s" % self.sqlquote(product), "versions of product '%s'" % product) return map(lambda x:x[0], rows) def components_of_product(self, product): rows = self.fetch_rows_as_list_of_dictionaries ( "select * from components where program=%s" % self.sqlquote(product), "components of product '%s'" % product) components={} for row in rows: components[row['value']] = row return components def product_group(self, product): return self.fetch_at_most_one_row_as_dictionary( "select * from groups " " where name = %s and " " isbuggroup = 1" % self.sqlquote(product), "group for product %s" % product) 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 email_from_userid(self, user): row = self.select_one_row("select login_name from profiles where" " userid = %d;" % user, "email for user %d" % user) return row[0] def userid_from_email(self, email): row = self.select_at_most_one_row("select userid from profiles where" " login_name = %s;" % self.sqlquote(email), "id for user %s" % email) if row == None: return row else: return row[0] def real_name_from_userid(self, user): row = self.select_one_row("select realname from profiles where" " userid = %d;" % user, "real name 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) # Figure out cc's to give to processmail def ccs(self, bug_id): ccrows = self.fetch_rows_as_list_of_lists( "select login_name from cc,profiles" " where cc.bug_id = %d" " and profiles.userid = cc.who" % bug_id, "ccs for bug %d" % bug_id) ccs = map(lambda x:x[0], ccrows) return '"' + string.join(ccs,',') + '"' # Figure out the list of additional bug id's to give to processmail. # This is less complex than in Bugzilla's process_bug.cgi because the # replicator doesn't write to the dependencies table. def dependency_bugs(self, bug_id): blocked = self.fetch_rows_as_list_of_lists( "select blocked from dependencies where dependson = %d" % bug_id, "blocked bugs for bug %d" % bug_id) blocking = self.fetch_rows_as_list_of_lists( "select dependson from dependencies where blocked = %d" % bug_id, "blocking bugs for bug %d" % bug_id) return map(lambda x: x[0], blocked + blocking) def defer_processmail(self, args): argstring = string.join(args,' ') command = ("cd %s; ./processmail %s > /dev/null" % (self.bugzilla_directory, argstring)) self.processmails.append(command) def processmail(self, bug_id, user): if self.bugzilla_directory == None: return who = self.email_from_userid(user) self.defer_processmail(['-forcecc', self.ccs(bug_id), str(bug_id), who]) dependency_bugs = self.dependency_bugs(bug_id) for bug in dependency_bugs: self.defer_processmail([str(bug), who]) def clear_processmails(self): self.processmails = [] def invoke_processmails(self): for command in self.processmails: # "Running command '%s'." self.log(104, command) os.system(command) ### Operations on replications (table p4dti_replications) def now(self): self.execute("select now();") value = self.fetchall() return value[0][0] # If there are no replications, in the replications table, insert a # record whose 'end' is the date given by start_date. That is, # pretend that we last did a replication on start_date. This # ensures that (a) when you run the replicator for the first time, # all issues changed since the start date get replicated (see # job000355), and (b) the replications table is never empty and we # always have a valid self.replication (see job000221). def first_replication(self, start_date): date = self.latest_complete_replication_no_checking() if date == None: self.insert_row_rid_sid('p4dti_replications', { 'start': start_date, 'end': start_date }) self.replication = start_date else: self.replication = date 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): assert self.replication != None self.update_row_rid_sid('p4dti_replications', {'end' : ''}, 'start = %s and end = 0' % self.sqlquote(self.replication)) # clean out old complete replication records from the # p4dti_replications table (job000236). self.delete_rows_rid_sid('p4dti_replications', 'end != 0 and ' 'end < date_sub(now(), INTERVAL 1 HOUR)') 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_no_checking(self): return self.select_one_row( "select max(start) from p4dti_replications where " " rid = %s and " " sid= %s and " " end != 0;" % (self.sqlquote(self.rid), self.sqlquote(self.sid)), "select latest complete replication")[0] # Start time of last complete replication. def latest_complete_replication(self): start = self.latest_complete_replication_no_checking() if start == None: # "Nothing in p4dti_replications table: database corrupted?" raise error, catalog.msg(122) return start # Ensure that schema version 0 is canonical. We need to do this # because we had two different schema both with no schema version. # See [NB 2000-11-14, 5.1] for details. def ensure_schema_version_0(self): # Do we have a schema from before release 1.0.2? replications_indexes = self.fetch_rows_as_list_of_dictionaries( "show index from p4dti_replications", "Getting indexes for the p4dti_replications table.") for i in replications_indexes: if i['Column_name'] == 'end': # We're in release 1.0.2 or later. return # "Your P4DTI/Bugzilla schema is prior to release 1.0.2. # Altering tables to upgrade schema to release 1.0.2." self.log(121) for alteration in ['alter table p4dti_bugs' ' add index(bug_id)', 'alter table p4dti_fixes' ' drop index bug_id,' ' drop index changelist,' ' add unique (bug_id, changelist, rid, sid),' ' add index (bug_id)', 'alter table p4dti_replications' ' drop index rid,' ' add unique (start, rid, sid),' ' add index (rid, sid),' ' add index (end)']: self.execute(alteration) # schema_upgrade maps schema version to a list of SQL command # which will bring the schema up to date, as explained in [NB # 2000-11-14, 5]. schema_upgrade = { '0': ['alter table p4dti_bugs' ' drop action', 'alter table p4dti_replications' ' add id int not null auto_increment,' ' drop index start,' ' add unique (start, rid, sid, id),' ' add unique (id)'], '1': ['alter table p4dti_bugs' ' add migrated datetime,' ' drop replication', 'alter table p4dti_changelists' ' drop replication', 'alter table p4dti_fixes' ' drop replication', 'alter table p4dti_filespecs' ' drop replication'], } # The set_config() method found that the schema version was out of # date, and called this method to bring the schema up to date. # The existing schema version is old_version (None if no version # was found) and the version to upgrade to is new_version (this is # always self.schema_version). def change_schema(self, old_version, new_version): assert new_version == self.schema_version # If no old schema version, make it version '0'. if old_version == None: self.ensure_schema_version_0() old_version = '0' if not self.schema_upgrade.has_key(old_version): # "Unknown or future P4DTI/Bugzilla schema version %s # detected." raise error, catalog.msg(120, old_version) # "Old P4DTI/Bugzilla schema version %s detected; # altering tables to upgrade to schema version %s." self.log(119, (old_version, new_version)) for sql in self.schema_upgrade[old_version]: self.execute(sql) # Configuration parameters which we pass through the database # to Bugzilla. def get_config(self) : dict = {} rows = self.fetch_rows_as_list_of_lists( "select config_key, config_value from p4dti_config where " " rid = %s and " " sid= %s;" % (self.sqlquote(self.rid), self.sqlquote(self.sid)), 'p4dti configuration') for row in rows: dict[row[0]] = row[1] return dict def add_config(self, key, value): self.insert_row_rid_sid('p4dti_config', {'config_key' : key, 'config_value' : value}) def update_config(self, key, value) : self.update_row_rid_sid('p4dti_config', {'config_value' : value}, ('config_key = %s' % self.sqlquote(key))) def delete_config(self, key): self.delete_rows_rid_sid('p4dti_config', ('config_key = %s' % self.sqlquote(key))) def set_config(self, dict) : old_config = self.get_config() old_schema = old_config.get('schema_version') if dict['schema_version'] != old_schema: self.change_schema(old_schema, dict['schema_version']) # Reload the old config in case the schema change # changed it. old_config=self.get_config() for key, value in dict.items(): if old_config.has_key(key) : if old_config[key] != value : self.update_config(key, value) del old_config[key] else: self.add_config(key, value) for key in old_config.keys(): self.delete_config(key) # Locking def lock_tables(self) : self.execute("lock tables " " bugs write, " " bugs_activity write, " " longdescs 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, " " cc read, " " dependencies read, " " products read, " " components read, " " versions read, " " bugs_activity as ba read, " " p4dti_bugs_activity as pba read;") def unlock_tables(self) : self.execute("unlock tables;") # A. References # # [NB 2000-11-14] "Bugzilla database schema extensions for integration # with Perforce"; Nick Barnes; Ravenbrook Limited; # ; # 2000-11-14. # # # 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). # # 2000-12-08 NB Add p4dti_config table and code to manipulate it. This gets # configuration from the replicator to Bugzilla. # # 2000-12-13 NB Stop replicating historical bugs, and add code to find # bugs which have been 'touched'. Put output through the logger. Add # indices to the tables. # # 2000-12-15 NB Added verbosity control. # # 2001-01-11 NB Added MySQL type parsing code so that we can do # replicated_fields. Also take code to make the MySQL connection # out to configure_bugzilla.py so we only make one connection when # starting up. # # 2001-01-12 NB Added longdesc support. # # 2001-01-15 NB Defaults for date types don't work. # # 2001-01-22 NB Fix job000184, if database isn't called 'bugs'. # # 2001-01-26 NB Added processmail support and tidied up our response to # a zero-row select. # # 2001-02-08 NB Added some checking. # # 2001-02-20 GDR Removed unused 'dict' argument from delete_rows_rid_sid, to # fix job000222. # # 2001-02-23 NB Made error messages more regular (job000227). # # 2001-03-01 NB Fixes for job000235, job000236, job000238. # # 2001-03-02 NB Fix for job000241 (convert_type for other MySQL versions). # # 2001-03-02 RB Transferred copyright to Perforce under their license. # # 2001-03-12 GDR Use new message classes when logging debug messages. Fixed # bug in error reporting for fetch_at_most_one_row_as_dictionary(). # # 2001-03-13 GDR Removed verbose parameter (made redundant by log_level). # Removed action field from table p4dti_bugs (since conflict resolution is now # always immediate). # # 2001-03-15 GDR Get configuration from module. # # 2001-03-29 NB Fix for job000283 (non-uniqueness in p4dti_replications index). # # 2001-04-10 NB Fix for job000291 (new message; add to catalog). # # 2001-04-23 NB Initial code to add bugs to Bugzilla. # # 2001-05-09 NB Now able to add bugs to Bugzilla. Also fixed job000262. # # 2001-06-26 NB Add functions for deleting a bug (needed when creating # a new bug from a new Perforce job fails half-way). Also added a # 'migrate' field to the p4dti_bugs table, so we can tell whether and # when a bug was migrated from Perforce. # # 2001-06-27 NB split all_bugs_since into all_bugs_since and # changed_bugs_since, to correctly pick up or ignore migrated bugs # accordingly. This also fixes an obscure bug which could sometimes # miss bugs, and thinking about it revealed job000339. # # 2001-06-27 NB change logic of all_issues_since: it needs to return # all issues replicated by this replicator regardless of their # timestamps. # # 2001-07-09 NB Only set creation_ts on a new bug if it's not already # set. # # 2001-07-09 NB Added job_url config item. # # 2001-07-13 NB Workaround for MySQL bug (left join with non-null # datetime field) # # # 2001-07-16 NB Old schema versions get upgraded. Made schema_version # a string (so we can have schema_version='1.2.3.4' if we need it). # Delete config entries from the p4dti_config table if necessary. # # 2001-07-16 GDR Ensured that there's always a row in the replications # table. On the first replication, this pretends that the last # replication was on the start_date. all_bugs_since() says "fieldid is # null" rather than "bug_when is null" to work around bug in MySQL. # # 2001-07-19 NB Always strip longdesc records on the way in or out of # the database. # # 2001-07-19 NB Because we were setting creation_ts on migration, to a # time in the (usually recent) past, the SQL to find recently touched # bugs was always returning newly migrated bugs as well, and # generating conflicts (job000016 means that they show up as changed # in Perforce too). # # 2001-07-25 NB job000375: non-empty blank lines must be cleared when # reading from Bugzilla, or Perforce will do it for us and confuse the # replicator.