Data Migration from Jetspeed 2.2.2 to 2.3.1

No data migration is necessary from version 2.2.2 to 2.3.1.

Data Migration from Jetspeed 2.2.1 to 2.2.2

The following tables describe database schema changes from version 2.2.1 to version 2.2.2.

Note: besides the below modifications, there are a few renames of index and foreign key names for naming consistency reasons only, which can be ignored and are not mentioned in the below tables either.

Dropped Foreign Keys (and their Indexes)

Table Foreign Key
CUSTOM_PORTLET_MODE FK_CUSTOM_PORTLET_MODE_1
CUSTOM_WINDOW_STATE FK_CUSTOM_WINDOW_STATE_1
EVENT_DEFINITION FK_EVENT_DEFINITION_1
PA_METADATA_FIELDS FK_PA_METADATA_FIELDS_1
PD_METADATA_FIELDS FK_PD_METADATA_FIELDS_1
SECURITY_ROLE FK_SECURITY_ROLE_REF_1
USER_ATTRIBUTE FK_USER_ATTRIBUTE_1
USER_ATTRIBUTE_REF FK_USER_ATTRIBUTE_REF_1

Dropped Indexes

Table Index
PROFILE_PAGE_ASSOC UN_PROFILE_PAGE_1

Added Columns

Table Column Type Required
PORTLET_DEFINITION CLONE_PARENT VARCHAR(80) false

Added Indexes

Table Index Unique Column(s)
FOLDER_MENU IX_FOLDER_MENU_2 non unique FOLDER_ID
PAGE_MENU IX_PAGE_MENU_2 non unique PAGE_ID
PRINCIPAL_PERMISSION IX_PRINCIPAL_PERMISSION_2 non unique PRINICIPAL_ID
RULE_CRITERION IX_RULE_CRITERION_0 non unique RULE_ID

Added Primary Keys

Table Column(s)
CLIENT_TO_CAPABILITY CLIENT_ID, CAPABILITY_ID
CLIENT_TO_MIMETYPE CLIENT_ID, MIMETYPE_ID
MEDIATYPE_TO_CAPABILITY MEDIATYPE_ID, CAPABILITY_ID
MEDIATYPE_TO_MIMETYPE MEDIATYPE_ID, MIMETYPE_ID
PROFILE_PAGE_ASSOC LOCATOR_HASH, PAGE_ID

Example SQL migration script from 2.2.1 to 2.2.2

Here is an example of a SQL migration script for a MySQL database: 

            
ALTER TABLE CUSTOM_PORTLET_MODE DROP FOREIGN KEY FK_CUSTOM_PORTLET_MODE_1;
ALTER TABLE CUSTOM_WINDOW_STATE DROP FOREIGN KEY FK_CUSTOM_WINDOW_STATE_1;
ALTER TABLE EVENT_DEFINITION DROP FOREIGN KEY FK_EVENT_DEFINITION_1;
ALTER TABLE PA_METADATA_FIELDS DROP FOREIGN KEY FK_PA_METADATA_FIELDS_1;
ALTER TABLE PD_METADATA_FIELDS DROP FOREIGN KEY FK_PD_METADATA_FIELDS_1;
ALTER TABLE SECURITY_ROLE DROP FOREIGN KEY FK_SECURITY_ROLE_REF_1;
ALTER TABLE USER_ATTRIBUTE DROP FOREIGN KEY FK_USER_ATTRIBUTE_1;
ALTER TABLE USER_ATTRIBUTE_REF DROP FOREIGN KEY FK_USER_ATTRIBUTE_REF_1;

ALTER TABLE CUSTOM_PORTLET_MODE DROP INDEX FK_CUSTOM_PORTLET_MODE_1;
ALTER TABLE CUSTOM_WINDOW_STATE DROP INDEX FK_CUSTOM_WINDOW_STATE_1;
ALTER TABLE EVENT_DEFINITION DROP INDEX FK_EVENT_DEFINITION_1;
ALTER TABLE PA_METADATA_FIELDS DROP INDEX FK_PA_METADATA_FIELDS_1;
ALTER TABLE PD_METADATA_FIELDS DROP INDEX FK_PD_METADATA_FIELDS_1;
ALTER TABLE SECURITY_ROLE DROP INDEX FK_SECURITY_ROLE_REF_1;
ALTER TABLE USER_ATTRIBUTE DROP INDEX FK_USER_ATTRIBUTE_1;
ALTER TABLE USER_ATTRIBUTE_REF DROP INDEX FK_USER_ATTRIBUTE_REF_1;

