[Dbix-class] Using and inter-linking multiple databases via DBIC: "Can't find source"

Tue Dec 3 23:11:05 GMT 2013

On 4/12/2013 6:24 AM, Mark West wrote:
>
> Hello,
>
> I am trying to create a similar set up connecting to two postgres =

> databases on the same host and having no luck. (These are two separate =

> databases not two schemas.)
>
Hold and and think this. How would you do this in SQL? I didn't think =

this was possible in Postgres. MySQL, MSSQL and Oracle have a =

"namespace" concept that allows you to fully qualify the path with a =

<database_name>.<table_name> type of syntax. But correct me if I am =

wrong, AFAIK Postgres does not have this.

In any case, regardless that you are trying to merge your class =

generation into one Schema namespace you admittedly have two connection =

strings. Think of that like doing a join over two client applications or =

even two connection tabs in a query tool. It just cannot be done.

You could look at 'dblink' but that is going to require custom SQL or =

other munging and is more of a subselect
than a true join.

Alternately, rethink your design. Schema namespacing ( in the PostgresQL =

sense not the DBIC sense ) is allowed
but you must be on the same "database" and therefore connection as it is =

required in the DSN. Check the docs here:
http://www.postgresql.org/docs/9.1/static/ddl-schemas.html

> I am using Schema::Loader to create my result class files as well as =

> the database connection files using the following commands:
>
> script/myapp_create.pl model DB DBIC::Schema MyApp::Schema =

> create=3Dstatic components=3DTimeStamp,EncodedColumn =

> dbi:Pg:dbname=3Dhandshake_vt 'user' 'pw' '{ AutoCommit =3D> 1 }'
>
> script/myapp_create.pl model DB2 DBIC::Schema MyApp::Schema =

> create=3Dstatic components=3DTimeStamp,EncodedColumn =

> dbi:Pg:dbname=3Dlegislators_vt 'user' 'pw' '{ AutoCommit =3D> 1 }'
>
> I get the following error when trying to connect to tables on the =

> second database [error] DBI Exception: DBD::Pg::st execute failed: =

> ERROR:  relation "countries" does not exist
>
> I've created the following files:
>
> 1.DB.pm
>
> package MyApp::Model::DB;
>
> use strict;
>
> use base 'Catalyst::Model::DBIC::Schema';
>
> __PACKAGE__->config(
>
> schema_class =3D> 'MyApp::Schema',
>
> connect_info =3D> {
>
> dsn =3D> 'dbi:Pg:dbname=3Dhandshake_vt',
>
> }
>
> );
>
> 2.DB2.pm
>
> package MyApp::Model::DB2;
>
> use strict;
>
> use base 'Catalyst::Model::DBIC::Schema';
>
> __PACKAGE__->config(
>
> schema_class =3D> 'MyApp::Schema',
>
> connect_info =3D> {
>
> dsn =3D> 'dbi:Pg:dbname=3Dlegislators_vt',
>
> }
>
> );
>
> 3.Schema.pm
>
> package MyApp::Schema;
>
> __PACKAGE__->load_namespaces;;
>
> My Table Class basically looks like this:
>
> package Handshake::Schema::Result::Country;
>
> ...
>
> __PACKAGE__->table("countries");
>
> In my controller I'm calling the model by specifying the second =

> database handle.
>
> So, have I misconfigured one of these files or am I missing another =

> configuration step to connect to two databases at one time?
>
> Or, is there another example of a Schema::Loader script that will set =

> up the dual connection?
>
> Thanks for any help.
>
> Mark
>
> *From:*Henry Van Styn [mailto:vanstyn at cpan.org]
> *Sent:* Friday, October 18, 2013 3:13 PM
> *To:* DBIx::Class user and developer list
> *Subject:* Re: [Dbix-class] Using and inter-linking multiple databases =

> via DBIC: "Can't find source"
>
> On 10/18/2013 1:26 PM, will trillich wrote:
>
>     We are using Catalyst for a web app. We've split off the
>     authentication database to facilitate multiple different apps
>     using the same user credentials, but we're having trouble linking
>     the auth db to the biz-info db.
>
>     The generalized user/team credentials and related info are in
>     Auth, and the actual business info for this app is in DB. Here's
>     an object from the DB database:
>
>     package Learn::Schema::DB::Result::TeamEmail;
>
>     #...
>
>     __PACKAGE__->belongs_to( team =3D>
>     'Learn::Schema::Auth::Result::Team' );
>
>     Note that we're trying to get DB::TeamEmail to refer to Auth::Team
>     here. And below is the related object from the Auth database:
>
>     package Learn::Schema::Auth::Result::Team;
>
>     #...
>
>     __PACKAGE__->has_many( emails =3D>
>     'Learn::Schema::DB::Result::TeamEmail', 'team' );
>
>     At this point we expect
>
>     *$team->emails_rs*
>
>     to work but instead we get
>
>     Can't find source for Learn::Schema::DB::Result::TeamEmail
>
>     because TeamEmail isn't in $self->source_registrations (full
>     names) or $self->class_mappings (brief names) in the
>     DBIx::Class::Schema::source() method.
>
>     Pointers? Is there a different approach? Got a clue stick?
>
>
> You probably just need to load the additional result classes into your =

> schema.
>
> Assuming you are using the typical __PACKAGE__->load_namespaces call =

> in your Schema class, you can call it with parameters to find classes =

> under multiple namespaces. Something like this in your Learn::Schema::DB:
>
>  __PACKAGE__->load_namespaces(
>    result_namespace    =3D> [ '+Learn::Schema::Auth::Result', 'Result' ],
>    resultset_namespace =3D> [ =

> '+Learn::Schema::Auth::ResultSet','ResultSet' ],
>  );
>
> See https://metacpan.org/module/DBIx::Class::Schema#load_namespaces =

> for more details
>
> You will also probably need to make sure you use 'dbname.tablename' =

> instead of just 'tablename' in the __PACKAGE__->table() call in your =

> result classes. If you're using Schema::Loader, see the =

> 'qualify_objects' option to have this generated automatically:
>
> https://metacpan.org/module/DBIx::Class::Schema::Loader::Base#qualify_obj=
ects
>
> Hope this helps,
>
> vanstyn
>
>
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.=
co.uk

---
This email is free from viruses and malware because avast! Antivirus protec=
tion is active.
http://www.avast.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20131204/802=
09543/attachment-0001.htm