Mercurial > repos > earlhaminst > gafa

annotate schema/gafa.sql @ 9:ab5611663f32 draft default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/main/tools/GAFA/ commit d96cd7ee9c686c185b6734aa08ab5a891cc44149-dirty
author earlhaminst
date Mon, 03 Mar 2025 17:47:53 +0000
parents c15476d4271c
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
6
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
1 CREATE TABLE meta (
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
2 version VARCHAR PRIMARY KEY NOT NULL);
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
3 CREATE TABLE gene (
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
4 gene_id VARCHAR PRIMARY KEY NOT NULL,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
5 gene_symbol VARCHAR,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
6 species VARCHAR NOT NULL,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
7 gene_json VARCHAR NOT NULL);
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
8 CREATE INDEX gene_symbol_index ON gene (gene_symbol);
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
9 CREATE TABLE transcript (
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
10 transcript_id VARCHAR PRIMARY KEY NOT NULL,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
11 protein_id VARCHAR UNIQUE,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
12 protein_sequence VARCHAR,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
13 gene_id VARCHAR NOT NULL REFERENCES gene(gene_id));
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
14 CREATE VIEW transcript_species as
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
15 SELECT transcript_id, species
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
16 FROM transcript JOIN gene
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
17 ON transcript.gene_id = gene.gene_id;
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
18 CREATE TABLE gene_family (
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
19 gene_family_id INTEGER PRIMARY KEY,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
20 gene_tree VARCHAR NOT NULL);
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
21 CREATE TABLE gene_family_member (
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
22 gene_family_id INTEGER NOT NULL REFERENCES gene_family(gene_family_id),
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
23 protein_id VARCHAR KEY NOT NULL REFERENCES transcript(protein_id),
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
24 protein_alignment VARCHAR NOT NULL,
c15476d4271c planemo upload for repository https://github.com/TGAC/earlham-galaxytools/tree/master/tools/GAFA/ commit af1fd308dde99cf40a9422c53dc680932be3765d
earlhaminst
parents:
diff changeset
25 PRIMARY KEY (gene_family_id, protein_id));