LEAD Database Schema with Views

GO Database Schema Autodoc

This is the GO LEAD Database Schema documentation, in Autodoc format. Everything on this page was automatically generated from the schema metadata. For more background on the schema, please read GO Database documentation.

note on modules: The tables are partitioned into modules for the purposes of Autodoc. These modules are invisible in the actual instantiations of the database, so the table "go_graph.term" is just simply "term".

This documentation was originally generated on 2009-07-20.

Schema go_annotation_reports

View: go_annotation_reports.annotated_publication_total

ungrouped: single number

go_annotation_reports.annotated_publication_total Structure
F-Key Name Type Description
total bigint
  SELECT count
  (DISTINCT evidence.dbxref_id) AS total 
  FROM go_associations.evidence;

Index - Schema go_annotation_reports

View: go_annotation_reports.annotated_publication_total_by_evidence_code_non_additive

non-additive: summing the totals does not give the total number of publications; this is because a publication may describe two or more distinct pieces of evidence

go_annotation_reports.annotated_publication_total_by_evidence_code_non_additive Structure
F-Key Name Type Description
code character varying(8)
total bigint
  SELECT evidence.code
  , count
  (DISTINCT evidence.dbxref_id) AS total 
  FROM go_associations.evidence 
  GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.annotated_total_gps_by_evidence_code_non_additive

non-additive: summing the totals does not give the total number of gene products; this is because they are double counted through distinct annotations using different codes

go_annotation_reports.annotated_total_gps_by_evidence_code_non_additive Structure
F-Key Name Type Description
code character varying(8)
total bigint
  SELECT evidence.code
  , count
  (DISTINCT association.gene_product_id) AS total 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction

go_annotation_reports.association_contradiction Structure
F-Key Name Type Description
term_pos_id integer
term_pos_acc character varying(255)
term_pos_name character varying(255)
term_pos_term_type character varying(55)
term_neg_id integer
term_neg_acc character varying(255)
term_neg_name character varying(255)
term_neg_term_type character varying(55)
a_pos_id integer
gp_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
a_neg_id integer
  SELECT term_pos.id AS term_pos_id
  , term_pos.acc AS term_pos_acc
  , term_pos.name AS term_pos_name
  , term_pos.term_type AS term_pos_term_type
  , term_neg.id AS term_neg_id
  , term_neg.acc AS term_neg_acc
  , term_neg.name AS term_neg_name
  , term_neg.term_type AS term_neg_term_type
  , a_pos.id AS a_pos_id
  , gp.id AS gp_id
  , gp.symbol AS gp_symbol
  , gp.dbxref_id AS gp_dbxref_id
  , gp.species_id AS gp_species_id
  , a_neg.id AS a_neg_id 
  FROM (
  (
  (
  (
  (go_graph.term term_pos 
  JOIN go_associations.association a_pos 
  ON (
  (term_pos.id = a_pos.term_id)
  )
  )
  JOIN go_optimisations.graph_path 
  ON (
  (term_pos.id = graph_path.term2_id)
  )
  )
  JOIN go_associations.association a_neg 
  ON (
  (graph_path.term1_id = a_neg.term_id)
  )
  )
  JOIN go_associations.gene_product gp 
  ON (
  (
  (a_neg.gene_product_id = gp.id)
  AND (a_pos.gene_product_id = gp.id)
  )
  )
  )
  JOIN go_graph.term term_neg 
  ON (
  (term_neg.id = a_neg.term_id)
  )
  )
  WHERE (
  (a_pos.is_not = 0)
  AND (a_neg.is_not = 1)
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_count_by_ontology

summary of association_contradiction, grouped by ontology

go_annotation_reports.association_contradiction_count_by_ontology Structure
F-Key Name Type Description
term_pos_term_type character varying(55)
count bigint
  SELECT association_contradiction.term_pos_term_type
  , count
  (DISTINCT association_contradiction.gp_id) AS count 
  FROM go_annotation_reports.association_contradiction 
  GROUP BY association_contradiction.term_pos_term_type;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_direct

APPARENT contradictions in associations, based on the NOT column. note that these do not genuinely contradict as annotation is context-specific

go_annotation_reports.association_contradiction_direct Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
a_pos_id integer
gp_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
a_neg_id integer
  SELECT term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation
  , a_pos.id AS a_pos_id
  , gp.id AS gp_id
  , gp.symbol AS gp_symbol
  , gp.dbxref_id AS gp_dbxref_id
  , gp.species_id AS gp_species_id
  , a_neg.id AS a_neg_id 
  FROM (
  (
  (go_graph.term 
  JOIN go_associations.association a_pos 
  ON (
  (term.id = a_pos.term_id)
  )
  )
  JOIN go_associations.gene_product gp 
  ON (
  (a_pos.gene_product_id = gp.id)
  )
  )
  JOIN go_associations.association a_neg 
  ON (
  (
  (term.id = a_neg.term_id)
  AND (a_neg.gene_product_id = gp.id)
  )
  )
  )
  WHERE (
  (a_pos.is_not = 0)
  AND (a_neg.is_not = 1)
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_direct_count_by_ontology

go_annotation_reports.association_contradiction_direct_count_by_ontology Structure
F-Key Name Type Description
term_type character varying(55)
count bigint
  SELECT association_contradiction_direct.term_type
  , count
  (DISTINCT association_contradiction_direct.gp_id) AS count 
  FROM go_annotation_reports.association_contradiction_direct 
  GROUP BY association_contradiction_direct.term_type;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_count_by_association_qualifier

go_annotation_reports.association_count_by_association_qualifier Structure
F-Key Name Type Description
qualifier character varying(255)
n_associations bigint
  SELECT qterm.acc AS qualifier
  , count
  (DISTINCT aq.association_id) AS n_associations 
  FROM (go_associations.association_qualifier aq 
  JOIN go_graph.term qterm 
  ON (
  (aq.term_id = qterm.id)
  )
  )
  GROUP BY qterm.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_count_by_association_qualifier_and_dbname

go_annotation_reports.association_count_by_association_qualifier_and_dbname Structure
F-Key Name Type Description
xref_dbname character varying(55)
qualifier character varying(255)
n_associations bigint
  SELECT x.xref_dbname
  , qterm.acc AS qualifier
  , count
  (DISTINCT aq.association_id) AS n_associations 
  FROM (
  (
  (
  (go_associations.association_qualifier aq 
  JOIN go_graph.term qterm 
  ON (
  (aq.term_id = qterm.id)
  )
  )
  JOIN go_associations.association a 
  ON (
  (aq.association_id = a.id)
  )
  )
  JOIN go_associations.gene_product gp 
  ON (
  (a.gene_product_id = gp.id)
  )
  )
  JOIN go_general.dbxref x 
  ON (
  (gp.dbxref_id = x.id)
  )
  )
  GROUP BY qterm.acc
  , x.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_total_by_evidence_code

this total IS additive

go_annotation_reports.association_total_by_evidence_code Structure
F-Key Name Type Description
code character varying(8)
total bigint
  SELECT evidence.code
  , count
  (DISTINCT evidence.association_id) AS total 
  FROM go_associations.evidence 
  GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_total_by_evidence_code_and_species

go_annotation_reports.association_total_by_evidence_code_and_species Structure
F-Key Name Type Description
ncbi_taxa_id integer
genus character varying(55)
species character varying(255)
common_name character varying(255)
code character varying(8)
total_associations bigint
  SELECT species.ncbi_taxa_id
  , species.genus
  , species.species
  , species.common_name
  , evidence.code
  , count
  (DISTINCT evidence.association_id) AS total_associations 
  FROM (
  (
  (go_associations.evidence 
  JOIN go_associations.association 
  ON (
  (evidence.association_id = association.id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gene_product.species_id = species.id)
  )
  )
  GROUP BY species.ncbi_taxa_id
  , species.genus
  , species.species
  , species.common_name
  , evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_annotations_per_gp_by_db

go_annotation_reports.avg_total_annotations_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_annotations numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_annotations) AS avg_total_annotations 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_annotations_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_annotations_per_gp_by_db

go_annotation_reports.avg_total_nonroot_annotations_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_annotations numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_annotations) AS avg_total_annotations 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_nonroot_annotations_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_pubs_per_gp_by_db

go_annotation_reports.avg_total_nonroot_pubs_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_pubs numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_pubs) AS avg_total_pubs 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_nonroot_pubs_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_terms_per_gp_by_db

as avg_total_terms_per_gp_by_db, excluding direct annotations to root

go_annotation_reports.avg_total_nonroot_terms_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_terms numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_terms) AS avg_total_terms 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_nonroot_terms_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_transitive_terms_per_gp

go_annotation_reports.avg_total_nonroot_transitive_terms_per_gp Structure
F-Key Name Type Description
avg_total_transitive_terms numeric
  SELECT avg
  (total_nonroot_transitive_terms_per_gp.total_transitive_terms) AS avg_total_transitive_terms 
  FROM go_annotation_reports.total_nonroot_transitive_terms_per_gp;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_transitive_terms_per_gp_by_db

go_annotation_reports.avg_total_nonroot_transitive_terms_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_transitive_terms numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_transitive_terms) AS avg_total_transitive_terms 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_nonroot_transitive_terms_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_pubs_per_gp_by_db

go_annotation_reports.avg_total_pubs_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_pubs numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_pubs) AS avg_total_pubs 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_pubs_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_terms_per_gp_by_db

average term coverage broken down by annotation DB here, term coverage is the DIRECT term count for that gene product see also: avg_total_transitive_terms_per_gp_by_db

go_annotation_reports.avg_total_terms_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_terms numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_terms) AS avg_total_terms 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_terms_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_transitive_terms_per_gp

go_annotation_reports.avg_total_transitive_terms_per_gp Structure
F-Key Name Type Description
avg_total_transitive_terms numeric
  SELECT avg
  (total_transitive_terms_per_gp.total_transitive_terms) AS avg_total_transitive_terms 
  FROM go_annotation_reports.total_transitive_terms_per_gp;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_transitive_terms_per_gp_by_db

