Michael Geary is a software designer at Gupta Technologies Inc. He can be reached via e-mail BIX or GENIE where he goes under the name of "Geary" or by regular mail at P.O. Box 1479, Los Gatos, CA 95031.
Although the structured query language (SQL) has been around for a number of years, only recently have programmers familiar with the microcomputer environment had to come to grips with it. This is due primarily to the momentum generated by the more powerful hardware platform---specifically the 80386 and the 68020---that are being used with high-performance PCs. SQL demands a lot of computing horsepower, and it has only been recently that PCs have been potent enough to drive SQL-compatible applications. More powerful hardware, of course, enables more powerful software, and IBM's planned introduction this month of the OS/2 Extended Edition, an operating system that will include an SQL database engine, is also si nificant. Such an engine will enable programmers to develop database applications that run across dissimilar computer environments, from PCs to minis to mainframes. At the same time, users can efficiently and transparently tap into databases using SQL-compatible application software---Paradox, dBase IV, Ingres, or custom databases, for instance---that acts as a front-end to the SQL database engine on the server.
SQL consists of only 12 keywords which perform the standard database operations of data manipulation, definition, and control. Because the language (actually a sublanguage) is standardized, a number of development tools that make it simpler to link application program front-ends with SQL server engines have become available. These development systems typically fall into one of two categories: 1. traditional command-line programming tools that have a programmatic interface and use preprocessors or precompilers to translate embedded SQL commands into the host language (see the accompanied sidebar); and 2. interactive graphical tools that make it easier for programmers to construct forms, report writers, tables, and other database features. SQLWindows, a database application development system for Microsoft Windows, which I developed for Gupta Technologies and will describe in this article, has aspects of both.
Applications built with SQLWindows are true Windows applications with the full Windows user interface: scrollable table and form windows with pull-down menus, dialog boxes, pushbuttons, radio buttons, and other sorts of gadgets. SQLWindows applications can access Gupta Technologies' SQLBase and IBM's DB/2. Future releases will be able to use other databases, such as the SQL Server from Sybase, and will run on Presentation Manager and the Macintosh as well as Windows.
Figure 1, next page, shows three SQLWindows applications in action: a database of SQLWindows bugs that is used internally to develop the tool, a small window showing part of the SysColumns table, and the SQLWindows calculator. Although SQLWindows is mainly designed for writing database applications, it can be used to construct other kinds of Windows applications, such as this calculator.
SQLWindows provides the developer with several tools that work together to construct an application:
Figure 2, this page, shows the SQLWindows outliner with the calculator application open. The outliner is zoomed up to full screen in this view, with the calculator window visible on the right. I selectively expanded and collapsed portions of the outline to give an overview of the various sections of an SQLWindows application outline. In the SQLWindows outliner, a solid diamond indicates that an item has additional items nested within it (which may or may not be visible); an open diamond indicates the item has no additional levels.
One of the nice things about using an outliner is this ability to focus in on what you are working on and to collapse out other portions. In fact, the main reason I chose an outliner as the base for SQLWindows is the way it ties everything together cleanly. In similar systems I've seen on the Macintosh, a series of dialog boxes are used for the various settings associated with each field. This is easy to work with, but doesn't give you any way to get the "big picture"---you can only look at one or another of these dialog boxes at a time. The outliner gives more flexibility; you can deal with the details of any particular item without distraction, and you can also get an overview of how your application is taking shape.
At the top of the outline are some global declarations, including references to the external libraries SQLWIN.EXE (SQLWindows itself) and USER.EXE (part of the Windows system). An SQLWindows application can call external functions in any Windows dynamic link library, thus extending the SQLWindows system. These external functions can be written in any language that can produce a Windows dynamic link library. The Global Declarations section also includes variable and constant definitions, along with internal functions written in SAL.
Figure 1: SQLWindows in action, running three applications. The large window at the top is the Bug Database we use to track SQLWindows bugs. Below that is a small table window showing a portion of the SysColumns table. The SQLWindows calculator---a popup calculator program written in SQLWindows---is at the lower right.
Figure 2: Coding an SQLWindows application. The outline window shows the window definition and SAL code that implement the R/M (Recall from Memory) button in the SQLWindows calculator application. The R/M button has code for two messages: SAM_Create and SAM_Click.
The second major section of this outline describes the calculator window itself, declared as a form window. (This "form" just happens to contain mostly pushbuttons rather than data fields!) The Menu section shows the SAL code that implements the Help menu item in the calculator window. It simply calls the SalCreate Window function to bring up the dlgHelp dialog window. (You can see the first line of the definition of dlgHelp at the bottom of the outline.) The Message Actions section contains SAL code to process any messages this form window needs to handle-in this case, the SAM__Create message, which notifies the window that it has been created.
Messages are one of the greater mysteries of Windows programming, and one of the causes of the fabled learning curve faced by programmers who are new to Windows. Messages are also one of the things that give Windows its power. SQLWindows doesn't try to hide this message-passing architecture; an SQLWindows developer must deal explicitly with messages. So, we had better take a look at just what they are.
An SQLWindows application, like any other Windows application, is built around a collection of various kinds of windows. Nearly every visible object on the screen is a window. In the calculator program, the form itself---with the title bar, and so forth---is a window. The items inside the form, which are mostly pushbuttons in this particular form, are also individual windows. This is important because all these windows can receive messages and execute program code based on what messages they receive. Every Windows application works this way; each is a collection of windows, each of which has executable code that responds to various messages.
SAM_Create Sent to each window when it is created
SAM_Destroy Sent to each window when it is destroyed (closed)
SAM_Click Sent to a pushbutton when the user clicks the
button with the mouse or keyboard
SAM_SetFocus Sent to a data field when the user selects the
field for editing by tabbing to it or clicking in it
What are messages? For the most part, they are notifications from SQLWindows or from Windows itself that some event has occurred. Each message has a name, which is really just a defined numeric constant that identifies the message. Figure 3 page 39, shows some examples.
When a window receives one of these messages, it may perform any actions needed, by executing procedural code. For example, a form window may initialize its data fields on the SAM__Create message. A window may disregard any message if no special processing is needed for that message. A pushbutton, say, might have code for a SAM__Create and a SAM__Click but not for a SAMw:)estroy. (SAM, by the way, stands for SQLWindows Application Message.)
Newcomers to Windows (and Macintosh) sometimes have a little trouble with this business of receiving messages. It's a little backwards compared to traditional programming, where the program is always in control and makes explicit requests for user input. Here, the program is a collection of pieces of code that are called by the system in response to certain events, including user input. The program doesn't know that order in which these events will occur; it just has to be ready to respond to them at any time. (There is some predictability---the very first message to any window will be SAM_Create and the very last will be SAM_Destroy.)
SQL BEGIN DECLARE . . .
char host[20] = d:750vms-t:
char host[20] = d:750vms-t:
E SQL END DECLARE
EXEC SQL DECLARE ORA1 DATABASE;
EXEC SQL DECLARE DB2 DATABASE;
EXEC SQL DECLARE CONNECT
:usr IDENTIFIED BY :pwd
AT ORA1 USING : host1;
AT DB2 USING : host2;
#include<stdio.h>
$include sqlca;
#define SQL_ERROR sqlca.sqtcode
#define SUCCESS O
{
raise_managers(raise)
$double raise;
$database company
if(SQL_ERROR)return(SQL_ERROR);
$update personnel
set salary = salary*(1.0+$raise)
where(year(hire_date)<1985) and
exists(select manager from offices
where manager = pers_num);
if(SQL_ERROR)return(SQL_ERROR);
return(SUCCESS);
}
A number of toolkits that are compatible with most conventional highlevel languages, including C, Cobol, Ada, Fortran, Basic, and Pascal, are available from different developers. Developer toolkits such as these solve a number of basic database creation problems, the most basic problem being that SQL is a nonprocedural language and, as such, is limited. In addition to performing complex data manipulation routines, embedded SQL commands let you create and alter databases and tables, retrieve and modify data, and add indexes. They also translate datatypes into the host language and automatically generate the code to do the conversion.
The Pro* series from Oracle is one such embedded-SQL development toolkit. Currently, the series is available in a number of implementations that support C, Pascal, Fortran, Cobol, PL/1, and Ada. With the Oracle kits, all SQL statements are prefixed with the words EXEC SQL. When run through the precompiler, all statements beginning with EXEC SQL are translated into the appropriate host language. See Example 1.
In this example, ORA1 and DB2 are logical names used by the connect statements. The USING clause specifies the network, machine, and database to be associated with the name ORA1 or DB2. As with cursor and statement names, ORA1 and DB2 are not host variables, but identification names given for ease of use.
The ESQL-family from Informix is another development tools series. Versions of the kit are currently available for C, Cobol, and Ada developers. In the Ada and Cobol versions of ESQL, an EXEC SQL statement placed at the beginning of the line tells ESQL that the following line or lines should be processed as ESQL statements. With C, a dollar sign ($) is used. The C code in Example 2 illustrates how ESQL can be used.
When used with an ESQL statement, the $ implies that the immediately following identifier is a C variable, and not the name of an SQL column or table. This code also illustrates a subquery condition associated with a single field condition.--eds.
Although this architecture seems foreign to people at first, it has a big payoff: the modeless user interface that Windows and Mac applications feature. instead of having menu mode, edit mode, insert mode, and who knows how many other modes, a Windows application---if put together right---and avoid most modes entirely. The user doesn't get locked into one corner of the application or another, but has all its facilities available.
Long before any procedural code has to be written, the SQLWindows developer can put together the basic shell of the application by laying out the various windows and dialog boxes. This is all done with a simple visual window editor, similar to an object-oriented "draw" program such as Windows Draw from Micrografx Inc. You can lay down items in a form, move them around and resize them, give them titles, set up the menu bar and popup menus for a window, and do it all using the window editor and the outline editor. It's quite practical for one person to prototype the visual appearance of an application this way, then hand off the application outline to someone else to write the detailed code.
Normal Windows editing facilities---cut, copy, and paste---are available during application development, which makes it easy to duplicate items in a form or copy over portions of one application into another. As mentioned before, the window editor and outline editor are tied together, so the outline always reflects the current application structure.
When it does come time to start adding procedural code and SQL calls, the outline editor is used to enter SAL code, which looks much like any block-structured language, such as C or Pascal. The biggest difference is that the code is entered at specific points in the outline, and the code itself is indented in outline format. Unlike C or Pascal, this indentation is significant; the "dependent" statements of an If statement, for example, are those statements nested under the If statement in the outline. This eliminates the need for curly braces or BEGIN END.
Figure 4, page 44, shows a portion of the application outline for the Bug Database from Figure 1. This section of code implements the update command in the Bug menu. It performs some field validation, sets the fldVerFixed status indicator, and calls SQL to update the database.
One handy feature in the SAL code is that you can refer directly to any item in a form by name, and SQLWindows will fetch or set that field value in the actual window contents. Fields work just like variables in this regard; you don't have to make special Windows calls to deal with their values. In Figure 4, for example, the SQL "update" statement takes its values such as fld-Status directly from the form fields and updates the database with those values.
Figure 4: Some detailed SAL/SQL code. This is the SAL code for the Update menu item under the Bug menu in the Database from Figure 1.
SQLWindows is designed so that most common database applications can be written completely using the SAL language and other features of SQLWindows. For applications that push the limits, it's easy to call dynamic link libraries, as mentioned earlier. This approach lets you write the bulk of your application without having to deal with the C compiler and the Windows Software Development Kit, and yet use those tools when necessary to write those special functions that you need. You can also call existing dynamic link libraries such as the graphics library from Micrografx. Calls to these external functions are coded just as any other SAL function call; it's necessary only to declare the function and library in the External Functions section of the outline.
SQLWindows can also talk to other Windows applications through the clipboard and Dynamic Data Exchange (DDE). DDE lets you set up cooperating applications that communicate with each other under program control. For example, you can write an Excel macro that passes data automatically from a spreadsheet into a SQL database through a SQLWindows application. Or, using the Proteus communications program, you can download data into a SQL database from an on-line database service, and then view the latest information through a SQLWindows form.
For debugging, SQLWindows provides several facilities including breakpoints with user-defined break actions and an animated program trace. It's also very easy to add a temporary test field in a form, or create another window, to display debugging information directly from your SAL code. A simple Set statement will display a new value in a field, making this approach handy.
SQLWindows attempts to combine the intuitive appeal of the Windows user interface with the power of a SQL database. It's designed for database application developers who want to build Windows applications without the effort of using the Windows Software Development Kit and C compiler.
SQLWindows does not yet have any automatic form generation, which would produce a default form given the name of a database table. Nor does it have graphic Metaphorlike tools for setting up connections between tables. Instead, the developer writes explicit SQL code to access the database. These other goodies will be coming in future releases, but for now we have tried to make sure that the developer can accomplish the basic task of quickly producing database applications that provide the full Windows user interface.
Researchers at the University of California at Berkeley aren't content with current database technology. While acknowledging that the relational model has solved many of the problems presented by rigidly structured data, a team of programmers led by Professors Michael Stonebraker and Lawrence Rowe also believe that there are categories of information where database technology can be applied to less structured, more dynamic data. Such complex data might include documents, engineering data, geographical data, CAD/CAM data, and programs. In these instances, Stonebraker thinks, databases might be represented more easily by a collection of rules rather than by data.
"Current database systems are great for doing data processing type things," says Stonebraker, "but they tend not to work when you stray outside of the norm. If you want to store the layout of a building or of a computer chip, for instance, they don't work well at all. That's why our objective is to be able to do object management as well as data management."
The database research at UC Berkeley revolves around two core programs: the development of a new database management system called Postgres, and the development of a supporting application development environment called Object FADS. Postgres (which stands for "after Ingres," ingres being a DBMS projectinitiated in the early 1970s), is a rule-based relational database system whereby the user can define rules that provide inference mechanisms alerters, triggers, and other pertinent rules. These rules may follow either a backward- or forward-chaining control paradigm, depending on which is more appropriate for a specific application; the rule subsystem itself can decide which is more suitable based on statistics about the current database use. A priority mechanism is also provided, so that if many rules apply at a certain case, the one with the highest priority will be used.
The project will also include the design and analysis of algorithms that support derived data objects (or views) in relational DBMSs, as well as a collection of rule-indexing algorithms, several lock-based algorithms, and a "new generation" of query optimization techniques that deal with rule processing, semantic optimization, and multi-statement optimization. Among the many unique algorithms the team has formulated is one that Stonebraker describes as an "asynchronous vacuum cleaner." Instead of immediately deleting or creating an audit trail of data as it is updated, the algorithm will simply add the new data to the system. At the same time, the system keeps track of the data that has been obsoleted and periodically collects, then discards the data that hasn't been used for a specified period of time.
The other component of the project, Object FADS, is an object-oriented development environment for interactive database applications that use a WYSIWYG interface. This project is an offshoot of an earlier system called Forms Application Development System (or FADS for short). Object FADS uses a program interface called a "portal" which allows blocks of data specified by a query to be retrieved and updated randomly. The prototype of the Object FADS system is being written in Common Lisp and has been implemented on the x/Common Lisp Interface under X-Windows.
Object FADS uses a shared object hierarchy programming environment. Since this environment is an extension of the Common Lisp Object Standard, shared classes are possible. The Object FADS toolkit includes an object-oriented forms system for building user interfaces that allows arbitrary nesting of field types. A shared-object browser, which allows programmers to browse through stored objects, is also being developed, along with a visual form editor and visual object editor.
To test the Postgres approach, the development team is using the system to create an integrated knowledge base that will analyze the water drainage system of portions of California's Central Valley. Initially, several sets of depth-to-groundwater measurements will be entered into Postgres using a customized spatial access method. The data sets will be of differing granularities, obtained at sporadic time intervals, and contain inconsistencies. The overall goal of the application is to provide a knowledge-based system that can integrate loosely related data sets so that users can make queries in a transparent manner. "Our objective here," explained Stonebraker, "is to steal a piece of what expert system shells do and do it better inside the database system."
Stonebraker believes that the biggest advantage to programmers of a DBMS like Postgres is that "they won't have to figure out how to get at islands of data since both objects and data will be managed in the same way."---eds.
One tactic programmers can take when writing SQL-compatible custom database applications with high-level programming languages is to embed English-like SQL commands directly into a program. (SQL was, in fact, originally designed with constructs to facilitate embedded coding.) To do so, however, programmers must generally use developer's toolkits that include preprocessors or precompilers in order to convert SQL-executable statements found in an input file to appropriate calls in an output file. That output file can subsequently be compiled, linked, and executed as normal.
The overall advantage of this approach is that SQL, which is a nonprocedural language, can take on some of the constructs of a procedural language such as C, Fortran, Pascal, Basic, and so on. Among other things, this means that embedded-SQL programming is more conceptually oriented and, consequently, easier to understand than programming in SQL only. It also means that a single program can be used with data in several different databases. In any event, the resulting applications tend to be more flexible than applications written only in SQL or only in a high-level language.
Although development time can often be shortened because less code needs to be written for file or database access and manipulation, an extra step or two is included in the development process. In a conventional scenario, for instance, you might write a C program, compile it into an output object file, link and edit the object file to create an EXEC file, and then run the program. With an embedded-SQL command program, however, you would: write the program, precompile it into an output file, compile the program into an object file, link-edit the object file into an EXEC file, and finally run it.