Package net.sf.basedb.core

Class Update

java.lang.Object

net.sf.basedb.core.Update

public final class Update
extends Object

This class contains static methods used to update items already in the database.

Schema version updates

Schema version	Description
90	The schema as released in BASE 2.17. For information about schema changes before this check the BASE 2.17 code.
100	The schema as released in BASE 3.0. We don't list all changes here.
101 and 102	Fixes an issue with duplicate FileSetMemberData entries due to a bug in the upgrade program. Make sure that the unique constraint that should prevent duplicates exists.
103	Added ReporterCloneTemplateData and related classes. No special update is required.
104	Make ExperimentData AnnotatableData. No special update is required.
105	Added ItemSubtypeData.getPushAnnotations(). All existing subtypes are set to 'false'.
106	Added BioPlateData.getEntryDate() and BioPlateData.getEventDate(). All existing bioplates get a null value.
107	Added BioPlateTypeData.getStorageType() All existing bioplate types get a null value.
108	Added BioPlateData.getSection(), BioPlateData.getTray() and BioPlateData.getPosition() All existing bioplates get null values.
109 and 110	Added DerivedBioAssayData.getPhysicalBioAssays() and DerivedBioAssayData.getParents() and removed DerivedBioAssayData.getPhysicalBioAssay() and DerivedBioAssayData.getParent(). Existing derived bioassays are updated in a two-step procedure. The first step is to copy existing information to the new tables and the second step is to drop columns that are no longer needed.
111	Not an actual database schema change, but we need to remove annotation sets that have been created without any annotations by the annotation importer and BASE 1->2 migration.
112	Not an actual database schema change, but we need to fix the parent_id and parent_type columns in the BioMaterials table to really reflect what is in the BioMaterialEventSources2 table. For more info see BASE ticket: http://base.thep.lu.se/ticket/1745
113	Made Hardware, Software, Tag and BioMaterialList Annotatable items. No special database update is needed. Only increase the schema version.
114	Added ChangeHistoryDetailData.getOldValue() and ChangeHistoryDetailData.getNewValue(). An update that changes the log format for annotation changes is needed to make log entries using the same format.
115	Added AnnotationTypeData.getDisableLogOfValues(). The update should set the value for all existing annotation types to false.
116	Added JobData.getExternalId() and made JobData a ShareableData item. No special database update is needed. Only increase the schema version.
117	Added FileServerData.getRootPath() and FileServerData.getSshFingerprint(). No special database update is needed. Only increase the schema version.
118	Added SchemaVersionData.getAppId(). The update will set the app id for the current entry to 'net.sf.basedb.core' and the update the schema version.
119	Added AnnotationTypeData.getDisableInheritance(). The update will set the value to FALSE for all existing annotation types.
120	Added ItemListData and removed BioMaterialListData. The update will convert all existing biomaterial lists to item lists and make other changes as are requried to keep things working.
121	No actual schema change, but we need to fix incorrect item lists that has got a '0' member due to ticket http://base.thep.lu.se/ticket/1945.
122	Added RootRawBioAssayData. Existing raw bioassay -- Experiment links are moved over to this item.
123	Remove AnnotationSetData.getInheritedSets(). Existing inherited annotations sets will be converted to inherited annotations.
124	Inherited annotations are moved from the "InheritedAnnotations" table to the "Annotations" table.
125	Experimental factor annotations for an experiment which are found as either primary or inherited annotations on the raw bioassays in the experiment are inherited by the RootRawBioAssayData items. This means that existing experiments will continue to have access to the experimental factors as before.
126	Fixes the conversion formula for Fahrenheit. Existing annotation values that are re-calculated to the corrent result.
127	Added ParameterValueData.isMasked(). All existing parameters are set to false.
128	Added KitData and other things related to it.
129 and 130	Added RemovableData.getRemovedBy() and removed RemovableData.isRemoved(). Existing items that has been flagged for removal are updated by setting the removedBy property to either the owner of the item or, if the item doesn't have an owner, to the root user. In a second step the removed columns are dropped from all tables. Note that this must be done in adjustExistingItems(ProgressReporter, int, String, String) since otherwise the installation will try to create new items in table that still has the 'removed' column but it will not accept 'null' values and the installation will fail.
131	Not an actual database schema change, but we need to fix issues with old date values in the change history table. See http://base.thep.lu.se/ticket/1989 for more information.
132	Not an actual database schema change, but we need to fix issues with annotation entries that have no values. See http://base.thep.lu.se/ticket/1992 for more information.
133	Added SessionData.getAuthenticationMethod(). No special database update is needed. Only increase the schema version.
134	Made Project annotatable. No special database update is needed. Only increase the schema version.
135	Added support for project specific annotations. Added AnnotationTypeData.getProjectAnnotations(). Existing annotation types are set to FALSE. Added AnnotationData.getProjectId() and AnnotationData.getOverrideId(). Existing annotations set both values to 0.
136	Added JobData.getNode() and JobData.getItemSubtype(). Job entries which have a server value containing '[...]' are updated so that the part within '[...]' is moved to the node column.
137	Added FileServerData.getFingerprintType(). File server entries which have a SSH fingerprint value are updated to "MD5".
138	Added NewsData.isSticky(). The update should set the value for all existing news items to false.
139	Added UserDeviceData and related changes in UserData, ClientData and SessionData classes.
140	Added SessionData.getLocation(), SessionData.getLocationLatitude() and SessionData.getLocationLongitude(). No special database update is needed. Only increase the schema version.
141	Added FileServerData.getSshPrivateKey(), FileServerData.getSshPrivateKeyPassword() and FileServerData.getSshPrivateKeyFormat(). No special database update is needed. Only increase the schema version.
142	Added the default password login form. It needs to be disabled by default which is triggered by the schema version update. No actual database change.
143 and 144	Remvoving support for secondary storage. FileData.action has been deleted. The update works in two step and the first step is to set FileData.location=0 (offline) on all files with FileData.location=2 (secondary) and remove filters on the action column The second step is to drop the action column.
145	Added AnnotationTypeData.isIdentifier(). The update will set the value to FALSE for all existing annotation types.
146 and 147	Removed support for spot images: SpotImageCreator plug-in was removed. The entry in the database is marked as disabled. The "SpotImages" table is dropped from the database.
148	Added UserDeviceData.isVerified(). The update will set the value to TRUE for all existing devices.
149	Made File annotatable. No special database update is needed. Only increase the schema version.
150	Added ItemListSyncFilterData.getFilterGroup(), ItemListSyncFilterData.getLastSyncProperties(), ItemListData.getSizeAtLastSync(), ItemListData.getMd5AtLastSync(). The update will set the filterGroup value to 'A+' for all existing filters and generate a lastSyncProperties value from current settings. The update will also set sizeAtLastSync to -1 for existing item lists.
151	Added ChangeHistoryData.getName(). The update will generate a default name for all existing entries based on the registred job, plug-in, client, etc.
152	Added PluginConfiguration.isDisabled(). The update will set the value to FALSE for all existing configurations.
153	Made FileServer annotatable. No special database update is needed. Only increase the schema version.

