Pathogenic Strong

1. Insertar Guia Holística

INSERT INTO "Guideline"(id, title, author, applicability, url, rules, evaluation_type) VALUES

Guía Holística

La guía 8 es la que voy a usar para todos los criterios de mi TFM.

(8, 'Hollistic guideline', 'PROS UPV', NULL, NULL, '{"benign": {"lower": -9999999999, "upper": -6}, "likely benign": {"lower": -6, "upper": -1}, "VUS-ice cold": {"lower": 0, "upper": 0}, "VUS-cold": {"lower": 1, "upper": 1}, "VUS-cool": {"lower": 2, "upper": 2}, "VUS-tepid": {"lower": 3, "upper": 3}, "VUS-warm": {"lower": 4, "upper": 4}, "VUS-hot": {"lower": 5, "upper": 5}, "likely pathogenic": {"lower": 6, "upper": 9}, "pathogenic": {"lower": 10, "upper": 9999999999}}', 'score');

2. Insertar grupo de criterios

INSERT INTO "Criterion"(id, name, dimension, description, pass_rule, strength, direction, suggested_strength, min_accepted_strength, max_accepted_strength) VALUES

Criterios de PVS1

(45, 'PVS1.1.1', 'Variant Type and Location', 'A variant, within a biologically relevant transcript, that produces a premature termination codon or disrupts the translational reading frame, is predicted to undergo Nonsense Mediated Decay (NMD) resulting in a Loss of Function (LoF), that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 8, NULL, NULL),
(46, 'PVS1.1.2', 'Variant Type and Location', 'A variant, within a biologically relevant transcript, that produces a premature termination codon or disrupts the translational reading frame, is predicted to undergo Nonsense Mediated Decay (NMD) resulting in a Loss of Function (LoF), that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 2, NULL, NULL),
(47, 'PVS1.2.1', 'Variant Type and Location', 'A variant, within a protein domain, that produces a premature termination codon or disrupts the translational reading frame and is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 4, NULL, NULL),
(48, 'PVS1.2.2', 'Variant Type and Location', 'A variant, within a protein domain, that produces a premature termination codon or disrupts the translational reading frame and is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(49, 'PVS1.3', 'Variant Type and Location', 'A variant, within a transcript of unknown role, that produces a premature termination codon or disrupts the translational reading frame, is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(50, 'PVS1.4.1', 'Variant Type and Location', 'A canonical splice variant, within a biologically relevant transcript that disrupts the reading frame by exon skipping or use of a cryptic splice site, is predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 8, NULL, NULL),
(51, 'PVS1.4.2', 'Variant Type and Location', 'A canonical splice variant, within a biologically relevant transcript that disrupts the reading frame by exon skipping or use of a cryptic splice site, is predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 2, NULL, NULL),
(52, 'PVS1.5.1', 'Variant Type and Location', 'A canonical splice variant, within a biologically relevant transcript, is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 4, NULL, NULL),
(53, 'PVS1.5.2', 'Variant Type and Location', 'A canonical splice variant, within a biologically relevant transcript, is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(54, 'PVS1.6', 'Variant Type and Location', 'A canonical splice variant, within a transcript of unknown role, is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(55, 'PVS1.7.1', 'Variant Type and Location', 'Full gene deleted and definitive association with the disease under study.', 'AND', NULL, NULL, 8, NULL, NULL),
(56, 'PVS1.7.2', 'Variant Type and Location', 'Full gene deleted and moderate association with the disease under study.', 'AND', NULL, NULL, 2, NULL, NULL),
(57, 'PVS1.8.1', 'Variant Type and Location', 'A variant that results in a deletion of a biologically relevant exon is predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 8, NULL, NULL),
(58, 'PVS1.8.2', 'Variant Type and Location', 'A variant that results in a deletion of a biologically relevant exon is predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 2, NULL, NULL),
(59, 'PVS1.9.1', 'Variant Type and Location', 'A variant, within a protein domain, that results in a deletion of an exon is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 4, NULL, NULL),
(60, 'PVS1.9.2', 'Variant Type and Location', 'A variant, within a protein domain, that results in a deletion of an exon is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(61, 'PVS1.10', 'Variant Type and Location', 'A variant that results in a deletion of a biologically relevant exon is not predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a definitive association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL),
(62, 'PVS1.11', 'Variant Type and Location', 'A duplication variant of more than one exon in size, completely contained within the gene, is predicted to undergo Nonsense Mediated Decay (NMD), resulting in a Loss of Function (LoF) that showcases a moderate association with the disease under study.', 'AND', NULL, NULL, 1, NULL, NULL);

3. Relacionar Guideline_Criterion

INSERT IGNORE INTO Guideline_Criterion(guideline_id, criterion_id) VALUES

Criterios de PVS1 con Guía holística

(8, 45),
(8, 46),
(8, 47),
(8, 48),
(8, 49),
(8, 50),
(8, 51),
(8, 52),
(8, 53),
(8, 54),
(8, 55),
(8, 56),
(8, 57),
(8, 58),
(8, 59),
(8, 60),
(8, 61),
(8, 62);

4. Insertar Métricas

INSERT INTO Metric(id, name, description, columns_pattern, min_percentage_data_fulfillment, data_evaluation_condition) VALUES

Métricas de PVS1

(101, 'exon_biologically_relevant', 'The affected exon is present in biologically-relevant transcript(s).', 'Transcript.name@SnpEff', 1, '{"operation": "DISTINCT", "value": "."}'),
(102, 'definitive_gen_disease_association', 'Clinical validity classification of gene is "Definitive"', 'GenDiseaseAssociation.validity', 1, '{"operation": "CONTAINS", "value": "definitive"}'),
(103, 'moderate_gen_disease_association', 'Clinical validity classification of gene is "Moderate"', 'GenDiseaseAssociation.validity', 1, '{"operation": "CONTAINS", "value": "moderate"}'),
(104, 'nonsense_or_fameshift', 'Variant causes a STOP codon e.g.: Cag/Tag, Q/* OR Insertion or deletion causes a frame shift e.g.: An indel size is not multple of 3', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "IN", "value": ["stop_gained","frameshift_variant"]}'),
(105, 'not_predicted_NMD', 'The variant is not predicted by SnpEff to cause loss of function (LOF) or trigger nonsense-mediated decay (NMD). Caveat: NMD prediction based on the premature termination codon not occurring in the 3 most exon or the 3-most 50 bp of the penultimate exon.', 'ScorePrediction.score[ScorePrediction.name=NMD]@SnpEff,ScorePrediction.score[ScorePrediction.name=LOF]@SnpEff', 0.4, '{"operation": "EQUALS", "value": 0}'),
(106, 'not_in_domain_region', 'The variant is not located within a functionally characterized protein domain.', 'AminoAcidEntity.name[AminoAcidEntity.description=Domain]', 1, '{"operation": "EQUALS", "value": "."}'),
(107, 'canonical_splice', 'Mutations of the canonical ± 1 or 2 splice sites (intronic variants within 1 or 2 nucleotides from the exon).', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "IN", "value": ["splice_acceptor_variant","splice_donor_variant"]}'),
(108, 'feature_ablation', 'Deletion of a gene. Caveat: For a full gene deletion of a known haploinsufficient gene, a Pathogenic classification is warranted (in the absence of conflicting data) even though application of PVS1 alone would not reach a Pathogenic classification using the combining rules in Richards et al 2017.', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "EQUALS", "value": "feature_ablation"}'),
(109, 'duplication', 'An insertion which derives from, or is identical in sequence to, nucleotides present at a known location in the genome.', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "EQUALS", "value": "duplication"}'),
(110, 'exon_loss_variant', 'A deletion removes the whole exon.', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "EQUALS", "value": "exon_loss_variant"}');

Métricas que ya existen: - 11 - 7 - 30

5. Relacionar Criterion_Metric

INSERT IGNORE INTO Criterion_Metric (criterion_id, metric_id) VALUES

Métricas de PVS1 con Criterios de PVS1

Crit 45

(45, 104),
(45, 7),
(45, 101),
(45, 102),
(45, 11),

crit 46

(46, 104),
(46, 7),
(46, 101),
(46, 103),
(46, 11),

crit 47

(47, 104),
(47, 105),
(47, 30),
(47, 102),
(47, 11),

crit 48

(48, 104),
(48, 105),
(48, 30),
(48, 103),
(48, 11),

crit 49

(49, 104),
(49, 105),
(49, 106),
(49, 101),
(49, 102),
(49, 11),

crit 50

(50, 107),
(50, 7),
(50, 101),
(50, 102),
(50, 11),

crit 51

(51, 107),
(51, 7),
(51, 101),
(51, 103),
(51, 11),

crit 52

(52, 107),
(52, 105),
(52, 30),
(52, 102),
(52, 11),

crit 53

(53, 107),
(53, 105),
(53, 30),
(53, 103),
(53, 11),

crit 54

(54, 107),
(54, 105),
(54, 106),
(54, 101),
(54, 102),
(54, 11),

crit 55

(55, 108),
(55, 102),
(55, 11),

crit 56

(56, 108),
(56, 103),
(56, 11),

Crit 57

(57, 110),
(57, 7),
(57, 101),
(57, 102),
(57, 11),

crit 58

(58, 110),
(58, 7),
(58, 101),
(58, 103),
(58, 11),

crit 59

(59, 110),
(59, 105),
(59, 30),
(59, 102),
(59, 11),

crit 60

(60, 110),
(60, 105),
(60, 30),
(60, 103),
(60, 11),

crit 61

(61, 110),
(61, 105),
(61, 106),
(61, 101),
(61, 102),
(61, 11),

crit 62

(62, 109),
(62, 7),
(62, 102),
(62, 11);

6. Insertar pipeline

INSERT IGNORE INTO Pipeline (id, name, description, filtering_guideline, classification_guideline, report_template_id) VALUES
(8, 'holistic guideline', 'Pipeline for holistic classification', 7, 8, 3);
EJEMPLO:
el id 3 es el de hereditary cancer-> no es el mío.Tengo que cambiar el ID 3 por el mío. Tengo que averigar cuál fué el ID del pipeline para mi caso. Es muy probable que tenga que hacer uno nuevo, el ID libre es 8
(3, hereditary_cancer_scores, Pipeline for heriditary cancer with scored based evaluation, 2, 5, 3);


filtering guideline --> 7 esta por que es la de ..._no_gene_filter
(yo no tengo filto por genes)

classification guideline --> 8 la que cree nueva.

report template--> tengo entendido que me corresponde la 3 (la que usan casi todas las demás)

ADEMÁS: 
En caso de que ya esté subido tendría que hacer update....(?)
UPDATE nombredelatabla valoraactualizae SET valorquequierocambiar WHERE iddelpipelinequequieroactualizar

7. Relacionar Pipeline_File

INSERT IGNORE INTO Pipeline_File (Pipeline_id, File_id) VALUES 
(8, 64),
(8, 65),
(8, 66),
(8, 67),
(8, 68),
(8, 69),
(8, 70),
(8, 71),
(8, 72),
(8, 73),
(8, 74),
(8, 75),
(8, 81),
(8, 82),
(8, 83),
(8, 84),
(8, 85),
(8, 88),
(8, 89),
(8, 90),
(8, 99),
(8, 100),
(8, 207),
(8, 208),
(8, 722),
(8, 723),
(8, 727),
(8, 728),
(8, 729),
(8, 733);