def importPerson(self, person):
p = person["person"]
if p["role"] == "Contestant":
t = int(p["team-id"])
self.peopleInTeam[t] += 1
self.contestants += 1
query = "UPDATE teams SET contestant%d_id = %%s, contestant%d_name = %%s WHERE team_id = %%s" % (self.peopleInTeam[t],self.peopleInTeam[t])
params = (self.contestants, "%s %s" % (p["first-name"], p["last-name"]), int(p["team-id"]))
cursor = dbConn.cursor()
cursor.execute(query, params)
elif p["role"] == "Coach":
t = int(p["team-id"])
query = "UPDATE teams SET coach_name = %s WHERE team_id = %s"
params = ("%s %s" % (p["first-name"], p["last-name"]), int(p["team-id"]))
cursor = dbConn.cursor()
cursor.execute(query, params)
def importTeam(self, team):
t = team["team"]
query = "INSERT INTO teams (id, team_id, team_name, institution_id, school_name, school_short, country) VALUES (%s,%s,%s,%s,%s,%s,%s)"
params = (int(t["id"]), int(t["id"]), t["name"], int(t["institution-id"]), t["affiliation"], t["affiliation-short-name"], t["nationality"])
cursor = dbConn.cursor()
cursor.execute(query, params)
def loadConfiguration(self):
# Read the list of problem names
self.probKeywords = {}
cursor = dbConn.cursor()
cursor.execute("SELECT problem_id, keyword FROM problem_keywords")
row = cursor.fetchone()
while (row != None):
if (row[0] in self.probKeywords):
self.probKeywords[row[0]].append(row[1].lower())
else:
self.probKeywords[row[0]] = [row[1].lower()]
row = cursor.fetchone()
# get latest known edit times for every team/path
cursor.execute(
"SELECT id, team_id, path, modify_timestamp FROM file_modtime")
row = cursor.fetchone()
while (row != None):
self.lastEditTimes[(row[1], row[2])] = [row[0], row[3], None]
row = cursor.fetchone()
# get existing mapping records for all mapped files.
cursor.execute(
"SELECT id, team_id, path, problem_id, lang_id, override FROM file_to_problem"
)
row = cursor.fetchone()
while (row != None):
self.fileMappings[(int(row[1]),
row[2])] = MappingRec(row[0], row[3], row[4],
row[5], None)
# Old mapping
# 0 -> id, 1 -> problem_id, 2 -> override, 3 -> new_problem_id
row = cursor.fetchone()
# load any team-specific strips.
cursor.execute("SELECT team_id, str FROM team_strips")
row = cursor.fetchone()
while (row != None):
if (row[0] in self.teamStrips):
self.teamStrips[row[0]].append(row[1].lower())
else:
self.teamStrips[row[0]] = [row[1].lower()]
row = cursor.fetchone()
cursor.close()
def initCDS( self ):
"""Initialization that just needs to be done for runs with backups from the CDS.
This is getting kind of ugly."""
# figure out a start time in the format we will get from the CDS.
cursor = dbConn.cursor()
cursor.execute( "SELECT start_time FROM contests ORDER BY id DESC LIMIT 1" )
row = cursor.fetchone()
if ( row == None ):
print("Error: no contest found in the database.")
exit(1)
startTime = time.strftime( "%a, %d %b %Y %H:%M:%S GMT", time.gmtime(0) )
self.teamLastModified = {};
for teamIdx in range( 1, self.lastTeam + 1 ):
self.teamLastModified[ teamIdx ] = startTime;
def loadConfiguration( self ):
# Read the list of problem names
self.probKeywords = {}
cursor = dbConn.cursor()
cursor.execute( "SELECT problem_id, keyword FROM problem_keywords" )
row = cursor.fetchone()
while ( row != None ):
if ( row[ 0 ] in self.probKeywords ):
self.probKeywords[ row[ 0 ] ].append( row[ 1 ].lower() )
else:
self.probKeywords[ row[ 0 ] ] = [ row[ 1 ].lower() ]
row = cursor.fetchone()
# get latest known edit times for every team/path
cursor.execute( "SELECT id, team_id, path, modify_time_utc FROM file_modtime" )
row = cursor.fetchone()
while ( row != None ):
t = int( calendar.timegm( row[ 3 ].timetuple() ) )
self.lastEditTimes[ ( row[ 1 ], row[ 2 ] ) ] = [ row[ 0 ], t, None ]
row = cursor.fetchone()
# get existing mapping records for all mapped files.
cursor.execute( "SELECT id, team_id, path, problem_id, lang_id, override FROM file_to_problem" )
row = cursor.fetchone()
while ( row != None ):
self.fileMappings[ ( int( row[ 1 ] ), row[ 2 ] ) ] = MappingRec( row[ 0 ], row[ 3 ], row[ 4 ], row[ 5 ], None )
# Old mapping
# 0 -> id, 1 -> problem_id, 2 -> override, 3 -> new_problem_id
row = cursor.fetchone()
# load any team-specific strips.
cursor.execute( "SELECT team_id, str FROM team_strips" )
row = cursor.fetchone()
while ( row != None ):
if ( row[ 0 ] in self.teamStrips ):
self.teamStrips[ row[ 0 ] ].append( row[ 1 ].lower() )
else:
self.teamStrips[ row[ 0 ] ] = [ row[ 1 ].lower() ]
row = cursor.fetchone()
cursor.close()
def initCDS(self):
"""Initialization that just needs to be done for runs with backups from the CDS.
This is getting kind of ugly."""
# figure out a start time in the format we will get from the CDS.
cursor = dbConn.cursor()
cursor.execute(
"SELECT start_time FROM contests ORDER BY start_time DESC LIMIT 1")
row = cursor.fetchone()
if (row == None):
print("Error: no contest found in the database.")
exit(1)
startTime = time.strftime("%a, %d %b %Y %H:%M:%S GMT", time.gmtime(0))
self.teamLastModified = {}
for teamIdx in range(1, self.lastTeam + 1):
self.teamLastModified[teamIdx] = startTime
def importConfig( self, info ):
t = info[ "info" ][ "start-time" ]
match = re.compile( '(\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2})\.\d*([-+]\d{2})' ).match( t )
if not match:
print("Error: can't parse start time")
exit(1)
# here, we're truncating seconds, but we could round.
dt = datetime.strptime( match.group( 1 ), "%Y-%m-%dT%H:%M:%S" )
# add in the UTC Offset (there should be an easier way to do this)
start_time = (dt - datetime(1970,1,1)).total_seconds()
start_time = start_time - int( match.group( 2 ) ) * 3600
# Parse contest duration
match = re.compile( '(\d{1,}):(\d{2}):(\d{2})' ).match( info[ "info" ][ "duration" ] )
if not match:
print("Error: can't parse start duration")
exit(1)
duration = int( match.group( 1 ) ) * 3600 + int( match.group( 3 ) ) * 60 + int( match.group( 2 ) )
# Parse freeze time.
match = re.compile( '(\d{1,}):(\d{2}):(\d{2})' ).match( info[ "info" ][ "scoreboard-freeze-duration" ] )
if not match:
print("Error: can't parse freeze duration")
exit(1)
freeze_duration = int( match.group( 1 ) ) * 3600 + int( match.group( 2 ) ) * 60 + int( match.group( 3 ) )
freeze_time = duration - freeze_duration
query = "INSERT INTO contests (contest_name, start_time, length, freeze ) VALUES (%s,%s,%s,%s)"
params = (info[ "info" ][ "name" ], start_time, duration, freeze_time)
cursor = dbConn.cursor()
cursor.execute(query, params)
def checkActivity( self, bdir, tag ):
"""Scan the given backup dir and generate reports of the state of
files believed to correspond to various problems in the problem set."""
# Time when this script started running.
scriptStartTime = int( time.time() )
self.loadConfiguration()
# Get diff reports from git.
gitDiffs = self.parseGitDiffs( bdir )
# We should rethink some of the following loop. Right now, it
# tries to find file changes, including changes to editor auto-save
# files. But, to report changed lines in the file, we depend on git
# (which probably isn't tracking these auto-save files, so we'd have
# nothing to report)
# Visit home directory for each team.
tlist = sorted( glob.glob( bdir + '/team*' ) )
for tdir in tlist:
( dirname, tname ) = os.path.split( tdir )
team = int( tname.lstrip( 'team' ) )
cmd = "find %s/ -type f" % tdir
for f in os.popen( cmd ).readlines():
f = f.rstrip( '\n' )
fname = f[len(tdir) + 1:]
( dummy, extension ) = os.path.splitext( fname )
extension = extension.lstrip( '.' )
if extension in self.extensionMap:
fobj = File( fname, os.path.getmtime( f ) )
# Get lines changed, etc. We need to consult the git diff output.
# The tag is to make sure we only record lines changed on an analysis
# pass that's paired to a git commit. Independent analysis passes
# don't get this, since that could infate the appearance of how
# much editing is being done.
gitPath = f[len(bdir) + 1:]
if gitPath in gitDiffs and tag != DEFAULT_TAG:
fobj.linesChanged = gitDiffs[ gitPath ][ 0 ] + gitDiffs[ gitPath ][ 1 ];
mappingRec = None
lastEditRec = None
# Files with completely implausible modification times can get ignored.
ignoreEdit = False
# see if there's a mapping for this file.
if ( team, fname ) in self.fileMappings:
mappingRec = self.fileMappings[ ( team, fname ) ]
# If there's no forced mapping for this problem, try to guess one.
if ( mappingRec == None or mappingRec.override == 0 ):
prob = self.guessProblem( team, fobj.path )
if prob != None:
if mappingRec == None:
mappingRec = MappingRec( None, None, self.extensionMap[ extension ],
0, None );
self.fileMappings[ ( team, fname ) ] = mappingRec
if mappingRec.problem_id != prob:
mappingRec.new_problem_id = prob;
# see if there's an edit record for this file.
if ( team, fname ) in self.lastEditTimes:
lastEditRec = self.lastEditTimes[ ( team, fname ) ]
# check common editor auto-saves, to see if there
# is a fresher modification time.
autoTime = self.checkAutosaves( f );
if ( autoTime != None and autoTime > fobj.time ):
fobj.time = autoTime
# Try to gurad against anomalous file edit times. These are unlikely to happen,
# but they could look strange in the report or even suppress tracking of files
# if they have a modification time in the future.
# No edits should happen before the start of the contest or after right now.
if fobj.time < self.contestStart:
fobj.time = self.contestStart
# If the file really changes, we definitely want to record it, possibly with
# a sane-ified modification time.
if fobj.time < scriptStartTime - 2 * self.backupInterval:
if fobj.linesChanged > 0:
fobj.time = int( scriptStartTime - self.backupInterval / 2 )
# If the file looks like it changed in the future, ignore it unless git agrees it's changing.
if fobj.time > scriptStartTime + self.backupInterval:
print "Future Modification: ", fobj.path, " changed ", fobj.linesChanged
if fobj.linesChanged > 0:
fobj.time = scriptStartTime
else:
ignoreEdit = True
# Is this newer than our last known edit?
# We don't just depend on git for this, since we can
# also watch auto-saves.
if not ignoreEdit and ( lastEditRec == None or lastEditRec[ 1 ] + 10 < fobj.time ):
if lastEditRec == None:
lastEditRec = [ None, None, None ]
self.lastEditTimes[ ( team, fname ) ] = lastEditRec
# Grab file size and number of lines.
fobj.size = os.path.getsize( f )
fobj.lineCount = self.countLines( f )
lastEditRec[ 2 ] = fobj;
# Write out any new mappings
cursor = dbConn.cursor()
for k, v in self.fileMappings.iteritems():
if v.new_problem_id != None:
if v.db_id == None:
update = "INSERT INTO file_to_problem (team_id, path, problem_id, lang_id, override ) VALUES ( '%s', '%s', '%s', '%s', '0' )" % ( k[ 0 ], dbConn.escape_string( k[ 1 ] ), v.new_problem_id, v.lang_id )
cursor.execute( update )
else:
update = "UPDATE file_to_problem SET problem_id='%s' WHERE id='%d'" % ( v.new_problem_id, v[ 0 ] )
cursor.execute( update )
print "( %s, %s ) -> %s" % ( k[ 0 ], k[ 1 ], v.new_problem_id )
# Write out fresh edit times to file_modtime and new records to edit_activity
cursor = dbConn.cursor()
for k, v in self.lastEditTimes.iteritems():
if v[ 2 ] != None:
tstr = time.strftime( "%Y-%m-%d %H:%M:%S", time.gmtime( v[ 2 ].time ) )
if v[ 0 ] == None:
update = "INSERT INTO file_modtime (team_id, path, modify_time_utc ) VALUES ( '%s', '%s', '%s' )" % ( k[ 0 ], dbConn.escape_string( k[ 1 ] ), tstr )
cursor.execute( update )
else:
update = "UPDATE file_modtime SET modify_time_utc='%s' WHERE id='%d'" % ( tstr, v[ 0 ] )
cursor.execute( update )
# Compute time since start of contest.
cmin = ( v[ 2 ].time - self.contestStart ) / 60
update = "INSERT INTO edit_activity (team_id, path, modify_time_utc, modify_time, file_size_bytes, line_count, lines_changed, git_tag ) VALUES ( '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s' )" % ( k[ 0 ], dbConn.escape_string( k[ 1 ] ), tstr, cmin, v[ 2 ].size, v[ 2 ].lineCount, v[ 2 ].linesChanged, tag )
cursor.execute( update )
# Create and write the summary of edit activity by problem, edit_latest
# Map from team and problem_id to a triple, database_id,
# timestamp and valid flag. the valid flag lets us delete
# database rows (say, if a file_to_problem mapping changes). An
# entry is valid as long as there is a file that's mapped to
# the given problem, even if the file no longer exists.
modLatest = {}
# get latest known edit times for every team/problem.
cursor.execute( "SELECT id, team_id, problem_id, modify_time_utc FROM edit_latest" )
row = cursor.fetchone()
while ( row != None ):
t = int( calendar.timegm( row[ 3 ].timetuple() ) )
modLatest[ ( row[ 1 ], row[ 2 ] ) ] = [ row[ 0 ], t, 0 ]
row = cursor.fetchone()
for k, v in self.fileMappings.iteritems():
prob = v.problem_id
if v.new_problem_id != None:
prob = v.new_problem_id
if prob != None and prob != 'none':
if k in self.lastEditTimes:
lastEditRec = self.lastEditTimes[ k ]
t = lastEditRec[ 1 ]
if lastEditRec[ 2 ] != None:
t = lastEditRec[ 2 ].time;
if ( k[ 0 ], prob ) in modLatest:
rec = modLatest[ ( k[ 0 ], prob ) ]
if t > rec[ 1 ]:
rec[ 1 ] = t
rec[ 2 ] = 1;
else:
modLatest[ ( k[ 0 ], prob ) ] = [ None, t, 1 ]
for k, v in modLatest.iteritems():
tstr = time.strftime( "%Y-%m-%d %H:%M:%S", time.gmtime( v[ 1 ] ) )
if v[ 0 ] == None:
update = "INSERT INTO edit_latest (team_id, problem_id, modify_time_utc ) VALUES ( '%s', '%s', '%s' )" % ( k[ 0 ], k[ 1 ], tstr )
cursor.execute( update )
elif v[ 2 ]:
update = "UPDATE edit_latest SET modify_time_utc='%s' WHERE id='%d'" % ( tstr, v[ 0 ] )
cursor.execute( update )
else:
update = "DELETE FROM edit_latest WHERE id='%d'" % ( v[ 0 ] )
cursor.execute( update )
def __init__(self, basePath):
# path to the top of the backup directory.
self.basePath = basePath
# index of the last team in the competition.
self.lastTeam = config["teambackup"]["lastTeam"]
# interval for updating team backups, used in this script to freshen stale modification
# times, if it looks like there's a reason.
self.backupInterval = config["teambackup"]["interval"]
# Strings to strip from the filename before we try to guess
self.commonStrips = ['problem', 'prob', '_', '-']
# Valid source file extensions, and what each one says
# about the source language.
self.extensionMap = {
'cc': "C++",
'cpp': "C++",
'c': "C",
'java': "Java",
'py': "Python" # FIXME: do we want to discern between Python 2/3?
}
# map from problem ID to a list of keywords to look for.
self.probKeywords = {}
# List of problems. We use this mostly as the offficial
# list of problem letters, from the configuration.
self.problemList = problems
# Contest start time in Unix seconds from the database.
cursor = dbConn.cursor()
cursor.execute(
"SELECT start_time FROM contests ORDER BY start_time DESC LIMIT 1")
row = cursor.fetchone()
if (row == None):
print("Error: no contest found in the database.")
exit(1)
self.contestStart = row[0]
# For each team, a list of team-specific strips from filenames.
self.teamStrips = {}
# we use the next two fields to hold copies of database
# information (the file_to_problem and file_modtime tables)
# while the script is running, and to update the tables once
# the script has run.
# For every team and path, this is a triple, datatabase_id,
# latest modification time and File object (if the file has
# has changed). We only add a new entry to edit_activity if
# it's sufficiently newer than what we have there or if we just
# committed and git reports that a file has changed.
self.lastEditTimes = {}
# map from team_id and path to a MappingRec instance
# containing db_id, problem_id, lang_id, override flag and new
# problem ID (if we just generated a new mapping). This lets
# us know what to ignore in the mapping and what to update
# when we re-write the database. Multiple files may map to
# the same problem, if the team is working on multiple
# versions or has some supporting files.
self.fileMappings = {}
if not path.startswith( prefix ):
print "Bad path format"
usage()
# Strip off the front, and extract the team id.
path = path[len(prefix):]
mg = re.match( "^([0-9]+)", path )
if ( mg == None ):
print "Bad path format, no team id"
usage()
team = mg.group( 1 )
path = path[ len(team)+1:]
cursor = dbConn.cursor()
# Make sure this is a legal problem_id.
if prob not in config['problems']:
print "Bad problem id: %s" % prob
usage()
# Just to get the extension map.
analyzer = Analyzer( BACKUP_TOP )
# See if there is already an entry for this file.
cmd = "SELECT team_id FROM file_to_problem WHERE path='%s' AND team_id='%s'" % ( path, team )
cursor.execute( cmd )
# Just see if it's arealdy there. There should be a better way to do this.
if cursor.fetchone() == None:
def __init__( self, basePath ):
# path to the top of the backup directory.
self.basePath = basePath
# index of the last team in the competition.
self.lastTeam = config[ "teambackup" ][ "lastTeam" ]
# interval for updating team backups, used in this script to freshen stale modification
# times, if it looks like there's a reason.
self.backupInterval = config[ "teambackup" ][ "interval" ]
# Strings to strip from the filename before we try to guess
self.commonStrips = [ 'problem', 'prob', '_', '-' ]
# Valid source file extensions, and what each one says
# about the source language.
self.extensionMap = {
'cc': "C++",
'cpp': "C++",
'c': "C",
'java': "Java",
'py': "Python" # FIXME: do we want to discern between Python 2/3?
}
# map from problem ID to a list of keywords to look for.
self.probKeywords = {}
# List of problems. We use this mostly as the offficial
# list of problem letters, from the configuration.
self.problemList = problems
# Contest start time in Unix seconds from the database.
cursor = dbConn.cursor()
cursor.execute( "SELECT start_time FROM contests ORDER BY id DESC LIMIT 1" )
row = cursor.fetchone()
if ( row == None ):
print("Error: no contest found in the database.")
exit(1)
self.contestStart = row[0]
# For each team, a list of team-specific strips from filenames.
self.teamStrips = {}
# we use the next two fields to hold copies of database
# information (the file_to_problem and file_modtime tables)
# while the script is running, and to update the tables once
# the script has run.
# For every team and path, this is a triple, datatabase_id,
# latest modification time and File object (if the file has
# has changed). We only add a new entry to edit_activity if
# it's sufficiently newer than what we have there or if we just
# committed and git reports that a file has changed.
self.lastEditTimes = {}
# map from team_id and path to a MappingRec instance
# containing db_id, problem_id, lang_id, override flag and new
# problem ID (if we just generated a new mapping). This lets
# us know what to ignore in the mapping and what to update
# when we re-write the database. Multiple files may map to
# the same problem, if the team is working on multiple
# versions or has some supporting files.
self.fileMappings = {}
if not path.startswith(prefix):
print "Bad path format"
usage()
# Strip off the front, and extract the team id.
path = path[len(prefix):]
mg = re.match("^([0-9]+)", path)
if (mg == None):
print "Bad path format, no team id"
usage()
team = mg.group(1)
path = path[len(team) + 1:]
cursor = dbConn.cursor()
# Make sure this is a legal problem_id.
if prob not in config['problems']:
print "Bad problem id: %s" % prob
usage()
# Just to get the extension map.
analyzer = Analyzer(BACKUP_TOP)
# See if there is already an entry for this file.
cmd = "SELECT team_id FROM file_to_problem WHERE path='%s' AND team_id='%s'" % (
path, team)
cursor.execute(cmd)
# Just see if it's arealdy there. There should be a better way to do this.
def importProblem(self, problem):
p = problem["problem"]
query = "INSERT INTO problems (id, problem_id, problem_name, color) VALUES (%s,%s,%s,%s)"
params = (int(p["id"]), dbConn.escape_string(p["label"]), dbConn.escape_string(p["name"]), dbConn.escape_string(p["rgb"]))
cursor = dbConn.cursor()
cursor.execute(query, params)
def cleanTables(self):
cursor = dbConn.cursor()
cursor.execute("DELETE FROM teams")
cursor.execute("DELETE FROM problems")