Testing environment for database server side logic

[0001] Under 35 USC §120, this application is a continuation application and claims the benefit of priority to U.S. Pat. No. 7,406,679 issued Jul. 29, 2008, entitled “Testing Environment for Database Server Side Logic”, all of which is incorporated herein by reference.

[0002] The present invention relates to server-side objects, and more particularly to the testing of server-side objects in a database network.

[0003] An important part of the development of any database server-side object, such as stored procedures, user-defined functions, methods, triggers, or any other type of objects running at the database server. The developer codes the object, builds it at the database server, and tests the execution of the object by performing a test run. For simple objects, the test run includes passing input parameter values to the object, executing the object, and obtaining a result. However, in most cases, the objects are more complicated and require setup in order to more accurately simulate the production environment in which the object will ultimately be executed. The environment to be setup can range from the simple to the complex. For complex environments, the setup could include setting up test data, creating other database artifacts such as regular or temporary tables, setting up database environment properties, such as current schema, function path, etc. Once the environment is set up, the developer can perform the test run and pass input parameters values to the object. At the completion of the test run, the environment typically needs to be destroyed. Sometimes, the developer codes additional procedures or functions for the purpose of setting up or destroying the environment. The developer will likely need to perform multiple test runs in the course of the development of the object.

[0004] However, with current development tools, for each test run performed, the developer must manually set up the test environment, manually pass the input parameter values to the object to be tested, and manually destroy the test environment. If additional procedures or functions were coded to set up or destroy the environment, these procedures or functions must be recoded each time a test run is performed. This is cumbersome and time consuming for the developer and negatively impacts the efficiency of the development process.

[0005] Accordingly, there exists a need for an improved method and system for providing a test environment for database server-side objects. The present invention addresses such a need.

[0006] A method and system for providing a test environment for database server-side objects persistently stores a profile for a plurality of test runs for database server-side objects, where the profile comprises persistently stored test parameter values, pre-execution scripts, and/or post-execution scripts. Once the profile is created, it can be selected for subsequent test runs for one or more objects. A plurality of profiles for the objects may also be created. The method and system eliminates the need for a manual set up or destruction of the test environment for each test run, improving efficiency in the development of database server-side objects significantly.

[0012] The present invention provides an improved method and system for providing a test environment for database server-side objects. The following description is presented to enable one of ordinary skill in the art to make and use the invention and is provided in the context of a patent application and its requirements. Various modifications to the preferred embodiment will be readily apparent to those skilled in the art and the generic principles herein may be applied to other embodiments. Thus, the present invention is not intended to be limited to the embodiment shown but is to be accorded the widest scope consistent with the principles and features described herein.

[0013] The method and system in accordance with the present invention persistently stores a profile for a plurality of test runs for database server-side objects, where the profile comprises persistently stored test parameter values, pre-execution scripts, and/or post-execution scripts. Once the profile is created, it can be selected for subsequent test runs for one or more objects. A plurality of profiles for the objects may also be created.

[0014] To more particularly describe the features of the present invention, please refer to FIGS. 1 through 5 in conjunction with the discussion below.

[0015] FIG. 1 illustrates a preferred embodiment of a system for providing a test environment for database server-side objects in accordance with the present invention. The system 100 comprises a client 102, a server 104, and a database 106. The developer at the client 102 creates a server-side object 108 to run against the database 106. To test the object 108 during development, the developer creates a persistently stored profile 110. In the preferred embodiment, the profile 110 is stored at the server 104, however, the profile 110 may be persistently stored elsewhere, such as at the client 102, without departing from the spirit and scope of the present invention.

[0016] The profile 110 defines any combination of input values, commands, or other test settings that are to be executed during a test run. In the preferred embodiment, the profile comprises test parameter values 112, pre-execution scripts 114, and/or post-execution scripts 116, which are also stored persistently. Test parameter values 112 comprise input parameters values to be passed to the object 108 during execution. Pre-execution scripts 114 comprise scripts that set up the testing environment prior to the test run of the object 108. The setup could include setting up test data, creating other database artifacts such as regular or temporary tables, setting up database environment properties, such as current schema, function path, etc. Post-execution scripts 116 comprise scripts that “clean up” or destroy the test environment once the test run of the object 108 has completed. More than one profile for the object 108 can be persistently stored. Because the profile 110 is persistently stored, it can be used for a plurality of test runs of the object 108. In the preferred embodiment, the server 104 comprises software that assists the developer in creating a persistently stored profile 110.

[0017] FIG. 2 is a flowchart illustrating a preferred embodiment of a method for creating a persistently stored profile for the test environment for database server-side objects in accordance with the present invention. The developer can choose to define test parameter values 112, pre-execution scripts 114, and/or post-execution scripts 116. If the developer creates pre-execution script(s), then the server 104 receives one or more pre-execution scripts 114 for the profile 110, via step 202. If the developer defines test parameter values, then the server 104 receives the test parameter values 112 for the profile 110, via step 204. If the developer creates post-execution script(s), then the server 104 receives one or more post-execution scripts 116 for the profile 110, via step 206. Once the test parameter values 112, pre-execution script(s) 114, and/or post-execution script(s) 116 are received for the profile 110, the profile 110 is persistently stored, via step 208. The profile 110 can now be used for test runs of the object 108.

