Skip Headers

Oracle® Database Lite Developer's Guide Release 10.3 Part Number E12090-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 6 Using Mobile Database Workbench to Create Publications

The following sections describe how to use the Mobile Database Workbench (MDW) to create publications. When using MDW, you first create a project and then create the other objects contained within a publication.

- Section 6.1, "Use MDW to Create Publications"

- Section 6.2, "Create a Project"

- Section 6.3, "Use the Quick Wizard to Create Your Publication"

- Section 6.4, "Create a Publication Item"

- Section 6.5, "Define the Rules Under Which the Automatic Synchronization Starts"

- Section 6.6, "Create a Sequence"

- Section 6.7, "Create and Load a Script Into The Project"

- Section 6.8, "Load a Resource Into the Project"

- Section 6.9, "Create a Publication"

- Section 6.10, "Import Existing Publications and Objects from Repository"

- Section 6.11, "Create a Virtual Primary Key"

- Section 6.12, "Test a Publication by Performing a Synchronization"

- Section 6.13, "Deploy the Publications in the Project to the Repository"

## 6.1 Use MDW to Create Publications

The Mobile Database Workbench (MDW) is a new tool that enables you to iteratively create and test publications—testing each object as you add it to a publication. Publications are stored within a project, which can be saved and restored from your file system, so that you can continue to add and modify any of the contained objects within it.

All work is created within a project, which can be saved to the file system and retrieved for further modifications later. Once you create the project, start creating the publication items, sequences, scripts and resources that are to be associated with the publication. You can create the publication and associated objects in any order, but you always associate an existing object with the publication. Thus, it saves time to start with creating the objects first and associating it with the publication afterwards.

The following describes how to launch MDW:

- Section 6.1.1, "Set Access Privileges to SYSTEM Tables for Your Application Schema"

- Section 6.1.2, "Launch MDW"

### 6.1.1 Set Access Privileges to SYSTEM Tables for Your Application Schema

Before you start up MDW, ensure that the application schema has the correct privileges to the following SYSTEM tables:

- `all_views`

- `all_objects`

- `all_synonyms`

- `all_tables`

- `all_constraints`

- `all_dependencies`

When you create a SQL statement in a publication item using MDW, then MDW checks the dependencies using the SYSTEM tables. So, if you have not set the privileges for the application schema to the SYSTEM tables, you may receive the ORA-1031 "Insufficient privileges" error message.

### 6.1.2 Launch MDW

To launch MDW, execute `oramdw`, which is located in `$ORACLE_HOME``\Mobile\Sdk\bin`.

## 6.2 Create a Project

Create a new project with MDW. The project is the vehicle that contains your iterative approach to defining publications, publication items, sequences, scripts and resources. The project can be saved and restored from your file system, so that you can continue to modify any of the contained objects within it.

You cannot perform any action on developing your publications without first creating the project.

You must have access to the back-end database with the Oracle Mobile Repository and already defined the tables and schema that you are going to be using in your publication items before entering the project wizard.

Perform the following to create the project:

1. Click File->New->Project to start the Project Wizard.

2. An Introductory screen appears. If you do not want this introductory screen to display each time you start a new project, check the "Skip This Page Next Time" box.

3. Define the project name. Enter the project name and location for your new project, as follows:

   - Project name: This name can be any valid Java identifier. The name cannot contain any spaces. For example, your project name may be something like `MY_NEW_PROJECT`.

   - Project location: Enter a valid location in the directory on your machine that has write permission to store the project. On a Windows machine, you could enter the location as `c:\myprojects`. To browse for a directory, click Browse.

   - Mobile client database type: Select the type of the Mobile client, which can be either the SQLite Mobile client or Oracle Lite Mobile client.

   Click Next to move to the next step in the wizard.

4. Provide the Mobile Repository access information. Because you are interacting with the repository to create and manipulate synchronization objects, including the SQL scripts for the publication items, you need access to the Mobile Repository. Enter the following access information:

   Username and Password

   Specify the Mobile Server repository username and password (with administrator privilege). This is the same username and password with which the repository was originally created. For example, the default Mobile Server repository user name and password is `mobileadmin/manager`.

   The administrator username and password are used to connect to the back-end database, create the schema and assign database privileges for the Mobile Server. In order to perform these actions, the administrator user must have the following privileges:

   - The following privileges are required with the Admin option:

     `ALTER ANY TABLE, ALTER SESSION, ALTER SYSTEM, ANALYZE ANY, CREATE SESSION, CREATE ANY SEQUENCE, CREATE ANY VIEW, CREATE ANY TRIGGER, CREATE ANY INDEX, CREATE ANY TABLE, CREATE ANY SYNONYM, CREATE ANY PROCEDURE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, DELETE ANY TABLE, DROP ANY SEQUENCE, DROP ANY PROCEDURE, DROP ANY VIEW, DROP ANY SYNONYM, DROP ANY TRIGGER, DROP ANY INDEX, DROP ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE, SELECT ANY DICTIONARY, SELECT_CATALOG_ROLE, UPDATE ANY TABLE`

   Specify the Connection

   When you define the connection, you have two options:

   - Simple connection definition. For a connection to a single back-end Oracle database, select the Oracle JDBC Thin driver and provide the host (or IP address), port and SID for the Oracle database that contains the Mobile repository.

     This creates a connect string which will be as follows:

         jdbc:oracle:thin:@<host>:<port>:<SID>

   - RAC connection definition. If you are connecting to a RAC configuration, then select the Oracle JDBC Thin driver—Advanced. Selecting this option enables the Connect String field where you can enter the tnsnames connect string that defines all databases included in the RAC configuration. For example, the following is a tnsnames connect string definition that includes two Oracle databases in the RAC configuration:

         (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=-ON)
          (ADDRESS=(PROTOCOL=TCP)(HOST=HOST1)(PORT=1555))
          (ADDRESS=(PROTOCOL=TCP)(HOST=HOST2)(PORT=1555)))
          (CONNECT_DATA=(SERVICE_NAME=ORCL.TW.ORACLE.COM)
          (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180)(DELAY=5))))

   Click Next to move to the next step in the wizard. Once you click Next, the wizard verifies that the database connection information is correct. If incorrect, the wizard prompts you to re-enter the information. You can only advance if you enter the correct information where the Mobile Server Repository is located.

5. Specify the application information, as follows:

   1. Specify the application schema username and password, each of which are limited to 28 characters. The Mobile application schema contains all database tables, views, synonyms used to build the snapshots for the application.

      Note:

      All schema objects for an application exist in the same back-end repository, which is why the Oracle database host, port and SID are only read-only on this screen.

   2. Use the Database Instance pull-down to select the database where the application is to be deployed. In this database, the application schema will be created. You can select the Main database, where the Mobile repository is stored, or any registered remote databases meant solely for application data.

      Note:

      For more information on using and registering remote databases for application data, see Section 3.2, "Register a Remote Oracle Database for Application Data".

      Once selected, the JDBC URL for the selected database is displayed in the Connect String field.

   Click Next to move on to the last screen in the Project Wizard. As you click Next, MDW verifies that the username and password that you entered are valid for connecting to the application schema in the back-end database. If these are not valid, you cannot advance until you supply a valid username and password.

6. A summary page appears. Once the creation of the project is completed, this page displays all of the information about your new project.

   - Click Back to modify any of the information supplied.

   - Click Finish to complete the project creation.

   - Click Cancel to abort creation of this project.

At this point, you can create your publication within this project.

## 6.3 Use the Quick Wizard to Create Your Publication

