Class::DBI
I'm trying to understand Class::DBI I'm reading the perldoc and TOny Bowden's article on perl.com The problem I have is that I can't see how to build up more complicated queries such as give me the first one hundred book titles Now I am using MySQL which I don't think can limit the number of records returned... but I do have a title number so I could try to do something like give me all the book titles where the titlenumber is 400 and the titlenumber 500 Unfortunately I can't see any way of doing this. Anybody know if I have missed something? I know I can use iterators but the perldoc say that Class::DBI iterators still suck up all the data into memory - which isn't really much use either. Alex Mc Available for java/perl/C++/web development in London, UK or nearby. Apache FOP, Cocoon, Turbine, Struts,XSL:FO, XML, Tomcat, JSP http://www.OWAL.co.uk/
Re: Class::DBI
On Sat, 28 Jun 2003, Alex McLintock wrote: The problem I have is that I can't see how to build up more complicated queries such as give me the first one hundred book titles Now I am using MySQL which I don't think can limit the number of records returned... but I do have a title number so I could try to do something like Yes it can. From the manual: SELECT * FROM table LIMIT 100; # Retrieve first 100 rows or SELECT * FROM table LIMIT 5,10 # Rows 6-15 The syntax is LIMIT offset, max_rows_to_return The MySQL manual is very good. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ Sign up now
Re: Class::DBI
On Sat, Jun 28, 2003 at 11:11:43AM +0100, Alex McLintock wrote: I'm trying to understand Class::DBI I'm reading the perldoc and TOny Bowden's article on perl.com The problem I have is that I can't see how to build up more complicated queries such as give me the first one hundred book titles There's the Cretrieve_from_sql method my @books = Book-retrieve_from_sql(id $lastid ORDER BY id LIMIT 100); Or, documented really nearby to that, Cadd_constructor __PACKAGE__-add_constructor( top100 = id ? ORDER BY id LIMIT 100 ); my @books = Book-top100(0); Of course ordering by id probably isn't correct, but I'm sure you know which column you really want to order on -- Richard Clamp [EMAIL PROTECTED]
Re: Class::DBI::Join
On Mon, Feb 17, 2003 at 12:37:17PM +, Kate L Pugh wrote: I sent this question to the Class::DBI list last week, but didn't get any replies so I thought I'd ask here too. Can anyone help me out? I think the problem is that Class::DBI::Join is a Schwern-ism, that no-one else knows much about, and he frequently disappears for months at a time ... My guess is that you don't actually want to be using Class::DBI::Join, but using a combination of the has_many-with-optional-mapping-method functionality of Class::DBI itself, with the has_many taking search arguments. Tony
Class::DBI::Join
I sent this question to the Class::DBI list last week, but didn't get
any replies so I thought I'd ask here too. Can anyone help me out?
Kake
- Forwarded message from Kate L Pugh [EMAIL PROTECTED] -
Hello. I only started using Class::DBI last week so please don't
laugh if this is a stupid question.
I'm using Class::DBI::Join to relate dealers (of the art kind) to
categories. So I've got a (many-to-many) table that relates dealer
IDs and category IDs, and a Categorisation class that subclasses
Class::DBI::Join so I can put dealers in categories and get them out
again. The thing I need to add is support for the 'importance' column
in that table - some dealers are major dealers in a category, others
only occasionally stock items from that category. Now as it stands I
can do:
$category-add_dealer( dealer = $minor_dealer, importance = 2 );
and the information goes in the table, and I can get all the dealers in
a category with:
@dealers = Categorisation-join( $category );
but I want to be able to do:
@dealers = Categorisation-join( $category, importance = 1 );
to get just the major dealers, and I can't work out what the
recommended way to do this is. I made a patch to Class::DBI::Join
(attached with tests) to make the line above Just Work, but I'm sure
I've missed something that makes this a bad idea.
Help?
Kake
diff -pubr Class-DBI-Join-0.03/lib/Class/DBI/Join.pm
class-dbi-join/lib/Class/DBI/Join.pm
--- Class-DBI-Join-0.03/lib/Class/DBI/Join.pm 2002-04-17 16:10:16.0 +0100
+++ class-dbi-join/lib/Class/DBI/Join.pm2003-02-12 12:27:24.0 +
@@ -24,7 +24,8 @@ Class::DBI::Join - many-to-many relation
# Given a join table like:
# CREATE TABLE films_and_actors (
# film_id INTEGER REFERENCES films,
- # actor_idINTEGER REFERENCES actors
+ # actor_idINTEGER REFERENCES actors,
+ # as_name VARCHAR(80)
# );
# And assuming Film and Actor are Class::DBI subclasses.
Roles-table('films_and_actors');
@@ -40,6 +41,7 @@ Class::DBI::Join - many-to-many relation
my $btaste = Film-retrieve('Bad Taste');
my @roles = Roles-join($btaste);
+ my @alien_roles = Roles-join($btaste, as_name = '3rd Class Alien');
=head1 DESCRIPTION
@@ -57,8 +59,17 @@ Class::DBI::Join adds the following meth
=item Ijoin
+ # Find all roles in the film Bad Taste.
my @roles = Roles-join($btaste);
+ # Or just those in which the actors are playing aliens.
+ my @alien_roles = Roles-join($btaste, as_name = '3rd Class Alien');
+
+The first argument to Cjoin should be the object whose relations you
+want to find. Any other arguments will be passed straight through to
+the Csearch method of LClass::DBI, so you can provide information
+here to construct additional WHERE clauses.
+
=cut
__PACKAGE__-set_sql('ManyToMany', SQL);
@@ -68,10 +79,10 @@ WHERE %s = ?
SQL
sub join {
-my($class, $one) = @_;
+my($class, $one, @args) = @_;
my $hasa_cols= __hasa_cols($class);
-return $class-search( $hasa_cols-{ref $one} = $one-id );
+return $class-search( $hasa_cols-{ref $one} = $one-id, @args );
}
diff -pubr Class-DBI-Join-0.03/t/Join.t class-dbi-join/t/Join.t
--- Class-DBI-Join-0.03/t/Join.t2002-04-16 21:09:03.0 +0100
+++ class-dbi-join/t/Join.t 2003-02-12 12:20:39.0 +
@@ -1,6 +1,6 @@
#!/usr/bin/perl -w
-use Test::More tests = 11;
+use Test::More tests = 13;
require_ok('Class::DBI::Join');
use lib qw(t/lib/);
@@ -60,6 +60,12 @@ my $btaste = Film-retrieve('Bad Taste')
my @bt_roles = Roles-join($btaste);
is( @bt_roles, 6 );
+my @aliens = Roles-join($btaste, as_name = '3rd Class Alien');
+is( @aliens, 2, 'join works with extra where clause' );
+my @alien_actors = sort map { $_-actor-name } @aliens;
+is_deeply( \@alien_actors, ['Craig Smith (I)', 'Terry Potter'],
+ '...returns the right things too' );
+
my $pj = Actor-retrieve('Peter Jackson');
my @pj_roles = Roles-join($pj);
is( @pj_roles, 4 );
- End forwarded message -
Class::DBI ponderings
We're currently considering shifting our core mod-perl system here at work to use Class::DBI, since many of the classes we have already are pretty much reinventing its wheel, and we currently have the time and space to make a few structural changes before the next development onslaught begins. However, I've run up against an interesting little problem. Currently, our database (mysql) has two users for cgi access - one called nobody, which has read-access only to the database, and one called, well, never you mind, which has full access. Scripts that are just dealing with front-end presentation use the nobody user, whilst the CMS side of the system uses the full-access user. The problem with Class::DBI is that its connections persist across classes, and since we want to do as much caching of DB connections etc as possible, the scheme of having two database users gets a bit tricky. So, two major questions: 1) The old nobody/full-access duality is an old piece of history, and rose out of the general principle that it's best to only have a full-access user connected when necessary. However, it came about when there was very little caching in the system, and has now become a pain where once it wasn't. What do other people do? Just connect using a user with full privileges, regardless of the script's task? I can't see huge security disadvantages in this, particularly as such users are locked down to only take connections from localhost. Still, opinions would be nice. 2) If people do connect with different users dependent on task, how do you keep your cached DB handles straight? I'd thought of subclassing admin-related tasks into separate packages, so that they always get an Object::Foo::Admin-created DB handle, but this seems unwieldy in the extreme. Unless anyone can convince me that it's a bad idea, I think having a single full access user is going to be the most attractive solution here, but opinions welcome.
Re: Class::DBI ponderings
On Fri, 2003-02-07 at 17:34, Simon Batistoni wrote: We're currently considering shifting our core mod-perl system here at work to use Class::DBI, since many of the classes we have already are pretty much reinventing its wheel, and we currently have the time and space to make a few structural changes before the next development onslaught begins. However, I've run up against an interesting little problem. Currently, our database (mysql) has two users for cgi access - one called nobody, which has read-access only to the database, and one called, well, never you mind, which has full access. Scripts that are just dealing with front-end presentation use the nobody user, whilst the CMS side of the system uses the full-access user. The problem with Class::DBI is that its connections persist across classes, and since we want to do as much caching of DB connections etc as possible, the scheme of having two database users gets a bit tricky. So, two major questions: 1) The old nobody/full-access duality is an old piece of history, and rose out of the general principle that it's best to only have a full-access user connected when necessary. However, it came about when there was very little caching in the system, and has now become a pain where once it wasn't. What do other people do? Just connect using a user with full privileges, regardless of the script's task? I can't see huge security disadvantages in this, particularly as such users are locked down to only take connections from localhost. Still, opinions would be nice. 2) If people do connect with different users dependent on task, how do you keep your cached DB handles straight? I'd thought of subclassing admin-related tasks into separate packages, so that they always get an Object::Foo::Admin-created DB handle, but this seems unwieldy in the extreme. Unless anyone can convince me that it's a bad idea, I think having a single full access user is going to be the most attractive solution here, but opinions welcome. I tend to use a single user with read/write access and do the security at the application level. They still don't have full access, just enough to do what they need to do. Assuming you have subclassed Class::DBI and then subclassed again for each of your objects (the recommended way) then you can overload db_Main to return the right handle. Basically db_Main is called every time Class::DBI needs a database connection. I overload it so that I can establish the connection dynamically at run time from a config file but there is no reason why you can't have it look at some variable (e.g. the users id or the attempted action) and return a read only or read/write connection. HTH, Simon.
Re: Class::DBI ponderings
On Fri, Feb 07, 2003 at 05:34:39PM +, Simon Batistoni wrote:
What do other people do? Just connect using a user with full
privileges, regardless of the script's task? I can't see huge security
disadvantages in this, particularly as such users are locked down to
only take connections from localhost. Still, opinions would be nice.
This is what we generally do ... but if you want to keep the two users:
2) If people do connect with different users dependent on task, how do
you keep your cached DB handles straight? I'd thought of subclassing
admin-related tasks into separate packages, so that they always get an
Object::Foo::Admin-created DB handle, but this seems unwieldy in the
extreme.
In your main Class::DBI subclass, which will presumably be the
superclass for each of your 'table' classes, you can override the
db_Main method.
Unfortunately, because of the way Ima::DBI just throws this method into
your namespace, you can't do this directly.
You can create another level of subclassing so that you can properly
override the method:
in file My/DBI.pm
package My::DBI::Base;
use base 'Class::DBI';
__PACKAGE__-set_db();
package My::DBI;
use base My::DBI::Base;
sub db_Main {
my $class = shift;
if ($class-different_privs) {
return Ima::DBI-connect_cached('other connect string');
}
return $class-SUPER::db_Main;
}
1;
Or, you can grab a copy of the method after it's been created, and mess
about with it:
in file My/DBI.pm
package My::DBI;
use base 'Class::DBI';
__PACKAGE__-set_db();
{
no warnings 'redefine';
*db_Orig = \db_Main;
*db_Mail = sub {
my $class = shift;
if ($class-different_privs) {
return Ima::DBI-connect_cached('other connect
string');
}
$class-db_Orig;
}
}
It's kinda tricky, and should really be a lot nicer, but it's fairly
flexible (we've used it to have lots of different databases with the
same schema, and the correct one gets picked depending on certain
circumstances (what user you're logged in as, which vhost you're using,
what the path name is, whatever)...
If you've any more questions, the Class::DBI mailing list has a few
people who've done things like this.
Thanks,
Tony
Re: Class::DBI ponderings
On Fri, Feb 07, 2003 at 08:00:07PM +, Shevek wrote: On reading the code, this is sufficient. Don't call set_db at all. This is pretty close to the architecture I used to use: Each class was responsible for providing an appropriate DB handle on demand. If you don't call set_db, and just use connect_cached, then much of what followed in Tony's mail may be simplified. yes, this is indeed true. I hadn't actually thought of that. I need to put together a good write-up on all the different approaches to this. Tony
Re: Class::DBI trickery
On Tue, Nov 12, 2002 at 07:09:28PM +, Michael Styer wrote:
I've just been introduced to Class::DBI. While I like it lots in general,
it seems like it's missing one obvious featur
So what I wanted to be able to do is this:
my $survey = Survey-retrieve($survey_id);
$survey-add_question(\%question_data);
where the 'add_question' method is created automagically
Very interesting approach...
rather than having to do this:
my $survey = Survey-retrieve($survey_id);
my $question = Survey::Question-create(\%question_data);
$question-survey($survey);
Technically, if you were doing it this way, you should probably do:
my $survey = Survey-retrieve($survey_id);
my $qn = Survey::Question-create({ %question_data, survey = $survey });
as some databases might complain that you're violating the schema at the
initial create (as your foreign key is null until the next statement).
But I definitely like the first approach.
I don't like this because I have to hard-code the name of the child
class somewhere else besides the has_many initialization statement,
and that feels messy. It also just feels backwards.
I'd be interested as to why you think it feels backwards...
I've overridden 'has_many' with a version that does what I want, but my
question is, am I missing a trick? Is there a standard idiom for doing
things more or less the way I wanted to within the existing Class::DBI
framework?
I don't think so. Would you mind submitting a patch?
Thanks,
Tony
Re: Class::DBI trickery
On Wed, 13 Nov 2002, Tony Bowden wrote: I don't like this because I have to hard-code the name of the child class somewhere else besides the has_many initialization statement, and that feels messy. It also just feels backwards. I'd be interested as to why you think it feels backwards... Well, if I have a parent object which has a collection of child objects of some class (or collections of objects of different classes), it seems like the parent should know what it has to do to add a child to (one of) its collection(s), as long as you give it the right data for the new child object. As it is, the parent can retrive objects it contains but can't add more, so the programmer has to know what class the child objects are in order to add more of them. I've overridden 'has_many' with a version that does what I want, but my question is, am I missing a trick? Is there a standard idiom for doing things more or less the way I wanted to within the existing Class::DBI framework? I don't think so. Would you mind submitting a patch? Sure. I'll send it off-list unless anyone else is interested. While we're on the subject of Class::DBI, I'm getting deep recursion in Class::DBI::DESTROY. It's the call to $self-id at line 501 that seems to set it off, but if I take out my triggers it works. Probably something I'm doing wrong and I've got the perl debugger on the task, but if anyone has any suggestions about where to look I'd love to hear them. Thanks. michael
Re: Class::DBI trickery
On Wed, Nov 13, 2002 at 12:36:54PM +, Michael Styer wrote: I'd be interested as to why you think it feels backwards... Well, if I have a parent object which has a collection of child objects of some class (or collections of objects of different classes), it seems like the parent should know what it has to do to add a child to (one of) its collection(s), as long as you give it the right data for the new child object. As it is, the parent can retrive objects it contains but can't add more, so the programmer has to know what class the child objects are in order to add more of them. Gotcha. That makes perfect sense! :) I don't think so. Would you mind submitting a patch? Sure. I'll send it off-list unless anyone else is interested. Great. While we're on the subject of Class::DBI, I'm getting deep recursion in Class::DBI::DESTROY. It's the call to $self-id at line 501 that seems to set it off, but if I take out my triggers it works. Probably something I'm doing wrong and I've got the perl debugger on the task, but if anyone has any suggestions about where to look I'd love to hear them. First suggestion is to update to 0.90_05 (available from www.class-dbi.com), which in the next day or two is going to become 0.90. It gives better error messages for some deep recursion cases that might help you track the problem better... If that doesn't help, then feel free to send me your code off-list (or to the CDBI mailing list), and I'll see if I can spot anything. Thanks, Tony
Class::DBI trickery
I've just been introduced to Class::DBI. While I like it lots in general,
it seems like it's missing one obvious feature, but being new to the
module I think it's more likely I'm missing the point than Class::DBI is
missing a feature.
Here's the situation.
I set up two classes, like so:
package Survey;
use base 'My::DBI';
use strict;
__PACKAGE__-table('surveys');
__PACKAGE__-columns(Primary = 'survey_id');
__PACKAGE__-columns(All = qw(title text));
__PACKAGE__-has_many('questions', Survey::Question = 'survey_id');
package Survey::Question;
use base 'My::DBI';
use strict;
__PACKAGE__-table('questions');
__PACKAGE__-columns(Primary = 'question_id');
__PACKAGE__-columns(All = qw(title text));
(the My::DBI class has all the db connection details in it and is a
subclass of Class::DBI, so I don't have to keep that in the
sub-classes. also, there's a class for answers as well but let's
leave that out for clarity.)
So what I wanted to be able to do is this:
my $survey = Survey-retrieve($survey_id);
## get question data from somewhere
$survey-add_question(\%question_data);
where the 'add_question' method is created automagically in the same way
the 'questions' method is created by Class::DBI, rather than having to do
this:
my $survey = Survey-retrieve($survey_id);
## get question data
my $question = Survey::Question-create(\%question_data);
$question-survey($survey);
which is, as far as I can tell, the only way to add a child object to a
parent object. (child being the 'hasa' end of the relationship and parent
being the 'has_many' end.) I don't like this because I have to hard-code
the name of the child class somewhere else besides the has_many
initialization statement, and that feels messy. It also just feels
backwards.
I've overridden 'has_many' with a version that does what I want, but my
question is, am I missing a trick? Is there a standard idiom for doing
things more or less the way I wanted to within the existing Class::DBI
framework?
michael
