3 Synchronization

The Oracle Lite database contains a subset of data stored in the Oracle database. This subset is stored in snapshots in the Oracle Lite database. Unlike a base table, a snapshot keeps track of changes made to it in a change log. Users can make changes in the Oracle Lite database and can synchronize them with the Oracle database.

The following sections describe how synchronization functions between Oracle Database Lite and an Oracle database using the Mobile Server. This chapter discusses how you can programmatically initiate the synchronization both from the client or the server side.

3.1 How Does Synchronization Work?

The full description of how synchronization works is in the "Managing Synchronization" chapter in the Oracle Database Lite 10g Administration and Deployment Guide. Each component and its function is described in the administration guide. The following graphic depicts these components for your reference:

Figure 3-1 Synchronization Architecture

Description of syncdemo.gif follows
Description of the illustration syncdemo.gif

  1. User initiates a synchronization from the Mobile client. Note that the Mobile client may be a Windows platform client or a PDA.

  2. Mobile client software gathers all of the client changes into a transaction and the Sync Client uploads the transaction to the Sync Server on the Mobile Server.

  3. Sync Server places the transaction into the In-Queue.

  4. Sync Server gathers all transactions destined for the Mobile client from the Out-Queue.

  5. Sync Server transfers these transactions down to the Sync Client.

  6. Mobile client downloads and applies all changes for client Oracle Lite database.

  7. All transactions compiled from all Mobile clients are gathered by the MGP out of the In-Queue.

  8. The MGP applies all transactions for the Mobile clients to their respective application tables.

  9. Any updates destined for any Mobile client is composed into a transaction by the MGP process.

  10. MGP places outgoing transactions for Mobile clients into the Out-Queue, waiting for the next client synchronization for the Sync Server to gather the updates to the client.

When we discuss how to perform the tasks associated with synchronization, refer back to this graphic to discover what part of the synchronization process that we are discussing.

3.1.1 How Updates Are Propagated to the Back-End Database

The synchronization process applies client operations to the tables in the back-end database according to a table weight assigned to the publication items, as follows:

  1. The operations for each publication item are processed according to table weight. The publication creator assigns the table weight to publication items within a specific publication. This value can be an integer between 1 and 1023. For example, a publication can have more than one publication item of weight "2" which would have INSERT operations performed after those for any publication item of a lower weight within the same publication. You define the order weight for tables when you add a publication item to the publication. See Section 3.3.1.6.2, "Using Table Weight" for more information.

  2. Within each publication item being processed, the SQL operations are processed as follows:

    1. Client INSERT operations are executed first, from lowest to highest table weight order.

    2. Client DELETE operations are executed next, from highest to lowest table weight order.

    3. Client UPDATE operations are executed last, from lowest to highest table weight order.

For details and an example of exactly how the weights and SQL operations are processed, see Section 3.3.1.6.2, "Using Table Weight".


Note:

This order of executing operations can cause constraint violations. See Section 3.9, "Synchronizing With Database Constraints" for more information.

In addition, the order in which SQL statements are executed against the client Oracle Lite database is not the same as how synchronization propagates these modifications. Instead, synchronization captures the end result of all SQL modifications as follows:

  1. Insert an employee record 4 with name of Joe Judson.

  2. Update employee record 4 with address.

  3. Update employee record 4 with salary.

  4. Update employee record 4 with office number

  5. Update employee record 4 with work email address.

When synchronization occurs, all modifications are captured and only a single insert is performed on the back-end database. The insert contains the primary key, name, address, salary, office number and email address. Even though the data was created with multiple updates, the Synch Server only takes the final result and makes a single insert.

3.2 What is The Process for Setting Up a User For Synchronization?

Before you can perform the synchronization, the publication must be created, the user created and granted access to the publication, and optionally, the publication packaged up with an application and published to the Mobile Server. This is referred to as the publish and subscribe model, which can be implemented in one of two ways:

Once created and subscribed, the user can be synchronized, either from the user initiating it from the device or programmatically from within an application. This chapter discusses how to start the synchronization programmatically in Section 3.7, "Client Synchronization".

On the back-end of the synchronization process, you can customize how the apply and compose phase are executed. See Section 3.5, "Customize the Apply and Compose Phases Using the Consolidator Manager APIs".

3.2.1 Creating a Snapshot Definition Declaratively

Use the Mobile Database Workbench (MDW), a GUI based tool of Oracle Database Lite—described fully in Chapter 5, "Using Mobile Database Workbench to Create Publications"—to create snapshots declaratively. The convenience of a graphical tool is a safer and less error prone technique for developers to create a Mobile application. Before actual application programming begins, the following steps must be executed:

  • Verify that the base tables exist on the server database; if not, create the base table.

  • Use MDW to define an application and its publication items (snapshot definitions).

  • Use the Packaging Wizard to publish the application to the Mobile Server. This creates the publication items associated with the application. See Chapter 6, "Using the Packaging Wizard" for details.

  • Use the Mobile Manager to create a subscription for a given user.

  • Install the application on the development machine.

  • Synchronize the Mobile client with the Mobile Server to create the client-side snapshots, which creates the Mobile client Oracle Lite database automatically.

See Chapter 5, "Using Mobile Database Workbench to Create Publications" and Chapter 6, "Using the Packaging Wizard" for full details.

3.2.1.1 Manage Snapshots

The Mobile Server administrator can manage a snapshot, which is a full set or a subset of rows of a table or view. Create the snapshot by executing a SQL query against the base table. Snapshots are either read-only or updatable.

The following sections describes how to manage snapshots using MDW:

3.2.1.1.1 Read-only Snapshots

Read-only snapshots are used for querying purposes. Changes made to the master table are replicated to the snapshot by the Mobile client. See Section 5.6.1, "Associate Publication Item With Publication" for instructions on how to define the publication item as read-only.

3.2.1.1.2 Updatable Snapshots

Updatable snapshots provide updatable copies of a master table. You can define updatable snapshots to contain a full copy of a master table or a subset of rows/columns in the master table that satisfy a value-based selection criteria. You can make changes to the snapshot which the Mobile Sync propagates back to the master table. See Section 5.6.1, "Associate Publication Item With Publication" for instructions on how to define the publication item as updatable.

A snapshot can only be updated when all the base tables that the snapshot is based on have a primary key. If the base tables do not have a primary key, a snapshot cannot be updated and becomes read-only.

3.2.1.1.3 Refresh a Snapshot

Your snapshot definition determines whether an updatable snapshot uses the complete or fast refresh method.

  • The complete refresh method recreates the snapshot every time it is refreshed.

  • The fast refresh method refreshes the existing data in the snapshot. In general, the simpler your snapshot definition, the faster it is updated.

See Section 5.2, "Create a Publication Item" and Section 3.8, "Understanding Your Refresh Options"

3.2.1.1.4 Snapshot Template Variables

Snapshots are application-based. In some cases, you may quantify the data that your application downloads for each user by specifying all of the returned data match a predicate. You can accomplish this by using snapshot templates.

A snapshot template is an SQL query that contains data subsetting parameters. A data subsetting parameter is a colon (:), followed by an identifier name, as follows:

:var1

When the Mobile client creates snapshots on the client machine, the Mobile Server replaces the snapshot variables with user-specific values. By specifying different values for different users, you can control the data returned by the query for each user.

You can use MDW to specify a snapshot template variable in the same way that you create a snapshot definition for any platform.

