<commit_before>  COMPARA PRODUCTION CYCLE
============================


#============================================================================
#  Intentions (declaration of intentions)
#============================================================================

1. Once the release coordinator has sent the mail for declaration of
intention, ask the other members of compara group (or related) what
they intend to produce. Sometimes, you have to wait a bit that gene
builders do their declaration first to know what compara will need to
produce and also potential schema changes. Compara has one extra day
to send the DOI, because we have to know which new species are added.

2. Submit the declaration of intentions using the http://admin.ensembl.org/ website.
Remember that compara has one additional day after the declaration of intentions deadline for the genebuilders.

3. Set up a web page with intentions in the Confluence wiki system to
allow easy tracking of the progress.

Note: The current (rel.71) compara_master database is sf5_ensembl_compara_master on compara1 

#============================================================================
# Important environment variables to be set
#============================================================================

4.1  As many example scripts/files in this document are given relative to $ENSEMBL_CVS_ROOT_DIR,
    make sure this variable is defined in your terminal
    (it is now necessary to run the Hive, so should be in your shell configs).

4.2 Also please make sure that you have set your shell's environment variable $ENSADMIN_PSW
    to the mysql password of 'ensadmin' user.


#============================================================================
# NCBI taxonomy data (handover to compara)
#============================================================================

The production team updates the ncbi_taxonomy database on livemirror just before
the handover to us (please check that this has been done).
We then need to update the tables on our master DB.

5.1 Update the ncbi_taxa_node and ncbi_taxa_name in the master DB using the
ncbi_taxonomy database located in mysql://ens-livemirror:3306/ncbi_taxonomy

$ time mysqldump -u ensro -h ens-livemirror -P3306 --extended-insert --compress --delayed-insert ncbi_taxonomy \
    ncbi_taxa_node ncbi_taxa_name | mysql -u ensadmin -p${ENSADMIN_PSW} -h compara1 sf5_ensembl_compara_master

# rel.60: 1 min
# rel.64: 45 sec
# rel.65: 47 sec
# rel.66: 47 sec
# rel.67: 30 sec
# rel.68: 30 sec
# rel.69: 35 sec
# rel.70  32 sec
# rel.71  34 sec

5.2 Update the extant taxon names

Each new species must have a 'ensembl alias name' tag. It should match the "web_name" used by
the production team in the "species" table of their "ensembl_production" database on staging1.

5.3 Update the ancestral taxon names

Each new extant species is anchored to the species tree at a certain taxon.
This taxon must be described with two fields in the ncbi_taxa_name table:
 - 'ensembl alias name': a "simple English" description of the taxon
 - 'ensembl timetree mya': the age of the taxon. It can be obtained from
    the TimeTree database (http://www.timetree.org)

Update the file $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/taxonomy/ensembl_aliases.sql to add
the two new tags

5.4 Load ensembl_aliases.sql onto the master database

$ mysql -u ensadmin -p${ENSADMIN_PSW} -h compara1 sf5_ensembl_compara_master < \
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/taxonomy/ensembl_aliases.sql

#============================================================================
# Master compara database (handover to compara)
#============================================================================

6.0  Update the registry configuration file $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl
that will be used throughout the release process.

Make sure to have edited the release numbers, added external core
databases and fixed name prefixes.
The convention right now (since 66) is to have the release database in
compara3.

Then check that file back into the CVS for everyone else to use.

6.1 Update the schema_version in master

>> update meta set meta_value = XX where meta_key = 'schema_version';

6.1.1 Update the release number in sql/table.sql

6.1.2 Create a patch file for the schema_version in sql/table.sql

6.2 Check the free space in the compara servers. Use this url:
http://www.ebi.ac.uk/~mp/compara_servers_disk_load.html
Assign where each pipeline will run.

6.3 Add in the master compara database
(compara1:3306/sf5_ensembl_compara_master) the new entries in the
genome_db and dnafrag tables. You can set up your registry and use the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl script. This script
sets the new genome_dbs as the default assemblies.
You have to create new genome_db_id (dnafrag) when it is a new assembly, or a new species. 
Sometimes it's done by the pairwise guys as they want to start building earlier. 

eg. (May have been run by kb3 or sf5 -- please ask. Make sure that the genomedb_id is added in the confluence page)
perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl \
 --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
 --compara compara_master --species "gadus_morhua" --collection ensembl

This script may take a while if the species you are adding is new. You can check the progress by counting dnaFrag entries in the master database:

>> select count(*) from dnafrag;

(If it is a new genebuild but the assembly doesn't change you can just edit the entry in master (genome_db table) introducing the new genebuild from meta.genebuild.start_date in the core database)

>> update genome_db set genebuild ="2011-07-FlyBase" where genome_db_id = 105;

6.4 Add in extra non-reference patches. (May have been run by kb3 or sf5 -- please ask)

To add extra non-reference patches to an assembly, eg human, you need the -force option to just add those dnafrags which aren't already in the database.

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    --compara compara_master --species human --force


7. (May have been run by kb3 or sf5 -- please ask). New method_link_species_set entries might be added using the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl script. The release
coordinator (or any team member) should create a new
method_link_species_set in the master database before starting a new
pipeline in order to get a unique method_link_species_set_id. Ideally
they can be created before starting to build the new database although
new method_link_species_sets can be added later on.
Sometimes the DNA alignment guys add the MLSS themselves. 
For the homologies this is done by the homologues guys

eg. for the pairwise alignment
$ perl create_mlss.pl --method_link_type  BLASTZ_NET --genome_db_id 22,51 --source "ensembl" \
    --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --compara compara_master

# --pw stands for all pairwised genome_db_ids in the list provided
# --sg stands for keep genome_db_id in the list alone (singleton) 



7.1. Create MLSS entries for homology side pipelines:

## choose a temp. directory where the output will be generated:

$ export MLSS_DIR="/tmp/mlss_creation"
$ mkdir $MLSS_DIR

## 

## run the loading script several times:

# orthologues
$ echo -e "201\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--pw --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_ORTHOLOGUES.201.out 2>$MLSS_DIR/create_mlss.ENSEMBL_ORTHOLOGUES.201.err

# paralogues btw
$ echo -e "202\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--pw --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.btw.202.out 2>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.btw.202.err

# paralogues wth
$ echo -e "202\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--sg --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.wth.202.out 2>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.wth.202.err

# proteintrees
$ echo -e "401\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "protein trees" --collection ensembl 1>$MLSS_DIR/create_mlss.PROTEIN_TREES.401.out 2>$MLSS_DIR/create_mlss.PROTEIN_TREES.401.err

# nctrees
$ echo -e "402\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "nc trees" --collection ensembl 1>$MLSS_DIR/create_mlss.NC_TREES.402.out 2>$MLSS_DIR/create_mlss.NC_TREES.402.err

# families
$ echo -e "301\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "families" --collection ensembl 1>$MLSS_DIR/create_mlss.FAMILY.301.out 2>$MLSS_DIR/create_mlss.FAMILY.301.err

Record the MLSSids in the confluence page.


## if output/error files are ok, remove them all:

$ rm -rf $MLSS_DIR

## ,otherwise make yourself a nice cup of tea and then *PANIC*


7.2. Update the species_set_tags.
Run the script: update_species_sets.pl

eg
$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_species_sets.pl \
    --conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --dbname compara_master > update_species_sets.out 2> update_species_sets.err

# Add/Update any dna tags if necessary (they may be already present - see NOTE below). There should be a tag for each of the multiple alignments.
# Note that fish will already have had a species_set_tag added by the above script.

# 6way epo
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34115,"name","primates"); ## Be careful with the IDs! They are ss_ids

# 12way epo
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34114,"name","mammals");  ## Be careful with the IDs!

# 19way mercator/pecan
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34117,"name","amniotes");  ## Be careful with the IDs!

# 35way low coverage
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34338,"name","mammals");  ## Be careful with the IDs!

# Check FISHES and BIRDS if there is any new alignments

#
# NOTE: create_mlss already performs this insert if --species_set_name option has been filled in on the command line
# (Check this! make sure this is the case)
 