ALTER TABLE PROFILE_PAGE_ASSOC DROP INDEX UN_PROFILE_PAGE_1;

ALTER TABLE PORTLET_DEFINITION ADD COLUMN CLONE_PARENT VARCHAR(80);

CREATE INDEX IX_FOLDER_MENU_2 ON FOLDER_MENU ( FOLDER_ID );
CREATE INDEX IX_PAGE_MENU_2 ON PAGE_MENU ( PAGE_ID );
CREATE INDEX IX_PRINICIPAL_PERMISSION_2 ON PRINCIPAL_PERMISSION ( PRINCIPAL_ID );
CREATE INDEX IX_RULE_CRITERION_0 ON RULE_CRITERION ( RULE_ID );

ALTER TABLE CLIENT_TO_CAPABILITY ADD PRIMARY KEY (CLIENT_ID, CAPABILITY_ID);
ALTER TABLE CLIENT_TO_MIMETYPE ADD PRIMARY KEY (CLIENT_ID, MIMETYPE_ID);
ALTER TABLE MEDIATYPE_TO_CAPABILITY ADD PRIMARY KEY (MEDIATYPE_ID, CAPABILITY_ID);
ALTER TABLE MEDIATYPE_TO_MIMETYPE ADD PRIMARY KEY (MEDIATYPE_ID, MIMETYPE_ID);
ALTER TABLE PROFILE_PAGE_ASSOC ADD PRIMARY KEY (LOCATOR_HASH, PAGE_ID );

Data Migrating from Jetspeed 2.1.3/2.1.4 to 2.2.1

Extensive schema changes were made between the 2.1 and 2.2 versions related to the Portlet API 2.0 Standard implementation, the Pluto 2.0 integration, and the refactoring of the security and SSO components. Here is a summary of the table modifications that were made:

Registry and Preferences Table Name Modification
CUSTOM_PORTLET_MODE columns changed
CUSTOM_WINDOW_STATE columns changed
EVENT_ALIAS new table
EVENT_DEFINITION new table
FILTERED_PORTLET new table
FILTER_LIFECYCLE new table
FILTER_MAPPING new table
LANGUAGE columns changed
LOCALE_ENCODING_MAPPING new table
LOCALIZED_DESCRIPTION columns changed
LOCALIZED_DISPLAY_NAME columns changed
NAMED_PARAMETER new table
PARAMETER columns changed
PARAMETER_ALIAS new table
PA_SECURITY_CONSTRAINT new table
PORTLET_APPLICATION columns changed
PORTLET_CONTENT_TYPE deleted
PORTLET_DEFINITION columns changed
PORTLET_ENTITY deleted
PORTLET_FILTER new table
PORTLET_LISTENER new table
PORTLET_PREFERENCE new table
PORTLET_PREFERENCE_VALUE new table
PORTLET_SUPPORTS new table
PREFS_NODE deleted
PREFS_PROPERTY_VALUE deleted
PROCESSING_EVENT new table
PUBLIC_PARAMETER new table
PUBLISHING_EVENT new table
RUNTIME_OPTION new table
RUNTIME_VALUE new table
SECURED_PORTLET new table
SECURITY_ROLE columns and constraints changed
USER_ATTRIBUTE columns changed
WEB_APPLICATION deleted

Security and SSO Table Name Modification
SECURITY_ATTRIBUTE new table
SECURITY_CREDENTIAL columns changed
SECURITY_DOMAIN new table
SECURITY_GROUP_ROLE deleted
SECURITY_PERMISSION columns and constraints changed
SECURITY_PRINCIPAL columns and constraints changed
SECURITY_PRINCIPAL_ASSOC new table
SECURITY_USER_GROUP deleted
SECURITY_USER_ROLE deleted
SSO_COOKIE deleted
SSO_COOKIE_TO_REMOTE deleted
SSO_PRINCIPAL_TO_REMOTE deleted
SSO_SITE columns and constraints changed
SSO_SITE_TO_PRINCIPALS deleted
SSO_SITE_TO_REMOTE deleted

Statisitics Table Name Modification
ADMIN_ACTIVITY column sizes changed
USER_ACTIVITY column sizes changed

Given the extensive modifications made between these releases, migration is best performed using an ETL approach. To support this outside of the Jetspeed portal build and runtime environments, export, initialization, and import functionality have been integrated into the Jetspeed installer. The installer can be downloaded as a binary and, together with a JDBC driver, can be used to migrate from 2.1.3/2.1.4 to 2.2.1.

Documentation for ETL migration using the Jetspeed installer can be found here: ETL Migration Guide. Note that migration of Derby databases using the installer is not currently supported.

