1 Oracle Database Lite Concepts

The following sections provide an introduction to Oracle Database Lite and its components:

1.1 Overview of Oracle Database Lite

A common misconception of Oracle Database Lite is that it is just a simplified small-scale database designed to run on laptops, handhelds, cell phones, and so on. While you can use it in this manner (see Section 1.2.1, "Embedded Application in Single Process"), this is only one model for using the product. Instead, Oracle Database Lite provides a complete mobile infrastructure designed to run enterprise database applications within a world constantly on the go. Oracle Database Lite provides the infrastructure that makes the enterprise application and data store available even when communications to the enterprise itself are not available or reliable. Oracle Database Lite brings the applications that were once limited to the base office and deploys these applications out into the world where they are most needed.

Oracle Database Lite can remove some of the manual processes performed out in the field. In the past, you may have manually written down the information in the field and then manually entered the data in the enterprise database once you returned to the corporate environment. With Oracle Database Lite, you can manually capture the data once—in the field by entering the data into a client device. Then, this data is synchronized up to the enterprise without returning to the office to manually enter data. This removes the loss of productivity due to manual processes and sends the data immediately to the enterprise where it belongs. In addition, data can flow bi-directionally. If you need information at the remote site that has been updated at the office, this data is brought down to the client device during synchronization.

1.1.1 What Is A Mobile Architecture?

The mobile architecture completes the enterprise system by merging the enterprise infrastructure with every remote aspect of the organization. Previously, the remote location was missing from the enterprise design. A mobile architecture contains the remote application, the remote data store, and the remote rules of the business. The Oracle Database Lite mobile infrastructure is responsible for connecting and synchronizing applications, associated data, and business rules with the applications, data store, and business rules of the enterprise.

There are several ways you can use and implement Oracle Database Lite. See Section 1.2, "Execution Models for Oracle Lite Database" for more details.

1.1.2 What Are The Benefits Of A Mobile Architecture?

A mobile architecture with the proper design, proper security components, and proper implementation saves money. Normally, you manually capturing data on site and then, when you get back to the office, manually enter the data into the office database. With proper design, your mobile application combines these steps into a single step of capturing the data at the remote location, which is then synchronized with the back-end database at the office.

This saves time and enables remote agents the capability of performing more tasks on site without returning to an office for the manual process of entering the captured data.

You can use the mobile architecture in several types of application environments, as follows:

  • Mobile option—An application is created, where the user enters data on a client device and the data is synchronized with a back-end Oracle database. For example, if you have a sales force, each sales person retrieves only his/her data on the client device. Any modifications made on either the client device by the sales person in regards to his/her accounts or modified on the server by the office can be synchronized.

  • Embedded software option—An application may need an independent small database to exist solely for the application's use. No synchronization of data with a back-end database is necessary. For example, if you have an individual accounting application, it may need a small embedded database to store the data for the individual accounting data.

  • Embedded hardware option—A hardware unit may need an embedded database to facilitate gathering information, which is then synchronized with a back-end database for the office to evaluate what is happening with the remote hardware unit.

    For example, a vending machine can use the Oracle Database Lite infrastructure to maintain inventory, control the dispatching of technicians and restock personnel, gather marketing statistics, and so on.

    Another example is a system included in an automobile—known as an in-vehicle system. The mobile infrastructure tracks and communicates maintenance needs for the automobile to the automobile owner and service department. When maintenance needs are proactively found, the customer saves on repair costs, towing and expensive part replacement that may otherwise have occurred.

1.1.3 Why Use Oracle Database Lite?

Oracle Database Lite provides a complete mobile infrastructure suitable for almost any enterprise demands using the following:

  • The Mobile repository resides in the back-end enterprise database, which links the enterprise data with the mobile data.

  • The Mobile Server is a Web-based tier that integrates with OracleAS and executes on top of the Oracle Containers for J2EE (OC4J). This accesses remote locations through different types of wireless or wired connectivity. It facilitates the major functions for the Mobile option, such as synchronization, application management, device management, and so on.

  • The Mobile client uses a client database, called the Oracle Lite database (ODB file), and the means for deploying applications developed using the most popular languages, such as C#, VB, VB.Net, Java, and C/C++. These clients can be executed on most any device from a cell phone, to a personal digital assistant (PDA), Tablet PC, Laptop, and so on.

1.2 Execution Models for Oracle Lite Database

Oracle Database Lite facilitates the development, deployment, and management of Mobile database applications for a large number of mobile users. A Mobile application is an application that can run on mobile devices without requiring constant connectivity to the server. The application requires a small, local database on the mobile device, whose content is a subset of data that is stored in the enterprise data server. This database is known as the Oracle Lite database (ODB file). Modifications made to the local database by the application are occasionally reconciled with the back-end server data. The technology used for reconciling changes between the mobile database and the enterprise database is known as data synchronization.

How your application integrates with the Oracle Lite database depends on the design of your application, as described in the following sections:

  1. Do you want to use the small footprint Oracle Lite database to store data embedded in a single application? If you have an application that needs a database with a small footprint for client devices, but you do not need to have the client data synchronized with a back-end Oracle database, then the embedded option is for you. See Section 1.2.1, "Embedded Application in Single Process" for more information.

    Other embedded application models are as follows:

  2. Do you want to use the Oracle Lite database to store changes that will be synchronized with a back-end Oracle database? You can design an application where the data is stored in a back-end Oracle database, but only the data that the user needs to see or update is downloaded to the client device. When either side modifies the data, it is synchronized between the client device and the back-end database. This is known as the Mobile client option. See Section 1.2.4, "Mobile Option for a Client in a Single Process" for more details.

    Alternatively, you can have a more complex situation where you want your application to access the database remotely and include the ability to synchronize the client data. If so, then see Section 1.2.5, "Mobile Option for Multiple Clients Accessing Remote Database" for more information.

1.2.1 Embedded Application in Single Process

As demonstrated in Figure 1-1, if you chose to build a standalone application with the Oracle Lite database embedded in the application, then when the application is launched, the Oracle Lite database libraries are loaded into the same process as the application.

Figure 1-1 Embedded Application With ODB Libraries in Single Process

Description of Figure 1-1 follows
Description of "Figure 1-1 Embedded Application With ODB Libraries in Single Process"

See Section 2.3, "Creating and Managing the Database in an Embedded Application" in the Oracle Database Lite Developer's Guide for more information on how to embed an Oracle Lite database into a standalone application.

1.2.2 Multiple Processes Accessing the Same Database

Figure 1-2 shows how you can configure multiple application processes to share the same database on the same machine. Thus, when each application is launched, each application exists in its own process and can access the same database independently. In this scenario, Oracle Database Lite libraries use shared memory to coordinate locking between both processes.

Figure 1-2 Applications in Multiple Processes Accessing Single Database

multiple process applications with single database.
Description of "Figure 1-2 Applications in Multiple Processes Accessing Single Database"

For details of how to package an embedded Oracle Lite database in your application, see Section 2.3, "Creating and Managing the Database in an Embedded Application" in the Oracle Database Lite Developer's Guide.

Note:

This scenario is not available on WinCE.

1.2.3 Multiple Embedded Application Clients Accessing Remote Database

If you are embedding a database into your application software, but you want the applications to be located on clients that are remote from the data within the Oracle Lite database, then use the client/server embedded approach with the multi-user service, as described in Section 2.5, "Oracle Database Lite Multi-User Service" in the Oracle Database Lite Developer's Guide.

1.2.4 Mobile Option for a Client in a Single Process

If you chose to install the Mobile client and synchronized your user on a single device, then when you launch your application, the Oracle Lite database libraries are loaded into the same process as your application. This scenario is demonstrated in Figure 1-3.

Figure 1-3 Diagram of Mobile Client and ODB Libraries in SIngle Process

Mobile client and ODB libraries in single process
Description of "Figure 1-3 Diagram of Mobile Client and ODB Libraries in SIngle Process"

For details of how to create a Mobile application using the Oracle Lite database, see Section 2.2, "Creating and Managing the Database for a Mobile Client" in the Oracle Database Lite Developer's Guide.

1.2.5 Mobile Option for Multiple Clients Accessing Remote Database

If you have several remote clients accessing the same data, you can use Branch Office to facilitate the remote applications. Figure 1-4 demonstrates how multiple remote Branch Office clients access the data through the Branch Office machine to the Mobile Server and finally accessing the back-end Oracle database.

The Branch Office machine contains the Branch Office executables and the local Oracle Lite database, which all clients access for their information. When a synchronization is requested, information is communicated between the Branch Office and the back-end database through the Mobile Server.

Note:

Oracle Database Lite is not identical to the Oracle database; thus, it is not designed for large amounts of transferred data or a large number of concurrent transactions.

Figure 1-4 Using Branch Office for Managing Multiple Clients that Access a Remote Database

Branch Office accessing Mobile Server remotely
Description of "Figure 1-4 Using Branch Office for Managing Multiple Clients that Access a Remote Database"

See Chapter 10, "Manage Your Branch Office" in the Oracle Database Lite Administration and Deployment Guide for more information.

1.3 Oracle Database Lite Application Models

If you are using the embedded model, as described in Section 1.2.1, "Embedded Application in Single Process", then you can create the tables and access the information as you would any RDBMS. However, if you want to use the Mobile option, you need to define the data in the snapshot.

The following sections describe how to define the data and subscribe users to this data.

1.3.1 Publish-Subscribe Model for Mobile Users

Oracle Database Lite operates within a publish-subscribe model. We use the example of the magazine as an effective way to explain the publish-subscribe model. A magazine is created with specific data that would be of interest to readers, such as sports, hunting, automobiles, and so on. Readers request a subscription for the specific magazine they feel would be in their interest to read. Once this subscription is created only the magazines to which the reader has been subscribed are sent to the reader.

For Oracle Database Lite, the publication is the magazine, the publication items are the specific articles of data and the subscription is the granting of access to the publication for specific users. In the Oracle Database Lite application model, each application defines its data requirements using a publication. Data subsets, known as publications items, are created and added to a publication. Application files are also uploaded to the same publication. Once these publications are deployed to the Mobile Server, any user may be granted a subscription to the publication.

Technically, a publication is akin to a database schema and it contains one or more publication items. A publication item is like a parameterized view definition and defines a subset of data, using a SQL query with bind variables in it. These bind variables are called subscription parameters or template variables.

As shown in Figure 1-5, a subscription defines the relationship between a user and a publication. Once you subscribe to a particular publication, you begin to receive information associated with that publication. With a newspaper you receive the daily paper or the Sunday paper, or both. With Oracle Database Lite you receive snapshots, and, depending on your subscription parameter values, those snapshots are partitioned with data tailored for you.

Subscription parameter values can be set by the administrator in order to tailor the snapshot data for each user.

Figure 1-5 Subscription Defines Relationship Between User and Publication

subscription
Description of "Figure 1-5 Subscription Defines Relationship Between User and Publication"

The subscription is the definition of how to retrieve data from the back-end database; the snapshot is the actual data that conforms to the definition within the subscription and which belongs to the user.

This process really forms a simple development cycle for mobile applications, as follows:

  1. Create the publication and its publication items that contains the data subset for a particular application.

  2. Grant users a subscription to a publication. This forms the specific dataset that is used on a Mobile client.

  3. Develop and test the Mobile application to work with the specific data set.

  4. Deploy the application to the Mobile Server and install it on the client.

Two of the more common questions and sources of confusion that comes up are what has to be done first:

  1. Do you create the publication first or the publication items?

    It does not matter. You can create either the publication or the publication item first. Consider an article for a magazine. That article may have been written by a freelance author. The article exists before it belongs to any publication. The author submits this to two or three magazine publishers since it is relevant to the content they advertise. Two decide it is appropriate for the publication they are distributing currently while one does not include it since the content is not quite what their readers want.

  2. Do you have to create a separate publication item for each publication?

    No, you can have one or more publication items in a publication.

Note:

You can create publications with the Mobile Database Workbench or the Java APIs.

The following sections describes other pertinent information for publication items:

1.3.1.1 Defining the Weight and Conflict Resolution for Publication Items

The following important aspects of the publication item should be taken into account when you are designing your application:

  • Weight—The publication item weight is used to control the order in processing publication items, which avoids conflicts. Changes made on the client are processed according to weight in order to prevent conflicts, such as foreign key violations. The weight determines what tables are applied to the enterprise database first. For example, the scott.emp table has a foreign key constraint to the scott.dept table. If a new department number is added to the dept table and a new record utilizing the new department number were added to the emp table, then the transaction would be placed in the error queue if the new record utilizing the new department in the emp table was applied to the repository before the new department in the dept table was applied. To prevent the violation of the foreign key constraint on the enterprise server, you set the dept snapshot to a weight of 1 and the emp snapshot to a weight of 2, which applies all updates to the dept table prior to any updates to the emp table as the lower weight is always processed first.

  • Conflict Resolution—In the same scenario, what if someone already updated the enterprise server with the new department number? This causes a conflict when the client attempts to synchronize with the new department that utilizes the same number. To handle this, conflict resolution may be set to either "client wins" or "server wins". If set to "server wins", then the setting on the server takes precedence to the setting on the client. The client transaction is sent to the error queue. However, if "client wins" is set, then the new department number from the client overrides the setting on the server.

1.3.1.2 Behavior and Requirements for Primary Keys, Foreign Keys and Not Null Fields in Publication Items

Only primary keys and not null fields are replicated down to the client. Publication items require a primary key field or, as in the case of a view, primary key hints.

If a foreign key needs to be applied to the client, then the script for the foreign key needs to be added to the publication, so that it will be executed when the client synchronizes the first time. You can set the script for the foreign key within either the MDW scripts section or the API.

Constraints are not the only type of script that may be executed on the client. The script could execute any valid SQL DDL statement on the client.

1.3.2 Client Mobile Database Created on First Synchronization

When a user synchronizes the Mobile client for the first time, the Mobile client creates an Oracle Lite database on the client machine for each subscription that is provisioned to the user. The Mobile client then creates a snapshot in this database for each publication item contained in the subscription, and populates it with data retrieved from the server database by running the SQL query (with all the variables bound) associated with the publication item. Once installed, Oracle Database Lite is transparent to the end user; it requires minimal tuning or administration.

As the user accesses and uses the application, changes made to the data in the Oracle Lite database are captured by the snapshots. When the connection to the Mobile Server is available, the changes can be synchronized with the Mobile Server.

See Section 1.4, "How Oracle Database Lite Synchronizes" for more details.

1.4 How Oracle Database Lite Synchronizes

When most people think of synchronizing data, they think of their Palm Pilot. When you hit the synchronization button for the Palm Pilot, any changes are added to the database of information on the Windows machine immediately. This is not the case for Oracle Database Lite, in that the synchrnization is used for multiple clients—rather than a single user. In order to accomodate a large number of concurrent users, the application tables on the back-end database cannot be locked by a single user. Thus, the synchronization process involves using queues to manage the information between the Mobile clients and the application tables in the database.

Oracle Database Lite uses a synchronization model that maintains data integrity between the Mobile Server and the Mobile client. In addition, the synchronization is asynchronous and that as a result, change propagation is not immediate. The benefit, however, is that the clients do not stay connected for long while the changes are being applied.

You can specify if the synchronization occurs automatically or by manual request. For more details, see Section 1.4.1, "Deciding on Automatic or Manual Synchronization".

A simplified view of Mobile synchronization is as follows:

  • On the client—The Mobile application communicates through the Mobile Sync Server with the Mobile Server and uploads the changes made in the client machine. It then downloads the changes for the client that are already prepared by the Mobile Server.

  • On the Mobile Server—A background process called the Message Generator and Processor (MGP), which runs in the same tier as the Mobile Server, periodically collects all the uploaded changes from many Mobile users and then applies them to the server database. Next, MGP prepares changes that need to be sent to each Mobile user. This step is essential because the next time the Mobile user synchronizes with the Mobile Server, these changes can be downloaded to the client and applied to the client database.

Figure 1-6 illustrates the architecture for Oracle Database Lite applications.

Note:

This section describes how the synchronization is performed across several components and enterprise tiers to complete successfully. For more details on each component, see Section 1.6, "Oracle Database Lite Components Involved in Synchronization".

Figure 1-6 Oracle Database Lite Architecture

Oracle Database Lite architecture
Description of "Figure 1-6 Oracle Database Lite Architecture"

Note:

Web-to-Go clients have one additional component, a light weight HTTP listener that is not shown in the diagram.

Oracle Database Lite uses the Mobile Server to replicate data between the Mobile clients with their client Oracle Lite databases (including those for OC4J, Web-to-Go, Win32, Windows CE, Symbian, and Linux platforms) and the application tables, which are stored on a back-end Oracle database.

Thus, the more detailed description of how synchronization is performed within the separate components of Oracle Database Lite is demonstrated by Figure 1-7.

Figure 1-7 Data Synchronization Architecture

synchronization architecture.
Description of "Figure 1-7 Data Synchronization Architecture"

  1. A synchronization is initiated on the Mobile client either by the user or from automatic synchronization. Note that the Mobile client may be a Windows platform client or a PDA.

  2. Mobile client software gathers all of the client changes into a transaction and the Sync Client uploads the transaction to the Sync Server on the Mobile Server.

  3. Sync Server places the transaction into the In-Queue.

    Note:

    When packaging your application, you can specify if the transaction is to be applied at the same time as the synchronization. If you set this option, then the transaction is immediately applied to the application tables. However, note that this may not be scaleable and you should only do this if the application of the transaction immediately is important and you have enough resources to handle the load.

  4. Sync Server gathers all transactions destined for the Mobile client from the Out-Queue.

  5. Sync client downloads all changes for client Oracle Lite database.

  6. Mobile client applies all changes for client Oracle Lite database. If this is the first synchronization, the Oracle Lite database is created.

    Note:

    For information on what Oracle Lite database (ODB) files are installed on the client, see Section 2.2, "Synchronizing or Executing Applications on the Mobile Client"in the Oracle Database Lite Administration and Deployment Guide.

  7. All transactions uploaded by all Mobile clients are gathered by the MGP out of the In-Queue. The MGP executes independently and periodically based upon an interval specified in the Job Scheduler in the Mobile Server.

  8. The MGP executes the apply phase by applying all transactions for the Mobile clients to their respective application tables to the back-end Oracle database. The MGP commits after processing each publication. If any conflicts occur during this phase, most are resolved by the MGP or by the conflict resolution rules. If the conflict cannot be resolved, the transaction is moved into the Error Queue. See Section 1.3.1.1, "Defining the Weight and Conflict Resolution for Publication Items" for more information.

    Note:

    The behavior of the apply/compose phase can be modified. See Section 5.1.1, "Defining Behavior of Apply/Compose Phase for Synchronization" in the Oracle Database Lite Administration and Deployment Guide for more information.

  9. MGP executes the compose phase by gathering the client data into outgoing transactions for Mobile clients.

  10. MGP places the composed data for Mobile clients into the Out-Queue, where the Sync Server downloads these updates to the client on the next client synchronization.

Overall, synchronization involves two parties: the Mobile client using the Sync Client/Server to upload and download changes and the MGP process interacting with the queues and the application tables to apply and compose transactions. These are displayed separately in the Data Synchronization section of the Mobile Manager.

1.4.1 Deciding on Automatic or Manual Synchronization

In the past, all that was available was manual synchronization. That is, a client manually requests a synchronization either through an application program executing an API or by a user manually pushing the Sync button.

Now, you can specify that synchronization happens automatically or only when manually requested.

  • Manual Synchronization may be initiated, as follows:

    • The user initiates the Oracle Database Lite Mobile Synchronization (msync) application directly.

    • The application programmatically invokes the Mobile Synchronization API.

    • Oracle Database Lite has a Web-based application model, known as Web-to-Go. For this type of application, the synchronization option can be defined from the Web-to-Go workspace to synchronize the data. See Section 1.5.3, "Supported Languages for Building Mobile Applications" for more information.

  • Automatic Synchronization—You can configure for synchronization to automatically occur under specific circumstances and conditions. When these conditions are met, then Oracle Database Lite automatically performs the synchronization for you without locking your database, so you can continue to work while the synchronization happens in the background. This way, synchronization can happen seamlessly without the client's knowledge.

For example, you may choose to enable automatic synchronization for the following scenarios:

  • If you have a user who changes data on their handheld device, but does not sync as often as you would prefer.

  • If you have multiple users who all sync at the same time and overload your system.

These are just a few examples of how automatic synchronization can make managing your data easier, be more timely, and occur at the moment you need it to be uploaded.

Synchronization is closely tied to how you define the snapshot for your application. See Section 1.3.1, "Publish-Subscribe Model for Mobile Users" for a description of a snapshot and its components. One of the components is a publication item. If you want automatic synchronization, you define it at the publication item level.

Note:

When a manual synchronization is requested by the client, ALL publication items are synchronized at that time—including those defined as manual and automatic synchronization. However, if an automatic synchronization is currently executing, the manual synchronization request is delayed until the automatic synchronization completes. Alternatively, you can stop the automatic synchronization to allow the manual synchronization to occur. If you choose to do this, then after the manual synchronization is finished, re-start the automatic synchronization.

The differences between the two types of synchronization are as follows:

Table 1-1 Difference Between Automatic and Manual Synchronization


 Manual Synchronization Automatic Synchronization

Initiation

After the snapshot is set up, you can initiate either by the user initiating mSync or by an application invoking one of the synchronization APIs.

All of the set up for automatic synchronization is configured. Once configured, it happens automatically, so there is no synchronization API.

Configuration for automatic synchronization can be defined when you create the publication item, publication or the platform.

Controlling synchronization

Synchronization occurs exactly when the user/application requests it.

Synchronization occurs without the user being aware of it occuring. You may have to manage synchronization through the Sync Control API if you have publications that contain both manual and automatic synchronization publication items.

For more information on Automatic Synchronization, see Section 3.2, "Automatic Synchronization Overview" in the Oracle Database Lite Developer's Guide.

1.4.2 Deciding on Synchronization Refresh Option

How or when data changes are applied to either the Mobile Server or the Mobile client depends upon the synchronization refresh option at the publication item level. Synchronization refresh options may ease the cost burden for resources, such as wireless connectivity, bandwidth and network availability, personnel loss of time during the synchronization process, and so on.

Oracle Database Lite employs synchronization refresh options that may be utilized to synchronize data between the Oracle enterprise database and the Mobile client. With the following Oracle Database Lite refresh options, you can maintain data accuracy and integrity between the database and Mobile client:

1.4.2.1 Fast Refresh

