GO Database Schema Autodoc

This is the GO Database Schema documentation, in Autodoc format. Everything on this page has been 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 generated on 2009-07-20

• go_annotation_reports
• annotated_publication_total
• annotated_publication_total_by_evidence_code_non_additive
• annotated_total_gps_by_evidence_code_non_additive
• association_contradiction
• association_contradiction_count_by_ontology
• association_contradiction_direct
• association_contradiction_direct_count_by_ontology
• association_count_by_association_qualifier
• association_count_by_association_qualifier_and_dbname
• association_total_by_evidence_code
• association_total_by_evidence_code_and_species
• avg_total_annotations_per_gp_by_db
• avg_total_nonroot_annotations_per_gp_by_db
• avg_total_nonroot_pubs_per_gp_by_db
• avg_total_nonroot_terms_per_gp_by_db
• avg_total_nonroot_transitive_terms_per_gp
• avg_total_nonroot_transitive_terms_per_gp_by_db
• avg_total_pubs_per_gp_by_db
• avg_total_terms_per_gp_by_db
• avg_total_transitive_terms_per_gp
• avg_total_transitive_terms_per_gp_by_db
• evidence_dbxref_summary
• evidence_pub_dbxref_summary
• gene_product_dbxref_summary
• iea_annotated_total_gps
• iea_or_iss_annotated_total_gps
• non_iea_annotated_total_gps
• non_iea_annotated_total_gps_by_dbname
• non_iea_or_iss_annotated_total_gps
• ont_association_count_by_association_qualifier
• seq_dbxref_summary
• term_association_count_by_association_qualifier
• term_association_count_by_fraction_type
• term_association_count_by_fraction_type_and_evidence
• term_dbxref_summary
• total_annotated_entities_by_dbname_and_type
• total_annotations_per_gp
• total_gps_by_dbname
• total_nonroot_annotations_per_gp
• total_nonroot_pubs_per_gp
• total_nonroot_terms_per_gp
• total_nonroot_transitive_terms_per_gp
• total_nonroot_transitive_terms_per_gp_pair
• total_pubs
• total_pubs_per_gp
• total_terms_per_gp
• total_transitive_terms_per_gp
• total_transitive_terms_per_gp_ont
• go_associations
• association
• association_property
• association_qualifier
• association_species_qualifier
• evidence
• evidence_dbxref
• gene_product
• gene_product_subset
• gene_product_synonym
• species
• go_audit
• instance_data
• source_audit
• term_audit
• go_evidence_views
• association_evidence_with
• association_inference_candidate_pair
• db_evidence_summary
• ic_evidence
• iss_annotation_to_nas_direct
• iss_annotation_to_nas_direct_without
• nd_evidence
• stale_ic_ipr
• stale_iss_annotation
• go_general
• db
• dbxref
• go_graph
• relation_composition
• relation_properties
• term
• term2term
• go_graph_views
• avg_max_distance_to_leaf_term_by_db
• avg_max_distance_to_leaf_term_by_db_and_ontology
• avg_max_distance_to_leaf_term_by_db_and_species
• distance_to_leaf_stats_by_term
• distance_to_root_stats_by_term
• leaf_node
• max_distance_to_leaf_by_term
• max_distance_to_root_by_term
• max_max_distance_to_root_by_term
• non_root_term
• path_to_leaf
• path_to_root
• root_term
• term_ancestor
• term_descendent
• term_having_max_delta_distance_to_root
• term_having_max_max_distance_to_root
• term_having_most_paths_to_root
• total_paths_to_root_by_term
• transitive_association
• go_homology
• gene_product_ancestor
• gene_product_homology
• gene_product_homolset
• homolset
• go_meta
• term2term_metadata
• term_dbxref
• term_definition
• term_subset
• term_synonym
• go_obd_bridge
• asserted_link
• implied_link
• node
• node_max_depth
• go_optimisations
• gene_product_count
• graph_path
• go_prejoined_views
• association_evidence
• association_j_evidence
• association_j_evidence_j_gene_product
• evidence_j_evidence_dbxref_j_dbxref
• gene_product_j_dbxref
• gene_product_j_dbxref_via_seq
• gene_product_j_gene_product_synonym
• gene_product_with_term_pair_via_graph
• term_j_association
• term_j_association_j_evidence_j_gene_product
• term_j_association_j_gene_product
• term_j_association_j_gene_product_via_graph
• term_j_association_j_species_summary_via_graph
• term_j_association_j_species_via_graph
• term_j_association_via_graph
• term_j_term
• term_jt_term
• go_refgenomes_views
• avg_max_distance_to_leaf_term_per_refg_within_refg_species
• avg_total_genes_by_homolset_and_ontol
• avg_total_transitive_terms_per_refg_gp_for_refspecies
• gene_product_in_refg_subset
• gene_product_with_subset
• gp_outlier_annotation
• gp_outlier_annotation_full_report
• gp_partial_outlier_annotation_nothing_above
• gp_partial_outlier_annotation_nothing_below
• homolset_annotation
• homolset_annotation_full
• homolset_annotation_non_outlier_with_subsumed
• homolset_annotation_non_outlier_with_subsumer
• homolset_annotation_outlier_full
• homolset_annotation_outlier_full2
• homolset_annotation_outlier_full_by_checking_ancestors
• homolset_annotation_outlier_old
• homolset_summary_by_term
• homolset_transitive_annotation
• homolset_transitive_annotation_full
• refg_total_transitive_terms
• refg_with_nd
• subsumed_by_association
• subsumed_by_noniea_association
• subsumer_of_association
• subsumer_of_noniea_association
• total_gps_by_homolset_and_term
• trusted_evidence
• go_sequence
• gene_product_seq
• seq
• seq_dbxref
• seq_property
• go_stats_views
• annotated_gp_total_by_code
• gene_product_count2
• implied_annotation
• implied_negative_annotation
• term_correlation_summary
• term_correlation_via_transitive_annotation
• go_taxon_views
• annotated_species
• annotated_species_id
• annotated_species_lacks_term
• gene_product_count_by_inner_taxon
• species_has_term
• species_has_term_d
• species_lacks_term
• species_lacks_term_d
• public

