PostgreSQL—the Linux under the Databases

A close look at the PostgreSQL database, including programming interfaces and using it for WWW applications.
Programming Interfaces

One advantage of the long history of PostgreSQL is the availability of many programming interfaces. An Interface for C, C++, Perl5, Tcl and Java JDBC is shipped with the release. Also available are packages for Python and Objective-C (the latter from the GNUStep project). With these libraries you can develop your own client applications.

The C-library is the basic interface to PostgreSQL, because it is used by most of the other libraries. The best example for using it is the psql client. If you are writing clients in C, this program will teach you much of the internal working method of the C-API.

I will now discuss a small program that writes all tuples from the cities table to the screen. (Source code can be obtained from ftp://ftp.linuxjournal.com/pub/lj/listings/issue46/2245.tgz.)

The C-Interface is located in the libpq library. If you have installed PostgreSQL properly, the linker should find the library. Link your program with the -lpq options set. In the source code, you have to include the header files with the line:

#include <libpq-fe.h>

Make sure the compiler finds the header files; they are usually located in the $POSTGRESDIR/include directory.

The first step in the dialog with the back end is to establish a connection to the database. This is done with the command:

char* dbname;
strcpy(dbname, "tutorial");
conn = PQsetdb(host, port, options, tty, dbname);

All parameters are of type char*. If one is NULL, a default value is used. Usually it is only necessary to specify the database name and a hostname if it's not the local host. The function returns a pointer, which must be used for further access to this connection.

To test the success of a PQsetdb operation the following code can be used:

if (PQstatus(conn) == CONNECTION_BAD) {
 printf(stderr,
"Connection to database '%s' failed.\n", dbname);
 fprintf(stderr, "%s", PQerrorMessage(conn));
 PQfinish(conn);
 exit(1);
}

PQerrorMessage returns a detailed error message. With PQfinish the connection is terminated and all internal buffers are freed.

After a successful connection, a query can be sent to the database in this way:

result = PQexec(conn, "select * from cities");
if ((!result) || (PGRES_TUPLES_OK !=
               PQresultStatus(result))) {
  fprintf(stderr,
"Error sending query.\nDetailed report: %s\n",
PQerrorMessage(conn));
  PQfinish(conn);
  exit(1);
}

Again, the result of the operation has to be checked, now with the function PQresultStatus which returns different codes dependent on the database operation that is to be performed. In our case, we expect to get some tuples back so we test against this condition. Another possible value is PGRES_COMMAND_OK, which is returned when a query such as INSERT was sent that returns no data. The returning pointer points to a structure within the record data. The structure could be very large. Don't try to access this structure directly; use the functions of the libpq instead, because the internal working methods of the library is subject to change. As a demonstration, here is code that prints out the contents of the cities database:

printf("name               population      altitude\n\n");
for (i = PQntuples(result)-1; i >= 0; i--) {
 printf("%s        %s              %s\n", PQgetvalue(result,i,0),
 PQgetvalue(result,i,1), PQgetvalue(result,i,2));
}
The function PQgetvalue returns the data as null-terminated ASCII strings, regardless of the field type. To determine the field type, the function PQftype could be used. This function is a weakness of the library, because it returns only the internal coding of the type which is difficult to handle. In order to interpret it correctly, one has to know that all data types are stored as separate tuples in the system database of PostgreSQL. The return value of PQftype is the OID, the unique identifier, of that tuple. To identify the type, you have to query the system database:
-- get the type with internal number 16 (bool)
select oid, typname from pg_type
where oid = '16'::oid;

Other Programming Interfaces

If you prefer a language other than C, there is a good chance that it is supported by PostgreSQL. The programming techniques are very similar to the C-library; in fact, some object-oriented interfaces make the programming simpler.

One such interface is the libpq++ library, written in C++ and included in the distribution. It has undergone a major revision in the v6.1 release. The class PgEnv is provided for manipulating the environment such as setting the port number for connections and the pathnames for the libraries and databases. The main class is PgDatabase, used to establish a connection and execute querys. It provides very convenient methods to make internal checks for successful operation. There are also classes for accessing large objects (PgLargeObject), for using transaction blocks (PgTransaction) and for defining cursors (PgCursor). The libpq++ is not built by default, you have to compile it with the provided Makefile.

The Perl5 interface Pgsql_perl5 also must be compiled with the Makefile. It provides two different interfaces. One is nearly identical to libpq so that porting C applications to Perl is easy. The other interface uses the object-oriented features of Perl5. Listing 1 demonstrates the use of that interface. Its purpose is to give a listing of all users who are owners of databases. To run this script, you need to have the proper access rights in the system database. As you can see, with the object-oriented interface, it is not necessary to close the connection explicitly. The module handles this automatically when the connection object is destroyed.

______________________

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix