%UnitTest.TestSqlScript

Class %UnitTest.TestSqlScript Extends %UnitTest.TestScript [ System = 3 ]

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

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:

• the script file (script.txt)
• a copy of the test class (Sample.SqlTest)
• a copy of the class under test (Sample.Person)
• the data file (person.data) 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")

Parameters

CORRELATIONLIST;

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.

DATACLASS;

Parameter DATACLASS;

Specifies an XML-enabled class, such as Sample.Person, to be correlated with the DATATAG in the DATAFILE.

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.

DATATAG;

Parameter DATATAG;

Specifies an XML tag in DATAFILE, such as (angle brackets omitted), to be correlated with the DATACLASS.

SHOWPLAN;

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.

DISPLAYTESTNAME

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."

TIMESCALE;

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.

DISPLAYELAPSEDTIME

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.

DISPLAYSECONDS

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."

Methods

OnBeforeAllTests

Method OnBeforeAllTests() As %Status [ CodeMode = generator ]

This method deletes the extent of the DATACLASS and populates it from the file specified by the DATAFILE parameter.

runScript

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 "####".

runSqlStatement

ClassMethod runSqlStatement(sql As %String, testName As %String) [ Internal ]

This method prepares and executes the SQL statement in sql, formatting the output according to the TIMESCALE parameter et al. If SHOWPLAN is true, it appends the plan to the output.