The Quick Start Wizard enables you to create a simple publication in just a few steps. It generates the publication items within your publication by assuming that you want the default settings. In addition, the snapshot defaults to select all items within the table. For example, if the table selected is `EMP`, then the select statement defaults to `select * from emp`.

You can associate a publication item in a publication, which is then associated in an application. The publication item is the vehicle that defines the SQL to retrieve data from the database for the application users. When you execute the quick wizard, it creates a publication item for each table you wish to include in the publication. In addition, the wizard defaults the SQL statement used to define the data subset for each table as `select * from <table_name>`.

Note:

Since this tool is a quick wizard, it associates a single publication item for each table you include in the publication. In order to create a more complex snapshot—such as one that enables automatic synchronization, creates multiple publication items based on the same table or a more complex SQL statement—see Section 6.4, "Create a Publication Item".

The publication item name defaults to the following: `<table_name>_PI<number>` where `<number>` is sequential between 1 and 9. For example, the first publication item created on table `EMP` would be named `EMP_PI1`. If, in a separate publication, you have already defined a publication item for `EMP_PI1`, then the next time you execute the wizard for the table `EMP`, it will be named `EMP_PI2`.

After creating this publication item, this wizard enables you to test it immediately. When the wizard completes, you can always return to the main menu and modify any of the default settings or specify a more specific data subset with your own SQL statement.

For each of the screens in the wizard, click Next to advance to the next screen.

1. To start the quick wizard, select the Quick Wizard button.

2. An introductory screen appears. If you do not want this introductory screen to display each time you start a new project, check the "Skip This Page Next Time" box.

   Figure 6-1 Welcome Screen for Quick Wizard to Create a Publication

   Description of "Figure 6-1 Welcome Screen for Quick Wizard to Create a Publication"

3. Provide a name for the client Oracle Lite database. This is the database that exists on the device to contain the downloaded snapshot information. The name that you choose will also be used as the name of the publication.

   When this publication is finished, a client database is created on your device and the first synchronization to retrieve the snapshot from the back-end Oracle database is initiated.

   Figure 6-2 Define Client Oracle Lite Database Name

   Description of "Figure 6-2 Define Client Oracle Lite Database Name"

4. Select the table(s) to be included in the publication item, as follows:

   Figure 6-3 Define the Tables to Include in the Publication

   Description of "Figure 6-3 Define the Tables to Include in the Publication"

   - Choose the application schema to associate with this publication item: The application schema is the schema from which the publication item retrieves data. All available schemas in the database are listed in the pull-down list. You must have created the schema prior to starting this wizard.

     Note:

     If the schema you want is not in this list, cancel the wizard, create the schema in the back-end database, and then re-start this wizard.

   - Click Search to display all tables within this schema in the Available column. To search for a specific table or tables, enter the name or partial name with wild charaters in the Object Filter field and then click Search. You can use any of the standard Oracle wild card characters.

   - Select the table(s) that you want in the publication item and click the arrow buttons to move one or all tables into the Selected column. You can move these tables back and forth using the arrow buttons.

     Note:

     If you do not see the object that you expect to see, verify that you created the table in this schema in the back-end Oracle database.

   - When you are satisfied with the list of the tables in this publication item, then click Next.

5. Once the creation of the publication item is completed, a Summary page displays the defaults used for each table included in this publication item, as follows:

   Figure 6-4 Modify the Table Properties for Synchronization

   Description of "Figure 6-4 Modify the Table Properties for Synchronization"

   - Table name: Displays the schema and name of the table included in this publication item.

   - Updatable: This is checked if the table is listed as updatable. You can toggle this item to read-only by double-clicking on the field. However, if it is unchecked, you should only enable it if the table has a virtual primary key.

     For more information on Read-Only or Updatable options, see Section 2.3.1.1, "Manage Snapshots".

   - Refresh Type: By default, all tables use fast refresh. If the table does not have a primary key, then the table uses complete refresh. Double-click on this field to change the refresh type.

     For more information on Fast or Complete refresh types, see Section 2.9, "Understanding Your Refresh Options".

   - Virtual Primary Key: This field displays the virtual primary key for the table. If you want to have the table be updatable or use the fast refresh type, then the table must have a virtual primary key. If the table does not have a primary key, but it does contain a field with UNIQUE constraints, then you can specify this field as the virtual primary key to be able to use fast refresh or updatable.

     Note:

     Any virtual primary key added must be unique and not null.

     To specify a column in the table as your virtual primary key, double-click on the Virtual Primary Key field to list all of the UNIQUE fields. If you select one of them to be the virtual primary key, then you can use the Updatable or fast refresh options for this table.

6. Decide if you want to test this publication.

   Figure 6-5 Decide to Test the Publication

   Description of "Figure 6-5 Decide to Test the Publication"

   You can specify that you want to test this publication as soon as the wizard exits. By default, Yes is selected. This provides a test of the publication against the back-end Oracle database.

   In order to perform this test, a valid client username must be provided. From the drop-down list, select the client username that you would like to use. You will be prompted for the password during synchronization.

7. You can end the wizard by performing one of the following:

   - Click Back to modify any of the information supplied.

   - Click Finish to complete the project creation.

   - Click Cancel to abort creation of this project.

8. If you clicked Yes for testing the publication, then the Test Publication screen is brought up. Click the Synchronize button to start the test.

This creates a basic publication, which you can now view in the project in the MDW main screen. You can modify this publication in any way.

## 6.4 Create a Publication Item

The Publication Item Wizard steps you through the process of creating a publication item in the project. A publication item encapsulates a snapshot definition. It can be based on a table, view or synonym in the Master Application schema in the back-end database. If you use a synonym for a remote object to build a publication item, then you are required to provide the JDBC connection information to the remote database where the remote object resides.

After you create the publication items in the project, then you can associate multiple publication items with a publication, which is then associated with an application. See Section 6.9.2, "Publication Item Tab Associates Publication Items With the Publication" for details.

You can create a publication item through the publication item wizard by clicking File->New->Publication Item.

1. The publication item wizard introduction appears. If you do not want this introductory screen to display each time you start a new project, check the "Skip This Page Next Time" box.

   Click Next to advance to the next screen.

2. Define name, refresh type, and automatic synchronization, as follows:

   - Publication item name: This name can be any valid Java identifier. The name cannot contain any spaces. Publication item names are limited to twenty-six characters and must be unique across all publications. For example, your publication item name may be something like `MY_PUBLICATION_ITEM`.

   - Refresh type: The refresh mode of the publication item is specified during creation to be either fast or complete refresh. See the Section 2.9, "Understanding Your Refresh Options" for more information.

     From the drop-down list choose one of the following refresh types:

     - Complete: All data is refreshed with current data. Everytime you synchronize, all data in the snapshot is retrieved. This can be performance intensive.

     - Fast: This is the recommended mode. Only incremental changes are synchronized. Thus, you are not downloading the complete data snapshot each time a synchronization is requested. 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.

     - Queue-based: You can create your own queue. Mobile Server will upload and download changes from the user. You perform the activity of the MGP and apply/compose the modifications to the back-end database. See the Section 2.13, "Customizing Synchronization With Your Own Queues" for more information.

     Once you create the publication item with a particular refresh type, the only way to modify the publication item to have a different refresh type is to delete is and recreate it with the desired refresh type.

   - Enable Automatic Synchronization checkbox: This defines the publication item to use Automatic Synchronization, where synchronization for this publication item occurs automatically and in the background. That is, you do not have to manually press the Sync button as it occurs automatically. You can have several publication items in a single publication where some use automatic synchronization and others require the user to manually request synchronization.

     If you have multiple publication items within a publication, you can specify which are to be automatically synchronized within each publication item. However, if you have multiple publication items where these contain tables with a master-detail relationship, then ALL of these publications items must be either enabled or disabled for automatic synchronization.

     Note:

     If, after you have published the application, you want to turn off automatic synchronization, you can only disable automatic synchronization as follows:

     1. Execute the API to disable the automatic synchronization. Use the `reCreatePublicationItem` method to disable automatic synchronization. For more information on modifying a publication item using the APIs, see Section 2.4.1.12, "Modifying a Publication Item".

     2. Execute a manual synchronization on the device.The manual synchronization restarts the automatic Sync Agent, which will then use the new rules The new settings will NOT be downloaded automatically during automatic synchronization.

     Step 7 shows you how to specify—with a SQL statement—which users receive the automatic synchronization. Section 6.5, "Define the Rules Under Which the Automatic Synchronization Starts" shows you how to define automatic synchronization rules that will apply to this publication item.

3. Designate the publication item object with the appropriate schema, as follows:

   - Choose the application schema to associate with this publication item: The application schema is the schema from which the publication item retrieves data. All available schemas in the database are listed in the pull-down list. You must have created the schema prior to creating the publication item.

     If the schema you want is not in this list, cancel this publication item, create the schema in the back-end database, and then associate the schema with the publication item.

   - Designate the object type as a table, view, or synonym.

   - To choose the table, view or synonym from within the schema that you wish to base this publication item on, click Search on the Object Filter. This brings up several items in the Object List. Select the object that you are interested in and click Next.

     Note:

     To search only for objects that match a condition, designate the condition in the Filter box and click Search. You can use the same pattern matching characters in a valid `SQL WHERE` clause. The filter is case-sensitive; use upper-case characters.

4. Choose columns to add to the publication item. When you are adding columns to the publication item, you should first verify what data types are supported and how others are modified when brought down to the Oracle Lite database. For example, the `TIMESTAMP` data type is supported, but the `TIMESTAMP WITH TIME ZONE` data type is not. For details, see Section 3.8, "Datatype Conversion Between the Oracle Server and the Oracle Lite Database".

   There are two tabs to enable you to structure your publication item, as follows:

   - Column Selection: Choose the columns from within the object that you will use to retrieve information for the application. To choose the appropriate columns, select the column name and click the left or right arrow buttons to move between the Available and Selected windows. To move all columns, use the double arrows.

     Note:

     The primary key defaults to being in the Selected window, as it is required if you are using Fast Refresh and Updateable option. Since most publication items use these options, MDW places the primary key as Selected. You can move it back to the Available window.

     The publication item query must select primary keys in the same order as they are defined in the base table.

   - Structure: If you are not sure what columns you want, you can see the entire table structure by clicking this tab.

   Note:

   Oracle Database Lite does not support creating publication items for a table with object type columns, even if the publication item query does not include any of the object type columns. However, it is possible to define a view which selects only columns of supported data types and then create a publication item using the view definition.

   If you have specified a fast refresh, you must provide a primary key. If you have specified a table or view that does not have a primary key, exit out of this wizard and create a virtual primary key specifying one of the columns in the table or view. If you do not create a virtual primary key before specifying this publication item, then any future synchronization of this primary key will fail.

   Note:

   Any virtual primary key must be unique and not null.

5. Create indexes and associate them with the publication item. These indexes will display in the Index Selection window. The index name, index type and the index columns are shown. If you want to add or remove an index in the publication item, use the following options:

   Note:

   You can only modify an index by removing and creating it again.

   - Add: Click Add to create a new index and associate it with the publication item. You will need to provide the index name, type and snapshot columns for the new index.

   - Remove: Removes an existing index from the publication item by selecting the index in the Index Selection window and clicking Remove.

   By default, Oracle Database Lite creates a primary key index for every publication item and is created on the primary key of the snapshot table. This index is named `<piName>``_PK`. You cannot remove the primary key index from the publication item within this screen. If you want to remove the primary key index, use the `dropPublicationItemIndex` method. For more details, see Section 2.4.1.6, "Create Publication Item Indexes".

   If a snapshot is based on a table without a primary key, then the primary key index will not be created and the Index Selection box is empty.

   When the desired indexes have been added or removed for this publication item, click Next to advance to the next window.

6. Modify the SQL statement for the publication item. From the columns that you selected in the previous screen, this simple SQL statement is available as a template for you to modify. You can add any qualifiers and complexity to this base statement. To view the structure of the schema object, select the Structure tab.

   - Perform Iterative Modifications

     See Section 6.4.1, "Create SQL Statement for Publication Item" for directions on how to edit and execute the SQL statement for this publication item.

   - Apply/Compose Callbacks

     When creating publication items, the user can specify a customized package to be called during the Apply and Compose phase of the MGP background process. 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. See Section 2.7.2, "Customize What Occurs Before and After Compose/Apply Phases for a Single Publication Item" for more information on how to create the callbacks.

     Provide the schema and package names for any apply/compose callbacks.

   - Dependency Hint

     Click Add to add a dependency hint. All existing dependency hints are shown in the window. See Section 6.4.2, "Create a Dependency Hint" for more information.

7. If you specified a view, then you may need to define parent table and primary key hints. See Section 6.4.3, "Specify Parent Table and Primary Key Hints" for directions on how to define these hints.

8. If you specified automatic synchronization for this publication item, then the Automatic Synchronization Query page is shown, which includes the following:

   - By default, all users are included in the Compose, which means that all users receive the data that is being retrieved from the server and brought down to the Mobile clients. If you want to specify that only certain users that subscribe to this application receive the application data, then check this box.

   - If you clicked the checkbox, then specify which users are to receive the Compose data with a SQL query.

     For example, if you only want `MADAUSER` within the `mobileadmin` schema to receive the Compose data, then type in `select name from mobileadmin.users where name ='MADAUSER'`. Click Run to verify that the SQL query returns what you want; click Next to advance to the next page.

9. Summary page provides an overview of the publication item that you just created. To view any dependency hints, click Advanced. If you have used a view as the base for your publication item and you created Parent Table or Primary Key hints, click Hint to view these hints. The Parent Table and Primary Key hints are only valid on views.

   To modify any part of the publication item, click Back to return to the previous screens.

   Click Finish if you are satisfied with the publication item. Click Cancel to eliminate all work in creating this publication item.

### 6.4.1 Create SQL Statement for Publication Item

You can compose your SQL statement through iterative steps to ensure that you are creating a valid statement. You have the following options:

- Modify the query by clicking Edit.

- Execute the statement against the schema in the back-end database by clicking Run. You will be notified directly if the statement fails or view the data from the schema object is retrieved for you to view.

  When you click Run, then the query of the publication item is validated by executing the query against the back-end database. A dialog is displayed with the returned snapshot that this publication item would generate. If there is more than one page, then click Previous and Next to move between the pages. Click Close to return to the publication item screen.

  If the publication item SQL statement requireds an input value, then a dialog appears for you to input the value for the SQL query. You can modify this value or the SQL query until you receive the results that you desire.

- To return the query to the original statement, click Reset.

- When finished, click Next.

### 6.4.2 Create a Dependency Hint

If the updates to this publication item effects another table, use the dependency hint to notify the synchronization engine to update the other table. For example, if the publication item updates the employee table, and these updates should also apply to the department table, add a dependency hint notifying the synchronization engine of the relationship with the department table.

For your dependency hint, specify whether the hint is based upon a table or synonym. Then, use the pulldown lists to select the schema and table/synonym names. Click OK to save the hint or Cancel to return to the Advanced screen.

For more information on dependency hints, see Section 3.3.2, "Creating or Removing a Dependency Hint".

### 6.4.3 Specify Parent Table and Primary Key Hints

When you use a view, you may need to specify a parent table and primary key hints. A view can be composed of one or more tables joined together. If you have specified fast refresh, then you must specify which table is the parent table and which column is the primary key.

- To create a parent table hint, select the base table from the Base Table(s) drop-down list and check the Parent Table Hint checkbox.

  Note:

  If you do not check the Parent Table Hint checkbox, then the hint is not created when you click Next.

- To create a primary key hint, click Add. Identify the primary key hint for this view, as follows:

  1. From the View Columns drop-down list, select the view column that you want to be the primary key. This column name may be an alias of the actual `table.column` name.

  2. From the Base Tables drop-down list, select the base table where the actual column exists that is to be the primary key.

  3. From the Primary Key Columns drop-down list, all primary key columns from the base table are shown, select the appropriate column for the primary key. If you have a composite primary key, iteratively add each column within the composite primary key.

     Note:

     If you do not provide accurate details in regards to the view, base table, and primary key to which the view column maps, the publication item may save, but the execution of the publication item will fail. For example, if you choose a view column which does not map to the primary key column, MDW will allow the action, but any execution of the publication item will result in failure.

  Click OK to accept this primary key hint.

## 6.5 Define the Rules Under Which the Automatic Synchronization Starts

Once you have enabled a publication item to use automatic synchronization, you must define the rules under which the automatic synchronization executes. The circumstances under which an automatic synchronization occurs is defined within the synchronization rules. There are two types of automatic synchronization rules: events and conditions. If an event is true, it starts a synchronization; however, the synchronization cannot occur unless all conditions are true, as well. This evaluates as follows:

    when EVENT and if (CONDITIONS) then sync;

If an event is true, then a synchronization can start—but only if all conditions are true.

Thus, event and condition rules are as follows:

- Events—An event is variable, as follows:

  - Data events: For example, you can specify that a synchronization occurs when there are a certain number of modified records in the client database.

  - System events: For example, you can specify that if the battery drops below a predefined minimum, you want to synchronize before the battery is depleted.

- Conditions—A condition is an aspect of the client that needs to be present for a synchronization to occur. This includes conditions such as battery life or network availability.

For example, if the event for new data inserted and the condition specified is that the network must be available, then a synchronization only occurs when the network is available and there is new data.

When you define the rules for the synchronization, you can define them in two places:

- Publication level: You specify the rules under which the synchronization occurs at the publication level for all publication items in that publication.

- Platform level: Some of the rules are very specific to the platform of the client, such as battery life, network bandwidth, and so on.

Note:

This section describes how to do this through MDW; see Section 2.2.3, "Define the Rules Under Which the Automatic Synchronization Starts" for directions on how to perform this programmatically.

If after defining these rules and publishing the application, you want to modify the rules, you can do so through MDW. However, you must perform a manual synchronization. The manual synchronization restarts the automatic Sync Agent, which will then use the new rules The new settings will NOT be downloaded automatically during automatic synchronization.

The following sections detail all of the rules you can configure for automatic synchronization:

- Section 6.5.1, "Configure Publication-Level Automatic Synchronization Rules"

- Section 6.5.2, "Configure Platform-Level Automatic Synchronization Rules"

### 6.5.1 Configure Publication-Level Automatic Synchronization Rules

When you are creating the publication, you can define data events that will cause an automatic synchronization. Although these are defined at the publication level, they apply only to the publication items within this publication that have automatic synchronization enabled.

For full details of how to configure these data events, see Section 6.9.6, "Event Tab Configures Automatic Synchronization Rules for this Publication".

Table 6-1 describes the publication level data events that trigger automatic synchronization. The lowest value you can specify is 1.

Table 6-1 Automatic Events for the Publication

Events | Description
--- | ---
Client commit | Upon commit to the Oracle Lite database, the Mobile client detects the total number of record changes in the automatic synchronization log. If the number of modifications is equal to or greater than your pre-defined number, automatic synchronization occurs.
Server MGP compose | If after the MGP compose cycle, the number of modified records for a user is equal to or greater than your pre-defined number, then an automatic synchronization occurs. Thus, if there are a certain number of records contained in an Out Queue destined for a client on the server, these modifications are synchronized to the client.

Note:

If you want to modify the publication-level automatic synchronization rules after you publish the appliation, you can do so through the Mobile Manager, as follows:

1. Click Data Synchronization.

2. Click Repository.

3. Click Publications.

4. Select the publication and click Automatic Synchronization Rules.

### 6.5.2 Configure Platform-Level Automatic Synchronization Rules

The platform-level synchronization rules apply to a selected client platform and all publications that exist on that platform. You can specify both platform events and conditions using either MDW or the Mobile Manager. This section describes MDW; see Section 6.4.1, "Specifying Platform Rules for Automatic Synchronization" in the Oracle Database Lite Administration and Deployment Guide for directions on how to define these rules using Mobile Manager.

To assign platform-level automatic synchronization rules, perform the following in MDW:

1. Click Platform.

2. Select either the Win32 or WINCE platform, which brings up a page with two tabs: Events and Conditions. These rules will apply to all publications for this platform.

3. You can modify the following for each platform:

   - Event Rules—See Section 6.5.2.1, "Define System Event Rules for the Platform".

   - Conditions—See Section 6.5.2.2, "Define Automatic Synchronization Conditions for the Platform".

   Note:

   You can only modify the network settings for the platform using Mobile Manager, see Section 6.4.1, "Specifying Platform Rules for Automatic Synchronization" in the Oracle Database Lite Administration and Deployment Guide for more information.

#### 6.5.2.1 Define System Event Rules for the Platform

When you choose the Event tab, select the checkbox for each event that you want to enable. If the event requires a value, enter the value you desire. This initiates the automatic synchronization the first time the event occurs. For example, if the battery runs below the percentage you specified, the automatic synchronization occurs. As the battery continues to deplete, you will not trigger another synchronization.

The following system events will trigger an automatic synchronization if true.

- Network Bandwidth: Synchronize when the network bandwidth is greater than `<number>` bits/second. Where `<number>` is an integer that indicates the bandwidth bits/seconds. When the bandwidth is at this value, the synchronization occurs.

- Battery Life: Synchronize when the battery level drops to `<number>`%, where `<number>` is a percentage. Often you may wish to synchronize before you lose battery power. Set this to the percentage of battery left, when you want the synchronization to automatically occur.

- AC Power: Synchronize when the AC power is detected. Select this checkbox if you want the synchronization to occur when the device is plugged in.

#### 6.5.2.2 Define Automatic Synchronization Conditions for the Platform

When you choose the Condition tab, you can set under what conditions the automatic synchronization is allowed or disallowed, as follows:

- Battery Level: Specify the minimum battery level required in order for an automatic synchronization to start. The battery level is specified as a percentage.