The most common method of synchronization is a fast refresh publication item where changes are uploaded and downloaded by the client. Meanwhile, the MGP periodically collects changes uploaded by all clients and applies them to the back-end Oracle database tables. Then, the MGP composes new data, ready to be downloaded to each client during the next synchronization, based on pre-defined subscriptions.

1.4.2.2 Complete Refresh

During a complete refresh, all data for a publication is downloaded to the client. For example, during the first synchronization session, all data on the client is refreshed from the Oracle database. This form of synchronization takes longer because all rows that qualify for a subscription are transferred to the client device, regardless of existing client data.

The complete refresh model is resource intensive as all aspects of synchronization are performed. This model should only be utilized for snapshots/publication items where it is an absolute requirement.

1.4.2.3 Queue-Based Refresh

The developer creates their own queues to handle the synchronization data transfer. There is no synchronization logic created with a queue-based refresh; instead, the synchronization logic is implemented solely by the developer. A queue-based publication item is ideally suited for scenarios that require synchronization to behave in a different manner than normally executed. For instance, data collection on the client; all data is collected on the client and pushed to the server.

With data collection, there is no need to worry about conflict detection, client state information, or server-side updates. Therefore, there is no need to add the additional overhead normally associated with a fast refresh or complete refresh publication item.

1.4.2.4 Forced Refresh

This is not a refresh option; however, we discuss it here because it is often mistaken for a refresh option—specifically, it is often confused with the complete refresh option. The Forced Refresh is a one-time execution request made from within Mobile Manager, the GUI interface for the Mobile Server. The forced refresh option may result in a loss of critical data on the client.

The forced refresh option is an emergency only synchronization option. This option is used when a client is corrupt or malfunctioning, so that you decide to replace the Mobile client data with a fresh copy of data from the enterprise data store with the forced refresh. When this option is selected, any data transactions that have been made on the client are lost.

When a forced refresh is initiated all data on the client is removed. The client then brings down an accurate copy of the client data from the enterprise database to start fresh with exactly what is currently stored in the enterprise data store.

1.5 Mobile Application Design

Before you start to design your Mobile application, it is important to read the following sections to understand the differences between an enterprise application and the mobile application as well as the choices you have in designing your application:

1.5.1 Steps for Designing Your Mobile Application

With a proper design, you can avoid the most common causes for mobile project failure, not meeting the needs of the business, poor performance, or issues occurring within a production environment. Proper design of the Mobile System includes the infrastructure and the mobile application. Without proper design, a mobile architecture could end up costing more then it saves.

The following assumption is one of the most common misconceptions for taking an enterprise application and incorporating it into a mobile component:

The mobile application is a scaled down version of the enterprise application.

By taking an existing enterprise application, you may intend to provide the same functionality in remote or disconnected locations. Since the enterprise application has already undergone thorough requirements gathering, design, development, testing, and successful implementation, you may assume that it will automatically work seamlessly as a mobile application and so do not test it in this environment. This assumption may lead to project failure.

For example, take an enterprise form-based client/server application. You have a client connecting through a middle-tier connecting to a database on the back-end. Taking this to a mobile infrastructure, such as with Oracle Database Lite, adds a completely new tier that did not exist within the original infrastructure. The Mobile Server tier introduces new concerns, such as the following:

  • Security—There is now a system in the infrastructure that potentially gives any outsider access to the entire organization if proper security configuration and implementation is not performed.

  • Bandwidth—The Mobile Server may become a bottleneck for all remote locations without the implementation of a Web farm.

  • Scalability—The applications are performing synchronization of hundreds to millions of records, which is not the same as providing static Web pages to a large number of users. A system that is fine for serving static Web pages may not be capable of servicing hundreds of users performing synchronization.

A complete redesign of the system specifications may be in order.

You may also need to re-evaluate the original design of the enterprise application. The following lists a few design considerations for the mobile application:

  • Memory—An application designed, tested, and implemented on a multiprocessor system with several gigabytes of memory will not perform the same on a standard PC with only a single processor and maybe 512 megabytes of memory.

  • Resource Limitations—Several years ago, limitations of available resources made the usage of data types an extreme concern. The storage space saved by using a small integer over an integer was crucial due to limited memory available. With advances in memory and system resources, this has not been a concern to most modern developers. Now, the mobile infrastructure brings resource limitations back to the list of chief concerns for the design and development of mobile applications. One of the most significant of these limitations is the bandwidth available for the mobile client. If a Mobile client is only able to synchronize over a cell phone network, you may not wish to bring a million records down to a client that only needs a few thousand records. This decision impacts the synchronization performance, as well as the costs associated with the synchronization. If the mobile client was only utilized to collect data, then you can create a data collection queue for synchronization and avoid the whole download phase of synchronization.

  • Use of Indexes—You use indexes for avoiding full-table scans. So, if you use the same data subset originally designed for a Windows machine down on a client device and do not use an index, then the performance may be adversely effected. Oracle Database Lite uses two types of scans for queries: full table scans and index based scans.

Thus, we recommend the following steps:

1.5.1.1 Read the Documentation Before Design

To ensure that you develop your applications correctly, we recommend that you start with a light scan of all of the documentation followed by detailed reading of pertinent areas related to the specific project that are to be undertaken.

1.5.1.2 Gather Mobile Requirements

Gathering requirements is the key to successful project development; a mobile project is no exception.

Mobile users have a specific task they perform. Gather the specific requirements of each mobile user type that will use the mobile application. For example, an insurance application may have two types of mobile users:

  • The insurance investigator may only drive to locations to photograph an incident for an insurance claim before any required evidence is altered or vanishes. This user needs a mobile application that captures and uploads the images of the incident.

  • A claims agent may later be sent to the site to process a claim for the client. This user needs more details for the client, such as their account information and what they are covered for in order to properly process a claim.

Each user requires a different subset of the enterprise data and functionality within the mobile application.

1.5.1.3 Proof of Concept

Proof of concept testing determines if the Oracle Database Lite mobile architecture meets the needs of the business before architectural design starts.

When an aspect of the mobile solution is determined to meet a requirement, a quick proof of concept test ensures that the requirement may be satisfied by the potential solution. There is a difference in what can be done in a mobile environment versus what can be accomplished in the enterprise environment.

1.5.1.4 Prototype

Prototyping may be seen as too costly to utilize for the proper design of a mobile architecture and application. However, consider that a prototype may be anything from sketching out a design on paper to writing a full application prototype.

You may use one of the sample applications included with the Oracle Database Lite product, such as the transport demo, to gain a full understanding of the infrastructure and the design considerations it involves. Take the application through the setup of the environment, the creation of users, the deployment of the mobile application to multiple clients, the synchronization of multiple clients, and the testing of updates made to the application. This provides a better understanding of the infrastructure as actual hands on experience.

1.5.1.5 Design for Data Subsets

Data subsetting reduces data to be downloaded, which has a direct impact on performance.

What is Data Subsetting? Data subsets are a crucial part of the design for a mobile application. Data sub-setting is accomplished through variables that limit the data to a specific requirement, such as by region or department. An easy way to conceptualize data subsets is to consider a subset that is limited through a WHERE clause that uses bind variables—also known as snapshot template variables—to limit the snapshot data.

When you set up your publication item, you may have set up an input parameter that defines what snapshot of data is to be retrieved for this user. For example, if you have an application that retrieves the customer base for each sales manager, the application needs to know the sales manager's identification number to retrieve the data specific to each manager. Thus, if you set up each sales manager as a unique user and set their identification number in the data subsetting screen, then the application is provided that unique information for retrieving data.

1.5.1.6 Design for Indexing

