Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Info
The SnomedRfsMySql package includes tools that are used to:
Apply the MySQL settings required by the SNOMED CT load process
The project can be downloaded as a zip archive file snomed-database-loader.
Unzip this file and rename the folder "mysql-loader-with-optimized-views" to "SnomedRfsMySql".
Use Finder (or File Explorer) to move the SnomedRfsMySql folder so it becomes a subfolder of you home folder.
On MacOS or other Unix based systems:/Users/ your-username/SnomedRfsMySql
On Windows systems:C:\SnomedRfsMySql
Zip archive
SNOMED CT Release Packages can be obtained from the Member Licensing & Distribution Service (MLDS).
Download the latest SNOMED CT International Release Package (a zip archive) into your SnomedCT_ReleaseFiles folder.
On MacOS or other Unix based systems: /Users/your-username/SnomedCT_ReleaseFiles
On Windows systems: C:\SnomedCT_ReleaseFiles
This page describes and explains the required additional MySQL configuration settings for the SNOMED CT example database. For instructions on how to apply these settings see A.3 Set Required MySQL Configuration (MacOS) or A.4 Set Required MySQL Configuration (Windows).
The following MySQL settings are required for loading and using the SNOMED CT MySQL database.
Setting
Explanation
Applies to
local-infile=1
The additional configuration settings required are specified in the following way in a file provided in the SnomedRfsMySql/cnf folder.
Content of additional configuration file: my_snomedserver.cnf
The following dialog should be displayed.
Click the spanner symbol to the right of the MySQL Connections prompt.
When the dialog below opens:
Required to ensure that data can be loaded into the database from local files (e.g. SNOMED CT Release Files)
mysqld, mysql, client
ft_stopword_file = '' ft_min_word_len = 2
The two settings improve the full text search capabilities of the database.
The first one removes the stop word list (which contains many words that are significant in clinical terms). An alternative approach would be a smaller stop word list but tests with SNOMED CT seem to suggest that this would not result in a significant improvement in performance.
The second setting allows words that are 2 letters long to be indexed (the default setting is 4 which means terms like leg, arm, eye, ear ... are not found in searches). Reducing this to 3 resolves this issue but still means that common clinical abbreviations like MI, FH, RA as not indexed.
mysqld
disable-log-bin skip-log-bin
These two settings stop the MySQL server from creating binary log files. Creation of these log files during the import process not only results in substantially slowing of the process but can also generates huge log files that more than double the space required for installation.
mysqld
[mysqld]
local-infile=1
ft_stopword_file = ''
ft_min_word_len = 2
disable-log-bin
skip-log-bin
default-authentication-plugin=mysql_native_password
[mysql]
local-infile=1
[client]
local-infile=1
protocol=tcp
host=localhost
port=3306Then click the Duplicate button
With the newly created connection selected:
Change the Connection Name to SNOMEDCT
Enter a Default Schema name as snomedct (i.e. the name of the newly created database)
Click the button:
If the connection is successful you will see the following dialog.
In future when you open MySQL Workbench you will see the option to open the SNOMEDCT connection.
To avoid future prompts for the MySQL Password you can save the password in the Keychain.
Click the button
Enter the MySQL password to be stored.
Open MySQL Workbench
Select the SNOMED CT Connection
To test the loaded SNOMED CT database open one of the example queries in the SnomedCtRfsMySql/mysql_examples folder.
Click open SQL button in the toolbar
Find the mysql_examples folder in the SnomedCtRfsMySql subfolder of your home directory.
Select and open one of the queries
The query should be display as shown below
Click the lightning icon to run the query
The results of running the query should be displayed as below
The SNOMED CT database can be used in different ways:
Running example SQL queries in the SnomedRfsMySql package.
Running queries provided as part of a SNOMED CT E-Learning assignment.
Using example SQL queries as templates for your own queries.
Running SQL queries you have written yourself from scratch.
You can run your SQL queries in MySQL Workbench as described in the previous section. Additionally these queries can be run using the MySQL command line interface or through a MySQL connector for one of the programming languages listed at: https://dev.mysql.com/downloads/.
The "root" user account is typically used for importing the SNOMED CT release files. However, you should create additional MySQL accounts for database users that do not have administrator rights. Ideally the name of these MySQL accounts should match the Mac username of the user as this simplifies call the mysql command line tool and the use of MySQL connectors.
To create user accounts in the MySQL Workbench select the Administration tab (see below) and select Users and Privileges (see below).
In this example an account with the username newuser account is being created as shown below:
In the schema privileges the newuser is given access to the snomedct database schema:
Within the snomedct database the rights of this newuser are limited to the following actions.
If you are using example queries as templates for your own queries alway copy the query first so that you do not overwrite the original example query.
If you accidentally overwrite an example query, you can download the SnomedRfsMySql.zip file again and extract the example queries folder.
Users of other Unix based system such as Linux or Ubuntu may find some of the instructions in this section applicable in their environment. However, the location of MySQL configuration files may differ and as a result some aspects of the configuration process may need to be altered.
Info
The instruction in this section assume you are installing MySQL for the first time or have fully uninstalled an earlier installation. If MySQL is already installed you may choose to skip this section.
Note
if MySQL was installed using a different installation package, some of the configuration steps described in later sections may need to be modified.
Download and install the DMG Archive version of the MySQL Community Server.
During the installation process you will be prompted to provide a password for the MySQL server root account.
Make a note of the root password - you will need to use it to load the SNOMED CT release package.
Select the Legacy Authentication Method unless you will be using the database for other purposes that require greater security.
Download and install the DMG Archive version of the .
Type the command lines shown below into the terminal window.
You have now completed the configuration process.
If MySQL is running the dots are green (as shown above).
Click Stop MySQL Server and wait for the dots to turn red (as shown below).
You will be prompted for your Mac password to confirm this action.
When the dots are red (as above) MySQL is not running
Click Start MySQL Server and wait for the dots to turn green.
You will be prompted for your Mac password to confirm this action.
You must change directories to the SnomedRfsMySql folder before starting the loader script.
As shown above you must be in the SnomedRfsMySql folder to run the loader script. Additionally as shown below you must include the name of the subfolder (bash) when running the loader script. The script may not run correctly if called from a different current folder or without the including the subfolder name.
You will be prompted to enter the following data when the script is run.
Before the creating the database the script generates an additional release file containing the transitive closure (this is used to optimize testing and listing of subtypes). This may take between 2 and 5 minutes to complete.
If you rerun the loader script again on the same release, the script will reuse the existing transitive closure. If you accept the options, there will be no delay while the rebuild occurs.
When the script starts to access MySQL you will then be prompted for your database password.
Note that the required password here is the password associated with your MySQL account.
As noted earlier the account used for the SNOMED CT load process must have appropriate access permissions and its username should match your Mac login name.
Depending on system performance the process may take between 20 and 45 minutes to complete. It may take longer with National Editions that contain additional content.
As the MySQL script runs it will report progress on the screen. Some steps take much longer than others. For example, loading data into the database tables and adding or building indexes take much longer than any of the other steps. So if the message about these steps are showing for a long time don't worry. Let the process continue.
When the script completes, scroll back up the command window to check for any ERROR reports for MySQL ... there should not be any!
Release package path
You must enter the full path of the release package folder or release package zip archive.
You will not be prompted for this if you select an update script option.
The path specified must point to a release package zip archive or an unzipped release package folder.
There is no need to include the .zip extension when referring to a release package archive or folder.
The script first looks for an unzipped release folder with the relevant name.
If the folder is not found the script looks for a zip archive with the same name plus the .zip extension.
Database name
Leave blank to accept the default
Default option
Uses the database name snomedct.
If the snomedct database already exists, it will be dropped (deleted) and recreated.
Database name
Specify a name (must begin with the letter s followed by lowercase letters and/or digits)
Uses the database name provided.
Using different names allows several SNOMED CT databases to co-exist (e.g. for different Editions)
Each SNOMED CT database will use about 5Gb of disk space ... so using different names may fill your available disk space!
If the named database already exists, it will be dropped (deleted) and recreated.
MySQL username
Leave blank to accept the default
The default is root.
Enter your MySQL username
The username chosen must be an account with administrator access rights enabling database creation.
Now it is time to open MySQL Workbench to view your SNOMED CT database.
Prompt
Response options
Notes
Loader script identifying tag
Leave blank to accept the default
Recommended option Uses the default create_latest script in the SnomedRfsMySql package.
Loader script identifying tag
Enter the name of one of the available scripts (these are listed above the prompt)
Tip
If this command does reports an error, please try the following modified command, which may prompt for your password to confirm the action:
sudo chmod u+x bash/*This script requires you to have administrator rights to access your computer and may prompt you to enter your login password. If you do not have administrator rights to access your computer, you will need to ask someone who does have those rights to run this part of the process.
Info
The MySQL Server must be started (or stopped and restarted) to apply the required configuration settings.
Disk Space
Before running the SNOMED CT load script, ensure you have at least 10Gb of free disk space. Once the load is completed 4Gb can be released by deleting the Release package folder and zip file.
The following figures apply to the SNOMED CT International Release package 2019-07-31.
Release Package zip file: 0.5 Gb
Release Package folder: 3.5 Gb
Installed database up to: 5.5 Gb
Uses the specified script to control the process of loading the SNOMED CT data into the database. Scripts keys have two parts a prefix and suffix separated by an underscore. The prefix indicates the kind of action:
create : Create a new database and load the data from the specified version.
update : Update the views and procedures in the database without recreating the database or reloading the the tables.
The suffix indicates the edition and version to which this applies:
latest : The most recent International Edition package
yyyymmdd: The International Edition for the stated year month and day
packageyyyymmdd: The identified Edition or Package for the stated year month and day.
If the zip file is found it is unzipped and the resulting folder is used.
The instructions in this section are specific to users of Windows systems.
Users of Mac OS systems should refer A.3 Instructions for Mac OS Users.
Users of other Unix based system such as Linux or Ubuntu may also find useful. However, the location of MySQL configuration files may differ and as a result some aspects of the configuration process may need to be altered.
Info
The instruction in this section assume you are installing MySQL for the first time or have fully uninstalled an earlier installation. If MySQL is already installed you may choose to skip this section.
Note
If MySQL was installed using a different installation package, some of the configuration steps described in later sections may need to be modified.
This is available as a free download from: https://dev.mysql.com/downloads/installer/
Note
Although the installers are 32-bit software, they will install the 64-bit version of MySQL if you are using a 64-bit system.
You can use either of the installers listed here:
The first installer option downloads a small package and then gets the other packages during the installation process.
The second installer downloads all the packages and can then continue the installation without an internet connection.
During the installation process you will be prompted to choose the setup type. The minimum recommended installation for the SNOMED CT database only required MySQL Server and MySQL Workbench so (unless you have additional requirements) select the Custom option as shown below.
Continue the process by clicking the next button.
You will then be prompted to specify the products and features to be installed.
Expand the nested list under MySQL Servers
Select the MySQL Server 8.0.17 (or a higher version if one is shown)
Click the arrow pointing to the right
When both Server and Workbench are in the right-hand list, click the Next button.
At this point you may see a message indicating a requirement for a Visual C++ Redistributable package as shown below.
If you see this message you should download and install the required package from before proceeding.
Further notes on this process are provided on .
Select the Standalone MySQL Server option.
Select the Development Computer option
Select the Legacy Authentication Method unless you will be using the database for other purposes that require greater security.
Accept or apply the following Server Connection settings
During the installation of MySQL 8.0.x on Windows you may see a message indicating that you need to install or update to the Microsoft Visual C++ 2015 Redistributable package.
If required this can be obtained as a free download from .
Click the Download button.
Choose the x64 version if you have a 64-bit computer or the x86 if you have an older 32-bit computer.
Continue an install the package.
The return to the MySQL installation process.
In the main Window menu locate the Command Prompt Desktop app.
Right-click on this item to show the drop down menu
Select the Run as administrator option.
This is necessary as some steps below require administrator status.
Adjust the path as necessary if you have not installed the SnomedRfsMySql folder in the root folder on drive C.
Type the following command to stop the MySQL server (if it is running).
Type the following command to run the configuration process. This command runs a script that updates the MySQL server configuration.
Type the following command to restart the MySQL server
Go to
Download either the appropriate version for your system (i.e. either 32-bit or 64-bit)
Open and run the downloaded file
This will have a name like "strawberry-perl-5.30.0.1-64bit.msi" (version numbers may differ).
Go through the installation process
When the installation is complete, you can test the installation by running the following command in a newly opened Command Prompt.
If Strawberry Perl is correctly installed the result should the following message.
In the main Window menu locate the Command Prompt Desktop app.
Right-click on this item to show the drop down menu
Select the Run as administrator option.
This is necessary as some steps below require adminstrator status.
You must change directories to the SnomedRfsMySql folder before starting the loader script.
As shown above you must be in the SnomedRfsMySql folder to run the loader script. Additionally as shown below you must include the name of the subfolder (win) when running the loader script. The script may not run correctly if called from a different current folder or without the including the subfolder name.
You will be prompted to enter the following data when the script is run.
Before the creating the database the script generates an additional release file containing the transitive closure (this is used to optimize testing and listing of subtypes). This may take between 2 and 5 minutes to complete.
If you rerun the loader script again on the same release, you will be offered the option to reuse the existing transitive closure. If you accept the options, there will be no delay while the rebuild occurs.
When the script starts to access MySQL you will then be prompted for your database password.
Note that the required password here is the password associated with your MySQL account.
As noted earlier the account used for the SNOMED CT load process must have appropriate access permissions and its username should match your Mac login name.
Depending on system performance the process may take between 20 and 90 minutes to complete. It may take longer with National Editions that contain additional content.
As the MySQL script runs it will report progress on the screen. Some steps take much longer than others. For example, loading data into the database tables and adding or building indexes take much longer than any of the other steps. So if the message about these steps are showing for a long time don't worry. Let the process continue.
When the script completes, scroll back up the command window to check for any ERROR reports for MySQL ... there should not be any!
This issue usually results from an incorrect service name reference. This arises when the MySQL Workbench contains a reference to an older service name (e.g. mysql), whereas MySQL Server 8.0.x uses the service name MySQL80.
Open the Services Desktop app from the Windows menu.
Then search the list of services to identify the name of the MySQL service
Open MySQL Workbench.
In the Server menu select Management Access Settings.
In the Manage Server Connections dialog update the Window Service Name to match the service name identified earlier.
Open MySQL Workbench and then select the Schema Tab.
You should see something like the image below.
If you gave your database a name other than snomedct you will see the name you chose listed as a schema.
If the name of you SNOMED CT database schema is displayed in bold this means it is the default database.
We recommend that you install in the default installation path (e.g. "C:\Strawberry)
Release package path
You must enter the full path of the release package folder or release package zip archive.
You will not be prompted for this if you select an update script option.
The path specified must point to a release package zip archive or an unzipped release package folder.
There is no need to include the .zip extension when referring to a release package archive or folder.
The script first looks for an unzipped release folder with the relevant name.
If the folder is not found the script looks for a zip archive with the same name plus the .zip extension.
Database name
Leave blank to accept the default
Default option
Uses the database name snomedct.
If the snomedct database already exists, it will be dropped (deleted) and recreated.
Specify a name (must begin with the letter s followed by lowercase letters and/or digits)
Uses the database name provided.
Using different names allows several SNOMED CT databases to co-exist (e.g. for different Editions)
Each SNOMED CT database will use about 5Gb of disk space ... so using different names may fill your available disk space!
If the named database already exists, it will be dropped (deleted) and recreated.
MySQL username
Leave blank to accept the default
The default is root.
Enter your MySQL username
The username chosen must be an account with administrator access rights enabling database creation.
Now it is time to open MySQL Workbench to view your SNOMED CT database.
Prompt
Response options
Notes
Loader script identifying tag
Leave blank to accept the default
Recommended option Uses the default create_latest script in the SnomedRfsMySql package.
Enter the name of one of the available scripts (these are listed above the prompt)
Note
The Configuration File setting must refer to: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
This is a file that will be modified in the to configure the server so that it will correctly load the SNOMED CT release files.
Warning
Be sure to remember your root password this as you will need it for access to the database.
Before importing the SNOMED CT data the import process creates a "transitive closure file". This allows this file to imported to create a transitive closure table that supports rapid subtype testing. The process that creates the transitive closure file from other release files uses the Perl language. Windows does not include a Perl language interpreter. Strawberry Perl is a widely used free Perl environment for Window and this should be installed on your computer before running the import script.
Warnings
Do not install Strawberry Perl within the SnomedRfsMySql folder as the installation will be removed by future updates to the SnomedRfsMySql package. Ideally you should install Strawberry Perl on drive C.
If Perl is installed on a network or a secondary drive (i.e. not drive C), you need to specify that location. To do this create (or edit) a plain text file called perlPath.cfg file in the SnomedRfsMySql\win folder. This file must contain a single line of text specifying the full path of the perl.exe file (for example the file might contain the text "D:\AddedSoftware\Strawberry\perl\bin\perl.exe")
If the import script cannot find perl.exe at the location specified in the perlPath.cfg file, that configuration file will be deleted and will need to be recreated with the correct full path to perl.exe.
Tip
If the message above does not appear, check the following:
The command line above assumes that SnomedRfsMySql is installed in the C:\ root folder. If this is not the case, repeat the test with the correct path to SnomedRdsMySql.
Ensure that you have closed all open command line windows. Then open a new command line window and run the test command again.
If neither of the above steps corrects corrects the issue, it suggest a problem with the installation. Consider uninstalling and reinstalling Strawberry Perl to correct the issue.
Disk Space
Before running the SNOMED CT load script, ensure you have at least 10Gb of free disk space. Once the load is completed 4Gb can be released by deleting the Release package folder and zip file.
The following figures apply to the SNOMED CT International Release package 2019-07-31.
Release Package zip file: 0.5 Gb
Release Package folder: 3.5 Gb
Installed database up to: 5.5 Gb
This section contains notes on some known issues with configuration of MySQL for use with Windows and the actions required to resolve them.
Uses the specified script to control the process of loading the SNOMED CT data into the database. Scripts keys have two parts a prefix and suffix separated by an underscore. The prefix indicates the kind of action:
create : Create a new database and load the data from the specified version.
update : Update the views and procedures in the database without recreating the database or reloading the the tables.
Expand the Tables Item under the SNOMED CT Database Schema name. This will reveal a list of table names.
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.
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 snap2
snap1_[component-type] (e.g. snap1_concept, snap1_description) snap1_refset_[refset-type] (e.g. snap1_refset_Simple) snap2_[component-type] (e.g. snap2_concept, snap2_description) snap2_refset_[refset-type] (e.g. snap2_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:
snap1 views are set as a snapshot date 6 months before the current release
snap2 views are set as a snapshot date 12 months before the current release
These snapshot times can be changed by calling the stored procedure
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 andsnap2. 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 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
-
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:
Left blank: no filtering
< conceptId : only terms of concepts that are subtypes of the concept identified by conceptid will be included in the search results.
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');
For example, to set the snap1 date to 31 January 2017
CALL setSnapshotTime( 1,"20170131");
and to set the snap2 date to 1 May 2016
CALL setSnapshotTime( 2, "20160501");
< shortcutTerm : only terms of concepts that are subtypes of the concept identified by looking up the shortcutTerm in the config_shortcuts table will be included in the search results
regular-expression : only terms that match the regular expression will be included in the search results
!regular-expression : only terms that do NOT match the regular expression will be included in the search results
Examples:
CALL snap_SearchPlus('fundus stomach', ''); CALL snap_SearchPlus('+fundus +stomach',''); CALL snap_SearchPlus('+lung +disease -chronic','');
CALL snap_SearchPlus('appendix','<proc'); CALL snap_SearchPlus('hemoglobin','<lab'); CALL snap_SearchPlus('infection','<19829001');
CALL snap_SearchPlus('+fundus', 'ch'); CALL snap_SearchPlus('fundus','!(eye|oculi|uter)'); CALL snap_SearchPlus('+lung +disease +chronic','oe?dema');
full
One table with the full prefix is also created for each reference set type present in the release.
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.
snap
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.
snap
Additional tables with snap prefix are created to represent the transitive closure and proximal primitive supertype relationships
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.
See sections below in Views and Procedures for further information.
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
Lookup and configuration files used by views and stored procedures.
See sections below in Views and Procedures for further information.
config_settings
A table storing configuration settings that determine:
The language reference set used to select synonyms and fully specified names
The effectiveTime of two configurable retrospective snapshot views (snap1 and snap2)
The effectiveTime range for two configurable delta views (delta1 and delta2)
config
Lookup and configuration files used by views and stored procedures.
See sections below in Views and Procedures for further information.
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.
delta delta1 delta2
delta_[component-type] (e.g. delta_concept, delta_description) delta_refset_[refset-type] (e.g. delta_refset_Simple) delta1_[component-type] (e.g. delta1_concept, delta1_description) delta1_refset_[refset-type] (e.g. delta1_refset_Simple) delta2_[component-type] (e.g. delta2_concept, delta2_description) delta2_refset_[refset-type] (e.g. delta2_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 views are set with a start date 6 months before the current release and an end date matching the current release date (this matches the current Delta release file content).
delta1 views start 12 months before the current release with an end date 6 months before the current release date.
delta2 views start 18 months before the current release with an end date 12 months before the current release date.
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
CALL setDeltaRange(0,"20180731","20190731");
The delta1 and delta2 ranges can also be set in the same way
CALL setDeltaRange(1,"20170731","20190731");
CALL setDeltaRange(2,"20020131","20070731");
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 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_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
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.
-
The suffix indicates the edition and version to which this applies:
latest : The most recent International Edition package
yyyymmdd: The International Edition for the stated year month and day
packageyyyymmdd: The identified Edition or Package for the stated year month and day.
If the zip file is found it is unzipped and the resulting folder is used.