7.3. Wait for the handover before starting to build the new database in case
any of the new species cannot make it. Don't forget to switch the
assembly_default values of genome_db in this case.
 [1] for species making it / used in the pipeline
 [0] for species not making it / or old assemblies


8. Create the new database for the new release and add it to your
registry configuration file. Use the $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/sql/table.sql
file to create the tables and populate the database with the relevant
primary data and genomic alignments that can be reused from the
previous release. This can be done with the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl script.  It
requires the master database, the previous released database and the
fresh new database with the tables already created. The script will
copy relevant data from the master and the old database into the new
one. 

$ mysql --defaults-group-suffix=_compara3 -e "CREATE DATABASE mm14_ensembl_compara_68"
$ mysql --defaults-group-suffix=_compara3 mm14_ensembl_compara_68 < $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/sql/table.sql

# Before you start copying, make a dry run of the populate_new_database.pl with -intentions flag to review the list of mlss_ids to be copied:

$ $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --old compara_prev --new compara_curr --intentions

# (takes about a minute and produces a long list)

# If you believe some of the entries should NOT be copied, you should manually add 'skip_mlss' and 'skip_ss' entries into master_db's meta table.

Rel.71: NB. There are cases where the mlss will not change but the underlying data does eg the H.sap-H.sap lastz-patch. This has a mlss_id of 556 and is currently set in the skip_mlss. If there are no human patches for the release, this should be removed to allow the data to be copied. If there is a new human patch, it should be added back.

# Now run the copying process (takes ~3 hours on a sunny day)

$ time $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --old compara_prev --new compara_curr

# took 3 hours for rel.pre57 (copied from rel.56)
# took 3 hours for rel.57 (copied from rel.pre57)
# took 2:15 hours for rel.58 (copied from rel.57)
# took 2:09 hours for rel.59 (copied from rel.58)
# took 3 hours for rel. 60 (copied from rel.59)
# rel.64:   2.6h
# rel.65:   2.5h
# rel.66:   4.8h
# rel.67:   2.1h (launched from compara3)
# rel.68:   1h40m (run on compara3)
# rel.69:   2.5h
# rel.70   ~3.5h (compara1 was slow)
# rel.71    4.1h (compara3)

If new method_link_species_sets are added in the master after this, you use this
script again to copy the new relevant data. In such case, you will have to:
 - skip the old_database in order to avoid trying to copy the dna-dna alignments and syntenies again
 - empty ncbi_taxa_name before

$ $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --new compara_curr

# 8.1. Copy the species_set_tag table 
# This needs to be added to the populate_new_database script and not just copied from the master where all old tags need to be deleted.
# But copy from master till this is done.
# INSERT INTO species_set_tag SELECT * FROM sf5_ensembl_compara_master.species_set_tag;

8.2. Add new species to phylogenetic tree
The easiest way to use this is to use the phylowidget.
From the EnsEMBL home page:
View full list of all Ensembl species
Species tree (Requires Java)

Select Arrow and select where you want the new species to go (use ncbi taxonomy or wikipedia etc) eg Gadus morhua
Then select in the menu "Tree Edit > Add > Sister"
Click on the empty node and edit name (add new name) and branch length

The tree should appear in the Toolbox but if not, then save the tree
Copy the new tree into
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/species_tree_blength.nh
cvs commit

## rel.65 -- Added Gadus morhua
## rel.66 -- Added Latimeria chalumnae (Coelacanth)
## Also included 'ensembl timetree mya' in ncbi_taxa_name for
## taxon_id: 8287 -- value: 414.9 in the master and final database.
## rel.69 added Xiphophorus_maculatus and Mustela_putorius_furo

9. Check that primary data (species data, dnafrags...) in the new compara DB
match the data in the corresponding core databases using the healthchecks. You
may have to edit the ensj-healthcheck/database.properties file, using
ensembl-compara/scripts/pipeline/database.properties as a reference.
Do not forget to replace ${ENSADMIN_PSW} with its appropriate, secret, value.

Make sure everything is up to date:
$ cd ensj-healthcheck
$ cvs update -Pd

Recompile:
$ export JAVA_HOME=/software/jdk1.6.0_01
$ ant

Now you can run the compara-compara_external_foreign_keys healthchecks:

$ time ./run-healthcheck.sh -d mp12_ensembl_compara_68 -type compara -d2 .+_core_69_.+ compara_external_foreign_keys

...and correct mismatches if any!


#============================================================================
# Merging (pre-compara handover)
#============================================================================

10. Merge data.

## NOTE: All the runs of copy_data.pl (except the last one) should have the flag "-re_enable 0" to avoid constantly recomputing the indices

10.1 TRANSLATED_BLAT_NET, BlastZ-Net, Pecan, Gerp

    The removal of old data shouldn't be necessary unless the skip_mlss entries are not up to date.
    Removal of old data :
        - Constrained elements are removed directly by mlss_id OF THE CONSTRAINED_ELEMENT:
> DELETE FROM constrained_element WHERE method_link_species_set_id=CE_MLSS_ID;

        - Conservation scores can be removed by gab_id, which can be done together with gabs:
> DELETE gab, cs FROM genomic_align_block gab LEFT JOIN conservation_score cs ON gab.genomic_align_block_id=cs.genomic_align_block_id \
    WHERE gab.method_link_species_set_id=Main_MLSS_ID;

          If the genomic_align_block table is empty, you may need to filter conservation scores by their ids
> DELETE FROM conservation_score WHERE FLOOR(genomic_align_block_id / 10000000000) = Main_MLSS_ID;

        - genomic_align_trees and genomic_align_groups are linked to genomic_aligns:
> DELETE ga, gag, gat FROM genomic_align ga LEFT JOIN genomic_align_group gag ON ga.genomic_align_id=gag.genomic_align_id \
    LEFT JOIN genomic_align_tree gat ON gag.node_id=gat.node_id \
    WHERE ga.method_link_species_set_id=Main_MLSS_ID;

          Again, when the data is very messy, some joins might fail. Here are the "rescue" queries:
> DELETE FROM genomic_align_tree WHERE FLOOR(node_id / 10000000000) = Main_MLSS_ID;
> DELETE FROM genomic_align_block WHERE method_link_species_set_id = Main_MLSS_ID;
> DELETE FROM genomic_align WHERE method_link_species_set_id = Main_MLSS_ID;

        - For the EPO alignments, you will need to delete the DNAFrag entries
> DELETE FROM dnafrag WHERE FLOOR(dnafrag_id / 10000000000) = Main_MLSS_ID;

    These data are usually in separate production databases. You can copy them using the
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl script.
    This script requires write access to the production database if the dnafrag_ids need fixing
    or the data must be copied in binary mode (this is required for conservation scores).
    Example:
$ bsub -q yesterday -R "select[mem>5000] rusage[mem=5000]" -M5000000 \
    -I time $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl \
    --from_url mysql://ensadmin:${ENSADMIN_PSW}@host/production_db \
    --to_url mysql://ensadmin:${ENSADMIN_PSW}@host/release_db --mlss 268

    Note_1: bear in mind that even though constrained elements and conservation scores are associated
    with a multiple alignment, they are not copied automatically and have their own mlss_id,
    so you should copy them by a separate execution of copy_data.

    Note_2: for copying conservation scores you have to provide rw_user and password for --from_url,
    because the script needs to write into the production database.

    Note_3: multiple alignments THAT PRODUCE ANCESTRAL SEQUENCES (not all of them do) eg EPO alignments, will also need
    the ancestral sequences copying to the core ancestral database. The copy_data script has been altered to automatically copy 
    the ancestral dnafrags into the compara database.

    Note_4: for multiple alignments that SHOULD HAVE BEEN automatically copied from the prev.release,
    check that you have the ancestral dnafrags copied. Again this should now happen automatically.

    Note 5: the "patch-to-ref" alignment as the mlss_id (eg. 556), which is skipped (via meta) because the data needs to be recomputed
    every release. This means that the entry in the mlss table is not present and must be manually added. This applies to both human 
    and mouse (as of e70). 

    Note 6: to avoid constantly recomputing the indices, there is a "-re_enable 0" flag on copy_data.pl.
            The last run of the script (for each type of data) should not have the flag, to re-enable the indices. Otherwise.
            the "keys disabled" properly will be transmitted to the staging / web servers, which may slow down the websites.

    Note 7: for human and mouse patches which have "changed" or no longer exist ("deleted") from the previous 
	    release the affected alignments will have to be removed from the release db. 

