AOLserver Database Administration Guide

$Header: /cvsroot/aolserver/aolserver.com/docs/admin/dbadmin.html,v 1.3 2002/10/09 12:31:57 scottg Exp $

What is a Database?

Databases and AOLserver

Internal vs. External Drivers

Available Database Drivers

Internal Database Drivers

External Database Drivers

Developing Database Drivers for AOLserver

What is a Database?

A database is a collection of data stored in a computer. A Database Management System (DBMS) is a software package that efficiently manages databases and lets one or more people use or modify the contents of the database. The main function of the DBMS is to manage the internal representation of the data in a form optimized for computer access, and to respond to requests from users to access or modify the data. The DBMS translates these requests into database commands that it then performs on the data.

One of the most common and powerful languages for accessing databases is SQL. SQL (Structured Query Language) commands perform actions on the database. There are two types of SQL commands: DDL (Data Definition Language) commands, which are used to define the structure of the data, and DML (Data Manipulation Language) commands, which are used to manipulate the data itself. The data is organized into tables in the database. Tables contain rows (sometimes called records) and columns (sometimes called fields). The structure of the table and its columns is defined by DDL commands. The commands to insert or update values in the rows of the table are DML commands.

Databases and AOLserver

AOLserver can be configured to have access to multiple databases through AOLserver's database drivers. AOLserver creates a database pool, a group of connections to a database, for each database it accesses.

You can write your own Tcl or C extensions to access the database. See the Tcl Developer's Guide and the C Developer's Guide for more information.

Internal vs. External Drivers

Prior to Version 2.1, AOLserver provided a tightly-coupled solution to database driver integration, with database client libraries linked directly into the server. Such database drivers are called internal drivers.

Since Version 2.1, AOLserver provides an additional, loosely-coupled architecture, where AOLserver redirects all database requests to a separate process known as the database proxy daemon. This is accomplished using a special external driver, which looks just like an internal database driver to AOLserver.

Available Database Drivers

AOLserver provides internal database drivers for Postgres and Solid. AOLserver also provides external database drivers for Sybase. Third-party database drivers are provided for Oracle, Informix, and InterBase.

Database Type of Driver Location Postgres95/PostgreSQL Internal A free implementation of SQL, available at: http://www.postgresql.org

Database driver available at: /examples/c/postgres Solid Internal A reasonably priced RDBMS available on several platforms at: http://www.solidtech.com

Database driver available at: /examples/c/solid Sybase External A well-known RDBMS available at: http://www.sybase.com

Database driver available at: /examples/c/nssybpd

Note: Since we cannot ship anything that includes Sybase's client libraries, you'll need to compile the Sybase driver example.

Oracle 8 Internal or External The most popular RDBMS.

Database driver available from ArsDigita at: http://arsdigita.com/services.html Informix Universal Server Internal or External A well-known RDBMS.

Database driver available from ArsDigita at: http://arsdigita.com/services.html InterBase External Database driver available at: http://www.lavsa.com/sskracic/aolinterbase/

Internal Database Drivers

Configuration

Configuration for an internal database driver consists of setting parameters for the internal driver and creating a database pool that uses that driver.

The internal database driver parameters can be typed directly in the configuration file as described below. This example describes the steps involved in configuring an internal driver to interface with the Postgres or Solid driver.

Configure an Internal Database Driver


1. Edit your AOLserver configuration file, usually named nsd.tcl.
2. Add a configuration file section called ns/db/drivers:
ns_section "ns/db/drivers"
3. List the database drivers you will be using. For example:
ns_section "ns/db/drivers"
ns_param postgres nspostgres.so
ns_param solid nssolid.so
4. Add a configuration file section to list your database pools. For example:
ns_section "ns/db/pools"
ns_param mypool "This pool will use Solid"
5. Add a configuration file section to define the database pool you have named. For example:
ns_section "ns/db/pool/mypool"
ns_param Driver solid
ns_param Datasource "TCP/IP hostname 1313"
ns_param Connection 1
6. Choose which server or servers is to have access to the database pools you have defined. For each server, add a configuration file section to define which database pools it can access. For example:
ns_section "ns/server/servername/db"
ns_params Pools *

The "*" designation means that this server can access any of the defined database pools.

Solid Database

The SOLID database is a reasonably-priced database from Solid Information Technology that can be installed on many platforms. AOLserver provides an internal database driver for the SOLID database. Temporary evalation copies can be downloaded from their web site, http://www.solidtech.com. There are some special considerations with using the SOLID database that are addressed in this section.

This database driver is installed in the same way as other database drivers. Add nssolid.so as a database driver in the ns/db/drivers section of the configuration file. When you create a database pool for it, the correct format for the datasource is this: TCP/IP hostname 1313

