Creating a New Publication using Mobile Database Workbench with Oracle Mobile Server

If you are a mobile device user, it is likely that you would have performed a sync at one point in time (with or without being aware of it). We are all familiar with the convenience of being able to just dock our PDA devices and have our calendars, tasks, and contacts automatically synced to our desktop machines.

The synchronization process is a necessity for any type of mobile device, whether it's a Smart Phone, iPhone, or a Pocket PC. The core of this necessity is simple—people need to have access to their data when they're on the move and when they're back at the office, and this data needs to be consistent—wherever they're accessing it from.

In a business scenario, the importance of this necessity increases manifold—it's not just about your personal data anymore. The data you've keyed in on your PDA needs to be synced to the server so that it can be shared with other users, used to generate reports, or even sent for number-crunching. With hundreds of mobile users synchronizing their data and server-side applications updating this data at the same time, things can quickly get messy. The synchronization process has to ensure that conflicts are gracefully handled, auto-generated numbers don't overlap, that each user only syncs down the data they're meant to see, and so on.

The Oracle Mobile Server can be a bit tedious to set up for first time beginners. Once you get going, however, it can be a powerful tool that can manage not only database synchronization but also mobile application deployment.

A publication represents an application (and its database) in the Oracle mobile server. You can create a publication through the Mobile Database Workbench tool provided with Oracle Mobile Server.

Creating a new mobile project

Launch the Mobile Database Workbench tool from Start | All Programs | Oracle Database Lite 10g | Mobile Database Workbench. Create a new project by clicking on the File | New | Project menu item in the Mobile Database Workbench window. A project creation wizard will run. Specify a name for your project and a location to store the project files.

The next screen will request you to key in mobile repository particulars. Specify your mobile repository connection settings, and use the mobile server administrator password you specified earlier to log in.

In the next step, specify a schema to use for the application. As you've created the master tables in the MASTER schema, you can specify your MASTER account username and password here.

The next screen will show a summary of what you've configured so far. Click the Finish button to generate the project. If your project is generated successfully, you should be able to see your project and a tree list of its components in the left pane.

Adding publication items to your project

Each publication item corresponds to a database table that you intend to publish. For example, if your application contained five tables, you will need to create five publication items. Let's create the publication items now for the Accounts, AccountTasks, AccountHistories, AccountFiles, and Products tables.

Click on the File | New | Publication Item menu item to launch the Publication Item wizard. In the first step of the wizard, specify a name for the publication item (use the table name as a rule of thumb). There are two options here worth noting:

  • Synchronization refresh type
    This refers to the type of refresh used for a particular table:
    • Fast: This is a type of incremental refresh—only the changes are synced down from the server during a sync. This is the most common mode of refresh used.
    • Complete: In this type of refresh, all content is synced down from the server during each sync. It is comparatively more time consuming and resource intensive. You might use this option with tables containing small lists of data that change very frequently.
    • Queue based: This is a custom refresh in that the developer can define the entire logic for the sync. It can be used for custom scenarios that may not exactly require synchronization—for instance you might need to simply collect data on the client and have it stored at the server. In such a case, the queue-based refresh works better because you can bypass the overhead of conflict detection.
  • Enable automatic synchronization
    Automatic synchronization allows a sync to be initiated automatical-ly in the background of the mobile device when a set of rules are met. For example, you might decide to use automatic synchronization if you wanted to spread out synchronization load over time and reduce peak-out on the server.

In the next step, choose the table that you want to map the publication item to. Select the MASTER schema, and click the Search button to retrieve a list of the tables under this schema. Locate the Accounts table and highlight it.

In the next screen, you will need to select all the columns you need from the Accounts table. As you need to sync every single column from the snapshot to the master table, include all columns. Move all columns from the Available list to the Selected list using the arrow buttons and click on the Next button to proceed.

The next step is one of the most important steps in creating a publication item. The SQL statement shown here basically defines how data is retrieved from the Accounts table at the server and synced down to the snapshot on the mobile device. This SQL statement is called the Publication Item Query. The first obvious thing you need to do is to edit the default query. You need to include a filter to sync down only the accounts owned by the specific mobile device user. You can easily use a filter that looks like the following:

WHERE OwnerID = :OwnerID

The following screenshot shows how your Publication Item Query will look after editing. If any part of it is defined or formatted incorrectly, you will receive a notification. Click on Next after that to get to the summary screen, then click on the Finish button to generate the publication item.

