Managing files is a breeze with this DBIx::Class plugin

Jun 30, 2014 by David Farrell

Managing application file uploads is challenging: storage, de-duplication, retrieval and permissions all need to be handled. DBIx::Class::InflateColumn::FS simplifies the challenge by handling the backend storage of files so the programmer can focus on application development. Let’s take a closer look at how it works.

Requirements

To use this example, you’ll need to install DBIx::Class::InflateColumn::FS from CPAN. The CPAN Testers results show that it should run on all platforms, including Windows. You’ll also need DBIx::Class::Schema::Loader and File::MimeInfo if you don’t already have them and SQLite3. To install the Perl modules, open the terminal and enter:

$ cpan DBIx::Class::InflateColumn::FS DBIx::Class::Schema::Loader File::MimeInfo

Setup the result class

Let’s create an example class for handling file uploads. DBIx::Class maps objects to database tables, so we need to create a database table that represents our file upload object. This is the SQL code for creating the upload table:

create table upload (
    id          integer     primary key,
    file        text        not null,
    mime        text        not null
);

Save the code into a script called create_upload.sql and run it at the command line:

$ sqlite3 MyApp.db < create_upload.sql

This will create the upload table. Next we can use the “dbicdump” app that comes with DBIx::Class::Schema::Loader to create the basic result class for us:

$ dbicdump MyApp::Schema dbi:SQLite:MyApp.db

Open up the newly-created MyApp/Schema/Result/Upload.pm in a text editor and add the following code, below the line beginning “# DO NOT MODIFY …”:

use File::MimeInfo 'extensions';

__PACKAGE__->load_components("InflateColumn::FS");
__PACKAGE__->add_columns(
    "file",
    {   
        data_type      => 'TEXT',
        is_fs_column   => 1,
        fs_column_path => 'uploads',
    }   
);

sub extension { 
    my ($self) = @_;
    [ extensions($self->mime) ]->[0];
}

This code enables the DBIx::Class::InflateColumn::FS plugin on the “file” attribute of our Upload class. Additionally we’ve added a subroutine called “extension” that will return the file extension for the file.

Create an upload

This script will create an upload object:

#!/usr/bin/env perl
use strict;
use warnings;
use MyApp::Schema;
use MIME::Types;
use lib '.';

open(my $file, '<', $ARGV[0]) or die $!; 

my $schema = MyApp::Schema->connect('dbi:SQLite:MyApp.db');

# Add the file to the database and file system
my $upload = $schema->resultset('Upload')->
        create({ file => $file,
                 mime => (MIME::Types->new->mimeTypeOf($ARGV[0])) });

Saving the script as “create_upload.pl” we can call it at the terminal, passing the filepath to the file we want to save:

$ perl create_upload.pl perltricks_logo.png

Just by creating the object, DBIx::Class::InflateColumn::FS will save the file in our uploads directory. No need to write extra code that explicitly copies the file.

Retrieve an upload

This script will retrieve the upload object. DBIx::Class::InflateColumn::FS automatically inflates the “file” column to be a Path::Class::File object, which gives us many convenience methods:

#!/usr/bin/env perl
use strict;
use warnings;
use MyApp::Schema;
use lib '.';

my $schema = MyApp::Schema->connect('dbi:SQLite:MyApp.db');

# retrieve the upload
my $upload = $schema->resultset('Upload')->find(1);

# get the relative path
$upload->file->relative;

# get the absolute path
$upload->file->absolute;

# get the base filename
$upload->file->basename;

# get the mime type (image/png)
$upload->mime;

# get the file extension
$upload->extension;

# get a read filehandle
$upload->file->openr;

# get a write filehandle
$upload->file->openw;

# get an append filehandle
$upload->file->opena;

Delete an upload

DBIx::Class::InflateColumn::FS makes it super-simple to delete files. Simply call delete on the result object to delete it from the table and the file system:

#!/usr/bin/env perl
use strict;
use warnings;
use MyApp::Schema;
use lib '.';

my $schema = MyApp::Schema->connect('dbi:SQLite:MyApp.db');

# retrieve the upload
my $upload = $schema->resultset('Upload')->find(1);

# delete the file from the database and file system
$upload->delete;

Conclusion

DBIx::Class::InflateColumn::FS is useful as-is, but it shines in certain situations. For example if you’re managing image files, it really pays to store the original high-quality image, and dynamically re-size the image when requested. This way you minimize disk use and retain the flexibility in the application logic to adjust the images as required.

Thanks to Devin Austin whose Catalyst advent calendar article was a useful source for this article.

Enjoyed this article? Help us out and tweet about it!

Cover image © Cas

This article was originally posted on PerlTricks.com.