[Dbix-class] how to add a method that performs a database lookup?

[Adam Witney]

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