Mercurial > repos > brinkmanlab > microbedb

annotate builds.sql @ 0:40d14d5c8125 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 e63162a36457c0337c67e536a8a394beb06f4d70"
author brinkmanlab
date Fri, 30 Oct 2020 21:04:47 +0000
parents
children 258826db8291
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 DROP TABLE IF EXISTS builds_temp;
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
2 CREATE TEMP TABLE builds_temp(dbkey unique, label);
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
3 .mode csv
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
4 .separator ' '
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
5 .import $__app__.config.builds_file_path builds_temp
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
6 INSERT OR REPLACE INTO builds_temp (dbkey, label)
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
7 SELECT (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
8 (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
9 ']') AS label
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
10 FROM genomeproject
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
11 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
12 WHERE genomeproject.version_id = (SELECT MAX(version_id) FROM version)
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
13 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
14 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
15 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
16 .mode list
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
17 .separator ' '
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
18 .once $__app__.config.builds_file_path
40d14d5c8125 "planemo upload for repository https://github.com/brinkmanlab/galaxy-tools/tree/master/microbedb commit e63162a36457c0337c67e536a8a394beb06f4d70"
brinkmanlab
parents:
diff changeset
19 SELECT dbkey, label FROM builds_temp ORDER BY dbkey;