class %UnitTest.TestSqlScript extends %UnitTest.TestScript

Adds methods to test an SQL script.

Summary: To test SQL code, you will:

1. Create a namespace, a database, and create a dataset.
   
   
2. Populate the new database from the dataset.
   
   
3. Create a test file that contains SQL select statements that test your feature.
   
   
4. Run the test file to produce a reference file (using the "reference" argument).
   
   
5. Add methods to your test file that
   
   
   1. Clear the database and populate the database from the dataset.
      
   2. Produce results to output.log.
      
   3. Compare output.log to reference.log, and
      
   4. Report whether the two files are the same (Passed) or different (Failed).
      
      

Each time that you run this test, your database always begins from a consistent, known state.

Procedure

(Note: This procedure is under construction.)

To set up a namespace, a database, and a dataset:

1. Create a namespace and a database called UT.
   
   
2. Create a class, such as dev.xyz.classname, where dev.xyz matches perforce directory structure, such dev.sql.Query or dev.obj. (Note that, within your test, you will specify this name with underscores, as dev_xyz.classname).
   
   
3. Set this class to extend %Populate and %XML.Adapter.
   
   
4. Open a Cache terminal, change to the UT namespace and populate this class with sufficient data with the Populate utility.
   

    zn "ut"
    do ##class(UT.classname).Populate(100)
    

5. In Studio, select Tools > Export. Select Add and the UT.classname.cls to an XML file data.xml in dev.xyz.

To create a test that compares a file called output.log to a file called reference.log.

1. Create a new class that extends TestSqlScript called, for example, TestSqlScriptX and link it to the sccs.
   
   
2. Put it in the dev.xyz directory. [Susan - however this works best]
   
   
3. Add instance methods to the new class. First clear the database and repopulate the database from the data.xml file:

   Method OnBeforeAllTests
         delete * from dev_xyz.classname
         import data.xml
    [find a statement to import the XML]
    

   Now the database is in a consistent known state. Method Testdevlog-id [how to automatically take from the devlog what we need or put the URL in the comment]
   
   
4. Create a file called script.txt in your test directory, containing only SQL select code you want to test, such as the following. Note that the four pound signs (####) is a command delimiter. This is needed since it is not unusual for SQL statements to be longer than a single line.

   select max(SSN) from Sample.Person
   ####
   select count(ID) from Sample.Person
   ####
   

   
   
5. Run the test.
   
   d ##class(%UnitTest.Manager).RunTest()
   
   Each line of SQL in script.txt is executed and command output is written to output.log until the end of file in script.txt is reached. output.log is compared to reference.log. The first time that you run the test, you get an error because the reference.log file doesn't yet exist.
   
   
6. Check that the contents of new output.log file are correct.
   
   
7. If they are, create a reference.log file that will be used in future runs of this test by copying output.log and to reference.log. Now your two files match.
   
   
8. Run the test again
   
   d ##class(%UnitTest.Manager).RunTest()
   
   This time the test passes.

Every time you run your test the new output is written to output.log. output.log is compared to reference.log. If the files match, the test passes. If the files do not match, the test fails. If the output changes, you need to update the reference.log file.

Inventory

Summary

Parameters

Methods

---