Conditional Testing of SQL Table Constraints

The script defines the `%conditional` macro which, upon execution, adapts its behavior based on the existence of the `sashelp.vcncolu` view. If this view is present (generally indicative of an environment supporting the management and querying of constraint metadata), the macro creates a `work.example` table with a composite primary key, a composite unique constraint, and a not null constraint. It then uses `%mp_getconstraints` to extract these constraints and `%mp_assert` to verify that 6 constraint records are returned (the exact number may depend on the specific implementation of `mp_getconstraints` for composite keys and multiple constraints). If `sashelp.vcncolu` is absent, the `work.example` table is created without constraints, and the test validates that constraint extraction returns 0 records, confirming the absence of support or visibility of constraints in that environment.

Data Analysis

Type : MIXTE

The script interacts with a system resource (`sashelp.vcncolu`) for its conditional logic. It dynamically creates test data (`work.example`) and result tables (`work.constraints`, `work.test_results`) to validate behaviors related to SQL constraints. No external data not managed by the script is used.

1 Code Block

MACRO

Explanation :
This block defines and executes the `%conditional` macro. It uses an `%IF/%THEN/%ELSE` statement with the `%SYSFUNC(EXIST())` function to check for the presence of the `sashelp.vcncolu` system view. This condition determines whether the test should evaluate a scenario with defined table constraints (if the view exists) or a scenario without constraints (if the view does not exist). The final call `%conditional()` triggers the execution of this logic.

Copied!

1	%macro conditional();
2
3	%IF %sysfunc(exist(sashelp.vcncolu,view))=1 %THEN %DO;
4	PROC SQL;
5	create TABLE work.example(
6	TX_FROM float FORMAT=datetime19.,
7	DD_TYPE char(16),
8	DD_SOURCE char(2048),
9	DD_SHORTDESC char(256),
10	constraint pk primary key(tx_from, dd_type,dd_source),
11	constraint unq unique(tx_from, dd_type),
12	constraint nnn not null(DD_SHORTDESC)
13	);
14	%mp_assertscope(SNAPSHOT)
15	%mp_getconstraints(lib=work,ds=example,outds=work.constraints)
16	%mp_assertscope(COMPARE)
17
18	%mp_assert(
19	iftrue=(%mf_nobs(work.constraints)=6),
20	desc=OUTPUT TABLE work.constraints created with correct number of records,
21	outds=work.test_results
22	)
23	%END;
24	%ELSE %DO;
25	PROC SQL;
26	create TABLE work.example(
27	TX_FROM float FORMAT=datetime19.,
28	DD_TYPE char(16),
29	DD_SOURCE char(2048),
30	DD_SHORTDESC char(256)
31	);
32	%mp_assertscope(SNAPSHOT)
33	%mp_getconstraints(lib=work,ds=example,outds=work.constraints)
34	%mp_assertscope(COMPARE)
35
36	%mp_assert(
37	iftrue=(%mf_nobs(work.constraints)=0),
38	desc=Empty TABLE created as constraints not supported,
39	outds=work.test_results
40	)
41	%END;
42	%mend conditional;
43
44	%conditional()

2 Code Block

PROC SQL Data

Explanation :
This code block is executed when the `sashelp.vcncolu` view is detected. It uses `PROC SQL` to create the `work.example` table with columns `TX_FROM`, `DD_TYPE`, `DD_SOURCE`, `DD_SHORTDESC`, and applies three constraints: a composite primary key (`pk`), a composite unique key (`unq`), and a not-null constraint (`nnn`). Then, the `%mp_assertscope(SNAPSHOT)` and `%mp_assertscope(COMPARE)` macros define a test context. `%mp_getconstraints` is called to extract all constraints from `work.example` into the `work.constraints` table. Finally, `%mp_assert` checks that the `work.constraints` table contains exactly 6 records, thus validating the correct detection of constraints in this environment.

Copied!

1	PROC SQL;
2	create TABLE work.example(
3	TX_FROM float FORMAT=datetime19.,
4	DD_TYPE char(16),
5	DD_SOURCE char(2048),
6	DD_SHORTDESC char(256),
7	constraint pk primary key(tx_from, dd_type,dd_source),
8	constraint unq unique(tx_from, dd_type),
9	constraint nnn not null(DD_SHORTDESC)
10	);
11	%mp_assertscope(SNAPSHOT)
12	%mp_getconstraints(lib=work,ds=example,outds=work.constraints)
13	%mp_assertscope(COMPARE)
14
15	%mp_assert(
16	iftrue=(%mf_nobs(work.constraints)=6),
17	desc=OUTPUT TABLE work.constraints created with correct number of records,
18	outds=work.test_results
19	)

3 Code Block

PROC SQL Data

Explanation :
This code block is executed when the `sashelp.vcncolu` view is not detected. It creates the `work.example` table using `PROC SQL` but without any explicit constraints. As in the previous case, the `%mp_assertscope(SNAPSHOT)` and `%mp_assertscope(COMPARE)` macros establish the test context, and `%mp_getconstraints` attempts to extract the constraints. The final `%mp_assert` assertion validates that the `work.constraints` table is empty (0 records), confirming that no constraints are detected or supported in this environment, which is the expected behavior in this scenario.

Copied!

1	PROC SQL;
2	create TABLE work.example(
3	TX_FROM float FORMAT=datetime19.,
4	DD_TYPE char(16),
5	DD_SOURCE char(2048),
6	DD_SHORTDESC char(256)
7	);
8	%mp_assertscope(SNAPSHOT)
9	%mp_getconstraints(lib=work,ds=example,outds=work.constraints)
10	%mp_assertscope(COMPARE)
11
12	%mp_assert(
13	iftrue=(%mf_nobs(work.constraints)=0),
14	desc=Empty TABLE created as constraints not supported,
15	outds=work.test_results
16	)

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Copyright Info : The code uses macros (`%mp_assert`, `%mp_getconstraints`, `%mf_nobs`, `%mp_assertscope`) that are part of the SASUnit framework. The referenced file `macro_without_brief_tag.sas` indicates a copyright 'Copyright 2010-2023 HMS Analytical Software GmbH', the publisher of SASUnit.

Retour à la liste