Oracle® Database Lite Developer's Guide
10g (10.2.0) Part No. B15920-01 |
|
Previous |
Next |
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.
Section 3.2, "What is The Process for Setting Up a User For Synchronization?"
Section 3.3, "Creating Publications Using Oracle Database Lite APIs"
Section 3.5, "Customize the Apply and Compose Phases Using the Consolidator Manager APIs"
Section 3.6, "Customize What Occurs Before and After Synchronization Phases"
Section 3.12, "Set DBA or Operational Privileges for the Mobile Server"
Section 3.13, "Create a Synonym for Remote Database Link Support For a Publication Item"
Section 3.14, "Using the Sync Discovery API to Retrieve Statistics"
Section 3.15, "Customizing Replication With Your Own Queues"
Section 3.18, "Datatype Conversion Between the Oracle Server and Client Oracle Lite Database"
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:
User initiates a synchronization from the Mobile client. Note that the Mobile client may be a Windows platform client or a PDA.
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.
Sync Server gathers all transactions destined for the Mobile client from the Out-Queue.
Sync Server transfers these transactions down to the Sync Client.
Mobile client downloads and applies all changes for client Oracle Lite database.
All transactions compiled from all Mobile clients are gathered by the MGP out of the In-Queue.
The MGP applies all transactions for the Mobile clients to their respective application tables.
Any updates destined for any Mobile client is composed into a transaction by the MGP process.
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.
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:
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.
Within each publication item being processed, the SQL operations are processed as follows:
Client INSERT
operations are executed first, from lowest to highest table weight order.
Client DELETE
operations are executed next, from highest to lowest table weight order.
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:
Insert an employee record 4 with name of Joe Judson.
Update employee record 4 with address.
Update employee record 4 with salary.
Update employee record 4 with office number
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.
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:
Declaratively, using MDW to create the publication and the Packaging Wizard to package and publish the applications. This is the recommended method. See Section 3.2.1, "Creating a Snapshot Definition Declaratively" for details.
Programmatically, using the Resource Manager and the Consolidator Manager APIs to invoke certain advanced features or customize an implementation. This technique is recommended for advanced users requiring specialized functionality. See Section 3.2.2, "Creating the Snapshot Definition Programmatically" for details.
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".
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.
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:
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.
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.
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"
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.
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 theoracle.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.
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.
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 thesample11.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: < On Windows: < |
Section 3.3.1.4, "Data Subsetting: Defining Client Subscription Parameters for Publications"
Section 3.3.1.7, "Creating Client-Side Sequences for the Downloaded Snapshot"
Section 3.3.1.10, "Bringing the Data From the Subscription Down to the Client"
Section 3.3.1.12, "Callback Customization for DML Operations"
Note: To call the Publish and Subscribe methods, the following JAR files must be specified in yourCLASSPATH .
|
Use the createUser
method of the MobileResourceManager
object to create the user for the publication.
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).
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. |
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 theMobileResourceManager object.
|
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");
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.
Create the ConsolidatorManager
object.
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).
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. |
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);
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.
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. |
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");
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. |
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);
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.
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:
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.
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.
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:
All insert operations for dept
are processed.
All insert operations for mgr
are processed.
All insert operations for emp
are processed.
All delete operations for emp
are processed.
All delete operations for mgr
are processed.
All delete operations for dept
are processed.
All update operations for dept
are processed.
All update operations for mgr
are processed.
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.
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.
See the Oracle Database Lite API Specification (included on the CD) for a complete listing of the APIs to define and administrate sequences.
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.
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.
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");
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. |
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.
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 thealterPublicationItem() method has no effect.
|
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.
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.
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".
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.
The following sections describe how you can customize the apply and compose phases of synchronization:
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:
Section 3.5.1.1, "Create a Class That Extends MyCompose to Perform the Compose"
Section 3.5.1.2, "Implement the Extended MyCompose Methods in the User-Defined Class"
Section 3.5.1.4, "Register the User-Defined Class With the Publication Item"
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.
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.
Note: To retrieve information, use the methods described in Section 3.5.1.3, "Use Get Methods to Retrieve Information You Need in the User-Defined Compose Class". |
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.
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:
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:
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.
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:
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:
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);
The BeforeProcessApply
procedure is called before the entire apply phase of the MGP process.
procedure BeforeProcessApply;
The AfterProcessApply
procedure is called after the entire apply phase of the MGP process.
procedure AfterProcessApply;
The BeforeProcessCompose
procedure is called before the entire compose phase of the MGP process.
procedure BeforeProcessCompose;
The AfterProcessCompose
procedure is called after the entire compose phase of the MGP process.
procedure AfterProcessCompose;
The BeforeProcessLogs
procedure is called before the logs are generated for the compose phase of the MGP process.
procedure BeforeProcessLogs;
The AfterProcessLogs
procedure is called after the logs are generated for the compose phase of the MGP process.
procedure AfterProcessLogs;
The BeforeClientCompose
procedure is called before each user is composed during the compose phase of the MGP process.
procedure BeforeClientCompose (clientid varchar2);
The AfterClientCompose
procedure is called after each user is composed during the compose phase of the MGP process.
procedure AfterClientCompose (clientid varchar2);
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. |
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.
Create PL/SQL package declaration with callback owner/schema name of SAMPLE3
and callback package name of SAMP3_PKG
.
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.
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:
Retrieve the template meta data with getTemplateItemMetaData
for the publication item.
Modify the attributes that specify the callback owner/schema (cbk_owner
) and the callback package (cbk_name
).
Register the package by executing the setTemplateItemMetaData
method.
// 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);
You can modify the client-side application to start the synchronization programmatically, which is discussed in the following sections:
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:
Initialize the synchronization parameters.
Set up the transport parameters.
Initialize the synchronization options and environment, such as username, password, and selective synchronization.
Perform the synchronization.
The following sections demonstrates how you can perform these steps in each of the allowed programming languages:
Section 3.7.1.1, "Starting Synchronization Upload and Download Phases With the COM Interface"
Section 3.7.1.2, "Starting Synchronization Upload and Download Phases With C or C++ Applications"
Section 3.7.1.3, "Starting Synchronization Upload and Download Phases With Java Applications"
Section 3.7.1.4, "Starting Synchronization Upload and Download Phases With the ADO.NET Provider"
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.
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.
You can initiate and monitor synchronization from a Java client application. See Section 4.3, "Synchronization API for Java Applications" for more information.
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.
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:
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:
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.
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:
The same Mobile user synching from multiple devices on the same platform, or synching from different platforms when the publications are not platform specific.
Republishing the application.
An unexpected server apply condition, such as constraint violations, unresolved conflicts, and other database exceptions.
Modifying the application, such as changing subsetting parameters or adding/altering publication items. This refresh only affects the publication items.
A force refresh requested by server administrator or a force refresh requested by the client.
Restoring an old Oracle Lite database (ODB file).
Two separate applications using the same backend store.
An unexpected client apply conditions, such as a moved or deleted database, database corruption, memory corruption, other general system failures.
Loss of transaction integrity between the server and client. The server fails post processing after completing the download and disconnects from the client.
Data transport corruptions.
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.
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:
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:
Record with primary key of one and unique field of ABC is deleted.
Record with primary key of 4 and unique field of ABC is inserted.
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:
Insert an employee record 4 with name of Joe Judson.
Update employee record 4 with address.
Update employee record 4 with salary.
Update employee record 4 with office number
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.
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".
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:
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".
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:
Drop all foreign key constraints and then recreate them as DEFERRABLE
constraints.
Bind user-defined PL/SQL procedures to publications that contain tables with referential integrity constraints.
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
:
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;
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.
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.
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
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.
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.
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:
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.
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. |
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.
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:
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 .
|
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. |
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:
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. |
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.
|
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. |
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(); } }}
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.
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
.
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:
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. |
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; /
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.
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. |
There are certain optimizations you can do to increase performance. See Section 16.1, "Increasing Synchronization Performance" for a full description.
The following section can assist you in troubleshooting any synchronization errors:
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.
Section 3.17.1.1, "Foreign Key Constraint Violation Example"
Section 3.17.1.2, "Avoiding Constraint Violations with Table Weights"
Section 3.17.1.3, "Avoiding Constraint Violations with BeforeApply and After Apply"
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.
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:
Client INSERT
operations are executed first, from lowest to highest table weight order.
Client DELETE
operations are executed next, from highest to lowest table weight order.
Client UPDATE
operations are executed last, from lowest to highest table weight order.
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".
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.
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.