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:
SELECT “TASKID”, A.”ACCOUNTGUID”, “TASKSUBJECT”,
“TASKDESCRIPTION”, “TASKCREATED”, “TASKDATE”, “TASKSTATUS”
FROM MASTER.ACCOUNTTASKS A, MASTER.ACCOUNTS B WHERE
A.ACCOUNTGUID=B.ACCOUNTGUID AND B.OWNERID = :OwnerID
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:
SELECT “PRODUCTID”, “PRODUCTCODE”, “PRODUCTNAME”, “PRODUCTPRICE” FROM
MASTER.Products
After you’ve done this, you can now move on to creating the “sequences” necessary in this mobile application.