Data subsetting parameters are bind variables and so should not be enclosed in quotation marks ('). If you want to specify a string as the value of the data subsetting parameter, then the string contains single quotation marks. You can specify the values for the template variables within the Mobile Manager.

The following examples specify a different value for every user. By specifying a different value for every user, the administrator controls the behavior and output of the snapshot template.

select * from emp where deptno = :dno

You define this select statement in your publication item. See Section 5.2.1, "Create SQL Statement for Publication Item" for instructions. Then, modify the user in the Mobile Manager to add the value for :dno. Then, when the user synchronizes, the value defined for the user is replaced in the select script. See the "Managing Application Parameter Input (Data Subsetting)" section in the Oracle Database Lite Administration and Deployment Guide for information on how to define the value of the variable. This value can only be defined after the application is published and the user is associated with it.

Table 3-1 provides a sample set of snapshot query values specified for separate users.

Table 3-1 Snapshot Query Values for Separate Users

User Value Snapshot Query
John 10 select * from emp where deptno = 10
Jane 20 select * from emp where deptno = 20 

select * from emp where ename = :ename

Table 3-2 provides another sample snapshot query value.

Table 3-2 Snapshot Query Value for User Names

User Value Snapshot Query
John 'KING' select * from emp where ename = 'KING'

3.2.2 Creating the Snapshot Definition Programmatically

You can use the Resource Manager or Consolidator Manager APIs to programmatically create the publication items on the Mobile Server. Create publication items from views and customize code to construct snapshots.


Note:

The Consolidator Manager API can only create a publication, which cannot be packaged with an application. In addition, a publication created with the Consolidator Manager API cannot be packaged with an application. See Section 3.3, "Creating Publications Using Oracle Database Lite APIs" for information on the Consolidator Manager API. Use the Resource Manager APIs to create the publication, package it with an application, and publish it to the Mobile Server. See the oracle.mobile.admin.ResourceManager Javadoc in the Web-to-Go API Specification section, which is located off the ORACLE_HOME/Mobile/index.htm page.

The base tables must exist before the Consolidator Manager API can be invoked. The following steps are required to create a a subscription:

  • Create a publication

  • Create a publication item and add it to the publication

  • Create a user

  • Creating a subscription for the user based on the publication

The details of how to create a publication are documented in Chapter 5, "Using Mobile Database Workbench to Create Publications". Anything that you can do with the MDW tool, you can also perform programmatically using the Consolidator Manager API. Refer to the Javadoc for the syntax.

3.3 Creating Publications Using Oracle Database Lite APIs

Mobile Server uses a publish and subscribe model to centrally manage data distribution between Oracle database servers and Oracle Database Lite clients. Basic functions, such as creating publication items and publications, can be implemented easily using the Mobile Development Workspace (MDW). See Chapter 5, "Using Mobile Database Workbench to Create Publications" for more information.

These functions can also be performed using the Consolidator Manager or Resource Manager APIs by writing Java programs to customize the functions as needed. Some of the advanced functionality can only be enabled programmatically using the Consolidator Manager or Resource Manager APIs.

The publish and subscribe model can be implemented one of two ways:

  • Declaratively, using MDW to create the publication and the Packaging Wizard to package and publish the applications. This is the recommended method. This method is described fully in Chapter 5, "Using Mobile Database Workbench to Create Publications" and Chapter 6, "Using the Packaging Wizard".

  • Programmatically, using the Consolidator Manager or Resource Manager APIs to invoke certain advanced features or customize an implementation. This technique is recommended for advanced users requiring specialized functionality.

    • Publications created with the Consolidator Manager API cannot be packaged with an application. See Section 3.3.1, "Defining a Publication With Java Consolidator Manager APIs".

    • Use the Resource Manager APIs to create the publication, package it with an application, and publish it to the Mobile Server. See the oracle.mobile.admin.MobileResourceManager Javadoc in the API Specification section, which is located off the ORACLE_HOME/Mobile/index.htm page.

3.3.1 Defining a Publication With Java Consolidator Manager APIs

While we recommend that you use MDW (see Chapter 5, "Using Mobile Database Workbench to Create Publications") or the Packaging Wizard (see Chapter 6, "Using the Packaging Wizard") for creating your publications, you can also create them, including the publication items and the user, with the Consolidator Manager API. Choose this option if you are performing more advanced techniques with your publications.

After creating the database tables in the back-end database, create the Resource Manager and Consolidator Manager objects to facilitate the creation of your publication:

  • The Resource Manager object enables you to create users to associate with the subscription.

  • The Consolidator Manager object enables you to create the subscription.

The order of creating the elements in the publication is the same as if you were using MDW. You must create a publication first and then add the publication items and other elements to it. Once the publications are created, subscribe users to them. See the Javadoc for full details on each method. See Chapter 5, "Using Mobile Database Workbench to Create Publications" for more details on the order of creating each element.


Note:

The following sections use the sample11.java sample to demonstrate the Resource Manager and Consolidator Manager methods used to create the publication and the users for the publication. The full source code for this sample can be found in the following directories:

On UNIX: <ORACLE_HOME>/mobile/server/samples

On Windows: <ORACLE_HOME>\Mobile\Server\Samples

  1. Section 3.3.1.1, "Create the Mobile Server User"

  2. Section 3.3.1.2, "Create Publications"

  3. Section 3.3.1.3, "Create Publication Items"

  4. Section 3.3.1.4, "Data Subsetting: Defining Client Subscription Parameters for Publications"

  5. Section 3.3.1.5, "Create Publication Item Indexes"

  6. Section 3.3.1.6, "Adding Publication Items to Publications"

  7. Section 3.3.1.7, "Creating Client-Side Sequences for the Downloaded Snapshot"

  8. Section 3.3.1.8, "Subscribing Users to a Publication"

  9. Section 3.3.1.9, "Instantiate the Subscription"

  10. Section 3.3.1.10, "Bringing the Data From the Subscription Down to the Client"

  11. Section 3.3.1.11, "Modifying a Publication Item"

  12. Section 3.3.1.12, "Callback Customization for DML Operations"

  13. Section 3.3.1.13, "Restricting Predicate"


Note:

To call the Publish and Subscribe methods, the following JAR files must be specified in your CLASSPATH.

  • <ORACLE_HOME>\jdbc\lib\classes12.zip

  • <ORACLE_HOME>\Mobile\classes\consolidator.jar

  • <ORACLE_HOME>\Mobile\classes\classgen.jar

  • <ORACLE_HOME>\Mobile\classes\servlet.jar

  • <ORACLE_HOME>\Mobile\classes\xmlparserv2.jar

  • <ORACLE_HOME>\Mobile\classes\jssl-1_2.jar

  • <ORACLE_HOME>\Mobile\classes\javax-ssl-1_2.jar

  • <ORACLE_HOME>\Mobile\classes\devmgr.jar

  • <ORACLE_HOME>\Mobile\classes\share.jar

  • <ORACLE_HOME>\Mobile\classes\oracle_ice.jar

  • <ORACLE_HOME>\Mobile\classes\phaos.jar

  • <ORACLE_HOME>\Mobile\classes\jewt-nls.jar

  • <ORACLE_HOME>\Mobile\classes\wtgpack.jar

  • <ORACLE_HOME>\Mobile\Server\bin\webtogo.jar

  • <ORACLE_HOME>\Mobile\Server\bin\jzlib.jar

  • <ORACLE_HOME>\Mobile\Server\bin\aes.jar

  • <ORACLE_HOME>\Mobile\Server\bin\repository.jar

3.3.1.1 Create the Mobile Server User

Use the createUser method of the MobileResourceManager object to create the user for the publication.

  1. Create the MobileResourceManager object. A connection is opened to the Mobile Server. Provide the schema name, password, and JDBC URL for the database the contains the schema (the repository).

  2. Create one or more users with the createUser method. Provide the user name, password, the user's real name, and privilege, which can be one of the one of the following: "O" for publishing an application, "U" for connecting to Web-to-Go as user, or "A" for administrating the Web-to-Go. If NULL, no privilege is assigned.


    Note:

    Always request a drop user before you execute a create, in case this user already exists.

  3. Commit the transaction, which was opened when you created the MobileResourceManager object, and close the connection.

MobileResourceManager mobileResourceManager =
    new MobileResourceManager(CONS_SCHEMA, DEFAULT_PASSWORD, JDBC_URL);
mobileResourceManager.createUser("S11U1", "manager", "S11U1", "U");
mobileResourceManager.commitTransaction();
mobileResourceManager.closeConnection();

Note:

If you do not want to create any users, you do not need to create the MobileResourceManager object.

3.3.1.1.1 Change Password

You can change passwords for Mobile Server users with the setPassword method, which has the following syntax:

public static void setPassword

   (String userName,

    String newpwd) throws Throwable

Execute the setPassword method before you commit the transaction and release the connection. The following example changes the password for the user MOBILE:

mobileResourceManager.setPassword("MOBILE","MOBILENEW");

3.3.1.2 Create Publications

A subscription is a combination of publications and the users who access the information gathered by the publications. Create any publication through the ConsolidatorManager object.

  1. Create the ConsolidatorManager object.

  2. Connect to the database using the openConnection method. Provide the schema name, password, and JDBC URL for the database the contains the schema (the repository).

  3. Create the publication with the createPublication method, which creates an empty publication.


Note:

Always request a drop publication before you execute a create, in case this publication already exists.		 

ConsolidatorManager consolidatorManager = new ConsolidatorManager();
consolidatorManager.openConnection(CONS_SCHEMA, DEFAULT_PASSWORD, JDBC_URL);
consolidatorManager.createPublication("T_SAMPLE11", 
            Consolidator.OKPI_CREATOR_ID, "OrdersODB.%s", null);

Note:

Special characters including spaces are supported in publication names. The publication name is case-sensitive.

3.3.1.3 Create Publication Items

An empty publication does not have anything that is helpful until a publication item is added to it. Thus, after creating the publication, it is necessary to create the publication item, which defines the snapshot of the base tables that is downloaded for your user. The refresh mode of the publication item is specified during creation to be either fast, complete-refresh, or queue-based. You can also establish the data-subsetting parameters when creating the publication item, which provides a finer degree of control on the data requirements for a given client.

Publication item names are limited to twenty-six characters and must be unique across all publications. The publication item name is case-sensitive. The following examples create a publication item named P_SAMPLE11-M.


Note:

Always drop the publication item in case an item with the same name already exists.

The following code creates a publication item P_SAMPLE11-M based on the ORD_MASTER database table and adds it to the publication with the createPublicationItem method:


Note:

For full details on the method parameters, see the Javadoc.		 

consolidatorManager.createPublicationItem("P_SAMPLE11-M", "MASTER", 
    "ORD_MASTER", "F", "SELECT * FROM MASTER.ORD_MASTER", null, null);
3.3.1.3.1 Defining Publication Items for Updatable Multi-Table Views

Publication items can be defined for both tables and views. When publishing updatable multi-table views, the following restrictions apply:

  • The view must contain a parent table with a primary key defined.

  • INSTEAD OF triggers must be defined for data manipulation language (DML) operations on the view. See Section 3.8, "Understanding Your Refresh Options" for more information.

  • All base tables of the view must be published.

3.3.1.4 Data Subsetting: Defining Client Subscription Parameters for Publications

Data subsetting is the ability to create specific subsets of data and assign them to a parameter name that can be assigned to a subscribing user. When creating publication items, a parameterized Select statement can be defined. Subscription parameters must be specified at the time the publication item is created, and are used during synchronization to control the data published to a specific client.

Creating a Data Subset Example

consolidatorManager.createPublicationItem("CORP_DIR1", 
   "DIRECTORY1", "ADDRLRL4P", "F" ,

   "SELECT LastName, FirstName, company, phone1, phone2, phone3, phone4,

    phone5, phone1id, phone2id, phone3id, displayphone, address, city, state,
    zipcode, country, title, custom1, custom2, custom3, note
    FROM directory1.addrlrl4p WHERE company = :COMPANY", null, null);

In this sample statement, data is being retrieved from a publication named CORP_DIR1, and is subset by the variable COMPANY.


Note:

Within the select statement, the parameter name for the data subset must be prefixed with a colon, for example:COMPANY.

When a publication uses data subsetting parameters, set the parameters for each subscription to the publication. For example, in the previous example, the parameter COMPANY was used as an input variable to describe what data is returned to the client. You can set the value for this parameter with the setSubscriptionParameter method. The following example sets the subscription parameter COMPANY for the client DAVIDL in the CORP_DIR1 publication to DAVECO:

consolidatorManager.setSubscriptionParameter("CORP_DIR1", "DAVIDL",
             "COMPANY", "'DAVECO'");

Note:

This method should only be used on publications created using the Consolidator Manager API. To create template variables, a similar technique is possible using MDW.

3.3.1.5 Create Publication Item Indexes

The Mobile Server supports automatic deployment of indexes in Oracle Database Lite on clients. The Mobile Server automatically replicates primary key indexes from the server database. The Consolidator Manager API provides calls to explicitly deploy unique, regular, and primary key indexes to clients as well.

By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on a publication item. If you do not want the primary index, you must explicitly drop it from the publication items.

If you want to create other indexes on any columns in your application tables, then use the createPublicationItemIndex method. The following demonstrates how to set up indexes on the name field in our publication item P_SAMPLE11-M:

consolidatorManager.createPublicationItemIndex("P_SAMPLE11M-I3", 
    "P_SAMPLE11-M", "I", "NAME");

An index can contain more than one column. You can define an index with multiple columns, as follows:

consolidatorManager.createPublicationItemIndex("P_SAMPLE11D-I1", "P_SAMPLE11-D",        "I", "KEY,NAME");
3.3.1.5.1 Define Client Indexes

Client-side indexes can be defined for existing publication items. There are three types of indexes that can be specified:

  • P - Primary key

  • U - Unique

  • I - Regular


Note:

When an index of type 'U' or 'P' is defined on a publication item, there is no check for duplicate keys on the server. If the same constraints do not exist on the base object of the publication item, synchronization may fail with a duplicate key violation. See the Oracle Database Lite API Specification for more information.

3.3.1.6 Adding Publication Items to Publications

Once you create a publication item, you must associate it with a publication. To change the definition, you can either drop the publication item and then recreate it with the new definition, or use schema evolution depending on your requirements. See dropPublicationItem and alterPublicationItem respectively in the Oracle Database Lite API Specification for more information.

Once you have finished creating the publication items, add it to the desired publication using the addPublicationItem method, as follows:

consolidatorManager.addPublicationItem("T_SAMPLE11", "P_SAMPLE11-M",       null, null, "S", null, null);
3.3.1.6.1 Defining Conflict Rules

When adding a publication item to a publication, the user can specify winning rules to resolve synchronization conflicts in favor of either the client or the server. See for more information.

3.3.1.6.2 Using Table Weight

Table weight is an integer associated with publication items that determines in what order the transactions for all publications are processed. For example, if three publication items exist—emp, dept, mgr, you can define the order in which the transactions associated with each publication item are executed. In our example, assign table weight of 1 to dept, table weight of 2 to mgr, and table weight of 3 to emp. In doing this, you ensure that the master table dept is always updated first, followed by mgr, and lastly by emp.

Irregardless of the table weight, the insert, update, and delete client operations are always executed in the following order:

  1. Client INSERT operations are executed first, from lowest to highest table weight order. This ensures that the master table entries are added before the details table entries.

  2. Client DELETE operations are executed next, from highest to lowest table weight order. Processing the delete operations ensures that the details table entries are removed before the master table entries.

  3. Client UPDATE operations are executed last, from lowest to highest table weight order.

In our example with dept, mgr, and emp tables, the execution order would be as follows:

  1. All insert operations for dept are processed.

  2. All insert operations for mgr are processed.

  3. All insert operations for emp are processed.

  4. All delete operations for emp are processed.

  5. All delete operations for mgr are processed.

  6. All delete operations for dept are processed.

  7. All update operations for dept are processed.

  8. All update operations for mgr are processed.

  9. All update operations for emp are processed.

Table weight is applied to publication items within a specific publication; for example, a publication can have more than one publication item of weight 2. In this case, it does not matter which publication is executed first.

Define the order weight for tables when you add a publication item to the publication.

3.3.1.7 Creating Client-Side Sequences for the Downloaded Snapshot

A sequence is a database schema object that generates sequential numbers. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. These unique integers can include primary key values. If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.

If you have more than a single client, you want to assign who gets which sequence numbers, so that when you synchronize, none of the records have duplicate sequence numbers. Thus, if you have multiple clients, then specify a distinct range of numbers for each client, so that they are not using the same numbers.

  • Specify a range of values for each client. In our example, client A would be assigned sequence numbers 1 through 100, client B would be assigned sequence numbers 101 to 200, and client C would be assigned sequence numbers 201 through 300. If they ran out of sequence numbers, they are assigned another 100, which is the defined window size in our example, during the next synchronization. Since none of the clients checked to generate server-side sequence, the database, in order to never collide with the sequence numbers, starts its sequence number at -1 and decrements for each subsequent sequence number.

  • You could specify that all clients are allowed to have only odd numbers and the database has all even numbers. That is, you could start the client at 1 and increment by 2 for all of its sequence numbers. This enables you to avoid having negative numbers for your sequence numbers. The clients still have a window size, which in this example is 100, but they start with an odd number within that window and always increment by 2 to avoid any positive numbers. Thus, client A would still have the window of 1 to 100, but the sequence numbers would be 1, 3, 5, and so on up to 99.

Thus, for each client that uses sequences, you must define what numbers each client can use through the Consolidator Manager API, which allow you to manage the sequences with methods that create/drop a sequence, add/remove a sequence from a publication, modify a sequence, and advance a sequence window for each user.


Note:

The sequence name is case-sensitive.

Once you have created the sequence, you place it into the publication with the publication item to which it applies.


Note:

If the sequences do not work properly, check your parent publications. All parent publications must have at least one publication item. If you do not have any publication items for the parent publication, then create a dummy publication item within the parent.

See the Oracle Database Lite API Specification (included on the CD) for a complete listing of the APIs to define and administrate sequences.

3.3.1.7.1 Specifying Sequence Threshold for Window Management

Oracle Database Lite also allows you to set a threshold. If you know that you need a minimum number of records between synchronizations to perform your work, set this number as the threshold. That way, if you have less than this number available to you, Oracle Database Lite provides the client with a new window to work from.

For example, if a client has a window of 100 and retrieves the first window of 1-100. If the sequence numbers retrieved is currently at record number 97, then—if no threshold is set—the Oracle Database Lite does not provide a new window since this window is not complete. However, if you state that you need at least 20 records to perform your duties, Oracle Database Lite would notice that there are less than 20 records left in the window and assigsn the client the next window, which in this case would be sequence numbers 101-200.

3.3.1.7.2 Description of Sequence Support

The following sequence support is available:

  • True sequence support on the client—The Sync Server supports replication of true sequence objects to the client.

  • Clear association with a publication—In a manner similar to publication items, adding sequences to a publication propagates the corresponding sequence objects to all subscribing users. Note that a publication and a sequence have a one-to-many relationship. This means a publication can contain many different sequences, but a single sequence cannot exist in more than one publication.

  • Offline and Online—There are two types of sequences, as follows:

    • Offline: The developer specifies the increment value of the sequence used by the client. The sequence exists solely for the client.

    • Online: An online sequence is designed to support online Web-to-Go applications. This is accomplished by creating the same sequence object on both the server and the client. The paired sequences are incremented by two and started with staggered values; one starts with an even number and one starts with an odd number. By using an odd/even window model such as the one described above, the Consolidator Manager ensures uniqueness—regardless of whether the application is running in online mode or in offline mode.

  • Sequence management - Once the sequences have been defined and associated with a publication, the Sync Server manages all aspects of administration for sequences, including allocation of new windows.

3.3.1.8 Subscribing Users to a Publication

Subscribe the users to a publication using the createSubscription function. The following creates a subscription between the S11U1 user and the T_SAMPLE11 publication:

consolidatorManager.createSubscription("T_SAMPLE11", "S11U1");

3.3.1.9 Instantiate the Subscription

After you subscribe a user to a publication, you complete the subscription process by instantiating the subscription, which associates the user with the publication in the back-end database. The next time that the user synchronizes, the data snapshot from the publication is provided to the user.

consolidatorManager.instantiateSubscription("T_SAMPLE11", "S11U1");

//Close the connection.
consolidatorManager.closeConnection();

Note:

If you need to set subscription parameters for data subsetting, this must be completed before instantiating the subscription. See Section 3.3.1.4, "Data Subsetting: Defining Client Subscription Parameters for Publications" for more information.

3.3.1.10 Bringing the Data From the Subscription Down to the Client

You can perform the synchronization and bring down the data from the subscription you just created. The client executes SQL queries against the client ODB to retrieve any information. This subscription is not associated with any application, as it was created using the low-level Consolidator Manager APIs.

3.3.1.11 Modifying a Publication Item

You can add additional columns to existing publication items. These new columns are pushed to all subscribing clients the next time they synchronize. This is accomplished through a complete refresh of all changed publication items.

  • An administrator can add multiple columns, modify the WHERE clause, add new parameters, and change data type.

  • This feature is supported for all Mobile client platforms.

  • The client does not upload snapshot information to the server. This also means the client cannot change snapshots directly on the client database, for example, you could not alter a table using Mobile SQL.

  • Publication item upgrades will be deferred during high priority synchronizations. This is necessary for low bandwidth networks, such as wireless, because all publication item upgrades require a complete refresh of changed publication items. While the high priority flag is set, high priority clients will continue to receive the old publication item format.

  • The server needs to support a maximum of two versions of the publication item which has been altered.

Use the alterPublicationItem method to add columns to an existing publication item. The WHERE clause may also be altered. If additional parameters are added to the WHERE clause, then these parameters must be set before the alter occurs. See the setSubscriptionParams method.

consolidatorManager.alterPublicationItem("P_SAMEPLE1", "select * from EMP");

Note:

If the select statement does not change, then the call to the alterPublicationItem() method has no effect.

3.3.1.12 Callback Customization for DML Operations

Once a publication item has been created, a user can use the Consolidator Manager API to specify a customized PL/SQL procedure that is stored in the Mobile Server repository to be called in place of all DML operations for that publication item. There can be only one Mobile DML procedure for each publication item. The procedure should be created as follows:

AnySchema.AnyPackage.AnyName(DML in CHAR(1), COL1 in TYPE, COL2 in TYPE, COLn.., PK1 in TYPE, PK2 in TYPE, PKn..)

The parameters for customizing a DML operation are listed in Table 3-3:

Table 3-3 Mobile DML Operation Parameters

Parameter Description
DML DML operation for each row. Values can be "D" for DELETE, "I" for INSERT, or "U" for UPDATE.
COL1 ... COLn List of columns defined in the publication item. The column names must be specified in the same order that they appear n the publication item query. If the publication item was created with "SELECT * FROM exp", the column order must be the same as they appear in the table "exp".
PK1 ... PKn List of primary key columns. The column names must be specified in the same order that they appear in the base or parent table.

The following defines a DML procedure for publication item exp:

select A,B,C from publication_item_exp_table

Assuming A is the primary key column for exp, then your DML procedure would have the following signature:

any_schema.any_package.any_name(DML in CHAR(1), A in TYPE, B in TYPE, C                          in TYPE,A_OLD in TYPE)

During runtime, this procedure is invoked with 'I', 'U', or 'D' as the DML type. For insert and delete operations, A_OLD will be null. In the case of updates, it will be set to the primary key of the row that is being updated. Once the PL/SQL procedure is defined, it can be attached to the publication item through the following API call:

consolidatorManager.addMobileDmlProcedure("PUB_exp","exp",                                            "any_schema.any_package.any_name")

where exp is the publication item name and PUB_exp is the publication name.

Refer to the Oracle Database Lite API Specification for more information.

3.3.1.12.1 DML Procedure Example

The following piece of PL/SQL code defines an actual DML procedure for a publication item in one of the sample publications. As described below, the ORD_MASTER table. The query was defined as:

SELECT * FROM "ord_master", where ord_master has a single column primary key               on "ID"

ord_master Table

SQL> desc ord_master

Name                                      Null?    Type
----------------------------------------- -------- -------------
ID                                        NOT NULL NUMBER(9)
DDATE                                              DATE
STATUS                                             NUMBER(9)
NAME                                               VARCHAR2(20)
DESCRIPTION                                        VARCHAR2(20)

Code Example

CREATE OR REPLACE  PACKAGE "SAMPLE11"."ORD_UPDATE_PKG"  AS
 procedure  UPDATE_ORD_MASTER(DML CHAR,ID NUMBER,DDATE DATE,STATUS
NUMBER,NAME VARCHAR2,DESCRIPTION VARCHAR2, ID_OLD NUMBER);
END ORD_UPDATE_PKG;
/
CREATE OR REPLACE  PACKAGE BODY "SAMPLE11"."ORD_UPDATE_PKG" as
  procedure  UPDATE_ORD_MASTER(DML CHAR,ID NUMBER,DDATE DATE,STATUS
NUMBER,NAME VARCHAR2,DESCRIPTION VARCHAR2, ID_OLD NUMBER) is
  begin
    if DML = 'U' then
     execute immediate 'update ord_master set id = :id, ddate = :ddate,
status = :status, name = :name, description = '||''''||'from
ord_update_pkg'||''''||' where id = :id_old'
      using id,ddate,status,name,id_old;
    end if;
    if DML = 'I' then
 begin
      execute immediate 'insert into ord_master values(:id, :ddate,
:status, :name, '||''''||'from ord_update_pkg'||''''||')'
        using id,ddate,status,name;
 exception
  when others then
   null;
 end;
    end if;
    if DML = 'D' then
     execute immediate 'delete from ord_master where id = :id'
      using id;
    end if;
  end UPDATE_ORD_MASTER;
end ORD_UPDATE_PKG;
/



The API call to add this DML procedure is as follows:


consolidatorManager.addMobileDMLProcedure("T_SAMPLE11",           "P_SAMPLE11-M","SAMPLE11.ORD_UPDATE_PKG.UPDATE_ORD_MASTER")




where T_SAMPLE11 is the publication name and P_SAMPLE11-M is the publication item name.










3.3.1.13 Restricting Predicate


A restricting predicate can be assigned to a publication item as it is added to a publication.The predicate is used to limit data downloaded to the client. The parameter, which is for advanced use, can be null. When using a restricting predicate, the synchronization uses the high priority replication mode. For using a restricting predicate, see Section 16.1.3, "Priority-Based Replication".












3.4 Client Device Database DDL Operations


The first time a client synchronizes, Oracle Database Lite automatically creates the Oracle Lite database with the snapshot tables for the user subscriptions on the Mobile client. If you would like to execute additional DDL statements on the database, then add the DDL statements as part of your publication. Oracle Database Lite executes these DDL statements when the user synchronizes.


This is typically used for adding constraints and check values.


For example, you can add a foreign key constraint to a publication item. In this instance, if the Oracle Database Lite created snapshots S1 and S2 during the initial synchronization, where the definition of S1 and S2 are as follows:


S1 (C1 NUMBER PRIMARY KEY, C2 VARCHAR2(100), C3 NUMBER);
S2 (C1 NUMBER PRIMARY KEY, C2 VARCHAR2(100), C3 NUMBER);




If you would like to create a foreign key constraint between C3 on S2 and the primary key of S1 , then add the following DDL statement to your publication item:


ALTER TABLE S2
   ADD CONSTRAINT S2_FK FOREIGN KEY (C3)
   REFERENCES S1 (C1);




Then, Oracle Database Lite executes any DDL statements after the snapshot creation or, if the snapshot has already been created, after the next synchronization.


See the Oracle Database Lite API Specification for more information on these APIs.








3.5 Customize the Apply and Compose Phases Using the Consolidator Manager APIs


The following sections describe how you can customize the apply and compose phases of synchronization:






3.5.1 Compose Phase Customization Using MyCompose


The compose phase takes a query for one or more server-side base tables and puts the generated DML operations for the publication item into the Out Queue to be downloaded into the client. The Consolidator Manager manages all DML operations using the physical DML logs on the server-side base tables. This can be resource intensive if the DML operations are complex—for example, if there are complex data-subsetting queries being used. The tools to customize this process include an extendable MyCompose with compose methods which can be overridden, and additional Consolidator Manager APIs to register and load the customized class.


When you want to customize the compose phase of the synchronization process, you must perform the following:


    

  1. 

    Section 3.5.1.1, "Create a Class That Extends MyCompose to Perform the Compose"
    

    2. 

  2. 

    Section 3.5.1.2, "Implement the Extended MyCompose Methods in the User-Defined Class"
    

    3. 

  3. 

    Section 3.5.1.3, "Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class"
    

    4. 

  4. 

    Section 3.5.1.4, "Register the User-Defined Class With the Publication Item"
    

    5. 






3.5.1.1 Create a Class That Extends MyCompose to Perform the Compose


The MyCompose class is an abstract class, which serves as the super-class for creating a user-written sub-class, as follows:


public class ItemACompose extends oracle.lite.sync.MyCompose

{

...

}




All user-written classes—such as ItemACompose—produce publication item DML operations to be sent to a client device by interpreting the base table DML logs. The sub-class is registered with the publication item, and takes over all compose phase operations for that publication item. The sub-class can be registered with more than one publication item—if it is generic—however, internally the Composer makes each instance of the extended class unique within each publication item.








3.5.1.2 Implement the Extended MyCompose Methods in the User-Defined Class


The MyCompose class includes the following methods—needCompose, doCompose, init, and destroy—which are used to customize the compose phase. One or more of these methods can be overridden in the sub-class to customize compose phase operations. Most users customize the compose phase for a single client. In this case, only implement the doCompose and needCompose methods. The init and destroy methods are only used when a process is performed for all clients, either before or after individual client processing.



needCompose Method 


The needCompose method to identifies a client that has changes to a specific publication item that is to be downloaded. Use this method as a way to trigger the doCompose method.


public int needCompose(Connection conn,

  String clientid) throws Throwable




The parameters for the needCompose method are listed in Table 3-4:




Table 3-4 needCompose Parameters






Parameter
Definition






conn
Database connection to the Mobile Server repository.




clientid
Specifies the client that is connecting to the database.









The following example examines a client base table for changes—in this case, the presence of dirty records. If there are changes, then the method returns MyCompose.YES, which triggers the doCompose method.


    public int needCompose(String clientid) throws Throwable{

        boolean baseDirty = false;

        String [][] baseTables = this.getBaseTables();


        for(int i = 0; i < baseTables.length; i++){

            if(this.baseTableDirty(baseTables[i][0], baseTables[i][1])){

                baseDirty = true;

                break;

            }

        }


        if(baseDirty){

            return MyCompose.YES;

        }else{

            return MyCompose.NO;

        }

    }




This sample uses subsidiary methods discussed in Section 3.5.1.3, "Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class" to check if the publication item has any tables with changes that need to be sent to the client. In this example, the base tables are retrieved, then checked for changed, or dirty, records. If the result of that test is true, a value of Yes is returned, which triggers the call for the doCompose method.


doCompose Method


The doCompose method populates the DML log table for a specific publication item, which is subscribed to by a client.


public int doCompose(Connection conn,

   String clientid) throws Throwable




The parameters for the doCompose method are listed in Table 3-5:




Table 3-5 doCompose Parameters






Parameter
Definition






conn
Database connection to the Mobile Server repository.




clientid
Specifies the client that is connecting to the database.









The following example contains a publication item with only one base table where a DML (Insert, Update, or Delete) operation on the base table is performed on the publication item. This method is called for each client subscribed to the publication item.


 public int doCompose(Connection conn, String clientid) throws Throwable {

        int rowCount = 0;

        

        String [][] baseTables = this.getBaseTables();

        String baseTableDMLLogName = 

            this.getBaseTableDMLLogName(baseTables[0][0], baseTables[0][1]);

        String baseTablePK =

            this.getBaseTablePK(baseTables[0][0],baseTables[0][1]);

        String pubItemDMLTableName = this.getPubItemDMLTableName();


        String sql = "INSERT INTO " + pubItemDMLTableName 

            + " SELECT " +  baseTablePK + ", DMLTYPE$$ FROM " +
             baseTableDMLLogName;


        Statement st = conn.createStatement();

        rowCount = st.executeUpdate(sql);

        st.close();

        return rowCount;

    }




This code uses subsidiary methods discussed in Section 3.5.1.3, "Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class" to create a SQL statement. The MyCompose method retrieves the base table, the base table primary key, the base table DML log name and the publication item DML table name using the appropriate get methods. You can use the table names and other information returned by these methods to create a dynamic SQL statement, which performs an insert into the publication item DML table of the contents of the base table primary key and DML operation from the base table DML log.


init Method


The init method provides the framework for user-created compose preparation processes. The init method is called once for all clients prior to the individual client compose phase. The default implementation has no effect.


public void init(Connection conn)




The parameter for the init method is described in Table 3-6:




Table 3-6 init Parameters






Parameter
Definition






conn
Database connection to the Mobile Server repository.









destroy Method


The destroy method provides the framework for compose cleanup processes. The destroy method is called once for all clients after to the individual client compose phase. The default implementation has no effect.


public void destroy(Connection conn)




The parameter for the destroy method is described in Table 3-7:




Table 3-7 destroy Parameters






Parameter
Definition






conn
Database connection to the Mobile Server repository.














3.5.1.3 Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class


The following methods return information for use by primary MyCompose methods.



getPublication


The getPublication method returns the name of the publication.


public String getPublication()



getPublicationItem


The getPublicationItem method returns the publication item name.


public String getPublicationItem()



getPubItemDMLTableName


The getPubItemDMLTableName method returns the name of the DML table or DML table view, including schema name, which the doCompose or init methods are supposed to insert into.


public String getPubItemDMLTableName()




You can embed the returned value into dynamic SQL statements. The table or view structure is as follows:


<PubItem PK> DMLTYPE$$




The parameters for getPubItemDMLTableName are listed in Table 3-8:




Table 3-8 getPubItemDMLTableName View Structure Parameters






Parameter
Definition






PubItemPK
The value returned by getPubItemPK()




DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.









getPubItemPK


Returns the primary key for the listed publication in comma separated format in the form of <col1>,<col2>,<col3>.


public String getPubItemPK() throws Throwable



getBaseTables


Returns all the base tables for the publication item in an array of two-string arrays. Each two-string array contains the base table schema and name. The parent table is always the first base table returned, in other words, baseTables[0].


public string [][] getBaseTables() throws Throwable



getBaseTablePK


Returns the primary key for the listed base table in comma separated format, in the form of <col1>, col2>,<col3>.


public String getBaseTablePK (String owner, String baseTable) throws Throwable




The parameters for getBaseTablePK are listed in Table 3-9:




Table 3-9 getBaseTablePK Parameters






Parameter
Definition






owner
The schema name of the base table owner.




baseTable
The base table name.









baseTableDirty


Returns the a boolean value for whether or not the base table has changes to be synchronized.


public boolean baseTableDirty(String owner, String store)




The parameters for baseTableDirty are listed in Table 3-10:




Table 3-10 baseTableDirty Parameters






Parameter
Definition






owner
The schema name of the base table.




store
The base table name.









getBaseTableDMLLogName


Returns the name for the physical DML log table or DML log table view for a base table.


public string getBaseTableDMLLogName(String owner, String baseTable)




The parameters for getBaseTableDMLLogName are listed in Table 3-11:




Table 3-11 getBaseTableDMLLogName Parameters






Parameter
Definition






owner
The schema name of the base table owner.




baseTable
The base table name.









You can embed the returned value into dynamic SQL statements. There may be multiple physical logs if the publication item has multiple base tables. The parent base table physical primary key corresponds to the primary key of the publication item. The structure of the log is as follows:


<Base Table PK> DMLTYPE$$




The parameters for getBaseTableDMLLogName view structure are listed in Table 3-12:




Table 3-12 getBaseTableDMLLogName View Structure Parameters






Parameter
Definition






Base Table PK
The primary key of the parent base table.




DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.









getMapView


Returns a view of the map table which can be used in a dynamic SQL statement and contains a primary key list for each client device. The view can be an inline view.


public String getMapView() throws Throwable




The structure of the map table view is as follows:


CLID$$CS <Pub Item PK> DMLTYPE$$




The parameters of the map table view are listed in Table 3-13:




Table 3-13 getMapView View Structure Parameters






Parameter
Definition






CLID$$CS
This is the client ID column.




Base Table PK
The primary key columns of the publication item.




DMLTYPE$$
This can have the values 'I' for insert, 'D' for delete, or 'U' for Update.














3.5.1.4 Register the User-Defined Class With the Publication Item


Once you have created your sub-class, it must be registered with a publication item. The Consolidator Manager API now has two methods registerMyCompose and deRegisterMyCompose to permit adding and removing the sub-class from a publication item.


    

  • 

    The registerMyCompose method registers the sub-class and loads it into the Mobile Server repository, including the class byte code. By loading the code into the repository, the sub-class can be used without having to be loaded at runtime.
    

    • 

  • 

    The deRegisterMyCompose method removes the sub-class from the Mobile Server repository.
    

    • 













3.6 Customize What Occurs Before and After Synchronization Phases


You can customize what happens before and after certain synchronization processes by creating one or more PL/SQL packages. The following sections detail the different options you have for customization:






3.6.1 Customize What Occurs Before and After Every Phase of Each Synchronization


You can customize the synchronization process through a set of predefined callback methods that add functionality to be executed before or after certain phases of the synchronization process. These callback methods are defined in the CUSTOMIZE PL/SQL package. Note that these callback methods are called before or after the defined phase for every publication item. If you want to customize certain activity for only a specific publication item, see Section 3.6.2, "Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item" for more information.


Manually create this package in the Mobile Server repository. The methods and their respective calling sequence are as follows:






3.6.1.1 NullSync


The NullSync procedure is called at the beginning of every synchronization session. It can be used to determine whether or not a particular user is uploading data.


procedure NullSync (clientid varchar2, isNullSync boolean);







3.6.1.2 BeforeProcessApply


The BeforeProcessApply procedure is called before the entire apply phase of the MGP process.


procedure BeforeProcessApply;







3.6.1.3 AfterProcessApply


The AfterProcessApply procedure is called after the entire apply phase of the MGP process.


procedure AfterProcessApply;







3.6.1.4 BeforeProcessCompose


The BeforeProcessCompose procedure is called before the entire compose phase of the MGP process.


procedure BeforeProcessCompose;







3.6.1.5 AfterProcessCompose


The AfterProcessCompose procedure is called after the entire compose phase of the MGP process.


procedure AfterProcessCompose;







3.6.1.6 BeforeProcessLogs


The BeforeProcessLogs procedure is called before the logs are generated for the compose phase of the MGP process.


procedure BeforeProcessLogs;







3.6.1.7 AfterProcessLogs


The AfterProcessLogs procedure is called after the logs are generated for the compose phase of the MGP process.


procedure AfterProcessLogs;







3.6.1.8 BeforeClientCompose


The BeforeClientCompose procedure is called before each user is composed during the compose phase of the MGP process.


procedure BeforeClientCompose (clientid varchar2);







3.6.1.9 AfterClientCompose


The AfterClientCompose procedure is called after each user is composed during the compose phase of the MGP process.


procedure AfterClientCompose (clientid varchar2);







3.6.1.10 Example Using the Customize Package


If a developer wants to use any of the procedures listed above, perform the following:


    

  • 

    Manually create the CUSTOMIZE package in the Mobile Server schema.
    

    • 

  • 

    Define all of the methods with the following specification:
    

    create or replace package CUSTOMIZE as 
    procedure NullSync (clientid varchar2, isNullSync boolean); 
    procedure BeforeProcessApply ; 
    procedure AfterProcessApply ; 
    procedure BeforeProcessCompose ; 
    procedure AfterProcessCompose ; 
    procedure BeforeProcessLogs ; 
    procedure AfterProcessLogs ; 
    procedure BeforeClientCompose(clientid varchar2); 
    procedure AfterClientCompose(clientid varchar2); 
    end CUSTOMIZE; 

    • 














WARNING:


It is the developer's responsibility to ensure that the package is defined properly and that the logic contained does not jeopardize the integrity of the synchronization process.




















3.6.2 Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item


When creating publication items, the user can define a customizable PL/SQL package that MGP calls during the Apply and Compose phase of the MGP background process. After you create the PL/SQL package and register it (with the publication item), then when the publication item is being processed, MGP calls the appropriate procedures from your package.


Client data is accumulated in the in queue prior to being processed by the MGP. Once processed by the MGP, data is accumulated in the out queue before being pulled to the client by Mobile Sync.


You can implement the following PL/SQL procedures to incorporate customized code into the MGP process. The clientname and tranid are passed to allow for customization at the user and transaction level.


    

  • 

    The BeforeApply method is invoked before the client data is applied:
    

    procedure BeforeApply(clientname varchar2)


    • 

  • 

    The AfterApply method is invoked after all client data is applied.
    

    procedure AfterApply(clientname varchar2)


    • 

  • 

    The BeforeTranApply method is invoked before the client data with tranid is applied.
    

    procedure BeforeTranApply(tranid number)


    • 

  • 

    The AfterTranApply method is invoked after all client data with tranid is applied.
    

    procedure AfterTranApply(tranid number)


    • 

  • 

    The BeforeCompose method is invoked before the out queue is composed.
    

    procedure BeforeCompose(clientname varchar2)


    • 

  • 

    The AfterCompose method is invoked after the out queue is composed.
    

    procedure AfterCompose(clientname varchar2)


    • 



The following is a PL/SQL example that creates a callback package and registers it when creating the P_SAMPLE3 publication item. The BeforeApply procedure disables constraints before the apply phase; the AfterApply procedure enables these constraints. Even though you are only creating procedures for the before and after apply phase of the MGP process, you still have to provide empty procedures for the other parts of the MGP process.


    

  1. 

    Create PL/SQL package declaration with callback owner/schema name of SAMPLE3 and callback package name of SAMP3_PKG.
    

    2. 

  2. 

    Create the package definition, with all MGP process procedures with callback owner.callback package name of SAMPLE3.SAMP3_PKG. Provide a null procedure for any procedure you do not want to modify.
    

    3. 

  3. 

    Register the package as the callback package for the SAMPLE3 publication item. If you are creating the publication item, provide the callback schema/owner and the callback package names as input parameters to the createPublicationItem method. If you want to add the callback package to an existing publication item, do the following:
    

      

    1. 

      Retrieve the template meta data with getTemplateItemMetaData for the publication item.
      

      2. 

    2. 

      Modify the attributes that specify the callback owner/schema (cbk_owner) and the callback package (cbk_name).
      

      3. 

    3. 

      Register the package by executing the setTemplateItemMetaData method.
      

      4. 

    

    4. 



// create package declaration
  stmt.executeUpdate("CREATE OR REPLACE PACKAGE SAMPLE3.SAMP3_PKG as"
  + " procedure BeforeCompose(clientname varchar2);"
  + " procedure AfterCompose(clientname varchar2);"
  + " procedure BeforeApply(clientname varchar2);"
  + " procedure AfterApply(clientname varchar2);"
  + " procedure BeforeTranApply(tranid number);"
  + " procedure AfterTranApply(tranid number);"
  + " end;"
  );
// create package definition
  stmt.executeUpdate("CREATE OR REPLACE PACKAGE body SAMPLE3.SAMP3_PKG as"
  + " procedure BeforeTranApply(tranid number) is"
  + " begin"
    + " null;"
  + " end;"
  + " procedure AfterTranApply(tranid number) is"
  + " begin"
    + " null;"
  + " end;"
  + " procedure BeforeCompose(clientname varchar2) is"
  + " begin"
        + "   null;"
  + " end;"
  + " procedure AfterCompose(clientname varchar2) is"
  + " begin"
  + "   null;"
  + " end;"
  + " procedure BeforeApply(clientname varchar2) is"
  + "   cur integer;"
  + "   ign integer;"
  + "   begin"
  + "     cur := dbms_sql.open_cursor;"
  + "     dbms_sql.parse(cur,'SET CONSTRAINT SAMPLE3.address14_fk DEFERRED',                              dbms_sql.native);"
  + "     ign := dbms_sql.execute(cur);"
  + "     dbms_sql.close_cursor(cur);"
  + "   end;"
  + " procedure AfterApply(clientname varchar2) is"
  + "   cur integer;"
  + "   ign integer;"
  + "   begin"
  + "     cur := dbms_sql.open_cursor;"
  + "     dbms_sql.parse(cur, 'SET CONSTRAINT SAMPLE3.address14_fk IMMEDIATE',                               dbms_sql.native);"
  + "     ign := dbms_sql.execute(cur);"
  + "     dbms_sql.close_cursor(cur);"
  + "   end;"
  + " end;"
  );




Then, register the callback package with the createPublicationItem method call, as follows:


// register SAMPLE3.SAMP3_PKG as the callback for MGP processing of 
// P_SAMPLE3 publication item.
 
cm.createPublicationItem("P_SAMPLE3","SAMPLE3","ADDRESS", "F",
    "SELECT * FROM SAMPLE3.ADDRESS", "SAMPLE3", "SAMP3_PKG");




In the previous code example, the following is required:


    

  • 

    stmt, which is used when creating the package definition, is an instance of java.sql.Statement
    

    • 

  • 

    cm, which is used when registering the callback package, is an instance of oracle.lite.sync.ConsolidatorManager
    

    • 

  • 

    The callback package must have the following procedures defined:
    

      

    • 

      BeforeCompose (clientname varchar2);
      

      • 

    • 

      AfterCompose (clientname varchar2);
      

      • 

    • 

      BeforeApply (clientname varchar2);
      

      • 

    • 

      AfterApply (clientname varchar2);
      

      • 

    • 

      BeforeTranApply (tranid number);
      

      • 

    • 

      AfterTranApply (tranid number);
      

      • 

    

    • 











3.7 Client Synchronization


You can modify the client-side application to start the synchronization programmatically, which is discussed in the following sections:






3.7.1 Performing Synchronization Upload and Download Phases for the Client Using Mobile Sync APIs


To execute the upload portion of synchronization from the client (see steps 1 and 2 in ) from within your C, C++, or Java application, use the Mobile Sync APIs.













Note:

To activate synchronization within a Palm OS application, use the SODA API (see Chapter 12, "Using Simple Object Data Access (SODA) for PalmOS and PocketPC Platforms"). Currently, there are no APIs to perform the upload activity on the UNIX platforms.		










To start the upload, perform the following steps:


    

  1. 

    Initialize the synchronization parameters.
    

    2. 

  2. 

    Set up the transport parameters.
    

    3. 

  3. 

    Initialize the synchronization options and environment, such as username, password, and selective synchronization.
    

    4. 

  4. 

    Perform the synchronization.
    

    5. 



The following sections demonstrates how you can perform these steps in each of the allowed programming languages:






3.7.1.1 Starting Synchronization Upload and Download Phases With the COM Interface


You can perform the upload phase of the synchronization process in your application with the COM interface—which uses the mSync_com.dll and ocapi.dll libraries. You can use COM interface languages—such as Visual Basic and VBScript—when implementing your application. See Section 4.1, "Synchronization API For the COM Interface" for full details.








3.7.1.2 Starting Synchronization Upload and Download Phases With C or C++ Applications


You can initiate and monitor synchronization from a C or C++ client application. The synchronization methods for the C/C++ interface are contained in ocapi.h and ocapi.dll, which are located in the <ORACLE_HOME>\Mobile\bin directory. See Section 4.2, "Synchronization APIs For C or C++ Applications" for full details.








3.7.1.3 Starting Synchronization Upload and Download Phases With Java Applications


You can initiate and monitor synchronization from a Java client application. See Section 4.3, "Synchronization API for Java Applications" for more information.








3.7.1.4 Starting Synchronization Upload and Download Phases With the ADO.NET Provider


You can initiate and monitor synchronization from an ADO.NET provider application. See Section 14.1.6, "Data Synchronization With the OracleSync Class" for full details.










3.7.2 Using Mobile Sync for Palm


You can modify how to synchronize data using Mobile Sync (msync) for Palm between the Palm pilot and the Mobile Server. The user can run the application (which can be found in Oracle Database Lite program group) manually and configure various settings. See Section 13.11, "Using Mobile Sync for Palm" for alternate methods to invoke sync with pre-configured settings:










3.8 Understanding Your Refresh Options 


The Mobile Server supports several refresh options. During a fast refresh, incremental changes are synchronized. However, during a complete refresh, all data is refreshed with current data. The refresh mode is established when you create the publication item using the createPublicationItem API call. In order to change the refresh mode, first drop the publication item and recreate it with the appropriate mode.


There are basically three types of refresh for your publication item that can be used to define how to synchronize, as follows:


    

  • 

    Fast Refresh: The most common method of synchronization is a fast refresh publication item where changes are uploaded by the client, and changes for the client are downloaded. Meanwhile, the MGP periodically collects the changes uploaded by all clients and applies them to database tables. It then composes new data, ready to be downloaded to each client during the next synchronization, based on predefined subscriptions.
    

    • 

  • 

    Complete Refresh: During a complete refresh, all data for a publication is downloaded to the client. For example, during the very first synchronization session, all data on the client is refreshed from the Oracle database. This form of synchronization takes longer because all rows that qualify for a subscription are transferred to the client device, regardless of existing client data.
    

    • 

  • 

    Queue-Based: The developer creates their own queues to handle the synchronization data transfer. This can be considered the most basic form of publication item, for the simple reason that there is no synchronization logic created with it. The synchronization logic is left entirely in the hands of the developer. A queue-based publication item is ideally suited for scenarios that do not require actual synchronization but require something somewhere in between. For instance, data collection on the client. With data collection, there is no need to worry about conflict detection, client state information, or server-side updates. Therefore, there is no need to add the additional overhead normally associated with a fast refresh or complete refresh publication item.
    

    • 



The following sections describe the refresh options in more detail:






3.8.1 Fast Refresh


Publication items are created for fast refresh by default. Under fast refresh, only incremental changes are replicated. The advantages of fast refresh are reduced overhead and increased speed when replicating data stores with large amounts of data where there are limited changes between synchronization sessions.


The Mobile Server performs a fast refresh of a view if the view meets the following criteria:


    

  • 

    Each of the view base tables must have a primary key.
    

    • 

  • 

    All primary keys from all base tables must be included in the view column list.
    

    • 

  • 

    If the item is a view, and the item predicate involves multiple tables, then all tables contained in the predicate definition must have primary keys and must have corresponding publication items.
    

    • 



The view requires only a unique primary key for the parent table. The primary keys of other tables may be duplicated. For each base table primary key column, you must provide the Mobile Server with a hint about the column name in the view. You can accomplish this by using the primaryKeyHint method of the Consolidator Manager object. See the Javadoc in the Oracle Database Lite API Specification for more information.








3.8.2 Complete Refresh for Views


A complete refresh is simply a complete execution of the snapshot query. When application synchronization performance is slow, tune the snapshot query. Complete refresh is not optimized for performance. Therefore, to improve performance, use the fast refresh option. The Consperf utility analyzes only fast refresh publication items.


Publication items can be created for complete refresh using the C refresh mode in the createPublicationItem API from the Consolidator Manager API. When this mode is specified, client data is completely refreshed with current data from the server after every sync. An administrator can force a complete refresh for an entire publication through an API call. This function forces complete refresh of a publication for a given client.


See the Javadoc in the Oracle Database Lite API Specification for more information.


The following lists what can cause a complete refresh, ordered from most likely to least likely:


    

  1. 

    The same Mobile user synching from multiple devices on the same platform, or synching from different platforms when the publications are not platform specific.
    

    2. 

  2. 

    Republishing the application.
    

    3. 

  3. 

    An unexpected server apply condition, such as constraint violations, unresolved conflicts, and other database exceptions.
    

    4. 

  4. 

    Modifying the application, such as changing subsetting parameters or adding/altering publication items. This refresh only affects the publication items.
    

    5. 

  5. 

    A force refresh requested by server administrator or a force refresh requested by the client.
    

    6. 

  6. 

    Restoring an old Oracle Lite database (ODB file).
    

    7. 

  7. 

    Two separate applications using the same backend store.
    

    8. 

  8. 

    An unexpected client apply conditions, such as a moved or deleted database, database corruption, memory corruption, other general system failures.
    

    9. 

  9. 

    Loss of transaction integrity between the server and client. The server fails post processing after completing the download and disconnects from the client.
    

    10. 

  10. 

    Data transport corruptions.
    

    11. 









3.8.3 Queue-Based Refresh


You can create your own queue. Mobile Server uploads and downloads changes from the user. Perform customized apply/compose modifications to the back-end database with your own implementation. See the Section 3.15, "Customizing Replication With Your Own Queues" for more information.










3.9 Synchronizing With Database Constraints


When you have database constraints on your table, you must develop your application in a certain way to facilitate the synchronization of the data and keeping the database constraints. The following sections detail each constraint and what issues you must take into account:






3.9.1 Synchronization And Database Constraints


Oracle Database Lite does not keep a record of the SQL operations executed against the database; instead, only the final changes are saved and synchronized to the back-end database.


For example, if you have a client with a unique key constraint, where the following is executed against the client Oracle Lite database:


    

  1. 

    Record with primary key of one and unique field of ABC is deleted.
    

    2. 

  2. 

    Record with primary key of 4 and unique field of ABC is inserted.
    

    3. 



When this is synchronized, according the Section 3.3.1.6.2, "Using Table Weight" discussion, the insert is performed before the delete. This would add a duplicate field for ABC and cause a unique key constraint violation. In order to avoid this, you should defer all constraint checking until after all transactions are applied. See Section 3.9.3.2, "Defer Constraint Checking Until After All Transactions Are Applied".


Another example of how synchronization captures the end result of all SQL modifications is as follows:


    

  1. 

    Insert an employee record 4 with name of Joe Judson.
    

    2. 

  2. 

    Update employee record 4 with address.
    

    3. 

  3. 

    Update employee record 4 with salary.
    

    4. 

  4. 

    Update employee record 4 with office number
    

    5. 

  5. 

    Update employee record 4 with work email address.
    

    6. 



When synchronization occurs, all modifications are captured and only a single insert is performed on the back-end database. The insert contains the primary key, name, address, salary, office number and email address. Even though the data was created with multiple updates, the Synch Server only takes the final result and makes a single insert.








3.9.2 Primary Key is Unique


When you have multiple clients, each updating the same table, you must have a method for guaranteeing that the primary key is unique across all clients. Oracle Database Lite provides you a sequence number that you can use as the primary key, which is guaranteed to be unique across all Oracle Database Lite clients.


For more information on the sequence number, see Section 3.3.1.7, "Creating Client-Side Sequences for the Downloaded Snapshot".








3.9.3 Foreign Key Constraints


A foreign key exists in a details table and points to a row in the master table. Thus, before a client adds a record to the details table, the master table must first exist.


For example, two tables EMP and DEPT have referential integrity constraints and are an example of a master-detail relationship. The DEPT table is the master table; the EMP table is the details table. The DeptNo field (department number) in the EMP table is a foreign key that points to the DeptNo field in the DEPT table. The DeptNo value for each employee in the EMP table must be a valid DeptNo value in the DEPT table.


When a user adds a new employee, first the employee's department must exist in the DEPT table. If it does not exist, then the user first adds the department in the DEPT table, and then adds a new employee to this department in the EMP table. The transaction first updates DEPT and then updates the EMP table. However, Oracle Database Lite does not store the sequence in which these operations were executed.


Oracle Database Lite does not keep a record of the SQL operations executed against the database; instead, only the final changes are saved and synchronized to the back-end database. For our employee example, when the user replicates with the Mobile Server, the Mobile Server could initiate the updates the EMP table first. If this occurs, then it attempts to create a new record in EMP with an invalid foreign key value for DeptNo. Oracle database detects a referential integrity violation. The Mobile Server rolls back the transaction and places the transaction data in the Mobile Server error queue. In this case, the foreign key constraint violation occurred because the operations within the transaction are performed out of their original sequence.


In order to avoid this violation, you can do one of two things:






3.9.3.1 Set Update Order for Tables With Weights


Set the order in which tables are updated on the back-end Oracle database with weights. To avoid integrity constraints with a master-details relationship, the master table must always be updated first in order to guarantee that it exists before any records are added to a details table. In our example, you must set the DEPT table with a lower weight than the EMP table to ensure that all records are added to the DEPT table first.


You define the order weight for tables when you add a publication item to the publication. For more information on weights, see Section 3.3.1.6.2, "Using Table Weight".








3.9.3.2 Defer Constraint Checking Until After All Transactions Are Applied


You can use a PL/SQL procedure avoid foreign key constraint violations based on out-of-sequence operations by using DEFERRABLE constraints in conjunction with the BeforeApply and AfterApply functions. DEFERRABLE constraints can be either INITIALLY IMMEDIATE or INITIALLY DEFERRED. The behavior of DEFERRABLE INITIALLY IMMEDIATE foreign key constraints is identical to regular immediate constraints. They can be applied interchangeably to applications without impacting functionality.


The Mobile Server calls the BeforeApply function before it applies client transactions to the server and calls the AfterApply function after it applies the transactions. Using the BeforeApply function, you can set constraints to DEFFERED to delay referential integrity checks. After the transaction is applied, call the AfterApply function to set constraints to IMMEDIATE. At this point, if a client transaction violates referential integrity, it is rolled back and moved into the error queues.


To prevent foreign key constraint violations using DEFERRABLE constraints:


    

  1. 

    Drop all foreign key constraints and then recreate them as DEFERRABLE constraints.
    

    2. 

  2. 

    Bind user-defined PL/SQL procedures to publications that contain tables with referential integrity constraints. 
    

    3. 

  3. 

    The PL/SQL procedure should set constraints to DEFERRED in the BeforeApply function and IMMEDIATE in the AfterApply function as in the following example featuring a table named SAMPLE3 and a constraint named address.14_fk:
    

    4. 



     procedure BeforeApply(clientname varchar2) is

     cur integer;

     begin

       cur := dbms_sql.open_cursor;

       dbms_sql.parse(cur,'SET CONSTRAINT SAMPLE3.address14_fk

                       DEFERRED', dbms_sql.native);

       dbms_sql.close_cursor(cur);

     end;

     procedure AfterApply(clientname varchar2) is

     cur integer;

     begin

       cur := dbms_sql.open_cursor;

       dbms_sql.parse(cur, 'SET CONSTRAINT SAMPLE3.address14_fk

                       IMMEDIATE', dbms_sql.native);

       dbms_sql.close_cursor(cur);

     end;









3.9.4 Unique Key Constraint


A unique key constraint enforces uniqueness of data. However, you may have multiple clients across multiple devices updating the same table. Thus, a record may be unique on a single client, but not across all clients. Enforcing uniqueness is the customer's reponsibility and depends on the data.


How do you guarantee that the records added on separate clients are unique? You can use the sequence numbers generated on the client by Oracle Database Lite. See Section 3.3.1.7, "Creating Client-Side Sequences for the Downloaded Snapshot" for more information.








3.9.5 Not Null Constraint


When you have a not null constraint on the client or on the server, you must ensure that this constraint is also on the table on the other side. If you execute the setPubItemColOption method in the ConsolidatorManager API, you can set a column as not null—whether it is set with the not null constraint in the server table or not. Provide Consolidator.NOT_NULL as the input parameter for nullType. The constraint is then enforced on the table in the client Oracle Lite database. It is not enforced in the server back-end database tables. You must define this constraint explicitly on the server table.










3.10 Parent Tables Needed for Updateable Views


For a view to be updatable, it must have a parent table. A parent table can be any one of the view base tables in which a primary key is included in the view column list and is unique in the view row set. If you want to make a view updatable, provide the Mobile Server with the appropriate hint and the view parent table before you create a publication item on the view.


To make publication items based on a updatable view, use the following two mechanisms:


    

  • 

    Parent table hints
    

    • 

  • 

    INSTEAD OF triggers or DML procedure callouts
    

    • 





3.10.1 Creating a Parent Hint


Parent table hints define the parent table for a given view. Parent table hints are provided through the parentHint method of the Consolidator Manager object, as follows:


consolidatorManager.parentHint("SAMPLE3","ADDROLRL4P","SAMPLE3","ADDRESS");




See the Javadoc in the Oracle Database Lite API Specification for more information.







3.10.2 INSTEAD OF Triggers


INSTEAD OF triggers are used to execute INSTEAD OF INSERT, INSTEAD OF UPDATE, or INSTEAD OF DELETE commands. INSTEAD OF triggers also map these DML commands into operations that are performed against the view base tables. INSTEAD OF triggers are a function of the Oracle database. See the Oracle database documentation for details on INSTEAD OF triggers.










3.11 Manipulating Application Tables


If you need to manipulate the application tables to create a secondary index or a virtual primary key, you can use ConsolidatorManager methods to programmatically perform these tasks in your application, as described in the following sections:






3.11.1 Creating Secondary Indexes on Client Device


The first time a client synchronizes, the Mobile Server automatically enables a Mobile client to create the database objects on the client in the form of snapshots. By default, the primary key index of a table is automatically replicated from the server. You can create secondary indexes on a publication item through the Consolidator Manager APIs. See the Oracle Database Lite API Javadoc for specific API information. See Section 3.3.1.5, "Create Publication Item Indexes" for an example.








3.11.2 Virtual Primary Key


You can specify a virtual primary key for publication items where the base object does not have a primary key defined. This is useful if you want to create a fast refresh publication item on a table that does not have a primary key.


A virtual primary key can be created for more than one column, but the API must be called separately for each column that you wish to assign a virtual primary key. The following methods create and drop a virtual primary key.


Use the createVirtualPKColumn method to create a virtual primary key column.


consolidatorManager.createVirtualPKColumn("SAMPLE1", "DEPT", "DEPT_ID"); 




Use the dropVirtualPKColumns method to drop a virtual primary key.


consolidatorManager.dropVirtualPKColumns("SAMPLE1", "DEPT"); 














Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		


















3.12 Set DBA or Operational Privileges for the Mobile Server


You can set either DBA or operational privileges for the Mobile Server with the following Consolidator Manager API:


void setMobilePrivileges( String dba_schema, String dba_pass, int type )
        throws ConsolidatorException




where the input parameter are as follows:


    

  • 

    dba_schema—The DBA schema name
    

    • 

  • 

    dba_pass—The DBA password
    

    • 

  • 

    type—Define the user by setting this parameter to either Consolidator.DBA or Consolidator.OPER
    

    • 



If you specify Consolidator.OPER type, then the privileges needed are those necessary for executing the Mobile Server without any schema modifications. The OPER is given access to publication item base objects, version, log, and error queue tables.


The Mobile Server privileges are modified using the C$MOBILE_PRIVILEGES PL/SQL package, which is created for you automatically after the first time you use the setMobilePrivileges procedure. After the package is created, the Mobile Server privileges can be administered from SQL or from this Java API.








3.13 Create a Synonym for Remote Database Link Support For a Publication Item


Publication items can be defined for database objects existing on remote database instances outside of the Mobile Server repository. Local private synonyms of the remote objects should be created in the Oracle database. Execute the following SQL script located in the <ORACLE_HOME>\Mobile\server\admin\consolidator_rmt.sql directory on the remote schema in order to create Consolidator Manager logging objects.


The synonyms should then be published using the createPublicationItem method of the ConsolidatorManager object. If the remote object is a view that needs to be published in updatable mode and/or fast-refresh mode, the remote parent table must also be published locally. Parent hints should be provided for the synonym of the remote view similar those used for local, updatable and/or fast refreshable views.


Two additional methods have been created, dependencyHint and removeDependencyHint, to deal with non-apparent dependencies introduced by publication of remote objects.


Remote links to the Oracle database must be established prior to attempting remote linking procedures, please refer to the Oracle SQL Reference for this information.













Note:

The performance of synchronization from remote databases is subject to network throughput and the performance of remote query processing. Because of this, remote data synchronization is best used for simple views or tables with limited amount of data.		










The following sections describe how to manage remote links:






3.13.1 Publishing Synonyms for the Remote Object Using CreatePublicationItem


The createPublicationItem method creates a new, stand-alone publication item as a remote database object. If the URL string is used, the remote connection is established and closed automatically. If the connection is null or cannot be established, an exception is thrown. The remote connection information is used to create logging objects on the linked database and to extract metadata.













Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		










consolidatorManager.createPublicationItem(

     "jdbc:oracle:oci8:@oracle.world",

     "P_SAMPLE1",

     "SAMPLE1",

     "PAYROLL_SYN",

     "F"

     "SELECT * FROM sample1.PAYROLL_SYN"+"WHERE SALARY >:CAP", null, null);












Note:

Within the select statement, the parameter name for the data subset must be prefixed with a colon, for example :CAP.		














3.13.2 Creating or Removing a Dependency Hint


Use the dependencyHint method to create a hint for a non-apparent dependency.


Given remote view definition

        create payroll_view as

        select p.pid, e.name 

        from payroll p, emp e

        where p.emp_id = e.emp_id;


Execute locally

        create synonym v_payroll_syn for payroll_view@<remote_link_address>;

        create synonym t_emp_syn for emp@<remote_link_address>;




Where <remote_link_address> is the link established on the Oracle database. Use dependencyHint to indicate that the local synonym v_payroll_syn depends on the local synonym t_emp_syn:


consolidatorManager.dependencyHint("SAMPLE1","V_PAYROLL_SYN","SAMPLE1","T_EMP_SYN");




Use the removeDependencyHint method to remove a hint for a non-apparent dependency.













Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		


















3.14 Using the Sync Discovery API to Retrieve Statistics


The sync discovery feature is used to request an estimate of the size of the download for a specific client, based on historical data. The following statistics are gathered to maintain the historical data:


    

  • 

    The total number of rows send for each publication item.
    

    • 

  • 

    The total data size for these rows.
    

    • 

  • 

    The compressed data size for these rows.
    

    • 



The following sections contain methods that can be used to gather statistics:






3.14.1 getDownloadInfo Method


The getDownloadInfo method returns the DownloadInfo object. The DownloadInfo object contains a set of PublicationSize objects and access methods. The PublicationSize objects carry the size information of a publication item. The method Iterator iterator() can then be used to view each PublicationSize object in the DownloadInfo object.


DownloadInfo dl = consolidatorManager.getDownloadInfo("S11U1", true, true);














Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		
















3.14.2 DownloadInfo Class Access Methods


The access methods provided by the DownloadInfo class are listed in Table 3-14:




Table 3-14 DownloadInfo Class Access Methods 






Method
Definition






iterator
Returns an Iterator object so that the user can traverse through the all the PublicationSize objects that are contained inside the DownloadInfo object.




getTotalSize
Returns the size information of all PublicationSize objects in bytes, and by extension, the size of all publication items subscribed to by that user. If no historical information is available for those publication items, the value returned is '-1'.




getPubSize
Returns the size of all publication items that belong to the publication referred to by the string pubName. If no historical information is available for those publication items, the value returned is '-1'.




getPubRecCount
Returns the number of all records of all the publication items that belong to the publication referred by the string pubName, that will be synchronization during the next synchronization.




getPubItemSize
Returns the size of a particular publication item referred by pubItemName. It follows the following rules in order.

    

  1. 

    If the publication item is empty, it will return '0'.
    

    2. 

  2. 

    If no historical information is available for those publication items, it will return '-1'.
    

    3. 







getPubItemRecCount
Returns the number of records of the publication item referred by pubItemName that will be synced in the next synchronization.




















Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		
















3.14.3 PublicationSize Class


The access methods provided by the PublicationSize class are listed inTable 3-15:




Table 3-15 PublicationSize Class Access Methods






Parameter
Definition






getPubName
Returns the name of the publication containing the publication item.




getPubItemName
Returns the name of the publication item referred to by the PublicationSize object.




getSize
Returns the total size of the publication item referred to by the PublicationSize object.




getNumOfRows
Returns the number of rows of the publication item that will be synchronized in the next synchronization.




















Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		










Sample Code


import   java.sql.*;
import   java.util.Iterator;
import   java.util.HashSet;

 

import   oracle.lite.sync.ConsolidatorManager;

import   oracle.lite.sync.DownloadInfo;

import   oracle.lite.sync.PublicationSize;

 

public class TestGetDownloadInfo{

 

   public static void main(String argv[]) throws Throwable

   {

// Open Consolidator Manager connection

      try

      {

// Create a ConsolidatorManager object

         ConsolidatorManager cm = new ConsolidatorManager ();

// Open a Consolidator Manager connection
         cm.openConnection ("MOBILEADMIN", "MANAGER",

                      "jdbc:oracle:thin:@server:1521:orcl", System.out);

// Call getDownloadInfo

         DownloadInfo dlInfo = cm.getDownloadInfo ("S11U1", true, true);

// Call iterator for the Iterator object and then we can use that to transverse

// through the set of PublicationSize objects.

         Iterator it = dlInfo.iterator ();         

// A temporary holder for the PublicationSize object.

         PublicationSize ps = null;

// A temporary holder for the name of all the Publications in a HashSet object.

         HashSet pubNames = new HashSet ();         

// A temporary holder for the name of all the Publication Items in a HashSet 

// object.

         HashSet pubItemNames = new HashSet ();         

// Traverse through the set.

         while (it.hasNext ())

         {

// Obtain the next PublicationSize object by calling next ().

            ps = (PublicationSize)it.next ();            

 

// Obtain the name of the Publication this PublicationSize object is associated

// with by calling getPubName ().

            pubName = ps.getPubName ();

            System.out.println ("Publication: " + pubName);

 

// We save pubName for later use.

            pubNames.add (pubName);

 

// Obtain the Publication name of it by calling getPubName ().

            pubItemName = ps.getPubItemName ();

            System.out.println ("Publication Item Name: " + pubItemName);

            

// We save pubItemName for later use.

            pubItemNames.add (pubItemName);

            

// Obtain the size of it by calling getSize ().

            size = ps.getSize ();

            System.out.println ("Size of the Publication: " + size);

            

// Obtain the number of rows by calling getNumOfRows ().

            numOfRows = ps.getNumOfRows ();

            System.out.println ("Number of rows in the Publication: "

                                + numOfRows);

         }

         

// Obtain the size of all the Publications contained in the 

// DownloadInfo objects.

         long totalSize = dlInfo.getTotalSize ();

         System.out.println ("Total size of all Publications: " + totalSize);

 

// A temporary holder for the Publication size.

         long pubSize = 0;

         

// A temporary holder for the Publication number of rows.

         long pubRecCount = 0;

         

// A temporary holder for the name of the Publication.

         String tmpPubName = null;

         

// Transverse through the Publication names that we saved earlier.

         it = pubNames.iterator ();

         while (it.hasNext ())

         {

// Obtain the saved name.

            tmpPubName = (String) it.next ();

            

// Obtain the size of the Publication.

            pubSize = dlInfo.getPubSize (tmpPubName);

            System.out.println ("Size of " + tmpPubName + ": " + pubSize);

            

// Obtain the number of rows of the Publication.

            pubRecCount = dlInfo.getPubRecCount (tmpPubName);

            System.out.println ("Number of rows in " + tmpPubName + ": " 

                                + pubRecCount);

         }

         

// A temporary holder for the Publication Item size.

         long pubItemSize = 0;

         

// A temporary holder for the Publication Item number of rows.

         long pubItemRecCount = 0;

 

// A temporary holder for the name of the Publication Item.

         String tmpPubItemName = null;

         

// Traverse through the Publication Item names that we saved earlier.

         it = pubItemNames.iterator ();

         while (it.hasNext ())

         {

// Obtain the saved name.

            tmpPubItemName = (String) it.next ();

            

// Obtain the size of the Publication Item.

            pubItemSize = dlInfo.getPubItemSize (tmpPubItemName);

            System.out.println ("Size of " + pubItemSize + ": " + pubItemSize);

 

// Obtain the number of rows of the Publication Item.

            pubItemRecCount = dlInfo.getPubItemRecCount (tmpPubItemName);

            System.out.println ("Number of rows in " + tmpPubItemName + ": " 

                               + pubItemRecCount);

         }

         System.out.println ();

         

// Close the connection
         cm.closeConnection ();

      }

      catch (Exception e)

      {

         e.printStackTrace();

      }      

   }}









3.15 Customizing Replication With Your Own Queues


Application developers can manage the replication process programmatically by using queue-based publication items, as described in Section 3.15.4, "Create a Publication Item as a Queue". Normally the MGP manages both the in queues and the out queues, this API allows the application developer to manage queue operations during a synchronization session using a PL/SQL package described in Section 3.15.3, "Queue Interface PL/SQL Procedure" and by creating the queues themselves.




3.15.1 Queue Interface Operation


When data arrives from the client it is placed in the publication item in queues. The Sync Server calls UPLOAD_COMPLETE once the data has been committed. All records in the current synchronization session are given the same transaction identifier. Data Synchronization has a Queue Control Table (C$INQ) that indicates which publication item in queues have received new transactions using this transaction identifier. You can refer to this table to determine which queues need processing.


Before the Sync Server begins the download phase of the synchronization session, it calls DOWNLOAD_INIT. This procedure allows customization of any settings which need to be set or modified to determine which data is sent to the client. The Sync Server finds a list of the publication items which can be downloaded based on the client's subscription. A list of publication items and their refresh mode, 'Y' for complete refresh, 'N' for fast refresh, is inserted into a temporary table (C$PUB_LIST_Q). Items can be deleted or the refresh status can be modified in this table since the Sync Server refers to C$PUB_LIST_Q to determine which items will be downloaded to the client.


Similar to the in queue, every record in the out queue should be associated with it a transaction identifier (TRANID$$). The Sync Server passes the last_tran parameter to indicate the last transaction that the client has successfully applied. New out queue records which have not been downloaded to the client before should be marked with the value of curr_tran parameter. The value of curr_tran is always greater than that of last_tran, though not necessarily sequential. The Sync Server only downloads records from the out queues when the value of TRANID$$ is greater than last_tran. When the data is downloaded, the Sync Server calls DOWNLOAD_COMPLETE.







3.15.2 Queue Creation


You need to create the out queue in the Mobile Server repository manually using SQL. You may also wish to create the in queue as well although the Sync Server creates this if one does not exist. Connect to your repository and execute the following statements to create in queues and out queues with the following structure:













Note:

The name that you provide in the +name field is the name of the queue-based publication item.		










Out queue


'CTM$'+name

(

CLID$$CS   VARCHAR2 (30),

..

publication_item_store_columns (c1..cN), 

..

TRANID$$   NUMBER (10),

DMLTYPE$$  CHAR (1) CHECK (DMLTYPE$$  IN ('I','U','D'),

)



In queue


'CFM$'+name

(

CLID$$CS   VARCHAR2 (30),

TRANID$$   NUMBER (10),

SEQNO$$    NUMBER (10),


DMLTYPE$$  CHAR (1) CHECK (DMLTYPE$$  IN ('I','U','D'),

..

publication_item_store_columns (c1..cN), 

..

)



The Sync Server creates a queue control table, C$INQ, and a temporary table, C$PUB_LIST_Q. You can examine the queue control table to determine which publication items have received new transactions.


Queue Control Table


'C$INQ'

(

CLIENTID   VARCHAR2 (30),

TRANID$$   NUMBER,

STORE      VARCHAR2 (30),


)



Temporary Table


'C$PUB_LIST_Q'

(

NAME   VARCHAR2 (30),

COMP_REF   CHAR(1),

CHECK(COMP_REF IN('Y','N'))


)




The parameters for the manually created queues are listed in Table 3-16:




Table 3-16 Queue Interface Creation Parameters






Parameter
Description






CLID$$CS
A unique string identifying the client.




TRANID$$
A unique number identifying the transaction.




SEQNO$$
A unique number for every DML language operation per transaction in the inqueue (CFM$) only.




DMLTYPE$$
Checks the type of DML instruction:

    

  • 

    'I' - Insert
    

    • 

  • 

    'D' - Delete
    

    • 

  • 

    'U' - Update
    

    • 



Outqueue only.






STORE
Represents the publication item name in the queue control table (C$INQ) only.




NAME
The publication item name in the temporary table (C$PUB_LIST_Q) only. 




COMP_REF
This value is either 'Y' for yes, or 'N' for no and is a flag used for determining the refresh mode of publication items.














3.15.3 Queue Interface PL/SQL Procedure


The following PL/SQL package specification defines the callouts needed by the queue interface:


Sample Code


CREATE OR REPLACE PACKAGE CONS_QPKG AS

/*

 *     notifies that inq has new transaction 

*/

PROCEDURE UPLOAD_COMPLETE(

     CLIENTID      IN     VARCHAR2, 

     TRAN_ID      IN     NUMBER     -- IN queue tranid

     );

/*

 *     init data for download

*/

PROCEDURE DOWNLOAD_INIT(

     CLIENTID      IN     VARCHAR2, 

     LAST_TRAN     IN     NUMBER,

     CURR_TRAN     IN     NUMBER,

     HIGH_PRTY     IN     VARCHAR2

     );

/*

 *  notifies when all the client's data is sent

*/

PROCEDURE DOWNLOAD_COMPLETE(

     CLIENTID     IN     VARCHAR2

     );

     

END CONS_QPKG;

/







3.15.4 Create a Publication Item as a Queue


The createQueuePublicationItem method creates a publication item in the form of a queue. This API call registers the publication item and creates CFM$name table as an in queue, if one does not exist.













Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		










You must provide the Consolidator Manager with the primary key of the owner and name of the base table or view in order to create a queue that can be updated or refreshed with fast-refresh. If the base table or view name has no primary key, one can be specified in the primary key columns parameter. If primary key columns parameter is null, then Consolidator Manager uses the primary key of the base table or view name.







3.15.5 Defining a PL/SQL Package Outside the Repository


The PL/SQL package can be defined outside of the Mobile Server repository; although it must refer to the in queues, out queues, queue control table and temporary table that are defined in the repository. The following methods register or remove a procedure, or retrieve the procedure name.


    

  • 

    The registerQueuePkg method registers the string pkg as the current procedure, as follows:
    

    consolidatorManager.registerQueuePkg("ASL.QUEUES_PKG");


    • 

  • 

    The getQueuePkg method returns the name of the currently registered procedure.
    

    • 

  • 

    The unRegisterQueuePkg method removes the currently registered procedure.
    

    • 














Note:

See the Javadoc in the Oracle Database Lite API Specification for more information.		


















3.16 Synchronization Performance


There are certain optimizations you can do to increase performance. See Section 16.1, "Increasing Synchronization Performance" for a full description.








3.17 Troubleshooting Synchronization Errors


The following section can assist you in troubleshooting any synchronization errors:






3.17.1 Foreign Key Constraints in Updatable Publication Items


Replicating tables between Oracle database and clients in updatable mode can result in foreign key constraint violations if the tables have referential integrity constraints. When a foreign key constraint violation occurs, the server rejects the client transaction. 






3.17.1.1 Foreign Key Constraint Violation Example


For example, two tables EMP and DEPT have referential integrity constraints. The DeptNo (department number) attribute in the DEPT table is a foreign key in the EMP table. The DeptNo value for each employee in the EMP table must be a valid DeptNo value in the DEPT table.


A Mobile Server user adds a new department to the DEPT table, and then adds a new employee to this department in the EMP table. The transaction first updates DEPT and then updates the EMP table. However, the database application does not store the sequence in which these operations were executed.


When the user replicates with the Mobile Server, the Mobile Server updates the EMP table first. In doing so, it attempts to create a new record in EMP with an invalid foreign key value for DeptNo. Oracle database detects a referential integrity violation. The Mobile Server rolls back the transaction and places the transaction data in the Mobile Server error queue. In this case, the foreign key constraint violation occurred because the operations within the transaction are performed out of their original sequence.


Avoid this violation by setting table weights to each of the tables in the master-detail relationship. See Section 3.17.1.2, "Avoiding Constraint Violations with Table Weights" for more information.








3.17.1.2 Avoiding Constraint Violations with Table Weights


Mobile Server uses table weight to determine in which order to apply client operations to master tables. Table weight is expressed as an integer and are implemented as follows:


    

  1. 

    Client INSERT operations are executed first, from lowest to highest table weight order.
    

    2. 

  2. 

    Client DELETE operations are executed next, from highest to lowest table weight order.
    

    3. 

  3. 

    Client UPDATE operations are executed last, from lowest to highest table weight order.
    

    4. 



In the example listed in Section 3.17.1.1, "Foreign Key Constraint Violation Example", a constraint violation error could be resolved by assigning DEPT a lower table weight than EMP. For example:


(DEPT weight=1, EMP weight=2)




You define the order weight for tables when you add a publication item to the publication. For more information on setting table weights in the publication item, see Section 3.3.1.6.2, "Using Table Weight".








3.17.1.3 Avoiding Constraint Violations with BeforeApply and After Apply


You can use a PL/SQL procedure avoid foreign key constraint violations based on out-of-sequence operations by using DEFERRABLE constraints in conjunction with the BeforeApply and AfterApply functions. See Section 3.9.3.2, "Defer Constraint Checking Until After All Transactions Are Applied" for more information.












3.18 Datatype Conversion Between the Oracle Server and Client Oracle Lite Database


Before you publish your application, you create the tables for your applications in the Oracle database. Thus, when the first synchronization occurs, Oracle Database Lite takes the Oracle database datatypes and converts them to corresponding allowed datatypes in the Oracle Lite database on the client. Table 3-17 lists the Oracle database datatypes in the left column and displays how the datatype can be mapped to the Oracle Lite database datatypes across the top row.













Note:

For Oracle Database Lite Datatypes, see Appendix B in the Oracle Database Lite SQL Reference.		












Table 3-17 Conversion of Oracle Database Datatypes to Oracle Database Lite Datatypes






Oracle Database Lite Datatypes
1 B
2 B
4 B
Float
Double
Number
Date Time
Long Var Binary
Varchar
Char
BLOB
CLOB




Oracle Database Datatypes






























INTEGER
 
 
X
 
 
 
 
 
 
 
 
 




VARCHAR2
 
 
 
 
 
 
 
 
X
 
 
 




VARCHAR
 
 
 
 
 
 
 
 
X
 
 
 




CHAR
 
 
 
 
 
 
 
 
 
X
 
 




SMALLINT
 
X
 
 
 
 
 
 
 
 
 
 




FLOAT
 
 
 
X
 
 
 
 
 
 
 
 




DOUBLE PRECISION
 
 
 
 
X
 
 
 
 
 
 
 




NUMBER
X
X
X
X
X
X
 
 
 
 
 
 




DATE
 
 
 
 
 
 
X
 
 
 
 
 




LONG RAW
 
 
 
 
 
 
 
X
 
 
 
 




LONG
 
 
 
 
 
 
 
 
X
 
 
 




BLOB
 
 
 
 
 
 
 
 
 
 
X
 




CLOB
 
 
 
 
 
 
 
 
 
 
 
X









"X" indicates that the datatype can be mapped to this Oracle Lite database datatype. To save on space, 1 B represents TINYINT, 2 B represents SMALLINT, and 4 B represents INTEGER.