go_annotation_reports.avg_total_transitive_terms_per_gp_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_total_transitive_terms numeric
  SELECT dbxref.xref_dbname
  , avg
  (aa.total_transitive_terms) AS avg_total_transitive_terms 
  FROM (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_annotation_reports.total_transitive_terms_per_gp aa 
  ON (
  (aa.gene_product_id = gene_product.id)
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.evidence_dbxref_summary

what kind of dbxrefs links to evidences on with WITH field: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

go_annotation_reports.evidence_dbxref_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
num_evidence_dbxrefs bigint
  SELECT d.xref_dbname
  , count
  (*) AS num_evidence_dbxrefs 
  FROM (go_associations.evidence_dbxref x 
  JOIN go_general.dbxref d 
  ON (
  (x.dbxref_id = d.id)
  )
  )
  GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.evidence_pub_dbxref_summary

what kind of dbxrefs links to evidence as the primary publication: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

go_annotation_reports.evidence_pub_dbxref_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
num_evidence_dbxrefs bigint
  SELECT d.xref_dbname
  , count
  (*) AS num_evidence_dbxrefs 
  FROM (go_associations.evidence x 
  JOIN go_general.dbxref d 
  ON (
  (x.dbxref_id = d.id)
  )
  )
  GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.gene_product_dbxref_summary

what kind of dbxrefs links to gene_products: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

go_annotation_reports.gene_product_dbxref_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
num_gene_product_dbxrefs bigint
  SELECT d.xref_dbname
  , count
  (*) AS num_gene_product_dbxrefs 
  FROM (go_associations.gene_product x 
  JOIN go_general.dbxref d 
  ON (
  (x.dbxref_id = d.id)
  )
  )
  GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.iea_annotated_total_gps

ungrouped: single number

go_annotation_reports.iea_annotated_total_gps Structure
F-Key Name Type Description
total bigint
  SELECT count
  (DISTINCT association.gene_product_id) AS total 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  WHERE (
  (evidence.code)::text = 'IEA'::text
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.iea_or_iss_annotated_total_gps

ungrouped: single number

go_annotation_reports.iea_or_iss_annotated_total_gps Structure
F-Key Name Type Description
total bigint
  SELECT count
  (DISTINCT association.gene_product_id) AS total 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  WHERE (
  (
  (evidence.code)::text = 'IEA'::text
  )
  OR (
  (evidence.code)::text = 'ISS'::text
  )
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_annotated_total_gps

ungrouped: single number

go_annotation_reports.non_iea_annotated_total_gps Structure
F-Key Name Type Description
total bigint
  SELECT count
  (DISTINCT association.gene_product_id) AS total 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  WHERE (
  (evidence.code)::text <> 'IEA'::text
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_annotated_total_gps_by_dbname

slow in mysql5.0

go_annotation_reports.non_iea_annotated_total_gps_by_dbname Structure
F-Key Name Type Description
xref_dbname character varying(55)
total bigint
  SELECT dbxref.xref_dbname
  , count
  (DISTINCT gene_product.id) AS total 
  FROM (
  (
  (go_associations.gene_product 
  JOIN go_general.dbxref 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  WHERE (
  (evidence.code)::text <> 'IEA'::text
  )
  GROUP BY dbxref.xref_dbname 
  ORDER BY count
  (DISTINCT gene_product.id);

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_or_iss_annotated_total_gps

ungrouped: single number

go_annotation_reports.non_iea_or_iss_annotated_total_gps Structure
F-Key Name Type Description
total bigint
  SELECT count
  (DISTINCT association.gene_product_id) AS total 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = association.id)
  )
  )
  WHERE (
  (
  (evidence.code)::text <> 'IEA'::text
  )
  AND (
  (evidence.code)::text <> 'ISS'::text
  )
  );

Index - Schema go_annotation_reports

View: go_annotation_reports.ont_association_count_by_association_qualifier

go_annotation_reports.ont_association_count_by_association_qualifier Structure
F-Key Name Type Description
term_type character varying(55)
qualifier character varying(255)
n_associations bigint
  SELECT term.term_type
  , qterm.acc AS qualifier
  , count
  (DISTINCT aq.association_id) AS n_associations 
  FROM (
  (
  (go_associations.association_qualifier aq 
  JOIN go_graph.term qterm 
  ON (
  (aq.term_id = qterm.id)
  )
  )
  JOIN go_associations.association 
  ON (
  (aq.association_id = association.id)
  )
  )
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  GROUP BY term.term_type
  , qterm.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.seq_dbxref_summary

what kind of dbxrefs links to seqs: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

go_annotation_reports.seq_dbxref_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
num_seq_dbxrefs bigint
  SELECT d.xref_dbname
  , count
  (*) AS num_seq_dbxrefs 
  FROM (go_sequence.seq_dbxref x 
  JOIN go_general.dbxref d 
  ON (
  (x.dbxref_id = d.id)
  )
  )
  GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_association_count_by_association_qualifier

go_annotation_reports.term_association_count_by_association_qualifier Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
term_type character varying(55)
qualifier character varying(255)
n_associations bigint
  SELECT term.acc
  , term.name
  , term.term_type
  , qterm.acc AS qualifier
  , count
  (DISTINCT aq.association_id) AS n_associations 
  FROM (
  (
  (go_associations.association_qualifier aq 
  JOIN go_graph.term qterm 
  ON (
  (aq.term_id = qterm.id)
  )
  )
  JOIN go_associations.association 
  ON (
  (aq.association_id = association.id)
  )
  )
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  GROUP BY term.acc
  , term.name
  , term.term_type
  , qterm.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_association_count_by_fraction_type

go_annotation_reports.term_association_count_by_fraction_type Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
term_type character varying(55)
n_genes bigint
  SELECT term.acc
  , term.name
  , term.term_type
  , count
  (DISTINCT association.gene_product_id) AS n_genes 
  FROM (go_associations.association 
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  WHERE (term.id IN 
  (
  SELECT graph_path.term2_id 
  FROM (go_optimisations.graph_path 
  JOIN go_graph.term p 
  ON (
  (p.id = graph_path.term1_id)
  )
  )
  WHERE (
  (p.acc)::text = 'GO:0000267'::text
  )
  )
  )
  GROUP BY term.acc
  , term.name
  , term.term_type;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_association_count_by_fraction_type_and_evidence

go_annotation_reports.term_association_count_by_fraction_type_and_evidence Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
term_type character varying(55)
code character varying(8)
n_genes bigint
  SELECT term.acc
  , term.name
  , term.term_type
  , e.code
  , count
  (DISTINCT association.gene_product_id) AS n_genes 
  FROM (
  (go_associations.association 
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  JOIN go_associations.evidence e 
  ON (
  (association.id = e.association_id)
  )
  )
  WHERE (term.id IN 
  (
  SELECT graph_path.term2_id 
  FROM (go_optimisations.graph_path 
  JOIN go_graph.term p 
  ON (
  (p.id = graph_path.term1_id)
  )
  )
  WHERE (
  (p.acc)::text = 'GO:0000267'::text
  )
  )
  )
  GROUP BY term.acc
  , term.name
  , term.term_type
  , e.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_dbxref_summary

what kind of dbxrefs links to terms: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

go_annotation_reports.term_dbxref_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
num_term_dbxrefs bigint
  SELECT d.xref_dbname
  , count
  (*) AS num_term_dbxrefs 
  FROM (go_meta.term_dbxref x 
  JOIN go_general.dbxref d 
  ON (
  (x.dbxref_id = d.id)
  )
  )
  GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_annotated_entities_by_dbname_and_type

go_annotation_reports.total_annotated_entities_by_dbname_and_type Structure
F-Key Name Type Description
xref_dbname character varying(55)
acc character varying(255)
total_gps bigint
  SELECT dbxref.xref_dbname
  , tt.acc
  , count
  (*) AS total_gps 
  FROM (
  (go_associations.gene_product 
  JOIN go_general.dbxref 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_graph.term tt 
  ON (
  (tt.id = gene_product.type_id)
  )
  )
  GROUP BY dbxref.xref_dbname
  , tt.acc 
  ORDER BY dbxref.xref_dbname
  , tt.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_annotations_per_gp

count of distinct direct annotations broken down by gene product note: does not correspnd to lines in gene_association file

go_annotation_reports.total_annotations_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_annotations bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT association.id) AS total_annotations 
  FROM go_associations.association 
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_gps_by_dbname

total number of GPs in the database instance grouped by contributing database (eg FlyBase, UniProt, ..)

go_annotation_reports.total_gps_by_dbname Structure
F-Key Name Type Description
xref_dbname character varying(55)
total_gps bigint
  SELECT dbxref.xref_dbname
  , count
  (*) AS total_gps 
  FROM (go_associations.gene_product 
  JOIN go_general.dbxref 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  GROUP BY dbxref.xref_dbname 
  ORDER BY count
  (*);

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_annotations_per_gp

as total_annotations_per_gp, excluding direct annotations to root

go_annotation_reports.total_nonroot_annotations_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_annotations bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT association.id) AS total_annotations 
  FROM go_associations.association 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT root_term.id
  , root_term.name
  , root_term.term_type
  , root_term.acc
  , root_term.is_obsolete
  , root_term.is_root
  , root_term.is_relation 
  FROM go_graph_views.root_term 
  WHERE (root_term.id = association.term_id)
  )
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_pubs_per_gp

go_annotation_reports.total_nonroot_pubs_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_pubs bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT evidence.dbxref_id) AS total_pubs 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (association.id = evidence.association_id)
  )
  )
  WHERE (NOT 
  (EXISTS 
  (
  SELECT root_term.id
  , root_term.name
  , root_term.term_type
  , root_term.acc
  , root_term.is_obsolete
  , root_term.is_root
  , root_term.is_relation 
  FROM go_graph_views.root_term 
  WHERE (root_term.id = association.term_id)
  )
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_terms_per_gp

go_annotation_reports.total_nonroot_terms_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_terms bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT association.term_id) AS total_terms 
  FROM go_associations.association 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT root_term.id
  , root_term.name
  , root_term.term_type
  , root_term.acc
  , root_term.is_obsolete
  , root_term.is_root
  , root_term.is_relation 
  FROM go_graph_views.root_term 
  WHERE (root_term.id = association.term_id)
  )
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_transitive_terms_per_gp

go_annotation_reports.total_nonroot_transitive_terms_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_transitive_terms bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT graph_path.term1_id) AS total_transitive_terms 
  FROM (go_associations.association 
  JOIN go_optimisations.graph_path 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  WHERE (NOT 
  (EXISTS 
  (
  SELECT root_term.id
  , root_term.name
  , root_term.term_type
  , root_term.acc
  , root_term.is_obsolete
  , root_term.is_root
  , root_term.is_relation 
  FROM go_graph_views.root_term 
  WHERE (root_term.id = association.term_id)
  )
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_transitive_terms_per_gp_pair

go_annotation_reports.total_nonroot_transitive_terms_per_gp_pair Structure
F-Key Name Type Description
gp1_id integer
gp2_id integer
total_transitive_terms bigint
  SELECT a1.gene_product_id AS gp1_id
  , a2.gene_product_id AS gp2_id
  , count
  (DISTINCT tc1.term1_id) AS total_transitive_terms 
  FROM go_associations.association a1
  , go_optimisations.graph_path tc1
  , go_optimisations.graph_path tc2
  , go_associations.association a2 
  WHERE (
  (
  (
  (tc1.term2_id = a1.term_id)
  AND (tc2.term2_id = a2.term_id)
  )
  AND (tc1.term1_id = tc2.term1_id)
  )
  AND (NOT 
  (EXISTS 
  (
  SELECT root_term.id
  , root_term.name
  , root_term.term_type
  , root_term.acc
  , root_term.is_obsolete
  , root_term.is_root
  , root_term.is_relation 
  FROM go_graph_views.root_term 
  WHERE (
  (root_term.id = a1.term_id)
  OR (root_term.id = a1.term_id)
  )
  )
  )
  )
  )
  GROUP BY a1.gene_product_id
  , a2.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_pubs

go_annotation_reports.total_pubs Structure
F-Key Name Type Description
total_pubs bigint
  SELECT count
  (DISTINCT evidence.dbxref_id) AS total_pubs 
  FROM go_associations.evidence;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_pubs_per_gp

go_annotation_reports.total_pubs_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_pubs bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT evidence.dbxref_id) AS total_pubs 
  FROM (go_associations.association 
  JOIN go_associations.evidence 
  ON (
  (association.id = evidence.association_id)
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_terms_per_gp

go_annotation_reports.total_terms_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_terms bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT association.term_id) AS total_terms 
  FROM go_associations.association 
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_transitive_terms_per_gp

go_annotation_reports.total_transitive_terms_per_gp Structure
F-Key Name Type Description
gene_product_id integer
total_transitive_terms bigint
  SELECT association.gene_product_id
  , count
  (DISTINCT graph_path.term1_id) AS total_transitive_terms 
  FROM (go_associations.association 
  JOIN go_optimisations.graph_path 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_transitive_terms_per_gp_ont

total number of terms this gp is annotated with, INCLUDING transitive annotations i.e. all terms above this gp in DAG too many rows to materialize?

go_annotation_reports.total_transitive_terms_per_gp_ont Structure
F-Key Name Type Description
term_type character varying(55)
gene_product_id integer
total_transitive_terms bigint
  SELECT term.term_type
  , association.gene_product_id
  , count
  (DISTINCT graph_path.term1_id) AS total_transitive_terms 
  FROM (
  (go_associations.association 
  JOIN go_optimisations.graph_path 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_graph.term 
  ON (
  (term.id = graph_path.term1_id)
  )
  )
  GROUP BY term.term_type
  , association.gene_product_id;

Index - Schema go_annotation_reports

Schema go_associations

Table: go_associations.association

Annotation model: An association is a link between a gene product record and an ontology term, with one or more pieces of evidence *** IMPORTANT: NOT all associations are positive: some posit negative links. THESE SHOULD TYPICALLY BE FILTERED OUT FOR MOST ANALYSIS PURPOSES. See the is_not column ***

go_associations.association Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_graph.term.id term_id integer NOT NULL the (GO) term to which the gene_product is associated
go_associations.gene_product.id gene_product_id integer NOT NULL the gene or gene_product to which the term is associated
is_not integer ** IMPORTANT ** when this field is non-zero, the meaning of the annotation is that the gene_product does NOT have the role defined by the GO term (column 4 = NOT in the gene-association file) See also: association_qualifier table
assocdate integer a date in YYYYMMDD format. This is the date the association was last checked the source db providers (column 14 in the gene-association file)
go_general.db.id source_db_id integer the source of the association; for instance, the association file may come from SwissProt, but the source of the association (Example: SGD) (Example: MGI) (column 15 = NOT in the gene-association file) (docs: http://www.geneontology.org/cgi-bin/xrefs.cgi)

Tables referencing this one via Foreign Key Constraints:

a1 term_id a2 gene_product_id a3 term_id, gene_product_id a4 id, term_id, gene_product_id a5 id, gene_product_id a6 is_not, term_id, gene_product_id

Index - Schema go_associations

Table: go_associations.association_property

(column 16 in the gene-association file) (see http://wiki.geneontology.org/index.php/Annotation_Cross_Products)

go_associations.association_property Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_associations.association.id association_id integer NOT NULL
go_graph.term.id relationship_type_id integer NOT NULL e.g. the term.id that has acc='part_of'
go_graph.term.id term_id integer NOT NULL e.g. the term.id that has acc='CL:0000017'

Index - Schema go_associations

Table: go_associations.association_qualifier

associations can have a number of qualifiers. These include, but are not limited to the NOT qualifier (which technically is not a qualifier at all as it fundamentally changes the semantics of an association) note that this table IS redundant with association.is_not this is intentional - negation is important enough to go directly in the association table. It also goes in this table for consistency with the gene association file (column 4 in the gene-association file) (docs: http://www.geneontology.org/GO.format.annotation.shtml)

go_associations.association_qualifier Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_associations.association.id association_id integer NOT NULL
go_graph.term.id term_id integer NOT NULL qualifiers come from their own terminology
value character varying(255) qualifiers can potentialy be tag=value pairs. however, all qualifiers are currently boolean tags, so this column is always null
aq1 association_id, term_id

Index - Schema go_associations

Table: go_associations.association_species_qualifier

(see http://www.geneontology.org/GO.annotation.shtml#manySpp) (column 13 in gene_association file, ONLY WHEN card>1, this is the next entry) to be used only in conjunction with terms that have the term 'interaction between organisms' as an ancestor. gene_product.species_id is for the organism type encoding the gene or gene product, association_species_qualifier.species_id should be that of the other organism in the interaction. aka "dual taxon"

go_associations.association_species_qualifier Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_associations.association.id association_id integer NOT NULL
go_associations.species.id species_id integer The species of the interacting organism (eg host) Example: in cytolysis of cells of another organism (GO:0051715) this would be the species playing the 'other organism' role

Index - Schema go_associations

Table: go_associations.evidence

each association can have one or more pieces of evidence attached to it (the schema actually allows zero or more, but with GO all annotation have at least one piece of evidence) (doc: http://www.geneontology.org/GO.evidence.shtml)

go_associations.evidence Structure
F-Key Name Type Description
id serial PRIMARY KEY
code character varying(8) UNIQUE#1 NOT NULL a string code (typically 3-letter) corresponding to a GO evidence code. (column 7 in the gene-association file) (Example: IEA - inferred from electronic annotation) (Example: IMP - inferred from mutant phenotype) evidence codes may eventually become "ontologized", allowing us to take full advantage of the OBO evidence ontology: http://www.obofoundry.org/cgi-bin/detail.cgi?evidence_code
go_associations.association.id association_id integer UNIQUE#1 NOT NULL
go_general.dbxref.id dbxref_id integer UNIQUE#1 NOT NULL A reference for the annotation. Typically a pubmed ID (column 6 in the gene-association file)
seq_acc character varying(255) a denormalised field containing a "|" separated list of accession supporting the call. for the normalised data, use evidence_dbxref (column 8 in the gene-association file, copied identically)

Tables referencing this one via Foreign Key Constraints:

ev1 association_id ev2 code ev3 dbxref_id ev4 association_id, code

Index - Schema go_associations

Table: go_associations.evidence_dbxref

each piece of evidence can have multiple dbxrefs associated with it; this is the *normalised* version of the "With" or "From" field of the evidence (column 8 in the gene-association file, normalized)

go_associations.evidence_dbxref Structure
F-Key Name Type Description
go_associations.evidence.id evidence_id integer NOT NULL
go_general.dbxref.id dbxref_id integer NOT NULL globally unique identifier for the evidence (Example: GO:0000346 - will be stored as DB=GO, Acc=0000346)
evx1 evidence_id evx2 dbxref_id evx3 evidence_id, dbxref_id

Index - Schema go_associations

Table: go_associations.gene_product

Represents a gene or gene_product, typically at the species level. GO allows for annotation of genes OR gene products. Annotation of a gene is understood to be "proxy" for annotation of the corresponding gene products. (docs: http://www.geneontology.org/GO.annotation.fields.shtml) (docs: http://www.geneontology.org/GO.annotation.shtml#file) (Example: human p53-gene) (Example: human p53-protein)

go_associations.gene_product Structure
F-Key Name Type Description
id serial PRIMARY KEY
symbol character varying(128) NOT NULL concise label for this gene product (Example: p53) (Example: BRCA) (Example: PHO3) typically unique within an originating database authority, but not guaranteed; (an example of such as authority is FlyBase or UniProt)
go_general.dbxref.id dbxref_id integer UNIQUE NOT NULL A globally unique identifier for this gene or gene product. All (non-GO) unique identifiers are stored as dbxrefs - they must consist of both a DB and a DB_Object_ID (Example: SGD:S000000296) (column 1 and 2 in gene_association file)
go_associations.species.id species_id integer The species or taxon to which this gene product belongs (Note: in future we reserve the option to use gene_product to represent families at higher levels in the taxonomic tree above species) (column 13 in gene_association file; if card>1, this is the first entry)
go_graph.term.id type_id integer gene_product type (eg gene, transcript, protein, complex) (column 13 in the gene-association file) the type term may correspond to a SO ID, but typically SO is not loaded an an ontology into the GO database
full_name text symbol is typically a concise label, full_name may be more textual (column 10 in the gene-association file)

Tables referencing this one via Foreign Key Constraints:

g1 symbol g2 dbxref_id g3 species_id g4 id, species_id g5 dbxref_id, species_id g6 id, dbxref_id g7 id, species_id g8 id, dbxref_id, species_id

Index - Schema go_associations

Table: go_associations.gene_product_subset

(aka goslims). Each subsetdef (slim) is stored as a gene_product in the database (with term_type = 'subset') The subset_id links to this term (OBO-Format: *subset* tag. term_id references a term housing the *subsetdef*)

go_associations.gene_product_subset Structure
F-Key Name Type Description
go_associations.gene_product.id gene_product_id integer NOT NULL
go_graph.term.id subset_id integer NOT NULL
gps1 gene_product_id gps2 subset_id

Index - Schema go_associations

Table: go_associations.gene_product_synonym

alternate label for the gene or gene product (column 11 in the gene-association file)

go_associations.gene_product_synonym Structure
F-Key Name Type Description
go_associations.gene_product.id gene_product_id integer UNIQUE#1 NOT NULL
product_synonym character varying(255) UNIQUE#1 NOT NULL alternate label. Typically NOT redundant with gene_product.symbol or gene_product.full_name, but this is not guaranteed (column 11 in the gene-association file)
gs1 gene_product_id gs2 product_synonym

Index - Schema go_associations

Table: go_associations.species

Linnaean taxonomic information for an organism type. Modeled after NCBI Taxonomy (Note: the name of the table is misleading, as it can model ANY node in Linnaen taxonomy) (The table should be better called "taxon")

go_associations.species Structure
F-Key Name Type Description
id serial PRIMARY KEY
ncbi_taxa_id integer UNIQUE identifier within the NCBI Taxonomy database. (Example: Dmel=7227) (Example: S Cerevisae=4932)
common_name character varying(255) Non-scientific name (Example: fruitfly)
lineage_string text denormalized list of taxon names as text. (Note: not currently populated)
genus character varying(55) If the row in the table is genuinely a species, this column is for storing the "genus" in the Linnaean system. If the row is a higher taxon, then this column is for the scientific name of that taxon. (Example: Drosophila -- for leaf node taxon) (Example: Homo -- for leaf node taxon) (Example: Metazoa -- for non-leaf node taxon) unfortunately the name of this column is misleading. However, it will be retained for backwards compatibility
species character varying(255) If the row in the table is genuinely a species, this column is for storing the "species" name in the Linnaean system. If the row is a higher taxon, this column is null (Example: sapiens) (Example: pombe) unfortunately the name of this column is misleading. However, it will be retained for backwards compatibility note that (genus,species) is not declared unique
parent_id integer parent taxon in hierarchy (direct parent - for indirect parents see left_value and right_value)
left_value integer left_value, right_value implement a nested set model see http://www.oreillynet.com/pub/a/network/2002/11/27/bioconf.html or Joe Celko's "SQL for smarties" for more information.
right_value integer see left_value
taxonomic_rank character varying(255) eg species, family, phylum, ...

Tables referencing this one via Foreign Key Constraints:

sp1 ncbi_taxa_id sp10 right_value sp11 left_value, right_value sp12 id, left_value sp13 genus, left_value, right_value sp2 common_name sp3 genus sp4 species sp5 genus, species sp6 id, ncbi_taxa_id sp7 id, ncbi_taxa_id, genus, species sp8 parent_id sp9 left_value

Index - Schema go_associations

Schema go_audit

Table: go_audit.instance_data

metadata on this particular instance/build of the GO database

go_audit.instance_data Structure
F-Key Name Type Description
release_name character varying(255) UNIQUE Typically named by date/version in YYYY-MM-DD format
release_type character varying(255) One of: termdb (ontology only) assocdb (termdb + associations) seqdb (assocdb + sequences) seqdblite (seqdb - IEAs)
release_notes text notes specific to this release. Will typically be null unless this release is unusual in some way
ontology_data_version character varying(255) data-version tag from the header of the obo file (not yet implemented)

Index - Schema go_audit

Table: go_audit.source_audit

time of last modification of data source (usually type:file) source_path is a file path or name

go_audit.source_audit Structure
F-Key Name Type Description
source_id character varying(255)
source_fullpath character varying(255)
source_path character varying(255)
source_type character varying(255)
source_md5 character(32)
source_parsetime integer
source_mtime integer
fa1 source_path

Index - Schema go_audit

Table: go_audit.term_audit

not in use

go_audit.term_audit Structure
F-Key Name Type Description
go_graph.term.id term_id integer UNIQUE NOT NULL
term_loadtime integer
ta1 term_id

Index - Schema go_audit

Schema go_evidence_views

View: go_evidence_views.association_evidence_with

go_evidence_views.association_evidence_with Structure
F-Key Name Type Description
association_id integer
code character varying(8)
with_dbname character varying(55)
with_acc character varying(255)
pub_dbname character varying(55)
pub_acc character varying(255)
  SELECT DISTINCT evidence.association_id
  , evidence.code
  , ex.xref_dbname AS with_dbname
  , ex.xref_key AS with_acc
  , px.xref_dbname AS pub_dbname
  , px.xref_key AS pub_acc 
  FROM (
  (
  (go_associations.evidence 
  JOIN go_associations.evidence_dbxref 
  ON (
  (evidence.id = evidence_dbxref.evidence_id)
  )
  )
  JOIN go_general.dbxref ex 
  ON (
  (evidence_dbxref.dbxref_id = ex.id)
  )
  )
  JOIN go_general.dbxref px 
  ON (
  (evidence.dbxref_id = px.id)
  )
  );

Index - Schema go_evidence_views

View: go_evidence_views.association_inference_candidate_pair

go_evidence_views.association_inference_candidate_pair Structure
F-Key Name Type Description
gp_src_dbname character varying(55)
gp_src_key character varying(255)
gp_src_id integer
gp_src_symbol character varying(128)
gp_der_dbname character varying(55)
gp_der_key character varying(255)
gp_der_id integer
gp_der_symbol character varying(128)
term_der_id integer
term_der_acc character varying(255)
term_der_name character varying(255)
term_der_is_obsolete integer
assoc_der_is_not integer
assoc_der_qual character varying(255)
  SELECT gpx_src.xref_dbname AS gp_src_dbname
  , gpx_src.xref_key AS gp_src_key
  , gp_src.id AS gp_src_id
  , gp_src.symbol AS gp_src_symbol
  , gpx_der.xref_dbname AS gp_der_dbname
  , gpx_der.xref_key AS gp_der_key
  , gp_der.id AS gp_der_id
  , gp_der.symbol AS gp_der_symbol
  , t_der.id AS term_der_id
  , t_der.acc AS term_der_acc
  , t_der.name AS term_der_name
  , t_der.is_obsolete AS term_der_is_obsolete
  , a_der.is_not AS assoc_der_is_not
  , aqt_der.name AS assoc_der_qual 
  FROM (
  (
  (
  (
  (
  (
  (
  (
  (go_associations.gene_product gp_src 
  JOIN go_general.dbxref gpx_src 
  ON (
  (gp_src.dbxref_id = gpx_src.id)
  )
  )
  JOIN go_associations.evidence_dbxref ex_der 
  ON (
  (ex_der.dbxref_id = gp_src.dbxref_id)
  )
  )
  JOIN go_associations.evidence e_der 
  ON (
  (e_der.id = ex_der.evidence_id)
  )
  )
  JOIN go_associations.association a_der 
  ON (
  (a_der.id = e_der.association_id)
  )
  )
  JOIN go_graph.term t_der 
  ON (
  (a_der.term_id = t_der.id)
  )
  )
  JOIN go_associations.gene_product gp_der 
  ON (
  (a_der.gene_product_id = gp_der.id)
  )
  )
  JOIN go_general.dbxref gpx_der 
  ON (
  (gp_der.dbxref_id = gpx_der.id)
  )
  )
  LEFT JOIN go_associations.association_qualifier aq_der 
  ON (
  (a_der.id = aq_der.association_id)
  )
  )
  LEFT JOIN go_graph.term aqt_der 
  ON (
  (aqt_der.id = aq_der.term_id)
  )
  )
  WHERE (
  (e_der.code)::text = 'ISS'::text
  );

Index - Schema go_evidence_views

View: go_evidence_views.db_evidence_summary

go_evidence_views.db_evidence_summary Structure
F-Key Name Type Description
xref_dbname character varying(55)
code character varying(8)
with_dbname character varying(55)
pub_dbname character varying(55)
pub_acc character varying(255)
total_associations bigint
  SELECT DISTINCT dbxref.xref_dbname
  , aew.code
  , aew.with_dbname
  , aew.pub_dbname
  , aew.pub_acc
  , count
  (aew.association_id) AS total_associations 
  FROM (
  (
  (go_associations.gene_product 
  JOIN go_general.dbxref 
  ON (
  (gene_product.dbxref_id = dbxref.id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_evidence_views.association_evidence_with aew 
  ON (
  (aew.association_id = association.id)
  )
  )
  GROUP BY dbxref.xref_dbname
  , aew.code
  , aew.with_dbname
  , aew.pub_dbname
  , aew.pub_acc;

Index - Schema go_evidence_views

View: go_evidence_views.ic_evidence

go_evidence_views.ic_evidence Structure
F-Key Name Type Description
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM go_associations.evidence 
  WHERE (
  (evidence.code)::text = 'IC'::text
  );

Index - Schema go_evidence_views

View: go_evidence_views.iss_annotation_to_nas_direct

go_evidence_views.iss_annotation_to_nas_direct Structure
F-Key Name Type Description
gp_src_dbname character varying(55)
gp_src_key character varying(255)
gp_src_id integer
gp_src_symbol character varying(128)
gp_der_dbname character varying(55)
gp_der_key character varying(255)
gp_der_id integer
gp_der_symbol character varying(128)
term_der_id integer
term_der_acc character varying(255)
term_der_name character varying(255)
term_der_is_obsolete integer
assoc_der_is_not integer
assoc_der_qual character varying(255)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
e_src_id integer
e_src_dbxref_id integer
  SELECT aicp.gp_src_dbname
  , aicp.gp_src_key
  , aicp.gp_src_id
  , aicp.gp_src_symbol
  , aicp.gp_der_dbname
  , aicp.gp_der_key
  , aicp.gp_der_id
  , aicp.gp_der_symbol
  , aicp.term_der_id
  , aicp.term_der_acc
  , aicp.term_der_name
  , aicp.term_der_is_obsolete
  , aicp.assoc_der_is_not
  , aicp.assoc_der_qual
  , a_src.id
  , a_src.term_id
  , a_src.gene_product_id
  , a_src.is_not
  , a_src.assocdate
  , a_src.source_db_id
  , e_src.id AS e_src_id
  , e_src.dbxref_id AS e_src_dbxref_id 
  FROM (
  (go_evidence_views.association_inference_candidate_pair aicp 
  JOIN go_associations.association a_src 
  ON (
  (aicp.term_der_id = a_src.term_id)
  )
  )
  JOIN go_associations.evidence e_src 
  ON (
  (a_src.id = e_src.association_id)
  )
  )
  WHERE (
  (e_src.code)::text = 'NAS'::text
  );

Index - Schema go_evidence_views

View: go_evidence_views.iss_annotation_to_nas_direct_without

go_evidence_views.iss_annotation_to_nas_direct_without Structure
F-Key Name Type Description
gp_src_dbname character varying(55)
gp_src_key character varying(255)
gp_src_id integer
gp_src_symbol character varying(128)
gp_der_dbname character varying(55)
gp_der_key character varying(255)
gp_der_id integer
gp_der_symbol character varying(128)
term_der_id integer
term_der_acc character varying(255)
term_der_name character varying(255)
term_der_is_obsolete integer
assoc_der_is_not integer
assoc_der_qual character varying(255)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
e_src_id integer
e_src_dbxref_id integer
  SELECT iss_annotation_to_nas_direct.gp_src_dbname
  , iss_annotation_to_nas_direct.gp_src_key
  , iss_annotation_to_nas_direct.gp_src_id
  , iss_annotation_to_nas_direct.gp_src_symbol
  , iss_annotation_to_nas_direct.gp_der_dbname
  , iss_annotation_to_nas_direct.gp_der_key
  , iss_annotation_to_nas_direct.gp_der_id
  , iss_annotation_to_nas_direct.gp_der_symbol
  , iss_annotation_to_nas_direct.term_der_id
  , iss_annotation_to_nas_direct.term_der_acc
  , iss_annotation_to_nas_direct.term_der_name
  , iss_annotation_to_nas_direct.term_der_is_obsolete
  , iss_annotation_to_nas_direct.assoc_der_is_not
  , iss_annotation_to_nas_direct.assoc_der_qual
  , iss_annotation_to_nas_direct.id
  , iss_annotation_to_nas_direct.term_id
  , iss_annotation_to_nas_direct.gene_product_id
  , iss_annotation_to_nas_direct.is_not
  , iss_annotation_to_nas_direct.assocdate
  , iss_annotation_to_nas_direct.source_db_id
  , iss_annotation_to_nas_direct.e_src_id
  , iss_annotation_to_nas_direct.e_src_dbxref_id 
  FROM go_evidence_views.iss_annotation_to_nas_direct 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT a_src.id 
  FROM go_associations.association a_src 
  WHERE (a_src.term_id = iss_annotation_to_nas_direct.term_der_id)
  )
  )
  );

Index - Schema go_evidence_views

View: go_evidence_views.nd_evidence

go_evidence_views.nd_evidence Structure
F-Key Name Type Description
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM go_associations.evidence 
  WHERE (
  (evidence.code)::text = 'ND'::text
  );

Index - Schema go_evidence_views

View: go_evidence_views.stale_ic_ipr

go_evidence_views.stale_ic_ipr Structure
F-Key Name Type Description
evidence_id integer
dbxref_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
  SELECT DISTINCT ex.evidence_id
  , ex.dbxref_id
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id 
  FROM (
  (
  (go_evidence_views.ic_evidence ic 
  JOIN go_associations.evidence_dbxref ex 
  ON (
  (ic.id = ex.evidence_id)
  )
  )
  JOIN go_associations.association a 
  ON (
  (a.id = ic.association_id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (ex.dbxref_id = dbxref.id)
  )
  )
  WHERE (
  (
  (dbxref.xref_dbname)::text = 'UniProt'::text
  )
  AND (NOT 
  (EXISTS 
  (
  SELECT a2.id
  , a2.term_id
  , a2.gene_product_id
  , a2.is_not
  , a2.assocdate
  , a2.source_db_id
  , tc.id
  , tc.term1_id
  , tc.term2_id
  , tc.relationship_type_id
  , tc.distance
  , tc.relation_distance
  , tx.term_id
  , tx.dbxref_id
  , tx.is_for_definition 
  FROM (
  (go_associations.association a2 
  JOIN go_optimisations.graph_path tc 
  ON (
  (tc.term2_id = a2.term_id)
  )
  )
  JOIN go_meta.term_dbxref tx 
  ON (
  (tx.term_id = tc.term1_id)
  )
  )
  WHERE (
  (a2.gene_product_id = a.gene_product_id)
  AND (tx.dbxref_id = ex.dbxref_id)
  )
  )
  )
  )
  );

Index - Schema go_evidence_views

View: go_evidence_views.stale_iss_annotation

go_evidence_views.stale_iss_annotation Structure
F-Key Name Type Description
gp_src_dbname character varying(55)
gp_src_key character varying(255)
gp_src_id integer
gp_src_symbol character varying(128)
gp_der_dbname character varying(55)
gp_der_key character varying(255)
gp_der_id integer
gp_der_symbol character varying(128)
term_der_id integer
term_der_acc character varying(255)
term_der_name character varying(255)
term_der_is_obsolete integer
assoc_der_is_not integer
assoc_der_qual character varying(255)
  SELECT association_inference_candidate_pair.gp_src_dbname
  , association_inference_candidate_pair.gp_src_key
  , association_inference_candidate_pair.gp_src_id
  , association_inference_candidate_pair.gp_src_symbol
  , association_inference_candidate_pair.gp_der_dbname
  , association_inference_candidate_pair.gp_der_key
  , association_inference_candidate_pair.gp_der_id
  , association_inference_candidate_pair.gp_der_symbol
  , association_inference_candidate_pair.term_der_id
  , association_inference_candidate_pair.term_der_acc
  , association_inference_candidate_pair.term_der_name
  , association_inference_candidate_pair.term_der_is_obsolete
  , association_inference_candidate_pair.assoc_der_is_not
  , association_inference_candidate_pair.assoc_der_qual 
  FROM go_evidence_views.association_inference_candidate_pair 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT a_src.id 
  FROM (go_associations.association a_src 
  JOIN go_optimisations.graph_path pth 
  ON (
  (pth.term2_id = a_src.term_id)
  )
  )
  WHERE (
  (pth.term1_id = association_inference_candidate_pair.term_der_id)
  AND (a_src.gene_product_id = association_inference_candidate_pair.gp_src_id)
  )
  )
  )
  );

Index - Schema go_evidence_views

Schema go_general

Table: go_general.db

metadata on the different database / accession granting authorities. the data should come from the GO.xref_abbs file. (docs: http://www.geneontology.org/cgi-bin/xrefs.cgi) the dbname is the abbreviation, and should match dbxref.xref_dbname however we have no foreign key so not every dbxref.xref_dbname will have an entry here most columns will not be populated in the short term - the other fields are for future expansion

go_general.db Structure
F-Key Name Type Description
id serial PRIMARY KEY
name character varying(55) UNIQUE
fullname character varying(255)
datatype character varying(255)
generic_url character varying(255)
url_syntax character varying(255)
url_example character varying(255)
uri_prefix character varying(255)

Tables referencing this one via Foreign Key Constraints:

db1 name db2 fullname db3 datatype

Index - Schema go_general

Table: go_general.dbxref

a unique bipartite identifier for a record typically housed in an external database. dbxrefs are of the form DB_ID:Local_ID. the combination of DB and local identifier is unique: it is up to the DB_ID authority to ensure Local_ID is unique within their ID space This table is referenced from a variety of other table: (1) as an xref for a sequence (*seq_dbxref* table) (2) as a primary identifier for a gene product (gene_product != seq except in the case of GOA) (*gene_product.dbxref_id* column) (3) as an xref for a term or term definition (eg swissprot keywords) (*term_dbxref* table) (4) as evidence for an association based on sequence evidence (*evidence* or *evidence_dbxref* table) (docs: http://www.geneontology.org/cgi-bin/xrefs.cgi)

go_general.dbxref Structure
F-Key Name Type Description
id serial PRIMARY KEY
xref_dbname character varying(55) UNIQUE#1 NOT NULL The name of the database or ID-granting authority from which information concerning this dbxref can be retrieved. It is recommended that this comes from the set here: http://www.geneontology.org/cgi-bin/xrefs.cgi; however, this is not enforced at the schema level. There *may* be a corresponding entry in the *db* table, housing metadata on this dbname but this is not enforced as a foreign key reference (Example: FB) (Example: SGD) (Example: UniProt) (Example: PubMed)
xref_key character varying(255) UNIQUE#1 NOT NULL The local identifier that is unique within xref_dbname (Example: FBgn0000001)
xref_keytype character varying(32) DEPRECATED. Was in principle used for what "type" the xref was - eg symbol vs ID
xref_desc character varying(255) DEPRECATED optional description of dbxref

Tables referencing this one via Foreign Key Constraints:

dx1 xref_dbname dx2 xref_key dx3 id, xref_dbname dx4 id, xref_key, xref_dbname dx5 id, xref_key

Index - Schema go_general

Schema go_graph

Table: go_graph.relation_composition

(See http://wiki.geneontology.org/index.php/Relation_composition) Stores rules of the form: r1 . r2 -> r i.e. IF [ X r1 Y ] AND [ Y r2 Z ] THEN [ X r Z ] Corresponds to "transitive_over" and "holds_over_chain" tags in obo-format.

go_graph.relation_composition Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_graph.term.id relation1_id integer UNIQUE#1 NOT NULL The first relation in the pairwise composition. References an entry in the term table. (recall that the term table housed both the terms themselves, and the relations)
go_graph.term.id relation2_id integer UNIQUE#1 NOT NULL The second relation in the pairwise composition. References an entry in the term table. (recall that the term table housed both the terms themselves, and the relations)
go_graph.term.id inferred_relation_id integer UNIQUE#1 NOT NULL The inferred relation in the pairwise composition. References an entry in the term table. (recall that the term table housed both the terms themselves, and the relations)
rc1 relation1_id rc2 relation2_id rc3 inferred_relation_id rc4 relation1_id, relation2_id, inferred_relation_id

Index - Schema go_graph

Table: go_graph.relation_properties

go_graph.relation_properties Structure
F-Key Name Type Description
go_graph.term.id relationship_type_id integer UNIQUE NOT NULL The first relation in the pairwise composition. References an entry in the term table. (recall that the term table housed both the terms themselves, and the relations)
is_transitive integer equals 1 if this relation is transitive. IF [X R Y] AND [Y R Z] AND [R is_transitive] THEN [X R Z] (OBO-Format: *is_transitive* tag) valid values: 0 or 1
is_symmetric integer equals 1 if this relation is symmetric. IF [X R Y] AND [R is_symmetric] THEN [Y R X] (OBO-Format: *is_symmetric* tag) valid values: 0 or 1
is_anti_symmetric integer equals 1 if this relation is anti_symmetric. IF [X R Y] AND [Y R X] AND [R is_anti_symmetric] THEN [X=Y] (OBO-Format: *is_anti_symmetric* tag) valid values: 0 or 1
is_cyclic integer equals 1 if this relation is cyclic. (OBO-Format: *is_cyclic* tag) valid values: 0 or 1
is_reflexive integer equals 1 if this relation is reflexive. IF [R is_reflexive] THEN [X R X] (OBO-Format: *is_reflexive* tag) valid values: 0 or 1
is_metadata_tag integer equals 1 if this relation is metadata_tag. IF [X R Y] AND [R is_metadata_tag] THEN [Y R X] (OBO-Format: *is_metadata_tag* tag) valid values: 0 or 1

Index - Schema go_graph

Table: go_graph.term

Fundamental representational unit in a controlled vocabulary or ontology. *Terms* form the nodes in the ontology graph (structured using the *term2term* table). An example of a term in GO is GO:0009333 "cysteine synthase complex". Each entry in the term table corresponds to a distinct type (kind, class) of entity in reality. Note that the term table is also used for storing *relations*. The two fundamental relations in GO are "is_a" and "part_of". Relations comprise the "labels" of the edges of the ontology graph. Note the term "term" is misleading in that this table is *not* used for storing synonyms and alternate labels; only the *preferred* terms, corresponding to nodes in the ontology graph each representing a distinct type of entity. In OBO-Format, both Term and Typedef (relation) stanzas are housed in the term table. (doc: http://www.geneontology.org/GO.format.obo-1_2.shtml)

go_graph.term Structure
F-Key Name Type Description
id serial PRIMARY KEY
name character varying(255) NOT NULL DEFAULT ''::character varying A textual label for the term. Each term has a single such label (see *term_synonym* for alternate labels). The name should be unique within an ontology (in fact uniqueness is encourage across ontologies - the principle of univocity. However, this is not enforced at the database schema level). the uniqueness recommendation is relaxed in the case of obsolete terms, which are also housed in this table: there can be many "ex-terms" with the same name. In some alternate systems, *term.name* is also known as the "preferred term" (OBO-Format: *name* tag) (Example: "cysteine biosnthetic process")
term_type character varying(55) NOT NULL The ontology or namespace to which this term belongs (OBO-Format: *namespace* tag) (Example: biological_process) (Note: the column name is somewhat misleading, but is retained for historical reasons. It would be better named "namespace" or "ontology") The namespace for GO terms will always be molecular_function, biological_process or cellular_component. The relations defined in the main GO obo file (from which this table is populated) go into the gene_ontology namespace, with the exception of is_a, which has namespace "relationship" (taken from the obo relation ontology). is_a is a builtin relation as far as obo is concerned, so it does not go in the gene_ontology namespace
acc character varying(255) UNIQUE NOT NULL The unique identifier for this term. This should be in OBO bipartite ID format, and should be unique within OBO, but this is not enforced at the schema level. (Example: GO:0019344) (OBO-Format: *id* tag)
is_obsolete integer NOT NULL equals 1 if this row corresponds to an obsoleted "ex-term". Note that obsoletes are not terms in the true sense, but we house them in the same table as this is the most expedient for the kinds of queries people wish to perform. (OBO-Format: *is_obsolete* tag) valid values: 0 or 1
is_root integer NOT NULL equals 1 if this term is the root term in the ontology graph. Note that in some instantiations of the GO database "fake" root nodes are added (OBO-Format: No correspoding tag)
is_relation integer NOT NULL equals 1 if this term is a relation (relationship type) (OBO-Format: Typedef stanzas)

Tables referencing this one via Foreign Key Constraints:

t1 name t2 term_type t3 acc t4 id, acc t5 id, name t6 id, term_type t7 id, acc, name, term_type

Index - Schema go_graph

Table: go_graph.term2term

Each entry in this table corresponds to an arc/edge in the ontology graph, which represents a relationship that holds between two entities in reality. Graphs are often thought of in terms of parent-child links; with this conception term1_id is the parent and term2_id is the child. However, it may be better to think of each edge as a *statement*, each statement being about a subject and it's relationship to some other entity. For example, a part_of edge between terms "nucleus" and "cell" is a statement about cell nuclei in general, namely that all nuclei are part_of some cell. (the statement is *not* a general statement about cells: not all cells have a nucleus) Here the "subject" of the statement corresponds to term2_id. EXAMPLE: if term1_id points to nucleic acid binding AND term2_id points to DNA binding AND relationship_type points to "is_a" THEN we have a statement "DNA binding is_a nucleic acid binding" (OBO-Format: *is_a* tag or *relationship* tag)

go_graph.term2term Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_graph.term.id relationship_type_id integer UNIQUE#1 NOT NULL References an entry in the term table corresponding to the relation that holds between term2 and term1 (recall that the term table housed both the terms themselves, and the relations) (Example: a reference to a row "part_of" in the term table)
go_graph.term.id term1_id integer UNIQUE#1 NOT NULL the "parent" node of the edge. For example, in the edge corresponding to "nucleus part_of cell", (all nuclei are part_of some cell) term1_id is "cell"
go_graph.term.id term2_id integer UNIQUE#1 NOT NULL the "child" node of the edge. For example, in the edge corresponding to "nucleus part_of cell", (all nuclei are part_of some cell) term2_id is "nucleus"
complete integer UNIQUE#1 NOT NULL equals 1 if this edges comprises an element of the *complete definition*, a set of necessary and sufficient conditions. Note that this field is always =0 for current publically deployed instantiations of the GO database, but is currently used in experimental instantiations for housing so called "cross-products". Example: the term "cysteine metabolism" is completely defined by the edges "is_a metabolism" and "has_participant cysteine" - which is to say anything that satisfies these two conditions is by definition an instance of cysteine metabolism. "complete" links/edges may be provided in addition to normal links/edges, even though these may be partially redundant. Formally: if complete=0, the edge states a NECESSARY CONDITION for term2_id. The set of all edges with complete=1 for any term2_id states the NECESSARY AND SUFFICIENT CONDITIONS for that term. Although currently unused in publically deployed GO DB instances, software using the schema may wish to explicitly query for complete=0 to ensure "normal" graph links are returned as results.
tt1 term1_id tt2 term2_id tt3 term1_id, term2_id tt4 relationship_type_id

Index - Schema go_graph

Schema go_graph_views

View: go_graph_views.avg_max_distance_to_leaf_term_by_db

go_graph_views.avg_max_distance_to_leaf_term_by_db Structure
F-Key Name Type Description
xref_dbname character varying(55)
avg_max_distance numeric
  SELECT dbxref.xref_dbname
  , avg
  (max_distance_to_leaf_by_term.max_distance) AS avg_max_distance 
  FROM (
  (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_graph_views.max_distance_to_leaf_by_term 
  ON (
  (max_distance_to_leaf_by_term.term_id = association.term_id)
  )
  )
  WHERE (NOT 
  (association.term_id IN 
  (
  SELECT root_term.id 
  FROM go_graph_views.root_term
  )
  )
  )
  GROUP BY dbxref.xref_dbname;

Index - Schema go_graph_views

View: go_graph_views.avg_max_distance_to_leaf_term_by_db_and_ontology

go_graph_views.avg_max_distance_to_leaf_term_by_db_and_ontology Structure
F-Key Name Type Description
xref_dbname character varying(55)
term_type character varying(55)
avg_max_distance numeric
  SELECT dbxref.xref_dbname
  , term.term_type
  , avg
  (max_distance_to_leaf_by_term.max_distance) AS avg_max_distance 
  FROM (
  (
  (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_graph_views.max_distance_to_leaf_by_term 
  ON (
  (max_distance_to_leaf_by_term.term_id = association.term_id)
  )
  )
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  WHERE (NOT 
  (association.term_id IN 
  (
  SELECT root_term.id 
  FROM go_graph_views.root_term
  )
  )
  )
  GROUP BY dbxref.xref_dbname
  , term.term_type;

Index - Schema go_graph_views

View: go_graph_views.avg_max_distance_to_leaf_term_by_db_and_species

go_graph_views.avg_max_distance_to_leaf_term_by_db_and_species Structure
F-Key Name Type Description
xref_dbname character varying(55)
common_name character varying(255)
avg_max_distance numeric
  SELECT dbxref.xref_dbname
  , species.common_name
  , avg
  (max_distance_to_leaf_by_term.max_distance) AS avg_max_distance 
  FROM (
  (
  (
  (go_general.dbxref 
  JOIN go_associations.gene_product 
  ON (
  (dbxref.id = gene_product.dbxref_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_graph_views.max_distance_to_leaf_by_term 
  ON (
  (max_distance_to_leaf_by_term.term_id = association.term_id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gene_product.species_id = species.id)
  )
  )
  WHERE (NOT 
  (association.term_id IN 
  (
  SELECT root_term.id 
  FROM go_graph_views.root_term
  )
  )
  )
  GROUP BY dbxref.xref_dbname
  , species.common_name;

Index - Schema go_graph_views

View: go_graph_views.distance_to_leaf_stats_by_term

each term can have multiple paths to the leaves of the DAG; this finds the min, max and avg distance - grouped by term (note: see max_distance_to_leaf_by_term for a more efficient query)

go_graph_views.distance_to_leaf_stats_by_term Structure
F-Key Name Type Description
term_id integer
max_distance integer
min_distance integer
avg_distance numeric
delta_distance integer
  SELECT p.term1_id AS term_id
  , max
  (p.distance) AS max_distance
  , min
  (p.distance) AS min_distance
  , avg
  (p.distance) AS avg_distance
  , (max
  (p.distance) - min
  (p.distance)
  ) AS delta_distance 
  FROM go_graph_views.path_to_leaf p 
  GROUP BY p.term1_id;

Index - Schema go_graph_views

View: go_graph_views.distance_to_root_stats_by_term

each term can have multiple paths to the root(s); this finds the min, max and avg distance - grouped by term (note: see max_distance_to_root_by_term for a more efficient query)

go_graph_views.distance_to_root_stats_by_term Structure
F-Key Name Type Description
term_id integer
max_distance integer
min_distance integer
avg_distance numeric
delta_distance integer
  SELECT p.term2_id AS term_id
  , max
  (p.distance) AS max_distance
  , min
  (p.distance) AS min_distance
  , avg
  (p.distance) AS avg_distance
  , (max
  (p.distance) - min
  (p.distance)
  ) AS delta_distance 
  FROM go_graph_views.path_to_root p 
  GROUP BY p.term2_id;

Index - Schema go_graph_views

View: go_graph_views.leaf_node

A term that has no children

go_graph_views.leaf_node Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT DISTINCT t.id
  , t.name
  , t.term_type
  , t.acc
  , t.is_obsolete
  , t.is_root
  , t.is_relation 
  FROM (
  (go_graph.term t 
  JOIN go_graph.term2term t2t1 
  ON (
  (t.id = t2t1.term2_id)
  )
  )
  LEFT JOIN go_graph.term2term t2t2 
  ON (
  (t2t1.term2_id = t2t2.term1_id)
  )
  )
  WHERE (t2t2.term1_id IS NULL);

Index - Schema go_graph_views

View: go_graph_views.max_distance_to_leaf_by_term

go_graph_views.max_distance_to_leaf_by_term Structure
F-Key Name Type Description
term_id integer
max_distance integer
  SELECT p.term1_id AS term_id
  , max
  (p.distance) AS max_distance 
  FROM go_optimisations.graph_path p 
  GROUP BY p.term1_id;

Index - Schema go_graph_views

View: go_graph_views.max_distance_to_root_by_term

each term can have multiple paths to the root(s); this finds the maximum distance - grouped by term note: this view is redundant with distance_to_root_stats_by_term, but it is faster we do not need to check if a path leads to the root node, since any path that does NOT lead to the root will have a longer path that DOES. This assumptions holds so long as we have a basic treatment of root and path that ignores relationship_type

go_graph_views.max_distance_to_root_by_term Structure
F-Key Name Type Description
term_id integer
max_distance integer
  SELECT p.term2_id AS term_id
  , max
  (p.distance) AS max_distance 
  FROM go_optimisations.graph_path p 
  GROUP BY p.term2_id;

Index - Schema go_graph_views

View: go_graph_views.max_max_distance_to_root_by_term

go_graph_views.max_max_distance_to_root_by_term Structure
F-Key Name Type Description
max integer
  SELECT max
  (max_distance_to_root_by_term.max_distance) AS max 
  FROM go_graph_views.max_distance_to_root_by_term;

Index - Schema go_graph_views

View: go_graph_views.non_root_term

go_graph_views.non_root_term Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM go_graph.term 
  WHERE (
  (term.name)::text <> ALL 
  (
  (ARRAY['biological_process'::character varying
  ,'molecular_function'::character varying
  ,'cellular_component'::character varying
  ,'biological process unknown'::character varying
  ,'molecular function unknown'::character varying
  ,'cellular component unknown'::character varying
  ,'all'::character varying]
  )::text[]
  )
  );

Index - Schema go_graph_views

View: go_graph_views.path_to_leaf

graph_path from a term to a leaf node

go_graph_views.path_to_leaf Structure
F-Key Name Type Description
id integer
term1_id integer
term2_id integer
relationship_type_id integer
distance integer
relation_distance integer
  SELECT DISTINCT p.id
  , p.term1_id
  , p.term2_id
  , p.relationship_type_id
  , p.distance
  , p.relation_distance 
  FROM (go_optimisations.graph_path p 
  JOIN go_graph_views.leaf_node t 
  ON (
  (p.term2_id = t.id)
  )
  );

Index - Schema go_graph_views

View: go_graph_views.path_to_root

graph_path from a term to a root node

go_graph_views.path_to_root Structure
F-Key Name Type Description
id integer
term1_id integer
term2_id integer
relationship_type_id integer
distance integer
relation_distance integer
  SELECT DISTINCT p.id
  , p.term1_id
  , p.term2_id
  , p.relationship_type_id
  , p.distance
  , p.relation_distance 
  FROM (go_optimisations.graph_path p 
  JOIN go_graph.term t 
  ON (
  (p.term1_id = t.id)
  )
  )
  WHERE (t.is_root = 1);

Index - Schema go_graph_views

View: go_graph_views.root_term

go_graph_views.root_term Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM go_graph.term 
  WHERE (
  (term.name)::text = ANY 
  (
  (ARRAY['biological_process'::character varying
  ,'molecular_function'::character varying
  ,'cellular_component'::character varying
  ,'biological process unknown'::character varying
  ,'molecular function unknown'::character varying
  ,'cellular component unknown'::character varying
  ,'all'::character varying]
  )::text[]
  )
  );

Index - Schema go_graph_views

View: go_graph_views.term_ancestor

term * graph_path ancestor_id is an ancestor of term

go_graph_views.term_ancestor Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
distance integer
ancestor_id integer
  SELECT term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation
  , graph_path.distance
  , graph_path.term1_id AS ancestor_id 
  FROM (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term2_id)
  )
  );

Index - Schema go_graph_views

View: go_graph_views.term_descendent

term * graph_path descendent_id is a descendent of term

go_graph_views.term_descendent Structure
F-Key Name Type Description
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
distance integer
descendent_id integer
  SELECT term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation
  , graph_path.distance
  , graph_path.term2_id AS descendent_id 
  FROM (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term1_id)
  )
  );

Index - Schema go_graph_views

View: go_graph_views.term_having_max_delta_distance_to_root

what are the most unbalanced terms in the DAG?

go_graph_views.term_having_max_delta_distance_to_root Structure
F-Key Name Type Description
term_id integer
max_distance integer
min_distance integer
avg_distance numeric
delta_distance integer
  SELECT distance_to_root_stats_by_term.term_id
  , distance_to_root_stats_by_term.max_distance
  , distance_to_root_stats_by_term.min_distance
  , distance_to_root_stats_by_term.avg_distance
  , distance_to_root_stats_by_term.delta_distance 
  FROM go_graph_views.distance_to_root_stats_by_term 
  WHERE (distance_to_root_stats_by_term.delta_distance IN 
  (
  SELECT max
  (distance_to_root_stats_by_term.delta_distance) AS max 
  FROM go_graph_views.distance_to_root_stats_by_term
  )
  );

Index - Schema go_graph_views

View: go_graph_views.term_having_max_max_distance_to_root

what are the deepest terms in the DAG?

go_graph_views.term_having_max_max_distance_to_root Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
max_distance integer
  SELECT term.acc
  , term.name
  , md.max_distance 
  FROM (go_graph_views.max_distance_to_root_by_term md 
  JOIN go_graph.term 
  ON (
  (term.id = md.term_id)
  )
  )
  WHERE (md.max_distance IN 
  (
  SELECT max
  (max_distance_to_root_by_term.max_distance) AS max 
  FROM go_graph_views.max_distance_to_root_by_term
  )
  );

Index - Schema go_graph_views

View: go_graph_views.term_having_most_paths_to_root

go_graph_views.term_having_most_paths_to_root Structure
F-Key Name Type Description
term_id integer
total_paths bigint
  SELECT total_paths_to_root_by_term.term_id
  , total_paths_to_root_by_term.total_paths 
  FROM go_graph_views.total_paths_to_root_by_term 
  WHERE (total_paths_to_root_by_term.total_paths IN 
  (
  SELECT max
  (total_paths_to_root_by_term.total_paths) AS max 
  FROM go_graph_views.total_paths_to_root_by_term
  )
  );

Index - Schema go_graph_views

View: go_graph_views.total_paths_to_root_by_term

go_graph_views.total_paths_to_root_by_term Structure
F-Key Name Type Description
term_id integer
total_paths bigint
  SELECT p.term2_id AS term_id
  , count
  (p.id) AS total_paths 
  FROM go_optimisations.graph_path p 
  GROUP BY p.term2_id;

Index - Schema go_graph_views

View: go_graph_views.transitive_association

association * graph_path

go_graph_views.transitive_association Structure
F-Key Name Type Description
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
ancestor_id integer
  SELECT association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , graph_path.term1_id AS ancestor_id 
  FROM (go_associations.association 
  JOIN go_optimisations.graph_path 
  ON (
  (association.term_id = graph_path.term2_id)
  )
  );

Index - Schema go_graph_views

Schema go_homology

Table: go_homology.gene_product_ancestor

go_homology.gene_product_ancestor Structure
F-Key Name Type Description
go_associations.gene_product.id gene_product_id integer NOT NULL
go_associations.gene_product.id ancestor_id integer NOT NULL

Index - Schema go_homology

Table: go_homology.gene_product_homology

go_homology.gene_product_homology Structure
F-Key Name Type Description
go_associations.gene_product.id gene_product1_id integer NOT NULL
go_associations.gene_product.id gene_product2_id integer NOT NULL
go_graph.term.id relationship_type_id integer NOT NULL References an entry in the term table corresponding to the relation that holds between 1 and 2

Index - Schema go_homology

Table: go_homology.gene_product_homolset

a set-member relation between a gene product and the homolset to which it belongs. the relation should, where possible, be supported by individual homology-based relations [TODO] REQUIRED FOR REFERENCE GENOMES PROJECT

go_homology.gene_product_homolset Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_associations.gene_product.id gene_product_id integer NOT NULL
go_homology.homolset.id homolset_id integer NOT NULL

Index - Schema go_homology

Table: go_homology.homolset

A collection of genes or gene products from a common evolutionary lineage. The purpose of this table is to group homologous sets of gene products to query for shared and divergent biological function. The table is neutral with respect to the method used to determine evolutionary relations; instead it represents the derived results of some kind of some analysis. A homolset may also be derived from a collection of analyses. The table is also neutral w.r.t questions of homology or orthology REQUIRED FOR REFERENCE GENOMES PROJECT

go_homology.homolset Structure
F-Key Name Type Description
id serial PRIMARY KEY
symbol character varying(128) A convenient human-assigned label for the homology set; this may be arbitarily chosen from one of the set members (frequently the target_gene_product); or it may be the name of the gene family. no guarantee is given to its usefulness
go_general.dbxref.id dbxref_id integer UNIQUE A globally unique identifier for this set, or some proxy for the set. For example, if the purpose of building homolsets is to examine disease genes, then an OMIM ID may be appropriate to use here, even though OMIM is not concerned with homology or orthology. If the homolset is derived
go_associations.gene_product.id target_gene_product_id integer A homolset may be constructed from a collection of pairwise homology assignments between individual gene products and a (possibly arbitrary) "target" gene_product. this field is optional: for example, a target is not required for sets that are derived from a tree-based analysis
go_associations.species.id taxon_id integer The least common ancestor of all members of the homolset. (may not be populated)
go_graph.term.id type_id integer homolsets may fall into different categories - this field identifies the category. May not be populated
description text for example: if the purpose of the homolset is to examine disease genes and model organism orthologs, the description could be a summary of the disease in human

Tables referencing this one via Foreign Key Constraints:

Index - Schema go_homology

Schema go_meta

Table: go_meta.term2term_metadata

a metadata link between two terms this is primarily to support the "consider" and "replaced_by" tags in OBO Format 1.2. It could also be used for other metadata links we may want to include in the future. The main difference between term2term and this table is that term2term is for encoding the relationships that hold between types of biological entity, whereas this table is for relationships between the units in the ontology. Different rules apply to both. Eg consider/replaced_by would never propagate over the is_a relation open question: do we also want to include disjointness axioms here?

go_meta.term2term_metadata Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_graph.term.id relationship_type_id integer NOT NULL "consider" or "replaced_by" may in future be extended to other tags
go_graph.term.id term1_id integer UNIQUE#1 NOT NULL the "parent" node of the edge. For example, in the edge corresponding to for "GO:0005696 telomere" consider "GO:0000781 chromosome, telomeric region" term1_id is "GO:0005696"
go_graph.term.id term2_id integer UNIQUE#1 NOT NULL the "child" node of the edge. For example, in the edge corresponding to for "GO:0005696 telomere" consider "GO:0000781 chromosome, telomeric region" term2_id is "GO:0000781"

Index - Schema go_meta

Table: go_meta.term_dbxref

linking table between term and dbxref used where there is some other information entity of relevance to the term in question; it may be a dbxref for a publication defining the term, or it may be a reference to an entity in another database, terminology or ontology-like system with similar or identical semantics to the term.

go_meta.term_dbxref Structure
F-Key Name Type Description
go_graph.term.id term_id integer UNIQUE#1 NOT NULL
go_general.dbxref.id dbxref_id integer UNIQUE#1 NOT NULL
is_for_definition integer UNIQUE#1 NOT NULL equals 1 if this dbxref references the source of the definition of the term in question. This includes but is not limited to PubMed IDs. It may also be so-called "GO Curator" references, of the form GOC:<curator-id>
tx0 term_id tx1 dbxref_id tx2 term_id, dbxref_id

Index - Schema go_meta

Table: go_meta.term_definition

In OBO, a term should where possible be defined. A term can have only one definition. Note: due to this cardinality constraint, it would be possible to merge term_definition into the *term* table. The decision was made not to do this early on, and the schema has not been changed since. (doc: http://www.geneontology.org/GO.format.obo-1_2.shtml) (OBO-Format: *def* tag; and also the *comment* tag - see column documentation)

go_meta.term_definition Structure
F-Key Name Type Description
go_graph.term.id term_id integer UNIQUE NOT NULL
term_definition text NOT NULL A textual definition for the term referenced in term_definition.term_id. The attentive user will have two criticisms; the first is that it is poor practice for a column to share a name with a table. The second is for more egregious: although this column is declared non-null, in fact some entries are in fact empty strings. This is due to the fact that term comments are housed in this table, rather than in the term table or their own term_comment table! We accept that this was a poor design decision. However, we have no plans to rectify this modeling error in the short term as this would break many pieces of 3rd party software not under our control. No fixes are likely until after MySQL has good efficient support for SQL Views.
go_general.dbxref.id dbxref_id integer SEVERELY DEPRECATED: see term_dbxref.is_for_definition
term_comment text A free-text comment with non-definitional information that may be useful for end-users or curators. (OBO-Format: *comment* tag - each term has max 1 comment) Please see notes for term_definition column
reference character varying(255) SEVERELY DEPRECATED
td1 term_id

Index - Schema go_meta

Table: go_meta.term_subset

(aka goslims). Each subsetdef (slim) is stored as a term in the database (with term_type = 'subset') The subset_id links to this term (OBO-Format: *subset* tag. term_id references a term housing the *subsetdef*)

go_meta.term_subset Structure
F-Key Name Type Description
go_graph.term.id term_id integer NOT NULL
go_graph.term.id subset_id integer NOT NULL
tss1 term_id tss2 subset_id tss3 term_id, subset_id

Index - Schema go_meta

Table: go_meta.term_synonym

In OBO, each term can have 0 or more synonyms or alternate identifiers. A synonym is an alternate label for a preferred term, intended for humans. An alternate identifer is an OBO ID intended for unique identification of the term in information systems. Note: OBO Format has the concept of broad and narrow synonyms - these might better be called broad and narrow aliases or alternate labels, since technically the definition of synonym is such that synonyms can replace words without changing the meaning.

go_meta.term_synonym Structure
F-Key Name Type Description
go_graph.term.id term_id integer UNIQUE#1 NOT NULL
term_synonym character varying(996) UNIQUE#1 A textual label typically intended for humans. (Example: "cysteine biosynthesis" is a synonym for the term named "cysteine biosynthetic process") One wrinkle is that alternate identifiers are redundantly stored in this column, as well as in the acc_synonym column. This design decision can be rightfully criticised, but we retain this use for software compatibility reasons. alt_ids can ve discriminated from synonyms using synonym_type_id - this will be "alt_id" for alternate identifiers. Note: although we accept it is bad practice to name columns the same as tables, we retain this for software compatibility reasons (OBO-Format: *synonym* tag)
acc_synonym character varying(255) An alternate identifier. (OBO-Format: *alt_id* tag) See also docs for term_synonym column
go_graph.term.id synonym_type_id integer NOT NULL actually corresponds to a synonym "scope" in OBO-Format - one of exact, broad, narrow, related for alternate identifiers we use the term "alt_id" - see notes above
go_graph.term.id synonym_category_id integer category/class to which this synonym belongs Correspinds to type OBO-Format 1.2 Not currently used in GO Note: the synonym "scope" (eg exact, narrow) goes in synonym_type_id
ts1 term_id ts2 term_synonym ts3 term_id, term_synonym

Index - Schema go_meta

Schema go_obd_bridge

View: go_obd_bridge.asserted_link

go_obd_bridge.asserted_link Structure
F-Key Name Type Description
node_id integer
predicate_id unknown
object_id integer
when_id unknown
  SELECT term2term.term2_id AS node_id
  , NULL::unknown AS predicate_id
  , term2term.term1_id AS object_id
  , NULL::unknown AS when_id 
  FROM go_graph.term2term;

Index - Schema go_obd_bridge

View: go_obd_bridge.implied_link

go_obd_bridge.implied_link Structure
F-Key Name Type Description
node_id integer
predicate_id unknown
object_id integer
when_id unknown
  SELECT graph_path.term2_id AS node_id
  , NULL::unknown AS predicate_id
  , graph_path.term1_id AS object_id
  , NULL::unknown AS when_id 
  FROM go_optimisations.graph_path;

Index - Schema go_obd_bridge

View: go_obd_bridge.node

go_obd_bridge.node Structure
F-Key Name Type Description
node_id integer
uid character varying(255)
label character varying(255)
is_obsolete text
  SELECT term.id AS node_id
  , term.acc AS uid
  , term.name AS label
  , CASE WHEN 
  (term.is_obsolete = 0) THEN 'f'::text ELSE 't'::text END AS is_obsolete 
  FROM go_graph.term;

Index - Schema go_obd_bridge

View: go_obd_bridge.node_max_depth

go_obd_bridge.node_max_depth Structure
F-Key Name Type Description
node_id integer
predicate_id text
max_distance integer
  SELECT graph_path.term2_id AS node_id
  , NULL::text AS predicate_id
  , max
  (graph_path.distance) AS max_distance 
  FROM go_optimisations.graph_path 
  GROUP BY graph_path.term2_id
  , NULL::text;

Index - Schema go_obd_bridge

Schema go_optimisations

Table: go_optimisations.gene_product_count

this table for use in "data warehouse mode" (will typically be populated in publically available instantiations of the GO database). caches recursive gene product counts the number of DISTINCT gene product records at OR BELOW a term filtered by evidence code refers to the evidence code used to filter this particular count; if preceded by a ! it means exclude this evidence code; typically this will be "!IEA" (ie exclude IEA) speciesdbname corresponds to the dbname from gene_product.dbxref_id and represents the authority that contributed the annotated gene product being counted the product count is partitioned by the speciesdbname product_count is the number of DISTINCT gene product IDs owned by speciesdbname at or below term_id in the DAG note that the product_count is additive across speciesdbnames (because no two speciesdbnames may contribute the same ID), but is NOT additive across evidence codes (the same gene product can be associated more than once beneath a term with different evidence codes) this makes filtering by evidence code hard - we must include all combinations which is a factorial!! to get round this we typically only include counts for non-IEA associations Equivalent to the query: SELECT path.term1_id AS term_id, count(DISTINCT a.gene_product_id) AS total FROM association AS a INNER JOIN evidence AS e ON (e.association_id=a.id) INNER JOIN graph_path AS path ON (path.term2_id=ae.term_id) WHERE <<evidence constraint here>>

go_optimisations.gene_product_count Structure
F-Key Name Type Description
go_graph.term.id term_id integer NOT NULL the term for which gene products are counted; also includes terms below this term in the graph
code character varying(8) evidence code over which this count hold. can include negation; eg "!IEA" note that not every combination will be pre-computed. typically just !IEA is stored. Also note that counts over evidence codes are NON-additive; this is because the same gene_product can be double-counted if it is anntated with >1 evidence code
speciesdbname character varying(55) this should match the gene_product.dbxref (Examples: FlyBase, SGD, MGI, UniProt) (docs: http://www.geneontology.org/cgi-bin/xrefs.cgi) counts *ARE* additive across speciesdbnames - this is because each gene_product record belongs to a single speciesdbname, so double counting is not possible (although the same gene product in reality may be double annotated, for example by UniProt and by a Model Organism Database - these should be filtered out, but even in cases where this filtering fails we do not worry about double-counting when summing across speciesdbnames as we are technically counting distinct gene product *records*) (Note: there is no foreign key reference to the *db* table, but their could in principle be a nullable link here)
go_associations.species.id species_id integer The species or taxon the count pertains to (Note: in future we reserve the option to use gene_product to represent families at higher levels in the taxonomic tree above species)
product_count integer NOT NULL total number of DISTINCT genes/gene product records annotated directly to or via transitivity to term_id
gpc1 term_id gpc2 code gpc3 speciesdbname gpc4 term_id, code, speciesdbname gpc5 term_id, species_id

Index - Schema go_optimisations

Table: go_optimisations.graph_path

A transitive or implied link between two terms Example: if nuclear chromosome is_a chromosome, AND chromosome is_a organelle, then nuclear chromosome is_a organelle this table states whether there exists a path between a parent and a child, and the distance between them. multiple paths mean multiple entries in this table [this table only used in "data warehouse mode"] an entry also exists linking every term with itself of distance 0. This is known as "reflexive transitive closure". [See http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?query=transitive+closure] [See also http://foldoc.doc.ic.ac.uk/foldoc/foldoc.cgi?query=reflexive+transitive+... note: use of this table is optional. but as most relational dbs don't implement recursive queries, you will have to incrementally calculate the transitive closure via multiple SQL calls if you do not use it for graph based queries At this time, this table holds the general transitive closure across *all* relations. In the future this table may house transitive closure on a per-relation basis, taking into account the various rules that follow from the definitions of the relation in question. For example, if X is_a Y and Y part_of Z then X part_of Z.

go_optimisations.graph_path Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_graph.term.id term1_id integer NOT NULL the object node See docs for *term.term1_id*
go_graph.term.id term2_id integer NOT NULL the subject node See docs for *term.term2_id*
go_graph.term.id relationship_type_id integer References an entry in the term table corresponding to the INFERRED relation that holds between term2 and term1. For future extension. See: http://wiki.geneontology.org/index.php/Transitive_closure
distance integer The distance in terms of the number of "hops" between nodes in the asserted graph (term2term). The relationship_type_id is ignored here. Example: if A part_of B is_a C part_of D, then distance=3 for A part_of D
relation_distance integer (added 2008-10-27) The distance in terms of the number of "hops" over relationship_type_id in the asserted graph (term2term). Example: if A part_of B is_a C part_of D, then relation_distance=2 for A part_of D
graph_path1 term1_id graph_path2 term2_id graph_path3 term1_id, term2_id graph_path4 term1_id, distance graph_path5 term1_id, term2_id, relationship_type_id graph_path6 term1_id, term2_id, relationship_type_id, distance, relation_distance graph_path7 term2_id, relationship_type_id graph_path8 term1_id, relationship_type_id

Index - Schema go_optimisations

Schema go_prejoined_views

View: go_prejoined_views.association_evidence

go_prejoined_views.association_evidence Structure
F-Key Name Type Description
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
is_not integer
term_id integer
gene_product_id integer
  SELECT e.id
  , e.code
  , e.association_id
  , e.dbxref_id
  , e.seq_acc
  , a.is_not
  , a.term_id
  , a.gene_product_id 
  FROM (go_associations.association a 
  JOIN go_associations.evidence e 
  ON (
  (a.id = e.association_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.association_j_evidence

convenience join-view

go_prejoined_views.association_j_evidence Structure
F-Key Name Type Description
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
is_not integer
term_id integer
gene_product_id integer
  SELECT e.id
  , e.code
  , e.association_id
  , e.dbxref_id
  , e.seq_acc
  , a.is_not
  , a.term_id
  , a.gene_product_id 
  FROM (go_associations.association a 
  JOIN go_associations.evidence e 
  ON (
  (a.id = e.association_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.association_j_evidence_j_gene_product

convenience join-view

go_prejoined_views.association_j_evidence_j_gene_product Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
evidence_dbxref_id integer
code character varying(8)
is_not integer
term_id integer
gene_product_id integer
  SELECT gp.id
  , gp.symbol
  , gp.dbxref_id
  , gp.species_id
  , gp.type_id
  , gp.full_name
  , e.dbxref_id AS evidence_dbxref_id
  , e.code
  , a.is_not
  , a.term_id
  , a.gene_product_id 
  FROM (
  (go_associations.association a 
  JOIN go_associations.evidence e 
  ON (
  (a.id = e.association_id)
  )
  )
  JOIN go_associations.gene_product gp 
  ON (
  (a.gene_product_id = gp.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.evidence_j_evidence_dbxref_j_dbxref

convenience join-view

go_prejoined_views.evidence_j_evidence_dbxref_j_dbxref Structure
F-Key Name Type Description
code character varying(8)
evidence_primary_dbxref_id integer
association_id integer
evidence_id integer
dbxref_id integer
xref_dbname character varying(55)
xref_key character varying(255)
  SELECT evidence.code
  , evidence.dbxref_id AS evidence_primary_dbxref_id
  , evidence.association_id
  , evidence_dbxref.evidence_id
  , evidence_dbxref.dbxref_id
  , dbxref.xref_dbname
  , dbxref.xref_key 
  FROM (
  (go_associations.evidence 
  JOIN go_associations.evidence_dbxref 
  ON (
  (evidence.id = evidence_dbxref.evidence_id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (dbxref.id = evidence_dbxref.dbxref_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.gene_product_j_dbxref

go_prejoined_views.gene_product_j_dbxref Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
xref_dbname character varying(55)
xref_key character varying(255)
  SELECT gene_product.id
  , gene_product.symbol
  , gene_product.dbxref_id
  , gene_product.species_id
  , gene_product.type_id
  , gene_product.full_name
  , dbxref.xref_dbname
  , dbxref.xref_key 
  FROM (go_associations.gene_product 
  JOIN go_general.dbxref 
  ON (
  (gene_product.dbxref_id = dbxref.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.gene_product_j_dbxref_via_seq

convenience view linking a GP to a dbxref view the seq table - this information is usually sourced from the gp2protein info

go_prejoined_views.gene_product_j_dbxref_via_seq Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
xref_key character varying(255)
xref_dbname character varying(55)
  SELECT gene_product.id
  , gene_product.symbol
  , gene_product.dbxref_id
  , gene_product.species_id
  , gene_product.type_id
  , gene_product.full_name
  , dbxref.xref_key
  , dbxref.xref_dbname 
  FROM (
  (
  (go_associations.gene_product 
  JOIN go_sequence.gene_product_seq 
  ON (
  (gene_product.id = gene_product_seq.gene_product_id)
  )
  )
  JOIN go_sequence.seq_dbxref 
  ON (
  (gene_product_seq.seq_id = seq_dbxref.seq_id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (dbxref.id = seq_dbxref.dbxref_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.gene_product_j_gene_product_synonym

convenience view for querying gene products by their synonyms

go_prejoined_views.gene_product_j_gene_product_synonym Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
product_synonym character varying(255)
  SELECT gene_product.id
  , gene_product.symbol
  , gene_product.dbxref_id
  , gene_product.species_id
  , gene_product.type_id
  , gene_product.full_name
  , gene_product_synonym.product_synonym 
  FROM (go_associations.gene_product 
  JOIN go_associations.gene_product_synonym 
  ON (
  (gene_product.id = gene_product_synonym.gene_product_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.gene_product_with_term_pair_via_graph

co-occurrence of terms via annotations

go_prejoined_views.gene_product_with_term_pair_via_graph Structure
F-Key Name Type Description
superterm_name character varying(255)
superterm_acc character varying(255)
superterm_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
term1_name character varying(255)
term1_acc character varying(255)
term1_type character varying(55)
term2_name character varying(255)
term2_acc character varying(255)
term2_type character varying(55)
association2_id integer
association2_is_not integer
  SELECT a1.superterm_name
  , a1.superterm_acc
  , a1.superterm_type
  , a1.id
  , a1.term_id
  , a1.gene_product_id
  , a1.is_not
  , a1.assocdate
  , a1.source_db_id
  , a1.gp_symbol
  , a1.gp_dbxref_id
  , a1.gp_species_id
  , term1.name AS term1_name
  , term1.acc AS term1_acc
  , term1.term_type AS term1_type
  , term2.name AS term2_name
  , term2.acc AS term2_acc
  , term2.term_type AS term2_type
  , a2.id AS association2_id
  , a2.is_not AS association2_is_not 
  FROM (
  (
  (go_graph.term term1 
  JOIN go_prejoined_views.term_j_association_j_gene_product_via_graph a1 
  ON (
  (term1.id = a1.term_id)
  )
  )
  JOIN go_associations.association a2 
  ON (
  (a1.gene_product_id = a2.gene_product_id)
  )
  )
  JOIN go_graph.term term2 
  ON (
  (term2.id = a2.term_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association

convenience join-view.

go_prejoined_views.term_j_association Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
term_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
  SELECT term.name AS term_name
  , term.acc AS term_acc
  , term.term_type
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id 
  FROM (go_graph.term 
  JOIN go_associations.association 
  ON (
  (term.id = association.term_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_j_evidence_j_gene_product

go_prejoined_views.term_j_association_j_evidence_j_gene_product Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
term_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
evidence_id integer
code character varying(8)
seq_acc character varying(255)
evidence_dbxref_id integer
pub_dbname character varying(55)
pub_acc character varying(255)
  SELECT a.term_name
  , a.term_acc
  , a.term_type
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id
  , a.gp_symbol
  , a.gp_dbxref_id
  , a.gp_species_id
  , evidence.id AS evidence_id
  , evidence.code
  , evidence.seq_acc
  , evidence.dbxref_id AS evidence_dbxref_id
  , dbxref.xref_dbname AS pub_dbname
  , dbxref.xref_key AS pub_acc 
  FROM (
  (go_prejoined_views.term_j_association_j_gene_product a 
  JOIN go_associations.evidence 
  ON (
  (evidence.association_id = a.id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (evidence.dbxref_id = dbxref.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_j_gene_product

convenience join-view. DIRECT association between a gene product and a term

go_prejoined_views.term_j_association_j_gene_product Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
term_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
  SELECT term.name AS term_name
  , term.acc AS term_acc
  , term.term_type
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , gene_product.symbol AS gp_symbol
  , gene_product.dbxref_id AS gp_dbxref_id
  , gene_product.species_id AS gp_species_id 
  FROM (
  (go_graph.term 
  JOIN go_associations.association 
  ON (
  (term.id = association.term_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_j_gene_product_via_graph

convenience join-view. TRANSITIVE association between a gene product and a term

go_prejoined_views.term_j_association_j_gene_product_via_graph Structure
F-Key Name Type Description
superterm_name character varying(255)
superterm_acc character varying(255)
superterm_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
gp_symbol character varying(128)
gp_dbxref_id integer
gp_species_id integer
  SELECT term.name AS superterm_name
  , term.acc AS superterm_acc
  , term.term_type AS superterm_type
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , gene_product.symbol AS gp_symbol
  , gene_product.dbxref_id AS gp_dbxref_id
  , gene_product.species_id AS gp_species_id 
  FROM (
  (
  (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term1_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_j_species_summary_via_graph

by-species summary of indirectly annotated terms (this view may move to a different module: see go-taxon-views)

go_prejoined_views.term_j_association_j_species_summary_via_graph Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
total bigint
  SELECT term_j_association_j_species_via_graph.term_name
  , term_j_association_j_species_via_graph.term_acc
  , term_j_association_j_species_via_graph.common_name
  , term_j_association_j_species_via_graph.ncbi_taxa_id
  , count
  (*) AS total 
  FROM go_prejoined_views.term_j_association_j_species_via_graph 
  GROUP BY term_j_association_j_species_via_graph.term_name
  , term_j_association_j_species_via_graph.term_acc
  , term_j_association_j_species_via_graph.common_name
  , term_j_association_j_species_via_graph.ncbi_taxa_id;

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_j_species_via_graph

go_prejoined_views.term_j_association_j_species_via_graph Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
term_type character varying(55)
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
species_id integer
common_name character varying(255)
ncbi_taxa_id integer
  SELECT term.name AS term_name
  , term.acc AS term_acc
  , term.term_type
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , species.id AS species_id
  , species.common_name
  , species.ncbi_taxa_id 
  FROM (
  (
  (
  (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term1_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gene_product.species_id = species.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_association_via_graph

TRANSITIVE association

go_prejoined_views.term_j_association_via_graph Structure
F-Key Name Type Description
term_name character varying(255)
term_acc character varying(255)
term_type character varying(55)
distance integer
association_id integer
is_not integer
association_term_id integer
gene_product_id integer
  SELECT term.name AS term_name
  , term.acc AS term_acc
  , term.term_type
  , graph_path.distance
  , association.id AS association_id
  , association.is_not
  , association.term_id AS association_term_id
  , association.term_id AS gene_product_id 
  FROM (
  (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term1_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_j_term

term * term2term * term

go_prejoined_views.term_j_term Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
term_type character varying(55)
id integer
relationship_type_id integer
term1_id integer
term2_id integer
complete integer
relation_acc character varying(255)
parent_acc character varying(255)
parent_name character varying(255)
parent_term_type character varying(55)
  SELECT term.acc
  , term.name
  , term.term_type
  , term2term.id
  , term2term.relationship_type_id
  , term2term.term1_id
  , term2term.term2_id
  , term2term.complete
  , r.acc AS relation_acc
  , p.acc AS parent_acc
  , p.name AS parent_name
  , p.term_type AS parent_term_type 
  FROM (
  (
  (go_graph.term 
  JOIN go_graph.term2term 
  ON (
  (term.id = term2term.term2_id)
  )
  )
  JOIN go_graph.term p 
  ON (
  (term2term.term1_id = p.id)
  )
  )
  JOIN go_graph.term r 
  ON (
  (term2term.relationship_type_id = r.id)
  )
  );

Index - Schema go_prejoined_views

View: go_prejoined_views.term_jt_term

term * graph_path * term

go_prejoined_views.term_jt_term Structure
F-Key Name Type Description
acc character varying(255)
name character varying(255)
term_type character varying(55)
id integer
term1_id integer
term2_id integer
relationship_type_id integer
distance integer
relation_distance integer
relation_acc character varying(255)
parent_acc character varying(255)
parent_name character varying(255)
parent_term_type character varying(55)
  SELECT term.acc
  , term.name
  , term.term_type
  , graph_path.id
  , graph_path.term1_id
  , graph_path.term2_id
  , graph_path.relationship_type_id
  , graph_path.distance
  , graph_path.relation_distance
  , r.acc AS relation_acc
  , p.acc AS parent_acc
  , p.name AS parent_name
  , p.term_type AS parent_term_type 
  FROM (
  (
  (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term2_id)
  )
  )
  JOIN go_graph.term p 
  ON (
  (graph_path.term1_id = p.id)
  )
  )
  JOIN go_graph.term r 
  ON (
  (graph_path.relationship_type_id = r.id)
  )
  );

Index - Schema go_prejoined_views

Schema go_refgenomes_views

View: go_refgenomes_views.avg_max_distance_to_leaf_term_per_refg_within_refg_species

go_refgenomes_views.avg_max_distance_to_leaf_term_per_refg_within_refg_species Structure
F-Key Name Type Description
term_type character varying(55)
code character varying(8)
avg_annot_dist_to_leaf numeric
  SELECT t.term_type
  , e.code
  , avg
  (ld.max_distance) AS avg_annot_dist_to_leaf 
  FROM (
  (
  (
  (go_associations.association a 
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  JOIN go_graph_views.non_root_term t 
  ON (
  (t.id = a.term_id)
  )
  )
  JOIN go_graph_views.max_distance_to_leaf_by_term ld 
  ON (
  (ld.term_id = t.id)
  )
  )
  JOIN go_homology.gene_product_homolset gph 
  ON (
  (gph.gene_product_id = a.gene_product_id)
  )
  )
  GROUP BY e.code
  , t.term_type 
  ORDER BY t.term_type
  , e.code;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.avg_total_genes_by_homolset_and_ontol

measure of congruence, by homolset and ontology. average of the total number of gene_products

go_refgenomes_views.avg_total_genes_by_homolset_and_ontol Structure
F-Key Name Type Description
homolset_id integer
term_type character varying(55)
avg_total_genes numeric
  SELECT s.homolset_id
  , t.term_type
  , avg
  (s.total_gps) AS avg_total_genes 
  FROM (go_refgenomes_views.total_gps_by_homolset_and_term s 
  JOIN go_graph.term t 
  ON (
  (s.term_id = t.id)
  )
  )
  GROUP BY s.homolset_id
  , t.term_type;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.avg_total_transitive_terms_per_refg_gp_for_refspecies

go_refgenomes_views.avg_total_transitive_terms_per_refg_gp_for_refspecies Structure
F-Key Name Type Description
avg_total_transitive_terms numeric
  SELECT avg
  (aa.total_transitive_terms) AS avg_total_transitive_terms 
  FROM (go_annotation_reports.total_transitive_terms_per_gp aa 
  JOIN go_homology.gene_product_homolset gph 
  ON (
  (gph.gene_product_id = aa.gene_product_id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gene_product_in_refg_subset

convenience view: all gene_products in reference_genome subset

go_refgenomes_views.gene_product_in_refg_subset Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
subset_acc character varying(255)
subset_name character varying(255)
  SELECT gene_product_with_subset.id
  , gene_product_with_subset.symbol
  , gene_product_with_subset.dbxref_id
  , gene_product_with_subset.species_id
  , gene_product_with_subset.type_id
  , gene_product_with_subset.full_name
  , gene_product_with_subset.subset_acc
  , gene_product_with_subset.subset_name 
  FROM go_refgenomes_views.gene_product_with_subset 
  WHERE (
  (gene_product_with_subset.subset_acc)::text = 'reference_genome'::text
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gene_product_with_subset

convenience view: gene_product * subset

go_refgenomes_views.gene_product_with_subset Structure
F-Key Name Type Description
id integer
symbol character varying(128)
dbxref_id integer
species_id integer
type_id integer
full_name text
subset_acc character varying(255)
subset_name character varying(255)
  SELECT gp.id
  , gp.symbol
  , gp.dbxref_id
  , gp.species_id
  , gp.type_id
  , gp.full_name
  , s.acc AS subset_acc
  , s.name AS subset_name 
  FROM (
  (go_associations.gene_product gp 
  JOIN go_associations.gene_product_subset gp2s 
  ON (
  (gp.id = gp2s.gene_product_id)
  )
  )
  JOIN go_graph.term s 
  ON (
  (gp2s.subset_id = s.id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gp_outlier_annotation

a gene_product partial 'outlier' annotation with no other gene_products DIRECTLY annotated via non-IEA above OR below it. Note: an annotation is NOT considered an outlier if a different gene_product in the same species is directly annotated above OR below it. Should we change this and only count an annotation as outlier if there are no annotations from different species are above an below it.

go_refgenomes_views.gp_outlier_annotation Structure
F-Key Name Type Description
id integer
gene_product_id integer
homolset_id integer
association_id integer
code character varying(8)
term_id integer
is_not integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_is_obsolete integer
  SELECT gp2h.id
  , gp2h.gene_product_id
  , gp2h.homolset_id
  , a.id AS association_id
  , e.code
  , a.term_id
  , a.is_not
  , t.acc AS term_acc
  , t.name AS term_name
  , t.term_type
  , t.is_obsolete AS term_is_obsolete 
  FROM (
  (
  (go_homology.gene_product_homolset gp2h 
  JOIN go_associations.association a 
  USING (gene_product_id)
  )
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  JOIN go_graph.term t 
  ON (
  (a.term_id = t.id)
  )
  )
  WHERE (
  (NOT 
  (EXISTS 
  (
  SELECT a2.gene_product_id
  , a2.subsuming_term_id
  , a2.id
  , a2.term_id
  , a2.is_not
  , a2.assocdate
  , a2.source_db_id
  , a2.distance
  , a2.code
  , a2.evidence_id
  , gp2h2.id
  , gp2h2.homolset_id 
  FROM (go_refgenomes_views.subsumer_of_noniea_association a2 
  JOIN go_homology.gene_product_homolset gp2h2 
  USING (gene_product_id)
  )
  WHERE (
  (
  (
  (a2.subsuming_term_id = t.id)
  AND (a2.gene_product_id <> a.gene_product_id)
  )
  AND (gp2h2.homolset_id = gp2h.homolset_id)
  )
  AND (a.is_not = 0)
  )
  )
  )
  )
  AND (NOT 
  (EXISTS 
  (
  SELECT a3.gene_product_id
  , a3.subsumed_term_id
  , a3.id
  , a3.term_id
  , a3.is_not
  , a3.assocdate
  , a3.source_db_id
  , a3.distance
  , a3.code
  , a3.evidence_id
  , gp2h3.id
  , gp2h3.homolset_id 
  FROM (go_refgenomes_views.subsumed_by_noniea_association a3 
  JOIN go_homology.gene_product_homolset gp2h3 
  USING (gene_product_id)
  )
  WHERE (
  (
  (
  (a3.subsumed_term_id = t.id)
  AND (a3.gene_product_id <> a.gene_product_id)
  )
  AND (gp2h3.homolset_id = gp2h.homolset_id)
  )
  AND (a.is_not = 0)
  )
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gp_outlier_annotation_full_report

As gp_outlier_annotation, but also includes joins to include full details from other table - gene symbol, gene dbxref

go_refgenomes_views.gp_outlier_annotation_full_report Structure
F-Key Name Type Description
homolset_symbol character varying(128)
is_not integer
code character varying(8)
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_is_obsolete integer
symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
  SELECT DISTINCT hs.symbol AS homolset_symbol
  , a.is_not
  , a.code
  , a.term_acc
  , a.term_name
  , a.term_type
  , a.term_is_obsolete
  , gp.symbol
  , x.xref_dbname
  , x.xref_key 
  FROM (
  (
  (go_refgenomes_views.gp_outlier_annotation a 
  JOIN go_associations.gene_product gp 
  ON (
  (a.gene_product_id = gp.id)
  )
  )
  JOIN go_homology.homolset hs 
  ON (
  (hs.id = a.homolset_id)
  )
  )
  JOIN go_general.dbxref x 
  ON (
  (x.id = gp.dbxref_id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gp_partial_outlier_annotation_nothing_above

a gene_product partial 'outlier' with no other gene_products for same homolset DIRECTLY annotated above it (it may have a different gene_product below) Note: not considered an outlier if a different gp in the same species is directly annotated above it. Should we change this to only consider different species?

go_refgenomes_views.gp_partial_outlier_annotation_nothing_above Structure
F-Key Name Type Description
id integer
gene_product_id integer
homolset_id integer
association_id integer
term_id integer
is_not integer
term_acc character varying(255)
term_name character varying(255)
  SELECT gp2h.id
  , gp2h.gene_product_id
  , gp2h.homolset_id
  , a.id AS association_id
  , a.term_id
  , a.is_not
  , t.acc AS term_acc
  , t.name AS term_name 
  FROM (
  (go_homology.gene_product_homolset gp2h 
  JOIN go_associations.association a 
  USING (gene_product_id)
  )
  JOIN go_graph.term t 
  ON (
  (a.term_id = t.id)
  )
  )
  WHERE (NOT 
  (EXISTS 
  (
  SELECT a2.gene_product_id
  , a2.subsuming_term_id
  , a2.id
  , a2.term_id
  , a2.is_not
  , a2.assocdate
  , a2.source_db_id
  , a2.distance
  , a2.code
  , a2.evidence_id
  , gp2h2.id
  , gp2h2.homolset_id 
  FROM (go_refgenomes_views.subsumer_of_noniea_association a2 
  JOIN go_homology.gene_product_homolset gp2h2 
  USING (gene_product_id)
  )
  WHERE (
  (
  (
  (a2.subsuming_term_id = t.id)
  AND (a2.gene_product_id <> a.gene_product_id)
  )
  AND (gp2h2.homolset_id = gp2h.homolset_id)
  )
  AND (a.is_not = 0)
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.gp_partial_outlier_annotation_nothing_below

a gene_product partial 'outlier' with no other gene_products DIRECTLY annotated below it (it may have a different gene_product above) Note: not considered an outlier if a different gp in the same species is directly annotated below it. Should we change this to only consider different species?

go_refgenomes_views.gp_partial_outlier_annotation_nothing_below Structure
F-Key Name Type Description
id integer
gene_product_id integer
homolset_id integer
association_id integer
term_id integer
is_not integer
term_acc character varying(255)
term_name character varying(255)
  SELECT gp2h.id
  , gp2h.gene_product_id
  , gp2h.homolset_id
  , a.id AS association_id
  , a.term_id
  , a.is_not
  , t.acc AS term_acc
  , t.name AS term_name 
  FROM (
  (go_homology.gene_product_homolset gp2h 
  JOIN go_associations.association a 
  USING (gene_product_id)
  )
  JOIN go_graph.term t 
  ON (
  (a.term_id = t.id)
  )
  )
  WHERE (NOT 
  (EXISTS 
  (
  SELECT a2.gene_product_id
  , a2.subsumed_term_id
  , a2.id
  , a2.term_id
  , a2.is_not
  , a2.assocdate
  , a2.source_db_id
  , a2.distance
  , a2.code
  , a2.evidence_id
  , gp2h2.id
  , gp2h2.homolset_id 
  FROM (go_refgenomes_views.subsumed_by_noniea_association a2 
  JOIN go_homology.gene_product_homolset gp2h2 
  USING (gene_product_id)
  )
  WHERE (
  (
  (
  (a2.subsumed_term_id = t.id)
  AND (a2.gene_product_id <> a.gene_product_id)
  )
  AND (gp2h2.homolset_id = gp2h.homolset_id)
  )
  AND (a.is_not = 0)
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation

go_refgenomes_views.homolset_annotation Structure
F-Key Name Type Description
homolset_id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT gene_product_homolset.homolset_id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM (
  (go_homology.gene_product_homolset 
  JOIN go_associations.association 
  USING (gene_product_id)
  )
  JOIN go_associations.evidence 
  ON (
  (association.id = evidence.association_id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_full

A convenience view joining homolset, association, term and gene_product Example: SELECT * FROM homolset_annotation_full WHERE homolset_symbol='ACTC'; summarises all annotations to this homology set * includes unknowns * direct annotation only

go_refgenomes_views.homolset_annotation_full Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT gph.homolset_id
  , homolset.symbol AS homolset_symbol
  , dbxref.xref_dbname
  , dbxref.xref_key
  , gene_product.symbol
  , gene_product.species_id
  , term.acc AS term_acc
  , term.name AS term_name
  , term.term_type
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM (
  (
  (
  (
  (
  (go_homology.homolset 
  JOIN go_homology.gene_product_homolset gph 
  ON (
  (homolset.id = gph.homolset_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (gene_product.id = gph.gene_product_id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (gene_product.dbxref_id = dbxref.id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  )
  JOIN go_associations.evidence 
  ON (
  (association.id = evidence.association_id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_non_outlier_with_subsumed

go_refgenomes_views.homolset_annotation_non_outlier_with_subsumed Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
subsumed_gene_product_id integer
subsumed_term_id integer
subsumed_association_code character varying(8)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc
  , ha1.gene_product_id AS subsumed_gene_product_id
  , ha1.term_id AS subsumed_term_id
  , ha1.code AS subsumed_association_code 
  FROM go_refgenomes_views.homolset_annotation_full ha
  , (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term2_id)
  )
  )
  WHERE (
  (
  (
  (ha1.gene_product_id <> ha.gene_product_id)
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  AND (ha.term_id = graph_path.term1_id)
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_non_outlier_with_subsumer

go_refgenomes_views.homolset_annotation_non_outlier_with_subsumer Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
subsuming_gene_product_id integer
subsuming_term_id integer
subsuming_association_code character varying(8)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc
  , ha1.gene_product_id AS subsuming_gene_product_id
  , ha1.term_id AS subsuming_term_id
  , ha1.code AS subsuming_association_code 
  FROM go_refgenomes_views.homolset_annotation_full ha
  , (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term1_id)
  )
  )
  WHERE (
  (
  (
  (ha1.gene_product_id <> ha.gene_product_id)
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  AND (ha.term_id = graph_path.term2_id)
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_outlier_full

an annotation outlier is any annotation in a homolset in which the the term annotated (or an ancestor/descendant) is not separately annotated using an experimental evidence code (in the same homolset) you can further constrain this using code; eg WHERE code != 'ISS' current: 700+ results where code!='ISS' http://www.berkeleybop.org/goose?sql_query=select+*+from++homolset_annot...

go_refgenomes_views.homolset_annotation_outlier_full Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc 
  FROM go_refgenomes_views.homolset_annotation_full ha 
  WHERE (
  (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term1_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term2_id)
  )
  )
  WHERE (
  (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying
  ,'ISS'::character varying]
  )::text[]
  )
  )
  AND (ha1.gene_product_id <> ha.gene_product_id)
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  AND (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term2_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term1_id)
  )
  )
  WHERE (
  (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying
  ,'ISS'::character varying]
  )::text[]
  )
  )
  AND (ha1.gene_product_id <> ha.gene_product_id)
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_outlier_full2

go_refgenomes_views.homolset_annotation_outlier_full2 Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc 
  FROM go_refgenomes_views.homolset_annotation_full ha 
  WHERE (
  (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term1_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term2_id)
  )
  )
  WHERE (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying]
  )::text[]
  )
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  AND (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term2_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term1_id)
  )
  )
  WHERE (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying]
  )::text[]
  )
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_outlier_full_by_checking_ancestors

go_refgenomes_views.homolset_annotation_outlier_full_by_checking_ancestors Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc 
  FROM go_refgenomes_views.homolset_annotation_full ha 
  WHERE (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term1_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term2_id)
  )
  )
  WHERE (
  (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying
  ,'ISS'::character varying]
  )::text[]
  )
  )
  AND (ha1.gene_product_id <> ha.gene_product_id)
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_annotation_outlier_old

go_refgenomes_views.homolset_annotation_outlier_old Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT ha.homolset_id
  , ha.homolset_symbol
  , ha.xref_dbname
  , ha.xref_key
  , ha.symbol
  , ha.species_id
  , ha.term_acc
  , ha.term_name
  , ha.term_type
  , ha.term_id
  , ha.gene_product_id
  , ha.is_not
  , ha.assocdate
  , ha.id
  , ha.code
  , ha.association_id
  , ha.dbxref_id
  , ha.seq_acc 
  FROM go_refgenomes_views.homolset_annotation_full ha 
  WHERE (
  (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term1_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term2_id)
  )
  )
  WHERE (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying
  ,'ISS'::character varying]
  )::text[]
  )
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  AND (NOT 
  (ha.term_id IN 
  (
  SELECT graph_path.term2_id 
  FROM (go_optimisations.graph_path 
  JOIN go_refgenomes_views.homolset_annotation ha1 
  ON (
  (ha1.term_id = graph_path.term1_id)
  )
  )
  WHERE (
  (
  (
  (ha1.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying
  ,'ISS'::character varying]
  )::text[]
  )
  )
  AND (ha1.is_not = 0)
  )
  AND (ha1.homolset_id = ha.homolset_id)
  )
  )
  )
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_summary_by_term

go_refgenomes_views.homolset_summary_by_term Structure
F-Key Name Type Description
inferred_term_id integer
gene_product_id integer
homolset_id integer
count bigint
  SELECT graph_path.term1_id AS inferred_term_id
  , association.gene_product_id
  , gene_product_homolset.homolset_id
  , count
  (DISTINCT association.id) AS count 
  FROM (
  (
  (go_homology.homolset 
  JOIN go_homology.gene_product_homolset 
  ON (
  (homolset.id = gene_product_homolset.homolset_id)
  )
  )
  LEFT JOIN go_associations.association 
  ON (
  (association.gene_product_id = gene_product_homolset.gene_product_id)
  )
  )
  LEFT JOIN go_optimisations.graph_path 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  GROUP BY graph_path.term1_id
  , association.gene_product_id
  , gene_product_homolset.homolset_id;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_transitive_annotation

go_refgenomes_views.homolset_transitive_annotation Structure
F-Key Name Type Description
inferred_term_id integer
asserted_term_id integer
relationship_type_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
homolset_id integer
  SELECT graph_path.term1_id AS inferred_term_id
  , graph_path.term2_id AS asserted_term_id
  , graph_path.relationship_type_id
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , gene_product_homolset.homolset_id 
  FROM (
  (go_optimisations.graph_path 
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_homology.gene_product_homolset 
  ON (
  (association.gene_product_id = gene_product_homolset.gene_product_id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.homolset_transitive_annotation_full

A convenience view joining homolset, association, term and gene_product AND graph_path Example: SELECT * FROM homolset_transitive_annotation_full WHERE term_name='hemopoiesis'; summarises all annotations to this homology set * direct + indirect annotations

go_refgenomes_views.homolset_transitive_annotation_full Structure
F-Key Name Type Description
homolset_id integer
homolset_symbol character varying(128)
xref_dbname character varying(55)
xref_key character varying(255)
symbol character varying(128)
species_id integer
term_acc character varying(255)
term_name character varying(255)
term_type character varying(55)
term_id integer
gene_product_id integer
is_not integer
assocdate integer
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT gph.homolset_id
  , homolset.symbol AS homolset_symbol
  , dbxref.xref_dbname
  , dbxref.xref_key
  , gene_product.symbol
  , gene_product.species_id
  , term.acc AS term_acc
  , term.name AS term_name
  , term.term_type
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM (
  (
  (
  (
  (
  (
  (go_homology.homolset 
  JOIN go_homology.gene_product_homolset gph 
  ON (
  (homolset.id = gph.homolset_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (gene_product.id = gph.gene_product_id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (gene_product.dbxref_id = dbxref.id)
  )
  )
  JOIN go_associations.association 
  ON (
  (gene_product.id = association.gene_product_id)
  )
  )
  JOIN go_optimisations.graph_path 
  ON (
  (association.term_id = graph_path.term2_id)
  )
  )
  JOIN go_graph.term 
  ON (
  (graph_path.term1_id = term.id)
  )
  )
  JOIN go_associations.evidence 
  ON (
  (association.id = evidence.association_id)
  )
  )
  ORDER BY homolset.symbol
  , gene_product.species_id;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.refg_total_transitive_terms

total number of terms "coloured" in DAG for all reference genome genes. includes transitive annotations

go_refgenomes_views.refg_total_transitive_terms Structure
F-Key Name Type Description
subset_acc character varying(255)
count bigint
  SELECT gp.subset_acc
  , count
  (DISTINCT graph_path.term1_id) AS count 
  FROM (
  (go_refgenomes_views.gene_product_with_subset gp 
  JOIN go_associations.association 
  ON (
  (gp.id = association.gene_product_id)
  )
  )
  JOIN go_optimisations.graph_path 
  ON (
  (association.term_id = graph_path.term2_id)
  )
  )
  GROUP BY gp.subset_acc;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.refg_with_nd

all reference genome gene_products and their ND annotations.

go_refgenomes_views.refg_with_nd Structure
F-Key Name Type Description
subset_acc character varying(255)
symbol character varying(128)
full_name text
genus character varying(55)
species character varying(255)
common_name character varying(255)
xref_dbname character varying(55)
xref_key character varying(255)
acc character varying(255)
name character varying(255)
term_type character varying(55)
  SELECT gp.subset_acc
  , gp.symbol
  , gp.full_name
  , species.genus
  , species.species
  , species.common_name
  , dbxref.xref_dbname
  , dbxref.xref_key
  , term.acc
  , term.name
  , term.term_type 
  FROM (
  (
  (
  (
  (go_evidence_views.nd_evidence 
  JOIN go_associations.association 
  ON (
  (nd_evidence.association_id = association.id)
  )
  )
  JOIN go_refgenomes_views.gene_product_with_subset gp 
  ON (
  (association.gene_product_id = gp.id)
  )
  )
  JOIN go_general.dbxref 
  ON (
  (gp.dbxref_id = dbxref.id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gp.species_id = species.id)
  )
  )
  JOIN go_graph.term 
  ON (
  (association.term_id = term.id)
  )
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.subsumed_by_association

All associations together with all the terms that are subsumed by the term in that association. intuitively, everything "below" a gene product annotation in a DAG Example: if A is to protein binding, then this view includes actinin binding subsumed_by(AB, PBgene)

go_refgenomes_views.subsumed_by_association Structure
F-Key Name Type Description
subsumed_term_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
distance integer
code character varying(8)
evidence_id integer
  SELECT tc.term2_id AS subsumed_term_id
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id
  , tc.distance
  , e.code
  , e.id AS evidence_id 
  FROM (
  (go_associations.association a 
  JOIN go_optimisations.graph_path tc 
  ON (
  (a.term_id = tc.term2_id)
  )
  )
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  WHERE (a.is_not = 0);

Index - Schema go_refgenomes_views

View: go_refgenomes_views.subsumed_by_noniea_association

as subsumed_by_association, excluding IEA associations.

go_refgenomes_views.subsumed_by_noniea_association Structure
F-Key Name Type Description
subsumed_term_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
distance integer
code character varying(8)
evidence_id integer
  SELECT tc.term2_id AS subsumed_term_id
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id
  , tc.distance
  , e.code
  , e.id AS evidence_id 
  FROM (
  (go_associations.association a 
  JOIN go_optimisations.graph_path tc 
  ON (
  (a.term_id = tc.term2_id)
  )
  )
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  WHERE (
  (
  (e.code)::text <> 'IEA'::text
  )
  AND (a.is_not = 0)
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.subsumer_of_association

All associations together with all the terms that subsume the term in that association intuitively, everything "above" a gene product annotation in a DAG Example: if A is to actinin binding, then this view includes protein binding subsumer_of(PB, ABgene)

go_refgenomes_views.subsumer_of_association Structure
F-Key Name Type Description
subsuming_term_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
distance integer
code character varying(8)
evidence_id integer
  SELECT tc.term1_id AS subsuming_term_id
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id
  , tc.distance
  , e.code
  , e.id AS evidence_id 
  FROM (
  (go_associations.association a 
  JOIN go_optimisations.graph_path tc 
  ON (
  (a.term_id = tc.term1_id)
  )
  )
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  WHERE (a.is_not = 0);

Index - Schema go_refgenomes_views

View: go_refgenomes_views.subsumer_of_noniea_association

as subsumer_of_association, excluding IEA associations.

go_refgenomes_views.subsumer_of_noniea_association Structure
F-Key Name Type Description
subsuming_term_id integer
id integer
term_id integer
gene_product_id integer
is_not integer
assocdate integer
source_db_id integer
distance integer
code character varying(8)
evidence_id integer
  SELECT tc.term1_id AS subsuming_term_id
  , a.id
  , a.term_id
  , a.gene_product_id
  , a.is_not
  , a.assocdate
  , a.source_db_id
  , tc.distance
  , e.code
  , e.id AS evidence_id 
  FROM (
  (go_associations.association a 
  JOIN go_optimisations.graph_path tc 
  ON (
  (a.term_id = tc.term1_id)
  )
  )
  JOIN go_associations.evidence e 
  ON (
  (e.association_id = a.id)
  )
  )
  WHERE (
  (
  (e.code)::text <> 'IEA'::text
  )
  AND (a.is_not = 0)
  );

Index - Schema go_refgenomes_views

View: go_refgenomes_views.total_gps_by_homolset_and_term

total number of gene products annotated to a term within a homolset. total(H,T) = |{g : g in H, annot*(g,T)}| intuitively, this tells us the degree of 'agreement' for a term across a set of orthologous genes. the number itself may not be meaningful without knowing the total number of genes in a homolset - see homolset_term_proportion_gps

go_refgenomes_views.total_gps_by_homolset_and_term Structure
F-Key Name Type Description
homolset_id integer
term_id integer
total_gps bigint
  SELECT gp2h.homolset_id
  , tc.term2_id AS term_id
  , count
  (DISTINCT a.gene_product_id) AS total_gps 
  FROM (
  (go_homology.gene_product_homolset gp2h 
  JOIN go_associations.association a 
  ON (
  (a.gene_product_id = gp2h.gene_product_id)
  )
  )
  JOIN go_optimisations.graph_path tc 
  ON (
  (a.term_id = tc.term2_id)
  )
  )
  GROUP BY gp2h.homolset_id
  , tc.term2_id;

Index - Schema go_refgenomes_views

View: go_refgenomes_views.trusted_evidence

/tr>
go_refgenomes_views.trusted_evidence Structure
F-Key Name Type Description
id integer
code character varying(8)
association_id integer
dbxref_id integer
seq_acc character varying(255)
  SELECT evidence.id
  , evidence.code
  , evidence.association_id
  , evidence.dbxref_id
  , evidence.seq_acc 
  FROM go_associations.evidence 
  WHERE (
  (evidence.code)::text <> ALL 
  (
  (ARRAY['IEA'::character varying
  ,'IGC'::character varying
  ,'NAS'::character varying
  ,'ND'::character varying
  ,'NR'::character varying
  ,'RCA'::character varying
  ,'TAS'::character varying]
  )::text[]
  )
  );

Index - Schema go_refgenomes_views

Schema go_sequence

Table: go_sequence.gene_product_seq

relationship between gene_product and seq is potentially many to many, although in practice each gene product may only have one seq, depending on data population method. If this link is for the representatibe sequence for a gene product, is_primary_seq should be set to true (=1) (a gene_product should only have one seq that is marked is_primary_seq, although this is not- enforced. the idea is that there may be different seqs for a product - allelic variations, variant spliceforms, but only one is the "representative" seq) This table is typically sourced from the gp2protein contributed data file: http://www.geneontology.org/gp2protein

go_sequence.gene_product_seq Structure
F-Key Name Type Description
go_associations.gene_product.id gene_product_id integer NOT NULL
go_sequence.seq.id seq_id integer NOT NULL
is_primary_seq integer If this link is for the representatibe sequence for a gene product, is_primary_seq should be set to true (=1)
gpseq1 gene_product_id gpseq2 seq_id gpseq3 seq_id, gene_product_id

Index - Schema go_sequence

Table: go_sequence.seq

A representative DNA or amino acid sequence for a gene_product; will typically be amino acid. This table is modeled after the BioPerl Bio::PrimarySeq model

go_sequence.seq Structure
F-Key Name Type Description
id serial PRIMARY KEY
display_id character varying(64) UNIQUE#1 the primary label used for identifying the sequence for humans. Not guaranteed to be globally unique. typically corresponds to the first part of a FASTA header
description character varying(255) textual information for humans concerning this sequence. typically corresponds to the part after the ID in the FASTA header
seq text residue sequence: standard IUPAC alphabetic codes are used
seq_len integer number of residues in sequence. should always correspond to length(seq), where seq is populated
md5checksum character varying(32) UNIQUE#1 result of md5(seq), where md5 is the standard MD5 checksum algorithm. see GO::Model::Seq for calculation almost 100% guaranteed to be unique for any sequence of symbols representing the biopolymer
moltype character varying(25) DNA or AA
timestamp integer

Tables referencing this one via Foreign Key Constraints:

seq1 display_id seq2 md5checksum

Index - Schema go_sequence

Table: go_sequence.seq_dbxref

linking table for external identifiers for a sequence seq_dbxref is derived from the dbxrefs in the source sequence file. For example, if the source is a UniProt file, this table will reflect the DR lines if the source is a FASTA file from UniProt, the DB:ACC parts of the fasta header will be used to populate this table typically a seq entry will be something like a protein/ polypeptide - ie something that can be annotated with GO; the dbxref could be for entities in other databases that may only be tangentially related to the protein for example: mRNA records, genomic records, OMIM IDs, etc

go_sequence.seq_dbxref Structure
F-Key Name Type Description
go_sequence.seq.id seq_id integer UNIQUE#1 NOT NULL
go_general.dbxref.id dbxref_id integer UNIQUE#1 NOT NULL
seqx0 seq_id seqx1 dbxref_id seqx2 seq_id, dbxref_id

Index - Schema go_sequence

Table: go_sequence.seq_property

seq can have various properties attached to it. not currently used

go_sequence.seq_property Structure
F-Key Name Type Description
id serial PRIMARY KEY
go_sequence.seq.id seq_id integer UNIQUE#1 NOT NULL
property_key character varying(64) UNIQUE#1 NOT NULL
property_val character varying(255) UNIQUE#1 NOT NULL
seqp0 seq_id seqp1 property_key seqp2 property_val

Index - Schema go_sequence

Schema go_stats_views

View: go_stats_views.annotated_gp_total_by_code

go_stats_views.annotated_gp_total_by_code Structure
F-Key Name Type Description
code character varying(8)
total bigint
  SELECT association_evidence.code
  , count
  (DISTINCT association_evidence.gene_product_id) AS total 
  FROM go_prejoined_views.association_evidence 
  GROUP BY association_evidence.code;

Index - Schema go_stats_views

View: go_stats_views.gene_product_count2

go_stats_views.gene_product_count2 Structure
F-Key Name Type Description
term_id integer
code character varying(8)
total bigint
  SELECT gene_product_count.term_id
  , gene_product_count.code
  , sum
  (gene_product_count.product_count) AS total 
  FROM go_optimisations.gene_product_count 
  GROUP BY gene_product_count.term_id
  , gene_product_count.code;

Index - Schema go_stats_views

View: go_stats_views.implied_annotation

go_stats_views.implied_annotation Structure
F-Key Name Type Description
term_id integer
id integer
gene_product_id integer
  SELECT DISTINCT p.term1_id AS term_id
  , a.id
  , a.gene_product_id 
  FROM (go_optimisations.graph_path p 
  JOIN go_associations.association a 
  ON (
  (p.term2_id = a.term_id)
  )
  )
  WHERE (a.is_not = 0);

Index - Schema go_stats_views

View: go_stats_views.implied_negative_annotation

go_stats_views.implied_negative_annotation Structure
F-Key Name Type Description
term_id integer
id integer
gene_product_id integer
  SELECT DISTINCT p.term1_id AS term_id
  , a.id
  , a.gene_product_id 
  FROM (go_optimisations.graph_path p 
  JOIN go_associations.association a 
  ON (
  (p.term2_id = a.term_id)
  )
  )
  WHERE (a.is_not = 1);

Index - Schema go_stats_views

View: go_stats_views.term_correlation_summary

go_stats_views.term_correlation_summary Structure
F-Key Name Type Description
count bigint
term1_id integer
term2_id integer
  SELECT count
  (DISTINCT a1.gene_product_id) AS count
  , a1.term_id AS term1_id
  , a2.term_id AS term2_id 
  FROM (go_stats_views.implied_annotation a1 
  JOIN go_stats_views.implied_annotation a2 
  USING (gene_product_id)
  )
  GROUP BY a1.term_id
  , a2.term_id;

Index - Schema go_stats_views

View: go_stats_views.term_correlation_via_transitive_annotation

go_stats_views.term_correlation_via_transitive_annotation Structure
F-Key Name Type Description
gene_product_id integer
association1_id integer
association2_id integer
term1_id integer
term2_id integer
  SELECT a1.gene_product_id
  , a1.id AS association1_id
  , a2.id AS association2_id
  , a1.term_id AS term1_id
  , a2.term_id AS term2_id 
  FROM (go_stats_views.implied_annotation a1 
  JOIN go_stats_views.implied_annotation a2 
  USING (gene_product_id)
  );

Index - Schema go_stats_views

Schema go_taxon_views

View: go_taxon_views.annotated_species

go_taxon_views.annotated_species Structure
F-Key Name Type Description
id integer
ncbi_taxa_id integer
common_name character varying(255)
lineage_string text
genus character varying(55)
species character varying(255)
parent_id integer
left_value integer
right_value integer
taxonomic_rank character varying(255)
  SELECT species.id
  , species.ncbi_taxa_id
  , species.common_name
  , species.lineage_string
  , species.genus
  , species.species
  , species.parent_id
  , species.left_value
  , species.right_value
  , species.taxonomic_rank 
  FROM go_associations.species 
  WHERE (EXISTS 
  (
  SELECT asi.species_id 
  FROM go_taxon_views.annotated_species_id asi 
  WHERE (asi.species_id = species.id)
  )
  );

Index - Schema go_taxon_views

View: go_taxon_views.annotated_species_id

go_taxon_views.annotated_species_id Structure
F-Key Name Type Description
species_id integer
  SELECT DISTINCT gene_product.species_id 
  FROM go_associations.gene_product;

Index - Schema go_taxon_views

View: go_taxon_views.annotated_species_lacks_term

annotated_species_lacks_term is true if and only if species_lacks_term is true and species is annotated to at least one term (via gene_product)

go_taxon_views.annotated_species_lacks_term Structure
F-Key Name Type Description
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
species_id integer
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT species_lacks_term.genus
  , species_lacks_term.species
  , species_lacks_term.common_name
  , species_lacks_term.ncbi_taxa_id
  , species_lacks_term.species_id
  , species_lacks_term.id
  , species_lacks_term.name
  , species_lacks_term.term_type
  , species_lacks_term.acc
  , species_lacks_term.is_obsolete
  , species_lacks_term.is_root
  , species_lacks_term.is_relation 
  FROM go_taxon_views.species_lacks_term 
  WHERE (EXISTS 
  (
  SELECT gene_product.id
  , gene_product.symbol
  , gene_product.dbxref_id
  , gene_product.species_id
  , gene_product.type_id
  , gene_product.full_name 
  FROM go_associations.gene_product 
  WHERE (species_lacks_term.species_id = gene_product.species_id)
  )
  );

Index - Schema go_taxon_views

View: go_taxon_views.gene_product_count_by_inner_taxon

go_taxon_views.gene_product_count_by_inner_taxon Structure
F-Key Name Type Description
id integer
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
term_id integer
product_count bigint
  SELECT it.id
  , it.genus
  , it.species
  , it.common_name
  , it.ncbi_taxa_id
  , gpc.term_id
  , sum
  (gpc.product_count) AS product_count 
  FROM (
  (go_optimisations.gene_product_count gpc 
  JOIN go_associations.species tt 
  ON (
  (tt.id = gpc.species_id)
  )
  )
  JOIN go_associations.species it 
  ON (
  (
  (tt.left_value >= it.left_value)
  AND (tt.left_value <= it.right_value)
  )
  )
  )
  GROUP BY it.id
  , it.genus
  , it.species
  , it.common_name
  , it.ncbi_taxa_id
  , gpc.term_id;

Index - Schema go_taxon_views

View: go_taxon_views.species_has_term

species_has_term if and only if there exists a gene_product for that species annotated_to* that term, (where annotated_to* indicates inclusion of transitive annotations, based on graph_path)

go_taxon_views.species_has_term Structure
F-Key Name Type Description
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
species_id integer
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT DISTINCT species.genus
  , species.species
  , species.common_name
  , species.ncbi_taxa_id
  , species.id AS species_id
  , term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM (
  (go_graph.term 
  JOIN go_optimisations.gene_product_count 
  ON (
  (term.id = gene_product_count.term_id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gene_product_count.species_id = species.id)
  )
  );

Index - Schema go_taxon_views

View: go_taxon_views.species_has_term_d

as species_has_term, but does not use pre-computed table (thus slower)

go_taxon_views.species_has_term_d Structure
F-Key Name Type Description
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
species_id integer
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT DISTINCT species.genus
  , species.species
  , species.common_name
  , species.ncbi_taxa_id
  , species.id AS species_id
  , term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM (
  (
  (
  (go_graph.term 
  JOIN go_optimisations.graph_path 
  ON (
  (term.id = graph_path.term1_id)
  )
  )
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  )
  JOIN go_associations.species 
  ON (
  (gene_product.species_id = species.id)
  )
  );

Index - Schema go_taxon_views

View: go_taxon_views.species_lacks_term

species_lacks_term if and only if there does not exist a gene_product for that species annotated_to* that term, (where annotated_to* indicates inclusion of transitive annotations, based on graph_path)

go_taxon_views.species_lacks_term Structure
F-Key Name Type Description
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
species_id integer
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT DISTINCT species.genus
  , species.species
  , species.common_name
  , species.ncbi_taxa_id
  , species.id AS species_id
  , term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM go_graph.term
  , go_associations.species 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT gene_product_count.term_id
  , gene_product_count.code
  , gene_product_count.speciesdbname
  , gene_product_count.species_id
  , gene_product_count.product_count 
  FROM go_optimisations.gene_product_count 
  WHERE (
  (gene_product_count.species_id = species.id)
  AND (term.id = gene_product_count.term_id)
  )
  )
  )
  );

Index - Schema go_taxon_views

View: go_taxon_views.species_lacks_term_d

same as species_lacks_term, bypasses pre-computed results

go_taxon_views.species_lacks_term_d Structure
F-Key Name Type Description
genus character varying(55)
species character varying(255)
common_name character varying(255)
ncbi_taxa_id integer
species_id integer
id integer
name character varying(255)
term_type character varying(55)
acc character varying(255)
is_obsolete integer
is_root integer
is_relation integer
  SELECT DISTINCT species.genus
  , species.species
  , species.common_name
  , species.ncbi_taxa_id
  , species.id AS species_id
  , term.id
  , term.name
  , term.term_type
  , term.acc
  , term.is_obsolete
  , term.is_root
  , term.is_relation 
  FROM go_graph.term
  , go_associations.species 
  WHERE (NOT 
  (EXISTS 
  (
  SELECT graph_path.id
  , graph_path.term1_id
  , graph_path.term2_id
  , graph_path.relationship_type_id
  , graph_path.distance
  , graph_path.relation_distance
  , association.id
  , association.term_id
  , association.gene_product_id
  , association.is_not
  , association.assocdate
  , association.source_db_id
  , gene_product.id
  , gene_product.symbol
  , gene_product.dbxref_id
  , gene_product.species_id
  , gene_product.type_id
  , gene_product.full_name 
  FROM (
  (go_optimisations.graph_path 
  JOIN go_associations.association 
  ON (
  (graph_path.term2_id = association.term_id)
  )
  )
  JOIN go_associations.gene_product 
  ON (
  (association.gene_product_id = gene_product.id)
  )
  )
  WHERE (
  (gene_product.species_id = species.id)
  AND (term.id = graph_path.term1_id)
  )
  )
  )
  );

Index - Schema go_taxon_views