We had a client who needed to display entire product categories (several hundred products in all) in a set of drop down list boxes on their Elastic Path Commerce storefront homepage. The data that had to be loaded was quite minimal, just the product codes, display names and a few localized attributes.

In this type of situation, using the default product loader to retrieve this information would not be the best approach. This is due to the fact that each Product domain object contains a large amount of data such as prices, skus, attributes, inventory and recommendations etc.  Loading all these details requires a significant number of queries to be run on the database so applying this approach to a homepage with hundreds of products would result in extremely poor performance.

For this customer, a better solution was to create a set of lightweight product display classes, and have these mapped directly to some custom JPA native queries. These queries were tailored to return only the specific product details needed and therefore avoid loading any unnecessary information.  To further reduce the number of database trips, we also introduced a timed cache in the storefront controllers which would store frequently accessed catalog items for a set period of time before being refreshed. 

The combination of these two techniques reduced their page response times from tens of seconds (using the default product loader) to under a few seconds even in the worst case scenario where a stale cache had to be refreshed.   Since most of the time the information would be available in the cache, the amount of database overhead was kept to a minimum.

If your storefront scenario has similar requirements, then the following code examples may also be useful for your project.

Using Native SQL Queries with JPA

To get all product codes and product names in a particular category, you can use the EntityManager's createNativeQuery method to create a native query with a WHERE clause that passes in the language string and a specific category UID.

final String productNamesByCatSql =

"SELECT tp.code, tpldf.display_name AS displayName"

+ " FROM tproduct AS tp"

+ " INNER JOIN tproductldf AS tpldf"

+ " ON tpldf.product_uid = tp.uidpk"

+ " INNER JOIN tproductcategory AS tpc"

+ " ON tp.uidpk = tpc.product_uid"

+ " WHERE tpldf.LOCALE = ?1"

+ " AND tpc.category_uid = ?2";

long categoryUid = getCategoryUid(request);

long start = System.currentTimeMillis();

// create a native SQL query

final Query query = entityManager.createNativeQuery(productNamesByCatSql, ProductDisplayBeanImpl.class);

// retrieve a simplified product list for a given category

final List<ProductDisplayBean> custProducts = (List<ProductDisplayBean>)

     query.setParameter(1, shoppingCart.getLocale().getLanguage())

     .setParameter(2, new Long(categoryUid))

     .getResultList();

if (LOG.isDebugEnabled()) {

     long elapsedTimeMillis = System.currentTimeMillis() - start;

     LOG.debug("No. of products returned = " + custProducts.size() + ", elapsed time (ms) = " + elapsedTimeMillis);

     for (ProductDisplayBean productBean : custProducts) {

          LOG.debug("[" + productBean.getCode() + ":" + productBean.getDisplayName() + "]");

     }

}

Like JPQL, native queries can be named for easy reuse using the @NamedNativeQuery annotation, however it's probably a better idea to externalize all the native query strings into an *orm.xml file so that the SQL can be updated without having to change the class files.  If you do that, you'll have to change all the createNativeQuery()calls to createNamedQuery().

A very simple display bean containing only the product name and code is needed just to pass the information to the storefront.

public interface ProductDisplayBean {

     public String getCode();

     public void setCode(String code);

     public String getDisplayName();

     public void setDisplayName(String displayName);

}

public class ProductDisplayBeanImpl implements ProductDisplayBean {

     public static final long serialVersionUID = 5000000001L;

     private String code;

     private String displayName;

     public String getCode() {

          return code;

     }

     public void setCode(String code) {

          this.code = code;

     }

     public String getDisplayName() {

          return displayName;

     }

     public void setDisplayName(String displayName) {

          this.displayName = displayName;

     }

}

Caching Frequently Accessed Items

Once you've retrieved a set of items from the catalog, you can easily cache these results at the application layer using either a third party solution, such as Ehcache (http://ehcache.sourceforge.net), or your own caching mechanism.  Elastic Path 6.1 already provides some out-of-the-box caching classes that you can use in your own code.  We normally use our SimpleTimeoutCache class to hold a list of frequently retrieved products that don't need to be updated very often. The following code sample shows how you could integrate SimpleTimeoutCache with your product retrieval service class.

private static final long CACHE_TIMEOUT = 30000;  // cache expires after 30 seconds

private final SimpleTimeoutCache<String, List<Product>> productsCache = new SimpleTimeoutCache<String, List<Product>>(CACHE_TIMEOUT);

...

String storeCode = shoppingCart.getStore().getCode();

List<StoreProduct> products = productsCache.get(storeCode);

// if the store code does not exist in the cache, then get it from the database

// and update the cache

if (products == null) {

     final IndexSearchResult productResults = retrieveProducts(browsingRequest, category);

     List<Long> productUids = getProductsUsingPageNumber(pageNumber, storeCode, productResults);

     products = getProductRetrieveStrategy().retrieveProducts(productUids,

     shoppingCart, productLoadTuner);

     productsCache.put(storeCode, products);

}

The timeout value can be adjusted for your requirements. A larger value will reduce the number of trips to the database but the trade-off is a longer wait time for catalog product changes to appear.

Elastic Path 6.1 provides a new way to manage your application configuration. In the past, settings were stored in a variety of places throughout the system; some were in XML files, others were in properties files, and still others were stored in different tables in the database. Now, the most commonly used settings are stored in the database and can be configured using the Commerce Manager.