Oracle Database Lite maintains two basic methods for accessing data within a table: full table scan and index-based access. The performance of an application can be affected if the design does not include the appropriate indexing. Use indexes to avoid full table scans.

You can execute the Explain Plan and SQL Trace capabilities within the product to determine the access method used by a statement and to determine if indexes are being utilized. Analyze your statements throughout the design and development cycles to ensure that they are being designed and developed for maximum efficiency.

1.5.1.7 Design for Sequences

Sequences guarantee uniqueness of a value, such as a primary key. Design how the sequences are generated within the mobile infrastructure. For example, if the enterprise database generates a sequence number and the Mobile client generates the same sequence number a conflict with the data occurs and causes an error.

Native sequences may be formed specifically for the Mobile clients. These sequences would never populate on the enterprise database itself, so there is no risk of a conflict occurring. This works well when data updates only occur from the Mobile clients and input to the database does not come from any other source. However, it is often necessary to have the sequences generated by both the database and the clients. To accomplish this, sequences must be designed so the database uses a range separate from the range used by the clients. For example, you could define the sequences where the database uses all odd numbers and the clients uses all even numbers.

You must also design sequences for the Mobile clients, so that each client uses a unique range of values without any two clients using the same range. For this you specify the sequence range for each client, such as sequences 1 through 1000 for client A and sequences 1001 through 2000 for client B. Using these ranges for the sequence numbers prevents each client from using the same sequence number as used by another client.

1.5.1.8 Design for Synchronization

If you are using the Mobile option, synchronization holds the mobile infrastructure together.

Analyze all of the data needed by the mobile user, as follows:

  • Most snapshots are created where the data can be modified on either the client or the server, where the modifications are propagated to the other side through synchronization.

  • If any snapshots require only the ability to read the data—that is, all modifications to the data are made on the server-level and not by the user—then create read-only snapshots.

  • If all or a majority of the users use the same read-only snapshots, then create a cached user that shares the read-only data across multiple clients.

Analyze the type of synchronization that is appopriate for the user's needs, as described below:

  • For optimal performance, use fast refresh for all publications, if appropriate.

  • Only design publication items for a complete refresh if the following is true:

    • If it is absolutely critical for all changes to be processed and applied to the data store immediately.

    • If it is critical that any enterprise updates are immediately brought down to the client.

    Note:

    The complete refresh is the most resource intensive method and should only be utilized after full consideration of the performance hit is analyzed. Only time critical publication items should be specified for a complete refresh synchronization type.

  • If a mobile user is only performing data collection and it is not necessary for server updates to be brought down to the user, then implement a push-only synchronization model for those publication items. For more information, see Section 3.19.2, "Creating Data Collection Queues for Uploading Client Collected Data" in the Oracle Database Lite Developer's Guide.

  • When a mobile user only requires specific table to be updated or synchronized, perform a selective synchronization methodology limiting the synchronization process to specific tables or specific publications.

1.5.1.9 Design for Administration

When designing for administration, you may create standardized groups and assign users to these groups. It is much easier to administer groups of users then it is to administer several thousand users each as individuals. The situations where groups simplify your administration is when your organizational architecture has data and the environment is structured into a hierarchy, such as by region or department. In this situation, all properties, access settings, and template variables may be set for groups of users and not the users individually.

1.5.1.10 Design for the Language Utilized for Handheld Devices

Any language selected for handheld development has limitations not existing in their fuller counterparts. The mobile application must be designed to meet these limitations. For example, when developing with WinCE utilizing the Microsoft Compact Framework with C# or VB.Net, forms are not maintained the same way as they are in the full framework when running on a normal PC. Without designing for this limitation, it would be easy to accidentally leave forms sitting in the background that should have been closed when the application exits. This prevents crucial memory from being freed and also prevents any application updates from downloading and installing correctly.

When you properly research and design for the device platform, everything done on the device may be performed on the larger non-mobile applications. Thus, you can reuse and update code simply as it uses the same coding for both environments. Designing in the reverse may result in functionality being utilized in the non-mobile applications that does not have a matching functionality in the mobile version.

1.5.2 Application Programming Interfaces

When you are developing your application, you may decide that you want to control more aspects of Oracle Database Lite within your application—rather than relying on user interaction. In this case, you can use the Oracle Database Lite Application Programming Interfaces (APIs). Almost any task performed by the tools and utilities included with Oracle Database Lite may also be accomplished with the APIs. Some of the more advanced functionality within the product is only available through the use of the APIs. Except for the synchronization APIs which are provided for most languages utilized for application development, most of the APIs are Java interfaces that must be developed with the Java programming language.

The most common APIs utilized and their uses are as follows:

  • Synchronization APIs: These APIs provide all of the basic synchronization functionality that is found within the mSync utility. The advantages of using these APIs are that the synchronization process can be fully integrated within the actual Mobile application. The APIs also provide the ability for a push-only synchronization, which allows Mobile clients to only upload data skipping the downloading of new data or applications. The push-only model is useful when bandwidth is limited and when the client just collects data—that is, it is not necessary for a remote client to have updated data from the enterprise.

  • Consolidator APIs: The Consolidator APIs provide administrative functionality for creating users, setting the user properties, working with applications, and so on. You can automate common administration tasks and speed up some of the administration tasks required, such as the creation of a large amount of users. The only limitation is that application and user settings are not displayed in the Mobile Manager Web administration tool as these APIs directly access the Mobile Repository.

  • Mobile Resource Manager APIs: The Mobile Resource Manager APIs also provides administration functionality for users and applications; however, this API actually updates the Mobile Manager administration tool as well as the repository. This utility may be used to create users, set user access, set the user template variables, and many other tasks.

  • Device Manager APIs: The Device Manager APIs provide the ability customize the management of devices. These APIs may be used to gather information on devices, send commands to devices, register devices, and so on.

1.5.3 Supported Languages for Building Mobile Applications

Mobile database applications can be developed using any of the following languages:

  • The most common way is to develop native C or C++ applications for specific Mobile platforms. C++ applications can access the Oracle Database Lite database using the Simple Object Data Access API (SODA), an easy-to-use C++ interface that is optimized for the object-oriented and SQL functionality of Oracle Database Lite. For more information about SODA, refer the SODA API documentation, which is installed as part of the Mobile Development Kit.

  • Applications that need a standard interface and work with multiple database engines can use either the Open Database Connectivity (ODBC) interface, Active Data Object (ADO) interface, or some other interface built on top of ODBC. ADO.NET can be used on Win32 and Windows CE. Another way to develop a Mobile database application is to use Java and the Java Database Connectivity (JDBC) interface.

  • Oracle Database Lite also offers a third way to develop Mobile database applications using the servlet-based Web application model called Web-to-Go. Web-to-Go applications can be built using Web technologies, such as servlet, Java Sever Pages (JSP), applet, HTML, and JDBC.

  • Symbian applications that need a standard interface and work with multiple database engines can use either the Open Database Connectivity (ODBC) interface or some other interface built on top of ODBC.

Oracle Database Lite is an integrated framework that simplifies the development, management, and deployment of mobile applications on the following mobile platforms, operating systems, and hardware:

Platform Programming Languages Operating System Hardware
Win32 on a laptop or notebook When you develop on a laptop, you are using one of the Windows operating systems. You can use any of the languages mentioned in this book. However, C, C++ are better for creating applications with a good user interface.

The languages available are C, C++, C#, Java, Visual Basic, JSPs and Servlets.

You can use Visual Studio 2003 for development.

 Windows XP Pentium processor
PocketPC C, C++, Visual Basic, Java applications (no Servlet or JSP support), SODA, ADO.NET.

You can use Visual Studio 2005 for development.

 Windows CE (WinCE) ARM
