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 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 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 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 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 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.'
}