def testSingleRowEmpty(self):
try:
cur = TestEmptyCursor()
ppghelper.singleRowSql(cur,"")
assert False, "must raise SQLDidNotReturnSingleRow"
except ppghelper.SQLDidNotReturnSingleRow,e:
pass
def testSingleRowEmpty(self):
try:
cur = TestEmptyCursor()
ppghelper.singleRowSql(cur,"")
assert False, "must raise SQLDidNotReturnSingleRow"
except ppghelper.SQLDidNotReturnSingleRow,e:
pass
def insert_or_update_bug_in_database(bugId, statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla, signatureSetFromBugzilla,
databaseCursor, signatureFoundInReportsFunction=signature_is_found):
try:
if len(signatureSetFromBugzilla) == 0:
databaseCursor.execute("delete from bugs where id = %s", (bugId,))
databaseCursor.connection.commit()
logger.info("rejecting bug (no signatures): %s - %s, %s", bugId, statusFromBugzilla, resolutionFromBugzilla)
else:
useful = False
insertMade = False
try:
statusFromDatabase, resolutionFromDatabase, shortDescFromDatabase = psy.singleRowSql(databaseCursor, "select status, resolution, short_desc from bugs where id = %s", (bugId,))
if statusFromDatabase != statusFromBugzilla or resolutionFromDatabase != resolutionFromBugzilla or shortDescFromDatabase != shortDescFromBugzilla:
databaseCursor.execute("update bugs set status = %s, resolution = %s, short_desc = %s where id = %s", (statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla, bugId))
logger.info("bug status updated: %s - %s, %s", bugId, statusFromBugzilla, resolutionFromBugzilla)
useful = True
listOfSignaturesFromDatabase = [x[0] for x in psy.execute(databaseCursor, "select signature from bug_associations where bug_id = %s", (bugId,))]
for aSignature in listOfSignaturesFromDatabase:
if aSignature not in signatureSetFromBugzilla:
databaseCursor.execute("delete from bug_associations where signature = %s and bug_id = %s", (aSignature, bugId))
logger.info ('association removed: %s - "%s"', bugId, aSignature)
useful = True
except psy.SQLDidNotReturnSingleRow:
databaseCursor.execute("insert into bugs (id, status, resolution, short_desc) values (%s, %s, %s, %s)", (bugId, statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla))
insertMade = True
listOfSignaturesFromDatabase = []
for aSignature in signatureSetFromBugzilla:
if aSignature not in listOfSignaturesFromDatabase:
if signatureFoundInReportsFunction(aSignature, databaseCursor):
databaseCursor.execute("insert into bug_associations (signature, bug_id) values (%s, %s)", (aSignature, bugId))
logger.info ('new association: %s - "%s"', bugId, aSignature)
useful = True
else:
logger.info ('rejecting association (no reports with this signature): %s - "%s"', bugId, aSignature)
if useful:
databaseCursor.connection.commit()
if insertMade:
logger.info('new bug: %s - %s, %s, "%s"', bugId, statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla)
else:
databaseCursor.connection.rollback()
if insertMade:
logger.info('rejecting bug (no useful information): %s - %s, %s, "%s"', bugId, statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla)
else:
logger.info('skipping bug (no new information): %s - %s, %s, "%s"', bugId, statusFromBugzilla, resolutionFromBugzilla, shortDescFromBugzilla)
except Exception, x:
databaseCursor.connection.rollback()
raise
def testSingleRowMulti(self):
try:
cur = TestMultiCursor(numRows=5, numCols=1)
assert ["Row 0, Column 0"] == ppghelper.singleRowSql(cur,"")
except Exception, e:
assert False, "must not raise this exception"
def testSingleRowSingle(self):
try:
cur = TestSingleCursor()
assert ["Row 0, Column 0"] == ppghelper.singleRowSql(cur,"")
except Exception, e:
assert False, "must not raise this exception"
def testSingleRowMulti(self):
try:
cur = TestMultiCursor(numRows=5, numCols=1)
assert ["Row 0, Column 0"] == ppghelper.singleRowSql(cur,"")
except Exception, e:
assert False, "must not raise this exception"
def testSingleRowSingle(self):
try:
cur = TestSingleCursor()
assert ["Row 0, Column 0"] == ppghelper.singleRowSql(cur,"")
except Exception, e:
assert False, "must not raise this exception"
def migrate (config, logger):
oneWeek = dt.timedelta(7)
databaseConnection, databaseCursor = socorro_schema.connectToDatabase(config, logger)
#logger.info ("create insert trigger")
#try:
#databaseCursor.execute("CREATE LANGUAGE plpythonu")
#except:
#databaseConnection.rollback()
#socorro_schema.ParititioningTriggerScript(logger=logger, userName=config.databaseUserName).create(databaseCursor)
#databaseConnection.commit()
socorro_schema.JobsTable(logger=logger).updateDefinition(databaseCursor)
databaseConnection.commit()
# get range for new partitions
#logger.info ("getting min max date information from reports")
#minDate, maxDate = socorro_psy.singleRowSql(databaseCursor, """select
#min(date_processed) as minDate,
#max(date_processed) as maxDate
#from reports""")
#weekIteratorGenerator = socorro_schema.iterateBetweenDatesGeneratorCreator(minDate, maxDate)
# delete everything older than 120 days
#oneHundredTwentyDaysEarlier = maxDate - dt.timedelta(120)
#logger.info("deleting stuff older than 120 days of the max date: %s - %s", maxDate, oneHundredTwentyDaysEarlier)
#databaseCursor.execute("delete from reports where date_processed < timestamp with time zone '%4d-%02d-%02d'" % (oneHundredTwentyDaysEarlier.year,
# oneHundredTwentyDaysEarlier.month,
# oneHundredTwentyDaysEarlier.day))
#databaseConnection.commit()
masterTableClassList = [ socorro_schema.ExtensionsTable, socorro_schema.FramesTable, socorro_schema.DumpsTable, socorro_schema.ReportsTable ]
masterTableList = [x(logger=logger, userName=config.databaseUserName) for x in masterTableClassList]
logger.info ("disconnect all old partitions")
oldPartitionLists = {}
for aTable in masterTableList:
logger.info(" %s", aTable.name)
oldPartitionLists[aTable.name] = oldPartitionList = getOldPartitionList(databaseCursor, aTable.name)
disconnectPartition(databaseCursor, oldPartitionList, aTable.name, logger)
logger.info ("drop and recreate all old master tables")
for aTable in masterTableList:
logger.info(" %s", aTable.name)
aTable.drop(databaseCursor)
aTable.create(databaseCursor)
databaseConnection.commit()
logger.info ("spill old partitions into new partitions")
for oldReportsPartitionName, oldextensionsPartitionName, oldFramesPartitionName, oldDumpsPartitionName in zip(oldPartitionLists["reports"], oldPartitionLists["extensions"], oldPartitionLists["frames"], oldPartitionLists["dumps"]):
logger.info(" %s, %s, %s, %s", oldReportsPartitionName, oldextensionsPartitionName, oldFramesPartitionName, oldDumpsPartitionName)
oldPartitionNames = {"reports": oldReportsPartitionName,
"extensions": oldextensionsPartitionName,
"frames": oldFramesPartitionName,
"dumps": oldDumpsPartitionName
}
#logger.info("adding some handy indexes")
#try:
#databaseCursor.execute("create index %(reports)s_date_processed_key on %(reports)s (date_processed)" % oldPartitionNames)
#except:
#databaseConnection.rollback()
#try:
#databaseCursor.execute("create index %(extensions)s_report_id_key on %(extensions)s (report_id)" % oldPartitionNames)
#except:
#databaseConnection.rollback()
#try:
#databaseCursor.execute("create index %(frames)s_report_id_key on %(frames)s (report_id)" % oldPartitionNames)
#databaseCursor.execute("analyze %(frames)s" % oldPartitionNames)
#except:
#databaseConnection.rollback()
#try:
#databaseCursor.execute("create index %(dumps)s_report_id_key on %(dumps)s (report_id)" % oldPartitionNames)
#databaseCursor.execute("analyze %(dumps)s" % oldPartitionNames)
#except:
#databaseConnection.rollback()
partitionUniqueId = oldReportsPartitionName[len("reports_"):]
#minDate, maxDate = socorro_psy.singleRowSql(databaseCursor, """select
#min(date_processed) as minDate,
#max(date_processed) as maxDate
#from %s""" % oldReportsPartitionName)
#if minDate is None or maxDate is None:
#logger.info("this table is empty - delete corresponding table partitions")
#for aPartitionName in [x.name for x in masterTableList]:
#try:
#partitionName = "%s_%s" % (aPartitionName, partitionUniqueId)
#databaseCursor.execute("drop table %s cascade" % partitionName)
#databaseCursor.connection.commit()
#except Exception, x:
#logger.info(str(x))
#logger.info("%s doesn't exist - can't drop it", partitionName)
#databaseCursor.connection.rollback()
#continue
#dateRangeIterator = socorro_schema.iterateBetweenDatesGeneratorCreator(minDate, maxDate)
##databaseConnection, databaseCursor = socorro_schema.connectToDatabase(config, logger)
#def wrapperIter():
#for x in list(dateRangeIterator())[::-1][:numberOfWeeksToPartition]:
#yield x
#for aPartitionedTable in masterTableList[::-1]:
#logger.info(" %s", aPartitionedTable.name)
#aPartitionedTable.createPartitions(databaseCursor, wrapperIter)
#for minPartitionDate, maxPartitionDate in wrapperIter():
#newPartitionNames = {}
#for aPartitionedTable in masterTableList:
#newPartitionNames[aPartitionedTable.name] = aPartitionedTable.partitionCreationParameters((minPartitionDate, maxPartitionDate))["partitionName"]
#sqlParameters = { "startDate":str(minPartitionDate)[:10],
#"endDate":str(maxPartitionDate)[:10],
#"newPartitionName":newPartitionNames["reports"],
#"oldPartitionName":oldPartitionNames["reports"],
#"oldReportsPartition":oldPartitionNames["reports"],
#"newReportsPartition":newPartitionNames["reports"]
#}
#databaseCursor.execute("""insert into %(newPartitionName)s
#(id, uuid, client_crash_date, date_processed, product, version, build, url, install_age, last_crash, uptime, email, build_date, user_id, user_comments, started_datetime, completed_datetime, success, truncated, processor_notes, app_notes, distributor, distributor_version)
#(select
#id, uuid, date, date_processed, product, version, build, url, install_age, last_crash, uptime, email, build_date, user_id, comments, starteddatetime, completeddatetime, success, truncated, message, NULL, NULL, NULL
#from %(oldPartitionName)s
#where TIMESTAMP without time zone '%(startDate)s' <= date_processed and date_processed < TIMESTAMP without time zone '%(endDate)s')""" % sqlParameters)
#databaseConnection.commit()
#logger.info("is this analyze necessary?")
#databaseCursor.execute('analyze %(newPartitionName)s' % sqlParameters)
#databaseConnection.commit()
##sys.exit()
#sqlParameters["newPartitionName"] = newPartitionNames["extensions"]
#sqlParameters["oldPartitionName"] = oldPartitionNames["extensions"]
#databaseCursor.execute("""insert into %(newPartitionName)s
#(select
#e.report_id,
#r.date_processed,
#e.extension_key,
#e.extension_id,
#e.extension_version
#from
#%(oldPartitionName)s e join %(newReportsPartition)s r on e.report_id = r.id)
#""" % sqlParameters)
#databaseConnection.commit()
#sqlParameters["newPartitionName"] = newPartitionNames["frames"]
#sqlParameters["oldPartitionName"] = oldPartitionNames["frames"]
#databaseCursor.execute("""insert into %(newPartitionName)s
#(select
#f.report_id,
#r.date_processed,
#f.frame_num,
#f.signature
#from
#%(oldPartitionName)s f join %(newReportsPartition)s r on f.report_id = r.id)""" % sqlParameters)
#databaseConnection.commit()
#sqlParameters["newPartitionName"] = newPartitionNames["dumps"]
#sqlParameters["oldPartitionName"] = oldPartitionNames["dumps"]
#databaseCursor.execute("""insert into %(newPartitionName)s
#(select
#d.report_id,
#r.date_processed,
#d.data
#from
#%(oldPartitionName)s d join %(newReportsPartition)s r on d.report_id = r.id)""" % sqlParameters)
#databaseConnection.commit()
#databaseCursor.execute("""delete from %(oldReportsPartition)s
#where
#TIMESTAMP without time zone '%(startDate)s' <= date_processed and date_processed < TIMESTAMP without time zone '%(endDate)s'
#""" % sqlParameters)
logger.info("Now take the rest of the original partition as a big steaming lump")
minDate, maxDate = socorro_psy.singleRowSql(databaseCursor, """select
min(date_processed) as minDate,
max(date_processed) as maxDate
from %s""" % oldReportsPartitionName)
if minDate is None or maxDate is None:
logger.info("There's nothing left in %s - deleting tables", oldReportsPartitionName)
for aPartitionName in [x.name for x in masterTableList]:
try:
partitionName = "%s_%s" % (aPartitionName, partitionUniqueId)
databaseCursor.execute("drop table %s cascade" % partitionName)
databaseCursor.connection.commit()
except Exception, x:
logger.info(str(x))
logger.info("%s doesn't exist - can't drop it", partitionName)
databaseCursor.connection.rollback()
continue
mondayBeforeMaxDate = maxDate - dt.timedelta(maxDate.weekday())
partitionNameDate = "%4d%02d%02d" % (mondayBeforeMaxDate.year, mondayBeforeMaxDate.month, mondayBeforeMaxDate.day)
mondayAfterMaxDate = mondayBeforeMaxDate + oneWeek
newReportsPartitionName = "reports_%s" % partitionNameDate
logger.info("rename %s to %s", oldReportsPartitionName, newReportsPartitionName)
databaseCursor.execute("alter table %s rename to %s" % (oldReportsPartitionName, newReportsPartitionName))
logger.info("add new columns to %s", newReportsPartitionName)
columnNameTypeDictionary = socorro_pg.columnNameTypeDictionaryForTable(newReportsPartitionName, databaseCursor)
if 'client_crash_date' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s RENAME COLUMN date TO client_crash_date""" % newReportsPartitionName)
if 'started_datetime' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s RENAME COLUMN starteddatetime TO started_datetime""" % newReportsPartitionName)
if 'completed_datetime' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s RENAME COLUMN completeddatetime TO completed_datetime""" % newReportsPartitionName)
if 'user_comments' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s RENAME COLUMN comments TO user_comments""" % newReportsPartitionName)
databaseCursor.execute("""ALTER TABLE %s ALTER COLUMN user_comments TYPE character varying(1024)""" % newReportsPartitionName)
if 'app_notes' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s ADD COLUMN app_notes character varying(1024)""" % newReportsPartitionName)
if 'distributor' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s ADD COLUMN distributor character varying(20)""" % newReportsPartitionName)
if 'distributor_version' not in columnNameTypeDictionary:
databaseCursor.execute("""ALTER TABLE %s ADD COLUMN distributor_version character varying(20)""" % newReportsPartitionName)
databaseCursor.execute("""ALTER TABLE %s rename column message to processor_notes""" % newReportsPartitionName)
logger.info("replace & update indexes for %s", newReportsPartitionName)
reportsParitionIndexList = socorro_pg.indexesForTable(newReportsPartitionName, databaseCursor)
if "reports_part1_pkey" in reportsParitionIndexList:
databaseCursor.execute("ALTER INDEX reports_part1_pkey RENAME TO %s_pkey" % newReportsPartitionName)
if "reports_part1_uuid_key" in reportsParitionIndexList:
databaseCursor.execute("ALTER INDEX reports_part1_uuid_key RENAME TO %s_uuid_key" % newReportsPartitionName)
if "idx_reports_part1_date" in reportsParitionIndexList:
databaseCursor.execute("ALTER INDEX idx_reports_part1_date RENAME TO %s_date_key" % newReportsPartitionName)
#if "reports_part1_date_processed_key" in reportsParitionIndexList:
# databaseCursor.execute("ALTER INDEX reports_part1_date_processed_key RENAME TO %s_date_processed_key" % newReportsPartitionName)
databaseCursor.execute("CREATE INDEX %s_signature_key ON %s (signature)" % (newReportsPartitionName, newReportsPartitionName))
databaseCursor.execute("CREATE INDEX %s_url_key ON %s (url)" % (newReportsPartitionName, newReportsPartitionName))
databaseCursor.execute("CREATE INDEX %s_signature_date_key ON %s (signature, date_processed)" % (newReportsPartitionName, newReportsPartitionName))
logger.info("adjust constraints for %s", newReportsPartitionName)
for constraintName, constraintType in socorro_pg.constraintsAndTypeForTable(newReportsPartitionName, databaseCursor):
if constraintType == 'c':
databaseCursor.execute("alter table %s drop constraint %s" % (newReportsPartitionName, constraintName))
startDate = "%4d-%02d-%02d" % (minDate.year, minDate.month, minDate.day)
endDate = "%4d-%02d-%02d" % (mondayAfterMaxDate.year, mondayAfterMaxDate.month, mondayAfterMaxDate.day)
databaseCursor.execute("alter table %s add constraint %s_date_check CHECK (TIMESTAMP without time zone '%s' <= date_processed and date_processed < TIMESTAMP without time zone '%s')" % (newReportsPartitionName, newReportsPartitionName, startDate, endDate))
logger.info("reconnect %s to report master", newReportsPartitionName)
databaseCursor.execute("alter table %s inherit reports" % newReportsPartitionName)
databaseCursor.connection.commit()
# rename partitions
for aMasterTableName in ("dumps", "frames", "extensions"):
oldPartitionName = "%s_%s" % (aMasterTableName, partitionUniqueId)
newPartitionName = "%s_%s" % (aMasterTableName, partitionNameDate)
logger.info("renaming %s to %s", oldPartitionName, newPartitionName)
try:
databaseCursor.execute("alter table %s rename to %s" % (oldPartitionName, newPartitionName))
except:
logger.info("%s did not exist - skipping", oldPartitionName)
databaseCursor.connection.rollback()
continue
logger.info("adding date_processed column to %s", newPartitionName)
databaseCursor.execute("alter table %s add column date_processed timestamp without time zone" % newPartitionName)
databaseCursor.execute("""update %s
set date_processed = (select
date_processed
from %s
where %s.report_id = %s.id)""" % (newPartitionName, newReportsPartitionName, newPartitionName, newReportsPartitionName))
logger.info("replace & update indexes for %s partition", newPartitionName)
indexList = socorro_pg.indexesForTable(newPartitionName, databaseCursor)
if "%s_pkey" % oldPartitionName in indexList:
databaseCursor.execute("alter index %s_pkey rename to %s_pkey" % (oldPartitionName, newPartitionName))
logger.info("adjust constraints for %s partition", newPartitionName)
for constraintName, constraintType in socorro_pg.constraintsAndTypeForTable(newPartitionName, databaseCursor):
print constraintName, constraintType
if constraintType in 'cf':
databaseCursor.execute("alter table %s drop constraint %s" % (newPartitionName, constraintName))
databaseCursor.execute("alter table %s add constraint %s_date_check CHECK (TIMESTAMP without time zone '%s' <= date_processed and date_processed < TIMESTAMP without time zone '%s')" % (newPartitionName, newPartitionName, startDate, endDate))
databaseCursor.execute("alter table %s add constraint %s_report_id_fkey foreign key (report_id) references %s (id) on delete cascade" % (newPartitionName, newPartitionName, newReportsPartitionName))
logger.info("reconnect %s partition to master", newPartitionName)
databaseCursor.execute("alter table %s inherit %s" % (newPartitionName, aMasterTableName))
databaseCursor.connection.commit()
logger.info("end inner loop")
def insert_or_update_bug_in_database(
bugId,
statusFromBugzilla,
resolutionFromBugzilla,
shortDescFromBugzilla,
signatureSetFromBugzilla,
databaseCursor,
signatureFoundInReportsFunction=signature_is_found):
try:
if len(signatureSetFromBugzilla) == 0:
databaseCursor.execute("delete from bugs where id = %s", (bugId, ))
databaseCursor.connection.commit()
logger.info("rejecting bug (no signatures): %s - %s, %s", bugId,
statusFromBugzilla, resolutionFromBugzilla)
else:
useful = False
insertMade = False
try:
statusFromDatabase, resolutionFromDatabase, shortDescFromDatabase = psy.singleRowSql(
databaseCursor,
"select status, resolution, short_desc from bugs where id = %s",
(bugId, ))
if statusFromDatabase != statusFromBugzilla or resolutionFromDatabase != resolutionFromBugzilla or shortDescFromDatabase != shortDescFromBugzilla:
databaseCursor.execute(
"update bugs set status = %s, resolution = %s, short_desc = %s where id = %s",
(statusFromBugzilla, resolutionFromBugzilla,
shortDescFromBugzilla, bugId))
logger.info("bug status updated: %s - %s, %s", bugId,
statusFromBugzilla, resolutionFromBugzilla)
useful = True
listOfSignaturesFromDatabase = [
x[0] for x in psy.execute(
databaseCursor,
"select signature from bug_associations where bug_id = %s",
(bugId, ))
]
for aSignature in listOfSignaturesFromDatabase:
if aSignature not in signatureSetFromBugzilla:
databaseCursor.execute(
"delete from bug_associations where signature = %s and bug_id = %s",
(aSignature, bugId))
logger.info('association removed: %s - "%s"', bugId,
aSignature)
useful = True
except psy.SQLDidNotReturnSingleRow:
databaseCursor.execute(
"insert into bugs (id, status, resolution, short_desc) values (%s, %s, %s, %s)",
(bugId, statusFromBugzilla, resolutionFromBugzilla,
shortDescFromBugzilla))
insertMade = True
listOfSignaturesFromDatabase = []
for aSignature in signatureSetFromBugzilla:
if aSignature not in listOfSignaturesFromDatabase:
if signatureFoundInReportsFunction(aSignature,
databaseCursor):
databaseCursor.execute(
"insert into bug_associations (signature, bug_id) values (%s, %s)",
(aSignature, bugId))
logger.info('new association: %s - "%s"', bugId,
aSignature)
useful = True
else:
logger.info(
'rejecting association (no reports with this signature): %s - "%s"',
bugId, aSignature)
if useful:
databaseCursor.connection.commit()
if insertMade:
logger.info('new bug: %s - %s, %s, "%s"', bugId,
statusFromBugzilla, resolutionFromBugzilla,
shortDescFromBugzilla)
else:
databaseCursor.connection.rollback()
if insertMade:
logger.info(
'rejecting bug (no useful information): %s - %s, %s, "%s"',
bugId, statusFromBugzilla, resolutionFromBugzilla,
shortDescFromBugzilla)
else:
logger.info(
'skipping bug (no new information): %s - %s, %s, "%s"',
bugId, statusFromBugzilla, resolutionFromBugzilla,
shortDescFromBugzilla)
except Exception, x:
databaseCursor.connection.rollback()
raise
