1
Overview

1.1.1 Oracle Lite DBMS

Oracle Lite database is a small footprint, Java-enabled, relational database created specifically for laptop computers, handheld computers, PDAs, and information appliances. The Oracle Lite database runs on Windows 95/98/NT/2000, Windows CE/Pocket PC, Palm Computing, and EPOC. Oracle Lite database provides ODBC and OKAPI programming interfaces to build database applications from a variety of programming languages such as C/C++, Visual Basic and Satellite Forms. These database applications are capable of use while disconnected from the database server.

1.1.2 Mobile Sync

Mobile Sync 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 runs on Windows 95/98/NT/2000, Windows CE/Pocket PC, Palm Computing, and EPOC.

There are several components to which the developer should have access:

• Oracle8i database server.

• Mobile Server which is the gateway between the mobile device and the Oracle8i database server.

• Message Generator and Processor (MGP), a background process that manages the transaction and data exchange between the Mobile Server and the Oracle8i database server

• Mobile Sync resides on the mobile devices and talks to the Mobile Server over any given communication mechanism.

• A communication mechanism between the mobile device and Mobile Server such as HTTP.

Typically, the handheld device needs to carry only a small subset of the data contained in a central database. For example, the sales force in a particular region only needs access to the contact information for customers in that region. Mobile Server, uses a publish/subscribe model for managing data and applications. In this model the publication, which you create using the Mobile Server Admin API, defines the subset of data that you make available to particular subscribers. A subscription associates a user with a publication. As the client and server exchange data, Mobile Server detects and resolves data conflicts using rules that you define.

1.1.3 Mobile Server

The Mobile Server provides seamless synchronization between Oracle Lite databases on handheld devices and Oracle servers.

Using a publish/subscribe model for data distribution, the Mobile Server enables individual client devices to subscribe to different subsets of data based on parameters such as user ID or location. Mobile Server supports the HTTP transport protocol between devices and servers.

1.1.4 Mobile SQL

Mobile SQL allows you to create, access, and manipulate Oracle Lite database on laptops and handheld devices. Using Mobile SQL you can:

• Create databases

• View tables

• Execute SQL statements

1.1.5 Samples

A sample application is included in the Mobile Development Kit. The sample are installed automatically with the Mobile Development Kit and can be found Oracle_Home/Mobile/SDK/Examples.

1.2.1 Development Interface Overview

Oracle Lite provides the following interfaces for developing database applications:

• For relational database development:

  • JDBC

  • ODBC

• For object database development:

  • Object Kernel API (OKAPI)

The models can be used either independently or in combination. For example, you can have OKAPI and JDBC calls in the same program. The following diagram illustrates the Oracle Lite application interfaces. The shaded interfaces support the relational model.

1.2.2 Mobile Sync API

This API allows the application to programmatically control the replication process. The application invokes Mobile Sync API functions to initiate the replication process and captures error messages generated by the Mobile Sync API. For more information on the Mobile Sync API please see Section 3.6, "Programming with the OCAPI.DLL".

1.2.3 Oracle Lite Loading Utility (OLLOAD)

The Oracle Lite Load Utility allows you to load data from an external file into a table in an Oracle Lite database, or to unload (dump) data from a table in an Oracle Lite database to an external file. For more information on OLLOAD see Appendix E, " Oracle Lite Loading Utility API".

1.4.1 Creating a New Database

When you create a new database using the POLITE data source name, the new database file is located in the Oracle_Home\Mobile\SDK\oldb40 directory. For ease of maintenance, it is recommended that you use one database directory for all of your databases.

You can create a new data source name using the ODBC Administrator. See Section 1.4.2, "Creating a Data Source Name with ODBC Administrator" for more information.

1.4.2 Creating a Data Source Name with ODBC Administrator

The ODBC Administrator is a tool provided by Microsoft to manage the ODBC.INI file and associated registry entries in Windows 95/98/NT and Windows 2000. It allows you to add a data source name and specify the database file you want to dedicate as the default for the data source name. See Section C.7, "ODBC Administrator and the Oracle Lite ODBC Driver" for more information on the ODBC Administrator, and for instructions on creating a data source name using the tool.

1.4.3 Creating a New Database Using a Command-Line Utility

To create a new database from the command line, use the CREATEDB utility. The syntax is:

CREATEDB mydatabase mydbname

For example:

CREATEDB polite newdb

mydatabase is the DSN name and mydbname is the new database name.

See Section C.2, "CREATEDB", for more information.

