Difference between revisions of "Chado Manual"

From GMOD
Jump to: navigation, search
m (Naming Conventions)
m
Line 230: Line 230:
 
must always by >0 for AA sequences';
 
must always by >0 for AA sequences';
 
</sql>
 
</sql>
 
 
  
  
Line 239: Line 237:
 
procedural SQL. Currently only PostgreSQL dialect is supported. The
 
procedural SQL. Currently only PostgreSQL dialect is supported. The
 
psql implementations are stored in *.plpgsql files.
 
psql implementations are stored in *.plpgsql files.
 +
 +
 +
[[Category:To Do]]
 +
[[Category:Chado]]

Revision as of 17:01, 14 February 2007

Introduction

A Feature is a Sequence

Chado does not distinguish between a sequence and a sequence feature, on the theory that a feature is a piece of a sequence, and a piece of a sequence is a sequence. Both are represented as a row in the feature table.

Feature types

Feature types are taken from the SO controlled vocabulary (see also Controlled Vocabulary section in this document). A selection of Chado-relevant types from SO are shown below:


Modules

We organised the tables into distinct modular components with tightly defined dependencies. This is recogised as good software engineering practice, it allows different software components to focus on the specific data compartments required. It allows for extensibility and schema evolution within specific modules without disrupting the rest of the schema. Finally, it allows for a mix and match approach - it is the authors' hope that the schema modules will be adopted by other model organism and bioinformatics groups; these groups may want to swap in their own table variants within specific modules, or add modules of their own.


Module Dependencies

general: NO DEPENDENCIES organism: general pub: general cv: general pub sequence: cv general pub genetic sequence cv general pub expression: sequence cv general pub map: sequence cv general pub


Inter-module Linking Tables

These can be thought of as floating outside of the respective modules they bridge, although they are generally bundled with one or the other module.

REVIEW - Not complete

Module Module Linking Table
sequence expression feature_expression
cv expression expression_cvterm
pub expression expression_pub
cv genetic phenotype_cvterm
sequence genetic feature_genotype
general organism organism_dbxref
general pub pub_dbxref
general pub journal_dbxref
pub sequence featureprop_pub
general sequence feature_dbxref
cv sequence feature_cvterm
organism sequence feature_organism
general sequence feature_synonym
general sequence gene_synonym


Chado Naming Conventions

Case sensitivity

We use lowercase in all tables and column names - DBMSs differ in how they treat case sensitivity. For example, Oracle will automatically capitalize everything. So it's best to be neutral and use lowercase.


Table names

In table names, we use underscores for linking tables; e.g. feature_dbxref is a linking table between feature and dbxref.

Where a table name is a noun phrase rather than a single noun, we concatenate the words together. for instance the table for describing feature properties is called featureprop. It could be argued this is harder to read, but it does allow consistent usage of underscores as above. FeatureProp could be used where it is known the DBMS is case insensitive.


Column names

In column names, we also use concatenated noun phrases, except in the case of primary or foreign keys, e.g. dbxref_id.

We try to keep column names unique where appropriate, which is useful for large join statements or views, in avoiding column name clash between different tables. The convention is to use an abbreviated form of the table name plus a noun describing the column, for instance fmin in the feature table. By consistently using abbreviated forms we stop column names getting too big (many DBMSs will complain about long column names).


Primary and foreign key names

We use the same column name for primary and foreign key columns - very useful for NATURAL JOIN statements.

Design Patterns

1.1.1 Module System


Module Metadata

View Layers

Views can be thought of as virtual tables. They provide a powerful abstraction layer over the database. All views should be portable across all DBMSs

Views in chado are defined on a per module basis. View definitions are maintained in the chado/modules/MODULE-NAME/views directory.

Included in the view directory are report views. These can usually be found in a file called chado/modules/MODULE-NAME/views/MODULE-NAME-report.sql

Collections of view definitions are bundled into packages, each package is a .sql file.


Inter-schema Bridges

GODB Bridge


BioSQL Bridge


DBMS Functions

DBMS Functions in Chado are entirely optional.

Functions in chado are defined on a per module basis. Function definitions are maintained in the chado/modules/MODULE-NAME/functions directory.

Collections of function definitions are bundled into packages. Each package comes with an interface descriptions and one or more implementations.


Function Interface Definitions

The interface descriptions are stored in a *.sqlapi file. The syntax used is a variant of SQL and is intended primarily as a consistent way of providing information for human, although it should be parseable by software.

Here is an example, taken from the top of the chado/modules/sequence/functions/subsequence.sqlapi package. This package provides basic subsequencing functions. It has dependencies on two other function packages, declared at the top of the file. The package declares multiple functions, only the first of which is show here, a function for extracting subsequences from the sequence of a feature.

<sql> IMPORT reverse_complement(TEXT) FROM 'sequtil'; IMPORT get_feature_relationship_type_id(TEXT) FROM 'sequence-cv-helper';


-- basic subsequencing functions --


DECLARE FUNCTION subsequence( srcfeature_id INT REFERENCES feature(feature_id), fmin INT, fmax INT, strandINT )

RETURNS TEXT;

COMMENT ON FUNCTION subsequence(INT,INT,INT,INT) IS 'extracts a subsequence from a feature referenced by srcfeature_id, within the interbase boundaries determined by fmin and fmax, reverse complementing if strand = -1. The sequence can be DNA or AA. Strand must always by >0 for AA sequences'; </sql>


Function Implementations

The goal is to provide implementations for different dialects of procedural SQL. Currently only PostgreSQL dialect is supported. The psql implementations are stored in *.plpgsql files.