Pervasync Now supports synchronization of Oracle Mobile Framework (MAF) SQLite databases

Pervasync just released a new version (7.0.0) of its sync framework that supports synchronization of Oracle Mobile Framework (MAF) SQLite databases with central Oracle, MySQL, SQL Server and PostgreSQL databases.
For more info see

P.S.

Oracle Mobile Application Framework (Oracle MAF) is a hybrid mobile framework that enables developers to rapidly develop single-source applications and deploy to both Apple’s iOS and Google’s Android platforms. For more info, see

http://www.oracle.com/technetwork/developer-tools/maf/overview/index.html

With Pervasync, you can synchronize a sub set of your central database data to your Oracle MAF app’s SQLite databases, easily achieving offline capability and making UI responsive and smooth.

Replication versus Synchronization

Database synchronization is closely related to database replication. In fact, sometimes people use the terms interchangeably. However, there are big differences between them. Understanding the differences will help you understand the different approaches used for solving replication and synchronization problems.

Database Synchronization Is Not Replication

Replication is mostly used in situations where identical replicas of the complete data set are maintained for high availability and performance. Replicas can often work independently as backups for each other. On the other hand, synchronization is often between a more temporal sub-set of data and a more persistent full-set of data, both of which are integral parts of a system. For instance, parts of a file could be buffered in-memory by an operating system and are “synchronized” with the file on hard disk. Another example is the synchronization of the data in a CPU cache memory with the data in the main memory. In both cases people use the term “synchronization”, not “replication”.

In Pervasive Computing, devices maintain a data cache that is a small subset of the data stored on centralized servers. Changes to the cache are temporary and should eventually be propagated to the servers and the server should refresh the caches with up-to-date data in central databases. Clearly, this is a synchronization process, not a replication process.

Replication Techniques Won’t Work for Synchronization

In traditional database replication schemes, physical transactions on each node are recorded and played back on all the other nodes. This technique would only work if each node has a replica of the full-set data.

There is also a stability issue with physical transaction based replications when the number of nodes goes up. Transactions on different replicas may conflict with each other. To handle this, cross system locking or complicated conflict resolution schemes are needed. In fact, they are used in eager replication and lazy replication respectively [3,5].

Eager replication synchronously updates all replicas as part of one atomic transaction. This is also called synchronous replication or pessimistic replication as it incurs global locking and waiting. This scheme is not suitable for Pervasive Computing since the locking and waiting are simply not feasible in an environment where there are lots of nodes/devices. All the devices may not even be connected to the network at a same time, let alone be locked at a same time to let the transaction go through.

In contrast to eager replication, lazy replication allows updates of any replicas without locking others. The local transactions then propagate to other replicas in background. This scheme is also called asynchronous replication or optimistic replication since it is based on the assumption that conflicts will occur rarely. Here each host must apply transactions generated by the rest of the hosts. Conflicts must be detected and resolved. Since the transactions cannot be simply un-done at their origin nodes, usually manual or complicated ad hoc conflict resolutions are required at the destination nodes.

Gray et al. [3] showed that the traditional transactional replication has unstable behaviors as the workload scales up: a ten-fold increase in node number or data traffic gives a thousand fold increase in deadlocks or reconciliation. A system that performs well on a few nodes may become unstable as the system scales up to even a dozen of nodes.

The traditional database replication schemes are clearly not suited for Pervasive Computing which involves hundreds or even thousands of nodes in one system. On the other hand, we can exploit the unique characteristics of Pervasive Computing to construct a synchronization scheme that may not be suitable for traditional replication situations but works well for Pervasive Computing.

Sync solutions usually employ a client-server model, instead of the peer-to-peer model as used in replication. Clients all communicate with the server directly. Clients do not sync with each other directly.

In synchronization, client and server usually exchange accumulated record level changes, instead of physical transactions as used in replication. Change tracking is the first thing you have to face in synchronization.

Pervasync User’s Guide

1    Server Centric Pervasive Computing and Data Synchronization    7

2    Concepts of Database Synchronization    9

2.1    Data Subsetting in Database Synchronization    9

2.2    Replication versus Synchronization    10

2.2.1    Database Synchronization Is Not Replication    10

2.2.2    Replication Techniques Won’t Work for Synchronization    10

2.3    The Dangers of Timestamp Based Change Tracking    11

2.4    Logical Transaction Based Pervasync Sync Engine    12

2.4.1    Logical Transactions    12

2.4.2    Conflict Resolution During Check-In    14

2.4.3    Computation of Server Logical Transaction in Refresh    14

3    System Architecture    16

4    Installing/Un-Installing, Upgrading, and Importing/Exporting    18

4.1    Installing Pervasync Server    18

4.1.1    Getting and Un-Packing Pervasync Server Distribution    18

4.1.2    Pervasync Server System Requirements    19

4.1.3    Setting up the Sync Server Using the Pervasync Server Setup Tool    21

4.1.4    Setting up the Sync Server Using the Non-GUI Setup Scripts    25

4.1.5    Applying a License Key    28

4.2    Installing Pervasync Client for Oracle or MySQL    28

4.2.1    Getting and Un-Packing Pervasync Client Distribution    28

4.2.2    Pervasync Client System Requirements    29

4.2.3    Setting up the Sync Client Using the Pervasync Client Setup Tool    30

4.2.4    Setting up the Sync Client Using the Non-GUI Setup Scripts    33

4.2.5    Customizing the Sync Client Distribution with Pre-Seeded Configuration    35

4.2.6    Cloning Pervasync Client to Avoid Costly Initial Sync    36

4.3    Installing Pervasync Client for SQLite on Android and Blackberry    37

4.3.1    Download and Install the Binaries    37

4.3.2    Setup Pervasync Client    39

4.4    Upgrading Pervasync    40

4.4.1    Upgrading from Version 3.0 or Newer    40

4.4.2    Upgrade from a pre-3.0 version    41

4.5    Importing and Exporting the Pervasync Metadata Repository    41

4.5.1    Exporting to XML File    41

4.5.2    Importing from XML File    41

4.6    Uninstalling Pervasync Server    42

4.6.1    Resetting the Server Using the Setup GUI    42

4.6.2    Resetting the Sync Server Using the Non-GUI Scripts    43

4.6.3    Removing Pervasync Server Home    44

4.7    Uninstalling Pervasync Client for Oracle and MySQL    45

4.7.1    Resetting the Client Using the Setup GUI    45

4.7.2    Resetting the Sync Client Using the Non-GUI Scripts    46

4.7.3    Removing Pervasync Client Home    46

4.8    Uninstalling Pervasync Client for SQLite on Android and Blackberry    46

5    Using Pervasync    48

5.1    Creating Publications and Subscriptions Using the Web Admin Console    48

5.1.1    Logging in to the Admin Console    48

5.1.2    Admin Console Home    49

5.1.3    The Publish and Subscribe Model    49

5.1.4    Publishing Sync Schemas    50

5.1.5    Publishing Sync Folders    53

5.1.6    Managing Groups    55

5.1.7    Managing Sync Clients    57

5.1.8    Creating Subscriptions    59

5.1.9    Checking Client Sync History    60

5.2    Doing Synchronization Using Pervasync Client for Oracle and MySQL    60

5.2.1    Run Pervasync Client in GUI Mode    61

5.2.2    Run Pervasync Client with Command-line Interface    63

5.3    Doing Synchronization Using Pervasync Client for Android and Blackberry    64

5.3.1    Starting Sync Sessions and Viewing Sync History    64

5.3.2    Updating Setup Info and Configuring Auto Sync    66

5.3.3    Database and File Browser    66

5.3.4    Syncing Data and Files to External Storage    69

5.3.5    Embedding Pervasync Client into Your Android Application on Device    70

5.3.6    Embedding Pervasync Client into Your Blackberry Application on Device    77

5.4    Data Subsetting Using a SQL Query with Parameters    82

5.4.1    A Step by Step Example of Data Subsetting    82

5.4.2    Subsetting Modes: SIMPLE and COMPLEX    84

5.4.3    Converting a COMPLEX Query to a SIMPLE Query    86

5.5    Schema Evolution and Sync Table Reload    87

5.5.1    Schema Evolution Steps    87

5.5.2    Propagation of Table Definition to Clients and Table Reload    87

5.6    Generating Unique Key Values in Distributed Databases    88

5.6.1    Non-Conflicting AUTO_INCREMENT for MySQL Databases    88

5.6.2    Sync Sequence    89

5.6.3    Other Options    90

5.7    Avoiding Sync Errors and Conflicts    90

5.7.1    Errors Due to DB Constraint Violations    91

5.7.2    Conflict Detection and Resolution    91

5.7.3    REFRESH-ONLY to the Rescue    92

5.8    Configuration and Logging    93

5.8.1    The Server Configuration File    93

5.8.2    The Client Configuration File    96

5.8.3    Email notification settings    99

5.8.4    Logging    99

5.9    Pervasync Server Java API    99

5.9.1    Server API Javadoc    99

5.9.2    Setting Up Environment for Server Applications    100

5.10    Pervasync Client Java API    101

5.10.1    Client API Javadoc    101

5.10.2    Setting Up Environment for Client Applications    102

5.10.3    Connecting to the Local DB Schema from Your Client Application    102

5.11    File-Based Synchronization    104

5.11.1    Exporting Sync Request Files on Client    104

5.11.2    Processing the Client Request Files on Server    105

5.11.3    Importing Server Response Files on Client    106

5.12    Using Sync SQL to Create Indexes and Constraints on Local DBs    107

5.12.1    Sync SQL    107

5.12.2    An Example of Foreign Key Creation via Sync SQL    107

5.13    Sync Based on Network Characteristics    110

5.13.1    Defining Network Characteristics Using a Matching String    110

5.13.2    Specifying No-Sync Lists Associated with Sync Schemas and Sync Folders    111

5.14    Supporting Dynamic Sync Users    111

5.14.1    Use Case    112

5.14.2    Solution    112

6    The Demo Application    114

6.1    The Application Scenario    114

6.2    The Server Piece of the Application    114

6.2.1    The parameter File: server_app_oradb.ini    115

6.2.2    Compile the Application    115

6.2.3    Create and Drop the Application Schema    115

6.2.4    Publish and Unpublish    115

6.2.5    DML Operations on the Server App Schema    117

6.3    The Client Piece of the Application    117

6.3.1    The parameter File client_app_oradb.ini    118

6.3.2    Compile the Application    118

6.3.3    Synchronize with Server    118

6.3.4    DML Operations on the Device App Schema    118

7    Trouble Shooting    119

7.1    Out of Memory Errors    119

7.1.1    Adjusting Sync Client Heap Memory Size    119

7.1.2    Adjusting Sync Server Heap Memory Size    119

7.2    MySQL Innodb Lock Wait Timeout Error    120

7.3    Sync Didn’t Bring Down Newly Added/Updated Records    120

Appendix    122

A.1     Installing Java JDK on Windows    122

A.2     Installing Apache Tomcat Service on Windows    122

A.3     Installing Standalone Java JDK on Linux    123

A.4     Installing Standalone Apache Tomcat on Linux    124

A.4.1    Installing Tomcat Software    124

A.4.2    Starting/Stopping Tomcat    125

A.4.3    Adding Tomcat as a Linux Service    126

A.4.4    Relaying HTTP Port 80 Connections to Tomcat Port 8080    128

A.5     Running Pervasync Client on Windows as a Scheduled Task    129

Bibliography    132

Server Centric Pervasive Computing and Data Synchronization

Pervasive Computing, also known as Ubiquitous Computing, Mobile and Embedded Computing, is believed to be the next generation computing following Mainframe Computing, PC Computing and Internet Computing.

In the vision of Pervasive computing, small, inexpensive and robust processing devices are pervasively embedded in the environment we live and serve us anywhere, anytime [1,2]. Eventually they become so pervasive that they blend into the environment and we take their services for granted and might not be aware of their existence.

There is clearly a trend toward this. Desktops/laptop computers are becoming more and more affordable. We also see more and more people are having smart phones, PDAs, netbooks, tablet PCs and iPads. While we may not be aware of, computing devices are also being embedded in things we interact with everyday, like cars and coke machines.

Figure 1 Server Centric Pervasive Computing

The computing devices may be able to perform tasks by their own, or by interacting with human and other devices. However, more often than not, central servers are needed to serve these devices. We call this Server Centric Pervasive Computing as illustrated in Fig. 1. The devices are connected with the servers via wired or wireless networks. Applications on the devices provide an interface to end users while all data are stored on a central server. Applications on the devices communicate with the central server to do data collection, data distribution and device management.

There are basically two models for how the servers serve the devices. One is online model, where a device has to keep a connection with a server to be able to perform any functions. The other is offline model, where the device has its own cache of data and is able to function even when it’s disconnected from the server. The device only needs to connect to the server when it needs to synchronize its cache with the server database.

Intrinsically the offline model is a more scalable approach since it offloads a big chunk of the server work to the devices, which are getting more and more capable. The offline model also helps in reducing network congestion and improving responsiveness of devices. Still, the success of the offline model largely hinges on the efficiency and robustness of the underlying data synchronization system.

Today, there are all kinds of data synchronization systems available, with or without relational databases being involved. We believe that more and more data synchronization applications will be standardized on database synchronization systems; just like information management applications are now mostly based on database systems.

Globalization represents another aspect of Pervasive Computing. Typically an enterprise’s geographically distributed employees, partners and customers access central servers through the Internet. All is fine until there is a poor Internet connection or a network outage. More and more enterprises are considering adding local servers to resolve this issue. Here again, the databases of the local servers and those of the central servers need to be synchronized in order to have the best of both worlds: fast, reliable access of the information and consolidation of the information.

Pervasync database synchronization system provides a data synchronization infrastructure for your pervasive business, supporting popular databases such as Oracle DBMS, MySQL DBMS and SQLite (currently only for Google Gears). Traditional replication techniques have failed to address the large scale and personalized characteristics of Pervasive Computing. Pervasync’s innovative logical transaction based approach guarantees the convergence and stability of the system. From ground up, the system is designed to support data subsetting, so that each device can synchronize its private data as well as the shared data.

Concepts of Database Synchronization

Data Subsetting in Database Synchronization

In a database synchronization system, typically you have a big central database on the server side and a large number of small databases each residing on a device. The central database contains data for all the devices while each device’s local database only contains the device’s private data and some shared data.

In Fig. 2 we illustrate the subsetting of the data of a big central DB table. A table has columns and rows. One or more columns form a primary key to identify a row. To define the data sub-set, you can first select columns of the table. Only primary key columns and selected columns will be synchronized to the devices. This is called vertical subsetting. All devices share the same vertical subsetting, meaning they all get the same set of columns.

In the more interesting horizontal data subsetting, you select a sub-set of the rows by specifying a set of primary key values. In the figure, the rows identified by primary keys <PK 1> and <PK 2> are chosen to be distributed to all devices. These rows contain shared data. The rows identified by primary keys <PK 3> and <PK 4> are chosen to be distributed to device # 1 only. These rows contain private data for device #1. Similarly, the rows identified by primary keys <PK 5> and <PK 6> are synchronized to device # 2.

Figure 2 Data Subsetting in Data Base Synchronization

The horizontal subsetting can be carried out through a SQL query that returns the primary key values of the row sub-set. When a table is published, the SQL query is defined with embedded parameters (optional). During subscription of a client to the sync schema containing the table, concrete values for the parameters are specified. Different values cause the SQL query to return a different sub-set of data to the clients.

Replication versus Synchronization

Database synchronization is closely related to database replication. In fact, sometimes people use the terms interchangeably. However, there are big differences between them. Understanding the differences will help you understand the different approaches used for solving replication and synchronization problems.

Database Synchronization Is Not Replication

Replication is mostly used in situations where identical replicas of the complete data set are maintained for high availability and performance. Replicas can often work independently as backups for each other. On the other hand, synchronization is often between a more temporal sub-set of data and a more persistent full-set of data, both of which are integral parts of a system. For instance, parts of a file could be buffered in-memory by an operating system and are “synchronized” with the file on hard disk. Another example is the synchronization of the data in a CPU cache memory with the data in the main memory. In both cases people use the term “synchronization”, not “replication”.

In Pervasive Computing, devices maintain a data cache that is a small subset of the data stored on centralized servers. Changes to the cache are temporary and should eventually be propagated to the servers and the server should refresh the caches with up-to-date data in central databases. Clearly, this is a synchronization process, not a replication process.

Replication Techniques Won’t Work for Synchronization

In traditional database replication schemes, physical transactions on each node are recorded and played back on all the other nodes. This technique would only work if each node has a replica of the full-set data.

There is also a stability issue with physical transaction based replications when the number of nodes goes up. Transactions on different replicas may conflict with each other. To handle this, cross system locking or complicated conflict resolution schemes are needed. In fact, they are used in eager replication and lazy replication respectively [3,5].

Eager replication synchronously updates all replicas as part of one atomic transaction. This is also called synchronous replication or pessimistic replication as it incurs global locking and waiting. This scheme is not suitable for Pervasive Computing since the locking and waiting are simply not feasible in an environment where there are lots of nodes/devices. All the devices may not even be connected to the network at a same time, let alone be locked at a same time to let the transaction go through.

In contrast to eager replication, lazy replication allows updates of any replicas without locking others. The local transactions then propagate to other replicas in background. This scheme is also called asynchronous replication or optimistic replication since it is based on the assumption that conflicts will occur rarely. Here each host must apply transactions generated by the rest of the hosts. Conflicts must be detected and resolved. Since the transactions cannot be simply un-done at their origin nodes, usually manual or complicated ad hoc conflict resolutions are required at the destination nodes.

Gray et al. [3] showed that the traditional transactional replication has unstable behaviors as the workload scales up: a ten-fold increase in node number or data traffic gives a thousand fold increase in deadlocks or reconciliation. A system that performs well on a few nodes may become unstable as the system scales up to even a dozen of nodes.

The traditional database replication schemes are clearly not suited for Pervasive Computing which involves hundreds or even thousands of nodes in one system. On the other hand, we can exploit the unique characteristics of Pervasive Computing to construct a synchronization scheme that may not be suitable for traditional replication situations but works well for Pervasive Computing.

Sync solutions usually employ a client-server model, instead of the peer-to-peer model as used in replication. Clients all communicate with the server directly. Clients do not sync with each other directly.

In synchronization, client and server usually exchange accumulated record level changes, instead of physical transactions as used in replication. Change tracking is the first thing you have to face in synchronization.

The Dangers of Timestamp Based Change Tracking

All sync solutions have to somehow track the changes to records/rows on the originating DB, which we call logical transactions, and apply the changes to the destination DB.

The most popular change tracking method is based on timestamps. The approach is very straightforward. You add a timestamp column to your table and update the timestamp column whenever you change a row. This can be done in your SQL statements or through triggers. By the way, deletions have to be tracked separately, e.g. using a companion table. You track the changes on server this way and then at sync time, the sync client would come in with a last sync timestamp and you select all the server changes that have a newer timestamp than the last sync timestamp.

A lot of sync solutions put the burden of change tracking on app developers and the timestamp approach is the number one recommended technique for change tracking. This is also a widely used technique when people have to implement their own sync logic. However, be aware of its pitfalls.

One obvious pitfall is system time. Timestamps are generated using system time so messing up system time would cause problems. Don’t adjust system time even if it is off. Do not sync during time shifts between daylight saving time and standard time.

There is a more serious problem with this technique which could cause change loss. We know that the default isolation level for MySQL with Innodb and Oracle is “Read Committed”, which means that others cannot see the changes before a transaction is committed. Let’s say at time T1 you modified a record R1. The timestamp column would have a value of T1. Then before you commit, someone synced at T2 (T2 > T1). This sync could not pick up the change to record R1 since the transaction was not committed yet and the change was invisible. At T3 you committed the transaction and at T4 (T4>T3>T2>T1) you do another sync. Guess what, you still won’t get the change to R1! It was committed but it has a timestamp T1 that is older than last sync time T2. The client will forever miss the change no matter how many times you sync.

This problem is not so well known and is very hard to work around in a production environment where you can’t control when a sync and transaction would occur, and how long they would last etc.

Fortunately Pervasync has an innovatively designed sync engine that can take care of this kind of sync issues. You don’t need to have any timestamp column to worry about. Just do your normal DB DML (Data Manipulation Language) operations and the Pervasync system would track the changes for you and guarantee no change loss.

Logical Transaction Based Pervasync Sync Engine

A traditional replication system is symmetric and each node contains a full-set data, while a synchronization system is asymmetric and the client nodes contain sub-sets of the data on central server. The asymmetry, together with the instability introduced with large number of nodes, calls for a different approach than propagating physical transactions among all nodes, as used in replication.

Instead of using physical transactions, database synchronization can be carried out by exchanging logical transactions between the devices and the central server. Devices do not interact with each other directly. Instead, devices only interact with the server. The logical transaction is the result of the physical transactions. At the heart of a successful sync solution is a sync engine that computes the logical transaction in a fast and scalable way.

Logical Transactions


Figure 3 Synchronization Is Composed Of the Check In And Refresh Of Logical Transactions

In a server centric synchronization system, the server database is the single source of truth. Physical transactions applied on the central database are final. In contrast, device database serves as a cache of a sub-set of central database data. Physical transactions applied on the device database are tentative until they are checked in to the central database. At check in time, the changes committed by all the local physical transactions form a single logical transaction, which is applied directly to only the central database, not directly to other devices. The checking in of logical transactions in the sync system is just like the committing of user physical transactions in a single DB system.

In Fig. 3 we show the synchronization process of the client and server databases. During the initialization, the row-subsetting SQL query is executed on the server and the result set is downloaded to the device. Now the client data and the corresponding server sub-set of data are consistent with each other. In the figure the subset include <PK_2>, <PK_3>, <PK_5> and <PK_6>. <PK_1> and <PK_4> do not exist yet and <PK_7> through <PK_9> are outside of the subset.

Then the device application makes changes to the local database by committing physical transactions. The local changes are recorded in the form of Data Manipulation Language (DML) operations on the table rows, which include inserts, deletes and updates. At check-in time, these DML operations form a client side logical transaction, which has the following properties.

  • State based. It is the result of the one or more local physical transactions. The physical transactions bring the subset data from an old state to a new state so the logical transaction can be seen as computed from the differences between the new and old states.
  • DML merged. The original DML operations in the physical transaction may be merged in the logical transaction. For example, an insert followed by an update will be merged as an insert.
  • DML re-ordered. The order of the DML operations in the logical transaction may be different from the original ones. We arrange the DML order in a way to minimize the chances of database integrity violations at check in time, e.g. referential constraints.
  • Tentative. The logical transaction may fail to apply on server due to conflicts or other reasons. It can be modified by new client local transactions, by conflict resolution rules on server, or by force refreshing it with server changes, so that it can be checked-in successfully and become permanent. Before checked-in, the affected rows are in a temporary (tentative) state.

NOTE: The device application needs to handle not only the success/failure of the local physical transactions, but also that of the logical transactions. For example, if a check-in fails, it should try to modify the logical transaction and retry. A good synchronization infrastructure should give application the access to the logical transaction. Application can in turn expose the logical transaction and check-in process to end-users or handle it using some pre-existing logic.

The server can also generate logical transactions to refresh device data from an old state to a new state so that the whole system will be consistent. The refreshing transactions are logical transactions too, which have the following properties.

  • State based. It is the result of the one or more physical transactions occurred on the server, which can be transactions committed by server side applications or transactions committed by other devices through the “check-in” process. The logical transaction is computed from the differences between the new and old states of the subsets.
  • DML merged. The original DML operations in the physical transaction may be merged or changed in the logical transaction.
  • DML re-ordered. The order of the DML operations in the logical transaction may be different from the original ones.
  • Preemptive. The server side logical transaction should be applied to device database forcefully. That is to say, if there are any conflicts, the changes on device will be overwritten by server side changes.

Conflict Resolution During Check-In