# rel.58
#           30m to copy over Human-vs-Marmoset
#
#           76m to copy over 33way LC EPO
#           23m to copy over 33way LC EPO constrained elements
#           70m to copy over 33way LC EPO conservation scores
#
# don't forget to copy ancestral dnafrags for 6-way primates! (done)
#
#           48m to copy over 6way primate EPO
#
# don't forget to copy ancestral dnafrags for 12-way eutherians! (done)
#
#           45m to copy over 12way eutherian EPO
#
#           43m to copy over 16way placental mercator/pecan
#           17m to copy over 16way placental mercator/pecan constrained elements
#            1h to copy over 16way placental mercator/pecan conservation scores

# rel.63
#           53m to copy over Human-vs-Marmoset LASTZ
#           54m to copy over Human-vs-Microbat LASTZ
#
#           54m to copy over 6way primate EPO
#           77m to copy over 12way mammal EPO
#
#           65m to copy over 19way amniota PECAN
#           10m to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#           40m to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES
#
#           373m to copy over 35way mammal LC EPO
#           66m to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS
#           61m to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES
#
#           90m to copy over 5way fish EPO
#           27m to copy over 5way fish EPO GERP_CONSTRAINED_ELEMENTS
#           23m to copy over 5way fish EPO GERP_CONSERVATION_SCORES

# rel.64
#           <1h to copy over Human patches LASTZ
#           50m to copy over Cow-vs-Pig LASTZ
#           51m to copy over Human-vs-Cow LASTZ
#            1h to copy over Human-vs-TasmanianDevil LASTZ
#           45m to copy over Human-vs-Gorilla LASTZ
#            1h to copy over Opossum-vs-TasmanianDevil LASTZ
#
#           44m to copy over Lamprey-vs-DanioRerio TBLAT
#           43m to copy over Lamprey-vs-GasterosteusAculeatus TBLAT
#           44m to copy over Lamprey-vs-CionaIntestinalis TBLAT
#           46m to copy over Human-vs-Lamprey TBLAT
#
#           52m to copy over 6way primate EPO
#           1.8h to copy over 12way mammal EPO
#
#           1h10m to copy over 19way amniota PECAN
#           3.2h  to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#           43m   to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES
#
#           3h    to copy over 35way mammal LC EPO
#           44m   to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS (in trust_ce mode!)
#           1h    to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES

# rel.65
#	    70m to copy over Human-vs-Chimpanzee LASTZ
#	    86m to copy over Human-vs-Bushbaby LASTZ
#	    73m to copy over Cod-vs-Danio rerio LASTZ
#	    74m to copy over Cod-vs-Stickleback LASTZ

#	    76m to copy over Human-vs-Cod TBLAT

#	    81m to copy over 6way primate EPO
#	    2h39m to copy over 12way mammal EPO

#	    1h58m to copy over 19way amniota PECAN
#	    14m to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#	    60m to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES

#	    3h7m to copy over 35way mammal LC EPO
#	    72m to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS
#	    78m to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES

# rel.66
#      Human-vs-Chimpanzee LASTZ
#      80m to copy over Cint-vs-Csav LASTZ
#      84m to copy over Human-vs-Chimp LASTZ
#      1m31s (no FK re-enable) to copy over Human-vs-Gorilla LASTZ
#      1m43s to copy over Human-vs-Orang LASTZ
#      1m33s to copy over Human-vs-Gibbon LASTZ
#      2m8s  to copy over Human-vs-Macaque LASTZ
#      2m56s to copy over Human-vs-Marmoset LASTZ

#      49s to copy over Human-vs-Cint TBLAT
#      78s to copy over Mouse-vs-Cint TBLAT
#      50s to copy over Cint-vs-Zebrafish TBLAT
#      44s to copy over Cint-vs-Lamprey TBLAT
#      1m8s to copy over Danio-vs-Coelacanth TBLAT
#      1m10s to copy over Stickleback-vs-Coelacanth TBLAT
#      1m9s to copy over Xenopus-vs-Coelacanth TBLAT

#      56s to copy over Human-vs-Human(lastz import from UCSC)

#      40s to copy over Human_ref-vs-Human_patches

# rel.67
#      57m: sf5_hsap_stri_lastz_67  mlss_id=576 [forgot to switch off keys)
#       4m: sf5_hsap_sscr_lastz_67  mlss_id=577
#       4m: kb3_sscr_btau_lastz_67  mlss_id=579
#      50s: kb3_hsap_onil_tblat_67  mlss_id=573
#      70s: sf5_mmus_onil           mlss_id=575
#      60s: kb3_drer_onil_tblat_67  mlss_id=574
#      30s: sf5_compara_human_lastz_patch_and_haplotype_67 mlss_id=556
#       4m: kb3_pecan_19way_67      mlss_id=580
#       3m: kb3_pecan_19way_67      ce_mlss_id=581
#      31m: kb3_pecan_19way_67      cs_mlss_id=50037
#      42m: kb3_epo_35way_67        mlss_id=582
#      13m: kb3_epo_35way_67        ce_mlss_id=583
#      53m: kb3_epo_35way_67        cs_mlss_id=50038
#       5m: kb3_12WAY_67            mlss_id=578

# rel.68
#  a few minutes to copy the database and ~1 hour to build the indexes

# rel.69
# several hours to copy from the pipeline databases and re-build the indexes

#rel 71.
#     2m: kb3_hsap_ggal_lastz_71  mlss_id=632
#     1m  kb3_mmus_ggal_lastz_71  mlss_id=633
#     1m  kb3_ggal_mgap_lastz_71  mlss_id=634
#     1m  kb3_ggal_xtro_tblat_71  mlss_id=638
#     1m  kb3_hsap_ggal_tblat_71  mlss_id=637
#     1m  sf5_olat_gmor_lastz_71  mlss_id=625
#     3m  kb3_pecan_20way_71      mlss_id=630
#     4m  kb3_pecan_20way_71      mlss_id=631
#    35m  kb3_pecan_20way_71      mlss_id=50045
#     3m  sf5_compara_epo_6way_71 mlss_id=548
#     1m  sf5_olat_onil_lastz_71  mlss_id=626
#     1m  sf5_olat_xmac_lastz_71  mlss_id=627
#     1m  sf5_epo_low_8way_fish_71 mlss_id=628
#     2m  sf5_epo_low_8way_fish_71 mlss_id=629
#     9m  sf5_epo_low_8way_fish_71 mlss_id=50044
#     1m  kb3_ggal_drer_tblat_71   mlss_id=639
#     1m  kb3_ggal_csav_tblat_71   mlss_id=640
#     1m  sf5_ggal_acar_lastz_71   mlss_id=636
#    91m  sf5_ggal_tgut_lastz_7    mlss_id=635 (re-enable 1)
#    93m  sf5_compara_epo_3way_birds_71 mlss_id=641 (re-enable 1)
#    14m  sf5_compara_epo_3way_birds_71 mlss_id=642 (re-enable 1)
#    16m  sf5_compara_epo_3way_birds_71 mlss_id=50046 (re-enable 1)

10.1.1 Human patches for high coverage blastz-net alignments
## Make sure that all the pairwise alignment are in the database
before loading these (TRANSLATED_BLAT_NET and BlastZ-Net).

         # Find all the relevant mlss_ids (NOTE you will need to do this 3 times in all. Once each for both human and mouse, and again for mouse patches against human - from e70):
$ export AFFECTED_MLSSS=`mysql --defaults-group-suffix=_compara2 kb3_mmus_lastz_hap_70 -N -e "select group_concat(distinct(method_link_species_set_id) SEPARATOR ' ') from genomic_align join dnafrag using (dnafrag_id) where genome_db_id=134 and method_link_species_set_id<1000"`

        # Use the --merge option of copy_data:
