Integrate to existing app

[Sogl]

Hello!

I have a big ERP and I want to include this bot into it. DB structure is obscure part for me. Do I really need php-telegram-bot DB structure or not? Is it used only for log or something else?

I already have users table in my DB. How to integrate bot structure to my DB?

[jacklul]

It is required when using getUpdates and recommended for webhooks. Some features and commands will be unavailable without it.

If you have already users table you might try adjusting structure to match bot's users table.

[Sogl]

Some features and commands will be unavailable without it.

What features? Logging?

If you have already users table you might try adjusting structure to match bot's users table.

I already have connection with mysql in my own app and no need to the new one. How to change DB writer to my own with future release support? I mean I want to write the same info as php-telegram-bot writes to DB, but to my own tables with my own PDO adapter.

[jacklul]

What features? Logging?

Conversation feature, few commands rely on database (/chats, /whois etc).

I already have connection with mysql in my own app and no need to the new one. How to change DB writer to my own with future release support? I mean I want to write the same info as php-telegram-bot writes to DB, but to my own tables with my own PDO adapter.

I believe this library allows using already existing PDO connection, never used it so I can't tell more.

[Sogl]

@MBoretto Can you help please?

[MBoretto]

For what concern connection you can inject your own inside the library. https://github.com/akalongman/php-telegram-bot#external-database-connection

What information you want to include in your ERP?

[Sogl]

For what concern connection you can inject your own inside the library. https://github.com/akalongman/php-telegram-bot#external-database-connection

I need to set an existent PDO object here?

What information you want to include in your ERP?

For example, what messages each user wrote to bot. I need to add telegramId (int), telegramToken (varchar), telegramRegistered (boolean) fields to existent users table and relate it to telegramMessages table by id or telegramId in users.

I want to exclude information that I don't need. How to do it in simple and non-breaking future releases support way?

p.s. Also I need to implement user registration/confirmation using tokens on my own. Do you know any ready-made solutions?

[MBoretto]

I suggest you to leave leave the bot table schema as it is and change the logic of your application in order to reference the bot table

[Sogl]

@MBoretto You missed my question.

For what concern connection you can inject your own inside the library. https://github.com/akalongman/php-telegram-bot#external-database-connection

I need to set an existent PDO object here?

If I set telegramId in my users table, than I can select all user messages, right? Also how to change this library table names?

[Sogl]

Ok, I use my PDO connection with telegram_ prefix now and it works fine.

The problem is that every message sent to the bot is written to the database. How do I clean the tables right?

[MBoretto]

if you dive into DB.php you can comment the right line in order to not store for example messages.

https://github.com/akalongman/php-telegram-bot/blob/master/src/DB.php#L431

Anyway this is not a good idea, because table are referenced with each other in order to keep consistency to the db. If you comment message line it think that the lib will work fine.

But why don't store messages?

[jacklul]

Something like this can be used in cron. If you care about the constraints remove 'SET foreign_key_checks = 0',.

if ($conn = mysqli_connect($mysql_credentials['host'], $mysql_credentials['user'], $mysql_credentials['password'])) {
    mysqli_select_db($conn, $mysql_credentials['database']);

    $queries = [
        'SET foreign_key_checks = 0',

        'TRUNCATE TABLE `telegram_update`',
        'TRUNCATE TABLE `user_chat`',

        'DELETE FROM `conversation` WHERE `updated_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `botan_shortener` WHERE `created_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `message` WHERE `date` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `edited_message` WHERE `edit_date` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `inline_query` WHERE `created_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `chosen_inline_result` WHERE `created_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `callback_query` WHERE `created_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'', '',
        'DELETE FROM `chat` WHERE `updated_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\'',
        'DELETE FROM `user` WHERE `updated_at` < \'' . date('Y-m-d H:i:s', strtotime("-7 days")) . '\''
    ];

    foreach ($queries as $query) {
        if (!empty($query)) {
            mysqli_query($conn, $query);
        }
    }

    mysqli_close($conn);
}

[noplanman]

@Sogl, is this solved for you?

[Sogl]

Solved.

[Sogl]

@noplanman When I tried to show messages from telegram_message table in my app, I got an error:

exception 'ErrorException' with message 'Illegal offset type in isset or empty' in C:\OpenServer\domains\aviashelf\vendor\koala-framework\koala-framework\Kwf\Model\Db.php:61
Stack trace:
#0 C:\OpenServer\domains\aviashelf\vendor\koala-framework\koala-framework\Kwf\Model\Db.php(61): Kwf_Debug::handleError(2, 'Illegal offset ...', 'C:\\OpenServer\\d...', 61, Array)
#1 C:\OpenServer\domains\aviashelf\vendor\koala-framework\koala-framework\Kwf\Controller\Action\Auto\Grid.php(182): Kwf_Model_Db->getColumnType(Array)
#2 C:\OpenServer\domains\aviashelf\vendor\koala-framework\koala-framework\Kwf\Controller\Action.php(21): Kwf_Controller_Action_Auto_Grid->preDispatch()
#3 C:\OpenServer\domains\aviashelf\vendor\koala-framework\zendframework1\library\Zend\Controller\Dispatcher\Standard.php(308): Kwf_Controller_Action->dispatch('indexAction')
#4 C:\OpenServer\domains\aviashelf\vendor\koala-framework\zendframework1\library\Zend\Controller\Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Kwf_Controller_Request_Http), Object(Kwf_Controller_Response_Http))
#5 C:\OpenServer\domains\aviashelf\vendor\koala-framework\koala-framework\Kwf\Controller\Front.php(140): Zend_Controller_Front->dispatch(Object(Kwf_Controller_Request_Http), Object(Kwf_Controller_Response_Http))
#6 C:\OpenServer\domains\aviashelf\bootstrap.php(22): Kwf_Controller_Front->dispatch()
#7 {main}

It happens because table uses two columns as primary key defined (id and chat_id), that's why the variable in php primaryKey is an array. My PHP framework can't support this situation.

How to solve this problem?

[noplanman]

I honestly don't know the best solution for this. What you could try is to only use the id field as the primary key. It should still work, as the id field should be unique.

[MBoretto]

I think that in you have to look for a solution inside the query builder of your framework. Also with Laravel seems not easy to manage table with double indexes. This double index has been introduced to handle properly messages from supergroup chat since the index of those message is independent fro each superchat. For this reason I discourage to use just one index.

[Sogl]

@MBoretto I have no idea how to show messages in that situation... you can also check related issue that I created in framework repo.

[MBoretto]

Did you solve your proble can i close here?

[Sogl]

Didn't solved yet... I do not know any solutions to the problem of double indexes

[MBoretto]

Closing here