unzip-bot
unzip-bot copied to clipboard
Published
20 hours ago •
EDM115
EDM115
Database rework
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"
After more thoughts into this, here's the quick rundown of what's needed :
- Migrate from
MotortoPyMongo Async
Motoris in the process of being deprecated, and the MongoDB team is working on a drop-in replacement by integrating an async driver directly intoPyMongo. However it is still experimental and lacks some features fromMotor, hence why we don't switch yet. Some docs : - 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
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.
asyncio bridge to the standard sqlite3 module. Contribute to omnilib/aiosqlite development by creating an account on GitHub.
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"
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
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")