[Catalyst] Accessing DB from external model

[Darren Duncan]

Mike Raynham wrote:
> Here, the connection information is moved from MyApp::Model::DB to 
> MyApp::DB, and then Catalyst::Model::Adaptor is used to glue 
> MyApp::Model::DB to MyApp::DB.  Is it a good idea?  MyApp::Model::DB 
> appears to be part of the Catalyst application, so if I want to access 
> the database from an external model, it makes sense to me to move the 
> connection code outside of the Catalyst application.

What you want to do is turn all the stuff that mediates access to the database 
into your own shared library formatted kind of like one you might find as a CPAN 
module, say "MyDBLib", and then "use" it in your Catalyst application by way of 
Catalyst::Model::Adaptor.

For any configuration details that might vary either per application or per 
deployment, make these user-configurable parameters of MyDBLib, with then each 
of your applications would supply arguments to it when instantiating a MyDBLib 
object; in the Catalyst app's case, said arguments would be in your Catalyst app 
config file as is normal for MyApp::Model::DB.

For any configuration details that are unlikely to vary per application or per 
deployment, especially if they are details for which your actual code would 
vary, then put these directly in your MyDBLib code instead.

Octavian Rasnita wrote:
> The best idea would be to put the connection information in the application's config file like in the example below. (This example uses a Perl data structure, but you can use any configuration type accepted by Config::Any).
> 
> 'Model::DB' => {
>  schema_class => 'MyApp::Schema',
>  connect_info => {
>    dsn => 'dbi:Oracle:host=10.10.10.10;port=1521;sid=ora8',
>    user => 'user',
>    password => "password",
>    #name_sep => '.',
>    LongReadLen => 100*1024*1024,
>    LongTruncOk => 1,
>    on_connect_call => 'datetime_setup',
>    on_connect_do => [
>      "alter session set NLS_COMP='LINGUISTIC'",
>      "alter session set NLS_SORT='BINARY_AI'",
>    ],
>  },
> },
> 
> The model will access the connection information directly if it is defined in the config file, and you can use the data from this config file in any other external program.
> You can use the module Config::JFDI for using the Catalyst config file easier.

Now some of this looks wrong to me; a lot of those details should be in your 
MyDBLib code instead of the config file.  Your config file should instead look 
more like this:

'Model::DB' => {
  schema_class => 'MyApp::Schema',
  connect_info => {
    host => '10.10.10.10',
    port => 1521,
    user => 'user',
    password => "password",
  },
},

These are more details that a non-programmer user would set.

The other details should all be in your code instead, because users shouldn't 
have to know and in particular if they are changed your code could break.  In 
particular I'm thinking of stuff like "on_connect_call" or the fact you are 
using "dbi:Oracle".

Where it can get fuzzier is if your application is cross-DBMS portable, in which 
case more info would need to be in the config file, but you'd want to abstract 
it somehow, such as users just naming the DBMS and then they are actually 
picking from a pre-defined profile which has the code-specific details.  But if 
the app isn't supposed to be portable, eg if it uses Oracle-specific stuff, then 
don't put dbi:Oracle and NLS* and that stuff in your user config file.

Stuff like LongReadLen might be okay here if and only if changing it just tunes 
performance, like you were setting a chunk-buffer size or something, and would 
have no impact on behavior nor break any code; otherwise don't have it here.

-- Darren Duncan