def get_free_space(this, r_id):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"SELECT COUNT(*) FROM UTKOZETRESZVETEL WHERE VERSENYID = :1",
[r_id])
db_data: list[tuple] = cursor.fetchall()
ossz = list()
for s in db_data:
ossz.append(s)
cursor.execute("SELECT BESTOF FROM FORDULO WHERE VERSENYID = :1",
[r_id])
db_data: list[tuple] = cursor.fetchall()
bof = list()
for s in db_data:
bof.append(s)
ConfigLoader.get_connection_pool().release(connection)
free = bof[0][0] - ossz[0][0]
return free
except Exception as e:
print(e)
return list()
def executesql(this, sql, args: list) -> list:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(sql, args)
ret = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
return ret
except Exception as e:
print(e)
return list()
def insert_to_room(this, uname, room_id) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("INSERT into kozossegtagja values (:1, :2)",
[uname, room_id])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def make_room(this, r_id, r_name) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("INSERT into kozosseg values (:1, :2)",
[r_id, r_name])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def add_answer(this, r_kerdes, r_name, r_adott, r_jel) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("INSERT into ADOTTVALASZ values (-1, :1,:2,:3,:4)",
[r_jel, r_kerdes, r_name, r_adott])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def join_comp(this, r_id, r_name) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("INSERT into UTKOZETRESZVETEL values (:1, :2)",
[r_id, r_name])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def get_number(this, r_id):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT BESTOF FROM FORDULO WHERE VERSENYID=:1",
[r_id])
db_data: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
return db_data[0][0]
except Exception as e:
print(e)
return 0
def delete_comp(this, r_id) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("DELETE FROM UTKOZETRESZVETEL WHERE VERSENYID = :1",
[r_id])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def get_all(self):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT FELHASZNALONEV FROM JATEKOS")
result: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
return result
except Exception as e:
print(e)
return None
def delete_forum_msg(this, uname, date) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"DELETE FROM UZENET WHERE KULDO = :1 AND IDOPONT = :2",
[uname, date])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def run_comp(this, r_id):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT count(*) FROM UTKOZET WHERE VERSENYID=:1",
[r_id])
db_data: list[tuple] = cursor.fetchall()
db = db_data[0][0]
ConfigLoader.get_connection_pool().release(connection)
print(db_data)
return not (db == 0)
except Exception as e:
print(e)
return False
def join_play(this, r_id, r_name, r_date) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"INSERT into UTKOZET values (:1, TO_DATE(:2, 'YYYY-MM-DD-HH24:MI:SS'),:3)",
[r_id, r_date, r_name])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def update_msg(this, uname, time, new) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"UPDATE UZENET SET SZOVEG = :1 WHERE KULDO = :2 AND IDOPONT = :3",
[new, uname, time])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def delete_reszvetel(this, r_id, r_name):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"DELETE FROM UTKOZETRESZVETEL WHERE VERSENYID = :1 AND FELHASZNALONEV = :2",
[r_id, r_name])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def send_message(this, u_name, room_id, content) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"INSERT INTO UZENET(KULDO, KOZOSSEG, IDOPONT, SZOVEG) VALUES (:1, :2, current_timestamp, :3)",
[u_name, room_id, content])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def get_room_id(this) -> int:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT max(ID)+1 FROM KOZOSSEG")
db_data: list[tuple[int, str]] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for room_id in db_data:
result.append((room_id))
return result[0][0]
except Exception as e:
print(e)
return -1
def insert(self, uname: str, email: str, pwdhash: str, salt: str,
birth_date: cx_Oracle.Date) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"INSERT INTO JATEKOS(FELHASZNALONEV, EMAIL, ADMIN, JELSZO, SALT, SZULDATUM) VALUES (:1, :2, :3, :4, :5, to_date(:6, 'yyyy-mm-dd'))",
[uname, email, False, pwdhash, salt, birth_date])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def get_unfinished_duel(this, challenger: str, challenged: str,
pending: int) -> dict:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"SELECT PARBAJ.ID, PARBAJKERDESE.SZOVEG, KERDES.BETUJEL FROM PARBAJ\
INNER JOIN PARBAJKERDESE ON PARBAJ.ID = PARBAJKERDESE.ID\
INNER JOIN PARBAJRAHIV ON PARBAJ.ID = PARBAJRAHIV.ID\
INNER JOIN PARBAJRAHIVOTT ON PARBAJ.ID = PARBAJRAHIVOTT.ID\
INNER JOIN KERDES ON PARBAJKERDESE.SZOVEG = KERDES.SZOVEG\
WHERE PARBAJRAHIV.JATEKOS = :1 AND PARBAJRAHIVOTT.JATEKOS = :2 AND PARBAJ.PENDING = :3 AND PARBAJ.NYERTES IS NULL",
[challenger, challenged, pending])
data = cursor.fetchone()
ret = {"id": data[0], "question": data[1], "correct_ans": data[2]}
cursor.execute(
"SELECT BETUJEL, SZOVEG FROM VALASZ WHERE KERDESSZOVEG = :1 ORDER BY BETUJEL ASC",
[ret["question"]])
ret["answers"] = cursor.fetchall()
return ret
except Exception as e:
print(e)
return dict()
def is_playing(this, r_id, r_name):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"SELECT count(*) FROM UTKOZET WHERE VERSENYID=:1 AND FELHASZNALONEV = :2",
[r_id, r_name])
db_data: list[tuple] = cursor.fetchall()
ossz = list()
for s in db_data:
ossz.append(s)
ConfigLoader.get_connection_pool().release(connection)
return not (ossz[0][0] == 0)
except Exception as e:
print(e)
return False
def get_non_member_room(this, u_name) -> list[tuple[int, str]]:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"SELECT * FROM KOZOSSEG WHERE KOZOSSEG.ID != 0 and KOZOSSEG.ID not in (Select KOZOSSEG from KOZOSSEGTAGJA where FELHASZNALONEV = :1)",
[u_name])
db_data: list[tuple[int, str]] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for room_id, room_name in db_data:
result.append((room_id, room_name))
return result
except Exception as e:
print(e)
return list()
def get_comp_id(this, r_name):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT ID FROM VERSENY WHERE NEV = :1", [r_name])
db_data: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for id in db_data:
result.append(id)
return result
except Exception as e:
print(e)
return list()
def get_new_id(this):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT max(ID)+1 FROM VERSENY")
db_data: list[tuple[int, str]] = cursor.fetchall()
if db_data[0][0] is None:
return 1
ConfigLoader.get_connection_pool().release(connection)
result = list()
for v_id in db_data:
result.append((v_id))
return result[0][0]
except Exception as e:
print(e)
return -1
def new_comp(this, r_id, r_name, r_date, r_best) -> bool:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("INSERT into VERSENY values (:1, :2)",
[r_id, r_name])
connection.commit()
cursor.execute(
"INSERT into FORDULO values (:1, to_date(:2, 'yyyy-mm-dd'), :3)",
[r_id, r_date, int(r_best)])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def insert(this, title: str, text: str, poster: Image) -> bool:
bytes = io.BytesIO()
poster.save(bytes, "png")
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"INSERT INTO HIRDETES(CIM, SZOVEG, PLAKAT) VALUES (:1, :2, :3)",
[title, text, bytes.getvalue()])
connection.commit()
ConfigLoader.get_connection_pool().release(connection)
return True
except Exception as e:
print(e)
return False
def get_players(this, r_id):
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("SELECT * FROM UTKOZETRESZVETEL WHERE VERSENYID=:1",
[r_id])
db_data: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for (id, name) in db_data:
result.append((id, name))
return result
except Exception as e:
print(e)
return list()
def get_play(this, uname: str) -> list[tuple]:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"select VERSENY.NEV, UTKOZET.KEZDES FROM VERSENY, UTKOZET WHERE VERSENY.ID = UTKOZET.VERSENYID AND UTKOZET.FELHASZNALONEV = :1",
[uname])
db_data: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for id, name in db_data:
result.append((id, name))
return result
except Exception as e:
print(e)
return list()
def get_all_comp(this, uname) -> list[tuple]:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute(
"SELECT * FROM VERSENY WHERE ID NOT IN (SELECT VERSENYID FROM UTKOZETRESZVETEL WHERE FELHASZNALONEV=:1)",
[uname])
db_data: list[tuple] = cursor.fetchall()
ConfigLoader.get_connection_pool().release(connection)
result = list()
for id, name in db_data:
result.append((id, name))
return result
except Exception as e:
print(e)
return list()
def create_new_duel(self, challenger: str, challenged: str) -> None:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.callproc("ADD_PARBAJ", [challenger, challenged])
connection.commit()
except Exception as e:
print(e)
def delete_duel_by_id(this, id: int) -> None:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("DELETE FROM PARBAJ WHERE ID = :1", [id])
connection.commit()
except Exception as e:
print(e)
def set_winner(this, id: int, winner: str) -> None:
try:
connection = ConfigLoader.get_connection_pool().acquire()
cursor = connection.cursor()
cursor.execute("UPDATE PARBAJ SET NYERTES = :1 WHERE ID = :2",
[winner, id])
connection.commit()
except Exception as e:
print(e)