FEDERAL INFORMATION PROCESSING STANDARDS PUBLICATION XXX (Draft - April 1994) Announcing the Standard for SQL Environments Federal Information Processing Standards Publications (FIPS PUBS) are issued by the National Institute of Standards and Technology after approval by the Secretary of Commerce pursuant to Section 111(d) of the Federal Property and Administrative Services Act of 1949 as amended by the Computer Security Act of 1987, Public Law 100-235. 1. Name of Standard. SQL Environments (FIPS PUB XXX). 2. Category of Standard. Software Standard, Database. 3. Explanation. An SQL environment is an integrated data processing environment in which heterogeneous products, all supporting some aspect of the FIPS SQL standard (FIPS PUB 127), are able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Some components in an SQL environment will be full-function SQL implementations that conform to an entire level of FIPS SQL and support all of its required clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other components in an SQL environment may be specialized data repositories, or graphical user interfaces and report writers, that support selected portions of the SQL standard and thereby provide a degree of integration between themselves and other products in the same SQL environment. This FIPS PUB is the beginning of a continuing effort to define appropriate conformance profiles that can be used by both vendors and users to specify exact requirements for how various products fit into an SQL environment. The emphasis in this first FIPS for SQL Environments is to specify general purpose, SQL external repository interface (SQL/ERI) profiles for non-SQL data repositories. These profiles specify how a subset of the SQL standard can be used to provide limited SQL access to legacy databases, or to support SQL gateways to specialized data managers such as Geographic Information Systems (GIS), full-text document management systems, or object database management systems. All of the profiles specified herein are for server-side products, that is, products that control persistent data and provide an interface for user access to that data. Subsequent versions of this FIPS PUB may specify SQL environment profiles for client-side products, that is, products that access data and then present that data in graphical or report-writer style to an end user, or process the data in some other way on behalf of the end user. 4. Approving Authority. Secretary of Commerce. 5. Maintenance Agency. Department of Commerce National Institute of Standards and Technology (Computer Systems Laboratory) 6. Cross Index. - FIPS PUB 127-2, Federal Information Processing Standards Publication - Database Language SQL, adoption of ANSI SQL (ANSI X3.135-1992) and ISO SQL (ISO/IEC 9075:1992) for Federal use, U.S. Department of Commerce, National Institute of Standards and Technology, June 2, 1993. - ANSI/ISO/IEC 9579, International Standard for Remote Database Access (RDA), Part 1: Generic RDA and Part 2: SQL Specialization, ISO/IEC 9579-1:1993 and ISO/IEC 9579-2:1993, published December, 1993. - ANSI/ISO/IEC CD 9075-3, (Committee Draft) International Standard for Database Language SQL, Part 3: Call Level Interface (SQL/CLI), JTC1 Committee Draft (CD), document SC21 N8436, February 1994. - ANSI/ISO/IEC CD 9075-4, (Committee Draft) International Standard for Database Language SQL, Part 4: Persistent Stored Modules (SQL/PSM), JTC1 Committee Draft (CD), document SC21 N8438, March 1994. 7. Related Documents. SQL Environment specifications will often depend upon existing Database Language SQL standards (see Cross Index above) and upon emerging SQL and SQL Multimedia standards. The following items identify formal ISO/IEC international standards projects for which preliminary specifications and base documents exist, but where the development effort has not yet reached a complete and stable stage (i.e. the Committe Draft (CD) stage). As these specifications mature and move through the standards processs, they can be referenced more reliably in procurement requirements. (Working Draft) Database Language SQL (SQL3) Part 1: Framework Part 2: Foundation -- including Abstract Data Types and Object SQL Part 3: Call Level Interface -- extensions to ISO/IEC CD 9075-3 identified above. Part 4: Persistent Stored Modules -- extensions to ISO/IEC CD 9075-4 identified above. Part 5: Language Bindings -- extensions to the binding clauses of ISO/IEC 9075:1992. Part 6: Encompassing Transactions -- to support X/Open XA-interface. (Working Draft) SQL Multimedia (SQL/MM) Part 1: Framework and General Purpose Facilities Part 2: Full Text Part 3: Spatial Other Parts: Reserved for other SQL/MM sub-projects with no current base document (e.g., images, photographs, motion pictures, sound, music, video, etc.). For information on the current status of the above Working Drafts, contact NIST personnel working on SQL Standardization at 301-975-3251. For document references to the above and for additional related documents, see the References section of the SQL/ERI Server Profiles specification. 8. Objective. The primary objective of this FIPS PUB for SQL Environments is to specify SQL profiles that can be used by Federal departments and agencies to support integration of legacy databases and other non-SQL data repositories into an SQL environment. The intent is to provide a high level of control over a diverse collection of legacy or specialized data resources. An SQL environment allows an organization to obtain many of the advantages of SQL without requiring a large, complex, and error-prone conversion effort; instead, the organization can evolve, in a controlled manner, to a new environment. 9. Applicability This standard is applicable in any situation where it is desirable to integrate a client-side productivity tool or a server-side data repository into an SQL environment. It is a non-mandatory standard that may be invoked on a case-by- case basis subject to the integration objectives of the procuring department or agency. It is particularly suitable for specifying limited SQL interfaces to legacy databases or to specialized data repositories not under the control of a full- function SQL database management system. It can be used along with other procurement information to specify SQL interface requirements for a wide range of data management procurements. One special area of application envisioned for this standard is Electronic Commerce, a National Challenge Application area of the National Information Infrastructure. The primary objective of Electronic Commerce is to integrate communications, data management, and security services in a distributed processing environment, thereby allowing business applications within different organizations to interoperate and exchange information automatically. At the data management level, electronic commerce requires a logically integrated database of diverse data stored in geographically separated data banks under the management and control of heterogeneous database management systems. An over- riding requirement is that these diverse data managers be able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. FIPS SQL provides a powerful database language for data definition, data manipulation, and integrity management to satisfy many of these requirements. It is unrealistic to expect that every data manager involved in electronic commerce will conform to even the Entry SQL level of the FIPS SQL standard; however, it is not unrealistic to require that they support a limited SQL interface, even a read-only interface, provided by one of the SQL/ERI Server profiles. New procurements to add components to the National Information Infrastructure, or to upgrade existing components, can define the necessary SQL schemas and point to appropriate SQL/ERI Server profiles as procurement requirements. This standard may also be applicable, on a case-by-case basis, in many of the following areas: Legacy databases Full-Text document databases Geographic Information Systems Bibliographic information retrieval Object database interfaces Federal data distribution Operating system file interface Open system directory interface Electronic mail repositories CASE tool repositories XBase repositories C++ sequence class repositories Object Request Broker interface repository Real-time database interface Internet file repositories Further detail on each of these potential application areas can be found in Section 8, "Applicability", of the FIPS specification for SQL Environments. 10. Specifications. See the Specifications for SQL Environments - SQL External Repository Interface (SQL/ERI) - Server Profiles (affixed). 11. Implementation. Implementation of this standard involves four areas of consideration: the effective date, acquisition of conforming implementations, interpretation, and validation. 11.1 Effective date. This publication is effective immediately upon publication. Since it is a non-mandatory specification, based on the established FIPS SQL standard, and used at the discretion of individual Federal procurements, no transitional period or delayed effective date is necessary. 11.2 Acquisition. All conforming implementations of a specific SQL/ERI profile will support some aspects of the FIPS SQL standard. However, such implementations will not normally be full function database management systems and conformance will often be dependent upon SQL schema definitions and other requirements provided as part of each individual procurement. In most cases, a procurement will not be able to simply point to an SQL/ERI profile and demand conformance to it. Instead, successful procurements will normally use an appropriate SQL/ERI profile, together with an application-specific schema definition, as one aspect of overall procurement requirements. In many cases, vendors of products that provide a limited SQL interface will define their interfaces in terms of a fixed SQL schema definition. In those cases, procurements can point to the vendor-provided schema definition and to an appropriate SQL/ERI profile as a procurement requirement. In some cases, especially in those situations where schema definitions and requirements are not known in advance, a request for a proposal (RFP) may require that an SQL schema, and adherence to one of the SQL/ERI Server profiles, be presented as part of the response proposal. 11.3 Interpretation. NIST provides for the resolution of questions regarding specifications and requirements of the FIPS for SQL Environments, and issues official interpretations as needed. Procedures for interpretations are specified in FIPS PUB 29-2. All questions about the interpretation of FIPS SQL Environments should be addressed to: Director Computer Systems Laboratory ATTN: SQL Environments National Institute of Standards and Technology Gaithersburg, MD 20899 Telephone: (301) 975-2833 11.4 Validation. Implementations of the FIPS for SQL Environments may be validated in accordance with NIST Computer Systems Laboratory (CSL) validation procedures for FIPS SQL (FIPS PUB 127). Recommended procurement terminology for validation of FIPS SQL is contained in the U.S. General Services Administration publication Federal ADP & Telecommunications Standards Index, Chapter 4 Part 2. This GSA publication provides terminology for three validation options: Delayed Validation, Prior Validation Testing, and Prior Validation. The agency may select the appropriate validation option and may specify appropriate time frames for validation and correction of nonconformities. Implementations may be evaluated using the NIST SQL Test Suite, a suite of automated validation tests for SQL implementations. Although this test suite was designed to test conformance of full-function SQL database management systems, it can be modified to accommodate testing of SQL/ERI Server implementations. The results of validation testing by the SQL Testing Service are published on a quarterly basis in the Validated Products List, available from the National Technical Information Service (NTIS). Current information about the NIST SQL Validation Service and the status of validation testing for SQL Environments is available from: National Institute of Standards and Technology Computer Systems Laboratory Software Standards Validation Group Building 225, Room A266 Gaithersburg, Maryland 20899 (301) 975-2490 12. Where to Obtain Copies. Copies of this publication are for sale by the National Technical Information Service, U.S. Department of Commerce, Springfield, VA 22161, telephone 703-487-4650. When ordering, refer to Federal Information Processing Standards Publication XXX (FIPS PUB XXX), SQL Environments. Payment may be made by check, money order, or deposit account. FEDERAL INFORMATION PROCESSING STANDARDS PUBLICATION XXX Specifications for SQL Environments SQL External Repository Interface (SQL/ERI) Server Profiles U.S. DEPARTMENT OF COMMERCE National Institute of Standards and Technology Computer Systems Laboratory Information Systems Engineering Division Gaithersburg, MD 20899 Table of Contents Abstract . . . . . . . . . . . . . . . . . . . . . . . . . . . v 1. Introduction . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Database Language SQL. . . . . . . . . . . . . . . . . 1 1.2 SQL environment. . . . . . . . . . . . . . . . . . . . 2 2. Data Integration Architecture. . . . . . . . . . . . . . . 4 3. SQL External Repository Interface (SQL/ERI) . . . . . . . . . . . . . . . . . . . . 6 4. SQL/ERI Leveling Rules . . . . . . . . . . . . . . . . . . 7 4.1 Minimal Schema Definition Language . . . . . . . . . . 8 4.2 Minimal Data Manipulation Language . . . . . . . . . . 9 5. Optional Extensions. . . . . . . . . . . . . . . . . . . . 10 5.1 SQL'92 features . . . . . . . . . . . . . . . . . . . 10 5.2 Stored procedures and callable routines (SQL/PSM) . . 10 5.3 SQL multimedia class library (SQL/MM) . . . . . . . . 11 5.4 Abstract data types and methods . . . . . . . . . . . 11 5.5 Object data management . . . . . . . . . . . . . . . 12 6. SQL Binding Alternatives . . . . . . . . . . . . . . . . . 12 6.1 SQL Module processor . . . . . . . . . . . . . . . . 12 6.2 Embedded SQL preprocessor . . . . . . . . . . . . . . 12 6.3 Direct invocation of SQL statements . . . . . . . . . 13 6.4 SQL call level interface (SQL/CLI) . . . . . . . . . 14 6.5 RDA/SQL-Server interface . . . . . . . . . . . . . . 16 7. SQL/ERI Server Profiles. . . . . . . . . . . . . . . . . . 18 7.1 SQL/ERI Read-Only Server . . . . . . . . . . . . . . . 21 7.2 SQL/ERI Read-Write Server . . . . . . . . . . . . . . 25 8. Applicability. . . . . . . . . . . . . . . . . . . . . . . 29 8.1 Legacy databases . . . . . . . . . . . . . . . . . . . 30 8.2 Full-Text document databases . . . . . . . . . . . . . 30 8.3 Geographic Information Systems . . . . . . . . . . . . 30 8.4 Bibliographic information retrieval. . . . . . . . . . 30 8.5 Object database interfaces. . . . . . . . . . . . . . . 31 8.6 Federal data distribution . . . . . . . . . . . . . . . 31 8.7 Operating system file interface . . . . . . . . . . . . 32 8.8 Open system directory interface . . . . . . . . . . . . 32 8.9 Electronic mail repositories. . . . . . . . . . . . . . 32 8.10 CASE tool repositories . . . . . . . . . . . . . . . . 33 8.11 XBase repositories . . . . . . . . . . . . . . . . . . 33 8.12 C++ sequence class repositories. . . . . . . . . . . . 33 8.13 Object Request Broker repositories . . . . . . . . . . 33 8.14 Real-Time database interface . . . . . . . . . . . . . 34 8.15 Internet file repositories . . . . . . . . . . . . . . 34 9. Conformance Testing. . . . . . . . . . . . . . . . . . . . 34 9.1 NIST SQL Test Suite . . . . . . . . . . . . . . . . . . 35 9.2 Testing SQL/ERI implementations . . . . . . . . . . . . 37 10. Procurement Considerations. . . . . . . . . . . . . . . . 38 10.1 Client-side products . . . . . . . . . . . . . . . . . 38 10.2 SQL/ERI Clients. . . . . . . . . . . . . . . . . . . . 39 10.3 SQL/ERI Servers. . . . . . . . . . . . . . . . . . . . 40 References . . . . . . . . . . . . . . . . . . . . . . . . . . 43 FIPS PUBLICATION XXX Specification for SQL Environments - SQL External Repository Interface (SQL/ERI) - Server Profiles Abstract An SQL environment is an integrated data processing environment in which heterogeneous products, all supporting some aspect of the FIPS SQL standard (FIPS PUB 127), are able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Some components in an SQL environment will be full-function SQL implementations that conform to an entire level of FIPS SQL and support all of its required clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other components in an SQL environment may be specialized data repositories, or graphical user interfaces and report writers, that support selected portions of the SQL standard and thereby provide a degree of integration between themselves and other products in the same SQL environment. This FIPS PUB is the beginning of a continuing effort to define appropriate conformance profiles that can be used by both vendors and users to specify exact requirements for how various products fit into an SQL environment. The emphasis in this first publication is to specify general purpose, SQL external repository interface (SQL/ERI) server profiles for non-SQL data repositories. The SQL/ERI interface supports integration of heterogeneous, non-SQL data repositories into an SQL environment while retaining full use of the SQL language for user applications. All of the profiles specified herein are for server-side products, that is, products that control persistent data and provide a standard interface for accessing that data. Subsequent versions of this FIPS PUB may specify profiles for client- side products in an SQL environment, that is, products that access data and then present that data in graphical or report-writer style to an end user, or process the data in some other way on behalf of the end user. To make it easier to specify integration among heterogeneous, non-SQL data models, this specification defines a new minimal level of the SQL language that can be supported by various non-SQL implementations. Non-SQL data repositories, such as Geographic Information Systems (GIS), full-text document management systems, or object database management systems, may use this minimal level, or one of the other levels specified in FIPS SQL, to describe their capabilities as SQL/ERI Servers. Two major SQL/ERI Server profiles are specified: read-only and read-write. This specification may also be used as a starting point for defining International Standardized Profiles (ISPs) for SQL language access to non-SQL data repositories. Keywords: (CLI; conformance; database; ERI; FIPS; interface; Internet; ISP; multimedia; object; profile; PSM; relational; repository; standard; SQL; testing) Electronic Availability: An electronic version of this specification is available using Internet anonymous FTP protocols. Internet Node: speckle.ncsl.nist.gov User name: ftp Password: @ Change Directory to: isowg3/FIPSdocs Get File: fipseri.ps -- Postscript version An ASCII text version of this document is also available in the same directory as above, but with file name "fipseri.txt". You will receive some sign-on messages. If these messages confuse your FTP client, you can turn them off when you sign-on again by preceding your password with a hyphen (-). 1. Introduction An SQL environment is an integrated data processing environment in which heterogeneous products, all supporting some aspect of the FIPS SQL standard (FIPS PUB 127), are able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Some components in an SQL environment will be full- function SQL implementations that conform to an entire level of FIPS SQL and support all of its required clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other components in an SQL environment may be specialized data repositories, or graphical user interfaces and report writers, that support selected portions of the SQL standard and thereby provide a degree of integration between themselves and other products in the same SQL environment. The intent is to provide a high level of control over a diverse collection of legacy or specialized data resources. An SQL environment allows an organization to obtain many of the advantages of SQL without requiring a large, complex, and error-prone conversion effort; instead, the organization can evolve, in a controlled manner, to a new environment. This FIPS PUB is the beginning of a continuing effort to define appropriate conformance profiles that can be used by both vendors and users to specify exact requirements for how various products fit into an SQL environment. The emphasis in this first specification is to specify general purpose, SQL external repository interface (SQL/ERI) profiles for non-SQL data repositories. These profiles specify how a subset of the SQL standard can be used to provide limited SQL access to legacy databases, or to support SQL gateways to specialized data managers such as Geographic Information Systems (GIS), full-text document management systems, or object database management systems. All of the profiles specified herein are for server-side products, that is, products that control persistent data and provide an interface for user access to that data. Subsequent versions of this FIPS PUB may specify SQL environment profiles for client-side products, that is, products that access data and then present that data in graphical or report- writer style to an end user, or process the data in some other way on behalf of the end user. 1.1 Database Language SQL Database Language SQL is enjoying success as an effective International Standard for customers and implementors of full-function, SQL-compliant database management systems that support the relational data model. Many vendors have implemented an entire level of the SQL'92 standard [8] and offer products that conform to all of its clauses for schema definition, data manipulation, transaction management, integrity constraints, access control, and schema information. Other vendors have implemented selected portions of the SQL standard, most often read- only data retrieval or very restricted data manipulation, in order to provide SQL access to legacy databases or to support SQL gateways to specialized data managers. The first SQL standard, in 1986, provided basic language constructs for defining and manipulating tables of data; a revision in 1989 added language extensions for referential integrity and generalized integrity constraints; and the most recent revision in 1992 provides new facilities for schema manipulation and data administration, as well as substantial enhancements for data definition and data manipulation. A companion standard for Remote Database Access (RDA) [9], completed in 1993, provides the basic services and protocols for SQL interoperability in a distributed, wide area client/server environment. A companion standard for an SQL Call Level Interface (SQL/CLI) [10], registered as a draft standard in February 1994, provides a language binding appropriate for third-party software developers in a local client/server environment. An extension to SQL for definition and invocation of persistent stored procedures and for SQL flow of control statements, named Persistent SQL Modules (SQL/PSM) [11] and registered as a draft standard in March 1994, permits user definition of procedural program blocks that can then be optimized at multiple SQL servers and invoked as needed, thereby reducing both processing time and communications volume. Features of the SQL'92 standard are discussed in References [1], [2], and [15]. Proposed features of the next SQL revision, often called SQL3, are discussed in [4] and [5]. SQL is particularly appropriate for the definition and management of data that is structured into repeated occurrences having common data structure definitions. SQL provides a high-level query and update language for set-at-a-time retrieval and update operations, as well as required database management functions for schema and view definition, integrity constraints, schema manipulation, and access control. SQL provides a data manipulation language that is mathematically sound and based on a first-order predicate calculus. SQL is self-describing in the sense that all schema information is queryable through a set of catalog tables called the Information Schema. SQL is becoming the language of choice for many user productivity tools -- such tools communicate with a human user through a graphical user interface and then formulate SQL queries to communicate with underlying persistent data repositories. Formal language profiles for partial SQL conformance are necessary because the user productivity tools and the underlying data managers may be purchased at different times from different vendors and are unlikely to even know of one another's existence. A recognized profile specification will allow limited portability and interoperability, even in otherwise non-homogeneous environments. 1.2 SQL environment Many applications require a logically integrated database of diverse data (e.g. documents, graphics, spatial data, alphanumeric records, complex objects, images, voice, video) stored in geographically separated data banks under the management and control of heterogeneous data management systems. An over-riding requirement is that these various data managers be able to communicate with one another and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Much of this source data may be stored in simple file systems, legacy data management systems, or very specialized data repositories that satisfy only a small percentage of these data management requirements. The objective of an SQL environment is to logically integrate these diverse data repositories "as if" they were under the control of a single SQL data manager. User presentation tools, such as graphical user interfaces or report writers, can then use this SQL interface to collect data from various sources, merge it together under ad hoc join conditions, and present it to the user in a pleasing graphical format. A properly functioning SQL environment will use the SQL language to describe this data using standardized facilities, integrate it into a single federated collection, enforce any integrity or access control constraints, and make it available as a logical whole to sophisticated user productivity or presentation tools. These client-side tools can then use the full power and flexibility of SQL for data retrieval and manipulation. The underlying data managers may implement non- relational data models and thus may have difficulty supporting SQL requirements for nested subqueries, multi-table joins, derived columns in a Select list, referential integrity, or other relational model features. On the other hand, they may offer advanced features of other data models that are rarely supported by relational implementations. With emerging features in the SQL language for user- defined abstract data types (ADTs), stored procedures, encapsulation, polymorphism, and other object management facilities, these diverse data repositories can be described as specialized SQL repositories and accessed using already standardized SQL binding alternatives identified in Section 6. With this approach, SQL may prove to be as successful as an integrator of heterogeneous data repositories as it has been as a language interface to the relational data model. The SQL language can meet these integration objectives if non-SQL implementations provide a "simple" SQL interface to their data and services, and if full-function SQL implementations use that simple interface to provide full- function services to end user tools. This specification defines standard profiles for such "simple" SQL interfaces, thereby making it easier to specify and support the desired integration. An SQL environment depends upon the data integration architecture presented in Section 2. A simplification of this architecture is given in the figure below. Components in the architecture consist of Application Processors, Full-Funtion SQL Processors, and Non-SQL Processors. The Application Processors represent client-side products in an SQL environment that desire the ability to use the full power and flexibility of the SQL language when accessing data from a database. The Non-SQL Processors are server-side products representing data managers that control much of the data that is to be integrated and made available to the Application Processors. The Full-Function SQL Processors serve a dual role, both as server-side data managers and as "integrators" that make it possible for Application Processors to access data managed by the Non-SQL Processors in a standard manner. The interface between an Application Processor and an SQL Processor is a full-function SQL interface, whereas the interface between an SQL Processor and a Non-SQL Processor is one of the more limited SQL/ERI interfaces described in Section 3. All interfaces use one of the binding styles identified in Section 6. It is the integrator's role to provide access to all data as if it were managed by a full-function SQL processor. Section 4 describes existing conformance levels of the SQL language and then defines a new, minimal SQL language level that can be used to define conformance alternatives for SQL/ERI Servers. Section 5 identifies various higher level SQL features and data types that an SQL/ERI Server may support. In this way, an SQL/ERI Server can present the features of a different data model to an SQL application by describing them as SQL abstract data types, methods, procedures, or other callable routines. Section 7 specifies two major SQL/ERI Server profiles -- a read-only profile for static data repositories, and a read-write profile that allows SQL Update, Insert, and Delete statements. The read-write profile also provides an option that allows creation of SQL tables and views. Section 8 identifies a number of application areas for which SQL/ERI Server profiles may be applicable. Section 9 describes how the NIST SQL Test Suite might be modified to provide conformance testing for SQL/ERI Servers and Section 10 identifies some procurement considerations for users that intend to use this FIPS PUB for SQL Evironments to aide in the specification of procurement requirements. The SQL/ERI profiles specified herein may be used by customers and vendors of non-SQL processors to validate claims of conformance for partial support of the SQL language. If these SQL/ERI Server profiles prove to be helpful for integrating non-SQL data repositories into SQL environments, then later versions of this FIPS PUB may specify profiles for SQL/ERI Clients as enhancements to full-function SQL implementations and profiles for other client-side products in an SQL environment. This specification may also be used as a starting point for defining International Standardized Profiles (ISPs) [14] for SQL language access to non-SQL data repositories. 2. Data Integration Architecture This FIPS for SQL Environments envisions an integrated data processing environment in which SQL and non-SQL processors are able to comunicate with each other and provide shared access to data and data operations and methods under appropriate security, integrity, and access control mechanisms. Application processors will then have protected access to all data using the full power and flexibility of Database Language SQL. Standard communication among cooperating systems is possible at the present time using either OSI protocols [16] or Internet Society protocols [7]. Efforts are underway within both of these arenas to provide cross-protocol mappings for interoperability. Application services in both protocol environments provide for association control, file transfer, virtual terminal, and electronic mail. Future versions will contain extensions of these facilities as well as enhancements for remote database access (RDA), document management, and electronic data interchange (EDI). Near-term extensions to these protocols should make it possible for user-defined objects at various remote sites to communicate their existence and provide access to their methods to application processors. Objects at remote sites may be able to "show themselves" to users at local workstations by using emerging specifications and standards for graphical user interfaces. The RDA component of both OSI and Internet Society communication protocols provides the basis of distributed access to remote data repositories and "standard" access to the data they manage. With implementation of an External Repository Interface (ERI), discussed in Section 3 below, it is possible for non-SQL data repositories to be "self-describing" in terms of SQL facilities so that they can be accessed and manipulated by all other sites using standard SQL language and RDA protocols. With longer-term emerging data management standards that support object-oriented and knowledge-based features, an ERI interface can evolve into a "seamless" integration of complex, structured data and supporting application services. Begin with an "Application Processor" that wishes to communicate with and access data at a number of different data repositories, some local and some remote. The Application Processor could use existing communication protocols to connect to external processes or transfer files, but it would prefer not to have to manage its own communications links or worry about integrity, access control, remote transactions, or any number of different data manipulation functions; instead, it would rather communicate with a single, "familiar" data manager for both schema data and actual data occurrences. The "familiar" data manager could then connect itself to remote sites and access the desired data and data definitions, returning them to the accessing processor in a standard format. A remote object would still be able to use windowing protocols to "show itself" to the accessing process or use file transfer protocols to transfer objects or object definitions not under the control of the communicating data managers. This architecture assumes the existence of any number of heterogeneous data repositories, some at the local site and some at distributed sites. It also assumes a full-function SQL processor at all sites, but not necessarily as the manager of the most important data. The non-SQL processors may control the maps, documents, graphics images, or complex engineering structures that the Application Processor wishes to access. The local SQL processor conforms to Database Language SQL and has two integrated client components, one conforming to the RDA/SQL Specialization and one conforming to the SQL/ERI interface proposed in Section 3 of this specification. Communications among the three SQL components are likely to be proprietary. The local site may have any number of non-SQL data repositories each controlled by a non-SQL Processor having a component that conforms to the SQL/ERI interface. Communications among the internal components of the non-SQL Processor are also proprietary. The local site has a proprietary local procedure calling mechanism and a proprietary local inter-process communications capability. Using these proprietary mechanisms and one of the standard local binding styles identified in Section 6 (e.g. SQL/CLI), the Application Processor issues standard SQL calls to the local full-function SQL processor, and the SQL/ERI Client component of the SQL processor is able to communicate, using an ERI specified subset of standard SQL, with the SQL/ERI Server of the non-SQL Processor. The local site is connected to one or more remote sites via a standard OSI or IETF communications network ([16], [7]) that allows "messages" or "calls" to be exchanged among processes. Some messages may be sent directly from the Application Processor to processes or file stores at the remote site, but ideally, some local repository manager makes a connection and sends messages on behalf of the Application Processor. The Generic RDA and RDA/SQL Specialization standards [9] specify protocols that allow the RDA Client component of the local SQL processor to send SQL statements to the RDA Server component of a remote SQL processor, or the SQL/ERI Server component of any Non-SQL processor, and receive data in return. All protocols and data are defined in the RDA standards and are transmitted as ASN.1 (ISO 8825) packages. If the Application Processor is operating, interactively, on behalf of a human user, then any of the data repositories may use a local graphical user interface (GUI), or non-local windowing protocols, to present status information or a "menu of choices" to the human user. In this way an interactive "browsing" or "navigational" capability is provided to the human user without losing the standard RDA/SQL protocol communications used by the non-human processors. At the remote site there exists a full-function SQL Processor as well as any number of non-SQL Processors. Components of the SQL Processor conform to the SQL and RDA standards, and satisfy the proposed SQL/ERI Client requirements. Each non-SQL Processor has a component that conforms to the SQL/ERI Server specification. The remote site handles internal communications and procedure calls in the same proprietary manner as does the local site. At the present time the RDA standards specify interchange protocols for transmitting records of data from a server site to a client site, provided that the data items in the records are either numbers or character strings. Near term RDA follow-on specifications will extend the data types handled to all of those specified in the SQL'92 standard [8], i.e. fixed and variable length character strings, fixed and variable length bit strings, fixed and floating point numerics, dates, times, timestamps, and intervals. Later RDA follow-on specifications will provide interchange mechanisms, in terms of ASN.1 elements, for the user defined abstract data types (ADTs) specified in the emerging SQL3 working draft [12]. RDA protocols do not by themselves provide interchange mechanisms for other data objects, so interchange standards for images, motion pictures, maps, topologies, or other complex objects will remain critical for transmitting object instances among various sites. SQL and RDA provide the basis for standardized communication. An SQL external repository interface (SQL/ERI) makes it possible for non-SQL data repositories to share their data with user applications. With emerging SQL3 enhancements for object-oriented and knowledge-based data management and emerging RDA extensions for distributed database, the ERI can evolve to support "seamless" data integration. 3. SQL External Repository Interface (SQL/ERI) Applications require access to multiple data repositories, many of which are managed by non-SQL processors. It is not unusual for applications to require data from the operating system, from graphics repositories, from CD-ROM's, from CAD/CAM databases, or from libraries of cataloged data. From a user's perspective, it is unrealistic to expect every data repository to be able to handle even the lowest "Entry SQL" queries. For example, who would expect an electronic mail system to handle SQL joins and subqueries over its message headers? Yet, every e-mail system is a data repository with information that applications sometimes require. What is needed is an interface specification that enables a non-SQL data repository to make certain external views available to SQL processors and for those SQL processors to, in turn, allow the full power and flexibility of the SQL query language over those views to the end user. It makes sense to specify a "client" and a "server" interface to external repositories so that non-SQL systems can act as servers to SQL requests for data. It makes sense to develop the conformance requirements needed for non-SQL systems to provide SQL views of their data and for SQL systems to provide full function SQL operations over that data to SQL users. This interface is defined to be the SQL External Repository Interface (SQL/ERI). It consists of a "Server" part and a "Client" part. Non-SQL systems may claim conformance as SQL/ERI Servers and full-function SQL systems may claim conformance as SQL/ERI Clients. This first FIPS PUB for SQL Environments only addresses conformance criteria for SQL/ERI Servers; subsequent versions may address conformance criteria for SQL/ERI Clients. A wide range of non-SQL products and services might be able to claim conformance as SQL/ERI Servers. They could provide high level abstract data types with application-specific methods and operations. They would be required to evaluate "simple" SQL queries over individual tables defined in the schema. The exact meaning of "simple" is specified in the SQL/ERI profile specifications at different levels of service. The SQL processor can then think of the external repository as an SQL-environment that can be connected to, but that can only respond to whatever SQL statements are specified for that level of service. If an SQL system claims conformance as an SQL/ERI Client, then it agrees to provide SQL functionality, at whatever level of the SQL standard it conforms to, over any table provided by an SQL/ERI Server. This may require that the SQL system automatically create a temporary table whenever the external view is referenced in a query, and then populate that table using the limited capabilities provided by the "server" interface so that it can guarantee the ability to perform nested queries, or searched updates and deletes, or recursive queries, or whatever is requested by its application. With the SQL/ERI "client" and "server" definitions, non-SQL systems would be able to provide services to SQL-based applications even though they might not be able to provide the expected query flexibility, access control, concurrency control, or updatability required of a full-function SQL data manager. Full-function SQL processors could provide these expected data management facilities and, in addition, provide user access to data repositories not otherwise accessible via the SQL language. Section 2 describes how SQL/ERI profiles might be used to provide uniform and integrated application access to both SQL and non-SQL data at local and remote sites. The SQL/ERI profile specifications provide several different conformance levels for non-SQL systems. A conforming SQL/ERI server is required to be "self- describing" as if it were a separate SQL-environment. It is required to supply an SQL Information Schema describing all available tables and the equivalent SQL data types for its columns. If the ERI Server provides new abstract data types not defined in the SQL standard, then it is also required to provide an SQL ADT interface definition as specified in the emerging SQL3 standard [12]. What is needed to make the above scenario feasible is an SQL/ERI Server profile, so that these non-SQL data repositories can provide a simple, external interface, accessible from full-function SQL systems. Sophisticated applications can then be built without the need to "understand" the non-standard data access methods unique to each repository. Instead, full-function SQL systems could be used as intermediaries. The SQL "client" could connect itself to the non-SQL "server" using the standard SQL/ERI interface; the application could then use the full power and flexibility of the SQL data manipulation language, as well as the system provided special access methods, to select and mange the data as if it were maintained in an SQL database. Section 7 of this FIPS PUB provides the necessary SQL/ERI Server profiles to get this integration scenario started. 4. SQL/ERI Leveling Rules The SQL'92 standard [8] specifies three levels of conformance for SQL language and SQL implementations: Entry SQL, Intermediate SQL, and Full SQL. In addition, FIPS SQL [3] defines a fourth level of conformance, called Transitional SQL, approximately halfway between Entry SQL and Intermediate SQL. FIPS Transitional SQL is intended to provide a common, near-term goal for SQL implementations that already have a number of features beyond Entry SQL. It is intended for use in U.S. federal government procurements in the interim period before Intermediate SQL implementations are readily available. All of these existing SQL conformance levels require the facilities of a full-function SQL processor, i.e. schema definition, data manipulation, transaction management, and access control. New conformance alternatives are needed for non-SQL processors that wish to claim conformance to only a portion of the SQL language. Such processors may be able to provide very sophisticated data retrieval capabilities, but may not be able to allow update of data instances or creation of new schema objects. Since existing SQL levels cut across both the schema definition and data manipulation facilities in the SQL standard, it is necessary to consider each SQL level separately as applied to schema definition or data manipulation. Consider the SQL leveling rules separately for schema definition and data manipulation. Use the term Schema Definition Language (SDL) to identify SQL language features defined in Clause 11, "Schema definition and manipulation", in the SQL'92 standard, and use the term Data Manipulation Language (DML) to identify SQL language features defined in Clause 13, "Data Manipulation". One is then able to discuss the following alternatives for partial support of the SQL language: Entry DML Entry SDL Transitional DML Transitional SDL Intermediate DML Intermediate SDL Full DML Full SDL There is an additional requirement to specify new Minimal DML and Minimal SDL levels to be used exclusively in the definition of SQL/ERI Server profiles. These Minimal definitions are intended for use only by non-SQL processors and cannot be used to claim conformance to the SQL standard as an SQL processor. Minimal DML will support SQL operations on a single table, with no joins and no subqueries, and with severe limitations on derived columns and set functions. Minimal SDL will support specification of only the simplest views and the simplest SQL tables, using only character string, integer, decimal, and real data types, with no table constraints and only very limited column constraints. Levels of conformance in the SQL standard are specified by Leveling Rules in each clause of the specification. Using the style of the SQL standard, the following subsections specify restrictions that apply for Minimal SDL and Minimal DML in addition to any restrictions for Entry SQL. All Clause and Subclause references, and all syntactic terms delimited by angle brackets (i.e. <...>) are from SQL'92 [8]. 4.1 Minimal Schema Definition Language 1.A contained in a shall be a or a . 2.A
contained in a
shall be a . 3.A shall not be a , a , or a . A may only specify NOT NULL. 4.The of a shall not specify NUMERIC, FLOAT, or DOUBLE PRECISION. A may only specify DECIMAL, REAL, INTEGER, SMALLINT, and fixed length CHARACTER string s. 5.A shall not specify WITH CHECK OPTION. 6.The contained in a shall satisfy the restrictions specified by the Minimal Data Manipulation Language leveling rules below. 4.2 Minimal Data Manipulation Language 1.A shall be a . 2.A in the of a shall be either COUNT(*) or a whose directly contained is a . 4.A
shall not contain a or a . 5.The contained in a
shall contain exactly one
, and that
shall be a single
without an associated . A
may be qualified to include a . 6.A contained in an shall not contain any . Any contained in a shall be a without subqueries, a , a , a , or an whose is a parenthesized list of s. 7.A contained in any shall have exactly one that is a . 8.A shall be a , or a that is a . 9.A shall not be a or a . 10.A shall not be a . 11.A shall not be a . 12.A in a shall not be an . Note: Leveling Rule 2a of Subclause 13.8, "", is incorrect in that it should also allow a . This is corrected in SQL Technical Corrigendum 1 [20]. 5. Optional Extensions An SQL/ERI Server will often support additional data types and SQL language facilities beyond those specified for the given level of service. This section identifies features in the SQL'92 standard, and emerging features in the SQL3 and SQL/MM specifications, that an SQL/ERI Server may support. Each of these items is an optional indication that must be explicitly declared by an SQL/ERI Server implementation before an application program can rely on its existence. An SQL/ERI Server that supports a Read-Only interface will support only the read- only aspects of each feature. Thus a non-SQL implementation may be able to define itself to an SQL client using very complex abstract data types (ADTs) and methods specified in SQL3, even though it does not allow creation or modification of such types. The SQL/ERI server will declare the accessing "signature" of such facilities in the Information Schema, so that an application can use the SQL/PSM routine and procedure calling mechanisms to access the data. This is how the very specialized data managers such as document management systems, geographic information systems, or CAD/CAM systems may make their specialized features available to SQL applications. 5.1 SQL'92 features The SQL conformance levels defined in Section 4 of this specification identify a broad level of capability for conforming SQL/ERI servers. In addition, it is sometimes desirable to identify other features specified in the SQL'92 standard [8] as either offered by a product or required by a specific procurement. Section 14 of FIPS SQL [3] identifies 83 features of the SQL'92 standard beyond the Entry SQL requirements. An SQL/ERI Server could identify which features are supported beyond its declared level of service by implementing the SQL_FEATURES table as specified in Section 15 of FIPS SQL. Implementation of the SQL_FEATURES table is a requirement for all SQL/ERI Servers that claim a base level of SQL data manipulation language support at the Intermediate DML level or above. A procurement could also use this list to identify, unambiguously, those SQL features beyond the identified conformance level that are either required or desirable for that procurement. FIPS SQL also identifies default minimum requirements for the precision, size, or number of occurrences of database constructs (see section 16.6 of [3]). Unless otherwise specified in a procurement, the Entry Value sizing limits apply to all Entry SQL or Transitional SQL features and the Intermediate Value sizing limits apply to all Intermediate SQL or Full SQL features. An SQL/ERI Server could identify its own sizing limits by implementing the SQL_SIZING table as specified in Section 15 of FIPS SQL. Implementation of the SQL_SIZING table is a requirement for all SQL/ERI Servers that claim a base level of SQL data manipulation language support at the Intermediate DML level or above. A procurement is responsible for identifying its own sizing limits on all required features, but in the absence of an explicit declaration, the default minimum limits apply for that procurement. 5.2 Stored procedures and callable routines (SQL/PSM) An emerging new part of the SQL standard for Persistent Stored Modules (SQL/PSM) was registered as an ISO/IEC Committee Draft (CD) in March 1994 (see [11]). Although this specification will not reach formal standardization until at least late 1995 or early 1996, it should be sufficiently complete and stable to justify its careful use in procurements before that date. The intent of SQL/PSM is to make SQL a computationally complete programming language with variables, procedures, functions, and flow-of-control statements. In particular, packages of SQL procedures called modules may be stored at server nodes in a communications network with only a procedure call needed to invoke a desired action. The advantage is that modules may be stored persistently in the database, subject to SQL access control and integrity management, thereby allowing a reduction of comunications overhead along with optimization and other performance efficiencies at the server site. A major advantage of the SQL/PSM is that non-SQL data managers will be able to present their special features to applications in an SQL environment as callable SQL functions or procedures. The only requirement is that the calling syntax be standard SQL syntax as far as the calling application is concerned and that the parameters be defined as SQL data types. The content body of the functions and procedures may not be visible to the end user and thus may be implementation- dependent. The SQL/ERI Server can use the SQL Information Schema catalog tables to make known to the users exactly which functions and procedures are available for their use. 5.3 SQL multimedia class library (SQL/MM) A new ISO/IEC international standardization project for development of an SQL class library for multimedia applications was approved in early 1993. This new standardization activity, named SQL Multimedia (SQL/MM), will specify packages of SQL abstract data type (ADT) definitions using the facilities for ADT specification and invocation provided in the emerging SQL3 specification [12]. SQL/MM intends to standardize class libraries for science and engineering, full-text and document processing, and methods for the management of multimedia objects such as image, sound, animation, music, and video. It will likely provide an SQL language binding for multimedia objects defined by other JTC1 standardization bodies (e.g. SC18 for documents, SC24 for images, and SC29 for photographs and motion pictures). The project plan for SQL/MM indicates that it will be a multi-part standard consisting of an evolving number of parts. Part 1 will be a Framework that specifies how the other parts are to be constructed. The Framework also specifies General Purpose Facilities such as numeric functions, complex numbers, or data structures that are common to multiple other parts of the SQL/MM standard. Each of the other parts will be devoted to a specific SQL application package. Even though this project is just getting started, initial base documents exist for Part 1: Framework and General Purpose Facilities, Part 2: Full Text, and Part 3: Spatial (see [13]). As the different components of the SQL/MM specification reach CD and DIS stability, an SQL/ERI Server could claim support for specific features. 5.4 Abstract data types and methods The emerging SQL3 specification contains a number of data abstraction facilities. For example, see Clauses 4.11, "Abstract data types", 11.47, "", and 11.48, "", of the March, 1994, version of [12]. If data abstraction is an inherent requirement of an SQL/ERI Server, then it could define its Abstract Data Types and make them available to SQL applications using these definitional mechanisms. As the data abstraction facilities of the SQL3 specification reach CD and DIS stability, an SQL/ERI Server could use them with more confidence to permanently define its abstract data types and methods. A major advantage of these ADT features combined with the SQL/PSM identified above is that non-SQL data managers will be able to present their application- specific ADTs to applications in an SQL environment. The signature of such ADTs would be available in the Information Schema provided by each SQL/ERI Server, and the special methods on each ADT would be callable as SQL functions or procedures. The only requirement is that the calling syntax be standard SQL syntax as far as the calling application is concerned and that the parameters be SQL data types or known ADT types provided by the SQL/ERI Server. The abstract data type body of usable ADTs may not be visible to the end user and thus may be implementation- dependent. The SQL/ERI Server can use the SQL Information Schema catalog tables to make known to the users exactly which ADTs and associated methods are available for their use. 5.5 Object data management The emerging SQL3 specification contains facilities for defining and referencing object identifiers. For example, see Clauses 4.9 "Object identifier type", 11.48 "" WITH OID option, and 6.5 "", of the March, 1994, version of [12]. If object identity is an inherent requirement of an SQL/ERI Server, then it could define its Abstract Data Types with Object Identifiers (OID) and make them available to SQL applications using these definitional mechanisms. In this manner, object database management systems and specialized object repositories could make their features and facilities available to an SQL environment. As the different OID facilities of the SQL3 specification reach CD and DIS stability, an object-oriented SQL/ERI Server could use them with more confidence to permanently define its objects and their methods. 6. SQL Binding Alternatives The SQL'92 standard [8] specifies three different binding styles: Module, Embedded SQL, and Direct Invocation. The RDA'93 standard [9] specifies protocol interfaces for RDA clients and RDA servers. An emerging standard for an SQL call level interface (SQL/CLI) is under a rapid development path in ISO/IEC with Draft International Standard (DIS) status expected sometime during calendar year 1994. The following subsections define how an SQL/ERI Server might claim conformance to an SQL/ERI profile using one of these interface alternatives. 6.1 SQL Module processor An SQL/ERI Server may provide a Module binding style to application programs. If a user creates a according to the Format and Syntax Rules of Clause 12, "Module", of the SQL'92 standard, and if the satisfies the restrictions of a given level of SQL for a given programming language, then the SQL/ERI Server shall process that as an input text file and shall produce a binary output file that can be linked to the compiled output of any application program written in the programming language identified by the of the . The output file shall abide by whatever restrictions are required for cross-language procedure calls by the operating system and processing platform for which Module binding support is claimed. 6.2 Embedded SQL preprocessor An SQL/ERI Server may provide an Embedded SQL binding style to application programs. If a user creates an according to the Format and Syntax Rules of Clause 13, "Embedded SQL", of the SQL'92 standard, and if the satisfies the restrictions of a given level of SQL for a given programming language, then the SQL/ERI Server shall process that according to the General Rules and other requirements specified in the SQL'92 standard. An SQL/ERI Server may compile the entire to produce an executable file, or it may produce a conforming program, P, written in the language identified by the of the and an implicit (maybe not actual) module, M, both as specified by Syntax Rules 13 through 15 of Subclause 19.1, "", of the SQL'92 standard. If the user compiles program P with a standard conforming programming language compiler designed for the operating system and processing platform environment for which Embedded SQL support is claimed, then the compiled version of P and an implementor-dependent version of M shall be linkable in that processing environment to produce an executable file that executes correctly according to the SQL'92 standard. 6.3 Direct invocation of SQL statements An SQL/ERI Server may provide a Direct Invocation style of binding according to the requirements of Clause 20, "Direct invocation of SQL", of the SQL'92 standard. This binding style is very difficult to write conformance tests for because there is no "standard" way to capture data returned as the result of a query. Instead, conformance to this binding style requires a subjective evaluation of the results by a human user. For this reason, among others, the FIPS SQL standard [3] does not recognize Direct Invocation as the sole conformance alternative of an SQL implementation. Instead, it allows Interactive Direct SQL as a conformance option in addition to a Module or Embedded binding style. For an SQL/ERI Server it makes more sense to recognize the Direct Invocation binding style as a viable conformance alternative. There are many situations, e.g. electronic bulletin boards, where a user may desire to send an SQL statement to an SQL/ERI Server and have the data results displayed on a screen or dumped into a human readable text file. For these reasons, the SQL/ERI Server profiles specified in Section 7 below do recognize Direct Invocation as a valid conforming interface style. If a user creates a according to the Format and Syntax Rules of Clause 20, "Direct invocation of SQL", of the SQL'92 standard, and if the satisfies the restrictions of a given level of SQL, then the SQL/ERI Server shall process that as input text and shall display the results, if any, in a human readable form on some sort of display device. For SQL/ERI Servers providing access to multimedia data, the display device may include a sound system, motion picture display, or even some form of virtual reality. The only real requirement is that a reasonable conformance testing authority be able to decide, subjectively, whether or not the was properly executed. If a is a that returns only character string or numeric data in the result rows and columns, then the SQL/ERI Server shall provide a user option to redirect the output of the query into a human readable text file. In this context, human readable means formatted so that a reasonable conformance testing authority can readily distinguish rows and columns and easily read the data. All numeric data returned as text shall be in the form of a valid SQL , unless some explicit user action results in its being cast into some other form, e.g. Money with currency symbols attached. The SQL/ERI Server may use General Rules 5a, 5b, 6a, and 6b, of Subclause 6.10, "", of the SQL'92 standard, for additional guidance in casting numeric values into numeric literals. Other requirements for the Direct Invocation binding style are as follows: if a statement raises an exception condition, then the SQL/ERI Server shall display a message indicating that the statement failed, giving a textual description of the failure; if a statement raises a completion condition that is a "warning" or "no data", then the SQL/ERI Server shall display a message indicating that the statement completed, giving a textual description of the "warning" or "no data"; an SQL/ERI Server that supports null values shall provide some implementation- defined symbol for displaying null values and, for character string values, this symbol must be distinguishable from a value of all s. Preferably, the SQL/ERI Server will provide an implementor-defined method for a user to specify how null values shall be displayed, e.g. SET NULL AS '*'; however, this SET feature is not really needed if, instead, the SQL/ERI Server supports the NULL alternative in the of the . 6.4 SQL call level interface (SQL/CLI) An SQL/ERI Server may provide an SQL Call Level Interface binding style according to the requirements of the emerging standard for SQL/CLI [10]. We expect the SQL/CLI specification to reach a stable state in the ISO/IEC standardization process during calendar year 1994, in time for any future NIST testing of SQL/ERI Server profiles. The call level interface is a requirement for third-party software developers who produce "shrink-wrapped" software for use on personal computers and workstations. They do not wish to use a Module processor or an Embedded SQL preprocessor binding style because they do not wish to distribute any source code with the products they sell to individual users. Instead they desire a services call interface to SQL data repositories that can be invoked from the calling environment provided by the host operating system. The calls to the SQL data repository can then be embedded in the object code just like calls to any other system service. The Call Level Interface is an alternative mechanism for executing SQL statements. Reference [10] states that the SQL/CLI consists of a number of routines that: -- Allocate and deallocate resources. --Control connections to SQL-servers. --Execute SQL statements using mechanisms similar to Dynamic SQL. --Obtain diagnostic information. --Control transaction termination. The AllocHandle routine allocates resources to manage an SQL-environment, an SQL-connection, a CLI descriptor area, or SQL-statement processing. An SQL- connection is allocated in the context of an allocated SQL-environment, and a CLI descriptor descriptor area and an SQL-statement are allocated in the context of an allocated SQL-connection. The FreeHandle routine deallocates a specified resource. The ReleaseEnv routine is used to deallocate all the allocated SQL- connections within a specified allocated SQL-environment. Each allocated SQL-environment has an attribute that determines whether output character strings are null terminated by the implementation. The application can set the value of this attribute by using the routine SetEnvAttr and can retrieve the current value of the attribute by using the routine GetEnvAttr. The Connect routine establishes an SQL-connection. The Disconnect routine terminates an established SQL-connection. Switching between established SQL connections occurs automatically whenever the application switches processing to a dormant SQL-connection. The ExecDirect routine is used for a one-time execution of an SQL-statement. The Prepare routine is used to prepare an SQL-statement for subsequent execution using the Execute routine. In each case, the executed SQL-statement may contain dynamic parameters. The interface for a description of dynamic parameters, dynamic parameter values, the resultant columns of a dynamic select statement, and the target specifications for the resultant columns is a CLI descriptor area. A CLI descriptor area for each type of interface is automatically allocated when an SQL-statement is allocated. The application may allocate additional CLI descriptor areas and nominate them for use as the interface for the description of dynamic parameter values or the description of target specifications by using the routine SetStmtAttr. The application can determine the handle value of the CLI descriptor area currently being used for a specific interface by using the routine GetStmtAttr. The GetDescField and GetDescRec routines enable information to be retrieved from a CLI descriptor area. The CopyDesc routine enables the contents of a CLI descriptor area to be copied to another CLI descriptor area. When a dynamic select statement is prepared or executed immediately, a description of the resultant columns is automatically provided in the applicable CLI descriptor area. In this case, the application may additionally retrieve information by using the DescribeCol routine to obtain a description of a single resultant column and by using the NumResultCols routine to obtain a count of the number of resultant columns. The application sets values in the CLI descriptor area for the description of the corresponding target specifications either explicitly using the routines SetDescField and SetDescRec or implicitly using the routine BindCol. When an SQL-statement is prepared or executed immediately, a description of the dynamic parameters is automatically provided in the applicable CLI descriptor area if this facility is supported by the current SQL-connection. An attribute associated with the allocated SQL-connection indicates whether this facility is supported. The value of the attribute may be retrieved using the routine GetConnectAttr. The application sets values in the CLI descriptor area for the description of dynamic parameter values and, regardless of whether automatic population is supported, in the CLI descriptor area for the description of dynamic parameters either explicitly using the routines SetDescField and SetDescRec or implicitly using the routine BindParam. When a dynamic select statement is executed, a cursor is implicitly declared and opened. The cursor name can be supplied by the application by using the routine SetCursorName. If a cursor name is not supplied by the application, an implementation-dependent cursor name is generated. The same cursor name is used for each implicit cursor within a single allocated SQL-statement. The cursor name can be retrieved by using the GetCursorName routine. The Fetch routine is used to position an open cursor on the next row and retrieve the values of bound columns for that row. A bound column is one whose target specification in the specified CLI descriptor area defines a location for the target value. Values for unbound columns can be individually retrieved by using the GetCol routine. The GetCol routine also enables the values of character string columns to be retrieved piece by piece. The current row of a cursor can be deleted or updated by executing a preparable dynamic delete statement or a preparable dynamic update statement, respectively, for that cursor under a different allocated SQL-statement to the one under which the cursor was opened. The CloseCursor routine enables a cursor to be closed. The GetDiagField and GetDiagRec routines obtain diagnostic information about the most recent routine operating on a particular resource. An SQL-transaction is terminated by using the EndTran routine. The Cancel routine is used to cancel the execution of a concurrently executing SQL/CLI routine. The CLI ExecDirect routine and the CLI Prepare routine each support an input character string parameter identified as StatementText. If P is the value of StatementText, then P shall satisfy the following restrictions: 1.P shall conform to the Format, Syntax Rules, and Access Rules for a as specified in Subclause 17.6, "", of the SQL'92 standard. 2.P shall not be a or a . 3.P shall abide by the Leveling Rules of the level of SQL support claimed by the SQL/ERI Server. The SQL/CLI specification is intended to support CLI routines embedded into both pointer-based programming languages and non-pointer-based programming languages. In particular, the Syntax Rules of specified in Subclause 5.2 of [10] indiate that CLI routines may be embedded into any one of the following standard programming languages: Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I. An SQL/ERI Server will indicate which of those languages it supports. 6.5 RDA/SQL-Server interface An SQL/ERI Server may provide an RDA/SQL-Server protocol interface according to the protocols defined in the RDA'93 standard [9]. The RDA protocols allow communication and interoperability among conforming RDA processors in an OSI communications network. Many vendors are also supporting the RDA protocols in a TCP/IP communications network using agreements specified by the NIST Opens Systems Environment Implementors Workshop (OIW) for RDA accessibility using Internet RFC 1006 for upper layer OSI interface to Internet protocols. This SQL/ERI profile specification allows an SQL/ERI Server to claim conformance to the RDA/SQL-Server interface over an arbitrary communications network. If an application program, acting as an RDA client, is able to form an association with an SQL/ERI Server and communicate thereafter using RDA protocols subject to the implementor agreements specified by the Open Systems Environment Implementor's Workshop (e.g. in [16]), then the SQL/ERI Server may claim conformance to the RDA/SQL-Server interface style. Reference [9] describes the services of the RDA standard in terms of an RDA Client, and RDA Server, and an RDA Service as follows: An RDA client is an application-process, within an open system, that requests database services from another application-process called a database server. A database server is an application-process, within the same or another open system, that supplies database storage facilities and provides, through OSI communication, database services to RDA clients. An RDA client and a database server communicate by means of the RDA Service, supported by an RDA service- provider. The part of the database server that uses the RDA service-provider to communicate with an RDA client is called an RDA server. The RDA client has the ability to initiate RDA service requests, while the RDA server can only issues RDA service responses to reply to such requests. A data resource is a named collection of data and/or capabilities on the database server and known to both the RDA client and the RDA server. The meaning of the data content and capabilities of a data resource depend upon the application of RDA, which is determined by each RDA specialization standard (e.g. the SQL specialization). The RDA client opens a data resource in order to gain access to the data content or capabilities of that resource through Database Language services (e.g. SQL). Data resources may be nested, with subordinate data resources grouped within their parent data resource. The RDA client is required to open a parent data resource before it can open subordinate data resources. An RDA transaction is a logically complete unit of processing as determined by the RDA client. Execution during an RDA transaction of a sequence of database access services that change data resources enables the set of changes to be handled as an atomic unit. When the RDA transaction is terminated, either the whole set of changes are applied to the data resources or no changes are applied. The RDA client requests termination of an RDA transaction by requesting the RDA server either to commit or to roll back the complete set of changes of that transaction. Changes made to the data content of data resources during an RDA transaction are not made available to other RDA clients until that RDA transaction is terminated at the RDA server. RDA a choice of two application-contexts for managing RDA transactions: 1) a basic application- context for one-phase commitment, and 2) a TP application-context for two-phase commitment. The RDA protocol for the basic application-context is completely specified in the RDA standard, whereas the protocol for the TP application context is dependent upon the ISO/IEC Distributed Transaction Processing standard (ISO/IEC 10026). An RDA operation models a request by an RDA client that is transferred to an RDA server for processing. RDA operations enable an RDA client to request any of five types of RDA services: a) RDA Dialogue Management services, to start and end RDA dialogues; b) RDA Transaction Management services, to start and end RDA transactions; c) RDA Control services, to report the status or cancel existing operations; d) Resource Handing services, to enable or disable access by RDA clients to data resources; e) Database Language services, to access and modify data resources. An RDA client may request RDA operations without waiting for the results of previously requested RDA operations. Thus an RDA server may have several RDA operations outstanding for a particular RDA dialogue. An RDA dialogue is a cooperative relationship between and RDA client and an RDA server. The RDA client initilizes the RDA dialogue and requests RDA operations that are to be performed by the RDA server. An RDA dialogue is uniquely identified within the scope of the OSI environment, and all RDA operations occur within the bounds of an RDA dialogue. An RDA dialogue can exist only in the context of an established application-association, and ceases to exist if the association is released. A failed RDA dialogue cannot be recovered; the process of recovery after a failure is a local matter beyond the scope of the RDA 1993 standard, and recovery actions outside the RDA service-provider may be necessary. In the event of dialogue failure, it is a requirement that all changes made to data resources by any RDA transaction that is not already terminating when RDA dialogue failure occurs be rolled back by the database server during its recovery process. If an RDA dialogue is terminating when RDA dialogue failure occurs, then it may either be committed or rolled back. The NIST OSE Implementor's Workshop (OIW) has specified implementation agreements for the Basic Application Context of the RDA'93 standard [9], with profiles for: Immediate Execution, Stored Execution, Status, and Cancel. Future work is in progress by the OIW to specify corresponding profiles for the Transaction Processing (TP) Application Context of the RDA'93 standard. For the purpose of the SQL/ERI Server profiles specified in this document, support for the RDA/SQL- Server interface requires support as an RDA Server for the Immediate Execution profile of the Basic Application Context as specified in [16], with the ability to respond to SQL statements at the level of support for SQL language claimed by the SQL/ERI Server. The other profiles of the Basic Application Context defined in [16], and the TP Application Context, are optional enhancements to this basic requirement as follows: RDA Stored Execution. Support for the basic requirements specified above and, in addition, support for the RDA Stored Execution Functional Unit as specified in the RDA'93 standard and with implementor agreements for the Stored Execution profile as specified in [16]. RDA Status. Support for the basic requirements specified above and, in addition, support for the RDA Status Functional Unit as specified in the RDA'93 standard and with implementor agreements for the Status profile as specified in [16]. RDA Cancel. Support for the basic requirements specified above and, in addition, support for the RDA Cancel Functional Unit as specified in the RDA'93 standard and with implementor agreements for the Cancel profile as specified in [16]. RDA TP Application Context. Support for the basic requirements specified above and, in addition, support for the RDA SQL TP Application Context as specified in the RDA'93 standard, and dependent upon ISO/IEC 10026 (Distributed Transaction Processing), and with implementor agreements for Distributed Transaction Processing as specified in [16]. 7. SQL/ERI Server Profiles This section specifies two general-purpose functional profiles for partial SQL language support that an implementation may claim conformance to, as follows: - SQL/ERI Read-Only Server - SQL/ERI Read-Write Server Each general-purpose profile has a number of "level of service" alternatives for data manipulation, schema definiton, transaction management, and binding style. If an implementation conforms to any one of these profiles, then it may claim to be a conforming SQL/ERI Server. Because many of the alternatives in these profiles identify a proper subset of full-function SQL requirements, conformance to any one of them does not imply conformance to the standard for Database Language SQL [8]. These profiles are intended for use by customers and vendors of products that claim only partial support of an SQL language interface to their data repository. Any implementation claiming conformace to one of the SQL/ERI Server profiles shall provide a written public statement responding to the ten profile items identified in the following paragraphs. The implementation requirements of each response is given in subsections specific to Read-Only or Read-Write Servers. 1.A base level of SQL data manipulation language (DML) support, by choosing exactly one of the following DML alternatives. Minimal DML Entry DML Transitional DML Intermediate DML Full DML 2.A base level of SQL schema definition language (SDL) support, by choosing exactly one of the following SDL alternatives. None Minimal SDL Entry SDL Transitional SDL Intermediate SDL Full SDL 3.A base level of SQL transaction management support, by choosing exactly one of the following transaction management alternatives. None Commit-Rollback Transaction Mode Transaction Isolation Transaction Diagnostics Constraints Note: The alternatives for SQL transaction management support are nested. Support for any one of them implies support for all those listed above it. The three alternatives for Transaction Mode, Transaction Isolation, and Transaction Diagnostics support , , and , respectively, in the SQL'92 , and the Constraints alternative supports the SQL'92 . 4.A default isolation level for SQL transaction management, by choosing exactly one of the following default isolation level alternatives. Read Uncommitted Read Committed Repeatable Read Serializable Note: If the default isolation level is anything other than Serializable, and if other concurrent users are able to update the database, then read statements may be subject to "dirty read", "non-repeatable read", or "phantom" rows (see Subclause 4.28, "SQL-transactions", of the SQL'92 standard). Even a read-only profile is subject to these phenomena if other concurrent users (not through that profile) can update the database. 5.Which binding styles are supported, by choosing one or more of the following binding style alternatives. Module Embedded SQL Direct Invocation SQL/CLI RDA/SQL-Server Note: It is expected that the SQL/CLI binding style will be the most popular choice for SQL/ERI products within a single local client/server environment and that the Direct Invocation or RDA/SQL-Server binding styles will be the most popular when the server data repository is an isolated node in a wide area client/server environment. 6.For each of the Module, Embedded SQL, or SQL/CLI binding styles chosen, which programming language interfaces are supported, by choosing one or more of the following programming language alternatives. Ada C COBOL Fortran MUMPS Pascal PL/I SAMeDL Note: The Direct Invocation and RDA/SQL-Server binding styles do not require or provide a programming language interface. The preferred language interfaces for SQL/CLI are C and/or COBOL. SAMeDL is an alternative only for the Module binding style (see [21]). 7.Whether or not SQL session management is supported, by specifying exactly one of the following session management alternatives. Session Supported Session Not Supported Note: SQL session management is defined in Clause 16, "Session management", of SQL'92. 8.Which optional extensions are supported, by choosing zero or more of the following optional extensions. SQL'92 features Stored procedures and callable routines (SQL/PSM) SQL multimedia class library (SQL/MM) ADTs and methods Object data management Note: If SQL'92 features is chosen, then the implementation shall support the SQL_FEATURES table as specified in Section 15 of FIPS SQL (see [3]); if SQL/PSM is chosen, then the implementation shall support all requirements of the then current SQL/PSM specification (see [11]); if SQL/MM is chosen, then the implementation shall point to the then current SQL/MM specification (see [13]) and explicitly indicate which Parts, and which conformance alternatives within each Part, are supported; if ADTs and methods is chosen, then the implementation shall support the appropriate Abstract Data Type clauses in the then current SQL3 specification (see Section 5.4 above and [12]); if Object data management is chosen, then the implementation shall support the appropriate object management clauses in the then current SQL3 specification (see Section 5.5 above and [12]). 9.If the RDA/SQL-Server binding style is chosen, then which underlying communications protocols are supported, by choosing one or more of the following. Minimal OSI (MOSI) -- see new OIW 1994 agreements Full Stack OSI -- see [16] for 1992 OIW stable agreements IETF RFC 1006 -- see unpublished NIST RDA TestBed Agreements Proprietary -- give name & version of commercial LAN product Note: All of the above depend upon the International Standard for Remote Database Access (RDA) [9] in their upper layers; however, they may differ in their directory services and in their services for making an association at the application layer and in how that association is propagated through to the transport and physical layers. 10. If the RDA/SQL-Server binding style is chosen, then which RDA options are supported, by choosing zero or more of the following (see Section 6.5 above). RDA Stored Execution RDA Status RDA Cancel RDA TP Application Context 7.1 SQL/ERI Read-Only Server This profile specifies a read-only interface to a data repository. It does not include support for any of the Schema Definition or Schema Manipulation SQL language elements specified in Clause 11 of the SQL'92 standard, or for any of the SQL data change statements, i.e. Insert, Update, or Delete, specified in Clause 13. It is expected that the level of support specified for SQL schema definition language is "None". Depending upon the various base level attributes specified, this profile may have Information Schema requirements that differ from those specified in SQL'92 [8] or FIPS SQL [3]. Schema Definition Rules 1.The SQL/ERI Read-Only Server profile assumes that all schema objects are owned by a user different from the user accessing the repository through this profile, and that appropriate privileges have been granted to all accessing users. If the RDA/SQL-Server binding style is identified, then users are made known to the system using the NIST OIW RDA Testbed implementor agreements; otherwise, as with the SQL'92 standard, the particular method by which users are made known to the system is implementation-defined. 2.If the level of SQL data manipulation language support claimed for the SQL/ERI Read-Only Server profile is Minimal DML, Entry DML, or Transitional DML, then the implicit schema definition may contain some
s, or various s, that are not visible to the user but whose existence may effect the semantics of certain statements. 3.If the level of SQL data manipulation language support claimed for the SQL/ERI Read-Only Server profile is Minimal DML or Entry DML, and if a table with table name TN is visible in the Information Schema TABLES view for a user with user name UN, then one of the following s, executed by the owner of table TN, is implicit: GRANT SELECT ON TABLE TN TO UN, or GRANT SELECT ON TABLE TN TO PUBLIC It shouldn't make any difference to a read-only user which of these statements is implicit, so the choice is implementation-dependent. 4.If the level of SQL data manipulation language support claimed for the SQL/ERI Read-Only Server profile is Transitional DML, Intermediate DML, or Full DML, then information about schema objects, privileges, and constraints are made visible to potential users through the Information Schema views, subject to the Information Schema Rules specified below. Data Manipulation Rules 1.If the Module, Embedded SQL, or RDA/SQL-Server binding styles are specified, then the SQL/ERI Read-Only Server profile requires support for the following SQL statements, as specified in Clause 13, "Data Manipulation", in the SQL'92 standard, with any restrictions specified by the given level of SQL data manipulation language support and subject to other rules specified in this profile.
s, or various s, that are not visible to the user but whose existence may effect the semantics of certain statements. 3.Information about schema objects, privileges, and constraints are made visible to potential users through the Information Schema views, subject to the Information Schema Rules specified below. 4.If the level of SQL schema definition language support is different from "None", then Case: a. If the Module, Embedded SQL, or RDA/SQL-Server binding styles are specified, then the SQL/ERI Read-Write Server profile requires support for all of the SQL data definition and manipulation statements, as specified in Clause 11, "Schema definition and manipulation", of the SQL'92 standard, with any restrictions specified by the given level of SQL schema definition language support and subject to other rules specified in this profile. b. If the Direct Invocation binding style is specified, then the SQL/ERI Read-Write Server profile requires support for the following s listed in Clause 20, "Direct invocation of SQL", in the SQL'92 standard, with any restrictions specified by the given level of SQL schema definition language support and subject to other rules specified in this profile: c. If the SQL/CLI binding style is specified, then the SQL/ERI Read-Write Server profile requires support for the following s listed in Subclause 17.6 of the SQL'92 standard, with any restrictions specified by the given level of SQL schema defintion language support and subject to other rules specified in this profile: Data Manipulation Rules 1.If the Module, Embedded SQL, or RDA/SQL-Server binding styles are specified, then the SQL/ERI Read-Write Server profile requires support for the following SQL statements, as specified in Clause 13, "Data Manipulation", in the SQL'92 standard, with any restrictions specified by the given level of SQL data manipulation language support and subject to other rules specified in this profile.