| Oracle® Retail POS Suite Implementation Guide, Volume 1 – Implementation Solutions Release 14.1 E54475-02 |
|
 Previous |
 Next |
| Oracle® Retail POS Suite Implementation Guide, Volume 1 – Implementation Solutions Release 14.1 E54475-02 |
|
Previous |
Next |
This chapter describes the database used with Point-of-Service and how to interface with it, including:
Updating tables
Rebuilding the database
Creating new tables
Updating flat file configurations
The chapter includes an example of writing code to store new data in the database.
The Point-of-Service system uses an Association of Retail Technology Standards (ARTS)-compliant database to store transactions and settings. The ARTS standard (see /www.nrf-arts.org/http:/) is a key element in maintaining compatibility with other hardware and software systems.
Although the Point-of-Service system complies with the ARTS guidelines, it does not implement the entire standard, and contains some tables which are not specified by ARTS. For example, ARTS tables for store equipment and recipe are not included, while tables for tender types and reporting have been added.
The ARTSDatabaseIfc.java file defines the mapping of ARTS names to constants in application code.
Figure 3-1 shows how Data Managers and Data Technicians handle communication with the database in the Point-of-Service application.
The Point-of-Service system uses the following components to write to the database:
The Data Manager's primary responsibilities are to provide an API to the application code and to contact the Data Technician and pass it data store requests.
There is one Data Manager per client. The Data Manager manages connections to multiple Data Technicians, for example, there is a Data Technician residing on the client that retrieves data from the offline (Derby) database, and there is also a Data Technician residing on the store server that manages access to the store database. The Data Manager on the client is configured to determine which Data Technician provides which data service.
The Data Manager Configuration Script is an XML file that specifies the properties of the Data Manager.
The Data Technician handles the database connection. Configure the Data Technician with an XML script. The Data Transaction class is the valet from the manager to the technician. The Data Transaction class has the add, find, and update methods to the database. Typically, there is one Data Technician that communicates with the local database and one that communicates with offline database.
|
Note: Most managers create valets when they need talk to technicians. Data Manager works a little differently: the Data Transaction class calls the Data Manager and passes itself as a valet. The valet finds the data operation class, then the valet knows which technician it is associated with and calls its execute method. |
The Data Technician configuration script is an XML file that specifies the properties of the Data Technician.
The Transaction Queue collects data transactions and guarantees delivery.
Offline Database is the Derby database that is used when the register is offline.
The Local Database is the store database.
This section gives an overview of how Oracle Retail Platform, Data Manager, and Data Technician components work together to store data in the database.
|
Note: The notation TXN refers to a data transaction, which can be any guaranteed transmission of data, not necessarily a sales transaction in the retail sense. |
Oracle Retail Platform is responsible for configuring the system so that the Data Manager, Data Technician, configuration scripts, and conduit scripts work together to provide the mechanism to update, store, and retrieve data from a database.
The client conduit script defines the name and package for the Data Manager and Data Manager configuration script, POSDataManager.xml.
The server conduit script defines the name and package for the Data Technician and Data Technician configuration script, DefaultDataTechnician.xml.
At runtime, the tour code requests a data transaction object from the Data Transaction Factory.
The Data Transaction Factory verifies that the transaction is defined in POSDataManager.xml and the transaction object is returned to the tour code.
The tour code calls a method on the transaction object that creates a vector of data actions. A data action corresponds to a set of SQL commands that are executed as a unit. (Data actions are reused by different transactions.)
The method in the transaction object gets a handle to the Data Manager and calls execute(), sending itself as a parameter. This instructs the Data Manager to send the Transaction object (a valet) across the network to the Data Technician.
|
Note: Most Manager/Technician pairs work differently. The standard pattern is for the tour code to get a handle to the Manager, then call a method on the manager that creates the valet object and sends it to the technician. For the Data Manager/Technician pair, the transaction object (the valet class), gets the handle to the Data Manager. The tour code is only responsible for getting a transaction object from the factory and calling the appropriate method. |
On the server side, the Data Technician configuration script, DefaultDataTechnician.xml, lists all available transactions. It also defines an operation class for each data action. Each data action is then processed by the appropriate data operation class.
|
Note:
See the DefaultDataTechnician.xml file. This file contains the following element at the end of the file:
<EXCEPTIONHANDLER class="SQLExceptionHandler"
package="oracle.retail.stores.domain.manager.data"/>
See the DataManager.xml file. This file contains the following element at the end of the file:
<QUEUE name="TransactionQueue"
encryptBuffer="true"
class="DataTransactionFileQueue"
package="oracle.retail.stores.foundation.manager.data">
<EXCEPTIONHANDLER class="TransactionQueueSQLExceptionHandler"
package="oracle.retail.stores.domain.manager.data"/>
</QUEUE>
The exception handling classes are implemented as plug points. |
Use this procedure when creating a new database table or updating an existing one. Refer to the ARTS standards when designing tables.
|
Note: When you add or change a table, you need to rebuild the database for your local copy of Point-of-Service before you can test your changes. See Step 6. |
Edit the appropriate database script, or write a new one.
Database scripts can be found in the source directory <source_directory>\modules\common\deploy\server\common\db\sql.
Start a new file (or edit the appropriate existing file) in the db/sql source directory file to store SQL commands for creating the new table. Example 3-1 shows the SQL commands for creating the table that stores the credit card data.
Example 3-1 CreateTableCreditDebitCardTenderLineItem.sql
DROP TABLE TR_LTM_CRDB_CRD_TN;
CREATE TABLE TR_LTM_CRDB_CRD_TN
(
ID_STR_RT char(5) NOT NULL,
ID_WS char(3) NOT NULL,
DC_DY_BSN char(10) NOT NULL,
AI_TRN integer NOT NULL,
AI_LN_ITM smallint NOT NULL,
TY_TND varchar(20),
ID_ISSR_TND_MD varchar(20),
TY_CRD VARCHAR(40),
...additional column lines omitted here...
);
ALTER TABLE TR_LTM_CRDB_CRD_TN ADD PRIMARY KEY (ID_STR_RT, ID_WS, DC_DY_BSN, AI_TRN, AI_LN_ITM);
COMMENT ON TABLE TR_LTM_CRDB_CRD_TN IS 'Credit/Debit Card Tender Line Item';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.ID_STR_RT IS 'Retail Store ID';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.ID_WS IS 'Workstation ID';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.DC_DY_BSN IS 'Business Day Date';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.AI_TRN IS 'Transaction Sequence Number';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.AI_LN_ITM IS 'Retail Transaction Line Item Sequence Number';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.ID_ISSR_TND_MD IS 'Tender Media Issuer ID';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.TY_TND IS 'TenderTypeCode';
COMMENT ON COLUMN TR_LTM_CRDB_CRD_TN.TY_CRD IS 'Card Type';
...additional comment lines omitted...
Create or edit the insert files (also in the db/sql source directory) for inserting initial data into the new database table.
This step is used only to insert data into the database table for purposes of initially logging on, testing, and so on.
Make updates to foreign keys in CreateForeignKeys.sql.
If you are creating a new table, add a string constant to the <source_directory>\modules\common\src\oracle\retail\stores\persistence\utility\ARTSDatabaseIfc.java file. Use a string constant with a meaningful name to store the official ARTS name of the database table.
Example 3-2 shows two examples of meaningful String constants found in ARTSDatabaseIfc.java.
Check foreign key constraints.
For performance reasons, the database build scripts do not turn on foreign key constraints until late. If you make inserts which break foreign key constraints, you are not notified. To check this, test all inserts with foreign key constraints in place, by editing the appropriate database build script.
Open a command prompt in the Point-of-Service installer directory and use the following command-lines:
To reset the store database: install.cmd ant install-database
To reset the scratchpad database: install.cmd ant install-scratchpad
To reset both: install.cmd ant install-database install-scratchpad
The install-database command uses the settings in the ant.install.properties file, so the dataset specified by the input.install.database property is loaded. The values can be:
no - no action taken
schema – only install the schema, no data
minimum – schema and minimum required data
sample – schema, minimum, and sample data
To reset the scratchpad database, the ant.install.properties file needs to have the scratchpad database information as well as input.install.scratchpad.database set to true.
After you verify that the table builds successfully and the code referencing the table works, check your updates into source control.
This section describes how to save data to the database, using till close information as an example.
To plan your database code, refer to functional requirements documents to determine what data must be stored.
Next, review the ARTS database standards for tables and columns. Determine whether you need to create a new table. If you need to create a table defined by ARTS but not currently used in the Store database, follow the ARTS standard. For instructions on creating a new table, see .
For the till transaction, there are several tables that need to be addressed: the tender line item table and the credit/debit card transaction table.
Table 3-1 lists database tables used in a credit card tender option.
Table 3-1 Database Tables Used in Credit Card Tender Option
| ARTS Table Name | Description |
|---|---|
|
TR_TL_OPN_CL |
till open close transaction table |
|
TR_CTL |
control transaction table |
|
LE_HST_STR_SF_TND |
Store Safe Tender History table |
|
AS_TL |
the Till table |
|
LE_HST_TL |
till history |
|
LE_HST_WS |
workstation history |
|
LE_HST_STR |
store history |
To save data to the database from a site:
Create and populate the domain object to be saved.
Save the data to the cargo's transaction.
For the Till Close option, the TillCloseCargo contains the tillID of the till to close.
In Example 3-3, from <source_directory>\applications\pos\src\oracle\retail\stores\pos\services\dailyoperations\till\tillclose\UpdateStatusSite.java, Till is a domain object that stores the till data such as the expected amount and entered amount, and so on. In the following code, the till object is retrieved from the register object (stored in cargo) based on the till id updated and added to the TillOpenCloseTransaction line item.
Example 3-3 UpdateStatusSite.java: Transaction Object
public void arrive(BusIfc bus)
{
TillCloseCargo cargo = (TillCloseCargo) bus.getCargo();
// Local references to register and till.
RegisterIfc register = cargo.getRegister();
TillIfc till = register.getTillByID(cargo.getTillID());
// create close till transaction
TillOpenCloseTransactionIfc transaction =
DomainGateway.getFactory().getTillOpenCloseTransactionInstance();
//save current register accountability in the register
till.setRegisterAccountability(register.getAccountability());
//add the till to the transaction
transaction.setTill(till);
...
// Add the credit line item to the transaction
trans.addTender(credit);
...
}
Call a method to save the transaction object.
After the till object is added to the TillOpenCloseTransactionIfc transaction, the collected data is saved to the database. In Example 3-4, the <source_directory>\applications\pos\src\oracle\retail\stores\pos\services\dailyoperations\till\tillclose\UpdateStatusSite.java file uses the Utility Manager to call the saveTransaction() method.
The Data Manager and Data Technician work together to provide access to the database from the application. The developer rarely modifies these. Typically, the site code and JDBC code are updated. To identify which JDBC class should be used, trace through the site code until the DataAction sets the operation name.
The following descriptions explain the labels in the figure.
UpdateStatusSite uses the Utility Manager to call the saveTransaction() method as shown in Example 3-4. The utility.saveTransaction() method uses the data transaction class TransactionWriteDataTransaction to save the till transaction.
The following code samples show details for the previous figure.
Example 3-5 UtilityManager.java: Save Data Transaction
TransactionWriteDataTransaction dbTrans = new TransactionWriteDataTransaction(tranName); dbTrans.saveTransaction(trans, totals, till, register);
Example 3-6 TransactionWriteDataTransaction.java: Save Transaction
public void saveTransaction(TransactionIfc transaction,
FinancialTotalsIfc totals,
TillIfc till,
RegisterIfc register)
throws DataException
{
...
int transactionType = transaction.getTransactionType();
...
switch(transactionType)
{ // begin add actions based on type
...
case TransactionIfc.TYPE_OPEN_TILL:
addSaveTillOpenTransactionActions(transaction);
break;
case TransactionIfc.TYPE_CLOSE_TILL:
addSaveTillCloseTransactionActions(transaction);
break;
...
}
The <source_directory>\applications\pos\deploy\server\config\technician\DefaultDataTechnician.xml file is the configuration file for the Data Technician and is used to configure the links between the application and the JDBC class that performs the work. All Data Transaction classes must be defined in this file, including TransactionWriteDataTransaction.
The TransactionWriteDataTransaction class instantiates the DataAction object and sets the data operation name to UpdateTillStatus and so on. Other data actions occurred before these till data actions. Data Actions are added in the specific order in which they should occur.
Example 3-8 TransactionWriteDataTransaction: DataAction
protected void addSaveTillCloseTransactionActions(TransactionIfc transaction)
// save the control transaction
DataActionIfc dataAction = createDataAction(artsTransaction,
"SaveControlTransaction");
actionVector.addElement(dataAction);
// this ensures that the change is backward compatible, because
// only if till open-close transaction is used, will the new data operations
// be executed
if (transaction instanceof TillOpenCloseTransactionIfc)
{
TillOpenCloseTransactionIfc tocTransaction =
(TillOpenCloseTransactionIfc) transaction;
// save the till open/close transaction
dataAction = createDataAction(transaction,
"SaveTillOpenCloseTransaction");
actionVector.addElement(dataAction);
// build ARTS till for other operations
TillIfc till =
((TillOpenCloseTransactionIfc) transaction).getTill();
RegisterIfc register =
((TillOpenCloseTransactionIfc) transaction).getRegister();
if (till.getStatus() == AbstractFinancialEntityIfc.STATUS_RECONCILED)
{
// update the safe as needed
dataAction = createDataAction(transaction,
"UpdateSafeFromTillOpenCloseTransaction");
actionVector.addElement(dataAction);
// Get deep copies of the till and register so they can be loaded
// with the till-close totals
TillIfc aTill = (TillIfc) till.clone();
RegisterIfc aRegister = (RegisterIfc) register.clone();
// Combine the till and float totals objects
FinancialTotalsIfc totals =
DomainGateway.getFactory().getFinancialTotalsInstance();
totals.addEndingFloatCount(tocTransaction.getEndingFloatCount());
totals.getCombinedCount().setEntered
(tocTransaction.getEndingCombinedEnteredCount());
// Set the counted totals on the till and register.
aTill.setTotals(totals);
aRegister.setTotals(totals);
ARTSTill artsTill = new ARTSTill(aTill, aRegister);
// creates or updates the till as needed
dataAction = createDataAction(artsTill,
"UpdateTillStatus");
actionVector.addElement(dataAction);
// creates or updates the till totals as needed
dataAction = createDataAction(artsTill,
"UpdateTillTotals");
actionVector.addElement(dataAction);
// add to register totals
dataAction = createDataAction(aRegister,
"AddRegisterTotals");
actionVector.addElement(dataAction);
// add to store totals
ARTSStore aStore = new ARTSStore(register.getWorkstation().getStore(),
register.getBusinessDate());
aStore.setFinancialTotals(aRegister.getTotals());
dataAction = createDataAction(aStore,
"AddStoreTotals");
actionVector.addElement(dataAction);
}
else
{
ARTSTill artsTill = new ARTSTill(till, register);
// creates or updates the till as needed
dataAction = createDataAction(artsTill,
"UpdateTillStatus");
actionVector.addElement(dataAction);
}
// update the register and drawer
dataAction = createDataAction(register,
"UpdateRegisterStatus");
actionVector.addElement(dataAction);
// update the drawer
dataAction = createDataAction(register,
"UpdateDrawerStatus");
actionVector.addElement(dataAction);
}
The DefaultDataTechnician uses the data command to list several data operation names. The data operation name UpdateTillStatus points to the name of the JDBC class, which is JdbcUpdateTillStatus.
Example 3-10 DefaultDataTechnician.xml: Define Data Operation Class
<DATATECHNICIAN
package="oracle.retail.stores.domain.arts">
...
<TRANSACTION name="TransactionWriteDataTransaction" command="jdbccommand"/>
...
<COMMAND name="jdbccommand"
class="DataCommand"
package="oracle.retail.stores.foundation.manager.data"
<COMMENT>
This command contains all operations supported on a JDBC
database connection.
</COMMENT>
<POOLREF pool="jdbcpool"/>
...
<OPERATION class="JdbcUpdateTillStatus"
package="oracle.retail.stores.domain.arts"
name="UpdateTillStatus">
<COMMENT>
This operation updates the till status in the database.
</COMMENT>
</OPERATION>
...
</DATATECHNICIAN>
The JdbcUpdateTillStatus class is used to update the till status to the database table. See the next section.
Use this procedure to modify the data operation class to access the database.
Add a save method to the data operation class.
Write an implementation for methods written for the data operation class.
Second, the credit data must be saved to the new database table using SQL factory methods.
Example 3-11 JdbcUpdateTillStatus.java: SQL Factory Methods
public boolean updateTill(JdbcDataConnection dataConnection,
TillIfc till,
RegisterIfc register)
throws DataException
{
boolean returnCode = false;
SQLUpdateStatement sql = new SQLUpdateStatement();
isUpdateStatement = true;
/*
* Define the table
*/
sql.setTable(TABLE_TILL);
/*
* Add columns and their values
*/
sql.addColumn(FIELD_TILL_SIGNON_OPERATOR, makeSafeString(till.getSignOnOperator().getEmployeeID()));
if (till.getSignOffOperator() != null)
{
sql.addColumn(FIELD_TILL_SIGNOFF_OPERATOR, makeSafeString(till.getSignOffOperator().getEmployeeID()));
}
sql.addColumn(FIELD_TILL_STATUS_CODE, getStatusCode(till));
sql.addColumn(FIELD_TILL_STATUS_DATE_TIME_STAMP, dateToSQLTimestampString(new Date()));
sql.addColumn(FIELD_WORKSTATION_ID, getWorkstationID(register));
sql.addColumn(FIELD_TILL_START_DATE_TIMESTAMP, getStartTimestamp(till));
sql.addColumn(FIELD_BUSINESS_DAY_DATE, getBusinessDay(till.getBusinessDate()));
sql.addColumn(FIELD_WORKSTATION_ACCOUNTABILITY, "'" + till.getRegisterAccountability() + "'");
sql.addColumn(FIELD_TILL_TYPE, "'" + till.getTillType() + "'");
/*
* Add Qualifier(s)
*/
sql.addQualifier(FIELD_RETAIL_STORE_ID + " = " + getStoreID(register));
sql.addQualifier(FIELD_TENDER_REPOSITORY_ID + " = " + getTillID(till));
try
{
dataConnection.execute(sql.getSQLString());
}
catch (SQLException se)
{
logger.error( "" + se + "");
throw new DataException(DataException.SQL_ERROR, "Update Till", se);
}
if (0 < dataConnection.getUpdateCount())
{
returnCode = true;
}
return(returnCode);
}
To test the new code:
Run Point-of-Service.
Select the path to the screen.
Enter the data.
Complete the till close.
To verify that the correct data exists in the database table, use a database access program to view the table that should contain the new information. Verify that the data in the database table matches the data entered. The following example shows a sample SQL statement you can use to retrieve the data.
select * from AS_TL;
Point-of-Service uses an ARTS-compliant database. Data is stored and retrieved by entity beans in a bean-managed persistence pattern, so the system makes database calls from the entity bean code.
A single entity bean exists for each database table, and handles reads and writes for that table. Each entity bean contains the necessary methods to create, load, store, and remove its object type.
The Central Office application writes data to the enterprise database, which serves as a repository for information about transactions across the whole enterprise.
The Back Office application writes data to the Store database, a repository for transaction information for a single store.
A data access object (DAO) provides an abstract interface to the underlying database tables. It accesses one or more tables that belong to the same logical unit to read and write information to the database.
Table 3-2 lists related sources that provide specific information about the database for your use when developing code.
Table 3-2 Related Documentation
| Source | Description |
|---|---|
|
ARTS Database Standard |
See |
|
Data Dictionary |
Contains table and column definitions for the database used to store Point-of-Service data. See the docs zip file provided with your Point-of-Service documentation. |
|
Database Diagrams |
Diagrams which show the relationships between various tables in the database schema. See the docs zip file provided with your Point-of-Service documentation. |
As described in Chapter 2, "Oracle Retail POS Suite Technical Architecture" a persistence layer of entity beans represents the database tables to the rest of the system. One bean represents each table.
Figure 3-4 illustrates these relationships.
Each commerce service communicates with one or more entity beans or DAOs, and each entity bean communicates with one database table. A DAO typically communicates with one or more tables that belong to the same logical grouping. Although there are exceptions, in general only one commerce service communicates with an entity bean; other services request the information from the relevant service rather than talking directly to the entity bean. For example, if the Customer Service needs information provided by the Item Bean, it makes a request to the Item Service.
When new code is added or features are added, modified, or extended, database plans should be evaluated to ensure that new data items fit the ARTS schema. Complying with the standards increases the likelihood that extensions can migrate into the product codebase and improves code reuse and interoperability with other applications.
|
Note: Because the ARTS standard continues to evolve, older code may contain deviations from the standard or may be compliant only with an earlier version of the standard. Oracle Retail continues to evaluate ARTS compliance with each release of its software. |
In general, the system uses standard J2EE bean-managed persistence techniques to persist data to the Oracle Retail database. Each of the entity beans that stores data requires JDBC code in standard ejbLoad, ejbStore, ejbCreate, and ejbRemove classes. However, there are some differences worth noting:
All SQL references are handled as constant fields in an interface.
Session and entity beans extend an EnterpriseBeanAdapter class. Special extensions for session and entity beans exist. These contain common code for logging and a reference to the Oracle Retail DBUtils class (which provides facilities for opening and closing data source connections, among other resources).
Example 3-12 ItemPriceDerivationBean.java: ejbStore Method
public void ejbStore() throws EJBException
{
ItemPriceDerivationPK key = (ItemPriceDerivationPK) getEntityContext().getPrimaryKey();
getLogger().debug("store");
PreparedStatement ps = null;
Connection conn = null;
if (isModified())
{
getLogger().debug("isModified");
try
{
conn = getDBUtils().getConnection();
ps = conn.prepareStatement(ItemPriceDerivationSQLIfc.STORE_SQL);
int n = 1;
ps.setBigDecimal(n++,getReductionAmount().toBigDecimal());
ps.setBigDecimal(n++,getDiscountPricePoint().toBigDecimal());
getDBUtils().preparedStatementSetDate(ps, n++, getRecordCreationTimestamp());
ps.setBigDecimal(n++,getReductionPercent().toBigDecimal());
getDBUtils().preparedStatementSetDate(ps, n++, getRecordLastModifiedTimestamp());
ps.setInt(n++, key.getPriceDerivationRuleID());
ps.setString(n++, key.getStoreID());
if (ps.executeUpdate() != 1)
{
throw new EJBException("Error storing (" + getEntityContext().getPrimaryKey() + ")");
}
setModified(false);
}
catch (SQLException ex)
{
getLogger().error(ex);
throw new EJBException(ex);
}
catch (Exception ex)
{
getLogger().error(ex);
throw new EJBException(ex);
}
finally
{
getDBUtils().close(conn, ps, null);
}
}
}
The system uses DAO persistence techniques to persist data to the database.
A DAO (data access object) provides an abstract interface to the underlying database tables. It accesses one or more tables that belong to the same logical unit to read and write information to database
The following is an example of a DAO:
Example 3-13 PluItemDAO
public PluItem getById(String storeId, String posItemId, String itemId, Locale lcl) throws DataException
{
PluItem pluItem = null;
Locale bestLocale = LocaleMap.getBestMatch(lcl);
PreparedStatement ps = null;
ResultSet rs = null;
Connection conn = null;
try
{
logger.debug("LOAD_PLU_ITEM_BY_ID_SQL " + LOAD_PLU_ITEM_BY_ID_SQL);
conn = getDBConnectionManager().getConnection();
ps = conn.prepareStatement(LOAD_PLU_ITEM_BY_ID_SQL);
ps.setString(1, bestLocale.toString());
ps.setString(2, bestLocale.toString());
ps.setString(3, storeId);
ps.setString(4, itemId);
ps.setString(5, posItemId);
rs = ps.executeQuery();
if (rs.next())
{
pluItem = getPluItem(rs, true, bestLocale);
}
}
catch (SQLException e)
{
logger.error(e);
throw new DataException(DataException.SQL_ERROR, "failed to load item. " + ", storeId = " + storeId
+ ", posItemId = " + posItemId + ", itemId = " + itemId + ", locale = " + bestLocale.toString(), e);
}
finally
{
getDBConnectionManager().close(conn, ps, rs);
}
return pluItem;
}
