def find_by_id(cat_id):
"""Найти каталог по id."""
sql = 'SELECT * FROM Catalogue WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (cat_id,))
row = cursor.fetchone()
return CatalogueGateway.__deserialize__(row)
def find_by_id(file_id):
"""Найти файл по id."""
sql = 'SELECT * FROM File WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (file_id,))
row = cursor.fetchone()
return FileGateway.__deserialize__(row)
def get_session(session_key):
sql = 'SELECT * FROM Session WHERE session_key = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (session_key,))
row = cursor.fetchone()
return SessionActiveRecord.deserialize(row)
def create(self):
sql = 'INSERT INTO Groups(title) VALUES (%s)'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.title,))
self.id = cursor.lastrowid
if self.id is not None:
return GroupActiveRecord.get_by_id(self.id)
def restore(self):
if self.id is None:
print("user id is None")
return
sql = 'UPDATE User SET is_deleted = 0 WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.id, ))
self.is_deleted = False
def create(title, author_id):
"""Добавить новый каталог в БД."""
sql = 'INSERT INTO Catalogue(title, author_id) VALUES (%s, %s)'
try:
with DbService.get_connection() as cursor:
cursor.execute(sql, (title, author_id))
except MySQLError as e:
pass # TODO duplicate error?
def get_users():
sql = 'SELECT * FROM User'
with DbService.get_connection() as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
if rows:
return [UserActiveRecord.deserialize(row) for row in rows]
return []
def get_catalogues_without_group(group_id):
sql = 'SELECT * FROM Catalogue WHERE id NOT IN (SELECT catalogue_id FROM GroupsCatalogue WHERE group_id = %s)'
with DbService.get_connection() as cursor:
cursor.execute(sql, (group_id, ))
rows = cursor.fetchall()
if rows:
return [CatalogueActiveRecord.__deserialize__(row) for row in rows]
return []
def create(self):
print("Inserting: ", self.session_key, self.user_id, self.expire_date)
sql = 'INSERT INTO Session(session_key, user_id, expire_date) VALUES (%s, %s, %s)'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.session_key, self.user_id, self.expire_date))
self.id = cursor.lastrowid
if self.id is not None:
return SessionActiveRecord.get_by_identity(self.session_key)
def find(username_like):
username_like += '%'
sql = 'SELECT * FROM User WHERE username LIKE %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (username_like, ))
rows = cursor.fetchall()
if rows:
return [UserActiveRecord.deserialize(row) for row in rows]
return []
def get_by_group_id(group_id):
sql = 'SELECT * FROM Groups INNER JOIN GroupsCatalogue ON Groups.id = GroupsCatalogue.group_id ' \
'INNER JOIN Catalogue ON GroupsCatalogue.catalogue_id = Catalogue.id WHERE group_id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (group_id,))
rows = cursor.fetchall()
if rows:
return [GroupCatalogue.__deserialize__(row) for row in rows]
return []
def get_user_groups(user_id):
"""Вернуть группы, в которых состоит пользователь"""
sql = 'SELECT * FROM Groups INNER JOIN UserGroup ON Groups.id = UserGroup.group_id WHERE user_id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (user_id,))
rows = cursor.fetchall()
if rows:
return [GroupActiveRecord.deserialize(row) for row in rows]
return []
def update(file_data, file_id):
"""Добавить новый файл в БД."""
sql = 'UPDATE File SET title=%s, description=%s, attributes=%s, other_attributes=%s ' \
'WHERE id=%s'
try:
with DbService.get_connection() as cursor:
cursor.execute(sql, (file_data['title'], file_data['description'],
file_data['attributes'], file_data['other_attributes'], int(file_id)))
except MySQLError as e:
print(e)
def create(file_data, user_id, cat_id):
"""Добавить новый файл в БД."""
sql = 'INSERT INTO File(path, title, description, attributes, other_attributes, user_id, catalogue_id) ' \
'VALUES (%s, %s, %s, %s, %s, %s, %s)'
try:
with DbService.get_connection() as cursor:
cursor.execute(sql, (str(file_data['file']), file_data['title'], file_data['description'],
file_data['attributes'], file_data['other_attributes'], user_id, cat_id))
except MySQLError as e:
print(e)
def get_by_group_id(group_id):
"""Вернуть каталоги, которые доступны группе"""
sql = 'SELECT * FROM Catalogue INNER JOIN GroupsCatalogue ' \
'ON Catalogue.id = GroupsCatalogue.catalogue_id WHERE group_id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (group_id, ))
rows = cursor.fetchall()
if rows:
return [CatalogueActiveRecord.__deserialize__(row) for row in rows]
return []
def find_by_cat_id(cat_id):
"""Найти файлы по id каталога."""
sql = 'SELECT * FROM File WHERE catalogue_id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (cat_id,))
all_rows = cursor.fetchall()
files = []
if all_rows is not None:
for row in all_rows:
files.append(FileGateway.__deserialize__(row))
return files
def find(title_like):
if not title_like:
return None
title_like += '%'
sql = 'SELECT * FROM Groups WHERE title LIKE %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (title_like,))
rows = cursor.fetchall()
if rows:
return [GroupActiveRecord.deserialize(row) for row in rows]
return []
def find_by_user_id(user_id):
"""Найти каталоги по id пользователя."""
sql = 'SELECT * FROM Catalogue WHERE author_id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (user_id,))
all_rows = cursor.fetchall()
catalogs = []
if all_rows is not None:
for row in all_rows:
catalogs.append(CatalogueGateway.__deserialize__(row))
return catalogs
def get_groups_without_user(user_id):
"""Вернуть группы, в которых не состоит пользователь"""
# Да простит меня Павел
sql = 'SELECT * FROM Groups WHERE id NOT IN ' \
'(SELECT id FROM Groups INNER JOIN UserGroup ON Groups.id = UserGroup.group_id WHERE user_id = %s);'
with DbService.get_connection() as cursor:
cursor.execute(sql, (user_id,))
rows = cursor.fetchall()
if rows:
return [GroupActiveRecord.deserialize(row) for row in rows]
return []
def delete(self):
if self.id is None:
return
try:
int(self.id)
except ValueError:
print('user id is not int')
return
sql = 'UPDATE User SET is_deleted = 1 WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.id, ))
self.is_deleted = True
def find_by_cat_id_and_user_id(cat_id, user_id):
"""Найти доступ к каталогу по id пользователя и каталога."""
sql = 'SELECT DISTINCT gc.permission FROM Catalogue c ' \
'JOIN GroupsCatalogue gc ON c.id = gc.catalogue_id ' \
'JOIN Groups g ON gc.group_id = g.id ' \
'JOIN UserGroup ug ON g.id = ug.group_id ' \
'JOIN User u ON ug.user_id = u.id ' \
'WHERE u.id = %s AND c.id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (user_id, cat_id))
result = cursor.fetchone()
return result
def create(self):
"""Добавляет пользователя в БД"""
user = UserActiveRecord.get_by_username(self.username)
if user is not None:
raise UserExistException()
sql = 'INSERT INTO User(username, password, is_admin) VALUES (%s, %s, %s)'
try:
with DbService.get_connection() as cursor:
cursor.execute(sql,
(self.username, self.password, self.is_admin))
self.id = cursor.lastrowid
except MySQLError as e:
if e.args[0] == 1062: # DUPLICATE_ENTRY
raise UserExistException()
raise e
def find_shared_by_user_id(user_id):
"""Найти каталоги по id пользователя."""
sql = 'SELECT * FROM Catalogue c ' \
'JOIN GroupsCatalogue gc ON c.id = gc.catalogue_id ' \
'JOIN Groups g ON gc.group_id = g.id ' \
'JOIN UserGroup ug ON g.id = ug.group_id ' \
'JOIN User u ON ug.user_id = u.id ' \
'WHERE u.id = %s AND author_id != %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (user_id, user_id))
all_rows = cursor.fetchall()
catalogs = []
if all_rows is not None:
for row in all_rows:
catalogs.append(CatalogueGateway.__deserialize__(row))
return catalogs
def get_by_id(catalogue_id):
sql = 'SELECT * FROM Catalogue WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (catalogue_id, ))
return CatalogueActiveRecord.__deserialize__(cursor.fetchone())
def save(self):
"""Обновляет информацию о пользователе"""
sql = 'UPDATE User SET is_admin = %s, is_deleted = %s WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.is_admin, self.is_deleted, self.id))
def delete_by_id(cat_id):
"""Удалить каталог по id."""
sql = 'UPDATE Catalogue SET author_id = NULL WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (cat_id,))
def delete(self):
sql = 'UPDATE Session SET user_id = NULL WHERE session_key = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (self.session_key,))
def get_by_username(username):
sql = 'SELECT * FROM User WHERE username = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (username, ))
return UserActiveRecord.deserialize(cursor.fetchone())
def get_by_identity(identity):
sql = 'SELECT * FROM Session WHERE session_key = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (identity,))
return SessionActiveRecord.deserialize(cursor.fetchone())
def get_by_id(identity):
sql = 'SELECT * FROM User WHERE id = %s'
with DbService.get_connection() as cursor:
cursor.execute(sql, (identity, ))
row = cursor.fetchone()
return UserActiveRecord.deserialize(row)