After creating the publication item for the Accounts table, let's move on to a child table—the AccountTasks table. Create another publication item in the same fashion that maps to the AccountTasks table. At Step 4 of the wizard, the Publication Item Query that you need to specify will be a little bit different.

The AccountTasks table does not contain the OwnerID field, so how do we filter what gets synced down to each specific mobile device. You obviously don't want to sync down every single record in this table—including those that are not meant to be accessible by the specific mobile device user.

One way to still apply the OwnerID filter is to use a table join with the Accounts table. You can easily specify a table join in the following manner:


If you try to save the Publication Item Query above in the Edit Query box, it may prompt you to select the primary base object for the publication item (as shown in the following screenshot). This should be set to AccountTasks because we are creating a publication item that maps to this table.

If you choose the Accounts table again, you will end up with two publication items that map to the same Accounts table. This will cause problems when you attempt to add both items to a publication.

If you have typed in everything correctly, you will be able to see your Publication Item Query show up in the Query tab shown as follows. You can then click on the Next and Finish buttons to complete the wizard.

Now that you've seen how to create a publication item based on a child table, repeat the same steps above for the other child tables – AccountFiles and AccountHistories.

The last table—the Products table deserves a special mention because it's different. You do not need a filter for this table, simply because every mobile device user will need to see the full list of products. You can, therefore, use the default Publication Item Query for the Products table:


After you've done this, you can now move on to creating the "sequences" necessary in this mobile application.

Adding sequence items to your project

When you are working with multiple mobile devices, you have to keep in mind that records from multiple devices will be synced into one single table on the server. If each mobile device uses its own set of running numbers, you may end up with duplicate numbers when the records from each device are synced to the server.

Oracle Mobile Server helps you manage sequence numbers by allocating a set of numbers that never overlap for each separate mobile device user. Let's see how this can be configured for the AccountTasks sequence number for example. Launch the Create Sequence window by clicking on the File | New | Sequence menu item. Fill in the name of the sequence. There are three fields worth noting:

  • Window Size: The window size defines the size of each new block of numbers that is allo-cated to each mobile user.
  • Threshold: Once the allocated number has reached the threshold, a new block of numbers of size defined by the Window size property is allocated. The threshold value is usually a few hundred numbers smaller than the Window size.
  • Generate Server side Sequence: In some cases, you might also be using sequences on the server side to create new records. In such a case, Oracle Mobile Server prevents any conflict by assigning all even numbers to the server and odd numbers to the mobile clients. If you don't need to use sequences on the server side, avoid selecting this option, because the mobile user will only be able to use half the numbers allocated to him or her.

The following screenshot shows the Create Sequence user interface:

After you've created three sequences—one for each of the AccountTasks, AccountHistories, and AccountFiles tables, your project should now contain the following items:

Adding a publication to your project

Now that you have created all the items you need, you can begin to create the publication itself. Click on the File | New | Publication menu item. In the General tab of the ensuing pop-up window, specify the name of the publication and the client database.

The Client Database Name field will become the name of the database generated on the mobile device when the mobile application is synced down to the device.

In the Publication Item tab, you can add a list of existing publication items to this publication. Click the Add button to add a publication item. You will be presented with a screen similar to the one shown below. There are a few fields worth noting in this screen:

  • Name
    You can see the list of all your publication items in the Name field. Select the Accounts publication item.
  • Updateability
    An updatable publication item means that the sync for this item is bidirectional. A read-only item means that the sync for this item is from master table (server) to snapshot (client) only.
  • Conflict resolution
    Client Wins means that in the event of a conflict (updates done on the same record at both the server side and client side), the client-side changes will always override the server-side changes. Server Wins means exactly the opposite.

Add the publication item in the same fashion for each of the Accounts, AccountTasks, AccountHistories, and AccountFiles tables.

For the Products table, you will never need to update the server with the list of products from the client device, so the sync will usually be unidirectional (from server to client). You will, therefore, need to set Updatebility to read-only. Since Conflict Resolution is also no longer applicable, set its value to Custom. When you're done, you should have the following in your publication items list.

It is now time for you to add the sequences you've created earlier to the Sequence tab of the publication. Click on the Add button to add an existing sequence to the publication. When you're done, you should see three sequences show in the list as follows:

The other tabs are not relevant and will not need to be filled in. Click on the OK button to create the publication item. You can also opt to save your publication at this point by clicking on the File | Save menu item.


We have discussed about the following:

  • Creating a new mobile project
  • Adding publication items to your project
  • Adding sequence items to your project
  • Adding a publication to your project

You've been reading an excerpt of:

.NET Compact Framework 3.5 Data Driven Applications

Explore Title