Page History
...
All the listed table names have an initial prefix followed by an underscore character. The meaning of the prefixes used and the names and content of specific tables with these prefixes are summarized in the table below.
| Prefix | Tables Using this Prefix | Table Names | Table Content | |||
| full | One table with the full prefix is created for each component. | These tables are named full_[component-type] (e.g. full_concept, full_description, full_relationship) | Each of these tables is populated with all the rows from the file (or files) representing this type component in the Full release subfolders. | |||
| One table with the full prefix is also created for each reference set type present in the release. | The tables are named full_refset_[refset-type] (e.g. full_refset_Simple, full_refset_Language, full_refset_Association). | Each of these tables is populated with all the rows from the file (or files) representing reference sets of this type in the Full release subfolders. | ||||
| snap | One table with the snap prefix is created for each component. | These tables are named snap_[component-type] (e.g. snap_concept, snap_description, snap_relationship) | Each of these tables is populated with all the rows from the file (or files) representing this type component in the Snapshot release subfolders. | |||
| One table with the snap prefix is also created for each reference set type present in the release. | The tables are named snap_refset_[refset-type] (e.g. snap_refset_Simple, snap_refset_Language, snap_refset_Association). | Each of these tables is populated with all the rows from the file (or files) representing reference sets of this type in the Snapshot release subfolders. | ||||
Additional tables with snap prefix are created to represent the transitive closure and
| The table are named snap_transclose and snap_proximal_primitives. | The snap_transclose table is populated with all the rows from the transitive closure files generated during the SnomedRfsMySql import process. The snap_proximal_primitives table is populated with |
| proximal primitive relationships derived by processing the snap_transclose table. | |||
| config | Lookup and configuration files used by views and stored procedures.
| config_language | A table linking ISO language codes (e.g. en-US, en-GB, es) to the identifier of the relevant language reference set. |
| config_settings | A table storing configuration settings that determine:
| ||
| config_shortcuts | A table linking a short text keys to commonly used concept ids. This is used to |
facilitate constraining searches to concepts within these hierarchies without requiring the query to specify the full SNOMED CT identifier for the concept. This is currently only used by the procedures snap_search_plus, snap_search1_plus and snap2_search_plus. In future it may also be used to support procedures with a common |
requirement for |
Views
SQL database views are in effect virtual tables. They can be queried in the same way as a table but they do not store data. The data that appears to be stored in a view is in fact defined by a stored query applied to the data stored in one or more tables.
It is important to be aware of performance issues related to the use of database views. For example, views that refer to other views can result in queries that take longer to process. Although, a detailed discussion on this topic is outside the scope of this guide, please keep this in mind when using the views and composite views that were created as part of this SNOMED CT example MySQL database.
The SNOMED CT import process creates two distinct types of views. Filtered views of a single table and composite views that bring together related data from different tables.
...
The import process creates six distinct sets of table views. Five of these are applied to every Full release table. The naming conventions and characteristics of each of these filtered views summarized in the table below.
| Prefix | View Names | View Content |
| snap1 | snap1_[component-type] (e.g. snap1_concept, snap1_description) snap1_refset_[refset-type] (e.g. snap1_refset_Simple) | These table views enable access to retrospective snapshots of the Full release data. The most recent version of every component in the table with an effectiveTime less than or equal to the snapshot date When the database is imported the snapshot dates are set as follows:
These snapshot times can be changed by calling the stored procedure setSnapshotTime(viewNumber, dateTime). For example, to set the snap1 date to 31 January 2017
and to set the snap2 date to 1 May 2016
|
| snap2 | snap2_[component-type] (e.g. snap2_concept, snap2_description) snap2_refset_[refset-type] (e.g. snap2_refset_Simple) | |
| delta | delta_[component-type] (e.g. delta_concept, delta_description) delta_refset_[refset-type] (e.g. delta_refset_Simple) | The delta table views enable access to delta views between any two dates. Only rows in the table with an effectiveTime greater than the start time and less that end time will be included in these views. When the database is imported the delta date ranges are set as follows:
Delta date ranges can be changed by calling the stored procedure setDeltaRange(viewNumber, startDateTime, endDateTime). For example, to set the delta view range to start on 31 July 2018 and end a year later
The delta1 and delta2 ranges can also be set in the same way
|
| delta1 | delta1_[component-type] (e.g. delta1_concept, delta1_description) delta1_refset_[refset-type] (e.g. delta1_refset_Simple) | |
| delta2 | delta2_[component-type] (e.g. delta2_concept, delta2_description) delta2_refset_[refset-type] (e.g. delta2_refset_Simple) |
An additional table view (with the prefix snapasview) provides a current snapshot view derived from the Full release. This is redundant in this database, because the import process imports the Snapshot release files as well as the Full release files. However, a few snapasview examples are included to provide examples of a views that could be used to avoid the need to import the Snapshot tables.
...
The table below summarizes the composite views supported by the database. Many of these composite views have variants that access specific snapshot views. These variants are indicated by the view prefixes snap, snap1 and snap2. Note that the snap variants use the snap tables, while snap1 and snap2 variants use the relevant snapshot table views. Composite views that require access to the transitive closure table can only access the current snapshot (i.e. the snap tables). A few specific composite views are also relevant to to the delta views and these have delta, delta1 and delta2 variants.
| Composite View | Purpose | Snap Table and Views | Delta Views |
| fsn | Display of fully specified name for a specified conceptid. | All snapshot views | - |
| pref | Display of preferred synonym for a specified conceptid. | All snapshot views | - |
| syn | Display of acceptable synonyms for a specified conceptid. | All snapshot views | - |
| synall | Display of all valid synonyms (preferred and acceptable) for a specified conceptid. | All snapshot views | - |
| syn_search_active | All valid synonyms of active concepts. This is used as the substrate for searches. | All snapshot views | - |
| term_search_active | Fully specified name and all valid synonyms of active concepts. This can be used as an extended substrate for searches including fully specified names. | All snapshot views | - |
| rel_fsn | All relationships with fully specified names returned for sourceid (src_id, src_term), typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup. | All snapshot views | - |
| rel_pref | All relationships with preferred synonyms returned for sourceid (src_id, src_term) typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup. | All snapshot views | - |
| rel_def_fsn | All defining attribute relationships with fully specified names returned for sourceid (src_id, src_term), typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup. | All snapshot views | - |
| rel_def_pref | All defining attribute relationships with preferred synonymsreturned for sourceid (src_id, src_term) typeid (type_id, type_term) and destinationid (dest_id, dest_term) and relationshipGroup. | All snapshot views | - |
| rel_child_fsn | All direct subtypes of a concept (conceptId) returned using the id and fully specified name (id, term) of the child concept. | All snapshot views | - |
| rel_child_pref | All direct subtypes of a concept (conceptId) returned using the id and preferred synonym (id, term) of the child concept. | All snapshot views | - |
| rel_parent_fsn | All direct supertypes of a concept (conceptId) returned using the id and fully specified name (id, term) of the parent concept. | All snapshot views | - |
| rel_parent_pref | All direct supertypes of a concept (conceptId) returned using the id and preferred synonym (id, term) of the parent concept. | All snapshot views | - |
| transclose_pref | Transitive closure table view returned with subtype and supertype returned with id and preferred term. | Only snap table | - |
| proxprim_pref | Proximal primitive relationships closure table view returned with subtype and supertype returned with id and preferred term. | Only snap table | - |
| inactive_concepts | Returns all inactive concepts in a specified snapshot or delta view. The returned data includes the fully specified name of the concept, the reason for inactivation (from the concept inactivation reference set) and the associations with active concepts shown in the historical association reference sets. | All snapshot views | All delta views |
| inactive_descriptions | Returns all inactive descriptions in a specified snapshot or delta view. The returned data includes the fully specified name and active status of the described concept, and the reason for inactivation (from the description inactivation reference set), | All snapshot views | All delta views |
Stored Procedures
| Procedure | Description | View Prefix Support |
| snap_SearchPlus(searchWords,filter) | Searches for acceptable synonyms of active concepts using a MySQL fulltext boolean search for the specified word or words. Word prefixed by "+" must be present, words prefixed by "-" but be absent and words with neither prefix will also be searched for but their absence from a term will not prevent a match. The filter can be used as follows to filter the search:
|
Examples: CALL snap_SearchPlus('fundus stomach', ''); CALL snap_SearchPlus('appendix','<proc'); CALL snap_SearchPlus('+fundus', 'ch'); | All snapshot views. | |
| snap_ShowLanguages(conceptId, languageCodeA, languageCodeB) | Shows the terms associated with a specified conceptId in two languages specified by the language codes. Example: CALL `snap_ShowLanguages`(80146002, 'en-GB','en-US'); | All snapshot views. |
| eclSimple(expression-constraint) | Allows a fairly simple ECL expression to be processed. Maximum of one focus concept constraint optionally refined by up to two attribute value constraints. Example: CALL `eclSimple`('<404684003:363698007=<<39057004,116676008=<<415582006'); | Only current snapshot |
| setLanguage(viewNumber, languageCode) | Sets the language reference set that determines the terms to be displayed by composite views with names ending _fsn, _pref, _syn, _synall, term. The language and dialect code is used to specify the language (e.g. en-US, en-GB). If other values are supported by the SNOMED Edition, these will need to be added to the config_languages to provide the refsetId lookup from the language code. Example: CALL `setLanguage`(0, "en-GB") | - |
| setDeltaRange(viewNumber, startDateTime, endDateTime) | Sets the date range for a specified delta view (viewNumber 0=delta, 1=delta1, 2=delta2) Examples: CALL setDeltaRange(0,"20180731","20190731"); CALL setDeltaRange(2,"20020131","20070731"); | - |
| setSnapshotTime(viewNumber, dateTime) | Sets the date on which a specified snapshot view is based (viewNumber 1=snap1, 2=snap2) Example: CALL setSnapshotTime( 1,"20170131"); CALL setSnapshotTime( 2,"20120131"); | - |
| resetConfig() | Resets the configuration file to the default initial starting snapshot time and delta range. | - |
| showConfig() | Displays the configuration table settings for language, snapshot dates and delta ranges. | - |
Overview
Content Tools
Apps