Skip to content

APL and Relational Databases

Brian Loughran (INTI Ltd)

Vector 5:3, page 75

data and files applications

Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 75

Introduction

Once upon a time …. a leading UK manufacturing company had a problem perhaps not unfamiliar to Vector readers:

  • an IBM mainframe was already fully loaded and committed growth would either reduce response times or generate substantial mainframe upgrade costs.

The cheapest solution of converting to popular PC packages was not sufficient for the “power users” (planning, pricing, sales departments) whose decision support systems consumed 50% of mainframe capacity.

Users required:

  • comprehensive facilities for departments to define and amend their own decision-support applications;
  • a high level of data integrity;
  • ease-of-use through full screen displays, automatic verification of input, context sensitive help;
  • simple commands for selecting, sorting and displaying data for update;
  • simple commands for defining complex reports and analyses;
  • for experienced users and system developers, a command mode with advanced functions including an EXPRESS option to permit as series of updates, calculations and displays to be performed in a pre-defined sequence;
  • data transfer between applications and with the mainframe;
  • the flexibility to enhance their applications and analyses without fear of hitting an insurmountable barrier.

These requirements have been met with a package known as INTI (Integrated Information) which uses SQL and APL to do what each is best at.

SQL
manages the relational data-bases, including data tables which define the data, the applications, the input verification rules, the calculations for deriving results, the definitions of report layouts and all the corresponding documentation.
APL
manages the user interface (both in full screen and command mode) and translates simple statements of user requirements into the corresponding SQL statements, circumventing in the process, the many niggardly restrictions imposed by ANSI standard SQL. And, by having the full power of APL available if needed, the applications developer is assured that there can be no statistical or business modelling problem so complex that it cannot be handled within the environment.

Design Principles:

Data Integrity. Any decision support system must confront the conflict between data integrity and data redundancy. For reports, data redundancy is essential, but redundancy threatens data integrity. INTI resolves this conflict by:

a) storing each non-derivable data element only once in a pool of relational tables;

b) for derivable data, storing not the data, but only the derivation rules;

c) presenting the user with displays of redundant data but immediately updating the whole display whenever any data element is changed. Thus the redundant displays are always self consistent.

Simplified Data Maintenance. When a user updates data and STORES the changes, the new data become available to other applications. For example, if a new department were added, then all other users of the DEPARTMENT table would gain access to it.

Ease of Application Development. Each “entity” in the business has its own table, with each row having its own unique identifiers. New applications can be generated by “mixing and matching” selected attributes of existing tables, or new tables or columns can be generated without disturbing existing applications. The overall pool of data tables (the schema) can evolve to model whatever aspects of the business users may require.

When defining an application (SETUP), each new data element is given a name (unique within its data-table as required by SQL) and a number (unique within the application). Then the attributes of the element are given, including width, decimal places or data-type (character or date), heading (default for reports), format (default for reports), comment (a description for the system catalogue), and a data-table. Optionally, an element may have a derivation rule. Input validations are specified as table look-ups or range checks. When SETUP is completed, INTI generates or modifies all the necessary data-base tables and indices through SQL.

The application SETUP is itself stored as a relational table so that, although the user perceives application definition as a separate task from data update, for INTI the same process is involved – updating an SQL table – and therefore exactly the same functions are used.

Thereafter, when defining reports or calculations for example, the user need only refer to the unique column number and all the information is already available for INTI to generate the appropriate SQL. Again, since all tasks are reduced to updating relational tables, a consistent user interface is maintained.

Ease of Use. The normal “end user” mode provides full screen menus and context-sensitive help so that users can run their applications with little computer difficulty.

A simple language is used to specify reports, calculations and graphics. This language permits column numbers, arithmetic operators, constants (numeric or alpha or date), variables and conditional expressions. INTI generates SQL statements which meet the user specification. For straightforward reports and calculations, SQL can do all the work. For requirements which SQL cannot meet (e.g. graphics, reports with control break calculations, or statistical analyses), INTI issues the appropriate SQL SELECT statement(s) and then manipulates the results.

Power and Flexibility. The COMMAND mode of INTI delivers speed and advanced functions to experienced users, including an EXPRESS mode. Moreover, the applications developer has access to a comprehensive set of tools, including:

  • screen painting of menus, data input screens and reports
  • device-independent graphics through a Dyadic supplied interface to GSL (Advanced Display Graphics Support Library)
  • links to operating system facilities including mainframe transfer, networking and DOS conversion
  • libraries for storing and sharing named APL objects.

Finally, for something really special, the applications developer can incorporate APL functions to build systems of any degree of sophistication.

Conclusion

The first implementation of INTI has been on a network of two IBM 6150’s using Dyalog APL. Applications converted from the mainframe range from simple clerical systems through to a complex suite of ten linked planning applications. Naturally, some lessons have been learned, especially about SQL.

In my personal opinion, the language is messy, verbose, non-orthogonal, full of petty restrictions and as user friendly as a wounded bear. Nevertheless, it exists: there is an ANSI standard and there is a lot of it coming (OS/2, Lotus, DBASE are all adding SQL database engines). Its disadvantages are minimised if we regard SQL not as an end-user language in itself, but rather a database handler to be embedded in another language.

Two major advantages arise from embedding in APL rather than the traditional compiler languages:

  1. The APL interpreter and a good full screen interface, provide an excellent basis for end-user flexibility.
  2. Developing and debugging the SQL generator part of INTI has been a time consuming task even in APL with its high programmer productivity. Anyone considering less productive environments should be aware that the Universe could implode in less than 150 billion years. It could be a race against time itself!

APL and SQL are both demanding of machine resources and the combination would probably have been prohibitively expensive before the arrival of the latest high powered workstations (and especially the new RISC chipsets). These provide the power and the price/performance advances that will enable SQL and APL, having been joined together in INTI – in accordance with the laws of Codd by the rites of Iverson – to live happily ever after.