Repository Queries in ATG

Repository Query API

•          All repositories have the ability to execute queries

•          Queries can be built and executed using the Repository API.

•          Queries can be represented in a textual form called Repository Query Language (RQL)

•          The basic elements of Repository Query API

1.       atg.repository.QueryBuilder : an interface

2.       atg.repository.QueryOptions : a class

QueryBuilder interface defines the available query operations that repositories support. The QueryBuilder interface enables you to build Query objects that can be passed to the repository. A Query is constructed from QueryExpressions. Each Query relates one or more QueryExpressions and a query operation. Queries can use standard logical query operations such as AND, OR, NOT, EQUALS, GREATER THAN, LESS THAN OR EQUALS, and more complicated query operations such as collection inclusion, and pattern matching.

·         Query Creation Example

–        Given a RepositoryView, we initialize a QueryBuilder for it:

                                QueryBuilder b = view.getQueryBuilder();

–        Next, create a QueryExpression for the gender property and     QueryExpression for the constant female:

                                QueryExpression female = b.createConstantQueryExpression ("female");

–        Create a ComparisonQuery that incorporates the gender and femaleQueryExpressions:

Query femaleQuery = b.createComparisonQuery(gender, female,

                                QueryBuilder.EQUALS);

–        Pass the resulting Query to the RepositoryView for execution:

items = view.executeQuery(femaleQuery);

•          The QueryOptions class to specify the ways it can be modified.

–        RepositoryItem[] executeQuery(Query equerry, QueryOptions pQueryOptions);



SAMPLE QUERY BUILDER CODE:

MutableRepository pRepository = (MutableRepository)ServletUtil.getCurrentRequest().resolveName("/atg/userprofiling/ProfileAdapterRepository"); 
RepositoryItemDescriptor userDesc = pRepository.getItemDescriptor("user"); 
RepositoryView userView = userDesc.getRepositoryView(); 
QueryBuilder userBuilder = userView.getQueryBuilder(); 
QueryExpression userType = userBuilder.createPropertyQueryExpression("userType"); 
QueryExpression two = userBuilder.createConstantQueryExpression(new Integer(2)); 
Query userTypeIsTwo = userBuilder.createComparisonQuery(userType, two, QueryBuilder.EQUALS); 
RepositoryItem[] answer = userView.executeQuery(userTypeIsTwo); 
System.out.println("running query: userType = 2"); 
if (answer == null) 
  { 
 System.out.println("no items were found"); 
  } 
else 
  { 
 for (int i=0; i<answer.length; i++) 
   System.out.println("id: " + answer[i].getRepositoryId()); 
  } 
userType < 2 AND login STARTS WITH "j"

Repository Cache Modes in ATG Commerce

Efficient database access is important to many ATG applications. You should design an application so it requires minimal access to the data base and ensures data integrity. An intelligent caching strategy is central to achieving these goals.

Item Caches & Query Caches

For each item descriptor, an SQL repository generally maintains two caches:

·         Item Caches

·         Query Caches

Item Caches

Item caches hold the values of repository items, indexed by repository IDs. An item cache entry is invalidated when that item is updated. The scope of an entry’s invalidation depends on its caching mode.

Query Caches

Query caches hold the repository IDs of items that match given queries. When a query returns repository items whose item descriptor enables query caching, the result set is cached as follows:

·  The query cache stores the repository IDs.

·  The item cache stores the corresponding repository items.

A query cache entry can be invalidated for two reasons:

· A cached item property that was specified in the original query is modified.

· Items of a queried item type are added to or removed from the repository.

If query caching is enabled, the first time this query is issued, the result set is retrieved from the database and stored in the query cache. Then, the next time this same query is issued, the SQL repository can retrieve the result set from the cache, rather than needing to access the database.

Cache Modes

The SQL repository implements several different modes of caching. Which mode is to be chosen depends on the nature of your Dynamo application. We can set the default caching mode on each item descriptor in a repository. Each property’s definition can override the cache mode of its item-descriptor.

The caching modes implemented in the SQL repository are:

•          No Caching (disabled)

