def markasdone(self, job, success, elapsedtime, error=None): """ log a job as being processed (either successfully or not) """ query = """ INSERT INTO modw_supremm.`process` (jobid, process_version, process_timestamp, process_time) VALUES (%s, %s, NOW(), %s) ON DUPLICATE KEY UPDATE process_version = %s, process_timestamp = NOW(), process_time = %s """ if error != None: version = -1000 - error else: version = Accounting.PROCESS_VERSION if success else -1 * Accounting.PROCESS_VERSION data = (job.job_pk_id, version, elapsedtime, version, elapsedtime) if self.madcon == None: self.madcon = getdbconnection(self.dbsettings, False) cur = self.madcon.cursor() try: cur.execute(query, data) except OperationalError: logging.warning("Lost MySQL Connection. Attempting single reconnect") self.madcon = getdbconnection(self.dbsettings, False) cur = self.madcon.cursor() cur.execute(query, data) self.madcon.commit()
def executequery(self, query, data): """ run the sql queries and yield a job object for each result """ if self.con == None: self.con = getdbconnection(self.dbsettings, True) if self.hostcon == None: self.hostcon = getdbconnection(self.dbsettings, False) cur = self.con.cursor() cur.execute(query, data) rows_returned=cur.rowcount logging.info("Processing %s jobs", rows_returned) for record in cur: hostcur = self.hostcon.cursor() hostcur.execute(self.hostquery, (record['job_id'], record['job_id'])) hostarchives = {} hostlist = [] for h in hostcur: if h[0] not in hostarchives: hostlist.append(h[0]) hostarchives[h[0]] = [] hostarchives[h[0]].append(h[1]) jobpk = record['job_id'] del record['job_id'] record['host_list'] = hostlist job = Job(jobpk, str(record['local_job_id']), record) job.set_nodes(hostlist) job.set_rawarchives(hostarchives) yield job
def __init__(self, dwconfig, tablename): # The database schema should be created with utf8-unicode encoding. self.con = getdbconnection(dwconfig, False, {'charset': 'utf8', 'use_unicode': True}) self.tablename = tablename self.query = "INSERT IGNORE INTO " + tablename + " (resource_id,local_job_id,script) VALUES(%s,%s,%s)" self.buffered = 0
def detectXdmodSchema(self): """ Query the XDMoD datawarehouse to determine which version of the data schema is in use """ xdmod_schema_version = 7 testconnection = getdbconnection(self.dbsettings, True) curs = testconnection.cursor() try: curs.execute('SELECT 1 FROM `modw`.`job_tasks` LIMIT 1') xdmod_schema_version = 8 try: curs.execute( 'SELECT gpu_count FROM `modw`.`job_tasks` LIMIT 1') xdmod_schema_version = 9 except OperationalError: # Operational Error is set if the column does not exist pass except ProgrammingError: # Programming Error is thrown if the job_tasks table does not exist pass curs.close() testconnection.close() return xdmod_schema_version
def insert(self, resource_id, hostname, filename, start, end, jobid): """ Insert an archive record """ try: self.insertImpl(resource_id, hostname, filename, start, end, jobid) except OperationalError: logging.error("Lost MySQL Connection. Attempting single reconnect") self.con = getdbconnection(self.dbconfig) self.insertImpl(resource_id, hostname, filename, start, end, jobid)
def __init__(self, dwconfig, tablename): # The database schema should be created with utf8-unicode encoding. self.con = getdbconnection(dwconfig, False, { 'charset': 'utf8', 'use_unicode': True }) self.tablename = tablename self.query = "INSERT IGNORE INTO " + tablename + " (resource_id,local_job_id,script) VALUES(%s,%s,%s)" self.buffered = 0
def __init__(self, config): super(XDMoDArchiveCache, self).__init__(config) self.con = getdbconnection(config.getsection("datawarehouse")) self.buffered = 0 self._hostnamecache = {} cur = self.con.cursor() cur.execute("SELECT hostname FROM modw.hosts") for host in cur: self._hostnamecache[host[0]] = 1
def create_config(display): """ Create the configuration file """ display.newpage("Configuration File setup (DB)") confpath = getvalidpath(display, "Enter path to configuration files", Config.autodetectconfpath()) defaults = default_settings(confpath) display.newpage() display.print_text("""XDMoD datawarehouse access credentials. There are two options to specify the XDMoD datawarehouse access credentials. Either specify the path to the XDMoD install or specify the hostname, username, password of the database directly. """) doxdmod = display.prompt("Do you wish to specify the XDMoD install directory", ["y", "n"], defaults['usexdmodconfig']) mycnf = None outconfig = {} if doxdmod == "y": outconfig = getxdmodsettings(display, defaults) else: outconfig, mycnf = getdirectsettings(display, defaults) archivedir = display.prompt_string(" Temporary directory to use for job archive processing", defaults['archiveoutdir']) outconfig["summary"] = {"archive_out_dir": archivedir, "subdir_out_format": "%r/%j"} display.newpage("Configuration File setup (Resources)") display.print_text("Autodetecting resources based on configuration file settings") outconfig["resources"] = {} try: config = generatetempconfig(outconfig, mycnf) dbconn = getdbconnection(config.getsection("datawarehouse")) dbcur = dbconn.cursor() dbcur.execute("SELECT id as resource_id, code as resource FROM modw.resourcefact") for resource in dbcur: resconf = configure_resource(display, resource[0], resource[1], defaults) outconfig['resources'][resource[1]] = resconf writeconfig(display, confpath, outconfig, mycnf) except Exception as exc: display.print_warning("An error occurred while detecting resources.\n{0}".format(exc)) display.hitanykey("Press ENTER to continue.")
def __init__(self, dwconfig, schema, timestamp_mode): # The database schema should be created with utf8-unicode encoding. self.con = getdbconnection(dwconfig, False, { 'charset': 'utf8', 'use_unicode': True }) self.tablename = "`{0}`.`batchscripts`".format(schema) self.xdmod_schema_version = 7 self.timestamp_mode = timestamp_mode try: cur = self.con.cursor() cur.execute( 'SELECT tg_job_id FROM `{0}`.`job_scripts` LIMIT 1'.format( schema)) cur.close() self.xdmod_schema_version = 8 self.tablename = "`{0}`.`job_scripts`".format(schema) except ProgrammingError: pass if self.xdmod_schema_version == 7: self.query = "INSERT INTO " + self.tablename + " (resource_id,local_job_id,script) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE script = VALUES(script)" else: self.query = "INSERT INTO " + self.tablename + """ (tg_job_id, resource_id, start_date, script) SELECT job_id AS tg_job_id, resource_id, DATE(FROM_UNIXTIME(start_time_ts)) AS start_date, %s AS script FROM `modw`.`job_tasks` WHERE resource_id = %s AND local_job_id_raw = %s""" if self.timestamp_mode == 'start': self.query += ' AND ABS(DATEDIFF(DATE(FROM_UNIXTIME(start_time_ts)), %s)) < 2' elif self.timestamp_mode == 'submit': self.query += ' AND ABS(DATEDIFF(DATE(FROM_UNIXTIME(submit_time_ts)), %s)) < 2' elif self.timestamp_mode == 'end': self.query += ' AND ABS(DATEDIFF(DATE(FROM_UNIXTIME(end_time_ts)), %s)) < 2' #else # self.timestamp_mode == 'None' which means no date restrictions self.query += " ON DUPLICATE KEY UPDATE resource_id = VALUES(resource_id), start_date = VALUES(start_date), script = VALUES(script)" self.buffered = 0
def checkForPreviousInstall(display, dbsettings): """ Query the database to check that the database table from a 1.0 install is present """ dbcon = getdbconnection(dbsettings) try: cur = dbcon.cursor() cur.execute('SELECT 1 FROM `modw_supremm`.`archive` LIMIT 1') cur.close() except ProgrammingError: display.print_warning( """No previous install detected. No migration will be performed. Please refer to the documentation for instructions on how to setup a new instance of the software using the 'supremm-setup' command. """) display.hitanykey("Press ENTER to continue.") sys.exit() dbcon.close()
def detectXdmodSchema(self): """ Query the XDMoD datawarehouse to determine which version of the data schema is in use """ xdmod_schema_version = 7 testconnection = getdbconnection(self.dbsettings, True) curs = testconnection.cursor() try: curs.execute('SELECT 1 FROM `modw`.`job_tasks` LIMIT 1') curs.close() xdmod_schema_version = 8 except ProgrammingError: pass testconnection.close() return xdmod_schema_version
def __init__(self, dwconfig, schema): # The database schema should be created with utf8-unicode encoding. self.con = getdbconnection(dwconfig, False, { 'charset': 'utf8', 'use_unicode': True }) self.tablename = "`{0}`.`batchscripts`".format(schema) self.xdmod_schema_version = 7 try: cur = self.con.cursor() cur.execute( 'SELECT tg_job_id FROM `{0}`.`job_scripts` LIMIT 1'.format( schema)) cur.close() self.xdmod_schema_version = 8 self.tablename = "`{0}`.`job_scripts`".format(schema) except ProgrammingError: pass if self.xdmod_schema_version == 7: self.query = "INSERT IGNORE INTO " + self.tablename + " (resource_id,local_job_id,script) VALUES(%s,%s,%s)" else: self.query = "INSERT IGNORE INTO " + self.tablename + """ (tg_job_id, resource_id, start_date, script) SELECT job_id AS tg_job_id, resource_id, DATE(FROM_UNIXTIME(start_time_ts)) AS start_date, %s AS script FROM `modw`.`job_tasks` WHERE resource_id = %s AND local_job_id_raw = %s AND DATE(FROM_UNIXTIME(start_time_ts)) = %s""" self.buffered = 0
def __init__(self, resource_id, config, nthreads, threadidx): super(XDMoDAcct, self).__init__(resource_id, config, nthreads, threadidx) self._query = """ SELECT jf.`job_id` as `job_id`, jf.`resource_id` as `resource_id`, COALESCE(jf.`local_job_id_raw`, jf.`local_jobid`) as `local_job_id`, jf.`start_time_ts` as `start_time`, jf.`end_time_ts` as `end_time`, jf.`submit_time_ts` as `submit`, jf.`eligible_time_ts` as `eligible`, jf.`queue_id` as `partition`, jf.`uid_number` as `uid`, aa.`charge_number` as `account`, sa.`username` as `user`, jf.`name` as `jobname`, jf.`nodecount` as `nodes`, jf.`processors` as `ncpus`, jf.`group_name` as `group`, jf.`gid_number` as `gid`, jf.`exit_code` as `exit_code`, jf.`exit_state` as `exit_status`, jf.`cpu_req` as `reqcpus`, jf.`mem_req` as `reqmem`, jf.`timelimit` as `timelimit` FROM modw.jobfact jf LEFT JOIN modw_supremm.`process` p ON jf.job_id = p.jobid INNER JOIN modw.systemaccount sa ON jf.systemaccount_id = sa.id INNER JOIN modw.account aa ON jf.account_id = aa.id WHERE jf.resource_id = %s """ self.hostquery = """SELECT h.hostname, a.filename FROM modw.`hosts` h, modw_supremm.`archive` a, modw.`jobhosts` jh, modw.`jobfact` j WHERE j.job_id = jh.job_id AND jh.job_id = %s AND jh.host_id = h.id AND a.hostid = h.id AND ( (j.start_time_ts BETWEEN a.start_time_ts AND a.end_time_ts) OR (j.end_time_ts BETWEEN a.start_time_ts AND a.end_time_ts) OR (j.start_time_ts < a.start_time_ts and j.end_time_ts > a.end_time_ts) OR (CAST(j.local_job_id_raw AS CHAR) = a.jobid) ) AND (a.jobid = CAST(j.local_job_id_raw AS CHAR) OR a.jobid IS NULL) GROUP BY 1, 2 ORDER BY 1 ASC, a.start_time_ts ASC """ self.con = getdbconnection(config.getsection("datawarehouse"), True) self.hostcon = getdbconnection(config.getsection("datawarehouse"), False) self.madcon = getdbconnection(config.getsection("datawarehouse"), False)