When a device is generating changes to the local subset data, other devices or server side applications may be changing copies of the same data on the server. This could result in conflicts. To help detect conflicts, we assign version numbers to rows. If a server row still has the same version number since last sync, then we know it hasn’t been modified by others and there is no conflict.

If a conflict is detected, there are configurable resolution schemes available to ignore client transaction or ignore the conflict.

Computation of Server Logical Transaction in Refresh

The data exchange between the server and the device is two-way. Check-in is the uploading of changes from client to server and Refresh is the downloading of changes from server to client.

Figuring out the server side changes for a device is a complicated process. It could become a bottleneck for sync performance. We cannot simply log the transactions on the whole table as done in replication schemes. What we need is the changes to the subset of data for a device, which is defined by the subsetting SQL query. Each unique set of the query parameter values corresponds to a subset of records. Changes made to the server side tables bring a subset from an old state to a new state. We need to compute the differences between the new and old states and wrap them into a logical transaction, then send them to the device and refresh the subset data on device to the new state.

In our implementation, for each base table, we create a companion log table to keep the DML operations made to the base table. We also create a companion state table to keep the states of all the data subsets. Each subset is identified by a unique parameter-value combination, and the state table contains the logical DML operations on the data subsets.

A sync engine that runs periodically in the background does the work of updating the state table. In each run, the refresh engine would identify the changed records and the associated DML operations. It then updates the state table with this information and assigns the changed records a new state number. The algorithm is as follows.

For each subset

  • Delete: if a record is in the state table subset but not in the current SQL query result set, mark it as “delete” in state table.
  • Insert: if a record is in the current SQL query result set but not in the state table subset, insert it to the state table and mark it as “insert”.
  • Update: if a record is in all of the following three tables: the state table subset, the current SQL query result set and the base table’s new logs, mark it as “update” in state table.

Note that our algorithm produces logical insert and delete operations. For example, suppose we have a TASKS table that has a user column for task owners. We could use user column in the subsetting of the tasks table so that each user only gets the tasks he or she owns. Now, if we update a task record to change the user from user1 to user2, user1 would get a logical delete operation to his subset and user2 would get a logical insert operation to his subset. This re-assignment type of operations is quite common in real-world applications and our algorithm handles it very well. Traditional replication schemes, which use original physical transactions, wouldn’t be able to handle this.

Note that data subsetting is essential to Pervasive Computing, which is based on user, location and device dependent data management. At sync time, device brings in the state number of its subset and downloads the records that have a newer state number. For records marked with a delete operation, we would only download the primary keys.

System Architecture

When you build an application for a server centric Pervasive Computing system, you typically would have a server piece and a client piece of the application (Fig. 4). The server application runs on the central server interacting with the data stored in the server application schema. The client application runs on the devices interacting with the data stored in the client application schema.

Figure 4 Pervasync Database Synchronization System Architecture

Your applications can rely on the Pervasync Database Synchronization System (shaded parts of Fig. 4) to make the client app schema and the server app schema in-sync. On the server side, the server application interacts with the sync server via the sync server admin API to define the sync behavior, e.g. data subsetting. On the client side, the client application interacts with the sync client. Each sync client has a sync agent API and a sync client admin API.

The Pervasync synchronization infrastructure has a server piece and a client piece. The sync server connects with the server admin schemas, which contain the sync server metadata. Multiple server DBs can be supported for scaling out. There is one server admin schema on each server DB. The sync server itself also can have multiple instances to meet scalability requirements.

The sync client connects with a client admin schema, which contains sync client metadata. Client application can initiate synchronization by invoking the sync agent. The sync client admin API can be used by client application to manage the sync client metadata.

Sync clients communicate with sync server via HTTP. Logical transactions from the client DB are wrapped in the HTTP requests and sent to server to be checked-in to server DB by the sync servlet. Check-in is the first half of the sync session. The second half of the sync session is Refresh, in which the logical transaction from server is wrapped in the HTTP response and sent to client.

In addition to the sync servlet component, which interacts directly with sync clients, the sync server also has a sync engine. Sync engine is a background process that updates data subset state tables by processing table logs so that sync servlet can quickly figure out server side changes for a client.

Installing/Un-Installing, Upgrading, and Importing/Exporting

Installing Pervasync Server

There is only one Pervasync server package that you can download from Pervasync web site. The single server package can work with both Oracle and MySQL databases. Once the Pervasync server is up and running, users can download sync clients from the start page of the server web application.

The installation process includes un-packing and setting up.

Getting and Un-Packing Pervasync Server Distribution

The latest and greatest version of Pervasync server is available on the Pervasync products web page at:

http://www.pervasync.com/products.

Download the server zip file, e.g. pervasync_server-4.0.0.zip, and save it on your server computer.

To install Pervasync server, choose a directory and un-pack the zip file there. For example, the following commands un-pack the server distribution and create sync server home /pervasync_server-4.0.0 on a Linux/Unix machine.

cd /

unzip pervasync_server-4.0.0.zip

On Windows platform, replace “/” with “\”. By the way the sync home doesn’t have to be under the top most root directory.

NOTE: Perform the setup as the user that will run the web container for Pervasync server. For example, if you run Tomcat on Linux as user tomcat, you should do the Pervasync server setup as user tomcat. If you do the setup as super user (e.g. root), you should change the ownership of the Pervasync server tree to tomcat at the end of the setup.

A Pervasync server home has the following directory layout:

pervasync_server-4.0.0

|

+— bin // contains executables.

|

+— classes // contains Pervasync java classes

|

+— config // contains configurations files

|

+— demo // contains demo apps

|

+— doc // contains documentation

|

+— install // contains scripts for setup, un-install etc.

|

+— lib // contains library jars

|

+— web // contains server web app

|

+— README.txt // the readme file

NOTE: You may encounter file permission issues on Windows 7 and Linux if you create un-pack the Pervasync home with an admin/root user account and later on setup or run the server with a non-admin user account. The reason is that at runtime, Pervasync needs to write some internal files so it has to have write permission to the home folder. The solution is to grant the runtime user FULL CONTROL of the Pervasync home. Better yet, make the runtime user the owner of the home.

Pervasync Server System Requirements

Before you run the setup program to setup the server, make sure you have the following ready.

Database Engines and JDBC Drivers

Pervasync server supports

  • MySQL version 5.0.2 or newer and
  • Oracle database version 8.1.7 or newer.

Get Oracle database products from Oracle:

http://www.oracle.com/technology/software/products/database/index.html

and MySQL database products from MySQL/Oracle:

http://dev.mysql.com/downloads/

Install the desired database engine. Make sure your database is up and the appropriate JDBC driver is available.

You need JDBC drivers for Pervasync server to communicate with the database engine. You need ojdbc5.jar or ojdbc6.jar for Oracle and mysql-connector-java-5.x.x-bin.jar for MySQL. You can download an Oracle JDBC driver (e.g. ojdbc5.jar or ojdbc6.jar) from Oracle:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

and a MySQL Java connector (e.g., mysql-connector-java-5.x.x-bin.jar) from MySQL/Oracle:

http://dev.mysql.com/downloads/connector/j/.

Copy the JDBC driver (the JDBC jar files) to the lib directory under Pervasync server home. Pervasync server home is the Pervasync directory where the README.txt resides.

NOTE: If you run Oracle for central DB, you only need Oracle JDBC driver jar. If you run MySQL, you only need MySQL Java connector jar.

Java Platform

The sync server and sync client are written in pure Java and can be installed on Windows, Linux and Mac OS X platforms that have a Sun JDK version 1.6 or newer installed.

Get the latest JDK from Oracle:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Install JDK on server host following the instructions.

NOTE: Pervasync server requires Sun JDK. It may not be able to work with pre-installed OpenJDK or gcj on Linux. Refer to the appendixes of this guide on steps of installing standalone Sun JDK on Linux. Instructions are also available in appendixes for installing JDK on Windows.

NOTE: Open a new shell window and run “java –version” on the command line to make sure the newly installed JDK is taking effect. Sample output:

java version “1.6.0_17”

Java(TM) SE Runtime Environment (build 1.6.0_17-b04)

Java HotSpot(TM) Client VM (build 14.3-b01, mixed mode, sharing)

If the old JDK or non-Oracle/Sun JDK is still taking effect, check the PATH env variable to make sure the bin dir of the new JDK is on the front.

Servlet/JSP Containers or Application Servers

To setup Pervasync server, you have to install a Java servlet container, e.g. Apache Tomcat. The sync server includes a standard J2EE servlet app, which needs to be deployed on an application server or a J2EE container. You may use whatever containers you prefer, but the following containers are recommended for the sync server:

Install one of the servlet containers on server host following the instructions.

NOTE: Pervasync server requires Tomcat 6 and cannot work with pre-installed Tomcat 5 on Linux. Refer to the appendix of this guide on steps of installing standalone Tomcat 6 on Linux and installing Tomcat as a service on Windows.

Setting up the Sync Server Using the Pervasync Server Setup Tool

NOTE: There are two ways setting up the sync server, a GUI way and a command-line scripts way. This section describes the GUI way. See section 4.1.4 for steps to use the non-GUI setup scripts in folder “install” to set up the server.

The pvs.sh (or pvs.bat for Windows) program in the “bin” folder is the entry point to the Pervasync Server Setup Tool for setting up the server. If you have a hosted sever and can only access the server via a text based telnet or ssh window, the program won’t run. However, it is not difficult to set up a graphic remote desktop to the server. For example, you could use VNC. See http://www.realvnc.com/support/getting-started.html for details.

NOTE: Optionally you may want to edit the conf files located in the config directory. Some of the configurations, such as pervasync.server.db.user.options and pervasync.server.db.user.grants, affect setup as well as server runtime. If you do edit the conf file, do it before you launch the Setup Tool GUI so that the new settings can take effect.

Launching the Pervasync Server Setup Tool

To launch the Pervasync Server Setup Tool, change directory to the “bin” folder of Pervasync server home and invoke “pvs.bat” for Windows or “pvs.sh” for Linux. A GUI window will pop up asking the database type of your central DB.

NOTE: If you see the following error, most likely you are using a Java version older than JDK 6. Download and setup JDK 6 or newer.

In the DB type dialogue window, select MYSQL or ORADB (for Oracle databases) and click OK. Then you will be presented with the main window with a Setup tab.

Use the “Setup” tab to setup the sync server. Carefully examine all the input fields and fill in proper values. Fields that have an asterisk (*) next to their names are required to be filled. Some fields are made read-only and you normally shouldn’t change them. If you do need to change their values, click “Advanced Mode” on the top right corner. See next section for descriptions of the fields.

TIP: Move mouse cursor over a field input box to show the description of the field (tooltip).

Central DB Connection Properties

Sync server needs a Pervasync admin schema to store its metadata. To create that schema, the database root/system user name and password are needed. The top panel of the setup screen is for the database connection. Listed below are the explanations of text boxes of the GUI:

  • JDBC URL – This is the URL to the Pervasync server repository database. By default, it’s jdbc:oracle:thin:@//localhost:1521/xe for Oracle, and jdbc:mysql://localhost:3306/ for MySQL. You may need to edit it to reflect your database’s actual host name, port number and SID (for Oracle only).
  • System User – Name of a DB user with System privileges. Normally you would use SYSTEM for Oracle and root for MySQL. This user/account should be pre-existent. The System user is only used to create/drop Pervasync Admin user during setup/reset. The System password is not saved.
  • System Password – System user’s password.
  • Pervasync Admin User – Pervasync server admin user name. You would use this user name to login to the web-based Pervasync admin console. If not already existing, a database user/schema with this name will be created in Pervasync DB repository during setup. At reset time, this user/schema will be dropped.
  • Admin Password – Pervasync server admin user’s password.
  • Server Host Name – The sync server host name or IP. This is for MySQL and Advanced Mode only. When MySQL database and the sync server are on separate hosts, sync server host name/IP is needed for authorization purposes. This is in addition to the DB user name.

    NOTE: Use the host name or IP of the machine on which you run the setup tool, not that of the databse.

Pervasync Web App Deployment

The bottom panel of the setup screen is for Pervasync web app deployment. The web folder in Pervasync server home folder contains a standard J2EE web app for the sync servlet and admin console. The web app can be deployed into any J2EE containers. If you select Tomcat as the container, the web app will be automatically deployed to Tomcat during setup. Otherwise, you need to deploy it yourself following the specific web container’s deployment instructions.

If you select Apache Tomcat, you need to click “Locate Tomcat Home“, then browse and find Tomcat Home directory.

NOTE: If you install a standalone Tomcat server and all the sub-folders such as conf and bin are under a same root folder, the root folder is the Tomcat Home. However if Tomcat is installed using one of the Linux distribution packages, Tomcat sub-folders may be installed in different places. For example, conf, webapps and log sub-folders may be put in /var/lib/tomcat6, while bin and doc are put in /usr/shared/tomcat6. In this case, use the root folder that contains the conf sub-folder as Tomcat Home, i.e. /var/lib/tomcat6.

Performing Setup

Now, simply click “Setup” button to start the setup process, which will do the following:

  1. Set up the sync server home.
  2. Create the sync server admin user/schema in the database if it does not exist; or upgrade the schema if already exits.
  3. Deploy the sync server web application if you select Tomcat as the servlet container.

NOTE: Do not move or remove the sync server home after setup.

NOTE: You could setup multiple sync server instances on different machines. All these sync servers share one and only one sync server DB user. To set up a new instance, repeat the setup on a new host machine.

Once the setup completes successfully and the Pervasync web application is properly deployed, you can access the start page of the Pervasync server web at:

http://<server&gt;:<port>/pervasync, e.g. http://localhost:8080/pervasync

If you see the Pervasync web app start page like that showing below, then your sync server setup has been successful. Congratulations!

Use Pervasync Admin User and Admin Password you specified in the GUI of server setup to login to the web based admin console after which you can publish central database tables and create sync users.

On the same Pervasync start page there is a link to the Java based sync client for end users to download.

NOTE: Users do not need to login to the web based admin console to download the sync client.

If your Internet Browser cannot display the web page, or it can’t establish a connection to the server, then you need to check your servlet container (e.g. Tomcat) to see whether or not it is started. If your servlet container is up but Pervasync is still not available, first check the log files of your servlet container (e.g. files inside the logs folder of Tomcat home) and then the log files in the log folder of Pervasync home to see if there are error messages.

NOTE: You may need to re-start your servlet container after the web app deployment.

If you see an AccessControl exception like the following, then your Tomcat has a security manager on.

SEVERE: Exception starting filter accessControl

java.lang.RuntimeException: java.security.AccessControlException: access denied (java.io.FilePermission /opt/pervasync/config read)

Locate your Tomcat startup script and remove “-security” and then restart Tomcat.

Updating Server Settings

If you need to change Pervasync server admin user password etc. after you have configured Pervasync server, you can go to the “bin” folder and invoke “pvs.bat” for Windows or “pvs.sh” for Linux. The setup main screen will pop up. You can make changes and click “Update”.

Setting up the Sync Server Using the Non-GUI Setup Scripts

NOTE: Skip this section if you have finished setting up the sync server using the Pervasync Server Setup Tool (pvs.sh/pvs.bat) described in section 4.1.3.

This section describes the setup steps using the non-GUI setup scripts in folder “install” of Pervasync server. However, if you have GUI access to your server, it is recommended to use the Server Setup Tool described in section 4.1.3 instead to setup the server.

Edit the ini File

Locate file pervasync_server_oradb.ini or pervasync_server_mysql.ini under directory install. Use your favorite editor to edit the property values.

  • pervasync.server.db.url – This is the URL to the Pervasync server repository database. By default, it’s jdbc:oracle:thin:@//localhost:1521/xe for Oracle, and jdbc:mysql://localhost:3306/ for MySQL. You may need to edit it to reflect your database’s actual host name, port number and SID (for Oracle only).
  • pervasync.server.host – The sync server host name or IP. This is for MySQL and Advanced Mode only. When connecting to MySQL database from the sync server, sync server host name is needed for authorization purposes. This is in addition to the DB user name.
  • pervasync.server.db.system.user – Name of a DB user with System privileges. By default, use SYSTEM for Oracle and root for MySQL. This user/account should be pre-existent.
  • pervasync.server.db.system.password – System user’s password.
  • pervasync.server.admin.user – Pervasync server admin user name. You would use this user name to login to the web-based Pervasync admin console. If not already exists, a database user/schema with this name will be created in Pervasync DB repository during setup. At reset time, this user/schema will be dropped.
  • pervasync.server.admin.password – Pervasync server admin user’s password.

NOTE: Optionally you may want to edit the conf files located in the config directory. Some of the configurations, such as pervasync.server.db.user.options and pervasync.server.db.user.grants, affect setup too.

Copy the JDBC Driver or Java Connector jar to the “lib” Folder

If you haven’t done so, download and save the JDBC jar files (e.g., ojdbc5.jar/ojdbc6.jar for Oracle or mysql-connector-java-5.x.x-bin.jar for MySQL) to the lib directory under Pervasync server home.

Run the Setup Scripts

In the following we use Oracle as an example. Replace oradb with mysql for MySQL DBMS.

Windows:

cd install

.\pervasync_server_oradb_setup.bat

Linux/Unix:

cd install

./pervasync_server_oradb_setup.sh

These setup scripts will create the sync server DB user/schema if it does not exist; or upgrade the schema if already exits.

NOTE: If you see the following error, most likely you are using a Java version older than JDK 6. Download and setup JDK 6 or newer.

Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file

To drop the sync server DB user, run the drop DB scripts pervasync_server_oradb_reset.bat or pervasync_server_oradb_reset.sh depending on your OS platform.

NOTE: To run the batch scripts successfully, pervasync_server_oradb.ini must have complete and correct information. After you run the scripts you may want to erase sensitive information such as passwords from the ini file for security reasons.

Deploy the Sync Server Web Application by Linking

The web folder contains a standard J2EE web app for the sync servlet and admin console. It could be deployed into any J2EE containers. In the following we use Tomcat servlet container as an example.

One way to deploy the web app to Tomcat is to tell Tomcat where the Pervasync web app is located. We call this linking.

Edit the configuration file located at <Pervasync Server Home>\config\Catalina\localhost\pervasync.xml. Change the docbase to where the Pervasync server web\pervasync folder is located, e.g.

docBase=”/pervasync_server-4.0.0/web/pervasync”.

Then copy the Catalina folder to Tomcat conf directory using the following commands.

Windows:

cd C:\pervasync_server-4.0.0\config\

xcopy /I /R /Y /E Catalina C:\apache-tomcat-6.0.14\conf\

Linux/Unix:

cd /pervasync_server-4.0.0/config/

cp –r Catalina /apache-tomcat-6.0.14/conf/

Deploy the Sync Server Web Application by Copying

Alternatively, you could deploy the sync server web application by copying the whole web\pervasync folder to Tomcat webapps folder.

NOTE: If you have already followed instructions in section 4.1.4.4 and deployed the web app by linking, do not deploy again by copying.

Windows:

cd C:\pervasync_server-4.0.0\web

xcopy /I /R /Y /E pervasync C:\apache-tomcat-6.0.14\webapps\pervasync

Linux/Unix:

cd /pervasync_server-4.0.0/web

cp –r pervasync /apache-tomcat-6.0.14/webapps/pervasync

Restart Servlet Container

Finally, restart your Java web container, e.g. Tomcat.

Your sync server should be available. As a test, enter the following URL in a web browser

http://<server&gt;:<port>/pervasync, e.g. http://localhost:8080/pervasync

If your Internet Browser cannot display the webpage, or it can’t establish a connection to the server, then you need to check your servlet container (e.g. Tomcat) to see whether or not it is up and running. You need to start it If is down. Otherwise check the log files in its logs folder to find error messages related to Pervasync web app.

If you see the Pervasync web app start page page as shown in section 4.1.3.4 then your sync server setup has been successful. Congratulations! Use the Pervasync admin user name and password you specified in 4.1.4.1 to login. Those are the property values you specified for pervasync.server.admin.user and pervasync.server.admin.password in the ini file.

Applying a License Key

Pervasync issues you a license key when you buy a commercial license. To apply the license key, login to the web admin console, go to “Home”, locate and click “Apply License Key” under “Other Admin Tasks”. Then fill in the license key and click Apply.

Alternately you can also apply the key by manually modifying the server config file. Locate the server config file in folder <Pervasync server home>/config, e.g. pervasync_server_mysql.conf for MySQL. Edit the following line and replace the value “0” with the license key:

pervasync.server.license.key=0

Then re-start the web server, e.g. Tomcat.

NOTE: License key could be applied before or after the server setup.

Installing Pervasync Client for Oracle or MySQL

This section is for Java SE based sync client for Oracle or MySQL. See next section for sync client setup for SQLite that comes with Android and Blackberry devices. The installation process includes unpacking and setting up.

NOTE: Pervasync client requires Oracle/Sun JDK and may not be able to work with pre-installed OpenJDK or gcj on Linux. Refer to the appendix of this guide on steps of installing standalone Oracle/Sun JDK on Linux.

Getting and Un-Packing Pervasync Client Distribution

When you get your server up and running, local database users can download sync client from the start page of Pervasync server web app:

http://<server&gt;:<port>/pervasync, e.g. http://localhost:8080/pervasync

Download the client zip file, e.g. pervasync_client-4.0.0.zip, and save it on the computer that runs local database.

To install Pervasync client, choose a directory and un-pack the zip file there. For example, the following commands un-pack the client distribution and creates sync client home /pervasync_client-4.0.0 on a Linux/Unix machine.

cd /

unzip pervasync_client-4.0.0.zip

On Windows platform, replace “/” with “\”. By the way the sync home doesn’t have to be under the top most root directory.

A Pervasync client home has the following directory layout:

pervasync_client-4.0.0

|

+— bin // contains executables.

|

+— classes // contains Pervasync java classes

|

+— config // contains configurations files

|

+— demo // contains demo apps

|

+— doc // contains documentation

|

+— lib // contains library jars

|

+— README.txt // the readme file

NOTE: You may encounter file permission issues on Windows 7 and Linux if you un-pack the Pervasync home with an admin/root user account and later on setup or run the client with a non-admin user account. The reason is that at runtime, Pervasync needs to write some internal files so it has to have write permission to the home folder. The solution is to grant the runtime user FULL CONTROL of the Pervasync home. Better yet, make the runtime user the owner of the home.

Pervasync Client System Requirements

The first time you run the pvc program (pvc.bat or pvc.sh) located in the bin folder, you will be presented with a setup screen for you to perform setup. Before you do that, make sure you have the following ready.

Database Engines and JDBC Drivers

Pervasync client supports

  • MySQL version 5.0.2 or newer and
  • Oracle database version 8.1.7 or newer.

Install the desired database engine. You will also need JDBC drivers for Pervasync client to communicate with the database engine. You can download an Oracle JDBC driver (e.g. ojdbc5.jar or ojdbc6.jar) from Oracle:

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

and/or a MySQL Java connector (e.g., mysql-connector-java-5.x.x-bin.jar) from MySQL/Oracle:

http://dev.mysql.com/downloads/connector/j/.

Save the JDBC jar files to the lib directory under Pervasync client home.

Java Platform

The sync server and sync client are written in pure Java and can be installed on Windows, Linux and Unix platforms that has a JDK version 1.6 or newer installed.

Get the latest JDK from Oracle:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Install JDK on client host following the instructions.

Setting up the Sync Client Using the Pervasync Client Setup Tool

The Sync client comes with a GUI Setup Tool that can be used to setup the sync client. This is the preferred way to do client setup. Alternatively, you could also use the setup scripts located in the “install” folder to do the setup. The steps are described in 4.2.4.

Launching the Sync Client Setup Tool by Invoking pvc.bat/pvc.sh

NOTE: Before you launch the Sync Client Setup Tool, you may want to edit the conf files located in the config directory. Some of the configurations, such as pervasync.client.db.user.options and pervasync.client.db.user.grants, affect setup as well as client runtime.

To launch the Sync Client Setup Tool, change directory to the “bin” folder of Pervasync client home and invoke “pvc.bat” for Windows or “pvc.sh” for Linux. A GUI window will pop up asking the database type of local DB.

