def get_free_hotel_room(hid, rtid, arrival, departure):
return DBQuery(
"""SELECT room.rid FROM room
JOIN hotel_rooms ON hotel_rooms.rid = room.rid
WHERE room.rid IN (SELECT free_rooms_in_time_set(%s,%s))
AND hid = %s AND rtid = %s
LIMIT 1""", (arrival, departure, hid, rtid))
def get_io_index_performance():
return DBQuery("""SELECT relname,
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read) + 1),2) as heap_ratio
FROM pg_statio_user_tables
GROUP BY relname
ORDER BY relname;""")
def delete_person_order(username, bid):
person_query = User.get_person_id(username)
if person_query.code == DBQuery.CODE_OK and person_query.result:
pid = person_query.result[0][0]
else:
return person_query
return DBQuery("DELETE FROM booked WHERE bid = %s AND pid = %s", (bid, pid))
def get_io_cache_performance():
return DBQuery("""SELECT relname,
sum(idx_blks_read) as idx_read,
sum(idx_blks_hit) as idx_hit,
round((sum(idx_blks_hit) - sum(idx_blks_read)) / (sum(idx_blks_hit)+1), 2) as idx_ratio
FROM pg_statio_user_tables
GROUP BY relname
ORDER BY relname;""")
def query_check_out(hid):
return DBQuery(
"""SELECT bid, fname, lname, checkout, booked.rid
FROM booked
JOIN person ON (booked.pid = person.pid)
JOIN hotel_rooms ON (booked.rid = hotel_rooms.rid)
WHERE hid = %s
AND bid IN (SELECT bid FROM registration
WHERE regout IS NULL)
ORDER BY checkout""", (hid, ))
def query_check_in(hid):
now = strftime("%Y-%m-%d %H:%M:%S")
return DBQuery(
"""SELECT bid, fname, lname, checkin, booked.rid
FROM booked
JOIN person ON (booked.pid = person.pid)
JOIN hotel_rooms ON (booked.rid = hotel_rooms.rid)
WHERE hid = %s AND checkin < %s AND checkout > %s
AND bid NOT IN (SELECT bid FROM registration)
ORDER BY checkin""", (hid, now, now))
def search_hotels(arrival, departure, city, people):
return DBQuery(
"""SELECT hotel_rooms.hid,title,rating,room.rtid,rtype,price
FROM room
JOIN roomtype ON room.rtid = roomtype.rtid
JOIN hotel_rooms ON hotel_rooms.rid = room.rid
JOIN hotel ON hotel_rooms.hid = hotel.hid
JOIN address ON hotel.aid = address.aid
WHERE room.rid IN (SELECT free_rooms_in_time_set(%s,%s))
AND city = %s
AND capacity >= %s
GROUP BY hotel_rooms.hid, rtype, room.rtid, price,title,rating
ORDER BY price ASC""",
(arrival, departure, city, people))
def get_orders(username):
person_query = User.get_person_id(username)
if person_query.code == DBQuery.CODE_OK and person_query.result:
pid = person_query.result[0][0]
else:
return person_query
return DBQuery("""SELECT booked.bid,title,checkin,checkout,
rtype,room.rid,cost,rating,regid
FROM booked
JOIN room ON booked.rid = room.rid
JOIN roomtype ON room.rtid = roomtype.rtid
JOIN hotel_rooms ON room.rid = hotel_rooms.rid
LEFT OUTER JOIN registration ON booked.bid = registration.bid
JOIN hotel ON hotel_rooms.hid = hotel.hid
WHERE pid = %s""", (pid,))
def query_update_person(fname, lname, email, username):
userid_query = User.get_user_id(username)
if userid_query.code == DBQuery.CODE_OK and userid_query.result:
uid = userid_query.result[0][0]
else:
return userid_query
today = strftime("%Y-%m-%d")
return DBQuery("""UPDATE person
SET (fname, lname, email) = (%(fname)s, %(lname)s, %(email)s)
WHERE uid = %(uid)s;
INSERT INTO person (uid, fname, lname, email, reg_date)
SELECT %(uid)s, %(fname)s, %(lname)s, %(email)s, %(today)s
WHERE NOT EXISTS (SELECT 1 FROM person WHERE uid = %(uid)s)""",
{'fname': fname,
'lname': lname,
'email': email,
'uid': uid,
'today': today})
def get_disk_performance():
return DBQuery("""SELECT TABLE_NAME,
pg_size_pretty(index_bytes) AS INDEX,
pg_size_pretty(table_bytes) AS TABLE,
pg_size_pretty(total_bytes) AS total
FROM (
SELECT
*, total_bytes - index_bytes - COALESCE(toast_bytes, 0) AS table_bytes
FROM ( SELECT
relname AS TABLE_NAME,
pg_total_relation_size(c.oid) AS total_bytes,
pg_indexes_size(c.oid) AS index_bytes,
pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND nspname = 'public'
) a
) a
ORDER BY TABLE_NAME;""")
def get_person(username):
return DBQuery("""SELECT fname, lname, email FROM person
WHERE uid = (SELECT uid FROM users
WHERE username = %s)""", (username,))
def get_manager_hotel(username):
return DBQuery("""SELECT hotel.hid, title FROM users
JOIN person ON (person.uid = users.uid)
JOIN employee ON (employee.pid = person.pid)
JOIN hotel ON (employee.hid = hotel.hid)
WHERE username = %s""", (username,))
def query_hash_role(username):
return DBQuery("SELECT hash, role FROM users WHERE username = %s", (username,))
def query_username(username):
return DBQuery("SELECT * FROM users WHERE username = %s", (username,))
def query_add(username, password, role):
return DBQuery("""INSERT INTO users (username, hash, role)
VALUES (%s, %s, %s)""", (username, password, role))
def get_person_id(username):
return DBQuery("""SELECT pid FROM person
JOIN users ON person.uid = users.uid
WHERE username = %s""", (username,))
def query_register_check_in(bid):
now = strftime("%Y-%m-%d %H:%M:%S")
return DBQuery(
"""INSERT INTO registration (bid, regin)
VALUES (%s, %s)""", (bid, now))
def get_role(username):
return DBQuery("SELECT role FROM users WHERE username = %s", (username,))
def registered_check_query(bid):
return DBQuery("SELECT * FROM registration WHERE bid = %s", (bid,))
def get_roomtype(rtid):
return DBQuery("SELECT rtype, price FROM roomtype WHERE rtid = %s",
(rtid, ))
def get_cities():
return DBQuery("""SELECT DISTINCT city FROM hotel
JOIN hotel_rooms ON hotel.hid = hotel_rooms.hid
JOIN address ON hotel.aid = address.aid
ORDER BY city""")
def query_add(rid, pid, arrival, departure):
return DBQuery(
"""INSERT INTO booked (rid,pid,checkin,checkout)
VALUES (%s, %s, %s, %s)""",
(rid, pid, arrival, departure))
def query_register_check_out(bid):
now = strftime("%Y-%m-%d %H:%M:%S")
return DBQuery(
"""UPDATE registration
SET regout = %s
WHERE bid = %s""", (now, bid))
def get_order_extra(hid):
return DBQuery(
"""SELECT title, rating, country, city, street, appartment
FROM hotel JOIN address ON hotel.aid = address.aid
WHERE hid = %s""", (hid, ))
def get_user_id(username):
return DBQuery("SELECT uid FROM users WHERE username = %s", (username,))
