[top]
[prev]
[next]

Table of Contents
Index
Documentation Top
Global Index
|
Work Unit Examples
Since multiple salespeople can alter the same source data when placing orders or sending leads, you should put the logic that places orders and leads into work units. For ease of deployment, source methods have been put into a SourceLogic class and target methods into a separate TargetLogic class.
The work aliases for this example are created at the source as follows:
CREATE WORK ALIAS placeOrder FOR SourceLogic.makeSaleAtSource, TargetLogic.makeSaleAtTarget
CREATE WORK ALIAS sendLead FOR SourceLogic.sendLeadAtSource, TargetLogic.sendLeadAtTarget
This example also contains a method alias for the getUniversalKeyValue method (described in Designing Universal Keys.) This method alias is created at the source as follows:
CREATE METHOD ALIAS getKey FOR COM.cloudscape.util.KeyGen.getUniversalKeyValue
The placeOrder Work Unit
The placeOrder work unit places an order for a given quantity of a toy.
As mentioned in Step 5: Listing the Inputs, the parameters of the placeOrder work unit are: the order ID (a generated universal key), the sales representative ID, the customer name, the toy name, and the quantity ordered.
As with all work units, the target-side method applies when the work unit is invoked, and the source-side method applies when the target refreshes.
The Target-Side Method
The target-side method first checks that the ordered toy is valid. If it is not, the status is set to TOY UNKNOWN. The transaction is not aborted because the item may have been added at the source since this target's last refresh, so the order could still be filled. The method then checks the number of units available as of the last refresh, and if the order is likely to succeed, sets the status to WILL ORDER. Otherwise, it sets the status to WILL BACKORDER.
The Source-Side Method
The source-side method does more than the target-side method. This is typical because the source usually contains more information than the target.
First, the source-side method checks that the sender is a valid salesperson. If the salesperson is invalid, the refresh is aborted by raising a StopRefreshSQLException. This makes it impossible for the target to refresh again until the source SALESREPS table (which is not published to the target) adds the salesperson's ID. (Production code would of course enforce security in a more sophisticated way; this example illustrates that raising a StopRefreshSQLException is a good way to disable updates from a target while that target's security is questionable.)
The source-side method then checks that the toy exists on the list of items, and sets status to TOY UNAVAILABLE if it does not. The method then checks the number of items in stock, which may differ from the results of the provisional target transaction due to conflicting updates from other targets. The method then assigns a durable value (ORDERED or BACKORDERED) to the STATUS column of the ORDERS table, and this value is copied to the target at the end of the refresh.
Using the placeOrder Work Unit
As an example of using this work unit, the following SQL-J statement places an order from sales representative number 1 for three Beanie Baby Elephants for customer "Tucker":
CALL placeOrder(getKey(),1,'Tucker','beanie baby elephant',3)
The sendLead Work Unit
The sendLead work unit sends a customer name to a specified salesperson.
The parameters of the sendLead work unit are: a universal key (the lead ID), the destination salesperson ID, the sending salesperson ID, and the lead.
The Target-side Method
sendLead is an example of a work unit that acts very differently at the target and at the source. Only leads assigned to a given salesperson are published to that salesperson, and it is assumed that the salesperson sending the lead has no interest in tracking it once she has sent it. Therefore, the target-side method does almost nothing except confirm the transaction. No status on the lead is maintained at the target. If there are problems with the lead, it is assumed the system administrator will handle them.
The Source-side Method
As in the source-side method of placeOrder, the method checks to see that the sender is valid. If she is not, the method aborts the refresh by raising a StopRefreshSQLException, which disables the target from further refreshes until the database administrator updates the SALESREPS table. The method then checks that the destination salesperson ID is valid, and aborts and logs the transaction by raising a skipTransactionSQLException if it is not. It is assumed that the system administrator will check the log for leads that have been sent to invalid salespeople, and will take appropriate action. Finally, it adds the lead to the LEADS table. Only the leads intended for the target issuing the refresh are sent to that target at the end of the refresh.
Using the sendLead Work Unit
As an example of using this work unit, the following SQL-J statement sends the lead "Tucker" from salesperson number 2 to salesperson number 1:
CALL sendLead(getKey(),2,1,'Tucker')
If the system administrator, rather than a sales person, wants to send a lead, he can run sendLead directly at the source. (The source might be salesperson number 0.) If a work unit is invoked at the source, the target-side work unit code never runs; the source database changes are simply sent to the target upon the next refresh.
Work Unit Example Code
This section shows target and source code for the placeOrder and sendLead work units.
The following code is for the target-side work unit methods:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TargetLogic {
public static void makeSaleAtTarget(byte[] orderID, int salesRepID, String customer,
String toy, int quantityOrdered) throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:cloudscape:;current=true");
String status = "";
int quantityAvailable;
PreparedStatement getToysQuantity = conn.prepareStatement(
"SELECT quantity FROM toys WHERE toy = ?");
PreparedStatement updateToysQuantity = conn.prepareStatement(
"UPDATE toys SET quantity=quantity-? WHERE toy = ?");
PreparedStatement insertOrder = conn.prepareStatement(
"INSERT INTO orders VALUES(?,?,?,?,?,?)");
getToysQuantity.setString(1, toy);
ResultSet rs = getToysQuantity.executeQuery();
if (!rs.next()) {
quantityAvailable = 0;
status = "TOY UNKNOWN";
} else {
quantityAvailable = rs.getInt(1);
}
getToysQuantity.close();
rs.close();
if (quantityAvailable >= quantityOrdered) {
updateToysQuantity.setInt(1, quantityOrdered);
updateToysQuantity.setString(2, toy);
updateToysQuantity.executeUpdate();
updateToysQuantity.close();
status = "WILL ORDER";
} else if (status != "TOY UNKNOWN") {
status = "WILL BACKORDER";
}
insertOrder.setBytes(1, orderID);
insertOrder.setInt(2, salesRepID);
insertOrder.setString(3, customer);
insertOrder.setString(4, toy);
insertOrder.setInt(5, quantityOrdered);
insertOrder.setString(6, status);
insertOrder.executeUpdate();
insertOrder.close();
}
public static void sendLeadAtTarget
(byte[] leadID, int sentToRepID, int sentFromRepID, String customer)
throws SQLException {
Connection conn = DriverManager.getConnection("jdbc:cloudscape:;current=true");
System.out.println("Lead " + customer +
" will be sent to sales rep " + sentToRepID +
" at your next refresh.");
}
}
The following is the code for the source-side work unit methods:
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import COM.cloudscape.synchronization.TargetDescription;
import COM.cloudscape.synchronization.SyncFactory;
import COM.cloudscape.synchronization.StopRefreshSQLException;
import COM.cloudscape.synchronization.SkipTransactionSQLException;
public class SourceLogic {
private PreparedStatement getSalesRepId;
private String getSalesRepIdText =
"SELECT salesRepId FROM salesReps WHERE salesRepId = ?";
private PreparedStatement getToysQuantity;
private PreparedStatement updateToysQuantity;
private PreparedStatement insertOrder;
private PreparedStatement insertLead;
private int salesRepIdPubParameter;
public static void makeSaleAtSource(byte[] orderID, int salesRepID, String customer,
String toy, int quantityOrdered) throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:default:connection");
TargetDescription td = SyncFactory.getRefresher();
SourceLogic state = (SourceLogic) td.getSavedObject();
if (state == null) {
state = new SourceLogic();
state.salesRepIdPubParameter=getTargetParameter("salesRepId", td);
state.getSalesRepId = conn.prepareStatement(
state.getSalesRepIdText);
state.getToysQuantity=conn.prepareStatement(
"SELECT quantity FROM toys WHERE toy = ?");
state.updateToysQuantity=conn.prepareStatement(
"UPDATE toys SET quantity = quantity-? " +
"WHERE toy = ?");
state.insertOrder=conn.prepareStatement(
"INSERT INTO orders VALUES(?,?,?,?,?,?)");
td.saveObject(state);
}
if (state.getToysQuantity == null) {
state.getToysQuantity=conn.prepareStatement(
"SELECT quantity FROM toys WHERE toy = ?");
state.updateToysQuantity=conn.prepareStatement(
"UPDATE toys SET quantity = quantity-? WHERE toy = ?");
state.insertOrder=conn.prepareStatement(
"INSERT INTO orders VALUES(?,?,?,?,?,?)");
td.saveObject(state);
}
if (salesRepID != state.salesRepIdPubParameter) {
throw StopRefreshSQLException.stopRefreshSQLException(
"Your sales repId is not the official "+
"salesRepId for your target." +
"Please contact your system administrator.");
}
PreparedStatement getSalesRepId = state.getSalesRepId;
getSalesRepId.setInt(1, salesRepID);
ResultSet repsRS = getSalesRepId.executeQuery();
if (!repsRS.next()) throw StopRefreshSQLException.stopRefreshSQLException(
"Your sales rep ID "+ salesRepID +
" is invalid. Please contact your system administrator.");
repsRS.close();
String status = "";
int quantityAvailable;
PreparedStatement getToysQuantity = state.getToysQuantity;
getToysQuantity.setString(1, toy);
ResultSet rs = getToysQuantity.executeQuery();
if (!rs.next()) {
quantityAvailable = 0;
status = "TOY UNAVAILABLE";
} else {
quantityAvailable = rs.getInt(1);
}
rs.close();
if (quantityAvailable >= quantityOrdered) {
PreparedStatement updateToysQuantity = state.updateToysQuantity;
updateToysQuantity.setInt(1, quantityOrdered);
updateToysQuantity.setString(2, toy);
updateToysQuantity.executeUpdate();
status = "ORDERED";
} else if (status != "TOY UNAVAILABLE") {
status = "BACKORDERED";
}
PreparedStatement insertOrder = state.insertOrder;
insertOrder.setBytes(1, orderID);
insertOrder.setInt(2, salesRepID);
insertOrder.setString(3, customer);
insertOrder.setString(4, toy);
insertOrder.setInt(5, quantityOrdered);
insertOrder.setString(6, status);
insertOrder.executeUpdate();
}
public static void sendLeadAtSource
(byte[] leadID, int sentToRepID, int sentFromRepID,
String customer)
throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:default:connection");
TargetDescription td = SyncFactory.getRefresher();
SourceLogic state = (SourceLogic) td.getSavedObject();
if (state == null) {
state = new SourceLogic();
state.salesRepIdPubParameter = getTargetParameter(
"salesRepId", td);
state.insertLead=conn.prepareStatement(
"INSERT INTO leads VALUES(?,?,?,?)");
state.getSalesRepId = conn.prepareStatement(
state.getSalesRepIdText);
td.saveObject(state);
}
if (state.insertLead == null) {
state.insertLead=conn.prepareStatement(
"INSERT INTO leads VALUES(?,?,?,?)");
td.saveObject(state);
}
if (sentFromRepID != state.salesRepIdPubParameter) {
throw StopRefreshSQLException.stopRefreshSQLException(
"Your sales repId is not the official "+
"salesRepId for your target." +
"Please contact your system administrator.");
}
PreparedStatement getSalesRepId = state.getSalesRepId;
getSalesRepId.setInt(1, sentFromRepID);
ResultSet repsRS = getSalesRepId.executeQuery();
if (!repsRS.next()) throw StopRefreshSQLException.stopRefreshSQLException(
"Invalid sales rep ID "+ sentFromRepID +
" sent a lead to sales rep "+ sentToRepID + ".");
repsRS.close();
// Abort transaction if lead assigned to
// a non-existent sales rep
getSalesRepId.setInt(1,sentToRepID);
ResultSet assignedRepsRS = getSalesRepId.executeQuery();
if (!assignedRepsRS.next())
throw SkipTransactionSQLException.skipTransactionSQLException(
"Lead assigned to invalid sales rep "+ sentToRepID);
assignedRepsRS.close();
PreparedStatement insertLead = state.insertLead;
insertLead.setBytes(1, leadID);
insertLead.setInt(2, sentToRepID);
insertLead.setInt(3, sentFromRepID);
insertLead.setString(4, customer);
insertLead.executeUpdate();
}
private static int getTargetParameter(String paramName, TargetDescription td) throws SQLException {
ResultSet rs = td.getPublicationParameters();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
rs.next();
int salesRepIdPubParam = 0;
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnName(i);
int type = rsmd.getColumnType(i);
if (type == Types.INTEGER && columnName.equals(paramName.toUpperCase())) {
salesRepIdPubParam = rs.getInt(1);
}
else
System.out.println(
"Unknown parameter" + rs.getObject(i));
}
return salesRepIdPubParam;
}
}
|