Change DBI database on runtime

[miguelele]

My wise monks:

I have many databases for different clients in Catalyst. All of them with the same Schema. Even with a master user/password to rule them all.

So, the only value that changes from one client to another is the "dsn"

How can I change that dsn value in the model package from the controller at runtime?

[zwon]

Usually you have just one instance of given model which is reused for multiple requests. In your case you probably need separate instance for every user (or maybe you can reconfigure single existing instance on a per-request basis, I don't know, it depends), solution that comes to my mind is to use Catalyst::Model::Factory::PerRequest from Catalyst::Model::Adaptor.

[miguelele]

Wow!, isn't it overkill just for a string with the name of the database?, a name that (yes or yes) must be used in each request?

It seems strange that I can't split up the connection data from the schema itself

I have been looking for info on the plugins you mention. Thank you!! and Factory::PerRequest seems to be a solution, but info is very schematic. I am still learning from tutorials!!!

What i have is this model:

package the_application::Model::DB;

use strict;
use base 'Catalyst::Model::DBIC::Schema';

__PACKAGE__->config(
    schema_class => 'the_application::Schema',
    
    connect_info => {
          dsn => 'dbi:mysql:database_one:the_domain.com',
        user => 'the_user',
        password => 'the_password',
        AutoCommit => q{1},
    }
);

1;

- How can I wrap it using Factory::PerRequest and change "dsn" on the fly from a Controller?

Regards

[zwon]

Wow!, isn't it overkill just for a string with the name of the database?, a name that (yes or yes) must be used in each request?

Well, actually no, normally this name is used only once, when you're instantiating model the first time. It's a rather weird idea to use different databases for different users.

As for how to do this... Look documentation for Catalyst::Model::Adapter, you need to define prepare_arguments and mangle_arguments methods. If you want more control (and you probably do, you can cache models for different users), you can write your own ACCEPT_CONTEXT method (see docs for Catalyst::Component and sources of Catalyst::Model::Factory). All this may be a bit tricky, and documentation is not especially verbose, but I'm sure you will enjoy the process.

[keszler]

To me it makes more sense to have one database, with ClientID as a Primary Key in the relevant tables or parent tables. Catalyst can them map the virtual host name to the ClientID to ensure each client sees only their own data.

[miguelele]

Thanks, but all data is already structured and being used in a real php production environment, so changing the db is not an option

Even more, each database has six tables, some of them with many thousands of records, and one table is a pair/value prefernces table (like Wordpress preferences),whith three keys, so adding another layer of relationship does not seems to be a good idea