Mastering Perl for Bioinformatics [Electronic resources] نسخه متنی

6.1 One Perl, Many Databases

There comes a time when disk files or the simple DBM hash database
(that you've seen in previous chapters) just
won't manage the data of a medium- or large-size
project, and you must turn to relational databases. Although they
take quite a bit more effort to set up and to program, they offer a
standard and reliable way to store data and to ask questions about
it.

There are two things that make relational databases standard. For one
thing, they all follow a certain model of data structures, the
relational model. These data structures have become a
fixture in the computing world; they combine a level of constraint
and flexibility that has proved its usefulness in many areas,
including bioinformatics.

Almost all relational databases are programmed with a programming
language called the Structured Query Language, or SQL.
This is a fairly simple language that creates, populates, queries,
and manages the kind of data structures relational databases provide.
The combination of a standard data structure with a standard
programming language is another reason relational databases have
become so successful.

One thing that's not standard is the proliferation
of relational database companies and their penchant for doing things
their own way. This may sometimes be a marketing decision, but
it's more often the natural process of
evolutionof different sets of programmers having different
ideas and making different implementations.[1]

[1] When I
first released software that used Perl for bioinformatics, I received
a letter arguing that because C was available everywhere and Perl
wasn't, Perl for bioinformatics was therefore a Bad
Idea and I should use C instead. Of course, Perl is available
everywhere now, including on the VMS systems that my correspondent
was complaining about, and bioinformatics software is written in a
variety of languages. He made the classic mistake of wanting to
standardize a field long before it had settled down.

This is important when you have some working database application
that uses a particular DBMS such as Oracle, and you find that you
have to port the application to work on another DBMS such as MySQL.
Perhaps another database system has become significantly faster or
cheaper, or your computer is replaced with a new one that supports a
different database, or your computer center or CIO decrees that some
new DBMS is now the mandatory standard. If your database application
makes extensive use of a feature that is available only on your old
DBMS, you'll have a lot of work ahead of you
rewriting your software to make it work on the new DBMS.

Luckily, thanks to some expert Perl programming, there is a way to
get around this proliferation of different DBMS with their special
ways of doing things and their special extensions of SQL. In this
chapter, I'll use the Perl DBI (DataBase
Independent) module that provides a common interface to different
relational database systems; it makes it possible to write SQL that
will run on many different relational database systems with little or
no change.

Still, unless you are subject to a decree, the problem that the Perl
bioinformatics programmer faces at the beginning of a project is,
"Which relational database system should I
use?" It depends on the computer
you're on and what DBMS is already in use,
available, paid for, or known locally. There are very expensive
systems, and there are free ones: we'll take a quick
look at some of the alternatives and use one of the most popular free
ones for the following examples.

The beginning programmer should be aware that relational databases
are a large field of endeavor. Stop at any local bookstore with a
good computer book section and you'll see an
impressive number of books dedicated to relational databases and SQL
in general, and especially dedicated to working with specific
relational database management systems such as Oracle, SQL Server
(the database, not the language), Sybase, MySQL, etc. There are books
devoted to specific tools for designing a database, managing a
database system, and programming a database system. In the workplace,
there are job titles and positions for people who specialize in these
three areas, and more.

So, don't expect this one chapter to reveal all. Do
expect it to explain the basic concepts, give you the lay of the
land, and demonstrate a practical example you can use as a template
as you begin to develop your own code. At the very least, you will
want access to the documentation for the particular database system
you will use in your own work.

Having given the obligatory warning, I'll also add
that, unless you are tackling a fairly large project, relational
databases aren't all that difficult to use. If they
were, they wouldn't be so popular. You may well
spend a lot of your programming time in the future dealing with
databases, or you may just spend a little. If you expect it will be a
lot, I recommend you do some further reading.

Relational databases are an important topic. Because they have their
own software systems, language, and concepts, they are a bit of an
additional challenge. Most bioinformaticians need to know the basics
of how to use them; some specialize in them. This chapter will get
you started.