A Database Server in Linux

Databases are an integral part of almost every human activity. And working with
computers often means using some form of database or the other. Walk up to any PC in a
typical office and you are likely to find a few xbase (dBASE, Foxpro, Clipper...) files
lying around on the hard disk. Since the early days of PCs (even from the time of the CP/M
operating system), dBASE has been a popular program, easy to use, flexible and powerful
enough to be applied to a wide range of database management tasks. As a result, there is
today a huge number of applications that use the DBF file format. Many organizations and
individuals have large databases of important and valuable information stored in this
xbase formats. And many people who wish to exchange or distribute databases commonly use
the DBF format.

Besides xbase-compatible applications and utilities, the PC world
has numerous other database management tools and options. The Windows environment plays
host to a wide variety of database management applications ranging from Access and paradox
to high-end products like Oracle.

So what about Linux? What are the database solutions in Linux and
how easy or difficult are they to use? How powerful are they? What does it take to set up
and use databases in Linux and integrate them into Web pages?

There are many database solutions for the Linux
platform–PostgreSQL, mSQL, MySQL, DB2 Universal database, Ingres, Sybase, and Oracle,
to name a few. In recent months major database vendors have come forward to offer their
products in the Linux bazaar. So if you are looking for database solutions, Linux has much
to offer, many of them for free.

In this article we will go through the essentials of setting up a
database server on Linux and building a Web interface to query the database. We discuss
the setup and use of MySQL, a robust, fast and scalable database server. It has a rich set
of features and can be accessed through many different client programs.

There are Win 95/NT-based clients and ODBC drivers to connect to
MySQL databases. MySQL is a good starting point for a quick demonstration of porting xbase
files to a Linux SQL server and getting them ready for Web output. There are some
ready-to-use tools that make it easy to transfer data from xbase and Access databases to
MySQL. And for Web-integration, there’s a neat little CGI program called WWW-SQL,
that’ll save you the trouble of using C, C++, Java or Perl. All these are available
on this month’s CD.

Setting up MySQL server COLOR="#000000" size="2">

Installation and setup of the MySQL
server is relatively easy. If you are installing the RPM distribution on a machine where
it hasn’t been installed before, you should install at least the server, client and
development RPMs (these are available on this month’s CD-ROM and need to be copied to
a temporary directory on your hard disk).

To install the MySQL server, change to the directory containing the
RPMs and type: rpm -ivh MySQL-3.22.19a-1. i386.rpm

You should get the following

output:

MySQL ###############

Creating db table

Creating host table

Creating user table

Creating func table

Creating tables_priv table

Creating columns_priv table These are the basic system tables that control access and privilege
levels to the databases you create. You are reminded to set a password for MySQL root
user. This can be done by "mysqladmin -u root password"

The mysqld daemon should be up and running with databases from /var/lib/mysql.

Next install the client, type "rpm -ivh
MySQL-client-3.22.19a-1.i386.rpm" and the development files by typing "rpm
-ivh MySQL-devel-3.22.19a-1. i386.rpm"

The above commands install the executables in /usr/bin, mysql
include header files in /usr/include/mysql and the lib files in /usr/lib/mysql. (The
header and lib files are needed for the installation of add-on programs discussed below).

Your SQL server is now up and running. To verify that your SQL
server is indeed ready for action, type "mysqladmin ver". You’ll see
some output on the screen that gives the server version and other information. To shut
down the server type "mysqladmin shutdown". To start the server daemon
again, type "safe_mysqld --log &".

One of the things you’ll want to do after starting the server
is to see the databases that are available. This is achieved by the running the program mysqlshow,
which should give the following output:

+----------------+

| Databases |

+---------------+

| mysql        |

| test           |

+---------------+

The two databases–mysql and test–are created
automatically by MySQL during installation. The "mysql" database is used by
MySQL to organize the program’s internal settings and privilege tables, while
"test" is made available to all users as a sample database. Data is stored
within each database as tables and each table consists of rows and columns which represent
what you may call records and field in xbase files. To see what tables are available
within a database, type "mysqlshow ". For example,
to see the tables that are available in the system database "mysql", issue the
command: mysqlshow mysql. The output will list the tables under
mysql.

Database: mysql

+---------------+

| mysql |

+---------------+

| db         |

| host |

| user |

+---------------+ Creating and using databases

Now that you’ve your SQL server
up, you would want to create databases and issue SQL commands to access and use these
databases. In order to access the server, you need to run a client program and know some
SQL syntax. The text-based client program packaged with the server is a good starting
point to get familiar with some of the basic SQL commands that you’ll later use in
designing the Web interface to the database.

We’ll use the "mysql" program to create a
database of contacts and insert a table containing addresses of people we know. First, to
set up a new database called contacts in MySQL, create a directory with the name contacts
in the /var/lib/mysql/ directory. The /var/lib/mysql directory is the root
directory for MySQL databases. If now you give the command "mysqlshow"
you’ll see the database contacts listed. You may wish to set up appropriate access
and privileges to users for your databases. The manual explains how to set up various
privileges. For our example we’ll use the defaults.

