Thanks for your reply... comments below...
On 16 Dec 2008, at 00:31, Toby Corkindale wrote:
Adam Witney wrote:
I am new to DBIx::Class, but have so far been impressed with how
easy it is to use, so thanks to those that have developed and
continue to develop it.
I wanted to know, is there a way of adding a method to a class
that performs a separate database lookup. I have used the
Cookbook example for "Arbitrary SQL through a custom
ResultSource", but this returns a query that is plugged into a
sub-select. Can i access the database connection handle at all
(eg $dbh) to build my own SQL query?
thanks for any help
adam
You can, but before I tell you how I'm going to ask what kind of
query you want. Give us SQL and we'll tell you if you can do it
without going via the raw DBH.
ok i have several places i think I might need this!
I have two tables containing data that originated from files; one
table contains the filename, format etc and the other table
contains the data itself. The data table contains about 100 million
rows now and the data needs only be retrieved in chunks (about
5k-10k rows) corresponding to the whole file (one row of first
table), although the required columns may very between queries. I
was thinking that creating a class and hence an object per row
would make this slower, so i thought i could write a method that
pulls back the whole data in one go for the main class/object.
Hmm, just out of interest, did you consider using the pg blob format
for the file data instead? (Which lets you work on it using read/
write/seek semantics)
Or has that fallen out of fashion these days?
I think it all depends on how you want to access the data, I do use
blobs to store images in other parts of the database. In this case
though, the data is from tab delimited files and i need access to the
data by row in other applications.
If you're working with frames of a movie, or rows of a CSV file, or
other such record-based data then I totally understand you would
want to store them all in individual rows instead though.
If I understand your query, you want to do something like:
SELECT filedata.foo, filedata.bar
FROM filedata JOIN metadata ON filedata.metadata = metadata.id
WHERE metadata.filename = 'myFile.bin'
However you expect that to return 7k5 (+/- 2k5) rows and you'd like
to retrieve it in chunks?
Yes that describes the setup, but I would like to retrieve all rows
for each 'file'... sometimes i will be retrieving data for 20 files
though, so i need it in chunks that correspond to the size of the
originating file.
You could do it with pages, like this?
while(
my @items = $schema->resultset('Filedata')->search(
{
'metadata.filename' => $filename,
},
{
join => 'metadata',
rows => 1000,
page => $page++
}
)
) {
do_stuff_with(@items)
}
But you're correct that it would be creating an object for every row.
However DBIC should be pulling down all 1000 of them from the DB in
a single query.
The second place i might need this is that i have some data in a
second database and so i would have to interact with this using
dblink (I am using PostgreSQL). This seems easier to just write the
query in a method?
Doesn't Pg just represent the foreign DBI-Linked database as another
schema? So I think you could just write the extra classes into dbic
and it should query them, without realising their foreign?
.. I haven't tried it myself though.
The problem is that with dblink non-superusers have to provide a
username/password in the dblink query, can you pass these kind of
parameters into the class/object?
eg
SELECT * from dblink('dbname= mydb username=user password=mypass',
'SELECT id FROM mytable') AS t1(id int);
I can wrap this in a function, but can i build a class on top of a
table function with DBIx::Class?
thanks
adam
_______________________________________________
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]
