##### 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