[0018] FIGS. 3A-3E illustrate an example user interface for creating a persistently stored profile for the test environment for database server-side objects in accordance with the present invention. In this example, the user interface is part of the DB2 Development Center™ from International Business Machines Corporation™. As illustrated in FIG. 3A, the Project View window 302 of DB2 Development Center displays the server-side object(s) in development. For example, a stored procedure 304 is being developed. To set up a profile 110 for a test run of the stored procedure 304, “Run Settings” 306 is selected from a menu. As illustrated in FIG. 3B, a “Run Settings” window 310 is then displayed. The “Run Settings” window 310 comprises several tabs: a “Before Run” tab 312, a “Parameter Values” tab 324, an “After Run” tab 330, and an optional “Options” tab 342.

[0019] As illustrated in FIG. 3B, under the “Before Run” tab 312, one or more pre-execution Structured Query Language (SQL) scripts 114 for the profile 110 can be specified in the input field 314, via step 202, to be run before running or debugging the stored procedure 304. By selected the “SQL Assistant” button 316, a SQL Assistant (not shown) can be displayed to assist the developer in creating the pre-execution SQL scripts. Shared pre-execution SQL scripts can be imported into the input field 314 by selecting the “Import” button 318. The pre-execution SQL scripts 114 created for the profile 110 can be shared with other profiles by selecting the “Export” button 320. Once the pre-execution SQL scripts 114 have been created, the “OK” button 322 is selected, and the pre-execution SQL scripts 114 are persistently stored as part of the profile 110.

[0020] As illustrated in FIG. 3C, under the “Parameter Values” tab 324, the values 112 for one or parameters for the profile 110 can be specified in the Value field 326, via step 204, to be input into the stored procedure 304 during its test run. Once the parameter values 112 have been created, the “OK” button 328 is selected, and the parameter values 112 are persistently stored as part of the profile 110.

[0021] As illustrated in FIG. 3D, under the “After Run” tab 330, one or more post-execution SQL scripts 116 for the profile 110 can be specified in the input field 332, via step 206, to be run after running or debugging the stored procedure 304. By selected the “SQL Assistant” button 334, the SQL Assistant (not shown) can be displayed to assist the developer in creating the SQL scripts. Shared post-execution SQL scripts can be imported into the input field 332 by selecting the “Import” button 336. The post-execution SQL scripts 116 created for the profile 110 can be shared with other profiles by selecting the “Export” button 338. Once the post-execution SQL scripts 116 have been created, the “OK” button 340 is selected, and the post-execution SQL scripts 116 are persistently stored as part of the profile 110.

[0022] Optionally, as illustrated in FIG. 3E, the “Run Settings” window 310 can comprise an “Options” tab 342. Under the “Options” tab 342, various items can be listed. For example, the option 344 to commit the changes to the database can be selected to occur as part of the profile 110. Once the desired options are selected, the “OK” button 346 is selected, and the selected options are persistently stored as part of the profile 110.

[0023] Once the persistently stored profile 110 has been created, it can be selected for test runs of the object 108.

[0024] Although the present invention is described above with the user interface and tabs illustrated above, one of ordinary skill in the art will understand that other user interfaces and/or tabs may be used without departing from the spirit and scope of the present invention.

[0025] FIG. 4 is a flowchart illustrating a preferred embodiment of a method for executing a test run of a database server-side object in accordance with the present invention. First, a selection of a profile 110 is received for a test run of a database object 108, where the profile 110 is persistently stored for a plurality of test runs, via step 402. The test run of the object 108 is then executed according to the selected profile 110, via step 404.

[0026] In our example above, the selected profile 110 comprises test parameter values 112, pre-execution scripts 114, and post-execution scripts 116 as defined above. When the test run is executed, via step 404, the persistently stored pre-execution scripts 114 of the selected profile 110, created under the “Before Run” tab 312, are first run to set up the test environment, via step 406. Next, the persistently stored test parameter values 112 of the selected profile 110, created under the “Parameter Values” tab 324, are input into the object 108, via step 408. The object 108 is then run, via step 410. Then, the persistently stored post-execution scripts 116 of the selected profile 110, created under the “After Run” tab 330, are run to destroy the test environment, via step 412.

[0027] Optionally, as illustrated in FIG. 5, prior to the execution of the object 108, via step 410, a “Specify Parameter Values” window 550 can be displayed to allow last minute changes to the parameter values 112. New values can be entered in the Value field 552.

[0028] Because the profile 110, and its parts 112-116, are persistently stored, it can be used in multiple test runs of the object 108 without requiring manual set up or destruction of the test environment for each test run. The same profile 110 can be used to test the object 108 in various stages of development. The profile 110 can be modified at different stages as well, and used multiple times. The profile 110 can also be used to test run different objects. In addition, a plurality of profiles 110 can be created for the same purposes.

[0029] An improved method and system for providing a test environment for database server-side objects have been disclosed. The method and system in accordance with the present invention persistently stores a profile for a plurality of test runs for database server-side objects, where the profile comprises persistently stored test parameter values, pre-execution scripts, and/or post-execution scripts. Once the profile is created, it can be selected for subsequent test runs for one or more objects. A plurality of profiles for the objects may also be created. The present invention eliminates the need for a manual set up or destruction of the test environment for each test run, improving efficiency in the development of database server-side objects significantly.

[0030] Although the present invention has been described in accordance with the embodiments shown, one of ordinary skill in the art will readily recognize that there could be variations to the embodiments and those variations would be within the spirit and scope of the present invention. Accordingly, many modifications may be made by one of ordinary skill in the art without departing from the spirit and scope of the appended claims.