Schema go_annotation_reports

View: go_annotation_reports.annotated_publication_total

ungrouped: single number

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, ..)

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

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

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

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

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

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

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

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

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

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?

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 ***

Tables referencing this one via Foreign Key Constraints:

• go_associations.association_property

• go_associations.association_qualifier

• go_associations.association_species_qualifier

• go_associations.evidence

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)

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)

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"

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)

Tables referencing this one via Foreign Key Constraints:

• go_associations.evidence_dbxref

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)

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)

Tables referencing this one via Foreign Key Constraints:

• go_associations.association

• go_associations.gene_product_subset

• go_associations.gene_product_synonym

• go_homology.gene_product_ancestor

• go_homology.gene_product_homology

• go_homology.gene_product_homolset

• go_homology.homolset

• go_sequence.gene_product_seq

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*)

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)

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")

Tables referencing this one via Foreign Key Constraints:

• go_associations.association_species_qualifier

• go_associations.gene_product

• go_homology.homolset

• go_optimisations.gene_product_count

Index - Schema go_associations

Schema go_audit

Table: go_audit.instance_data

metadata on this particular instance/build of the GO database

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

Index - Schema go_audit

Table: go_audit.term_audit

not in use

Index - Schema go_audit

Schema go_evidence_views

View: go_evidence_views.association_evidence_with

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

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

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

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

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

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

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

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

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

Tables referencing this one via Foreign Key Constraints:

• go_associations.association

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)

Tables referencing this one via Foreign Key Constraints:

• go_associations.evidence

• go_associations.evidence_dbxref

• go_associations.gene_product

• go_homology.homolset

• go_meta.term_dbxref

• go_meta.term_definition

• go_sequence.seq_dbxref

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.

Index - Schema go_graph

Table: go_graph.relation_properties

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)

Tables referencing this one via Foreign Key Constraints:

• go_associations.association

• go_associations.association_property

• go_associations.association_qualifier

• go_associations.gene_product

• go_associations.gene_product_subset

• go_audit.term_audit

• go_graph.relation_composition

• go_graph.relation_properties

• go_graph.term2term

• go_homology.gene_product_homology

• go_homology.homolset

• go_meta.term2term_metadata

• go_meta.term_dbxref

• go_meta.term_definition

• go_meta.term_subset

• go_meta.term_synonym

• go_optimisations.gene_product_count

• go_optimisations.graph_path

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)

Index - Schema go_graph

Schema go_graph_views

View: go_graph_views.avg_max_distance_to_leaf_term_by_db

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

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

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)

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)

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

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

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

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

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

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

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

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

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

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

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?

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?

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

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

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

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

Index - Schema go_homology

Table: go_homology.gene_product_homology

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

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

Tables referencing this one via Foreign Key Constraints:

• go_homology.gene_product_homolset

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?

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.

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)

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*)

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.

Index - Schema go_meta

Schema go_obd_bridge

View: go_obd_bridge.asserted_link

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

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

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

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>>

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+closure] 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.

Index - Schema go_optimisations

Schema go_prejoined_views

View: go_prejoined_views.association_evidence

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

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

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

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

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

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

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

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.

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

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

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

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)

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

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

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

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

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

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

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

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

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

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.

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

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?

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?

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

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

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

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

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_annotation_outlier_full+where+code%3D%27ISS%27+order+by+term_type%2Chomolset_symbol%2Cxref_dbname%3B&limit=0&mirror=1

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