def testSetupAndTeardownDatabase():
"""
testSetupAndTeardownDatabase():
- test that when we setupDatabase, we get all the expected tables
- test that when we teardownDatabase, we remove all the expected tables
"""
global me
tcon,tcur = schema.connectToDatabase(me.config,me.logger)
tcur.execute("DROP TABLE IF EXISTS %s CASCADE"%','.join(me.expectedTableNames))
tcon.commit()
try:
schema.setupDatabase(me.config,me.logger)
try:
for t in me.expectedTableNames:
# next line raises if the table does not exist
tcur.execute("SELECT count(*) from %s"%t)
tcon.commit()
count = tcur.fetchone()[0]
assert 0 == count
finally:
schema.teardownDatabase(me.config,me.logger)
for t in me.expectedTableNames:
try:
tcur.execute("SELECT count(*) from %s"%t)
assert False, 'Expected table %s does not exist'%t
except psycopg2.ProgrammingError:
tcon.rollback()
except Exception,x:
assert False, 'Expected psycopg2.ProgrammingError, not %s: %s'%(type(x),x)
finally:
tcon.close()
def testConnectToDatabase():
"""
testConnectToDatabase():
- check that we can connect to the database and do something with the connection and cursor provided
"""
global me
tcon,tcur = schema.connectToDatabase(me.config,me.logger)
connection = psycopg2.connect(me.dsn)
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS foo")
connection.commit()
try:
cursor.execute("CREATE TABLE foo (id integer)")
connection.commit()
cursor.execute("SELECT * from foo")
connection.commit()
assert [] == cursor.fetchall()
tcur.execute("INSERT INTO foo (id) values(%s)",(666,))
tcon.commit()
cursor.execute("SELECT * from foo")
assert 666 == cursor.fetchone()[0]
finally:
cursor.execute("DROP TABLE IF EXISTS foo")
connection.commit()
connection.close()
def testSetupAndTeardownDatabase():
"""
testSetupAndTeardownDatabase():
- test that when we setupDatabase, we get all the expected tables
- test that when we teardownDatabase, we remove all the expected tables
"""
global me
tcon, tcur = schema.connectToDatabase(me.config, me.logger)
tcur.execute("DROP TABLE IF EXISTS %s CASCADE" %
','.join(me.expectedTableNames))
tcon.commit()
try:
schema.setupDatabase(me.config, me.logger)
try:
for t in me.expectedTableNames:
if '_enum' in t:
continue
# next line raises if the table does not exist
tcur.execute("SELECT count(*) from %s" % t)
tcon.commit()
count = tcur.fetchone()[0]
assert 0 == count
finally:
schema.teardownDatabase(me.config, me.logger)
for t in me.expectedTableNames:
try:
tcur.execute("SELECT count(*) from %s" % t)
assert False, 'Expected table %s does not exist' % t
except psycopg2.ProgrammingError:
tcon.rollback()
except Exception, x:
assert False, 'Expected psycopg2.ProgrammingError, not %s: %s' % (
type(x), x)
finally:
tcon.close()
def testConnectToDatabase():
"""
testConnectToDatabase():
- check that we can connect to the database and do something with the connection and cursor provided
"""
global me
tcon, tcur = schema.connectToDatabase(me.config, me.logger)
connection = psycopg2.connect(me.dsn)
cursor = connection.cursor()
cursor.execute("DROP TABLE IF EXISTS foo")
connection.commit()
try:
cursor.execute("CREATE TABLE foo (id integer)")
connection.commit()
cursor.execute("SELECT * from foo")
connection.commit()
assert [] == cursor.fetchall()
tcur.execute("INSERT INTO foo (id) values(%s)", (666, ))
tcon.commit()
cursor.execute("SELECT * from foo")
assert 666 == cursor.fetchone()[0]
finally:
cursor.execute("DROP TABLE IF EXISTS foo")
connection.commit()
connection.close()
def removePriorityTables(self,config,logger):
dbCon,dbCur = db_schema.connectToDatabase(config,logger)
priorityTableNames = db_postgresql.tablesMatchingPattern('priority_job_%%',dbCur)
if priorityTableNames:
sql = "DROP TABLE IF EXISTS %s CASCADE;"%(", ".join(priorityTableNames))
dbCur.execute(sql)
dbCon.commit()
else:
logger.info("There were no priority_job tables to close")
def removePriorityTables(self, config, logger):
dbCon, dbCur = db_schema.connectToDatabase(config, logger)
priorityTableNames = db_postgresql.tablesMatchingPattern(
'priority_job_%%', dbCur)
if priorityTableNames:
sql = "DROP TABLE IF EXISTS %s CASCADE;" % (
", ".join(priorityTableNames))
dbCur.execute(sql)
dbCon.commit()
else:
logger.info("There were no priority_job tables to close")
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")
