How to make user plugin generates and use tables in an another database

[informas]

It may be possible to do, and with a few simple modifications to the plugin, that instead of using the default database, it uses its associated tables but in an another external auxiliary database?

By the term "simple", I mean something, for example similar to the following hypothetical code:

Plugin.php file

use System\Classes\PluginBase;
use Winter\User\Models\User as UserModel;

class Plugin extends PluginBase;
{
    public function boot()
    {
        UserModel::extend(function ($model) {
            $model->connection('second_database');
        });
    }
}

And as a consequence of this "adjustment" any use of this model, always works on that second database.

Please note that, the idea behind this post is to use the same methodology for other plugins, not only User plugin.

The objective would be to separate a certain part of the data (enterprise model data) from the more specific design data of the site.

Thanks in advance.

[LukeTowers]

UPDATE: This should be fixed as of wintercms/storm@2cd7370

Your provided example is actually pretty close to how you would actually go about doing this, you just need to call setConnection() instead of connection(). See https://laravel.com/api/8.x/Illuminate/Database/Eloquent/Model.html#method_setConnection.

UserModel::extend(function ($model) {
    $model->setConnection('custom-connection');
});

NOTE: You can also run specific queries on a given connection if you want by calling the MyModelClass::on('custom-connection') to get a query builder instance initialized to the provided connection, see https://laravel.com/api/8.x/Illuminate/Database/Eloquent/Model.html#method_on.

[LukeTowers]

You shouldn't have to modify the contents of any third party files anywhere in Winter, you can put the code I provided into the boot() method of your own custom plugin and it will properly affect the models as desired.

With regards to the database migrations, yeah, those aren't going to be easy to automatically switch to use a different connection; it's theoretically possible that there's some internal event you could use to detect when those migrations were firing and run them on a separate connection, but I haven't tried to do that personally.

As far as your issues with deferred bindings and system_files, I'd be very curious to know why those ones had to exist in the other connection, as far as I'm aware they don't explicitly inherit the connection property of the parent model of the relationship, so that seems odd to me. If you could create an issue for those two tables with replication instructions from a fresh install then we can take a look into that.

[informas]

Thank you very much LukeTowers for your quick response.

I will analyze your suggestions and comments and most likely I will do what you mention and I will be try to test with a clean install.

I will keep you informed about the tests I am doing.

Thanks.

[informas]

Just as you asked me, I have tried to reproduce the problem raised in my previous posting regarding errors that appear when moving user tables (and associated) to a secondary database.

The following are the steps followed for me.

1) Create 2 new MYSQL databases:
winter_test_user (Main)
winter_test_user_data (Auxiliary)

2) Download WinterCMS
composer create-project wintercms/winter wintertestuser

3) Install WinterCMS
php artisan winter:install

   Database type [MySQL]: 0
   Database Name []: winter_test_user
   Would you like to change the backend options? (URL, default locale, default timezone) (yes/no) [no]: no
   Configure advanced options? (yes/no) [no]: no

5) Install plugin users
php artisan plugin:install Winter.User

php artisan plugin:list
+-----------------------+---------+-----------------+----------------+
| Plugin name           | Version | Updates enabled | Plugin enabled |
+-----------------------+---------+-----------------+----------------+
| Winter.Demo           | 1.0.1   | Yes             | Yes            |
+-----------------------+---------+-----------------+----------------+
| Winter.User           | 2.0.0   | Yes             | Yes            |
+-----------------------+---------+-----------------+----------------+ 

6) Copy the tables of the Winter.User plugin from the main database to the auxiliary database

+---------------------------------+
| Tables in winter_test_user_data |
+---------------------------------+
| user_groups                     |
| user_throttle                   |
| users                           |
| users_groups                    |
| winter_user_mail_blockers       |
+---------------------------------+

7) Create a new InforMAS.UserExtended plugin
php artisan create:plugin InforMAS.UserExtended

8) Modify the Plugin.php file of the created plugin, as follow:

<?php namespace InforMAS\UserExtended;

use Backend; 
use System\Classes\PluginBase;

