This page contains details on how to set up databases for use with SQL Server.
See platform requirements for information on supported versions of SQL Server.
A script similar to the following can be used to create a new database named cdr with username cdr and password SmileCDR123 in MSSQL:
--create db off of master
USE master;
--check if exists
IF DB_ID (N'cdr') IS NOT NULL
DROP DATABASE cdr;
--create database
CREATE DATABASE cdr
COLLATE SQL_Latin1_General_CP1_CI_AS
WITH TRUSTWORTHY ON, DB_CHAINING ON;
--Verifying collation and option settings.
SELECT name, collation_name, is_trustworthy_on, is_db_chaining_on
FROM sys.databases
WHERE name = N'cdr';
CREATE LOGIN cdr WITH PASSWORD = 'SmileCDR123'
Use cdr
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'cdr')
BEGIN
CREATE USER [cdr] FOR LOGIN [cdr]
EXEC sp_addrolemember N'db_owner', N'cdr'
END;
When configuring Smile CDR to connect to a MSSQL database, set the appropriate persistence and cluster manager properties to values similar to the following (making adjustments where necessary, e.g. for hostname, port number, username and password):
module.clustermgr.config.db.driver =MSSQL_2012
module.clustermgr.config.db.url =jdbc:sqlserver://localhost:1433;database=cdr
module.clustermgr.config.db.username =cdr
module.clustermgr.config.db.password =SmileCDR123
To initialize a new SQL Server database for the Cluster Manager, initialize a database with the following SQL:
create sequence SEQ_AG_APPL_PID start with 1 increment by 50;
create sequence SEQ_AG_CLASS_PID start with 1 increment by 50;
create sequence SEQ_AG_NOTE_PID start with 1 increment by 50;
create sequence SEQ_AUDITEVT_PID start with 1 increment by 50;
create sequence SEQ_AUDITEVT_TARGETMODULE_PID start with 1 increment by 50;
create sequence SEQ_AUDITEVT_TARGETRES_PID start with 1 increment by 50;
create sequence SEQ_AUDITEVT_TARGETUSER_PID start with 1 increment by 50;
create sequence SEQ_AUDITEVT_TYPE_PID start with 1 increment by 50;
create sequence SEQ_CDATEMPLATE_PID start with 1 increment by 50;
create sequence SEQ_CDATEMPLATEPARAM_PID start with 1 increment by 50;
create sequence SEQ_FHIRSEARCHPARAM_PID start with 1 increment by 50;
create sequence SEQ_HTTPSESSION_PID start with 1 increment by 50;
create sequence SEQ_METRICHC_PID start with 1 increment by 50;
create sequence SEQ_MODPROC_PID start with 1 increment by 50;
create sequence SEQ_MODSTAT_PID start with 1 increment by 50;
create sequence SEQ_MODULE_CFGPROP_PID start with 1 increment by 50;
create sequence SEQ_MODULE_DEP_PID start with 1 increment by 50;
create sequence SEQ_MODULE_PID start with 1 increment by 50;
create sequence SEQ_MODULE_PROT_CFG_PID start with 1 increment by 50;
create sequence SEQ_MODULE_PROT_DEP_PID start with 1 increment by 50;
create sequence SEQ_MODULE_PROT_SUPPLIES_PID start with 1 increment by 50;
create sequence SEQ_MODULE_PROTOTYPE_PID start with 1 increment by 50;
create sequence SEQ_NODE_PID start with 1 increment by 50;
create sequence SEQ_NODEPROC_PID start with 1 increment by 50;
create sequence SEQ_OA2ATOKADNLINFO_PID start with 1 increment by 50;
create sequence SEQ_OA2ATOKEN_PID start with 1 increment by 50;
create sequence SEQ_OA2ATOKGRNTDA_PID start with 1 increment by 50;
create sequence SEQ_OA2ATOKSCP_PID start with 1 increment by 50;
create sequence SEQ_OA2AUTHCODE_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIDETAASCOP_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIDETAGSCOP_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIDETAUTHGRNTTYP_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIDETCLISEC_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIDETREGREDIRURI_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIENTDETAILS_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIENTDETAILSSCOPE_PID start with 1 increment by 50;
create sequence SEQ_OA2CLIENTPERM_PID start with 1 increment by 50;
create sequence SEQ_OA2REFTOKEN_PID start with 1 increment by 50;
create sequence SEQ_OA2RTOKADNLINFO_PID start with 1 increment by 50;
create sequence SEQ_OA2RTOKGRNTDA_PID start with 1 increment by 50;
create sequence SEQ_OA2RTOKREQPRM_PID start with 1 increment by 50;
create sequence SEQ_OA2RTOKSCP_PID start with 1 increment by 50;
create sequence SEQ_OA2SERVER_PID start with 1 increment by 50;
create sequence SEQ_USER_PERM_PID start with 1 increment by 50;
create sequence SEQ_USER_PID start with 1 increment by 50;
create sequence SEQ_USERDEFLNCHCTX_PID start with 1 increment by 50;
create sequence SEQ_USERTFAKEY_PID start with 1 increment by 50;
create sequence SEQ_USROA2CLNTAPRVDSCP_PID start with 1 increment by 50;
create sequence SEQ_XACT_LOG_PID start with 1 increment by 50;
create sequence SEQ_XACT_LOG_STEP_PID start with 1 increment by 50;
create table AG_APPL (
PID bigint not null,
ATTESTATION_ACCEPTED bit not null,
COUNTRY_CODE varchar(200) not null,
LAUNCH_URL varchar(200) not null,
LONG_DESC varchar(1000) not null,
NAME varchar(200) not null,
OAUTH_REDIRECT_URL varchar(200) not null,
PRIVACY_POLICY_URL varchar(200) not null,
SHORT_DESC varchar(200) not null,
AG_STATUS varchar(20) not null,
SUPPORT_EMAIL varchar(200) not null,
SUPPORT_PHONE_NUMBER varchar(200) not null,
TERMS_OF_SERVICE_URL varchar(200) not null,
CREATED_BY varchar(200) not null,
CREATED_DATE datetime2 not null,
LAST_MODIFIED_BY varchar(200) not null,
LAST_MODIFIED_DATE datetime2 not null,
COMPANY_NAME varchar(200) not null,
COMPANY_SITE_URL varchar(200) not null,
COUNTRY varchar(200) not null,
FIRST_NAME varchar(200) not null,
LAST_NAME varchar(200) not null,
MODULE_PID bigint not null,
OAUTH2_CLIENT_PID bigint,
primary key (PID)
);
create table AG_CLASS (
PID bigint not null,
CLASSIFICATION varchar(200) not null,
AG_APPL_PID bigint,
primary key (PID)
);
create table AG_NOTE (
PID bigint not null,
CREATED_BY varchar(200) not null,
CREATED_DATE datetime2 not null,
LAST_MODIFIED_BY varchar(200) not null,
LAST_MODIFIED_DATE datetime2 not null,
MESSAGE varchar(200) not null,
REASON varchar(1000) not null,
AG_APPL_PID bigint,
primary key (PID)
);
create table CDR_AUDIT_EVT (
PID bigint not null,
AUSER_TYPE int,
REMOTE_ADDRESS varchar(100),
HAVE_TARGET_MODULES bit not null,
HAVE_TARGET_RESOURCES bit not null,
HAVE_TARGET_USERS bit not null,
EVT_TIMESTAMP datetime2 not null,
TYPE_DISPLAY varchar(200) not null,
CLIENT_PID bigint,
ENDPOINT_MODULE_PID bigint not null,
TYPE_PID bigint not null,
USER_PID bigint,
primary key (PID)
);
create table CDR_AUDIT_EVT_TARGET_MODULE (
PID bigint not null,
EVENT_PID bigint not null,
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_AUDIT_EVT_TARGET_RES (
PID bigint not null,
REQ_VAL_RESULT int,
RES_ID varchar(120) not null,
RES_VERSION bigint,
EVENT_PID bigint not null,
PERS_MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_AUDIT_EVT_TARGET_USER (
PID bigint not null,
EVENT_PID bigint not null,
USER_PID bigint not null,
primary key (PID)
);
create table CDR_AUDIT_EVT_TYPE (
PID bigint not null,
CODEVAL varchar(200),
SYSTEM_URL varchar(200),
primary key (PID)
);
create table CDR_CDA_TEMPLATE (
PID bigint not null,
TEMPLATE_DESCRIPTION varchar(250),
TEMPLATE_ID varchar(250) not null,
TEMPLATE_CONTENT varchar(MAX) not null,
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_CDA_TEMPLATE_PARAM (
PID bigint not null,
TEMPLATE_PARAMETER_NAME varchar(250) not null,
TEMPLATE_PID bigint,
primary key (PID)
);
create table CDR_DB_VERSION (
DB_VERSION varchar(100) not null,
DB_INIT_TIME datetime2 not null,
primary key (DB_VERSION)
);
create table CDR_FHIR_SEARCHPARAM (
PID bigint not null,
SP_BASE varchar(500) not null,
SP_CODE varchar(500) not null,
SP_DESC varchar(500),
SP_FHIR_STATUS varchar(255),
LAST_UPDATED datetime2 not null,
LAST_USED datetime2,
STAT_OVERALL_COUNT bigint,
SP_PATH varchar(500),
STAT_RESOURCE_SPREAD double precision not null,
SP_RES_ID varchar(150),
SP_SYNC_STATUS varchar(255) not null,
SP_TITLE varchar(500),
SP_TYPE varchar(20),
BUF_UNIQUE_RESOURCES varbinary(MAX),
BUF_UNIQUE_VALUES varbinary(MAX),
STAT_VALUE_SPREAD double precision not null,
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_GLOBAL_GAUGE (
ID varchar(200) not null,
VAL_DOUBLE double precision,
VAL_LONG bigint,
primary key (ID)
);
create table CDR_HTTP_SESSION (
PID bigint not null,
SES_ATTRS varbinary(MAX) not null,
SES_EXPIRY datetime2 not null,
LAST_ACCESSED_TIME datetime2,
MAX_INACTIVE bigint not null,
SES_ID varchar(200) not null,
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_METRIC_GAUGE (
GAUGE_TYPE int not null,
INTERVL int not null,
START_TIME datetime2 not null,
IS_COLLAPSED bit not null,
GAUGE_VAL bigint not null,
MODPROC_PID bigint not null,
primary key (GAUGE_TYPE, INTERVL, MODPROC_PID, START_TIME)
);
create table CDR_METRIC_HC (
PID bigint not null,
ERROR_STRING varchar(200),
HEALTHCHECK_TYPE int not null,
IS_HEALTHY bit not null,
HEALTHY_STRING varchar(200),
MODPROC_PID bigint not null,
primary key (PID)
);
create table CDR_METRIC_TIMER (
INTERVL int not null,
START_TIME datetime2 not null,
TIMER_TYPE int not null,
IS_COLLAPSED bit not null,
COUNT_VAL bigint,
RATE_15_MIN float not null,
FIRST_SINCE_RS bit not null,
RATE_5_MIN float not null,
LATENCY_MAX int not null,
LATENCY_MEAN int not null,
LATENCY_MIN int not null,
MODPROC_PID bigint not null,
RATE_1_MIN float not null,
primary key (INTERVL, MODPROC_PID, START_TIME, TIMER_TYPE)
);
create table CDR_MODULE (
PID bigint not null,
DELETED bit not null,
MODULE_DISABLED bit not null,
MODULE_ID varchar(30) not null,
PORTVAL int,
NODE_PID bigint not null,
PROTOTYPE_PID bigint not null,
primary key (PID)
);
create table CDR_MODULE_CFG_PROP (
PID bigint not null,
VALUE_STRING varchar(200),
VALUE_EXTENDED varchar(MAX),
MODULE_PID bigint not null,
PROTOTYPE_PID bigint not null,
primary key (PID)
);
create table CDR_MODULE_DEP (
PID bigint not null,
MODULE_PID bigint not null,
PROTOTYPE_PID bigint not null,
TARGET_PID bigint,
primary key (PID)
);
create table CDR_MODULE_PROCESS (
PID bigint not null,
FIRST_MP bit,
MODULE_PID bigint not null,
NODEPROC_PID bigint not null,
RESTART_REQUIRED bit not null,
MODULE_STATUS int not null,
MODULE_STATUS_DESC varchar(200),
STATUS_TIMESTAMP datetime2,
primary key (PID)
);
create table CDR_MODULE_PROT_CFG (
PID bigint not null,
IS_ADVANCED bit not null,
CATEGORY_KEY varchar(100) not null,
DEFAULT_VALUE varchar(200),
DESC_KEY varchar(200),
ENUM_TYPE varchar(200),
CFG_INDEX int not null,
CFG_KEY varchar(200) not null,
NAME_KEY varchar(200) not null,
IS_OPTIONAL bit not null,
CFG_TYPE varchar(100) not null,
PROTOTYPE_PID bigint not null,
primary key (PID)
);
create table CDR_MODULE_PROT_DEP (
PID bigint not null,
DESCRIPTION_KEY varchar(200) not null,
ENTRY_KEY varchar(200) not null,
PROTOTYPE_PID bigint,
primary key (PID)
);
create table CDR_MODULE_PROT_SUPPLIES (
PID bigint not null,
ENTRY_KEY int not null,
PROTOTYPE_PID bigint,
primary key (PID)
);
create table CDR_MODULE_PROTOTYPE (
PID bigint not null,
MODULE_TYPE varchar(200) not null,
STOPPABLE bit not null,
primary key (PID)
);
create table CDR_MODULE_STATE (
PID bigint not null,
STATE_TYPE int not null,
STATE_VAL varchar(200),
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_NODE (
PID bigint not null,
IS_DELETED bit,
NODE_ID varchar(30) not null,
primary key (PID)
);
create table CDR_NODE_PROCESS (
PID bigint not null,
CREATED_TIME datetime2 not null,
HEARTBEAT_TIME datetime2,
KILLED_BY varchar(20),
NODE_PID bigint not null,
PROCESS_ID varchar(20) not null,
PROCESS_NAME varchar(100),
STARTED_TIME datetime2,
NODE_STATUS int not null,
STOPPED_TIME datetime2,
OPT_LOCK int not null,
primary key (PID)
);
create table CDR_OA2_CLI_DET_AUTH_GRNT_TYP (
PID bigint not null,
GRANT_TYPE varchar(100) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OA2_CLI_DET_AUTOAP_SCOPE (
PID bigint not null,
SCOPE varchar(100) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OA2_CLI_DET_AUTOGRNT_SCOPE (
PID bigint not null,
SCOPE varchar(100) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OA2_CLI_DET_CLISEC (
PID bigint not null,
SEC_ACTIVATION datetime2,
SEC_CREATION datetime2,
SEC_DESC varchar(250),
SEC_EXPIRATION datetime2,
CLI_SECRET varchar(250) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OA2_CLI_DET_REG_REDIR_URI (
PID bigint not null,
REDIR_URI varchar(200) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OA2_SERVER (
PID bigint not null,
FEDERATION_AUTH_SCRIPT_TEXT varbinary(MAX),
FEDERATION_AUTH_URL varchar(200),
FEDERATION_JWKS_URL varchar(200),
FEDERATION_REG_ID varchar(200) not null,
FEDERATION_REQ_SCOPES varchar(200),
FEDERATION_TOKEN_URL varchar(200),
FEDERATION_USER_INFO_URL varchar(200),
ISS varchar(200) not null,
SERVER_NAME varchar(200),
INTRSPCT_CLIENT_ID varchar(200),
INTRSPCT_CLIENT_SCRT varchar(200),
JWKS_FILE varchar(200),
JWKS_TEXT varchar(MAX),
MODULE_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_ATOKEN (
PID bigint not null,
EXPIRATION datetime2 not null,
GRANT_TYPE varchar(20),
ID_TOKEN_BYTES varbinary(MAX),
ID_TOKEN_VAL varchar(1000),
ISSUED datetime2 not null,
REDIRECT_URI varchar(200),
TOKEN_HASH varchar(150) not null,
TOKEN_BYTES varbinary(MAX),
TOKEN_VAL varchar(1000),
CLIENT_PID bigint not null,
MODULE_PID bigint not null,
REFRESH_TOKEN_PID bigint,
USER_PID bigint,
primary key (PID)
);
create table CDR_OAUTH2_ATOKEN_ADNLINFO (
PID bigint not null,
INFO_KEY varchar(200) not null,
INFO_VALUE varchar(200) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_ATOKEN_GRNTDA (
PID bigint not null,
PERM_ARG varchar(200),
PERM_NAME varchar(100) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_ATOKEN_SCOPE (
PID bigint not null,
SCOPE varchar(100) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_AUTH_CODE (
PID bigint not null,
OAUTH2_AUTHENTICATION varbinary(MAX),
CLIENT_ID varchar(200) not null,
CODE_VAL varchar(100) not null,
EXPIRATION datetime2 not null,
PKCE_CHALLENGE varchar(200),
PKCE_CHALLENGE_TYPE varchar(10),
STATE_VAL varchar(100),
primary key (PID)
);
create table CDR_OAUTH2_CLIENT_DET_SCOPE (
PID bigint not null,
SCOPE varchar(100) not null,
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_CLIENT_DETAILS (
PID bigint not null,
ACCESS_TOKEN_VALIDITY_SECONDS int,
ALWAYS_REQUIRE_APPROVAL bit,
ATTESTATION_ACCEPTED bit,
CAN_INTROSPECT_ANY bit,
CAN_INTROSPECT_OWN bit,
CAN_REUSE_TOKENS bit,
CLIENT_ID varchar(200) not null,
CLIENT_NAME varchar(200),
CLIENT_SECRET varchar(200),
ENABLED bit,
FIXED_SCOPE bit not null,
REFRESH_TOKEN_VALIDITY_SECONDS int,
REMEMBER_APPROVED_SCOPES bit,
SECRET_CLIENT_CAN_CHANGE bit,
SECRET_REQUIRED bit not null,
MODULE_PID bigint,
primary key (PID)
);
create table CDR_OAUTH2_CLIENT_PERM (
PID bigint not null,
PERMISSION_TYPE varchar(100) not null,
PERMISSION_ARG varchar(100),
CLIENT_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_JWT_UNIQ_ID (
JWT_ID varchar(500) not null,
CONSUMED_TIMESTAMP datetime2 not null,
primary key (JWT_ID)
);
create table CDR_OAUTH2_REFRESH_TOKEN (
PID bigint not null,
EXPIRATION datetime2,
ISSUED datetime2 not null,
TOKEN_VAL varchar(150) not null,
CLIENT_PID bigint,
USER_PID bigint,
primary key (PID)
);
create table CDR_OAUTH2_RTOKEN_ADNLINFO (
PID bigint not null,
INFO_KEY varchar(200) not null,
INFO_VALUE varchar(200) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_RTOKEN_GRNTDA (
PID bigint not null,
PERM_ARG varchar(200),
PERM_NAME varchar(100) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_RTOKEN_REQPRM (
PID bigint not null,
PARM_KEY varchar(200) not null,
PARM_VALUE varchar(1600) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_OAUTH2_RTOKEN_SCOPE (
PID bigint not null,
SCOPE varchar(100) not null,
TOKEN_PID bigint not null,
primary key (PID)
);
create table CDR_USER_DETAILS (
PID bigint not null,
IS_ANON_ACCT bit,
CREDS_EXPIRE_ON datetime2,
USER_DISABLED bit not null,
EMAIL varchar(200),
EXPIRES_ON datetime2,
IS_EXTERNAL bit,
FAILED_LOGIN_ATTEMPTS int,
FAMILY_NAME varchar(200),
GIVEN_NAME varchar(200),
LAST_ACTIVE datetime2,
USER_LOCKED bit not null,
MODULE_PID bigint not null,
NOTES varchar(MAX),
USER_PASSWORD varchar(200),
IS_SERVICE_ACCT bit,
IS_SYSTEM_USER bit not null,
UPDATED_TS datetime2 not null,
USERNAME varchar(200) not null,
USERNAME_HAS_NAMESPACE bit,
primary key (PID)
);
create table CDR_USER_DETAILS_DEF_LNCH_CTX (
PID bigint not null,
CONTEXT_TYPE varchar(50) not null,
RESOURCE_ID varchar(64) not null,
USER_PID bigint not null,
primary key (PID)
);
create table CDR_USER_DETAILS_OA2CLNAPRVSCP (
PID bigint not null,
IS_APPROVED bit not null,
SCOPE varchar(100) not null,
CLIENT_PID bigint not null,
USER_PID bigint not null,
primary key (PID)
);
create table CDR_USER_PERM (
PID bigint not null,
PERMISSION_TYPE varchar(100) not null,
PERMISSION_ARG varchar(100),
USER_PID bigint,
primary key (PID)
);
create table CDR_USER_TFA_KEY (
PID bigint not null,
CONFIRMED_AT datetime2,
KEY_EXPIRES datetime2,
FAILED_VERIFS int,
A_KEY varchar(250) not null,
A_STYLE varchar(20) not null,
OPTLOCK int not null,
USER_PID bigint not null,
primary key (PID)
);
create table CDR_XACT_LOG (
PID bigint not null,
INITIAL_TIMESTAMP datetime2 not null,
EVT_OUTCOME int not null,
SRC_XACT_ID varchar(128),
EVT_SUBTYPE int not null,
SRC_GUID varchar(36),
EVT_TYPE int not null,
CLIENT_PID bigint,
CDR_ENDPOINT_MODULE_PID bigint,
USER_PID bigint,
primary key (PID)
);
create table CDR_XACT_LOG_STEP (
STEP_PID bigint not null,
BODY_BYTES varbinary(MAX),
BODY_TYPE int,
LOCAL_HOST varchar(100),
LOCAL_PORT int,
EVT_OUTCOME int,
PROCESSING_TIME bigint,
REMOTE_HOST varchar(100),
REMOTE_PORT int,
REQUEST_URL_STR varchar(250),
REQ_VAL_RESULT int,
REQUEST_VERB varchar(7),
RESPONSE_STATUS smallint,
SUBSCRIPTION_ID varchar(77),
LOG_TIMESTAMP datetime2 not null,
STEP_TYPE int not null,
LOG_PID bigint not null,
primary key (STEP_PID)
);
alter table AG_CLASS
add constraint IDX_AG_CLASSIFICATION_PID unique (CLASSIFICATION, PID);
create index IDX_AUDITEVT_TARGMOD_EVT on CDR_AUDIT_EVT_TARGET_MODULE (EVENT_PID);
create index IDX_AUDITEVT_TARGRES_EVT on CDR_AUDIT_EVT_TARGET_RES (EVENT_PID);
create index IDX_AUDITEVT_TARGUSR_EVT on CDR_AUDIT_EVT_TARGET_USER (EVENT_PID);
alter table CDR_AUDIT_EVT_TYPE
add constraint IDX_AUDITEVTTYPE_SYSTEM_CODE unique (SYSTEM_URL, CODEVAL);
create index IDX_CDR_TEMPLATE_ID on CDR_CDA_TEMPLATE (TEMPLATE_ID);
alter table CDR_CDA_TEMPLATE
add constraint IDX_CDATEMPL_ID unique (TEMPLATE_ID);
create index IDX_FHIRSP_SP_RES_ID on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_RES_ID);
create index IDX_FHIRSP_BASE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_BASE);
create index IDX_FHIRSP_CODE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_CODE);
create index IDX_FHIRSP_OVERALLCOUNT on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_OVERALL_COUNT);
create index IDX_FHIRSP_RESSPREAD on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_RESOURCE_SPREAD);
create index IDX_FHIRSP_TYPE on CDR_FHIR_SEARCHPARAM (MODULE_PID, SP_TYPE);
create index IDX_FHIRSP_VALSPREAD on CDR_FHIR_SEARCHPARAM (MODULE_PID, STAT_VALUE_SPREAD);
create index IDX_FHIRSP_LASTUPDATED on CDR_FHIR_SEARCHPARAM (MODULE_PID, LAST_UPDATED);
create index IDX_FHIRSP_LASTUSED on CDR_FHIR_SEARCHPARAM (MODULE_PID, LAST_USED);
create index IDX_HTTPSESSION_EXPIRY on CDR_HTTP_SESSION (SES_EXPIRY);
create index IDX_HTTPSESSION_MODULE on CDR_HTTP_SESSION (MODULE_PID);
alter table CDR_HTTP_SESSION
add constraint IDX_HTTPSESSION_ID unique (SES_ID);
create index IDX_GAUGE_MODCOLLINTSTART on CDR_METRIC_GAUGE (MODPROC_PID, GAUGE_TYPE, INTERVL, START_TIME);
create index IDX_GAUGE_MODCOLLSTART on CDR_METRIC_GAUGE (MODPROC_PID, IS_COLLAPSED, START_TIME);
alter table CDR_METRIC_HC
add constraint IDX_METRICHC_NODEP_HID unique (MODPROC_PID, HEALTHCHECK_TYPE);
create index IDX_TIMER_MODCOLLINTSTART on CDR_METRIC_TIMER (MODPROC_PID, TIMER_TYPE, INTERVL, START_TIME);
create index IDX_TIMER_MODCOLLSTART on CDR_METRIC_TIMER (MODPROC_PID, IS_COLLAPSED, START_TIME);
alter table CDR_MODULE
add constraint IDX_MODULE_ID unique (NODE_PID, MODULE_ID);
alter table CDR_MODULE_CFG_PROP
add constraint IDX_CDR_MODULE_CDR_PROP_MODKEY unique (MODULE_PID, PROTOTYPE_PID);
alter table CDR_MODULE_PROCESS
add constraint IDX_MODPROC_NODEPROC_MOD unique (NODEPROC_PID, MODULE_PID);
alter table CDR_MODULE_PROT_CFG
add constraint IDX_CDR_MODULE_PROT_CDG_MODKEY unique (PROTOTYPE_PID, CFG_KEY);
alter table CDR_MODULE_PROT_DEP
add constraint IDX_MODULEPROTDEP_PROT_KEY unique (PROTOTYPE_PID, ENTRY_KEY);
alter table CDR_MODULE_PROTOTYPE
add constraint IDX_MODULEPROTOTYPE_TYPE unique (MODULE_TYPE);
create index IDX_MODSTAT_MOD on CDR_MODULE_STATE (MODULE_PID);
alter table CDR_MODULE_STATE
add constraint IDX_MODSTAT_MODTYPE unique (MODULE_PID, STATE_TYPE);
alter table CDR_NODE
add constraint IDX_NODE_ID unique (NODE_ID);
alter table CDR_NODE_PROCESS
add constraint IDX_NODEPROC_PROCID unique (PROCESS_ID);
alter table CDR_OA2_CLI_DET_AUTH_GRNT_TYP
add constraint IDX_OA2TOKGRTTYP_TOK_GRNT_TYP unique (CLIENT_PID, GRANT_TYPE);
alter table CDR_OA2_CLI_DET_AUTOAP_SCOPE
add constraint IDX_OA2CLIDETAASCOPE_TOK_SCOP unique (CLIENT_PID, SCOPE);
alter table CDR_OA2_CLI_DET_AUTOGRNT_SCOPE
add constraint IDX_OA2CLIDETAGSCOPE_TOK_SCOP unique (CLIENT_PID, SCOPE);
alter table CDR_OA2_CLI_DET_CLISEC
add constraint IDX_OA2CLIDETCLISEC_SECRET unique (CLIENT_PID, CLI_SECRET);
alter table CDR_OA2_CLI_DET_REG_REDIR_URI
add constraint IDX_OA2CLIDETRRURI_TOK_SCO unique (CLIENT_PID, REDIR_URI);
alter table CDR_OA2_SERVER
add constraint IDX_OA2SERVER_FED_REG_ID unique (MODULE_PID, FEDERATION_REG_ID);
alter table CDR_OA2_SERVER
add constraint IDX_OA2SERVER_MODULE_ISSURL unique (MODULE_PID, ISS);
create index IDX_OA2ATOKEN_TOKEN_HASH on CDR_OAUTH2_ATOKEN (TOKEN_HASH);
create index IDX_OA2ATOKEN_USER on CDR_OAUTH2_ATOKEN (USER_PID);
create index IDX_OA2ATOKEN_EXPIRATION on CDR_OAUTH2_ATOKEN (EXPIRATION);
alter table CDR_OAUTH2_ATOKEN_ADNLINFO
add constraint IDX_OA2ATOKADLNF_ATOKEN_ADNLNF unique (TOKEN_PID, INFO_KEY);
alter table CDR_OAUTH2_ATOKEN_SCOPE
add constraint IDX_OA2ATOKSCP_ATOKEN_SCOPE unique (TOKEN_PID, SCOPE);
create index IDX_OA2AUTHCODE_EXPIRATION on CDR_OAUTH2_AUTH_CODE (EXPIRATION);
alter table CDR_OAUTH2_AUTH_CODE
add constraint IDX_OA2AUTHCODE_CODE unique (CODE_VAL);
alter table CDR_OAUTH2_CLIENT_DET_SCOPE
add constraint IDX_OA2CLIDETSCOP_TOKEN_SCOPE unique (CLIENT_PID, SCOPE);
alter table CDR_OAUTH2_CLIENT_DETAILS
add constraint IDX_OA2CLIENTDETAILS_CLI_MOD unique (MODULE_PID, CLIENT_ID);
create index IDX_OA2JWTUNIQID_TIMESTAMP on CDR_OAUTH2_JWT_UNIQ_ID (CONSUMED_TIMESTAMP);
create index IDX_OA2REFTOKEN_USER on CDR_OAUTH2_REFRESH_TOKEN (USER_PID);
create index IDX_OA2REFTOKEN_EXPIRATION on CDR_OAUTH2_REFRESH_TOKEN (EXPIRATION);
alter table CDR_OAUTH2_REFRESH_TOKEN
add constraint IDX_OA2REFTOKEN_TOKEN unique (TOKEN_VAL);
alter table CDR_OAUTH2_RTOKEN_ADNLINFO
add constraint IDX_OA2RTOKADLNF_RTOKEN_ADNLNF unique (TOKEN_PID, INFO_KEY);
alter table CDR_OAUTH2_RTOKEN_REQPRM
add constraint IDX_OA2RTOKREQPRM_RTOKEN_PARM unique (TOKEN_PID, PARM_KEY);
alter table CDR_OAUTH2_RTOKEN_SCOPE
add constraint IDX_OA2RTOKSCP_RTOKEN_SCOPE unique (TOKEN_PID, SCOPE);
create index IDX_USER_FAMILYNAME on CDR_USER_DETAILS (MODULE_PID, FAMILY_NAME);
create index IDX_USER_GIVENNAME on CDR_USER_DETAILS (MODULE_PID, GIVEN_NAME);
alter table CDR_USER_DETAILS
add constraint IDX_USERDET_MODULE_USERNAME unique (MODULE_PID, USERNAME);
create index IDX_USERDEFLNCHCTX_USER on CDR_USER_DETAILS_DEF_LNCH_CTX (USER_PID);
alter table CDR_USER_DETAILS_DEF_LNCH_CTX
add constraint IDX_USERDEFLNCHCTX_USER_CTYP unique (USER_PID, CONTEXT_TYPE);
create index IDX_USROA2CLNTAPRVDSCP_CLNTUSR on CDR_USER_DETAILS_OA2CLNAPRVSCP (CLIENT_PID, USER_PID);
alter table CDR_USER_TFA_KEY
add constraint IDX_USERTFAKEY_USER unique (USER_PID);
create index IDX_XACTLOG_INITTS on CDR_XACT_LOG (INITIAL_TIMESTAMP);
alter table CDR_XACT_LOG
add constraint IDX_GUID unique (SRC_GUID);
create index IDX_XACTLOGSTEP_LOGID on CDR_XACT_LOG_STEP (LOG_PID);
alter table AG_APPL
add constraint FK_AG_APPL_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table AG_APPL
add constraint FK_AG_APPL_OAUTH2_CLIENT
foreign key (OAUTH2_CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table AG_CLASS
add constraint FK_AG_CLASS_AG_APPL
foreign key (AG_APPL_PID)
references AG_APPL;
alter table AG_NOTE
add constraint FK_AG_NOTE_AG_APPL
foreign key (AG_APPL_PID)
references AG_APPL;
alter table CDR_AUDIT_EVT
add constraint FK_AUDITEVT_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_AUDIT_EVT
add constraint FK_AUDITEVT_ENDPOINTMODULE
foreign key (ENDPOINT_MODULE_PID)
references CDR_MODULE;
alter table CDR_AUDIT_EVT
add constraint FK_AUDITEVT_TYPE
foreign key (TYPE_PID)
references CDR_AUDIT_EVT_TYPE;
alter table CDR_AUDIT_EVT
add constraint FK_AUDITEVT_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_AUDIT_EVT_TARGET_MODULE
add constraint FK_AUDITEVT_TARGETMODULE_EVT
foreign key (EVENT_PID)
references CDR_AUDIT_EVT;
alter table CDR_AUDIT_EVT_TARGET_MODULE
add constraint FK_AUDITEVT_TARGETMOD_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_AUDIT_EVT_TARGET_RES
add constraint FK_AUDITEVT_TARGETRES_EVT
foreign key (EVENT_PID)
references CDR_AUDIT_EVT;
alter table CDR_AUDIT_EVT_TARGET_RES
add constraint FK_AUDITEVT_TARGETRES_PERSMOD
foreign key (PERS_MODULE_PID)
references CDR_MODULE;
alter table CDR_AUDIT_EVT_TARGET_USER
add constraint FK_AUDITEVT_TARGETUSER_EVT
foreign key (EVENT_PID)
references CDR_AUDIT_EVT;
alter table CDR_AUDIT_EVT_TARGET_USER
add constraint FK_AUDITEVT_TARGETUSER_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_CDA_TEMPLATE
add constraint FK_CDA_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_CDA_TEMPLATE_PARAM
add constraint FK_CDATEMPLATE_PID
foreign key (TEMPLATE_PID)
references CDR_CDA_TEMPLATE;
alter table CDR_FHIR_SEARCHPARAM
add constraint FK_FHIRSEARCHPARAM_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_HTTP_SESSION
add constraint FK_HTTPSESSION_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_METRIC_GAUGE
add constraint FK_METGAUGE_MODP
foreign key (MODPROC_PID)
references CDR_MODULE_PROCESS;
alter table CDR_METRIC_HC
add constraint FK_HC_MODP
foreign key (MODPROC_PID)
references CDR_MODULE_PROCESS;
alter table CDR_METRIC_TIMER
add constraint FK_METTIMER_MODP
foreign key (MODPROC_PID)
references CDR_MODULE_PROCESS;
alter table CDR_MODULE
add constraint FK_MODULE_NODE
foreign key (NODE_PID)
references CDR_NODE;
alter table CDR_MODULE
add constraint FK_MODULE_PROTOTYPE
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROTOTYPE;
alter table CDR_MODULE_CFG_PROP
add constraint FK_MODULECFGPROP_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_MODULE_CFG_PROP
add constraint FK_MODULECFGPROP_PROTOTYPE
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROT_CFG;
alter table CDR_MODULE_DEP
add constraint FK_MODULEDEP_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_MODULE_DEP
add constraint FK_MODULEDEP_PROTOTYPE
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROT_DEP;
alter table CDR_MODULE_DEP
add constraint FK_MODULEDEP_TARGET
foreign key (TARGET_PID)
references CDR_MODULE;
alter table CDR_MODULE_PROCESS
add constraint FK_MODPROC_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_MODULE_PROCESS
add constraint FK_MODPROC_NODEPROC
foreign key (NODEPROC_PID)
references CDR_NODE_PROCESS;
alter table CDR_MODULE_PROT_CFG
add constraint FK_CDR_MODULEPROTCFG_PROT
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROTOTYPE;
alter table CDR_MODULE_PROT_DEP
add constraint FK_MODULE_PROT_DEP_PROT
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROTOTYPE;
alter table CDR_MODULE_PROT_SUPPLIES
add constraint FK_MODULE_PROT_SUPPLIES_PROT
foreign key (PROTOTYPE_PID)
references CDR_MODULE_PROTOTYPE;
alter table CDR_MODULE_STATE
add constraint FK_MODSTAT_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_NODE_PROCESS
add constraint FK_NODEPROCESS_NODE
foreign key (NODE_PID)
references CDR_NODE;
alter table CDR_OA2_CLI_DET_AUTH_GRNT_TYP
add constraint FK_OA2CLIDETAUTHGRNTTY_CLI
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OA2_CLI_DET_AUTOAP_SCOPE
add constraint FK_OA2CLIDETAUTOAPPSCOP_CLI
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OA2_CLI_DET_AUTOGRNT_SCOPE
add constraint FK_OA2CLIDETAUTOGRNTPSCOP_CLI
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OA2_CLI_DET_CLISEC
add constraint FK_OA2CLIDETCLISEC_CLI
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OA2_CLI_DET_REG_REDIR_URI
add constraint FK_OA2CLIDETREGREDURI_CLI
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OA2_SERVER
add constraint FK_OA2SERVER_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_OAUTH2_ATOKEN
add constraint FK_OA2ATOKEN_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OAUTH2_ATOKEN
add constraint FK_OA2ATOKEN_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_OAUTH2_ATOKEN
add constraint FK_OA2ATOKEN_REF_TOK
foreign key (REFRESH_TOKEN_PID)
references CDR_OAUTH2_REFRESH_TOKEN;
alter table CDR_OAUTH2_ATOKEN
add constraint FK_OA2ATOKEN_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_OAUTH2_ATOKEN_ADNLINFO
add constraint FK_OA2ATOKADNLINFO_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_ATOKEN;
alter table CDR_OAUTH2_ATOKEN_GRNTDA
add constraint FK_OA2ATOKGRNTDA_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_ATOKEN;
alter table CDR_OAUTH2_ATOKEN_SCOPE
add constraint FK_OA2ATOKSCP_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_ATOKEN;
alter table CDR_OAUTH2_CLIENT_DET_SCOPE
add constraint FK_OA2CLIDETSCOP_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OAUTH2_CLIENT_DETAILS
add constraint FK_OA2CLIENTDETAILS_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_OAUTH2_CLIENT_PERM
add constraint FK_OA2CLIENTPERM_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OAUTH2_REFRESH_TOKEN
add constraint FK_OA2REFTOKEN_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_OAUTH2_REFRESH_TOKEN
add constraint FK_OA2REFTOKEN_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_OAUTH2_RTOKEN_ADNLINFO
add constraint FK_OA2RTOKADNLINFO_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_REFRESH_TOKEN;
alter table CDR_OAUTH2_RTOKEN_GRNTDA
add constraint FK_OA2RTOKGRNTDA_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_REFRESH_TOKEN;
alter table CDR_OAUTH2_RTOKEN_REQPRM
add constraint FK_OA2RTOKREQPRM_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_REFRESH_TOKEN;
alter table CDR_OAUTH2_RTOKEN_SCOPE
add constraint FK_OA2RTOKSCP_TOKEN
foreign key (TOKEN_PID)
references CDR_OAUTH2_REFRESH_TOKEN;
alter table CDR_USER_DETAILS
add constraint FK_USERDETAILS_MODULE
foreign key (MODULE_PID)
references CDR_MODULE;
alter table CDR_USER_DETAILS_DEF_LNCH_CTX
add constraint FK_USERDEFLNCHCTX_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_USER_DETAILS_OA2CLNAPRVSCP
add constraint FK_USROA2CLNTAPRVDSCP_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_USER_DETAILS_OA2CLNAPRVSCP
add constraint FK_USROA2CLNTAPRVDSCP_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_USER_PERM
add constraint FK_USERPERM_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_USER_TFA_KEY
add constraint FK_USERTFAKEY_USER
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_XACT_LOG
add constraint FK_TRANSIT_CLIENT
foreign key (CLIENT_PID)
references CDR_OAUTH2_CLIENT_DETAILS;
alter table CDR_XACT_LOG
add constraint FK_XACTLOGSTEP_MODULE
foreign key (CDR_ENDPOINT_MODULE_PID)
references CDR_MODULE;
alter table CDR_XACT_LOG
add constraint FK_TRANSIT_EVENT
foreign key (USER_PID)
references CDR_USER_DETAILS;
alter table CDR_XACT_LOG_STEP
add constraint FK_XACTLOGSTEP_XACTLOG
foreign key (LOG_PID)
references CDR_XACT_LOG;
To initialize a new SQL Server database for FHIR Storage (Relational), initialize a database with the following SQL:
create sequence SEQ_BLKEXCOL_PID start with 1 increment by 50;
create sequence SEQ_BLKEXCOLFILE_PID start with 1 increment by 50;
create sequence SEQ_BLKEXJOB_PID start with 1 increment by 50;
create sequence SEQ_CDH_LB_REF start with 1 increment by 50;
create sequence SEQ_CDH_LB_SUB_GROUP start with 1 increment by 50;
create sequence SEQ_CDH_LB_WL start with 1 increment by 50;
create sequence SEQ_CDH_LB_WL_SUBS start with 1 increment by 50;
create sequence SEQ_CNCPT_MAP_GRP_ELM_TGT_PID start with 1 increment by 50;
create sequence SEQ_CODESYSTEM_PID start with 1 increment by 50;
create sequence SEQ_CODESYSTEMVER_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_DESIG_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_MAP_GROUP_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_MAP_GRP_ELM_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_MAP_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_PC_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_PID start with 1 increment by 50;
create sequence SEQ_CONCEPT_PROP_PID start with 1 increment by 50;
create sequence SEQ_EMPI_LINK_ID start with 1 increment by 50;
create sequence SEQ_FORCEDID_ID start with 1 increment by 50;
create sequence SEQ_HISTORYTAG_ID start with 1 increment by 50;
create sequence SEQ_IDXCMPSTRUNIQ_ID start with 1 increment by 50;
create sequence SEQ_NPM_PACK start with 1 increment by 50;
create sequence SEQ_NPM_PACKVER start with 1 increment by 50;
create sequence SEQ_NPM_PACKVERRES start with 1 increment by 50;
create sequence SEQ_RES_REINDEX_JOB start with 1 increment by 50;
create sequence SEQ_RESLINK_ID start with 1 increment by 50;
create sequence SEQ_RESOURCE_HISTORY_ID start with 1 increment by 50;
create sequence SEQ_RESOURCE_ID start with 1 increment by 50;
create sequence SEQ_RESPARMPRESENT_ID start with 1 increment by 50;
create sequence SEQ_RESTAG_ID start with 1 increment by 50;
create sequence SEQ_SEARCH start with 1 increment by 50;
create sequence SEQ_SEARCH_INC start with 1 increment by 50;
create sequence SEQ_SEARCH_RES start with 1 increment by 50;
create sequence SEQ_SPIDX_COORDS start with 1 increment by 50;
create sequence SEQ_SPIDX_DATE start with 1 increment by 50;
create sequence SEQ_SPIDX_NUMBER start with 1 increment by 50;
create sequence SEQ_SPIDX_QUANTITY start with 1 increment by 50;
create sequence SEQ_SPIDX_QUANTITY_NRML start with 1 increment by 50;
create sequence SEQ_SPIDX_STRING start with 1 increment by 50;
create sequence SEQ_SPIDX_TOKEN start with 1 increment by 50;
create sequence SEQ_SPIDX_URI start with 1 increment by 50;
create sequence SEQ_SUBSCRIPTION_ID start with 1 increment by 50;
create sequence SEQ_TAGDEF_ID start with 1 increment by 50;
create sequence SEQ_VALUESET_C_DSGNTN_PID start with 1 increment by 50;
create sequence SEQ_VALUESET_CONCEPT_PID start with 1 increment by 50;
create sequence SEQ_VALUESET_PID start with 1 increment by 50;
create table CDH_LB_REF (
PID bigint not null,
EXPIRES datetime2,
LB_RES_ID bigint not null,
ORDER_DATE datetime2,
ROOT_RES_ID bigint not null,
RULE_SYSTEM varchar(200) not null,
RULE_VALUE varchar(200) not null,
SUBS_RES_ID bigint not null,
TRACK_PARAM varchar(200),
TRACK_SUBPARAM varchar(200),
primary key (PID)
);
create table CDH_LB_SUB_GROUP (
PID bigint not null,
SUBS_GROUP varchar(200) not null,
SUBS_ID varchar(200) not null,
SUBS_RES_ID bigint not null,
primary key (PID)
);
create table CDH_LB_WL (
PID bigint not null,
SUBSCRIBER_TYPE varchar(200) not null,
WATCHLIST_SYSTEM varchar(200) not null,
WATCHLIST_VALUE varchar(200) not null,
primary key (PID)
);
create table CDH_LB_WL_SUBS (
PID bigint not null,
SEED_STATUS int not null,
SUBS_ID varchar(200) not null,
SUBS_RES_ID bigint not null,
WATCHLIST_ID bigint not null,
primary key (PID)
);
create table HFJ_BINARY_STORAGE_BLOB (
BLOB_ID varchar(200) not null,
BLOB_DATA varbinary(MAX) not null,
CONTENT_TYPE varchar(100) not null,
BLOB_HASH varchar(128),
PUBLISHED_DATE datetime2 not null,
RESOURCE_ID varchar(100) not null,
BLOB_SIZE int,
primary key (BLOB_ID)
);
create table HFJ_BLK_EXPORT_COLFILE (
PID bigint not null,
RES_ID varchar(100) not null,
COLLECTION_PID bigint not null,
primary key (PID)
);
create table HFJ_BLK_EXPORT_COLLECTION (
PID bigint not null,
TYPE_FILTER varchar(1000),
RES_TYPE varchar(40) not null,
OPTLOCK int not null,
JOB_PID bigint not null,
primary key (PID)
);
create table HFJ_BLK_EXPORT_JOB (
PID bigint not null,
CREATED_TIME datetime2 not null,
EXP_TIME datetime2 not null,
JOB_ID varchar(36) not null,
REQUEST varchar(500) not null,
EXP_SINCE datetime2,
JOB_STATUS varchar(10) not null,
STATUS_MESSAGE varchar(500),
STATUS_TIME datetime2 not null,
OPTLOCK int not null,
primary key (PID)
);
create table HFJ_FORCED_ID (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
FORCED_ID varchar(100) not null,
RESOURCE_PID bigint not null,
RESOURCE_TYPE varchar(100) default '',
primary key (PID)
);
create table HFJ_HISTORY_TAG (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
TAG_ID bigint,
RES_VER_PID bigint not null,
RES_ID bigint not null,
RES_TYPE varchar(40) not null,
primary key (PID)
);
create table HFJ_IDX_CMP_STRING_UNIQ (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
IDX_STRING varchar(200) not null,
RES_ID bigint,
primary key (PID)
);
create table HFJ_PARTITION (
PART_ID int not null,
PART_DESC varchar(200),
PART_NAME varchar(200) not null,
primary key (PART_ID)
);
create table HFJ_RES_LINK (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SRC_PATH varchar(200) not null,
SRC_RESOURCE_ID bigint not null,
SOURCE_RESOURCE_TYPE varchar(40) not null,
TARGET_RESOURCE_ID bigint,
TARGET_RESOURCE_TYPE varchar(40) not null,
TARGET_RESOURCE_URL varchar(200),
TARGET_RESOURCE_VERSION bigint,
SP_UPDATED datetime2,
primary key (PID)
);
create table HFJ_RES_PARAM_PRESENT (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
HASH_PRESENCE bigint,
SP_PRESENT bit not null,
RES_ID bigint not null,
primary key (PID)
);
create table HFJ_RES_REINDEX_JOB (
PID bigint not null,
JOB_DELETED bit not null,
REINDEX_COUNT int,
RES_TYPE varchar(100),
SUSPENDED_UNTIL datetime2,
UPDATE_THRESHOLD_HIGH datetime2 not null,
UPDATE_THRESHOLD_LOW datetime2,
primary key (PID)
);
create table HFJ_RES_TAG (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
TAG_ID bigint,
RES_ID bigint,
RES_TYPE varchar(40) not null,
primary key (PID)
);
create table HFJ_RES_VER (
PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
RES_DELETED_AT datetime2,
RES_VERSION varchar(7),
HAS_TAGS bit not null,
RES_PUBLISHED datetime2 not null,
RES_UPDATED datetime2 not null,
RES_ENCODING varchar(5) not null,
RES_TEXT varbinary(MAX),
RES_ID bigint not null,
RES_TYPE varchar(40) not null,
RES_VER bigint not null,
primary key (PID)
);
create table HFJ_RES_VER_PROV (
RES_VER_PID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
REQUEST_ID varchar(16),
SOURCE_URI varchar(100),
RES_PID bigint not null,
primary key (RES_VER_PID)
);
create table HFJ_RESOURCE (
RES_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
RES_DELETED_AT datetime2,
RES_VERSION varchar(7),
HAS_TAGS bit not null,
RES_PUBLISHED datetime2 not null,
RES_UPDATED datetime2 not null,
SP_HAS_LINKS bit,
HASH_SHA256 varchar(64),
SP_INDEX_STATUS bigint,
RES_LANGUAGE varchar(20),
SP_CMPSTR_UNIQ_PRESENT bit,
SP_COORDS_PRESENT bit,
SP_DATE_PRESENT bit,
SP_NUMBER_PRESENT bit,
SP_QUANTITY_NRML_PRESENT bit,
SP_QUANTITY_PRESENT bit,
SP_STRING_PRESENT bit,
SP_TOKEN_PRESENT bit,
SP_URI_PRESENT bit,
RES_TYPE varchar(40) not null,
RES_VER bigint,
primary key (RES_ID)
);
create table HFJ_SEARCH (
PID bigint not null,
CREATED datetime2 not null,
SEARCH_DELETED bit,
EXPIRY_OR_NULL datetime2,
FAILURE_CODE int,
FAILURE_MESSAGE varchar(500),
LAST_UPDATED_HIGH datetime2,
LAST_UPDATED_LOW datetime2,
NUM_BLOCKED int,
NUM_FOUND int not null,
PREFERRED_PAGE_SIZE int,
RESOURCE_ID bigint,
RESOURCE_TYPE varchar(200),
SEARCH_PARAM_MAP varbinary(MAX),
SEARCH_QUERY_STRING varchar(MAX),
SEARCH_QUERY_STRING_HASH int,
SEARCH_TYPE int not null,
SEARCH_STATUS varchar(10) not null,
TOTAL_COUNT int,
SEARCH_UUID varchar(36) not null,
OPTLOCK_VERSION int,
primary key (PID)
);
create table HFJ_SEARCH_INCLUDE (
PID bigint not null,
SEARCH_INCLUDE varchar(200) not null,
INC_RECURSE bit not null,
REVINCLUDE bit not null,
SEARCH_PID bigint not null,
primary key (PID)
);
create table HFJ_SEARCH_RESULT (
PID bigint not null,
SEARCH_ORDER int not null,
RESOURCE_PID bigint not null,
SEARCH_PID bigint not null,
primary key (PID)
);
create table HFJ_SPIDX_COORDS (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
SP_LATITUDE double precision,
SP_LONGITUDE double precision,
primary key (SP_ID)
);
create table HFJ_SPIDX_DATE (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
SP_VALUE_HIGH datetime2,
SP_VALUE_HIGH_DATE_ORDINAL int,
SP_VALUE_LOW datetime2,
SP_VALUE_LOW_DATE_ORDINAL int,
primary key (SP_ID)
);
create table HFJ_SPIDX_NUMBER (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
SP_VALUE numeric(19,2),
primary key (SP_ID)
);
create table HFJ_SPIDX_QUANTITY (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
HASH_IDENTITY_AND_UNITS bigint,
HASH_IDENTITY_SYS_UNITS bigint,
SP_SYSTEM varchar(200),
SP_UNITS varchar(200),
SP_VALUE double precision,
primary key (SP_ID)
);
create table HFJ_SPIDX_QUANTITY_NRML (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
HASH_IDENTITY_AND_UNITS bigint,
HASH_IDENTITY_SYS_UNITS bigint,
SP_SYSTEM varchar(200),
SP_UNITS varchar(200),
SP_VALUE double precision,
primary key (SP_ID)
);
create table HFJ_SPIDX_STRING (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_EXACT bigint,
HASH_IDENTITY bigint,
HASH_NORM_PREFIX bigint,
SP_VALUE_EXACT varchar(200),
SP_VALUE_NORMALIZED varchar(200),
primary key (SP_ID)
);
create table HFJ_SPIDX_TOKEN (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
HASH_SYS bigint,
HASH_SYS_AND_VALUE bigint,
HASH_VALUE bigint,
SP_SYSTEM varchar(200),
SP_VALUE varchar(200),
primary key (SP_ID)
);
create table HFJ_SPIDX_URI (
SP_ID bigint not null,
PARTITION_DATE date,
PARTITION_ID int,
SP_MISSING bit not null,
SP_NAME varchar(100) not null,
RES_ID bigint not null,
RES_TYPE varchar(100) not null,
SP_UPDATED datetime2,
HASH_IDENTITY bigint,
HASH_URI bigint,
SP_URI varchar(254),
primary key (SP_ID)
);
create table HFJ_SUBSCRIPTION_STATS (
PID bigint not null,
CREATED_TIME datetime2 not null,
RES_ID bigint,
primary key (PID)
);
create table HFJ_TAG_DEF (
TAG_ID bigint not null,
TAG_CODE varchar(200),
TAG_DISPLAY varchar(200),
TAG_SYSTEM varchar(200),
TAG_TYPE int not null,
primary key (TAG_ID)
);
create table MPI_LINK (
PID bigint not null,
CREATED datetime2 not null,
EID_MATCH bit,
GOLDEN_RESOURCE_PID bigint not null,
NEW_PERSON bit,
LINK_SOURCE int not null,
MATCH_RESULT int not null,
TARGET_TYPE varchar(40),
PERSON_PID bigint not null,
RULE_COUNT bigint,
SCORE double precision,
TARGET_PID bigint not null,
UPDATED datetime2 not null,
VECTOR bigint,
VERSION varchar(16) not null,
primary key (PID)
);
create table NPM_PACKAGE (
PID bigint not null,
CUR_VERSION_ID varchar(200),
PACKAGE_DESC varchar(200),
PACKAGE_ID varchar(200) not null,
UPDATED_TIME datetime2 not null,
primary key (PID)
);
create table NPM_PACKAGE_VER (
PID bigint not null,
CURRENT_VERSION bit not null,
PKG_DESC varchar(200),
DESC_UPPER varchar(200),
FHIR_VERSION varchar(10) not null,
FHIR_VERSION_ID varchar(10) not null,
PACKAGE_ID varchar(200) not null,
PACKAGE_SIZE_BYTES bigint not null,
SAVED_TIME datetime2 not null,
UPDATED_TIME datetime2 not null,
VERSION_ID varchar(200) not null,
PACKAGE_PID bigint not null,
BINARY_RES_ID bigint not null,
primary key (PID)
);
create table NPM_PACKAGE_VER_RES (
PID bigint not null,
CANONICAL_URL varchar(200),
CANONICAL_VERSION varchar(200),
FILE_DIR varchar(200),
FHIR_VERSION varchar(10) not null,
FHIR_VERSION_ID varchar(10) not null,
FILE_NAME varchar(200),
RES_SIZE_BYTES bigint not null,
RES_TYPE varchar(40) not null,
UPDATED_TIME datetime2 not null,
PACKVER_PID bigint not null,
BINARY_RES_ID bigint not null,
primary key (PID)
);
create table TRM_CODESYSTEM (
PID bigint not null,
CODE_SYSTEM_URI varchar(200) not null,
CURRENT_VERSION_PID bigint,
CS_NAME varchar(200),
RES_ID bigint,
primary key (PID)
);
create table TRM_CODESYSTEM_VER (
PID bigint not null,
CS_DISPLAY varchar(200),
CODESYSTEM_PID bigint,
CS_VERSION_ID varchar(200),
RES_ID bigint not null,
primary key (PID)
);
create table TRM_CONCEPT (
PID bigint not null,
CODEVAL varchar(500) not null,
CODESYSTEM_PID bigint,
DISPLAY varchar(400),
INDEX_STATUS bigint,
PARENT_PIDS varchar(MAX),
CODE_SEQUENCE int,
CONCEPT_UPDATED datetime2,
primary key (PID)
);
create table TRM_CONCEPT_DESIG (
PID bigint not null,
LANG varchar(500),
USE_CODE varchar(500),
USE_DISPLAY varchar(500),
USE_SYSTEM varchar(500),
VAL varchar(2000) not null,
CS_VER_PID bigint,
CONCEPT_PID bigint,
primary key (PID)
);
create table TRM_CONCEPT_MAP (
PID bigint not null,
RES_ID bigint,
SOURCE_URL varchar(200),
TARGET_URL varchar(200),
URL varchar(200) not null,
VER varchar(200),
primary key (PID)
);
create table TRM_CONCEPT_MAP_GROUP (
PID bigint not null,
CONCEPT_MAP_URL varchar(200),
SOURCE_URL varchar(200) not null,
SOURCE_VS varchar(200),
SOURCE_VERSION varchar(200),
TARGET_URL varchar(200) not null,
TARGET_VS varchar(200),
TARGET_VERSION varchar(200),
CONCEPT_MAP_PID bigint not null,
primary key (PID)
);
create table TRM_CONCEPT_MAP_GRP_ELEMENT (
PID bigint not null,
SOURCE_CODE varchar(500) not null,
CONCEPT_MAP_URL varchar(200),
SOURCE_DISPLAY varchar(400),
SYSTEM_URL varchar(200),
SYSTEM_VERSION varchar(200),
VALUESET_URL varchar(200),
CONCEPT_MAP_GROUP_PID bigint not null,
primary key (PID)
);
create table TRM_CONCEPT_MAP_GRP_ELM_TGT (
PID bigint not null,
TARGET_CODE varchar(500) not null,
CONCEPT_MAP_URL varchar(200),
TARGET_DISPLAY varchar(400),
TARGET_EQUIVALENCE varchar(50),
SYSTEM_URL varchar(200),
SYSTEM_VERSION varchar(200),
VALUESET_URL varchar(200),
CONCEPT_MAP_GRP_ELM_PID bigint not null,
primary key (PID)
);
create table TRM_CONCEPT_PC_LINK (
PID bigint not null,
CHILD_PID bigint,
CODESYSTEM_PID bigint not null,
PARENT_PID bigint,
REL_TYPE int,
primary key (PID)
);
create table TRM_CONCEPT_PROPERTY (
PID bigint not null,
PROP_CODESYSTEM varchar(500),
PROP_DISPLAY varchar(500),
PROP_KEY varchar(500) not null,
PROP_TYPE int not null,
PROP_VAL varchar(500),
PROP_VAL_LOB varbinary(MAX),
CS_VER_PID bigint,
CONCEPT_PID bigint,
primary key (PID)
);
create table TRM_VALUESET (
PID bigint not null,
EXPANSION_STATUS varchar(50) not null,
VSNAME varchar(200),
RES_ID bigint,
TOTAL_CONCEPT_DESIGNATIONS bigint default 0 not null,
TOTAL_CONCEPTS bigint default 0 not null,
URL varchar(200) not null,
VER varchar(200),
primary key (PID)
);
create table TRM_VALUESET_C_DESIGNATION (
PID bigint not null,
VALUESET_CONCEPT_PID bigint not null,
LANG varchar(500),
USE_CODE varchar(500),
USE_DISPLAY varchar(500),
USE_SYSTEM varchar(500),
VAL varchar(2000) not null,
VALUESET_PID bigint not null,
primary key (PID)
);
create table TRM_VALUESET_CONCEPT (
PID bigint not null,
CODEVAL varchar(500) not null,
DISPLAY varchar(400),
VALUESET_ORDER int not null,
SYSTEM_URL varchar(200) not null,
SYSTEM_VER varchar(200),
VALUESET_PID bigint not null,
primary key (PID)
);
create index IDX_CDH_LB_REF_RULE_SUBS on CDH_LB_REF (RULE_SYSTEM, RULE_VALUE, SUBS_RES_ID);
create index IDX_CDH_LB_REF_TRACK_PARAM on CDH_LB_REF (TRACK_PARAM);
create index IDX_CDH_LB_REF_TRACK_SUBPARAM on CDH_LB_REF (TRACK_SUBPARAM);
create index IDX_CDH_LB_REF_EXPIRES on CDH_LB_REF (EXPIRES);
alter table CDH_LB_REF
add constraint IDX_CDH_LB_REF_UNIQ unique (RULE_SYSTEM, RULE_VALUE, ROOT_RES_ID, SUBS_RES_ID, LB_RES_ID);
create index IDX_CDH_LB_SUB_GROUP_GROUP on CDH_LB_SUB_GROUP (SUBS_GROUP);
create index IDX_CDH_LB_SUB_GROUP_ID on CDH_LB_SUB_GROUP (SUBS_ID);
alter table CDH_LB_SUB_GROUP
add constraint IDX_CDH_LB_SUB_ID_GROUP unique (SUBS_RES_ID, SUBS_GROUP);
alter table CDH_LB_WL
add constraint IDX_CDH_LB_WL_WATCHLIST_TOKEN unique (WATCHLIST_SYSTEM, WATCHLIST_VALUE);
alter table CDH_LB_WL_SUBS
add constraint IDX_CDH_LB_WL_SUBS_WATCHLIST unique (WATCHLIST_ID, SUBS_RES_ID);
create index IDX_BLKEX_EXPTIME on HFJ_BLK_EXPORT_JOB (EXP_TIME);
alter table HFJ_BLK_EXPORT_JOB
add constraint IDX_BLKEX_JOB_ID unique (JOB_ID);
alter table HFJ_FORCED_ID
add constraint IDX_FORCEDID_RESID unique (RESOURCE_PID);
alter table HFJ_FORCED_ID
add constraint IDX_FORCEDID_TYPE_FID unique (RESOURCE_TYPE, FORCED_ID);
alter table HFJ_HISTORY_TAG
add constraint IDX_RESHISTTAG_TAGID unique (RES_VER_PID, TAG_ID);
create index IDX_IDXCMPSTRUNIQ_RESOURCE on HFJ_IDX_CMP_STRING_UNIQ (RES_ID);
alter table HFJ_IDX_CMP_STRING_UNIQ
add constraint IDX_IDXCMPSTRUNIQ_STRING unique (IDX_STRING);
alter table HFJ_PARTITION
add constraint IDX_PART_NAME unique (PART_NAME);
create index IDX_RL_TPATHRES on HFJ_RES_LINK (SRC_PATH, TARGET_RESOURCE_ID);
create index IDX_RL_SRC on HFJ_RES_LINK (SRC_RESOURCE_ID);
create index IDX_RL_DEST on HFJ_RES_LINK (TARGET_RESOURCE_ID);
create index IDX_RESPARMPRESENT_RESID on HFJ_RES_PARAM_PRESENT (RES_ID);
create index IDX_RESPARMPRESENT_HASHPRES on HFJ_RES_PARAM_PRESENT (HASH_PRESENCE);
alter table HFJ_RES_TAG
add constraint IDX_RESTAG_TAGID unique (RES_ID, TAG_ID);
create index IDX_RESVER_TYPE_DATE on HFJ_RES_VER (RES_TYPE, RES_UPDATED);
create index IDX_RESVER_ID_DATE on HFJ_RES_VER (RES_ID, RES_UPDATED);
create index IDX_RESVER_DATE on HFJ_RES_VER (RES_UPDATED);
alter table HFJ_RES_VER
add constraint IDX_RESVER_ID_VER unique (RES_ID, RES_VER);
create index IDX_RESVERPROV_SOURCEURI on HFJ_RES_VER_PROV (SOURCE_URI);
create index IDX_RESVERPROV_REQUESTID on HFJ_RES_VER_PROV (REQUEST_ID);
create index IDX_RES_DATE on HFJ_RESOURCE (RES_UPDATED);
create index IDX_RES_LANG on HFJ_RESOURCE (RES_TYPE, RES_LANGUAGE);
create index IDX_RES_TYPE on HFJ_RESOURCE (RES_TYPE);
create index IDX_INDEXSTATUS on HFJ_RESOURCE (SP_INDEX_STATUS);
create index IDX_SEARCH_RESTYPE_HASHS on HFJ_SEARCH (RESOURCE_TYPE, SEARCH_QUERY_STRING_HASH, CREATED);
create index IDX_SEARCH_CREATED on HFJ_SEARCH (CREATED);
alter table HFJ_SEARCH
add constraint IDX_SEARCH_UUID unique (SEARCH_UUID);
alter table HFJ_SEARCH_RESULT
add constraint IDX_SEARCHRES_ORDER unique (SEARCH_PID, SEARCH_ORDER);
create index IDX_SP_COORDS_HASH on HFJ_SPIDX_COORDS (HASH_IDENTITY, SP_LATITUDE, SP_LONGITUDE);
create index IDX_SP_COORDS_UPDATED on HFJ_SPIDX_COORDS (SP_UPDATED);
create index IDX_SP_COORDS_RESID on HFJ_SPIDX_COORDS (RES_ID);
create index IDX_SP_DATE_HASH on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW, SP_VALUE_HIGH);
create index IDX_SP_DATE_HASH_LOW on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW);
create index IDX_SP_DATE_ORD_HASH on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW_DATE_ORDINAL, SP_VALUE_HIGH_DATE_ORDINAL);
create index IDX_SP_DATE_ORD_HASH_LOW on HFJ_SPIDX_DATE (HASH_IDENTITY, SP_VALUE_LOW_DATE_ORDINAL);
create index IDX_SP_DATE_RESID on HFJ_SPIDX_DATE (RES_ID);
create index IDX_SP_DATE_UPDATED on HFJ_SPIDX_DATE (SP_UPDATED);
create index IDX_SP_NUMBER_HASH_VAL on HFJ_SPIDX_NUMBER (HASH_IDENTITY, SP_VALUE);
create index IDX_SP_NUMBER_UPDATED on HFJ_SPIDX_NUMBER (SP_UPDATED);
create index IDX_SP_NUMBER_RESID on HFJ_SPIDX_NUMBER (RES_ID);
create index IDX_SP_QUANTITY_HASH on HFJ_SPIDX_QUANTITY (HASH_IDENTITY, SP_VALUE);
create index IDX_SP_QUANTITY_HASH_UN on HFJ_SPIDX_QUANTITY (HASH_IDENTITY_AND_UNITS, SP_VALUE);
create index IDX_SP_QUANTITY_HASH_SYSUN on HFJ_SPIDX_QUANTITY (HASH_IDENTITY_SYS_UNITS, SP_VALUE);
create index IDX_SP_QUANTITY_UPDATED on HFJ_SPIDX_QUANTITY (SP_UPDATED);
create index IDX_SP_QUANTITY_RESID on HFJ_SPIDX_QUANTITY (RES_ID);
create index IDX_SP_QNTY_NRML_HASH on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY, SP_VALUE);
create index IDX_SP_QNTY_NRML_HASH_UN on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY_AND_UNITS, SP_VALUE);
create index IDX_SP_QNTY_NRML_HASH_SYSUN on HFJ_SPIDX_QUANTITY_NRML (HASH_IDENTITY_SYS_UNITS, SP_VALUE);
create index IDX_SP_QNTY_NRML_UPDATED on HFJ_SPIDX_QUANTITY_NRML (SP_UPDATED);
create index IDX_SP_QNTY_NRML_RESID on HFJ_SPIDX_QUANTITY_NRML (RES_ID);
create index IDX_SP_STRING_HASH_IDENT on HFJ_SPIDX_STRING (HASH_IDENTITY);
create index IDX_SP_STRING_HASH_NRM on HFJ_SPIDX_STRING (HASH_NORM_PREFIX, SP_VALUE_NORMALIZED);
create index IDX_SP_STRING_HASH_EXCT on HFJ_SPIDX_STRING (HASH_EXACT);
create index IDX_SP_STRING_UPDATED on HFJ_SPIDX_STRING (SP_UPDATED);
create index IDX_SP_STRING_RESID on HFJ_SPIDX_STRING (RES_ID);
create index IDX_SP_TOKEN_HASH on HFJ_SPIDX_TOKEN (HASH_IDENTITY);
create index IDX_SP_TOKEN_HASH_S on HFJ_SPIDX_TOKEN (HASH_SYS);
create index IDX_SP_TOKEN_HASH_SV on HFJ_SPIDX_TOKEN (HASH_SYS_AND_VALUE);
create index IDX_SP_TOKEN_HASH_V on HFJ_SPIDX_TOKEN (HASH_VALUE);
create index IDX_SP_TOKEN_UPDATED on HFJ_SPIDX_TOKEN (SP_UPDATED);
create index IDX_SP_TOKEN_RESID on HFJ_SPIDX_TOKEN (RES_ID);
create index IDX_SP_URI on HFJ_SPIDX_URI (RES_TYPE, SP_NAME, SP_URI);
create index IDX_SP_URI_HASH_IDENTITY on HFJ_SPIDX_URI (HASH_IDENTITY, SP_URI);
create index IDX_SP_URI_HASH_URI on HFJ_SPIDX_URI (HASH_URI);
create index IDX_SP_URI_RESTYPE_NAME on HFJ_SPIDX_URI (RES_TYPE, SP_NAME);
create index IDX_SP_URI_UPDATED on HFJ_SPIDX_URI (SP_UPDATED);
create index IDX_SP_URI_COORDS on HFJ_SPIDX_URI (RES_ID);
alter table HFJ_SUBSCRIPTION_STATS
add constraint IDX_SUBSC_RESID unique (RES_ID);
alter table HFJ_TAG_DEF
add constraint IDX_TAGDEF_TYPESYSCODE unique (TAG_TYPE, TAG_SYSTEM, TAG_CODE);
alter table MPI_LINK
add constraint IDX_EMPI_PERSON_TGT unique (PERSON_PID, TARGET_PID);
alter table NPM_PACKAGE
add constraint IDX_PACK_ID unique (PACKAGE_ID);
alter table NPM_PACKAGE_VER
add constraint IDX_PACKVER unique (PACKAGE_ID, VERSION_ID);
create index IDX_PACKVERRES_URL on NPM_PACKAGE_VER_RES (CANONICAL_URL);
alter table TRM_CODESYSTEM
add constraint IDX_CS_CODESYSTEM unique (CODE_SYSTEM_URI);
alter table TRM_CODESYSTEM_VER
add constraint IDX_CODESYSTEM_AND_VER unique (CODESYSTEM_PID, CS_VERSION_ID);
create index IDX_CONCEPT_INDEXSTATUS on TRM_CONCEPT (INDEX_STATUS);
create index IDX_CONCEPT_UPDATED on TRM_CONCEPT (CONCEPT_UPDATED);
alter table TRM_CONCEPT
add constraint IDX_CONCEPT_CS_CODE unique (CODESYSTEM_PID, CODEVAL);
alter table TRM_CONCEPT_MAP
add constraint IDX_CONCEPT_MAP_URL unique (URL, VER);
create index IDX_CNCPT_MAP_GRP_CD on TRM_CONCEPT_MAP_GRP_ELEMENT (SOURCE_CODE);
create index IDX_CNCPT_MP_GRP_ELM_TGT_CD on TRM_CONCEPT_MAP_GRP_ELM_TGT (TARGET_CODE);
alter table TRM_VALUESET
add constraint IDX_VALUESET_URL unique (URL, VER);
alter table TRM_VALUESET_CONCEPT
add constraint IDX_VS_CONCEPT_CSCD unique (VALUESET_PID, SYSTEM_URL, CODEVAL);
alter table TRM_VALUESET_CONCEPT
add constraint IDX_VS_CONCEPT_ORDER unique (VALUESET_PID, VALUESET_ORDER);
alter table CDH_LB_REF
add constraint FK_LB_RES
foreign key (LB_RES_ID)
references HFJ_RESOURCE;
alter table CDH_LB_REF
add constraint FK_LB_ROOT
foreign key (ROOT_RES_ID)
references HFJ_RESOURCE;
alter table CDH_LB_REF
add constraint FK_LB_SUBS
foreign key (SUBS_RES_ID)
references HFJ_RESOURCE;
alter table CDH_LB_SUB_GROUP
add constraint FK_LB_GP_SUBS
foreign key (SUBS_RES_ID)
references HFJ_RESOURCE;
alter table CDH_LB_WL_SUBS
add constraint FK_LB_WL_SUBS
foreign key (SUBS_RES_ID)
references HFJ_RESOURCE;
alter table CDH_LB_WL_SUBS
add constraint FK_LB_WL_ID
foreign key (WATCHLIST_ID)
references CDH_LB_WL;
alter table HFJ_BLK_EXPORT_COLFILE
add constraint FK_BLKEXCOLFILE_COLLECT
foreign key (COLLECTION_PID)
references HFJ_BLK_EXPORT_COLLECTION;
alter table HFJ_BLK_EXPORT_COLLECTION
add constraint FK_BLKEXCOL_JOB
foreign key (JOB_PID)
references HFJ_BLK_EXPORT_JOB;
alter table HFJ_FORCED_ID
add constraint FK_FORCEDID_RESOURCE
foreign key (RESOURCE_PID)
references HFJ_RESOURCE;
alter table HFJ_HISTORY_TAG
add constraint FKtderym7awj6q8iq5c51xv4ndw
foreign key (TAG_ID)
references HFJ_TAG_DEF;
alter table HFJ_HISTORY_TAG
add constraint FK_HISTORYTAG_HISTORY
foreign key (RES_VER_PID)
references HFJ_RES_VER;
alter table HFJ_IDX_CMP_STRING_UNIQ
add constraint FK_IDXCMPSTRUNIQ_RES_ID
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_LINK
add constraint FK_RESLINK_SOURCE
foreign key (SRC_RESOURCE_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_LINK
add constraint FK_RESLINK_TARGET
foreign key (TARGET_RESOURCE_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_PARAM_PRESENT
add constraint FK_RESPARMPRES_RESID
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_TAG
add constraint FKbfcjbaftmiwr3rxkwsy23vneo
foreign key (TAG_ID)
references HFJ_TAG_DEF;
alter table HFJ_RES_TAG
add constraint FK_RESTAG_RESOURCE
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_VER
add constraint FK_RESOURCE_HISTORY_RESOURCE
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_RES_VER_PROV
add constraint FK_RESVERPROV_RES_PID
foreign key (RES_PID)
references HFJ_RESOURCE;
alter table HFJ_RES_VER_PROV
add constraint FK_RESVERPROV_RESVER_PID
foreign key (RES_VER_PID)
references HFJ_RES_VER;
alter table HFJ_SEARCH_INCLUDE
add constraint FK_SEARCHINC_SEARCH
foreign key (SEARCH_PID)
references HFJ_SEARCH;
alter table HFJ_SPIDX_COORDS
add constraint FKc97mpk37okwu8qvtceg2nh9vn
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_DATE
add constraint FK17s70oa59rm9n61k9thjqrsqm
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_NUMBER
add constraint FKcltihnc5tgprj9bhpt7xi5otb
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_QUANTITY
add constraint FKn603wjjoi1a6asewxbbd78bi5
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_QUANTITY_NRML
add constraint FKrcjovmuh5kc0o6fvble319pyv
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_STRING
add constraint FK_SPIDXSTR_RESOURCE
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_TOKEN
add constraint FK7ulx3j1gg3v7maqrejgc7ybc4
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SPIDX_URI
add constraint FKgxsreutymmfjuwdswv3y887do
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table HFJ_SUBSCRIPTION_STATS
add constraint FK_SUBSC_RESOURCE_ID
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table MPI_LINK
add constraint FK_EMPI_LINK_GOLDEN_RESOURCE
foreign key (GOLDEN_RESOURCE_PID)
references HFJ_RESOURCE;
alter table MPI_LINK
add constraint FK_EMPI_LINK_PERSON
foreign key (PERSON_PID)
references HFJ_RESOURCE;
alter table MPI_LINK
add constraint FK_EMPI_LINK_TARGET
foreign key (TARGET_PID)
references HFJ_RESOURCE;
alter table NPM_PACKAGE_VER
add constraint FK_NPM_PKV_PKG
foreign key (PACKAGE_PID)
references NPM_PACKAGE;
alter table NPM_PACKAGE_VER
add constraint FK_NPM_PKV_RESID
foreign key (BINARY_RES_ID)
references HFJ_RESOURCE;
alter table NPM_PACKAGE_VER_RES
add constraint FK_NPM_PACKVERRES_PACKVER
foreign key (PACKVER_PID)
references NPM_PACKAGE_VER;
alter table NPM_PACKAGE_VER_RES
add constraint FK_NPM_PKVR_RESID
foreign key (BINARY_RES_ID)
references HFJ_RESOURCE;
alter table TRM_CODESYSTEM
add constraint FK_TRMCODESYSTEM_CURVER
foreign key (CURRENT_VERSION_PID)
references TRM_CODESYSTEM_VER;
alter table TRM_CODESYSTEM
add constraint FK_TRMCODESYSTEM_RES
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table TRM_CODESYSTEM_VER
add constraint FK_CODESYSVER_CS_ID
foreign key (CODESYSTEM_PID)
references TRM_CODESYSTEM;
alter table TRM_CODESYSTEM_VER
add constraint FK_CODESYSVER_RES_ID
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table TRM_CONCEPT
add constraint FK_CONCEPT_PID_CS_PID
foreign key (CODESYSTEM_PID)
references TRM_CODESYSTEM_VER;
alter table TRM_CONCEPT_DESIG
add constraint FK_CONCEPTDESIG_CSV
foreign key (CS_VER_PID)
references TRM_CODESYSTEM_VER;
alter table TRM_CONCEPT_DESIG
add constraint FK_CONCEPTDESIG_CONCEPT
foreign key (CONCEPT_PID)
references TRM_CONCEPT;
alter table TRM_CONCEPT_MAP
add constraint FK_TRMCONCEPTMAP_RES
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table TRM_CONCEPT_MAP_GROUP
add constraint FK_TCMGROUP_CONCEPTMAP
foreign key (CONCEPT_MAP_PID)
references TRM_CONCEPT_MAP;
alter table TRM_CONCEPT_MAP_GRP_ELEMENT
add constraint FK_TCMGELEMENT_GROUP
foreign key (CONCEPT_MAP_GROUP_PID)
references TRM_CONCEPT_MAP_GROUP;
alter table TRM_CONCEPT_MAP_GRP_ELM_TGT
add constraint FK_TCMGETARGET_ELEMENT
foreign key (CONCEPT_MAP_GRP_ELM_PID)
references TRM_CONCEPT_MAP_GRP_ELEMENT;
alter table TRM_CONCEPT_PC_LINK
add constraint FK_TERM_CONCEPTPC_CHILD
foreign key (CHILD_PID)
references TRM_CONCEPT;
alter table TRM_CONCEPT_PC_LINK
add constraint FK_TERM_CONCEPTPC_CS
foreign key (CODESYSTEM_PID)
references TRM_CODESYSTEM_VER;
alter table TRM_CONCEPT_PC_LINK
add constraint FK_TERM_CONCEPTPC_PARENT
foreign key (PARENT_PID)
references TRM_CONCEPT;
alter table TRM_CONCEPT_PROPERTY
add constraint FK_CONCEPTPROP_CSV
foreign key (CS_VER_PID)
references TRM_CODESYSTEM_VER;
alter table TRM_CONCEPT_PROPERTY
add constraint FK_CONCEPTPROP_CONCEPT
foreign key (CONCEPT_PID)
references TRM_CONCEPT;
alter table TRM_VALUESET
add constraint FK_TRMVALUESET_RES
foreign key (RES_ID)
references HFJ_RESOURCE;
alter table TRM_VALUESET_C_DESIGNATION
add constraint FK_TRM_VALUESET_CONCEPT_PID
foreign key (VALUESET_CONCEPT_PID)
references TRM_VALUESET_CONCEPT;
alter table TRM_VALUESET_C_DESIGNATION
add constraint FK_TRM_VSCD_VS_PID
foreign key (VALUESET_PID)
references TRM_VALUESET;
alter table TRM_VALUESET_CONCEPT
add constraint FK_TRM_VALUESET_PID
foreign key (VALUESET_PID)
references TRM_VALUESET;