NOTE: If you see the following error, most likely you are using a Java version older than JDK 6. Download and setup JDK 6 or newer.

In the DB type dialogue window, select MYSQL or ORADB (for Oracle databases) and click OK. Then you will be presented with the main setup window.

Carefully examine all the input fields and fill in proper values. Fields that have an asterisk (*) next to their names are required. Some fields are made read-only. If you do need to change their values, click “Advanced Mode” on the top right corner. Read following sections for explanations of each field.

Local Database Connection

Sync client needs a Pervasync admin schema to store its metadata. To create that schema, the database root/system user name and password are needed.

The top panel of the sync client setup tab is for local database connection info. Listed below are the explanations of text boxes of the GUI:

  • JDBC URL – This is the URL to the Pervasync client repository database. By default, it’s jdbc:oracle:thin:@//localhost:1521/xe for Oracle, and jdbc:mysql://localhost:3306/ for MySQL. You may need to edit it to reflect your database’s actual host name, port number and SID (for ORADB only). In normal mode this field is read-only. Check “Advanced Mode” on the top-right corner to make it editable.
  • System User – Name of a DB user with System privileges. Normally you use SYSTEM for Oracle and root for MySQL. This user/account should be pre-existent with your Oracle or MySQL DB. The System user is only used to create/drop Pervasync Admin user during setup/reset. The System password is not saved to be used during runtime.
  • System Password – System user’s password.
  • Pervasync Admin User – Pervasync client admin user name. If not already existing, a database user/schema with this name will be created in Pervasync DB repository at setup time. At reset time, the user/schema will be dropped.
  • Admin Password – Pervasync client admin user’s password.

Sync Server Connection

The bottom panel is for sync server connection. Sync client needs the following information to connect to and authenticate with a sync server.

  • Sync Server Url – Pervaync server URL. Use the host name/IP that has the sync servlet deployed. It’s http://localhost:8080/pervasync/server by default.
  • Sync User Name – Pervasync user name. The user and device should be created on sync server using server admin API or the web-based admin console. This can be done before or after client setup. The user name, device name and user password that are set here have to match those specified on server so that the client can sync with the server.
  • Sync Device Name – Pervasync user device name. By default, it’s DEFAULT.
  • Sync User Password – Pervasync user’s password.
  • HTTP Proxy Host – If sync client resides inside a firewall and sync server resides outside the firewall, you need to set the HTTP proxy. Proxy host takes the host name or IP, for example: www-proxy.mycorp.com. You need to check/click “Advanced Mode” to be able to see and edit HTTP proxy info.
  • HTTP Proxy Port – Proxy port takes the port number, for example: 80.

Performing Client Setup

Now, simply click on the “Setup” button to start the setup process, which will setup the sync client home, create the sync client DB user/schema if it does not exist, or upgrade the schema if already exits.

Once client setup completes successfully, you will see two more tabs, Sync and Schedule, added to the sync client main window. Before you can sync the client with the server, go ahead and follow the steps in section 5.1 to create the sync user and device for this client, a sync schema with a simple table and subscribe the client to the schema.

Setting up the Sync Client Using the Non-GUI Setup Scripts

NOTE: Skip this section if you have finished setting up the sync client using the Pervasync Client Setup Tool (pvc.sh/pvc.bat) described in section 4.2.3.

This section describes the setup steps using the scripts located in the “install” folder.

Edit the ini File

Locate file pervasync_client_oradb.ini or pervasync_client_mysql.ini under directory install. Use your favorite editor to edit the property values.

  • pervasync.client.db.url – This is the URL to the Pervasync client repository database. By default, it’s jdbc:oracle:thin:@//localhost:1521/xe for Oracle, and jdbc:mysql://localhost:3306/ for MySQL. You may need to edit it to reflect your database’s actual host name, port number and SID (for ORADB only).
  • pervasync.client.host – The sync client host name or IP. This is for MySQL only. When connecting to MySQL database from the sync client, sync client host is needed for authorization purposes. This is in addition to the DB user name. By default, it’s localhost.
  • pervasync.client.db.system.user – Name of a DB user with System privileges. Normally use SYSTEM for Oracle and root for MySQL. This user/account should be pre-existent.
  • pervasync.client.db.system.password – System user’s password.
  • pervasync.client.admin.user – Pervasync client admin user name. If not already existent, a database user/schema with this name will be created in Pervasync DB repository at setup time. At reset time, the user/schema will be dropped.
  • pervasync.client.admin.password – Pervasync client admin user’s password.
  • pervasync.server.url – Pervaync server URL. Use the host name/IP that has the sync servlet deployed. It’s http://localhost:8080/pervasync/server by default.
  • pervasync.user.name – Pervasync user name. The user and device should be created on sync server using server admin API or the web-based admin console. This can be done before or after client setup. The user name, device name and user password that are set here have to match those that are created on server so that the client can sync with the server.
  • pervasync.device.name – Pervasync user device name. By default, it’s DEFAULT.
  • pervasync.user.password – Pervasync user’s password.
  • pervasync.client.http.proxy.host – If sync client resides inside a firewall and sync server resides outside the firewall, you need to set the HTTP proxy. Proxy host takes the host name or IP, for example: www-proxy.mycorp.com.
  • pervasync.client.http.proxy.port – Proxy port takes the port number, for example: 80.

NOTE: Optionally you may want to edit the conf files located in the config directory. Some of the configurations, such as pervasync.client.db.user.options and pervasync.client.db.user.grants, affect setup too.

Copy the JDBC Driver or Java Connector jar to the “lib” Folder

If you haven’t done so, download and save the JDBC jar files (e.g., ojdbc5.jar/ojdbc6.jar for Oracle or mysql-connector-java-5.x.x-bin.jar for MySQL) to the lib directory under Pervasync client home.

Run the Setup Scripts

Then run the setup batch scripts: pervasync_client_oradb_setup.bat (pervasync_client_mysql_setup.bat for MySQL) on Windows, or pervasync_client_oradb_setup.sh (pervasync_client_mysql_setup.sh for MySQL)
on Linux/Unix.

Windows:

cd install

.\pervasync_client_oradb_setup.bat

Linux/Unix:

cd install

./pervasync_client_oradb_setup.sh

These setup scripts will create the sync client DB user/schema if it does not exist; or upgrade the schema if already exits.

NOTE: If you see the following error, most likely you are using a Java version older than JDK 6. Download and setup JDK 6 or newer.

Exception in thread “main” java.lang.UnsupportedClassVersionError: Bad version number in .class file

To drop the sync client DB admin user, run the drop DB scripts pervasync_client_oradb_reset.bat or pervasync_client_oradb_reset.sh depending on the platform.

NOTE: To run the shell scripts successfully, pervasync_client_oradb.ini or pervasync_client_mysql.ini must have complete and correct information. After you run the scripts you may want to erase sensitive information such as passwords from the ini file for security reasons.

Congratulations! You have finished server and client setup. Before you can sync the client with the server, go ahead and follow the steps in section 5.1 to create the sync user and device for this client, a sync schema with a simple table and subscribe the client to the schema.

Customizing the Sync Client Distribution with Pre-Seeded Configuration

For deployment of large number of sync clients, it is often desirable to customize the sync client with pre-seeded configuration so that

  1. it is much easier for end users to setup the client, and
  2. it is much harder for end users to mess up with things like local DB connection credentials.

The sync client zip file, e.g. pervasync_client-4.0.0.zip, is located on the sync server at <Pevasync server home>/web/download. You can un-pack it, modify it and zip it back. Then when sync users download the client from the server web, they will get the modified version. Following are the steps.

  1. Un-pack the zip file, for example:

cd \pervasync\web\pervasync\download

unzip pervasync_client-4.0.0.zip

  1. Now you have a sync client folder, e.g. \pervasync\web\pervasync\download\ pervasync_client-4.0.0. Change directory to the “bin” sub-folder and start the pvc.bat/pvc.sh program.

cd pervasync_client-4.0.0\bin

pvc.bat

  1. Complete the client setup process. In the process the sync client will copy the appropriate JDBC driver (or MySQL Java connector) to the “lib” folder of sync client home under web\pervasync\download. Sync client will also save the local DB connection info and sync server connection info in file pervasync_install.dat under “config” folder of sync client home.
  2. Copy pervasync_install.dat to classes\pervasync\config\ under sync client home.

copy ..\config\pervasync_install.dat ..\classes\pervasync\config\

  1. Edit the copied file pervasync_install.dat under classes\pervasync\config\. Remove the properties that you don’t want to be pre-seeded. Any properties left in the file will become pre-seeded and read-only during end user setup. You would want to leave system and admin user/password there and remove sync user name, device name and password. Also don’t touch the encryption key.
  2. Do a reset of the client using the Setup tab of the sync client setup tool..
  3. Zip up the client home folder.

cd \pervasync\web\pervasync\download

zip pervasync_client-4.0.0.zip pervasync_client-4.0.0

rmdir /S /Q pervasync_client-4.0.0

When end users download and setup your customized sync client distribution, certain fields will have pre-seeded, read-only values as you desired.

Cloning Pervasync Client to Avoid Costly Initial Sync

Initial sync could take a long time if you have Gigabytes of application data or files to sync. If a lot of the data or files are shared by all or groups of your clients, you could use cloning to avoid doing an initial sync for each client.

NOTE: “Sharing the same data or files” means that clients subscribe to a schema table or a sync folder with the same parameter values. This includes no parameter subscriptions.

Let’s call the client to clone from the “seed client” and the client to clone to the “target client”. The idea is to sync the data and files to the seed client, make a copy of the seed client and change the client ID and user name to those of the target client. Following are the cloning steps.

  1. Create publications, clients and subscriptions on Pervasync Admin Console.
  2. Install and sync the seed client. For fast synchronization, install the client on the same host or network as the Pervasync server.
  3. Copy the seed client to target client hosts. Includes Pervasync client home folder, local database and folders synced to client. Refer to documentation of the specific database type (Oracle, MySQL) on how to clone the database schemas to another host. Remember to include both Pervasync admin schema (default “pvcadmin”) and the application schema(s) when you clone the databases.
  4. On Pervasync Admin Console, click on the “Clients” tab and find the client ID, user name and device name of the target client. Also create subscriptions for the target client if you haven’t done so.
  5. On the target client host, connect to the Pervasync admin schema (default “pvcadmin”) of the local database. Change the client ID, user name and device name in table pvc$sync_client_properties from seed client to target client. Assuming your target client ID is 12, client name is “user_2” and device name is “DEFAULT”, do the following:

update pvcadmin.pvc$sync_client_properties set value=’12’ where name = ‘pervasync.client.id’;

update pvcadmin.pvc$sync_client_properties set value=’user_2′ where name = ‘pervasync.user.name’;

update pvcadmin.pvc$sync_client_properties set value=’DEFAULT’ where name = ‘pervasync.device.name’;

  1. On the target client host, open file <Pervasync Client Home>/config/pervasync_install.dat. You may need to change some parameter values. For example, if the database is on a different host than the sync client host, you will need to correct values of pervasync.client.db.url and pervasync.client.host.

    NOTE: If Pervasync client and DB are on the same host, you should use “localhost” in the values when you setup the seed client. Then you don’t need to update the pervasync_install.dat file.

  2. On the target client host, launch the sync client (pvc.bat or pvc.sh). You will find that the new values are reflected on the “Setup” tab. Use the same tab to update sync user password if needed.
  3. Start a sync session. You will see that shared data/files are not re-synced while client specific data/file subscriptions are synced to client.

NOTE: Make sure you have all the sync folder files on the target host. Any missing files will cause delete operations sent to server during synchronization. To avoid accidental deletions, consider using REFRESH-ONLY sync or setting the server config parameter “pervasync.policy.check.in.deletes” to “DISCARD” depending on your business logic.

Installing Pervasync Client for SQLite on Android and Blackberry

Pervasync clients for Android and Blackberry synchronize the on device native SQLite databases with your central database. The installation process includes downloading and setting up.

NOTE: Pervasync supports Android version 1.5 and newer, Blackberry version 5.0 and newer.

Download and Install the Binaries

Enter the following URL to the web browser on your mobile device.

http://<sync server name>:<port>/pervasync

You will be presented with the Pervasync start page where you can find the links to the Pervasync client apk file for Android and jad file for Blackberry. Click to download and install.

NOTE: If you are using a Blackberry device simulator, you will need to start the MDS simulator in order for the Blackberry browser to work. Also, on devices with Blackberry OS 5.0, the Pervasync start page may not be rendered properly. In that case, enter the following URL of the jad file directly to the browser on your Blackberry.

http://<sync server name>:<port>/pervasync/download/pervasync.jad

Below are screen shots for installation of Pervasync on Android devices.


On Blackberry devices you will see the following.



After a successful installation, you can find Pervasync app icon among other installed apps. Click to launch the client app.

Setup Pervasync Client

The first time you launch the Pervasync client, you will be presented with the sync client setup screen, which has the following fields to fill in.

  • Server Url – Pervaync server url. Use the name/IP of the host that has the sync servlet deployed. Don’t forget the port number if it’s not 80.
  • Sync User Name – Pervasync user name. The user and device should be created on sync server using server admin API or the web-based admin console. This can be done before or after client setup. The user name, device name and user password that are set here have to match those that are created on server so that the client can sync with the server.
  • Device Name – Pervasync user device name. By default, it’s DEFAULT.
  • User Password – Pervasync user password.

Optionally you can change the sync direction and log level.

Following are screen shots for Android and Blackberry devices respectively.





Click “Setup” button to start the setup process. Once you are done with the setup, Pervasync client will be re-launched displaying the “Sync” tab for Android and “Sync” screen for Blackberry. You are now ready to start a sync session. Jump to section 5.3 to learn how to do synchronization, browse synced tables and files and how to embed the sync client into your on-device application.

Upgrading Pervasync

In general, to upgrade to a new version, do not perform the reset steps, which would wipe out the Pervasync DB repository among others. Instead, install the new version to a new folder and set it up following the same setup steps and use the same DB accounts as those in the last setup. Pervasync DB repository will be automatically upgraded if needed. You may remove the old installation folder after the new installation folder is setup.

NOTE: You need to apply a new license key if the new major (first) or minor (second) version number is different that of the old version numbers.

Upgrading from Version 3.0 or Newer

If you have 3.0.x or a newer version, for either sync client or sync server, you can upgrade it to the current version without resetting it so that the existing Pervasync admin DB repository will be re-used instead of being re-created. In addition, local DB application schema tables will not be dropped and re-created in an upgrade; while in a reset, they will.

To do the upgrade, first, un-pack the current release to a folder. Then, follow the same setup steps to set up the server/client in this new folder, and use the same DB accounts as those of previous setup. You may remove or keep the old installation folder after the new installation folder is setup. The setup process will automatically update the DB repository if needed.

Note for upgrading error: In general, upgrading from 3.0.x or a newer version does not require a reset. However, in some cases, you might get an exception that says upgrading from that version is not supported. If this happens to client upgrade, you will only need to reset the client. You do not need to reset and re-install the server.

Upgrade from a pre-3.0 version

If you have a pre-3.0 version installed, you need to reset it first. Then, install and setup the new version.

See section 4.5 and 4.6 that follows instructions of reset.

Importing and Exporting the Pervasync Metadata Repository

When you have to un-install an old version and then install a new version, you would have to re-create the publications and other sync objects as they are wiped out during un-installation.

To help the recreation of the sync objects, Pervasync provides a tool for you to export publications to an XML file, then import from the XML file to restore the publications on server. This is also useful when you need to move your app from a development/test environment to production environment.

Exporting to XML File

Look for link “Export to XML File” on the Home screen of the admin console. Click to launch the exporting screen and perform the export. Remember to do this before reset.

NOTE: For security reasons, all user passwords of clients are exported as “welcome1”. Reset the passwords before or after you import the XML.

Importing from XML File

NOTE: This feature is not available in trial version.

To import from the XML file, look for link “Publish from XML File” on the Home screen of the admin console. Click to launch the importing screen and perform the import. “Publish from XML File” will restore the publications that you have exported.

Since all user passwords of clients are exported as “welcome1”, you need to change them back to normal before you perform and client synchronization. To change password, you can either edit the XML file before the import or use the “Clients” tab of the admin console after the import. To edit XML file, open the file, find “welcome1”, then, replace them with your real passwords. Editing needs to be done before you import/publish from the XML File. If you want to use the admin console to change, you should do the following after you import/publish from the XML File: on the admin console, click “Clients”, click “Update” for each user, type your real password in the textbox labeled as “New User Password”, click checkbox labeled as “Change Password”, and lastly, click “Update”.

Uninstalling Pervasync Server

To uninstall the server, you first reset it so that Pervasync admin repository is removed from the database. After that you can remove the Pervasync home to complete the un-installation.

NOTE: A reset removes Pervasync objects from the database. In cases when you want to start over with your installation, you could do a reset followed by a setup.

NOTE: Un-publish all your publications before you reset the server so that the log tables and triggers created in the app schemas can be cleaned up.

Resetting the Server Using the Setup GUI

Just like setup, there are two ways to reset. This section describes the GUI way and the next section describes the non-GUI command-line way.

Go to the “bin” folder and invoke “pvs.bat” for Windows or “pvs.sh” for Linux. A GUI window will pop up:

On the Setup tab, fill in the required fields and click “Reset“. What this does is removing Pervasync server admin schema from the DB and un-deploying sync server web application.

NOTE: You can reset an older server from the GUI of a newer version. For example, you can reset pervasync_server-2.1.3 using “pvs.bat” (or “pvs.sh”) of pervasync_server-4.0.0.

A sync server could have multiple instances/installations on different machines. All these installations share one and only one sync server DB repository. The reset on any instance will drop the sync server DB repository. However, reset still needs to be done on all instances to un-deploy the Pervasync web app deployed on all the instances.

Resetting the Sync Server Using the Non-GUI Scripts

This section describes an alternative way to reset the server. Skip it if you have done it using the setup GUI described in last section.

Remove the Server Admin Schema

To drop the sync admin schema, the database system user name and password are needed.

Locate file pervasync_server_oradb.ini under directory bin. Use your favorite editor to supply values to the properties.

Then run the reset batch scripts, pervasync_server_oradb_reset.bat: on Windows or pervasync_server_oradb_reset.sh on Linux/Unix.

Windows:

cd bin

.\pervasync_server_oradb_reset.bat

Linux/Unix:

cd bin

./pervasync_server_oradb_reset.sh

A sync server could have multiple instances on different machines. All these instances share one and only one sync server DB user. The reset script only needs to be run on one of the host machines.

NOTE: To run the batch scripts successfully, pervasync_server_oradb.ini must have complete and correct information. After you run the scripts you may want to erase sensitive information such as passwords from the ini file for security reasons.

Un-Deploy Sync Server Web Application

NOTE: You may need to shutdown the servlet container that runs the Pervasync web app before the un-deployment.

This depends on how you deploy the sync web app and on which J2EE containers.

If you deploy the web app to Tomcat using the configuration file pervasync.xml, remove it to un-deploy.

Windows:

del C:\apache-tomcat-6.0.14\conf\Catalina\localhost\pervasync.xml

Linux/Unix:

rm /apache-tomcat-6.0.14/conf/Catalina/localhost/pervasync.xml

If you deploy the web app to Tomcat by copying the whole web folder to Tomcat webapps folder, remove the copied folder.

Windows:

rmdir /S /Q C:\apache-tomcat-6.0.14\webapps\pervasync

Linux/Unix:

rm –rf /apache-tomcat-6.0.14/webapps/pervasync/

Removing Pervasync Server Home

After you Reset Pervasync server, you may want to remove Pervasync server home, although you do not have to do so. To remove Pervasync server home, you can do something like the following:

Windows:

rmdir /S /Q C:\pervasync_server-4.0.0\

Linux/Unix:

rm –rf /pervasync_server-4.0.0/

Uninstalling Pervasync Client for Oracle and MySQL

To uninstall the client, you first reset it so that Pervasync admin repository is removed from the database. After that you can remove the Pervasync client home folder.

NOTE: A reset removes Pervasync objects from the database. In cases when you want to start over with your installation, you could do a reset followed by a setup.

Resetting the Client Using the Setup GUI

Just like setup, there are two ways to reset. This section describes the GUI way. Next section describes the non-GUI way.

Go to the “bin” folder and invoke “pvc.bat” for Windows or “pvc.sh” for Linux. A GUI window will pop up. Click on the “Setup” tab.

Fill in the required fields and click “Reset“. What this does is removing the client sync admin schema and your application schemas that have been created on your local DB.

NOTE: You can reset an older client from the GUI of a newer version. For example, you can reset pervasync_client-2.1.3 using “pvc.bat” (or “pvc.sh”) of pervasync_client-4.0.0.

Resetting the Sync Client Using the Non-GUI Scripts

This section describes an alternative way to reset the client. Skip it if you have done it using the setup GUI described in last section.

Locate file pervasync_client_oradb.ini under directory bin. Use your favorite editor to edit its property values. The database system user name and password are needed.

Then run the reset batch scripts, pervasync_client_oradb_reset.bat: on Windows or pervasync_client_oradb_reset.sh on Linux/Unix.

Windows:

cd install

.\pervasync_client_oradb_reset.bat

Linux/Unix:

cd install

./pervasync_client_oradb_reset.sh

Removing Pervasync Client Home

After you reset Pervasync client, you may want to remove Pervasync client home, although you do not have to do so. To remove Pervasync client home, you can do something like the following:

Windows:

rmdir /S /Q C:\pervasync_client-4.0.0\

Linux/Unix:

rm –rf /pervasync_client-4.0.0/

Uninstalling Pervasync Client for SQLite on Android and Blackberry

To uninstall the client, you first reset it so that Pervasync is removed from the database. After that you can do an OS level un-installation to remove the Pervasync binaries.

NOTE: A reset removes Pervasync objects from the database. In cases when you want to start over with your installation, you could do a reset followed by a setup so that you don’t have to do an OS level reinstall.

To reset, navigate to the setup screen and click on the “reset” button or menu item.

Using Pervasync

Pervasync enables you to synchronize distributed local databases with a central database without writing a single line of code. You start with a central DB with existing schemas and schema objects. The local DB should be initially empty. You use the web based admin console to publish central database objects and subscribe clients to the publications. Then on the client machine you use a shell script (pvc.sh or pvc.bat) to invoke the sync agent to initiate a sync session. The very first sync session will create the subscribed sync schemas and schema objects in local DB. The second sync will copy the central DB schema data to client schemas. After that, synchronization will be incremental – only changes on client and server will be exchanged.

Alternatively you can use the server and client side Java API to do publication, subscription and synchronization from your Java application.

Creating Publications and Subscriptions Using the Web Admin Console

You use the web based sync server admin console to publish sync objects and create subscriptions. Besides publications and subscriptions, the admin console is also used to monitor and control the sync servlet and sync engine, manage devices, users and groups.

Logging in to the Admin Console

Once the Pervasync web application is deployed in the J2EE servlet container, the admin console will be available at <a href=”http://:/pervasync”&gt;http://<server&gt;:<port>/pervasync, e.g. http://localhost:8080/pervasync. The following is the login page.

Use the admin user name and password you specified when you setup the Pervasync server to login.

Admin Console Home

Once logged in, you will see the admin console home page.

The admin console allows you to check the status of the sync servlet and the sync engine as well as startup/shutdown them.

The Sync Engine helps preparing server side logical transactions to be used to refresh client side databases and folders to new states. Sync Servlet serves sync clients receiving transactions (Check-Ins) from clients and sending transactions to clients. You need to have both Sync Servlet and Sync Engine up for synchronization to work. However, when you are creating publications and subscriptions, it is better to temporally shutdown both.

The Publish and Subscribe Model

Pervasync employs a publish and subscribe model. You first define sync schemas and sync folders as publications. Then you subscribe sync clients or groups to a set of publications. Publications may have parameters defined. At subscription time, you supply values to the parameters to customize the subscription. For example, you may define “region” as a parameter of your publications. Then at subscription time, you assign a value to region for subscribers so that they get data related to a specific region.

