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.