def purge_jobhistory(): with db.trans() as c: c.execute(''' SELECT id FROM jobs ''') for job in c: job_id = job['id'] with db.trans() as c2: c2.execute( ''' SELECT daystokeep FROM jobconfig WHERE job_id = %(job_id)s ''', locals()) daystokeep = 30 r = c2.fetchone() if r: daystokeep = r['daystokeep'] with db.trans() as c3: c3.execute( ''' DELETE FROM jobhistory WHERE date(now()) - date(datestarted) > %(daystokeep)s AND job_id = %(job_id)s ''', locals()) if c3.rowcount > 0: log.debug("purged %s entries for jobhistory", c3.rowcount)
def test_trans(self): "Test transaction with commit" tbl = rand() with db.trans() as c: c.execute("create table " + tbl + " (test text)") c.execute("insert into " + tbl + " values ('test')") with db.trans() as c: c.execute("select test from " + tbl + " limit 1") r = c.fetchone() self.assertEqual(r[0], "test")
def test_trans(self): "Test transaction with commit" tbl = rand() with db.trans() as c: c.execute("create table " + tbl + " (test text)") c.execute("insert into " + tbl + " values ('test')") with db.trans() as c: c.execute("select test from " + tbl + " limit 1") r = c.fetchone() self.assertEqual(r[0], 'test')
def test_trans_rollback(self): "Test transaction with rollback" tbl = rand() with db.trans() as c: c.execute("create table " + tbl + " (test text)") with self.assertRaises(psycopg2.DataError): with db.trans() as c: c.execute("insert into " + tbl + " values ('test')") c.execute("select 1/0") with db.trans() as c: c.execute("select test from " + tbl + " limit 1") r = c.fetchone() self.assertIs(r, None)
def jobhistory(computername, computeruser, script): ctx = getctx() ctx['computername'] = computername ctx['computeruser'] = computeruser ctx['script'] = script with db.trans() as c: c.execute( ''' SELECT h.id , j.computername , j.computeruser , j.script , h.datestarted , h.datefinished , h.status , h.duration FROM jobhistory AS h INNER JOIN jobs AS j ON j.id = h.job_id WHERE j.computername = %(computername)s AND j.computeruser = %(computeruser)s AND j.script = %(script)s ORDER BY j.computername , j.computeruser , j.script , h.datestarted DESC ''', locals()) history = [] for hist in c: h = dict(hist) h['duration'] = duration_to_human(h['duration']) history.append(h) return dict(history=history, ctx=ctx)
def savedaystokeep(computername, computeruser, script): daystokeep = request.forms.daystokeep if not re.match(r'^\d+$', daystokeep): abort(400, "invalid days to keep") daystokeep = int(daystokeep) if daystokeep < 0: return 'days to keep must be >= 0' job_id = get_job_id(computername, computeruser, script) with db.trans() as c: c.execute( ''' UPDATE jobconfig SET daystokeep = %(daystokeep)s WHERE job_id = %(job_id)s ''', locals()) if c.rowcount == 0: c.execute( ''' INSERT INTO jobconfig (job_id, daystokeep) VALUES (%(job_id)s, %(daystokeep)s) ''', locals()) return redirect('/config-job/' + computername + '/' + computeruser + '/' + script + '/')
def joboutput(computername, computeruser, script, id): if not re.match(r'^[a-f0-9-]{36}$', id): raise ValueError('invalid id') output = '' with db.trans() as c: c.execute( ''' SELECT o.output FROM jobhistory AS h INNER JOIN jobs AS j ON h.job_id = j.id INNER JOIN outputs AS o ON o.sha1 = h.output_sha1 WHERE j.computername = %(computername)s AND j.computeruser = %(computeruser)s AND j.script = %(script)s AND h.id = %(id)s ''', locals()) r = c.fetchone() if not r: response.status = 404 return 'not found' else: response.content_type = 'text/plain; charset=utf-8' return zlib.decompress(r['output'])
def configjob(computername, computeruser, script): ctx = getctx() ctx['computername'] = computername ctx['computeruser'] = computeruser ctx['script'] = script daystokeep = 30 with db.trans() as c: c.execute( ''' SELECT c.daystokeep FROM jobconfig AS c INNER JOIN jobs AS j ON j.id = c.job_id WHERE j.computername = %(computername)s AND j.computeruser = %(computeruser)s AND j.script = %(script)s ''', locals()) r = c.fetchone() if r: daystokeep = r['daystokeep'] c.execute( ''' SELECT a.email FROM jobconfigalert AS a INNER JOIN jobs AS j ON j.id = a.job_id WHERE j.computername = %(computername)s AND j.computeruser = %(computeruser)s AND j.script = %(script)s ''', locals()) emails = [] for r in c: emails.append(r['email']) return dict(ctx=ctx, daystokeep=daystokeep, emails="\n".join(emails))
def allhistory(): offset = 0 if 'offset' in request.query: if re.match(r'^\d+$', request.query.offset): offset = int(request.query.offset) * 25 with db.trans() as c: c.execute( ''' SELECT h.id , j.computername , j.computeruser , j.script , h.datestarted , h.datefinished , h.status , h.duration FROM jobhistory AS h INNER JOIN jobs AS j ON j.id = h.job_id ORDER BY h.datestarted DESC LIMIT 25 OFFSET %(offset)s ''', locals()) history = [] for hist in c: h = dict(hist) h['duration'] = duration_to_human(h['duration']) history.append(h) return dict(history=history, offset=offset)
def removesession(session_id): with db.trans() as c: c.execute( ''' DELETE FROM sessions WHERE session_id = %(session_id)s ''', locals())
def purge_sessions(): with db.trans() as c: c.execute(''' DELETE FROM sessions WHERE date(now()) - date(date_login) > 7 ''') if c.rowcount > 0: log.info('purged %s login sessions', c.rowcount)
def userisadmin(username): with db.trans() as c: c.execute( ''' SELECT is_admin FROM users WHERE username = %(username)s ''', locals()) return c.fetchone()['is_admin']
def purge_outputs(): with db.trans() as c: c.execute(''' DELETE FROM outputs WHERE sha1 NOT IN ( SELECT DISTINCT output_sha1 FROM jobhistory ) ''') if c.rowcount > 0: log.debug("purged %s entries for outputs", c.rowcount)
def validatesession(session_id): with db.trans() as c: c.execute( ''' SELECT session_id FROM sessions WHERE session_id = %(session_id)s ''', locals()) r = c.fetchone() return bool(r)
def currentuser(): session_id = request.get_cookie('clog') with db.trans() as c: c.execute( ''' SELECT username FROM sessions WHERE session_id = %(session_id)s ''', locals()) return c.fetchone()['username']
def makesession(user): with db.trans() as c: session_id = str(uuid4()) c.execute( ''' INSERT INTO sessions (session_id, username) VALUES (%(session_id)s, %(user)s) ''', locals()) return session_id
def admin(): users = [] with db.trans() as c: c.execute(''' SELECT username, is_admin FROM users ORDER BY username ''') for user in c: user = dict(user) users.append(user) return dict(ctx=getctx(), users=users)
def validateuserdb(user, passwd): passwdsha1 = sha1(passwd).hexdigest() with db.trans() as c: c.execute( ''' SELECT username FROM users WHERE username = %(user)s AND password = %(passwdsha1)s ''', locals()) r = c.fetchone() return bool(r)
def forceuserpassword(username): password = str(int(random.random() * 999999)) sha1password = sha1(password).hexdigest() if username == currentuser(): return 'cant change password for current user!' with db.trans() as c: c.execute( ''' UPDATE users SET password = %(sha1password)s WHERE username = %(username)s ''', locals()) return u'user %s had password changed to: %s' % (username, password)
def getalertemails(computername, computeruser, script): job_id = get_job_id(computername, computeruser, script) with db.trans() as c: c.execute( ''' SELECT email FROM jobconfigalert WHERE job_id = %(job_id)s ''', locals()) emails = [] for row in c: emails.append(row['email']) return emails
def changeuseradminstatus(username, status): if username == currentuser(): return 'cant change admin status for current user!' if status not in ('0', '1'): abort(400, "invalid status") status = bool(int(status)) with db.trans() as c: c.execute( ''' UPDATE users SET is_admin = %(status)s WHERE username = %(username)s ''', locals()) return redirect('/admin')
def get_job_id(computername, computeruser, script): with db.trans() as c: c.execute( ''' SELECT id FROM jobs WHERE computername = %(computername)s AND computeruser = %(computeruser)s AND script = %(script)s ''', locals()) r = c.fetchone() if not r: return None else: return r[0]
def removeuser(username): if username == currentuser(): return 'cant remove current user!' with db.trans() as c: c.execute( ''' DELETE FROM sessions WHERE username = %(username)s ''', locals()) c.execute( ''' DELETE FROM users WHERE username = %(username)s ''', locals()) return redirect('/admin')
def newuser(): username = request.forms.username if username.strip() == '': return 'invalid user!' password = str(int(random.random() * 999999)) sha1password = sha1(password).hexdigest() with db.trans() as c: c.execute( ''' INSERT INTO users (username, password, is_admin) VALUES (%(username)s, %(sha1password)s, 'f') ''', locals()) return u'user %s created with password %s' % (username, password)
def savealertemails(computername, computeruser, script): job_id = get_job_id(computername, computeruser, script) with db.trans() as c: c.execute( ''' DELETE FROM jobconfigalert WHERE job_id = %(job_id)s ''', locals()) for email in request.forms.emails.split(): c.execute( ''' INSERT INTO jobconfigalert VALUES (%(job_id)s, %(email)s) ''', locals()) return redirect('/config-job/' + computername + '/' + computeruser + '/' + script + '/')
def changepasswordsave(): oldpasswd = request.forms.oldpasswd newpasswd = request.forms.newpasswd newpasswd2 = request.forms.newpasswd2 username = currentuser() if not validateuserdb(username, oldpasswd): return 'invalid current password!' if newpasswd.strip() == '' or newpasswd2.strip() == '': return 'invalid new password!' if newpasswd != newpasswd2: return 'new passwords do not match!' passwdsha1 = sha1(newpasswd).hexdigest() with db.trans() as c: c.execute( ''' UPDATE users SET password = %(passwdsha1)s WHERE username = %(username)s ''', locals()) return redirect('/')
def jobs(): with db.trans() as c: c.execute(''' SELECT computername , computeruser , script , date_last_success , date_last_failure , last_status , last_duration FROM jobs ORDER BY computername , computeruser , script ''') jobs = [] for job in c: j = dict(job) j['date_last_success'] = date_to_human(j['date_last_success']) j['date_last_failure'] = date_to_human(j['date_last_failure']) j['last_duration'] = duration_to_human(j['last_duration']) jobs.append(j) return dict(jobs=jobs)
def purgejob(computername, computeruser, script): job_id = get_job_id(computername, computeruser, script) with db.trans() as c: c.execute( ''' DELETE FROM jobhistory WHERE job_id = %(job_id)s ''', locals()) c.execute( ''' DELETE FROM jobconfig WHERE job_id = %(job_id)s ''', locals()) c.execute( ''' DELETE FROM jobconfigalert WHERE job_id = %(job_id)s ''', locals()) c.execute( ''' DELETE FROM jobs WHERE id = %(job_id)s ''', locals()) return redirect('/')
def newjob(): rid_regexp = r'^[a-f0-9]{8}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{12}$' rid = request.forms.id if not re.match(rid_regexp, rid): abort(400, "invalid job id") start_time = request.forms.start_time if not re.match(r'^[\d+\.]+$', start_time): abort(400, "invalid start time") start_time = datetime.datetime.fromtimestamp(int(float(start_time))) end_time = request.forms.end_time if not re.match(r'^[\d+\.]+$', end_time): abort(400, "invalid end time") end_time = datetime.datetime.fromtimestamp(int(float(end_time))) duration = request.forms.duration if not re.match(r'^[0-9\.]+$', duration): abort(400, "invalid duration") status = request.forms.status if status not in ('fail', 'ok'): abort(400, "invalid status") script = request.forms.script if not re.match(r'^[a-zA-Z0-9\-_\.]+$', script): abort(400, "invalid script name") output = request.forms.output or '' output = b64decode(output) outputz = buffer(zlib.compress(output)) computername = request.forms.computername computeruser = request.forms.username # Windows if '\\' in computeruser: computeruser = computeruser.split('\\')[-1] ip = request.remote_addr log.info('new job status from %s@%s/%s (%s)', computeruser, computername, script, ip) log.info(' id: %s', rid) output_sha1 = sha1(output).hexdigest() with db.trans() as c: try: c.execute( ''' INSERT INTO outputs (sha1, output) VALUES (%(output_sha1)s, %(outputz)s) ''', locals()) except db.psycopg2.IntegrityError: pass job_id = get_job_id(computername, computeruser, script) if not job_id: with db.trans() as c: c.execute( ''' INSERT INTO jobs ( computername, computeruser, script, last_status, last_duration ) VALUES ( %(computername)s, %(computeruser)s, %(script)s, %(status)s, %(duration)s ) RETURNING id ''', locals()) job_id = c.fetchone()[0] try: with db.trans() as c: c.execute( ''' INSERT INTO jobhistory ( id, job_id, ip, datestarted, datefinished, status, duration, output_sha1 ) VALUES ( %(rid)s, %(job_id)s, %(ip)s, %(start_time)s, %(end_time)s, %(status)s, %(duration)s, %(output_sha1)s ) ''', locals()) if status == 'ok': c.execute( ''' UPDATE jobs SET date_last_success = %(start_time)s , last_status = 'ok' , last_duration = %(duration)s WHERE id = %(job_id)s ''', locals()) else: c.execute( ''' UPDATE jobs SET date_last_failure = %(start_time)s , last_status = 'fail' , last_duration = %(duration)s WHERE id = %(job_id)s ''', locals()) except db.psycopg2.IntegrityError: # Ignoring duplicate insertion. return 'ok' else: emails = getalertemails(computername, computeruser, script) if emails: if status == 'fail': for email in emails: log.info(" job failed, sending alert to %s", email) send_alert(email, computername, computeruser, script, status, output) elif status == 'ok': with db.trans() as c: c.execute( ''' SELECT status FROM jobhistory WHERE job_id = %(job_id)s ORDER BY datestarted DESC LIMIT 1 OFFSET 1 ''', locals()) r = c.fetchone() if r and r['status'] == 'fail': for email in emails: log.info(" job ok, sending alert to %s", email) send_alert(email, computername, computeruser, script, status, output) return 'ok'