Add feature multibots

[customsoft]

I would like to modify the DB, and its classes, to add the field
bot_id int (10) unsigned
to manage multiple bots with the same 12 main tables.

Then you can still change the name of the tables to make groups of bots, for example, based on customers.

Are you interested in this modification of your code?

Before changing the code, I await your confirmation.
Thank you

SQL:

ALTER TABLE `tb_botan_shortener` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_botan_shortener` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_callback_query` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_callback_query` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_chat` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_chat` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_chosen_inline_result` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_chosen_inline_result` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_conversation` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_conversation` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_edited_message` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_edited_message` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_inline_query` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_inline_query` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_message` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_message` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_request_limiter` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_request_limiter` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_telegram_update` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_telegram_update` ADD INDEX `bot_id` (`bot_id`);

ALTER TABLE `tb_user` ADD `bot_id` int unsigned NOT NULL COMMENT 'Bot identifier' FIRST;
ALTER TABLE `tb_user` ADD INDEX `bot_id` (`bot_id`);

[noplanman]

Thanks for your suggestion!

I guess what you're suggesting is implemented by using the table prefix when setting up the MySQL database connection:

$table_prefix = 'customerN_';
$telegram->enableMySql($credentials, $table_prefix);

What advantages do you see with adding a new field as you do, instead of prefixing tables?

[customsoft]

Example:
For 5 customers and 100 bots, we now need 1200 tables ( = 12 * 100 bots)
Having the multibots feature it would be possible to manage only 12 main tables + 1 (customers / bots) or 60 + 1 tables (= 12 * 5 customers)

[noplanman]

Right, I see what you mean.

But, (yes, of course there's a but 😁) apart from reducing the table count, this change will need many code modifications, which I'm not sure makes sense 🤔

@php-telegram-bot/developers @Hitmare @chuv1 What do you guys think?

(By the way, number of tables shouldn't be a real-life issue: https://dev.mysql.com/doc/refman/5.7/en/database-count-limit.html)

[customsoft]

Ok, I try to get more involved in the reasons for it.

1. with large DB files you make a backup faster than the many small files :-D (physical backup not logical)
2. with less tables the statistical queries of the various bots are simpler. 1 query multi bots stats.
3. it is easier to make relationships with other tables ... of my project. [-:
4. there are many changes, but I do it, then you just have to check :-)
5. the bot_id already recover it on the __construct telegram class. I only need to save it in the db, use it in select and pass it to the other classes.

    preg_match('/(\d+)\:[\w\-]+/', $api_key, $matches);
[...]
    $this->bot_id = $matches[1];

6. the field in the user table is not necessary; the redundancy of user data using multiple bots is eliminated.
   Example: 100 unique user and 100 bots (= 100 user tables)
   Now: until 10.000 record
   After: 100 record

I do not insist further, if you do not like the idea, ok ... no problem. (-:

[customsoft]

I evaluated the situation again
I must be able to filter the commands for the different bots; each bot does not have access to all the commands in the commands folder.
At this point I choose to write all the code I need.
Thanks for the attention.

[noplanman]

Right, I fully understand that.
Maybe you'd like to have a look at #613? That would have many extra benefits!

I do not insist further, if you do not like the idea, ok ... no problem. (-:

It's not about that, it's more about making smart choices and not doing extra work that could be put into something else 😊

[akalongman]

I like this idea, but we should think more about implementation details

Maybe it is better to add bots table and register each bot in that table? And use a foreign key in all related tables as a bot_id

[customsoft]

Maybe you'd like to have a look at #613?

now I evaluate, thanks

[noplanman]

As part of this whole issue, is to make the library less static (regarding Request and DB classes).

Not sure how best to tackle that though. Using a proper ORM for DB handling has been pending for quite some time. Really should move on getting something done for that, to not let it hinder progress.

[akalongman]

@noplanman

As part of this whole issue, is to make the library less static (regarding Request and DB classes).

ok, if it's so important, let's do that in the current version. What do you think?

[noplanman]

Ok! This was one of the things that you and @MBoretto said you'd implement as far as I remember, which is why I haven't really made a move on this.

Let's move this conversation to a new issue (or maybe continue on #170)

[akalongman]

@noplanman this is not easy because of library architecture. Lets continue here #826

[MBoretto]

Is not easy like that, In messages table for example you have double primary index:
https://github.com/php-telegram-bot/core/blob/master/structure.sql#L106
adding multiple bot the primary index will increase from 2 to 3 fields. Otherwise it can happen that messages collide.

[jacklul]

Plus, if I may add, I think the whole DB feature (and so does Conversation) should be handled as plugin or addon after the refactor, those things shouldn't be core of the library because in larger project people might want to use their own implementations of both of these features and they are not required for library to function.