Click on the Publications tab to show all available publications. There are two types of publications: sync schemas and sync folders. A sync schema is a container for database tables and other objects while a sync folder is a container for files.

Publishing Sync Schemas

A sync schema corresponds to a physical DB schema. You may create multiple sync schemas for a single DB schema. However, one table can only belong to one sync schema.

On the Publications page click on “Add Sync Schema” button under Sync Schemas header to add a schema.

Move mouse cursor over the fields for tooltips.

The No-Init-Sync Networks and No-Sync Networks fields are for the “sync based on network characteristics” feature. This enables you to automatically skip (initial or all) synchronization of a publication if certain network card is used (for reasons such as being too expensive or too slow etc.). If you do not need to use this feature, just keep the default value, which is “NONE”, unchanged.

To use “sync based on network characteristics” feature, first, you need to gather information about network characteristics of your client machines. After you setup Pervasync client, you can use the utility listnetworks.bat (or listnetworks.sh for Linux) in the bin folder of Pervasync client home to list the display names, hardware addresses and IP addresses of your network cards on client machines. Then, on Pervasync admin console, you need to change the value of the No-Init-Sync Networks and No-Sync Networks fields from the default value “NONE” into a value which contain matching strings that identify the network cards. You may use full strings or sub-strings of the following network card attributes: display name, hardware address and IP address. You may also have “&&” and/or “||” between the strings to indicate whether it’s “match all” or “match any” respectively. Here when you add new sync schema, you specify global values of the no-sync lists that apply to all user devices. At subscription time you have a chance to overwrite the values for a specific user device or group. For more information and explanation regarding “sync based on network characteristics” feature, refer to chapter 5, section 13, i.e., 5.13, “Sync Based on Network Characteristics”.

After the schema is created, you go back to the Publications page and click on the “Tables” link under “Schema Objects” to find the published tables of the schema.

Initially there is no sync table. Click on “Add Sync Table” to select a table and specify columns and rows to sync.

In the above screen shot, we selected table “employees” and chose to sync all columns and rows to clients that subscribe to the schema “schema_1”.

Note: Table names and column names cannot contain spaces. For example, table name “xxxx table” is not supported. We suggest you replace spaces with underscores. For example, use “xxxx_table” instead of “xxxx table”.

Rank is used to indicate the referential relationship among the sync tables. Parent tables should have a smaller rank number than child tables. When you insert new records, you would insert first to a parent table and then to a child table as the child table records reference columns in the parent table.

If “Allow Refresh” is checked, Pervasync server will refresh changes to the table to Pervasync clients. If “Allow CheckIn” is checked, sync clients can check in client changes to this table on server. “CheckIn SuperUsers” takes a comma-separated list of users who are allowed to check in even when “Allow CheckIn” is not checked.

Subsetting mode and query are used to identify the rows of the table to be synced to clients. Use subsetting query to specify the primary key query as row filters. The query should return the set of the primary key values of the selected rows. The query can have parameters enclosed in “${}”. The parameter values are set at subscription time.

Subsetting mode SIMPLE is different from COMPLEX in that a SIMPLE query can select from only one table, which could be the sync table itself or a different table.

“Query Referenced Tables” takes a comma-separated list of tables that are referenced in the subsetting query. This is only for subsetting mode COMPLEX.

Note: Subsetting is an advanced and complicated topic. If you are confused, you can leave all fields with default values. To further understand data subsetting, see section 5.3.1 A Step by Step Example of Data Subsetting and section 5.3.2 Subsetting Modes: SIMPLE and COMPLEX.

Publishing Sync Folders

If you need to sync files in a folder, you can achieve it by publishing sync folders, which is what this section is about. If you do not need to do this, you can skip this section.

On the Publications page click on “Add Sync Folder” button under “Sync Folders” header to add a folder for synchronization.

Move mouse cursor over the fields for tooltips.

Sync Folder Name: A unique name for the sync folder, e.g., “folder_1”.

Server Folder Path: The server side folder path, e.g., “c:\ server_folder”. If not starting with “/” or drive letter and “:”, it will be treated as relative to Pervasync server root folder.

Client Folder Path: The sync client side folder path, e.g., “c:\ client_folder”. It can have parameters to bind to values at subscription time. Parameters are enclosed by “${}”. If not starting with “/” or drive letter and “:”, it will be treated as relative to Pervasync client root folder. You can also have env variables in client folder path. These env variables should be enclosed in a pair of % signs. They will be substituted with env variable values on client side.

NOTE: Use % pairs instead of ${} for env variables regardless of whether your client machine is Windows or Linux.

Prefix Filters: Filters on file relative paths. All files in the server root folder have a relative path. For example, “c:\ server_folder\user_1\ images\file1.gif” has a relative path of “user_1\images\file1.gif”. The prefix filters apply to the file relative path. For example, if the value of prefix filter is set to “user_1\images\”, any file whose relative path starts with “user_1\images\” will be synced. Multiple filters can be supplied using comma as separator. A file would pass this “starts with” test if it passes any one of the prefix filters. However, a file has to pass both the “starts with” (Prefix Filter) and the “ends with” (Suffix Filter – see below) tests to be selected. It can have parameters to bind to values at subscription time. Parameters are enclosed by “${}”. If you do not need to use this, simply leave the field empty.

Suffix Filters: File filter by filename suffix or extension, which corresponds to what filename ends with. These are comma (“,”) separated file filters by file name suffix. For example: if “.gif,.jpeg,.png,.PNG,.doc” is set, any file whose name ends with “.gif” , “.jpeg”, “.png”, “.PNG”, or “.doc” will be synced. If you do not need to use this, simply leave the field empty.

Check In Super Users: Comma (“,”) separated list of users who are allowed to check in even when “Allow Check In” is set to false. If you do not need to use this, simply leave the field empty.

No-Init-Sync Networks: Refer to last section (Publishing Sync Schemas) for explanations.

No-Sync Networks: Refer to last section (Publishing Sync Schemas) for explanations.

You can publish a server folder by specifying its path. The folder will be synced to client machine. The client folder name does not have to be the same as the server’s. You can even put parameters in the client folder path so that different clients could have a different folder. Parameters should be enclosed in ${}, just as in table subsetting query. These parameters should be assigned a value at subscription time.

Once a folder is published, you can associate it with one or more clients by creating a folder subscription.

Managing Groups

Publications can be subscribed in two ways. One is direct subscription by sync clients on user devices (See section 5.1.7 and 5.1.8); the other is indirect subscription through groups. In this section, we are going to discuss the latter, i.e., managing groups.

Very often you have a group of clients that share the same set of publications and even subscription parameters. In this case it is more convenient to create groups and group subscriptions first and then add/remove clients to/from the groups. Clients will inherit group subscriptions.

You can choose either indirect or direct subscription based on your situation. If you do not need to manage groups, you can skip this section and go ahead to read section 5.1.7 and 5.1.8 for direct subscription by sync clients.

Click on Groups tab to view existing groups.

When you delete a group, you have an option to keep the group members (sync clients). The clients’ group will be set to NULL if you choose “Delete Group Only”.

Initially you may not have any groups. Click on “Add Client Group” to add one.

Go back to Groups page, find the group and click on button “Subscriptions”.

The Group Subscriptions page lists all the available publications, subscribed or not. Subscribed publications (including sync schemas and sync folders) have their name bold-typed and you can select and un-subscribe them. For publications that are currently not subscribed, you can click on the “Add” button to add them to the group’s subscriptions.

Before you click the Add button, fill in the values for the parameters to customize the subscription. These values will be shared by all clients that will later be added to this group.

Refer to section 5.1.4 Publishing Sync Schemas for explanations of fields “No-Init-Sync Networks” and No-Sync Networks”. Note that you can set the no-sync list values at three levels: publication, group subscription and client subscription. Publication level values are inherited and can be overridden by group subscription values, which in turn are inherited and can be overridden by client subscription values.

Similarly, you can add sync folder publications to a group subscription.

If you un-subscribe a group from a publication, all clients that belong to the group would lose the subscription.

In the following we will create a user-device (client) and do the subscription.

Managing Sync Clients

Click on the “Clients” tab.

Initially you may not have any sync clients. Click on “Add New Sync Client”.

Here we create user “john.doe” and add device “DEFAULT”. We put in a password and check “Create User” to create the user in Pervasync repository. Alternatively, you could choose to manage users and their passwords yourself. See javadoc for interface pervasync.server.UserManager for details. In that case you need and only need to specify User Name and Device name to add the sync client.

When you create a client you can assign it to a group.

Creating Subscriptions

Click on “Subscriptions” button on the row of the sync client on the Clients page.

If you have assigned a group for the client, you would see the client has all the group subscriptions. Click on Update (or Add if not subscribed) button on the row of the publication.

A subscription is an association of a client with a publication, in our case, user john.doe’s DEFAULT device and schema “schema1”. Edit the parameter values if needed and click button “Update” to update the client subscription.

Once you have successfully created publications, user-devices and subscriptions using the web admin console, you are ready to execute synchronizations using a sync client as described in 5.2.

Checking Client Sync History

On the admin console Home click on link “Sync Session History” under section “Sync Servlet”. This page is useful for admin to identify client sync issues in the field and track user synchronization trends.

The “Clients” page also shows each client’s last sync status and provides a link to sync history for a specific client.

Doing Synchronization Using Pervasync Client for Oracle and MySQL

Continue our case in section 5.1. Setup a sync client using user john.doe and device DEFAULT, if you haven’t set it up yet. Follow the directions in section 4.2, Configuring Pervasync Client for Oracle or MySQL.

Pervasync client can be launched in either GUI mode (see section 5.2.1) or command-line mode (see section 5.2.2).

It will take two sync sessions to bring a newly created subscription to client. In the first sync, the schemas and tables will be created on device database. In the second sync, the table data will be synced to device. After the two initial syncs, data exchange will be incremental.

Run Pervasync Client in GUI Mode

Locate pvc.bat (for Windows) or pvc.sh (for Linux) in the “bin” folder of Pervasync client home. Invoke it with the “-h” option to show its usage:

C:\pervasync\bin>pvc -h

Usage

—–

pvc.bat [{sync|refresh|checkin|auto_sync|sys_tray} [<password>]]

Use this command to launch the Pervasync Client Configuration and Sync Utility.

If no arguments are specified, i.e.

pvc.bat

the sync client GUI will be launched.

If “sys_tray” is specified as an argument, i.e.

pvc.bat sys_tray

the sync client GUI will be launched in system tray (minimized).

Otherwise, it will run in non-GUI mode.

Use the “sync” sub-command to do two-way sync, e.g.

pvc.bat sync

Use “refresh” and “checkin” to do one-way sync.

You use “auto_sync” option to start the job scheduler for running sync jobs

at pre-arragned times. Use the “Schedule” tab of the GUI to update job schedules.

You can optionally supply the sync user password as a second argument.

Stored password will be used if it is absent.

Note that the JDBC jar file (for Oracle) or Java connector jar file

(for MySQL) has to be available in folder ../lib to run this script.

If invoked without any arguments, the client will run in GUI mode. In GUI mode, users can start or schedule sync sessions by pointing and clicking.

NOTE: You close the GUI window to terminate the client. When you do that, you will be given an option to run the client in system tray. There is also a command-line option “sys_tray” to “pvc.bat” that you can use to launch the client to run directly in system tray. You may want to create a Windows task to do this upon system re-boot. This is useful to run scheduled sync tasks in background.

The Sync Tab of the Pervasync Client GUI

Go to the “bin” folder and invoke “pvc.bat” for Windows or “pvc.sh” for Linux. A GUI window will pop up. There are five tabs, Setup, Sync, Schedule, Export and Import. Setup tab is used for setup and reset. Click on Sync tab you will see the following.

Listed below are the GUI settings that need some explanations:

Sync Direction: By default, it’s TWO_WAY sync. You can use “REFRESH_ONLY” and “CHECK_IN_ONLY” to do one-way sync.

Sync User Password: You can optionally supply the sync user password. Stored password will be used if it is absent. You can have the password saved using the setup tab, or use the sync tab and click on the “Save Password” checkbox.

Single Session: When there are sync definition changes in a sync session, Pervasync will only download the definition changes and leave data changes to next sync session. By default, Pervasync will do two sync sessions with one “Sync” button click when there are sync definition changes to make sure both sync definition and data changes are synced. Check this checkbox to perform only one sync session with one click.

NOTE: If sync client resides inside a firewall and sync server resides outside the firewall, you need to set the HTTP proxy host and port using the setup tab with “Advanced Mode” turned on.

You can change the default settings if you need to. Then, click “Sync” to invoke the Sync Agent to do synchronization.

Once the sync session is completed you will see an alert box telling you whether the session succeeded or failed. The “Sync History” panel on the “Sync” tab will also show you a summary of completed sessions.

If the sync fails, first check the error stack trace on the “Sync History” panel. For more detailed debugging info, check the log files under <Pervasync Client Home>/log. If you cannot resolve the issue yourself, send the log file to Pervasync support at support@pervasync.com.

The Schedule Tab of the Pervasync Client GUI

The Schedule tab enables you to schedule up to three sync jobs that do background synchronization at a pre-determined time. In addition to the start time, you also can specify the sync direction and optionally, the sync interval.

Before you schedule background sync jobs, make sure you have the sync user password saved using the “Setup” or the “Sync” tabs.

Click “Schedule” button to start a job and click on “Cancel” button to stop a job. A scheduled sync will be skipped if there is already a sync going on at the scheduled sync start time. Sync history is available on the sync tab.

NOTE: All sync jobs will be cancelled if you exit the sync client.

Run Pervasync Client with Command-line Interface

Alternatively, users could also run Pervasync client in command-line mode instead of GUI mode. Locate pvc.bat (for Windows) or pvc.sh (for Linux) in the “bin” folder of Pervasync client home. If invoked with arguments, the client will run in command-line mode. Users can supply sub commands for the client to perform synchronization on command-line.

C:\pervasync\bin>pvc -h

Usage

—–

pvc.bat [{sync|refresh|checkin|auto_sync|sys_tray} [<password>]]

Use this command to launch the Pervasync Client Configuration and Sync Utility.

If no arguments are specified, i.e.

pvc.bat

the sync client GUI will be launched.

If “sys_tray” is specified as an argument, i.e.

pvc.bat sys_tray

the sync client GUI will be launched in system tray (minimized).

Otherwise, it will run in non-GUI mode.

Use the “sync” sub-command to do two-way sync, e.g.

pvc.bat sync

Use “refresh” and “checkin” to do one-way sync.

You use “auto_sync” option to start the job scheduler for running sync jobs

at pre-arragned times. Use the “Schedule” tab of the GUI to update job schedules.

You can optionally supply the sync user password as a second argument.

Stored password will be used if it is absent.

Note that the JDBC jar file (for Oracle) or Java connector jar file

(for MySQL) has to be available in folder ../lib to run this script.

Doing Synchronization Using Pervasync Client for Android and Blackberry

In this section we will first show the Pervasync client working in standalone mode and then describe how to embed the client in to your Android or Blackberry app.

Starting Sync Sessions and Viewing Sync History

Once the sync client is installed and setup (see section 4.3 for details), you can click on the “Start Sync” button on the Sync screen of the Pervasync client.

During synchronization, the Sync screen will show you the progress. Once completed, the sync history is available on the History screen. For Android, you switch between screens by clicking on the tabs while for Blackberry, you click on the menu items.

NOTE: If you see non-empty “Sent(bytes)” but empty “Received” and sync seems to get stuck, most likely you have a typo in sync URL or the sync server is not up. Use the “Setup” tab to correct the URL. Make sure you have the right IP and port number.

Below are Sync and History screen shots for Android and Blackberry respectively.

Updating Setup Info and Configuring Auto Sync

NOTE: In the following we will only show screen shots for Android. Blackberry screens are similar. The main difference is that instead of tabs, you usually use menu items to navigate to different screens on Blackberry.

The Setup screen allows you to update the setup info. It also allows you to reset the client to pre-setup state.

The Scheduler screen allows you to schedule sync jobs so that you don’t have to manually initiate each sync session. Normally you would turn on the “Repeat” flag to make the sync happen periodically with a preset sync interval. Note that you need to have both the Job Scheduler running and the job enabled for the auto sync to be on.

Database and File Browser

The Browser screen allows you to inspect the databases and files that are synced to the device. You may want to hide this functionality from end users after you compose your own user interface to the synced data. However, it’s a great tool to use during development of your application that uses Pervasync for data synchronization.

The Browser screen lists all the schemas/databases and folders. Clicking on a schema will bring up a dialogue with options to show tables or sequences that belong to the schema.

Following are the table list and sequence list.

Click on table to show table definition and table data. The table data screen will by default show 10 rows. You can change the Fetch Limit to show more or less than that. You can also put a predicate in the Where Clause box to show the desired rows, for example:

NAME like ‘%Jonh%’

Click on a row to bring up the row editor screen where you can delete or update the row data. You could also insert a new row using this screen.

Back to the main Browser screen, you can click on a sync folder to show the sub folders and files.

Syncing Data and Files to External Storage

If you run the sync client in standalone mode (as opposed to embedding it into your device app), your app usually won’t be able to access the synced data unless the data is on external storage. To sync data and files to external storage, prefix the client schema name and client folder name with “EXTERNAL;”. Note the trailing semi colon.

Embedding Pervasync Client into Your Android Application on Device

If you run the sync client in standalone mode, your app usually won’t be able to access the synced data unless the data is on external storage. Instead, if you embed the sync client into your own application, both the sync client code and your app code share the same permissions to the data and you would be able to store the data on internal and external storages.

The “android/lib” folder of your Pervasync server home has a Pervasync client library jar that you can easily include into your app.

Let’s assume you created an Android project “my_android_app” in your Eclipse workspace. Your package name is “com.mycorp.myapp” and your main Activity is “MyMainActivity”. The AndroidManifest.xml of your project would look like the following:

<?xml
version=“1.0”
encoding=“utf-8”?>

<manifest package=“com.mycorp.myapp” android:versionCode=“1”

    android:versionName=“1.0” xmlns:android=http://schemas.android.com/apk/res/android&#8221;>

    <application
android:icon=“@drawable/icon”

        android:label=“My Android App”>

        <activity
android:name=“.MyMainActivity”
android:label=“@string/app_name”>

            <intent-filter>

                <action
android:name=“android.intent.action.MAIN” />

                <category
android:name=“android.intent.category.LAUNCHER” />

            </intent-filter>

</application>

    <uses-sdk
android:minSdkVersion=“7”
android:targetSdkVersion=“7” />


</manifest>

Following are the steps to embed Pervasync client.

Add Pervasync Android client library to Java Build Path

Right click on your Eclipse project for your Android app and open the “Properties” window. Click on “Java Build Path” on the left panel and then click the “Libraries” tab on the right panel. Click on “Add External JARs…” button to add Pervasync Android client library “pervasync_android_lib-4.0.0.jar” which is located in the “android/lib” folder of Pervasync server home.

NOTE: If your Pervasync server and your Eclipse IDE are on different hosts, just copy the jar to your Eclipse host.

After adding the jar, click the “Order and Export” tab. Select the check box in front of “pervasync_android_lib-4.0.0.jar”.

Add Pervasync Activities and Permissions to AndroidManifest.xml

Copy-paste the “activity” and “uses-permission” elements from the following sample AndroidManifest.xml (see also android/AndroidManifest_sample.xml) to your AndroidManifest.xml.

NOTE: Don’t copy the grayed-out parts. Your AndroidManifest.xml should already have them.

<?xml version=“1.0” encoding=“utf-8”?>

<manifest package=“com.mycorp.myapp” android:versionCode=“1”

    android:versionName=”1.0″ xmlns:android=”http://schemas.android.com/apk/res/android”&gt;

    <application android:icon=“@drawable/icon”

        android:label=“My Android App”>

        <activity android:name=“.MyMainActivity” android:label=“@string/app_name”>

            <intent-filter>

                <action android:name=“android.intent.action.MAIN” />

                <category android:name=”android.intent.category.LAUNCHER” />

            </intent-filter>

        </activity>

        <activity android:name=“pervasync.ui.android.PervasyncClientActivity”

            android:label=“Pervasync”>

        </activity>


        <service
android:name=“pervasync.ui.android.PervasyncService”>

            <intent-filter>

                <action
android:name=“pervasync.ui.android.PervasyncService” />

            </intent-filter>

        </service>

        <activity android:name=“pervasync.ui.android.PervasyncSyncActivity”

            android:label=“Pervasync Sync”>

        </activity>

        <activity android:name=“pervasync.ui.android.AutoSyncSchedulerActivity”

            android:label=“Pervasync Scheduler”>

        </activity>

        <activity android:name=“pervasync.ui.android.PervasyncHistoryActivity”

            android:label=“Pervasync Sync History”>

        </activity>

        <activity android:name=“pervasync.ui.android.PervasyncSetupActivity”

            android:label=“Pervasync Setup”>

        </activity>

        <activity android:name=“pervasync.ui.android.PervasyncBrowserActivity”

            android:label=“Pervasync Browser”>

        </activity>

        <activity android:name=“pervasync.ui.android.SchemaTablesActivity”

            android:label=“Schema Tables”>

        </activity>


        <activity android:name=“pervasync.ui.android.FolderFilesActivity”

            android:label=“File Browser”>

        </activity>

        <activity android:name=“pervasync.ui.android.SchemaSequencesActivity”

            android:label=“Schema Sequences”>

        </activity>

        <activity android:name=“pervasync.ui.android.TableRowsActivity”

            android:label=“Table Data”>

        </activity>

        <activity android:name=“pervasync.ui.android.TableColumnsActivity”

            android:label=“Table Column Types”>

        </activity>

        <activity android:name=“pervasync.ui.android.RowEditActivity”

            android:label=“Row Edit”>

        </activity>

        <activity android:name=“pervasync.ui.android.ImageFileActivity”

            android:label=“Image File”>

        </activity>

        <activity android:name=“pervasync.ui.android.TextFileActivity”

            android:label=“TextFile”>

        </activity>

        <receiver
android:enabled=“true”
android:name=“pervasync.ui.android.BootUpReceiver”

            android:permission=“android.permission.RECEIVE_BOOT_COMPLETED”>

            <intent-filter>

                <action
android:name=“android.intent.action.BOOT_COMPLETED” />

                <category
android:name=“android.intent.category.DEFAULT” />

            </intent-filter>

        </receiver>

    </application>

    <uses-sdk android:minSdkVersion=“7” android:targetSdkVersion=“7” />

<uses-permission
android:name=“android.permission.RECEIVE_BOOT_COMPLETED” />

    <uses-permission
android:name=“android.permission.ACCESS_NETWORK_STATE”></uses-permission>

    <uses-permission
android:name=“android.permission.ACCESS_WIFI_STATE”></uses-permission>

    <uses-permission
android:name=“android.permission.INTERNET”></uses-permission>

    <uses-permission
android:name=“android.permission.WRITE_EXTERNAL_STORAGE”></uses-permission>

</manifest>

Invoke Pervasync Client from Your App Code

With the inclusion of the library and the additions to the manifest file, Pervasync is now part of your application. You can now invoke Perasync. In the main activity of your app, you should start the Pervasync service so that it can perform auto-sync in the background:

if (!PervasyncService.isStarted()) {

    startService(new Intent(this, PervasyncService.class));

}

To show the Pervasync UI, start the Pervasync client activity:

Intent intent = new Intent(myMainActivity,

PervasyncClientActivity.class);

startActivity(intent);

Following is a sample main activity:

package com.mycorp.myapp;

import pervasync.ui.android.PervasyncService;

import pervasync.ui.android.PervasyncClientActivity;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.Gravity;

import android.view.Menu;

import android.view.MenuItem;

import android.view.View;

import android.view.View.OnClickListener;

import android.widget.Button;

import android.widget.LinearLayout;

import android.widget.TextView;

import android.widget.LinearLayout.LayoutParams;

/*

* This is the main screen of your app

*/