1.4.4 Connecting to a New Database

To connect to a new database using Mobile SQL, connect as the user SYSTEM, with the password MANAGER and the data source name. For example:

C:\msql system/manager@ODBC:polite

You can replace MYDATABASE with a previously defined ODBC data source name.

If there is more than one database associated with a data source name, use the format:

ODBC:dsn:dbname

dsn is the data source name and dbname is the name of the database. For example:

C:\msql system/manager@ODBC:polite:newdb

1.5.1 Pre-defined Roles

Oracle Lite combines some privileges into pre-defined roles for convenience. In many cases it is easier to grant a user a pre-defined role than to grant specific privileges in another schema. Oracle Lite does not support creating or dropping roles. Following is a list of Oracle Lite pre-defined roles:

Table 1-1 Pre-Defined Roles

1.5.2 Creating Users

You can create users if you are connected to the database as "system", or if you are granted the ADMIN or DBA role. To create a user, issue the following statement:

CREATE USER user IDENTIFIED BY password

Here, user is a unique user name with up to 128 characters, beginning with a letter, and password is a string of up to 128 characters. This statement creates a schema with the user name and assigns the schema as the default schema for the user.

For encrypted databases, all user names and passwords are written to a file named dsn.opw. Each user can then use the password as a "key" to unlock the .opw file before the .odb file is accessed. When you copy or back up the database, you should include the .opw file.

1.5.3 Dropping Users

You can drop users if you are connected to the database as "system", or if you are granted the ADMIN or DBA role.

To drop a user and all schemas owned by the user, use the syntax:

DROP USER user

To drop all objects in the user's schema before dropping the user, use the syntax:

DROP USER user CASCADE

For more information on the DROP USER command, see the Oracle Lite SQL Reference.

1.5.4 Changing Passwords

You can change a user's password if you meet one of the following conditions:

• You are connected to the database as that user

• You are connected to the database as SYSTEM

• You are granted the ADMIN, DBA, or RESOURCE role

To change a user's password, issue the following statement:

ALTER USER user IDENTIFIED BY password

1.5.5 Granting Roles

You can grant the ADMIN or DDL roles to users by issuing the following statement:

GRANT role TO user_list

Here, user_list is either one user or a comma-separated list of multiple users.

1.5.6 Granting Privileges

You can grant privileges on a database object to users by issuing the following statement:

GRANT privilege_list ON object_name TO user_list

Here, privilege_list is either a comma-separated list of the following privileges or a combination called ALL:

• ALL

• INSERT

• DELETE

• UPDATE (column_list)

• SELECT

Object_name is a table name prefixed with a schema name.

If privilege_list is ALL, then the user can INSERT, DELETE, UPDATE or SELECT from the table or view. If privilege_list is either INSERT, DELETE, UPDATE, or SELECT, then the user has that privilege on a table.

1.5.7 Revoking Roles

You can revoke user roles by issuing the following statement:

REVOKE role FROM user_list

1.5.8 Revoking Privileges

You can revoke privileges on database objects from users by issuing the following statement:

REVOKE privilege_list ON table_name FROM user_list

1.5.9 Building Demo Tables

Oracle Lite comes with a script called POLDEMO.SQL, which enables you to build the same tables that are in your Oracle Lite default starter database (POLITE.ODB).

1.5.10 Populate Your Database Using Mobile SQL

You can use SQL scripts to create tables and schema, and to insert data into tables. A SQL script is a text file, generally with a .SQL extension, that contains SQL commands. You can run a SQL script from the Mobile SQL prompt by typing:

SQL> @Oracle_home\DBS\Poldemo.sql

You can also type:

SQL> START filename

1.5.11 Backing Up a Database

The Oracle Lite database occupies one file, and has dependent log files which can be backed up by copying to another location. Before any files can be copied, however, your database administrator must shut down the database which ensures that log file changes are applied to the database. Once that has been accomplished, you can copy the *.odb, *.opw, and *.plg files to another directory to make a backup of the database.

1.5.12 Encrypting and Decrypting a Database

Two tools, ENCRYPDB and DECRYPDB, allow you to encrypt and decrypt Oracle Lite databases. These tools allow you to encrypt an Oracle Lite database with a password. The password can be used to prevent unauthorized access to the database and also to encrypt the database so that the data stored in the database files cannot be interpreted by examining the files. The password is used to derive a 40-bit encryption key. Oracle Lite uses a version of the Data Encryption Standard (DES) algorithm known as CAST5.

