Chado Tutorial 2012
From GMOD
Revision as of 23:32, 8 October 2012 by Girlwithglasses (Talk | contribs)
This Chado tutorial was presented by Scott Cain as part of the 2012 GMOD Summer School.
To follow along with the tutorial, you will need to use AMI ID: ami-ad2f98c4, name: GMOD 2012 final day, available in the US East (N. Virginia) region. See the GMOD Cloud Tutorial for information on how to get this AMI.
Chado is the database schema of the GMOD project. This session introduces database concepts, and then provides an overview of Chado's design and architecture, and then goes into detail about how to use a Chado database.
Contents
Theory
Introduction
Database Terminology
Or six years of school in 15 minutes or less.
What's a database?
- Chado is a schema, a database design - a blueprint for a database containing genomic data
- Distinct from
- Database Management System (DBMS)
- Software system for storing databases
- e.g., Oracle, PostgreSQL, MySQL
- Database, a very loose term
- Any set of organized data that is readable by a computer
- A web site with database driven content, e.g., FlyBase
- Schema + DBMS + Data
- Database Management System (DBMS)
SQL
SQL is a standardized query language for defining and manipulating databases. Chado uses it. SQL is supported by all major DBMSs.
FlyBase Field Mapping Tables shows some example SQL that queries the FlyBase Chado database. (Caveat: FlyBase sometimes uses Chado in ways that no other organizations do.)
Will SQL be on the test?
No, we aren't going to teach in-depth SQL in this course but we will use it in examples and show how to write queries in Chado.
You can do basics with Chado without knowing SQL. Many common tasks already have scripts written for them. However, as you get more into using Chado, you will find that a working knowledge of SQL is necessary.
Why Chado?
- Integration
- Supports many types of data, integrates with many tools
- Modular
- Use only what you need, ignore the rest
- Extensible
- Write your own modules and properties
- Widely used
- FlyBase - Chado started here, large diverse dataset and organization
- Xenbase - Smaller, but with several IT staff
- ParameciumDB - Smaller still, complete GMOD shop, including Chado
- IGS - Large-scale annotation/comparative data in Chado, more than a dozen active developers
- Plus AphidBase, BeeBase, BeetleBase, BovineBase, ...
- Great Community of Support
Chado Architecture: Modules
The Chado schema is built with a set of modules. A Chado module is a set of database tables and relationships that stores information about a well-defined area of biology, such as sequence or attribution.
(Also available as a PowerPoint animation)
Arrows are dependencies between modules. Dependencies indicate one or more foreign keys linking modules.
- General - Identifying things within the DB to the outside world, and identifying things from other databases.
- Controlled Vocabulary (cv) - Controlled vocabularies and ontologies
- Publication (pub) - Publications and attribution
- Organism - Describes species; pretty simple. Phylogeny module stores relationships.
- Sequence - Genomic features and things that can be tied to or descend from genomic features.
- Map - Maps without sequence
- Genetic - Genetic data and genotypes
- Companalysis - Storage of Computational sequence analysis. The key concept is that the results of a computational analysis can be interpreted or described as a sequence feature.
Extensible
These modules have been contributed to Chado by users who developed them.
- Mage - Microarray data
- Stock - Specimens and biological collections
- Natural Diversity - geolocation, phenotype, genotype
- Plus property tables in many modules.
Plus
- Audit - Database audit trail
- Expression - Summaries of RNA and protein expression
- Library - Descriptions of molecular libraries
- Phenotype - Phenotypic data
- Phylogeny - Organisms and phylogenetic trees
Module Caveats
All modules are blessed, but some modules are more blessed than others.
The General, CV, Publication, Organism, Sequence and Companalysis modules are all widely used and cleanly designed. After that modules become less frequently used (Stock, Expression, Phenotype, Mage). Also several modules are not as cleanly separated as we would like them to be. Phenotypic data is spread over several modules. Organism and Phylogeny overlap. CMap is all about maps, but it does not use the Map module.
From Jeff Bowes, at XenBase:
As for Chado, we are more Chadoish than exactly Chado. We use the core modules with few changes - feature, cv, general, analysis. Although I prefer to add columns to tables when it is reasonable and limit the use of property tables (too many left outer joins). We use a slightly modified version of the phylogeny module. We have developed completely different modules for community, literature, anatomy and gene expression. If there is a PATO compatible Chado Phenotype solution we'd prefer to go with that. Although, it might cause problems that we have a separate anatomy module as opposed to using cvterm to store anatomy.
In other words the ideal is good, but implementation and usage is uneven. See the 2008 GMOD Community survey for what gets used.
Exploring the schema
Rather than simply listing out the modules and what is stored in them, we'll take a data-centric view and imagine what we want to store in our database, then learn the Chado way of storing it.
During this course you'll be working with genome annotation data from MAKER. We'll simplify this and start by considering that we have annotation on chromosomes that we want to store in our database. These are the sort of things we want to store:
- chromosomes
- genes
- gene predictions
- tRNAs
- BLAST matches
You may have worked with databases in the past where each type of thing you want to store is given its own table. That is, we'd have a table for genes, one for chromosomes, tRNAs, etc. The problem with this sort of design is that, as you encounter new types of things, you have to create new tables to store them. Also, many of these 'thing' tables are going to look very much alike.
Chado is what is a generic schema which, in effect, means that data are abstracted wherever possible to prevent duplication in both the design and data itself. So, instead of one table for each type of 'thing', we just have one table to hold 'things', regardless of their types. In the Chado world these are known as 'features'.
This brings us to the Sequence Module, which contains the central feature table.
Sequence Module
The sequence module is used to manage genomic features.
Features
Chado defines a feature to be a region of a biological polymer (typically a DNA, RNA, or a polypeptide molecule) or an aggregate of regions on this polymer. A region can be an entire chromosome, or a junction between two bases. Features are typed according to the Sequence Ontology (SO), they can be localized relative to other features, and they can form part-whole and other relationships with other features.
Features are stored in the feature table.
| Table: feature | |||||
|---|---|---|---|---|---|
| feature_id | name | uniquename | type_id | is_analysis | ... |
Within this feature table we can store all types of features and keep track of their type with the type_id field. It's conceivable to store the named value of each type in this field, like 'gene', 'tRNA', etc. but this would be prone to things like spelling errors, not to mention disagreement of the definition of some of these terms.
So solve this, all features are linked to a specific type in a controlled vocabulary or ontology. These are stored in the cv module.
CV (Controlled Vocabularies) Module
Controlled Vocabulary Module Tables
The CV module implements controlled vocabularies and their more complex cousins, ontologies.
Controlled Vocabularies
A controlled vocabulary (CV) is a list of terms from which a value must come. CVs are widely used in all databases, not just biological ones. Pull down menus are often used to present CVs to users in query or annotation interfaces.
| ZFIN's Assay Type CV | 
|
|---|
Ontologies
Controlled vocabularies are simple lists of terms. Ontologies are terms plus rules and relationships betwen the terms. The Gene Ontology (GO) and Sequence Ontology (SO) are the two best known ontologies, but there are many more available from OBO.
Ontologies can be incredibly complex with many relationships between terms. Representing them and reasoning with them is non-trivial, but the CV module helps with both.
| FlyBase CV Term Viewer showing GO term "tissue regeneration" |
|---|
|
CVs and Ontologies in Chado
(See the CVTerm table referencing table list.)
Every other module depends on on the CV module. CVs and ontologies are central to Chado's design philosophy. Why?
Data Integrity
Using CVs (and enforcing their use as Chado does) ensures that your data stays consistent. For example, in the most simple case it prevents your database from using several different values all to mean the same thing (e.g., "unknown", "unspecified", "missing", "other", " ",...), and it prevents misspellings ("sagital" instead of "sagittal") and typos.
Data Portability and Standardization
If you are studying developmental processes and you use the Gene Ontology's biological process terms then your data can be easily shared and integrated with data from other researchers. If you create your set of terms or just enter free text (egads!), then it will require a lot of human intervention to convert your data to a standard nomenclature so it can be integrated with others.
Using an established ontology when one exists usually involves some compromises, but it greatly increases the usability of your data to others (and to yourself).
Complexity
Controlled vocabularies are not particularly complex - they are just lists of terms. Ontologies, however, can be very complex, as shown by the GO example above. This complexity could be ignored. You could, for example, convert GO to a controlled vocabulary - a very long list of terms. You would still have data integrity and portability, and it wouldn't be as complex.
It also would not be as powerful. Ontologies support reasoning about the terms in them and this can be very useful. With GO, for example, you can ask
Show me all genes involved in anatomical structure development
and get back genes directly tagged with anatomical structure development, plus any genes tagged with any of that term's sub-terms, from organ development to regulation of skeletal muscle regeneration. If you convert GO to just a list of terms, you can no longer answer that question.
The Chado CV Module supports such complex queries with ontologies by pre-calculating the transitive closure of all terms in an ontology. There is a great explanation of transitive closure on the Chado CV Module page. Also see the description of these 3 tables:
We won't go into any more detail on it here.
Opening our sample database
$ psql chado
Our first example query
Up to this point we've seen how to store features using the feature table as well as rigidly define what types of things they are using the cv module tables. Here is an SQL example of how to query some very basic information about all gene features in our database:
SELECT gene.feature_id, gene.uniquename, gene.name FROM feature gene JOIN cvterm c ON gene.type_id = c.cvterm_id WHERE c.name = 'gene' AND organism_id = 12;</sql> This should RETURN something LIKE: <pre> feature_id | uniquename | name ------------+------------------------------------------------+------------------------------------------------ 405 | maker-scf1117875582023-snap-gene-0.0 | maker-scf1117875582023-snap-gene-0.0 409 | maker-scf1117875582023-snap-gene-0.3 | maker-scf1117875582023-snap-gene-0.3 415 | genemark-scf1117875582023-abinit-gene-0.42 | genemark-scf1117875582023-abinit-gene-0.42 1011 | maker-scf1117875582023-snap-gene-1.0 | maker-scf1117875582023-snap-gene-1.0 1018 | maker-scf1117875582023-snap-gene-1.4 | maker-scf1117875582023-snap-gene-1.4 1022 | maker-scf1117875582023-snap-gene-1.1 | maker-scf1117875582023-snap-gene-1.1 1027 | maker-scf1117875582023-snap-gene-1.2 | maker-scf1117875582023-snap-gene-1.2 1032 | maker-scf1117875582023-snap-gene-1.7 | maker-scf1117875582023-snap-gene-1.7 1038 | maker-scf1117875582023-snap-gene-1.5 | maker-scf1117875582023-snap-gene-1.5 1698 | snap_masked-scf1117875582023-abinit-gene-2.4 | snap_masked-scf1117875582023-abinit-gene-2.4 ...</pre> TYPE <tt>q</tt> TO escape the listing. Lists OF things are great, but we're going to need to do a lot more with our genomic data than keep lists of features. First, we need to be able to identify them properly. This may seem straightforward, but creating one column per ID type in feature would be a bad idea, since any given feature could have dozens of different identifiers from different data sources. The General Module helps resolve this. ==== General Module ==== The General module is about identifying things within this DB to the outside world, and identifying things from the outside world (i.e., other databases) within this database. ===== IDs ===== {{ChadoModuleLink|General|General module tables}} Biological databases have ''public'' and ''private'' IDs and they are usually different things. ====== Public IDs ====== These are shown on web pages and in publications. These are also known as ''accession numbers''. <center> {| class="wikitable" |- ! GO + 0043565 = GO:0043565 |- ! InterPro + IPR001356 = InterPro:IPR001356 |- ! YourDB + whatever = YourDB:whatever |} </center> Public IDs tend to be ''alternate keys'' inside the database: they do uniquely identify objects in the database. ====== Private IDs ====== These are used inside the database and are not meant to be shown or published. Tend to be long integers. There are many more private IDs than public IDs. Private IDs are used for ''primary keys'' and ''foreign keys''. Most DBMSs have built-in mechanisms for generating private IDs. ===== IDs in Chado ===== The {{ChadoModuleLink|General|General module}} defines ''public'' IDs of * items defined in this databases, and * items defined in other databases, that are used or referenced in this database. In fact, those two classes of IDs are defined in exactly the same way, in the [http://gmod.org/Chado_General_Module#Table:_dbxref dbxref table]. In Chado every table (in every module) defines its own private IDs. <!-- We can explore this variety of identifiers in our database by querying public identifiers for a given gene: <syntaxhighlight lang="sql"> SELECT id.accession, db.name FROM dbxref id JOIN db ON id.db_id = db.db_id JOIN feature_dbxref fdbx ON id.dbxref_id=fdbx.dbxref_id JOIN feature gene ON fdbx.feature_id = gene.feature_id WHERE gene.name = 'SDR1';</sql> This will return <pre> accession | name -------------------+------------ maker | GFF_source G3DSA:3.40.50.720 | Gene3D IPR002198 | InterPro IPR002347 | InterPro IPR016040 | InterPro PTHR19410 | PANTHER PTHR19410:SF105 | PANTHER PR00081 | DB:PRINTS PF00106 | Pfam maker | GFF_source G3DSA:3.40.50.720 | Gene3D IPR002198 | InterPro IPR002347 | InterPro IPR016040 | InterPro PTHR19410 | PANTHER PTHR19410:SF105 | PANTHER PR00081 | DB:PRINTS PF00106 | Pfam (18 rows)</pre> Huh? Why is everything listed twice? It turns out that MAKER has create both a gene and an mRNA named 'SDR1', and both the mRNA and the gene are linked out to their related entities elsewhere. To see only the external IDs associated with the gene: <syntaxhighlight lang="sql"> SELECT id.accession, db.name FROM dbxref id JOIN db ON id.db_id = db.db_id JOIN feature_dbxref fdbx ON id.dbxref_id=fdbx.dbxref_id JOIN feature gene ON fdbx.feature_id = gene.feature_id JOIN cvterm on gene.type_id = cvterm_id WHERE gene.name = 'SDR1' and cvterm.name = 'gene';</sql> This demonstrates one drawback of using a generic table like <tt>feature</tt> to store all feature types. If we had separate <tt>gene</tt> and <tt>mrna</tt> tables our first query would have been: <syntaxhighlight lang="sql"> SELECT id.accession, db.name FROM dbxref id JOIN db ON id.db_id = db.db_id JOIN feature_dbxref fdbx ON id.dbxref_id=fdbx.dbxref_id JOIN gene ON fdbx.feature_id = gene.feature_id WHERE gene.name = 'SDR1';</sql> and we would have been done. However, at the expense of having possibly 10s or 100s of structurally identical tables in your database. This would also make other queries such as <div class="quotebox"> Show me all features within 1000bp of gene SDR1. </div> much, ''much'' harder to answer. --> ==== Properties ==== So far we've ONLY seen very basic information FOR each feature stored. Because the feature TABLE was designed TO be very generic AND store ALL feature types, attributes specific TO ONLY SOME types can't be stored there. It wouldn't make sense, FOR example, TO have a COLUMN called 'gene_product_name', since that COLUMN would be empty FOR ALL the features, LIKE chromosomes, that aren't gene products. These feature-specific attributes are known as 'properties' of a feature in Chado. They are stored in a table called [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_featureprop featureprop]. {| class="wikitable" |- ! colspan="5" | Table: featureprop |- ! featureprop_id ! feature_id ! type_id ! value ! rank |- |} You may have noticed that the featureprop table shares a 'type_id' column with the feature table. Properties of features are typed according to a controlled vocabulary just as the features themselves are. This helps to ensure that anyone using the same vocabularies are encoding their property assertions in the same way. Using feature properties we can now describe our features as richly as needed, but our features are still independent of one another. Because many of the features we're storing have SOME sort OF relationship TO one another (such AS genes AND their polypeptide end-products), our schema needs TO accommodate this. ===== Relationships ===== Relationships BETWEEN features are stored IN the [http://gmod.org/wiki/INDEX.php/Chado_Sequence_Module#Table:_feature_relationship feature_relationship TABLE]. {| class="wikitable" |- ! colspan="6" | TABLE: feature_relationship |- ! feature_relationship_id ! subject_id ! object_id ! type_id ! rank ! ... |- |} Features can be arranged IN graphs, e.g. "exon ''part_of'' transcript ''part_of'' gene"; IF TYPE IS thought OF AS a verb, THEN each arc OR edge makes a statement: * Subject ''verb'' Object, OR * Child ''verb'' Parent, OR * Contained ''verb'' Container, OR * Subfeature ''verb'' Feature Again, notice the USE OF controlled vocabularies (type_id) TO define the relationship BETWEEN features. One might think OF a relationship BETWEEN a gene AND chromosome AS 'located_on', AND store that AS a feature_relationship entry BETWEEN the two. IF you did, pat yourself ON the back FOR Chado-ish thinking, but there's a better way to handle locatable features, since a relationship entry alone wouldn't actually say WHERE the feature was located, ONLY that it was. ===== Locations ===== Location describes WHERE a feature IS/comes FROM relative TO another feature. SOME features such AS chromosomes are NOT localized IN SEQUENCE coordinates, though contigs/assemblies which make up the chromosomes could be. Locations are stored IN the [http://gmod.org/wiki/INDEX.php/Chado_Sequence_Module#Table:_featureloc featureloc TABLE], AND a feature can have zero OR more featureloc records. Features will have either * one featureloc record, FOR localized features FOR which the location IS known, OR * zero featureloc records, FOR unlocalized features such AS chromosomes, OR FOR features FOR which the location IS NOT yet known, such AS a gene discovered USING classical genetics techniques. * Features WITH multiple featurelocs are explained below. (FOR a good explanation OF how features are located IN Chado see [http://gmod.org/wiki/INDEX.php/Chado_Sequence_Module#Feature_Locations Feature Locations]. This explanation IS excerpted FROM that.) ====== Interbase Coordinates ====== This IS covered IN more detail ON the [[Introduction TO Chado#Interbase Coordinates|GMOD web site]]. [[Image:Interbase.png]] A featureloc record specifies an INTERVAL IN ''interbase SEQUENCE coordinates'', bounded BY the fmin AND fmax COLUMNS, each representing the LOWER AND UPPER linear POSITION OF the ''boundary'' BETWEEN bases OR base pairs (WITH directionality indicated BY the strand COLUMN). Interbase coordinates were chosen OVER the base-oriented coordinate system because the math IS easier, AND it cleanly supports zero-LENGTH features such AS splice sites AND insertion points. ====== Location Chains ====== Chado supports ''location chains''. FOR example, locating an exon relative TO a contig that IS itself localized relative TO a chromosome. The majority OF Chado instances will NOT require this flexibility; features are typically located relative TO chromosomes OR chromosomes arms. The ability TO store such localization networks OR location graphs can be useful FOR unfinished genomes OR parts OF genomes such AS heterochromatin, IN which it IS desirable TO locate features relative TO stable contigs OR scaffolds, which are themselves localized IN an unstable assembly TO chromosomes OR chromosome arms. Localization chains do NOT necessarily ONLY span assemblies - protein domains may be localized relative TO polypeptide features, themselves localized TO a transcript (OR TO the genome, AS IS more common). Chains may also span SEQUENCE alignments. ====== featureloc TABLE ====== Feature location information IS stored IN the [http://gmod.org/wiki/INDEX.php/Chado_Sequence_Module#Table:_featureloc featureloc TABLE]. {| class="wikitable" |- ! colspan="6" | TABLE: featureloc |- ! featureloc_id ! feature_id ! srcfeature_id ! fmin ! rank ! ... |- |} ===== Example: Gene ===== '''Note:''' This example AND SOME OF the figures are extracted FROM ''[http://gmod.org/wiki/INDEX.php/Image:A_Chado_Case_Study.pdf A Chado CASE study: an ontology-based modular schema FOR representing genome-associated biological information]'', BY Christopher J. Mungall, David B. Emmert, AND the FlyBase Consortium (2007) How IS a "central dogma" gene represented IN Chado? [[Image:CentralDogma.png]] How do we represent these exons, mRNAs, proteins, AND there relationships BETWEEN them? {| | {| class="wikitable" |- ! colspan="6" | TABLE: feature |- ! feature _id ! name ! uniquename ! type_id ! is_ analysis ! |- | 10456 | <font color="aqua">exon 1</font> | genex_exon1 | (exon) | FALSE | |- | 10457 | <font color="silver">exon 2</font> | genex_exon2 | (exon) | FALSE | |- | 10458 | <font color="fuchsia">exon 3</font> | genex_exon3 | (exon) | FALSE | |- | 10459 | <font color="gray">exon 4</font> | genex_exon4 | (exon) | FALSE | |- | 10460 | <font color="green">exon 5</font> | genex_exon5 | (exon) | FALSE | |- | 10461 | <font color="lime">exon 6</font> | genex_exon6 | (exon) | FALSE | |- | 10462 | <font color="maroon">exon 7</font> | genex_exon7 | (exon) | FALSE | |- | 10463 | <font color="navy">mRNA-A</font> | genex_mRNA-A | (mRNA) | FALSE | |- | 10464 | <font color="olive">mRNA-B</font> | genex_mRNA-B | (mRNA) | FALSE | |- | 10465 | <font color="purple">protein-p2</font> | genex_protein-p2 | (polypeptide) | FALSE | |- | 10466 | <font color="red">protein-p1</font> | genex_protein-p1 | (polypeptide) | FALSE | |- | 10467 | <font color="blue">gene X</font> | genex | (polypeptide) | FALSE | |} | {| class="wikitable" |- ! colspan="6" | TABLE: feature_relationship |- ! feature_ relationship _id ! subject_id ! object_id ! type_id ! rank ! |- | 589 | (<font color="aqua">exon 1</font>) | (<font color="navy">mRNA-A</font>) | (part_of) | 0 |- | 590 | (<font color="silver">exon 2</font>) | (<font color="navy">mRNA-A</font>) | (part_of) | 0 |- | 591 | (<font color="fuchsia">exon 3</font>) | (<font color="navy">mRNA-A</font>) | (part_of) | 0 |- | 592 | (<font color="gray">exon 4</font>) | (<font color="navy">mRNA-A</font>) | (part_of) | 0 |- | 593 | (<font color="green">exon 5</font>) | (<font color="navy">mRNA-A</font>) | (part_of) | 0 |- | 594 | (<font color="silver">exon 2</font>) | (<font color="olive">mRNA-B</font>) | (part_of) | 0 |- | 595 | (<font color="gray">exon 4</font>) | (<font color="olive">mRNA-B</font>) | (part_of) | 0 |- | 596 | (<font color="lime">exon 6</font>) | (<font color="olive">mRNA-B</font>) | (part_of) | 0 |- | 597 | (<font color="maroon">exon 7</font>) | (<font color="olive">mRNA-B</font>) | (part_of) | 0 |- | 598 | (<font color="purple">protein-p2</font>) | (<font color="navy">mRNA-A</font>) | (derives_from) | 0 |- | 599 | (<font color="red">protein-p1</font>) | (<font color="olive">mRNA-B</font>) | (derives_from) | 0 |- | 600 | (<font color="navy">mRNA-A</font>) | (<font color="blue">gene X</font>) | (part_of) | 0 |- | 601 | (<font color="olive">mRNA-B</font>) | (<font color="blue">gene X</font>) | (part_of) | 0 |} |} ===== Example: Computational Analysis ===== '''Note:''' This IS example IS based ON an example BY Scott Cain FROM an earlier [http://gmod.org/wiki/INDEX.php/Image:ChadoWorkshopArthopod2008.pdf Chado workshop]. You can store the results OF computational analysis such AS BLAST OR BLAT runs IN the SEQUENCE module. Here's an example of you could store a BLAST highest scoring pair (HSP) result. With HSP results there are two reference sequences, which means two entries in the featureloc table. (The analysis and analysisfeature tables, in the Companalysis module, are used to store information about how the analysis was done and what scores resulted.) [[Image:CompAnalysisFeature.png]] Every horizontal line becomes a record in the feature table, and every vertical line becomes a record in the feature_relationship table. ===== Other Feature Annotations ===== Link to any feature via [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_feature feature_id]: * GO terms in [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_feature_cvterm feature_cvterm] * DB links in [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_feature_dbxref feature_dbxref] * Miscellaneous features in [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_featureprop featureprop] * Attribution in [http://gmod.org/wiki/index.php/Chado_Sequence_Module#Table:_feature_pub feature_pub] ===== Extending Chado: Properties tables and new modules ===== Chado was built to be easily extensible. New functionality can be added by adding new modules. Both the Mage and the Stock modules were added after Chado had been out for a while. They both addressed needs that were either not addressed, or were inadequately addressed in the original release. Chado can be tailored to an individual organization's needs BY USING ''property TABLES''. Property TABLES are a means TO virtually ADD NEW COLUMNS WITHOUT HAVING TO MODIFY the schema. Property TABLES are included IN many modules. Property TABLES are incredibly flexible, AND they do make Chado extremely extensible, but they do so at a cost. * The SQL TO GET attributes FROM property TABLES IS much more complex, AND * It IS more WORK TO enforce constraints ON DATA IN properties TABLES THEN IN regular COLUMNS. == Practice == ===Prerequisites=== USING DATA FROM [[MAKER]]. ====PostgreSQL==== Already installed PostgreSQL 8.4 via apt-GET. =====Edit config files===== <pre class="enter"> sudo su cd /etc/postgresql/8.4/main/ less pg_hba.conf </pre> At the bottom OF the file, we've already changed <tt>ident sameuser</tt> to <tt>trust</tt>. This means that anyone who is on the local machine is allowed to connect to any database. If you want to allow people to connect from other machines, their IP address or a combo of IP address and netmasks can be used to allow remote access. Now (re)start the database server: <pre class="enter"> /etc/init.d/postgresql-8.4 restart </pre> =====Create a gmod user===== First, switch to the postgres user (it was created during the PostgreSQL package install): <pre> su - postgres createuser gmod Shall the new role be a superuser? (y/n) y exit # to leave postgres user shell exit # to leave root shell </pre> =====Installing {{CPAN|DBIx::DBStag}} by hand===== In the previous course, this was installed by hand, but in this server it is installed already. It is moderately tricky, because it is difficult to install by CPAN when using <code>sudo cpan</code> but is easy to install "by hand" or using <code>cpan</code>. ==== [[BioPerl|BioPerl]] ==== Version 1.6 already installed. ====Let's GO!==== ====Environment VARIABLES==== IN <tt>~/.bashrc</tt> ADD: <pre class="enter"> GMOD_ROOT='/usr/local/gmod' export GMOD_ROOT </pre> AND SOURCE the profile: <pre class="enter"> SOURCE ~/.bashrc </pre> ===Installing GMOD=== <pre class="enter"> cd ~/schema/chado/ perl Makefile.PL </pre> Trying again: <span class="enter">perl Makefile.PL</span> USE VALUES IN '/home/ubuntu/schema/chado/build.conf'? [Y] <span class="enter">n</span> This allows you TO specify Pythium WHEN you are asked FOR a DEFAULT organism. > "Pythium" FOR the DEFAULT organism Just hit enter WHEN it asks FOR a password FOR USER gmod. This <tt>Makefile.PL</tt> does quite a bit OF stuff TO GET the installation ready. Among other things it: * Sets up a variety OF DATABASE USER parameters * Prepares the SQL files FOR installation * It can rebuild {{CPAN|Class::DBI}} api FOR [[Chado]], but isn't now (they are prebuilt for the default schema) * All the 'normal' stuff that would happen when running <tt>Makefile.PL</tt>, like copying <tt>lib</tt> and <tt>bin</tt> files to be ready for installation <span class="enter">make sudo make install make load_schema make prepdb make ontologies</span> Available ontologies: [1] Relationship Ontology [2] Sequence Ontology [3] Gene Ontology [4] Chado Feature Properties [5] Plant Ontology Which ontologies would you like to load (Comma delimited)? [0] <span class="enter">1,2,4</span> You can pick any ontologies you want, but GO will take over an hour to install, and it doesn't matter TO much what you pick, because we are going TO be blowing this DATABASE away soon anyway. ====Saving your progress TO this point==== It IS generally a good idea TO save your progress WHEN you are done loading ontologies BEFORE you've attempted to load any other data. That way, if something goes wrong, it is very easy to restore to this point. To make a db dump, do this: <span class="enter">pg_dump chado_test | bzip2 -c --best > db_w_ontologies.bz2</span> The <tt>-c</tt> tells <tt>bzip2</tt> to take input on standard in and spit it out on <tt>stdout</tt>. To restore from a dump, drop and recreate the database and then uncompress the dump into it, like this: <pre class="dont"> dropdb chado_test createdb chado_test bzip2 -dc db_w_ontologies.bz2 | psql chado_test </pre> ====A Note about Redos==== If at some point you feel like you want to rebuild your database from scratch, you need to get rid of the temporary directory where ontology files are stored. You can either do this with <tt>rm -rf ./tmp</tt> '''(be very careful about that <tt>./</tt> IN front OF <tt>tmp</tt>)''' or with a make target that was designed for this: <tt>make rm_locks</tt>, which only gets rid of the lock files but leaves the ontology files in place. ===Preparing [[GFF]] data for loading=== Issues: *splitting 'annotations' from 'computational analysis' *very large files *fasta → gff *utility scripts for various preparation activities ====Working with Large [[GFF]] files==== Large files (more than 3-500,000 rows) can cause headaches for the [[GFF3]] bulk loader, but genome annotation GFF3 files can frequently be millions of lines. What to do? The script <tt>gmod_gff3_preprocessor.pl</tt> will help with this, both by splitting the files in to reasonable size chunks and sorting the output so that it make sense. Note that if your files are already sorted (and in this case, that means that all parent features come before their child features and lines that share IDs (like CDSes sometimes do) are together), then all you need to do is split your files. Frequently files are already sorted and avoiding sorting is good for two reasons: #It takes a long time to do the sort compared to the split (it parses every line, loads it into temporary tables and pulls out lines via query to rebuild the GFF file) #The sorting process makes it more difficult to read the resulting GFF, since the parent feature will no longer be near the child features in the GFF file. Chado doesn't care about that but you might. ====Loading [[GFF3]]==== Working WITH the LARGE NUMBER OF files that come OUT OF the preprocessor can be a bit OF a headache, so I've 'developed' a few tricks. Basically, I make a bash script that will execute all of the loads at once. The easy way (for me) to do this is: <pre class="enter"> ls *.gff3 > load.sh vi load.sh </pre> and then use vim regex goodness to write the loader commands into every line of the file: <span class="enter">:% s/^/gmod_bulk_load_gff3.pl --analysis -g /</span> which puts the command at the beginning every line. The <tt>--analysis</tt> tells the loader that it is working with analysis results, so the scores need to be store in the companalysis module. While the genes file are not really analysis results, I faked them to look like FgenesH results so that Apollo would have something to work with. Then I manually edit the file to add and remove the few modifications I need: *Add <tt>--noexon</tt> to the genes file (since it contains both CDS and exon features, I don't want the loader TO CREATE exon features FROM CDS features). THEN I ADD <tt>#!/bin/bash</tt> at the top OF the file (which isn't actually needed) and run <span class="enter">bash load.sh</span> and it loads all of the files sequentially. I have done this with 60-70 files at a time, letting the loader run for a few days(!) ====Capturing the output to check for problems==== If you are going to let a load run a very long time, you probably should capture the output to check for problems. There are two ways to do this: *run inside the <tt>screen</tt> command: <div class="indent"> <span class="enter">screen -S loader</span> which creates a new 'screen' separate from your login. Then execute the load command in the screen. To exit the screen but let your load command continue running in it, type <tt>ctrl-a</tt> followed by a <tt>d</tt> (to detach) and you get your original terminal back. To reconnect to the loader screen, type <span class="enter">screen -R loader</span> </div> *capture <tt>stdout</tt> and <tt>stderr</tt> to a file <div class="indent"> When you run the load command, you can use redirection to collect the <tt>stdout</tt> and <tt>stderr</tt> to a file: <span class="enter">bash load.sh >& load.output</span> </div> ====Really loading data==== OK, let's put the DATA INTO chado: <pre class="enter"> gmod_bulk_load_gff3.pl -g NEW.gff </pre> This should NOT WORK (yet!) =====Adding our organism===== We told the installer that we were going TO USE an organism named "Pythium", but Chado doesn't know about it. We need to add it to the database: <span class="enter">psql chado</span> chado> <span class="enter">SELECT * FROM organism;</span> chado> <span class="enter">INSERT INTO organism ( abbreviation, genus, species, common_name) VALUES ('P.ultimum','Pythium','ultimum','Pythium');</span> chado> <span class="enter">\q</span> =====Try again ===== <span class="enter">gmod_bulk_load_gff3.pl -g scf1117875582023.gff</span> Doh! The loader is trying to tell us that this looks like analysis data (that is, data produced by computer rather than humans). <div class="dont"> We need to tell the loader that it is in fact analysis results: <span class="enter">gmod_bulk_load_gff3.pl --analysis -g new.gff</span> <div class="emphasisbox"> <span style="font-size:120%">Kill, ''kill'', '''KILL!''' (ctrl-c)</span> the load as soon as you see this message: There are both CDS and exon features in this file, but you did not set the --noexon option, which you probably want. Please see `perldoc gmod_bulk_load_gff3.pl for more information. </div> </div> Argh! Now the loader is pointing out that this [[GFF]] file has both exons and CDS features and Chado prefers something a little different. While the loader will load this data as written, it won't be "standard." Instead, we'll add the <tt>--noexon</tt> option (which tells the loader not to create exon features from the CDS features, since we already have them). One (at least) more time: <span class="enter">gmod_bulk_load_gff3.pl --analysis -g scf1117875582023.gff --noexon</span> Success! (Probably.) If this failed, when rerun, we may need the <tt>--recreate_cache</tt> option, which recreates a "temporary" table that the loader uses to keep track of IDs. ===Loading other data=== [[Chado XML|Chado XML]] and [[XORT|XORT]] == Chado for Expression, Genotype, Phenotype, and Natural Diversity == This section is about some of the lesser used Chado Modules. === Expression === From an organism database point of view, expression is about turning this: {| class="wikitable" | [[Image:ZfinGSC.jpg|200px]] | Fig. S5 Synergistic effect of zdia2 sMO and profilin I tMO. Embryos were injected with 8 ng zdia2 sMO (A and B) or co-injected with 4 ng zdia2 sMO and 4 ng profilin I tMO (C and D). Embryos were incubated until tail bud stage (10 hpf), fixed and stained with ntl and gsc riboprobe. Photographs were taken for side view (A and C) and dorsal view (B and D) after WISH. From [http://dx.doi.org/10.1371/journal.pone.0003439 Lai ''et al.'', 2008 - Diaphanous-related formin 2 and profilin I are required for gastrulation cell movements. PLoS One 3(10):e3439] |} Into this: {| class="wikitable" ! Gene !! Fish !! Stage !! Anatomy !! Assay |- | gsc || wild type (unspecified), MO:diaph2,pfn1 || Bud || prechordal plate || ISH |- | gsc || wild type (unspecified), MO:diaph2 || Bud || prechordal plate || ISH |- |ntla || wild type (unspecified), MO:diaph2,pfn1 || Bud || notochord || ISH |- | ntla || wild type (unspecified), MO:diaph2,pfn1 || Bud || tail bud || ISH |- | ntla || wild type (unspecified), MO:diaph2 || Bud || notochord || ISH |- | ntla || wild type (unspecified), MO:diaph2|| Bud || tail bud || ISH |- | colspan="5" align="center" | From [http://zfin.org/cgi-bin/webdriver?MIval=aa-fxfigureview.apg&OID=ZDB-FIG-081103-25 ZFIN: Figure: ''Lai et al.'', 2008, Fig. S5] |} ==== What defines an expression pattern? ==== Expression could include a lot of different things, and what it includes depends on the community: ; Gene, or Transcript, or Protein, or ... : What are we measuring? ; Stage : Store single stages, or stage windows? : What do stage windows or adjacent stages mean: throughout window, or we aren't sure ON the stage (high-throughput) ; Strain / Genotype : What geneotype did we see this ON. ; Anatomy : Does "expressed in brain" mean expressed IN ALL, most, OR somewhere IN brain? ; Assay : ISN, antibody, probe, ... ; SOURCE : Publication, high-throughput screen, lab, project, ... : Genotype : Environment ; Pattern : Do you want TO keep track OF homogeneous, graded, spotty ; NOT Expressed : Do you keep track OF absence? IF so, what does it mean? (NOT ''detected'') ; Image : IS an image required OR optional? ; Strength : What do strengths mean across different experiments? ==== How does Chado deal WITH this variety? ==== ''Post-composition'', which IS a very Chadoish way OF doing things. * Embrace a minimal definition OF what an expression pattern IS. IN Chado, ALL that IS required IS a ''name'', e.g., ''SLC21A IN GMOD Course Participant kidney after 4 days at NESCent'', OR just "GMOD0002347". * You can also provide a description. IF your name IS "GMOD0002347", this may be a good idea * Details are THEN hung off that. ==== FlyBase Example ==== A specific example FROM FlyBase: <div class="indent"> Here IS an example OF a simple CASE OF the sort OF DATA that [http://flybase.org FlyBase] curates. :The ''dpp'' transcript IS expressed IN embryonic stage 13-15 IN the cephalic segment AS reported IN a [http://flybase.bio.indiana.edu/reports/FBrf0053806.html paper BY Blackman ''et al.'' IN 1991]. This would be implemented IN the expression module BY linking the ''dpp'' transcript feature TO expression via [[Chado_Tables#Table:_feature_expression|feature_expression]]. We would THEN link the following cvterms TO the expression USING [[#Table:_expression_cvterm|expression_cvterm]]: * ''embryonic stage 13'' WHERE the cvterm_type would be stage AND the rank=0 * ''embryonic stage 14'' WHERE the cvterm_type would be stage AND the rank=1 * ''embryonic stage 15'' WHERE the cvterm_type would be stage AND the rank=1 * ''cephalic segment'' WHERE the cvterm_type would be anatomy AND the rank=0 * ''IN situ hybridization'' WHERE the cvterm_type would be assay AND the rank=0 </div> ;Translation: <div class="indent"> IN FlyBase, this would be a single expression record, WITH 5 Ontology/CV terms attached TO it. * 1 saying what anatomy the expression IS FOR - ''cephalic segment'' * 1 saying that the assay TYPE was ''IN situ hybridization'' * 1 each FOR each OF the 3 stages - ''embryonic stages 13, 14, 15'' AND * 1 record saying this expression pattern IS FOR ''dpp''. * 1 record saying this expression pattern IS FROM [http://flybase.bio.indiana.edu/reports/FBrf0053806.html Blackman ''et al.'' IN 1991]. </div> ==== Chado Allows ==== The Expression module TABLE design allows each expression pattern TO have * 1 name * 0, 1 OR more ** Publications / Sources ** Features ** Images ** Anatomy terms ** Stages ** Assay types ** Any other CV/Ontology term (e.g., detected, NOT detected) ;Two KEY Points: # '''Chado can support whatever your community decides your definition of an expression pattern is.''' # '''However, Chado will not enforce that definition for you.''' IF you require an expression pattern TO have * 1 name * 1 publication/SOURCE * 1 Feature * 1 anatomy term * 1 stage * 1 assay TYPE * 1 detected / NOT detected flag * 0, 1 OR more images THEN you will have TO WRITE a script TO CHECK that. ==== TABLE: expression ==== {| class="wikitable" |+ expression STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | expression_id | serial | '' PRIMARY KEY '' |- class="tr1" | | uniquename | text | '' UNIQUE NOT NULL '' |- class="tr0" | | md5checksum | CHARACTER(32) | '' '' |- class="tr1" | | description | text | '' '' |} TABLES REFERENCING this one via FOREIGN KEY Constraints: * [[#Table:_expression_cvterm| expression_cvterm]], [[Chado_Tables#Table:_expression_image| expression_image]], [[Chado_Tables#Table:_expression_pub| expression_pub]], [[Chado_Tables#Table:_expressionprop| expressionprop]], [[Chado_Tables#Table:_feature_expression| feature_expression]], [[Chado_Tables#Table:_wwwuser_expression| wwwuser_expression]] ==== TABLE: expression_cvterm ==== {| class="wikitable" |+ expression_cvterm STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | expression_cvterm_id | serial | '' PRIMARY KEY '' |- class="tr1" | [[#Table:_expression| expression]] | expression_id | INTEGER | '' UNIQUE#1 NOT NULL '' |- class="tr0" | [[Chado_Tables#Table:_cvterm| cvterm]] | cvterm_id | INTEGER | '' UNIQUE#1 NOT NULL '' |- class="tr1" | | rank | INTEGER | '' NOT NULL '' |- class="tr0" | [[Chado_Tables#Table:_cvterm| cvterm]] | cvterm_type_id | INTEGER | '' UNIQUE#1 NOT NULL '' |} TABLES REFERENCING this one via FOREIGN KEY Constraints: * [[Chado_Tables#Table:_expression_cvtermprop| expression_cvtermprop]] === Genotype === Defined IN the [[Chado Genetic Module|Chado Genetic Module]]. A ''genotype'' IN Chado IS basically a ''name'', WITH a pile OF ''features'' associated TO it. This used TO mean a SET OF alleles. NOT sure how strains have been handled IN the era OF high-throughput sequencing, this may be much more detailed. ==== TABLE: genotype ==== Genetic context. A genotype IS defined BY a collection OF features, mutations, balancers, deficiencies, haplotype blocks, OR engineered constructs. {| class="wikitable" |+ genotype STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | genotype_id | serial | '' PRIMARY KEY '' |- class="tr1" | | name | text | '' ''<br /><br />Optional alternative name FOR a genotype, FOR display purposes. |- class="tr0" | | uniquename | text | '' UNIQUE NOT NULL ''<br /><br />The UNIQUE name FOR a genotype; typically derived FROM the features making up the genotype. |- class="tr1" | | description | CHARACTER VARYING(255) | '' '' |} TABLES REFERENCING this one via FOREIGN KEY Constraints: * [[Chado_Tables#Table:_feature_genotype| feature_genotype]], [[Chado_Tables#Table:_phendesc| phendesc]], [[Chado_Tables#Table:_phenotype_comparison| phenotype_comparison]], [[Chado_Tables#Table:_phenstatement| phenstatement]], [[Chado_Tables#Table:_stock_genotype| stock_genotype]], [[Chado_Tables#Table:_wwwuser_genotype| wwwuser_genotype]] ==== TABLE: feature_genotype ==== {| class="wikitable" |+ feature_genotype STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | feature_genotype_id | serial | '' PRIMARY KEY '' |- class="tr1" | [[Chado_Tables#Table:_feature| feature]] | feature_id | INTEGER | '' UNIQUE#1 NOT NULL '' |- class="tr0" | [[#Table:_genotype| genotype]] | genotype_id | INTEGER | '' UNIQUE#1 NOT NULL '' |- class="tr1" | [[Chado_Tables#Table:_feature| feature]] | chromosome_id | INTEGER | '' UNIQUE#1 ''<br /><br />A feature OF SO TYPE "chromosome". |- class="tr0" | | rank | INTEGER | '' UNIQUE#1 NOT NULL ''<br /><br />rank can be used FOR n-ploid organisms OR TO preserve ORDER. |- class="tr1" | | cgroup | INTEGER | '' UNIQUE#1 NOT NULL ''<br /><br />Spatially distinguishable GROUP. GROUP can be used FOR distinguishing the chromosomal groups, FOR example (RNAi products AND so ON can be treated AS different groups, AS they do NOT fall ON a particular chromosome). |- class="tr0" | [[Chado_Tables#Table:_cvterm| cvterm]] | cvterm_id | INTEGER | '' UNIQUE#1 NOT NULL '' |} === Environment === Also defined IN the [[Chado Genetic Module|Chado Genetic Module]]. ==== TABLE: environment ==== The environmental component OF a phenotype description. {| class="wikitable" |+ environment STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | environment_id | serial | '' PRIMARY KEY '' |- class="tr1" | | uniquename | text | '' UNIQUE NOT NULL '' |- class="tr0" | | description | text | '' '' |} TABLES REFERENCING this one via FOREIGN KEY Constraints: * [[#Table:_environment_cvterm| environment_cvterm]], [[Chado_Tables#Table:_phendesc| phendesc]], [[Chado_Tables#Table:_phenotype_comparison| phenotype_comparison]], [[Chado_Tables#Table:_phenstatement| phenstatement]] ==== TABLE: environment_cvterm ==== {| class="wikitable" |+ environment_cvterm STRUCTURE |- ! F-KEY ! Name ! TYPE ! Description |- class="tr0" | | environment_cvterm_id | serial | '' PRIMARY KEY '' |- class="tr1" | [[#Table:_environment| environment]] | environment_id | INTEGER | '' UNIQUE#1 NOT NULL '' |- class="tr0" | [[Chado_Tables#Table:_cvterm| cvterm]] | cvterm_id | INTEGER | '' UNIQUE#1 NOT NULL '' |} === Phenotype, NATURAL Diversity AND Atlas Support === Phenotypes, NATURAL diversity, AND atlas support are ALL areas OF future WORK IN Chado. Chado does have a phenotype module, but it has NOT aged AS well AS other modules. Its support FOR NATURAL diversity IS limited TO what IS implemented IN the genotype, environment, AND phenotype modules. This IS NOT robust enough TO deal WITH studie that are frequently done IN the plant community, AND are increasingly done IN animal communities AS well. TO address this there are several efforts currently underway. The [http://aniseed-ibdm.univ-mrs.fr/ Aniseed project] includes 4 dimensional anatomy, expression, AND cell fate graphical atlases. Aniseed IS currently IN the process OF reimplementing itself TO USE Chado. This WORK IS likely TO lead TO contributions back TO GMOD (BOTH IN Chado AND a web interface) TO better support these types OF atlases. Better NATURAL diversity support will be added IN the coming YEAR. [http://nescent.org NESCent] has developed a prototype NATURAL diversity Chado module based ON the [http://www.maizegenetics.net/gdpdm/ GDPDM], that will added robust support FOR NATURAL diversity DATA. [[Category:Chado]] [[Category:Presentations]] [[Category:Tutorials]] [[Category:2012 Summer School]]
