Programmer's ROM - The Computer Language Library

home *** CD-ROM | disk | FTP | other *** search

/ Programmer's ROM - The Computer Language Library / programmersrom.iso / ada / sql / sqlspc.int < prev next >

Wrap

Text File | 1988-05-03 | 42.3 KB | 786 lines

ADA/SQL+: A STANDARD, PORTABLE ADA-DBMS INTERFACE++ Fred J. Friedman* - Bill R. Brykczynski** *RACOM Computer Professionals, P.O. Box 576, Annandale, VA 22003 **Institute for Defense Analyses, 1801 N. Beauregard St., Alexandria, VA 22311 + Ada is a registered trademark of the U.S. Government (Ada Joint Program Office). ++ The work reported in this document was conducted as part of Institute for Defense Analyses Project T-4-206 under Contract No. MDA-903-84-C-0031 for the Department of Defense. The publication of this paper does not indicate en- dorsement by the Department of Defense or IDA, nor should the contents be construed as reflecting the official positions of those organizations. ABSTRACT A standard relational database interface for the Ada programming language is presented. By accessing databases through this standard interface, Ada pro- grams may be written in a consistent and transportable fashion, regardless of which underlying database management system (DBMS) ultimately provides actual database support. The Data Definition Language (DDL) serves three purposes, with all transformations automated to ensure consistency across uses: (1) it is standard Ada, so that data types defined therein may be "with'ed" into application programs, (2) it may be transformed into the DDL required by an underlying DBMS to define an application's database, and (3) it contains augmented information that enables it to be used to generate test data. The Data Manipulation Language (DML), while being standard Ada, is also as similar to SQL as permitted by Ada syntax, to provide all the power and flexibility of the language proposed as the ANSI relational standard. Major portions of the system described have actually been implemented on a prototype basis to prove the feasibility of the approach. ACKNOWLEDGMENTS The authors thank Col. William A. Whitaker, of the WIS JPMO, for providing the ideas and encouragement that led this work to fruition. The driving force behind our efforts has been his guidance that "it can all be done in Ada" providing you "use the full power of Ada". We also thank Maj. Terry Court- wright, also of the WIS JPMO, and Dr. John Salasin, of the Institute for Defense Analyses, for their fine technical direction and management support of this effort, ensuring that the authors had all the facilities and resources necessary to reach a successful conclusion. INTRODUCTION The United States Department of Defense initiative towards programming language standardization with Ada [1] shows great promise. High-level plan- ners realize, however, that standards must go beyond the basic language defi- nition in order to reduce the cost and time required for software development and maintenance. The standards and software developed under this effort have addressed the area of interfacing with database management systems (DBMS). A standard DBMS interface for Ada has been developed, consisting of both a data definition language (DDL) and a data manipulation language (DML). Use of this standard within application programs will permit them to operate transportably with any of a variety of commercial, off the shelf (COTS) DBMSs. In addition, tools have been developed to assist in the implementation of the standard with a new COTS DBMS, and to automatically generate test data for use during system checkout and tuning phases. The standard DBMS interface, Ada/SQL, adheres to the current version of the proposed ANSI standard for SQL [2] as much as possible. The underlying DBMS need not, however, conform to the SQL standard; the Ada/SQL environment translates between the standard Ada/SQL interface and that of the underlying DBMS. COMPONENTS OF A DATABASE MANAGEMENT SYSTEM Every database management system provides two main language components, one for data definition and one for data manipulation. The data definition language has traditionally been used for the one-time function of creating new databases, and perhaps for modifying existing databases where such actions were permitted. With its definitions of tables and columns (in the relational data model), it is used not only to define the structure of the database, but also to maintain the consistency of that structure and its contents. It seems reasonable to also import the data definitions into programs accessing the database, to maintain consistency between their program variable types and those within the database. This has, however, been implemented by very few relational DBMSs, undoubtedly because the languages to which they provide interfaces are not strongly typed. Virtually all relational database management systems provide two flavors of data manipulation language, one available for program use of data and one for interactive use. It is desirable, and most DBMSs have, in fact, imple- mented this, that the programming language interface should be as close to the interactive interface as possible. Programmers will very often desire to use the interactive interface to experiment with various data manipulation com- mands during the design phases, and to set/modify data values for testing programs during later development phases. The selection of SQL as the stan- dard DML maximizes the number of existing DBMSs with which this desired simi- larity between the programming language and interactive interfaces can be achieved. THE STANDARD ADA DBMS INTERFACE The Ada/SQL standard Ada DBMS interface provides these two major compon- ents, a DDL and a DML, required of any database management system. The DDL is not, of course, used directly to define the contents of databases, since it is not the DDL of any existing database management system. It is, however, designed to be translatable into the DDLs required by typical COTS DBMSs. Any database schema written using the standard DDL will be in- stantly and automatically transportable to any DBMS, providing a DDL generator is available for the target DBMS. Several such DDL generators have already been written, and tools have been developed to streamline their development. The second goal for a DDL mentioned above, that of using it to define program data types consistent with database data types, is achieved by having the Ada/SQL DDL be standard Ada, compiled by any validated Ada compiler. Application programs can therefore simply "with" a DDL package in order to immediately and consistently have all database data types defined for them. The DDL is also used for a third purpose, that of automatically generat- ing test data for populating databases during the program checkout phase. Automatically generated test data, in large quantities, is also useful for determining the performance of new DBMSs, schemas, and/or programs. Certain constructs have been built into the DDL to enable it to be used for this purpose, particularly to ensure that the test data generated is meaningful in terms of the application. The Ada/SQL standard data manipulation language is, as noted above, SQL, or as close to it as is possible within the constraints of Ada syntax. Since the DML is also Ada, it may be used directly within programs that are compiled by any validated Ada compiler. In order to make this possible, functions are defined to build data structures that are then used to translate the SQL operations into the commands required by the underlying COTS DBMS actually storing the database. A mini-DBMS that uses these data structures has been implemented, in order to show how they may be used to process the SQL func- tions of SELECT, UPDATE, INSERT, and DELETE. PORTABLE APPLICATIONS AND TOOLS Ada/SQL is more than just an interface specification; it includes a set of tools for implementing the interface with an underlying DBMS. The way all these components fit together is shown in Figure 1. As can be seen, applica- tion DDL and programs are totally transportable across underlying database management systems. This portability is created by tools that translate between the Ada/SQL standard protocols and those required by a specific under- lying DBMS. These tools must obviously have some components that are specific to the underlying DBMS, but they are designed such that much of their code is also transportable across DBMSs. The DDL for a database application is written as one or more Ada pack- ages. Application programs may "with" these packages to define the data types they will need to access the database. A DDL generator program reads the text of the standard DDL to generate the DDL required to define the application database to the underlying DBMS. Once the database has been defined, the application programs may use Ada/SQL statements to process the data stored therein. These statements are actually Ada subprograms which build data structures descriptive of the opera- tion performed, and/or cause execution of the operation. Procedures executing Ada/SQL operations can be viewed as part of a DML converter package, which converts the Ada/SQL operations into the instructions required by the specific underlying DBMS, thereby causing the operations to actually be performed. Parts of the DML converter are transportable; the bulk of it is, however, dependent on the underlying DBMS. The Ada/SQL data manipulation language includes references to table and column names defined by the data definition language. A SQL function genera- tor reads the DDL and defines the necessary overloaded functions to implement these name references. Data types for strong typing of database operations are also automatically defined. The output of the SQL function generator, which consists largely of instantiations of generic functions, is "with'ed" into application programs to make the functions and data types defined visi- ble. The data types and table/column names are independent of the underlying DBMS, so the SQL function generator is totally transportable. The standard DDL package may also be read as text by a test data genera- tor tool. The test data generator uses the augmented database descriptions of the DDL to generate meaningful test data for the application programs. Since the test data generator uses Ada/SQL statements to load the database, it is totally transportable. Output can also be targeted for bulk load of a data- base, if warranted by the data volumes and processing speed. As already noted, large volumes of test data can also be used to derive performance fig- ures for new DBMSs, schemas, and/or programs. The DDL generator, SQL function generator, and test data generator all read the Ada/SQL DDL. Code to read the DDL and build descriptive data struc- tures can be shared by all three components. The prototype was in fact implemented in this fashion, where code written for the SQL function generator was reused to write DDL generators for two different underlyings DBMSs. The test data generator has not yet been prototyped. REQUIREMENTS FOR DATA DEFINITION The main purpose of a relational data definition language is to define the tables that will be present within a database. Each table is named, and consists of one or more named columns. Each column has a particular data type for values that may be assigned to it. Unique keys for tables may also be specified, as this can be used both for consistency checking and for perform- ance improvement. The Ada/SQL standard DDL includes provision for specifying all the above items, as well as privilege and view definition (these latter functions are not discussed here). CONCERNS OF AUTOMATIC TEST DATA GENERATION Additional information beyond that of a typical DDL is required for automatic generation of meaningful test data. Column data types are, of course, still required, but are now used to actively guide the data generated. In this regard, it is useful to know when the domain of one column is a subset of the domain of another. For example, EMPLOYEE and MANAGER columns may both be of type EMPLOYEE_NAME (a strongly typed version of STRING), but test data should be generated such that all MANAGERs are EMPLOYEEs, but only some EM- PLOYEEs are MANAGERs. Likewise, knowledge of key uniqueness is required to determine whether or not duplicate values should be generated for columns. In addition, which columns may be used for joins between tables must be known, so that corre- sponding data can be generated for join columns. The type of join for each pair of columns must also be known; whether the join is one-to-one, one-to- many, or many-to-many. FEATURES OF THE STANDARD ADA DDL The Ada/SQL standard DDL upholds the Ada philosophy of strong typing, by using Ada data types to also indicate database data types. Since the DDL is legal Ada that is "with'ed" into application programs, this naturally also determines the corresponding program data types. Strong typing also indicates which columns may be joined to which others, since join operations will only make sense between columns of the same data type. A typical DBMS might only support one variety of STRING type, but the Ada/SQL DDL, Ada programs using it, and the test data generator will have the benefit of knowing about EMPLOY- EE_NAMEs, HOME_ADDRESSes, etc. Strong typing will prevent a program from comparing an EMPLOYEE_NAME to a HOME_ADDRESS, and the test data generator will, of course, also generate different data for each type of column. The Ada subtype mechanism provides a convenient technique for determining how data is to be subsetted for automatic generation. Expanding the EMPLOYEE and MANAGER example above, the Ada DDL statement subtype MANAGER_NAME is EMPLOYEE_NAME; makes it obvious that MANAGERs are a subset of EMPLOYEEs. The EMPLOYEE column would then be of subtype EMPLOYEE_NAME, while the MANAGER column would be of subtype MANAGER_NAME. Type and subtype names may be suffixed with "_NOT_NULL" or "_NOT_NULL_- UNIQUE" to indicate that columns of those types should have the appropriate SQL constraints. The test data generator will generate data in accordance with these constraints. For example, a column of type EMPLOYEE_NAME_NOT_NULL- _UNIQUE might be a key for an employee roster, and the test data generator would not generate any duplicate values for that column. As noted above, column types determine which columns may be joined to- gether. Thus, a column of subtype EMPLOYEE_NAME may be joined to one of subtype MANAGER_NAME, because both columns are of the same type, although of different subtypes. The uniqueness constraints given by the type and subtype definitions determine the type of join performed, and hence the nature of the test data generated. Joins between two unique columns are one-to-one, those between one unique and one non-unique column are one-to-many (the "one" side is obviously on the unique column), and joins between two non-unique columns are many-to-many. Subsetting, as indicated by subtype, determines whether a join will match all values in a column or whether some values will not have corresponding matches in the other join column. There are three possibilities: (1) all values in each join column have corresponding value(s) in the other, (2) one join column has values that are a subset of the other, and (3) although some values in each join column overlap, both columns have values that are not present in the other. The Ada DDL instructing the test data generator to produce data appropriate for each of these three cases is as follows: (1) -- no special DDL required, -- both columns of subtype A (2) subtype B is A; -- subset column of subtype B, -- other column of subtype A (3) subtype A is COMMON_TYPE; subtype B is COMMON_TYPE; -- one column of subtype A, -- other of subtype B Two subtypes, one derived directly from the other and with names differing only in the "_NOT_NULL" or "_NOT_NULL_UNIQUE" suffixes, will not be subset, and are considered by the test data generator to be the same subtype for all purposes except uniqueness and the null value possibility. Thus, given the following DDL: type EMPLOYEE_NAME is new STRING(1..20); subtype EMPLOYEE_NAME_NOT_NULL_UNIQUE is EMPLOYEE_NAME; subtype MANAGER_NAME is EMPLOYEE_NAME_NOT_NULL_UNIQUE; Columns of subtype EMPLOYEE_NAME and EMPLOYEE_NAME_NOT_NULL_UNIQUE will have the same range of values generated for them, except that null and duplicate values will be generated for EMPLOYEE_NAME columns but not for EMPLOYEE_NAME_- NOT_NULL_UNIQUE columns. Only a subset of these values will be generated for columns of subtype MANAGER_NAME. The MANAGER_NAME subtype does not inherit the unique or null constraints from EMPLOYEE_NAME_NOT_NULL_UNIQUE; the actual subtype name must include the suffix in order for the constraints to apply. The declaration for MANAGER_NAME is therefore equivalent, for test data gene- ration purposes, to the following declaration subtype MANAGER_NAME is EMPLOYEE_NAME; Database rows are indicated by Ada records in the Ada/SQL standard DDL, which is a most natural notation. Database columns are therefore represented, more or less, by the components of the Ada records. Ada records may, however, have components which are themselves records, and most DBMSs do not support this subrecord concept. When encountering a subrecord, the DDL generator will expand it into its components, as many levels down as necessary, to produce the non-composite column descriptions required by the DDL of the target DBMS. Subrecords may be used advantageously in two ways: (1) they provide handy data types for items which are almost always processed together, such as latitude and longitude, and (2) they may be used to indicate uniqueness constraints over groups of columns (composite keys), even though each individual column within the group may not have unique values. THE STANDARD DDL IS ADA Figure 2 shows a sample database specification in the standard Ada/SQL DDL. The tables defined are a small part of an illustrative database used in [3]. As already stated, Ada record types are used to indicate the columns of database tables, as well as programming language subrecords within those columns. The PARCELS and PARCEL_ACCOUNTS record types define database tables, since they are not used as subrecords of any other records. On the other hand, the PARCEL_TRANSACTION_KEY record is used as a subrecord within another record, so the DDL generator will not produce a database table for it. PARCEL_TRANSACTION_KEY_NOT_NULL_UNIQUE is used to designate a unique composite key (group of columns indicated by a subrecord) within the PARCEL_- ACCOUNTS table. Another use of subtyping to indicate uniqueness is illustrat- ed by the ASSESSOR_PARCEL_NUMBER_NOT_NULL_UNIQUE subtype. The example does not include a use of subtyping to show subsetting for test data generation. In the example, all type definitions used for the database are included within a single package. This is not necessary; packages of type definitions may be "with'ed" into database definitions, as into any Ada packages. The Ada/SQL DDL also includes a facility for combining table definitions from several packages into a single database schema. Database definitions may therefore be organized in logical, modular fashion. Defining logically sepa- rate parts of a database in separate packages may reduce the number of appli- cation program recompilations required by changes to the data definition, since only users of the affected packages must be adjusted. Each separate package may also be viewed as a subschema, defining and enabling operations on only a portion of the database. Views and privileges may also be used to define subschemas and protections for different classes of users of a data- base. THE STANDARD ADA DML LOOKS LIKE SQL Several representative statements from the Ada/SQL standard data manipu- lation language are shown in Figure 3. All examples except the last are recodings of SQL statements used in [3]. As part of developing the DML, all the functions necessary to process every example in the book were coded and executed, as verification of the versatility and completeness of the Ada/SQL DML. It is surprising how close we can come to SQL using Ada syntax (remember, these examples are excerpts from actual programs that may be compiled by a validated Ada compiler and then executed), but certain minor concessions did, of course, have to be made due to the natures of the two languages. For example, the SQL keywords SELECT and DECLARE are also reserved words in Ada, so the corresponding Ada/SQL subprograms are called SELEC and DECLAR. And, the arguments to the subprograms must have parentheses surrounding them (open- ing on line 1 and closing on line 9, for example), whereas no parentheses are used in SQL. The same naturally holds true for arguments to INSERT_INTO, UPDATE, and DELETE_FROM. The necessity to join separate words with under- scores to make them single identifiers in Ada is also apparent in CURSOR_FOR, GROUP_BY, ORDER_BY, INSERT_INTO, and DELETE_FROM. Lists of items in SQL are separated by commas, in Ada/SQL they are separated by ampersands, since the ampersand can be overloaded as an Ada function whereas the comma cannot be. Examples of such lists can be seen in the following clauses: SELECT (line 2), FROM (line 3), GROUP BY (no example shown), ORDER BY (no example shown), and the INSERT INTO column list (line 22). For INSERT INTO value lists (line 24) the "and" operator is used as the connective. STRINGs would often be used as values, and using ampersands would have required redefining the array catenation operator, which is often used with STRINGs. UPDATE SET clauses (lines 27 and 28) are also separated by "and", in order to achieve the correct precedence between clauses. "<=" is used within each clause to indicate assignment of a value to a column, and an operator of lower precedence (i.e., not ampersand) must therefore be used to separate clauses. The various SQL clauses become subprogram parameters in Ada/SQL. Thus, the clause names must be followed by the Ada parameter association symbol "=>", and the clauses must be separated by commas. Perhaps the greatest concession in Ada/SQL was required by the restric- tions on overloading the Ada equality and inequality operators ("=" and "/="). Ada/SQL functions return data structures, but these Ada operators can only be overloaded to return BOOLEAN, with operands of the same type. Hence, it is necessary for Ada/SQL to write "A=B" as EQ(A,B). This is required for compar- ison operators (see, for example, lines 4 and 5) and is the reason that "<=" is used instead of "=" for setting UPDATE values (lines 27 and 28). Other SQL comparison and arithmetic operators that can be redefined in Ada are expressed in their natural fashion, however (line 8 shows an example of the greater than operator). SQL functions translate directly to Ada (see, for example, SUM on lines 2 and 8), but infix operators that have no equivalent in Ada must be written as Ada prefix functions (e.g., LIKE on line 6). Several other minor concessions were also required. For example, an asterisk cannot stand by itself in Ada, as in "SELECT *" or "COUNT(*)", so the asterisk is instead made into a character literal (line 15). Also, Ada strings are delimited by double quotes instead of the single quotes (apostro- phes) used by SQL. Even with all these concessions the similarity between SQL and Ada/SQL is remarkable. This is because the Ada language provides many features that can be exploited for Ada/SQL. Already noted were the use of subprogram and param- eter names for SQL clause names, the direct translation of SQL functions such as SUM into corresponding Ada functions, and the redefinition of arithmetic and comparison operators other than equality and inequality. The redefinition of operators also applies to the boolean operators of "and", "or", and "not", so that predicates can be joined in their most natural fashion. The use of functions for Ada/SQL operators and SELECT statements allows nested queries, as with the EXISTS example beginning on line 11. Database table and column names are also functions (defined by the SQL function generator from the DDL), overloaded to return objects of the appro- priate type depending on the context in which they are used. The Ada record component selection operator (period) corresponds precisely with the SQL column selection operator, so qualified columns can be directly indicated in Ada (see, for example, line 17. PARCELS.APN is a column name qualified with a table name. The notation KEY.APN selects the KEY column in the PARCEL_- ACCOUNTS table by SQL semantics, then the APN subcolumn by Ada semantics.) THE DML AND DDL ARE TIED TOGETHER BY ADA/SQL The examples thus far did not include the use of program variables within SQL statements, but that is very straightforward. Any constant in the exam- ples can obviously be replaced with a program variable; it is merely an argument to a subprogram. In short, program variables may be used anywhere they are semantically meaningful. There is no need (and no possibility, in fact) to differentiate program variables from database columns with a prefix such as the colon used by SQL. Of course, this does mean that program vari- ables may not have the same names as database tables or columns, but this should not be a major problem. If record program variables are defined for each table in the database, using the types declared in the standard Ada DDL, then the record component names will be the same as the database column names. Continuing with the road association example, the program/PDL shown in Figure 4 illustrates the naturalness of this approach and the tie-in between the DDL and the DML. The ROAD_ASSOCIATION_SCHEMA package "with'ed" on line 1 is the standard Ada DDL already discussed, defining record types for the road association database. The ROAD_ASSOCIATION package on line 2 contains the definitions of all Ada/SQL functions (table and column names, and overloaded functions on unique data types) for the road association database. It is automatically generated from the standard Ada DDL by the SQL function generator. A use clause is not used for ROAD_ASSOCIATION_SCHEMA, defining record types, but is used for ROAD_ASSOCIATION, defining table names, because the automatic genera- tion procedures cause table names to be homographs of database record type names. Table names are used in all Ada/SQL operations, while record type names are used only in declarations. Since the former use is expected to be more frequent than the latter, table names are made directly visible, while the record type names are visible only by selection. The SQL_OPERATIONS package on line 3 contains the standard Ada/SQL subprograms, such as SELEC, and operators not dependent on data types, such as the AND used to build search conditions. Line 6 shows how the standard DDL may be used within a program. PARCELS, in the ROAD_ASSOCIATION_SCHEMA package, is a definition of a record type used within the database. The CURRENT_PARCEL object of that type is the logical choice into which to retrieve tuples from the corresponding table. The CURSOR declared on line 7 is used with the DML to fetch successive tuples from a retrieved table. The exact fetch mechanism used is not shown here; it paral- lels the SQL FETCH-INTO operation. Procedure SHOW_PARCELS_ON_ROAD asks the user to select a road (the PDL on line 9), queries the database for information on parcels on the selected road (the query is set up by the Ada/SQL on lines 10-13), and displays information on all parcels on the selected road (the PDL on lines 14-15 would turn into a loop in the actual code). Within the Ada/SQL query, PARCELS is the name of the database table generated from the PARCELS record type definition in the DDL. PARCELS.ROAD is the ROAD column in the PARCELS database table; CURRENT_- PARCEL.ROAD is the ROAD component in the CURRENT_PARCEL program object. The way in which the functions and other declarations are set up causes this distinction to be automatically maintained by the Ada compiler. ADA/SQL DML IMPLEMENTATION The preceding discussion of Ada/SQL DML examples presented details of the strategy for implementing the SQL language within pure Ada code. This section concisely recapitulates the major ideas discussed. Subprograms are defined for the basic SQL statements, such as SELECT. The parameters of these subprograms are given the same names as the SQL clause keywords, so that named parameter associations use the SQL clause keywords. Functions are also defined for those SQL operations, such as the AND used to build search conditions, that do not depend on database-unique data types. Operations, such as EQ, that must be defined on user data types, are defined generically. The SQL function generator automatically generates in- stantiations of these functions based on the data types declared within the standard Ada DDL. The SQL function generator also writes functions (most are generic in- stantiations) corresponding to database table names and column names. These functions are overloaded based on the type of result returned; the Ada compil- er selects the correct version based on context. For example, a table name function returns a very simple data structure (an indication of the table name) when used within a FROM list. When used to qualify a column, however, as with PARCELS.APN, the table name function (PARCELS in this case) returns an access value designating a record object. The record object has one appro- priately named component for each column in the corresponding table. Each component (such as APN) is a data structure describing both the table name and the column name. The SQL function generator is totally transportable, since all the functions it produces are independent of the underlying DBMS. COMPILE-TIME CHECKING OF OPERATOR FUNCTIONS Writing SQL within Ada enables the Ada compiler to perform type checking on database columns just as it does on program variables. This naturally improves the reliability and maintainability of the resultant programs. Using the road association example again, the Ada compiler would not permit a programmer to say EQ(PARCELS.ROAD,"I don't know") for example. PARCELS.ROAD returns a data structure describing the database column selected. The type of this data structure is derived from the base type of all such data structures, specifically to correspond to the type of the column. Since the PARCELS.ROAD column is of type ROAD_DESIGNATOR, the SQL function generator might produce type ROAD_DESIGNATOR_COLUMN is new COLUMN; Code would then be generated to instantiate the EQ function, as well as all other appropriate ones, to allow comparisons between database columns and program variables of type ROAD_DESIGNATOR: function EQ is new BINARY_OPERATOR(O_EQ, ROAD_DESIGNATOR_COLUMN,ROAD_DESIGNATOR_COLUMN); function EQ is new BINARY_OPERATOR(O_EQ, ROAD_DESIGNATOR_COLUMN,ROAD_DESIGNATOR); function EQ is new BINARY_OPERATOR(O_EQ, ROAD_DESIGNATOR,ROAD_DESIGNATOR_COLUMN); BINARY_OPERATOR is a generic function for defining (not surprisingly) binary operators. Its first argument is an opcode (an enumeration type indicating the type of operation performed) to be placed into the data structure returned by the operator function. The next two arguments are the types of the left and right operands, respectively, of the binary operator. (This also applies to the two parameters, in order, of an operator, such as EQ, which must be written using Ada prefix function notation.) The first instantiation enables programs to compare two database columns of type ROAD_DESIGNATOR, which would most likely be used for joining tables. The remaining two instantiations enable programs to compare database columns with program objects and literals of type ROAD_DESIGNATOR. As a convenience, either order of the database column and the program object is permitted. As can be seen, there is no EQ function defined for comparing ROAD_DESIGNATOR_COLUMNs with string objects, which is why the Ada compiler would not permit the erroneous statement noted above. Another function of operators such as EQ is to convert their program object operand (if any) to an internal representation that is independent of any user-defined data types. This is required so that the underlying Ada/SQL routines can operate on data types known to them; they do not know about any user-defined data types. (The underlying routines are independent of any specific database content.) The conversion is simple for integer, real, or string types -- values are just converted to the appropriate predefined type. Enumeration, array, and record types require data structures to represent values. Much compile-time checking of SQL syntax is also provided by appropriate- ly defining the Ada/SQL operators. If, for example, the EQ operator returned a result of type SEARCH_CONDITION, then the AND operator would be defined only for objects of type SEARCH_CONDITION. (Other definitions of "and" are pro- vided for use within insert value lists and update set clauses, but they are not germaine to this discussion.) This would make a use such as WHERE => EQ(..) AND EQ(..) legal, while WHERE => "hello" AND "goodbye" would be rejected by the compiler. Similar syntax checking is applicable to lists of items. A typical example is the GROUP BY clause, which requires lists of column names. By defining the ampersand operator correctly, the compiler will accept GROUP_BY => ROAD & OWNER but will reject GROUP_BY => ROAD & 7 SUMMARY There are several objectives that are important for an Ada database interface. The interface should be portable by virtue of database indepen- dence, so that application programs using the interface can be run on any computer system using any underlying DBMS. Details of using a specific DBMS should be the concern of the interface implementation, not of the application programs. The interface should be written using pure Ada, in line with the philosophy of not subsetting or extending the language. It should be designed so that the resulting code satisfies the readability, reliability, and main- tainability objectives of Ada itself. Ada/SQL clearly satisfies these objectives. It also provides the addi- tional advantage of being compatible with the SQL language, which is not only the most widely used relational database language, but which has also been proposed as the ANSI standard language for relational databases. REFERENCES [1] Military Standard: Ada Programming Language, ANSI/MIL-STD-1815A, 1983. [2] Draft Proposed American National Standard: Database Language SQL. New York, NY: American National Standards Institute, Inc., 1985. [3] C.J. Date, Database: A Primer. Reading, MA: Addison-Wesley, 1983. ------------------------------------------------------------------------------ +--------------------------------------------------+ | with | | \|/ +-+-----+ * Legend in block +-------+ +-----------+ / +--+ to right: / SQL / |Application| Totally / DDL / | SQL Function +--->/ Func +----->|Program | Portable / +--+ | Instan- | / Inst / with | | Applications +-----+-+ | | tiations | +-------+ +-----+-----+ | | | | | - - - | - - | | Data - - - - | - - - - - - - - - - - | - - - - - - - - - | | | | | Data | | | +-----------+ | +-----------+ | Call | | | | SQL +-+ | Test | | Totally | | +->| Function | | Data | | Portable \|/ \ | Generator | +->| Generator | \|/ Tools +-----------+ \ +-----------+ | +-----+-----+ +-----------+ | DDL | +---------------+ | | DML | | Generator | - Data - - - - +--------->| Converter | - - - - - - | | Call | | +-----+-----+ +-----+-----+ | | DBMS- | | Specific | | Tools | | | | - - - | - - - - - - - - - - - - - - - - - - - - - - - - | - - - - - - - - - | | \|/ \|/ COTS DBMS COTS DBMS COTS DBMS DDL DML Calls Figure 1. Ada/SQL Portability Design ------------------------------------------------------------------------------ 1.package ROAD_ASSOCIATION_SCHEMA is 2. 3. type ASSESSOR_PARCEL_NUMBER is new STRING(1..9); 4. subtype ASSESSOR_PARCEL_NUMBER_NOT_NULL_UNIQUE 5. is ASSESSOR_PARCEL_NUMBER; 6. 7. type ROAD_DESIGNATOR is (REDWOOD, CREEK, MILL); 8. type OWNER_NAME is new STRING(1..20); 9. type IMPROVED_FLAG is (Y,N); 10. type ENTRY_NUMBER is range 1 .. 99999; 11. type MONEY is delta 0.01 range -99_999.99 .. 99_999.99; 12. type CALENDAR_DATE is new STRING(1..6); 13. type TRANSACTION_DESCRIPTION is new STRING(1..20); 14. type TRANSACTION_TYPE is (CHARGE, CREDIT); 15. 16. type PARCEL_TRANSACTION_KEY is 17. record 18. APN : ASSESSOR_PARCEL_NUMBER; 19. EN_TRY : ENTRY_NUMBER; 20. end record; 21. 22. subtype PARCEL_TRANSACTION_KEY_NOT_NULL_UNIQUE 23. is PARCEL_TRANSACTION_KEY; 24. 25. type PARCELS is 26. record 27. APN : ASSESSOR_PARCEL_NUMBER_NOT_NULL_UNIQUE; 28. ROAD : ROAD_DESIGNATOR; 29. OWNER : OWNER_NAME; 30. IMPROVED : IMPROVED_FLAG; 31. LAST_ENTRY : ENTRY_NUMBER; 32. BALANCE : MONEY; 33. end record; 34. 35. type PARCEL_ACCOUNTS is 36. record 37. KEY : PARCEL_TRANSACTION_KEY_NOT_NULL_UNIQUE; 38. DATE : CALENDAR_DATE; 39. DESCRIPTION : TRANSACTION_DESCRIPTION; 40. TY_PE : TRANSACTION_TYPE; 41. AMOUNT : MONEY; 42. BALANCE : MONEY; 43. end record; 44. . 45. . 46. . 47.end ROAD_ASSOCIATION_SCHEMA; Figure 2. The Standard DDL is Ada ------------------------------------------------------------------------------ 1. DECLAR ( CURSOR , CURSOR_FOR => 2. SELEC ( PARCELS.OWNER & SUM(PARCEL_ACCOUNTS.AMOUNT), 3. FROM => PARCELS & PARCEL_ACCOUNTS, 4. WHERE => EQ(PARCELS.APN,PARCEL_ACCOUNTS.KEY.APN) 5. AND EQ(PARCEL_ACCOUNTS.TY_PE,CREDIT) 6. AND LIKE(PARCEL_ACCOUNTS.DATE,"82%"), 7. GROUP_BY => PARCELS.OWNER, 8. HAVING => SUM(PARCEL_ACCOUNTS.AMOUNT) > 500.00, 9. ORDER_BY => PARCELS.OWNER ) ); 10. 11. DECLAR ( CURSOR , CURSOR_FOR => 12. SELEC ( APN & OWNER, 13. FROM => PARCELS, 14. WHERE => EXISTS 15. ( SELEC ( '*', 16. FROM => PARCEL_ACCOUNTS, 17. WHERE => EQ(KEY.APN,PARCELS.APN) 18. AND EQ(TY_PE,CREDIT) 19. AND AMOUNT > 499.99 ) ) ) ); 20. 21. INSERT_INTO ( PARCELS 22. ( APN & ROAD & OWNER & IMPROVED & LAST_ENTRY & BALANCE ), 23. VALUES 24. ( "93-282-55" and CREEK and "I.J.KING" and Y and 1 and 120.00 ) ); 25. 26. UPDATE ( PARCELS, 27. SET => LAST_ENTRY <= 24 28. and BALANCE <= 0.00 , 29. WHERE => EQ(APN,"93-282-55") ); 30. 31. DELETE_FROM ( PARCEL_ACCOUNTS, 32. WHERE => DATE < "84" ); Figure 3. The Standard Ada DML Looks Like SQL ------------------------------------------------------------------------------ 1. with ROAD_ASSOCIATION_SCHEMA; 2. with ROAD_ASSOCIATION; use ROAD_ASSOCIATION; 3. with SQL_OPERATIONS; use SQL_OPERATIONS; 4. 5. procedure SHOW_PARCELS_ON_ROAD is 6. CURRENT_PARCEL : ROAD_ASSOCIATION_SCHEMA.PARCELS; 7. CURSOR : CURSOR_NAME; 8. begin 9. read CURRENT_PARCEL.ROAD from user 10. DECLAR ( CURSOR , CURSOR_FOR => 11. SELEC ( '*', 12. FROM => PARCELS, 13. WHERE => EQ(PARCELS.ROAD,CURRENT_PARCEL.ROAD) ) ); 14. fetch successive database records into CURRENT_PARCEL, displaying 15. information on parcels on the selected road to the user 16. end SHOW_PARCELS_ON_ROAD; Figure 4. Relation of DDL and DML in Ada/SQL