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

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.

For homologues and family, ask Albert (<EMAIL>) or Abel
(<EMAIL>). For whole genome alignments and synteny ask Javier
(<EMAIL>) or Abel (<EMAIL>)

2. Mail the compara declaration of intention to <EMAIL>

3. Define priorities and assign jobs to the team members.

4. Add in the master compara database (ia64f:3306/jh7_ensembl_compara_master)
the new entries in the genome_db and dnafrag tables. You can set up your
registry and use the ~ensembl-compara/pipeline/update_genome.pl script. This
script sets the new genome_dbs as the default assemblies.

5. 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 in this case.

6. Update the ncbi_taxa_nodes and ncbi_taxa_names in the master DB using the
ncbi_taxonomy database located in mysql::/ecs2:3365/ncbi_taxonomy

7. New method_link_species_set entries might be added using the
~ensembl-compara/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.

8. Create the new database for the new release and add it to your registry
configuration file. Use the ~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-compara/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 -u USER -pPASSWD -h ia64f -P 3306 \
      -e "CREATE DATABASE jh7_ensembl_compara_39"
  mysql -u USER -pPASSWD -h ia64f -P 3306 \
      jh7_ensembl_compara_39 < ~/src/ensembl-compara/sql/table.sql
  populate_new_database.pl --master compara_master \
      --old compara_38 --new_compara_39

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.

  populate_new_database.pl --master compara_master --new compara_39

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 enj-healthcheck/database.properties file. It should look
like this:

    host=ia64f
    port=3306
    user=ensadmin
    password=***********

    # Database driver class - shouldn't need to be changed
    driver=org.gjt.mm.mysql.Driver

    # Master schema - see CompareSchema healthcheck
    # This setting is ignored if CompareSchema is not run
    master.schema=master_schema_38

    # Secondary database connection details
    secondary.host=ecs2
    secondary.port=3364
    secondary.user=ensro
    secondary.driver=org.gjt.mm.mysql.Driver

Now you can run the compara-compara_external_foreign_keys healthchecks:

  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      -d2 .+_core_39_.+ compara_external_foreign_keys

...and correct mismatches if any!

10. Merge data. The fastest way to merge new data is to mysqlimport them.

- Translated BLAT: the LoadBLATAlignment.pl script creates tab-delimited files
which can be imported directly into the release database.

- BlastZ-Net: you need to dump the tables manually. Check the
method_link_species_set_id. You will probably need to update the
genomic_align_block_id, genomic_align_id and genomic_align_group_id in order to
match the 10^10 convention for the IDs. For instance, in order to dump
BlastZ-Net alignments from database
mysql://ensro@ia64f:3306/kb3_compara_human_mouse_blastz_39, you will have to run
the following commands:

    mysql -hia64f -uensro -N -e "select \
        genomic_align_block_id+method_link_species_set_id*10000000000, \
        method_link_species_set_id,score,perc_id,length from \
        genomic_align_block where method_link_species_set_id=194" \
        kb3_compara_human_mouse_blastz_39 > genomic_align_block.txt

    mysql -hia64f -uensro -N -e "select \
        genomic_align_id+method_link_species_set_id*10000000000, \
        genomic_align_block_id+method_link_species_set_id*10000000000, \
        method_link_species_set_id, dnafrag_id, dnafrag_start, \
        dnafrag_end, dnafrag_strand, cigar_line, level_id from \
        genomic_align where method_link_species_set_id=194" \
        kb3_compara_human_mouse_blastz_39 > genomic_align.txt

    mysql -hia64f -uensro -N -e "select \
        gag.group_id+ga.method_link_species_set_id*10000000000, \
        gag.type, gag.genomic_align_id+ga.method_link_species_set_id* \
        10000000000 from genomic_align_group gag, genomic_align ga where \
        ga.method_link_species_set_id=194 and ga.genomic_align_id= \
        gag.genomic_align_id" kb3_compara_human_mouse_blastz_39 > \
        genomic_align_group.txt

Hopefully, these databases should have the right method_link_species_sets,
genome_dbs and dnafrags. In order to check for inconsistencies, you can run
the following query (this should be implemented as healthcheck...):

    create table mlss_checks select mlss.method_link_species_set_id, \
    mlss.name as mlss_name, gdb.name as species_name, count(*) from \
    method_link_species_set mlss left join genomic_align_block using \
    (method_link_species_set_id) left join genomic_align using \
    (genomic_align_block_id) left join dnafrag using (dnafrag_id) left \
    join genome_db gdb using (genome_db_id) where mlss.method_link_id < \
    100 group by mlss.name, gdb.name;

and check that all the alignments correspond to the right species.

