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.

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:

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<your_pass word>"

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 <database- name>". 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

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:

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 <name_of_existing_MySQL_database> -t <MySQL_table_name> -c -f -v <name_of_dbf_file>

(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 contacts < mycontacts.txt

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.