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
| Column | Description | Datatype | Permitted Values | Default Values |
| id | Identifies the configuration setting and links to directly to view name prefixes
| 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 prefix1 . | DATETIME |
|
|
| 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 prefix1 . | DATETIME |
|
|
| 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 prefix1 . | DATETIME |
|
|
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 | ||||||
| id | languageId | languageName | refsetName3 | 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 Example 4.9.2-2.
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.
Example 4.9.2-2: 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. |
| 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
Overview
Content Tools
Apps