If the database had wrong dnafrag_ids, you will have to update them as well.
This process can be done after dumping the files. At the moment there is a
script in /ecs4/work1/jh7/dumps/mlagan/update_dnafrags.pl which reads the right
dnafrags from a file called "new_dnafrag.txt", the wrong ones from the file
called "old_dnafrag.txt" and the genomic_align from the "genomic_align.txt"
file. This scripts prints the genomic_aligns with the right dnafrag_ids to the
standard output:

    mysql -N -e "SELECT * FROM dnafrag" jh7_ensembl_compara_39 > new_dnafrag.txt
    mysql -N -e "SELECT * FROM dnafrag" other database > old_dnafrag.txt
    update_danfrags.pl > new_genomic_align.txt
    mv genomic_align.txt old_genomic_align.txt
    mv new_genomic_align.txt genomic_align.txt

- Syntenies:

- Protein trees and homologues:

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

12. Update the max_alignment_length. You can use the corresponding healthcheck
with the -repair option:

    ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara -repair Meta

13. Run the healthchecks:

  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      -d2 .+_core_39_.+ compara_external_foreign_keys
  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      compara_db_constraints

...and correct mismatches if any!

14. Ask the release coordinator to point the test web server to the compara DB.

15. Create a patch to convert a compara DB from the previous release to the new
one. The patch should include at least an update of the schema_version in the
meta table!

16. Update the files in the ~ensembl-compara/sql directory:
  $ mysql -h ecs4 -u ensro -P 3364 -e "SELECT * FROM genome_db" \
      ensembl_compara_XX > ~ensembl-compara/sql/genome_db.txt
  $ mysql -h ecs4 -u ensro -P 3364 -e "SELECT * FROM method_link" \
      ensembl_compara_XX > ~ensembl-compara/sql/method_link.txt

17. Apply patches to the test databases and verify that they work fine.
Recompile the test database.

Don't forget to commit these changes into the repository!<commit_msg>Add note about help tables in ensembl-webcode/htdocs/info/data/compara<commit_after>  COMPARA PRODUCTION CYCLE
============================

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.

For homologues and family, ask Albert (<EMAIL>) or Abel
(<EMAIL>). For whole genome alignments and synteny ask Javier
(<EMAIL>) or Abel (<EMAIL>)

2. Mail the compara declaration of intention to <EMAIL>

3. Define priorities and assign jobs to the team members.

4. Add in the master compara database (ia64f:3306/jh7_ensembl_compara_master)
the new entries in the genome_db and dnafrag tables. You can set up your
registry and use the ~ensembl-compara/pipeline/update_genome.pl script. This
script sets the new genome_dbs as the default assemblies.

5. 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 in this case.

6. Update the ncbi_taxa_nodes and ncbi_taxa_names in the master DB using the
ncbi_taxonomy database located in mysql::/ecs2:3365/ncbi_taxonomy

7. New method_link_species_set entries might be added using the
~ensembl-compara/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.

8. Create the new database for the new release and add it to your registry
configuration file. Use the ~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-compara/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 -u USER -pPASSWD -h ia64f -P 3306 \
      -e "CREATE DATABASE jh7_ensembl_compara_39"
  mysql -u USER -pPASSWD -h ia64f -P 3306 \
      jh7_ensembl_compara_39 < ~/src/ensembl-compara/sql/table.sql
  populate_new_database.pl --master compara_master \
      --old compara_38 --new_compara_39

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.

  populate_new_database.pl --master compara_master --new compara_39

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 enj-healthcheck/database.properties file. It should look
like this:

    host=ia64f
    port=3306
    user=ensadmin
    password=***********

    # Database driver class - shouldn't need to be changed
    driver=org.gjt.mm.mysql.Driver

    # Master schema - see CompareSchema healthcheck
    # This setting is ignored if CompareSchema is not run
    master.schema=master_schema_38

    # Secondary database connection details
    secondary.host=ecs2
    secondary.port=3364
    secondary.user=ensro
    secondary.driver=org.gjt.mm.mysql.Driver

Now you can run the compara-compara_external_foreign_keys healthchecks:

  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      -d2 .+_core_39_.+ compara_external_foreign_keys

...and correct mismatches if any!

10. Merge data. The fastest way to merge new data is to mysqlimport them.

- Translated BLAT: the LoadBLATAlignment.pl script creates tab-delimited files
which can be imported directly into the release database.

