Palm Database Programming: The Complete Developer's Guide - Softcover

Gigu?re, Eric

Softcover

ISBN 10: 0471354015 ISBN 13: 9780471354017

Publisher: Wiley, 1999

Synopsis

All the tools and information you'll need to write effective applications for Palm devices Palm(TM) Database Programming The demand for developers who can build applications for the Palm OS(TM) platform has never been greater. Now, with this "how-to" guide, you'll gain the necessary skills to program these new devices. Written by a software engineer specializing in Palm OS development, this book covers everything from the basics of Palm devices and writing applications to database synchronization and building database-centric applications. You'll be able to apply the information presented to create database applications, graphical user interfaces, and more. With the help of numerous examples and sample programs integrated throughout this book, you will:
* Learn the key features of the Palm operating system
* Master the development tools needed to build, run, and debug applications
* Write interactive database programs for the Palm platform
* Explore the code behind the Palm user interface
* Learn about Palm databases, relational databases, and database synchronization
* Create Palm applications that synchronize with external databases
* Build applications using Sybase(TM) Ultra Lite and Oracle(r) Lite Consolidator
The CD-ROM provides you with:
* All the examples discussed in the text
* Useful software that guides you through the development process, including a copy of CodeWarrior(r) Lite
* An evaluation version of Sybase's Adaptive Server Anywhere relational database, including the UltraLite deployment technology
* Comprehensive reference material on Palm OS
* Complete working code for current database technologies
The companion Web site at www.ericgiguere.com/palm features:
* Updates on the Palm Computing(r) platform
* Additional information not covered in the book
* Links to other important Palm Computing Web sites

About this title

About the Author

ERIC GIGUČRE is a software engineer in the Mobile and Embedded Computing Division of Sybase, Inc Previously, he was a lead developer on PowerJ, Sybase's Java development tool, where he was responsible for the PowerJ class library and database connectivity. He has written extensively for various magazines, including Dr. Dobb's Journal, Software Development, Computer Language, Byte, and Compute. He also wrote the Programmer's Guide to ARexx for Commodore Electronics. He holds bachelor's and master's degrees in computer science from the University of Waterloo, Ontario.

Excerpt. © Reprinted by permission. All rights reserved.

Excerpt from Chapter 7, Data Synchronization:

The key to the success of the Palm Computing platform is its support for seamless synchronization of data between the handheld device and a desktop computer. Synchronization allows a user to enter data once and have it appear on both machines, no matter where the data was input. In effect, both the handheld device and the desktop computer become extensions of each other. Data synchronization is not, however, a simple matter, and in this chapter we explore what data synchronization is, why its difficult, and what strategies to use when synchronizing with external databases.

What Is Synchronization?

Generally speaking, data synchronization refers to the exchange and transformation of data between two applications that maintain separate data stores. The applications share data, although perhaps stored in different ways, and the synchronization process keeps each applications copy of the shared data current and up-to-date.

Two applications running against the same data store, such as a database server, dont require synchronization because a single copy of the data is used by both applications. Synchronization only comes into play when the same data is spread among two or more separate data stores. On the Palm Computing platform, data synchronization occurs between an application running on the Palm device and a desktop application called a conduit, which we discuss in more detail shortly.

Data synchronization is not new technology. Companies use one-way data synchronization, sometimes referred to as data replication, to mirror databases so that if a database server crashes another is ready to takes its place with minimal loss of data. Some would argue that data replication is not the same as data synchronization and reserve the term data synchronization for a two-way exchange of information.

Data synchronization is also used in sales force automation, where field personnel with laptop computers run personal or standalone database servers that synchronize with the main corporate database servers. This allows field personnel to access and update customer information while disconnected from the corporate network. Any changes are uploaded and applied to the central database the next time the laptop connects to the network. Since data synchronization is usually a two-way process, any changes to the central database are also downloaded to the laptop and applied to the standalone database server. Field personnel are thus able to work whether theyre connected to the network or not.