Version:

2.0

Author:

Nicklas

Last modified

$Date: 2023-05-31 13:45:57 +0200 (Wed, 31 May 2023) $

Nested Class Summary

Nested Classes

Modifier and Type	Class	Description
(package private) static class	Update.DropColumnChange	Action that drops a column in a table.
(package private) static class	Update.SchemaChange	Action that need to make some change to the database schem after the main transaction in adjustExistingItems(ProgressReporter, int, String, String) has finished.

Field Summary

Fields

Modifier and Type	Field	Description
private static org.slf4j.Logger	log	Log core events.

Constructor Summary

Constructors

Constructor	Description
Update()

Method Summary

Modifier and Type	Method	Description
static void	adjustExistingItems(ProgressReporter progress, int schemaVersion, String rootLogin, String rootPassword)	Adjust the existing items in the database to be compatible with the latest mappings.
private static void	cleanContextFromProperty(Session session, Item itemType, String propertyName, String replacementPropertyName, boolean forceRemoveFilter)
private static void	createIndex(Session session, boolean unique, String indexName, String tableName, String... columnNames)
private static void	dropColumn(Session session, String tableName, String columnName, ProgressReporter progress)
private static void	dropIndex(Session session, boolean unique, String tableName, String... columnNames)
private static void	dropNotNullConstraint(Session session, String tableName, String columnName, String mySqlDataType)
private static void	dropTable(Session session, String tableName)
(package private) static int	getSchemaVersion(Session session, String appId)
(package private) static int	getSchemaVersionInTransaction(Session session, String appId)	Get the current schema version number as it is stored in the database.
private static TableInfo	getTableInfo(Session session, String tableName)
private static Map<Integer,String>	loadIdToNameMap(Session session, String sql)
static int	recalculateFreeWells(ProgressReporter progress)	Vefify and update the free wells of all bioplates.
static int	recalculateRemainingQuantity(ProgressReporter progress)	Vefify and update the remaining quantity of all biomaterials.
private static void	removeContext(Session session, int itemType)	Remove context-related information for the given item type.
private static String	replaceInCommaSeparatedList(String list, String find, String replacement)
private static void	setSchemaVersion(Session session, int schemaVersion)
(package private) static void	setSchemaVersion(Session session, String appId, int schemaVersion, int build)	Update the database with a new schema version number.
private static int	setSchemaVersionInTransaction(Session session, int schemaVersion)	Update the schema version version number using a new transaction.
static void	updateDatabase(ProgressReporter progress, String rootLogin, String rootPassword)	Update the database by modifying existing items to follow new requirements.
private static int	updateToSchemaVersion101(Session session)	Remove duplicate file set members.
private static int	updateToSchemaVersion102(Session session)	Add unique index on FileSetMembers(fileset_id,datafiletype_id,file_id)
private static int	updateToSchemaVersion110(Session session, int currentSchemaVersion)	Move derived bioassay parent items to different tables.
private static int	updateToSchemaVersion111(Session session, ProgressReporter progress)	Remove annotation sets that have no annotation values and no inherited annotations.
private static int	updateToSchemaVersion112(Session session, ProgressReporter progress)	Ensure that parent_id and parent_type in BioMaterials table is correct when compared to BioMaterialEventSources2 table.
private static int	updateToSchemaVersion114(Session session, ProgressReporter progress)	Ensure that old log entries for annotation changes use the same text format that is used after this update.
private static int	updateToSchemaVersion120(Session session)	Transfer existing biomaterial lists to item lists.
private static int	updateToSchemaVersion121(Session session)	Remove all '0' members from item lists and decrease the size of the list with 1 where it is found.
private static int	updateToSchemaVersion122(Session session)	Move all entries from the "ExperimentRawBioAssays" table to "RootRawBioAssays".
private static int	updateToSchemaVersion123(Session session)	Convert all entries from the "InheritedAnnotationSets" table to entries in the "InheritedAnnotations" table.
private static int	updateToSchemaVersion124(Session session)	Inherited annotations in the "InheritedAnnotations" table must be converted to entries in the "Annotations" table with source=1 (INHERITED)
private static int	updateToSchemaVersion125(Session session)	Experimental factors in existing experiments are inherited to the RootRawBioAssayData items from the RawBioAssayData parents.
private static int	updateToSchemaVersion126(Session session)	Fix the conversion formula for Fahrenheit.
private static int	updateToSchemaVersion131(Session session, ProgressReporter progress)	Fix the old_values for date annotations in the change history.
private static int	updateToSchemaVersion132(Session session, ProgressReporter progress)	Delete annotation entries that have no values.
private static int	updateToSchemaVersion136(Session session, ProgressReporter progress)	Update the Jobs table by splitting the server value into a server+node value for all entries that contain '[..]'.
private static int	updateToSchemaVersion144(Session session, int currentSchemaVersion)	Update the Files table by setting location=0 (offline) on all files with location=2 (secondary), remove the 'action' column and all stored contexts using the 'action' column.
private static int	updateToSchemaVersion147(Session session, int currentSchemaVersion)
private static int	updateToSchemaVersion151(Session session, int currentSchemaVersion, ProgressReporter progress)
(package private) static void	verifyRootLoginAndPassword(String login, String password)	Verify the root user login and password.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Details

