The SNOMED CT example database, has includes configuration settings that control configurable aspects of versioned table views (see 4.6.2. Versioned Database Table Views). The configuration settings also affect queries, composite views and procedures that refer to configurable versioned table views.

The configuration settings are represented by a database table called config_settings and each of the procedures described in this section either selects data from that table or updates data in the table. The details and default settings of the config_settings table are shown in .

Configuration Table Specification and initial settings

Column

Description

Datatype

Permitted Values

Default Values

id

Identifies the configuration setting and links to directly to view name prefixes

id=0 refers to snap and delta views (the time values are fixed for these views)
id=1 refers to snap1 and delta1 views
id=2 refers to snap2 and delta2 views

TINYINT

Integer in range 0-255

0, 1, 2

languageId

The refsetId of a language reference set.

BIGINT

Any language refsetId (e.g. 900000000000509007, 900000000000508004)

900000000000509007

languageName

The name of the language represented by the language reference set identified by languageId.

VARCHAR(255)

The name of any language or dialect represented by a language refset (e.g. US English, GB English).

US English

snapshotTime

The snapshotTime for views with the relevant view name prefix

Internally all these configuration dates are stored as the time 23:59:59 on the stated date. This ensure all changes on the end date are included in snapshot and delta views while all changes on the start date are excluded from a delta view.

.

DATETIME

Id=0: Release date (fixed)
Id>1: Any valid date.

Id=0: Release date
Id=1: Six months before release date
Id=2: One year before release date.

deltaStartTime

The effectiveTime of a component or refset member row must be greater than deltaStartTime to be included in the delta view with the relevant prefix

Internally all these configuration dates are stored as the time 23:59:59 on the stated date. This ensure all changes on the end date are included in snapshot and delta views while all changes on the start date are excluded from a delta view.

.

DATETIME

Id=0: Six months before release date (fixed)
Id>1: Any valid date before the release date.

Id=0: Six months before release date
Id=1: One year before release date
Id=2: Eighteen months before release date.

deltaEndTime

The effectiveTime of a component or refset member row must be less than or equal to the deltaStartTime to be included in the delta view with the relevant prefix

Internally all these configuration dates are stored as the time 23:59:59 on the stated date. This ensure all changes on the end date are included in snapshot and delta views while all changes on the start date are excluded from a delta view.

.

DATETIME

Id=0: Release date (fixed)
Id>1: Any valid date after the deltaStartTime.

Release DateTime (fixed)
Id=1: Six months before release date
Id=2: One year before release date.

Show Configuration Procedure

The showConfig procedure selects and displays the configuration files data.

Using the showConfig Procedure

SQL Call to Procedure

call showConfig();

Result

The results shown here are those the initial default settings for the 2019-07-31 release.

id

languageId

languageName

refsetName

The refsetName is selected by looking up the languageId in the snap_fsn view.

snapshotTime

deltaStartTime

deltaEndTime

0

900000000000509007

US English

United States of America English language reference set (foundation metadata concept)

2019-07-31

2019-01-31

2019-07-31

1

900000000000509007

US English

United States of America English language reference set (foundation metadata concept)

2019-01-31

2018-07-31

2019-01-31

2

900000000000509007

US English

United States of America English language reference set (foundation metadata concept)

2018-07-31

2018-01-31

2018-07-31

Set Language

The setLanguage procedure sets the languageId and languageName for a configuration row specified by its identifier value.

Languages can only be set if the following conditions apply.

The concept identifying the language reference set is available in the database.
The language abbreviation, language name and the identifier of the language reference set are in the config_language table.
The identified language reference set is available in the snap_refset_language reference set table or view.

SQL Call to Procedure

call setDeltaRange(p_id,p_deltaStartTime,p_deltaEndTime);

Parameter

Description

Data type

Valid values

Example

p_id

The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies).

TINYINT

0, 1 or 2

The language setting can be changed for the id=0 row.

1

p_lang_code

The date after which changes will be included in the delta view.

VARCHAR(5)

Any value that MySQL recognizes as a date or date-time.

'en-GB'

Example Procedure Call

call setLanguage(1,'en-GB');

Result

If p_lang_code does not refer to a language code in the config_language file or if no valid refset or refset members are found the procedure reports an error.

If the procedure succeeds, the language setting is changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.

Set Snapshot Time

The setDeltaRange procedure sets the snapshotTime for a configuration row specified by its id value.

SQL Call to Procedure

call setDeltaRange(p_id,p_snapshotTime,p_deltaEndTime);

Parameter

Description

Data type

Valid values

Example

p_id

The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies).

TINYINT

1 or 2

(Note: Values less than 1 or greater than 2 will be treated as referring to row 2)

1

p_snapshotTime

The date for which the identified snapshot view will be computed.

DATETIME

Any value that MySQL recognizes as a date or date-time.

'2017-07-31'

Example Procedure Call

call setSnapshotTime(1,'2017-07-31');

Result

The snapshotTime setting is changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.

Set Delta Range

The setDeltaRange procedure sets the deltaStartTime and deltaEndTime for a configuration row specified by its id value.

SQL Call to Procedure

call setDeltaRange(p_id,p_deltaStartTime,p_deltaEndTime);

Parameter

Description

Data type

Valid values

Example

p_id

The identifier of the configuration table view (also the number of the snapshot or delta view number to which the setting applies).

TINYINT

1 or 2

(Note: Values less than 1 or greater than 2 will be treated as referring to row 2)

1

p_deltaStartTime

The date after which changes will be included in the delta view.

DATETIME

Any value that MySQL recognizes as a date or date-time.

'2016-07-31'

p_deltaEndTime

The date on or before which changes with be included in the delta view.

DATETIME

Any value that MySQL recognizes as a date or date-time.

'2019-01-31'

Example Procedure Call

call setDeltaRange(1,'2016-07-31','2017-07-31');

Result

The deltaStartTime and deltaEndTime settings are changed but there is no output data. To check the result of the change, call showConfig() after resetConfig.

Reset Configuration

The resetConfig procedure resets all the configuration settings to the default values shown in .

The reset depends on the date times config_settings on the row with id=0 being unchanged. In particular, if assumes the snapshotTime of that row as the releaseDate. The other procedures described in this section do not change those values. However, if those values are changed by update queries the resetConfig procedure will not correctly reset the snapshot and delta times.

Reset Configuration

SQL Call to Procedure

call resetConfig();

.

Result

The reset is performed but there is no output data. To check the result of the reset, call showConfig() after resetConfig.