- Network Availability: Network quality can be specified using several properties. For example, if you have a very low network bandwidth and a high ping delay, you may only want to synchronize your high priority data. To add network quality condition for a specified data priority, click the Add button, which brings up a screen where you can specify a minimum value for the following network properties:

  - Data Priority: You could have defined records in the snapshot with a data priority number. Use this condition to specify under what conditions the different data priority records are synchronized. Data priority can be either one or zero, where zero is high priority. By default, all records are entered with a value of NULL, which is the lowest priority.

    Note:

    You can only use fast refresh with a high priority restricting predicate. If you use any other type of refresh, the high priority restricting predicate is ignored.

    See Section 1.2.10, "Priority-Based Replication" in the Oracle Database Lite Troubleshooting and Tuning Guide for more information.

  - Minimum Network Bandwidth (bits/sec): Configure the minimum bandwidth (bits/second) in which the automatic synchronization can occur for records with this data priority.

  - Maximum Ping Delay (ms): Configure the maximum ping delay (milliseconds) in which the automatic synchronization can occur for records with this data priority.

  - Include Dial-up Networks?: The always-on network is used if available. However, if this network is not available, select YES if you want to use any of the dial-up networks for this data priority.

## 6.6 Create a Sequence

Note:

There is no support for sequences on a SQLite Mobile client.

A sequence is a database object, from which you can generate unique integers. You can use sequences to automatically generate primary key values. However, when you have multiple clients accessing a single server, you need a method to guarantee unique identifying numbers for new records from multiple clients. Oracle Database Lite provides a method for unique sequence 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.

For Oracle Database Lite, you can add a sequence to a publication; then, the sequence is created on all subscribed clients during the initial synchronization. On each client Oracle Lite database, the sequence can be used independently. However, since the sequences are used to generate primary key and unique key values for snapshot tables, it is important to ensure that different clients do not generate the same sequence values. If they did, then conflicts may occur when the clients synchronize their changes to the server tables.

The Sync Server guarantees uniqueness across all clients. During synchronization, the Sync Server assigns separate sequence ranges, known as a window, to each client when necessary. A client cannot increment a sequence beyond its current window. Once a client exhausts its window, the Sync Server assigns a new window on the next synchronization. All windows are unique and never reassigned.

Since the sequence windows are obtained from the Sync Server only during synchronization, there is a chance that the client could exhaust all available sequence numbers in its window in between synchronization events. To prevent this from happening, the administrator can configure clients to obtain a new window before the current one is exhausted by setting the threshold value. A threshold is less than the window size. If the range of values left in the window is less than the threshold size, then during the next synchronization, a new window will be assigned to the client. For example, you set the window to 200 and the threshold to 25. During a synchronization event, the Sync Server notices that the current sequence value is greater than or equal to 175, then it allocates the next window for the client.

The following describes how to use Oracle Database Lite sequences:

- Only clients use the sequence: If you have more than a single client, you want each client to use a specific range of unique sequence numbers, so that none of the records have duplicate sequence numbers.

  Specify the start value and the window size. When you define the size of the window, you provide the Sync Server the number of assigned identifiers for each client and the range of values never overlaps with those of other clients.

- Server and clients use the same sequence: If you want the server and one or more clients to share a sequence, then the server and the client use every other number—the identifiers are generated where the server uses all even numbers and the clients use odd numbers—or vice versa, if the start value of the sequence is an even number. Specify the start value, window size and select the "Generate Server-side Sequence" option that tells Oracle Database Lite to generate a server-side sequence. The increment value always defaults to 2 for this case, even if you specify another number. If you have more than one client, configure the window size to ensure that the client sequence numbers do not conflict.

### 6.6.1 Configuring Sequences in MDW

Within Oracle Database Lite, you configure how you want sequence numbers generated in the sequence definition. In MDW, create a sequence by clicking File->New->Sequence. When you are creating your publication, configure the following values to instruct how the sequence is generated for all clients and the server:

- Name: This sequence name must be a valid database identifier.

- Starts With: Enter the number with which you want this sequence to start.

- Increment: Specify the increment from the starting value for the next value in the sequence.

  Note:

  If you have checked the Generate server-side sequence checkbox and set the increment value to 1, then this value is ignored and is set to 2. When you specify the server-side sequence, then both the client and the server use every other number in the sequence. Thus, you cannot increment by 1 on the client.

- Window Size: Provide the size of the window that the Sync Server assigns to the client. For each client, the Sync Server assigns the initial range of sequence values at the time of subscription. For example, if you set the window size to 100 and the Starts With value to 1, then the Sync Server assigns the client windows as follows:

  - Client A: sequence numbers 1 through 100

  - Client B: sequence numbers 101 through 200

  - Client C: sequence numbers 201 through 300

  If any client exhausts their window, they are assigned another 100, which is the defined window size in our example, during the next synchronization. If you also click the Generate Server-Side Sequence checkbox, then the sequence numbers used by the clients are the odd numbers in their range, such as 1, 3, 5, 7 and so on.

- Threshold: When the number of identifiers left in the window is less than the threshold, a new window of sequence numbers is assigned to the client on the next synchronization. For example, if you have a window size of 200 and threshold of 25, then when the current sequence number is equal to or greater than 175, the Sync Server assigns the next window of values to the client.

  Note:

  If the window size is 100 and the threshold is 25, then no matter what the increment is, the next window is assigned when the sequence numbers are equal to or greater than 75. It is based on the window size, not on the number of sequence values left for the client.

- Description: A description of the sequence.

- Generate server-side sequence: If you want the client and the server-sides to share a sequence, where one side has all even numbers and the other has the odd numbers, check this box. If unchecked, then the sequence is created solely for the client.

  When you check the Generate Server-Side Sequence checkbox, then no matter what value is specified for Increment, it will always be set to 2. A sequence of the supplied name is created automatically on the server in the `mobileadmin` schema to use all even numbers. Specify a 1 as the Starting Value, so that on the server side, the sequence uses even values starting with 2 and on the clients, the odd values are used. Thus, the server and client sequence values are unique.

  If there are multiple clients, then to ensure that the clients use unique numbers, set up separate windows for each client. There is no window for the server, because the server uses all even numbers in the whole range of the sequence.

  For example, the sequence number for the first client starts at 1 and increments by 2 for all of its sequence numbers. The first client still has a window size, which in this example is 100, but it starts with an odd number within that window and always increments by 2 to avoid any even numbers. Thus, client A has the window of 1 to 100, but the sequence numbers would be 1, 3, 5, and so on up to 99.

Oracle Database Lite creates and maintains the sequence based on the sequence definition in the publication. Once you create a sequence in the project, you can associate it with a publication. See Section 6.9.3, "Sequence Tab Associates Existing Sequences With the Publication" for details.

See the Section 2.4.1.8, "Creating Client-Side Sequences for the Downloaded Snapshot" for more information on sequences.

### 6.6.2 Configuration Scenarios for Sequence Generation

When setting up a sequence, you can configure one of the following three scenarios:

- Multiple Clients: In this case, always define Start Value and the Window Size parameters. When you define the size of the window, you provide the Sync Server the number of assigned identifiers for each client and the range of values never overlaps with those of other clients. Also, set the starting value for each client.

- Server and Clients Use Same Sequence: If you want the server and one or more clients to share a sequence, select the "Generate Server-Side Sequence" checkbox. If you have multiple clients, set the Start Value and the Window Size. The checkbox tells Oracle Database Lite to create a sequence on the server side, where the clients use the Start Value and the server uses Start Value +1. The identifiers are generated where the server or the client uses either all odd numbers or even numbers. The window ensures that the clients do not use the same sequence window.

### 6.6.3 Example of a Sequence

For this example, the sequence is defined as follows:

Table 6-2

Parameter | Definition
--- | ---
Name | `audiodb_seq`
Start Value | 1
Increment | 1
Window size | 200
Threshold | 25

The first client starts at 1 with an increment of 1. The full range of sequential values provided to client 1 is 200 and a new set of sequential numbers is assigned during synchronization by the Sync Server when the unused portion of the window is less than 25.