When caching is disabled for an item, its property values are cached only during the current transaction, and only if the transaction requires one or more of that item’s properties. This ensures a consistent view of item data while the transaction is in progress. Thus, multiple calls to getPropertyValue() for the same property within the same transaction require only one database query. Cached item properties are reloaded from the datastore for each transaction.

•          Inherited

You can set a property to inherit the default caching mode by setting its cache-mode attribute to inherit. This setting can be useful when a property’s caching mode is set to disabled at one point in the configuration path and you want to reset the property to the default caching mode at a later point.

•          Simple Caching (caching is handled separately in each VM, with no invalidation events to synchronize updates from different server instances).

A server obtains changes to an item’s persistent state only after the cached entry for that item is invalidated. This mode is suitable for read-only repositories such as product catalogs, where changes are confined to a staging server, and for architectures where only one server handles a given repository item type.

•          Locked Caching (read and write locks are used to synchronize the caches)

A multi-server application might require locked caching, where only one ATG instance at a time has write access to the cached data of a given item type. You can use locked caching to prevent multiple servers from trying to update the same item simultaneously.

Prerequisites

Locked caching has the following prerequisites:

·   Item descriptors that specify locked caching must disable query caching by setting their query-cache-size attribute to 0.

· A repository with item descriptors that use locked caching must be configured to use a ClientLockManager component; otherwise, caching is disabled for those item descriptors. The repository’s lockManager property is set to a component of type atg.service.lockmanager.ClientLockManager

· At least one ClientLockManager on each ATG instance where repositories participate in locked caching must be configured to use  ServerLockManager.

· A ServerLockManager component must be configured to manage the locks among participating ATG instances.

•          Distributed Caching (caching with cache invalidation events) types of distributed caching are

a.       Distributed TCP caching (uses TCP to broadcast cache invalidation events to all servers )

b.      Distributed JMS caching (uses JMS to broadcast cache invalidation events to all servers )

c.    Distributed hybrid caching (uses Both)

Distributed TCP

                The following requirements apply:

                                Real-time access to item changes

                                Infrequent updates to cached items

                                Modest number of item caches to monitor

Distributed JMS

                The following requirements apply:

                                Reliable delivery of invalidation messages

                                Infrequent updates to cached items

                                Large number of item caches to monitor

Distributed hybrid

                The following requirements apply:

                                Real-time access to item changes

                                Large number of cached items to monitor across many clients

                                Infrequent updates to cached items

By default, the SQL repository uses simple caching. To enable a different cache mode for an item descriptor, set the cache-mode attribute in the <item-descriptor> tag of the repository definition file:

                <item-descriptor name="profile" cache-mode="locked">

Display Cart Contents in ATG

 First display errors if any as shown below:

<dsp:droplet name="Switch">
 <dsp:param bean="CartModifierFormHandler.formError" name="value"/>
 <dsp:oparam name="true">
  <UL>
   <dsp:droplet name="ErrorMessageForEach">
    <dsp:param bean="CartModifierFormHandler.formExceptions" name="exceptions"/>
    <dsp:oparam name="output">
     <LI>
     <dsp:valueof param="message"/>
    </dsp:oparam>
   </dsp:droplet>
  </UL>
 </dsp:oparam>
</dsp:droplet>

If there are any manipulations allowed on cart content, use the below code for pricing related changes to be replicated:

 <dsp:droplet name="RepriceOrderDroplet">
     <dsp:param name="pricingOp" value="ORDER_SUBTOTAL"/>
   </dsp:droplet>
     <dsp:droplet name="RepriceOrderDroplet">
     <dsp:param name="pricingOp" value="ORDER_TOTAL"/>
   </dsp:droplet> 

Display commerce Items in jsp as shown below:

<dsp:getvalueof var="commerceItemCount" bean="ShoppingCart.current.CommerceItemCount"/> 
<c:choose>
 <c:when test='${commerceItemCount < 1}'>
  No product is added to your cart. Please click on "Continue Shopping" button.
 </c:when>
 <c:otherwise>
  <dsp:droplet name="/atg/dynamo/droplet/ForEach">
   <dsp:param name="array" bean="ShoppingCart.current.commerceItems"/>
   <dsp:setvalue param="commerceItem" paramvalue="element" />
   <dsp:oparam name="output">
    SkuID:<dsp:valueof param="commerceItem.catalogRefId" />
    <c:set var="commerceItemId"><dsp:valueof param="commerceItem.id"/></c:set>
    <c:set var="commerceItemQuantity"><dsp:valueof param="commerceItem.quantity"/></c:set>
    Price:</b><br><dsp:valueof converter="currency" param="commerceItem.priceInfo.listPrice">no price</dsp:valueof>
    ToatalPrice:</b><br><dsp:valueof converter="currency" param="commerceItem.priceInfo.amount">no price</dsp:valueof>
   </dsp:oparam> 
  </dsp:droplet>
 </c:otherwise>
</c:choose>

sample Code for Add single Item to Cart and multiple Items to cart in ATG

Adding Single Item to cart (Simple Way):

<dsp:form method="post">
 <input type="hidden" name="group" value="${group}"/>
 <dsp:input type="hidden" paramvalue="sku.id" bean="CartModifierFormHandler.catalogRefIds"></dsp:input>
 <dsp:input  type="text" value="${Product.id}" bean="CartModifierFormHandler.productId"></dsp:input>
 <dsp:input  type="hidden" value="${MySite}" bean="CartModifierFormHandler.siteId"></dsp:input>
 Quantity:<dsp:input  type="text" value="1" size="4" bean="CartModifierFormHandler.quantity"></dsp:input>
 <dsp:input type="hidden" value="cart.jsp" bean="CartModifierFormHandler.addItemToOrderSuccessURL"></dsp:input> 
 <dsp:input type="hidden" value="productDetailsPage.jsp" bean="CartModifierFormHandler.addItemToOrderErrorURL"></dsp:input>
 <dsp:input type="submit" bean="CartModifierFormHandler.addItemToOrder" value="Add Item TO Cart"></dsp:input> 
</dsp:form>

Adding Single item to cart (using Items property of cart form handler) :

<c:set var="successParams">/walletshop/productDisplay.jsp?categoryId=<dsp:valueof param="categoryId"/></c:set>
<dsp:form id="addSku" action="/walletshop/productDisplay.jsp">
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.addItemCount" value="1" type="hidden"/>
 <dspel:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.addItemToOrderSuccessURL" value="${successParams}" type="hidden"/>
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.addItemToOrderErrorURL" value="/walletshop/errors.jsp" type="hidden"/>
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.items[0].productId" paramvalue="childProduct.id" type="hidden"/>
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.items[0].quantity" type="hidden" value="1" />
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.items[0].catalogRefId" paramvalue="childSKU.id" type="hidden" />
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.pageFlow" value="true" type="hidden" />
 <dsp:input bean="/atg/commerce/order/purchase/CartModifierFormHandler.addItemToOrder" value="add" type="submit"  value="Add to Cart"/>  
</dsp:form>

Adding Multiple Items to cart:

<dsp:importbean bean="/atg/commerce/order/purchase/CartModifierFormHandler"/>
<dsp:importbean bean="/atg/dynamo/droplet/ForEach"/>
<dsp:form action="display_product.jsp" method="post">
 <input name="id" type="hidden" value='<dsp:valueof param="product.repositoryId"/>'>
 <dsp:input bean="CartModifierFormHandler.addItemToOrderSuccessURL" type="hidden" value="shoppingcart.jsp"/>
 <table border=1>
  <tr>
   <td>SKU</td>
   <td>Quantity</td>
  </tr>
  <dsp:droplet name="ForEach">
   <dsp:param name="array" param="product.childSKUs"/>
   <dsp:param name="elementName" value="sku"/>
   <dsp:param name="indexName" value="skuIndex"/>
   <dsp:oparam name="outputStart">
    <dsp:input bean="CartModifierFormHandler.addItemCount" paramvalue="size" type="hidden"/>
   </dsp:oparam>
   <dsp:oparam name="output">
    <tr>
     <td><dsp:valueof param="sku.displayName"/></td>
     <td>
      <dsp:input bean="CartModifierFormHandler.items[param:skuIndex].quantity" size="4" type="text" value="0"/>
      <dsp:input bean="CartModifierFormHandler.items[param:skuIndex].catalogRefId" paramvalue="sku.repositoryId" type="hidden"/>
      <dsp:input bean="CartModifierFormHandler.items[param:skuIndex].productId" paramvalue="product.repositoryId" type="hidden"/>
     </td>
    </tr>
   </dsp:oparam>
  </dsp:droplet>
 </table>
 <BR>
 <dsp:input bean="CartModifierFormHandler.addItemToOrder" type="submit" value="Add To Cart"/>
