Revisions Requiring Data Migration - upgrade ofbiz

If you are upgrading an existing system, be prepared to run the following commands in the ofbiz home directory:

• svn up or git pull

• ./gradlew clean

• ./gradlew "ofbiz --load-data readers=seed"
• ./gradlew ofbiz

Summary (in historical order)

These are data model changes that usually involve deprecating an entity or field and replacing it with a new one. Committers should follow the pattern described here to make things easier for end-users: Deprecated Entities

If the pattern described there is used then end-users will simply have to update OFBiz, run it on a server that is not publicly accessible, let OFBiz do the automatic database table changes (ie add tables and columns for new entities and fields), and then run the series of services described here between the revision they were using, and the revision they are updating to.

If you are an end-user and committer who has done something that does not allow you to follow this simple set of steps, please contribute something better, or at the very least complain so committers will better understand your needs.

Committers should make an entry in this list if your revision causes a need for any manual data migration or other update steps in production instances:

1. A file change which needs additional unload/reload/convert activities
2. Any tables which can be deleted(dropped) because they are not used anymore.
3. Any other required activity to keep your production site operational

R587127:refactored party content

1. export the content of entity: PartyContent
2. add the fields partyContentTypeId and fromDate to the exported file
3. upgrade and restart the system.
4. Import the exported file

R643847:Rename FeaturePrice entity to ProductFeaturePrice

1. export the content of the entity with webtools export.
2. rename the entityname in the exported file
3. upgrade and restart the system.
4. Import the exported file

R673842:Fixed Asset Meter refactor

1. Run the migrateFixedAssetMaintMeter service from WebTools (no parameters needed).
2. Check to make sure all data was transferred from FixedAssetMaintMeter to FixedAssetMeter.
3. Drop the FixedAssetMaintMeter table.

R686731:Add support of fromDate & thruDate in CustRequestRole entity

1. after upgrade run the service 'migrateCustRequestRole' to copy the current data to the new table.

R688741:Add currency to PartyRate entity

1. export the content of the entity with webtools export.
2. add the currency to the exported file.
3. drop the table
4. upgrade and restart the system.
5. Import the exported file

R690360 Remove taxCategory, taxVatCode and taxDutyCode fields from Product entity and all things related

R691362 Fix an issue in artifactInfo reported on user ML

R1805961 "Manage life span of marketing related

R691380 Some changes related to GeoPoint have slipped in in r691362

R1805961 "Manage life span of marketing related