Oracle Database Lite creates the sequence locally on the Mobile client by executing the following SQL statement:

    create sequence audiodb_seq start with 1 maxvalue 200 increment by 1;

On the second client, the Sync Server adjusts the numbers appropriately to accommodate what was created on client 1 and creates the sequence locally with the following SQL statement:

    create sequence audiodb_seq start with 201 maxvalue 400 increment by 1;

During each synchronization, the Sync Server tracks the number of assigned windows to ensure that each client has a unique range. When the Sync Server assigns a new set of sequential numbers as identifiers for the client, it recreates the sequence, as follows:

    drop sequence audiodb_seq;
    create sequence audiodb_seq start with 401 maxvalue 600 increment by 1;

### 6.6.4 Example of a Client and Server Sharing a Sequence

You can define a sequence to provide unique sequence values by assigning all odd or even sequence numbers to either the client or the server. The value specified in the Start Value sets the starting value for the clients. If the server is sharing a sequence with a client, then the start value also determines the values for the server. If the starting value is odd, then the server will use all even numbers; if the starting value is even, then the server uses all odd numbers.

The following example demonstrates how to set up a sequence where the odd numbers are for the client and the even numbers for the server.

Enter the following sequence definitions for the client in MDW when defining the publication:

Table 6-3

Parameter | Definition
--- | ---
Name | `audiodb_seq`
Start Value | 1
Increment | 2
Window size | 200
Threshold | 25
Generate server-side sequence | Check on

The sequence on the server starts at 2 and uses all even numbers; within the publication, you specified that all clients use odd numbers starting at 1.

## 6.7 Create and Load a Script Into The Project

You can add a script to this project. Create the script on your file system and then upload it to MDW. Before you add the script to the project, you can use MDW to test the script. See the following sections for more information:

- Section 6.7.1, "Writing SQL Scripts"

- Section 6.7.2, "Test SQL Scripts"

- Section 6.7.3, "Load the Script Into the Project"

### 6.7.1 Writing SQL Scripts

When you write and upload a SQL script to the project, each script is executed independently by Oracle Database Lite in no specified order. Therefore, if you have dependencies and need the DDL statements to be executed in a certain order, include all statements in the correct order in a single script, where each DDL statement is separated by a semicolon ("`;`").

Alternatively, you can specify the weight for the script when loading them to specify the order in which each script is executed on the client. See Section 6.7.3, "Load the Script Into the Project" for more details.

If a SQL script fails upon execution, Oracle Database Lite will execute it once more, in case the failure was due to a dependency of a later script. However, if you have a script with a dependency on another script, you could effect your performance while Oracle Database Lite re-executes all of the scripts to resolve dependencies.

Note:

If you upload scripts using one of the Consolidator APIs, you must also ensure that the order of execution for these scripts does not matter. Include all dependent DDL statements in a single script and in the order necessary for clean resolution.

### 6.7.2 Test SQL Scripts

You can test a SQL script that resides on your file system by selecting Tool->SQL Window. Through the SQL Wizard, perform the following:

1. Connect to the correct database—whether it is an Oracle database or a device Oracle Lite database.

2. Load the SQL script from your file system.

3. Execute the script. You can execute the current script or re-execute scripts that are on the history page. You can choose to have the results displayed on the screen or spooled to a file.

The following sections describe how to accomplish these tasks:

- Section 6.7.2.1, "Connect to the Database"

- Section 6.7.2.2, "Load and Execute SQL Scripts"

#### 6.7.2.1 Connect to the Database

1. Select type of database—Select the radio button next to the type of database to which you are connecting—an Oracle database or the client Oracle Lite database. If you selected the client Oracle Lite database, you must also specify the Mobile client platform and protocol with the drop-down lists. Only currently installed platforms and protocols are displayed in these drop-down lists.

2. Specify database authentication and destination connection information—Part of the information necessary for completing the database connection is the authentication user name and password and the database destination information, which can include either the DSN of the Mobile client Oracle Lite database or the host, port, and SID for the Oracle database.

3. Review database connection values—The final screen displays a summary of all of the configured values for the database connection. Verify that the information is correct and then click Finish. You can return to any page to modify the information by clicking Back.

#### 6.7.2.2 Load and Execute SQL Scripts

You can test any SQL scripts against the database defined in the previous portion of this wizard.

- Click Load Script to browse your file system for the script that you want to test. Define if you want this script to be spooled or auto-committed.

- Click Execute to run the script on the destination database. The results show up in the bottom results screen.

  If you want, you can spool the results to a file by checking the spool checkbox before you click Execute. When you check Spool, a dialog appears for you to define the location and name of the file to receive the output from the script. Check the Overwrite checkbox if you want this file overwritten each time that the script is executed.

  Check the Autocommit checkbox if you want the SQL committed automatically after the script completes.

The Results and History tabs show the current results and all past results respectively. Clear the Results screen by either clicking Clear Results or by checking the Auto Clear checkbox. Clear the historical information by clicking the Clear History button on the History page.

Note:

Any SQL on the History page can be executed by selecting the corresponding row and clicking Execute.

### 6.7.3 Load the Script Into the Project

Define the script on your machine. Once defined, perform the following:

1. Bring the script into the project by clicking File->New->Script.

2. Provide a user-defined name to identify the script and browse for the script in your file system.

3. Specify the weight, if necessary. You can specify the weight for the script when loading them to specify the order in which each script is executed on the client. For example, when creating a master detail table on the client, you must create first the master table and then the detail table. The client does not know which script should be executed first, unless you specify a weight to let the client know the order in which to execute the scripts.

4. Click OK to accept the definition and Cancel to return to the previous screen.

Once you include a script in the project, you can associate it with a publication. See Section 6.9.4, "Script Tab Associates Existing Scripts With the Publication" for more information.

## 6.8 Load a Resource Into the Project

You can load a JAR file that contains Java class files as a resource in a project. Once loaded as a resource, the JAR file is downloaded to the client on the first synchronization. In addition, if this resource is modified, it will be sent down on the next synchronization.

Note:

You can only load JAR files as resources, not individual class files. Once you load a JAR file, the only way you can replace it is by dropping the JAR and then loading the new JAR file.

To specify an existing resource, click File->New->Resource.

Note:

You cannot add resources for a SQLite Mobile client.

Provide the JAR file on your machine. Specify a user-defined name to identify the resource and browse for the JAR file in your file system. Click OK to accept the definition and Cancel to return to the previous screen.

Once you include a resource in the project, you can associate it with a publication. See Section 6.9.5, "Resource Tab Associates Existing Resources With the Publication" for more information.

## 6.9 Create a Publication

Create a publication by clicking File->New->Publication. You can create the publication at any time. This starts the dialog for creating a publication.

There are six tabs included for configuring information about the new publication. On configures general information about the publication, one defines event rules for automatic synchronization, and the others enable you to associate different objects with the publication.

If you click OK, then you can associate the objects by selecting the publication name and then selecting the appropriate tab.

When you are finished creating the publication, click File->Save to save the publication.

- Section 6.9.1, "General Tab Configures Publication Name"

- Section 6.9.2, "Publication Item Tab Associates Publication Items With the Publication"

- Section 6.9.3, "Sequence Tab Associates Existing Sequences With the Publication"

- Section 6.9.4, "Script Tab Associates Existing Scripts With the Publication"

- Section 6.9.5, "Resource Tab Associates Existing Resources With the Publication"

- Section 6.9.6, "Event Tab Configures Automatic Synchronization Rules for this Publication"

### 6.9.1 General Tab Configures Publication Name

The General tab provides the following information about your new publication within your project:

- Publication name: Enter a valid Java identifier for the publication name. The name cannot contain any spaces or special characters.