</dsp:form> 

DSP Taglib usage and anatomy of JSP in ATG

A JSP must import the ATG platform’s DSP tag library so it access Nucleus components. The following page directive specifies the DSP tag library location and the dsp prefix to use for its tags:

<%@ taglib uri="http://www.atg.com/taglibs/daf/dspjspTaglib1_0" 

prefix="dsp"%>

Below the DSP tag libraries import statements, <dsp:page> tags enclose the remaining document. These tags facilitate ATG page processing and must be included in each pag.

There are two ways to deploy a tag library. By default, the DSP tag libraries tld files are kept in the tag library JAR file inside WEBINF/lib. The URI used in JSP page directives are defined in the tld itself.

The URI in the page directive must match the URI that is specified in either the tag library’s tld file (as is the case with the DSP tag libraries) or web.xml. In order to define a different URI for the DSP tag libraries, specify the new URI and tld file location in web.xml. Then, the JSPs can use either the default or the new URI.

Anatomy of a JSP Looks Like:

 

<%@ taglib uri="http://www.atg.com/taglibs/daf/dspjspTaglib1_0" prefix="dsp" %>

<dsp:page>

<html>
<head>
  <title>A Simple Test Page</title>
</head>

<body bgcolor="#ffffff">
  <h1>A Simple Test Page</h1>
<<can use dsp: tags through out the JSP page>>
</body>
</html>

</dsp:page>

SQL Repository Data Models

SQL Repository Data Models in ATG

•          The ATG Dynamo SQL repository can be used to connect ATG applications to a SQL database

•          The SQL repository is implemented through the atg.adapter.gsa package. (GSA stands for "Generic SQL Adapter")

•          The main Dynamo component in the SQL repository is an instance of the atg.adapter.gsa.GSARepository class, which implements the interfaces atg.repository.MutableRepository and atg.repository.content.ContentRepository and which extends the class atg.repository.RepositoryImpl

•          Whenever you need to create a new SQL repository instance, you should instantiate the atg.adapter.gsa.GSARepository class

Repository setup steps

Step1: Create a Repository Definition File

•          This template is an XML file that defines repository item descriptors and their attributes, and describes the relationship of your SQL repository to the SQL database.  While the SQL repository can represent a variety of data models, it cannot easily represent any arbitrary data model. Thus, it is usually a good idea to design the SQL repository schema before you design your SQL database schema.

Step2: Create a SQL Repository Component

•          This component’s “definitionFiles” property points to the repository definition file. 

Step3: Set up Database Tables

•          Can add tables in the same table space as ATG Platform tables or different

•          ATG provides a tool to create database tables based on repositories:

startSQLRepository –outputSQL

Data Types in Definition file of ATG, SQL data type  and Java object mappings  are shown in below table:

data-type Attribute Value	Java Object Type	Recommended SQL Data Type
string	String	VARCHAR VARCHAR or CLOB (Oracle)
big string	String	LONG VARCHAR or CLOB
date	java.util.Date	DATETIME (Sybase or MS) DATE (DB2 or Oracle)
timestamp	java.sql.Timestamp	DATETIME (Sybase or MS) DATE (Oracle 8i) TIMESTAMP (DB2 or Oracle 9i)
enumerated	String	INTEGER
boolean	Boolean	NUMERIC(1)
int	Integer	INTEGER
byte	Byte	INTEGER
binary	byte[]	LONG VARBINARY (Sybase or MS) LONG RAW or BLOB (Oracle) BLOB (DB2)
short	Short	INTEGER SMALLINT (DB2)
float	Float	FLOAT (DB2, Sybase or MS) NUMBER (Oracle)
double	Double	DOUBLE (DB2, Sybase or MS) NUMBER (Oracle)
long	Long	NUMERIC(19), BIGINT (DB2)

