##### connectdots.ini - defines MapTables
## header defines maptable macro which autogenerates maptable names
[%- PROCESS $maptable_header -%]
[GLOBAL]
type=GDxBase::Pipeline::ConnectDots::LoadMapTable
[gene_entrez_information]
inputs=MainData/GeneInformation
idtypes=gene_entrez gene_symbol gene_description organism_name_common
query=<<QUERY
SELECT
GENE.locus_link_eid AS gene_entrez,
GENE.symbol AS gene_symbol,
GENE.description AS gene_description,
ORG.common_name AS organism_name_common
FROM
gene_information AS GENE
LEFT OUTER JOIN
organism AS ORG ON GENE.organism_id=ORG.organism_id
QUERY
[% maptable %]
inputs=MainData/GeneUnigene
idtypes=gene_entrez gene_unigene
query=<<QUERY
SELECT UG.locus_link_eid AS gene_entrez, UG.unigene_eid AS gene_unigene
FROM gene_unigene AS UG
QUERY
[% maptable %]
inputs=MainData/GeneSynonym
idtypes=gene_entrez gene_symbol_synonym
query=<<QUERY
SELECT SYN.locus_link_eid AS gene_entrez, SYN.synonym AS gene_symbol_synonym
FROM gene_synonym AS SYN
QUERY
[% maptable %]
inputs=MainData/OMIM/Gene
idtypes=gene_entrez omim
query=<<QUERY
SELECT OMIM.locus_link_eid AS gene_entrez, OMIM.omim_eid AS omim
FROM gene_omim AS OMIM
QUERY
[% maptable %]
inputs=MainData/OMIM/Title
idtypes=omim omim_description
query=<<QUERY
SELECT OMIM.omim_eid AS omim, OMIM.omim_title AS omim_description
FROM omim_title AS OMIM
QUERY
[% maptable %]
inputs=MainData/Gene_via_ECUniProt
idtypes=gene_entrez ec
query=<<QUERY
SELECT EC.locus_link_eid AS gene_entrez, EC.ec_eid AS ec
FROM ec_gene AS EC
QUERY
[% maptable %]
inputs=MainData/GeneRefseq
idtypes=gene_entrez transcript_refseq protein_refseq
query=<<QUERY
SELECT REF.locus_link_eid AS gene_entrez,
REF.transcript_eid AS transcript_refseq, REF.protein_eid AS protein_refseq
FROM gene_refseq AS REF
QUERY
[% maptable %]
inputs=MainData/GeneSequence
idtypes=gene_entrez transcript protein
query=<<QUERY
SELECT SEQ.locus_link_eid AS gene_entrez,
SEQ.transcript_eid AS transcript, SEQ.protein_eid AS protein
FROM gene_sequence AS SEQ
QUERY
[% maptable %]
inputs=MainData/GeneEnsembl
idtypes=gene_entrez gene_ensembl
query=<<QUERY
SELECT ENS.locus_link_eid AS gene_entrez, ENS.ensembl_eid AS gene_ensembl
FROM gene_ensembl AS ENS
QUERY
[% maptable %]
inputs=MainData/EnsgeneEnsseq
idtypes=gene_ensembl transcript_ensembl protein_ensembl
query=<<QUERY
SELECT ENS.ensembl_eid AS gene_ensembl,
ENS.transcript_eid AS transcript_ensembl, ENS.protein_eid AS protein_ensembl
FROM ensgene_ensseq AS ENS
QUERY
[% maptable %]
inputs=MainData/KnownGenes
idtypes=gene_entrez gene_known
query=<<QUERY
SELECT UKG.locus_link_eid AS gene_entrez, UKG.ucsc_known_eid AS gene_known
FROM ucscknown_gene AS UKG
QUERY
[% maptable %]
inputs=MainData/IPI/ipi_gene
idtypes=gene_entrez protein_ipi
query=<<QUERY
SELECT IPI.locus_link_eid AS gene_entrez, IPI.ipi_eid AS protein_ipi
FROM ipi_gene AS IPI
QUERY
[% maptable %]
inputs=MainData/IPI/ipi_information
idtypes=protein_ipi protein_description
query=<<QUERY
SELECT IPI.ipi_eid AS protein_ipi, IPI.description AS protein_description
FROM ipi_information AS IPI
QUERY
[% maptable %]
inputs=MainData/UniProt/Gene
idtypes=gene_entrez protein_uniprot
query=<<QUERY
SELECT UP.locus_link_eid AS gene_entrez, UP.uniprot_eid AS protein_uniprot
FROM uniprot_gene AS UP
QUERY
[% maptable %]
inputs=MainData/Pepatlas/gene_pepatlas
idtypes=gene_entrez peptide_pepatlas
query=<<QUERY
SELECT PEP.locus_link_eid AS gene_entrez, PEP.pepatlas_eid AS peptide_pepatlas
FROM gene_pepatlas AS PEP
QUERY
[% maptable %]
inputs=MainData/Affy
idtypes=gene_entrez chip_affy probe_affy sequence_affy
query=<<QUERY
SELECT AFFY.locus_link_eid AS gene_entrez,
AFFY.chip_id as chip_affy, AFFY.probe_id as probe_affy, AFFY.sequence_id AS sequence_affy
FROM affy_probe AS AFFY
QUERY
[% maptable %]
inputs=MainData/Illumina
idtypes=gene_entrez chip_lumi probe_lumi probe_nu sequence_nu
query=<<QUERY
SELECT
NU.locus_link_eid AS gene_entrez,
LUMI.lumi_chip_id as chip_lumi, LUMI.lumi_probe_id as probe_lumi,
NU.nu_eid as probe_nu, NU.sequence_id as sequence_nu
FROM
nu_gene AS NU
LEFT OUTER JOIN
illumina_probe AS LUMI ON LUMI.nu_eid = NU.nu_eid
QUERY