Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Change DBI database on runtime

by miguelele (Beadle)
on Nov 12, 2011 at 19:28 UTC ( #937776=perlquestion: print w/replies, xml ) Need Help??
miguelele has asked for the wisdom of the Perl Monks concerning the following question:

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?

Replies are listed 'Best First'.
Re: Change DBI database on runtime
by zwon (Abbot) on Nov 13, 2011 at 04:13 UTC

    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.

      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 => '', 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?


        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.

Re: Change DBI database on runtime
by keszler (Priest) on Nov 12, 2011 at 21:30 UTC

    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.

      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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://937776]
Approved by moritz
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2018-06-20 15:24 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (116 votes). Check out past polls.