check_subs/async_db.py

import aiosqlite
from loader import logger
from utils import User, Ads, ScheduledMessage


class Database:
    def __init__(self, path_to_db="database.db"):
        self.path_to_db = path_to_db

    # TODO create normal database connector
    async def jobs_add(self, message_id, chat_id, text):
        async with aiosqlite.connect(self.path_to_db) as db:
            sql = "INSERT INTO jobs VALUES (?,?,?)"
            parameters = (message_id, chat_id, text)
            await db.execute(sql, parameters)
            await db.commit()
            logger.info(f'{sql}, {parameters}')

    async def jobs_delete(self, message_id, chat_id, text):
        async with aiosqlite.connect(self.path_to_db) as db:
            message_id = 0 if message_id is None else message_id
            sql = "DELETE FROM jobs WHERE message_id=? AND chat_id=? AND text=?"
            parameters = (message_id, chat_id, text)
            await db.execute(sql, parameters)
            await db.commit()
            logger.info(f'{sql}, {parameters}')

    async def get_jobs(self):
        """
        :return: [task in every second; max length 20 [1 message in one chat; Max 20 message] ]
        """
        for_scheduler = [[] for i in range(55)]
        messages = []
        limit = 20
        async with aiosqlite.connect(self.path_to_db) as db:
            chats = [i[0] async for i in await db.execute('SELECT distinct(chat_id) from jobs')]
            for chat in chats:
                jobs_list = [ScheduledMessage(
                    message_id=None if int(i[0]) == 0 else i[0],
                    chat_id=i[1], text=i[2]
                ) async for i in
                    await db.execute(f'SELECT * FROM jobs WHERE chat_id={chat} LIMIT {limit};')]
                # messages.extend([i for i in list(zip(*[iter(jobs_list)] * 10))])
                messages.extend(jobs_list)

        for job in messages:
            # print(f"\n{job}")
            i = 0
            while i <= 55:
                # print(f'ITER:{i}')
                if len(for_scheduler[i]) < 20:
                    try:
                        contain_chat = list(filter(lambda x: int(x.chat_id) == int(job.chat_id), for_scheduler[i]))
                    except AttributeError:
                        # if task list empty
                        # print(f"ADD: {job} to jobs_to_schedule[{i}]")
                        for_scheduler[i].extend(job)
                        break

                    if not contain_chat:
                        # print(f"ADD: {job} to jobs_to_schedule[{i}]")
                        for_scheduler[i].append(job)
                        break
                    # elif contain_chat:
                    #     i += 4

                i += 1
        return for_scheduler