log

private static final org.slf4j.Logger log

Log core events.

Constructor Details

Update

public Update()

Method Details

updateDatabase

public static void updateDatabase(ProgressReporter progress,
String rootLogin,
String rootPassword)
                           throws BaseException

Update the database by modifying existing items to follow new requirements.

Parameters:

progress - An object implementing the ProgressReporter interface

rootLogin - The root user login

rootPassword - The root user password

Throws:

BaseException

verifyRootLoginAndPassword

static void verifyRootLoginAndPassword(String login,
String password)

Verify the root user login and password. This method is called first in the update procedure, which means that tables, etc. are usually still from the old version.

getSchemaVersionInTransaction

static int getSchemaVersionInTransaction(Session session,
String appId)
                                  throws BaseException

Get the current schema version number as it is stored in the database.

Returns:

The schema version number, or 0 if no schem version is found

Throws:

BaseException

getSchemaVersion

static int getSchemaVersion(Session session,
String appId)
                     throws BaseException

Throws:

BaseException

setSchemaVersion

static void setSchemaVersion(Session session,
String appId,
int schemaVersion,
int build)
                      throws BaseException

Update the database with a new schema version number. This method should be called from an open transaction.

Parameters:

schemaVersion - The new schema version number

Throws:

BaseException

setSchemaVersion

