Revisit database schema on the analysis part so that it is compatible with multiple workflows

[dshao]

No description provided.

[dshao]

sample ---< sequencing_experiment ---< analysis_workflow_run ----------------< analysis    
                    |adapter_dimer_count               |history_id
                    |fastq_file                                  |workflow_category: e.g. "alignment"...
                    |fastqc_report                           |results: JSON

• rename table sequence_alignment to analysis_workflow_run
• add field workflow_category to table analysis_workflow_run
• in table analysis_workflow_run, change all the alignment statistics to a JSON dictionary

[dshao]

Deployment plan

1. first DB migration

switch to git branch "multi-workflow-scratch"

# start transaction;
# delete from DATABASECHANGELOG;
# commit;

$ grails dbm-changelog-sync

# start transaction;
# delete from DATABASECHANGELOG where id like "1588082889454%";
# delete from DATABASECHANGELOG where id like "1588127332358%";
# commit

$ grails dbm-update

2. update objects in AnalysisWorkflowRun

start transaction;

update analysis_workflow_run set workflow_category = "Sequence Alignment";

update analysis_workflow_run set results = concat('{"bamFile":"', bam_file, 
                                                  '","peHistogram":"', pe_histogram,
                                                  '","mappedReads":', mapped_reads,
                                                  ',"uniquelyMappedReads":', uniquely_mapped_reads,
                                                  ',"dedupUniquelyMappedReads":', dedup_uniquely_mapped_reads,
                                                  ',"avgInsertSize":', avg_insert_size,
                                                  ',"stdDevInsertSize":', std_dev_insert_size,
                                                  ',"genomeCoverage":', genome_coverage, '}');

commit;

3. second DB migration
   switch to git branch "multi-workflow"

$ grails dbm-update

4. import updated database to production

$ sudo mysql pegr < filepath

The following maybe needed before import

# drop database pegr;
# create database pegr character set utf8 collate utf8_general_ci;