def db_setLastVisit(ID):
sql='''
UPDATE users
SET last_visit = NOW()
WHERE id='{:d}';
'''.format(ID)
sql_execute(sql, fetch_all=False)
def db_addUserInChat(userID, chatID, permission=0):
sql = '''
INSERT INTO permissions_users (chat_id, user_id, permission)
VALUES ('{:d}', '{:d}', '{:d}')
'''.format(chatID, userID, permission)
sql_execute(sql, fetch_all=False)
return {'status': 1}
def db_blockProfile(ID, status=True):
sql='''
UPDATE users
SET is_blocked='{}'
WHERE id='{:d}';
'''.format(status, ID)
sql_execute(sql, fetch_all=False)
def db_setOnline(ID, status=True):
sql='''
UPDATE users
SET is_online='{}'
WHERE id='{:d}'
'''.format(status, ID)
sql_execute(sql, fetch_all=False)
def db_setActive(ID, status=True):
sql='''
UPDATE users
SET is_confirmed='{}'
WHERE id='{:d}'
'''.format(status, ID)
sql_execute(sql, fetch_all=False)
def db_FullDelProfile(ID):
sql='''
DELETE FROM auth
WHERE user_id='{:d}';
DELETE FROM users
WHERE id='{:d}';
'''.format(ID, ID)
sql_execute(sql, fetch_all=False)
return {'status': 1}
def db_updateProfileInfo(ID, data, change_pw=False):
rows = []
if 'password' in data.keys():
data.update({'password': sha256(data['password'].encode()).hexdigest()})
for key in data:
if not change_pw:
if not key in ('first_name', 'second_name'):
return {'status': 0, 'message': 'Неизвестное поле. Менять можно только first_name/second_name'}
else:
if not key in 'password':
return {'status': 0, 'message': 'Неизвестное поле. Менять можно только password'}
if data[key]:
if not change_pw:
sql='''
SELECT first_name, second_name
FROM users
WHERE id='{:d}'
'''.format(ID)
else:
sql = '''
SELECT password
FROM auth
WHERE user_id='{:d}'
'''.format(ID)
answer = sql_execute(sql, fetch_all=False)
if data[key] == answer[key]: # Если введённое и из БД поля эквиваленты, то выкидываем ошибку.
rows.append(key)
continue
if not change_pw:
sql = '''
UPDATE users
SET {}='{}'
WHERE id='{:d}';
'''.format(key, data[key], ID)
else:
sql = '''
UPDATE auth
SET password='******'
WHERE user_id='{:d}'
'''.format(data['password'], ID)
sql_execute(sql, fetch_all=False)
if not len(rows):
return {'status': 1}
elif len(rows) >= 1:
return {'status': 1, 'message': 'Эквивалентное поле {} не было изменено'.format(rows)}
else:
return {'status': 0, 'message': 'Эквивалентные поля {} не были изменены'.format(rows)}
def db_addMessageForChat(userID, chatID, content, section_id=0):
sql = '''
INSERT INTO message (context, created_at, section_id)
VALUES ('{}', NOW(), '{:d}')
RETURNING id;
'''.format(content, section_id)
messageID = sql_execute(sql, fetch_all=True)
sql = '''
INSERT INTO messages_users (chat_id, user_id, message_id)
VALUES ('{:d}', '{:d}', '{:d}')
'''.format(chatID, userID, messageID)
sql_execute(sql, fetch_all=False)
return {'status': 1}
def db_addProfile(data):
data.update({'password': sha256(data['password'].encode()).hexdigest()})
sql='''
INSERT INTO users (first_name, second_name, created_at, last_visit, is_blocked, is_online, is_deleted, is_confirmed)
VALUES ('{first_name}', '{second_name}', NOW(), NOW(), false, false, false, false)
RETURNING id;
'''.format(**data)
user_id = sql_execute(sql, fetch_all=True)
sql = """
INSERT INTO auth (user_id, login, password, email)
VALUES ('{:d}', '{login}', '{password}', '{email}');
""".format(user_id[0]['id'], **data)
sql_execute(sql, fetch_all=False)
return {'status': 1, 'message': 'На Вашу почту было отправлено сообщение ссылкой подтверждения. Перейдите по ней.'}
def db_getUserIDbyEmail(data):
if type(data) == str:
sql="SELECT user_id FROM auth WHERE email='{}'".format(data)
elif type(data) == dict:
sql = "SELECT user_id FROM auth WHERE email='{email}'".format(**data)
user_id = sql_execute(sql, fetch_all=False)
return False if user_id is None else user_id['user_id']
def db_isEmailUsed(data):
sql='''
SELECT count(user_id) from auth where email='{email}'
'''.format(**data)
answer = sql_execute(sql, fetch_all=False)
print(bool(answer['count']))
return bool(answer['count'])
def db_getProfileInfo(ID):
sql='''
SELECT first_name, second_name, id, last_visit, is_deleted, is_blocked, email, is_confirmed
FROM users
WHERE id='{:d}';
'''.format(ID)
return sql_execute(sql, fetch_all=False)
def db_restoreProfile(ID):
sql='''
UPDATE users
SET is_deleted=false
WHERE id='{:d}';
'''.format(ID)
return sql_execute(sql, fetch_all=False)
def db_updateMessage(messageID, content):
sql = '''
UPDATE message
SET content='{:s}' and is_edited=true
WHERE id={:d} and section_id=0
'''.format(content, messageID)
return sql_execute(sql, fetch_all=False)
def db_getChat(chatID):
sql = '''
SELECT name, created_at
FROM chat
WHERE id={:d}
'''.format(chatID)
return sql_execute(sql, fetch_all=True)
def db_getMessagesFromChat(chatID):
sql = '''
SELECT user_id, content, created_at, section_id
FROM messages_users, message
WHERE chat_id='{:d}'
'''.format(chatID)
return sql_execute(sql, fetch_all=True)
def db_addChat(nameChat):
sql = '''
INSERT INTO chat (name, created_at, is_deleted)
VALUES ('{name}', NOW(), false)
RETURNING id;
'''.format(**nameChat)
chatID = sql_execute(sql, fetch_all=False)
return chatID['id']
def db_getChatFromUser(userID, chatID):
sql = '''
SELECT name, created_at
FROM chat
WHERE (
SELECT chat_id
FROM permissions_users
WHERE user_id={:d})={:d}
'''.format(userID, chatID)
return sql_execute(sql, fetch_all=True)
def db_isProfileExists(data):
sql="SELECT count(login) FROM auth"
if type(data) == int:
sql += " WHERE user_id='{:d}';".format(data)
elif type(data) == dict:
sql += " WHERE login='******';".format(**data)
users = sql_execute(sql, fetch_all=False)['count']
return bool(users)
def db_delChat(chatID, fully=False):
if fully:
sql = '''
DELETE FROM chat
WHERE id={:d}
'''.format(chatID)
else:
sql = '''
UPDATE chat
SET is_deleted=true
WHERE id={:d}
'''.format(chatID)
return sql_execute(sql, fetch_all=False)
def db_delMessage(messageID, fully=False):
if fully:
sql = '''
DELETE FROM message
WHERE id={:d}
'''.format(messageID)
else:
sql = '''
UPDATE message
SET is_deleted=true
WHERE id={:d}
'''.format(messageID)
return sql_execute(sql, fetch_all=True)
def db_isAuthDataValid(data):
print('-- db_isAuthDataValid() --')
print(data)
sql='''
SELECT user_id
FROM auth
WHERE login='******' AND password='******';
'''.format(**data)
answer = sql_execute(sql, fetch_all=False)
print(answer)
print('------------------------')
if not answer:
return False
else:
return True
def db_getUserIDbyLogin(data):
sql="SELECT user_id FROM auth WHERE login='******';".format(**data)
user_id = sql_execute(sql, fetch_all=False)
return False if user_id is None else user_id['user_id']
def db_getProfilesInfo():
sql='''
SELECT first_name, second_name, id, last_visit, is_deleted, is_blocked, email, is_confirmed
FROM users;
'''
return sql_execute(sql, fetch_all=True)