MicrobesOnline: Tools and Downloads for Programmers

• Access to data
  • How to connect to pub.microbesonline.org
  • How to download auxilliary data: BLAST and FastBLAST databases
  • Example SQL queries
  • Schema documentation
• Code
  • Obtaining our source code
  • Popular perl scripts
  • Overview of the perl modules
  • Installing your own web server
• Links
  • Linking to the MicrobesOnline genome browser

This page gives technical advice on how to programmatically obtain data from the MicrobesOnline data. Please see the tutorial for background on MicrobesOnline and on the data it contains.

How to connect to pub.microbesonline.org

Due to security concerns, as of April 5, 2018, we are restricting access to our public SQL server. Please contact us at help@microbesonline.org if you wish to have access to our server.

We have set up a public read-only SQL server at pub.microbesonline.org. (This SQL server may move to another host in the future.) This server will include only data from public genomes. Data about genes and genomes will be in the "genomics" database. Functional genomics data, such as microarray data, will ultimately be in the "microarray" database. (The microarray database will not be available initially.) For both databases, use the username "guest" and the password "guest".

To connect, use the mysql command-line tool, e.g.

mysql -h pub.microbesonline.org -u guest -pguest genomics

mysql -h pub.microbesonline.org -u guest -pguest genomics -B -e 'my query' > output_file

Please limit your active queries to two, and active connections to three. If a query of yours is killed, it may be because it was running for too long, using too much memory, or using too many connections. If this happens to you, please email us at help@microbesonline.org and we will help you debug any problematic queries.

How to download auxiliary data: BLAST and FastBLAST databases

Nucleotide and protein BLAST databases and a FastBLAST database of the predicted proteins will be available for download at a later date.

Example SQL queries

Get all VIMSS locus Ids and genbank ids for protein-coding genes in E. coli K12 (NCBI taxonomyId 511145):

SELECT Locus.locusId, Synonym.name AS genbankId
FROM Scaffold JOIN Locus USING (scaffoldId) 
JOIN Synonym USING (locusId,version)
WHERE Locus.priority=1
AND Locus.type=1
AND Scaffold.isActive=1
AND Scaffold.taxonomyId=511145
AND Synonym.type=2;

Get all taxonomy Ids for sequenced delta-Proteobacteria (NCBI taxonomyId 28221), and their names:

SELECT DISTINCT Taxonomy.taxonomyId, Taxonomy.shortName
FROM Scaffold JOIN Taxonomy USING (taxonomyId)
JOIN TaxParentChild ON TaxParentChild.childId=Taxonomy.taxonomyId
WHERE Scaffold.isActive=1 AND Scaffold.isGenomic=1 AND TaxParentChild.parentId=28221;

Schema documentation

AASeq

ACL

Annotation

AnnotationDetail

COG

COGCount

COGFun

COGInfo

COGrpsblast

Carts

CrisprFamily

Description

DomainInfo

ECInfo

FastBLASTDomains

FasthmmFamily2HMM

FasthmmRawHits

GoCount

GroupUsers

Groups

HitInfo

IPR2Go

IPRInfo

InterPro

Jobs

KEGG2Taxonomy

KEGGCompound

KEGGConf

KEGGInfo

KEGGMap

KEGGblast

Locus

The central object in the MicrobesOnline database is a gene or "Locus". Locus includes protein-coding genes, non-coding RNA genes and pseudogenes. The Locus table includes a locusId, which is not unique, a version number, and a priority flag. The combination of a locusId and a version number is unique, and priority=1 for the current version. To avoid using stale and/oor redundant data, please make sure that you require "Locus.priority=1" whenever you query this table. Locus ids are also referred to as VIMSS ids and MicrobesOnline gene ids. (They are also referred to as orfIds in some of our older code.) Locus.type gives the type of the locus, e.g. type=1 for protein-coding gene. See the LocusType table for more information.

Locus also links to Scaffold.scaffoldId and to Position.posId. The Scaffold table includes a taxonomyId, so you can use that to determine what genome the Locus is from. The Position table stores the location of the gene. For protein-coding genes, the Position stores the range from the predicted start codon to the stop codon.

A large number of tables give additional information about a locus. Most of these tables include both locusId and version. Some of the tables includes only locusId but no version -- those entries implicitly refer to the active version of the locus (the one with priority=1).

Locus2Domain

Locus2Ec

Locus2Go

Locus2Ipr

Locus2Operon

MicrobesOnline operon predictions. Genes predicted to be transcribed by themselves will still have an operon, but with only one gene in it. For E. coli, we also have known operons, as explained in the Operon table. The tuId field links to the Operon table.

Plasmids, viruses, incomplete genomes, and eukaryotes usually do not have operon predictions.

Locus2Pdb

Locus2RegPrecise

