89 lines
2.6 KiB
Python
89 lines
2.6 KiB
Python
from backend.code.modules.database.database import DatabaseModule
|
|
|
|
class SessionRepository():
|
|
def __init__(self, repo: DatabaseModule):
|
|
self.conn = repo.conn
|
|
self.cursor = repo.cursor
|
|
|
|
def SelectByUsername(self, username: str):
|
|
sql = '''
|
|
SELECT a.*, u.name, u.surnames, u.email
|
|
FROM user_accounts a
|
|
JOIN users u ON u.id = a.user_id
|
|
WHERE a.username = ?
|
|
AND a.active
|
|
'''
|
|
self.cursor.execute(sql, [username])
|
|
return self.cursor.fetchone()
|
|
|
|
def SelectByToken(self, token: str):
|
|
sql = '''
|
|
SELECT uas.account_id, u.id
|
|
FROM user_account_sessions uas
|
|
JOIN user_accounts ua ON uas.account_id = ua.id
|
|
JOIN users u ON ua.user_id = u.id
|
|
WHERE token = ?
|
|
AND uas.active
|
|
'''
|
|
self.cursor.execute(sql, [token])
|
|
return self.cursor.fetchone()
|
|
|
|
def RefreshAttempts(self, account_id: int, attempts: int, locked: bool):
|
|
sql = '''
|
|
UPDATE user_accounts
|
|
SET attempts = ?, locked = ?
|
|
WHERE id = ?
|
|
'''
|
|
self.cursor.execute(sql, [attempts, locked, account_id])
|
|
self.conn.commit()
|
|
return self.cursor.rowcount
|
|
|
|
def RegisterSession(self, accountId: int, token: str):
|
|
sql = '''
|
|
INSERT INTO user_account_sessions(account_id, token)
|
|
VALUES (?, ?)
|
|
'''
|
|
self.cursor.execute(sql, [accountId, token])
|
|
self.conn.commit()
|
|
return self.cursor.rowcount
|
|
|
|
def DisableToken(self, token: str):
|
|
sql = '''
|
|
UPDATE user_account_sessions
|
|
SET active = false
|
|
WHERE token = ?
|
|
'''
|
|
self.cursor.execute(sql, [token])
|
|
self.conn.commit()
|
|
return self.cursor.rowcount
|
|
|
|
def InsertRecoverRequest(self, accountId: int, token: str):
|
|
sql = '''
|
|
INSERT INTO user_account_recover_requests(account_id, token)
|
|
VALUES(?, ?)
|
|
'''
|
|
self.cursor.execute(sql, [accountId, token])
|
|
self.conn.commit()
|
|
return self.cursor.rowcount
|
|
|
|
def SelectRecoverRequest(self, accountId: int, token: str):
|
|
sql = '''
|
|
SELECT *
|
|
FROM user_account_recover_requests
|
|
WHERE account_id = ?
|
|
AND token = ?
|
|
AND active
|
|
'''
|
|
self.cursor.execute(sql, [accountId, token])
|
|
return self.cursor.fetchone()
|
|
|
|
def DisableRecoverRequest(self, token: str):
|
|
sql = '''
|
|
UPDATE user_account_recover_requests
|
|
SET active = false
|
|
WHERE token = ?
|
|
AND active
|
|
'''
|
|
self.cursor.execute(sql, [token])
|
|
self.conn.commit()
|
|
self.cursor.rowcount |