There were a number of changes to the database schema in Elastic Path 6.1 compared to the schema in Elastic Path 6.0.4. Some new tables and columns were added to support new functionality, one table that is no longer used was removed, and a few columns were modified to increase data storage efficiency and data integrity.
Let's look at the details of these schema changes.
New Tables for Settings Storage
The settings framework feature was added in 6.1, and settings that were previously stored in commerce-config.xml files in 6.0.x have been moved to the database. The new settings-related tables have interdependencies but are not referentially linked to other tables in the system.
- TSETTINGDEFINITION keeps track of all the application setting definitions and their default values.
- TSETTINGMETADATA has information on how various settings apply to the system.
- TSETTINGVALUE keeps track of setting values.
New Table for Advanced Search
TADVANCEDSEARCHQUERY stores saved queries that were created for the Advanced Search feature. It has a referential link to TCMUSER.
New Table for Gift Certificate Themes
TGIFTCERTIFICATETHEME stores gift certificate theme definitions, and has no referential links to any other tables.
New Table for PCI Compliance
TPASSWORDHISTORY stores a user's password history, and has a referential link to TCMUSER.
New Table for Index Build Status
TINDEXBUILDSTATUS stores the status of each SOLR/Lucene index, including its last build date and its current status (built, rebuilding, etc).
New Tables for Storefront Supported Locales and Supported Currencies
TSTORESUPPORTEDLOCALE and TSTORESUPPORTEDCURRENCY were added to take the place of the deleted TSTORE.DEFAULTLOCALE and TSTORE.DEFAULTCURRENCY columns. These tables have a foreign key to the TSTORE table.
Unused Table
TPROMOCODE is a legacy table that is not longer referenced by Elastic Path code and is no longer created by the 6.1 database creation scripts. If you are not using it, it can be safely removed.
New or Modified Columns
The following new columns were added to the schema:
- Store States
- TSTORE.STORE_STATE - Integer column - keeps track of the state a store is currently in. Value is required but has a default value of 0.
- PCI Compliance
- TCMUSER.LAST_CHANGED_PASSWORD_DATE - Date column - value not required.
- TCMUSER.FAILED_LOGIN_ATTEMPTS - Integer column - value required, defaults to 0.
- TCMUSER.STATUS - Integer column - value required, defaults to 4.
- TCMUSER.ENABLED - This column was removed, to be replaced with the STATUS field.
- Miscellaneous
- TSTORE.DEFAULT_LOCALE is no longer a required column.
- TSTORE.DEFAULT_CURRENCY is no longer a required column.
- TSTORE.EMAIL_SENDER_NAME is no longer a required column.
- TSTORE.EMAIL_SENDER_ADDRESS is no longer a required column.
- TSTORE.STORE_ADMIN_EMAIL is no longer a required column.
- TORDERSHIPMENT.SHIPMENT_NUMBER was increased in size from 10 characters to 64.
- TINVENTORYAUDIT.EVENT_ORIGINATOR was increased in size from 30 to 255.
- Indexes
- TORDERLOCK.ORDER_UID is now an indexed column.
- TORDERSHIPMENT.SHIPMENT_NUMBER is now an indexed column.
So, what does this mean for the average developer working on an upgrade from 6.0.4 to 6.1? Not very much. The database upgrade scripts included in the 6.1 distribution take care of all the necessary schema changes.
- Populating the settings tables with the appropriate values is critical to getting your 6.1 install up and running. For more information, see the Elastic Path 6.1 Upgrade Guide at http://docs.elasticpath.com/display/EP61UPGRADE.
- Increasing the size of a few columns should have minimal impact.
- Removing the "required" flag on some fields in the STORE table is essential to the Store States feature, but should have no impact on existing systems.
- TSTORE.STORE_STATE - 6.0.x only had a single store state: OPEN. The OPEN state in 6.1 is represented by an integer value of 200 in this field.
- TCMUSER.STATUS replaces the ENABLED column but is backward compatible with its values. The value of the STATUS column should be the same as the value of the ENABLED column for every record during upgrade.
- The new indexes can be added without any problems.
- Removing the unused TPROMOCODE table should have no impact, but if you have customizations that are using this table, then it can also be left in with no impact.
- Creating the new TSTORESUPPORTEDCURRENCY and TSTORESUPPORTEDLOCALE tables for Stores, and creating records corresponding to the column values in the corresponding columns that were removed from TSTORE should be fairly easy.
Upgrading an Elastic Path database-from 6.0.x to 6.1 is relatively painless. Only one integer field's data must be translated to a new field and removed (TCmUser.Enabled to TCmUser.Status), and only one new integer field (TStore.store_state) must added and populated with data (but every row has the same value for an upgrade). Other schema changes should have no significant impact on a stock EP6.0.x database.