<commit_before>/**************************************************************************
* Copyright 2016 Observational Health Data Sciences AND Informatics (OHDSI)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* Authors: <NAME>, <NAME>, <NAME>
* Date: 2022
**************************************************************************/

--1. Update latest_update field to new date
DO $_$
BEGIN
	PERFORM VOCABULARY_PACK.SetLatestUpdate(
	pVocabularyName			=> 'CGI',
	pVocabularyDate			=> TO_DATE('20180216', 'yyyymmdd'),
	pVocabularyVersion		=> 'CGI' || '20180216', -- should be hardcoded when API will not provide reproducible way for Updates
	pVocabularyDevSchema	=> 'dev_cgi'
);
END $_$;

--2. Truncate all working tables
TRUNCATE TABLE concept_stage;
TRUNCATE TABLE concept_relationship_stage;
TRUNCATE TABLE concept_synonym_stage;
TRUNCATE TABLE pack_content_stage;
TRUNCATE TABLE drug_strength_stage;

--3. Create temporary table
DROP TABLE IF EXISTS cgi_source;
CREATE UNLOGGED TABLE cgi_source AS
SELECT DISTINCT REGEXP_SPLIT_TO_TABLE(gdna, '__') AS concept_name,
	'CGI' AS vocabulary_id,
	REGEXP_SPLIT_TO_TABLE(gdna, '__') AS concept_code,
	REGEXP_SPLIT_TO_TABLE(gdna, '__') AS hgvs,
	gene,
	protein,
	gdna
FROM dev_cgi.genomic_cgi_source
WHERE gdna <> ''
	AND protein <> '.';

--4. Fill the concept_stage
INSERT INTO concept_stage (
	concept_name,
	domain_id,
	vocabulary_id,
	concept_class_id,
	standard_concept,
	concept_code,
	valid_start_date,
	valid_end_date
	)
SELECT vocabulary_pack.CutConceptName(n.concept_name) AS concept_name,
	'Measurement' AS domain_id,
	n.vocabulary_id AS vocabulary_id,
	'Variant' AS concept_class_id,
	NULL AS standard_concept,
	TRIM(SUBSTR(n.concept_code, 1, 50)) AS concept_code,
	v.latest_update AS valid_start_date,
	TO_DATE('20991231', 'yyyymmdd') AS valid_end_date
FROM cgi_source n
JOIN vocabulary v ON v.vocabulary_id = 'CGI';

--5. Fill the concept_synonym_stage
WITH tab
AS (
	SELECT *,
		REGEXP_REPLACE((REGEXP_MATCH(reference, 'NM_.\d.+\(p\..+\)\s|NM_.\d.+\(p\..+\)(?<!__PMID)|NM_.\d.+\(p\..+\)$')) [1], 'AND .+__Clinvar:|__PMID.+__Clinvar:|__PMID', ' @ ', 'gi') AS hgvs_array,
		TRIM(SUBSTR(REGEXP_SPLIT_TO_TABLE(gdna, '__'), 1, 50)) AS concept_code
	FROM dev_cgi.genomic_cgi_source
	WHERE reference ILIKE '%Clinvar%'
		AND protein <> '.'
	),
ref_hgvs
AS (
	SELECT gene,
		gdna,
		protein,
		transcript,
		info,
		context,
		cancer_acronym,
		source,
		reference,
		COALESCE(hgvs_array, (REGEXP_MATCH(SPLIT_PART(reference, ' AND ', 1), 'NM_.\d.+\s|NM_.\d.+$')) [1]) AS hgvs,
		concept_code
	FROM tab
	),