- Optional description: You can add a description to remind you of the content of this publication.

- Client database name: This defaults to the same name as the publication name. However, you can modify it. The purpose of this name is to specify the name of the client Mobile database, which is created during the first synchronization.

### 6.9.2 Publication Item Tab Associates Publication Items With the Publication

Selecting the Publication Item tab from within the publication enables you to associate any existing publication item to this publication.

Manage Publication Items In This Publication

- To add an existing publication item to this publication, Click Add.

- To remove a publication item from this publication, select the desire publication item from the list and click Remove.

- To edit the details of the association for the publication item, select the desired publication item and click Edit.

To accept the current changes, click OK.

#### 6.9.2.1 Associating a Publication Item to this Publication

To associate any publication item to this publication, the publication item must first exist. Thus, all of the information requested on this screen is about existing publication items.

Provide the following information to identify the publication item to associate to this publication:

Identify Existing Publication Item

From the Name drop-down list, select the name of the publication item.

Updatable or Read-Only Snapshot

Select if the snapshot is updatable or read-only. See Section 2.3.1.1, "Manage Snapshots" for more details.

- Read-only snapshots are used for querying purposes. Changes made to the master table are replicated to the snapshot by the Mobile client.

- 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 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.

  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.

Conflict Resolution

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. A Mobile Server synchronization conflict is detected under any of the following situations:

- The same row was updated on the client and on the server.

- Both the client and server created rows with equal primary keys.

- The client deleted a row and the server updated the same row.

- The client updated a row and the server deleted the same row. This is considered a synchronization error for compatibility with Oracle database advanced replication.

- For systems with delayed data processing, where a client's data is not directly applied to the base table (for instance in a three tier architecture) a situation could occur when first a client inserts a row and then updates the same row, while the row has not yet been inserted into the base table. In that case, if the `DEF_APPLY` parameter in `C$ALL_CONFIG` is set to `TRUE`, an `INSERT` operation is performed, instead of the `UPDATE`. It is up to the application developer to resolve the resulting primary key conflict. If, however, `DEF_APPLY` is not set, a "`NO DATA FOUND`" exception is thrown (see below for the synchronization error handling).

- All the other errors including nullity violations and foreign key constraint violations are synchronization errors.

- If synchronization errors are not automatically resolved, the corresponding transactions are rolled back and the transaction operations are moved into Mobile Server error queue in C$EQ, while the data is stored in CEQ$. Mobile Server database administrators can change these transaction operations and re-execute or purge transactions from the error queue.

Choose the type of conflict resolution you want for this publication item, as follows:

- Client wins—When the client wins, the Mobile Server automatically applies client changes to the server. And if you have a record that is set for `INSERT`, yet a record already exists, the Mobile Server automatically modifies it to be an `UPDATE`.

- Server wins—If the server wins, the client updates are not applied to the application tables. Instead, the Mobile Server automatically composes changes for the client. The client updates are placed into the error queue, just in case you still want these changes to be applied to the server—even though the winning rules state that the server wins.

- Custom—You have created your own callbacks to resolve the conflict resolution.

All synchronization errors are placed into the error queue. For each publication item created, a separate and corresponding error queue is created. The purpose of this queue is to store transactions that fail due to unresolved conflicts. The administrator can attempt to resolve the conflicts, either by modifying the error queue data or that of the server, and then attempt to re-apply the transaction.

See Section 2.11, "Resolving Conflicts with Winning Rules" for more information.

DML Callback

A user can use Java to specify a customized PL/SQL procedure which is stored in the Mobile Server repository to be called in place of all DML operations for this publication item. There can be only one mobile DML procedure for each publication item. See Section 2.4.1.13, "Callback Customization for DML Operations" for more information on how to specify a DML Callback.

Enter a string for the schema and package of the DML callback, such as `schema.package_name`.

Grouping Function

If you know that two tables should share a map, but Oracle Database Lite would not normally associate these tables, provide a grouping function that denotes the shared publication item data between the tables.

Note:

The Mobile Server schema owner needs to be granted execute privilege on the defined grouping function.

The grouping function is a PL/SQL function with the following signature.

    (
    CLIENT in VARCHAR2,
    PUBLICATION in VARCHAR2,
    ITEM in VARCHAR2
    ) return VARCHAR2.

The returned value must uniquely identify the client's group.

In this field, provide the PL/SQL grouping function fully-qualified, either with `schema.package.function_name` or `schema.function_name`.

See the Section 1.2.7, "Shared Maps" in the Oracle Database Lite Troubleshooting and Tuning Guide for more information.

Priority Condition

Provide a string that is to be added to the publication item query statement to limit what is returned based on priority.

See Section 1.2.10, "Priority-Based Replication" in the Oracle Database Lite Troubleshooting and Tuning Guide for more information.

MyCompose Class

Provide a string with the full path and classname of the location and name of the `MyCompose` Class. See Section 2.6, "Customize the Compose Phase Using MyCompose" for more information on this class.

Weight

You can rate the order in which each publication item in this publication is executed by specifying the weight. This should be a number. Each publication item must have a unique number in ascending order. The first publication item executed is the one with the weight of one.

### 6.9.3 Sequence Tab Associates Existing Sequences With the Publication

You can only associate an existing sequence with the publication on this screen. To add an existing sequence, click Add.

Note:

You can create a sequence through the File->New->Sequence screen.

Click on the drop-down list and select one of the existing sequences to add to the publication. Click OK to add the sequence; click Cancel to go back to the previous screen.

### 6.9.4 Script Tab Associates Existing Scripts With the Publication

You can only associate an existing script with the publication on this screen. To add an existing script, click Add.

Note:

You can import a script through the File->New->Script screen.

Click on the drop-down list and select one of the existing scripts to add to the publication. Click OK to add the script; click Cancel to go back to the previous screen.

It is important that all scripts follow the instructions listed in Section 6.7.1, "Writing SQL Scripts".

### 6.9.5 Resource Tab Associates Existing Resources With the Publication

You can only associate an existing resource with the publication on this screen. To add an existing resource, click Add.

Note:

You can import a resource through the File->New->Resource screen.

Click on the drop-down list and select one of the existing resources to add to the publication. Click OK to add the resource; click Cancel to go back to the previous screen.

### 6.9.6 Event Tab Configures Automatic Synchronization Rules for this Publication

When you select the Event Tab, you can configure data event rules for this publication, which apply to all automatic synchronization enabled publication items associated in this publication.

Data events define when an automatic synchronization is triggered.

- Client Data Events—Synchronize if the client database contains more than `<number>` modified records, where you specify the `<number>` of modifed records in the client database to trigger an automatic synchronization.

- Server Data Events—Synchronize if the out queue contains more than `<number>` modified records, where you specify the `<number>` of modifed records in the client database to trigger an automatic synchronization.

The lowest value that can be provided in these fields is 1. Specify a high value if you want the synchronization to occur based upon other rules. Click Apply when finished.

## 6.10 Import Existing Publications and Objects from Repository

You can import existing publications, publication items, sequences, scripts or resources that already exist within the repository by choosing the Project->Add From Repository option, as described in the following sections:

- Section 6.10.1, "Import Existing Publication from Repository"

- Section 6.10.2, "Import Existing Publication Item From the Repository"

- Section 6.10.3, "Import Existing Sequence From the Repository"

- Section 6.10.4, "Import Existing Resource From the Repository"

- Section 6.10.5, "Import an Existing Script From the Repository"

### 6.10.1 Import Existing Publication from Repository