// Modules used by Winter.User plugin
use Winter\User\Models\User as UserModel;
use Winter\User\Models\UserGroup as UserGroupModel;
use Winter\User\Models\Throttle as UserThrottleModel;
use Winter\User\Models\MailBlocker as MailBlockerModel;

/**
 * UserExtended Plugin Information File
 */
class Plugin extends PluginBase
{
    public $require = ['Winter.User'];
    public $DB4Data = 'mysql_custom_data'; // Auxiliary DB for custom data
    
    /**
     * Returns information about this plugin.
     *
     * @return array
     */
    public function pluginDetails()
    {
        return [
            'name'        => 'User Extended',
            'description' => 'Clone de Winter User for work with tables in auxiliary database',
            ...
        ];
    }

    /**
     * Register method, called when the plugin is first registered.
     *
     * @return void
     */
    public function register()
    {
    }

    public function boot()
    {
        $this->extendModels();
    }

    protected function extendModels()
    {
        UserModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });
        
        UserGroupModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });  
        
        UserThrottleModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });          
        
        MailBlockerModel::extend(function($model) {
            $model->setConnection($this->DB4Data);
        });          
    }
}

9) Modify config\database.php
I add a new item to the connections array similar to mysql but changing the database name for the auxiliary database

        'mysql_custom_data' => [
            ...
            'database' => 'winter_test_user_data',
            ...
        ]

10) Refresh the plugin
artisan plugin:refresh informas.userextended

11) Enter in Backend

12) I'm trying to create a new user
I press the "+ New User" button and an error immediately appears

ERROR:
We're sorry, but an unhandled error occurred. Please see the details below.
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.system_files' doesn't exist (SQL: select * from `system_files` where (system_files.id in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and `is_bind` = 1)) and system_files.id not in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and `is_bind` = 0 and id > ifnull((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = avatar and master_type = Winter\User\Models\User and session_key = 4eBL0FXKqn49pBY5cxcyPtlFpfa3YEq4dnJ1ldhW and is_bind = 1 ), 0)) order by `sort_order` asc)
E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php line 669

13) I copy the system_files table (which is currently empty) from the main database to the auxiliary database.

14) I'm trying to create a new user
I press the "+ New User" button and immediately another error appears different from the previous one

ERROR:
We're sorry, but an unhandled error occurred. Please see the details below.
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.deferred_bindings' doesn't exist (SQL: select * from `system_files` where (system_files.id in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and `is_bind` = 1)) and system_files.id not in (select `slave_id` from `deferred_bindings` where `master_field` = avatar and `master_type` = Winter\User\Models\User and `session_key` = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and `is_bind` = 0 and id > ifnull((select max(id) from deferred_bindings where slave_id = system_files.id and master_field = avatar and master_type = Winter\User\Models\User and session_key = eRK6auj7sSyduuX6esRhwQVaGPcPpAQnFEGuM2mJ and is_bind = 1 ), 0)) order by `sort_order` asc)
E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php line 669

15) I copy the deferred_bindings table (which is currently empty) from the main database to the auxiliary database

16) I'm trying to create a new user

I press the "+ New User" button and it works perfectly

If instead I try the same situation on the table Groups (of users) ...

17) I remove the system_files and deferred_bindings tables from the auxiliary database

18) I'm trying to create a new group
I press the "+ New Group" button
It shows the form "Create User Group", I enter the corresponding data, but when pressing the "Create and close" button an error appears in a popup

POPUP ERROR
"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'winter_test_user_data.deferred_bindings' doesn't exist (SQL: select * from `deferred_bindings` where `master_type` = Winter\User\Models\UserGroup and `session_key` = bKKpuCwcV1yD16UTXfCS7EbIY2x2uo72Ed1uLSmT)" on line 669 of E:\websw10\wintercms\wintertestuser\vendor\laravel\framework\src\Illuminate\Database\Connection.php

19) If I copy deferred_bindings table from main database to auxiliary database then I can create the groups without problems.

Take note: in the case of groups it seems that the system_files table is not necessary.

Any comments?

Thanks in advance!

[LukeTowers]

@informas created an issue for this (#389), will take a look soon.

[informas]

Thanks, LukeTowers!