$ time for i in $AFFECTED_MLSSS; do echo $i; $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl --from_url mysql://ensro@compara2/kb3_mmus_lastz_hap_70 --to_url mysql://ensadmin:${ENSADMIN_PSW}@compara3/sf5_ensembl_compara_70 --mlss $i --merge; done

# rel.64: 1h (13 affected mlsss)
# rel.66: 12m49s (14 affected mlss_ids)
# rel.67: 10m  (14 affected mlss_ids)
# rel.70: 14min for human (14 mlssids) and 5min for mouse (5 mlssids)

10.2 Syntenies

    1) First make sure the entries in $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl file
        point at the latest (staging) versions of the core databases.
    
    2) Then run something like:
$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/synteny/LoadSyntenyData.pl --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    --dbname compara_curr -ref "Homo sapiens" -nonref "Callithrix jacchus" -mlss_id 10052 \
    /lustre/scratch101/ensembl/kb3/scratch/hive/release_64/kb3_hsap_cjac_synteny_64/synteny/all.100000.100000.BuildSynteny

    3) Check the output of the script whether it has created a new MLSS entry (it should not).
       If so, update the name of the newly created MLSS and copy it to the master database.


======================================


10.3 Putting together the database of ancestral sequence is now done using a dedicated Hive-Core mini-pipeline:

     Check you have the most recent core checkout ie the correct schema and patch files are added to the meta table.

    Go to $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file AncestralMerge_conf.pm .

    Make sure you have edited/checked the following:
        1) current release number
        2) names and locations of current and previous ancestral core databases
        3) the table of ancestral sequence sources in the second analysis (some entries might point to the previous release ancestral database, some will be new)

    For (3), you can run the following query on your release database and on the previous database:
> SELECT * FROM method_link_species_set WHERE method_link_id = 13; (equivalent to method_link_type = "EPO")
      The new mlss_id should be attached to their production database:
         '578' => 'mysql://ensadmin:$ENSADMIN_PSW@compara1/kb3_12WAY_67_ancestral_core' 
      The mlss_id that are reused should be linked to the previous database
         '505' => $self->o('prev_ancestral_db'),

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl AncestralMerge_conf.pm -password $ENS<PASSWORD>

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will merge the separate ancestral core sources into ensembl_ancestral_{rel_number}.
    
    You may want to check the msg table for errors and have a look at the result of the merger:
> SELECT left(name,12) na, count(*), min(seq_region_id), max(seq_region_id), max(seq_region_id)-min(seq_region_id)+1 FROM seq_region GROUP BY na;

    If everything is ok, drop hive-specific tables:
    You may need to remove a foreign key in analysis_description before that.

> ALTER TABLE analysis_description DROP KEY analysis_idx;
> CALL drop_hive_tables;
> TRUNCATE analysis; TRUNCATE analysis_description;

    Make sure all tables are turned into myISAM.

# rel.67  20min
# rel.71  20min

10.4 Merging GeneTrees+Families+NCTrees together is now done by running a mini-pipeline.

10.4.1  Make sure you have performed StableID mapping step on ProteinTrees (done manually)

10.4.2  Make sure you have performed TreeFam mapping step on
ProteinTrees (done manually)


(10.4.3 -- Removed)

10.4.4  Actually perform the merging step:

    Go to $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file MergeHomologySideTogether_conf.pm
    
    It has 6 sections for connecting to databases where you will have to change the names of the databases and possibly their locations:
        pipeline_db  - is your intermediate target (all protein side pipelines merged together)
        master_db    - is the main compara master
        prevrel_db   - should point to the previous release database
        genetrees_db - should point to the current GeneTrees pipeline database
        families_db  - should point to the current Families pipeline database
        nctrees_db   - should point to the current ncRNAtrees pipeline database

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl MergeHomologySideTogether_conf.pm

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will create a database with protein side pipeline databases merged together.

#                 2.7h to execute for release 63
#                 9.6h to execute for release 65
#                 ~9h to execute for release 66
#                 ~3h to execute for release 71

10.4.5 Run the script to include projections of genes as homologies for human and mouse

   First update the AUTO_INCREMENT of the following tables to 
> ALTER TABLE member   AUTO_INCREMENT=300000001;
> ALTER TABLE sequence AUTO_INCREMENT=300000001;
> ALTER TABLE homology AUTO_INCREMENT=300000001;
   It makes easier to spot the new entries

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/convert_patch_to_compara_homologies.pl \
    -reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    -comp_alias compara_homology_merged -species homo_sapiens

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/convert_patch_to_compara_homologies.pl \
    -reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    -comp_alias compara_homology_merged -species mus_musculus

# For e70 - human
#new compara entries:
#578 ref genes
#1028 projected genes
#1459 new homologies

#For e71 mouse
new compara entries:
10 ref genes
22 projected genes
65 new homologies


# NB: The underscore in the species name is necessary to match the genome_db name.
# There is a "-no_store 1" flag to avoid writing to the compara database.

#         30min for release 65
#          3min for release 66
#          2min for release 67

10.5 Final merger of "protein side" into the release database is done by running another mini-pipeline.

    Go to (or stay in) $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file MergeHomologyIntoRelease_conf.pm
    
    Usually, only the release version and the server names have to be changed. You can still double-check the 3 sections:
        pipeline_db        - is a hive database that is only used for job tracking - it may/should be removed right after the pipeline is done
        merged_homology_db - is the result of the previous step (protein side databases merged together)
        rel_db             - is the main release database

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl MergeHomologyIntoRelease_conf.pm -password $ENS<PASSWORD>W -hive_driver sqlite

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will merge the protein side tables into the main release database.

# 2.6h for rel65
# ~1.0h for rel66

10.6 Populate the member_production_counts table using the script at $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/production/populate_member_production_counts_table.sql

# For 72 you should run something like...
$ time mysql -hcompara3 -uensadmin -pensembl kb3_ensembl_compara_72 < $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/production/populate_member_production_counts_table.sql
# Time to complete? 

10.7 Cleanup and CVS commit

    After you are happy about the result of both mergers
    you can drop both "compara_homology_merged" and "compara_full_merge" databases.

    Also, please commit the changes to the PipeConfig files that you have made.


11. Drop method_link_species_set entries for alignments which did not make it.

11.5 Check for method_links that do not have a corresponding method_link_species_set:
> SELECT ml.* FROM method_link ml LEFT JOIN method_link_species_set mlss ON ml.method_link_id=mlss.method_link_id WHERE mlss.method_link_id IS NULL;
In most cases they can be removed, but check with other members of Compara.

Removal of redundant method_link entries:
> DELETE ml FROM method_link ml LEFT JOIN method_link_species_set mlss ON ml.method_link_id=mlss.method_link_id WHERE mlss.method_link_id IS NULL;


12. Updating member.display_label fields for the members generated by EnsEMBL prediction.

This step has to happen ASAP, but AFTER the Core name projections have been done. And before you analyze/optimize the tables.
(1. Matthieu/Leo runs the Homology pipeline
 2. The homology database is given to Rhoda, who uses it to run name projections on Core databases (display_labels and gene_descriptions change in Core databases)
 3. We use the information in Core databases (derived from Homology, i.e. Compara) to fix the display_labels and gene_descriptions for Compara
!MAKE SURE YOU ARE IN SYNC WITH THE REST OF THE WORLD!
)
<commit_msg>Added estimated time for populate_member_production_counts_table.sql<commit_after>  COMPARA PRODUCTION CYCLE
============================


#============================================================================
#  Intentions (declaration of intentions)
#============================================================================

1. Once the release coordinator has sent the mail for declaration of
intention, ask the other members of compara group (or related) what
they intend to produce. Sometimes, you have to wait a bit that gene
builders do their declaration first to know what compara will need to
produce and also potential schema changes. Compara has one extra day
to send the DOI, because we have to know which new species are added.

2. Submit the declaration of intentions using the http://admin.ensembl.org/ website.
Remember that compara has one additional day after the declaration of intentions deadline for the genebuilders.

3. Set up a web page with intentions in the Confluence wiki system to
allow easy tracking of the progress.