public
class MyMainActivity extends Activity {

    private
static
final
int
SYNC_CLIENT_ID = 0;

    private
final MyMainActivity myMainActivity = this;

    /** Called when the activity is first created. */

    @Override

    public
void onCreate(Bundle savedInstanceState) {

        super.onCreate(savedInstanceState);

        // start Pervasync service if not already started.

        if (!PervasyncService.isStarted()) {

            startService(new Intent(this, PervasyncService.class));

        }

        // set title

        setTitle(“Pervasync Sample App”);

        // contentView is a LinearLayout

        LinearLayout contentView = new LinearLayout(this);

        contentView.setOrientation(LinearLayout.VERTICAL);

        this.setContentView(contentView);

        // empty line

        contentView.addView(new TextView(myMainActivity));

        // description

        String description = “This is a place holder main screen for your application. “

                + “You can replace the title, add UI components to the screen and add “

                + “child screens that can be linked from this main screen.\n\n”

                + “We added a button below that invokes the Pervasync Client main screen where users can setup “

                + “Pervasync and perform synchronization.\n\n”

                + “The Pervasync Client also includes a database browser for you to explore the “

                + “synced databases during developemnt time. You could hide the browser in the production “

                + “version of your app.”;

        TextView textView = new TextView(this);

        textView.setText(description);

        contentView.addView(textView);

        // empty line

        contentView.addView(new TextView(myMainActivity));

        // buttonBar

        LinearLayout buttonBar = new LinearLayout(myMainActivity);

        buttonBar.setGravity(Gravity.CENTER); // How button bar aligns its

                                                // children

        buttonBar.setOrientation(LinearLayout.HORIZONTAL);

        contentView.addView(buttonBar, new LinearLayout.LayoutParams(

                LayoutParams.FILL_PARENT, LayoutParams.WRAP_CONTENT));

        // Launch Pervasync Client button

        Button pervasyncButton = new Button(this);

        LinearLayout.LayoutParams params = new LinearLayout.LayoutParams(

                LayoutParams.WRAP_CONTENT, LayoutParams.WRAP_CONTENT);

        pervasyncButton.setLayoutParams(params);

        pervasyncButton.setText(“Launch Pervasync Client”);

        pervasyncButton.setTag(“Pervasync”);

        pervasyncButton.setOnClickListener(new OnClickListener() {

            public
void onClick(View view) {

                Button button = (Button) view;

                if (“Pervasync”.equalsIgnoreCase((String) button.getTag())) {

                    Intent intent = new Intent(myMainActivity,

                            PervasyncClientActivity.class);

                    startActivity(intent);

                }

            }

        });

        buttonBar.addView(pervasyncButton);

    }

    @Override

    public
boolean onCreateOptionsMenu(Menu menu) {

        menu.add(Menu.NONE, SYNC_CLIENT_ID, Menu.CATEGORY_CONTAINER,

                “Launch Pervasync Client”);

        return (super.onCreateOptionsMenu(menu));

    }

    @Override

    public
boolean onOptionsItemSelected(MenuItem item) {

        switch (item.getItemId()) {

        case
SYNC_CLIENT_ID:

            startActivity(new Intent(myMainActivity,

                    PervasyncClientActivity.class));

            return (true);

        default:

            ;

        }

        return
super.onOptionsItemSelected(item);

    }

}

You can now build and run your Android app.

Advanced Topics

OTA Deployment

Once you package and sign your app with Android signature tool, you will have an apk file generated. Copy the apk file, e.g. my_android_app.apk, to <Pervasync Server Home>/web/pervasync/download and your users can install the app over-the-air (OTA) by entering the following URL in their mobile browser:

http://<Pervasync_Host&gt;:<port>/pervasync/download/my_android_app.apk

Javadoc

You can find Javadoc of the Pervasync client interface in <Pervasync Server Home>/android/doc. Open the index.html in a browser.

Embedding Pervasync Client into Your Blackberry Application on Device

If you embed the sync client into your own application, both the sync client code and your app code share the same permissions to the data. This allows you to secure the data so that only your app (including sync code) can access the data.

The “blackberry/lib” folder of your Pervasync server home has a Pervasync client library jar that you can easily include into your app.

Let’s assume you created a Blackberry project “my_blackberry_app” in your Eclipse workspace. Your package name is “com.mycorp.myapp” and your UIApplication is “MyBlackberryApp”. You also have a MainScreen called “MyMainScreen”.

Following are the steps to embed Pervasync client.

Add Pervasync Blackberry client library to Java Build Path

Right click on your Eclipse project for your Blackberry app and open the “Properties” window. Click on “Java Build Path” on the left panel and then click the “Libraries” tab on the right panel. Click on “Add External JARs…” button to add Pervasync Blackberry client library “pervasync_blackberry_lib-4.0.0.jar” which is located in the “blackberry/lib” folder of Pervasync server home.

NOTE: If your Pervasync server and your Eclipse IDE are on different hosts, just copy the jar to your Eclipse host.

After adding the jar, click the “Order and Export” tab. Select the check box in front of “pervasync_blackberry_lib-4.0.0.jar”.

Enable Auto-Run

Open Blackberry_App_Descriptor.xml under your app project and select the checkbox “Auto-run on start”. This is to auto start your app after a device re-boot so that your app can do auto-sync in the background.

Invoke Pervasync Client from Your App Code

With the inclusion of the library, Pervasync is now part of your application. In the UIApplication “MyBlackberryApp” of your app, you should start the Pervasync JobScheduler so that it can perform auto-sync in the background:

if(!SyncClient.needSetup()){

SyncClient.restartJobScheduler();

}

To show the Pervasync UI, push PervasyncSyncScreen in your MyMainScreen class:

UiApplication.getUiApplication().pushScreen(

                    new PervasyncSyncScreen());

Following is a sample UIApplication “MyBlackberryApp”:

package com.mycorp.myapp;

import pervasync.client.SyncClient;

import net.rim.device.api.ui.UiApplication;

public
class MyBlackberryApp extends UiApplication {

    /**

     * This is a place holder for your Blackberry application.

     * You could replace the package name and class name.

     * @param args

     * @throws Exception

     */

    public
static
void main(String[] args) throws Exception {

        MyBlackberryApp myBlackberryApp = new MyBlackberryApp();

        myBlackberryApp.enterEventDispatcher();

    }

    /**

     * Constructor. Displays the main screen of your application

     * @throws Exception

     */

    public MyBlackberryApp() throws Exception {

        // start Pervasync job scheduler if you want to have auto sync

        if(!SyncClient.needSetup()){

            SyncClient.restartJobScheduler();

        }


        // show the app main screen

        pushScreen(new MyMainScreen());

    }

}

Following is a sample MainScreen “MyMainScreen”:

package com.mycorp.myapp;

import net.rim.device.api.ui.Font;

import net.rim.device.api.ui.MenuItem;

import net.rim.device.api.ui.UiApplication;

import net.rim.device.api.ui.component.Dialog;

import net.rim.device.api.ui.component.LabelField;

import net.rim.device.api.ui.component.RichTextField;

import net.rim.device.api.ui.component.SeparatorField;

import net.rim.device.api.ui.container.MainScreen;

import pervasync.client.SyncClient;

import pervasync.ui.blackberry.PervasyncSyncScreen;

public
class MyMainScreen extends MainScreen {

    /**

     * Constructor

     */

    public MyMainScreen() {

        // set page title

        LabelField title = new LabelField(“My Blackberry App”,

                LabelField.ELLIPSIS | LabelField.USE_ALL_WIDTH);

        setTitle(title);

        // set header

        addHeader(“My Blackberry App Main Screen”);

        // set description

        String description = “This is a place holder main screen for your application. “

            + “You can replace the title, add UI components to the screen and add “

            + “child screens that can be linked from this main screen.\n\n” +

            “We added a menu item that invokes the Pervasync Client main screen where users can setup “+

            “Pervasync and perform synchronization.\n\n” +

            “The Pervasync Client also includes a database browser for you to explore the “+

            “synced databases during developemnt time. You could hide the browser in the production “ +

            “version of your app”;

        RichTextField richTextField = new RichTextField(description,

                RichTextField.READONLY);

        add(richTextField);

    }

    /**

     * Helper to add a bold typed header

     * @param labelStr

     */

    private
void addHeader(String labelStr) {

        LabelField labelField = new LabelField(labelStr,

                LabelField.FIELD_HCENTER);

        labelField.setFont(labelField.getFont().derive(Font.BOLD));

        this.add(labelField);

        this.add(new SeparatorField());

    }

    /**

     * Put the app in the background when closing.

     */

    public
boolean onClose() {

        UiApplication.getUiApplication().requestBackground();

        return
true;

    }

    /**

     * Kill the app on exit

     */

    private
void exit() {

        if (SyncClient.isSynchronizing()) {

            int retVal = Dialog

                    .ask(Dialog.D_YES_NO,

                            “Exiting will kill the on-going sync session. Are you sure you want to exit?”);

            if (retVal == Dialog.YES) {

                System.exit(0);

            }

        } else {

            System.exit(0);

        }

    }

    /**

     * Pervasync Client Menu Item

     */

    private
final
class PervasyncMenuItem extends MenuItem {

        private PervasyncMenuItem() {

            super(“Pervasync Client”, 1, 1);

        }

        public
void run() {

            UiApplication.getUiApplication().pushScreen(

                    new PervasyncSyncScreen());

        }

    }

    /**

     * Exit Menu Item

     */

    private
final
class ExitMenuItem extends MenuItem {

        private ExitMenuItem() {

            super(“Exit”, Integer.MAX_VALUE, 1);

        }

        public
void run() {

            exit();

        }

    }

    /**

     * This is called when user invokes menu

     */

    public
boolean onMenu(int instance) {

        removeAllMenuItems();

        addMenuItems();

        return
super.onMenu(instance);

    }

    private
void addMenuItems() {

            PervasyncMenuItem pervasyncMenuItem = new PervasyncMenuItem();

            this.addMenuItem(pervasyncMenuItem);

        ExitMenuItem exitMenuItem = new ExitMenuItem();

        this.addMenuItem(exitMenuItem);

    }

}

You can now build and run your Blackberry app.

Advanced Topics

Encryption

To encrypt the database, prefix the client schema name with “ENCRYPT;” when you publish the sync schema. If you want it be both encrypted and stored on external storage, use prefix “EXTERNAL; ENCRYPT;”.

Note that other apps on the device can still access the databases when it’s encrypted. To further secure your DB so that only your app can access it, use prefix “PROTECT;”.

OTA Deployment

Once you package and sign your app with Blackberry signature tool, you will have a jad file and some cod files generated (e.g. in <your project root>/deliverables/Web/6.0.0). Copy these files to <Pervasync Server Home>/web/pervasync/download and your users can install the app over-the-air (OTA) by entering the following URL in their mobile browser:

http://<Pervasync_Host&gt;:<port>/pervasync/download/my_blackberry_app.jad

Javadoc

You can find Javadoc of the Pervasync client interface in <Pervasync Server Home>/android/doc. Open the index.html in a browser.

Data Subsetting Using a SQL Query with Parameters

A Step by Step Example of Data Subsetting

Let’s use the following table EMP of schema SCOTT as an example. Suppose you have your entire employee data stored in the EMP table of your central DB. The EMP table has the following definition. Each employee has an employ ID (column EMPNO) and belongs to a department (column DEPTNO):

CREATE TABLE “SCOTT”.”EMP”

(    “EMPNO” NUMBER(4,0),

    “ENAME” VARCHAR2(10),

    “JOB” VARCHAR2(9),

    “MGR” NUMBER(4,0),

    “HIREDATE” DATE,

    “SAL” NUMBER(7,2),

    “COMM” NUMBER(7,2),

    “DEPTNO” NUMBER(2,0),

     CONSTRAINT “PK_EMP” PRIMARY KEY (“EMPNO”) ENABLE,

     CONSTRAINT “FK_DEPTNO” FOREIGN KEY (“DEPTNO”)

     REFERENCES “SCOTT”.”DEPT” (“DEPTNO”) ENABLE

);

Your company has grown very big and become geographically distributed. Employees started to have problems accessing the EMP table because the central DB is under heavy load and the network is not always fast and stable. So you decide to create a local DB for each department. The EMP table in a local DB only contains data for employees belonging to that department. Here we are subsetting by DEPTNO. In real world applications, people may do subsetting by country, region, branch office name/ID, and clinic name/ID etc.

Following are the steps to take to use Pervasync to synchronize the EMP table in a local DB with the EMP table in the central DB.

  1. Prepare the central DB schema.

You may need to make some adjustments to the structure of your existing table to make it ready to be published as a sync table.

Pervasync requires that all sync tables have a primary key. The EMP table already has a primary key: column EMPNO. So, we are fine.

Assume we want to do the subsetting by DEPTNO. Then, we should have a table that defines the association between DEPTNO and EMPNO. One way to do this is to add a DEPTNO column to EMP table. EMP table already has a DEPTNO column, so we don’t need to do anything. Another way is to create a separate table that has two columns EMPNO and DEPTNO with EMPNO being the primary key column. You know what I mean if you learned about table normalization.

Now we have a decision to make: do we want to keep EMPNO as the sole primary key column or do we want to make DEPTNO and EMPNO together a composite primary key? Both have pros and cons. You make the decision based on your business requirements. If we keep EMPNO as the sole primary key column, we have to make sure that local DB do not assign conflicting EMPNO values when adding new employees locally. In Pervasync, you can create a sync sequence for the EMPNO column and draw globally unique values from the sync sequence locally (For more information and explanation of sync sequence, see section 5.5.2 Sync Sequence). If you make DEPTNO and EMPNO together a composite primary key, you only need to make sure EMPNO is locally unique when you add a new employee. If your company does not require every employee to have a company wide unique ID (value of EMPNO), you can choose the latter way.

In summary, to make a table ready to be published as a sync table, it has to have a primary key defined and an association between the primary key and the subsetting column defined.

  1. Publish the sync table and add it to the sync schema

When you define the sync table via the Pervasync web admin console at Sync Schemas-> Sync Tables-> Add new Sync Table, or via the admin Java API using method addSyncTable of class SyncServerAdmin, you have a chance to supply a data subsetting query. If you already have a sync table that you want to use for subsetting, you can click “Update”, and then edit “Subsetting Query”. Data subsetting query determines which rows go to which local database. There is no restriction on the query as long as it returns the Ids of the rows to be synced to a local DB. If we keep EMPNO as the sole primary key column we would use the following query:

SELECT EMPNO FROM SCOTT.EMP WHERE DEPTNO=${DEPTNO_PARAM}

If we make DEPTNO and EMPNO together a composite primary key we would use the following query:

SELECT DEPTNO, EMPNO FROM SCOTT.EMP WHERE DEPTNO=${DEPTNO_PARAM}

In the above queries, we have a placeholder, ${DEPTNO_PARAM}. The variables enclosed in ${} are subscription parameters. Queries for other tables could share the same parameter names. The collection of unique table level parameters becomes parameters for the published sync schema.

The queries in our example are among the simplest as they have only one parameter and reference only one table. One could have multiple parameters and use table joins in the query. Pervasync employs different algorithms for simple and complex queries to achieve maximum efficiency for both cases. You need to specify a subsetting mode when doing the subsetting: SIMPLE or COMPLEX. Subsetting mode SIMPLE is different from COMPLEX in that a SIMPLE query can select from only one table, which could be the sync table itself or a different table. For more information and explanation of SIMPLE or COMPLEX subsetting modes, see section 5.4.2: “Subsetting Modes: SIMPLE and COMPLEX”.

  1. Subscribe a sync user to a sync schema

For each local DB, you create a sync client – a combination of user and device. When you subscribe a sync client to a sync schema via the Pervasync web admin console at Sync Schemas-> Schema Subscriptions-> Add new Schema Subscription, or via the admin Java API method addSchemaSubscription of class SyncServerAdmin, you have a chance to supply a value for each of the subscription parameters. If you already have a Schema Subscription that you want to use for subsetting, you can click “Update Subscription”, and then enter the value of the parameter, which you specify as a placeholder in subsetting query at step 2. In our case, we will see DEPTNO_PARAM listed as a parameter name and we can assign a value. For example, if we want a sync client to sync data for DEPTNO=1, we put 1 as the value for DEPTNO_PARAM.

  1. Sync

During synchronization for the sync client we just subscribed, effectively the following query is executed:

SELECT EMPNO FROM SCOTT.EMP WHERE DEPTNO=1

So that only the rows with DEPTNO=1 are synced to this client.

Subsetting Modes: SIMPLE and COMPLEX

The parameterized SQL query is at the heart of data subsetting. There is virtually no restriction to the query as long as it returns the primary key values of the desired data sub-sets. This gives users great flexibilities but at the same time, it poses great challenges to the sync engine, which has to figure out physical and logical changes to the data sub-sets. Algorithms that apply to generic queries may not be scalable. On the other hand, algorithms that perform well for simple queries may not apply for complex queries.

Pervasync asks users to indicate the complexities of their queries via subsetting modes so that different algorithms can be applied to achieve best possible performance and scalability.

Criteria for SIMPLE query

There are two subsetting modes, SIMPLE and COMPLEX. Criteria for SIMPLE query are as follows.

  1. The query can reference only one table. This table can be the table you are subsetting or a different table.

    The query predicates that involve the table columns can use not only “=”, but also other conditional operators like “>”, “<“, and “like” etc. For example, the following queries qualify as SIMPLE queries,

SELECT EMPNO FROM SCOTT.EMP WHERE DEPTNO=${DEPTNO_PARAM} AND MGR = ${MGR_PARAM}

SELECT EMPNO FROM SCOTT.EMP WHERE DEPTNO=${DEPTNO_PARAM} AND SAL < ${SAL_PARAM}

SELECT EMPNO FROM SCOTT.EMP WHERE SAL > ${SAL_MIN} AND SAL < ${SAL_MAX}

SELECT EMPNO FROM SCOTT.EMP WHERE JOB like ‘%Engineer’

  1. The query cannot use functions, expressions, joins, set operations like “union”, “intersect” etc.
  2. Query referenced columns (e.g. DEPTNO, MGR, SAL and JOB in the above examples) cannot have NULL values and the column data type cannot be too long. The reason is that these columns will become part of a primary key for a Pervasync internal table so they have to satisfy primary key column criteria of the database. If you cannot make these columns “NOT NULL” and have shorter length, mark the subsetting query COMPLEX.

Scalability implications

In general, SIMPLE queries are much more scalable than COMPLEX queries in terms of database space and sync engine processing time. For SIMPLE queries, sync engine performance is virtually independent of the number of sync clients you have.

COMPLEX queries can be scalable if a large number of clients share a same set of parameter values. For example, suppose you have 1000 clients and there are only 10 unique values for the subscription parameter DEPTNO_PARAM, the resources needed by sync engine are about the same as 10 clients each having a unique value for the parameter. Still, if at all possible, we recommend you use SIMPLE queries as opposed to COMPLEX queries.

Converting a COMPLEX Query to a SIMPLE Query

If you mark a SIMPLE query as COMPLEX, sync will still work but just not as efficiently. However, if you simply mark a COMPLEX query as SIMPLE, the sync may not work – you may find that client is not getting the changes you expect.

Fortunately, there are ways to convert a COMPLEX query to a SIMPLE query. For example, let’s say you want to sync users’ emails to their mobile devices and you use email recipient as subsetting parameter. The query qualifies as a SIMPLE query:

SELECT EMAIL_ID FROM USER_EMAILS WHERE RECEIPIENT = ${EMAIL_ADDRESS_1} OR RECEIPIENT = ${EMAIL_ADDRESS_2}

Now, let’s say you only want to sync last 30 days’ emails to save space on device. The query becomes COMPLEX:

SELECT EMAIL_ID FROM USER_EMAILS WHERE (RECEIPIENT = ${EMAIL_ADDRESS_1} OR RECEIPIENT = ${EMAIL_ADDRESS_2}) AND RECEIVE_DATE > (SYSDATE – INTERVAL ’30’ DAY(5))

To convert this query to a SIMPLE query, you can add a column INCLUDE_IN_SYNC, which takes values of “Y” for yes and “N” for no and defaults to “N”. You create a DB job to update the INCLUDE_IN_SYNC values every night:

UPDATE USER_EMAILS SET INCLUDE_IN_SYNC=”Y” WHERE INCLUDE_IN_SYNC=”N” AND RECEIVE_DATE > (SYSDATE – INTERVAL ’30’ DAY(5))

With this in place, you would be able to use the following SIMPLE query to achieve the same result as the original COMPLEX query.

SELECT EMAIL_ID FROM USER_EMAILS WHERE (RECEIPIENT = ${EMAIL_ADDRESS_1} OR RECEIPIENT = ${EMAIL_ADDRESS_2}) AND INCLUDE_IN_SYNC=”Y”

Schema Evolution and Sync Table Reload

Sometimes it is inevitable that new business requirements come up after the system has gone production and you have to change table structure or even drop/add table from/to schemas. Pervasync supports schema evolution so that you don’t have to re-build the whole system from scratch.

Schema Evolution Steps

  1. Shutdown sync server

Before you make any changes to the system, it is recommended to first shut down sync engine and sync servlet using the web admin console. It is also recommended to sync all the clients before the server shutdown so that all client side changes are uploaded to server.

  1. Alter DB table

Then, you make physical changes to the central database schemas/tables, such as create/drop tables, alter tables to add/remove columns or change column data types.

  1. Update sync table

Go to the web admin console and locate the table you just changed. Delete the sync table if you dropped the corresponding DB table. Update the sync table if you altered the corresponding DB table.

  1. Start sync server

Re-start sync engine and re-open sync servlet using the web admin console.

Propagation of Table Definition to Clients and Table Reload

The first sync after the server schema change will propagate the new schema definition to client. If a sync table was removed, the corresponding client DB table will be dropped. Also in general, if a sync table was updated, the corresponding client DB table will be altered.

Some updates to the sync table would cause the client DB table be dropped, re-created and re-populated with data from server. This is called a re-load.

If you change the subsetting query, clients may be assigned a different sub-set of data. Therefore, Pervasync will do a table re-load for all the clients. Altering a table’s primary key will cause the same.

If you change the values of subsetting parameters for a particular client, that client would get a reload for the affected tables. Other tables and clients won’t get a reload.

Generating Unique Key Values in Distributed Databases

A synchronization system contains distributed databases. How do you ensure that new records created on these databases have unique key values? In a single DB system, people usually use AUTO_INCREMENT columns (e.g. in MySQL) or sequence objects (e.g. in Oracle) that produce unique sequence numbers. In a multi-DB environment, special treatments have to be done to AUTO_INCREMENT columns and sequences otherwise different DBs might generate same primary key values that would conflict with each other when new records are synced from one DB to another.

Non-Conflicting AUTO_INCREMENT for MySQL Databases

NOTE: AUTO_INCREMENT does not apply to Oracle, which uses sequences. See next section for using sequences in a sync system.

AUTO_INCREMENT is the standard way for MySQL applications to generate unique key values. MySQL 5 introduces a couple of server variables, auto_increment_increment and auto_increment_offset, which make it possible to keep using AUTO_INCREMENT columns in a multi-DB system.

By default, auto_increment_increment and auto_increment_offset both have a value of 1. The inserted value is the least in the series [1, 2, 3…] that is greater than the maximum existing value of the column. When auto_increment_increment and auto_increment_offset take on non-default values, the series is no longer [1, 2, 3…], but calculated using the following formula:

auto_increment_offset + N × auto_increment_increment

where N is a non-negative integer value in the series [0, 1, 2, 3, …].

For example, with auto_increment_increment = 10 and auto_increment_offset = 5, the values are drawn from series [5, 15, 25…].

Following are the steps to take for using AUTO_INCREMENT in Pervasync.

  1. Choose an auto_increment_increment value. The value should be at least one greater than the number of local DBs you plan to deploy. For example, you may choose 1000 if your system is expected to have hundreds of local DBs.
  2. On central DB and all local DBs, edit your MySQL Server configuration file (usually called my.cnf or my.ini) to set auto_increment_increment and auto_increment_offset values. All MySQL servers should use a same auto_increment_increment value and a different auto_increment_offset value. For example, on central DB you may put

