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

(69, '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),
(70, '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),
(71, '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),
(72, '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),
(73, '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),
(74, '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),
(75, '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),
(76, '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),
(77, '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),
(78, '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),
(79, 'PVS1.7.1', 'Variant Type and Location', 'Full gene deleted and definitive association with the disease under study.', 'AND', NULL, NULL, 8, NULL, NULL),
(80, 'PVS1.7.2', 'Variant Type and Location', 'Full gene deleted and moderate association with the disease under study.', 'AND', NULL, NULL, 2, NULL, NULL),
(81, '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),
(82, '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),
(83, '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),
(84, '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),
(85, '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),
(86, '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, 69),
(8, 70),
(8, 71),
(8, 72),
(8, 73),
(8, 74),
(8, 75),
(8, 76),
(8, 77),
(8, 78),
(8, 79),
(8, 80),
(8, 81),
(8, 82),
(8, 83),
(8, 84),
(8, 85),
(8, 86);

4. Insertar Métricas

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

Métricas de PVS1

(104, 'exon_biologically_relevant', 'The affected exon is present in biologically-relevant transcript(s).', 'Transcript.name@SnpEff', 1, '{"operation": "DISTINCT", "value": "."}'),
(105, 'definitive_gen_disease_association', 'Clinical validity classification of gene is "Definitive"', 'GenDiseaseAssociation.validity', 1, '{"operation": "CONTAINS", "value": "definitive"}'),
(106, 'moderate_gen_disease_association', 'Clinical validity classification of gene is "Moderate"', 'GenDiseaseAssociation.validity', 1, '{"operation": "CONTAINS", "value": "moderate"}'),
(107, 'lof_mechanism_gene_loeuf', 'The gene demonstrates a loss-of-function (LoF) disease mechanism, supported by its low observed/expected (o/e) upper bound fraction (LOEUF) score (<0.6) in gnomAD.', 'ScorePrediction.score[ScorePrediction.name=LOEUF_Score]@GnomadConstraints', 1, '{"operation": "MINOR", "value": 0.6}'),
(108, '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"]}'),
(109, 'lof_nmd_variant', 'The variant is 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": 1}'),
(110, '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}'),
(111, 'variant_in_domain', 'The variant is located within a functionally characterized protein domain.', 'AminoAcidEntity.name[AminoAcidEntity.description=Domain]', 0.4, '{"operation": "DISTINCT", "value": "."}'),
(112, 'not_in_domain_region', 'The variant is not located within a functionally characterized protein domain.', 'AminoAcidEntity.name[AminoAcidEntity.description=Domain]', 1, '{"operation": "EQUALS", "value": "."}'),
(113, '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"]}'),
(114, '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"}'),
(115, '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"}'),
(116, 'exon_loss_variant', 'A deletion removes the whole exon.', 'ImpactPrediction.description@SnpEff', 1, '{"operation": "EQUALS", "value": "exon_loss_variant"}');

5. Relacionar Criterion_Metric

INSERT IGNORE INTO Criterion_Metric (criterion_id, metric_id) VALUES

Métricas de PVS1 con Criterios de PVS1

Crit 69

(69, 108),
(69, 109),
(69, 104),
(69, 105),
(69, 107),

crit 70

(70, 108),
(70, 109),
(70, 104),
(70, 106),
(70, 107),

crit 71

(71, 108),
(71, 110),
(71, 111),
(71, 105),
(71, 107),

crit 72

(72, 108),
(72, 110),
(72, 111),
(72, 106),
(72, 107),

crit 73

(73, 108),
(73, 110),
(73, 112),
(73, 104),
(73, 105),
(73, 107),

crit 74

(74, 113),
(74, 109),
(74, 104),
(74, 105),
(74, 107),

crit 75

(75, 113),
(75, 109),
(75, 104),
(75, 106),
(75, 107),

crit 76

(76, 113),
(76, 110),
(76, 111),
(76, 105),
(76, 107),

crit 77

(77, 113),
(77, 110),
(77, 111),
(77, 106),
(77, 107),

crit 78

(78, 113),
(78, 110),
(78, 112),
(78, 104),
(78, 105),
(78, 107),

crit 79

(79, 114),
(79, 105),
(79, 107),

crit 80

(80, 114),
(80, 106),
(80, 107),

Crit 81

(81, 116),
(81, 109),
(81, 104),
(81, 105),
(81, 107),

crit 82

(82, 116),
(82, 109),
(82, 104),
(82, 106),
(82, 107),

crit 83

(83, 116),
(83, 110),
(83, 111),
(83, 105),
(83, 107),

crit 84

(84, 116),
(84, 110),
(84, 111),
(84, 106),
(84, 107),

crit 85

(85, 116),
(85, 110),
(85, 112),
(85, 104),
(85, 105),
(85, 107),

crit 86

(86, 115),
(86, 109),
(86, 105),
(86, 107);

6. Insertar pipeline

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

# Pipeline (id ???) 
INSERT IGNORE INTO Pipeline (id, name, description, filtering_guideline, classification_guideline, report_template_id) VALUES
(3, hereditary_cancer_scores, Pipeline for heriditary cancer with scored based evaluation, 2, 5, 3);
filtering guideline?
classification guideline
report template

En caso de que ya esté subido tendría que hacer update….(?)

UPDATE nombredelatabla valoraactualizae SET valorquequierocambiar WHERE iddelpipelinequequieroactualizar

7. Relacionar Pipeline_File

De nuevo, cambiar el 3 por el ID que toca. los otros están bien así.


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