Note: The current (rel.71) compara_master database is sf5_ensembl_compara_master on compara1 

#============================================================================
# Important environment variables to be set
#============================================================================

4.1  As many example scripts/files in this document are given relative to $ENSEMBL_CVS_ROOT_DIR,
    make sure this variable is defined in your terminal
    (it is now necessary to run the Hive, so should be in your shell configs).

4.2 Also please make sure that you have set your shell's environment variable $ENSADMIN_PSW
    to the mysql password of '<PASSWORD>' user.


#============================================================================
# NCBI taxonomy data (handover to compara)
#============================================================================

The production team updates the ncbi_taxonomy database on livemirror just before
the handover to us (please check that this has been done).
We then need to update the tables on our master DB.

5.1 Update the ncbi_taxa_node and ncbi_taxa_name in the master DB using the
ncbi_taxonomy database located in mysql://ens-livemirror:3306/ncbi_taxonomy

$ time mysqldump -u ensro -h ens-livemirror -P3306 --extended-insert --compress --delayed-insert ncbi_taxonomy \
    ncbi_taxa_node ncbi_taxa_name | mysql -u ensadmin -p${ENSADMIN_PSW} -h compara1 sf5_ensembl_compara_master

# rel.60: 1 min
# rel.64: 45 sec
# rel.65: 47 sec
# rel.66: 47 sec
# rel.67: 30 sec
# rel.68: 30 sec
# rel.69: 35 sec
# rel.70  32 sec
# rel.71  34 sec

5.2 Update the extant taxon names

Each new species must have a 'ensembl alias name' tag. It should match the "web_name" used by
the production team in the "species" table of their "ensembl_production" database on staging1.

5.3 Update the ancestral taxon names

Each new extant species is anchored to the species tree at a certain taxon.
This taxon must be described with two fields in the ncbi_taxa_name table:
 - 'ensembl alias name': a "simple English" description of the taxon
 - 'ensembl timetree mya': the age of the taxon. It can be obtained from
    the TimeTree database (http://www.timetree.org)

Update the file $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/taxonomy/ensembl_aliases.sql to add
the two new tags

5.4 Load ensembl_aliases.sql onto the master database

$ mysql -u ensadmin -p${ENSADMIN_PSW} -h compara1 sf5_ensembl_compara_master < \
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/taxonomy/ensembl_aliases.sql

#============================================================================
# Master compara database (handover to compara)
#============================================================================

6.0  Update the registry configuration file $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl
that will be used throughout the release process.

Make sure to have edited the release numbers, added external core
databases and fixed name prefixes.
The convention right now (since 66) is to have the release database in
compara3.

Then check that file back into the CVS for everyone else to use.

6.1 Update the schema_version in master

>> update meta set meta_value = XX where meta_key = 'schema_version';

6.1.1 Update the release number in sql/table.sql

6.1.2 Create a patch file for the schema_version in sql/table.sql

6.2 Check the free space in the compara servers. Use this url:
http://www.ebi.ac.uk/~mp/compara_servers_disk_load.html
Assign where each pipeline will run.

6.3 Add in the master compara database
(compara1:3306/sf5_ensembl_compara_master) the new entries in the
genome_db and dnafrag tables. You can set up your registry and use the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl script. This script
sets the new genome_dbs as the default assemblies.
You have to create new genome_db_id (dnafrag) when it is a new assembly, or a new species. 
Sometimes it's done by the pairwise guys as they want to start building earlier. 

eg. (May have been run by kb3 or sf5 -- please ask. Make sure that the genomedb_id is added in the confluence page)
perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl \
 --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
 --compara compara_master --species "gadus_morhua" --collection ensembl

This script may take a while if the species you are adding is new. You can check the progress by counting dnaFrag entries in the master database:

>> select count(*) from dnafrag;

(If it is a new genebuild but the assembly doesn't change you can just edit the entry in master (genome_db table) introducing the new genebuild from meta.genebuild.start_date in the core database)

>> update genome_db set genebuild ="2011-07-FlyBase" where genome_db_id = 105;

6.4 Add in extra non-reference patches. (May have been run by kb3 or sf5 -- please ask)

To add extra non-reference patches to an assembly, eg human, you need the -force option to just add those dnafrags which aren't already in the database.

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_genome.pl --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    --compara compara_master --species human --force


7. (May have been run by kb3 or sf5 -- please ask). New method_link_species_set entries might be added using the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl script. The release
coordinator (or any team member) should create a new
method_link_species_set in the master database before starting a new
pipeline in order to get a unique method_link_species_set_id. Ideally
they can be created before starting to build the new database although
new method_link_species_sets can be added later on.
Sometimes the DNA alignment guys add the MLSS themselves. 
For the homologies this is done by the homologues guys

eg. for the pairwise alignment
$ perl create_mlss.pl --method_link_type  BLASTZ_NET --genome_db_id 22,51 --source "ensembl" \
    --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --compara compara_master

# --pw stands for all pairwised genome_db_ids in the list provided
# --sg stands for keep genome_db_id in the list alone (singleton) 



7.1. Create MLSS entries for homology side pipelines:

## choose a temp. directory where the output will be generated:

$ export MLSS_DIR="/tmp/mlss_creation"
$ mkdir $MLSS_DIR

## 

## run the loading script several times:

# orthologues
$ echo -e "201\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--pw --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_ORTHOLOGUES.201.out 2>$MLSS_DIR/create_mlss.ENSEMBL_ORTHOLOGUES.201.err

# paralogues btw
$ echo -e "202\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--pw --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.btw.202.out 2>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.btw.202.err

# paralogues wth
$ echo -e "202\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--sg --collection ensembl 1>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.wth.202.out 2>$MLSS_DIR/create_mlss.ENSEMBL_PARALOGUES.wth.202.err

# proteintrees
$ echo -e "401\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "protein trees" --collection ensembl 1>$MLSS_DIR/create_mlss.PROTEIN_TREES.401.out 2>$MLSS_DIR/create_mlss.PROTEIN_TREES.401.err

# nctrees
$ echo -e "402\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "nc trees" --collection ensembl 1>$MLSS_DIR/create_mlss.NC_TREES.402.out 2>$MLSS_DIR/create_mlss.NC_TREES.402.err

# families
$ echo -e "301\n" | perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/create_mlss.pl --f \
--reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
--name "families" --collection ensembl 1>$MLSS_DIR/create_mlss.FAMILY.301.out 2>$MLSS_DIR/create_mlss.FAMILY.301.err

Record the MLSSids in the confluence page.


## if output/error files are ok, remove them all:

$ rm -rf $MLSS_DIR

## ,otherwise make yourself a nice cup of tea and then *PANIC*


7.2. Update the species_set_tags.
Run the script: update_species_sets.pl

eg
$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/update_species_sets.pl \
    --conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --dbname compara_master > update_species_sets.out 2> update_species_sets.err

# Add/Update any dna tags if necessary (they may be already present - see NOTE below). There should be a tag for each of the multiple alignments.
# Note that fish will already have had a species_set_tag added by the above script.

# 6way epo
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34115,"name","primates"); ## Be careful with the IDs! They are ss_ids

# 12way epo
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34114,"name","mammals");  ## Be careful with the IDs!

# 19way mercator/pecan
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34117,"name","amniotes");  ## Be careful with the IDs!

# 35way low coverage
#> INSERT INTO species_set_tag (species_set_id, tag,value) VALUES (34338,"name","mammals");  ## Be careful with the IDs!

# Check FISHES and BIRDS if there is any new alignments

#
# NOTE: create_mlss already performs this insert if --species_set_name option has been filled in on the command line
# (Check this! make sure this is the case)
 

7.3. Wait for the handover before starting to build the new database in case
any of the new species cannot make it. Don't forget to switch the
assembly_default values of genome_db in this case.
 [1] for species making it / used in the pipeline
 [0] for species not making it / or old assemblies


8. Create the new database for the new release and add it to your
registry configuration file. Use the $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/sql/table.sql
file to create the tables and populate the database with the relevant
primary data and genomic alignments that can be reused from the
previous release. This can be done with the
$ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl script.  It
requires the master database, the previous released database and the
fresh new database with the tables already created. The script will
copy relevant data from the master and the old database into the new
one. 

