The National Institute of Standards and Technology offers a formal testing service for SQL implementations in support of its federally mandated program of Federal Information Processing Standards (FIPS). The NIST SQL Test Suite was first developed in 1988 to support testing of FIPS PUB 127, the standard for Database Language SQL. This test suite has evolved over the years to support new interfaces and other enhancements to the SQL standard as they are adopted by national and international SQL standardization groups. Version 4.0 of the NIST SQL Test Suite, available since June 1993, contains tests for the Entry SQL level of the 1992 standard; future versions will test other levels of conformance as well as new interface standards such as the Call Level Interface (CLI) and Remote Database Access (RDA). It is expected that this test suite will be modified as needed to enable conformance testing of the various SQL/ERI Server profiles specified herein and that validation reports for tested SQL/ERI products will be published quarterly in the NIST Validated Products List. The following sections discuss features of the NIST SQL Test Suite and how it might be modified for future validation of SQL/ERI products.

9.1 NIST SQL Test Suite

The purpose of the NIST SQL Test Suite is to help evaluate conformance of SQL implementations to mandatory requirements of FIPS PUB 127. This test suite is used as part of the formal testing service for SQL that issues Certificates of Validation for tested products passing all required tests. A Validation Summary Report is issued for all implementations tested. A Validation Summary Report documents, to the extent tested, the implementation's conformance to FIPS PUB 127-2. NIST publishes a quarterly register, Validated Products List, showing SQL implementations which hold current Certificates of Validation and registered Validation Summary Reports.

The NIST SQL Test Suite was first made available to the public in August 1988 as Version 1.1, and included tests for three programming languages: COBOL, FORTRAN, and C. In May 1989 the test suite was enlarged and released as Version 1.2, and included tests for additional SQL features, as well as tests for Embedded SQL Pascal and a Pascal interface to Module Language SQL. Version 2.0, completed about a year later, contained additional tests as well as the support system (software utilities) to administer the validation process. Continuing standardization work for SQL resulted in a revised SQL standard, ANSI X3.135-1989, published December 1989. This revised standard contained integrity enhancements for SQL, including referential integrity, default values for columns, and check clauses. FIPS PUB 127 was revised to specify these new integrity features as an optional module which federal agencies could either require or (by default) not require in a procurement. Version 2.0 of the test suite also contained a set of tests to validate conformance to this optional module. In the same time frame, ANSI X3.168-1989 standardized the embedding of SQL in programming languages (Ada, C, COBOL, FORTRAN, Pascal and PL/I). The first release of the NIST SQL Test Suite contained tests for Embedded SQL, in anticipation of this standard. Since numerous implementations of Embedded SQL already existed, prior to standardization, NIST hoped that the early availability of tests for that interface would hasten the conformance of implementations to the revised FIPS PUB 127-1. Version 3.0 provided test suites for Ada bindings to SQL and also tests for the errata in the SQL Information Bulletin SQLIB-1. ANSI X3.135-1992, the 1992 revision of the SQL standard, represents a major enhancement in SQL functionality. Conformance to FIPS PUB 127-2, Entry SQL, requires additional capabilities from an SQL implementation beyond those required for minimal conformance to FIPS PUB 127-1. The Integrity Enhancement Feature is now mandatory. Support for the following additional features is now required: SQLSTATE status codes, delimited identifiers, renaming columns, commas in parameter lists, SQL Errata against ANSI X3.135- 1989 (approved after publication of SQLIB-1). Version 4.0 of the NIST SQL Test Suite provides tests for all the features in Entry SQL. Version 2.0 was used in the formal testing service offered by NIST which opened in April 1990. Version 3.0 became the official version of the test suite in July 1992, and Version 4.0 became the official version in January 1994.

