def send_message(chat_id: int = 0,
user_id: int = 0,
content: str = 'hello',
added_at: str = "1999-10-10 20:09:07"):
# TODO: В методе чтения сообщений надо будет, напротив, уменьшать число непрочитанных сообщений
# Создадим сообщение в таблице messages
message_id = db.query_all("""
INSERT INTO messages (chat_id, user_id, content, added_at)
VALUES ( %(chat_id)s, %(user_id)s, %(content)s, %(added_at)s )
RETURNING message_id;
""",
chat_id=chat_id,
user_id=user_id,
content=content,
added_at=added_at)
if message_id:
message_id = message_id[0].get('message_id')
# А теперь всем пользователям добавим запись о созданном сообщении
# и увеличим счетчик непрочитанных
db.query_all("""
UPDATE members
SET new_messages = new_messages + 1
WHERE chat_id = %(chat_id)s
/* самому себе счетчик увеличивать не надо :) */
AND user_id != %(user_id)s
RETURNING chat_id
""",
chat_id=chat_id,
user_id=user_id)
return message_id
def read_messages(user_id: int, chat_id: int, last_read_message_id: int,
number_of_messages: int):
db.query_all("""
UPDATE members
SET new_messages = new_messages - %(number_of_messages)s,
last_read_message_id = %(last_read_message_id)s
WHERE chat_id = %(chat_id)s
AND user_id = %(user_id)s
RETURNING last_read_message_id
""",
chat_id=chat_id,
user_id=user_id,
number_of_messages=number_of_messages,
last_read_message_id=last_read_message_id)
def find_users(name):
return db.query_all("""
SELECT user_id, nick
FROM Users
WHERE name=%(name)s
ORDER BY user_id
""", name=name)
def get_chats_list(nick):
return db.query_all("""
SELECT chat_id, topic
FROM Chats
JOIN Members USING(chat_id)
JOIN Users USING(user_id)
WHERE Users.nick=%(nick)s
""", nick=nick)
def get_chat_for_message(message_id):
return db.query_all("""
SELECT messages.chat_id, messages.message_id
FROM messages
WHERE messages.message_id = %(message_id)s
RETURNING messages.chat_id
""",
message_id=message_id)
def list_messages_by_chat(chat_id, limit):
return db.query_all("""
SELECT user_id, nick, name,
message_id, content, added_at
FROM Messages
JOIN users USING (user_id)
WHERE chat_id=%(chat_id)s
ORDER BY added_at DESC
LIMIT %(limit)s
""", chat_id=int(chat_id), limit=int(limit))
def get_user_chats(user_id, limit):
chats = cache.get('user_chats_{}'.format(user_id))
if chats is None:
chats = db.query_all("""
SELECT chats.chat_id, chats.topic
FROM chats
JOIN members m on chats.chat_id = m.chat_id
WHERE m.user_id = %(user_id)s
LIMIT %(limit)s
""",
user_id=user_id,
limit=limit)
cache.set('user_chats_{}'.format(user_id), chats, timeout=10 * 60)
print('NO CACHE!', file=sys.stdout)
# Возвращаем [] из ID чатов
print('chats are:', chats, file=sys.stdout)
return chats
def mark_as_unread(message_id, chat_id):
db.query_all("""
UPDATE members
SET new_messages = new_messages + 1
WHERE chat_id =
""")