- BlastZ-Net: you need to dump the tables manually. Check the
method_link_species_set_id. You will probably need to update the
genomic_align_block_id, genomic_align_id and genomic_align_group_id in order to
match the 10^10 convention for the IDs. For instance, in order to dump
BlastZ-Net alignments from database
mysql://ensro@ia64f:3306/kb3_compara_human_mouse_blastz_39, you will have to run
the following commands:

    mysql -hia64f -uensro -N -e "select \
        genomic_align_block_id+method_link_species_set_id*10000000000, \
        method_link_species_set_id,score,perc_id,length from \
        genomic_align_block where method_link_species_set_id=194" \
        kb3_compara_human_mouse_blastz_39 > genomic_align_block.txt

    mysql -hia64f -uensro -N -e "select \
        genomic_align_id+method_link_species_set_id*10000000000, \
        genomic_align_block_id+method_link_species_set_id*10000000000, \
        method_link_species_set_id, dnafrag_id, dnafrag_start, \
        dnafrag_end, dnafrag_strand, cigar_line, level_id from \
        genomic_align where method_link_species_set_id=194" \
        kb3_compara_human_mouse_blastz_39 > genomic_align.txt

    mysql -hia64f -uensro -N -e "select \
        gag.group_id+ga.method_link_species_set_id*10000000000, \
        gag.type, gag.genomic_align_id+ga.method_link_species_set_id* \
        10000000000 from genomic_align_group gag, genomic_align ga where \
        ga.method_link_species_set_id=194 and ga.genomic_align_id= \
        gag.genomic_align_id" kb3_compara_human_mouse_blastz_39 > \
        genomic_align_group.txt

Hopefully, these databases should have the right method_link_species_sets,
genome_dbs and dnafrags. In order to check for inconsistencies, you can run
the following query (this should be implemented as healthcheck...):

    create table mlss_checks select mlss.method_link_species_set_id, \
    mlss.name as mlss_name, gdb.name as species_name, count(*) from \
    method_link_species_set mlss left join genomic_align_block using \
    (method_link_species_set_id) left join genomic_align using \
    (genomic_align_block_id) left join dnafrag using (dnafrag_id) left \
    join genome_db gdb using (genome_db_id) where mlss.method_link_id < \
    100 group by mlss.name, gdb.name;

and check that all the alignments correspond to the right species.

If the database had wrong dnafrag_ids, you will have to update them as well.
This process can be done after dumping the files. At the moment there is a
script in /ecs4/work1/jh7/dumps/mlagan/update_dnafrags.pl which reads the right
dnafrags from a file called "new_dnafrag.txt", the wrong ones from the file
called "old_dnafrag.txt" and the genomic_align from the "genomic_align.txt"
file. This scripts prints the genomic_aligns with the right dnafrag_ids to the
standard output:

    mysql -N -e "SELECT * FROM dnafrag" jh7_ensembl_compara_39 > new_dnafrag.txt
    mysql -N -e "SELECT * FROM dnafrag" other database > old_dnafrag.txt
    update_danfrags.pl > new_genomic_align.txt
    mv genomic_align.txt old_genomic_align.txt
    mv new_genomic_align.txt genomic_align.txt

- Syntenies:

- Protein trees and homologues:

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

12. Update the max_alignment_length. You can use the corresponding healthcheck
with the -repair option:

    ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara -repair Meta

13. Run the healthchecks:

  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      -d2 .+_core_39_.+ compara_external_foreign_keys
  ./run-healthcheck.sh -d jh7_ensembl_compara_39 -type compara \
      compara_db_constraints

...and correct mismatches if any!

14. Ask the release coordinator to point the test web server to the compara DB.

15. Create a patch to convert a compara DB from the previous release to the new
one. The patch should include at least an update of the schema_version in the
meta table!

16. Update the files in the ~ensembl-compara/sql directory:
  $ mysql -h ecs4 -u ensro -P 3364 -e "SELECT * FROM genome_db" \
      ensembl_compara_XX > ~ensembl-compara/sql/genome_db.txt
  $ mysql -h ecs4 -u ensro -P 3364 -e "SELECT * FROM method_link" \
      ensembl_compara_XX > ~ensembl-compara/sql/method_link.txt

17. Apply patches to the test databases and verify that they work fine.
Recompile the test database.

Don't forget to commit these changes into the repository!

18. Update files in ensembl-webcode/htdocs/info/data/compara

  perl ~/src/ensembl_main/ensembl-compara/docs/create_mlss_table.pl \
    --reg_conf ~/.ensembl_init --dbname Compara39 --method_link BLASTZ_NET \
    --trim --use_names > blastz_net.html

  perl ~/src/ensembl_main/ensembl-compara/docs/create_mlss_table.pl \
    --reg_conf ~/.ensembl_init --dbname Compara39 --method_link MLAGAN \
    --list > mlagan.html

  perl ~/src/ensembl_main/ensembl-compara/docs/create_mlss_table.pl \
    --reg_conf ~/.ensembl_init --dbname Compara39 --method_link TRANSLATED_BLAT \
    --trim > tblat.html

{'fraction_non_alphanumeric': 0.060585547469961074, 'fraction_numerical': 0.023354205449314606, 'mean_word_length': 4.042093287827076, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 32, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 10, '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': '5872502', 'n_tokens_mistral': 5959, 'n_tokens_neox': 5474, 'n_words': 2019}