Custom settings can be migrated to use this framework with little code change and would be able to take advantage of Elastic Path 6.1 multi-store setting management features.

Setting Definitions

Setting definitions are stored in the database, in the TSETTINGDEFINITION table. A setting definition contains:

• a path
• a default value
• a value type
• the maximum number of overrides

The path is a unique identifier for the setting. For example COMMERCE/STORE/giftcertificatesEnabled path refers to the setting that sets whether gift certificates are enabled for a given store.

The default value is the fallback to use when a specific value hasn't been assigned to the setting.

The value type indicates the type of value stored in the setting (a string, a number, a URL, etc.).

The maximum number of overrides specifies the number of values that can be assigned to a setting. Some settings can have multiple values assigned to them. Others can only have one. For example, a store-specific setting would need to have different values for different stores, so the number of overrides would be very large or unlimited (-1). For a system-wide setting, there is generally only one override.

Setting Values

The TSETTINGVALUE table contains the values that have been assigned to settings. Each value has a context. For system settings, the context is empty. For store-specific values, the context contains a store code.

Setting Metadata

Setting metadata is stored in the TSETTINGMETADATA table. It can be used to store additional configuration information relating to the setting. A setting can have any number of metadata associated with it.

Setting metadata consists of a set of key/value pairs. In Elastic Path 6.1, there are two metadata key/value pairs that can be assigned to settings:

• RefreshStrategy, which defines the setting's refresh strategy

• availableToMarketing, which indicates whether the setting is displayed in the store Marketing tab.

Setting Refresh Strategies

The setting refresh strategies metadata provides fine-grained control over when setting changes are applied to the system. In the past, most setting changes required restarting the server. Now, when the setting change is applied depends on how you've configured its refresh strategy. Out of the box, Elastic Path 6.1 provides four refresh strategies:

• application: settings that use the application refresh strategy are only applied after the application is restarted

• session: settings that use the session refresh strategy do not change during the lifetime of the user's session. If an administrator changes the setting value, users will not see the change until their session cookie is removed or expires and a new session is created

• interval: settings that use the interval refresh strategy are cached for a specified period of time. Users do not see changes until the cache expires

• immediate: settings that use the immediate refresh strategy are updated immediately. As soon as the administrator changes a setting that uses this strategy, the change is applied everywhere

Creating a Setting Definition

New setting definitions can be added by executing SQL insert statements. For example, the following creates a definition identified by the path CUSTOM/WIDGETS/widgetType:

INSERT INTO TSETTINGDEFINITION(UIDPK, PATH, DEFAULT_VALUE, VALUE_TYPE, MAX_OVERRIDE_VALUES)
VALUES(10001, "CUSTOM/WIDGETS/widgetType", "flat", "String", 0, 1);

For your custom settings, you should use a naming convention, to avoid conflicts with other settings. Do not use the COMMERCE path prefix, which is reserved for Elastic Path settings.

Adding a Value for the Setting

Setting values can be added by using the Commerce Manager client application. For more information, see the Commerce Manager User Manual.

You can also use SQL insert statements. Remember that you don't need to create a setting value if there is a default value that comes from the setting definition.

Assuming you do want to override the setting definition for a specific context (store code for stores), you can create the value with the following SQL:

INSERT INTO TSETTINGVALUE(UIDPK, SETTING_DEFINITION_UID, CONTEXT, CONTEXT_VALUE)
 VALUES(1011, 10001, "SLRWORLD", "circle")

Note the value of SETTING_DEFINITION_UID should match the UIDPK for the related row in TSETTINGDEFINITION.

Note that multiple setting values can be defined on a setting definition if the setting definition's max_override_values field is set to -1.

Settings Framework Services

Elastic Path 6.1 provides a low level SettingsService class to manage and retrieve of setting definitions and values. To get a setting value for a system level setting with no context:

SettingsService settingsService = getElasticPath()
   getBean(ContextIdNames.SETTINGS_SERVICE);
final int numOfMinutes = Integer.valueOf(settingsService.getSettingValue(
   "COMMERCE/APPSPECIFIC/RCP/idleTimeForLock").getValue());

To get a setting value for a particular context:

settingsService.getSettingValue(path, store.getCode()).getValue();

If there is no setting value in TSETTINGVALUE for the specified context, the system will fall back to the default value in TSETTINGDEFINITION. For example, the default value of the COMMERCE/STORE/giftcertificatesEnabled setting is false, but a setting value for the context SNAPITUP is true, so the Snap It Up store has gift certificates enabled.

A SettingsReader service interface is provided for read only operations. Additionally, the CachedSettingsReader service wrapper provides caching for settings retrieval, and is the preferred method in the storefront to avoid frequent database calls.

In storefront controllers, the StoreConfig object (available through the RequestHelper) can be used to obtain setting values. If a store specific value is available, it will automatically be retrieved. Otherwise, the default value from the setting definition is returned.  For example:

String pagination = getRequestHelper().getStoreConfig()
 getSetting("COMMERCE/STORE/CATALOG/catalogViewPagination").getValue();
int paginationNumber = NumberUtils.toInt(pagination, DEFAULT_PAGINATION);

Elastic Path 6.1's settings framework should replace usage of configuration files for custom configuration settings, especially where multiple stores are being deployed. Previous code retrieving settings from the ElasticPathImpl object should be migrated to the settings framework and its suite of APIs.

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

• Miscellaneous

• Indexes

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.