Emacs-Interface for the Relational Database SYBASE

Masami Hagiya

University of Tokyo

1. Introduction

There have been developed many programs for managing interface between database systems and their users. In this work, we developed a user-interface for SYBASE, one of the most popular relational database systems at this moment. The user-interface is implemented on top of the general-purpose text-editor GNU Emacs, which is the de facto standard text-editor on workstations. Following are some advantages of using Emacs.

    1. Since Emacs is a free software, we can provide a free user-interface for SYBASE.
    2. Since Emacs is a general-purpose text-editor, one can easily include the result of retrieving a database into text being edited.
    3.One can use those functionalities of Emacs as a general-purpose text-editor during retrieving a database.
On Emacs there have actually been developed many user-interfaces for electric mail, electric news, etc., and those Emacs-interfaces are used as de fact standard by workstation users. In this work, we also designed an Emacs-interface for SYBASE, taking the approach of those Emacs-interfaces.
For designing our user-interface, we paid attention to the following problem, which is specific to database-interfaces. During experiments using databases, such as genomic analyses, researchers usually retrieve a database many times by changing retrieval conditions by trial and error. Therefore they often forget the correspondence between a retrieval condition and the corresponding retrieval result, or they often lose one of the retrieval condition and result. We try to solve this problem by our user-interface.

2. Retrieval File

In order to solve the above problem, we took the following approach. A database user prepares for each retrieval condition a file called a retrieval file for retrieving a database. The result of retrieving the database by the condition is directly written on the retrieval file. The result is then displayed by a schema file, which is prepared for each table in the database.

A retrieval file contains an SQL command for retrieving a database and the result of retrieving the database by the command. One retrieval file should be prepared for each retrieval condition. More specifically, a retrieval file saves a select statement and the result of executing the select statement. When a user retrieves a database with a different retrieval condition, he or she should prepare another retrieval file.
A retrieval file is of the following form.

-*- dbif-query -*-
use DATABASE-NAME
go
select FIELD-NAME1,FIELD-NAME2,... from TABLE-NAME
where RETRIEVAL-CONDITION
go
--query result follows this line--
RETRIEVAL-RESULT

The part for the retrieval result is empty before the database is retrieved.

For example, if a user wants to retrieve records in the authors table whose contract field is equal to 1, he or she can use the following retrieval file.

-*- dbif-query -*-
use pubs
go
select au_id,af_name,al_name from authors
where contract=1
go
--query result follows this line--

After preparing the select statement in the retrieval file, a user invokes the Emacs-command C-cC-c. By this command, Emacs sends the select statement to SYBASE and receives the retrieval result, which is then inserted after the line.

--query result follows this line--

Each record in the retrieval result should fit on one line and should contain the key field. This means that in the select statement, one should specify the key field and possibly some more short fields.

When a user wants to display each record in the retrieval result, he or she moves the text cursor of Emacs to the beginning of the record and types a space key. This kind of interface is derived from the Emacs-interfaces for electric mail and news. When a space key is typed, the Emacs window is split into two parts. On the lower window, the specified record is displayed according to the format defined by the schema file of the table. The displayed record can then be edited on the lower window.
A retrieval file is usually saved with the retrieval result. Therefore, whenever the retrieval file is read into Emacs, one can display, update or delete a record without retrieving the database again with the same retrieval condition. If a database has been changed, one can retrieve the database again with the same retrieval condition by simply typing \verb+C-cC-c+ on the retrieval file.

3. Schema File

A schema file specifies a format for displaying or editing a table (or a view) in a relational database. By preparing a schema file for each table in the database, a user can enjoy a display-oriented database-interface on Emacs. Following is an example of a record displayed on a window. 

       id: 888-888-888
            Name: Masami Hagiya

              Phone:   03(3812)2111

              Address: Ohara 1-8-11, Setagaya-ku
                       Tokyo Japan
                       156

             Contract: 0
In order to display a record of the authors table of the pubs database in the above format, a user should prepare the following schema file. 

        id: %(au_id varchar)
             Name: %(au_fname varchar) %(au_lname varchar)

               Phone:   %(phone varchar)

               Address: %(address varchar)
                        %(city varchar) %(state varchar)
                        %(zip varchar)

              Contract: %(contract bit)
Strings in the schema file are literally displayed on a window unless they take the following form.

%(FIELD-NAME TYPE)

In each item of the above form, the name and the type of a field are specified. The actual value of the specified field in each record is displayed at the position of the item. In the above example, au_id is a field name and varchar is a type.

A schema file should have the following file name.

DATABASE-NAME_TABLE-NAME.s

The Emacs-interface can then select the appropriate schema file when it displays a record in a table.

When a record is displayed according to a schema file, one can edit the record as specified by the schema file. For example, one can move from a field to another file, update a field, choosing among the alternatives for a field, etc.

4. Summary

We successfully developed a user-interface for the relational database SYBASE on top of the text-editor Emacs, taking the approach of the Emacs-interfaces for electric mail and news. In our user-interface, the correspondence between a retrieval condition and its result is naturally maintained because they are saved on the same file. By schema files, we also provide the display-oriented database-interface on Emacs.