Skip to main content

%UnitTest.TestSqlScript

class %UnitTest.TestSqlScript extends %UnitTest.TestScript

The sample class mentioned here (Sample.Person) is part of https://github.com/intersystems/Samples-Data.Opens in a new tab See Downloading SamplesOpens in a new tab.

This class replays script-driven SQL tests. It provides an implementation of the runScript() method that reads and executes SQL statements from the script specified in the TESTFILE parameter, comparing it to a reference log of expected output. It also provides an implementation of the OnBeforeAllTests() method that populates the XML-enabled class under test (DATACLASS) from DATAFILE.

To write a test, start by creating a subclass of this class with appropriate values for the DATACLASS, DATAFILE, and DATATAG parameters: 

  Class Sample.SqlTest extends %UnitTest.TestSqlScript {
  Parameter DATACLASS = "Sample.Person";
  Parameter DATAFILE = "person.data";
  Parameter DATATAG = "person";
  }
Populate the class under test: 
  Write ##class(Sample.Person).Populate(100)
Export the instances of the class under test to the data file: 
  Set writer=##class(%XML.Writer).%New()
  Set writer.Charset="UTF-8"
  Set sc=writer.OutputToFile("person.data")
  Set sc=writer.RootElement("people")
  Set statement=##class(%SQL.Statement).%New()
  Set sc=statement.%PrepareClassQuery("Sample.Person","Extent")
  Set result=statement.%Execute()
  While result.%Next(.sc) {
  	Set id=result.%Get("ID")
  	Set person=##class(Sample.Person).%OpenId(id)
  	Set sc=writer.Object(person,"person")
  }
  Set sc=writer.EndRootElement()
  Set sc=writer.EndDocument()
Create a script file with a series of SQL statements, each terminated by a line starting with "####": 
  select max(SSN) from Sample.Person
  ####
  select count(ID) from Sample.Person
  ####count IDs
Place the following files in a sub-directory of ^UnitTestRoot: To generate the expected results, run the test with the "reference" parameter: 
  Do ##class(%UnitTest.Manager).RunTest("Sample",,"reference")
It populates the data class, executes the SQL statements, and writes the expected output to reference.log: 
  SQL> select max(SSN) from Sample.Person
  Aggregate_1:
  992-27-1936:
  
  SQL> select count(ID) from Sample.Person
  Aggregate_1:
  100:
  
  Test count IDs
Subsequent runs without the "reference" parameter will write to output.log, then compare it to reference.log: 
  Do ##class(%UnitTest.Manager).RunTest("Sample")

Method Inventory

Parameters

parameter CORRELATIONLIST;
List that correlates XML tags in a data file with classes. Entries are of the form tag1:class1, ..., tagn:classn, with each entry specifying a pair of DATATAG and DATACLASS parameter values.

If CORRELATIONLIST, DATACLASS, and DATATAG are all specified, the DATATAG and DATACLASS pair are added to the end of the CORRELATIONLIST.

parameter DATACLASS;
Specifies an XML-enabled class, such as Sample.Person, to be correlated with the DATATAG in the DATAFILE.
parameter DATAFILE;
XML file, such as person.data, that contains data for the DATACLASS. If no directory name is specified by RunTest, then the current UnitTest directory is used.
parameter DATATAG;
Specifies an XML tag in DATAFILE, such as <person> (angle brackets omitted), to be correlated with the DATACLASS.
parameter DISPLAYELAPSEDTIME = executed and fetched in ;
When TIMESCALE is a positive number, this parameter is part of the elapsed time message written to output.log. Override it to localize the display of elapsed time.
parameter DISPLAYSECONDS = seconds;
When TIMESCALE is a positive number, this parameter is part of the elapsed time message written to output.log. Override it to localize the spelling of "seconds."
parameter DISPLAYTESTNAME = Test ;
If a test name is specified after the #### terminator in the script, it is written to output.log. Override this parameter to localize the spelling of "Test."
parameter SHOWPLAN;
Set this parameter to 1 to dump the access plans used by each test in the test script. Use this feature when you need to verify plan stability from one release to the next, or when you want to investigate the impact of creating, dropping, or altering an index.
parameter TIMESCALE;
This parameter specifies the number of significant digits used to display elapsed time for each SQL statement to output.log. It can be used as a coarse indicator of differences in the run time of any individual query.

The default value of "" prevents elapsed time information from being displayed at the end of each SQL statement in the test. Setting it to 1 will display the result in seconds. Setting it to 10 will display elapsed time in multiples of 10 seconds. Setting it to 0.1 will display elapsed time to the nearest tenth second.

Methods

method OnBeforeAllTests() as %Status
This method deletes the extent of the DATACLASS and populates it from the file specified by the DATAFILE parameter.
classmethod runScript(scriptfile As %String, outputfile As %String) as %Status
This method reads SQL statements from scriptfile, writing their output to outputfile. Statements are terminated by a line that starts with "####".

Inherited Members

Inherited Properties

Inherited Methods

FeedbackOpens in a new tab