As you can see from the sales force automation example, synchronization is often used when a network connection isnt always available to connect to a particular data source. The application maintains a local copy of the data so that it can continue running without a network connection. This mode of operation is often referred to as disconnected or, more accurately, occasionally connected computing. Occasionally connected computing isnt limited to corporate applicationsreading and writing electronic mail offline on your home computer is an example of occasionally connected computing. When the computer reconnects to the networksuch as when your home computer connects to your Internet service providerthe local and remote copies of your mailbox are synchronized. Even when a network connection is available, a local database can be used to provide faster response times and to avoid overwhelming a central database. Synchronization can be scheduled to occur when the database server isnt as busy or when the network is less congested. Note that data synchronization refers to data transformation as well as data exchange. Theres nothing that says that two synchronized data stores have to be mirror images of each other. One data store can hold a subset of the data in a second data store. This is often the case with Palm-based applications, for example, because the Palm device simply doesnt have the memory required to hold large amounts of data. The two data stores can also store their data in completely different formats. Its not uncommon, for example, for the standalone database server on a field employees laptop computer to be from a different vendor than the central, corporate database server it synchronizes with. Synchronizing your Palms Address Book application with the address lists held in your desktop computers copy of Lotus Notes is another example of how data is transformed as well as exchanged, because the database used by Notes is quite different from the Address Books database.

Although well understood, data synchronization can be complicated to code, and in the next section well run through an example that shows you why thats the case.

Why Synchronization Is Difficult

You could argue that data synchronization on the Palm Computing platform is actually quite simple: Create a database, set some flags, and the database will be backed up automatically at the next HotSync. But, as we just discussed, synchronization involves more than just backing up your data. Lets explore what synchronization means on the Palm Computing platform and the difficulties it poses.

The Learning Curve

Data synchronization with a Palm device is performed by a conduit. The conduit is an application that runs on the desktop computer, not on the Palm device, and is invoked automatically as part of the HotSync process. The conduit receives the modified data from the Palm device and updates the device with new data. Typically, the conduit is a gateway to another application, communicating directly with the application (which might be a database server) or else knowing how to read and write the applications data files. You can purchase conduits, for example, to synchronize data from the built-in Palm applications including the Address Book or the Date Book to common desktop applications such as Lotus Notes or Microsoft Outlook.

When youre writing your own applications, however, its unlikely that youll find a suitable conduit to do the data synchronization for you. After all, the conduit has to know how data is laid out within the Palm database and how to communicate with the desktop application and/or convert the data to the appropriate desktop format. This then, is the first challenge to doing data synchronization on the Palm Computing platform: Unless you can find a suitable alternative, youre going to have to write your own custom conduit. This means learning another SDK (the Conduit SDK we mentioned in Chapter 3) and a new set of development tools, perhaps even a new language if you want to write a conduit in Java as opposed to C/C++.

Even after you learn all of that, the hardest partthe data synchronization itselfhas to be written, and this requires even more knowledge. When youre synchronizing to a relational database, for example, you have to know how to interface to the database, how to extract the necessary data, and how to ensure data integrity. Its not a bad idea to learn all these things in the long run, but its hard to get started when the learning curve is so steep, even if you split the tasks among two or more people. Given these complexities, custom synchronization is not something you necessarily want to do, but can you avoid it?

A Database Synchronization Example

Lets stop for a moment and work our way through a simple synchronization example, which will allow us to identify the challenges involved in building a conduit to perform custom data synchronization. The example well use is an application called Time Book, which youll find on the CD-ROM in the Samples folder. In this section, we use of the Time Book application to identify the synchronization issues that need to be addressed as part of the development process. Although Time Book synchronizes with a relational database, the issues we identify apply to synchronization with other types of data sources as well.

Time Book Goals

