def create_todo():
title = request.json.get('title', None)
notes = request.json.get('notes', None)
conn = connect_db()
cur = conn.cursor()
cur.execute(
"""
INSERT INTO
todo
(
title,
notes
)
VALUES
(?, ?)
""", (title, notes))
conn.commit()
id = cur.lastrowid
cur.execute("""
SELECT
*
FROM
todo
WHERE
id = '{0}';
""".format(id))
rows = cur.fetchall()
return jsonify(rows)
def delete(id):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""DELETE FROM roomTable WHERE userId = %s;""", (id,))
conn.commit()
conn.close()
def delete(username):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""DELETE FROM userTable WHERE username = %s;""", (id))
conn.commit()
conn.close()
def delete(id):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""DELETE FROM timeslotTable WHERE timeid = %s;""", (id, ))
conn.commit()
conn.close()
def delete(id):
conn = connect_db()
if conn:
cur = conn.cursor()
print(id)
cur.execute("""DELETE FROM reservationTable WHERE reservationId = %s;""", (id,))
conn.commit()
conn.close()
def delete_todo(id):
conn = connect_db()
cur = conn.cursor()
cur.execute("""
DELETE FROM todo WHERE id='{0}';
""".format(id))
conn.commit()
return jsonify({'message': 'successfully deleted'})
def delete(id):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""DELETE FROM equipmentTable WHERE equipmentid = %s;""",
(id, ))
conn.commit()
conn.close()
def insert(id, room, description, holder, timeslot, equipment):
conn = connect_db()
if conn:
cur = conn.cursor()
print(timeslot)
cur.execute("""INSERT INTO reservationTable(reservationId, room, description, holder, timeslot, equipment) VALUES
(%s, %s, %s, %s, %s, %s);""", (id, room, description, holder, timeslot, equipment))
conn.commit()
conn.close()
def update(username, password, capstone):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""UPDATE userTable SET password = %s AND capstone = %s WHERE username = %s;""",
(password, capstone, username))
conn.commit()
conn.close()
def insert(username, password, capstone):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""INSERT INTO userTable(username, password, capstone) VALUES
(%s, %s);""", (username, password, capstone))
conn.commit()
conn.close()
def insert(equipmentId, laptops, projectors, whiteboards):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""INSERT INTO equipmentTable(equipmentId,laptops, projectors, whiteboards) VALUES
(%s, %s, %s, %s);""", (equipmentId, laptops, projectors, whiteboards))
conn.commit()
conn.close()
def update(id, roomId, userId, description, timeslot):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""UPDATE reservationTable SET room = %s, holder = %s,
description = %s, timeslot = %s WHERE reservationId = %s;""",
(roomId, userId, description, timeslot, id))
conn.commit()
conn.close()
def find(id):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM roomTable WHERE roomId = %s;""", (id,))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findByUserId(userId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM reservationTable WHERE holder = %s;""", (userId,))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findAll():
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM reservationTable""")
data = cur.fetchall()
conn.close()
return data
else:
return []
def update(id, laptops, projectors, whiteboards):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""UPDATE equipmentTable SET equipmentId = %s, laptops = %s,
projectors = %s, whiteboards = %s WHERE equipmentId= %s;""",
(id, laptops, projectors, whiteboards, id))
conn.commit()
conn.close()
def update(id, roomId, userId, description, timeslotId, equipmentId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""UPDATE waitingTable SET room = %s, reservee = %s,
description = %s, timeslot = %s WHERE waitingId = %s;""",
(roomId, userId, description, timeslotId, equipmentId, id))
conn.commit()
conn.close()
def update(id, st, et, date, block):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""UPDATE timeslotTable SET startTime = %s, endTime = %s,
date = %s, block = %s WHERE timeid = %s;""",
(st, et, date, block, id))
conn.commit()
conn.close()
def findByRoom(roomId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM reservationTable WHERE room = %s;""", (roomId))
data = cur.fetchall()
conn.close()
return data
else:
return []
def insert(waitingId, room, reservee, description, timeslot, equipment):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""INSERT INTO waitingTable(waitingId, room, reservee, description, timeslot, equipment) VALUES
(%s, %s, %s, %s, %s, %s);""",
(waitingId, room, reservee, description, timeslot, equipment))
conn.commit()
conn.close()
def get_todos():
conn = connect_db()
cur = conn.cursor()
cur.execute("""
SELECT
*
FROM
todo
""")
rows = cur.fetchall()
return jsonify(rows)
def findUserRes(userId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM reservationTable LEFT OUTER JOIN timeslotTable
ON (reservationTable.timeslot = timeslotTable.timeid) WHERE userid = %s;""", (userId,))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findUser(username):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM timeslotTable WHERE username = %s;""",
(username, ))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findTimeslot(waitingId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""SELECT timeslot FROM waitingTable WHERE waitingId = %s;""",
(waitingId, ))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findByUser(userId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""SELECT * FROM waitingTable LEFT OUTER JOIN timeslotTable ON (waitingTable.timeslot = timeslotTable.timeid) WHERE reservee = %s;""",
(userId, ))
data = cur.fetchall()
conn.close()
return data
else:
return []
def homepage():
conn = connect_db()
cur = conn.cursor()
cur.execute("""
SELECT
*
FROM
todo
""")
rows = cur.fetchall()
return render_template('homepage.html', rows=rows)
def findDateRoom(roomId, date):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute("""SELECT * FROM reservationTable LEFT OUTER JOIN timeslotTable
ON (reservationTable.timeslot = timeslotTable.timeid) WHERE room = %s AND date = %s;""",
(roomId, date))
data = cur.fetchall()
conn.close()
return data
else:
return []
def findByRoom(roomId):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""SELECT waitingId, room, reservee, description, timeslot, equipment, startTime, endTime FROM waitingTable LEFT OUTER JOIN timeslotTable
ON (waitingTable.timeslot = timeslotTable.timeid) WHERE room = %s;""",
(roomId))
data = cur.fetchall()
conn.close()
return data
else:
return []
def test_alerting_logic_one_alert():
start_datetime = datetime.utcnow()
end_datetime = None
log_entry = {
"ip": "127.0.0.1",
"user": "******",
"method": "GET",
"request_path": "blog/index.html",
"section": "blog",
"http_version": "1.1",
"status_code": "200",
"response_size": "1000",
"referrer": "http://test.com/index.html",
"user_agent": "Chrome",
}
# generate exactly 11 rps over 2 minutes
threshold_rps = []
for i in range(60 * 2):
i_datetime = start_datetime + timedelta(seconds=i)
end_datetime = i_datetime
i_log_entry = log_entry.copy()
i_log_entry["date"] = i_datetime
for j in range(11):
threshold_rps.append(i_log_entry)
try:
os.remove("log_analyzer.test.db")
except OSError:
pass
db.connect_db("log_analyzer.test.db")
db.setup_database()
db.insert_log_entries(threshold_rps)
db.create_alerts(end_datetime, 60 * 2, 10)
alerts = db.find_alerts()
assert len(alerts) == 1
def insert(timeslotId, startTime, endTime, date, block, username):
conn = connect_db()
if conn:
cur = conn.cursor()
cur.execute(
"""INSERT INTO timeslotTable(timeid, startTime, endTime, date, block, username) VALUES
(%s, %s, %s, %s, %s, %s);""", (
timeslotId,
startTime,
endTime,
date,
block,
username,
))
conn.commit()
conn.close()
def main():
connection = connect_db()
run_sql_file("db_schema.sql", connection)
connection.close()