Data Migrating from Jetspeed 2.1.3 to 2.1.4

The following tables describe database schema changes from version 2.1.3 to version 2.1.4.

Constraint removed

Table column(s) 2.1.3 2.1.4
PARAMETER PARAMETER_VALUE Required Not required

Here is an example of the SQL migration scripts for the MySQL database: 

ALTER TABLE 'PARAMETER` MODIFY COLUMN `PARAMETER_VALUE` DEFAULT NULL;

Data Migrating from Jetspeed 2.1.2 to 2.1.3

The following tables describe database schema changes from version 2.1.3 to version 2.1.2.

Added Indexes

Table Index type column(s)
PREFS_NODE IX_PREFS_NODE_1 non unique PARENT_NODE_ID
PREFS_NODE IX_PREFS_NODE_2 non unique FULL_PATH
PREFS_PROPERTY_VALUE IX_FKPPV_1 non unique NODE_ID

Foreign Key Altering

Table Name Column(s) Referencing table Referencing column(s) 2.1.2 2.1.3
PREFS_NODE FK_PREFS_NODE_1 PARENT_NODE_ID PREFS_NODE NODE_ID (not casade delete) CASCADE DELETE
PREFS_PROPERTY_VALUE (not named) NODE_ID PREFS_NODE NODE_ID (not casade delete) CASCADE DELETE

Here is an example of the SQL migration scripts for the MySQL database: 

            
CREATE INDEX IX_PREFS_NODE_1 ON PREFS_NODE (PARENT_NODE_ID); 
CREATE INDEX IX_PREFS_NODE_2 ON PREFS_NODE (FULL_PATH); 
CREATE INDEX IX_FKPPV_1 ON PREFS_PROPERTY_VALUE (NODE_ID); 

ALTER TABLE PREFS_NODE ADD CONSTRAINT FK_PREFS_NODE_1 FOREIGN KEY (PARENT_NODE_ID) REFERENCES PREFS_NODE (NODE_ID) ON DELETE CASCADE; 
ALTER TABLE PREFS_PROPERTY_VALUE ADD CONSTRAINT FK_PREFS_PROPERTY_VALUE_1 FOREIGN KEY (NODE_ID) REFERENCES PREFS_NODE (NODE_ID) ON DELETE CASCADE;

Here is an example of the SQL migration scripts for the MySQL database: 

            
CREATE INDEX IX_PREFS_NODE_1 ON PREFS_NODE (PARENT_NODE_ID); 
CREATE INDEX IX_PREFS_NODE_2 ON PREFS_NODE (FULL_PATH); 
CREATE INDEX IX_FKPPV_1 ON PREFS_PROPERTY_VALUE (NODE_ID); 

ALTER TABLE PREFS_NODE ADD CONSTRAINT FK_PREFS_NODE_1 FOREIGN KEY (PARENT_NODE_ID) REFERENCES PREFS_NODE (NODE_ID) ON DELETE CASCADE; 
ALTER TABLE PREFS_PROPERTY_VALUE ADD CONSTRAINT FK_PREFS_PROPERTY_VALUE_1 FOREIGN KEY (NODE_ID) REFERENCES PREFS_NODE (NODE_ID) ON DELETE CASCADE;

Data Migrating from Jetspeed 2.1 to 2.1.2

The following tables describe database schema changes from version 2.1 to version 2.1.2.

Added Tables

Table
ADMIN_ACTIVITY
USER_ACTIVITY

Configuration Migrating from Jetspeed 2.0 to 2.1

The following items describe important functional changes in Jetspeed 2.1

Portlet API Caching

Version 2.1 is the first Jetspeed version to support Portlet API Caching. In 2.0 the expiration-cache was set to -1 (infinity time) for all layouts in the file WEB-INF\apps\jetspeed-layouts\WEB-INF\portlet.xml. When upgrading to version 2.1 or greater from 2.0, you will need to update your database to disable portlet caching on the Jetspeed layouts. Updating the layouts can be achieved by either redeploying the jetspeed-layouts portlet application, or by updating your database as shown below (assuming the APPLICATION_ID value is 2) for the jetspeed-layouts application. 

UPDATE PORTLET_DEFINITION P SET EXPIRATION_CACHE = 0 WHERE P.APPLICATION_ID = 2

Portlet Preferences

Portlet Preferences are now by default user specific on all pages. In Jetspeed 2.0, for shared pages, the Portlet Preferences were shared as well.

To retain most of the old Jetspeed 2.0 behavior, allowing an administrator to still set/modify initial Preferences for other users,
modify the following setting in the registry.xml spring assembly configuration for the portletEntityAccessImpl component: 