$ mysql --defaults-group-suffix=_compara3 -e "CREATE DATABASE mm14_ensembl_compara_68"
$ mysql --defaults-group-suffix=_compara3 mm14_ensembl_compara_68 < $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/sql/table.sql

# Before you start copying, make a dry run of the populate_new_database.pl with -intentions flag to review the list of mlss_ids to be copied:

$ $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --old compara_prev --new compara_curr --intentions

# (takes about a minute and produces a long list)

# If you believe some of the entries should NOT be copied, you should manually add 'skip_mlss' and 'skip_ss' entries into master_db's meta table.

Rel.71: NB. There are cases where the mlss will not change but the underlying data does eg the H.sap-H.sap lastz-patch. This has a mlss_id of 556 and is currently set in the skip_mlss. If there are no human patches for the release, this should be removed to allow the data to be copied. If there is a new human patch, it should be added back.

# Now run the copying process (takes ~3 hours on a sunny day)

$ time $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --old compara_prev --new compara_curr

# took 3 hours for rel.pre57 (copied from rel.56)
# took 3 hours for rel.57 (copied from rel.pre57)
# took 2:15 hours for rel.58 (copied from rel.57)
# took 2:09 hours for rel.59 (copied from rel.58)
# took 3 hours for rel. 60 (copied from rel.59)
# rel.64:   2.6h
# rel.65:   2.5h
# rel.66:   4.8h
# rel.67:   2.1h (launched from compara3)
# rel.68:   1h40m (run on compara3)
# rel.69:   2.5h
# rel.70   ~3.5h (compara1 was slow)
# rel.71    4.1h (compara3)

If new method_link_species_sets are added in the master after this, you use this
script again to copy the new relevant data. In such case, you will have to:
 - skip the old_database in order to avoid trying to copy the dna-dna alignments and syntenies again
 - empty ncbi_taxa_name before

$ $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/populate_new_database.pl \
    --reg-conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl --master compara_master --new compara_curr

# 8.1. Copy the species_set_tag table 
# This needs to be added to the populate_new_database script and not just copied from the master where all old tags need to be deleted.
# But copy from master till this is done.
# INSERT INTO species_set_tag SELECT * FROM sf5_ensembl_compara_master.species_set_tag;

8.2. Add new species to phylogenetic tree
The easiest way to use this is to use the phylowidget.
From the EnsEMBL home page:
View full list of all Ensembl species
Species tree (Requires Java)

Select Arrow and select where you want the new species to go (use ncbi taxonomy or wikipedia etc) eg Gadus morhua
Then select in the menu "Tree Edit > Add > Sister"
Click on the empty node and edit name (add new name) and branch length

The tree should appear in the Toolbox but if not, then save the tree
Copy the new tree into
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/species_tree_blength.nh
cvs commit

## rel.65 -- Added Gadus morhua
## rel.66 -- Added Latimeria chalumnae (Coelacanth)
## Also included 'ensembl timetree mya' in ncbi_taxa_name for
## taxon_id: 8287 -- value: 414.9 in the master and final database.
## rel.69 added Xiphophorus_maculatus and Mustela_putorius_furo

9. Check that primary data (species data, dnafrags...) in the new compara DB
match the data in the corresponding core databases using the healthchecks. You
may have to edit the ensj-healthcheck/database.properties file, using
ensembl-compara/scripts/pipeline/database.properties as a reference.
Do not forget to replace ${ENSADMIN_PSW} with its appropriate, secret, value.

Make sure everything is up to date:
$ cd ensj-healthcheck
$ cvs update -Pd

Recompile:
$ export JAVA_HOME=/software/jdk1.6.0_01
$ ant

Now you can run the compara-compara_external_foreign_keys healthchecks:

$ time ./run-healthcheck.sh -d mp12_ensembl_compara_68 -type compara -d2 .+_core_69_.+ compara_external_foreign_keys

...and correct mismatches if any!


#============================================================================
# Merging (pre-compara handover)
#============================================================================

10. Merge data.

## NOTE: All the runs of copy_data.pl (except the last one) should have the flag "-re_enable 0" to avoid constantly recomputing the indices

10.1 TRANSLATED_BLAT_NET, BlastZ-Net, Pecan, Gerp

    The removal of old data shouldn't be necessary unless the skip_mlss entries are not up to date.
    Removal of old data :
        - Constrained elements are removed directly by mlss_id OF THE CONSTRAINED_ELEMENT:
> DELETE FROM constrained_element WHERE method_link_species_set_id=CE_MLSS_ID;

        - Conservation scores can be removed by gab_id, which can be done together with gabs:
> DELETE gab, cs FROM genomic_align_block gab LEFT JOIN conservation_score cs ON gab.genomic_align_block_id=cs.genomic_align_block_id \
    WHERE gab.method_link_species_set_id=Main_MLSS_ID;

          If the genomic_align_block table is empty, you may need to filter conservation scores by their ids
> DELETE FROM conservation_score WHERE FLOOR(genomic_align_block_id / 10000000000) = Main_MLSS_ID;

        - genomic_align_trees and genomic_align_groups are linked to genomic_aligns:
> DELETE ga, gag, gat FROM genomic_align ga LEFT JOIN genomic_align_group gag ON ga.genomic_align_id=gag.genomic_align_id \
    LEFT JOIN genomic_align_tree gat ON gag.node_id=gat.node_id \
    WHERE ga.method_link_species_set_id=Main_MLSS_ID;

          Again, when the data is very messy, some joins might fail. Here are the "rescue" queries:
> DELETE FROM genomic_align_tree WHERE FLOOR(node_id / 10000000000) = Main_MLSS_ID;
> DELETE FROM genomic_align_block WHERE method_link_species_set_id = Main_MLSS_ID;
> DELETE FROM genomic_align WHERE method_link_species_set_id = Main_MLSS_ID;

        - For the EPO alignments, you will need to delete the DNAFrag entries
> DELETE FROM dnafrag WHERE FLOOR(dnafrag_id / 10000000000) = Main_MLSS_ID;

    These data are usually in separate production databases. You can copy them using the
    $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl script.
    This script requires write access to the production database if the dnafrag_ids need fixing
    or the data must be copied in binary mode (this is required for conservation scores).
    Example:
$ bsub -q yesterday -R "select[mem>5000] rusage[mem=5000]" -M5000000 \
    -I time $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl \
    --from_url mysql://ensadmin:${ENSADMIN_PSW}@host/production_db \
    --to_url mysql://ensadmin:${ENSADMIN_PSW}@host/release_db --mlss 268

    Note_1: bear in mind that even though constrained elements and conservation scores are associated
    with a multiple alignment, they are not copied automatically and have their own mlss_id,
    so you should copy them by a separate execution of copy_data.

    Note_2: for copying conservation scores you have to provide rw_user and password for --from_url,
    because the script needs to write into the production database.

    Note_3: multiple alignments THAT PRODUCE ANCESTRAL SEQUENCES (not all of them do) eg EPO alignments, will also need
    the ancestral sequences copying to the core ancestral database. The copy_data script has been altered to automatically copy 
    the ancestral dnafrags into the compara database.

    Note_4: for multiple alignments that SHOULD HAVE BEEN automatically copied from the prev.release,
    check that you have the ancestral dnafrags copied. Again this should now happen automatically.

    Note 5: the "patch-to-ref" alignment as the mlss_id (eg. 556), which is skipped (via meta) because the data needs to be recomputed
    every release. This means that the entry in the mlss table is not present and must be manually added. This applies to both human 
    and mouse (as of e70). 

    Note 6: to avoid constantly recomputing the indices, there is a "-re_enable 0" flag on copy_data.pl.
            The last run of the script (for each type of data) should not have the flag, to re-enable the indices. Otherwise.
            the "keys disabled" properly will be transmitted to the staging / web servers, which may slow down the websites.

    Note 7: for human and mouse patches which have "changed" or no longer exist ("deleted") from the previous 
	    release the affected alignments will have to be removed from the release db. 