See Section C.4, "ENCRYPDB"and Section C.3, "DECRYPDB", for more information about these utilities.

1.6.1 Atomicity

A transaction is a sequence of database operations, such as SELECT, UPDATE, DELETE, and INSERT. All operations either succeed and are committed or are rolled back. This is called the atomicity property of a transaction.

Oracle Lite implements atomicity by not updating the actual database file until a database commit. During commit, a temporary undo log is created and then the database file is updated. If an event, such as a power outage, interrupts commit, the database is restored from the log during the next connection.

1.6.2 Consistency

Transactions preserve database consistency. A transaction transforms a consistent state of the database into another consistent state, without necessarily preserving consistency at all intermediate points. Oracle Lite does not permit a transaction to commit if it violates a constraint and would therefore violate consistency.

1.6.3 Isolation

Transactions are isolated from one another. Even though many transactions run concurrently, a given transaction's updates are concealed from other transactions until the transaction commits. Oracle Lite supports the following isolation levels for transactions:

Table 1-2 Isolation Levels

See the ODBC documentation for more information on isolation levels, specifically, for the terms "Dirty Read", "Nonrepeatable Read", and "Phantom", which define transaction isolation levels.

IsolationLevel = XX

SET TRANSACTION ISOLATION LEVEL <ISOLATION_LEVEL>;

1.6.4 Tuning the Application

Tuning your application design ideally occurs before you begin to implement your application. Before beginning your design, you should carefully read about each of the Oracle Lite features available and consider which features best suit your requirements. Also, you should work with your Oracle database administrator to determine how the Oracle master site can be tuned to accommodate your application. Some specific design tips to consider are outlined in Appendix D, " Optimizing SQL Queries ".

1.7.1 Creating a Snapshot Definition Declaratively

This method takes advantage of the Mobile Server Packaging Wizard. This GUI tool can be used to package applications into a .jar file. This file is the deployment descriptor for the application which contains the information necessary for Mobile Server to manage the application. This .jar file is published to the Mobile Server repository.

A secondary feature is the ability of the Packaging Wizard to generate a SQL script which can be executed to create database tables which will be used by your application. These tables are the Oracle 8i database base tables that the Mobile Server synchronizes with.

The convenience of a graphical tool is a safer and less error prone technique for developers to create a mobile application. Before actual application programming begins, the following steps must be executed:

• Verify that the base tables exist on the database, if not:

• Use the Packaging Wizard to collect the snapshot definitions.

• Use the Packaging Wizard to generate a .jar file and a SQL script.

• Execute the SQL script on the database to create the base tables, if they do not exist there yet.

• Publish the .jar file to the Mobile Server repository.

• Setup the Mobile Client.

• Synchronize the Mobile Client with the Mobile Server to create the client-side snapshots.

Creating the snapshot definitions using the Packaging Wizard\Mobile Server architecture allows a centralized server to be used to manage and deploy mobile applications and the snapshot definitions they require. This documentation provides a step-by-step approach in Chapter 4, " Using the Packaging Wizard".

[[[Note to reviewers: Use of "publication" and "publication item" in the following section is likely to be revised to snapshot definition and snapshot.]]]

1.7.2 Creating the Snapshot Definition Programmatically

The second way to create a database and populate data is to create a snapshot definition programmatically using the Consolidator Admin API, which is described in Chapter 3, " Synchronization". The terminology is slightly different because Mobile Server interacts with both the client system and the Oracle8i database. Mobile Server deals with everything in terms of publications.

A publication includes data replication objects such as publication items which are equivalent to snapshot definitions. The database base tables must exist before the Consolidator Admin API can be invoked. The following steps are required to create a distributed database schema:

• Creating a Publication

• Creating a Publication Item

• Create User ID

• Creating a Subscription

Publications are template groups containing metadata such as table subsetting definitions and indexes. You can create publications using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in these APIs from within Java programs as standard function calls.

A publication item is a SQL select statement that specifies which data subset of the parent database and is replicated on the client at when synchronization occurs. A publication item usually corresponds to a snapshot on the client device. You can create publication items using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in this API from within Java programs as standard function calls.

Each client is identified by a user ID. For development purposes, a user ID must be created using the Consolidator Admin API in order to assign data subscriptions to a particular user.

Subscription links a user to a publication. You can create subscriptions using the Consolidator Admin API. This API contains Java functions that implement the publish/subscribe model. You can call the functions in this API from within Java programs as standard function calls. To create publications and subscriptions using Java, see Chapter 3, "The Publish and Subscribe Model".