SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 7 Current »

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  Table 4.9.2-1.

Table 4.9.2-1: Configuration Table Specification and initial settings

ColumnDescriptionDatatypePermitted ValuesDefault 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
TINYINTInteger in range 0-2550, 1, 2
languageIdThe refsetId of a language reference set.BIGINTAny language refsetId (e.g. 900000000000509007, 900000000000508004)900000000000509007
languageNameThe 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 prefix1 .

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 1 .

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 1 .

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. 

Example 4.9.2-1: Using the showConfig Procedure

SQL Call to Procedure
call showConfig();

Result2

idlanguageIdlanguageName

refsetName3

snapshotTimedeltaStartTimedeltaEndTime
0900000000000509007US EnglishUnited States of America English language reference set (foundation metadata concept)

2019-07-31

2019-01-312019-07-31
1900000000000509007US EnglishUnited States of America English language reference set (foundation metadata concept)2019-01-312018-07-312019-01-31
2900000000000509007US EnglishUnited States of America English language reference set (foundation metadata concept)2018-07-312018-01-312018-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.

  1. The concept identifying the language reference set is available in the database.
  2. The language abbreviation, language name and the identifier of the language reference set are in the config_language table.
  3. 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);

ParameterDescriptionData typeValid valuesExample
p_idThe 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_codeThe 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);

ParameterDescriptionData typeValid valuesExample
p_idThe 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.DATETIMEAny 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);

ParameterDescriptionData typeValid valuesExample
p_idThe 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_deltaStartTimeThe date after which changes will be included in the delta view.DATETIMEAny value that MySQL recognizes as a date or date-time.'2016-07-31'
p_deltaEndTimeThe date on or before which changes with be included in the delta view.DATETIMEAny 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  Table 4.9.2-1.

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.

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.


Footnotes
Ref Notes
1 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. [ a b c ]
2 The results shown here are those the initial default settings for the 2019-07-31 release.
3 The refsetName is selected by looking up the languageId in the snap_fsn view.


Feedback
  • No labels