private static void setSchemaVersion(Session session,
int schemaVersion)
                              throws BaseException

Throws:

BaseException

setSchemaVersionInTransaction

private static int setSchemaVersionInTransaction(Session session,
int schemaVersion)

Update the schema version version number using a new transaction.

Parameters:

schemaVersion - The new schema version number

adjustExistingItems

public static void adjustExistingItems(ProgressReporter progress,
int schemaVersion,
String rootLogin,
String rootPassword)
                                throws BaseException

Adjust the existing items in the database to be compatible with the latest mappings. No update to the schema version should be done here. The code must also consider the case that the update failes at a later stage and that the same update is executed again.

Parameters:

progress - An object implementing the ProgressReporter interface

rootLogin - The root user login

rootPassword - The root user password

Throws:

BaseException

updateToSchemaVersion101

private static int updateToSchemaVersion101(Session session)
                                     throws BaseException

Remove duplicate file set members.

Returns:

The new schema version (=101)

Throws:

BaseException

updateToSchemaVersion102

private static int updateToSchemaVersion102(Session session)
                                     throws BaseException

Add unique index on FileSetMembers(fileset_id,datafiletype_id,file_id)

Returns:

The new schema version (=102)

Throws:

BaseException

updateToSchemaVersion110

private static int updateToSchemaVersion110(Session session,
int currentSchemaVersion)
                                     throws BaseException

Move derived bioassay parent items to different tables. Get rid of old columns in the DerivedBioAssays table.

Returns:

The new schema version (=110)

Throws:

BaseException

updateToSchemaVersion111

private static int updateToSchemaVersion111(Session session,
ProgressReporter progress)
                                     throws BaseException

Remove annotation sets that have no annotation values and no inherited annotations.

Returns:

The new schema version (=111)

Throws:

BaseException

updateToSchemaVersion112

private static int updateToSchemaVersion112(Session session,
ProgressReporter progress)
                                     throws BaseException

Ensure that parent_id and parent_type in BioMaterials table is correct when compared to BioMaterialEventSources2 table.

Returns:

The new schema version (=112)

Throws:

BaseException

updateToSchemaVersion114

private static int updateToSchemaVersion114(Session session,
ProgressReporter progress)
                                     throws BaseException

Ensure that old log entries for annotation changes use the same text format that is used after this update.

Returns:

The new schema version (=114)

Throws:

BaseException

updateToSchemaVersion120

private static int updateToSchemaVersion120(Session session)
                                     throws BaseException

Transfer existing biomaterial lists to item lists.

Returns:

The new schema version (=120)

Throws:

BaseException

updateToSchemaVersion121

private static int updateToSchemaVersion121(Session session)
                                     throws BaseException

Remove all '0' members from item lists and decrease the size of the list with 1 where it is found.

Returns:

The new schema version (=121)

Throws:

BaseException

updateToSchemaVersion122

private static int updateToSchemaVersion122(Session session)
                                     throws BaseException

Move all entries from the "ExperimentRawBioAssays" table to "RootRawBioAssays".

Returns:

The new schema version (=122)

Throws:

BaseException

updateToSchemaVersion123

private static int updateToSchemaVersion123(Session session)
                                     throws BaseException

Convert all entries from the "InheritedAnnotationSets" table to entries in the "InheritedAnnotations" table.

Returns:

The new schema version (=123)

Throws:

BaseException

updateToSchemaVersion124

private static int updateToSchemaVersion124(Session session)
                                     throws BaseException

