Re: [Dbix-class] multiple database connection
On Fri, Oct 21, 2011 at 7:52 AM, Roland Philibert wrote:
>
> Hi all,
> Could anybody send me an example on how to connect multiple databases
> using DBIC::Schema?
>
> I have:
>
> package MyApp::Model::DB;
>
> use strict;
> use base 'Catalyst::Model::DBIC::Schema';
>
> __PACKAGE__->config(
>schema_class => 'MyApp::Schema',
>
>connect_info => {
>dsn => 'dbi:mysql:database=db1:host=host1',
>user => 'root',
>password => 'root',
>}
> );
>
> And I need to connect to another mysql db (db2) under a different host
> (host2)
>
>
[assuming catalyst app]
Create a second model MyApp::Model::DB2 with connection info for the second
db. I autocreate them using...
# create/update model for netadmin db
script/netadmin_create.pl model *DB* DBIC::Schema NetAdmin::Schema
create=static components=TimeStamp
'dbi:Pg:dbname=netadmin;host=hostname;port=5432' 'username' 'password' '{
AutoCommit => 1, quote_char => q{"}, }'
# create/update model for radius db
script/netadmin_create.pl model *RadiusDB* DBIC::Schema
NetAdmin::RadiusSchema create=static components=TimeStamp
'dbi:Pg:dbname=radius;host=hostname2' 'username2' 'password2' '{ AutoCommit
=> 1 }'
Then I use one connection using $c->model('*DB*::Table')->... and the other
using $c->model('*RadiusDB*::OtherTable')->...
Don't know if this is the best way, but it's what I use?!!
--
Steve Rippl
Technology Director
Woodland Public Schools
360 841 2730
___
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
[Dbix-class] New many-to-many warnings
Hi, I have a Catalyst app using DBIx::Class and just updated to latest versions and suddenly I'm getting warnings when loading the schema from our Postgres DB that I hadn't seen before DBIx::Class is up to date. (0.08204) DBIx::Class::Schema::Loader is up to date. (0.07033) *** DBIx::Class::Carp::__ANON__(): *** The many-to-many relationship 'roles' is trying to create a utility method called roles_rs. This will completely overwrite one such already existing method on class Site::Schema::Result::User. You almost certainly want to rename your method or the many-to-many relationship, as the functionality of the original method will not be accessible anymore. To disable this warning set to a true value the environment variable DBIC_OVERWRITE_HELPER_METHODS_OK * I'm getting a few of these on tables that I haven't altered in the interim, I'm not creating relationships manually, they're defined in the DB. Are these utility methods new? I'm not seeing them in the schema but I suppose that's what the warning is telling me? Is it OK to just set DBIC_OVERWRITE_HELPER_METHODS_OK to true? Thanks, Steve -- Steve Rippl Technology Director Woodland Public Schools 360 841 2730 ___ 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
[Dbix-class] Re: New many-to-many warnings
OK, my bad! I had previously manually defined many-to-many relations for a couple of the tables and now they're being autogenerated with the same names. Sorry! On Thu, Dec 6, 2012 at 8:17 PM, Rippl, Steve wrote: > Hi, > > I have a Catalyst app using DBIx::Class and just updated to latest > versions and suddenly I'm getting warnings when loading the schema from our > Postgres DB that I hadn't seen before > > DBIx::Class is up to date. (0.08204) > DBIx::Class::Schema::Loader is up to date. (0.07033) > > *** > DBIx::Class::Carp::__ANON__(): > *** > The many-to-many relationship 'roles' is trying to create a utility method > called roles_rs. > This will completely overwrite one such already existing method on class > Site::Schema::Result::User. > > You almost certainly want to rename your method or the many-to-many > relationship, as the functionality of the original method will not be > accessible anymore. > > To disable this warning set to a true value the environment variable > DBIC_OVERWRITE_HELPER_METHODS_OK > > * > > I'm getting a few of these on tables that I haven't altered in the > interim, I'm not creating relationships manually, they're defined in the > DB. Are these utility methods new? I'm not seeing them in the schema but > I suppose that's what the warning is telling me? Is it OK to just > set DBIC_OVERWRITE_HELPER_METHODS_OK to true? > > Thanks, > Steve > > -- > Steve Rippl > Technology Director > Woodland Public Schools > 360 841 2730 > -- Steve Rippl Technology Director Woodland Public Schools 360 841 2730 ___ 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
[Dbix-class] Generating DISTINCT ON with sub query
I have something like the following
$self->search(
$search,
{ join => [
{ 'course' => [
{ course_coursetypes => 'type' },
{ course_grades => 'grade' },
'course_locations',
] },
{ 'section' => { 'section_staffs' => 'staff' } }
],
'+select' => [
'staff.name_first', 'staff.name_last',
'course.name',
],
'+as' => [ qw( name_first name_last course_name ) ],
order_by => [ 'grade.id', 'course.name', 'section.name' ]
}
);
which give or take generates
SELECT "me"."section_id", "me"."course_id", "staff"."name_first",
"staff"."name_last", "course"."name"
FROM "course_section" "me" JOIN "course" "course"
ON "course"."id" = "me"."course_id"
LEFT JOIN "course_grade" "course_grades" ON "course_grades"."course_id" =
"course"."id"
LEFT JOIN "grade" "grade" ON "grade"."id" = "course_grades"."grade_id"
LEFT JOIN "course_location" "course_locations" ON
"course_locations"."course_id" = "course"."id"
JOIN "section" "section" ON "section"."id" = "me"."section_id"
LEFT JOIN "section_staff" "section_staffs" ON "section_staffs"."section_id"
= "section"."id"
LEFT JOIN "staff" "staff" ON "staff"."id" = "section_staffs"."staff_id"
WHERE ( "section"."active" = 1 )
ORDER BY "grade"."id", "course"."name", "section"."name"
Sections can have multiple grades, so the join to course_grades can produce
multiple lines for each section. I need that join so I can still sort by
their lowest grade (for sensible ordered display) but then I want to go
back to distinct sections. The following SQL (in postgres) seems to do
that for me...
SELECT DISTINCT ON (section_id) * FROM (
SELECT "me"."section_id" AS section_id, "me"."course_id",
"staff"."name_first", "staff"."name_last", "course"."name"
FROM "course_section" "me" JOIN "course" "course"
ON "course"."id" = "me"."course_id"
LEFT JOIN "course_grade" "course_grades" ON "course_grades"."course_id"
= "course"."id"
LEFT JOIN "grade" "grade" ON "grade"."id" = "course_grades"."grade_id"
LEFT JOIN "course_location" "course_locations" ON
"course_locations"."course_id" = "course"."id"
JOIN "section" "section" ON "section"."id" = "me"."section_id"
LEFT JOIN "section_staff" "section_staffs" ON
"section_staffs"."section_id" = "section"."id"
LEFT JOIN "staff" "staff" ON "staff"."id" = "section_staffs"."staff_id"
WHERE ( "section"."active" = 1 )
ORDER BY "grade"."id", "course"."name", "section"."name"
) AS foo
Can I generate that in dbic?
Thanks,
Steve
--
Steve Rippl
Technology Director
Woodland Public Schools
360 841 2730
___
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
Re: [Dbix-class] Generating DISTINCT ON with sub query
On Wed, Apr 3, 2013 at 2:46 AM, Peter Rabbitson wrote:
> On Tue, Apr 02, 2013 at 08:20:34PM -0700, Rippl, Steve wrote:
> > I have something like the following
> >
> > $self->search(
> > $search,
> > { join => [
> > { 'course' => [
> > { course_coursetypes => 'type' },
> > { course_grades => 'grade' },
> > 'course_locations',
> > ] },
> > { 'section' => { 'section_staffs' => 'staff' } }
> > ],
> > '+select' => [
> > 'staff.name_first', 'staff.name_last',
> > 'course.name',
> > ],
> > '+as' => [ qw( name_first name_last course_name ) ],
> > order_by => [ 'grade.id', 'course.name', 'section.name'
> ]
> > }
> > );
> >
> > Sections can have multiple grades, so the join to course_grades can
> produce
> > multiple lines for each section. I need that join so I can still sort by
> > their lowest grade (for sensible ordered display) but then I want to go
> > back to distinct sections. The following SQL (in postgres) seems to do
> > that for me...
> >
> > SELECT DISTINCT ON (section_id) * FROM (
> > SELECT "me"."section_id" AS section_id, "me"."course_id",
> > "staff"."name_first", "staff"."name_last", "course"."name"
> > FROM "course_section" "me" JOIN "course" "course"
> > ON "course"."id" = "me"."course_id"
> > LEFT JOIN "course_grade" "course_grades" ON
> "course_grades"."course_id"
> > = "course"."id"
> > LEFT JOIN "grade" "grade" ON "grade"."id" =
> "course_grades"."grade_id"
> > LEFT JOIN "course_location" "course_locations" ON
> > "course_locations"."course_id" = "course"."id"
> > JOIN "section" "section" ON "section"."id" = "me"."section_id"
> > LEFT JOIN "section_staff" "section_staffs" ON
> > "section_staffs"."section_id" = "section"."id"
> > LEFT JOIN "staff" "staff" ON "staff"."id" =
> "section_staffs"."staff_id"
> > WHERE ( "section"."active" = 1 )
> > ORDER BY "grade"."id", "course"."name", "section"."name"
> > ) AS foo
> >
> > Can I generate that in dbic?
>
> Not directly because this is highly non-standard SQL, and there isn't
> smooth support for this kind of thing in the API yet. However there are
> other ways of achieveing what you want. I want to clarify however what
> is your final goal. From your relationship map it is unclear:
>
> - What is the relationship between me (course_section) and section. Is this
> a belongs_to ? a might_have? a has_many?
>
> - Your Pg query would return *multiple* identical course_section's that
> only differ by the related names injected into them by the +select. This
> seems rather non-DBIC-ish - the usual way os to have has_many related
> data hanging as separate objects.
>
> Clarify the above and I'll get back to you with practical ways to
> achieve what you want
>
>
Thanks for the offer of help Peter.
CourseSection.pm
...
__PACKAGE__->belongs_to(
"course",
"WsdSis::Schema::Result::Course",
{ id => "course_id" },
{ is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);
__PACKAGE__->belongs_to(
"section",
"WsdSis::Schema::Result::Section",
{ id => "section_id" },
{ is_deferrable => 1, on_delete => "CASCADE", on_update => "CASCADE" },
);
Each Section only has one teacher and belongs to one course, so it's not
the +select columns that are giving me multiple values as those are all the
same for each repeated row. A course can have multiple grades and so
that's where the join is producing multiple rows per section_id.
Course.pm
...
__PACKAGE__->has_many(
"course_grades",
"WsdSis::Schema::Result::CourseGrade",
{ "foreign.course_id" => "self.id" },
{ cascade_copy => 0, cascade_delete => 0 },
);
I was trying to group by section_id, but when sorting on grade_id I have to
include that in the group by and it doesn't seem to actually do any
grouping by!
Have I given you enough info?
Thanks again,
Steve
--
Steve Rippl
Technology Director
Woodland Public Schools
360 841 2730
___
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
Re: [Dbix-class] Generating DISTINCT ON with sub query
On Wed, Apr 3, 2013 at 8:55 AM, Peter Rabbitson wrote:
> On Wed, Apr 03, 2013 at 08:41:05AM -0700, Rippl, Steve wrote:
> > Have I given you enough info?
>
> I think so but I want to make sure I got it right. Tell me if the thing
> below is correct:
>
> $course_section_rs->search({}, {
> join => [
> { 'course' => [ <--- me -> belongs_to(course)
> { course_coursetypes => 'type' }, <-- me -> belongs_to(course) ->
> has_many(course_coursetypes) -> belongs_to(type)
> { course_grades => 'grade' }, <--- me -> belongs_to(course) ->
> has_many(course_grades) -> belongs_to(grade)
> 'course_locations', < me ->
> has_many(course_locations) (this one is not referenced anywhere - mistake?)
> ] },
> { 'section' => { 'section_staffs' => 'staff' } } me ->
> belongs_to(section) -> has_many(section_staffs) -> belongs_to(staff)
> ]})
>
> If this is true (specifically the has_many(section_staffs) part) then this:
> > Each Section only has one teacher and belongs to one course
> can't be correct...
>
> Please clarify ;)
You are correct with the relations, logically each section should only have
one teacher but I haven't specified that and the above relations are the
ones auto-generated. I have "CONSTRAINT section_id_staff_id PRIMARY KEY
(section_id, staff_id)" on the section_staff table when I guess just
staff_id should be the primary key?
The joins to course_coursetype and course_locations are there because in
some contexts I want to filter by location or type so my original $search
would change accordingly, but right now I'm trying to get the most general
case to work.
Thanks
___
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
