
-- ************************************************************************
-- This mysql code to generate "FlatFileDir/matrix_list.txt" files 
--  as provided in JASPAR 2010
-- 
-- 
-- (c) Xiaobei Zhao
-- 
-- Fri Jul 25 12:23:51 EDT 2014 -0400 (Week 29)
-- 
-- 
-- Reference: 
-- http://dev.mysql.com/doc/#manual
-- 
-- ************************************************************************

-- Prerequisites: you need to create the JASPAR database follow 
-- the instruction linked below,
-- http://jaspar.binf.ku.dk/html/DOWNLOAD/README

USE JASPAR_CURRENT;

SELECT 
CONCAT(BASE_ID,'.',VERSION) as BASE_VERSION, 
ICM, 
MATRIX.NAME, 
X.CLASS,
GROUP_CONCAT(CONCAT(U.TAG,' "',U.VAL,'"') SEPARATOR ';') as OTHER
FROM MATRIX,MATRIX_ICM,
(
SELECT MATRIX.ID, VAL as CLASS FROM MATRIX,MATRIX_ANNOTATION WHERE TAG='class' AND MATRIX.ID=MATRIX_ANNOTATION.ID AND MATRIX.ID
) AS X,
(
SELECT ID, 'collection' as TAG, COLLECTION as VAL FROM MATRIX 
UNION ALL 
SELECT ID,'acc' as TAG, ACC as VAL FROM MATRIX_PROTEIN
UNION ALL 
SELECT ID, 'species' as TAG, TAX_ID as VAL FROM MATRIX_SPECIES 
UNION ALL 
SELECT * FROM MATRIX_ANNOTATION
) 
AS U 
WHERE
MATRIX.ID=MATRIX_ICM.ID
AND 
MATRIX.ID=X.ID
AND 
MATRIX.ID=U.ID
AND
NOT U.TAG in ('alias','class','symbol') 
GROUP BY U.ID
;

