From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on gnuweeb.org X-Spam-Level: X-Spam-Status: No, score=-0.8 required=5.0 tests=ALL_TRUSTED,DKIM_SIGNED, DKIM_VALID,DKIM_VALID_AU,DKIM_VALID_EF,NO_DNS_FOR_FROM,URIBL_BLOCKED autolearn=no autolearn_force=no version=3.4.6 Received: from localhost.localdomain (unknown [101.128.125.100]) by gnuweeb.org (Postfix) with ESMTPSA id 473E880D61; Sun, 11 Sep 2022 10:33:44 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gnuweeb.org; s=default; t=1662892426; bh=CP8aQHm3pNQDUvdNdMvHz49nj+qkn+qu3+ZJ5REaHaI=; h=From:To:Cc:Subject:Date:In-Reply-To:References:From; b=c/I8t4ye61uSO8fjVIOipgYuUYKdNEyt8xp0HZC6mGe9SSL3PO98i6WQcGQoCDP60 /BsOUukt5F0pYqj/0vvc6PTg7JU0BR89QOfe/ZVtr9MRV25D5Ky4uqtWh3AneFzQM2 xAaM8UEyq1EkSyed3ibm1uEXoDCu5lOtNoP8y5/+4xwekYSn+UxchA4CIHdSwzPUs8 Lx0QV3EZ2EiP9+LVAOPo5r7zE3aK+nk0BXrFeLm8/joQFbbDbdGsB6vBfTREmJfpQI dW4elw2beRhvpQ+cDNiBo+puIFKh7Tgf7R0gTCOOpOj8ViMKisvQEQlm11ZwS1LdE2 W3lSdUSZ66zRQ== From: Muhammad Rizki To: Ammar Faizi Cc: Muhammad Rizki , Alviro Iskandar Setiawan , GNU/Weeb Mailing List Subject: [RFC PATCH v1 02/17] [telegram] Refactor Telegram bot database method Date: Sun, 11 Sep 2022 17:33:08 +0700 Message-Id: <20220911103323.1949-3-kiizuha@gnuweeb.org> X-Mailer: git-send-email 2.34.1.windows.1 In-Reply-To: <20220911103323.1949-1-kiizuha@gnuweeb.org> References: <20220911103323.1949-1-kiizuha@gnuweeb.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: I decide to refactor the Telegram bot database to make it more clean and manageable to maintain. Signed-off-by: Muhammad Rizki --- daemon/telegram/database/__init__.py | 7 + daemon/telegram/database/core.py | 20 ++ daemon/telegram/database/methods/__init__.py | 17 ++ .../database/methods/deletion/__init__.py | 14 ++ .../database/methods/deletion/delet_atom.py | 15 ++ .../methods/deletion/delete_broadcast.py | 15 ++ .../database/methods/getter/__init__.py | 18 ++ .../database/methods/getter/get_atom_urls.py | 21 ++ .../methods/getter/get_broadcast_chats.py | 21 ++ .../database/methods/getter/get_email_id.py | 62 +++++ .../methods/getter/get_telegram_reply.py | 33 +++ .../database/methods/insertion/__init__.py | 18 ++ .../database/methods/insertion/insert_atom.py | 27 +++ .../methods/insertion/insert_broadcast.py | 56 +++++ .../methods/insertion/insert_email.py | 27 +++ .../methods/insertion/insert_telegram.py | 21 ++ daemon/telegram/db.sql | 18 +- daemon/telegram/packages/client.py | 4 +- .../packages/plugins/commands/manage_atom.py | 2 +- .../plugins/commands/manage_broadcast.py | 2 +- daemon/telegram/scraper/bot.py | 8 +- daemon/telegram/scraper/db.py | 217 ------------------ 22 files changed, 409 insertions(+), 234 deletions(-) create mode 100644 daemon/telegram/database/__init__.py create mode 100644 daemon/telegram/database/core.py create mode 100644 daemon/telegram/database/methods/__init__.py create mode 100644 daemon/telegram/database/methods/deletion/__init__.py create mode 100644 daemon/telegram/database/methods/deletion/delet_atom.py create mode 100644 daemon/telegram/database/methods/deletion/delete_broadcast.py create mode 100644 daemon/telegram/database/methods/getter/__init__.py create mode 100644 daemon/telegram/database/methods/getter/get_atom_urls.py create mode 100644 daemon/telegram/database/methods/getter/get_broadcast_chats.py create mode 100644 daemon/telegram/database/methods/getter/get_email_id.py create mode 100644 daemon/telegram/database/methods/getter/get_telegram_reply.py create mode 100644 daemon/telegram/database/methods/insertion/__init__.py create mode 100644 daemon/telegram/database/methods/insertion/insert_atom.py create mode 100644 daemon/telegram/database/methods/insertion/insert_broadcast.py create mode 100644 daemon/telegram/database/methods/insertion/insert_email.py create mode 100644 daemon/telegram/database/methods/insertion/insert_telegram.py delete mode 100644 daemon/telegram/scraper/db.py diff --git a/daemon/telegram/database/__init__.py b/daemon/telegram/database/__init__.py new file mode 100644 index 0000000..930e3d9 --- /dev/null +++ b/daemon/telegram/database/__init__.py @@ -0,0 +1,7 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +from .core import DB diff --git a/daemon/telegram/database/core.py b/daemon/telegram/database/core.py new file mode 100644 index 0000000..c34d7a8 --- /dev/null +++ b/daemon/telegram/database/core.py @@ -0,0 +1,20 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from .methods import DBMethods + + +class DB(DBMethods): + def __init__(self, conn): + self.conn = conn + self.conn.autocommit = True + self.cur = self.conn.cursor(buffered=True) + + + def __del__(self): + self.cur.close() + self.conn.close() diff --git a/daemon/telegram/database/methods/__init__.py b/daemon/telegram/database/methods/__init__.py new file mode 100644 index 0000000..961b4e0 --- /dev/null +++ b/daemon/telegram/database/methods/__init__.py @@ -0,0 +1,17 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from .deletion import Deletion +from .getter import Getter +from .insertion import Insertion + + +class DBMethods( + Deletion, + Getter, + Insertion +): pass diff --git a/daemon/telegram/database/methods/deletion/__init__.py b/daemon/telegram/database/methods/deletion/__init__.py new file mode 100644 index 0000000..b206929 --- /dev/null +++ b/daemon/telegram/database/methods/deletion/__init__.py @@ -0,0 +1,14 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +from .delet_atom import DeleteAtom +from .delete_broadcast import DeleteBroadcast + + +class Deletion( + DeleteAtom, + DeleteBroadcast +): pass diff --git a/daemon/telegram/database/methods/deletion/delet_atom.py b/daemon/telegram/database/methods/deletion/delet_atom.py new file mode 100644 index 0000000..d8ad4bf --- /dev/null +++ b/daemon/telegram/database/methods/deletion/delet_atom.py @@ -0,0 +1,15 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +class DeleteAtom: + + def delete_atom(self, atom: str): + q = """ + DELETE FROM tg_atoms + WHERE url = %(atom)s + """ + self.cur.execute(q, {"atom": atom}) + return self.cur.rowcount > 0 diff --git a/daemon/telegram/database/methods/deletion/delete_broadcast.py b/daemon/telegram/database/methods/deletion/delete_broadcast.py new file mode 100644 index 0000000..d076dec --- /dev/null +++ b/daemon/telegram/database/methods/deletion/delete_broadcast.py @@ -0,0 +1,15 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +class DeleteBroadcast: + + def delete_broadcast(self, chat_id: int): + q = """ + DELETE FROM tg_broadcasts + WHERE chat_id = %(chat_id)s + """ + self.cur.execute(q, {"chat_id": chat_id}) + return self.cur.rowcount > 0 diff --git a/daemon/telegram/database/methods/getter/__init__.py b/daemon/telegram/database/methods/getter/__init__.py new file mode 100644 index 0000000..e978c72 --- /dev/null +++ b/daemon/telegram/database/methods/getter/__init__.py @@ -0,0 +1,18 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +from .get_atom_urls import GetAtomURL +from .get_broadcast_chats import GetBroadcastChats +from .get_email_id import GetEmailID +from .get_telegram_reply import GetTelegramReply + + +class Getter( + GetAtomURL, + GetBroadcastChats, + GetEmailID, + GetTelegramReply +): pass diff --git a/daemon/telegram/database/methods/getter/get_atom_urls.py b/daemon/telegram/database/methods/getter/get_atom_urls.py new file mode 100644 index 0000000..04a9315 --- /dev/null +++ b/daemon/telegram/database/methods/getter/get_atom_urls.py @@ -0,0 +1,21 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +class GetAtomURL: + + def get_atom_urls(self): + ''' + Get lore kernel raw email URLs. + - Return list of raw email URLs: `List[str]` + ''' + q = """ + SELECT tg_atoms.url + FROM tg_atoms + """ + self.cur.execute(q) + urls = self.cur.fetchall() + + return [u[0] for u in urls] diff --git a/daemon/telegram/database/methods/getter/get_broadcast_chats.py b/daemon/telegram/database/methods/getter/get_broadcast_chats.py new file mode 100644 index 0000000..d92e879 --- /dev/null +++ b/daemon/telegram/database/methods/getter/get_broadcast_chats.py @@ -0,0 +1,21 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# + + +class GetBroadcastChats: + + def get_broadcast_chats(self): + ''' + Get broadcast chats that are currently + listening for new email. + - Return list of chat object: `List[Object]` + ''' + q = """ + SELECT * + FROM tg_broadcasts + """ + self.cur.execute(q) + + return self.cur.fetchall() diff --git a/daemon/telegram/database/methods/getter/get_email_id.py b/daemon/telegram/database/methods/getter/get_email_id.py new file mode 100644 index 0000000..44a6179 --- /dev/null +++ b/daemon/telegram/database/methods/getter/get_email_id.py @@ -0,0 +1,62 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +class GetEmailID: + + def get_email_id(self, email_id, chat_id): + ''' + Determine whether the email needs to be sent to @tg_chat_id. + - Return an email id (PK) if it needs to be sent + - Return None if it doesn't need to be sent + ''' + if self.__is_sent(email_id, chat_id): + return + + res = self.__email_id(email_id) + if not bool(res): + return + + return int(res[0]) + + + def __is_sent(self, email_id, chat_id): + ''' + Checking if this email has already been sent + or not. + - Return True if it's already been sent + ''' + + q = """ + SELECT tg_emails.id, tg_mail_msg.id FROM tg_emails LEFT JOIN tg_mail_msg + ON tg_emails.id = tg_mail_msg.email_id + WHERE tg_emails.message_id = %(email_id)s + AND tg_mail_msg.chat_id = %(chat_id)s + LIMIT 1 + """ + + self.cur.execute(q, { + "email_id": email_id, + "chat_id": chat_id + }) + + res = self.cur.fetchone() + return bool(res) + + + def __email_id(self, email_id): + ''' + Get the email id if match with the email message_id. + - Return the result if it's match and exists + ''' + + q = """ + SELECT id FROM tg_emails WHERE message_id = %(email_id)s + """ + + self.cur.execute(q, {"email_id": email_id}) + res = self.cur.fetchone() + return res diff --git a/daemon/telegram/database/methods/getter/get_telegram_reply.py b/daemon/telegram/database/methods/getter/get_telegram_reply.py new file mode 100644 index 0000000..94e3138 --- /dev/null +++ b/daemon/telegram/database/methods/getter/get_telegram_reply.py @@ -0,0 +1,33 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +class GetTelegramReply: + + def get_reply_id(self, email_msg_id, chat_id): + ''' + Get Telegram message ID sent match with + email message ID and Telegram chat ID. + - Return Telegram message ID if exists: `int` + - Return None if not exists` + ''' + q = """ + SELECT tg_mail_msg.tg_msg_id + FROM tg_emails INNER JOIN tg_mail_msg + ON tg_emails.id = tg_mail_msg.email_id + WHERE tg_emails.message_id = %(email_msg_id)s + AND tg_mail_msg.chat_id = %(chat_id)s + """ + + self.cur.execute(q, { + "email_msg_id": email_msg_id, + "chat_id": chat_id + }) + res = self.cur.fetchone() + if not bool(res): + return None + + return res[0] diff --git a/daemon/telegram/database/methods/insertion/__init__.py b/daemon/telegram/database/methods/insertion/__init__.py new file mode 100644 index 0000000..3604f82 --- /dev/null +++ b/daemon/telegram/database/methods/insertion/__init__.py @@ -0,0 +1,18 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + +from .insert_atom import InsertAtom +from .insert_broadcast import InsertBroadcast +from .insert_email import InsertEmail +from .insert_telegram import InsertTelegram + + +class Insertion( + InsertAtom, + InsertBroadcast, + InsertEmail, + InsertTelegram +): pass diff --git a/daemon/telegram/database/methods/insertion/insert_atom.py b/daemon/telegram/database/methods/insertion/insert_atom.py new file mode 100644 index 0000000..ac068ae --- /dev/null +++ b/daemon/telegram/database/methods/insertion/insert_atom.py @@ -0,0 +1,27 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from mysql.connector import errors +from datetime import datetime + + +class InsertAtom: + + def save_atom(self, atom: str): + try: + return self.__insert_atom(atom) + except errors.IntegrityError: + # + # Duplicate data, skip! + # + return None + + + def __insert_atom(self, atom: str): + q = "INSERT INTO tg_atoms (url, created_at) VALUES (%s, %s)" + self.cur.execute(q, (atom, datetime.utcnow())) + return self.cur.lastrowid diff --git a/daemon/telegram/database/methods/insertion/insert_broadcast.py b/daemon/telegram/database/methods/insertion/insert_broadcast.py new file mode 100644 index 0000000..11ebc6e --- /dev/null +++ b/daemon/telegram/database/methods/insertion/insert_broadcast.py @@ -0,0 +1,56 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from mysql.connector import errors +from datetime import datetime + + +class InsertBroadcast: + + def save_broadcast( + self, + chat_id: int, + name: str, + type: str, + created_at: "datetime", + username: str = None, + link: str = None, + ): + try: + return self.__insert_broadcast( + chat_id=chat_id, + name=name, + type=type, + created_at=created_at, + username=username, + link=link + ) + except errors.IntegrityError: + # + # Duplicate data, skip! + # + return None + + + def __insert_broadcast( + self, + chat_id: int, + name: str, + type: str, + created_at: "datetime", + username: str = None, + link: str = None, + ): + q = """ + INSERT INTO tg_broadcasts + (chat_id, username, name, type, link, created_at) + VALUES + (%s, %s, %s, %s, %s, %s) + """ + values = (chat_id, username, name, type, link, created_at) + self.cur.execute(q, values) + return self.cur.lastrowid diff --git a/daemon/telegram/database/methods/insertion/insert_email.py b/daemon/telegram/database/methods/insertion/insert_email.py new file mode 100644 index 0000000..adbe86b --- /dev/null +++ b/daemon/telegram/database/methods/insertion/insert_email.py @@ -0,0 +1,27 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from mysql.connector import errors +from datetime import datetime + + +class InsertEmail: + + def save_email(self, email_msg_id): + try: + return self.__insert_email(email_msg_id) + except errors.IntegrityError: + # + # Duplicate data, skip! + # + return None + + + def __insert_email(self, email_msg_id): + q = "INSERT INTO tg_emails (message_id, created_at) VALUES (%s, %s)" + self.cur.execute(q, (email_msg_id, datetime.utcnow())) + return self.cur.lastrowid diff --git a/daemon/telegram/database/methods/insertion/insert_telegram.py b/daemon/telegram/database/methods/insertion/insert_telegram.py new file mode 100644 index 0000000..8e0615c --- /dev/null +++ b/daemon/telegram/database/methods/insertion/insert_telegram.py @@ -0,0 +1,21 @@ +# SPDX-License-Identifier: GPL-2.0-only +# +# Copyright (C) 2022 Muhammad Rizki +# Copyright (C) 2022 Ammar Faizi +# + + +from datetime import datetime + + +class InsertTelegram: + + def save_telegram_mail(self, email_id, tg_chat_id, tg_msg_id): + q = """ + INSERT INTO tg_mail_msg + (email_id, chat_id, tg_msg_id, created_at) + VALUES (%s, %s, %s, %s); + """ + self.cur.execute(q, (email_id, tg_chat_id, tg_msg_id, + datetime.utcnow())) + return self.cur.lastrowid diff --git a/daemon/telegram/db.sql b/daemon/telegram/db.sql index ac6ed0d..c55c464 100644 --- a/daemon/telegram/db.sql +++ b/daemon/telegram/db.sql @@ -7,8 +7,8 @@ SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; SET NAMES utf8mb4; -DROP TABLE IF EXISTS `emails`; -CREATE TABLE `emails` ( +DROP TABLE IF EXISTS `tg_emails`; +CREATE TABLE `tg_emails` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `message_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `created_at` datetime NOT NULL, @@ -18,8 +18,8 @@ CREATE TABLE `emails` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; -DROP TABLE IF EXISTS `tg_emails`; -CREATE TABLE `tg_emails` ( +DROP TABLE IF EXISTS `tg_mail_msg`; +CREATE TABLE `tg_mail_msg` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `email_id` bigint unsigned NOT NULL, `chat_id` bigint NOT NULL, @@ -30,12 +30,12 @@ CREATE TABLE `tg_emails` ( KEY `chat_id` (`chat_id`), KEY `tg_msg_id` (`tg_msg_id`), KEY `created_at` (`created_at`), - CONSTRAINT `tg_emails_ibfk_2` FOREIGN KEY (`email_id`) REFERENCES `emails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `tg_mail_msg_ibfk_2` FOREIGN KEY (`email_id`) REFERENCES `tg_emails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; -DROP TABLE IF EXISTS `atom_urls`; -CREATE TABLE `atom_urls` ( +DROP TABLE IF EXISTS `tg_atoms`; +CREATE TABLE `tg_atoms` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `created_at` datetime NOT NULL, @@ -45,8 +45,8 @@ CREATE TABLE `atom_urls` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci; -DROP TABLE IF EXISTS `broadcast_chats`; -CREATE TABLE `broadcast_chats` ( +DROP TABLE IF EXISTS `tg_broadcasts`; +CREATE TABLE `tg_broadcasts` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `chat_id` bigint NOT NULL, `username` varchar(32), diff --git a/daemon/telegram/packages/client.py b/daemon/telegram/packages/client.py index 282daf6..820c3e2 100644 --- a/daemon/telegram/packages/client.py +++ b/daemon/telegram/packages/client.py @@ -9,7 +9,7 @@ from pyrogram.types import Message, InlineKeyboardMarkup, InlineKeyboardButton from typing import Union from email.message import Message from scraper import utils -from scraper.db import Db +from database import DB from .decorator import handle_flood @@ -18,7 +18,7 @@ class DaemonClient(Client): api_hash: str, conn, **kwargs): super().__init__(name, api_id, api_hash, **kwargs) - self.db = Db(conn) + self.db = DB(conn) @handle_flood diff --git a/daemon/telegram/packages/plugins/commands/manage_atom.py b/daemon/telegram/packages/plugins/commands/manage_atom.py index bcb2f35..4ba422a 100644 --- a/daemon/telegram/packages/plugins/commands/manage_atom.py +++ b/daemon/telegram/packages/plugins/commands/manage_atom.py @@ -25,7 +25,7 @@ async def add_atom_url(c: DaemonClient, m: Message): if not is_atom: return await m.reply("Invalid Atom URL") - inserted = c.db.insert_atom(text) + inserted = c.db.save_atom(text) if inserted is None: return await m.reply(f"This URL already listened for new email.") diff --git a/daemon/telegram/packages/plugins/commands/manage_broadcast.py b/daemon/telegram/packages/plugins/commands/manage_broadcast.py index ffb5a6b..6d75c36 100644 --- a/daemon/telegram/packages/plugins/commands/manage_broadcast.py +++ b/daemon/telegram/packages/plugins/commands/manage_broadcast.py @@ -20,7 +20,7 @@ async def add_broadcast(c: DaemonClient, m: Message): else: chat_name = m.chat.title - inserted = c.db.insert_broadcast( + inserted = c.db.save_broadcast( chat_id=m.chat.id, name=chat_name, type=str(m.chat.type), diff --git a/daemon/telegram/scraper/bot.py b/daemon/telegram/scraper/bot.py index 7adfb12..9ef5508 100644 --- a/daemon/telegram/scraper/bot.py +++ b/daemon/telegram/scraper/bot.py @@ -112,7 +112,7 @@ class Bot(): tg_chat_id, text,reply_to, url ) - self.db.insert_telegram(email_id, m.chat.id, m.id) + self.db.save_telegram_mail(email_id, m.chat.id, m.id) for d, f in files: await m.reply_document(f"{d}/{f}", file_name=f) await asyncio.sleep(1) @@ -124,11 +124,11 @@ class Bot(): def __need_to_send_to_telegram(self, email_msg_id, tg_chat_id): - email_id = self.db.save_email_msg_id(email_msg_id) + email_id = self.db.save_email(email_msg_id) if email_id: return email_id - email_id = self.db.need_to_send_to_tg(email_msg_id, tg_chat_id) + email_id = self.db.get_email_id(email_msg_id, tg_chat_id) return email_id @@ -141,4 +141,4 @@ class Bot(): if not reply_to: return None - return self.db.get_tg_reply_to(reply_to, tg_chat_id) + return self.db.get_reply_id(reply_to, tg_chat_id) diff --git a/daemon/telegram/scraper/db.py b/daemon/telegram/scraper/db.py deleted file mode 100644 index 58601d1..0000000 --- a/daemon/telegram/scraper/db.py +++ /dev/null @@ -1,217 +0,0 @@ -# SPDX-License-Identifier: GPL-2.0-only -# -# Copyright (C) 2022 Muhammad Rizki -# Copyright (C) 2022 Ammar Faizi -# - -from datetime import datetime -import mysql - - -class Db(): - def __init__(self, conn): - self.conn = conn - self.conn.autocommit = True - self.cur = self.conn.cursor(buffered=True) - - - def __del__(self): - self.cur.close() - self.conn.close() - - - def save_email_msg_id(self, email_msg_id): - try: - return self.__save_email_msg_id(email_msg_id) - except mysql.connector.errors.IntegrityError: - # - # Duplicate data, skip! - # - return None - - - def __save_email_msg_id(self, email_msg_id): - q = "INSERT INTO emails (message_id, created_at) VALUES (%s, %s)" - self.cur.execute(q, (email_msg_id, datetime.utcnow())) - return self.cur.lastrowid - - - def insert_telegram(self, email_id, tg_chat_id, tg_msg_id): - q = """ - INSERT INTO tg_emails - (email_id, chat_id, tg_msg_id, created_at) - VALUES (%s, %s, %s, %s); - """ - self.cur.execute(q, (email_id, tg_chat_id, tg_msg_id, - datetime.utcnow())) - return self.cur.lastrowid - - - # - # Determine whether the email needs to be sent to @tg_chat_id. - # - # - Return an email id (PK) if it needs to be sent. - # - Return None if it doesn't need to be sent. - # - def need_to_send_to_tg(self, email_msg_id, tg_chat_id): - q = """ - SELECT emails.id, tg_emails.id FROM emails LEFT JOIN tg_emails - ON emails.id = tg_emails.email_id - WHERE emails.message_id = %(email_msg_id)s - AND tg_emails.chat_id = %(tg_chat_id)s - LIMIT 1 - """ - - self.cur.execute( - q, - { - "email_msg_id": email_msg_id, - "tg_chat_id": tg_chat_id - } - ) - res = self.cur.fetchone() - if bool(res): - # - # This email has already been sent to - # @tg_chat_id. - # - return None - - q = """ - SELECT id FROM emails WHERE message_id = %(email_msg_id)s - """ - self.cur.execute(q, {"email_msg_id": email_msg_id}) - res = self.cur.fetchone() - if not bool(res): - # - # Something goes wrong, skip! - # - return None - - return int(res[0]) - - - def get_tg_reply_to(self, email_msg_id, tg_chat_id): - q = """ - SELECT tg_emails.tg_msg_id - FROM emails INNER JOIN tg_emails - ON emails.id = tg_emails.email_id - WHERE emails.message_id = %(email_msg_id)s - AND tg_emails.chat_id = %(chat_id)s - """ - - self.cur.execute( - q, - { - "email_msg_id": email_msg_id, - "chat_id": tg_chat_id - } - ) - res = self.cur.fetchone() - if not bool(res): - return None - - return res[0] - - - def insert_atom(self, atom: str): - try: - return self.__save_atom(atom) - except mysql.connector.errors.IntegrityError: - # - # Duplicate data, skip! - # - return None - - - def __save_atom(self, atom: str): - q = "INSERT INTO atom_urls (url, created_at) VALUES (%s, %s)" - self.cur.execute(q, (atom, datetime.utcnow())) - return self.cur.lastrowid - - - def delete_atom(self, atom: str): - q = """ - DELETE FROM atom_urls - WHERE url = %(atom)s - """ - try: - self.cur.execute(q, {"atom": atom}) - return True - except: - return False - - - def get_atom_urls(self): - q = """ - SELECT atom_urls.url - FROM atom_urls - """ - self.cur.execute(q) - urls = self.cur.fetchall() - - return [u[0] for u in urls] - - - def insert_broadcast( - self, - chat_id: int, - name: str, - type: str, - created_at: "datetime", - username: str = None, - link: str = None, - ): - try: - return self.__save_broadcast( - chat_id=chat_id, - name=name, - type=type, - created_at=created_at, - username=username, - link=link - ) - except mysql.connector.errors.IntegrityError: - # - # Duplicate data, skip! - # - return None - - - def __save_broadcast( - self, - chat_id: int, - name: str, - type: str, - created_at: "datetime", - username: str = None, - link: str = None, - ): - q = """ - INSERT INTO broadcast_chats - (chat_id, username, name, type, link, created_at) - VALUES - (%s, %s, %s, %s, %s, %s) - """ - values = (chat_id, username, name, type, link, created_at) - self.cur.execute(q, values) - return self.cur.lastrowid - - - def delete_broadcast(self, chat_id: int): - q = """ - DELETE FROM broadcast_chats - WHERE chat_id = %(chat_id)s - """ - self.cur.execute(q, {"chat_id": chat_id}) - return self.cur.rowcount > 0 - - - def get_broadcast_chats(self): - q = """ - SELECT * - FROM broadcast_chats - """ - self.cur.execute(q) - - return self.cur.fetchall() -- Muhammad Rizki