auto_increment_increment=1000

auto_increment_offset = 1

while on the first local DB you may put

auto_increment_increment=1000

auto_increment_offset = 2

On central Db, keep the AUTO_INCREMENT column definitions intact. The AUTO_INCREMENT flag of the columns will be synced to local DB.

That’s it! From now on, do inserts as you normally do on a single DB without worrying about unique key violations. Nevertheless, if for some reason you prefer not to use the AUTO_INCREMENT feature, you can use the Pervasync “Sync Sequence” feature described in next section.

Sync Sequence

The idea of the Pervasync “sync sequence” feature is to divide a sequence into contiguous partitions/windows and distribute the partitions to central DB and local DBs. Database applications can draw globally unique values for unique keys from the sequence partitions. When a local DB is about to run out of the numbers, a new range is synced to the local DB.

Following are the steps to use Sync Sequences.

  1. Determine central DB sequence partition range. Normally you create a sequence for each unique key column. The central DB sequence has to be modified or re-created so that it will only occupy a partition. The START WITH value should be 1 greater than the maximum existing value of the column. The MAXVALUE of the sequence should be big enough so that central DB app would not easily run out of numbers, while at the same time it shouldn’t be too big so that local DBs have more room for their partitions. Manually create (or modify) the central DB sequence, for example, on Oracle server:

CREATE SEQUENCE pvsdemo.task_id_seq START WITH 1000 MAXVALUE 1000000000;

and on MySQL server

CALL pvsadmin.create_sequence(‘pvsdemo’, ‘task_id_seq’, 1000, 1000000000);

  1. Publish the sync sequence on web admin console. Here you need to supply a sequence name, task_id_seq in our example and add it to a schema, pvsdemo in our example. In addition, you need to supply a “Start Value” and a “Window Size”. The start value should be one greater than the central DB sequence partition MAXVALUE. In our case, it should be 1000000001. The window size determines the number of values available in one local DB sequence partition. We use 1000000 for our example.
  2. After the first sync, the sequence partitions will be automatically created on local DBs. This is in contract to central DB, where you create the sequences manually. To use the sequences, call their NEXTVAL methods. For example, to insert into a table with column col1 as primary key, on Oracle server you do:

INSERT INTO pvcdemo.table1(c1, c2) VALUES

(pvcadmin.task_id_seq.nextval, ‘hello world’);

and on MySQL server you do

INSERT INTO pvcdemo.table1(c1, c2) VALUES

(pvcadmin.sequence_nextval(‘pvcdemo’, ‘task_id_seq’), ‘hello world’);

Other Options

We believe that AUTO_INCREMENT and sync sequence are the best choices for most situations. Still, there are other options that may fit your specific needs. We list them below.

  1. Only allow transactions to happen on central DB. Device local DBs are made read-only, i.e. for queries only. Believe it or not, there are systems that adopt this model.
  2. Use randomly generated numbers for key values. The length of the random numbers has to be long to reduce the possibility of collisions.
  3. Use a composite key. The sync client API has a method that returns the sync client ID. Apparently this is a globally unique number. You can use the client ID as the first column of a primary key. You then use a second column that takes locally unique values. Let’s call it LUID column. You define the client ID column and the LUID column together as your composite primary key. The values of the composite key are globally unique.
  4. Compute a GUID using client ID and LUID. For example, you could use this formula:

    GUID = <client ID> * 1000000000 + LUID

  5. Map local UID with GUID. This is the technique used by OMA DS (aka SyncML), and Activesync. Locally created records are assigned an LUID. During Activesync synchronization, a GUID for the same record is generated on server by mapping the LUID and synced back to client. For OMA DS, client sends an LUID back to server for every server sent record. A map table of LUID and GUID is maintained on server.

    The mapping methods may be OK for simple PIM (Personal Information Management) sync. However, for enterprise applications that have large amount of data, a lot of tables and complex referential relationships between tables, the mapping would cause performance and maintenance problems. Hence, Pervasync does not support mapping method.

Avoiding Sync Errors and Conflicts

Bad things happen. Synchronization systems are no exception. However, Pervasync has measures to help you keep things under control.

As you know a sync session has two phases, Check-in and Refresh. Refresh should rarely go wrong, as it is a forced applying of central DB changes to local DB. If it does go wrong, you have to manually fix the error and try Refresh again. As a last resort, the local DB has to be re-loaded by either re-installing the client or re-subscribe the client from server side.

Most of the errors happen in the Check-in phase where client tries to apply local DB transactions to central DB. In the following we will talk about errors due to DB constraint violations and conflicts due to con-current updates of same records from different clients. The emphasis here is prevention.

Errors Due to DB Constraint Violations

The sync client check-in is just like other central DB client transactions. It has to meet all the DB constraints to be committed in central DB.

Create Local DB Constraints via Sync SQL

First thing to do to avoid the constraint violations during Check-in is to ensure the local transactions meet the constraints on client side. Pervasync does not sync central DB constraints to local DB except for NOT NULL and primary key constraints. However, Pervasync has a “Sync SQL” feature that allows you publish SQL statements to be applied to client DB during synchronization. On web admin console, locate the sync schema and click on “Add Sync Sql” to define the desired UNIQUE constraints, CHECK constraints or referential constraints, for example,

ALTER TABLE pvcdemo.tasks ADD CONSTRAINT tasks_unique UNIQUE (NAME)

Instead of local DB constraints, we recommend you ensure the constraints in the client application layer where you can give end users direct, meaningful feedback when a constraint is violated.

Sync Table Ranks: Check-in Order

Even through your local changes meet the referential constraints, during Check-in, you still may encounter referential constraint violations like “Parent Key Not Found” and “Child Record Found”. This has to do with the order of the DML (Data Manipulation Language) operations in the Check-in transaction.

Pervasync uses the sync table ranks to determine the check-in order. When you publish a sync table, you need to assign a rank number that indicates the referential relationship among the sync tables. Parent tables should have a smaller rank number than child tables. For Insert operations, tables with a lower rank number are done first so that parent table is inserted first to avoid the “Parent Key Not Found” error when child table is inserted. Deletes are done in the reverse order to avoid the “Child Record Found” error.

Conflict Detection and Resolution

In an online system, a client could lock a DB row (record) when making changes to it so that the client is sure that it is modifying an up-to-date version of the row. In a sync system, sync client keeps a local copy of the row and makes offline changes to it without locking the central DB row. While the client makes these offline changes, other sync clients or central DB users may be making changes to the same row in central DB. During check-in time, if we do not do anything to handle conflicts, the newly checked-in row will overwrite changes from other sources. This may break your business logic since essentially the client was modifying an out-of-date copy of the row and changes from other clients were not taken into consideration.

Pervasync provides conflict detection and resolution to help you handle the conflicts so that your business requirements are satisfied.

There are three types of conflicts:

  • Insert conflict – Insert causes primary key violation. Likely others have inserted a row with a same primary key value. To avoid insert conflicts, refer to section 5.6 Generating Unique Key Values in Distributed Databases.
  • Update Conflict – Record to be updated has a higher version number indicating it was updated or has been deleted.
  • Delete conflict – Record to be deleted has a higher version number indicating it was updated or has been deleted.

For each type of conflict, we provide three resolution methods:

  • FORCE_CHECK_IN – Apply client transaction ignoring the conflict. For delete operations, records are deleted regardless of their versions. If records to be inserted already exist, update the records. If records to be updated have been deleted, insert the records.
  • DISCARD: Discard the conflicting changes from the client and check in non-conflicting changes.
  • REPORT_ERROR: Treat conflicts as errors and notify the client. This will cause the sync session to fail. In this case client could do a REFRESH_ONLY sync to let the server side changes overwrite the conflicting client side changes. This effectively modified the client transaction so that it won’t cause conflicts in next check-in assuming others won’t make new changes on server again. Before check-in, the client application could give end users a chance to inspect and update the new version of the records. End users should be clear that until the check-in succeeds, all changes are temporary although they are committed locally.

To configure the resolution methods, edit the following parameters in <PERVASYNC SERVER HOME>/config/pervasync_server_<db>.conf.

pervasync.conflict.resolution.InsertExisting=REPORT_ERROR

pervasync.conflict.resolution.UpdateChanged=FORCE_CHECK_IN

pervasync.conflict.resolution.DeleteChanged=DISCARD

REFRESH-ONLY to the Rescue

Pervasync supports three sync directions, TWO_WAY, REFRESH_ONLY and CHECK_IN_ONLY. You can pass in sync direction to the sync() method of client SyncAgent object, or you can use the corresponding pvc.bat/pvc.sh sub commands sync, refresh and checkin.

In a two-way sync session, Check-in is always done before Refresh. The reason is that conflict detection is done on the server side during Check-in. Refresh force-applies changes on local DB without any conflict detection. If Refresh is done before a successful Check-in, some local changes may be silently overwritten.

However, when Check-in encounters some errors or conflicts and cannot proceed, it is often useful to do a REFESH-ONLY sync to intentionally overwrite client changes with server changes so that a sub-sequent Check-in can go through. Examples are server has added or dropped a column, or server sets the conflict resolution method to be REPORT_ERROR and a conflict is found. The algorithm is as follows.

  1. Do two-way sync.
  2. Catch sync error, present the error to end-user and ask for permission to do a REFESH-ONLY sync.
  3. Do a REFESH-ONLY sync and give user a chance to edit the refreshed data.
  4. Do two-way sync.

By the way, even a lot of online applications do not lock the records while end users are editing the data. Instead, when the user is done with the editing and submits the data, they do the conflict check and if a conflict is found, a new version of the data is returned for user to edit and re-submit. The algorithm is essentially the same as above.

Configuration and Logging

Pervasync server and client have a similar way for configuration and logging.

Configuration is mainly done by editing the .conf files, which is located in the config directory. Property values are needed to customize runtime behaviors. Server or client has to be re-started for any changes to take effect.

NOTE: The .dat file in the same folder is for Pervasync internal use. So never modify it.

The Server Configuration File

The content of file pervasync_server_oradb.conf is located in the server config directory:

#=====================================================================

#== Pervasync server for ORADB ==

#== conf file for runtime options ==

#== ==

#== Property values are needed to customize runtime behaviors. ==

#== Server has to be re-started for any changes to take effect. ==

#=====================================================================

# This is the pervasync license key that you get from Pervasync sales

pervasync.server.license.key=0

# encryptor class name. Class needs to implement interface pervasync.Encryptor

pervasync.encryptor=pervasync.security.DefaultEncryptor

# UserManager class name. Class needs to implement interface pervasync.server.UserManager

pervasync.server.user.manager=pervasync.server.PervasyncUserManager

# Values: SEPARATELY, TOGETHER; default TOGETHER

# This will affect only client check in, not refresh engine

# SEPARATELY (for table groups) for maximum concurrency, TOGETHER for maximum consistency

pervasync.check.in.commit.mode=TOGETHER

# client subscription metadata cache limit (number of clients whose sub metadata are cached)

pervasync.server.cached.client.subs=1000

# Sync Server DB create user, table options

pervasync.server.admin.user.options=DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP

pervasync.server.admin.user.grants=connect, resource, select any table, delete any table, insert any table, update any table, create any table, drop any table,alter any table,lock any table,create any sequence, drop any sequence, alter any sequence, select any sequence, create any view, drop any view,create any trigger,drop any trigger,create any index, drop any index,create role,drop any role,grant any role,create profile, drop profile,create any synonym, create user, drop user,grant any privilege,SELECT_CATALOG_ROLE

pervasync.server.db.table.options=

# acceptable values: true, false

pervasync.server.db.conn.cache.enable=true

# acceptable values: integer; default 0; don’t set to a value other than 0

pervasync.server.db.conn.cache.InitialLimit=0

# acceptable values: integer; default 0

pervasync.server.db.conn.cache.MinLimit=0

# acceptable values: integer; default no limit

pervasync.server.db.conn.cache.MaxLimit=10

# acceptable values: integer seconds?; default 0, no timeout

pervasync.server.db.conn.CacheInactivityTimeout=0

# acceptable values: integer; default 0

pervasync.server.db.conn.cache.MaxStatementsLimit=0

# When client uses JSON transport, a sync session could consist multiple sync

# messages. This sets an upper limit for the message size.

Pervasync.max.message.size=10000000

# BLOBs are transferred in chunks. This is the chunk size (number of bytes).

Pervasync.blob.buffer.size=4000

# CLOBs are transferred in chunks. This is the chunk size (number of chars).

Pervasync.clob.buffer.size=4000

# If set to true, use one query to select non-lob columns and a separate

# query to select lob columns. Use true for Mysql Db and false for Oracle Db.

Pervasync.separate.lob.query=false

# Sleep this amount of milli seconds between trigger creation to work around a mysql issue.

Pervasync.create.trigger.sleep.ms=0

# For multi-message session, client has to come back within this

# amount of time, default 30 minutes

pervasync.session.timeout.seconds=180

# Max message processing time, default 100 hours. Need a big value for

# object transport where there is only one request and response message

# for the whole session

pervasync.message.timeout.seconds=360000

#

# Sync engine run interval (seconds).

# Sync engine will start another run after this amount of time

pervasync.engine.run.interval=30

#

# Sync engine retry interval (seconds).

# When sync engine encounters a runtime error, it will retry after this amount of time

pervasync.engine.retry.interval=300

#

# Sync engine retry limit.

# When sync engine encounters a runtime error, it will retry this many times

# before it shuts down itself.

Pervasync.engine.retry.limit=48

# Number of days that delete operations are kept in user’s subscriptions.

# older deletes will be purged. Sync client would get “snapshot too old”

# error if the deletes were not synced down before they were purged.

Pervasync.server.deletes.keep.days=365

pervasync.server.ignore.snapshot.too.old=false

# Which sync sessions are logged. Valid values are NONE, ALL and NON_EMPTY.

# NON_EMPTY sessions have at least one data op or an exception.

Pervasync.server.sync.history.log.mode=NON_EMPTY

# Number of days that sync session logs are kept before they are purged.

Pervasync.server.sync.history.keep.days=30

#

# Conflict detection and Conflict resolution. Default REPORT_ERROR.

# For each type of conflict, valid resolution methods are

# — FORCE_CHECK_IN: no conflict detection; Delete as is; If Insert fails Update; If update fails insert.

# — DISCARD: Ignore conflicting changes; check in non-conflicting changes

# — REPORT_ERROR: Treat conflicts as errors and notify client

#

# Insert causes primary key violation

pervasync.conflict.resolution.InsertExisting=REPORT_ERROR

# Record to be updated has a higher version numbe (was updated or has been deleted)

pervasync.conflict.resolution.UpdateChanged=FORCE_CHECK_IN

# Record to be deleted has a higher version number (was updated or has been deleted)

pervasync.conflict.resolution.DeleteChanged=DISCARD

# If TRUE/YES, enable rollback of file sync

pervasync.file.check.in.rollback.enabled=TRUE

# SQl statements to be executed before check in.

pervasync.server.before.check.in.sql=

# SQl statements to be executed after check in.

pervasync.server.after.check.in.sql=

# SQl statements to be executed before refresh.

Pervasync.server.before.refresh.sql=

# SQl statements to be executed after refresh.

Pervasync.server.after.refresh.sql=

# If TRUE/YES, ignore errors in executing the before/after sqls

pervasync.server.before.after.sql.ignore.error=TRUE

# The level of the logger named “pervasync.server.oradb” and appenders

# Acceptible values for level and threshold are:

# DEBUG, INFO, WARN, ERROR and FATAL

log4j.logger.pervasync.server.oradb=DEBUG, CONSOLE, FILE

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender

log4j.appender.CONSOLE.Threshold=INFO

log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout

log4j.appender.CONSOLE.layout.ConversionPattern=[%d{MM/dd,HH:mm:ss} %t] %-5p %x- %m%n

# Appender FILE writes to the log files in Pervasync log folder.

Log4j.appender.FILE=org.apache.log4j.RollingFileAppender

log4j.appender.FILE.Threshold=DEBUG

log4j.appender.FILE.File=${pervasync.home}/log/pervasync_server_oradb.log

log4j.appender.FILE.MaxBackupIndex=3

log4j.appender.FILE.MaxFileSize=1MB

# Truncate file or not

log4j.appender.FILE.Append=true

# Appender A2 uses the PatternLayout.

Log4j.appender.FILE.layout=org.apache.log4j.PatternLayout

log4j.appender.FILE.layout.ConversionPattern=[%d{MM/dd,HH:mm:ss} %t] %-5p %x- %m%n

#

# Oracle JDBC Logging

#

# acceptable values: OFF, SEVERE, WARNING,INFO, CONFIG, FINE, FINER, FINEST, ALL

# This will override <JAVA_HOME>/jre/lib/logging.properties

# oracle.jdbc.driver.OracleLog.level value

# oracle.jdbc.driver.OracleLog.level=WARNING

oracle.jdbc.level=OFF

#

# Email notification settings

#

# Use true to turn on email notifications of server failures like

# sync engine errors and/or sync session errors

pervasync.server.enable.notification = false

# Number of seconds before the Notification engine wakes up to do its job

pervasync.server.notification.process.interval = 600

# smtp mail server host name. Defaults to localhost

pervasync.server.mail.smtp.host =

# smtp mail server user name. Defaults to current login user.

Pervasync.server.mail.smtp.user =

# comma-separated notification recipient email addresses

pervasync.server.mail.recipients =

#

# admin consol preferences

#

pervasync.console.rows.to.display=25

pervasync.console.rows.to.retrieve=1000

pervasync.console.look.and.feel=beach

pervasync.console.accessibility.mode=inaccessible

pervasync.console.date.format=short

pervasync.console.time.format=short

The Client Configuration File

The content of file pervasync_client_oradb.conf is located in the client config directory:

#======================================================================

#== Pervasync client for ORADB ==

#== conf file for runtime options ==

#== ==

#== Property values are needed to customize runtime behaviors. ==

#== Client has to be re-started for any changes to take effect. ==

#======================================================================

# General

# Sync Client DB create user table options

pervasync.client.admin.user.options=DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP

pervasync.client.admin.user.grants=connect, resource, select any table, delete any table, insert any table, update any table, create any table, drop any table, alter any table, lock any table, create any sequence, drop any sequence, alter any sequence, select any sequence, create any view, drop any view, create any trigger,drop any trigger, create any index, drop any index, create role,drop any role,grant any role,create profile, drop profile,create any synonym, create user, drop user, grant any privilege,SELECT_CATALOG_ROLE

pervasync.client.db.table.options=

# Sync sequence options

# A sequence is considered full if (MaxValue – CurrentValue) < threshold

# A full sequence will get refreshed with a new range in next sync

pervasync.client.sync.sequence.threshold=10

# Transport serialization, use Object or Json

pervasync.client.transport.serialization=Json

# Http transport content encoding. Use gzip to compress traffic; else leave empty

pervasync.client.transport.content.encoding=gzip

# When client uses JSON transport, a sync session could consist multiple sync

# messages. This sets an upper limit for the message size.

Pervasync.max.message.size=4000000

# BLOBs are transferred in chunks. This is the chunk size (number of bytes).

Pervasync.blob.buffer.size=4000

# CLOBs are transferred in chunks. This is the chunk size (number of chars).

Pervasync.clob.buffer.size=4000

# Values: SEPARATELY, TOGETHER; default TOGETHER

# This will affect only client refresh

# SEPARATELY (for table groups) for maximum concurrency, TOGETHER for maximum consistency

pervasync.client.refresh.commit.mode=TOGETHER

# If set to true, use one query to select non-lob columns and a separate

# query to select lob columns. Use true for Mysql Db and false for Oracle Db.

Pervasync.separate.lob.query=false

# Sleep this amount of milli seconds between trigger creation to work around a mysql issue.

Pervasync.create.trigger.sleep.ms=0

# Whether to send server the client sync summary. Valid values are ALWAYS,

# NEVER, NON_EMPTY and ON_ERROR.

Pervasync.client.send.sync.summary=NON_EMPTY

# Number of days that sync session logs are kept before they are purged.

Pervasync.client.sync.history.keep.days=30

# If TRUE/YES, enable rollback of file sync

pervasync.file.refresh.rollback.enabled=TRUE

# SQl statements to be executed before check in.

pervasync.client.before.check.in.sql=

# SQl statements to be executed after check in.

pervasync.client.after.check.in.sql=

# SQl statements to be executed before refresh.

Pervasync.client.before.refresh.sql=

# SQl statements to be executed after refresh.

Pervasync.client.after.refresh.sql=

# If TRUE/YES, ignore errors in executing the before/after sqls

pervasync.client.before.after.sql.ignore.error=TRUE

#Log4j properties

# The level of the logger named “pervasync.client.oradb” and appenders

# Acceptible values for level and threshold are:

# DEBUG, INFO, WARN, ERROR and FATAL

log4j.logger.pervasync.client.oradb=DEBUG, CONSOLE, FILE

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender

log4j.appender.CONSOLE.Threshold=INFO

log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout

log4j.appender.CONSOLE.layout.ConversionPattern=[%d{MM/dd,HH:mm:ss} %t] %-5p %x- %m%n

# Appender FILE writes to the log files in Pervasync log folder.

Log4j.appender.FILE=org.apache.log4j.RollingFileAppender

log4j.appender.FILE.Threshold=DEBUG

log4j.appender.FILE.File=${pervasync.home}/log/pervasync_client_oradb.log

log4j.appender.FILE.MaxBackupIndex=2

log4j.appender.FILE.MaxFileSize=1MB

# Truncate FILE or not

log4j.appender.FILE.Append=true

# Appender CONSOLE uses the PatternLayout.

Log4j.appender.FILE.layout=org.apache.log4j.PatternLayout

log4j.appender.FILE.layout.ConversionPattern=[%d{MM/dd,HH:mm:ss} %t] %-5p %x- %m%n

# Oracle JDBC Logging

# acceptable values: OFF, SEVERE, WARNING,INFO, CONFIG, FINE, FINER, FINEST, ALL

# This will override <JAVA_HOME>/jre/lib/logging.properties oracle.jdbc.driver.OracleLog.level value

#oracle.jdbc.driver.OracleLog.level=WARNING

oracle.jdbc.level=OFF

# Whether to allow dropping tables in initial sync

pervasync.client.allow.dropping.tables=true

# Auto sync schedules

# Schedule1

pervasync.client.schedule1.enabled=false

pervasync.client.schedule1.sync.direction=TWO_WAY

pervasync.client.schedule1.start.time=1264208583000

pervasync.client.schedule1.repeat=true

pervasync.client.schedule1.interval.hours=0

pervasync.client.schedule1.interval.minutes=15

pervasync.client.schedule1.interval.seconds=0

# Schedule2

pervasync.client.schedule2.enabled=false

pervasync.client.schedule2.sync.direction=TWO_WAY

pervasync.client.schedule2.start.time=0

pervasync.client.schedule2.repeat=false

pervasync.client.schedule2.interval.hours=0

pervasync.client.schedule2.interval.minutes=0

pervasync.client.schedule2.interval.seconds=0

# Schedule3

pervasync.client.schedule3.enabled=false

pervasync.client.schedule3.sync.direction=TWO_WAY

pervasync.client.schedule3.start.time=0

pervasync.client.schedule3.repeat=false

pervasync.client.schedule3.interval.hours=0

pervasync.client.schedule3.interval.minutes=0

pervasync.client.schedule3.interval.seconds=0

Email notification settings

To turn on email notifications of server failures like sync engine errors and/or sync session errors, assign a “true” value for the sync server parameter “pervasync.server.enable.notification”.

In addition, you need to specify the email recipients. If the sync server host does not have local SMTP setup, you need to specify a SMTP host.

#

# Email notification settings

#

# Use true to turn on email notifications of server failures like

# sync engine errors and/or sync session errors

pervasync.server.enable.notification = false

# Number of seconds before the Notification engine wakes up to do its job

