Database rework

[EDM115]

Redo the database, have less collections (hopefully reduces requests), allow for a local deployment + SQLite

Current state of the database :

erDiagram
    USERS_DB {
        int user_id
    }
    BANNED_USERS_DB {
        int banned_user_id
    }
    ULMODE_DB {
        int _id PK
        string mode
    }
    UPLOADED_COUNT_DB {
        int _id PK
        int uploaded_files
    }
    THUMB_DB {
        int _id PK
        string file_id
        string temp
        string url
    }
    BOT_DATA {
        bool boot
        datetime time
        bool old_boot
    }
    ONGOING_TASKS {
        int user_id
        datetime start_time
        string type
    }
    CANCEL_TASKS {
        int user_id
    }
    MERGE_TASKS {
        int user_id
        int message_id
    }
    MAINTENANCE_MODE {
        bool maintenance
        bool val
    }
    VIP_USERS {
        int _id PK
        string subscription
        datetime ends
        int used
        float billed
        bool early
        bool donator
        datetime started
        int successful
        int gap
        bool gifted
        bool referral
        bool lifetime
    }
    REFERRALS {
        int _id PK
        string type
        string referral_code
        int referees[]
    }

    USERS_DB ||--o{ BANNED_USERS_DB : "1 : 1"
    USERS_DB ||--o{ ULMODE_DB : "1 : 1"
    USERS_DB ||--o{ UPLOADED_COUNT_DB : "1 : 1"
    USERS_DB ||--o{ THUMB_DB : "1 : 1"
    USERS_DB ||--o{ ONGOING_TASKS : "1 : n"
    USERS_DB ||--o{ CANCEL_TASKS : "1 : n"
    USERS_DB ||--o{ MERGE_TASKS : "1 : n"
    USERS_DB ||--o{ VIP_USERS : "1 : 1"
    USERS_DB ||--o{ REFERRALS : "1 : 1"
    MAINTENANCE_MODE ||--o{ BOT_DATA : "1 : 1"

Loading

[EDM115]

After more thoughts into this, here's the quick rundown of what's needed :

• Migrate from Motor to PyMongo Async
Motor is in the process of being deprecated, and the MongoDB team is working on a drop-in replacement by integrating an async driver directly into PyMongo. However it is still experimental and lacks some features from Motor, hence why we don't switch yet. Some docs :
  • PyMongo docs
  • Migrate from Motor to PyMongo Async
  • Switch from PyMongo to PyMongo Async
  • Changelog to check if it's still experimental
• Merge most of the collections
  Having a ton of collections like we have right now isn't great to work with. Using a document-based database doesn't help either, as I'd like to make it at least 2NF. Which leads us to...
• Switch to a relational model
  This could honestly help with most of the request and would allow to query faster for linked information. Handling of the VIP plan would also greatly benefit from this
• Allow a fully local deployment
  Users should be able to deploy the bot without relying on MongoDB Atlas. This solution was chosen to work around the daily restart/disk swipe of Heroku. However, on a VPS this isn't necessary. A backup system should be implemented, but this would lead to the final step :
• Local SQLite DB
  At first this DB would only be used for actions that quickly change and needs fast retrieval (namely the current tasks gestion). At startup, this DB would cache all data from MongoDB to reduce requests. Any modification to non volatile tables (ex user settings) would be backed up to Atlas every 1 min or so, which would greatly reduce the number of network requests going on. Atlas would only be a backup of our db, and all actions would happen locally. I consider using aiosqlite for this task

The new DB scheme still have to be made

Motor 3.6.0 Released

We are pleased to announce the 3.6.0 release of Motor - MongoDB’s Asynchronous Python Driver. This release has the following changes: Add support for MongoDB 8.0 and PyMongo 4.9. The length parameter in MotorCursor.to_list is now optional. Note This is the last planned minor version of Motor. We are sunsetting Motor in favor of native asyncio support in PyMongo 4.9+. We will continue to provide security releases and bug fixes for Motor, but it will not gain new features.

MongoDB PyMongo Documentation

Migrate from Motor to PyMongo Async

Switch from PyMongo to PyMongo Async

What's New

Database normalization - Wikipedia

GitHub - omnilib/aiosqlite: asyncio bridge to the standard sqlite3 module

asyncio bridge to the standard sqlite3 module. Contribute to omnilib/aiosqlite development by creating an account on GitHub.

[EDM115]

Potential DB Scheme :

erDiagram
    User {
        int id
        bool is_banned
        str upload_mode
        str lang
        int uploaded_count
    }

    Upload_Modes {
        str type
    }

    Languages {
        str locale
    }

    Thumb {
        int id
        int uid
        str file_id
        str temp_id
    }

    Password {
        int id
        int uid
        str pass
    }

    Task {
        int id
        int uid
        int user_task_nb
        bool started
        float start_time
        str type
        bool cancelled
        int message_id
    }

    Task_Types {
        str type
    }

    System {
        int id
        float boot_time
        float old_boot_time
        bool maintenance
    }

    Vip {
        int id
        int uid
        date start
        date end
        str mean
        str proof
        str frequency
        bool early
        bool donator
        date first_start
        int nb_payments
        bool gap
        bool giftes
        str referral
        str referee
        bool lifetime
    }

    Payment_Means {
        str mean
    }

    Payment_Frequencies {
        str freq
    }

    %% Relationships %%
    User ||--o{ Upload_Modes : "upload_mode"
    User ||--o{ Languages : "lang"
    User ||--o{ Thumb : "1 : n"
    User ||--o{ Password : "1 : 1"
    User ||--o{ Task : "1 : n"
    User ||--o{ Vip : "1 : 1"
    Task ||--o{ Task_Types : "type"
    Vip ||--o{ Payment_Means : "mean"
    Vip ||--o{ Payment_Frequencies : "frequency"
    Thumb ||--o| User : "uid"
    Password ||--o| User : "uid"
    Task ||--o| User : "uid"
    Vip ||--o| User : "uid"

Loading

DB Scheme as i wrote it

User(
  id: int (PK),
  is_banned: bool,
  upload_mode@Upload_Modes.type: str (NN),
  [email protected]: str (NN),
  uploaded_count: int{#0} (NN)
)

Upload_Modes(
  type: str{#media, doc} (PK)
)

Languages(
  locale: str{#en, fr} (PK)
)

Thumb(
  id: int (PK),
  [email protected]: int (NN),
  file_id: str (NN),
  temp_id: str
)

Password(
  id: int (PK),
  [email protected]: int (NN),
  pass: str (NN)
)

Task(
  id: int (PK),
  [email protected]: int (NN),
  user_task_nb: int (NN),
  started: bool,
  start_time: float,
  type@Task_Types.type: str (NN),
  cancelled: bool,
  message_id: int (NN)
)

Task_Types(
  type: str{extract, merge} (PK)
)

System(
  id: int{#0} (PK),
  boot_time: float,
  old_boot_time: float,
  maintenance: bool
)

Vip(
  id: int (PK),
  [email protected]: int (NN, UQ),
  start: date (NN),
  end: date (NN),
  mean@Payment_Means.mean: str (NN),
  proof: str (NN, UQ),
  frequency@Payment_Frequencies.freq: str (NN),
  early: bool,
  donator: bool,
  first_start: date (NN),
  nb_payments: int (NN),
  gap: bool,
  giftes: bool,
  referral: str (NN, UQ),
  referee: str (UQ),
  lifetime: bool
)

Payment_Means(
  mean: str{paypal, telegram, gh, bmac, custom} (PK)
)

Payment_Frequencies(
  freq: str{monthly, yearly} (PK)
)

The Task table would be the only one to be present only locally

[EDM115]

we should also remove an user from the db when we get Telegram says: [403 USER_IS_BLOCKED] - The user is blocked (caused by "messages.SendMessage")