[Dbix-class] Re: Presentation about DBIx::Class
On 1/26/2010 5:42 PM, dbix-class-requ...@lists.scsys.co.uk wrote: Date: Tue, 26 Jan 2010 14:06:10 -0800 From: Ihnen, Daviddih...@amazon.com Subject: [Dbix-class] So they want ME to give a presentation about DBIx::Class So seems to be interest in the Seattle Perl Users Group to learn more about using DBIx::Class - and since no good deed (like suggesting an interesting topic) goes unpunished, I'm drafted to do a presentation next month to the group about DBIx::Class! I know I'll be deriving a lot of my material from the fine manual pages and cookbook and my experience, but thought I'd toss this out there in case anybody has further thoughts or 'don't forget to tell them...' sorts of things to say as I collect materials to prepare for this. Welcoming contributions, David David, As a relative newcomer to DBIx::Class myself, the distinction between a Result and a ResultSet was puzzling. Especially when using D:C:Loader, as depending on the command line arguments, there may not be a Result directory, and there certainly won't be a ResultSet directory. The very fact that you don't have to have a ResultSet directory is a good tidbit! Good Luck! Steve ___ 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] Insert into join table succeeds but issues error anyway
Hi, I'm using Catalyst along with DBIC, and I recently got this error: DBIx::Class::ResultSet::create(): DBI Exception: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`WebStmt/users_roles`, CONSTRAINT `users_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)) [for Statement "INSERT INTO users_roles ( role_id, user_id) VALUES ( ?, ? )" with ParamValues: 0='HASH(0xb57f080)', 1='66'] at /home/steve/Catalyst/WebStmt/script/../lib/FSUstmt/Controller/WebUsers.pm line 109 The interesting thing is that even though this error was thrown, the insert worked. The use case is simply that I've just created a new 'User', and I now want to give that user a 'Role'. I'm using a join table to do so. That table has the id's of both the User and Role(s) associated. Is this a bug? The problem I have is that even though the insert works, it blows up my Catalyst application. Thoughts? attachment: steve.vcf___ 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: Insert into join table succeeds but issues error anyway
Hello again. My error got scrubbed earlier, so this is a second attempt. I'm using Catalyst along with DBIC, and I recently got the error below. The interesting thing is that even though this error was thrown, the insert worked. The use case is simply that I've just created a new 'User', and I now want to give that user a 'Role'. I'm using a join table to do so. That table has the id's of both the User and Role(s) associated. Is this a bug? The problem I have is that even though the insert works, it blows up my Catalyst application. Thoughts? DBI Exception: DBD::mysql::st execute failed: Cannot add or update a child row: a foreign key constraint fails (`WebStmt/users_roles`, CONSTRAINT `users_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)) [for Statement "INSERT INTO users_roles ( role_id, user_id) VALUES ( ?, ? )" with ParamValues: 0='HASH(0xb57f080)', 1='66'] On 4/16/2010 5:54 AM, dbix-class-requ...@lists.scsys.co.uk wrote: Send DBIx-Class mailing list submissions to dbix-class@lists.scsys.co.uk To subscribe or unsubscribe via the World Wide Web, visit http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class or, via email, send a message with subject or body 'help' to dbix-class-requ...@lists.scsys.co.uk You can reach the person managing the list at dbix-class-ow...@lists.scsys.co.uk When replying, please edit your Subject line so it is more specific than "Re: Contents of DBIx-Class digest..." Today's Topics: 1. Insert into join table succeeds but issues error anyway (Steve) 2. Result Classes (Ben Hitz) 3. Jobs, jobs, jobs, and jobs (don't forget about jobs!) (fREW Schmidt) 4. can i add custom error info for throw_exception in dbix::class 0.07006? (??) 5. Re: can i add custom error info for throw_exception in dbix::class 0.07006? (Alexander Hartmaier) 6. Re: can i add custom error info for throw_exception in dbix::class 0.07006? (??) 7. Re: New experimental development release of DBD::ODBC 1.23_4 - faster - please test (Martin Evans) -- Message: 1 Date: Thu, 15 Apr 2010 15:40:57 -0400 From: Steve st...@matsch.com Subject: [Dbix-class] Insert into join table succeeds but issues error anyway To: dbix-class@lists.scsys.co.uk Message-ID: 4bc76bc9.1040...@matsch.com Content-Type: text/plain; charset="us-ascii" An HTML attachment was scrubbed... URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100415/deaef6f4/attachment-0001.htm -- next part -- A non-text attachment was scrubbed... Name: steve.vcf Type: text/x-vcard Size: 268 bytes Desc: not available Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20100415/deaef6f4/steve-0001.vcf -- Message: 2 Date: Thu, 15 Apr 2010 14:29:51 -0700 From: Ben Hitz h...@genome.stanford.edu Subject: [Dbix-class] Result Classes To: "DBIx::Class user and developer list" dbix-class@lists.scsys.co.uk Message-ID: ad38710a-14a1-4eca-8a1c-86eca5292...@genome.stanford.edu Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes I don't use load_namespaces, but I blundered into it via some Catalyst docs. With no arguments, this method uses Module::Find to load all your Result classes from a sub-namespace Result under your Schema class' namespace, i.e. with a Schema of MyDB::Schema all files in MyDB::Schema::Result are assumed to be Result classes What is a "Result class" ? Did I miss a memo? I know what a ResultSet class is (sort of) and I know what a ResultSource class is (at least in the sense of it corresponding to a DB Table...) but this terminology is new to me. There is a link to a non-existent glossary entry in the DBIx::Class::ResultSource POD as well. "As mentioned above, a result source instance is created and stored for you when you define a a href="" class="moz-txt-link-rfc2396E" href="http://search.cpan.org/~frew/DBIx-Class-0.08121/lib/DBIx/Class/Manual/Glossary.pod#Result_Class">"http://search.cpan.org/~frew/DBIx-Class-0.08121/lib/DBIx/Class/Manual/Glossary.pod#Result_Class "Result Class/a" If I had to guess, I would say that Result Class = "class of ResultSource instance object"... but that's just a guess. Ben -- Ben Hitz Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium Stanford University ** h...@genome.stanford.edu -- Message: 3 Date: Thu, 15 Apr 2010 19:49:24 -0500 From: fREW Schmidt fri...@gmail.com Subject: [Dbix-class] Jobs, jobs, jobs, and jobs (don't forget about jobs!) To: The elegant MVC web framework catal...@lists.scsys.co.uk, "DBIx::Class user and developer list" dbix-class@lists.scsys.co.uk Message-ID: l2wfb3648c61004151749s21b58735r83fcd29ef70ac...@m
[Dbix-class] missing 'many-to-many' relationship
Hi all,
I am bewildered as to why my 'many_to_many' relationship is being ignored.
I get an error when trying to use the relationship because it 'does not
exist',
and clearly it doesn't. WHY?
My result classes are 'User.pm', 'Role.pm', and 'UserRole.pm'.
I'm using the DBIC::Loader to generate the 'has_many', and 'belongs_to'
relationships, which leaves me to create the 'many_to_many' relationship
manually.
The User.pm result class relationships:
(generated by loader)
__PACKAGE__-has_many(user_roles, DCO::Schema::Result::UserRole,
{ foreign.users_id = self.id });
(written by me)
__PACKAGE__-many_to_many('roles', 'user_roles', 'role');
I set a breakpoint for the debugger just prior to the 1; at the end of
the package
and interrogated the schema. I clearly see that the 'user_roles'
relationship exists,
but the 'roles' relationship does not. No syntax errors, all other
relationships
are being used successfully in the schema.
Any clue as to why this relationship is not created?
Thanks as always,
Steve
___
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] missing 'many-to-many' relationship
On 8/4/2010 10:46 AM, fREW Schmidt wrote:
On Wed, Aug 4, 2010 at 7:34 AM, Steve st...@matsch.com
mailto:st...@matsch.com wrote:
Hi all,
I am bewildered as to why my 'many_to_many' relationship
is being ignored.
I get an error when trying to use the relationship because
it 'does not
exist',
and clearly it doesn't. WHY?
My result classes are 'User.pm', 'Role.pm', and 'UserRole.pm'.
I'm using the DBIC::Loader to generate the 'has_many', and
'belongs_to'
relationships, which leaves me to create the
'many_to_many' relationship
manually.
...
include the error message please.
From memory: No such relationship roles in DCO::Schema::Result::User
So to be clear, you are saying that you added the roles many to many
in user and you do $user-roles and get that error?
I'm guessing you did something like $user-related_resultset('roles'),
which wouldn't work since technically m2m isn't a relationship.
That's exactly what I'm doing! Since I had to get something working I
took a couple of extra steps,
and got the functionality I was looking for, but I find myself wondering
what the benefit of having a
'many_to_many' pseudo-relationship is?
Thank you for this explanation, it will keep me sane! :)
I can't really say more though without seeing actual code and actual
errors.
--
fREW Schmidt
http://blog.afoolishmanifesto.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
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3050 - Release Date: 08/04/10
00:45:00
___
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] Chained resultsets and WHERE operators
It appears that you are *not* chaining these resultsets...
If you first do...
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
then in order to 'chain' you must...
$date_ranges = $date_ranges-search({...
You overwrote your resultset when you...
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
On 11/9/2010 8:36 AM, Mike Raynham wrote:
Hi,
I am probably missing something obvious here, but have been unable to
find a clean solution to the following problem, and I'm hoping that
your expert knowledge will come to the rescue.
I have created a query which determines if a given date range exists
within date ranges stored in the database:
###
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
my $date_ranges = $rs-search({
-or = [
-and = [
start_date = { '=', $start_date },
end_date = { '=', $start_date },
],
-and = [
start_date = { '=', $end_date },
end_date = { '=', $end_date },
],
],
});
###
This works correctly, but I would like to be able to run the search
for open ended date ranges - that is, if only the start or only the
end date is supplied. I have attempted to do this by chaining search
queries, as follows:
###
# Create a search.
my $date_ranges = $rs-search({});
# Narrow the search down if the start date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $start_date },
end_date = { '=', $start_date },
}) if $start_date;
# Narrow the search down if the end date is supplied.
$date_ranges = $rs-search({
start_date = { '=', $end_date },
end_date = { '=', $end_date },
}) if $end_date;
###
This works correctly when either the start or end date is supplied.
However, when both dates are supplied, the two WHERE conditions are
chained together with an AND:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) AND
# (start_date = $end_date AND end_date = $end_date)
Instead, I need them to be chained together with an OR:
# SELECT * FROM my_table WHERE
# (start_date = $start_date AND end_date = $start_date) OR
# (start_date = $end_date AND end_date = $end_date)
Is there a way that I can cause the two queries to be chained together
with an OR instead of an AND?
At the moment, I have got round the problem by using the non-chained
query at the top of this post, and specifying out-of-range defaults
for dates that have not been supplied.
Regards,
Mike
___
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
___
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] Relationship question/database structure
Hi All, I'm trying to use an existing schema wherein I have a generic table called 'Note' containing - you guessed it, notes!. The thing is that these notes may be associated with many other types of data and therefore tables in my database. They may be related to a request, user, device, etc. I am sure that this is a fairly common use case, but don't see much in the docs or on the list. The question is two-fold: What's the best structure, and how do we go about defining the DBIC rels? Thanks, Steve ___ 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] Relationship question/database structure
Reading between the lines, is it your opinion that having several
differently named tables with identical fields is better than the
current approach?
On 1/10/2011 1:34 PM, David Ihnen wrote:
You're not thinking specifically enough. The column which has the ID
in it is being used to store disparate types of information - a
transaction note identifier is a DIFFERENT type than a customer
service event identifier - but they're in the *same column*. *hits
the penalty buzzer* bad dba. ;) The DB is almost assuredly not
constraint checking this column to make sure that the proper record
occurs in the proper foreign table. *buzz* This is sloppy db design
and is going to result in compromises in how you access it.
But relationships don't join on multiple columns.
As I said, you CAN create multiple belongs-to relationships, and as
long as your tableName column is included in your query (which is
sloppy because it should be encapsulated) then you're going to get the
result you want. Something like:
$caseresultset-search( { 'notes.TableName' = 'cases' }, { join =
notes } );
Also, you can chain resultsets - using a technique to create something
like this in your notes class
sub case_notes {
return shift-search( { TableName = 'case' } );
}
And query through the chain to find notes for the case.
my $cases = ...ResultSet('cases')-search( { case_id = 5 }
)-notes-case_notes;
(which is sloppy because it should be encapsulated in the relationship
without you being explicit about it)
What you really have constructed is an abstract data store for
instances of note data, and each type of note data will have its *own*
class - its not immediately obvious to me if you can actually express
that in DBIx::class - I'm sure that eventually we will be able to do
so. Its complicated though, and involves the idea of constraints on
subqueries which is just outside the purveyance of sql::abstract to my
understanding of the system.
Whatever way you manage to munge DBIx::Class into handling this
abstract data store for note instance data i don't think its going to
be as clean as anybody would like, probably a little sloppy and/or
ugly. Its not the level of problem the object relational mapper was
designed to solve, though i'm sure it will eventually be capable.
Patch? ;) (okay, that would probably require a pretty significant
level of changes...)
David
On Mon, Jan 10, 2011 at 1:18 PM, Steve st...@matsch.com
mailto:st...@matsch.com wrote:
I was unclear... sorry for that. The current 'note' table has two
columns: 'TableName' and 'TableId', where the 'TableName' field is
an abbreviation for the name of the table the note is related to,
and the 'TableId' is the record within that table.
So, it is in fact a multi-column join between the note table and
several other tables, each with their own 'TableName'.
Steve
On 1/10/2011 1:07 PM, David Ihnen wrote:
A relational database cannot easy express foreign key
constraints to multiple tables from a single column. In fact
I am sure you are using the artifact of identical underlying
native types to overload a single column to contain multiple
different types of data. That is to say that a reference to a
transaction entry is not the same as a reference to a customer
service event. But here you have placed both types of data in
the same column, permitted only because their underlying data
types were identical and you are not making the db check
foreign constraints. Now you want to express this sloppy
schema in the class structure and run into the fact it is
going to be sloppy there too.
You really should fix the schema to be tight and checked but
this is not an ideal world.
There isn't anything stopping you from adding multiple belongs
to directives to your notes result class other than it implies
that all the notes are related to all the tables -
semantically absurd but as long as you use the proper
constraints when joining the result will be what you want.
It's just... sloppy.
No offense intended and I do understand you are working under
constraints I am not familiar with that may limit your solutions.
Trying to be helpful,
Daivd
On Mon, Jan 10, 2011 at 12:58 PM, Skylos sky...@gmail.com
mailto:sky...@gmail.com mailto:sky...@gmail.com
mailto:sky...@gmail.com wrote:
A relational database cannot easy express foreign key
constraints
to multiple tables from a single column. In fact I am sure you
are using the artifact of identical underlying native types to
overload a single column to contain multiple different types of
data. That is to say that a reference to a transaction
entry is
not the same
Re: [Dbix-class] Multiple DBs, one Schema to rule them all
I second that thought. I was advised to do it this way from some
experienced Catalyst-ers.
On 2/28/2011 12:01 PM, Wes Cravens wrote:
On 2/11/2011 7:20 PM, Trevor Leffler wrote:
Hello,
I am investigating whether DBIC has support for switching between
logical databases (ala 'use db1; ...; use db2; ...;'). I've a situation
where there exist many cloned databases (same DDL), one per customer.
The current model is that all customers share the same front-end, and it
switches between DBs as needed. I would like to avoid the following:
multiple open connections per app process, multiple MyDB::Schema
objects, server hups upon customer adds/drops, etc.
My user authentication holds information on a particular login's
'default db'. I then use InstancePerContext. Not sure if this is a
great solution so I'm posting for criticism as much as anything.
code
package MyApp::Model::DB;
use strict;
use base 'Catalyst::Model::DBIC::Schema';
use Moose;
with 'Catalyst::Component::InstancePerContext';
__PACKAGE__-config(
schema_class = 'MyApp::DBICSchema::DB',
connect_info = []
);
sub build_per_context_instance {
my ($self, $c) = @_;
my $new = bless({ %$self }, ref($self));
$new-schema( $self-schema-connect( {
dsn = 'dbi:Pg:dbname=' . $c-user-default_db-dbname .
';host=' . $c-user-default_db-dbhost,
user = $c-user-default_db-dbusername,
password = $c-user-default_db-dbpassword } ) );
}
/code
The very reasonable suggestion to combine all of the client databases
into one is being considered, so please let's not get side-tracked by
that line of thought, thanks. :)
So... how might I have my Schema issue a 'use $db' prior to any queries?
Or perhaps are there other solutions to how one could have multiple DB
clones while avoiding the code and connection scaling issues?
Thanks,
--Trevor
___
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
___
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
___
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] Looking for a DBIC developer for new SaaS product
Hi Al, Depending on your requirements (mostly time) I might be able to lend a hand. I've been developing Catalyst applications for a little over 2 years in a SaaS environment. One of the applications serves a somewhat similar purpose, though narrower in scope. I'd need to hear more about your requirements and the project to see whether I could be of help. Feel free to call my cell if you'd like to discuss this further. Steve Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.com/ On 7/28/2011 11:42 PM, Al Newkirk wrote: Hello all, For the past three weeks I have been working on a SaaS product offering, the first for my company since moving away from custom software application development. The product is a Help Desk application called PHD (The Practical Tactical Help Desk) which I have been developing myself using DBIx-Class (obviously), and Mojolicious. I am having difficulty juggling other projects while working on this and handling the design, flow-control and business logic myself. I am looking for a single person to come on-board and create the necessary DBIC classes, application-specific functions, recommend logic, etc. If you're interested, please respond with your hourly rate or retainer and I will respond asap. standard startup/small business disclaimer - not alot of capital, please be gentle Thanks friends. ___ 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 ___ 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] set_on_create problem
Hello,
I have set up a user table both 'updatedon' and 'createdon' fields.
They are of type Datetime (MySQL). My entire schema was autogenerated
by DBIC::Loader script with the 'components=TimeStamp' directive. I
have confirmed that the resultset loads the InflateColumn::DateTime
component, and included the:
__PACKAGE__-add_columns( 'createdon',{ %{
__PACKAGE__-column_info('createdon') }, set_on_create = 1, } );
directive. If I understand this correctly... any time I create a new
user, this field should be populated with the current (system) time, but
it is not.
I am using HTML::FormHandler, and so have gotten 'around' this (bit of a
pun here in case you missed it) with a moose method modifier. This is a
kludge, and I would like to fix it by having DBIx::Class take care of it
for me.
Can anyone tell me what I'm missing, or if more info is needed?
Thanks,
Steve
___
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
Out of office 10/4-10/5 (was: [Dbix-class] Caching total_entires in the Pager -- how can build a good cache key?)
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: [Dbix-class] Caching total_entires in the Pager -- how can build a good cache key?))
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: [Dbix-class] Caching total_entires in the Pager -- how can build a good cache key?)))
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: [Dbix-class] Caching total_entires in the Pager -- how can build a good cache ke
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: [Dbix-class] Caching total_entires in the Pager --
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: [Dbix-class] Caching
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-10/5 (was: Out of office 10/4-1
Thank you for your email. I will be out of the office Thursday and Friday, returning Monday the 8th. I will respond to your email at that time. Please call our office for any immediate concerns: 616-459-0782. -- Steve Schafer Matsch Systems Phone: 616-477-9629 Mobile: 616-304-9440 Email: st...@matsch.com Web: http://www.matsch.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
Re: [Dbix-class] DBIx-Class Digest, Vol 176, Issue 1
If you use the convention:
my $value = $row->colABC, then you'll get the inflated value, provided
the column is defined as an object. If you want the raw value, ask for
$row->get_column('colABC').
On 12/9/2022 7:00 AM, dbix-class-requ...@lists.scsys.co.uk wrote:
Send DBIx-Class mailing list submissions to
dbix-class@lists.scsys.co.uk
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
or, via email, send a message with subject or body 'help' to
dbix-class-requ...@lists.scsys.co.uk
You can reach the person managing the list at
dbix-class-ow...@lists.scsys.co.uk
When replying, please edit your Subject line so it is more specific
than "Re: Contents of DBIx-Class digest..."
Today's Topics:
1. unexpected behavior using select with inflate_result
(Mitchell Elutovich)
--
Message: 1
Date: Fri, 9 Dec 2022 11:44:28 +0200
From: Mitchell Elutovich
Subject: [Dbix-class] unexpected behavior using select with
inflate_result
To: "DBIx::Class user and developer list"
Message-ID:
Content-Type: text/plain; charset="utf-8"
I wrote some code similar to:
my $row = $self->search_related('xyzes', {}, { select => "colABC", as =>
"colABC" }->first();
xyzes is of type XYZ
Because of the "select", I was not expecting inflate_result on XYZ to be
called, however it is.
-- next part --
An HTML attachment was scrubbed...
URL:
--
___
DBIx-Class mailing list
DBIx-Class@lists.scsys.co.uk
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
End of DBIx-Class Digest, Vol 176, Issue 1
**
___
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] DBIx::Class creating get_* and set_* methods
Hello,
I created a post on Perl Monks regarding this
question(http://www.perlmonks.org/?node_id=666868) and someone referred
to this mailing list.
This was my post verbatim,
---
I was looking to DBIx::Class
http://search.cpan.org/search?mode=modulequery=DBIx%3A%3AClass to
work with my database. The one thing I would like to change is the
accessor/mutators that it auto generates. Instead of the typical,
# Accessor method
$object-username;
# mutator method
$object-username('bob');
I want to methods that begin with 'get_' or 'set_' (depending on their
functionality).
# Accessor method
$object-get_username;
# mutator method
$object-set_username('bob');
Has anyone done anything for this for DBIx::Class?
Sincerely,
Steve K
___
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/[EMAIL PROTECTED]
[Dbix-class] Oracle Column Quoting
Howdy, I have a rather unique problem and I am unsure of how to fix this. I am connecting to Oracle, one of the tables I HAVE to work with is using a keyword as the column name (I didn't do it!). The way to get select statements to work is by single-quoting the column call. Example: SELECT 'me.level' FROM test me; The problem is that DBIx::Class does not do the single quoting by default and the single-quoting behavior, I don't believe, does not work for all databases. Does anyone have suggestions about how this problem could be addressed? Steve Kirkup ___ 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/[EMAIL PROTECTED]
[Dbix-class] Dynamically removing an auto-increment column
Hi everyone, I've just began using DBIx::Class late last week. Although I've got it doing pretty well everything I want, I'm quite overwhelmed by the amount of documentation, and hence, I'm having a hard time locating the solution to a specific issue. In normal mode, my software uses a MySQL Replicated setup, which is all well and good. In test mode, I use a Schema attached to an SQLite db. All reads work ok when switching back and forth, however any attempt to write break. For MySQL, I have to have the 'id' field populated with a null value when calling create(), and with SQLite, I have to remove the id field (auto incr). Instead of writing if/else code in all of the methods that do an insert to either include or exclude the id field, can someone advise on which docs I need to read to be able to dynamically remove the id field from the data I want to insert if SQLite is being used? Regards, Steve ___ 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] Dynamically removing an auto-increment column
Peter Rabbitson wrote: Steve Bertrand wrote: For MySQL, I have to have the 'id' field populated with a null value when calling create() Why? Please explain. Well, I can explain that I've been at this for hours on end, and when I went to dig up the code so I could explain, I realized that I was reading from an out-of-date database, so the writes weren't showing up. Apparently, beating ones head off the wall for hours and embarrassing themselves by posting publicly makes the reason for an issue immediately apparent. Oh well, as a side effect, I've probably read half of the man pages, and learnt a tremendous amount regarding DBIx::Class flexibility ;) Steve ___ 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] Dynamically removing an auto-increment column
Dermot wrote: 2009/12/8 Steve Bertrand st...@ibctech.ca: Hi everyone, Hi Steve, I've just began using DBIx::Class late last week. Although I've got it doing pretty well everything I want, I'm quite overwhelmed by the amount of documentation, and hence, I'm having a hard time locating the solution to a specific issue. In normal mode, my software uses a MySQL Replicated setup, which is all well and good. In test mode, I use a Schema attached to an SQLite db. All reads work ok when switching back and forth, however any attempt to write break. For MySQL, I have to have the 'id' field populated with a null value when calling create(), and with SQLite, I have to remove the id field (auto incr). Instead of writing if/else code in all of the methods that do an insert to either include or exclude the id field, can someone advise on which docs I need to read to be able to dynamically remove the id field from the data I want to insert if SQLite is being used? I don't want to feed you incorrect information here so hopefully someone will confirm. create, insert and populate do not need to be supplied a id (where id is a Primary Key, with or without auto-increment). That is taken care of by DBIx::Class::PK::Auto. You use id generally for search/find. Omit the id from your hashref. Thanks Dermot, I'm now going through all of my code and removing the id field from everywhere. (Un)fortunately, this was a case of reading from a stale replicated MySQL server, which is why I could not see the updates. Having the id field in tact, broke completely when running my tests. All is well now, and I've learnt a great deal in the process. Steve ___ 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] Dynamically removing an auto-increment column
Peter Rabbitson wrote: Steve Bertrand wrote: (Un)fortunately, this was a case of reading from a stale replicated MySQL server... Are you doing the reading manually or are you using the Replicated storage driver? If the former - take a look at Replicated. If the later - make sure you wrap your inserts in a transaction - this will trigger an always-read-from-master mode. I am using Replicated, and I'll have a look at wrapping in a transaction. The entire DBIx::Class is a lot to take in... it took me two days to figure out that all I had to do to switch between Replicated storage and normal DBI storage via a config file was to create a simple subclass ;) ...quite different from what I'm used to. Steve ___ 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] Issue converting select/insert from DBI to DBIx::Class
Hi all,
I've been working on converting much of my DBI code to DBIx::Class, and
am a tad bit stuck.
This particular issue is one that I've worked on for quite a few hours,
while testing out different combinations I've found in the great number
of docs. I'm at the point now where I could use an experienced set of eyes.
My original code:
my $insert_sth = $dbh-prepare(
INSERT INTO aggregate_daily
( UserName, AcctDate, ConnNum,
ConnTotDuration, ConnMaxDuration, ConnMinDuration,
InputOctets, OutputOctets, NASIPAddress
)
SELECT UserName, '$day', COUNT(*), SUM(AcctSessionTime),
MAX(AcctSessionTime), MIN(AcctSessionTime),
SUM(AcctInputOctets),
SUM(AcctOutputOctets), NASIPAddress
FROM radacct
WHERE AcctStopTime like '$day%'
GROUP BY UserName,NASIPAddress
) or die $DBI::errstr;
...and the new code (albeit unfinished, as I am just trying to get the
basics of a select down first). Although it runs without error, I have
no indication that I'm doing things properly. When I dump the $rs with
Data::Dumper, I do not see the expected data.
Can someone let me know if I'm on the right track?
my $rs = $schema-resultset( 'Radacct' )-search( undef, {
select = [
'username',
{ count = 'radacctid' },
{ sum = 'acctsessiontime' },
{ max = 'acctsessiontime' },
{ min = 'acctsessiontime' },
{ sum = 'acctinputoctets' },{
{
sum = 'acctoutputoctets' },
'nasipaddress',
],
group_by = [ qw/ username nasipaddress / ],
as = [ 'daily' ],
});
Steve
___
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] Issue converting select/insert from DBI to DBIx::Class
Rob Kinyon wrote:
The first problem is that you're ignore AcctDate in the select and
AcctStopTime in the where clauses. Second is the 'as' element is
unneeded.
Thanks Rob, you set me on another tangent, so I now have this working
code which simulates exactly what I had before (which hopefully doesn't
get mangled):
my $daily_fetch_rs = $schema-resultset( 'Radacct' )-search(
{
'username' = 'test3',
'acctstoptime' = { like = $day% },
},{
select = [
'username',
{ count = 'radacctid' },
{ sum = 'acctsessiontime' },
{ max = 'acctsessiontime' },
{ min = 'acctsessiontime' },
{ sum = 'acctinputoctets' },
{ sum = 'acctoutputoctets' },
'nasipaddress',
],
group_by = [ qw/ username nasipaddress / ],
as = [ qw/
UserName
ConnNum
ConnTotDuration
ConnMaxDuration
ConnMinDuration
InputOctets
OutputOctets
NASIPAddress
/,
],
});
$daily_fetch_rs-result_class(
'DBIx::Class::ResultClass::HashRefInflator'
);
my $agg_table = $schema-resultset( 'DailyAgg' );
while ( my $daily_entry = $daily_fetch_rs-next() ) {
$daily_entry-{ AcctDate } = $day;
$agg_table-create( $daily_entry );
}
Does that help?
Yep! Thanks ;)
Steve
___
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] Weird INSERT RETURNING error after updating to DBIx::Class .08123
Searched the archives on this and didn't see anyone else raise the issue
Just upgraded to DBIx::Class .08123 from 08120.
I see in the Changelog:
DBIx::Class
0.08121 2010-04-11 18:43:00 (UTC)
- Add core support for INSERT RETURNING (for storages that supports
this syntax, currently PostgreSQL and Firebird)
I'm using Postgres. Before the update, a simple create query was dispatched as:
INSERT INTO project.vendor_view ( name, project,
classification_xref) VALUES ( 1277144170, 3819016, 10 );
That worked great and returned the last_insert_id as expected.
After upgrading to DBIx::Class . 08123, the SQL generated is now:
INSERT INTO project.vendor_view ( name, project,
classification_xref) VALUES ( 1277144170, 3819016, 10 ) RETURNING id
And that throws the error:
ERROR: cannot perform INSERT RETURNING on relation vendor_view
HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING
clause.
I see in DBIX::Class::Storage::DBI::Pg the comment:
# only used when INSERT ... RETURNING is disabled
sub last_insert_id {
...
}
Seems to me I want to disable the RETURNING functionality to make the behavior
like it was, but I can't figure out how to do that.
Anyone else experienced this?
Thanks in advance.
-steve kleiman
___
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] multiple database connection
On Fri, Oct 21, 2011 at 7:52 AM, Roland Philibert rphilib...@aptina.comwrote:
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] Join Myself?
I'm trying to convert this SQL statement to DBIC and I'm failing miserably.
given:
mytable
id
foreignkey
date_added
SQL
--
select T1.id from mytable T1
left join mytable T2 on
(T1.foreignkey = T2.foreignkey
and
T1.date_added T2.date_added)
where T2.date_added is null
order by T1.date_added desc;
-
At first I just tried joining the table normally but I'm stumped as to
how to add a table to itself without changing the Mytable.pm and
setting a relationship back to itself…
I tried adding a belongs_to relationship to Mytable.pm as 'myself' and
link the foreign keys but I seem to only be able to add 'foreignkey =
foreignkey' as it won't accept the AND portion with the as opposed
to =.
in Mytable.pm
---
__PACKAGE__-belongs_to(
myself,
Schema::Result::Mytable,
{ 'foreignkey' = 'foreignkey', 'date_added date_added' },
{
is_deferrable = 1,
join_type = LEFT,
}
);
I'm either missing something obvious or this can't be done (yet) and I
should just use SQL to make the request. I guess I just want to know
either way.
TIA,
--
STEVE
-
___
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: Join Myself?
On Fri, Jul 6, 2012 at 11:33 AM, Steve Wells we...@cedarnet.org wrote: I'm trying to convert this SQL statement to DBIC and I'm failing miserably. Let me clarify a bit… Given this setup: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group it is possible to convert the SQL statement: SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id m2.id) WHERE m2.id IS NULL; to DBIx::Class. I have read the manual / tutorials etc but it doesn't seem possible to add and extras (i.e. m1.id m2.id) to your join command. Is this true? ___ 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 rip...@woodlandschools.orgwrote: 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 rabbit+d...@rabbit.uswrote:
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 rabbit+d...@rabbit.uswrote:
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