Linux Java, C, C++ Linux Redhat 3.0 X86
Symbian OS on Nokia and Motorola C, C++, JDBC Symbian OS versions 7.0 and 8.0 ARM

Oracle Database Lite provides the Mobile Development Kit, which includes facilities, tools, APIs, and sample code for you to develop your applications. There are three application models:

1.5.3.1 Native Applications

Native applications are built using C, C++, Visual C++, Visual Basic, Embedded Visual tools, ActiveX Data Objects (ADO), and MetroWerks CodeWarrior. The application must be compiled against the mobile device operating system, such as the Windows CE platform.

Use ODBC to access the Oracle Lite database on the client. See Section 2.4.1, "ODBC" in the Oracle Database Lite Developer's Guide for more information.

See Chapter 8, "Native Application Development" in the Oracle Database Lite Developer's Guide for more information on C and C++.

1.5.3.2 Standalone Java Applications

Standalone Java applications do not include Web and J2EE technology—such as Servlets and JSPs. Instead, Java applications revolve around using JDBC driver to access the Oracle Lite database on the client platform, and use AWT and SWING classes to build the application UI. In addition, the database supports Java stored procedures and triggers.

Your Java/JDBC application must be compiled for the particular mobile device JVM environment, which can be different across various client devices. Thus, when you are developing your Java application, do the following:

  1. Check the environment: Verify that the olite40.jar, which is located in OLITE_HOME/bin, is in your CLASSPATH, which should have been modified during installation.

  2. Load the JDBC driver in to your applications. The following is an example:

    Class.forName("oracle.lite.poljdbc.POLJDBCDriver");

  3. Connect to the Oracle Lite database installed on the client. If your database is on the same machine as the JDBC application, connect using the native driver connection URL syntax, as follows:

    jdbc:polite:dsn

    Or if not local, connect as follows:

    jdbc:polite@[hostname]:[port]:dsn

See Chapter 9, "Java Application Development" and Chapter 10, "JDBC Programming" in the Oracle Database Lite Developer's Guide for more information.

1.5.3.3 Web Applications

You can execute existing Web applications using the J2EE Java technologies, such as servlets and JSPs, in a disconnected mode without modifying the code base. Web-to-Go is a development option for Web applications, and can be executed on laptops. Web-to-Go applications use Java servlets and JSPs that may invoke JDBC to access the database.

For more information, see Chapter 6, "Developing Mobile Web-to-Go Applications" in the Oracle Database Lite Developer's Guide.

1.5.4 Application Deployment into the Mobile Environment

Deployment of applications includes setting up the server system so that end users can easily install and use the applications. Mobile applications are deployed to the Mobile Server.

Deployment consists of the following steps:

  1. Create the publication with the Mobile Database Workbench (MDW). See Section 1.7.2, "Using the Mobile Database Workbench" for more information.

  2. Publishing the application to the server includes installing all the components for an application on the Mobile Server with the Packaging Wizard tool. See Section 1.7.3, "Using the Packaging Wizard" for details.

  3. Provisioning the applications to the Mobile users through the Mobile Manager, which is a GUI interface for the Mobile Server. This phase includes determining user accesses to applications with a specified subset of data. The Mobile Manager can create users, grant privileges to execute applications, and define the data subsets for them, among others. You can also use the Java API to provision applications.

  4. Testing for functionality and performance in a real deployment environment. A Mobile application system is a complex system involving the following:

    • Multiple Mobile device client technologies—such as, operating systems, form factors, and so on.

    • Multiple connectivity options—such as, LAN, Wireless LAN, cellular, wireless data, and other technologies.

    • Multiple server configuration options.

    When testing, pay particular attention to tuning the performance of the data subsetting queries, as it is the most frequent cause of performance problems.

  5. Determining the method of initial installation of applications on Mobile devices (application delivery). Initial installation involves installing the Oracle Database Lite client, the application code, and the initial Oracle Lite database. The volume of data required to install applications on a Mobile device for the first time could be quite high, necessitating the use of either a high-speed reliable connection between the Mobile device and the server, or using a technique known as offline instantiation. In offline instantiation, everything needed to install an application on a Mobile device is put on a CD or a floppy disk and physically given to the user. The user then uses this media to install the application on the device by means of a desktop machine. Oracle Database Lite provides a tool for offline instantiation.

After deployment, both the application and the data schema may change because of enhancements or defect resolution. The Mobile Server manages application updates and data schema evolution. The only requirement is that the administrator must republish the application and the schema. The Mobile Server automatically updates the Mobile clients that have older version of the application or the data.

1.6 Oracle Database Lite Components Involved in Synchronization

The following is a more formal definition of each of the components involved in the synchronization process:

1.6.1 Oracle Database Lite RDBMS

The Oracle Database Lite RDBMS is a small footprint, Java-enabled, secure, relational database management system created specifically for laptop computers, handheld computers, PDAs, and information appliances. The Oracle Database Lite RDBMS runs on Windows 2000/XP, Windows CE/Windows Mobile, Linux, and Symbian. Oracle Database Lite RDBMS provides JDBC, ODBC, and SODA interfaces to build database applications from a variety of programming languages such as Java, C/C++, and Visual Basic. These database applications can be used while the user is disconnected from the Oracle database server.

When you install the Mobile Development Kit, the Oracle Database Lite RDBMS and all the utilities listed in Appendix C are installed on your development machine. In a production system, when the Mobile Server installs Oracle Database Lite applications, only the RDBMS, the Mobile Sync, and Mobile SQL applications are installed on the client machine.

1.6.2 Mobile Sync

Mobile Sync (msync) is a small footprint application that resides on the Mobile device. Mobile Sync enables you to synchronize data between handheld devices, desktop and laptop computers and Oracle databases. Mobile Sync authenticates locally, collects changes from the Oracle Lite database and sends them to the server, where the user is authenticated before the changes are uploaded.

Use the msync executable for Mobile Sync.

Mobile Sync synchronizes the snapshots in Oracle Database Lite with the data in corresponding Oracle data server. These snapshots are created by the Mobile Server for each user from the publication items associated with a Mobile application. The Mobile Server also coordinates the synchronization process.

The Mobile Sync application communicates with the Mobile Server using any of the supported protocols (that is, HTTP or HTTPS). When called by the Mobile user, the Mobile Sync application first collects the user information and authenticates the users with the Mobile Server. It then collects the changes made to Oracle Database Lite (from the snapshot change logs) and uploads them to the Mobile Server. It then downloads the changes for the user from the Mobile Server and applies them to the Oracle Database Lite.

In addition to this basic function, the Mobile Sync application can also encrypt, decrypt, and compress transmitted data.

When you install the Mobile Development Kit, the Mobile Sync application is also installed on your development machine. The Mobile Server also installs the Mobile Sync on the client machine as part of application installation.

1.6.3 Mobile Server

The installation of the Mobile Server requires an Oracle database instance to be running. You can use an existing test database as well. The Mobile Server stores its metadata in this database.

The Mobile Server is a Web middle-tier server that can exist on Windows, Solaris, HP-UX, AIX, and Linux. The Mobile Server uses the Oracle Containers for Java (OC4J) Web engine and provides the interface between the mobile infrastructure and the enterprise database. Most administration tasks are accomplished through the Mobile Server Web application—the Mobile Manager.

The Mobile Server provides the following features.

  • Application Publishing

  • Application Provisioning

  • Application Installation and Update

  • Data Synchronization

The Mobile Manager application provides the capability to manage users, devices, publications and applications. This utility can provides the following:

  • Monitors and manages synchronization between the client data store and the enterprise data store.

  • Sends administrative commands to the Mobile clients. These commands capture data and logs from the client or instructs the client to carry out necessary tasks. For example, the Mobile Manager could send a command to a client to perform synchronization or to remove the entire client data store, if a device may have been compromised.

