Ticket #1153: sequencing-update-script-draft-2.txt

What the update script needs to do
==========================================================

1. Move [Label]:s to [Tag]:s with a subtype
   - Make sure the 'Label' subtype exists in the db.
   - Copy all labels to tags. Re-use the same id:s if
     possible.
   - Update all labeled extracts to use the new tags.
   
   - Soft references: [Item.TAG] should re-use the [Item.LABEL]
     id so we don't have to modify anything related to this.
     Soft references using 'LABEL' or 'LabelData' must be fixed.
     Hopefully the tags can use the same id:s as the labels.

   - Delete the [Labels] table.
   - Delete the [BioMaterials.label_id] column.


2. Convert [LabeledExtract]:s into [Extract]:s with a subtype
   - Make sure the 'LabeledExtract' subtype exists in the db
   - No move of labeled extract data is needed, just change
     the discriminator column from 4 to 3. 
   - Switch from current labels to the new tags.
   
   - Soft refefecens: The [Item.LABELEDEXTRACT] is removed and 
     all soft references to it must be changed to [Item.EXTRACT].
     Item id:s don't change which is good.
  

3. Linking biomaterial parents
   - Move parent links to the new table for [BioMaterialParent]. 
     It is a one-to-one mapping (except for the 'dummy' column)
     and so this should not be difficult.
   - Pooled biomaterials with a single parent item should copy
     the parent information to [BioMaterials.parent_id] column
     and reset the pooled flag.
     
   - Delete the [BioMaterialSources] table.
   
4. Move [Hybridization]:s to [PhysicalBioAssay]:s
   - Make sure the 'Hybridization' subtype exists.
   - Copy all data to the new table. It would be good if
     we can use the old id:s otherwise keep track of
     old vs. new id:s. 
   - Update [BioMaterialEvents] table to point to the
     physical bioassays.
     
   - Soft references: [Item.PHYSICALBIOASSAY] should re-use the 
     [Item.HYBRIDIZATION] id so we don't have to modify anything 
     related to this. 
     Soft references using 'HYBRIDIZATION' or 'HybridizationData' 
     must be fixed. Hopefully the bioassays can use the same id:s 
     as the hybridizations.
     
   - Delete the [BioMaterialEvents.hybridization_id] column.

5. Move [Scan]:s to [DerivedBioAssaySet]
   - Make sure the 'Scan' subtype exists
   - Copy all data to the new table. Make sure the correct
     physical bioassay is linked (easy if they keep the old
     id:s). 
   - Create [DerivedBioAssay] for each array index used on the
     hybridization the scan is taken from. Try to link the bioassay
     with the extract on that index.
   - Move images to [FileSet] for the bioassay set. Image properties
     should be added as annotations.
 
   - Update [RawBioassay]:s created from the scan to use the 
     derived bioassay instead. 

   - Soft references: [Item.DERIVEDBIOASSAYSET] should re-use the 
     [Item.SCAN] id so we don't have to modify anything 
     related to this. 
     Soft references using 'SCAN' or 'ScanData' must be fixed. 
     Hopefully the bioassay setss can use the same id:s as the 
     scans.

   - Delete the [RawBioAssays.scan_id] column.
   - Delete the [Images] table.
   - Delete the [Scans] table.
   - Delete the [Hybridizations] table.
   
6. Core plug-ins 
   Some of the core plug-ins are affected a lot by the changes. 
   - Labeled extract importer
   - Hybridization importer
   - Scan importer
   - Raw bioassay importer
   - Annotation importer?
   
   I have not yet investigated what the best approach is. One option
   is to disable the old plug-ins and create new plug-ins for the new
   item types. The other biomaterial importers are also affected, but it
   should be possible to update them with new functionality. Eg. work
   on specific item subtype and parent/pooled item changes.
   


Soft item references
--------------------
The database has several places with soft references. This is
typically a two-column reference where one column holds the id
of the item and the other the type of item. In most cases the
type is the integer value from Item.getValue(), but it can also
be the Item.name() value or the Item.getClass().getName() value.
When we move and/or convert between item types we need to update 
the soft references.

So far, I have found the following tables with soft references:

 - AnnotationSets: item_type and item_id
   Update the item_type and item_id so that it references the
   new/moved item.

 - AnnotationTypeItems: item_type
   Update the item_type.

 - AnyToAny: from_id, from_type, to_id, to_type
   Update the id:s and type:s so that they references the
   new/moved items.
   
 - BioMaterialLists: member_type
   Update the member_type.
   
 - BioPlateTypes: biomaterial_type
   Update the biomaterial_type. 
  
 - ChangeHistoryDetails: item_type and item_id
   Update the item_type and item_id so that it references the
   new/moved item.

 - Contexts: item_type and item_id
   
 - ContextSettings: value
   Probably has settings related to most recently used
   items. I guess it is easiest to simply remove those
   settings for affected item types.

 - ItemValues: data_class and data_class_id
   Update to the new class and id.
   
 - Jobs: context_item
   Update to the new item type.

 - Keys: item_type
   Hmmm... remove/merge/or...???
 
 - PluginDefinitionGuiContexts: item_type
   Remove non-existing contexts and let plug-ins register
   for new contexts themself.
   
 -