SQL Conventions

SQL Conventions used by RVF

This page explains the SQL conventions used by RVF.

SQL Conventions

The RVF includes the ability to execute SQL statements as part of an Test. These tests are called SQL tests and have the following characteristics.

• Each test can include multiple SQL statements.
• Each statement has been to separated by ;
• SQL statements included with a test are executed in the order in which they have been specified in the test.

For example, in the SQL below the create statement is executed before the insert statement.

create table if not exists concept_f;
// import some content into concept_f table
insert into concept_f select *... ;

There are conventions for referring to tables that belong to prospective and current releases.

Table Name	Description	Generated SQL value
<PROSPECTIVE>.simplemaprefset	Refers to a table in the prospective release corresponding to the simple map refset	rvf_int_20150131.simplemaprefset (where the prospective release is the January 2015 release)
<PREVIOUS>.stated_relationship	Refers to a table in the previous release corresponding to the stated relationships	rvf_int_20140731.simplemaprefset (where the previous release is the July 2014 release)
simplemaprefset_ <DELTA>	Refers to a table corresponding to the delta simple map refset	simplemaprefset_d
<PROSPECTIVE>.simplemaprefset_ <DELTA>	Refers to a table corresponding to the delta simple map refset in the prospective release	rvf_int_20150131.simplemaprefset_d
<PREVIOUS>.stated_relationship_ <FULL>	Refers to a table corresponding to the full stated relationships in the previous release	rvf_int_20140731.stated_relationship_f

Temporary Tables

SQL scripts can also include temporary tables as part of the SQL statements. Temporary tables are sometimes created when temporary values need to be held for processing. It is good practice to drop these temporary tables after you have done processing the temporary data. The RVF replaces every instance of a temporary table reference with a randomly generated table name as shown in the example below.

SQL statement	Description	Generated SQL value
create table if not exists <TEMP>.curr_table;	Creates to a temporary table by replacing curr_table with a table name based on a random UUID	create table if not exists rvf_int_20150131.36ccde55_2888_40f7_8361_bd27ab24b91a (where the prospective release is the January 2015 release)

Note that it is possible to reference the temporary tables more than once in a SQL script. For example, given the SQL code below, RVF processes the entire SQL script (composed of more than one statement) and replaces all instances of <TEMP>.curr_snapshot with a randomly generated table with the same name. It is also possible to use more than one temporary table in the SQL script. RVF should track the references and replace the appropriate instances during execution.

/* 	view of current snapshot made by finding FSN's with leading and training spaces */
	drop table if exists <TEMP>.curr_snapshot;
	create table if not exists  <TEMP>.curr_snapshot as
	select a.id 
	from <PROSPECTIVE>.textdefinition_<SNAPSHOT> a 
	where a.casesignificanceid != '900000000000017005';

	drop table if exists <TEMP>.curr_snapshot;