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