pervasync.server.notification.process.interval = 600

# smtp mail server host name. Defaults to localhost

pervasync.server.mail.smtp.host =

# smtp mail server user name. Defaults to current login user.

Pervasync.server.mail.smtp.user =

# comma-separated notification recipient email addresses

pervasync.server.mail.recipients =

Logging

You can change the logging behavior by editing the related properties in the configuration files. By default, the Pervasync logging outputs go to log files in folder log and the console/stdout.

NOTE: Depending on the running environment, the console/stdout logs might be re-directed to files too. For example, the Tomcat console/stdout logs on Linux is redirected to <Catalina_Home>/logs/Catalina.out.

Pervasync Server Java API

Server API Javadoc

To access the server API javadoc, open the following page in a browser:

<PERVASYNC_HOME>/doc/server_javadoc/index.html

The Pervasync server API includes a SyncServerAdmin class for interacting with the server DB repository. There are also a UserManager interface and Encryptor interface for users to customize user management and encryption.

The SyncServerAdmin class in package pervasync.server contains methods for users to publish sync objects, as well as create subscriptions.

The UserManager interface in package pervasync.server enables a user to plug in his own user management system for Pervasync’s use.

The Encryptor interface in package pervasync.security enables a user to plug in his own encryption implementations for Pervasync’s use.

The SyncServerAdmin class contains methods for users to publish sync objects, as well as create subscriptions. To call its methods, a SyncServerAdmin instance needs to be created, e.g., syncServerAdmin = new SyncServerAdmin(). The constructor will open a JDBC connection to the Pervasync server repository database. The destroy() method should be called when you are done with the SyncServerAdmin object so that the database connection could be released.

The methods that start with “add” would add sync objects to the sync repository. Those that start with “remove” would remove and those start with “update” would update.

A sync schema is a container that contains other sync objects, including sync tables, sequences and sql statements to be applied to client DB. Typically, you create a schema and add other sync objects to it. That makes a publication. Then you create a sync client and subscribe the client to the publication.

A sync schema corresponds to a physical DB schema that by default resides in the same DB as the sync repository. To publish a schema from a different DB, you need to use the “addServerDb” method to define that DB.

In addition to publishing sync schemas for synchronizing DB data to devices, you can also publish a file folder, which is not DB related, and sync its files to devices.

When you publish sync objects, you can optionally define some sync parameters. At subscription time, you would specify values to the parameters so that different clients could get different sub-sets of data.

The xmlExport() method would export the sync object definitions into an XML doc. Then you can use xmlUnpublish() to remove all the sync objects and use xmlPublish() to re-create all the sync objects.

Setting Up Environment for Server Applications

To make the sync server admin API available to server applications, include the classes directory and the necessary jar files in the CLASSPATH environment variable.

NOTE: We assume that you have already included Oracle JDBC driver or MySQL Java connector jars in CLASSPATH.

Windows:

SET CLASSPATH=C:\pervasync_server-4.0.0\classes; C:\pervasync_server-4.0.0\lib\commons-codec-1.3.jar;C:\pervasync_server-4.0.0\lib\log4j-1.2.9.jar;%CLASSPATH%

Linux/Unix bash:

export CLASSPATH=/pervasync_server-4.0.0/classes: /pervasync_server-4.0.0/lib/commons-codec-1.3.jar: /pervasync_server-4.0.0/lib/log4j-1.2.9.jar :${CLASSPATH}

Linux/Unix csh:

setenv CLASSPATH /pervasync_server-4.0.0/classes: /pervasync_server-4.0.0/lib/commons-codec-1.3.jar: /pervasync_server-4.0.0/lib/log4j-1.2.9.jar :${CLASSPATH}

Pervasync Client Java API

NOTE: If sync client resides inside a firewall and sync server resides outside the firewall, you need to set the HTTP proxy host and port in the client configuration file. See section 5.7.2 for details.

Client API Javadoc

To access the client API javadoc, open the following page in a browser:

<PERVASYNC_HOME>/doc/client_javadoc/index.html

The Pervasync client API includes a SyncClientAdmin class for interacting with the client DB repository and a SyncAgent class for interacting with the sync agent.

The SyncClientAdmin class in package pervasync.client contains methods for users to do admin work, such as setting the sync user’s password etc.

The SyncAgent class in package pervasync.client is for client applications to start a sync session.

The Encryptor interface in package pervasync.security enables a user to plug in his own encryption implementations for Pervasync’s use.

Package pervasync contains classes used by the above-mentioned classes.

The SyncAgent class is for client applications to trigger a sync session. The sync agent synchronizes application data on device with that on server on behalf of the client application. In your client application, you should call the static method getInstance() to get a SyncAgent object and then call its instance method sync() to trigger a synchronization. The method sync() can be called multiple times and each call would return a SyncSummary object for you to check sync result. The instance method destroy() should be called when you are done with the SyncAgent object.

Setting Up Environment for Client Applications

To make the sync client admin API available to client applications, include the classes directory and the necessary jar files in the CLASSPATH environment variable.

NOTE: We assume that you have already included Oracle JDBC driver or MySQL Java connector jars in CLASSPATH.

Windows:

SET CLASSPATH=C:\pervasync_ client-4.0.0\classes; C:\pervasync_client-4.0.0\lib\commons-codec-1.3.jar;C:\pervasync_client-4.0.0\lib\log4j-1.2.9.jar;%CLASSPATH%

Linux/Unix bash:

export CLASSPATH=/pervasync_client-4.0.0/classes: /pervasync_client-4.0.0/lib/commons-codec-1.3.jar: /pervasync_client-4.0.0/lib/log4j-1.2.9.jar :${CLASSPATH}

Linux/Unix csh:

setenv CLASSPATH /pervasync_client-4.0.0/classes: /pervasync_client-4.0.0/lib/commons-codec-1.3.jar: /pervasync_client-4.0.0/lib/log4j-1.2.9.jar :${CLASSPATH}

Connecting to the Local DB Schema from Your Client Application

For client deployment, we recommend you embed the sync client in your client application so that they are installed at the same time. The sync client keeps the local DB and central DB in sync while the client app connects to the local DB schema and does its job. The client DB schema name is what you have specified when you created the sync schema on server. The password is the same as the Pervasync client Admin Password you specified in the GUI during sync client install.

NOTE: Not to confuse “File-Based Synchronization” with “File Sync”. The former is the feature of using file as transport for synchronization. The latter is the feature of synchronizing files residing in corresponding server and client folders.

When a local database is located in a place that has no network connection to the server at all (always offline!), user can export the client sync requests to files. The client request files can then be taken to a place that has network access to the sync server and be uploaded to the sync server.. Sync server would process the requests and return response files, which can then be taken back to the sync client and be imported to the local database.

Exporting Sync Request Files on Client

Click on the “Export” tab of the sync client GUI.

This tab is used to export client sync requests to files. You first need to select the sync type and sync direction, fill in the correct password just as what you do in a regular synchronization. Optionally you can change the folder and file name of the exported request file. However it is recommended to use the pre-generated values.

Click on the “Export” button to start the exporting process. If you need to sync both DATA and FILE, export one request file for each.

Processing the Client Request Files on Server

Once you have the client request files, you can take the files to the sync server and process them there. There are two ways to do that: using the native Java app pvs.sh/pvs.bat or via the web app. The native Java app has a tab called “Request Processor”.

Specify the client request folder and server response folder. Then select the client request file to import to server. Response file name will be automatically generated. Click on the “Process” button to start the processing. If you have multiple request files, repeat this process for each.

Alternatively you can use the Pervasync web app to upload the client request file. This is a more convenient way. However, for large amount of data, we recommend using the native app.

The login page of the server web app has an area for file-based sync.

Simply upload the request file and save the response file when prompted.

Importing Server Response Files on Client

Take the server response files back to the client. Click on the “Import” tab of the sync client.

Select the server response file and click the “Import” button. If everything goes well, congratulations, you just completed a complete “offline” synchronization!

Using Sync SQL to Create Indexes and Constraints on Local DBs

Sync SQL

Pervasync does not automatically sync indexes (except for Primary key indexes) and constraints (except for NOT NULL) to local databases. The reason is to make the system less complex and to make synchronization between different types of databases possible.

In addition to indexes and constraints, there are other database objects, such as views, functions, triggers and PL/SQL packages (for Oracle) that users may or may not want to sync to local DBs. If they do, they may not want the exact same objects as central Db to be created on local DBs.

To assist users in managing these generic DB objects, Pervasync implemented a feature called “Sync SQL” that enables users to publish SQL statements on the web admin console. The SQL statements will be downloaded and executed on local DB during synchronization. In the SQL statement, users could create, alter and delete any DB objects that are appropriate for the local databases.

Sync SQL belongs to a sync schema. To get started, locate a sync schema on the web admin console “Sync Schemas” page, click on “Sqls” under “Sync Objects” and then you will get to a page where you can add, remove and update sync SQLs.

The Sync SQLs are executed with the client side sync schema as the default schema.

NOTE: How to include multiple SQL statements in one Sync SQL so that you don’t have to create too many Sync SQLs? For Oracle databases each Sync SQL has to be a single execution unit to be executed by the database. However you could enclose multiple SQL statements by “BEGIN” and “END;” to make it a PLSQL block so that it can be executed as a single unit. For MySQL databases, you could put multiple statements, separated by semi-colon, in one Sync SQL as long as you have the “allowMultiQueries=true” in your JDBC URL, for example:

jdbc:mysql://localhost:3306/?zeroDateTimeBehavior=convertToNull&allowMultiQueries=true

To update the JDBC URL, go to the “Setup” tab of the sync client UI, enable “Advance Mode”, edit and submit.

NOTE: For Oracle databases, a simple SQL statement cannot have a trailing “;” while a PLSQL block has to have a trailing “;”.

An Example of Foreign Key Creation via Sync SQL

Foreign key constraints on client DB can be tricky as they may interfere with the synchronization process. For example, in some situations like subscription parameter values being updated, Pervasync would need to re-create and re-populate affected tables on device. If a table has a foreign key constraint, the operation may fail.

A solution to this problem is to disable the foreign key constraints during sync: drop the foreign key constraints before sync and re-create the constraints after sync. In <Pervasync Client Home>/config/ pervasync_client_mysql.conf, you can set the “drop constraint” sql as value of parameter pervasync.client.before.refresh.sql and set the “create constraint” sql as value of pervasync.client.after.refresh.sql. To make the “create/drop” sql easier to manage, you can put them in stored procedures and use Sync SQL to create the stored procedures. This way you can make changes to the constraints any time you want from the admin console.

Following is an example of the stored procedures that you can modify and then publish as a Sync SQL of your publication schema.

DROP PROCEDURE IF EXISTS `CreateFK`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateFK`(

    IN parm_table_name VARCHAR(100),

    IN parm_key_name VARCHAR(100),

    IN parm_cascade_on_delete BOOL,

    IN parm_child_field_name VARCHAR(100),

    IN parm_parent_table_name VARCHAR(100),

    IN parm_parent_field_name VARCHAR(100)

)

BEGIN

    set @ParmTable = parm_table_name ;

    set @ParmKey = parm_key_name ;

    set @ParmChildFieldName = parm_child_field_name;

    set @ParmParentTableName = parm_parent_table_name;

    set @ParmParentFieldName = parm_parent_field_name;

    IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN

        set @StatementToExecute = concat(‘ALTER TABLE ‘,@ParmTable,’ DROP FOREIGN KEY ‘,@ParmKey);

        prepare DynamicStatement from @StatementToExecute ;

        execute DynamicStatement ;

        deallocate prepare DynamicStatement ;

    END IF;

    IF (parm_cascade_on_delete = false) THEN

        set @AddFKStatement1 = concat(‘ALTER TABLE `’,@ParmTable,’` ADD CONSTRAINT `’,@ParmKey, ‘` FOREIGN KEY (`’, @ParmChildFieldName, ‘`) REFERENCES `’, @ParmParentTableName, ‘` (`’, parm_parent_field_name, ‘`) ON UPDATE NO ACTION’);

        prepare DynamicStatement1 from @AddFKStatement1;

        execute DynamicStatement1 ;

        deallocate prepare DynamicStatement1 ;

    ELSE

        set @AddFKStatement2 = concat(‘ALTER TABLE `’,@ParmTable,’` ADD CONSTRAINT `’,@ParmKey, ‘` FOREIGN KEY (`’, @ParmChildFieldName, ‘`) REFERENCES `’, @ParmParentTableName, ‘` (`’, parm_parent_field_name, ‘`) ON DELETE CASCADE ON UPDATE NO ACTION’);

        prepare DynamicStatement2 from @AddFKStatement2;

        execute DynamicStatement2 ;

        deallocate prepare DynamicStatement2 ;

    END IF;

END

DROP PROCEDURE IF EXISTS `DropFK`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `DropFK`(

    IN parm_table_name VARCHAR(100),

    IN parm_key_name VARCHAR(100),

    IN parm_cascade_on_delete BOOL,

    IN parm_child_field_name VARCHAR(100),

    IN parm_parent_table_name VARCHAR(100),

    IN parm_parent_field_name VARCHAR(100)

)

BEGIN

    set @ParmTable = parm_table_name ;

    set @ParmKey = parm_key_name ;

    set @ParmChildFieldName = parm_child_field_name;

    set @ParmParentTableName = parm_parent_table_name;

    set @ParmParentFieldName = parm_parent_field_name;

    IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND CONSTRAINT_NAME = parm_key_name) THEN

        set @StatementToExecute = concat(‘ALTER TABLE ‘,@ParmTable,’ DROP FOREIGN KEY ‘,@ParmKey);

        prepare DynamicStatement from @StatementToExecute ;

        execute DynamicStatement ;

        deallocate prepare DynamicStatement ;

    END IF;

END

DROP PROCEDURE IF EXISTS `CreateForeignKeys`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateForeignKeys`( )

BEGIN

CALL YourDBName.CreateFK(‘announcementattachments’, ‘fk_AnnouncementAttachments_Announcements1’, true, ‘AnnouncementID’, ‘announcements’, ‘AnnouncementID’);

CALL YourDBName.CreateFK(‘announcementattachments’, ‘fk_AnnouncementAttachments_Companies’, false, ‘CompanyID’, ‘companies’, ‘CompanyID’);

…. (add the other call to CreateFK here for other foreign keys)

END

DROP PROCEDURE IF EXISTS `DropForeignKeys`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `DropForeignKeys`( )

BEGIN

CALL YourDBName.DropFK(‘announcementattachments’, ‘fk_AnnouncementAttachments_Announcements1’, true, ‘AnnouncementID’, ‘announcements’, ‘AnnouncementID’);

CALL YourDBName.DropFK(‘announcementattachments’, ‘fk_AnnouncementAttachments_Companies’, false, ‘CompanyID’, ‘companies’, ‘CompanyID’);

…. (add the other call to DropFK here for other foreign keys)

END

After that, call the stored procedures DropForeignKeys and CreateForeignKeys in the client config file:

pervasync.client.before.refresh.sql = call YourDBName.DropForeignKeys()

pervasync.client.after.refresh.sql = call YourDBName.CreateForeignKeys()

Sync Based on Network Characteristics

A mobile computer usually has multiple network interfaces, e.g. cable, Wi-Fi and cellular wireless (mobile data), to connect to the Internet, through which to connect to the central sync server. Some of the network interfaces are slower or more expensive compared with others. On the other hand, of all the subscribed publications for synchronization, some may involve much larger amount of data than others. One example is a folder of media files (photos or videos) or a database table of blobs of data items. When you are on a slow or expensive network, you may want to exclude these publications from synchronization. The “Sync Based on Network Characteristics” feature was implemented for this purpose.

Defining Network Characteristics Using a Matching String

First, we provide a way to identify the network interfaces of your client machines. After you install Pervasync client, you can use the utility listnetworks.bat (or listnetworks.sh for Linux) in the bin folder of Pervasync client home to list attributes of all the network interfaces on client machines. Pay attention to the display names, hardware addresses and IP addresses of your network cards. Following is a sample output.

C:\pervasync\bin>listnetworks.bat

Network Name: lo

Status: Active

Display Name: MS TCP Loopback interface

Hardware Address:

IP Address: 127.0.0.1

IP Address: 0:0:0:0:0:0:0:1

IP Address: fe80:0:0:0:0:0:0:1%1

Network Name: eth0

Status: Active

Display Name: Intel(R) PRO/Wireless 3945ABG Network Connection – McAfee NDIS Intermediate Filter Miniport

Hardware Address: 0018DE157BA9

IP Address: 182.138.0.102

IP Address: fe80:0:0:0:218:deff:fe15:7ba9%4

Network Name: eth1

Status: Non-Active

Display Name: Broadcom NetXtreme 57xx Gigabit Controller – McAfee NDIS Intermediate Filter Miniport

Hardware Address: 0015C54B210F

According to the network attributes, admin comes up with strings that match the display name, hardware address and/or IP address. The matching string defines a specific network card or a type of network. The matching string may contain operators “&&” and “||”. For example, the following matching string will match network eth0 in the above listing:

Intel && Wireless && Network Connection

The following matching string will match network cards that have an IP address that starts with “182.138” or “193.111.2”:

182.138. || 193.111.2

Specifying No-Sync Lists Associated with Sync Schemas and Sync Folders

After you have gathered information about network characteristics of your client machines, then, on Pervasync admin console, you can specify No-Sync Lists Associated with Sync Schemas and Sync Folders.

Pervasync adds two no-sync-list attributes to the sync schema and sync folder definition: NO_INIT_SYNC_NETWORKS and NO_SYNC_NETWORKS. The values of the attributes are the matching strings that identify a specific network card or a type of network. To enable this feature, you need to change the value of the No-Init-Sync Networks and No-Sync Networks fields from the default value “NONE” into matching string that identifies the network cards. You may use full strings or sub-strings of the following network card attributes: display name, hardware address and IP address. You may also have “&&” and/or “||” between the strings to indicate whether it’s “match all” or “match any” respectively.

When you add a new publication (sync schema or folder), you specify global values of the no-sync lists that apply to all user devices. At subscription time you have a chance to overwrite the values for a specific user device or group. That is to say, values can be specified at publication level, group subscription level and client subscription level. Publication level values are inherited and can be overridden by group subscription values, which in turn are inherited and can be overridden by client subscription values.

Pervasync client will match the active network with the no-sync list matching strings before starting a synchronization. If there is match, the sync schema/folder will be excluded from synchronization. For example, if schema “schema1” has a NO_SYNC_NETWORKS value of “182.138. || 193.111.2” and the current active network card has an IP of 193.111.222.111, “schema1” will be excluded from the synchronization.

NOTE: NO_INIT_SYNC_NETWORKS only applies to initial sync of a publication while NO_SYNC_NETWORKS applies to both initial and incremental syncs.

On the Pervasync admin console’s publications and subscriptions UI, sync folders and sync schemas are displayed on the same pages. Admins have a complete view of all the available schema and folder publications. Admin could easily make sure related folders and schemas are either included or excluded together for a certain network type. At subscription time, admin again could easily make sure related folders and schema are either included or excluded together in a group or client subscription.

No-sync lists are part of the sync metadata that admin can update at any time. When a sync session detects new metadata changes, it will postpone data and file syncs and will instead sync these metadata changes to device. Device will then use these new metadata in following syncs.

Supporting Dynamic Sync Users

Sync users are normally static. That is to say, one user is associated with a specific local host, mobile or fixed. You setup the sync client once, often manually, on the local host. The sync client will then download the user’s data and keep it in sync with a central server. However, there are situations where the sync users are dynamic, meaning they move from host to host. How do you handle that?

Use Case

Say you are a health organization with many clinics and doctors. There is a central database server containing all the data. In each clinic you have a local database server serving your local application. A doctor may work at any clinic at a given time. However, you cannot afford synchronizing all your central DB data to each local DB. When a doctor logs in to your local app on a local host, you want this doctor’s data be synchronized to that local DB so that the doctor can use your app to do his or her work. You want the same to happen when the doctor logs on to another host, or another doctor logs on to this host.

Solution

The solution is to dynamically setup a sync client when a new user logs on to your app on a local host. The sync client will synchronize this user’s data to a user-specific schema in the local DB. The same local DB could have data for multiple users in separate schemas which can be synced separately. A same user could have a sync client on multiple hosts. When user logs on to any of the hosts, data can be fast refreshed and presented to the user through you local app.

Following are the implementation steps.

Step 1. On Pervasync server admin console, create publications. When you define a sync schema, include a parameter in the value of “Client DB Schema”, for example, use “schema1_${USER_ID}”. This is to sync the schema to a user specific schema on local DB.

Step 2. On Pervasync server admin console, create a sync client and subscriptions for each user. At this time, assign a value to the USER_ID parameter.

Step 3. On each local host, setup the local DB and your client application. Your client app should include a copy of Pervasync client zip file. Do not setup the sync client yet.

Step 4. When a user logs in to your app, your app detects that this is the first time for this user so it needs to programmatically set up a sync client for this user:

  • Unzip the Pervasync client zip file to a user specific folder
  • Edit file pervasync_client_oradb.ini under the “install” folder of the user-specific sync client. You need to assign a user-specific value for parameter “pervasync.client.admin.user”, e.g. pvcadmin_user1. Also assign value for “pervasync.user.name”. Then invoke the pervasync_client_oradb_setup.bat script in this “install” folder to setup the client. Refer to section 4.2.4 “Setting up the Sync Client Using the Non-GUI Configuration Scripts”, for more details.
  • Once the client is setup, invoke pvc.bat script under the “bin” folder to do synchronization.

Step 5. After synchronization, the user’s data will be synced to a user-specific schema. Your app can then retrieve/modify data in that schema. Repeat step 4 and 5 when another user logs on to your local app.

The Demo Application

There is a server piece and a client piece to the demo application. They are in the demo directory of the sync server home and sync client home respectively. In the demo directory you will find a java file, an ini file and two shell script files with suffix bat and sh. The java file reads the ini file for site-specific configurations. To facilitate the compilation and execution of the java file, we created the two shell script files, which will take care of things like setting the CLASSPATH. However, you still need to download the JDBC driver or Java connector jar and include them in CLASSPATH first.

Tasks except for compile require the parameters in the ini file to be set to appropriate values.

NOTE: On both server and client, there are a demo app for Oracle database and a demo app for MySQL database. In the following we use Oracle database as an example.

The Application Scenario

This application is for a virtual company to synchronize a manager’s device DB with the company central DB. The company has multiple departments and each department has several managers. The central DB schema contains the following tables: EXECUTIVES, DEPARTMENTS, MANAGERS, EMPLOYEES and TASKS. The device DB schemas are expected to have the same tables and data except for EMPLOYEES and TASKS tables, which would contain a sub-set of data, only belong to the manager’s department.

The Server Piece of the Application

On Windows machines, open a shell window, go to C:\pervasync_server-4.0.0\demo and execute server_app_oradb.bat you will get the usage:

server_app_oradb.bat {compile | createschema | dropschema | publish | unpublish | insert | delete | update}

On Linux/Unix machines, Open a shell window, go to /pervasync_server-4.0.0/demo and execute server_app_oradb.sh you will get the usage:

server_app_oradb.sh {compile | createschema | dropschema | publish | unpublish | insert | delete | update}

You can see that the shell scripts take one command-line argument, each of which would carry out a sub-task.

The parameter File: server_app_oradb.ini

Make sure the parameter file contains complete and correct information before you execute any of the sub-tasks.

Compile the Application

Note: To compile the application, you need to have Java Development Kit (JDK) installed. Make sure the “bin” folder of JDK is included in the value of environment variable “Path”. Also, you need to download the JDBC driver or Java connector jar and include them in CLASSPATH.

To compile ServerAppOradb.java to ServerAppOradb.class, execute the compile sub-command.

Windows:

server_app_oradb.bat compile

Linux/Unix bash:

server_app_oradb.sh compile

Create and Drop the Application Schema

The createschema sub-command is used to create the server side application schema. Dropschema will do the opposite.

Windows:

