viewdata.test.3.sas

Go to the documentation of this file.

/**
  @file
  @brief testing public/viewdata service full table search semantics
  @details The viewer's search box runs a full table search through
  %mp_searchdata.  A character search is a partial (CONTAINS) match against
  every character column of the table and is case sensitive; a numeric search
  is an exact match against every numeric column.
 
  The fixture below holds both cases of the same substring so the assertions
  can show the behaviour in both directions - a value with a capital letter is
  not found by a lowercase search and vice versa.
 
  <h4> SAS Macros </h4>
  @li mf_getuniquefileref.sas
  @li mf_getuniquelibref.sas
  @li mp_assert.sas
  @li mx_execute.sas
 
**/
 
%let _program=&appLoc/services/public/viewdata;
 
/* deterministic fixture - mixed case, and a value that only exists in the
  second character column, so the search must cover every character column */
data &dclib..mpe_x_search;
  length NAME $32 NOTES $64;
  name='Selkie';        qty=12345; notes='grey seal folklore';   output;
  name='Vampire squid'; qty=23456; notes='deep sea cephalopod';  output;
  name='siphonophore';  qty=34567; notes='colonial organism';    output;
  name='Smithson';      qty=45678; notes='capital S surname';    output;
  name='Goldsmith';     qty=56789; notes='lowercase substring';  output;
run;
 
/**
  * Run one full table search and assert the row count the viewer would show.
  */
%macro search(searchtype,searchval,expected,desc);
%local f1 outlib nobs;
%let f1=%mf_getuniquefileref();
%let outlib=%mf_getuniquelibref(prefix=web);
data _null_;
  file &f1 termstr=crlf;
  put 'LIBDS:$char41. FILTER_RK:best. SEARCHTYPE:$char4. SEARCHVAL:$char100.';
  put "&dclib..MPE_X_SEARCH,-1,&searchtype,&searchval";
run;
%mx_execute(&_program,
  viyacontext=&defaultcontext,
  inputfiles=&f1:SASControlTable ,
  outlib=&outlib,
  mdebug=&sasjs_mdebug
)
%let nobs=0;
data _null_;
  set &outlib..sasparams;
  putlog (_all_)(=);
  call symputx('nobs',nobs);
run;
%mp_assert(
  iftrue=(&nobs=&expected),
  desc=&desc (&searchtype "%superq(searchval)" returned &nobs rows, expected &expected),
  outds=work.test_results
)
%mend search;
 
/* a character search matches part of a value, in the same case */
%search(CHAR,Selkie,1,Checking a same-case partial match returns the row)
%search(CHAR,quid,1,Checking a partial match on the middle of a value)
%search(CHAR,seal,1,Checking the search covers every character column)
 
/* the same term in another case does not match */
%search(CHAR,selkie,0,Checking a lowercase search does not match a capital S)
%search(CHAR,SELKIE,0,Checking an uppercase search does not match mixed case)
%search(CHAR,Quid,0,Checking a capital Q does not match a lowercase value)
 
/* a search in the case the value is stored in finds it, whether the match
  starts the value or sits inside it */
%search(CHAR,Smith,1,Checking Smith finds Smithson)
%search(CHAR,smith,1,Checking smith finds Goldsmith)
 
/* a numeric search is an exact match, not a partial one */
%search(NUM,12345,1,Checking an exact numeric search returns the row)
%search(NUM,1234,0,Checking a partial numeric search returns nothing)
 
/* a search that matches more rows than the web view cap returns the cap,
  and reports the same number of rows as it returns */
data &dclib..mpe_x_cap;
  length txt $24;
  do i=1 to 600;
    txt='needle'!!cats(i);
    output;
  end;
run;
 
%let f1=%mf_getuniquefileref();
%let outlib=%mf_getuniquelibref(prefix=web);
data _null_;
  file &f1 termstr=crlf;
  put 'LIBDS:$char41. FILTER_RK:best. SEARCHTYPE:$char4. SEARCHVAL:$char100.';
  put "&dclib..MPE_X_CAP,-1,CHAR,needle";
run;
%mx_execute(&_program,
  viyacontext=&defaultcontext,
  inputfiles=&f1:SASControlTable ,
  outlib=&outlib,
  mdebug=&sasjs_mdebug
)
%let capnobs=0;
%let capmax=0;
data _null_;
  set &outlib..sasparams;
  putlog (_all_)(=);
  call symputx('capnobs',nobs);
  call symputx('capmax',maxrows);
run;
%let caprows=0;
proc sql noprint;
  select count(*) into :caprows from &outlib..viewdata;
quit;
%mp_assert(
  iftrue=(&caprows=&capnobs and &capnobs gt 0 and &capnobs le &capmax),
  desc=Checking a search beyond the cap returns as many rows as it reports (&capnobs reported, &caprows returned, cap &capmax),
  outds=work.test_results
)