*** Ensembl database schema ***

This documents describes how to load the Ensembl database schema and to use the
patches to keep your schema up-to-date between releases.


1. Creating an empty Ensembl db from scratch:
=============================================

The Ensembl database schema is defined in the file ensembl/sql/table.sql. Use
the mysql commandline client to load this schema into a new database:

a. log into your MySQL database server and create a new database.

b. load the schema into this database:

  $ mysql -h mysql_host -P 3306 -u your_username -pyour_password \
      new_ensembl_db < ensembl/sql/table.sql


2. Patching an existing Ensembl database to the latest schema:
==============================================================

For each release, the core team provides patches to bring your databases to the
latest schema. Before release 39, there was a single patch file for each
release. From release 39, the patches will be split into multiple files, each
containing one "task". The new convention for patch files (to be found in
ensembl/sql) is:

  release < 39:   patch_FROM_TO.sql
  release >= 39:  patch_FROM_TO_[a-z].sql

where FROM is the schema version before patching, TO the version to
patch to, and [a-z] is a letter specifying the respective patch.

Each patch will put an entry into the meta table (meta_key = 'patch') to
indicate that it has been applied successfully.

This meta information is also used by the patch script to determine which
patches need to be applied to a db. The patch script is
ensembl/misc-scripts/schema_patch.pl, run with --help to see all options it
accepts (from the commandline or from an ini-style configuration file passed in
with --conffile). You can patch multiple database at a time by using an
appropriate --pattern.

Note that you can still apply the patches manually if you prefer.

a. Find out the schema version of your db(s):
---------------------------------------------

Look at the 'schema_version' entry in the meta table to find out the current
schema version of your db. You will have to apply all patches from this to the
current version sequentially, e.g. if you version is 36 and you want to patch to
39, you'll have to apply patches 36->37, 37->38 and 38->39.

b. Check which patches need to be applied:
------------------------------------------

This step is optional, you can proceed to step (c) immediately if you like.

  $ ensembl/misc-scripts/schema_patch.pl --host mysql_host --port 3306 \
      --user your_username --pass your_password --pattern %_core_39_% \
      --schema 39 --dry_run 1 --interactive 0

c. Actually patch the databases:
--------------------------------

  $ ensembl/misc-scripts/schema_patch.pl --host mysql_host --port 3306 \
      --user your_username --pass your_password --pattern %_core_39_% \
      --schema 39 --logfile /your/log/path/schema_patch.39.log

The script will run interactively and prompt you for each database matching the
pattern. --logfile is optional but useful for keeping track of what you've done.