The NIST SQL Test Suite provides ten programming language test suite types: Embedded (preprocessor) SQL Ada, Embedded SQL C, Embedded SQL COBOL, Embedded SQL FORTRAN, Embedded SQL Pascal, Module Language Ada, Module Language C, Module Language COBOL, Module Language FORTRAN, and Module Language Pascal. NIST also provides an Interactive Direct SQL test suite type to test interactive invocation of SQL statements as defined in FIPS 127-2. The original test programs were developed in Embedded (preprocessor) SQL for the C language. The design objective for the test programs was to provide a simple test for every general rule in the standard and to cover fully all SQL syntax. Ada, COBOL, FORTRAN, and Pascal test routines, as well as module language test routines, were generated by software (written by NIST) from the original Embedded SQL C language. The original Embedded SQL C Language tests are very simple, using only a carefully restricted subset of the C Language. Otherwise, it would be technically infeasible to translate these tests into the other programming languages. The Interactive Direct SQL test files were created by extracting SQL statements from the Embedded SQL C programs. Test cases were reworked to avoid reference to cursors and host variables. The resulting text files were annotated with comments describing the test and the expected results required for a "pass."

Each test is designed to be short and simple, exercising as little of the host language as possible. The host language compiler should be validated separately to ensure that it conforms to the applicable standards. The use of complex host language code in SQL conformance programs would make tests difficult to understand and would make it more difficult to resolve questions of interpretation of the SQL standard. Most of the tests involve 3 small tables containing a total of 23 rows. The data types of columns in these tables are either character string or integer, so the tests will work across all these programming languages. Other tables are used to test approximate numeric and scaled exact numeric data types. Additional tests have been written to cover the data type variables specific to each language. Each program contains one or more tests. Although allowing only one test per program would simplify the evaluation of implementations with a high degree of nonconformity, it would impose additional overhead on implementations with a high degree of conformity. The tests within a program are intended to be independent so any one test may be removed without affecting the remaining tests.

Each test is self-evaluating; i.e., each test is written with knowledge of the data in the database and the correct response for a specific SQL statement. Each test checks for correct execution of the SQL statement and then inserts into the reporting table, TESTREPORT, a "pass" or "fail" value for that test. After all the test programs have executed, a summary of test results is produced automatically by another program which reads TESTREPORT. As each test is executed, a description of the test is printed on standard output (the screen) along with appropriate data values and the test result. This output should be considered as a "log" of the test programs. It is intended to assist in debugging and in analyzing nonconformities. This output is not needed to produce the automated conformance analysis of the SQL test suite.

These tests are not designed to debug DBMS software; however, they may help identify problem areas. The use of small tables does not challenge the buffer-management strategy of an implementation. In addition, the frequent use of ROLLBACK (after tests which modify tables), to restore the base data to its original state (and thus simplify testing), limits testing of the COMMIT path. Since the SQL standard does not address physical database design, it is likely that schema definition and DML tests will be run in the simplest manner possible, without optimization.

The test suite includes a few tests for the "SQL Flagger" option specified in Section 10.d of FIPS PUB 127-2. These tests contain extensions to the SQL standard. In general, if an SQL implementation supports these extensions, it must be able to flag the extensions with warning messages. These tests are to be run with the flagging turned off and then, if successful, rerun with the flagging turned on. Test evaluation for the SQL Flagger is subjective, based upon examination of any warnings which are printed (or displayed on the screen) when extensions to SQL are used. The "SQL Flagger" tests are very limited. They are intended to demonstrate the existence and style of monitoring provided by a vendor. They do not systematically attempt to detect SQL extensions which are not flagged. For Entry SQL, standard features which are required only by higher levels (beyond Entry) should all be flagged along with nonstandard features. It is desirable, but not required, that the flagging message indicate the exact status (Transitional SQL, Intermediate SQL, Full SQL, nonstandard extension) of the flagged feature.

