def POST(
self,
username,
email,
password = None,
user_id = None
):
# clean
if user_id:
try:
user_id = int(user_id)
except:
return { 'result': 'failure', 'message': 'Invalid user_id'}
# do
action = None
db = DB()
cur = db.connect(settings.DSN)
if user_id:
if password:
pasSQL = 'password = %s,'
vals = (username, auth.hash(password), email, user_id)
else:
pasSQL = ''
vals = (username, email, user_id)
cur.execute(
"""UPDATE pq_user
SET
username = %s,
""" + pasSQL + """
email = %s
WHERE user_id = %s;""",
vals
)
action = Updated()
else:
cur.execute(
"""INSERT INTO pq_user (username, password, email) VALUES (%s,%s,%s);""",
(username, auth.hash(password), email)
)
action = Inserted()
if cur.rowcount > 0:
db.commit()
db.disconnect()
if type(action) == type(Updated()):
return { 'result': 'success', 'message': 'User updated successfully.'}
elif type(action) == type(Inserted()):
return { 'result': 'success', 'message': 'User added successfully.'}
else:
return { 'result': 'failure', 'message': 'Something was successful? Add/update reported successful, but we have no idea what happened.'}
else:
db.rollback()
db.disconnect()
return {
'result': 'failure',
'message': 'Add/update failed.'
}
def GET(self, username):
""" Check if a username is unique/unused
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT true FROM pq_user WHERE username = %s", (username, ))
if cur.rowcount > 0:
return { 'available': False, }
else:
return { 'available': True, }
def GET(self, page = None, total = 100):
""" Return JSON of logs
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT count(log_id) FROM pq_log;")
row = cur.fetchone()
log_count = row[0]
pages = math.ceil(log_count / int(total))
pages = int(math.ceil(float(log_count) / float(total)))
try:
offset = (int(page) * int(total)) - int(total)
except TypeError:
offset = 0
cur.execute("""SELECT
log_id,
lt.log_type_id,
lt.name AS log_type,
test_id,
t.name AS test_name,
message,
stamp,
notify
FROM
pq_log l
JOIN pq_log_type lt USING (log_type_id)
JOIN pq_test t USING (test_id)
ORDER BY stamp DESC, log_id DESC
LIMIT %s OFFSET %s""", (total, offset, ))
results = {
'meta': {
'totalLogs': log_count,
'pages': pages,
},
'logs': [],
}
for log in cur.fetchall():
l = {
'log_id': log['log_id'],
'log_type': log['log_type'],
'log_type_id': log['log_type_id'],
'test_id': log['test_id'],
'test_name': log['test_name'],
'message': log['message'],
'stamp': log['stamp'].isoformat(),
'notify': log['notify'],
}
results['logs'].append(l)
db.disconnect()
return results
def single(self, user_id):
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT user_id, username, email FROM pq_user WHERE user_id = %s ORDER BY username;", (user_id, ))
user = cur.fetchone()
db.disconnect()
return {
'user_id': user['user_id'],
'username': user['username'],
'email': user['email']
}
def POST(
self,
name,
username,
password,
hostname,
database_id = None,
port = 5432,
active = True
):
# do
action = None
db = DB()
cur = db.connect(settings.DSN)
if database_id:
cur.execute(
"""UPDATE pq_database
SET
name = %s,
username = %s,
password = %s,
port = %s,
hostname = %s,
active = %s
WHERE database_id = %s;""",
(name, username, password, port, hostname, active, database_id)
)
action = Updated()
else:
cur.execute(
"""INSERT INTO pq_database (name, username, password, port, hostname, active) VALUES (%s,%s,%s,%s,%s,%s);""",
(name, username, password, port, hostname, active)
)
action = Inserted()
if cur.rowcount > 0:
db.commit()
db.disconnect()
if type(action) == type(Updated()):
return { 'result': 'success', 'message': 'Database updated successfully.'}
elif type(action) == type(Inserted()):
return { 'result': 'success', 'message': 'Database added successfully.'}
else:
return { 'result': 'failure', 'message': 'Something was successful? Add/update reported successful, but we have no idea what happened.'}
else:
db.rollback()
db.disconnect()
return {
'result': 'failure',
'message': 'Add/update failed.'
}
def multiple(self):
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT user_id, username, email FROM pq_user ORDER BY username;")
users = []
for user in cur.fetchall():
u = {
'user_id': user['user_id'],
'username': user['username'],
'email': user['email']
}
users.append(u)
db.disconnect()
return users
def GET(self):
""" Return JSON of schedules
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT schedule_id, name FROM pq_schedule ORDER BY name;")
schedules = []
for schedule in cur.fetchall():
s = {
'schedule_id': schedule['schedule_id'],
'name': schedule['name']
}
schedules.append(s)
db.disconnect()
return schedules
def GET(self):
""" Return JSON of test types
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT test_type_id, name FROM pq_test_type ORDER BY name;")
types = []
for type in cur.fetchall():
t = {
'test_type_id': type['test_type_id'],
'name': type['name']
}
types.append(t)
db.disconnect()
return types
def single(self, database_id):
""" Return JSON of known databases
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT database_id, name, username, password, port, hostname, active FROM pq_database WHERE database_id = %s ORDER BY name, hostname;", (database_id, ))
dbase = cur.fetchone()
db.disconnect()
return {
'database_id': dbase['database_id'],
'name': dbase['name'],
'username': dbase['username'],
'password': dbase['password'],
'port': dbase['port'],
'hostname': dbase['hostname'],
'active': dbase['active'],
}
def multiple(self):
""" Return JSON of known databases
"""
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT database_id, name, username, password, port, hostname, active FROM pq_database ORDER BY name, hostname;")
dbs = []
for dbase in cur.fetchall():
d = {
'database_id': dbase['database_id'],
'name': dbase['name'],
'username': dbase['username'],
'password': dbase['password'],
'port': dbase['port'],
'hostname': dbase['hostname'],
'active': dbase['active'],
}
dbs.append(d)
db.disconnect()
return dbs
def GET(self, total = 100, page = None):
db = DB()
cur = db.connect(settings.DSN)
cur.execute("SELECT count(log_id) FROM pq_log;")
row = cur.fetchone()
log_count = row[0]
pages = math.ceil(log_count / int(total))
pages = int(math.ceil(float(log_count) / float(total)))
try:
offset = (int(page) * int(total)) - int(total)
except TypeError:
offset = 0
cur.execute("""SELECT
log_id,
lt.log_type_id,
lt.name AS log_type,
test_id,
t.name AS test_name,
message,
stamp,
notify
FROM
pq_log l
JOIN pq_log_type lt USING (log_type_id)
JOIN pq_test t USING (test_id)
ORDER BY stamp DESC, log_id DESC
LIMIT %s OFFSET %s""", (total, offset, ))
results = {
'meta': {
'totalLogs': log_count,
'pages': pages,
},
'logs': [],
}
items = []
for log in cur.fetchall():
items.append(
PyRSS2Gen.RSSItem(
title = '[' + log['log_type'] + '] ' + log['test_name'],
link = 'http://' + host + ':' + str(port) + '/pyqual#test:' + str(log['test_id']),
description = log['message'],
guid = PyRSS2Gen.Guid(str(log['log_id'])),
pubDate = log['stamp']
)
)
rss = PyRSS2Gen.RSS2(
title = "Pyqual Logs",
link = "http://" + host + ':' + str(port) + '/',
description = "Latest Pyqual logs.",
lastBuildDate = datetime.datetime.now(),
items = items
)
db.disconnect()
cherrypy.response.headers['Content-Type'] = 'application/rss+xml'
return rss.to_xml()
def GET(self, test_id = None):
""" Return JSON detail of a test
"""
db = DB()
cur = db.connect(settings.DSN)
def multiple(self):
""" Return JSON of tests in the DB
"""
cur.execute("""SELECT
test_id,
t.name,
lastrun,
schedule_id,
s.name AS schedule_name,
database_id,
d.name AS database_name
FROM pq_test t
LEFT JOIN pq_schedule s USING (schedule_id)
LEFT JOIN pq_database d USING (database_id)
WHERE deleted = false
ORDER BY lastrun, test_id;""")
tests = []
for test in cur.fetchall():
try:
lastrun = test['lastrun'].isoformat()
except AttributeError:
lastrun = None
t = {
'test_id': test['test_id'],
'name': test['name'],
'lastrun': lastrun,
'schedule_id': test['schedule_id'],
'schedule_name': test['schedule_name'],
'database_id': test['database_id'],
'database_name': test['database_name'],
}
tests.append(t)
return tests
def single(self, test_id):
# Is test_id valid?
if not test_id:
raise cherrypy.HTTPError(404)
else:
try:
test_id = int(test_id)
except:
raise cherrypy.HTTPError(404)
cur.execute("""SELECT
test_id,
t.name,
lastrun,
schedule_id,
database_id,
test_type_id,
user_id,
cc,
sql,
python,
fail_on_no_results
FROM pq_test t
LEFT JOIN pq_schedule s USING (schedule_id)
LEFT JOIN pq_database d USING (database_id)
WHERE test_id = %s AND deleted = false
ORDER BY lastrun;""", (test_id, ))
if cur.rowcount > 0:
test = cur.fetchone()
try:
lastrun = test['lastrun'].isoformat()
except AttributeError:
lastrun = None
t = {
'test_id': test['test_id'],
'name': test['name'],
'lastrun': lastrun,
'cc': test['cc'],
'schedule_id': test['schedule_id'],
'database_id': test['database_id'],
'test_type_id': test['test_type_id'],
'user_id': test['user_id'],
'sql': test['sql'],
'python': test['python'],
'fail_on_no_results': test['fail_on_no_results'],
}
else:
raise cherrypy.HTTPError(404)
db.disconnect()
return t
if test_id:
return single(self, test_id)
else:
return multiple(self)
def DELETE(self, user_id):
""" Delete a user """
db = DB()
cur = db.connect(settings.DSN)
cur.execute("""DELETE FROM pq_user WHERE user_id = %s""", (user_id, ))
if cur.rowcount > 0:
db.commit()
db.disconnect()
return {
'result': 'success',
'message': 'User deleted successfully.',
}
elif cur.rowcount > 1:
db.rollback()
db.disconnect()
return {
'result': 'failure',
'message': 'Delete failed. Attempt was made to delete more than one record.'
}
else:
db.rollback()
db.disconnect()
return {
'result': 'failed',
'message': 'User delete failed.',
}
def DELETE(self, test_id):
""" Delete a test """
db = DB()
cur = db.connect(settings.DSN)
cur.execute("""UPDATE pq_test SET deleted = true WHERE test_id = %s""", (test_id, ))
if cur.rowcount == 1:
db.commit()
db.disconnect()
return {
'result': 'success',
'message': 'Test deleted successfully.',
}
elif cur.rowcount > 1:
db.rollback()
db.disconnect()
return {
'result': 'failure',
'message': 'Delete failed. Attempt was made to delete more than one record.'
}
else:
db.rollback()
db.disconnect()
return {
'result': 'failed',
'message': 'Test delete failed.',
}
def main():
""" Handle cli arguments """
if __name__ == "__main__":
parser = argparse.ArgumentParser(description="Run pyqual tests logging results to the DB.")
parser.add_argument(
"-d", "--debug", action="store_true", default=False, help="Output debug statements to stdout"
)
parser.add_argument("-s", "--sql", action="store_true", default=False, help="Output utility SQL(not test SQL)")
parser.add_argument(
"-r", "--dry-run", action="store_true", default=False, dest="dry", help="Dry Run(Don't actually run tests)"
)
args = parser.parse_args()
else:
class FakeArgs(object):
def __init__(self):
self.debug = False
self.sql = False
self.dry = False
args = FakeArgs()
db = DB()
cur = db.connect(settings.DSN)
""" schedule_id | name
-------------+---------
1 | Hourly
2 | Daily
3 | Weekly
4 | Monthly
"""
cur.execute(
"""SELECT
test_id,
test.name,
sql,
python,
test_type_id,
database_id,
db.name AS database_name,
db.username AS database_username,
db.password AS database_password,
db.port AS database_port,
db.hostname AS database_hostname,
fail_on_no_results
FROM
pq_test test
JOIN pq_database db USING (database_id)
WHERE
db.active IS TRUE
AND CASE WHEN lastrun IS NOT NULL THEN
CASE schedule_id
WHEN 1 THEN INTERVAL '1 hour' <= now() - lastrun
WHEN 2 THEN INTERVAL '1 day' <= now() - lastrun
WHEN 3 THEN INTERVAL '1 week' <= now() - lastrun
WHEN 4 THEN INTERVAL '1 month' <= now() - lastrun
ELSE FALSE
END
ELSE TRUE
END
AND deleted = false
ORDER BY db.database_id"""
)
if args.sql:
print cur.query
if cur.rowcount == 0:
if args.debug:
print "Debug: No tests to run at this time."
for test in cur.fetchall():
if args.debug:
print "Debug: Running test #%s" % test.get("test_id")
testCur = None
try:
if args.debug:
print "Debug: Connecting to target DB %s on %s" % (
test.get("database_name"),
test.get("database_hostname"),
)
# connect to target DB
testDSN = "dbname=%s user=%s password=%s port=%s host=%s" % (
test["database_name"],
test["database_username"],
test["database_password"],
test["database_port"] or 5432,
test["database_hostname"],
)
testDb = DB()
testCur = testDb.connect(testDSN)
if args.debug:
print "Debug: Connection successful"
except psycopg2.OperationalError, e:
if args.debug:
print "Debug: Connection failed!"
errMessage = e.pgerror or e or "Unknown Error"
message = "Test failed due to an SQL or connection error: %s" % errMessage
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (3,%s,%s);""", (test["test_id"], message)
)
if args.sql:
print cur.query
db.commit()
if testCur and not args.dry:
# update lastrun
cur.execute("""UPDATE pq_test SET lastrun = now() WHERE test_id = %s""", (test["test_id"],))
if args.sql:
print cur.query
try:
if args.debug:
print "Debug: Running test query"
testCur.execute(test["sql"])
except psycopg2.ProgrammingError, e:
if args.debug:
print "Debug: Query failed due to an SQL error."
message = "Test failed due to an SQL error: %s" % e.pgerror
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (3,%s,%s);""",
(test["test_id"], message),
)
if args.sql:
print cur.query
db.commit()
# Whether or not to consider the test a failure due to results
doAnyway = False
if test["fail_on_no_results"] or testCur.rowcount > 0:
doAnyway = True
if doAnyway:
if test["test_type_id"] == 1: # SQL only
if args.debug:
print "Debug: Test is SQL only"
if testCur.rowcount == 1:
row = testCur.fetchone()
if row[0] != True:
if args.debug:
print "Debug: Test failed! (170)"
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (1,%s,'Test failed!');""",
(test["test_id"],),
)
if args.sql:
print cur.query
db.commit()
else:
if args.debug:
print "Debug: Test passed"
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (1,%s,'Test passed!');""",
(test["test_id"],),
)
if args.sql:
print cur.query
db.commit()
else:
if args.debug:
print "Debug: Test failed because the query returned multiple rows"
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (1,%s,'Test failed! The query returned more than one row.');""",
(test["test_id"],),
)
if args.sql:
print cur.query
db.commit()
elif test["test_type_id"] == 2: # + python
if args.debug:
print "Debug: Test is SQL+Python"
data = testCur.fetchall()
try:
if args.debug:
print "Debug: Initializing python test"
t = TestPythonWrapper(test["test_id"])
if args.debug:
print "Debug: Setting python test code"
t.code = test["python"]
if args.debug:
print "Debug: Running python test"
t.run({"data": data})
except RunDenied, e:
if args.debug:
print "Debug: Python test not run for security reasons"
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (3,%s,'Test not run for security reasons.');""",
(test["test_id"],),
)
if args.sql:
print cur.query
db.commit()
except Exception as e:
message = "Python test failed to run for uknown reason. Check for previous error: %s: %s" % (
type(e),
e.args,
)
if args.debug:
print "Debug: %s" % message
cur.execute(
"""INSERT INTO pq_log (log_type_id, test_id, message) VALUES (3,%s,%s);""",
(test["test_id"], message),
)
if args.sql:
print cur.query
db.commit()
finally:
def main():
""" Handle cli arguments """
if __name__ == '__main__':
parser = argparse.ArgumentParser(description='Send pyqual notifications.')
parser.add_argument(
'-d', '--debug',
action='store_true',
default=False,
help='Output debug statements to stdout'
)
args = parser.parse_args()
else:
class FakeArgs(object):
def __init__(self):
self.debug = False
args = FakeArgs()
db = DB()
cur = db.connect(settings.DSN)
cur.execute("""SELECT log_id, message, t.test_id, t.name, t.lastrun, u.email, cc, result_data FROM pq_log l JOIN pq_test t USING (test_id) JOIN pq_user u USING (user_id) WHERE notify = false ORDER BY u.email, t.cc, l.stamp DESC, t.lastrun DESC, test_id;""")
currentEmail = ''
currentCC = ''
logs = ()
#csvFiles = []
pp = pprint.PrettyPrinter(indent=2)
if cur.rowcount > 0:
for l in cur.fetchall():
logs += (l['log_id'], )
if l['email'] != currentEmail or l['cc'] != currentCC:
if currentEmail != '':
if args.debug:
print 'Debug: Sending TO: %s CC: %s (120)' % (currentEmail, currentCC)
msg.test_results = testResults
msg.result_data = resultData
msg.setMessage()
for f in msg.csvFiles:
if args.debug:
print 'Debug: Attaching CSV'
part = MIMEBase('application', "octet-stream")
f[1].seek(0)
part.set_payload(f[1].read())
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="%s.csv"' % f[0])
msg.msg.attach(part)
msg.send(settings.EMAIL_SENDER, currentEmail, 'Pyqual Test Results', cc=currentCC)
msg = LogNotify()
currentEmail = l['email']
currentCC = l['cc']
testResults = []
msg.csvFiles = []
resultData = []
if re.search('passed', l['message'], re.IGNORECASE):
result = 'Success'
elif re.search('fail', l['message'], re.IGNORECASE):
result = 'Failure'
else:
result = 'Unknown'
testResults.append((l['test_id'], l['name'], result))
if l.get('result_data'):
if args.debug:
print 'Debug: Found data'
data = pickle.loads(l.get('result_data'))
if data:
try:
for key, val in data.iteritems():
if is_list_of_tuples(val):
strData = '\n'.join([','.join(map(str, x)) for x in val])
if strData:
tf = tempfile.NamedTemporaryFile()
tf.seek(0)
tf.write(strData)
tf.flush()
os.fsync(tf)
msg.csvFiles.append((key, tf))
else:
strData = pp.pformat(val)
if strData:
if args.debug:
print 'Debug: storing data'
resultData.append( (l['test_id'], strData) )
except:
if args.debug:
print "Debug: Failure iterating data for test_id = %s" % l['test_id']
if args.debug:
print 'Debug: Sending TO: %s CC: %s (150)' % (currentEmail, currentCC)
msg.test_results = testResults
msg.result_data = resultData
msg.setMessage()
for f in msg.csvFiles:
if args.debug:
print 'Debug: Attaching CSV'
part = MIMEBase('application', "octet-stream")
f[1].seek(0)
part.set_payload(f[1].read())
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="%s.csv"' % f[0])
msg.msg.attach(part)
msg.send(settings.EMAIL_SENDER, currentEmail, 'Pyqual Test Results', cc=currentCC)
else:
if args.debug:
print "Debug: Nothing to send."
if len(logs) > 0:
cur.execute("UPDATE pq_log SET notify = true WHERE log_id IN %s", (logs, ))
db.commit()
db.disconnect()
def POST(
self,
test_id = None,
name = None,
cc = None,
schedule_id = None,
database_id = None,
test_type_id = None,
user_id = None,
sql = None,
python = None,
fail_on_no_results = False):
""" Insert/update a test
"""
# do
action = None
db = DB()
cur = db.connect(settings.DSN)
#cur.execute("""SELECT TRUE AS exist FROM pq_test WHERE test_id = %s""" % test_id)
#res = cur.fetchone()
#if res['exist']:
if test_id:
cur.execute(
"""UPDATE pq_test
SET
name = %s,
schedule_id = %s,
database_id = %s,
test_type_id = %s,
cc = %s,
sql = %s,
python = %s,
user_id = %s,
fail_on_no_results = %s
WHERE test_id = %s;""",
(name, schedule_id, database_id, test_type_id, cc, sql, python, user_id, fail_on_no_results, test_id)
)
action = Updated()
else:
cur.execute(
"""INSERT INTO pq_test (name, schedule_id, database_id, test_type_id, cc, sql, python, user_id, fail_on_no_results) VALUES (%s,%s,%s,%s,%s,%s,%s,%s, %s);""",
(name, schedule_id, database_id, test_type_id, cc, sql, python, user_id or auth.user_id, fail_on_no_results)
)
action = Inserted()
if cur.rowcount > 0:
db.commit()
db.disconnect()
if type(action) == type(Updated()):
return { 'result': 'success', 'message': 'Test updated successfully.'}
elif type(action) == type(Inserted()):
return { 'result': 'success', 'message': 'Test added successfully.'}
else:
return { 'result': 'failure', 'message': 'Something was successful? Add/update reported successful, but we have no idea what happened.'}
else:
db.rollback()
db.disconnect()
return {
'result': 'failure',
'message': 'Add/update failed.'
}