Table Types in ATG Repositories:

An item descriptor in an SQL repository defines a repository item type. It specifies repository item properties and the database tables and columns that store the data of those properties. Item descriptor can have different types of tables, they are:

                 1.    Primary

                 2.    Auxiliary

                 3.    Multi

 

Primary Table:

•          Each item descriptor must have one and only one primary table.

•          The primary table is specified using the type="primary" XML attribute in a <table> tag.

•          The primary table must define a column or columns that store the repository ID.

•          This column is specified using the id-column-names attribute.

                        <table name="user" type="primary" id-column-name="id">

properties...

</table>

•          Use “id-column-names” if the primary keys are more than one.

Auxiliary Table:

•          To specify a relationship of  one to one, we will use auxiliary tables. The Auxiliary table is specified using the type="auxiliary" XML attribute in a <table> tag.

•          Example: Each user has a single address. For the purposes of this example, the user information is stored in a separate table from the user’s address information.

•          The columns in the id-column-names attribute of auxiliary table must be listed in the same order as they are in the id-column-names attribute of the primary table.

         <item-descriptor name="user">

                <table name="dps_user" type="primary" id-column-names="id">

                       <property name="login" data-type="string"/>

                </table>

                <table name="dps_address"  type="auxiliary"  id-column-names="id">

                       <property name="address1"/>

                       <property name="city"/>

                       <property name="state"/>

                       <property name="zip"/>

                </table>

         </item-descriptor>

Multi Table:

•          To specify a relationship of many to one, many to many we will use auxiliary tables.

•          The Multi table is specified using the type="multi" XML attribute in a <table> tag.

•          The multi-column-name attribute ensures that the ordering of the multi-values are maintained. The column specified by the multi-column-name attribute is used for multi-valued properties of data type array, map, and list and is not used for sets (which are unordered). For map type properties, the values in the column specified by the multi-column-name attribute must be a string. For list or array type properties, these values should be an integer or numeric type, and must be sequential.

•          As with auxiliary tables, the ordering of the ID column names is important. The columns specified by this attribute must list table columns in the same order as the id-column-names attribute of the primary table.

•          The <property> tag for a multi-valued property sets the following attributes:

data-type is set to One of the following: array,set,map,list

For example:

<property ... data-type="array" ..

component-data-type is set to A primitive data type such as int, string, etc..

SQL repository does not support references to binary types

For example:

<property name="interests" column-name="interest" data-type="array" component-data-type="string"/>

component-item-type is set to The item descriptor name of the referenced repository items

For example:

<property name="..." column-name="designers"   data-type="array"

 component-item-type="user"/>

By default, null values are not allowed in multi-valued properties. You can specify to allow null values at two levels:

•          Enable all multi-valued properties in a repository to accept null values by setting the repository property allowNullValues to true

•          Allow null values for an individual property by setting its <property> tag attribute allowNullValues to true

In general, auxiliary and multi tables should not have REFERENCES constraints that point to each other. Instead, each of these tables can have a REFERENCES constraint that points to the primary table for the repository item. This limitation exists because the SQL repository processes insert and delete statements for auxiliary and multi tables in the same order. As a result, if you specify REFERENCES constraints between an auxiliary and a multi table or vice versa, a constraint error results on the insert or the delete.

Best Practices

•Define primary key of table as String or Long

•Avoid using multiple primary keys to a table (Not more than 1)

•Incase we need to have composite primary key which has more than 1 key, Add a new id column as part of the primary key

•Identify strong and week relationships and represent accordingly

•Normalize the data model and introduce redundancy where ever is required

•Highly normalized data model kills the performance