The test suite has a set of programs to test the specifications in FIPS PUB 127-2, Section 16.6, "Sizing for database constructs." These minimum specifications for the precision, size, or number of occurrences of database constructs are contained (by default) in procurements which do not provide alternate specifications. Reporting of the FIPS sizing tests is separate from reporting on other tests. FIPS sizing tests are not technically considered conformance tests, and passing these tests is not required for a Certificate of Validation for FIPS 127-2. Utility programs are included to make global and program-specific changes in a controlled and systematic manner and to document those changes in the automated report.

The test suite contains additional tests to help evaluate conformance to: (1) the minimum sizing parameters for database constructs specified in FIPS PUB 127-2, Section 16.6, (2) the flagging of extensions, specified in FIPS PUB 127-2, Section 10.d, SQL Flagger, and (3) Interactive Direct SQL, as specified in FIPS PUB 127-2 Section 16.5.

The test suite contains ten different programming language test suite types. An SQL implementation claiming conformance to FIPS PUB 127-2 for a particular SQL interface; for example, Embedded SQL COBOL, should be tested with the appropriate test suite type. The programming language compiler used for testing should conform to the FIPS standard for that language and should be listed in the Validated Products List, which is published quarterly by NIST.

The intention of NIST is that this test suite should be used to help evaluate compliance of implementations of SQL to FIPS PUB 127- 2. A correct implementation of FIPS 127-2 requires the incorporation of the SQL standard document, ANSI X3.135-1992 (or ISO/IEC 9075:1992), into the design specifications for the SQL implementation. The SQL test suite then confirms that the standard has been interpreted and implemented correctly by the SQL supplier. The test suite is intended to be used, in conjunction with the SQL supplier's own independently-developed regression tests, to ensure a robust and internally consistent product. A quality SQL implementation is not achievable by simply "fixing the product" until it passes the tests.

It is important to recognize the limitations of this test suite and of any test suite. In particular, it would be incorrect for implementations to claim conformance to FIPS PUB 127-2 simply by virtue of correct performance of these tests. It is reasonable, however, for purposes of procurement, to substantiate claims of conformance to FIPS PUB 127-2 by demonstrating correct execution of these tests. Performance is recognized as a critical selection factor in many DBMS procurements. However, performance is not an issue for standards validation testing and is not measured by this test suite.

9.2 Testing SQL/ERI implementations

The NIST SQL Test Suite tests an entire level of the FIPS SQL standard and includes tests for schema definition, data manipulation, transaction management, and programming language interface at each level. Each of these areas are tested separately, so with a moderate effort it will be possible to modify the test programs to test the various levels of SDL and DML, the transaction mangement alternatives, and the different binding styles of the SQL/ERI Read-Write profiles. At the present time, the test suite does not distinguish between the Minimal SQL and Entry SQL levels, so more extensive modification will be needed to accommodate Minimal DML testing without losing completeness of coverage. The timescale and scope of this work is dependent upon available funding for NIST SQL Test Suite development.

In many cases, SQL/ERI profile testing will be accomplished by giving an implementation an SQL script of table definitions that will be used for DML testing. Any implementation claiming to support Minimal SDL or above will be able to read and implement the table definitions. If an implementation does not claim to support any schema definition, then they will be free to implement the schema definition in an implementation-dependent manner, so long as the result is "as if" the SQL script had been properly executed.

Most of the DML testing is accomplished over just three table definitions with a handful of rows in each table. If an SQL/ERI Server implementation supports Read-Write at the Minimal DML level or above, it should be able to load data into the three tables to satisfy the testing requirements of a large majority of the tests. If the SQL/ERI Server implementation is a Read-Only implementation, then it would be free to load the data in an implementation-dependent manner.

Since the test suite in the past did not make a distinction between Read-Write servers and Read-Only servers, a number of the tests have Update and Select statements mixed together in the same test. Often a test will update an existing column, or add a new row to a table, and then test to determine if a certain query condition is satisfied. For a Read-Only implementation, these tests will have to be substantially modified in order not to lose completeness of coverage. It is the intent of NIST to do this modification on a schedule consistent with the availability of funding and other resource requirements.