CGI::UploadDB - Manage CGI uploads using SQL database
# Define your upload form field names as hash keys along with # any thumbnails they might need, and their max width and heights.
my %Uploads = (
img_1 => {
thumbs => [
{ name => 'img_1_thumb_1', w => 100, h => 100 },
{ name => 'img_1_thumb_2', w => 50 , h => 50 },
],
},
# A simple syntax if you just want some thumbnails
img_2 => [
{ name => 'img_2_thumb_1', w => 100, h => 100 },
],
# And a very simple syntax if you want to store the file untouched. img_3 => [], );
my $udb = CGI::UploadDB->new( spec => \%Uploads,
updir_url => 'http://localhost/uploads', updir_path => '/home/user/www/uploads',
dbh => $dbh, query => $q, # defaults to CGI->new(), );
# ... now do something with $udb
updir_url and updir_path should not include a trailing slash.
This module is designed to help with the task of managing files uploaded through a CGI application. The files are stored on the file system, and the file attributes stored in a SQL database.
It expects that you have a SQL table dedicated to describing uploads, designed like this:
-- Note the MySQL specific syntax here create table uploads ( upload_id int AUTO_INCREMENT primary key not null, mime_type character varying(64), extension character varying(8), -- file extension width integer, height integer )
For Postgres, a sequence is also required. This can be named
in the constructor, or the default of upload_id_seq will be used.
Sample SQL scripts are included in the distribution to create such tables.
Other table names are allowed, but at least these fields must be present in the table.
The expectation is that these file uploads will be related to at least one other entity in the database. Tables which reference the uploads table can do so with any column name that ends in '_id'. Column definitions to store photos with an addressbook might look like this:
CREATE TABLE address_book (
friend_id int primary key,
name varchar(64),
photo_id int,
photo_thumbnail_id int
);
To create the object, provide a specification the tells what field names are for files you want to manage, and the details any thumbnails that will be created for these files (if they are images). Here the file names are given without the "_id" part:
my %Uploads = {
img_1 => [
# The first image has 2 different sized thumbnails
# that need to be created.
{ name => 'img_1_thumb_1', w => 100, h => 100 },
{ name => 'img_1_thumb_2', w => 50 , h => 50 },
],
img_2 => [
{ name => 'img_2_thumb_1', w => 100, h => 100 },
],
img_3 => [],
};
The new() constructor accepts the attributes described here:
my $udb = CGI::UploadDB->new( spec => \%Uploads,
updir_url => 'http://localhost/uploads', updir_path => '/home/user/www/uploads',
dbh => $dbh, query => $q, # defaults to CGI->new(),
up_table => 'uploads', # defaults to "uploads" up_seq => 'upload_id_seq', # Required for Postgres );
spec
updir_url
updir_path
dbh
query
param and upload functions.
Defaults to CGI->new() if omitted.
up_table
up_seq
upload_id_seq if omitted.
my $entity = $udb->install_uploads($results);
Installs uploaded files based on the definition given in
spec.
Specifically, it does the following:
o
o
o
As input, a Data::FormValidator::Results object is expected. Furthermore, the Data::FormValidator::Constraints::Upload module is expected to have been used to the generate meta data that will be used.
The expection is that you are validating some entity which has some files attached to it.
It returns a hash reference of the valid data with some transformations. File upload fields will be removed from the hash, and corresponding "_id" fields will be added.
So for a file upload field named 'img_field', the 'img_field' key will be removed from the hash and 'img_field_id' will be added, with the appropriate upload ID as the value.
my @deleted_field_ids = $udb->delete_checked_uploads;
This method deletes all uploads and any associated thumbnails based on form input. File system files as well as database rows are removed.
It looks through all the field names defined in spec. For an upload named
img_1, a field named img_1_delete is checked to see if it has a true
value.
A list of the field names is returned, prepended with '_id', such as:
img_1_id
The expectation is that you have colums with this name defined in another table, and by deleting these field names from the $valid hash, they will be set to NULL when that database table is updated.
# Provide the file upload field name my $field_name = $udb->delete_upload(name => 'img_1');
# Or the upload_id my $field_name = $udb->delete_upload(upload_id => 14 );
This method is used to delete a row in the uploads table and file system file associated
with a single upload. Usually it's more convenient to use delete_checked_uploads
than to call this method.
There are two ways to call it. The first to provide the name of the file upload field used:
my $field_name = $udb->delete_upload(name => 'img_1');
Here, it expects tofind a query field name with the same prefix and '_id' appended (ie: img_1_id). The id field should contain the upload_id to delete.
As an alternate interface, you can provide the upload_id directly:
my $field_name = $udb->delete_upload(upload_id => 14 );
The method returns the field name deleted, with "_id" included.
my $tmpl_vars_ref = $udb->create_tmpl_vars($table,\%where,@prefixes);
This method is used to return a hash reference suitable for sending to HTML::Template. Here's an example:
my $tmpl_vars_ref = $udb->create_tmpl_vars('news',{ item_id => 23 },qw/file_1/);
This is going to fetch the file information from the upload table for using the row where news.item_id = 23 AND news.file_1_id = uploads.upload_id. The result might look like this:
{
file_1_id => 523,
file_1_url => 'http://localhost/images/uploads/523.pdf',
}
If the files happen to be images and have their width and height defined in the database row, template variables will be made for these as well.
The %where hash mentioned here is a SQL::Abstract where clause. The
complete SQL that used to fetch the data will be built like this:
SELECT upload_id as id,width,height,extension
FROM uploads, $table where (upload_id = ${prefix}_id and (%where_clause_expanded here));
Returns an array of all the upload names, including any thumbnails.
Mark Stosberg <mark@summersault.com>
This program is free software; you can redistribute it and/or modify it under the terms as perl itself.