TCP/IP specifies the protocol with which the SOLID AOLServer driver will communicate with the SOLID database server. The hostname is the fully-qualified hostname on which the SOLID database server resides, and 1313 is the port on that server where it is listening.

If you are using the nssolid database driver on Solaris 2.5.1, you need to set the LD_PRELOAD environment variable as follows: setenv LD_PRELOAD /usr/lib/libm.so.1

Note that the Solid database driver only works with nsdp under Irix.

Special thanks to Robert Locke for contributing the code for this module.

External Database Drivers

The external driver sends messages to the external database proxy daemon instead of calling database client libraries directly. This database proxy daemon can be a local or remote process. The reason for this seemingly unnecessary indirection is that some database client libraries are undesirable partners in the AOLserver process space. For example, they may make assumptions regarding per-process resources such as signals, or they may not be thread-safe. Also, platforms without support for a particular database client library can still interface with a database via a remote database proxy daemon.

A database proxy daemon is created for each connection in an AOLserver database pool. Like connections within a pool configured for an internal driver, the connections associated with proxy daemons are efficiently managed by the AOLserver on an ad hoc basis. Thus, database proxy daemon processes are created and shut down by the AOLserver as demand for concurrent database handles varies over time.

This distributed approach is not intended to replace the existing practice of linking database client libraries into the server via a driver. It merely provides an alternative interface mechanism--augmenting the choices available to developers who are extending the AOLserver database interface capabilities. It is likely, however, that this distributed approach, i.e. a separate process per database connection, will improve overall database throughput, even with the additional communication overhead. We expect this performance improvement because vendor-supplied database client libraries, running within a multi-threaded server, must limit concurrency via resource locks.

Configuration

Configuration for an external database driver consists of setting parameters for the external driver and creating a database pool that uses that driver. An external driver is configured to spawn and communicate with a database-specific proxy daemon. Database proxy daemons are provided for Sybase.

The following example describes the steps involved in configuring an external driver to interface with the Sybase proxy daemon.

Configure an External Database Driver


1. Edit your AOLserver configuration file, usually named nsd.tcl.
2. Add a configuration file section called ns/db/drivers:
ns_section "ns/db/drivers"
3. List the Sybase database driver. For example:
ns_section "ns/db/drivers"
ns_param extsyb nssybpd.so
4. Configure the Sybase driver. For example:
ns_section "ns/db/driver/extsyb"
ns_param LocalDaemon nssybpd
ns_param Param SYBASE-env-variable

The Param parameter should be set to the value of the SYBASE environment variable.


5. Add a configuration file section to list your database pools. For example:
ns_section "ns/db/pools"
ns_param mypool "This pool will use Sybase"
6. Add a configuration file section to define the database pool you have named. For example:
ns_section "ns/db/pool/mypool"
ns_param Driver extsyb
7. Choose which server or servers is to have access to the database pools you have defined. For each server, add a configuration file section to define which database pools it can access. For example:
ns_section "ns/server/servername/db"
ns_params Pools *

The "*" designation means that this server can access any of the defined database pools.

Remote Database Proxy Daemons

The previous example showed configuration of a local proxy daemon. One of the advantages of the external driver interface is that you are free to run database proxy daemons on any other host, perhaps one on which database vendor client libraries are more readily available or more reliable.

The following example shows how to configure an interface to a proxy daemon on a remote machine.

Configure a Remote External Database Driver


1. Edit your AOLserver configuration file as described in the previous section, "External Database Drivers" section.
2. In the ns/db/drivers/extsyb configuration section, delete the definition for the LocalDaemon parameter.
3. Add definitions for the RemoteHost and RemotePort parameters. For example:
ns_section "ns/db/driver/extsyb"
ns_param RemoteHost host-name
ns_param RemotePort port-number
ns_param Param SYBASE-env-variable
4. Run nssybpd, or configure inetd as shown in the next step instead.
For example:
nssybpd 8199
5. As an alternative to running nssybpd as shown in the previous step, you can configure inetd on the target remote machine as follows.
1. Add the following line to /etc/inet/services:
nssybpd 8199/tcp
1. Add the following line to /etc/inet/inetd.conf:
nssybpd stream tcp nowait sa /serverhome/bin/nssybpd nssybpd

(Type the above all on one line.)

1. Send a SIGHUP signal to your inetd process.

Tcl Interface

In addition to the built-in, database-driver independent ns_db Tcl functions, the ns_ext function is available for special functions that relate to the external driver mechanism. In general, you will not need to use ns_ext for most database operations. The ns_ext functions are used primarily by the ns_db functions, but ns_ext is provided in the event that you need to call it directly.

Developing Database Drivers for AOLserver

For information on developing your own database drivers, see the Database Drivers Development guide.