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=static components=TimeStamp,EncodedColumn
dbi:Pg:dbname=handshake_vt 'user' 'pw' '{ AutoCommit => 1 }'
script/myapp_create.pl model DB2 DBIC::Schema MyApp::Schema
create=static components=TimeStamp,EncodedColumn
dbi:Pg:dbname=legislators_vt 'user' 'pw' '{ AutoCommit => 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 => 'MyApp::Schema',
connect_info => {
dsn => 'dbi:Pg:dbname=handshake_vt',
}
);
2.DB2.pm
package MyApp::Model::DB2;
use strict;
use base 'Catalyst::Model::DBIC::Schema';
__PACKAGE__->config(
schema_class => 'MyApp::Schema',
connect_info => {
dsn => 'dbi:Pg:dbname=legislators_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:vans...@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 =>
'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 =>
'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 => [ '+Learn::Schema::Auth::Result', 'Result' ],
resultset_namespace => [
'+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_objects
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 protection
is active.
http://www.avast.com
_______________________________________________
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