Regulatory predictions from RegPrecise. regulatorLocusId is the regulator and locusId is the regulated gene. sourceTypeTermId is 1 for manually curated predictions and 2 for predictions that are automatically propagated to orthologs that have a conserved match to the weight matrix. regulonId is from RegPrecise itself and should be used together with sourceTypeTermId. geneInOperonIndex is the position in the operon (e.g., 1 for the 1st gene in the operon).

Locus2RTB

Locus2RTBArticles

Locus2SwissProt

Locus2SwissProt stores a mapping between MicrobesOnline genes and UniProt (formerly known as SwissProt) accesssions and identifiers. This table only stores the best hit(s) of each MicrobesOnline gene in UniProt. The SwissProt2Locus table stores a best-hit mapping in the other direction. The Locus2SwissProt.bidir flag is set if this relation is also in the SwissProt2Locus table.

Redundant mappings are common if multiple strains of a genome have been sequenced, because all of the copies of the protein sequence will usually be identical. Although UniProt contains organism information, it generally does not specify the strain. So, we do not use the UniProt organism information to make the mapping -- we only use the protein sequences.

Usually, a gene from a complete genome will have an exact or nearly-exact match in UniProt, but hits down to 80% identity are used if no closer match is available.

Locus2TigrFunCat

Locus2Tree

LocusCount

LocusSeq

LocusType

MOG

A MicrobesOnline Ortholog Group, or MOG, is the union of maximal ortholog groups, from different trees, that have similar membership. The "components" of a MOG are the maximal ortholog groups, see MOGComponent. The loci that belong to a MOG are stored in MOGMember. Note that a genome can have more than one representative in a MOG, but a locus belongs to at most one MOG. While constructing MOGs, we use the "best" maximal ortholog groups first, as determined by the metric of the average alignment length of the ogmembers. (We may use another metric, such as alignment score, in the future.) A MOG's metric is that of the first ortholog group that was placed in this MOG.

Also see the orthologs overview or the OrthologGroup or COG tables.

MOGComponent

MOGMember

MOGNeighborScores

MOGNeighborScores reports which MOGs are conserved near each other across more than one group of closely-related genomes. Only pairs with mog1 < mog2 are included in the table. nTaxGroupsBoth is the number of genome groups for which the genes are near each other. nTaxGroups1 is the number of genome groups that contain at least one representative of mog1. score = nTaxGroupsBoth / max(nTaxGroups1, nTaxGroups2). Genes are considered to be near each other if they are adjacent and spaced by less than 200 nucleotides, or if they are within a run of genes on the same strand for which each adjacent pair of which is spaced by less than 200 nucleotides. Groups of closely-related genomes are defined by clades from the MicrobesOnline species tree that have diverged by at most 0.02 from their common ancestor. (For example, this threshold groups strains of Escherichia coli and various Shigella species with each other but separately from Salmonella species.)

ObjectDescr

OGMember

This table describes the members of each ortholog group. A gene with a domain duplication can be in a tree more than once, which is why begin and end are necessary to guarantee uniqueness. nMemberThisGenomes helps determine if this is a 1:1 ortholog. aaLength (the length of this protein in amino acids) helps determine if the orthology relation covers the full length of the gene.

Operon

Ortholog

Ortholog stores putative orthology relationships between pairs of genes. Any gene will map to at most 1 gene in another genome. These "orthologs" are from a FastBLAST-based variant of bidirectional best BLASTp hits and are often incorrect. Also, the table is growing very rapidly (it grows as the square of the number of genomes). So, these will be discontinued soon. Use the OrthologGroup or MOG tables instead.

OrthologGroup

The primary key of this table is the combination of treeId and ogId. ogIds themselves are not unique.

Our definition of ortholog group is a clade in a gene tree that is mostly one copy per genome, or, if isDuplication is set, a clade of genes from a closely-related group of genomes that have undergone duplication. These are not evolutionary orthologs as originally defined by Fitch (evolutionary orthologs are genes that diverged by a speciation event). Nor is there any guaranatee that these orthologs have the same function. However, be believe that these tree-based orthologs, if used carefully (see below), are more accurate than bidirectional-best hits, and they are faster to compute and smaller to store. The members of the ortholog groups are in the OGMember table. Every gene in a gene tree will be in an ortholog group, even if the ortholog "group" contains only that gene.

Ortholog groups can be nested inside larger ortholog groups, as described by parentOG (which may be NULL). For example, suppose that two closely related genomes 1 and 2 have a duplication of a gene A, into A1/A2 and a1/a2. There will be ortholog groups for A1/A2, for a1/a2, another with isDuplication=1 for A1/A2/a1/a2, and then (potentially) a larger ortholog group with isDuplication=0 that contains A1/A2/a1/a2 as well as orthologs of these genes from other genomes that did not undergo duplication. splitTaxId is a genome that contains paralogs that are just outside this ortholog group and is hence at least partially responsible for this ortholog group being split here.

nGenes and nGenomes are the total number of genes, and nNonUniqueGenomes is the number of genomes with more than one gene in this ortholog group.

Complications with using tree-based orthologs. These are handled by Gene::treeOrthologs().