Time Book is an application to track the time spent by a user on a project. Consultants and other professionals who bill clients on an hourly basis obviously need to keep accurate records of the time spent on each project, whether it be at their desks working at a computer or out visiting clients. Even workers who arent billing clients need to keep track of their time, if only for calculating overtime pay or else keeping track of research activities that might qualify for tax credits or other government incentives. Almost any worker can use the Time Book application. And because projects can be shared among several people, a central data store is required to store the information, which means Time Book uses a relational database, running on a database server, as its data repository. Using a database has the added advantage of letting otherssuch as managers, team leaders, or accounting personneltrack the status of projects and prepare reports for various purposes from invoic! ing clients to filing for tax credits.

A desktop version of Time Book can be easily written using such popular development tools as Visual Basic or PowerBuilderanything that lets you connect to a database server and write a GUI-based client. The Palm version of Time Book doesnt have direct access to the database server, however, so it must rely on data synchronization via a custom conduit.

Time Book Walk-Through

Lets say youre the developer whos been tasked to write the Palm version of Time Book, including the conduit. Your first step is to walk through the use of the Time Book application in order to identify the issues youll need to address. The developer of the desktop version (which may be you) will need to perform a similar kind of walk-through (and hopefully youll do it together if there are two or more of you working on the application), but youll have additional problems to solve that the other developer wont run into, all related to the occasionally connected nature of your users.

The Time Book walk-through involves three users. John and Mary are coworkers who often work offsite at client locations. Sue is their manager, who also spends time away from her desk. Each uses the Palm and desktop versions of Time Book, but in this scenario we concentrate exclusively on their use of the Palm version.

The first step is to get the data out of the database and onto the devices. When the users HotSync their devices, the conduit is activated. The conduit interfaces with the database and pulls out the appropriate information. The information for John and Mary is limited to the projects theyre working on or have recently worked on; Sue gets detailed information for all the projects she or her subordinates are working on. Once extracted, the conduit transforms the information into records that are then sent down to the device for storage in a Palm database. After the first HotSync, only changes to the data are downloaded to the device. See Figure 7.1.

The next step is to let users change the information. For example, John and Mary add new entries each day for the projects theyre working on. They may also update data theyve already entered. All these changes are recorded in the Palm database. At the next HotSync, the conduit receives the new and updated records and applies the changes to the database. Because John and Mary only have access to their own time records, there are no conflicts and everything proceeds smoothly. See Figure 7.2.

Sue can also change information, both for herself and for her subordinates. When she HotSyncs, the changes are also applied to the database. Sue also sees any changes that were made by John or Mary. As long as there are no conflicts, the next time John or Mary HotSyncs, the changes that Sue made that apply to them are downloaded to their devices. See Figure 7.3.

If John and Sue both change the same information, however, a conflict will occur when the second user performs a HotSync. The conduit notices that the information in the database has been updated since the last HotSync and that the information in the device has also been updated. The conduit then tries to resolve the conflict, using any of several schemes. For example, the conduit could let one update overwrite the other. Or it could determine that since Sue is Johns manager, her changes should take precedence and report an error back to John along with the new data from Sue. Either way, the conduit has to ensure the integrity of the data in the database so that other users are always presented with the most consistent and correct data. See Figure 7.4.

Synchronization Challenges

User interface issues aside, the Time Book walk-through demonstrates the following synchronization challenges: You have to understand the structure of the relational database and the data your application needs to extract from the database. This is true for any database application, of course, whether or not it runs on a Palm device. With Time Book, the database structure is fairly straightforward, consisting of a set of tables listing employees, clients, projects, and the time entries. If the database doesnt already have the information you need, youll need to define new tables or modify existing tables to hold that information. If youre not using a relational database as the data source, you still need to understand how the data source is structured.

You have to know how to interface with the database. Your conduit needs to talk to the database server in order to read and write the data. It can do this through an ODBC driver (for C/C++ and other languages), a JDBC driver (for Java), or through a set of vendor-specific APIs or language extensions. The Time Book conduit knows where the database server resides and which interfaces it supports. If youre not using a relational database, you need to find similar ways of interacting with the data source, whether it be through a set of vendor-supplied library functions or through automation interfaces such as DDE or OLE.

