Mercurial > repos > brinkmanlab > microbedb

annotate all_fasta.sql @ 4:69186a3c9e76 draft

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
"planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit ada9e882d260555d79c64e38a8ce26b1e84966f0"
author brinkmanlab
date Fri, 06 Nov 2020 23:56:33 +0000
parents 40d14d5c8125
children b7884a377566
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
1 SELECT json_group_array(json_object('value', unique_build_id, 'dbkey', dbkey, 'name', label, 'path', file_path))
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
2 FROM (
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
3 SELECT (r.rep_accnum || '.' || r.rep_version) AS unique_build_id,
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
4 (r.rep_accnum || '_' || r.rep_version) AS dbkey,
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
5 (REPLACE(r.definition, ', complete genome.', '') || ' [' || r.rep_accnum || '.' || r.rep_version ||
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
6 ']') AS label,
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
7 (:basepath || '/' || genomeproject.gpv_directory || '/' || genomeproject.filename || '_genomic.fna') AS file_path
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
8 FROM genomeproject
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
9 JOIN replicon r ON genomeproject.gpv_id = r.gpv_id AND r.rep_type = 'chromosome'
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
10 WHERE genomeproject.version_id = (SELECT version_id FROM version WHERE is_current == 1)
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
11 AND genomeproject.file_types IS NOT NULL
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
12 AND genomeproject.file_types LIKE '%.fna%'
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
13 AND r.rep_type = 'chromosome'
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
14 )