Class %UnitTest.TestSqlScript

class %UnitTest.TestSqlScript extends %UnitTest.TestScript

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

Inventory

Parameters	Properties	Methods	Queries	Indices	ForeignKeys	Triggers
9		3

Summary

Properties
AutoPassword	Debug	SkipTest

Methods
%AddToSaveSet	%ClassIsLatestVersion	%ClassName
%ConstructClone	%DispatchClassMethod	%DispatchGetModified
%DispatchGetProperty	%DispatchMethod	%DispatchSetModified
%DispatchSetMultidimProperty	%DispatchSetProperty	%Extends
%GetParameter	%IsA	%IsModified
%New	%NormalizeObject	%ObjectModified
%OnNew	%OriginalNamespace	%PackageName
%RemoveFromSaveSet	%SerializeObject	%SetModified
%ValidateObject	AssertEqualsViaMacro	AssertFailureViaMacro
AssertFilesSQLUnorderedSameViaMacro	AssertFilesSameViaMacro	AssertNotEqualsViaMacro
AssertNotTrueViaMacro	AssertSkippedViaMacro	AssertStatusEqualsViaMacro
AssertStatusNotOKViaMacro	AssertStatusOKViaMacro	AssertSuccessViaMacro
AssertTrueViaMacro	Checkout	LogMessage
OnAfterAllTests	OnAfterOneTest	OnBeforeAllTests
OnBeforeOneTest	TestRunScript	runScript

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

Class Reference IRIS for UNIX 2024.1.2
Documentation Search