You can add an existing publication that already exists in the repository to this project by selecting Project->Add From Repository->Publication. All associated objects—publication items, sequences, scripts, resources—are also pulled into the project with the publication.

To view all publications in the repository, click Search. All publications are shown in the left-hand screen. To limit the displayed publications to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those publications that match the filter are shown.

Note:

In the Search Filter, you can use the same pattern matching characters in a valid `SQL WHERE` clause. The filter is case-sensitive; use upper-case characters.

Select the desired publications and either double-click or select the right arrow to move them to the right window. Once all desired publications are in the right window, click OK to move these publications into the project.

### 6.10.2 Import Existing Publication Item From the Repository

You can add an existing publication item that already exists in the repository to this project by selecting Project->Add From Repository->Publication Item.

To view all publication items in the repository, click Search. All publication items are shown in the left-hand screen. To limit the displayed publication items to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those publication items that match the filter are shown.

Note:

To search only for objects that match a condition, designate the condition in the Filter box and click Search. You can use the same pattern matching characters in a valid SQL WHERE clause. The filter is case-sensitive; use upper-case characters.

Select the desired publication items and either double-click or select the right arrow to move them to the right window. Once all desired publication items are in the right window, click OK to move these publication items into the project.

Once added into the project, you still must associate them with the publication if you want to test the synchronization of the publication item. See Section 6.9.2, "Publication Item Tab Associates Publication Items With the Publication" for more information.

### 6.10.3 Import Existing Sequence From the Repository

You can add an existing sequence that already exists in the repository to this project by selecting Project->Add From Repository->Sequence.

To view all sequences in the repository, click Search. All sequences are shown in the left-hand screen. To limit the displayed sequences to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those sequences that match the filter are shown.

Note:

To search only for objects that match a condition, designate the condition in the Filter box and click Search. You can use the same pattern matching characters in a valid `SQL WHERE` clause. The filter is case-sensitive; use upper-case characters.

Select the desired sequences and either double-click or select the right arrow to move them to the right window. Once all desired sequences are in the right window, click OK to move these sequences into the project.

Once added into the project, you still must associate them with a publication if you want to test it with a synchronization. See Section 6.9.3, "Sequence Tab Associates Existing Sequences With the Publication" for more information.

### 6.10.4 Import Existing Resource From the Repository

You can add an existing resource that already exists in the repository to this project by selecting Project->Add From Repository.

Note:

You cannot add resources for a SQLite Mobile client.

To view all resources in the repository, click Search. All resources are shown in the left-hand screen. To limit the displayed resources to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those resources that match the filter are shown.

Note:

To search only for objects that match a condition, designate the condition in the Filter box and click Search. You can use the same pattern matching characters in a valid SQL WHERE clause. The filter is case-sensitive; use upper-case characters.

Select the desired resources and either double-click or select the right arrow to move them to the right window. Once all desired resources are in the right window, click OK to move these resources into the project.

Once added into the project, you still must associate them with a publication if you want to test it with a synchronization. See Section 6.9.5, "Resource Tab Associates Existing Resources With the Publication" for more information.

### 6.10.5 Import an Existing Script From the Repository

You can add an existing script that already exists in the repository to this project by selecting Project->Add From Repository->Script.

To view all scripts in the repository, click Search. All scripts are shown in the left-hand screen. To limit the displayed scripts to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those scripts that match the filter are shown.

Note:

To search only for objects that match a condition, designate the condition in the Filter box and click Search. You can use the same pattern matching characters in a valid SQL WHERE clause. The filter is case-sensitive; use upper-case characters.

Select the desired scripts and either double-click or select the right arrow to move them to the right window. Once all desired scripts are in the right window, click OK to move these scripts into the project.

Note:

All scripts added to the project must follow the guidelines as described in Section 6.7.1, "Writing SQL Scripts".

Once added into the project, you still must associate them with a publication if you want to test it with a synchronization. See Section 6.9.4, "Script Tab Associates Existing Scripts With the Publication" for more information.

## 6.11 Create a Virtual Primary Key

For fast refresh, you must have a primary key. If the table, view, or synonym does not currently have a primary key, you can designate one of the columns as the virtual primary key through this screen, as follows:

Note:

Any virtual primary key must be unique and not null.

1. Using the drop-down lists, choose the following:

   - Schema name

   - Object type: table, view or synonym type

   - Any string that exists within the object name, if desired

2. Click Search, which brings up a list of available objects.

3. From the object list, choose the appropriate table, view, or synonym. Once chosen, the available columns are listed.

4. Select the column(s) that you wish to be the primary key and click OK.

If you have a composite primary key, iteratively add each column within the composite primary key.

## 6.12 Test a Publication by Performing a Synchronization

You can create a test to perform a synchronization of the designated publication. Click Project->Test Publication. When you create the test, MDW automatically creates the subscription for the user.

1. Click Create to design the test and provide the following information:

   - Name: If the test is remote, then the user name is populated with the registered owner of the remote target device. If the test is local, then the user name should be a valid Mobile user in the repository.

   - Publication: From the drop-down list, select one of the available publications in this project for this test.

   - Client type: Designate if the client is local or remote. Default is local. If Active Sync is not installed, the remote option is not available.

   - Specify a user that is defined in Mobile Manager.

   Click OK to save the test; click Cancel to revert back to the previous screen.

   Note:

   To remove any tests, select the test and click Remove.

2. Once created, click Synchronize to perform a synchronization for the designated publication. On the pop-up dialog, provide the password for the given username and the URL of the Mobile Server. The URL for the Mobile Server should be the `hostname/webtogo`.

   Click Option to specify priority of the publication items, as follows:

   - High Priority: Limits synchronization to server tables flagged as high priority, otherwise all tables are synchronized.

     Note:

     You can only use fast refresh with a high priority restricting predicate. If you use any other type of refresh, the high priority restricting predicate is ignored.

     See Section 1.2.10, "Priority-Based Replication" in the Oracle Database Lite Troubleshooting and Tuning Guide for more information.

   - Push Only: Upload changes from the client to the server only, do not download. This is useful when data transfer is one way, client to server.

   - Complete Refresh: All data is refreshed from the server to the client.

   - Debug: Turn on debugging when synchronizing.

   - Selective Synchronization: Determine which publication and publication items are allowed to synchronize. When you click this option, move the publication items that you want to synchronize from the left window to the right window using the arrow buttons. For details on how selective synchronization performs, see Section 4.1.6, "Manage What Tables Are Synchronized With ocSetTableSyncFlag" and Section 4.2.8, "Manage What Tables Are Synchronized With Selective Sync".

   Click OK to save the synchronization options or Cancel to return to the previous screen.

## 6.13 Deploy the Publications in the Project to the Repository

You can deploy one or more of the publications in the current project from the development/test Mobile Server repository to a target production Mobile Server repository by clicking File->Deploy. You should adequately test all publications before deploying to the production Mobile Server repository.

All available publications are displayed in the project publications section. To limit the displayed publications to only those with a certain string as part of the name, provide this string in the Filter and then click Search. Only those publications that match the filter are shown.

Note:

In the Search Filter, you can use the same pattern matching characters in a valid `SQL WHERE` clause. The filter is case-sensitive; use upper-case characters.

Select the desired publications and click OK to deploy these publications into the repository. A dialog appears where you specify the remote database connection information, as follows:

- User name and password for database connection authentication.

- JDBC Driver type: Based on the type of the JDBC driver, different information is required. At this time, you can only use the JDBC Thin driver. Provide the host name, port, and SID for the remote database.

Click OK to accept the input values for the remote database; click Cancel to return to the previous screen.