Inherited annotations in the "InheritedAnnotations" table must be converted to entries in the "Annotations" table with source=1 (INHERITED)

Returns:

The new schema version (=124)

Throws:

BaseException

updateToSchemaVersion125

private static int updateToSchemaVersion125(Session session)
                                     throws BaseException

Experimental factors in existing experiments are inherited to the RootRawBioAssayData items from the RawBioAssayData parents.

Returns:

The new schema version (=125)

Throws:

BaseException

updateToSchemaVersion126

private static int updateToSchemaVersion126(Session session)
                                     throws BaseException

Fix the conversion formula for Fahrenheit.

Returns:

The new schema version (=126)

Throws:

BaseException

updateToSchemaVersion131

private static int updateToSchemaVersion131(Session session,
ProgressReporter progress)
                                     throws BaseException

Fix the old_values for date annotations in the change history. The fix will scan for all log entries related to DATE annotation types. By ordering the changes by timestamp it should be possible to set the old_value of UPDATE entries to the new_value of the previous entry for the same item and annotation.

Returns:

The new schema version (=131)

Throws:

BaseException

updateToSchemaVersion132

private static int updateToSchemaVersion132(Session session,
ProgressReporter progress)
                                     throws BaseException

Delete annotation entries that have no values.

Returns:

The new schema version (=132)

Throws:

BaseException

updateToSchemaVersion136

private static int updateToSchemaVersion136(Session session,
ProgressReporter progress)
                                     throws BaseException

Update the Jobs table by splitting the server value into a server+node value for all entries that contain '[..]'. That part withing the brackets are removed from the server column and moved to the node column.

Returns:

The new schema version (=136)

Throws:

BaseException

updateToSchemaVersion144

private static int updateToSchemaVersion144(Session session,
int currentSchemaVersion)
                                     throws BaseException

Update the Files table by setting location=0 (offline) on all files with location=2 (secondary), remove the 'action' column and all stored contexts using the 'action' column. Update the QuotaValues table by removing all entries with location=2. Remove the 'secondary_location' column from the QuotaTypes table.

Returns:

The new schema version (=144)

Throws:

BaseException

updateToSchemaVersion147

private static int updateToSchemaVersion147(Session session,
int currentSchemaVersion)
                                     throws BaseException

Returns:

The new schema version (=147)

Throws:

BaseException

updateToSchemaVersion151

private static int updateToSchemaVersion151(Session session,
int currentSchemaVersion,
ProgressReporter progress)
                                     throws BaseException

Returns:

The new schema version (=151)

Throws:

BaseException

loadIdToNameMap

private static Map<Integer,String> loadIdToNameMap(Session session,
String sql)

recalculateRemainingQuantity

public static int recalculateRemainingQuantity(ProgressReporter progress)
                                        throws BaseException

Vefify and update the remaining quantity of all biomaterials.

Returns:

The number of biomaterial fixed

Throws:

BaseException

recalculateFreeWells

public static int recalculateFreeWells(ProgressReporter progress)
                                throws BaseException

Vefify and update the free wells of all bioplates.

Returns:

The number of bioplates fixed

Throws:

BaseException

getTableInfo

private static TableInfo getTableInfo(Session session,
String tableName)
                               throws SQLException

Throws:

SQLException

dropColumn

private static void dropColumn(Session session,
String tableName,
String columnName,
ProgressReporter progress)
                        throws SQLException

Throws:

SQLException

dropIndex

private static void dropIndex(Session session,
boolean unique,
String tableName,
String... columnNames)
                       throws SQLException

Throws:

SQLException

createIndex

private static void createIndex(Session session,
boolean unique,
String indexName,
String tableName,
String... columnNames)
                         throws SQLException

Throws:

SQLException

dropNotNullConstraint

private static void dropNotNullConstraint(Session session,
String tableName,
String columnName,
String mySqlDataType)

dropTable

private static void dropTable(Session session,
String tableName)

removeContext

private static void removeContext(Session session,
int itemType)

Remove context-related information for the given item type.

cleanContextFromProperty

private static void cleanContextFromProperty(Session session,
Item itemType,
String propertyName,
String replacementPropertyName,
boolean forceRemoveFilter)

replaceInCommaSeparatedList

private static String replaceInCommaSeparatedList(String list,
String find,
String replacement)