Upgrade script for BASE 2.17 --> 3.0

[Nicklas Nordborg]

The existing BASE 2.17 database should remain mainly intact, but there may be some things that we need to move around to different tables. We don't know any details yet but we'll probably need some kind of script for this. Here is a list of what we have found so far:

• Done [5621]. Remove the affy_chip column from the ArrayDesigns table. This column has not been used for a very long time (BASE 2.5?)
• Done [5621]. Remove the jar_path column from the PluginDefinitions and PluginTypes tables. But first copy the filename (not path) to the jar_file column in each table.
• Done [5621]. Remove the jar_path column from the JobAgentSettings table.
• Done [5621]. Remove references to jarPath property from context filters, sort settings, etc. for PLUGINDEFINITION and PLUGINTYPE.
• Done [5624]. Remove four properties for validator and metadata extractor from DataFileType and drop the four database columns.
• Done [5703]. Copy information in BioMaterialEventSources to BioMaterialEventSources2 so that parent-child linking of biomaterial is retained. Remove the BioMaterialEventSources table.
• Done [5703], [5730]. Set BioMaterials.parent_type and parent_id column based on parent/child information. Remove the pooled column.
• Done [5703]. Convert LabeledExtract to Extract items by updating the BioMaterials.discriminator column. All "soft" relations (eg. item_type+item_id pairs) need to update the item_type column (204->203). Set the subtype on all existing labeled extracts.
• Done [5703]. Convert Label to Tag. Set the subtype on all existing labels.
• Done [5703]. Convert Hybridization to PhysicalBioAssay. Copy information from the Hybridizations table to PhysicalBioAssays. Set new id on BioMaterialEvents.physicalbioassay_id column. Remove BioMaterialEvents.hybridization_id column. All "soft" relations (eg. item_type+item_id pairs) need to update the item_type column (261->267). Set the subtype on all existing hybridizations.
• Done [5703], [5730], [5734]. Convert Scan to DerivedBioAssay. Copy information from the Scans table to DerivedBioAssays. Copy information from Images table to FileSetMember. All "soft" relations (eg. item_type+item_id pairs) need to update the item_type column (262->268). Set the subtype on all existing scans.
• Done [5733]. Update RawBioAssay to point to the derived bioassay instead of the scan. Remove scan_id column. Populate extract_id column based on information in the array_num and the positions of biomaterials on the linked hybridization.
• Done [5730]. Drop unique contraint on FileSetMember table for fileset_id and datafiletype_id columns.
• Done [5733]. Set allow_multiple=false for all existing platform file types.
• Done [5703], [5730]. Convert ProtocolType, HardwareType, SoftwareType and FileType to ItemSubtype and make sure all hard and soft references are updated. Drop tables and columns that are no longer used.
• Done [5734]. Deprecate and disable batch importers: LabeledExtractImporter, HybridizationImporter, ScanImporter.
• Done [5803]. Project default items are stored as a ParameterValues list instead of AnyToAny links.
• Done [5828]. Encrypt existing passwords with bcrypt.

[Nicklas Nordborg]

(In [5592]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Removed almost everything from Update.java since we will only support upgrading from BASE 2.17.

[Nicklas Nordborg]

(In [5593]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Removed code in Install.java that performs special tasks when updating.

[Nicklas Nordborg]

(In [5621]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Removed unused queries from predefined query xml files. Fixes update for:

• jarPath --> jarFile properties of PluginDefinition, PluginTypes and JobAgentSettings.
• affy_chip column.

[Nicklas Nordborg]

(In [5624]) References #1591: Upgrade script for BASE 2.17 --> 3.0

References #1598: Use the extensions system for data file validators and metadata readers

Update that removes the four columns from the DataFileTypes table and remove all references to the properties from the context settings.

[Nicklas Nordborg]

(In [5703]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Update biomaterials, hybridizations and scans. Still missing some cases for re-linking pooled biomaterials.

Dropping lots of a database columns and tables that are not needed in BASE 3.

[Nicklas Nordborg]

(In [5730]) References #1591: Upgrade script for BASE 2.17 --> 3.0

• Images to files attached to derived bioassays
• Convert software types, hardware types, protocol types and file types to item subtypes.
• Fix biomaterial parent linking

[Nicklas Nordborg]

(In [5733]) References #1591: Upgrade script for BASE 2.17 --> 3.0

• Parent derived bioassay for raw bioassays
• Perent extract for raw bioassays
• Set allow_multiple=false for all existing platform file types

[Nicklas Nordborg]

(In [5734]) References #1591: Upgrade script for BASE 2.17 --> 3.0

• The file for an image can be null, skip those when updating
• Deprecated batch importers that have been replaced with new ones (labeled extract, hybridization and scan importer). The update will disable the old plug-ins.
• Disable BioAssaySetExporter if it is still present in the database. The plug-in no longer exists in the api.
• Remove role keys and plug-in keys for item types that no longer exists (labeled extract, hyb, scan, software type, hardware type, etc.)

[Nicklas Nordborg]

The update script seems to be working on our test database and also on the demo server database. It has only been tested with MySQL so far but I think it doesn't work on a PostgreSQL database. Unless something really big comes up, any remaining issues will be fixed after the alfa release.

[Nicklas Nordborg]

(In [5736]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Disable all plug-ins during the first part of the update. Core plug-ins are later re-enabled near the end of the installation. External plug-ins should be re-installed from a BASE 3-compatible pacakge. If no such package exists, the admin may try to manually re-install the old plug-in.

[Nicklas Nordborg]

(In [5753]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Update AnnotationSet using id instead of item_type and item_id which should be a lot quicker and we have to try updating items without annotations.

Using a temporary index on ItemValues.data_class_id to make updating quicker. The index is dropped after the update.

Display ... as indication of progress if things take a long time.

[Nicklas Nordborg]

(In [5755]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Handle duplicate names of item subtypes by suffixing them with (2), (3), and so on.

[Nicklas Nordborg]

(In [5756]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Several changes for supporting PostgreSQL when upgrading.

• Different syntax for dropping foreign keys
• Different syntax for dropping indexes
• Items are not INSERTED until flush/commit so we must do some manual flushing before we can issue batch UPDATE statements referencing the new items (MySQL do INSERT when save() is called)
• Different syntax is needed to let INSERT INTO ... SELECT FROM ... generate ID values for the new rows.

[Nicklas Nordborg]

(In [5803]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Handle project default items by moving them to ItemParameterValues.

[Nicklas Nordborg]

(In [5833]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Check login and password before actually doing any changes. Previously, the check was not made until a lot of changes had been made to the database and it was impossible to retry again without restoring the 2.17 database from a backup.

NOTE! A BACKUP IS STILL NEEDED! It was just annoying to have to use it because the root password was incorrect.

[Nicklas Nordborg]

(In [5834]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Added a short note about the importance of a backup in the "Upgrade" section.

[Nicklas Nordborg]

(In [5837]) References #1591: Upgrade script for BASE 2.17 --> 3.0

Create a temporary index on AnyToAny table to speed up the update.