server_app_oradb.bat createschema

server_app_oradb.bat dropschema

Linux/Unix bash:

server_app_oradb.sh createschema

server_app_oradb.sh dropschema

Publish and Unpublish

The publish sub-command is used to define the sync objects so that they can be synchronized to device DBs. Unpublish would do the opposite.

Windows:

server_app_oradb.bat publish

server_app_oradb.bat unpublish

Linux/Unix bash:

server_app_oradb.sh publish

server_app_oradb.sh unpublish

publish invokes the method publish() in ServerAppOradb.java. The sync server API is in a class named pervasync.server.SyncServerAdmin. Refer to the javadoc for the usages of the API. We will describe here briefly how we use it in the demo app. First we create an object out of SyncServerAdmin:

SyncServerAdmin syncServerAdmin = new SyncServerAdmin();

To publish the application schema and tables for synchronization, we call the addSchema() and addTable() methods of SyncServerAdmin. To define a sync table, the API takes a primary key query as an argument to select all or a sub-set of table records for synchronization. The primary key query could have parameters. For example, the query for table TASKS has a parameter named DEPT_ID. You need to supply values for the parameters at subscription time.

// add sync schema

syncServerAdmin.addSyncSchema( “schema1”,

syncServerDbAppSchema, syncClientDbAppSchema, null);

// add sync tables

syncServerAdmin.addSyncTable(“schema1”, “DEPARTMENTS”, 1, null, “SELECT ID FROM ” + syncServerDbAppSchema + “.DEPARTMENTS”);


syncServerAdmin.addSyncTable(“schema1”, “TASKS”, 3, null, “SELECT EMP_ID,ID FROM ” + syncServerDbAppSchema + “.TASKS WHERE ” + “EMP_ID IN (SELECT ID FROM ” + syncServerDbAppSchema + “.EMPLOYEES WHERE DEPT_ID=:DEPT_ID)”);

A user can then subscribe to the published sync schema so that the user’s device DB can have a synchronized DB schema.

Now let’s create a user. We have a flexible mechanism for customers to plug in their own user management system. Refer to Javadoc for sync.UserManager and the sync server configuration sections for how to plug in your user manager. In the demo, we use the built-in user manager: sync.server.oracle.DefaultUserManager to create users:

defaultUserManager = new DefaultUserManager();

defaultUserManager.createUser(syncUserName, syncUserPassword);

Each user could have multiple device DBs. Here we add one:

syncServerAdmin.addSyncClient(syncUserName, “DEFAULT”);

The subscription is an association of a sync schema with a user device DB. As mentioned earlier, you need to supply values for the primary key query parameters at subscription time:

HashMap paramNameValMap = new HashMap();

paramNameValMap.put(“DEPT_ID”, iDept + “”);

syncServerAdmin.addSubscription(“schema1”,

syncUserName, “DEFAULT”, paramNameValMap);

After publish, you are ready to initiate synchronization from the client side.

To un-subscribe and un-publish, you call the remove methods.

DML Operations on the Server App Schema

The insert, delete and update tasks can be used to do DML changes to the central app schema. You can use this to test server to client incremental sync.

The Client Piece of the Application

On Windows machines, open a shell window, go to C:\pervasync_client-4.0.0\demo and execute client_app_oradb.bat you will get the usage:

client_app_oradb.bat {compile | dosync | insert | delete | update}

On Linux/Unix machines, Open a shell window, go to /pervasync_client-4.0.0/demo and execute client_app_oradb.sh you will get the usage:

client _app_oradb.sh {compile | dosync | insert | delete | update}

You can see that the shell scripts take one command-line argument, each of which would carry out a sub-task.

The parameter File client_app_oradb.ini

Make sure the parameter file contains complete and correct information before you execute any of the sub-tasks.

Compile the Application

To compile ClientAppOradb.java to ClientAppOradb.class, execute the compile sub-command.

Windows:

client_app_oradb.bat compile

Linux/Unix bash:

client_app_oradb.sh compile

Synchronize with Server

The dosync sub-command is used to invoke a sync with the sync server.

Windows:

server_app_oradb.bat dosync

Linux/Unix bash:

server_app_oradb.sh dosync

sync would create a ClientSyncSession object and invoke its sync() method. Following is what is done in ClientAppOracle.java:

ClientSyncSession syncSession = new ClientSyncSession(null);

syncSession.sync();

DML Operations on the Device App Schema

The insert, delete and update tasks can be used to do DML changes to the device app schema. You can use this to test client to server incremental sync.

Trouble Shooting

Out of Memory Errors

When you sync large amount of data, you may encounter some memory exceptions running the script, for example

java.lang.OutOfMemoryError: Java heap space

Adjusting Sync Client Heap Memory Size

If the exception was thrown by sync client, you can edit the pvc.sh/pvc.bat script file to adjust the maximum heap memory size, i.e. change the value of option –Xmx.

Start javaw –Xmx512m –classpath %myclasspath% pervasync.client.gui.SyncClient

Adjusting Sync Server Heap Memory Size

If the exception was thrown by the server, try adjusting the server heap space size. Consult your servlet container or application documentation for how to adjust the maximum heap space size. For example, for Tomcat you can put the following at the beginning of <Tomcat Home>/bin/catalina.bat:

set JAVA_OPTS=%JAVA_OPTS% -Xmx1024m

or put the following at the beginning of <Tomcat Home>/bin/catalina.bat after #!/bin/sh,

JAVA_OPTS=$JAVA_OPTS –Xmx1024m

If you install Tomcat as a Windows service, these settings are kept in the Windows Registry.
If you have the start menu items, the easiest way to change them is to go to
Start -> Programs -> Apache Tomcat -> Tomcat Configuration -> Java (tab).
There are fields for your initial and max heap sizes.

Setting maximum heap memory (Xmx) too small may cause Pervasync server to fail with OutOfMemoryError: Java heap space. Setting it too big may cause other native programs, including MySQL server and Tomcat itself(!) to crash with OutOfMemoryError.

If you still get memory exceptions on server, locate the config file, e.g. pervasync_client_mysql.conf, under <Pervasync Client Home>/config and make sure Json is used for transport:

pervasync.client.transport.serialization=Json

Also try reducing the max message size from 10MB to, say 2 MB:

pervasync.max.message.size=2000000

MySQL Innodb Lock Wait Timeout Error

You may occasionally see the following error that is encountered by sync engine:

java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

This was caused by sync engine taking a long time to finish one processing cycle. Sync engine is a batch-processing process that can potentially have long running transactions. MySQL Innodb has a transaction timeout with a relatively small default value of 50 seconds. If sync engine takes more than 50 seconds, the transaction could timeout and this error would be thrown.

Try increasing the MySQL Innodb timeout value to give sync engine more time. In my.ini or my.conf, under section [mysqld], add or edit parameter innodb_lock_wait_timeout.

[mysqld]

# How long an InnoDB transaction should wait for a lock to be granted

# before being rolled back. Default 50 seconds.

Innodb_lock_wait_timeout = 600

Restart MySQL DB and Tomcat for the new value to take effect.

Another thing to consider is to speed up the sync engine. Sync engine takes more time to process COMPLEX sync tables than SIMPLE sync tables. Try converting the COMPLEX sync tables to SIMPLE ones.

Sync Didn’t Bring Down Newly Added/Updated Records

The scenario:

Update the server database (add new record). Immediately (within a few seconds), do a SYNC operation from the client. Look at client database – the new record is not there. Wait a little bit and do a SYNC operation again. Now the new record is in the client database.

The explanation:

This is normal. There is a sync engine running on the server to periodically take snapshots of the DB. When client syncs with server, data is retrieved using the latest snapshot. The default sync engine run interval is 30 seconds (you can check and/or change this value on web admin console), so it is possible that during sync, a client is using a 30-second old snapshot that does not include changes in the last 30 seconds.

The sync engine and snapshot mechanism is the basis of the consistency, performance and scalability of the sync system. The lag normally is not an issue. Sync client is most times offline so the client data is hours or days old depending on the sync interval. So it doesn’t matter whether it’s 30-second older.

Also the sync engine interval can be set smaller to achieve a near real-time result.

Appendix

A.1        Installing Java JDK on Windows

Pervasync requires a Java platform to run. You may already have Java on your Windows host. To check this, do the following in a command window:

java –version

If java is not found or the output indicates the version is older than 1.6, you need to upgrade to Oracle/Sun JDK 1.6 or newer.

Download Windows exe installer of the latest JDK at:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

Before you run the installer, make sure you log in as a user with administrative permissions. Follow the instructions the installer provides. The installer may ask you to reboot your computer. Take a note of where you install the JDK. By default, it is C:\Program Files\Java\jdk1.6.0_<version>. That would be the Java home.

Now if you do “java –version” on command line, you may still see the old version. You have to set the PATH variable on command line to let the system pick the newly installed JDK, e.g.

set PATH=c:\Program Files\Java\jdk1.6.0_18\bin;%PATH%

java –version

We recommend you set the PATH permanently so it will persist after rebooting. To set the PATH permanently:

  • Click Start > Control Panel > System on Windows XP or Start > Settings > Control Panel > System on Windows 2000.
  • Click Advanced > Environment Variables.
  • Add the location of bin folder of JDK installation for PATH in System Variables. A typical value for PATH is: C:\Program Files\Java\jdk1.6.0_<version>\bin;…

PATH environment variable is a series of directories separated by semi-colons (;) and is not case sensitive. Microsoft Windows looks for programs in the PATH directories in order, from left to right.

You should only have one bin directory for a JDK in the path at a time. Those following the first instance are ignored. If one is already present, update it to jdk1.6.0_<version>\bin.

The new path takes effect in each new command window you open after setting the PATH variable.

A.2        Installing Apache Tomcat Service on Windows

Before installing Tomcat, you need to setup Java, for which refer to the previous section.

For Tomcat on Windows, you have a choice of a standalone install or a service install. For standalone install, you download and extract the zip file to a folder and then use the startup.bat and shutdown.bat scripts in the bin sub-folders to start/stop Tomcat. The service install will install Windows native executables plus a Windows service so that Tomcat can be auto-started at Windows boot time. We recommend you choose the service install.

Download the latest Tomcat 6.x version from http://tomcat.apache.org/download-60.cgi. Choose “Binary Distributions”, à “Core” à32-bit/64-bit Windows Service Installer“, e.g. apache-tomcat-6.0.18.exe and run.

Follow the instructions to complete the installation. Take note of the installation folder, default C:\Program Files\Apache Software Foundation\Tomcat 6.0. This is the Catalina/Tomcat home.

Once installed, you will have “Apache Tomcat 6.0” on Windows start menu where you can click to launch Tomcat configure GUI. One thing you may want to do with it is to go to the “Java” tab and set the max memory pool to a reasonably big value like 1024 MB.

A.3        Installing Standalone Java JDK on Linux

Pervasync requires a Java platform to run. If you have a Linux host, you may already have Java on it. A lot of Linux distributions come with open source OpenJDK or gcj pre-installed. However, Pervasync works best with Oracle/Sun JDK. There have been reports that Pervasync failed to install with certain versions of OpenJDK and gcj. While OpenJDK shares 99% code with Oracle/Sun JDK, to tell you the truth, Oracle/Sun JDK is much more stable and robust than OpenJDK and gcj. So you should install Oracle/Sun JDK 1.6 or newer to have a smooth run of Pervasync.

To check what you have on your host, do the following in a shell window:

java –version

If the output contains “OpenJDK” or “gcj”, or the version is older than 1.6, you need to upgrade to Oracle/Sun JDK 1.6 or newer.

There are different editions of Oracle/Sun Java. You need Java Standard Edition (Java SE), which you can download at:

http://www.oracle.com/technetwork/java/javase/downloads/index.html

For Linux, you have a choice of RPM based package install or a standalone install. Select the multiplatform binary for a standalone install, for example, jdk-6u18-linux-i586.bin. This one will work for most, if not all, Linux distributions while the RPM one will not. Your host may not support RPM packages.

Downloaded the binary file to /tmp and installed it as follows as root. The reason to install as root is that you want to install Java under a system folder so that it is available to everybody. People usually install JDK under /usr/share or /usr/java.

$ sudo su root

# cd /usr/share

#

# chmod 700 /tmp/jdk-6u18-linux-i586.bin

# /tmp/jdk-6u18-linux-i586.bin

Type q and yes to accept license and let the installation go through. Now you have the JDK installed in Java home /usr/java/jdk1.6.0_05!

To use the newly installed Java, you need to add its bin dir to the front of the PATH env variable:

# export JAVA_HOME=/usr/share/jdk1.6.0_05

# export PATH=$JAVA_HOME/bin:$PATH

#

# which java

/usr/share/jdk1.6.0_05/bin/java

# java -version

java version “1.6.0_05”

Java(TM) SE Runtime Environment (build 1.6.0_05-b13)

Java HotSpot(TM) Client VM (build 10.0-b19, mixed mode)

To set the PATH at boot time, create file jdk.sh in /etc/profile.d:

# cd /etc/profile.d

# vi /etc/profile.d/jdk.sh

Then add the following lines to jdk.sh:

export JAVA_HOME=/usr/share/jdk1.6.0_05/

export PATH=$JAVA_HOME/bin:$PATH

A.4        Installing Standalone Apache Tomcat on Linux

Pervasync server needs a web server that supports Java web applications. Apache Tomcat is a popular choice. It is possible that your Linux host already has Tomcat installed. However, Pervasync requires Tomcat 6 or newer. You need to do an install if you have Tomcat 5 or older.

A.4.1    Installing Tomcat Software

Before installing Tomcat, you need to have Java installed, for which refer to the previous section. Like Java, for Tomcat you have a choice of a standalone or a package install. Package install will distribute Tomcat files to multiple Linux folders, e.g. /etc for configuration files, /var/lib for variable application state files and /usr/bin for read-only execution files etc. The problem with package install is that it might be hard to find an up-to-date Tomcat package for your particular Linux distribution. Here we describe how to do a standalone install, which is also our recommendtion.

Download the latest Tomcat 6.x version from http://tomcat.apache.org/download-60.cgi. Choose “Binary Distributions”, à “Core” à “tar.gz”, e.g. apache-tomcat-6.0.18.tar.gz and download to /tmp.

We will do the installation as root. If any of the commands cannot be found, try adding /sbin/ and /usr/sbin/ to PATH.

$ sudo su root

For security reasons we do not recommend running Tomcat as root. So we create a user account “tomcat” for Tomcat.

# groupadd tomcat

# useradd -g tomcat -d /home/tomcat tomcat

Next extract the tar file to /home/tomcat and changed the ownership of all files and directories to tomcat:

# cd /home/tomcat

# tar zxvf /tmp/apache-tomcat-6.0.18.tar.gz

# makdir –p /home/tomcat/apache-tomcat-6.0.18/logs

# chown -R tomcat:tomcat /home/tomcat/apache-tomcat-6.0.18

The get the Tomcat version of the newly installed Tomcat, run:

# /home/tomcat/apache-tomcat-6.0.18/bin/version.sh

Using CATALINA_BASE: /home/tomcat/apache-tomcat-6.0.18

Using CATALINA_HOME: /home/tomcat/apache-tomcat-6.0.18

Using CATALINA_TMPDIR: /home/tomcat/apache-tomcat-6.0.18/temp

Using JRE_HOME: /usr/share/jdk1.6.0_05

Server version: Apache Tomcat/6.0.18

Server built: Jul 22 2008 02:00:36

Server number: 6.0.18.0

OS Name: Linux

A.4.2    Starting/Stopping Tomcat

Now try to startup the Tomcat server. Note that we switch user to run it as user “tomcat”.

# export JAVA_HOME=/usr/share/jdk1.6.0_05

# export PATH=$JAVA_HOME/bin:$PATH

#

# su –l –p tomcat -c /home/tomcat/apache-tomcat-6.0.18/bin/startup.sh

Verify that Tomcat was started successfully by opening the URL http://localhost:8080 (Port number 8080 is the default port used by Tomcat). Note that you should also be able to use the name of your server instead of localhost. Once you opened the URL in your browser you should see Tomcat’s Congratulation page.

To stop Tomcat, run shutdown.sh:

# su –l –p tomcat -c /home/tomcat/apache-tomcat-6.0.18/bin/shutdown.sh

Check the log files in /home/tomcat/apache-tomcat-6.0.18/logs if things do not go as expected.

A.4.3    Adding Tomcat as a Linux Service

To add Tomcat as a Linux service so that it can be auto started at boot time, create a startup script.

# vi /etc/init.d/tomcat

Then paste the following as content. Note that you may need to edit the Java and Tomcat home paths if yours are different. Also don’t remove the comment lines for chkconfig.

#!/bin/sh

#

# Startup script for Tomcat Servlet Engine

# chkconfig: 345 86 14

# description: Tomcat Servlet Engine

# processname: tomcat

#

# Java and Tomcat homes

export JAVA_HOME=/usr/share/jdk1.6.0_05

export PATH=$JAVA_HOME/bin:$PATH

export TOMCAT_USER=tomcat

export CATALINA_HOME=/home/tomcat/apache-tomcat-6.0.18

export CATALINA_BASE=$CATALINA_HOME

RETVAL=0

# start, debug, stop, and status functions

start() {

# Start Tomcat in normal mode

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

if [ $SHUTDOWN_PORT -ne 0 ]; then

echo “Tomcat already started”

else

echo “Starting tomcat…”

mkdir -p $CATALINA_HOME/logs

chown -R $TOMCAT_USER:$TOMCAT_USER $CATALINA_HOME

su -l -p $TOMCAT_USER -c “$CATALINA_HOME/bin/startup.sh”

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

while [ $SHUTDOWN_PORT -eq 0 ]; do

sleep 1

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

done

RETVAL=$?

echo “Tomcat started in normal mode”

[ $RETVAL=0 ] && touch /var/lock/subsys/tomcat

fi

}

debug() {

# Start Tomcat in debug mode

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

if [ $SHUTDOWN_PORT -ne 0 ]; then

echo “Tomcat already started”

else

echo “Starting tomcat in debug mode…”

chown -R $TOMCAT_USER:$TOMCAT_USER $CATALINA_HOME

su -l -p $TOMCAT_USER -c “$CATALINA_HOME/bin/catalina.sh jpda start”

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

while [ $SHUTDOWN_PORT -eq 0 ]; do

sleep 1

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

done

RETVAL=$?

echo “Tomcat started in debug mode”

[ $RETVAL=0 ] && touch /var/lock/subsys/tomcat

fi

}

stop() {

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

if [ $SHUTDOWN_PORT -eq 0 ]; then

echo “Tomcat already stopped”

else

echo “Stopping tomcat…”

su -l -p $TOMCAT_USER -c “$CATALINA_HOME/bin/shutdown.sh”

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

while [ $SHUTDOWN_PORT -ne 0 ]; do

sleep 1

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

done

RETVAL=$?

echo “Tomcat stopped”

[ $RETVAL=0 ] && rm -f /var/lock/subsys/tomcat $CATALINA_HOME/bin/tomcat.pid

fi

}

status() {

SHUTDOWN_PORT=`netstat -vatn|grep LISTEN|grep 8005|wc -l`

if [ $SHUTDOWN_PORT -eq 0 ]; then

echo “Tomcat stopped”

else

MODE=”normal”

JPDA_PORT=`netstat -vatn|grep LISTEN|grep 8000|wc -l`

if [ $JPDA_PORT -ne 0 ]; then

MODE=”debug”

fi

echo “Tomcat running in $MODE mode”

fi

}

case “$1” in

start)

start

;;

debug)

debug

;;

stop)

stop

;;

restart)

stop

start

;;

redebug)

stop

debug

;;

status)

status

;;

*)

echo “Usage: $0 {start|debug|stop|restart|redebug|status}”

exit 1

esac

exit $RETVAL

Then use chkconfig command to add and turn on the tomcat service:

# chkconfig –add tomcat

# chkconfig tomcat on

# chkconfig –list tomcat

A.4.4    Relaying HTTP Port 80 Connections to Tomcat Port 8080

By default, Tomcat listens on port 8080. To have the Tomcat server itself listen on HTTP port 80, Tomcat would have to run as root since only root can listen on ports below 1024 on Linux. But for security reasons this is not recommended.

One solution would be to use a service wrapper like jsvc from the Jakarta Commons Daemon project. But that solution would require the installation and maintenance of another piece of software on your system.

Here we describe the steps to relay port 80 TCP connections to port 8080 using the Netfilter package that already comes with Linux and is transparent to Tomcat. Execute the following commands as root:

# iptables -t nat -I PREROUTING -p tcp –dport 80 -j REDIRECT –to-ports 8080

# iptables -t nat -I OUTPUT -p tcp –dport 80 -j REDIRECT –to-ports 8080

The first rule redirects incoming requests on port 80 generated from other computer nodes and the second rule redirects incoming requests on port 80 generated from the local node where Tomcat is running.

Rules created with the iptables command are only stored in RAM. To save them so that they will still work on re-boot, do the following as root:

# service iptables save

This causes the iptables init script to run the /sbin/iptables-save program and write the current iptables configuration to the /etc/sysconfig/iptables file.

It should be noted here that there is one Tomcat configuration parameter that you may want to change, the proxyPort parameter in the server.xml file. Since Tomcat still receives requests on port 8080 as they are relayed by the Linux Netfilter system from port 80, Tomcat may display port 8080 in the URL depending on the application’s content. So if you want to change it to port 80, the proxyPort parameter would need to be added in the $CATALINA_BASE/conf/server.xml file for port 8080:

<Connector port=”8080″ protocol=”HTTP/1.1″ proxyPort=”80″

connectionTimeout=”20000″

redirectPort=”8443″ />

After that you need to restart Tomcat to make this change effective.

ervasync Client on Windows as a Scheduled Task

Pervasync client has a tab named “Schedule” , where you can schedule sync jobs to run at specific times and/or intervals. The job schedules are persisted and if you do not hit the “Cancel” button, the jobs would resume after you restart the client.

Now the problem is, you still need to start the sync client in order for the synchronizations to happen. This section describes how to automatically start the client after Windows re-boot or user logon.

From Windows “Start” menu, go to “Control Panel” and click “Scheduled Tasks”.

Double click “Add Scheduled Task”.

Click on “Next”.

Click on “Browse…” and locate pvc.bat.

Click “pvc.bat” and then “Open”.

NOTE: To invoke pvc.bat with argument “sys_tray” or “auto_sync”, you can create a new batch file, say auto_sync.bat and put “pvc.bat auto_sync” in it and then pick to run auto_sync.bat.

Choose “When my computer starts” or “When I log on” and click “Next”.

Enter the username and password.

Remember to check “Open advanced properties …” before you click “Finish”. Or you can right click the scheduled task and choose “Properties”. Go to the “Settings” tab.

Uncheck “Stop the task if it runs for…” under “Scheduled Task Completed” so that the Pervsync client will keep running. The client won’t consume any CPU when there is no synchronization going on.

After re-boot, you can check the status of the scheduled task in “Control Panel”->”Scheduled Tasks”. For more detailed info of the synchronization, check the sync history on web admin console and/or the client/server log files.

Bibliography

[1] Weiser M. The computer for the twenty-first century. In Scientific

American, September 1991, pp. 94-104.

[2] Franklin M J. Challenges in Ubiquitous Data Management. In Informatics – 10 Years Back. 10 Years Ahead.
Lecture Notes In Computer Science 2000, Wilhelm R (eds.), Springer-Verlag, 2001, pp. 24-33.

[3] Gray J, Helland P, O’Neil P, Shasha D. The dangers of replication and a solution. SIGMOD Rec., 1996, 25(2): 173-182.

[4] Ding Z, Meng X, Wang S. A Transactional asynchronous replication scheme for mobile database systems. J. Comput. Sci. Technol., 2002, 17(4): 389-396.

[5] Saito Y, Shapiro M. Optimistic replication. ACM Comput. Surv., 2005, 37(1): 42-81.

Last revised: August 9th, 2011

Copyright © 2008-2011, Pervasync Software. All Rights Reserved.