Chapter 9. Doing the Database Thing

Databases are the meat-and-potatoes—or perhaps the stomach—of any application. They are by no means sexy or glamorous, but they are essential.

Everything and anything that is organized at all uses some form of a database: for example, files and directories in a file system.

Even the most mundane chores—tracking users and their preferences at a Web site, for instance—require the use of a database. (For information on using Linux and the Apache Web server, see Chapter 14.)

When you think about a database on a server operating system like Linux, you should understand that the database is, itself, a client and server affair. For the database to work, the two pieces must be communicating. This leads to some interesting issues.

First, the database server pretty much feels like an operating system: It needs to control users, access, and processes, and it typically provides a prompt of its own.

Next, you need to consider the interaction between your clients and the server. Generally, a database server provides a client program for administrative purposes or to permit ad-hoc queries against databases. The client program connects to an instance of the database server and allows you to list the tables it contains. More important, it allows you to issue the SQL statements that create and populate relational databases and to run ad-hoc queries against the data they contain. The PostgreSQL Interactive SQL Monitor (explained later in this chapter) is an example of this kind of utility, as is Informix's DB-Access.

But outside users—for example, those running queries against the database via a program activated by submitting a Web form—will need a custom interface created using a programming language spoken by the database server. This is a job for an experienced programmer. The language the programmer employs depends on a variety of factors, but the database server must be able to supply a client connection in that language. Common choices include C, C++, and in the Web space, Perl and Java.

While on the topic of languages, what language does a database server—also called a DBMS—speak? SQL, or Structured Query Language, is the common lingua franca of relational databases. Although there are slight variations in this language from DBMS to DBMS, most standard SQL statements will work with any database that bills itself as SQL compliant.

SQL itself is divided conceptually into three components:

• Data Definition Language—DDL—which is responsible for creating tables and relationships

• Data Manipulation Language—DML—which performs operations on the data contained in the database entities

• Data Control Language—DCL—which is responsible for transactions and for controlling users and privileges

The Red Hat Linux 6 VQS CD includes a powerful database server, PostgreSQL, that runs well on Linux. PostgreSQL was probably installed on your system when you installed Red Hat Linux and Gnome. It is free for you to use (although not under the Gnu license, as I'll explain later).

This chapter explains how to configure and run PostgreSQL.

I'll also provide an overview of how to download and install Informix Dynamic Server (IDS), Linux Edition. IDS is commercial software with a 30-day free trial. If you need the very best in an enterprise database server for Linux, Informix Dynamic Server may be for you.