[Dbix-class] Write only + No-read/retrieve on a column

[Peter Edwards]

AP
>> What do you think? Two classes for the table? One for admin functions
>> which has the LONGBLOB in it and one for the web app (Catalyst) which
>> doesn't?
>>
>> Is there a better trick for this kind of thing?
MS
>Typical SQL "best practice" is to use two tables ... the meta-data
>table (or main table) and then another table that contains only the
>data that you don't want to be loaded/cached so much (the blob).
>
>If you do that, then you could simply create two classes, with a
>has_one relationship.  If you're going to be using the blob you can
>use prefetch on the initial find/search, and if not, then it doesn't
>get loaded.

Keep any blob fields in separate tables to metadata, the database
performance will be much faster searching and reading the metadata, or
adding new fields.

I'd think twice about mastering your images in the database.
It depends on data volumes and number of simultaneous accesses, but the
easiest is to keep them in a directory hierarchy under Unix with a normal
backup to protect them, running an overnight resynchronization job to check
that the database index matches the file system.
It's faster to have a spread of files across directories, so take an MD5 of
the record id and split on every 3 characters to give the directory path:
my $imageid = 12345;
my $fullid = sprintf("%012i", $imageid);
my $md5 = md5_hex($fullid);
my @dirs = ($md5 =~ /^(...)(...)(...)/);
my $file = Path::Class::File->new(@dirs, $fullid);
(thanks to mst for that idea)

Add an accessor to your image db model to read in the file when you need the
binary data.
It also means other apps can get at the files directly without having to go
through your app. 

Regards, Peter
http://perl.dragonstaff.co.uk/