You have to understand how to map that structure onto your Palm database. The database interfaces your conduit uses arent available on the Palm device, so you have to map the data into Palm database records that the application can easily read and manipulate. The Time Book conduit maps each column it reads from the relational database into a specific offset inside the Palm database record. The Time Book application reads and writes data at those offsets.

You need to decide which subset of data is needed on the device. Memory and speed limitations make it impractical to load the complete set of data on the device. Security and confidentiality considerations also come into play. Time Book users only see the information that is relevant to their role within the company.

You have to ensure data integrity at all times, because the data is going to be used by many different users and/or applications. The Time Book data entered by Mary is used by the accounting department to bill her clients. Business rules in the database and in the Time Book application ensure that Mary enters data that is consistent, such as not billing the same time segment to two different customers. The Time Book conduit ensures that if Mary aborts a HotSync, no incomplete data gets entered in the database.

You have to detect and resolve conflicts when synchronizing data with multiple clients. This problem exists in any multiuser system, but users who are directly connected to a database server are more likely to see up-to-date information and avoid conflicts than disconnected users. A Time Book user who doesnt HotSync for several days may have made changes based on old information (from the previous HotSync) without knowing that someone else made similar changes in the central database the day before. The Time Book conduit has to resolve those changes and ideally notify any users whose changes were rejected or modified. When you think about it, however, most of these tasks are routine and could be automated to some degree: Interfacing with the database, mapping the data, ensuring data integrity, and detecting conflicts would differ very little in each conduit you write. In fact, youd probably package these tasks as separate libraries or DLLs to be shared among your conduits. With a bit of work you could even make the code general enough to distribute to others outside your organization.

This, in fact, has been done by at least two database vendors, as we see in the next two chapters, who have developed conduits you can use with your own applications for transferring data to and from a relational database. These conduits drastically reduce the learning curve by taking care of routine synchronization tasks, leaving you to concentrate on your applications synchronization strategies. Instead of spending your time writing a conduit, you concentrate on the Palm application and the database configuration.

Strategies for Synchronization

The nonroutine synchronization tasksdata extraction and subsetting as well as conflict resolutionchange from application to application depending on the applications requirements and your organizations business rules. Whether youre writing your own conduit or using someone elses, only you, the application developer, can decide which strategies to use. This section discusses the important issues when it comes to determining the strategies for each task.

Extracting and Subsetting Data

A relational database contains more data than your application requires. Even if the database is only used by your application, the self-describing nature of a relational database means that it includes system tables to hold database metadata in addition to the tables used by the application. Your first task is to subset the data so that youre only extracting data your application requires, in the format best suited to its purposes. Conceptually, this is done by writing a SELECT statement that defines the subset, although your synchronization software may present it differently. The following strategies are available.

Incremental Synchronization

If possible, synchronization with a database should be incremental. In other words, only the rows in a database table that are new or have changed since the last synchronization should be downloaded to the device. The easiest way to do this is to insert a timestamp column into the table and to update the timestamp for a row each time the row is modified (use a trigger to automatically execute a SQL statement that updates the timestamp). The application stores the timestamp of its last update and uses that value to perform further row partitioning by excluding the rows with lesser timestamps. Note that the timestamp column does not need to be synchronized onto the device. See Figure 7.5 for an example.

If incremental synchronization is not used, then all rows that match the selection criteria participate in the synchronization process. This is sometimes referred to as refreshing the data or performing a snapshot synchronization. Small tables or tables with frequent changes are good candidates for refreshing as opposed to incremental synchronization. While developing your application you may also find it useful to include a way to perform snapshot synchronizations as you develop and test your data extraction and conflict resolution strategies.

Column Partitioning

Column partitioning means selecting only the columns in a table that are required by your application. The other columns are not synchronized. Note that you shouldnt just select the columns that are displayed by your application, although thats a good startyour application may require other columns for proper functioning. In particular, the primary key column (or columns) is almost always necessary, because it uniquely identifies each row in the table. See Figure 7.6.

