Designing Synchronized Applications
Page 5 of 7

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(?,?,?,?,?,?)");
        // find out the number of toys available
        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();
        // set status provisionally
        // to WILL ORDER or WILL BACKORDER
        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");
        // Acknowledge lead
        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");
        //get the TargetDescription so we can 
        //save the PreparedStatement
        //across connections
        TargetDescription td = SyncFactory.getRefresher();
        SourceLogic state = (SourceLogic) td.getSavedObject();
        if (state == null) {
            //this must be the first work unit
            // execution in the refresh.
            //create the object.
            state = new SourceLogic();
            //get the value of the publication parameter salesRepId
           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 was instantiated only by sendLeadAtSource
            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);
        }
        
            //get some other information from the TargetDescription
            //for extra security, make sure salesRepId that is passed
            //as a parameter to the work unit is
            // also the official publication
            //parameter
            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);
        // Abort refresh if salesRepID is invalid
        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;
        // Find out how many toys are available
        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();
        // set status durably
        // to ORDERED or BACKORDERED
        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();
        //don't close the prepared statements or the connection
    }
    public static void sendLeadAtSource
            (byte[] leadID, int sentToRepID, int sentFromRepID, 
            String customer)
            throws SQLException {
        Connection conn = DriverManager.getConnection(
            "jdbc:default:connection");
        //get the TargetDescription so we can save the
        //PreparedStatement across connections
        TargetDescription td = SyncFactory.getRefresher();
        SourceLogic state = (SourceLogic) td.getSavedObject();
                if (state == null) {
            //this must be the work unit execution in the refresh.
            //create the object.
            state = new SourceLogic();
            //get the value of the publication parameter salesRepId
            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 was instantiated by makeSaleAtSource
            state.insertLead=conn.prepareStatement(
                "INSERT INTO leads VALUES(?,?,?,?)");
            td.saveObject(state);
        }


    // Abort refresh if lead sent from a non-qualifying sales rep
    //get some other information from the TargetDescription 
    //for extra security, we're making sure salesRepId that is passed
    //as a parameter to the work unit is also the official publication
    //parameter
            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();
        // Add lead to leads table
        PreparedStatement insertLead = state.insertLead;
        insertLead.setBytes(1, leadID);
        insertLead.setInt(2, sentToRepID);
        insertLead.setInt(3, sentFromRepID);
        insertLead.setString(4, customer);
        insertLead.executeUpdate();
        //don't close the prepared statements or the connection
    }
    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;
    }
}