hgvs_synonyms
AS (
	SELECT 'ref' AS flag,
		'CGI' AS synonym_vocabulary_id,
		TRIM(REGEXP_SPLIT_TO_TABLE(hgvs, ' @ ')) AS synonym_name,
		concept_code AS synonym_concept_code
	FROM ref_hgvs
	
	UNION ALL
	
	SELECT flag,
		synonym_vocabulary_id,
		synonym_name,
		synonym_concept_code
	FROM (
		SELECT 'gdna' AS flag,
			'CGI' AS synonym_vocabulary_id,
			TRIM(REGEXP_SPLIT_TO_TABLE(gdna, '__')) AS synonym_name,
			TRIM(SUBSTR(concept_code, 1, 50)) AS synonym_concept_code
		FROM cgi_source
		WHERE protein <> '.'
		) AS x
	WHERE synonym_name ILIKE synonym_concept_code || '%'
	
	UNION ALL
	
	SELECT 'protein' AS flag,
		'CGI' AS synonym_vocabulary_id,
		TRIM(CONCAT (
				gene,
				':',
				protein
				)) AS synonym_name,
		TRIM(SUBSTR(concept_code, 1, 50)) AS synonym_concept_code
	FROM cgi_source
	WHERE protein <> '.'
	),
synonyms
AS (
	SELECT DISTINCT flag,
		synonym_vocabulary_id,
		synonym_name,
		synonym_concept_code
	FROM hgvs_synonyms
	WHERE flag IN (
			'protein',
			'gdna'
			)
		OR (
			flag = 'ref'
			AND synonym_name ILIKE 'NM%'
			)
	)
INSERT INTO concept_synonym_stage (
	synonym_vocabulary_id,
	language_concept_id,
	synonym_name,
	synonym_concept_code
	)
SELECT synonym_vocabulary_id,
 as     language_concept_id,
	synonym_name,
	synonym_concept_code
FROM synonyms 
;

--6. Clean up
DROP TABLE cgi_source;

-- At the end, the three tables concept_stage, concept_relationship_stage AND concept_synonym_stage should be ready to be fed into the generic_update.sql script<commit_msg>Genetic nomenclature is added as synonym name<commit_after>/**************************************************************************
* Copyright 2016 Observational Health Data Sciences AND Informatics (OHDSI)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
* Authors: <NAME>, <NAME>, <NAME>
* Date: 2022
**************************************************************************/

--1. Update latest_update field to new date
DO $_$
BEGIN
	PERFORM VOCABULARY_PACK.SetLatestUpdate(
	pVocabularyName			=> 'CGI',
	pVocabularyDate			=> TO_DATE('20180216', 'yyyymmdd'),
	pVocabularyVersion		=> 'CGI' || '20180216', -- should be hardcoded when API will not provide reproducible way for Updates
	pVocabularyDevSchema	=> 'dev_cgi'
);
END $_$;

--2. Truncate all working tables
TRUNCATE TABLE concept_stage;
TRUNCATE TABLE concept_relationship_stage;
TRUNCATE TABLE concept_synonym_stage;
TRUNCATE TABLE pack_content_stage;
TRUNCATE TABLE drug_strength_stage;

--3. Create temporary table
DROP TABLE IF EXISTS cgi_source;
CREATE UNLOGGED TABLE cgi_source AS
SELECT DISTINCT REGEXP_SPLIT_TO_TABLE(gdna, '__') AS concept_name,
	'CGI' AS vocabulary_id,
	REGEXP_SPLIT_TO_TABLE(gdna, '__') AS concept_code,
	REGEXP_SPLIT_TO_TABLE(gdna, '__') AS hgvs,
	gene,
	protein,
	gdna
FROM dev_cgi.genomic_cgi_source
WHERE gdna <> ''
	AND protein <> '.';

--4. Fill the concept_stage
INSERT INTO concept_stage (
	concept_name,
	domain_id,
	vocabulary_id,
	concept_class_id,
	standard_concept,
	concept_code,
	valid_start_date,
	valid_end_date
	)
SELECT vocabulary_pack.CutConceptName(n.concept_name) AS concept_name,
	'Measurement' AS domain_id,
	n.vocabulary_id AS vocabulary_id,
	'Variant' AS concept_class_id,
	NULL AS standard_concept,
	TRIM(SUBSTR(n.concept_code, 1, 50)) AS concept_code,
	v.latest_update AS valid_start_date,
	TO_DATE('20991231', 'yyyymmdd') AS valid_end_date
