NISTIR 5998
User's Guide for the SQL Test Suite, Version 6.0
ABSTRACT: This manual describes the SQL Test Suite (Version 6.0) and the procedures needed
to test and evaluate an SQL implementation through a standard programming language interface or
through Interactive Direct SQL. This release of the SQL Test Suite was completed December 31,
1996, and includes new tests to validate the Intermediate level of conformance to 1992 SQL
standards. The SQL Test Suite, Version 6.0, was developed jointly by the U.S. National Institute
of Standards and Technology (NIST), National Computing Centre Limited (NCC) in the U.K, and
Computer Logic R&D in Greece. The first five versions of the NIST SQL Test Suite were produced
by NIST over the years 1987 through 1995. The donation of tests by NCC and Computer Logic,
under the CTS5 SQL2 Project sponsored by the European Community, has been a major
contribution to the current version.
The SQL Test Suite may be used to evaluate conformance to the following SQL standards
specifications: ISO/IEC 9075:1992, ANSI X3.135-1992, FIPS 127-2, and X/Open XPG4 SQL. The
test suite contains tests and procedures to evaluate conformance to various levels of the standards
or profiles: Intermediate SQL, Transitional SQL, Entry SQL, sizing profiles, flagging of extensions,
X/Open profiles. The test suite consists of schemas and test programs for Interactive SQL as well
as ten different programming language test suite types: Embedded C, Embedded COBOL,
Embedded Fortran, Embedded Ada, Embedded Pascal, Module Language C, Module Language
COBOL, Module Language Fortran, Module Language Ada, and Module Language Pascal.
The SQL Test Suite is used to validate commercial SQL products for conformance to ISO, ANSI,
and FIPS SQL standards. The results of the validation service are listed in an online Validated
Products List. The software for the SQL Test Suite can be downloaded from the Web pages of the
NIST Software Diagnostics and Conformance Testing Division. To download this conformance
testing software, go to:
KEY WORDS: conformance testing; database standards; interoperability; SQL; testing of software;
user guide; Validated Products List; validation of software.
DISCLAIMER: Because of the nature of this report, it is necessary to mention vendors and
commercial products. The presence or absence of a particular trade name product does not imply
criticism or endorsement by the National Institute of Standards and Technology, nor does it imply
that the products identified are necessarily the best available.
APPENDICES
1. INTRODUCTION
This manual describes the SQL Test Suite (Version 6.0) and the procedures needed to test and
evaluate an SQL implementation through a standard programming language interface or through
Interactive Direct SQL.
The SQL Test Suite may be used to evaluate conformance to the following SQL standards specifications:
Goals of SQL Test Suite
This test suite was originally developed by the Information Technology Laboratory of the National
Institute of Standards and Technology in support of its federally mandated program of Federal
Information Processing Standards (FIPS). The purpose of this test suite is to help evaluate
conformance of SQL implementations to mandatory requirements of FIPS PUB 127-2. This is the
FIPS Publication that adopts ANSI X3.135-1992, a voluntary industry standard for database
language SQL, for use by the federal government. FIPS PUB 127-2 became effective on December
3, 1993, six months after its publication in the Federal Register.
FIPS PUB 127-2 supersedes previous FIPS PUBs 127 and 127-1. The original FIPS PUB 127
required SQL in relational DBMS applications acquired or developed after August 3, 1988. FIPS
PUB 127-1 offered new conformance alternatives, new programming language interfaces, a new
integrity enhancement option, clarification and correction of existing specifications, and additional
considerations for use in procurements. FIPS 127-2 provides a substantial, upward-compatible
enhancement of Database Language SQL. It includes four levels of conformance: Entry SQL,
Transitional SQL, Intermediate SQL, and Full SQL. Entry SQL is a minor enhancement over the
requirements of FIPS PUB 127-1. Version 6.0 of the SQL Test Suite contains tests for Entry SQL,
Transitional SQL, and Intermediate SQL.
An important objective of FIPS PUB 127-2 is:
To reduce overall software costs by making it easier and less expensive to maintain
database definitions and database application programs and to transfer these
definitions and programs among different computers and database management
systems, including replacement database management systems.
The programs in this test suite can be viewed as straightforward standard application programs that
a user wishes to transfer from one standard environment to another standard environment. Is this
goal achievable on the implementation being tested? Or, is substantial analysis and modification
required before these programs will execute correctly?
The process of installing and running the programs in this test suite is to be documented by the
testers according to instructions provided in this manual. That documentation, along with the
automated summary report of pass/fail results for individual SQL features, can be used to help
evaluate conformance to FIPS PUB 127-2.
Since April 1990, NIST has offered a formal testing service. This service issues Certificates of
Validation for tested products passing all required tests. A Validation Summary Report is issued
for all implementations tested. This validation report documents, to the extent tested, the
implementation's conformance to FIPS PUB 127-2. Beginning July 1, 1997, NIST will turn over
SQL testing services to the private sector. Federal agencies should continue to require validated
products in their SQL procurements, since validation services will continue to be available and since
standard-conforming SQL products are critically important in heterogeneous distributed database
environments.
The test suite can also be used to evaluate the adherence to X/Open XPG4 SQL specifications (with
optional Integrity Enhancement Feature) for X/Open Branding Requirements; it covers the language
bindings Embedded SQL C and COBOL only.
NIST publishes an on-line register, Validated Products List, showing SQL implementations that hold
current Certificates of Validation and registered Validation Summary Reports. This publication also
lists results of testing for the programming languages (Ada, C, COBOL, and FORTRAN) as well
as Graphics, POSIX, and Security standards. As NIST transitions its testing services to the private
sector, NIST World Wide Web pages will point to a directory of conformance testing programs,
products, and services to provide additional information.
History of SQL Test Suite
The SQL Test Suite was first made available to the public in August 1988 as the NIST SQL Test
Suite, Version 1.1. This version included tests for three programming languages: COBOL,
FORTRAN, and C. Version 1.1 included tests for Embedded SQL as well as Module Language
SQL. In May 1989 the test suite was enlarged and released as Version 1.2. This version included
tests for additional SQL features, as well as tests for Embedded SQL Pascal and a Pascal interface
to Module Language SQL. The NIST SQL Test Suite was distributed, for a fee, under the terms of
a software agreement. Version 2.0 of the SQL Test Suite 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-1 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 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, and SQL Errata against ANSI X3.135-1989 (approved after publication of SQLIB-1). Version 4.0 of the SQL Test Suite provides tests for all the features in Entry SQL. Although
MUMPS is one of the standard programming language interfaces specified in FIPS 127-2, the SQL
Test Suite does not yet have programs to validate the MUMPS interfaces to 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.
In Version 5.0, tests were included to address features of Transitional SQL features defined in FIPS
127-2, as well as features of the X/Open CAE Specification Structured Query Language (SQL).
Version 6.0 grew substantially through the donation of new tests for Intermediate SQL written by
European collaborators, National Computing Centre in the U.K. and Computer Logic R&D in
Greece. These tests were developed under the Conformance Testing Service Project for SQL-92
called CTS5 SQL2, sponsored by the European Community (EC). Version 6.0 became freely
accessible over the World Wide Web on December 31, 1996.
Description of SQL Test Suite
The 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. The test suite also provides an Interactive
Direct SQL test suite type to test interactive invocation of SQL statements as defined in FIPS 127-2.
In Versions 5.0 through 6.0, new tests for Transitional and Intermediate SQL were not translated into
three interfaces: Module Language FORTRAN, Embedded, and Module Language Pascal. These
three interfaces have been the least popular, and generating additional tests for these interfaces is not
cost effective.
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. For this
reason, the style of the translated code may seem unnatural for a given 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.
Many 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 (until Intermediate SQL), 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.
The test suite includes a set of programs to test features from the X/Open CAE Specification
Structured Query Language (SQL), Document Number C201, which contain some extensions to the
ISO/IEC 9075:1992 standard.
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.
Unless stated otherwise, all references to sections, syntax rules or general rules in this documentation
are to ANSI X3.135-1992 (or equivalently ISO/IEC 9075:1992).
2. TECHNICAL GOAL OF SQL TEST SUITE
The technical goal of the test suite is to help evaluate an SQL implementation's conformance to
various levels of the SQL standard, as specified in ANSI X3.135-1992 (or equivalently ISO/IEC
9075:1992), through one or more standard programming language interfaces.
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, (3) Interactive Direct SQL,
as specified in FIPS PUB 127-2 Section 16.5, and (4) X/Open Extensions for features specified in
the X/Open CAE Specification.
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 on the NIST Web Server URL address ftp://speckle.ncsl.nist.gov/vpl/sqlintro.htm.
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 SQL Test Suite.
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.
Currently, it is the responsibility of the implementor to prepare the driver scripts (i.e. operating
system command files, shells, makefiles, runstreams, JCL) to execute the test suite.
NIST will maintain Version 6.0 of the SQL test suite as resources allow. NIST will evaluate error
reports and distribute documentation of approved corrections via a World Wide Web page on SQL
programs.
3. OVERVIEW OF SQL TEST SUITE
The SQL Test Suite contains schemas and programs to test an SQL implementation for various
levels of the SQL standard. The test suite contains 18 users, 19 schemas, 208-463 programs and up
to 849 test cases (depending upon test suite type). The Interactive Direct SQL version is smaller,
with 379 programs and 660 test cases. In all there are 5887 files in the Version 6.0 distribution.
Before testing begins, the Test Editor, TEd, is installed. This editor is used (1) to install maintenance
updates from NIST and (2) to facilitate installation and documentation of any changes made by the
tester to the original test suite files.
Figure 1 shows a system flow diagram for basic SQL testing. Running an SQL test suite consists
of 5 steps.
In step 1, the schema files are processed in some implementation-defined manner, typically using
Interactive SQL.
In step 2, a few programs are run to insert values into the base tables. The contents of these base
tables will remain unchanged throughout testing; i.e., these values will be restored by each
program that changes them.
In step 3, the test programs are run to interact with the database tables. Each program contains
logic to evaluate the database responses and determine whether a test passes or fails. This
pass/fail decision is recorded by inserting a row into the table TESTREPORT. In general,
programs may be run and rerun in any order.
In step 4, static values are inserted into the reference tables. These tables are required to produce
the automated summary report. These tables are also a valuable resource to testers, since they
can be queried interactively to create a variety of useful cross-references. In addition to the static
values, rows are inserted (by the tester, via Interactive SQL) into tables FEATURE_CLAIMED
and BINDING_CLAIMED to specify which profiles and interfaces are to be tested.
In step 5, the report programs are run to produce three listings for each profile tested:
PROBLEMS - a listing of failed or missing results
TEST RESULTS - a listing of the result (pass / fail / missing / not applicable) for each individual test in each interface tested
TOTALS - counts for test results for each interface tested
To test Interactive Direct SQL, the tester visually evaluates the execution log (screen display) from
running the SQL command files, assigns a pass/fail grade, and completes a check list.
4. INSTALLING THE TEST SUITE
1. Download files from the World Wide Web. The SQL Test Suite is available as a set of
compressed TAR files. You will download the default SQL TAR file and then will select
from among the TAR files for different programming language interfaces.
The full SQL Test Suite consists of 14 directories, organized as 11 TAR files. When you
download the default TAR file, you will receive the basic set of directories needed by all
the other directories. The default SQL TAR file contains a schema directory (SCHEMA),
a reporting directory (REPORT), the Interactive SQL directory (SQL), and the
utility/documentation directory (OTHER). You then download directories for the
programming language interfaces you want to test. The 10 remaining directory names,
consistent with our file-naming conventions (see item 5, below), are: PC, PCO, PFO, PAD,
PPA, MC, MCO, MFO, MAD, and MPA. The most popular of these 10 directories is the
PC directory, for testing the Precompiler C (Embedded C) interface.
2. Uncompress the TAR files in a suitable directory. Each TAR file will create its own
directories. Lock the files so they cannot be changed. DO NOT CHANGE the test suite
filenames in these directories (unless mandated by your operating system). The
maintenance scheme depends on stable naming conventions.
3. Create additional directories. In preparation for a validation, we suggest that you create
at least three additional working directories to store (1) permanent objects which you create
such as driver scripts, TEd input files, makefiles, subroutine AUTHID; (2) generated
intermediate objects that can be recreated at will and deleted en masse, such as temporary
versions of the programs (output of TEd or precompiler), object modules, executables, Ted
executable, etc.; and (3) audit trail/documentation objects such as logs showing schema
creation and test program execution. These logs will be stored on tape or diskette after a
validation, along with the permanent objects which you used to run the tests.
The procedures for creating schemas, preprocessing, compiling, linking, and running will
vary with the operating system, DBMS, programming language compilers, etc. The user
of this test suite is responsible for creating the driver scripts (i.e. operating system
command files, shells, makefiles, runstreams, JCL) to execute the test suite. Sample driver
scripts for a few environments are included as APPENDIX A.
4. Verify that all files have been received for the test suite type being tested. Print the file
RUN*.ALL (where * denotes a wild-card matching symbol in a file name) for the test suite
type being tested. For example, the Embedded SQL C ("PC") test suite will contain a file
RUNPC.ALL. This file lists the programs to be executed. Additional files are CHG*.TED
and RPT*.SQL (i.e. CHGPC.TED and RPTPC.SQL for the "PC" test suite). Print these
files also. All other files in the directory are programs of the type to be tested (e.g.,
Embedded SQL C programs - ending in "PC").
Note that each test suite type will contain a slightly different list of programs. Certain
programs are tests of the Embedded SQL and are not meaningful tests for module
language. For example, for the following programs you will find only precompiler
versions: DML017 (tests WHENEVER) and DML063 (tests use of reserved words as host
variables). Other programs are meaningful for only one host language. For each of the
five embedded test suite types, there is a sample optional login program, AUTHID, which
may be useful.
Note that directories PPA, MFO, and MPA contain programs which are not listed in the
files RUNPPA.ALL, RUNMFO.ALL, and RUNMPA.ALL. These extraneous programs
are tests for Transitional SQL features. The translation of programs into test suite types
PPA, MFO, and MPA was begun, but not completed because of time constraints and
limited demand. Interfaces PPA, MFO, and MPA will not be validated for conformance
to Transitional SQL. Programs for validation of Entry SQL are complete in all test suite
types.
5. Identify test programs. Each of our test programs is of the form CDRiii.xxx,
DMLiii.xxx, SDLiii.xxx, FLGiii.xxx, MPAiii.xxx, MPBiii.xxx, ADAiii.xxx, CCCiii.xxx,
COBiii.xxx, FORiii.xxx, ISIiii.xxx, ISTiii.xxx, PASiii.xxx, XOPiii.xxx, XTSiii.xxx, or
YTSiii.xxx.
The program prefix has the following meaning:
| CDR | tests "integrity enhancement" to SQL |
| -- | Check clause, Default column value, Referential integrity |
| DML | tests data manipulation language |
| SDL | test schema definition language via DML |
| ISI | information schema for Intermediate SQL |
| IST | information schema for Transitional SQL |
| FLG | SQL Flagger test |
| MPA | concurrency test, program A |
| MPB | concurrency test, program B, to be run at the same time as program A |
| XOP | X/Open Extension tests, for Embedded SQL C and COBOL only |
| XTS,YTS | Intermediate SQL programs donated by the CTS2 SQL2 project |
| ADA | Ada only |
| CCC | C Language only |
| COB | COBOL only |
| FOR | FORTRAN only |
| PAS | Pascal only |
The xxx designates the type of program as follows:
| .PAD | precompiler (embedded syntax) Ada program |
| .SAD | standard (calling module language) Ada program |
| .MAD | Module Language SQL called by a Ada program |
| .PC | precompiler (embedded syntax) C program |
| .SC | standard (calling module language) C program |
| .MC | Module Language SQL called by a C program |
| .PCO | precompiler (embedded syntax) COBOL program |
| .SCO | standard (calling module language) COBOL program |
| .MCO | Module Language SQL called by a COBOL program |
| .PFO | precompiler (embedded syntax) FORTRAN programa |
| .SFO | standard (calling module language) FORTRAN program |
| .MFO | Module Language SQL called by a FORTRAN program |
| .PPA | precompiler (embedded syntax) Pascal program |
| .SPA | standard (calling module language) Pascal program |
| .MPA | Module Language SQL called by a Pascal program |
| .SQL | Interactive Direct SQL statements |
The iii stands for an integer, assigned serially.
6. Install the NIST Test Editor, TEd. It is not necessary to use the NIST Test Editor for
in-house evaluations. However, if you plan to have your product validated in the future,
you will need to learn how to use the editor. Keep in mind that the explicit purpose of the
editor is to facilitate running the test suite. Despite the natural resistance of users to
learning yet another editor, and a batch editor at that, we expect that your investment of
time will more than pay for itself.
Our test editor is written in highly-portable C. The name of the source code program in
the OTHER directory is TED.C. Please notify us immediately of any portability issues or
errors which we have overlooked. We will assist you in debugging, if problems arise.
Read the user documentation for TEd to understand why and how we plan to use this
editor. If you change any of our test programs or schemas in the process of testing, we
suggest that you use our editor to accomplish this. At the end of testing, you will have a
single text file of batch editor commands (input to TEd) which documents all the changes
you made. This single file will greatly simplify retesting (and validation) later. A separate
text file may be used for each test suite type and for schema processing. Or, all change
specifications may be stored in a single file.
Each directory contains a sample or "starter" change file, CHG*.TED, which the tester
should modify (with any editor) throughout the testing process. For example, the sample
change file for Embedded SQL C is CHGPC.TED. This file also contains instructions for
installing implementation-dependent options.
Prior to scheduling for an SQL validation, the SQL testing laboratory will review the
proposed file and will determine whether proposed changes are nonconformities or
allowable modifications.
7. Download the Version 6 maintenance file UPD600.TED. The approved maintenance
file, UPD600.TED, is an important input to TEd. This file should be copied into the same
directory containing your driver scripts and CHG*.TED files. This file should be
referenced with the "-t" option on the TEd command line to effect automatic maintenance
of the SQL Test Suite. From time to time, this file should be replaced with the most recent
version of the maintenance file. See the section titled "Software Maintenance" for more
details.
Since the TEd editor is used to install maintenance, it is important to process every file
from the SQL Test Suite using the TEd editor with the official maintenance file
UPD600.TED. This means that schema files, data files, test programs, reporting programs,
etc. must have maintenance applied before they are used.
Prior to scheduling for an SQL validation, the SQL testing laboratory will review the
maintenance file to verify that it contains the most recent changes.
Note that TEd has an option (-o) to write the edited program (or schema file) with a different name. If you want to change the downloaded filenames to satisfy naming conventions for your SQL processor, do so on output from TEd. For example, if your SQL preprocessor expects Embedded C programs (such as dml001.pc) to have a file extension of ec, then use the following command:
ted -t upd600.ted -t chgpc.ted -o dml001.ec dml001.pc
This command will read the downloaded Embedded C test program dml001.pc, apply the changes coded in file upd600.ted, apply the changes coded in file chgpc.ted, and then write the modified Embedded C test program dml001.ec. If the file chgpc.ted contained a command such as:
sub dml001.pc //
/E1/E2/
This command would change the text E1 to the text E2 globally in only program
dml001.pc. It would not change anything if program dml001.ec were input to TEd. So,
it is important not to change filenames prior to applying the NIST file upd600.ted.
5. CREATING THE TEST SCHEMAS
1. Set up accounts and passwords, with the assistance of the DBA, for the following list of
authorization id's:
| HU | for file SCHEMA1 |
| CUGINI | for file SCHEMA2 |
| MCGINN | for file SCHEMA3 |
| SULLIVAN1 | for file SCHEMA4 |
| FLATER | for file SCHEMA5 |
| for file SCHEMA6 | |
| for file SCHEM11** | |
| for file SCHEM12*** | |
| CANWEPARSELENGTH18 | for file SCHEMA7 |
| SUN* | for file SCHEMA8 |
| SULLIVAN* | for file SCHEMA9 |
| SCHANZLE* | for file SCHEM10 |
| CTS1*** | for file CTS5SCH2*** |
| for file CTS5SCH3*** | |
| CTS2*** | for file CTS5SCH1*** |
| CTS3*** | for file CTS5SCH5*** |
| CTS4*** | for file CTS5SCH4*** |
| T7013bPC*** | has no schema file |
| T7013PC*** | has no schema file |
| XOPEN1++ | for file XSCHEMA1 |
| XOPEN2++ | for file XSCHEMA2 |
| XOPEN3++ | has no schema file |
* this authorization and schema are used to test Integrity Enhancement Feature (not used for one of the X/Open profiles, but required for FIPS 127-2).
++ this authorization and schema are used to test X/Open profiles only.
** this authorization and/or schema are used to test Transitional SQL profiles.
*** this authorization and/or schema are used to test Intermediate SQL profiles.
If the operating system does not allow a user id of length 18, then replace
CANWEPARSELENGTH18 with another user id which is the maximum length allowed.
If your SQL implementation uses the system user id as the SQL USER value or as the
authorization id for privilege enforcement, then your computer system administrator should
also create system accounts for these users. You may need to process schema and
programs for each authorization id while logged in as that user.
The tester will need to choose passwords for the authorization id's. The SQL standard has
no requirements concerning the passwords chosen. The tester should probably choose
passwords consistent with in-house regression testing procedures, if possible.
2. Define the schema for each of the appropriate authorization id's, processing the
schema definition text files in an implementation-defined manner; e.g., interactively. See
Step 1 of Figure 1. The file RUNSCH.ALL contains pseudo-scripts for creating the
schemas. Use the SCHEMA text files as follows:
a. If your implementation is fully conforming to the standard, use SCHEMA1.STD
through SCHEM10.STD. (See item (e) below for a discussion of privilege violation
processing for schemas).
b. If your implementation needs semicolons to terminate SQL schema definition
statements, use SCHEMA1.SMI through SCHEM10.SMI. Be sure to report this
nonconformity in your final conformance analysis. Note that this will be counted as
a nonconformity for a validation.
c. If your implementation needs semicolons and also requires CREATE UNIQUE
INDEX instead of the UNIQUE declaration, then use SCHEMA1.NC instead of
SCHEMA1.SMI. Be sure to report this nonconformity in your final conformance
analysis. Note that this will be counted as a second nonconformity for a validation.
d. Otherwise, make whatever changes are needed to obtain a logically equivalent syntax
for the schema definition. Keep in mind that changes are generally counted as
nonconformities. It is essential that the tables HU.TESTREPORT, HU.STAFF,
HU.PROJ, and HU.WORKS be installed; otherwise further evaluation of the test suite
is impossible. It may be necessary to change the exact numeric data types to
INTEGER instead of DECIMAL(I). (We chose to use exact numeric data types with
a declared scale so that numeric precision would not be an issue.)
Code your change specifications as inputs to TEd by editing the sample file
CHGSCH.TED or CHGALL.TED, which is provided. If the schema contains a
privilege violation, capture the error message as proof that a privilege violation has
been detected and then edit CHGSCH.TED to specify removal of the offending text.
The appropriate changes may already be coded in the CHGSCH.TED file as comments.
If you are not using TEd, keep a log of your changes as documentation of either
implementation-defined or as nonconformities to be reported in your final conformance
analysis.
e. In an effort to simplify the process of editing schema files to remove schema privilege
violation syntax (demonstrated by the SQL implementation to be a "fatal" error),
alternate files are provided in the test suite. If your schema processor will entirely
reject a whole schema because of a single error, then you may use the schema files
ending in PV* and OK. For example, instead of using SCHEMA2.STD without and
with TEd changes, you may use SCHEMA2.PV1 (with privilege violation #1) to
demonstrate the fatal error, followed by SCHEMA2.OK (no privilege violation).
f. Note that there is a file of DROP TABLE and DROP VIEW statements, DROPHU.NC
(clearly containing extensions to Entry SQL), to drop all views and tables created by
SCHEMA1. There is a similar file, DROPSUN.NC, for SCHEMA8. These files may
prove useful while you are analyzing your implementation's SDL conformance. Note
that SCHEMA1 and SCHEMA8 are the only large schemas.
It is permissible to insert one or more statements at the beginning of the schema file to
login or to establish the ANSI/ISO environment. You may add an implementation-defined
terminator, such as a semicolon, at the end of the schema file. Other variations may be
allowed after consultation with NIST or the SQL testing laboratory.
Verify that the tables have been created. Read the schema files and compare to tables and
views existing in your data dictionary.
6. RUNNING/DEBUGGING WITH THE TEST PROGRAMS
1. Code a procedure to create an executable. Decide how to invoke all processors, such
as precompilers, compilers, and TEd, to create an executable. Be sure to invoke any option
on a processor which is needed to obtain the ANSI/ISO/FIPS behavior. Be sure to keep
notes on which options or parameters are used (whether explicitly or implicitly), because
they should be documented later in your evaluation. Read "Special Notes on Individual
Programs" at the end of this section for variations in creating or running executables. Your
procedure should invoke TEd as the first step, to install changes without touching the NIST
original programs. On output from TEd, you should rename files to suit your SQL
implementation's naming conventions. For example, you may want to rename
DML001.PC to DML001.EC. We do not recommend renaming files before executing
TEd, because the NIST maintenance TEd file will then need to be modified to specify the
new names of the test programs.
To save mass storage space, you will probably want to delete all intermediate files (such as outputs of TEd, precompilers, and compilers) after successful completion of the program.
2. Code a script to invoke the procedure for each program. For each test suite type, there
is a text file, RUN*.ALL, listing the programs to be executed to support various claims of
conformance. For example, file RUNPC.ALL lists the Embedded SQL C programs,
organized into sections for the various possible claims. You may ignore programs not
applicable for your claim. The text file may be edited to create a command file that will
create executables or run the test suite. Read the notes in RUN*.ALL for additional
instructions. Also, if you are going to claim support for individual features in addition to
the Entry SQL Profile, you will need to use the automated reporting system to generate the
correct list of programs. The files RUN*.ALL were generated by program REPORTA, and
you may choose to run program REPORTA rather than using files RUN*.ALL.
The authorization id for each program is listed in these files to assist you in constructing
driver scripts with the appropriate authorization id values.
If your SQL password is tied to your system login password (and this is not disallowed by
the SQL standard) you will probably need to compile and/or execute each of the test
programs while logged into the system as that user. Since the order of execution of the test
programs is not important, you should group them by authorization id; i.e., in the interest
of efficiency, you should write driver scripts which minimize login and logout.
3. Solve the login problem. The method of establishing a <module authorization identifier>
is implementation-defined in embedded languages. We have chosen to code a CALL to
a subroutine, AUTHID, with a variable, uid, containing the authorization identifier. This
subroutine logs into the database with the given authorization. This solution may not work
for you! You may need to move the authorization identifier into the driver scripts (external
to the program) and logically delete the subroutine call. You may replace the subroutine
call with an include statement or a CONNECT statement - see APPENDIX D. In
Embedded SQL C, you may want to insert after the #include <stdio.h> statement some
other statement which will effect initialization of your database. Or, you may solve the
authorization identifier problem in some other way. Your report of test results should
include a description of how your implementation handles login.
Additional statements are allowed to connect to database components and to establish the
ANSI/ISO environment. Basically, one or more statements to login and to establish the
ANSI/ISO environment will be allowed near the beginning of each test file. For example,
you may make a call to the server to invoke ANSI/ISO mode or to invoke ANSI/ISO-required features separately (e.g., SERIALIZATION mode, TRANSACTION mode, etc.).
The call to AUTHID is a convenient hook for TEd to make these insertions. It is very
desirable that all ANSI/ISO-required features be default features; however, it is allowable
to invoke them as a group or individually. This invocation may take the form of
precompiler/compiler parameters, configuration files, software installation parameters,
calls from inside a program (prior to execution of test cases), etc.
Since it is implementation-defined (in Entry SQL-92) whether the authorization is checked
at run time or at compile time, the most straightforward approach is to precompile (prepare
executables) and execute each program while logged in as the USER specified (by the
variable uid) inside the test program. For client/server architectures where the catalog is
not referenced until run time, it may be more convenient to compile all programs while
logged on as a single user.
Note that each test program verifies that the correct USER is logged in before executing
the test cases. Incorrect USER value will STOP the program. You may easily use TEd to
remove or replace this verification during debugging if your SQL implementation does not
support the key word USER.
4. Make global changes for implementation-defined parameters. We have made an
attempt to code certain constructs consistently so that global changes would be possible
as a means of installing implementation-defined parameters. Edit the sample TEd input
file, CHG*.TED, for the test suite type being run. Specify the appropriate implementation-defined values according to examples contained in the CHG*.TED file.
Change authorization identifiers which are too long to shorter ones; e.g.,
CANWEPARSELENGTH18 may be changed to CANWEPAR. Exercise care, when
making global changes to character string literals in the Pascal and Ada interfaces, not to
change the length of the literal, since unequal-length comparisons and assignments may
not compile or execute as expected.
The precision of indicator variables is implementation-defined. We have chosen to use
"short" in Embedded SQL C, "integer*2" in FORTRAN, "PIC S9(4) DISPLAY SIGN
LEADING SEPARATE" in COBOL, and "integer" in Pascal. If this is not the correct
precision or exact numeric type for your implementation, you must change the precision
in the declarations of variables beginning with the name "indic"; e.g., indic1, indic2, etc.
These variables occur in programs CDR003, CDR027, DML004, DML008, DML010,
DML013, DML023, DML025, DML036, DML061, DML071, DML076, DML077,
DML082, SDL026, etc. For Ada programs, the required package SQL_STANDARD (for
SQL-92) or INTERFACES.SQL (for SQL-92 as corrected by Technical Corrigendum #2,
TC2) specifies whether the indicator type is INT or SMALLINT.
The precision of SQLCODE is implementation-defined for COBOL. We have chosen
"PIC S9(9) COMP" in COBOL. If this is not the correct precision for your
implementation, you must change globally the precision of the COBOL SQLCODE
declaration.
COBOL programs may need global changes in the Source-Computer and Object-Computer
paragraphs.
The precision of a CHAR column with DEFAULT USER in SCHEMA8 should be
changed to reflect the implementation-defined length for USER.
Implementation-defined keywords (possibly HOURS and PROGRAM), which are not
allowed by your implementation as column and tables names, should be changed globally
to some other word. This does not count as a nonconformity.
Character set is implementation-defined. Contact NIST or the SQL testing laboratory if
your character set (whether SQL processor or host language compiler) is incompatible with
test suite materials. NIST or the SQL testing laboratory will discuss the implications with
you and authorize appropriate workarounds.
5. Using TEd, make global changes for documentation purposes. Code a TEd substitution for
"59-byte ID" to identify the SQL product and version being tested and the test platform,
including hardware and operating system. If the length exceeds 59, then the replacement string
may overflow COBOL and FORTRAN source code margins. (Compulsively wordy testers
should investigate the -c option in TEd to specify multiple-line substitutions.)
For FORTRAN, Ada, and Pascal interfaces, determine how to print date and time for your
system. Code TEd substitutions for "date_time declaration" and "date_time print". These
substitutions will differ among host languages and operating systems. C and COBOL
programs have already been modified to print date and time using standard features.
6. Using TEd, make global changes for nonconformities which must be resolved before
further testing is possible. These global changes should be included in your conformance
analysis as nonconformities. All test suite materials must be supported correctly by the
SQL implementation being tested. That is, in addition to the obvious test programs, all test
suite materials such as schema files, dataload programs, reporting system files (schema
files, report dataload program, programs reporta and reportb), are considered part of the
demonstration of conformance by the SQL implementation under test. Any changes to any
of these materials necessitated by nonconformities of the SQL implementation are to be
documented as nonconformities.
If you are testing Embedded SQL COBOL and your SQL implementation does not support
the COBOL numeric data type [USAGE] DISPLAY SIGN LEADING SEPARATE, you
will need to make global changes to the COBOL programs (.PCO and .SCO) and the
COBOL modules (.MCO). TEd can be directed to change only those declarations within
the scope of the BEGIN and END DECLARE SECTION.
If your implementation of SQL does not support a direct declaration of SQLCODE, but
instead requires an INCLUDE SQLCA statement, you should globally delete the
declaration for SQLCODE and insert one for the required structure. For language C, you
may want to direct TEd to delete all lines containing "long SQLCODE" and insert the
required INCLUDE SQLCA statement before the text "main()". This will be counted as
a nonconformity.
If SQLCODE has some other name in your product, such as SQLCDE or sqlca.sqlcode,
you will want to make a global change to the programs. If the substitute value is longer,
you may also need to split lines which become too long. In C, if the substitute value is
longer, you may prefer to "#define SQLCODE sqlca.sqlcode". This will be counted as a
nonconformity.
We have attempted to code certain SQL constructs in a consistent manner, so global
changes would be possible. These constructs include:
We have taken care to code Embedded SQL statements so the global insertion of a line of
code (such as a "print" of SQLCODE) either before or after executable SQL statements
will not disrupt the logic of any "for" loops or "if" blocks containing them.
7. Begin running the script to invoke the programs listed in RUN*.ALL. Run program
BASETAB to load the primary tables. The correct authorization identifier for BASETAB
is HU. Then run programs CUGTAB, FLATTAB, SUNTAB0, SUNTAB1, SUNTAB2,
SUNTAB3, and SULTAB1 with the authorization shown in RUN*.ALL. For testing
Intermediate SQL, run program CTS5TAB. For testing of X/Open profiles, run
XBASETAB. For testing X/Open profiles without Integrity Enhancement Feature, there
is no need to execute programs for authorizations SUN and SULLIVAN. See Step 2 of
Figure 1. Check the printout to verify that the data has been inserted. These programs may
be rerun at any time to re-initialize the data in the primary tables.
8. Run each of the programs which are appropriate to the test suite type. See Step 3 of
Figure 1. Typically, if a problem occurs in one of the test programs, the tester performs
the following steps: (1) determines the cause of the problem, (2) changes the program by
modifying input to TEd, (3) recreates the executable program, and (4) runs the modified
executable program. Often, for the purpose of debugging, it is helpful to run the
Interactive Direct SQL version of a program. The on-line user interface is often very
informative and the SQL statement in question can be rapidly modified and retried until
the problem is isolated. The test programs may be run and rerun in any order (except for
programs beginning with MP, which must be run in pairs). Other exceptions are the
sequence of programs YTS790 through YTS792 and the sequence of programs YTS793
through YTS795 for Intermediate SQL, as well as the sequence of programs XOP719
through XOP723 for X/Open profiles. Each of these sequences must be run in ascending
order. See Special Notes on Individual Programs below.
There are thirteen pairs of programs for concurrent testing:
Each pair is to be run concurrently, either from separate terminals or windows or started
as separate batch processes. All MPA and MPB programs for a given test suite type may
be run at the same time. Any pair of concurrent programs may be run and rerun in any
order.
Start the MPA program first, and then a few seconds later or when prompted by the MPA
program, start the MPB program. It is common to see both programs issue messages to
start the companion program, even after the tester has started both.
The concurrency programs contain tuning variables which may be used to lengthen the
workload or planned waiting periods. This will allow programs to "interleave" better, as
required by program logic to get a "pass."
9. Use TEd to install changes. If the SQL language for a given test (except for a FLG test)
prevents a program containing several tests from running, use TEd to delete the entire
problem test. Then rerun the program to exercise the remaining tests. Note that the
missing test will be reported as a "fail" by the automated reporting system. Make only the
global changes to the FLG programs. Do not make any other special changes (except
FLG005) to these programs. (If an SQL Flagger test does not compile and execute, then
the reasons given by the implementor for not compiling or executing may constitute the
"flagging" required by FIPS PUB 127-2.)
Another approach is to change the syntax of the problem test to syntax acceptable to your
DBMS. This would allow you to further evaluate the SQL implementation; however, it
would probably give a false test result of "pass." Code these changes in the SYNTAX
DEFICIENCIES SECTION of the CHG*.TED file. This file is a log of changes applied
to tests which fail syntactically, although they may pass functionally.
10. Run PREDML. When you are ready for a final analysis of a test suite type, remove all
rows for that test suite type from the table TESTREPORT. This will eliminate conflicting
results caused by earlier rerunning of changed tests. This is done by executing the program
PREDML for authorization identifier HU. Then rerun all the test programs. Capture the
screen printout of this final run as part of the documentation of your testing.
7. RUNNING THE AUTOMATED REPORTING SYSTEM
1. Install the Reporting Structure.
Run the following in Interactive SQL as user HU:
a. REPORT.SQL, in directory REPORT (creates the tables) See Appendix H.1.
b. DATALOAD.SQL, in directory REPORT (loads the static data)
** If DATALOAD.SQL causes problems, refer to the long instructions on the media, file
REPORT_L.DOC in directory REPORT.
Compile the following programs:
a. REPORTA (any embedded or module language)
b. REPORTB (any embedded or module language)
c. REPORTZX.C, in directory REPORT (ANSI C with no embedded SQL)
Put the executables for REPORTA, REPORTB, and REPORTZX together in a directory
where user permissions will allow the creation of temporary files.
2. Execute the Reporting System.
a. Insert one or more of the following values into BINDING_CLAIMED according to
which bindings you wish to test: 'PCO', 'PFO', 'PC ', 'PPA', 'PAD', 'MCO', 'MFO', 'MC
', 'MPA', 'MAD', 'SQL'. For example, to test Embedded C: INSERT INTO
BINDING_CLAIMED VALUES ('PC ');.
b. Insert the profile identifiers for the profiles that you want to test into FEATURE_CLAIMED. For example, to test Transitional SQL:
INSERT INTO FEATURE_CLAIMED VALUES ('P135');.
The profile identifiers can be found in the diagram PROFILES.PS (in directory OTHER) or by typing the Interactive SQL command:
SELECT * FROM REPORTFEATURE
WHERE FEATURE1 LIKE 'P%';
The typical FIPS 127-2 validation (for Entry SQL) will use only the following inserts into FEATURE_CLAIMED:
INSERT INTO FEATURE_CLAIMED VALUES ('P125)';
INSERT INTO FEATURE_CLAIMED VALUES ('P325');
INSERT INTO FEATURE_CLAIMED VALUES ('P415');
NOTE: Subprofiles are automatically selected when you select a parent profile. Refer to
the diagram PROFILES.PS in directory REPORT or to Appendix H.3. For example, if you
select 'P135' (FIPS 127-2 Transitional SQL), the following subprofiles are automatically
selected: 'P110' (FIPS 127-2 Entry Syntax Flags), 'P120' (ISO/IEC 9075:1992 Entry
SQL), and 'P125' (FIPS 127-2 Entry SQL).
c. Run REPORTA. If you are testing a combination of individual features, rather than
one of the established profiles, then you will need to capture the output to get a list of
programs that you must run to test the feature(s) that you selected. If only some of the
tests in a particular program are required, REPORTA will also provide a TEd change
to delete the extra tests. Include the TEd change specification in your TEd file. Run
any additional test suite programs required for your claim.
d. Run RPT*.SQL for each binding claimed (test suite type). Before producing the final
reports, it is often useful to know if there are any unexpected failures, any missing
tests, or any conflicting results (both "pass" and "fail" for a single test). An efficient
way to ensure that the table TESTREPORT contains no surprises is to run the
Interactive Direct SQL queries in the appropriate RPT*.SQL file. For example, for
Embedded SQL COBOL, run RPTPCO.SQL. Run it under authorization HU.
RPT*SQL must be run after REPORTA in order to detect missing tests. If the query
results of RPTPCO.SQL accurately reflect the testing, then you are ready to run the
final report.
e. Run REPORTB to generate the temporary files used by REPORTZX.
f. Run REPORTZX and look at COMBINED.LST to see the results of testing. As a
general rule, you should always run REPORTB immediately before running
REPORTZX.
g. If REPORTZX shows a test number is missing or a test has failed incorrectly, then you
will need to follow restart procedures below.
NOTE: The diagram REPORTIN.PS in directory OTHER shows the data flow of the
above procedure.
NOTE: Programs REPORTA, REPORTB, REPORTZX are run only once to report on all
bindings claimed. This is a different architecture from the one in Version 4.0, where the
reporting programs were run for each binding claimed.
3. Restart Procedures for the Reporting System.
If you do find a test case with conflicting results (e.g., both a "pass" and a "fail") for the
test suite type being tested, then the test will be assigned a "fail" by the automated
reporting system. If a test fails for an operational reason (such as starting two copies of
MPA001 at the same time) and does not represent a real failure (a nonconformity) and you
wish to rerun to demonstrate conformance, then delete all results for that test number and
that test suite type. You may then rerun the program containing the test. A similar
procedure is allowed for a test case which fails because it was executed incorrectly; e.g.,
before loading the initial data into the tables. For example, DELETE FROM
HU.TESTREPORT WHERE TESTTYPE = 'PCO' AND TESTNO = '0099'; will
remove all results for the Embedded SQL COBOL test number 99. Note that duplicate test
results in TESTREPORT do not cause a problem.
If test cases are missing because you simply failed to run the containing program, then you
need to run the missing program(s) and rerun REPORTB and REPORTZX. It is not
necessary to rerun REPORTA. Refer to Appendix H.2 to see the structure of the reporting
system.
If you want to change the bindings or features claimed, you will need to adjust tables
BINDING_CLAIMED and FEATURE_CLAIMED using Interactive SQL. You will then
need to rerun programs REPORTA, REPORTB, and REPORTZX.
8. PREPARING FOR VALIDATION OR REGRESSION TESTING
1. Prepare the final CHG*.TED file. Edit the cumulative change file which has been used
to specify changes to TEd. Remove any of the original NIST comments or examples
which are not applicable. Analyze each change and assign it to one of the sections in the
CHG*.TED file. Create a new section if necessary and document each change with a
comment explaining the purpose of the change.
2. Select a testing strategy. Now that all test programs have been debugged on your SQL
implementation, it is time to plan for regression testing and/or validation. You will want
to develop procedures to run the tests from beginning to end with minimal effort. We have
found, through experience conducting validations, there are two approaches generally used
to execute the test suite.
a. Process/Link/Execute: For each test program, one at a time: (1) prepare the executable
program (execute TEd, precompiler, compiler, linker), (2) execute it, and (3) delete it.
This saves on mass storage and generates a complete log. This approach is
recommended for debugging and for validations running only one or two test suite
types.
b. Prepare executables in advance: An efficient way to run multiple test suite types is the
following: (1) prepare executable versions of each program, (2) prepare a driver script
to execute PREDML and all of the test programs (except the MPB programs) in the
correct order, (3) turn on the "screen capture" or log and start the driver script, (4) when
the MPA programs call for the MPB programs, start the appropriate MPB program
from another terminal, or specify in the driver script that MPB starts a few seconds
after MPA, (5) turn off the "screen capture" or log and print or review the output, and
(6) rerun the reporting programs in batch if the page ejects got lost in the "screen
capture."
Steps 3 through 5 of this strategy typically take us 30 minutes, while step 1 takes 3
hours; although we have seen platforms which complete all steps in 20 minutes. Step
1 can be broken up into several driver scripts, submitted as separate processes or run
from different terminals. There is no need to worry about concurrency during step 1.
While we are running steps 3 through 5, we do worry about concurrency, and we do
not run any test programs from other test suite types.
Each of the concurrency program pairs uses tables used by no other programs in that test
suite type. Consequently, all thirteen program pairs may be run simultaneously, and they
may overlap the stream of other test programs in execution without concern for
accidentally altering data used by another test program. However, no two test suite types
should be executed simultaneously.
9. SPECIAL NOTES ON INDIVIDUAL PROGRAMS
AUTHID This is a sample subroutine which can be modified with implementation-defined
statements to accomplish login and/or to establish the ANSI/ISO test environment. It
is entirely optional. Instead of using a subroutine to login, the tester may globally
replace the call to AUTHID in the main routine with some implementation-defined
statement.
CCC004 Each of these C language programs contains an additional subroutine, CCC004S
CCC009 and CCC009S respectively, which needs compilation and additional link statements.
Embedded SQL C test suites contain programs CCC004 and CCC009; Module SQL
C contains only program CCC004.
DML015 Tests in this program are not independent of each other. If one test is deleted, other
tests in the program may be affected.
DML035 In COBOL, this program is optional (and may not compile) because it contains a
nonstandard variable with USAGE COMP-1. Change the data type to suit the
compiler.
DML038 Test number 0205 prints a Cartesian product of three tables. Do not panic and cancel
the program when you see 360 lines scrolling across the screen. Do cancel the job,
however, if you see more than 400 lines!
DML044 In Pascal, there is no standard way to continue a character string literal onto the
DML047 the next line. In order to test database columns for long character strings, we coded a
procedure, "concat," to create a long character string value. "concat" is used in test
0216 to assign a 118-byte value to variable vtr119 and in test 0222 to assign a 240-byte
value to variable STR240. If procedure "concat" does not work for your compiler,
propose another method to assign the long character string values.
DML063 This is an Embedded (only) SQL program to test the use of SQL key words as host
identifiers. A vendor may declare up to one third of these key words to be "reserved"
and not allowable as host identifiers. The CHG*.TED file must contain specifications
to disallow the "reserved" key words.
DML102 Two copies of this program may be run in place of MPA013.PC and MPB013.PC.
This program is a more elegant approach to concurrency testing, but unfortunately, it
contains C language constructs which do not translate easily into the other test suite
types. This option is available only in Embedded SQL C.
DML103 Two copies of this program may be run in place of MPA012.PC and MPB012.PC.
DML116 This program contains two subroutines for each Embedded SQL main routine. For
module language interfaces, there are two host language subroutines and three modules
to be linked together.
DML169 Check that some form of flagging relevant to the extension is present for each test that
compiles. Read the comments in the program.
FLG005 This SQL Flagger program must be modified to execute two SQL extensions of the
tester's choosing. If the SQL implementation does not support a character function or
integer function extension, then any extension may be coded. The tester must then
verify that the extensions are flagged. If the SQL implementation does not support
extensions at all, this will need to be demonstrated after discussions with NIST.
FLG* All SQL Flagger programs, except FLG005, which fail to compile because the
extension being tested is not supported, are judged to pass by default. Test cases for
which flagging is based on "catalog lookup," rather than "syntax only," are optional
(for Entry SQL); therefore flagging is not required, even if the feature is supported.
These optional programs are FLG006 and FLG009.
MP* MPA* programs should always be started before the corresponding MPB* program.
If any pair of concurrent programs have no screen display for 15 minutes at the point
where deadlock is expected, then cancel the programs and consider them passed. The
standard does not require deadlock management. It only requires transaction
serializability when the implementation successfully processes SQL statements. If any
pair of concurrent programs appears, from the screen display, to be restarting
transactions over and over without progress, contact NIST for additional program fixes
(program code) to introduce asymmetries into the transaction workload and to provide
expanding time gaps between transactions.
XOP719 through XOP723
This set of programs from the X/Open profile are not independent of each other. The
tests are associated with GRANT and REVOKE PRIVILEGES so you need to run
them in the following order: XOP719, XOP720, XOP721, XOP722, XOP723. If any
of the tests in this set need to be run again, you must run the restart SQL command file
XRECRE1.NC after XOP723, and then run this set of tests again.
XTS713 This program uses AUTHORIZATION T7013bPC. Some implementations may need
to create a system or database authorization for this.
XTS725-8 Check that some form of flagging relevant to the extension is present for each test that
compiles, specifically check that all FULL SQL functionality is flagged. Read the
comments in the program.
XTS734 This program tests for the National Character Data type in comparison predicates. It
is necessary to incorporate the appropriate National Character set into the program by
replacing the TEd hook "_VANGELIS" to the correct character set name.
YTS767 Three tests in this program should allow alternate syntax for the CHECK clause. If
there is anything but a "pass" for tests 7544, 7545, or 7546, review the
implementation's syntax and substitute, using TEd, with equivalent syntax.
(YTS790 through YTS792) and (YTS793 through YTS795)
These sets of programs from Intermediate SQL are not independent of each other.
Each set must be run in ascending order as they test for the granting and revoking of
privileges. To restart the first set, user CTS1 should issue the command REVOKE
USAGE ON CHARACTER SET CS FROM CTS2 CASCADE;
YTS814 This program applies to module language interfaces only. It contains special characters
in the LATIN1 character set which may be inadvertently replaced by utilities (e.g.,
editors or E-mail) manipulating these programs.
10. RUNNING THE INTERACTIVE DIRECT SQL TEST SUITE
Establish Test Environment
Interactive Direct SQL allows the implementor considerable freedom in designing print formats for
the screen. Unlike our programming-language test suites, pass/fail grades are assigned to each test
by a tester, rather than by program logic. Each of our test files is designed to execute some SQL
statements and then have the tester examine the results on the screen (or standard output) for
appropriate responses.
Most Interactive Direct SQL implementations have some command to "run" a text file of SQL
commands; i.e. execute a named file. As a matter of convenience, all the Interactive Direct SQL
tests, except the concurrency tests, should be "run" rather than typed. Support for "interactive access
to the database," as specified by Section 16.5 of FIPS 127-2 will be demonstrated during the
concurrency tests.
We anticipate a variety of screen presentations and do not have any fixed criteria for column
headings, numeric formats, character string wrapping, error messages, or other status feedback.
Our test files have comments after SQL statements, detailing the response needed on the screen to
pass that test. All PASS comments for a given test must be judged to pass, otherwise the test fails.
An SQL Flagger test that fails is simply not applicable, since flagging applies only to extensions that
are supported.
Experiment with a few test files to decide how your SQL implementation will allow you to evaluate
responses against our PASS criteria.
If you have some command to force all SQL commands and comments to print on the screen,
interspersed with the SQL query results, then you can simply run all the test files and capture the
screen printing for later evaluation. This command may be something like ECHO or VERIFY.
If not, then some creativity may be needed to force the comments to print on the screen. See the
section "Printing the Interactive Direct SQL Comments" below.
If your SQL implementation has a comment style that is different from the format used in our test
files, you may globally convert the comments in the test files to your format. In all probability, you
can do this with a few TEd commands. Contact NIST for suggestions if it is not obvious how to do
this.
If your SQL implementation has a line-continuation style for SQL statements that is different from
the format used in our test files, you may globally convert the test files to your format. Although
it may be possible to use TEd to accomplish this, you should review the C program ATERM which
was designed to perform this reformatting. ATERM is in the OTHER directory.
Solve the Login Problem
There are many acceptable ways to run the Interactive Direct SQL files. You can actually log in as
the correct USER. You can create a script which logs in as the correct user and then runs a list of
files for that user. You can modify, via TEd, the "-- AUTHORIZATION" comment at the beginning
of each module to login or attach to a schema.
Execute Test Files
Run the data load file BASETAB.SQL if data has not been loaded earlier by another test suite type.
Also run data load files CUGTAB.SQL, FLATTAB.SQL, SUNTAB0.SQL through
SUNTAB3.SQL, and SULTAB1.SQL. For Transitional SQL run the data load file SCHEM11.STD.
For Intermediate SQL, run the data load files SCHEM12.SQL and CTS5TAB.SQL. The order of
execution of data load files is important. Run all of the *.SQL test files. The order of execution of
test files is not usually significant. Read the file RUNSQL.ALL for a listing of the test files to be
run. Print file CHECK.LST to use as a worksheet for recording pass/fail results.
SQL files named MPA010* or MPB010* must be run in the order listed in RUNSQL.ALL; i.e., MPA010A, MPB010A, MPA010B, MPB010B,... MPA010G. Read comments in these files.
If you need to modify a test file for whatever reason, do not change the downloaded *.SQL file
directly. Instead, edit the file CHGSQL.TED to specify the changes, and use our editor, TEd, to
install the changes. For debugging only, it may be more convenient to work with a copy of the
downloaded file which is executed and then modified interactively by a local editor.
If at any time, you suspect that a failed test has corrupted the initial data in the HU directory, run the
files SEEHUE.SQL (to verify that all tables in the HU schema which should be empty are empty)
and SEEHUD.SQL (to view tables which should have rows inserted by the dataload programs).
Delete rows from any table listed in SEEHUE.SQL, if necessary. And rerun BASETAB.SQL at any
time to re-initialize the tables listed in SEEHUD.SQL.
Execute Concurrency Test Procedure
There is only one concurrency test procedure for Interactive SQL. Print file MPQUIC.TXT and
follow the instructions for two testers (or equivalent). Previous concurrency procedures were very
time-consuming and duplicated concurrency testing already done by the programming language
interfaces. The MPQUIC procedure verifies that serializability is turned on for the Interactive SQL
interface.
Interactive SQL test files MPA001* and MPB001* through MPA008* and MPB008* have been
superseded by the MPQUIC procedure, which is much more efficient. These files are still included
in the Interactive SQL test suite because they may be helpful for SQL products which do not have
a programming language interface. Refer to APPENDIX I for detailed instructions on how to
execute these tests.
Evaluate Test Results
Tests may be evaluated on the spot or at some later time by examining the log or captured screen
output. A pass/fail grade is assigned to each test run. The tester should fail any test for which one
or more of the PASS comments is judged to have failed. Tabulate the pass/fail results on worksheet
CHECK.LST, a file in the SQL directory.
Tests for access violations may abort for an Interactive Direct SQL implementation. This is
acceptable. We consider this a demonstration of support for SQL GRANTS. The tester should
simply capture the screen output and then delete that test from the test program before rerunning,
so that the remaining tests in the test program can be run. Capturing the screen output is needed to
get a "pass" for tests which abort due to access violations.
After an UPDATE, INSERT, or DELETE statement, there may be a PASS comment that a given
number of rows were affected. If the SQL implementation being tested does not provide this
information for successful completion, then simply ignore the PASS comment. In most cases, there
will be a subsequent SELECT statement which double checks (verifies) the success or failure of the
database modifications.
For a SELECT statement, the rows will be displayed on the screen and should be counted by the
tester. If there is a PASS comment immediately following the SELECT statement, the visual count
of rows selected must match the count required by the PASS comment (the default count is 1 row).
In addition, if the SQL implementation being tested echoes a row count, that row count must be
accurate.
References to SQLCODE and/or SQLSTATE in Interactive Direct SQL are a carry-over from the
original programming language tests. The revised FIPS PUB 127-2 does not require explicit support
for SQLCODE or SQLSTATE. Rather, the tester should expect some implementation-defined
message or messages which map to the SQL concepts of no-data (SQLCODE = 100) and run-time
error (SQLCODE < 0) or to the standard SQLSTATE conditions and values. If, however, explicit
values for SQLCODE or SQLSTATE are given, then an incorrect value is a failure. (Note that it is
allowable for an SQLCODE 100 to be returned after every successful SELECT statement, since the
SELECT may be implicitly implemented as a cursor, fetching until end of data, where SQLCODE
100 is expected.)
SQL Flagging is now required for Interactive Direct SQL. Failure to flag the supported extensions
in the programs FLG001, FLG005, and FLG008 will be a nonconformity in the Interactive Direct
SQL interface. However, since flagging which requires schema access is optional, failure to flag for
test files FLG006 and FLG009 is not considered a nonconformity.
Print the Interactive Direct SQL Comments
If your implementation of SQL has some command to force all SQL commands and comments to
print on the screen, then you may ignore this section.
You will note that the comments in our *.SQL files are coded in a very stylized manner. The goal is to provide "hooks" for you to do global changes with our editor to convert these comments into some printable object. This may be a system call for printing on the screen or some other facility allowed by your SQL implementation. Only the comments beginning with
-- TEST: and -- PASS:
are of interest in test evaluation.
If all else fails, you can always use SQL. You can change the comment
-- PASS:0247 If count = 3?
into
SELECT '0247 If count = 3' FROM HU.ECCO;
Using our test editor, TEd, the commands:
sub *.sql /-- PASS:/
/'/"/
! replace embedded single quotes
sub *.sql /-- PASS:/
/?/' FROM HU.ECCO;/
sub *.sql /-- PASS:/
/-- PASS:/SELECT '/
will cause SQL to print the PASS comments. Similar global changes will force the "-- TEST"
comments to print. The one-row table called ECCO that is needed for this gimmick to work has
already been created.
11. EVALUATION INSTRUCTIONS
The tests in the SQL Test Suite are designed to conform to Intermediate SQL-92. Only programs designated as FLG should contain extensions to Intermediate SQL-92.
1. Examine the log of changes you made to each program or to SCHEMA files. If a change
was made to SQL syntax to allow your implementation to complete the test using
nonconforming syntax (such as FOR UPDATE in the DECLARE CURSOR statement),
or even alternate conforming syntax, you should report this change as a nonconformity in
your analysis. You should document, for informational purposes, any changes allowed for
implementation-defined options.
2. Report errors in the test suite. If a change was made to SQL syntax to correct an error
(programming bug or interpretation of the standard) in a test, then report this error to NIST
or an SQL testing laboratory. You should report this discrepancy in your conformance
analysis as an unresolved issue.
3. Examine the output of REPORTZX. Three reports are produced by program
REPORTZX: PROBLEMS.LST, TOTALS.LST, and TESTNO.LST. These three files
are rewritten as one file, COMBINED.LST, to simplify printing. Print COMBINED.LST.
The PROBLEMS list should be almost empty for a conforming SQL implementation. This
is a list of test cases which require attention because they are missing or failed. Typically,
a missing or failed test is a nonconformity; however, there are a few exceptions. Test
number 0399 (for Embedded SQL C only) is always missing, because it require visual
inspection in order to pass. See Special Notes on Individual Programs above. Missing
flagger tests (programs FLG*) are judged to pass if they will not compile because the
tested extension is not supported. The compilation and execution log of any test on the
PROBLEMS list must be reviewed.
The TOTALS list should be compared for accuracy against the file REPORT.TOT in the
directory REPORT. The file REPORT.TOT contains control totals for the most common
profiles. Other profiles are possible. Failure to match the control totals means either the
control totals are wrong (NIST will fix them), the SQL implementation has errors
processing the SQL queries in REPORTA and REPORTB (this is a nonconformity), or
there is some operational error in running the automated reporting system.
The TESTNO list shows the contents of TESTREPORT, rearranged for reporting in the
profiles claimed. Test numbers are sorted within the appropriate subprofile. The result
(pass / fail / missing / not applicable) for all test suite types appear on a single line,
facilitating comparison among the supported interfaces. A test number from
TESTREPORT may appear more than once, if it is required by more than one profile; e.g.,
X/Open and FIPS 127-2 Entry. A test number from TESTREPORT will not appear in the
TESTNO list if it is not required by any of the profiles claimed. Duplicate results (e.g.,
duplicate "pass" results) will appear only once. Conflicting results (e.g., "pass" and "fail"
results for the same test number in the same interface) will appear as a "fail".
The TESTNO list subprofile for "Entry Syntax Flags" is special; It must be evaluated
further. For each "pass" result, the SQL implementation is judged to be supporting an SQL
extension and should demonstrate warning message(s) that an extension is being used (as
required by Section 10.d of FIPS 127-2). For each "nogo" or "missing" a fatal compilation
error or run time error must appear in the execution log for the test. This error
demonstrates that the SQL implementation does not support the SQL extension in the test,
and therefore does not need to flag the extension. In summary, each documented error in
the "Entry Syntax Flags" subprofile satisfies its test objective, and is judged to be
conforming.
The TESTNO list subprofiles for "Entry Catalog-Lookup Flagging" and "Miscellaneous
Informational" are strictly informational, listing the optional tests and showing the pass/fail
results for those tests. These subprofiles are routinely run for FIPS 127-2 testing.
4. Prepare a Report Package, including:
a. Full description of test environment. This would include date, testing staff, hardware
make/model, software description and versions for precompiler/ compiler/linker/SQL
engine/communications software/libraries, special installation parameters, special
processor parameters. For client/server architectures, describe both platforms and
communications software/hardware.
b. Automated Summary Report COMBINED.LST
c. Listing of global changes to schema and program files (or listing of the CHG*.TED
files). (This would include data types/precision for SQLCODE and indicator
variables.)
d. Listing of specific changes to individual schema or program files (or listing of the
CHG*.TED files).
e. Log of schema creation run. Include earlier logs of failed schema runs containing
privilege violations which were removed.
f. Screen capture or printout of executing programs showing pass/fail printout.
g. Printouts of FLG005 through FLG013 SQL/preprocessor compilations, with the "SQL
Flagger" turned on and again with the flagger turned off. Failure to flag any supported
extensions in the programs FLG005, FLG008, and FLG010 and FLG013 is a
nonconformity in any of the test suite types, including Interactive Direct SQL.
However, since flagging which requires schema access is optional, failure to flag in
program FLG006 or FLG009 is not considered a nonconformity.
h. Printouts of SQL compilations detecting syntax errors for privilege violations or
standard SQL run-time errors detectable at compilation time. (These will document
the detection of the error and may be needed to change a "missing" to a "pass" )
i. Listing of driver scripts (i.e. operating system command files, shells, makefiles,
runstreams, or JCL) used to process the schema and program files, including any
special parameters settings.
j. Description of how login was accomplished, including a listing of the modified
AUTHID or included file.
k. A diskette or tape copy of all schema and program files (as modified and run), to be
used for regression testing and to resolve any future disputes over changes made to the
SQL test suite.
l. A diskette or tape copy of a full Interactive Direct SQL run (with the possible
exception of the MP* test cases). You may then avoid the tedium of rereading the
Interactive Direct SQL log at some future date by using a DIFF utility for regression
testing.
If you are a vendor, you will want to use this information (1) to guide your standards-conformance
effort, (2) to assist federal agencies or other users who may be evaluating the conformance of your
product to FIPS PUB 127-2, or (3) to demonstrate to the SQL testing laboratory, when you request
to schedule a validation, that you are prepared to conduct the testing efficiently.
If you are a user, you may use this information (1) as part of your acceptance testing of
implementations claiming conformance to FIPS PUB 127-2 or other SQL profiles, (2) as input to
your RFP process, or (3) as a measure of your current SQL implementation's conformance and hence
a measure of the portability of your standard application programs using standard SQL language.
12. SOFTWARE MAINTENANCE
As users of the test suite work with the software, questions arise about allowable changes to the
programs and schema files. Questions usually fall in one of the following areas: (1) interpretation
of the ANSI or ISO SQL standards, (2) interpretation of FIPS 127-2, (3) possible errors in the test
suite, (4) procedures for running the test suite, (5) procedures for validations.
SQL Updates
Answers to questions of general interest which are answered in narrative form will be distributed to
test suite users as SQL TESTING UPDATE newsletters. These newsletters are to be viewed as
updates to our test suite documentation and to our published procedures for validations.
TEd Maintenance File
Some questions result in changes to the test suite software. These approved maintenance changes
will be distributed periodically to test suite users on paper. The changes are formatted as inputs to
the Test Suite Editor, TEd. The changes should be appended to the cumulative maintenance file,
UPD600.TED.
The cumulative maintenance file is available in machine-readable formats. Users may access the
maintenance file on NIST World Wide Web pages.
Validation Considerations
When an SQL testing laboratory performs a validation, a special validation version of the test suite
may be used. This version contains the same SQL test cases as the original distribution version.
There have been only a few changes made to the software in the validation version. With these
changes, test laboratory staff can tell that the validation is being run from the software they bring,
rather than a distribution version downloaded earlier.
The validation version does not contain maintenance updates, so your TEd file UPD600.TED must
have them. Your TEd file should have exactly the same effect on the validation programs as on the
programs of your distribution version. Before the validation, test laboratory staff will review your
TEd file(s) and make sure that all mandatory maintenance changes are included. They will also
review any implementor-proposed changes and determine whether these changes are allowed under
the procedures of the test suite or whether these changes are nonconformities.
13. SQL TEST SUITE REFERENCE MATERIALS
A variety of references are attached to assist you in your testing and evaluation.
APPENDIX A. Examples of Driver Scripts
These are just examples which work in certain environments. There is no guarantee that any of these
will work for you. Note that TEd acts as a filter for NIST schema files and program files.
APPENDIX B. Base Data for Primary Test Tables
This is a schematic representation of the simple tables used for most of the tests in the HU schema.
This represents the contents of the tables STAFF, PROJ and WORKS after the program BASETAB
has been run. Note that most of the other tables in the HU schema are empty. In order to make tests
reproducible, programs in the test suite will (1) execute a ROLLBACK if base data is changed or
(2) delete all rows from a table at the beginning of a test if an auxiliary table is used. BASETAB
should be run whenever there is any question that the data in the HU schema may have been
corrupted.
For a listing of base data used by the various schemas, refer to the appropriate dataload programs.
The Interactive Direct SQL formats are the shortest and easiest to read.
APPENDIX C. TESTCASE columns (TESTNO, PROG, DESCR)
This is a listing of all the tests in the test suite. It shows the three columns from table TESTCASE:
TESTNO (test number), PROG (program containing the test), and DESCR (a 50-character
description).
APPENDIX D. TEd Change Files
APPENDIX D.1 is a listing of the downloaded file CHGPCO.TED. There is a similar file for each
of the eleven test suite types and for the schema. APPENDIX D.2 is a listing of this file after editing
by the tester to specify changes to the NIST Test Suite programs. Note that the tester has attempted
to place each change in some category. This file is sent to the SQL testing laboratory as part of the
prevalidation package of the SQL implementation to be tested. The laboratory examines each of the
proposed changes and determines whether it will be considered a nonconformity. APPENDIX D.3
is an example of a counter-proposed file CHGPCO.TED, showing how the SQL testing laboratory
interprets the proposed changes and how it is willing to prepare a validation report.
APPENDIX E. Sample Printout from Program Execution
This is a sample of what will print on standard output upon execution of any of the sample programs.
APPENDIX F. Sample Summary Reports
This is a sample printout of the three reports produced by REPORTZX: PROBLEMS, TOTALS,
and TEST RESULTS. Your results will differ.
APPENDIX G. "SQL Flaggers" Examples
Considerable latitude is given to vendors in how to meet the SQL Flagger requirement of FIPS PUB
127-2 (Section 10.d). This appendix is only a suggestion of how a vendor may monitor SQL syntax
for conformance to Entry SQL-92. Desirable features include: designation of the token which
begins the syntax for the extension, identification of the name of the extension being used, and
location of all extensions within each statement (as opposed to locating only the first).
APPENDIX H. Automated Reporting System Diagrams
APPENDIX H.1 shows the Table Definitions (CREATE TABLE statements) for the Reporting
System. APPENDIX H.2 through APPENDIX H.4 are diagrams that are helpful in understanding
the architecture of the automated reporting system. These diagrams are not included in the ASCII
version of the SQL User's Guide in directory OTHER. They are stored as PostScript files in
directory REPORT.
APPENDIX H.2, Test Reporting Structure, depicts a dataflow for the three programs in the reporting
structure (REPORTA, REPORTB, and REPORTZX). This diagram is helpful when switching
reporting options and/or restarting the reporting system. APPENDIX H.2 is PostScript file
REPORTIN.PS.
APPENDIX H.3, SQL Testing Profiles, is a four-page diagram stored in four PostScript files:
APPENDIX H.4, Reporting System Tables, diagrams the major tables defined in APPENDIX H.1.
APPENDIX H.4 is useful in identifying referential constraints among these tables. It is PostScript
file REPTABLS.PS.
APPENDIX I. Informational Interactive Currency Tests
These are detailed instructions on how to test the Interactive SQL interface for serialized
transactions. Eight different concurrency challenges are created by executing files and typing SQL
statements according to scripts.
14. DESIGN NOTES
After six releases of the test suite, many suggestions for improvement have been incorporated into
the software. There are other good suggestions which we have not incorporated, due to either lack
of resources or as a conscious decision. The purpose of this section is to answer questions about the
structure of the test suite and to explain some of our design decisions.
Version 5.0 of the test suite is the first version where the test cases for many features preceded all
known implementations of the features. Consequently, the new tests are written with many subtests
and variations on the features under test. We assume implementors will be relying on our tests as
regression tests (for which they are not really intended), so we have made an effort to make the tests
more thorough. Also, there is considerably more printing to assist in debugging.
The following are comments about frequently asked questions which are of general interest.
1. Most of our early tests determine if an SQL feature passes or fails after checking only some
of the expected results, rather than systematically verifying that all values returned
(including SQLCODE) are identical to expected values. The latter approach requires
considerably more coding. With Version 5.0 of the test suite, we have begun to check
status return codes and retrieved data values more thoroughly. We feel the early approach
will be successful in finding nonconformities; although, it may be less successful in finding
bugs.
2. Many of our early tests base their pass/fail analysis on the returned value of SQLCODE.
Although some of our tests do require an SQLCODE of 100 (no data) to pass, many tests
accept an SQLCODE > 0 when the standard specifies that the value should be 100. In
these circumstances our tests do not adhere strictly to the SQL standard; however, they do
not penalize conforming implementations. The purpose of testing for SQLCODE > 0 is
to prevent the case where an implementation fails tests designed to test features (other than
SQLCODE return values) when that implementation does indeed support those features.
If an implementation does not support the standard values for SQLCODE, then it will fail
tests 8, 13, 18, 24, etc.
3. It would obviously be useful for debugging if SQLCODE return values were printed after
every SQL statement. We suggest you use our editor to install (temporarily) a printout of
SQLCODE after each SQL statement, if needed. New tests (starting with Version 5.0)
print both SQLCODE and SQLSTATE consistently. This makes the screen display look
like a trace and is less pleasing than the earlier style.
4. Testing security (access rules violations) is difficult. We recognize access control may be
enforced differently across implementations. An implementation may reject a program at
syntax-evaluation time or may reject an SQL statement at run time. An implementation
may treat an unauthorized table or column as non-existent or as empty. Our tests expect
that unauthorized users should not be able to modify the rows in the database, nor should
unauthorized users be able to retrieve results based on actual data values.
5. Some vendors produce syntax errors for test cases where it can be determined at syntax
evaluation time that a general rule will be violated at execution time. For example,
inserting a literal NULL into a column which has a NOT NULL constraint can be
determined to be an error at syntax evaluation time. The table TESTCASE contains a
value "synvio_yes" in column T_NOTE for tests in this category to indicate that a syntax
violation is likely. This message prints on the PROBLEMS list when a test is missing. A
vendor may pass these tests, by demonstrating appropriate error messages.
6. Many of the tests that change the data in the tables (via INSERT, DELETE, UPDATE)
issue a ROLLBACK statement (after test evaluation) to restore the data to its original state
and to make the test repeatable. If the implementation does not support ROLLBACK or
if it has an autocommit feature turned ON then the data is left in a changed state, and the
tests may not be reproducible. If the data is corrupted, the data load programs may be
executed again to restore the data.
7. Most new test programs (for Transitional SQL features) create the tables and views needed
by the test. At the end of the test, the tables and views are dropped in order to facilitate
retesting. This approach makes the test more modular and easier to debug. This approach
was not possible for Entry SQL tests, since schema manipulation is a Transitional SQL
feature.
8. Many of the new tests, especially those for dynamic SQL, need to create long character
strings. Rather than creating a long character string in the host language, we have used the
SQL concatenation expression. This decision was made because our translation software
is not able to process concatenation of C character arrays or continuation of character
literals.
9. Except for the SQL Flagger tests and the privileges violations tests, our goal is to write
programs which adhere strictly to the syntax of the standard. Several beta testers suggested
that we test for violations of syntax; e.g., creating a duplicate table name or a referencing
an undefined column. Unlike some other standards, SQL allows a conforming
implementation to provide additional facilities or options not specified by the standard.
The semantics of such syntax is implementation-defined. It is beyond the scope of our
SQL test suite to evaluate an extension. Our only requirement is that extensions be
explicitly identified when the SQL Flagger is "on."
10. The variable names in the test suite programs are short and consequently not very
descriptive. For most programs, our data names are restricted to six characters so the
programs can be translated into FORTRAN. With this restriction, programs do not have
data names as descriptive as Ada, C, COBOL, and Pascal programmers would expect.
11. Some users noted that our tests do not consistently close each cursor that has been opened.
According to the standard, a cursor that is opened may be closed explicitly by a CLOSE
statement, or implicitly by a COMMIT or ROLLBACK. Our tests use all three methods.
12. SQLSTATE tests are difficult to code for conditions involving implementation-defined
precision or other features. If the code (logic) in a program is unable to cause the error
condition under test, then the program logic assigns a "pass" for that SQLSTATE test.
This does not mean that the SQL implementation actually produces the SQLSTATE code.
It means that the SQL implementation did meet all the requirements of the test case. It also
means that we need to "build a better mouse trap."
An example of this situation is the test for data exception - indicator overflow (SQLSTATE
22022). We create a CHAR(33000) column, hoping to overflow a 16-bit indicator variable
on a SELECT. If the SQL implementation supports more precision in the indicator, then
the overflow condition is not raised. Or, if the SQL implementation does not support
CHAR(33000), which is larger than the FIPS 127-2 requirement of at least CHAR(240),
then we cannot execute the test case and a "pass" is assigned. Other difficult-to-test
SQLSTATEs are: error in assignment, invalid parameter value, numeric value out of range,
and serialization failure. If you have a better test case for any of these difficult-to-test
conditions, please donate it to us. We are always open to suggestions for improved testing
strategies.
13. Many of the Version 3.0 programs carried forward into subsequent versions still contain
references to the SQL-89 standard. We apologize, even though the research to obtain the
correct reference is rather straightforward. The effort to update all programs and re-test
them would have delayed release for several months. In retrospect, the references probably
do not belong in the programs, since the SQL standard evolves rather rapidly.
We have developed a documentation scheme that is intended to satisfy ISO requirements
for test suites to be used internationally. We have documented new test cases in the ISO
format in files DOCUCTS5.TXT and DOCU_V5.TXT as well as inside the programs
themselves (as print statements and comments). Test cases from Version 4.0 are
documented less rigorously in file DOCU_V4.TXT. File DOCUNIST.TXT provides a
convenient list of all test cases developed by NIST for both Versions 5.0 and 6.0. As
always, if users of the test suite have questions about references, or if they wish to
challenge our interpretation of the standard, we welcome their calls and email.
15. ANNOTATED BIBLIOGRAPHY
The following is an informal listing of documents of interest to users of the SQL Test Suite and to
federal agencies acquiring SQL implementations. Frequently requested items are listed first.
Validated Products List
o accessible on the World Wide Web at URL address ftp://speckle.ncsl.nist.gov/vpl/sqlintro.htm
o lists tested SQL products and publicly available Validation Summary Reports (VSRs).
o contains testing information for the following Information Technology Standards: Programming
Languages COBOL, Fortran, Ada, and C; Database Language SQL; Graphics; POSIX; and
Computer Security
Miscellaneous documents/forms/reports available on NIST Web pages
o The table of contents page for the SQL Projects is URL address
ftp://speckle.ncsl.nist.gov/sql-testing/contents_sql.htm
o SQL TESTING UPDATEs (testing "updates" and "status reports")
o SQL Validation Questionnaire
o SQL Processor Validation Procedures
FIPS PUB 127-2, Database Language SQL
o Federal Information Processing Standard Publication, issued by NIST (30 pages), dated June 2, 1993
o URL: http://www.itl.nist.gov/div897/pubs/fip127-2.htm
o packaged with the adopted specification ANSI X3.135-1992 (580 pages) when purchased from NTIS
o available from NTIS (703) 487-4650
Database Language - SQL
o published as ISO/IEC 9075:1992, Database Language - SQL
o except for a different Foreword, Introduction, and Normative references, this is identical to ANSI X3.135-1992
o available from ANSI international sales office (212) 642-4900
SQL Technical Corrigendum 2
o referred to as ANSI/ISO/IEC 9075 TC2, Database Language SQL - Technical Corrigendum 2
o SQL information bulletin to inform the public of responses to interpretation requests and errata against the SQL standards ISO/IEC 9075:1992 and ANSI X3.135-1992. It is a list of all formally approved corrections, including typos, missing rules, corrected rules, etc. from the originally published SQL-92.
o Technical Corrigendum 2 (TC2), adopted in 1995, supersedes Technical Corrigendum 1 (TC1), published in 1993.
o included with any new purchase of SQL-92, and can be obtained from ANSI by asking for
document number JTC1/SC21 N10146.
Call Level Interface (CLI)
o published as ANSI/ISO/IEC 9075-3:1995 Database Language SQL - Part 3: Call Level Interface.
o evolved from the popular ODBC specification
o standardizes over 40 functions used as an application programming interface (API)
o is being extended to support handles for the management of large objects and to extend the facilities for metadata access and exception diagnostics (expected in 1999).
o available from ANSI.
Persistent Stored Modules (PSM)
o published as ANSI/ISO/IEC 9075-4:1996, Database Language SQL _ Part 4: Persistent Stored Modules.
o evolved from the popular stored procedure capabilities in may client-server products.
o extends SQL to be a computationally complete, block structured programming language with support for functions, procedures, program variables, flow-of-control statements, and sophisticated exception handling and exception resolution.
o available from ANSI
Remote Database Access (RDA)
o published as ISO/IEC 9579-1 and 9579-2, Remote Database Access
o Part 1: Generic Model, Service and Protocol
o Part 2: SQL Specification
o available from ANSI international sales office (212) 642-4900
o ISO/IEC 9579-2:1997, RDA SQL Specialization Amendment, adds facilities to support all
conformance levels of Database Language SQL, rather than just Entry SQL as specified in the
original. It is published as a consolidated document, thereby replacing the original
FIPS PUB 193, SQL Environments
o FIPS PUB 193, dated February 3, 1995
o specifies SQL profiles that can be used to support integration of legacy databases and other non-SQL data repositories into an SQL environment.
o a non-mandatory FIPS that may be invoked on a case-by-case basis subject to various database integration objectives.
o URL: ftp://speckle.ncsl.nist.gov/isowg3/FIPSdocs/fips193.{ps | txt}
CAE Specification, Structured Query Language (SQL)
o Common Applications Environment (CAE) specification produced by X/Open Company Ltd., August 1992. The CAE Specification describes X/Open as an independent, worldwide, open systems organization supported by most of the world's largest information systems suppliers, user organizations and software companies.
o available from X/Open Company Ltd., U.K., XoSpecs@xopen.co.uk
16. ONGOING SQL STANDARDIZATION - SQL3
The standard specification of SQL is under continual development with draft documents at various
stages of development available from your National Body representatives in the ANSI/ISO/IEC
standardization process. In the United States, ANSI/X3 technical committee X3H2 (Database) is
a very active contributor and the U.S. technical advisory group (TAG) to this process. Draft
documents are all available from ANSI as JTC1/SC21 working documents and include the
following:
SQL Part 1 - Framework: An overview document intended to explain the new Part structure for
future SQL development. It will also describe procedures for claiming conformance to the base
standard and to various optional levels and components. Publication expected in 1999.
SQL Part 2 - Foundation: The basic definition of the SQL language. A substantial, upward compatible extension of the language facilities specified in SQL-92, including triggers, assertions,
recursion, new data types for handling collections and large objects, as well as user-defined abstract
data types (ADTs), type hierarchies, inheritance, polymorphism, and other facilities normally
associated with object data management. However, object references are specified in Part 8 rather
than in Part 2. This part of SQL will be a leveled specification, with nested levels analogous to those
specified in SQL-92. Implementations will be able to claim conformance at a specific level. This
document reached ISO/IEC Committee Draft (CD) status in 1996 with final adoption and publication
expected by 1999.
SQL Part 3 - Call Level Interface: An upward compatible extension to the existing SQL/CLI
standard, which is published as ANSI/ISO/IEC 9075-3:1995. This document is expected to reach
Committee Draft (CD) status sometime in 1997.
SQL Part 4 - Persistent Stored Modules: An upward compatible extension to the existing
SQL/PSM standard, which is published as ANSI/ISO/IEC 9075-4:1996. This document is expected
to reach Committee Draft (CD) status sometime in 1997 or 1998.
SQL Part 5 - Bindings: Specification of Dynamic SQL and Embedded SQL interfaces to standard
programming languages such as Ada, C, COBOL, Fortran, Mumps, Pascal, and PL/I. The basis of
this document is taken from the Dynamic SQL and Embedded SQL sections of SQL-92, but new
facilities have been added to accommodate handles and other new SQL data types in various
programming languages. This document reached ISO/IEC Committee Draft (CD) status in 1996
with final adoption and publication expected by 1999.
SQL Part 6 - XA Specialization: Originally approved as a project to be developed, if needed, to
supplement the ISO/IEC project on Distributed Transaction Processing - The XA interface. XA is
a collection of popular distributed transaction processing interfaces developed by X/Open. With
successful publication of an ISO/IEC XA specification in 1996 (see document JTC1/SC21 N10133),
it is not clear if this SQL specification is still necessary. The project was not very active in 1996
with very little technical content in the working draft base document.
SQL Part 7 - Temporal: The intent of this project is to add support for temporal data management,
such as valid time and transaction time, to the SQL language. The TSQL2 Language Specification
prevalent in the academic literature during 1994 to 1995 is the basis for much of the new
development. This document is expected to reach Committee Draft (CD) status sometime in 1998.
SQL Part 8 - Extended Objects: The intent of this project is to specify how object identity is to
be handled in SQL. The base document specifies named row types, references to instances of named
row types, and reference and dereference operators, as well as other related facilities for object data
management. This document reached ISO/IEC Committee Draft (CD) status in 1996 with final
adoption and publication expected by 1999.
SQL Part 9 - Virtual Tables: The intent of this newly adopted project is to specify virtual tables
as the mechanism for allowing SQL language access to legacy files and data repositories. The
specification will provide tools that a database administrator can use to simplify the process of
making such legacy data available to standard conforming SQL applications. This document is
expected to reach Committee Draft (CD) status by 1999.
ANSI/ISO/IEC 9075 TC3, Database Language SQL - DRAFT Technical Corrigendum 3: This
draft corrigendum to all existing published parts of Database Language SQL includes corrections
informally approved by the SQL development group since publication of TC2 in 1995, including
corrections to SQL/CLI-95 and to SQL/PSM-96. It has not yet been submitted to ISO/IEC for
formal adoption and publication, although such submission is expected sometime during 1997. The
draft document is available from any active participant in the SQL standardization process.
APPENDIX A.1
VAX/VMS using Rdb
DCL Listing for Embedded C
APPENDIX A.2
VAX/VMS using Oracle
DCL Listing for Embedded FORTRAN
$ted -t upd600.ted -t chgpfo.ted 'p1.pfo $proc iname='p1 host=fortran include=sys$oracle: $for 'p1 $@dra4:[oracle]loutl 'p1 'p1,authid,sys$oracle:sqllib/lib map$:s $run 'p1 |
APPENDIX A.3
UNIX Command Language for Unify
Embedded C