1. Drop the SimpleTaxLookup from you production DB (subsidiary as it's probably already not used)
2. upgrade to at least R691380 and restart the system.

R712366 http://svn.eu.apache.org/viewvc?view=rev&revision=712366

1. Export the UserPrefGroupType entity with webtools export - NB The export needs to be done before you upgrade otherwise the userPrefTypeId will not be included in the export
2. Change the field userPrefTypeId to userPrefGroupTypeId in the exported data file.
3. Drop the UserPrefGroupType table.
4. Upgrade and restart the system.
5. Import the exported file.

R72889# Rate refactoring

if there is not much data in the tables below we advise you to re-enter these by hand otherwise:

1. Unload (with webtools) the WorkEffortAssignmentRate, EmplPositionTypeRate and PartyRate entities, before upgrading
2. upgrade and reload the seed data,
3. restart the system
4. reload, if required the above mentioned tables.

R754604 my portal update http://svn.apache.org/viewvc?rev=754604&view=rev

Go to webtools and delete the all records from entity PortalPagePortlet where PortalPagePortletId = "mycommunications" or "otherCommunications"

R759853, 762232 drop entity Portlet_PortLet_Category and Portlet_Category portal_page_column,  portal_page_portlet and portal_page and reload these from seed data.

R765862: any users which have the theme set in userPreferences set to "DEFAULT", the record should be deleted.

R766426

alter table WEB_SITE drop VISUAL_THEME_SET
drop table Visual_Theme_Resource, Visual_Theme, VISUAL_THEME_SET
and then reload from seed data

R767278 PaymentGatewayPayflowPro and PaymentGatewayClearCommerce password encrypted.

ALTER TABLE Payment_Gateway_Payflow_Pro ADD COLUMN new_pwd VARCHAR(255)
ALTER TABLE Payment_Gateway_Payflow_Pro DROP COLUMN pwd
RENAME COLUMN Payment_Gateway_Payflow_Pro.new_pwd TO pwd
UPDATE Payment_Gateway_Payflow_Pro SET pwd = null

ALTER TABLE Payment_Gateway_Clear_Commerce ADD COLUMN new_pwd VARCHAR(255)
ALTER TABLE Payment_Gateway_Clear_Commerce DROP COLUMN pwd
RENAME COLUMN Payment_Gateway_Clear_Commerce.new_pwd TO pwd
UPDATE Payment_Gateway_Clear_Commerce SET pwd = null
execute an ant run-install-seed

R785872: go to webtools -> Entity Data Maintenance and edit entity PortalPagePortlet

portalPageId=MYPORTAL_EMPLOYEE delete entry:
    portalPortletId=NCOM_CUST_REQUESTS
    portalPortletId=MyCommunications
remove the the same portalPortId's on portalpage=MYPORTAL_EMPL-NOEML
execute an ./ant run-install-seed

R827914:Add support of fromDate & thruDate in WorkEffortContactMech entity

After upgrade run the service 'migrateWorkEffortContactMech' to copy the current data to the new table.

R883140: AgreementWorkEffortAppl entity relation types change

1. Upgrade and restart the system.
2. Run the migrateAgreementWorkEffortAppl service from WebTools.
3. Check to make sure all data was transferred from OldAgreementWorkEffortAppl to AgreementWorkEffortApplic.
4. Drop the AgreementWorkEffortAppl table.

R929912: deprecate squareFootage and replace it by facilitySize (type fixed-point) and facilitySizeUomId

This script should be run initially to move values from square_footage to facility_size and set the Uom
UPDATE Facility SET facility_size=square_footage;
UPDATE Facility SET facility_size_uom_id='AREA_ft2';
then the square_footage column might be removed
ALTER TABLE Facility DROP COLUMN square_footage;

R935969: Production run cost formulae are cloned when a production run is created OFBIZ-4194 - Getting issue details... STATUS

If you have open production runs before the upgrade then production run costs will not be computed when the order is finalized; in fact, after the upgrade, the system will clone WorkEffortCostCalc from the routing definition to the production run and will only consider the cloned records when the production run is finalized.

R1076507 Reorganization of product images.

Before doing this upgrade, make a backup of your framework/images/webapp/images directory because the existing subdirectories will be deleted because of a reorganization. After the upgrade, if you restore these directories your system will work again and images can be found. Mor info at: OFBIZ-4194 - Getting issue details... STATUS

R1204455 Move of webstatistics and simplification

After upgrade: webanalytics entity has moved from product to content, this entity has normally only a single record per website so a conversion program is not provided. After upgrade, save the content of the entity web_analytics_config and drop it in webtools. Then restart and re-enter the saved record.
If you already using this feature in your ecommerce remove the line:
<field-map field-name="isEnabled" value="Y"/>
from the file ofbiz/trunk/specialpurpose/ecommerce/widget/CommonScreens.xml

R1222544 adds the possibility of adding tags to products next to having keywords to products, can also optionally approve/reject via a status field"

This affects the upgrade of the productKeyword entity. If the content can be re-generated, just run the generate keywords program, if you want to keep the content then see the service: migrateProductKeyword in the file: applications/product/servicedef/services_upgrade.xml for more information

Trunk r1557447+57, R13.07 r1557448+58, R12.04 r1557449+59: Improve the GeoPoint data model OFBIZ-5758 - Getting issue details... STATUS

1. export the GeoPoint entity with webtools export.
2. drop the table
3. restart the system.
4. Import the exported file

R1647271 Refactoring of Visit/Visitor/ServerHit* (and related) entities in order to be able to maintain them in a database separate from the main transactional database:

Added a new entity group ("org.ofbiz.stats") for the Visit/Visitor/ServerHit* (and related) entities; for now the group is not active so that the entities are still in the same database as before; however assigning them to a different database is now a matter of uncommenting the group file definition in framework/webapp/ofbiz-component.xml and provide the new datasource definition

 Removed foreign key relationship from/to this group of entities and the other entities of the OFBiz data model; the fields are still available but they are not enforced by a foreign key constraint

ALTER TABLE CONTENT_SEARCH_RESULT DROP FOREIGN KEY CNT_SCHRES_VST;

ALTER TABLE TRACKING_CODE_VISIT DROP FOREIGN KEY TKNG_CODVST_VST;

ALTER TABLE CART_ABANDONED_LINE DROP FOREIGN KEY CART_ABLN_VST;

ALTER TABLE SHOPPING_LIST DROP FOREIGN KEY SHLIST_VSTR;

ALTER TABLE PARTY_NEED DROP FOREIGN KEY PARTY_NEED_VSIT;

ALTER TABLE PARTY_DATA_SOURCE DROP FOREIGN KEY PARTY_DATSRC_VST;

ALTER TABLE INVENTORY_ITEM_TEMP_RES DROP FOREIGN KEY INV_ITEM_TR_VIS;

ALTER TABLE PRODUCT_SEARCH_RESULT DROP FOREIGN KEY PROD_SCHRES_VST;

ALTER TABLE OLD_PRODUCT_KEYWORD_RESULT DROP FOREIGN KEY PROD_KWDRES_VST;

ALTER TABLE WORK_EFFORT_SEARCH_RESULT DROP FOREIGN KEY WEFF_SCHRES_VST;

ALTER TABLE OLD_PRODUCT_KEYWORD_RESULT DROP FOREIGN KEY PROD_KWDRES_VST;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_CIP_STPRV;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_CIP_CNTRY;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_CONT_MECH;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_PARTY;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_ROLE_TYPE;

ALTER TABLE VISIT DROP FOREIGN KEY VISIT_PARTY_ROLE;

ALTER TABLE VISITOR DROP FOREIGN KEY VISITOR_PARTY;

ALTER TABLE SERVER_HIT DROP FOREIGN KEY SERVER_HIT_STATUS;

ALTER TABLE SERVER_HIT DROP FOREIGN KEY SERVER_HIT_USER;

R1709192 "SalaryStep entity was missing From and Thru dates" OFBIZ-5758 - Getting issue details... STATUS

This added the fromDate, createdByUserLogin and lastModifiedByUserLogin fields, fromDate is a PK field.

After upgrade run the 'migrateCustRequestRole' service to copy the current data to the new table (from OldSalaryStep to SalaryStep).

R1738588 "Improved CMS tree: more functionality and faster for large trees" OFBIZ-4502 - Getting issue details... STATUS

This added the fromDate, and thru Date fields to the WebSitePathAlias entity, fromDate is a PK field.

It was said in the JIRA that "To migrate existing path aliases the following SQL statements can be used:"

BEGIN;

UPDATE web_site_path_alias SET from_date=w.created_stamp FROM web_site_path_alias w WHERE w.path_alias=web_site_path_alias.path_alias;

ALTER TABLE web_site_path_alias

    DROP CONSTRAINT pk_web_site_path_alias,

    ADD CONSTRAINT pk_web_site_path_alias PRIMARY KEY (web_site_id, path_alias, from_date);

COMMIT;

I quickly tried this in EntitySQLProcessor it does not work. We need instead to provide a migrateWebSitePathAlias migrating service.

R1743656 "Wrong field type for fromDate and thruDate of CustomTimePeriod entity."  OFBIZ-5740 - Getting issue details... STATUS

 Changed CustomTimePeriod.fromDate and CustomTimePeriod.thruDate field type from date to date-time.

ALTER TABLE CUSTOM_TIME_PERIOD MODIFY COLUMN FROM_DATE datetime, MODIFY COLUMN THRU_DATE datetime;

R1746459 "Remove parentTypeId from InvoiceType data where InvoiceTypeId and parentTypeId are equal" OFBIZ-7147 - Getting issue details... STATUS

+R1747223 (minor only screens) "Fix different forms, screens and services affected due to removal of parentTypeId from "PURCHASE_INVOICE" and "SALES_INVOICE" InvoiceType data." OFBIZ-7201 - Getting issue details... STATUS

You may need to run these SQL expressions:

update invoice_type set parent_type_id = 'INVOICE' where invoice_type_id = 'SALES_INVOICE';
update invoice_type set parent_type_id = 'INVOICE' where invoice_type_id = 'PURCHASE_INVOICE';
update invoice_type set parent_type_id = 'INVOICE' where invoice_type_id = 'TEMPLATE';

R1793300 "Update msyql sql-type for datetime field-type to support Fractional Seconds in Time Values " OFBIZ-9337 - Getting issue details... STATUS

Updated sql-type for date-time and time field in fieldtypemysql.xml file,

Please upgrade mysql to at least 5.6.4 or higher.

After upgrade run 'generateMySqlFileWithAlterTableForTimestamps' service, groupName is required field for this service,

It will generate SQL file with alter query statement for date-time and time field at location "${ofbiz.home}/runtime/tempfiles/<groupName>.sql"

You can execute a SQL statement from any of the mysql batch command.

R1804408 "Removed OEMPartyId (manufacturerPartyId) from Product entity as it was remnant of an old approach."  OFBIZ-9368 - Getting issue details... STATUS

 SQL query to remove manufacturerPartyId from the database to avoid error in the logs for field count mismatch.

ALTER TABLE PRODUCT DROP MANUFACTURER_PARTY_ID;

R1805961 Manage life span of marketing related entities OFBIZ-9550 - Getting issue details... STATUS

This added the fromDate, and thruDate fields to the MarketingCampaignPrice, MarketingCampaignPromo, MarketingCampaignRole entities, fromDate is a PK field.

After an OFBiz start, run the SQL

BEGIN;

UPDATE marketing_campaign_price SET from_date='2000-01-01 00:00:00';

ALTER TABLE marketing_campaign_price

    DROP CONSTRAINT pk_marketing_campaign_price,

    ADD CONSTRAINT pk_marketing_campaign_price PRIMARY KEY (marketing_campaign_id, product_price_rule_id, from_date);

COMMIT;

BEGIN;

UPDATE marketing_campaign_promo SET from_date='2000-01-01 00:00:00';

ALTER TABLE marketing_campaign_promo

    DROP CONSTRAINT pk_marketing_campaign_promo,

    ADD CONSTRAINT pk_marketing_campaign_promo PRIMARY KEY (marketing_campaign_id, product_promo_id, from_date);

COMMIT;

BEGIN;

UPDATE marketing_campaign_role SET from_date='2000-01-01 00:00:00';

ALTER TABLE marketing_campaign_role

    DROP CONSTRAINT pk_marketing_campaign_role,

    ADD CONSTRAINT pk_marketing_campaign_role PRIMARY KEY (marketing_campaign_id, party_id, role_type_id, from_date);

COMMIT;

R1809396:  Fixed ShipmentGatewayConfig and PaymentGatewayConfig entities as we fixed type entities to follow a naming convention. OFBIZ-9696 - Getting issue details... STATUS

UPDATE Shipment_Gateway_Config SET shipment_gateway_conf_type_id = 'SHIP_GATEWAY_DHL' WHERE shipment_gateway_conf_type_id = 'DHL';
UPDATE Shipment_Gateway_Config SET shipment_gateway_conf_type_id = 'SHIP_GATEWAY_FEDEX' WHERE shipment_gateway_conf_type_id = 'FEDEX';
UPDATE Shipment_Gateway_Config SET shipment_gateway_conf_type_id = 'SHIP_GATEWAY_UPS' WHERE shipment_gateway_conf_type_id = 'UPS';
UPDATE Shipment_Gateway_Config SET shipment_gateway_conf_type_id = 'SHIP_GATEWAY_USPS' WHERE shipment_gateway_conf_type_id = 'USPS';

UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_SAGEPAY' WHERE payment_gateway_config_type_id = 'SAGEPAY';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_AUTH_NET' WHERE payment_gateway_config_type_id = 'AUTHORIZE_NET';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_CYBERSRC' WHERE payment_gateway_config_type_id = 'CYBERSOURCE';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_EWAY' WHERE payment_gateway_config_type_id = 'EWAY';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_PAYFLOWPRO' WHERE payment_gateway_config_type_id = 'PAYFLOWPRO';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_PAYPAL' WHERE payment_gateway_config_type_id = 'PAYPAL';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_CLRCOMRC' WHERE payment_gateway_config_type_id = 'CLEARCOMMERCE';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_WORLDPAY' WHERE payment_gateway_config_type_id = 'WORLDPAY';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_ORBITAL' WHERE payment_gateway_config_type_id = 'ORBITAL';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_SECUREPAY' WHERE payment_gateway_config_type_id = 'SECUREPAY';
UPDATE Payment_Gateway_Config SET payment_gateway_config_type_id = 'PAY_GATWY_IDEAL' WHERE payment_gateway_config_type_id = 'IDEAL';

R1812383: OFBIZ-9801 - Getting issue details... STATUS

After an OFBiz start, run the SQL

UPDATE security_group_permission SET from_date='2000-01-01 00:00:00';
ALTER TABLE security_group_permission
  DROP PRIMARY KEY
  ADD PRIMARY KEY (group_id, permission_id, from_date)

R1858479: OFBIZ-10921 - Getting issue details... STATUS

Person entity's maritalStatus field has been changed from indicator to Enumeration to manage various classification of legal marital status.
After upgrate migrateMaritalStatusFromIndicatorToEnum service(committed at R1864215) can be used to upgrade existing data of maritalStatus field to the new maritalStatusEnumId.

R1866558: OFBIZ-11182 - Getting issue details... STATUS

PicklistStatus replace PicklistStatusHistory (this has been rename OldPicklistStatusHistory) to be support natively by entityauto engine lije other EntityStatus.

After upgrate migrateOldPicklistStatusHistoryToPickListStatus service can be used to forward  historical data present in PicklistStatusHistory to PicklistStatus.

Commits e7b3169, f906994: Rename some connoted words like blacklist and whitelist OFBIZ-12168 - Getting issue details... STATUS

After upgrate the migrateOldOrderBlacklistAndOldOrderBlacklistType service can be used to forward  historical data from OrderBlacklist and OrderBlacklistType entities to new OrderDenylist and OrderDenylistType entities

Commit d288e12: Change GeoPoint latitude an longitude fields from type short-varchar to fixed-point for correct comparisons https://github.com/apache/ofbiz-framework/pull/220