• A small number of duplications by horizontal gene transfer ("xenoparalogs") are also allowed within an ortholog group. If you want a stricter definition of orthology, use only the groups with no non-unique genomes. And, you probably want to examine the smaller orthology groups first.
• Genes typically belong to many trees, so you need to choose which tree(s) to use.
• Genes with very different domain structures, or truncated genes, may nevertheless be in some of the same gene trees, and you might not want to include these as orthologs.

Also see the orthologs overview or the MOG or COG tables.

PdbEntries

PdbReps

PdbSeq

PfamClan

Position

Position stores the position of objects such as genes or operons. begin and end are 1-based, and strand is either "+" or "-". For objects on linear chromosomes, begin is always less than end and strand, so for a protein-coding gene on the "-" strand, the start codon will be at the end position.

Objects on circular chromosomes (see Scaffold.isCircular) can wrap around the origin. In this case, both begin and end will be within the range [1,Scaffold.length], and end will be less than begin. In other words, end < begin indicates that the object wraps around the origin.

RegulonCluster

RegulonLinks

Scaffold

This table represents chromosomes, plasmids, or contigs. A genome may have multiple scaffolds and each scaffold may have thousands of genes. To make sure that you do not use out-of-date data, please require "Scaffold.isActive=1" whenever you query this table. If you don't want data from plasmids, require "isGenomic=1". Require "isPartial=0" to consider only complete genomes. isCircular is 1 for circular chromosomes and 0 for linear chromosomes.

For scaffolds from RefSeq, file will be the NC_nnnnnn identifier and gi should be a genbank id.

ScaffoldIsActiveFlag

ScaffoldPosChunks

This table indexes the positions of genes on scaffolds. This way you can quickly find genes at between, say, 110 and 120 kB on the E. coli chromosome. The posId field contains the gene's position (the same as Locus.posId), and the kbt value is the gene's position divided by 10,000 and rounded down. If a gene overlaps more than one 10 kB chunk of the genome then it will appear multiple times in this table.

ScaffoldSeq

SwissProt

SwissProt2Locus

SwissProt2Pubmed

SwissProtBlast

Synonym

SynonymType

TIGRInfo

TIGRroles

TaxName

TaxNode

TaxParentChild

Taxonomy

This table includes only organisms (or viruses or plasmids) whose genome sequences are in our database. The taxonomyId values are from the NCBI taxonomy database. These are usually species- or strain-level taxa.

Higher-level taxa are available from TaxParentChild, TaxNode, or TaxName. Note that if a plasmid from an organism is sequenced, then that organism may appear in this table, even though its genome is not sequenced. Use Scaffold.isGenomic=1 to avoid plasmid scaffolds, and use TaxParentChild to get genomes within a certain taxa. In particular, to avoid viruses, use parentId=131567, which corresponds to all cellular organisms.

Use Scaffold to get to complete chromosomes or contigs, and from there you can get to genes (the Locus table).

Taxonomy2Tree

Tree

Gene trees and species trees. The newick field contains the actual tree, in Newick format. For gene trees, the leaf ids are of the form locusId_version_begin_end or locusId_begin_end. For species trees, the leaf ids are taxonomy ids. See the tree-browser documentation for more information on how these trees are constructed. The full species tree always has name="MOSpeciesTree". There are also other species trees for subgroups of genomes. The type field is "species" for species trees or otherwise the type of family that was used to build the tree: 16S, Adhoc, COG, FastBLAST, Gene3D, PFAM, PIRSF, SMART, SSF, TIGRFAMs. For gene trees, the name is some sort of name for the family (e.g., the cogInfoId for COG trees).

Because private genomes are removed from pub.microbesonline.org, these trees may contain genes or organisms that are not in the public versions of the tables. Use the TreeUtils::simplify() routine or the Genomics/stat/simplifyTree.pl program to remove these leaves from the trees.

UserSessions

UserSessionsURLTracking

Users

graph_path

term

term2term

term_definition

term_synonym

Obtaining our source code

Popular perl scripts

use GenomicsUtils;
use Genome;
use Gene;
GenomicsUtils::connect();
my $genome = Genome::new("taxonomyId" => 511145);
my @genes = $genome->genes();
push @genes, $genome->nongenomicGenes();
foreach my $gene (@genes) {
   if($gene->type()==1) {
       my $l = $gene->treeOrthologs("taxa" => [83334,882]);
       while (my ($orthId, $hash) = each %$l) {
           print join("\t",$gene->locusId(), $orthId, $hash->{taxonomyId})."\n";
       }
   }
}

use GenomicsUtils;
use Gene;
GenomicsUtils::connect();
my @loci = (14484,14485);
my @taxa = (83334,882);
my $mogOrthologs = Gene::MOGForTaxa(\@loci, \@taxa);
while (my ($locus,$moghash) = each %$mogOrthologs) {
   while (my ($taxId,$orthId) = each %$moghash) {
      print join("\t", $locus, $orthId, $taxId)."\n";
   }
}