<!-- 
  All preferences were shared. With JS2-449, preferences are now
  stored 'per user'. The username is stored in the preferences FULL_PATH
  To turn on mergeSharedPreferences configure this property to true 
  This will NOT turn off per user prefs, 
  but instead merge with them, where user prefs override.
  boolean
-->    
<constructor-arg type="boolean">
<value>false</value>
</constructor-arg>
As is commented in the assembly configuration also, setting mergeSharedPreferences to true will NOT turn off per user Preferences.
Once a user sets its own Preference values (if allowed), those will override the shared Preference values.
See for further information: JS2-449

Data Migrating from Jetspeed 2.0 to 2.1

The following tables describe database schema changes from version 2.0 to version 2.1.

Added Tables

Table
CUSTOM_PORTLET_MODE
CUSTOM_WINDOW_STATE

Column Altering

Table Column 2.0 2.1
PORTLET_STATISTICS ELAPSED_TIME INTEGER BIGINT
PAGE_STATISTICS ELAPSED_TIME INTEGER BIGINT
USER_STATISTICS ELAPSED_TIME INTEGER BIGINT
FOLDER SKIN NEW: VARCHAR(80)
FOLDER DEFAULT_LAYOUT_DECORATOR NEW: VARCHAR(80)
FOLDER DEFAULT_PORTLET_DECORATOR NEW: VARCHAR(80)
FRAGMENT STATE VARCHAR(40) VARCHAR(10)
FRAGMENT PMODE NEW: VARCHAR(10)
FRAGMENT LAYOUT_X NEW: REAL
FRAGMENT LAYOUT_Y NEW: REAL
FRAGMENT LAYOUT_Z NEW: REAL
FRAGMENT LAYOUT_WIDTH NEW: REAL
FRAGMENT LAYOUT_HEIGHT NEW: REAL
LINK SKIN NEW: VARCHAR(80)
PORTLET_DEFINITION SECURITY_REF NEW: VARCHAR(40)
PORTLET_DEFINITION SECURITY_REF NEW: VARCHAR(40)
PORTLET_APPLICATION SECURITY_REF NEW: VARCHAR(40)
SSO_COOKIE COOKIE VARCHAR(250) VARCHAR(1024)

Index Altering

Table Index 2.0 2.1
FOLDER_MENU UN_FOLDER_MENU_1 unique (non unique) index
PAGE_MENU UN_PAGE_MENU_1 unique (non unique) index
FRAGMENT UN_FRAGMENT_1 unique (non unique) index

Foreign Key Altering