The client program can now be started. Type mysql.
You’ll get the MySQL prompt from where you can issue SQL commands interactively.

There’s not much you can do at this stage, as the contacts database
is empty. The first step therefore is to create the tables that’ll store your data.
Creating a table requires you to specify a table name and define the structure for the
data it’ll contain. In order to create a table named addresses that’ll contain
the names and addresses of your contacts, type the following commands.

CREATE TABLE addresses (FirstName CHAR (20),LastName CHAR (50) NOT
NULL, Address CHAR (255),KEY (LastName), PRIMARY KEY (LastName));

In SQL, data is entered into tables using the INSERT INTO clause.
You can type in the following commands to insert some data into the newly created table
addresses.

INSERT INTO addresses VALUES
(‘Ravi’,’Gupta’,’335, 2nd Cross -

10th Main - Phase I - J.P. Nagar - Bangalore - Karnataka’);

INSERT INTO addresses VALUES
(‘Taru’,’Agarwal’,’D-74 Panchsheel Enclave - New Delhi’);

(Note that SQL commands terminate with a semi-colon)

It would be tedious to enter data into tables in this fashion. You
would be better off installing one of the X Window or Windows-based clients to use data
entry forms. These clients are available on the CD-ROM). However, there’s still a
shortcut for entering data into MySQL tables. Why not import them from existing databases?
Most of you would have xbase DBF files or an Access table containing contact addresses.
Well, MySQL doesn’t provide the tools to transfer the data directly from these
sources. However, pieces of code available on the Internet makes it possible to do it.

The program dbf2mysql does a decent job of importing data from DBF
files into MySQL tables. The dbf2mysql source file available on the PCQ CD needs to be
unpacked and compiled on the machine on which you have installed MySQL. Copy the file dbf2
mysql-1.10d.tar.gz from the PCQ CD to a temporary directory and type the following: gunzip dbf2mysql-1.10d.tar.gz

tar -xvf dbf2mysql-1.10d.tar

Edit Makefile in the dbf2mysql root directory to reflect the paths
to MySQL include and lib directories. This is done by setting the following in Makefile:

MYSQLINC=-I/usr/include/mysql

MYSQLLIB=-L/usr/lib/mysql

Compile and install the program with:

make

make install

Once dbf2mysql is installed, you can convert DBF files to MySQL
tables with the command:

dbf2mysql -d -t
-c -f -v

(Note: For the above command to work, the MySQL database should
already exist. This is as simple as creating a sub-directory in /var/lib/mysql with the
name of the required database. The -c parameter creates the table indicated against the -t
option. Refer to the dbf2mysql documentation for more information).

The access_to_mysql.txt file is a piece of Access basic code
that you paste into your Access modules associated with your database. When you run that
code in Access, it outputs a text file containing the SQL commands to create the MySQL
tables corresponding to your Access tables. It also contains the SQL commands to insert
data values into the MySQL table. The output file will contain a series of INSERT INTO
clauses. Let us say that the file created is called "mycontacts.txt", which
contains the INSERT INTO commands. To load the data from this file into your MySQL
contacts database type the following from the mysql directory:

mysql

And behold, when you next issue the command mysqlshow contacts you’ll
find your Access table listed in the MySQL list of tables in the database contacts.

When you’ve managed to load data into MySQL database tables,
you can try out some of the SQL SELECT commands to get a feel of how to query the
database. Here are some commands you can try from within the interactive mysql
client:

use contacts;

Database changed

select FirstName, LastName from addresses; +-----------------+----------------+

| FirstName    | LastName |

+----------------+-----------------+

| Ravi           | Gupta
       |

| Rahul         | Roy
       |

| James        | Welch
        |

| George       | Mathew      |

| James        | Bond
       |

+----------------+------------------+

5 rows in set (0.01 sec)

select FirstName, LastName from addresses where firstname like
"James%";

+---------------------+----------------+

| FirstName       | LastName |

+---------------------+----------------+

| James             | Wlch
        |

| James             | Bond
        |

+---------------------+----------------+

2 rows in set (0.01 sec)

Look into the MySQL documentation and manual for more information on
SQL commands and their syntax. Or refer to a book on SQL commands and try them out to
query your database. When you are done, exit the client program with the "quit"
command.

If you find the text-based client inconvenient, you can try
xmysqladmin, a x-based MySQL database administration program. Or you can try
"MySQLWinAdmin", a Windows client for MySQL. The x-based client is contained in
the file xmysqladmin-1.0. tar.gz and it requires the xforms package to get
compiled. The Windows client is a no-frills program that provides access to your MySQL
databases over a network.

These GUI clients can be found on the PCQ CD. What would be more
interesting is to design a Web-based interface to your databases. We’ll look at this
in the next issue.