Test generator for database management systems providing tight joins

#The present invention relates to electronic data processing, and more specifically concerns automated testing of database management systems using statements having a tight join of a plurality of tables.

#Relational database management systems (DBMS), such as Microsoft SQL Server, interpret statements written in a database query language such as Structured Query Language (SQL) to create and manage database objects, to insert and update data, and to perform complex, multilevel queries against huge amounts of data. Testing these systems is recognized throughout the industry as a technical challenge of the first magnitude. SQL and similar database-system interpreters are highly complex. For example, they offer sophisticated optimization techniques and execution planning for queries input on the fly; opportunities for arcane design problems are ubiquitous. At the same time, the state space to be tested is gigantic. For a one-gigabyte database, the possible combinations of database configuration and SQL statement to be executed exceeds 102,000,000,000.

#Libraries of test scripts for relational database systems typically contain thousands or tens of thousands of sample statements which are applied to a test database for comparison of their results with known correct data. Existing libraries are known to be inadequate; most commercial database systems produce a constant and substantial stream of reported bugs. However, the amount of work required to generate larger libraries quickly becomes prohibitive.

#FIG. 1 is a block diagram of an environment in which the invention can be practiced.

#FIG. 2 is a block diagram of an illustrative database system to be tested by the invention.

#FIG. 3 is a flow diagram for a test generator according to the invention.

#FIG. 1 is a high-level block diagram of a conventional client/server computer system 100. Network wiring 110 interconnects a number of personal computers (PCs) 120 to a server 130 via network adapters 121 and 131. Server 130 includes a storage subsystem 132 for holding the large amounts of data in typical enterprise databases. Other system architectures are also suitable environments for the invention; for example, units 120 may be terminals connected to a mainframe or midrange computer 130, or unit 130 may itself comprise a PC coupled to PCs 120 in a peer-to-peer network. For small databases, the entire system 100 may comprise a single PC acting as both client and server. Likewise, file storage may be distributed among a number of different machines. Furthermore, the system can be implemented as a three tier or a multi-tier system. FIG. 1 includes a schematic representations of an external storage medium 133 which may store client and server software for distribution and downloading to clients, and another medium 134, such as a diskette, for offline storage of database tables. Medium 134 can also store instructions and data for the test program of the present invention; the test program can be executed in one or more of the clients 120, or even in server 130.

#FIG. 2 is a block diagram of a typical conventional client/server database management system 200 capable of operating in system 100, FIG. 2. A client application program 210 executes within each PC 120, under a PC operating system 220 such as a version of the Microsoft Windows operating system, including Windows 95®, Windows 98®, Windows Me®, Windows NT®, or Windows 2000®. Among other functions, client application 210 contains a facility 211 for accepting database queries from a user at a PC 120. In addition to user entries, other application programs 230 executing in some of the PCs 120 may present queries to DBMS client 210, via predefined host-language application-program interfaces (APIS) 231. One of these programs can be the test program of the invention.

#Within server 130, a DBMS server application 240, such as Microsoft SQL Server, executes under a server operating system 250 such as Microsoft NT. DBMS program 240 provides services for creating, maintaining, and modifying a number of relational databases, exemplified by database 260. Program 240 may employ the file-system services 251 of operating system 250, or may provide its own file system. Operating system 250 could execute a separate instance of the entire DBMS application for each request from a client 210. For greater efficiency, however, program 240 gives each client connection a separate thread 242 in the DBMS kernel. Further, this thread is a native operating-system thread, which carries with it all the Windows NT mechanisms for process memory protection, better access to storage devices, and so forth. Search engine 241 processes queries from individual clients 210 upon tables 261 of a database 260, as described more fully below. It also enforces database integrity with conventional facilities for record locking, atomic transactions, etc. In the Microsoft SQL Server, the interface language between query facility 211 and search engine 241 is Transact-SQL, which provides much of the function of the standard ANSI SQL, 89 and ANSI SQL 92 languages, plus extensions for providing greater flexibility and programmability.