Table Column 2.0 2.1
FOLDER (FK constraint) (not named) named FK constraint "FK_FOLDER_1"
FOLDER_METADATA (FK constraint) (not named) named FK constraint "FK_FOLDER_METADATA_1"
FOLDER_CONSTRAINT (FK constraint) (not named) named FK constraint "FK_FOLDER_CONSTRAINT_1"
FOLDER_CONSTRAINTS_REF (FK constraint) (not named) named FK constraint "FK_FOLDER_CONSTRAINT_REF_1"
FOLDER_ORDER (FK constraint) (not named) named FK constraint "FK_FOLDER_ORDER_1"
FOLDER_MENU (FK constraint) (not named) named FK constraint "FK_FOLDER_MENU_1"
FOLDER_MENU (FK constraint) (not named) named FK constraint "FK_FOLDER_MENU_2"
FOLDER_MENU_METADATA (FK constraint) (not named) named FK constraint "FK_FOLDER_MENU_METADATA_1"
PAGE (FK constraint) (not named) named FK constraint "FK_PAGE_1"
PAGE_METADATA (FK constraint) (not named) named FK constraint "FK_PAGE_METADATA_1"
PAGE_CONSTRAINT (FK constraint) (not named) named FK constraint "FK_PAGE_CONSTRAINT_1"
PAGE_CONSTRAINT_REF (FK constraint) (not named) named FK constraint "FK_PAGE_CONSTRAINTS_REF_1"
PAGE_MENU (FK constraint) (not named) named FK constraint "FK_PAGE_MENU_1"
PAGE_MENU (FK constraint) (not named) named FK constraint "PM_M_FK_PAGE_ID_PAGE"
PAGE_MENU_METADATA (FK constraint) (not named) named FK constraint "FK_PAGE_MENU_METADATA_1"
FRAGMENT (FK constraint) (not named) named FK constraint "FK_FRAGMENT_1"
FRAGMENT (FK constraint) (not named) named FK constraint "FK_FRAGMENT_2"
FRAGMENT_CONSTRAINT (FK constraint) (not named) named FK constraint "FK_FRAGMENT_CONSTRAINT_1"
FRAGMENT_CONSTRAINT_REF (FK constraint) (not named) named FK constraint "FK_FRAGMENT_CONSTRAINT_REF_1"
FRAGMENT_PREF (FK constraint) (not named) named FK constraint "FK_FRAGMENT_PREF_1"
FRAGMENT_PREF_VALUE (FK constraint) (not named) named FK constraint "FK_FRAGMENT_PREF_VALUE_1"
LINK (FK constraint) (not named) named FK constraint "FK_LINK_1"
LINK_METADATA (FK constraint) (not named) named FK constraint "FK_LINK_METADATA_1"
LINK_CONSTRAINT (FK constraint) (not named) named FK constraint "FK_LINK_CONSTRAINT_1"
LINK_CONSTRAINT_REF (FK constraint) (not named) named FK constraint "FK_LINK_CONSTRAINT_REF_1"
PAGE_SECURITY (FK constraint) (not named) named FK constraint "FK_PAGE_SECURITY_1"
PAGE_SEC_CONSTRAINTS_DEF (FK constraint) (not named) named FK constraint "FK_PAGE_SEC_CONSTRAINTS_DEF_1"
PAGE_SEC_CONSTRAINT_DEF (FK constraint) (not named) named FK constraint "FK_PAGE_SEC_CONSTRAINT_DEF_1"
PAGE_SEC_CONSTRAINTS_REF (FK constraint) (not named) named FK constraint "FK_PAGE_SEC_CONSTRAINTS_REF_1"
PAGE_SEC_CONSTRAINT_REF (FK constraint) (not named) named FK constraint "FK_PAGE_SEC_CONSTRAINT_REF_1"
RULE_CRITERION (FK constraint) (not named) named FK constraint "FK_RULE_CRITERION_1"
PREFS_NODE (FK constraint) (not named) named FK constraint "FK_PREFS_NODE_1"
PA_METADATA_FIELDS (FK constraint) (not named) named FK constraint "FK_PA_METADATA_FIELDS_1"
PD_METADATA_FIELDS (FK constraint) (not named) named FK constraint "FK_PD_METADATA_FIELDS_1"
USER_ATTRIBUTE_REF (FK constraint) (not named) named FK constraint "FK_USER_ATTRIBUTE_REF_1"
USER_ATTRIBUTE (FK constraint) (not named) named FK constraint "FK_USER_ATTRIBUTE_1"
PRINCIPAL_PERMISSION (FK constraint) (not named) named FK constraint "FK_PRINCIPAL_PERMISSION_1"
PRINCIPAL_PERMISSION (FK constraint) (not named) named FK constraint "FK_PRINCIPAL_PERMISSION_2"
SECURITY_CREDENTIAL (FK constraint) (not named) named FK constraint "FK_SECURITY_CREDENTIAL_1"
SSO_SITE_TO_PRINCIPALS (FK constraint) (not named) named FK constraint "SSO_SITE_TO_PRINC_FK1"
SSO_SITE_TO_PRINCIPALS (FK constraint) (not named) named FK constraint "SSO_SITE_TO_PRINC_FK2"
SSO_PRINCIPAL_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_PRINCIPAL_TO_REMOTE_1"
SSO_PRINCIPAL_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_PRINCIPAL_TO_REMOTE_2"
SSO_SITE_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_SITE_TO_REMOTE_1"
SSO_SITE_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_SITE_TO_REMOTE_2"
SSO_COOKIE_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_COOKIE_TO_REMOTE_1"
SSO_COOKIE_TO_REMOTE (FK constraint) (not named) named FK constraint "FK_SSO_COOKIE_TO_REMOTE_2"
SECURITY_USER_ROLE (FK constraint) (not named) named FK constraint "FK_SECURITY_USER_ROLE_1"
SECURITY_USER_ROLE (FK constraint) (not named) named FK constraint "FK_SECURITY_USER_ROLE_2"
SECURITY_USER_GROUP (FK constraint) (not named) named FK constraint "FK_SECURITY_USER_GROUP_1"
SECURITY_USER_GROUP (FK constraint) (not named) named FK constraint "FK_SECURITY_USER_GROUP_2"
SECURITY_GROUP_ROLE (FK constraint) (not named) named FK constraint "FK_SECURITY_GROUP_ROLE_1"
SECURITY_GROUP_ROLE (FK constraint) (not named) named FK constraint "FK_SECURITY_GROUP_ROLE_2"