# rel.58
#           30m to copy over Human-vs-Marmoset
#
#           76m to copy over 33way LC EPO
#           23m to copy over 33way LC EPO constrained elements
#           70m to copy over 33way LC EPO conservation scores
#
# don't forget to copy ancestral dnafrags for 6-way primates! (done)
#
#           48m to copy over 6way primate EPO
#
# don't forget to copy ancestral dnafrags for 12-way eutherians! (done)
#
#           45m to copy over 12way eutherian EPO
#
#           43m to copy over 16way placental mercator/pecan
#           17m to copy over 16way placental mercator/pecan constrained elements
#            1h to copy over 16way placental mercator/pecan conservation scores

# rel.63
#           53m to copy over Human-vs-Marmoset LASTZ
#           54m to copy over Human-vs-Microbat LASTZ
#
#           54m to copy over 6way primate EPO
#           77m to copy over 12way mammal EPO
#
#           65m to copy over 19way amniota PECAN
#           10m to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#           40m to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES
#
#           373m to copy over 35way mammal LC EPO
#           66m to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS
#           61m to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES
#
#           90m to copy over 5way fish EPO
#           27m to copy over 5way fish EPO GERP_CONSTRAINED_ELEMENTS
#           23m to copy over 5way fish EPO GERP_CONSERVATION_SCORES

# rel.64
#           <1h to copy over Human patches LASTZ
#           50m to copy over Cow-vs-Pig LASTZ
#           51m to copy over Human-vs-Cow LASTZ
#            1h to copy over Human-vs-TasmanianDevil LASTZ
#           45m to copy over Human-vs-Gorilla LASTZ
#            1h to copy over Opossum-vs-TasmanianDevil LASTZ
#
#           44m to copy over Lamprey-vs-DanioRerio TBLAT
#           43m to copy over Lamprey-vs-GasterosteusAculeatus TBLAT
#           44m to copy over Lamprey-vs-CionaIntestinalis TBLAT
#           46m to copy over Human-vs-Lamprey TBLAT
#
#           52m to copy over 6way primate EPO
#           1.8h to copy over 12way mammal EPO
#
#           1h10m to copy over 19way amniota PECAN
#           3.2h  to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#           43m   to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES
#
#           3h    to copy over 35way mammal LC EPO
#           44m   to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS (in trust_ce mode!)
#           1h    to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES

# rel.65
#	    70m to copy over Human-vs-Chimpanzee LASTZ
#	    86m to copy over Human-vs-Bushbaby LASTZ
#	    73m to copy over Cod-vs-Danio rerio LASTZ
#	    74m to copy over Cod-vs-Stickleback LASTZ

#	    76m to copy over Human-vs-Cod TBLAT

#	    81m to copy over 6way primate EPO
#	    2h39m to copy over 12way mammal EPO

#	    1h58m to copy over 19way amniota PECAN
#	    14m to copy over 19way amniota PECAN GERP_CONSTRAINED_ELEMENTS
#	    60m to copy over 19way amniota PECAN GERP_CONSERVATION_SCORES

#	    3h7m to copy over 35way mammal LC EPO
#	    72m to copy over 35way mammal LC EPO GERP_CONSTRAINED_ELEMENTS
#	    78m to copy over 35way mammal LC EPO GERP_CONSERVATION_SCORES

# rel.66
#      Human-vs-Chimpanzee LASTZ
#      80m to copy over Cint-vs-Csav LASTZ
#      84m to copy over Human-vs-Chimp LASTZ
#      1m31s (no FK re-enable) to copy over Human-vs-Gorilla LASTZ
#      1m43s to copy over Human-vs-Orang LASTZ
#      1m33s to copy over Human-vs-Gibbon LASTZ
#      2m8s  to copy over Human-vs-Macaque LASTZ
#      2m56s to copy over Human-vs-Marmoset LASTZ

#      49s to copy over Human-vs-Cint TBLAT
#      78s to copy over Mouse-vs-Cint TBLAT
#      50s to copy over Cint-vs-Zebrafish TBLAT
#      44s to copy over Cint-vs-Lamprey TBLAT
#      1m8s to copy over Danio-vs-Coelacanth TBLAT
#      1m10s to copy over Stickleback-vs-Coelacanth TBLAT
#      1m9s to copy over Xenopus-vs-Coelacanth TBLAT

#      56s to copy over Human-vs-Human(lastz import from UCSC)

#      40s to copy over Human_ref-vs-Human_patches

# rel.67
#      57m: sf5_hsap_stri_lastz_67  mlss_id=576 [forgot to switch off keys)
#       4m: sf5_hsap_sscr_lastz_67  mlss_id=577
#       4m: kb3_sscr_btau_lastz_67  mlss_id=579
#      50s: kb3_hsap_onil_tblat_67  mlss_id=573
#      70s: sf5_mmus_onil           mlss_id=575
#      60s: kb3_drer_onil_tblat_67  mlss_id=574
#      30s: sf5_compara_human_lastz_patch_and_haplotype_67 mlss_id=556
#       4m: kb3_pecan_19way_67      mlss_id=580
#       3m: kb3_pecan_19way_67      ce_mlss_id=581
#      31m: kb3_pecan_19way_67      cs_mlss_id=50037
#      42m: kb3_epo_35way_67        mlss_id=582
#      13m: kb3_epo_35way_67        ce_mlss_id=583
#      53m: kb3_epo_35way_67        cs_mlss_id=50038
#       5m: kb3_12WAY_67            mlss_id=578

# rel.68
#  a few minutes to copy the database and ~1 hour to build the indexes

# rel.69
# several hours to copy from the pipeline databases and re-build the indexes

#rel 71.
#     2m: kb3_hsap_ggal_lastz_71  mlss_id=632
#     1m  kb3_mmus_ggal_lastz_71  mlss_id=633
#     1m  kb3_ggal_mgap_lastz_71  mlss_id=634
#     1m  kb3_ggal_xtro_tblat_71  mlss_id=638
#     1m  kb3_hsap_ggal_tblat_71  mlss_id=637
#     1m  sf5_olat_gmor_lastz_71  mlss_id=625
#     3m  kb3_pecan_20way_71      mlss_id=630
#     4m  kb3_pecan_20way_71      mlss_id=631
#    35m  kb3_pecan_20way_71      mlss_id=50045
#     3m  sf5_compara_epo_6way_71 mlss_id=548
#     1m  sf5_olat_onil_lastz_71  mlss_id=626
#     1m  sf5_olat_xmac_lastz_71  mlss_id=627
#     1m  sf5_epo_low_8way_fish_71 mlss_id=628
#     2m  sf5_epo_low_8way_fish_71 mlss_id=629
#     9m  sf5_epo_low_8way_fish_71 mlss_id=50044
#     1m  kb3_ggal_drer_tblat_71   mlss_id=639
#     1m  kb3_ggal_csav_tblat_71   mlss_id=640
#     1m  sf5_ggal_acar_lastz_71   mlss_id=636
#    91m  sf5_ggal_tgut_lastz_7    mlss_id=635 (re-enable 1)
#    93m  sf5_compara_epo_3way_birds_71 mlss_id=641 (re-enable 1)
#    14m  sf5_compara_epo_3way_birds_71 mlss_id=642 (re-enable 1)
#    16m  sf5_compara_epo_3way_birds_71 mlss_id=50046 (re-enable 1)

10.1.1 Human patches for high coverage blastz-net alignments
## Make sure that all the pairwise alignment are in the database
before loading these (TRANSLATED_BLAT_NET and BlastZ-Net).

         # Find all the relevant mlss_ids (NOTE you will need to do this 3 times in all. Once each for both human and mouse, and again for mouse patches against human - from e70):
$ export AFFECTED_MLSSS=`mysql --defaults-group-suffix=_compara2 kb3_mmus_lastz_hap_70 -N -e "select group_concat(distinct(method_link_species_set_id) SEPARATOR ' ') from genomic_align join dnafrag using (dnafrag_id) where genome_db_id=134 and method_link_species_set_id<1000"`

        # Use the --merge option of copy_data:
$ time for i in $AFFECTED_MLSSS; do echo $i; $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/copy_data.pl --from_url mysql://ensro@compara2/kb3_mmus_lastz_hap_70 --to_url mysql://ensadmin:${ENSADMIN_PSW}@compara3/sf5_ensembl_compara_70 --mlss $i --merge; done

# rel.64: 1h (13 affected mlsss)
# rel.66: 12m49s (14 affected mlss_ids)
# rel.67: 10m  (14 affected mlss_ids)
# rel.70: 14min for human (14 mlssids) and 5min for mouse (5 mlssids)

10.2 Syntenies

    1) First make sure the entries in $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl file
        point at the latest (staging) versions of the core databases.
    
    2) Then run something like:
$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/synteny/LoadSyntenyData.pl --reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    --dbname compara_curr -ref "Homo sapiens" -nonref "Callithrix jacchus" -mlss_id 10052 \
    /lustre/scratch101/ensembl/kb3/scratch/hive/release_64/kb3_hsap_cjac_synteny_64/synteny/all.100000.100000.BuildSynteny

    3) Check the output of the script whether it has created a new MLSS entry (it should not).
       If so, update the name of the newly created MLSS and copy it to the master database.


======================================


10.3 Putting together the database of ancestral sequence is now done using a dedicated Hive-Core mini-pipeline:

     Check you have the most recent core checkout ie the correct schema and patch files are added to the meta table.

    Go to $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file AncestralMerge_conf.pm .

    Make sure you have edited/checked the following:
        1) current release number
        2) names and locations of current and previous ancestral core databases
        3) the table of ancestral sequence sources in the second analysis (some entries might point to the previous release ancestral database, some will be new)

    For (3), you can run the following query on your release database and on the previous database:
> SELECT * FROM method_link_species_set WHERE method_link_id = 13; (equivalent to method_link_type = "EPO")
      The new mlss_id should be attached to their production database:
         '578' => 'mysql://ensadmin:$ENSADMIN_PSW@compara1/kb3_12WAY_67_ancestral_core' 
      The mlss_id that are reused should be linked to the previous database
         '505' => $self->o('prev_ancestral_db'),

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl AncestralMerge_conf.pm -password $ENSADMIN_PSW

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will merge the separate ancestral core sources into ensembl_ancestral_{rel_number}.
    
    You may want to check the msg table for errors and have a look at the result of the merger:
> SELECT left(name,12) na, count(*), min(seq_region_id), max(seq_region_id), max(seq_region_id)-min(seq_region_id)+1 FROM seq_region GROUP BY na;

    If everything is ok, drop hive-specific tables:
    You may need to remove a foreign key in analysis_description before that.

> ALTER TABLE analysis_description DROP KEY analysis_idx;
> CALL drop_hive_tables;
> TRUNCATE analysis; TRUNCATE analysis_description;

    Make sure all tables are turned into myISAM.

# rel.67  20min
# rel.71  20min

10.4 Merging GeneTrees+Families+NCTrees together is now done by running a mini-pipeline.

10.4.1  Make sure you have performed StableID mapping step on ProteinTrees (done manually)

10.4.2  Make sure you have performed TreeFam mapping step on
ProteinTrees (done manually)


(10.4.3 -- Removed)

10.4.4  Actually perform the merging step:

    Go to $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file MergeHomologySideTogether_conf.pm
    
    It has 6 sections for connecting to databases where you will have to change the names of the databases and possibly their locations:
        pipeline_db  - is your intermediate target (all protein side pipelines merged together)
        master_db    - is the main compara master
        prevrel_db   - should point to the previous release database
        genetrees_db - should point to the current GeneTrees pipeline database
        families_db  - should point to the current Families pipeline database
        nctrees_db   - should point to the current ncRNAtrees pipeline database

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl MergeHomologySideTogether_conf.pm

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will create a database with protein side pipeline databases merged together.

#                 2.7h to execute for release 63
#                 9.6h to execute for release 65
#                 ~9h to execute for release 66
#                 ~3h to execute for release 71

10.4.5 Run the script to include projections of genes as homologies for human and mouse

   First update the AUTO_INCREMENT of the following tables to 
> ALTER TABLE member   AUTO_INCREMENT=300000001;
> ALTER TABLE sequence AUTO_INCREMENT=300000001;
> ALTER TABLE homology AUTO_INCREMENT=300000001;
   It makes easier to spot the new entries

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/convert_patch_to_compara_homologies.pl \
    -reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    -comp_alias compara_homology_merged -species homo_sapiens

$ perl $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/convert_patch_to_compara_homologies.pl \
    -reg_conf $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/pipeline/production_reg_conf.pl \
    -comp_alias compara_homology_merged -species mus_musculus

# For e70 - human
#new compara entries:
#578 ref genes
#1028 projected genes
#1459 new homologies

#For e71 mouse
new compara entries:
10 ref genes
22 projected genes
65 new homologies


# NB: The underscore in the species name is necessary to match the genome_db name.
# There is a "-no_store 1" flag to avoid writing to the compara database.

#         30min for release 65
#          3min for release 66
#          2min for release 67

10.5 Final merger of "protein side" into the release database is done by running another mini-pipeline.

    Go to (or stay in) $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/modules/Bio/EnsEMBL/Compara/PipeConfig and open the PipeConfig file MergeHomologyIntoRelease_conf.pm
    
    Usually, only the release version and the server names have to be changed. You can still double-check the 3 sections:
        pipeline_db        - is a hive database that is only used for job tracking - it may/should be removed right after the pipeline is done
        merged_homology_db - is the result of the previous step (protein side databases merged together)
        rel_db             - is the main release database

    Save the changes, exit the editor and run init_pipeline.pl with this file:
$ init_pipeline.pl MergeHomologyIntoRelease_conf.pm -password $ENS<PASSWORD> -hive_driver sqlite

    Then run both -sync and -loop variations of the beekeeper.pl command suggested by init_pipeline.pl .
    This pipeline will merge the protein side tables into the main release database.

# 2.6h for rel65
# ~1.0h for rel66

10.6 Populate the member_production_counts table using the script at $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/production/populate_member_production_counts_table.sql

# For 72 you should run something like...
$ time mysql -hcompara3 -uensadmin -pensembl kb3_ensembl_compara_72 < $ENSEMBL_CVS_ROOT_DIR/ensembl-compara/scripts/production/populate_member_production_counts_table.sql
# Time to complete? (The last test on 71 compara database took ~58 minutes in a more or less quiet server)

10.7 Cleanup and CVS commit

    After you are happy about the result of both mergers
    you can drop both "compara_homology_merged" and "compara_full_merge" databases.

    Also, please commit the changes to the PipeConfig files that you have made.


11. Drop method_link_species_set entries for alignments which did not make it.

11.5 Check for method_links that do not have a corresponding method_link_species_set:
> SELECT ml.* FROM method_link ml LEFT JOIN method_link_species_set mlss ON ml.method_link_id=mlss.method_link_id WHERE mlss.method_link_id IS NULL;
In most cases they can be removed, but check with other members of Compara.

Removal of redundant method_link entries:
> DELETE ml FROM method_link ml LEFT JOIN method_link_species_set mlss ON ml.method_link_id=mlss.method_link_id WHERE mlss.method_link_id IS NULL;


12. Updating member.display_label fields for the members generated by EnsEMBL prediction.

This step has to happen ASAP, but AFTER the Core name projections have been done. And before you analyze/optimize the tables.
(1. Matthieu/Leo runs the Homology pipeline
 2. The homology database is given to Rhoda, who uses it to run name projections on Core databases (display_labels and gene_descriptions change in Core databases)
 3. We use the information in Core databases (derived from Homology, i.e. Compara) to fix the display_labels and gene_descriptions for Compara
!MAKE SURE YOU ARE IN SYNC WITH THE REST OF THE WORLD!
)

{'fraction_non_alphanumeric': 0.08178038508631477, 'fraction_numerical': 0.031727237254611754, 'mean_word_length': 3.9534790722474007, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 103, 'https://': 0, 'lorem ipsum': 0, 'www.': 4, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 12, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4525796', 'n_tokens_mistral': 25918, 'n_tokens_neox': 23844, 'n_words': 8477}