FROM cgi_source n
JOIN vocabulary v ON v.vocabulary_id = 'CGI';

--5. Fill the concept_synonym_stage
WITH tab
AS (
	SELECT *,
		REGEXP_REPLACE((REGEXP_MATCH(reference, 'NM_.\d.+\(p\..+\)\s|NM_.\d.+\(p\..+\)(?<!__PMID)|NM_.\d.+\(p\..+\)$')) [1], 'AND .+__Clinvar:|__PMID.+__Clinvar:|__PMID', ' @ ', 'gi') AS hgvs_array,
		TRIM(SUBSTR(REGEXP_SPLIT_TO_TABLE(gdna, '__'), 1, 50)) AS concept_code
	FROM dev_cgi.genomic_cgi_source
	WHERE reference ILIKE '%Clinvar%'
		AND protein <> '.'
	),
ref_hgvs
AS (
	SELECT gene,
		gdna,
		protein,
		transcript,
		info,
		context,
		cancer_acronym,
		source,
		reference,
		COALESCE(hgvs_array, (REGEXP_MATCH(SPLIT_PART(reference, ' AND ', 1), 'NM_.\d.+\s|NM_.\d.+$')) [1]) AS hgvs,
		concept_code
	FROM tab
	),
hgvs_synonyms
AS (
	SELECT 'ref' AS flag,
		'CGI' AS synonym_vocabulary_id,
		TRIM(REGEXP_SPLIT_TO_TABLE(hgvs, ' @ ')) AS synonym_name,
		concept_code AS synonym_concept_code
	FROM ref_hgvs
	
	UNION ALL
	
	SELECT flag,
		synonym_vocabulary_id,
		synonym_name,
		synonym_concept_code
	FROM (
		SELECT 'gdna' AS flag,
			'CGI' AS synonym_vocabulary_id,
			TRIM(REGEXP_SPLIT_TO_TABLE(gdna, '__')) AS synonym_name,
			TRIM(SUBSTR(concept_code, 1, 50)) AS synonym_concept_code
		FROM cgi_source
		WHERE protein <> '.'
		) AS x
	WHERE synonym_name ILIKE synonym_concept_code || '%'
	
	UNION ALL
	
	SELECT 'protein' AS flag,
		'CGI' AS synonym_vocabulary_id,
		TRIM(CONCAT (
				gene,
				':',
				protein
				)) AS synonym_name,
		TRIM(SUBSTR(concept_code, 1, 50)) AS synonym_concept_code
	FROM cgi_source
	WHERE protein <> '.'
	),
synonyms
AS (
	SELECT DISTINCT flag,
		synonym_vocabulary_id,
		synonym_name,
		synonym_concept_code
	FROM hgvs_synonyms
	WHERE flag IN (
			'protein',
			'gdna'
			)
		OR (
			flag = 'ref'
			AND synonym_name ILIKE 'NM%'
			)
	)
INSERT INTO concept_synonym_stage (
	synonym_vocabulary_id,
	language_concept_id,
	synonym_name,
	synonym_concept_code
	)
SELECT synonym_vocabulary_id,
33071 as     language_concept_id, -- Genetic nomenclature
	synonym_name,
	synonym_concept_code
FROM synonyms s
;

--6. Clean up
DROP TABLE cgi_source;

-- At the end, the three tables concept_stage, concept_relationship_stage AND concept_synonym_stage should be ready to be fed into the generic_update.sql script
SELECT * from devv5.concept where domain_id='Language'
{'fraction_non_alphanumeric': 0.13178126691932865, 'fraction_numerical': 0.012994044396318355, 'mean_word_length': 4.916720051249199, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 27, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1730654', 'n_tokens_mistral': 3739, 'n_tokens_neox': 3347, 'n_words': 864}