• NAME
• SYNOPSIS
• PURPOSE
• TUTORIAL
  • Obtaining an SQL dump
  • The Homo Sapiens Core SQL Dump
  • Configuring Schemaball
  • Parsing the Schema
• HISTORY
• BUGS
• AUTHOR
• CONTACT

NAME

sbtut_01 - fetching, parsing and viewing an SQL schema from a remote server

SYNOPSIS

  > mysql -u anonymous -h kaka.sanger.ac.uk -e "show databases" > databases.txt
  > grep homo_sapiens_core databases.txt
  homo_sapiens_core_19_34b
  homo_sapiens_core_20_34c
  homo_sapiens_core_21_34d
  > mysqldump -u anonymous -h kaka.sanger.ac.uk -d -n homo_sapiens_core_21_34d > hs.sql

PURPOSE

To introduce Schemaball, we'll download an SQL schema dump for a Ensembl human genome database. We'll create a simple configuration file, parse the database and create a schema diagram.

TUTORIAL

For this tutorial, the Ensembl database (www.ensembl.org) for the human genome will be used as an example. Ensembl is an open-source system for storing, analyzing and visualizing genomic data for any organism. It is a very popular public resource.

The diagram created in this tutorial will not contain any constraint lines. Tutorial 2 will show you how to parse constraint relationships from the schema and add them to the diagram.

Obtaining an SQL dump

Ensembl provides a public MySQL server, making it possible to query the database directly.

  host: kaka.sanger.ac.uk
  user: anonymous
  pass: <NO PASSWORD>

The Ensembl server contains a large number of databases. Data for each species stored in several databases. See www.ensembl.org/Docs for details. To list the databases at Ensembl

  > mysql -u anonymous -h kaka.sanger.ac.uk -e "show databases" 

Let's look at the tables with data for the human (homo sapiens) genome. At any point, there may be multiple versions of the database available. For example, at the time of writing, the ``core'' human database was available in three versions

  > homo_sapiens_core_19_34b
  > homo_sapiens_core_20_34c
  > homo_sapiens_core_21_34d

Version 21 is comprised of these databases

  > homo_sapiens_core_21_34d
  > homo_sapiens_disease_21_34d
  > homo_sapiens_est_21_34d
  > homo_sapiens_estgene_21_34d
  > homo_sapiens_haplotype_21_34d
  > homo_sapiens_lite_21_34d
  > homo_sapiens_snp_21_34d
  > homo_sapiens_vega_21_34d

Let's fetch the schema for the _core_ database.

  > mysqldump -u anonymous -h kaka.sanger.ac.uk -d -n homo_sapiens_core_21_34d 

It's important to use -d, so that only the table structure is downloaded and not the data, and -n, to avoid the database creation statement which can confuse the SQL::Translator module used by Schemaball.

The Homo Sapiens Core SQL Dump

The SQL schema dump consists of all the necessary CREATE TABLE directives needed to reconstruct the structure of the database. One such table is

  CREATE TABLE karyotype (
    karyotype_id int(10) unsigned NOT NULL auto_increment,
    seq_region_id int(10) unsigned NOT NULL default '0',
    seq_region_start int(10) NOT NULL default '0',
    seq_region_end int(10) NOT NULL default '0',
    band varchar(40) NOT NULL default '',
    stain varchar(40) NOT NULL default '',
    PRIMARY KEY  (karyotype_id),
    KEY region_band_idx (seq_region_id,band)
  ) TYPE=MyISAM;

Ensembl uses MyISAM tables, which do not support foreign keys in MySQL 4.0. By looking at the schema, you'll see that the primary key usually has the format TABLENAME_id and that foreign keys have the format TABLENAME_FIELD. For example, the karotype table uses karotype_id as its primary key and points to the seq_region table using the field seq_region_id. Because the schema is does not store these foreign keys using constraint options, you'll need to tell Schemaball how to figure out which fields are foreign keys in order to work out what the constraint relationships are. This process is addressed in tutorial 2 (sbtut_02).

Configuring Schemaball

Schemaball supports many configuration options. Many of them are optional and a small configuration file is required to get the job done. The configuration file for this tutorial, sbtut_01.conf, specifies the schema file in a <db> block and image parameters in an <image> block.

  <db>
  sqlfile = tutorial/01/hs.sql
  </db>

The image parameters direct where and how the table glyphs should be constructed. Since we don't have any foreign key relationships parsed, only table options need to be included.

  <image>
   file        = /home/httpd/htdocs/worldatwar/schema.png
   size        = 1000
   glyphradius = 0.7
   background  = ffffff
   foreground  = 000000
   <elements>
    <label>
     font   = tahoma.ttf
     size   = 9
    </label>
    <table>
     size    = 25
     fill    = 000000
     stroke  = 2
     outline = cccccc
   </table>
   </elements>
  </image>

Parsing the Schema

To parse the schema and produce the schema diagram,

  > schemaball -conf tutorial/01/sbtut_01.conf -debug > parsed.txt

The diagram is included as sbtut_01.png.

Schemaball generates useful information about the schema when the -debug flag is used.

• table list

  The structure of the schema, which includes the list of tables and constraints, is reported in ``schemadump'' lines.

    > grep schemadump parsed.txt
    debug schemadump table alt_allele
    debug schemadump table analysis
    debug schemadump table assembly
    ...
  

  At this point, these lines do not provide very useful information. First, we don't know anything about constraints (yet) and the table names can be easily parsed from the hs.sql file.

• element coordinates

  The coordinates of each element drawn by schemaball are reported in ``ballelem'' lines.

    > grep ballelem parsed.txt
    ballelem type text xy 506 125 rt 375 0.01733 rot 1.57080 size 25 label alt_allele 
    ballelem type circle xy 500 150 rt 350 0.00000 rot 0.00000 size 25 label alt_allele 
  

  These lines can be parsed to generate SVG images.

HISTORY

• v0.01 17 May 2004

BUGS

Please report errors and suggestions to martink@bcgsc.ca

AUTHOR

Martin Krzywinski, mkweb.bcgsc.ca, martink@bcgsc.ca

$Id: schemaball,v 1.5 2003/10/27 20:25:11 martink Exp $

CONTACT

  Martin Krzywinski
  Genome Sciences Centre
  Vancouver BC Canada
  www.bcgsc.ca
  mkweb.bcgsc.ca
  martink@bcgsc.ca