Note:

If you wish, you can also accomplish the same tasks as the Mobile Manager with the Application Programming Interfaces (APIs).

As with any Web server tier, the Mobile Server may be configured within a Web farm for improved performance within the mobile infrastructure. This enables the use of a load balancer, such as the balancer included with Oracle Internet Application Server (Oracle AS), or with one provided by a 3rd party vendor. The Mobile Server is designed to be fully integrated with Oracle AS to take advantage of the features within Oracle AS, such as the Oracle Internet Directory (OID) capabilities.

Note:

As the Mobile Server is a Web-based environment, it is important to design for a proper security environment as would be done with any Web server.

The Mobile Server has two major modules called the Resource Manager and the Consolidator Manager. The Resource Manager is responsible for application publishing, application provisioning, and application installation. The Consolidator Manager is responsible for data and application synchronization.

Application publishing refers to uploading your application to the Mobile Server so that it can be provisioned to the Mobile users. Once you have finished developing your application, you can publish it to the Mobile Server by using the development tool called the Packaging Wizard.

Application provisioning is concerned with creating subscriptions for users and assigning application execution privilege to them. Application provisioning can also be done in one of two ways.

  • Using the administration tool called the Mobile Manager, you can create users and groups, create subscriptions for users by assigning values to subscription parameters, and give users or groups privileges to use the application.

  • Using the Resource Manager API, you can programmatically perform the above tasks.

End users install Mobile applications in two steps.

  1. As the Mobile user, browse the setup page on the Mobile Server and choose the setup program for the platform you want to use.

  2. Run the Mobile Sync (mSync) command on your Mobile device, which prompts for the Mobile username and password. The Mobile Sync application communicates with the Consolidator Manager module of the Mobile Server and downloads the applications and the data provisioning for the user.

After the installation of the applications and data, you can start using the application. Periodically, use msync or a custom command to synchronize your local database with the server database. This synchronization updates all applications that have changed.

1.6.4 Message Generator and Processor (MGP)

The Consolidator Manager module of the Mobile Server uploads the changes from the client database to the server, and it downloads the relevant server changes to the client. But it does not reconcile the changes. The reconciliation of changes and the resolution of any conflicts arising from the changes are handled by MGP. MGP runs as a background process which can be controlled to start its cycle at certain intervals.

Note:

The mobile infrastructure may allow for multiple Mobile Servers to be configured within a Web farm. However, there may only be one MGP application utilized for the entire Web farm.

Each cycle of MGP consists of two phases: Apply and Compose.

The Apply Phase

In the apply phase, MGP collects the changes that were uploaded by the users since the last apply phase and applies them to the server database. For each user that has uploaded his changes, the MGP applies the changes for each subscription in a single transaction. If the transaction fails, MGP will log the reason in the log file and stores the changes in the error file.

The Compose Phase

When the apply phase is finished, MGP goes into the compose phase, where it starts preparing the changes that need to be downloaded for each client.

Applying Changes to the Server Database

Because of the asynchronous nature of data synchronization, the Mobile user may sometimes get an unexpected result. A typical case is when the user updates a record that is also updated by someone else on the server. After a round of synchronization, the user may not get the server changes.

This happens because the user's changes have not been reconciled with the server database changes yet. In the next cycle of MGP, the changes will be reconciled with the server database, and any conflicts arising from the reconciliation will be resolved. Then a new record will be prepared for downloading the changes to the client. When the user synchronizes again (the second time), the user will get the record that reflects the server changes. If there is a conflict between the server changes and the client changes, the user will get the record that reflects either the server changes or the client changes, depending on how the conflict resolution policy is defined.

1.6.5 Mobile Server Repository

The Mobile Server Repository contains all the application data as well as all information needed to run the Mobile Server. The Mobile Repository contains the repository schema under which all the data mapping and internal tables utilized to maintain data synchronization exist. This schema also stores the application, application tables and its data published for use with a Mobile client.

The information is normally stored in the same database where the application data resides. The only exception to this is in cases where the application data resides in a remote instance and there is a synonym defined in the Mobile Server to this remote instance.

The Mobile Repository contains some internal tables that the Mobile Server uses to perform its functions. You may query these tables to gain more details about the current state of the environment; however, most of the information needed from these tables is already accessible from the Mobile Manager. You should never alter any of the internal tables and their contents unless explicitly directed to by Oracle Support Services or Oracle Development.

Administration, backup, and recovery of the repository are no different then for any other Oracle database requiring standard Database Administrator (DBA) skills

Changes to the repository should only be made using the Mobile Server Mobile Manager or the Resource Manager API.

1.6.6 Device Manager

The Device Manager manages client devices. On install of the Mobile client, the Device Manager registers a device with the Mobile Server. The Device Manager invokes the update executable after synchronization completes to determine if any mobile application updates are available, then downloads and installs these application updates to a Mobile client. You can request—through the Mobile Manager—that certain commands are invoked on the client. The Device Manager executes these commands. The Device Manager is responsible for most administrative actions between the Mobile Server and the Mobile client.

1.7 Mobile Development Kit (MDK)

On the middle tier, Oracle Database Lite installation provides you with an option to install the Mobile Server and/or the Mobile Development Kit. For application development, you need to install the Mobile Development Kit on your development machine.

Before you develop an application using Oracle Database Lite, you should install the Oracle Database Lite Mobile Development Kit (MDK) on the machine on which you intend to develop your application. The Mobile Development Kit is installed in <ORACLE_HOME>\Mobile\Sdk. For instructions on how to install the Mobile Development Kit, see Section 4.3, "Installing Oracle Database Lite".

The Oracle Database Lite Mobile Development Kit includes the following components.

  • Oracle Database Lite RDBMS—A lightweight, object-relational database management system, including Mobile SQL (msql.exe). Mobile SQL is written in Java. It requires the Java runtime environment JRE 1.4.2 or higher to be installed on your system before you can use it. If you have installed JDK 1.4.2 or higher, the JRE is already installed in your machine

  • Mobile Database Workbench (MDW)—A development tool for creating a publication.

  • Packaging Wizard—A tool to publish applications to the Mobile Server through executing runwtgpack.bat.

  • Mobile Sync—A transactional synchronization engine that includes the executable (msync.exe) and the Java wrapper for it.

  • mSQL—An interactive tool to create, access, and manipulate Oracle Database Lite on laptops and handheld devices

Using any C, C++, or Java development tool in conjunction with the Mobile Development Kit for Windows, you can develop your Mobile applications for Windows against Oracle Database Lite, and then publish the applications to the Mobile Server by using the Packaging Wizard. See Section 4.3, "Installing Oracle Database Lite" for instructions on how to install the Mobile Server.

Once you have published the applications to the Mobile Server, you can use the Mobile Manager to provision the applications to the Mobile users. Provisioning involves specifying the values of the subscription parameters used for subsetting the data needed by the application for a particular user. A user to whom an application has been provisioned can then log in to the Mobile Server and request it to set up everything the user needs to run the applications on the user's device.

The Samples directory <ORACLE_HOME>\Mobile\Sdk\Samples directory contains some sample applications. Section 2.12, "Using Oracle Database Lite Samples" in the Oracle Database Lite Developer's Guide describes the sample programs and explains how to run them. You should familiarize yourself with the various Oracle Database Lite features by perusing the source code and running the samples.

When you install the MDK, it installs a starter database file in the <ORACLE_HOME>\Mobile\Sdk\OLDB40 directory named polite.odb.

Note:

The polite.odb starter database is not the name of the Mobile client database. For information on what Oracle Lite database (ODB) files are installed on the client, see Section 2.2, "Synchronizing or Executing Applications on the Mobile Client" in the Oracle Database Lite Administration and Deployment Guide.

When you install the Mobile Development Kit, the installer sets the PATH environment variable to include the bin directory of the Mobile Development Kit. You can use the Command Prompt on your Windows 32 machine to do the following quick test.

At the command prompt, enter the following.

msql system/manager@jdbc:polite:polite
...
SQL>create table test (c1 int, c2 int);
Table created
SQL>insert into test values(1,2)
1 row(s) created
SQL>select * from test;
 
                  C1 | C2
                 ----+----
                  1  |  2
SQL>rollback;
Rollback completed
SQL>exit

1.7.1 Mobile SQL (mSQL)

Mobile SQL is an interactive tool that allows you to create, access, and manipulate Oracle Database Lite on laptops and handheld devices. The mSQL installations on laptops cannot be used to create a database, but can create a database on hand-held devices. Using mSQL, you can perform the following actions.

  • Create database objects such as tables and views

  • View tables

  • Execute SQL statements

The mSQL tool is installed with the Mobile Development Kit installation. It is also installed by the Mobile Server as part of application installation. The mSQL tool for the Windows 32 platform is a command line tool that is similar to the Oracle SQL*Plus tool, but does not provide compatibility with SQL*Plus. The mSQL tool for Windows CE supports a graphical user interface.

Note:

UTF8 SQL Scripts are not supported in mSQL.

1.7.2 Using the Mobile Database Workbench

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.

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.

For detailed information on how to use MDW, see Chapter 5, "Using Mobile Database Workbench to Create Publications" in the Oracle Database Lite Developer's Guide.

1.7.3 Using the Packaging Wizard

The Packaging Wizard is a graphical tool that enables you to perform the following tasks.

  1. Create a new Mobile application.

  2. Edit an existing Mobile application.

  3. Publish an application to the Mobile Server.

When you create a new Mobile application, you must define its components and files. In some cases, you may want to edit the definition of an existing Mobile application's components. For example, if you develop a new version of your application, you can use the Packaging Wizard to update your application definition. The Packaging Wizard also enables you to package application components in a .jar file which can be published using the Mobile Manager. The Packaging Wizard also enables you to create SQL scripts which can be used to execute any SQL statements in the Oracle database.

For detailed information on how to use the Packaging Wizard, see Chapter 6, "Using the Packaging Wizard" in the Oracle Database Lite Developer's Guide.

1.8 Overview of Performance Tuning

Mobile devices do not have the processing power and memory that standard enterprise systems maintain. If the mobile applications and infrastructure are not tuned appropriately they really are of little benefit to the organization.

The two most important components within the mobile infrastructure to perform tuning on in order to increase the feasibility of a mobile application are as follows:

  • The time it takes to enter and retrieve data.

  • The time it takes to synchronize data with the enterprise data store.

The type of performance you enjoy can be tuned with SQL query tuning and through the use of the ConsPerf utility provided with Oracle Database Lite, as follows:

1.8.1 SQL Query Tuning

To decrease the time it takes to enter and retrieve data, query tuning may be performed through the use of the Explain Plan or through SQL tracing and indexes. Unlike the enterprise database, Oracle Database Lite utilizes two methods for scanning a table and retrieving data: a full table scan and an index based scan.

1.8.1.1 Determining Performance of Client SQL Queries With the EXPLAIN PLAN

If you want to access data on the local client Oracle Lite database, then you can use the EXPLAIN PLAN to determine the performance of your SQL query execution on the Oracle Lite database. To execute a SQL statement, Oracle might need to perform several steps. Each of these steps either physically retrieves rows of data from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle uses to execute a statement is called an execution plan, which includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method. The execution plan shows you exactly how Oracle Database Lite executes your SQL statement.

The components of an execution plan include the following:

  • An ordering of the tables referenced by the statement.

  • An access method for each table mentioned in the statement.

  • A join method for tables affected by join operations in the statement.

The EXPLAIN PLAN command stores the execution plan chosen by the Oracle Database Lite optimizer for SELECT, UPDATE, INSERT, and DELETE statement.

You can generate an Explain Plan using either of the following methods:

1.8.1.2 Determining Performance of Client SQL Queries With SQL Tracing

By setting the parameter OLITE_SQL_TRACE = TRUE in the polite.ini or polite.txt file on the client device, Oracle Database Lite generates a trace file named oldb_trc.txt that shows the following:

  • The order tables are accessed by a query.

  • The table scan access method used.

  • The value of any bind variables utilized by the query.

  • The time it takes for the first record to be retrieved.

These are the main items reported that you can use to tune the majority of SQL queries.

If the trace file identifies that a full table scan is occurring, the most common way to get better performance from the query is to add an index that accommodates that query. To do this quickly for testing purposes, you can create the indexes directly on the client until you are satisfied with the performance. Once you identify the structure for the indexes, then they should then be created in the Mobile Database Workbench, or with the APIs with the publication items. Then, brought down to the device through the normal deployment processes. Final testing should validate the deployment and improvements before any application is even moved out of the development stage for testing, quality assurance, or deployment into production.

1.8.2 Analyzing Performance of Publications With the Consperf Utility

The Consperf utility profiles your subscriptions and may modify how the publication item is executed if the utility determines that there is a more performant option. The Consperf tool evaluates how the SQL within the publication item interacts with our Data Synchronization query templates. The first synchronization is always a complete refresh, which is a direct invocation of the query. On subsequent synchronizations, the query templates determine incremental refreshes. This improves your performance from not having to perform a complete refresh each time you synchronize. However, the interaction of our query templates and your SQL may not be optimal, which is discovered by the Consperf tool. We either modify the query template or type of logical delete or insert for you or you can adjust your SQL to be more performant in regards to our templates.

In addition, application developers and administrators use this utility to analyze the performance of subscriptions and identify potential bottlenecks during synchronization.

1.9 Security Considerations

The introduction of handheld devices within the corporate environment can pose a security threat to an organization. Devices are now used to store not only company contacts; but, with external cards, may store up to 60 gigabytes of information or more. Devices also provide a mobile point of entry into the organizational network that is located outside the network security perimeter. It is essential to secure this data if a device is lost or compromised.

Securing a device involves a layered approach. You must secure not only access to the device, but data stored on the device and communications across the network. Most aspects of security for a mobile device must be incorporated before Oracle Database Lite is even involved within the security infrastructure.

  1. Security needs to start with the device itself. Authentication on the device must be implemented through pin or password authentication, biometric readers, secure digital media for storage, and even how the device is stored, transported, and accounted for.

  2. Once access is gained to the device, further security needs to be implemented within the mobile application to prevent the application from being able to retrieve invalid data. Technologies, such as the Microsoft.Net Compact Framework, incorporate API calls that may be used to encrypt and decrypt any data that will be stored or retrieved from the device.

Oracle Database Lite provides several security features that may be utilized to help in securing data. These features aid in protecting information during both synchronization, and once access to a device has been obtained. The two most important aspects of security provided by Oracle Database Lite for the mobile infrastructure are the following:

  1. Use Secure Socket Layer (SSL) to protect the transmission of data during the synchronization process.

  2. Use the Oracle Database Lite encryption utility, ENCRYPT_DB, to help protect the actual database files.

1.9.1 The Oracle Database Lite Encryption Utility—EncryptDB

EncyrptDB encrypts the Oracle Lite database by using 128 bit Advanced Encryption Standard (AES) encryption. This does not encrypt the data stored within the Oracle Lite database itself; it only encrypts the database as a whole.