Although it may surprise you, consider adding computed columns to the set of synchronized columns if the overhead of storing the computed values on the device is minimal when compared to the time to perform the same calculations inside your application.

Row Partitioning

Row partitioning is very similar to column partitioning, only it deals with the rows of a table instead of the columns. The premise is quite simple: select only the rows that are required by the user of your application. For example, the Time Book example uses row partitioning to ensure that John and Mary see only their own time entries, and not anyone elses. Row partitioning is done by adding a WHERE clause to the SELECT statement. See Figure 7.7.

Explicit Type Conversions

The data in the database isnt always stored in the format that is the most efficient or convenient for your application. You can explicitly convert data values to other types using the CAST function or its equivalent. It might make sense, for example, to convert a fixed-length character column to a variable-length character column to save some space on the device: SELECT CAST( Address AS VARCHAR(100) ) FROM CUSTOMER Explicit conversions on updateable columns must always be reversible. That is, the database server has to be able to implicitly convert any updated values to the correct type for the column if the update is to succeed.

Resolving Conflicts

A conflict occurs when two or more users modify the same piece of data independently. To detect conflicts, the application must store both the old and the new data values. When synchronization occurs, a conflict is detected by first comparing the old value stored by the application to the current value stored in the database. If the two are equal, no conflict occurs and the new value is stored in the database. Otherwise, another user or application has already modified the value in the database and a choice must be made as to which value to keepthe (already-modified) value stored in the database or the new value from the application, or perhaps a combination of the two values. No single conflict resolution strategy is appropriate for all applications, so your application must decide how conflicts are to be handled. What follows are some common strategies.

Conflict Avoidance

The simplest strategy is to avoid conflicts altogether. If users arent allowed to make changes, no conflicts can occur, for example. Or you can define mutually exclusive data subsets for each user, so that no user can change another users data.

One Side Always Wins

If conflicts cant be avoided, the next simplest strategy is that one side of the synchronization conflict always wins. In other words, either the new value from the device overwrites the current (already-modified) value in the database, or else the value in the database overwrites the value in the application. If the database always wins over the device, your application will need to inform the user that the update failed and perhaps give the user the opportunity to reapply the change if it makes sense.

Both Sides Win

Another strategy is to allow both changes to occur. The built-in Palm applications offer this choice, for example, ensuring that no changes are ever lost when a conflict occurs. However, when updating data in a relational database, this strategy rarely makes sense, because the synchronization procedure identifies the table rows to synchronize by their primary keys, and the primary key of a row must always be unique. For both sides to win, a copy of the row, identical except for the data in question, would have to be inserted into the table, and this would violate the uniqueness constraint. The only way for this to work would be to generate a new primary key for the copy to use. Its better to report an error than to have duplicate rows.

Custom Strategies

If none of the simpler strategies fit the bill, you can always implement your own custom conflict resolution rules. You could allow a managers changes to always override those of his or her subordinates. You could merge two changes together. Or you can even send the conflict to a human for arbitration.

Primary Key Pooling

One situation where conflict avoidance is desired is in the assignment of primary keys. When the application adds data to the database, the synchronization process will assign primary keys for any new rows that are inserted into a table. For consistency, the primary key always has to be assigned by the database server, otherwise two devices could choose the same primary key value and create an unexpected conflict.

Primary key conflicts are avoided by creating a pool of unused primary key values and assigning each device a unique range of values from the pool. The application uses primary key values from the devices pool wherever possible. At each HotSync, the primary key pool is updated to reflect any changes made by the application and to add new values if necessary.

While the assignment of new primary keys can be automated, the size of the pool and the ranges within the pool depends on the application. If the application runs out of primary key values, it should either prevent the user from entering new data (and tell the user to HotSync as soon as possible) or else explicitly handle primary key conflicts.

Edition details

• Publisher: Wiley
• Publication date: 1999
• Language: English
• ISBN 10: 0471354015
• ISBN 13: 9780471354017
• Binding: Paperback
• Edition number: 1
• Number of pages: 358