Hack 46. Store Images in a Database
Most database servers can handle large binary objects such as images. But you can also handle images using files.
You can deal with large data items in a database in a number of different ways. You can use BLOBs and store the data in the database, or store the data in files and store only a reference to the file in the database.
6.6.1. Use a BLOB
A BLOB is a binary large object. You can use a column such as this to store images, sounds, or other binary large objects. You can create a table with a BLOB column:
CREATE TABLE friends (id INTEGER PRIMARY KEY ,name VARCHAR(20) ,img BLOB )
MySQL and Oracle use the term BLOB. SQL Server allows the IMAGE data type; this is not restricted to image data, despite its name. The VARBINARY(max) type is available in SQL Server 2005. In PostgreSQL, you can use the OID data type.
To get data into a BLOB you can use an API such as the DBI interface in Perl:
#!/usr/bin/perl use strict; use DBI; my ($id,$img) = (shift,shift); die "Usage $0 id img" if ! $img; my $dbh=DBI->connect('DBI:mysql:dbname','scott','tiger') or die 'Connect failed'; my $sth = $dbh->prepare("INSERT INTO friends(id,img) VALUES (?,?)"); open A, $img; my $bin; read A, $bin, -s A; $sth->execute($id,$bin);
The $img variable is a filename; Perl opens the file and copies the contents into the $bin variable. The read instruction on the second-to-last line tells the system to read in s A bytes, where s is Perl syntax for the file size.
In MySQL, you can also use LOAD_FILE('/path/to/file.png') in an INSERT statement, for example. In PostgreSQL, you can use the large object import function, lo_import('/path/to/file.png').
Getting the data in is only half the problem. You also need to get the data out. Here is how to retrieve an image and display it from a database. The Perl script shown here will allow a user to navigate to images in friends:
#!/usr/bin/perl
use strict;
use DBI;
use CGI qw(:standard);
import_names;
if ($Q::id){
print header( );
print "Friend number $Q::id
";
print "
Showing a page such as friends.pl?id=1, shown in Figure 6-9, involves two HTTP calls. The page generated looks like this:
Friend number 1
Figure 6-9. The friends.pl?id=1 web page
The img element is a reference to the same script, but with the img CGI parameter set.
The browser will read this and generate a second HTTP call to the same location, but this time the CGI variable img will be set. The second call results in a database lookup. The binary object is retrieved and sent to the browser, preceded by the appropriate MIME header.
6.6.2. Use a File for Your Image
In contrast to storing images in a database, you can store the images in a separate directory and stick to the convention that the filename is the primary key of the nonimage data plus the .jpg extension. Your code to display the page is simpler and you are not using the database or your application to pump binary large objects around.
Managing a large number of images using the filesystem can be perplexing if the name of the file has to be simply a primary key. You can be kinder to your users and allow them a more flexible filenaming convention.
For example, they can call the images anything they like, as long as the primary key is included in a consistent manner, as shown in Figure 6-10.
Figure 6-10. The filename ending with the primary key
The full filename of the file is irrelevant to the web script. It uses only the last few characters of the name. You can write a script to copy these files from the original directory into another location suitable for the web server:
#!/usr/bin/perl my @fl = glob("original/*"); foreach (@fl){ if (/(d)+.jpg/){ my $originalName = $_; my $primaryKey = $1; my $sh = "cat '$originalName'|jpegtopmn|". "pnmscale --height 40|pnmtojpeg > ". "small/$primaryKey.jpg"; print "$sh "; } else { print "The file $_ does not follow the agreed naming convention. "; } }
The Perl script shown loops over every file in the originals directory.
For each file with the correct format, it scales the image down to 40 pixels high and copies it to the small directory. The new filename is simpler; it is the primary key followed by the .jpg suffix. With this simpler name, you can conveniently reference images from the HTML pages.
The pnm library used here contains many options for changing images; you can convert formats, crop, and scale.