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.

Advertisements

How to Checkout and Build Apache Trinidad

1. Download Maven from http://maven.apache.org/download.cgi. Un-zip Maven so that you have the mvn tool at, e.g. ~/apache-maven-3.2.3/bin/

2. Check out Apache Trinidad from its SVN repository

svn co http://svn.apache.org/repos/asf/myfaces/trinidad-maven/trunk trinidad-maven
svn co http://svn.apache.org/repos/asf/myfaces/trinidad/trunk/ trinidad

3. Update trinidad pom.xml with new trinidad-maven version

<trinidad-plugins.version>2.0.9-SNAPSHOT</trinidad-plugins.version>

4. Use Maven to build Trinidad

cd ~/trinidad-maven/
~/apache-maven-3.2.3/bin/mvn -Dmaven.test.failure.ignore=true clean install

cd ~/trinidad/
~/apache-maven-3.2.3/bin/mvn -Dmaven.test.failure.ignore=true clean install

5. Find trinidad-impl-2.1.1-SNAPSHOT.jar in trinidad-impl/target/

ls trinidad-impl/target/

Links to download the build results

trinidad-impl-2.1.1.jar

trinidad-api-2.1.1.jar

Add Private HTTPS certificate to an Oracle Mobile Application Framework (MAF) App

If you get this error:

“Certificate was issued by an unrecognized entity”

when connecting an Oracle Mobile Application Framework (MAF) app with a server via HTTPS, you need to retrieve and package the server certificate with the app.

The Oracle MAF cacerts file identifies a set of certificates from well-known and trusted sources to JVM 1.4 and enables deployment. For an application that requires custom certificates (such as in cases where RSA cryptography is not used), you must add private certificates before deploying the application.

  1. Retrieve the cert from the server.
    openssl s_client -connect <server>:<port> -showcerts

    Copy-paste the certificate to a file “cert_file”: the output between —–BEGIN CERTIFICATE—— and —–END CERTIFICATE——, inclusive.

  2. Add the cert to the certs store.
    Change directory to <MAF app root>/resources/security. There is a “carets” file there.

     keytool -importcert -keystore cacerts -file cert_file -storepass changeit -noprompt
  3. Redeploy

NullPointerException When Running an Apache Trinidad App in Internet Explorer 11 (IE 11)

When running a web app built with Apache Trinidad in IE 11, you may encounter NullPointerException.

java.lang.NullPointerException
at org.apache.myfaces.trinidadinternal.ui.RootRenderingContext.getRendererManager(RootRenderingContext.java:193)
at org.apache.myfaces.trinidadinternal.uinode.UIXComponentUINode.getRenderer(UIXComponentUINode.java:379)
at org.apache.myfaces.trinidadinternal.uinode.UIXComponentUINode.renderInternal(UIXComponentUINode.java:166)
at org.apache.myfaces.trinidadinternal.uinode.UINodeRendererBase.encodeEnd(UINodeRendererBase.java:70)
at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:839)

Same error with latest builds of Apache Trinidad versions 1.0, 1.2 and 2.0. This issue was actually fixed in Apache Trinidad 2.1 which you don’t currently see it on the official download page:

https://myfaces.apache.org/trinidad/download.html

To upgrade to Apache Trinidad 2.1 so that your app can run in IE 100 as well as other browsers, do the following.

1. Download Apache Trinidad 2.1 from Maven repository:

http://search.maven.org/#artifactdetails|org.apache.myfaces.trinidad|trinidad-impl|2.1.0|jar

2. Download the required compatible versions of MyFaces 2.1.x. Uppack the zip package to find the jars.

https://myfaces.apache.org/download.html

3. Download the required compatible versions of JSTL 1.2:

http://download.java.net/maven/1/jstl/jars/

4. Copy the jars to your WEB-INF folder. Remove older versions of Apache Trinidad/MyFaces and JSTL.

Pervasync in the Cloud – Setting up Pervasync with Amazon EC2 & RDS

Following is a tutorial for setting up Pervasync server in the Amazon cloud so that you can sync your mobile devices with Oracle, MySQL or SQL Server databases hosted in Amazon cloud.

 Preparing the AWS Env

1. Create an AWS account at http://aws.amazon.com/ec2/

2. Create a Beanstalk env to have a running Tomcat instance and a RDS database instance: http://aws.amazon.com/elasticbeanstalk/.

Setup the DB security group so that the DB instance can be accessed from the EC2 instance where Tomcat is hosted.

3. Create your app database, schema and tables. For MS SQL Server, connect to the DB server and create a database for your application’s DB schemas and tables. The DB name will be used as value of pervasync.server.db.database.name during Pervasync setup. For Oracle you supply the DB name during DB instance creation. The DB name will be the Oracle SID to be used in DB JDBC URL. For MySQL the RDS DB name is really the schema name.

4. For MySQL, create a DB Parameter Group and set the value of parameterlog_bin_trust_function_creators to 1. Modify the MySQL instance and set its parameter group to the one with log_bin_trust_function_creators set to 1. Reboot the DB instance.

Alternately to Beanstalk, you could also directly create an EC2 instance and RDS instance. To install Java & Tomcat on your EC2 instance, see
http://www.excelsior-usa.com/articles/tomcat-amazon-ec2-basic.html

Installing Pervasync in the AWS Env

Note: You need to substitute the host name, password etc. with yours in the following.

1. Download pervasync_server-5.0.3.zip from
https://www.dropbox.com/s/4uwhvo99j7ra2xc/pervasync_server-5.0.3.zip

2. Copy the zip file to your Amazon EC2 instance home folder:
scp -i keypair1.pem pervasync_server-5.0.3.zip ec2-user@ec2-54-214-22-102.us-west-2.compute.amazonaws.com:/home/ec2-user

3. Log on to your Amazon EC2 instance
ssh -i keypair1.pem ec2-user@ec2-54-214-22-102.us-west-2.compute.amazonaws.com

4. From now on act as root user. Un-package the zip in /usr/share/:
sudo su root
cd /usr/share/
unzip /home/ec2-user/pervasync_server-5.0.3.zip

5. Go to non-GUI install folder for your DB type, for example for SQL Server:
cd pervasync_server-5.0.3/util/mssql/

Edit pervasync_server_mssql.ini to supply your DB connection info:

 #=========================================================================
 #== Pervasync server for MS SQL Server ==
 #== ini file for setup/uninstall ==
 #== ==
 #== Property values are needed and only needed when you run ==
 #== the setup/uninstall scripts. After you are done with ==
 #== setup/uninstall, you can erase sensitive info from this file. ==
 #=========================================================================
# The JDBC URL to the Pervasync server repository database.
 # Replace "localhost" with the DB host name or IP if DB is not on the same host
 # For AWS RDS, replace "localhost" with the DB Endpoint
pervasync.server.db.url=jdbc:sqlserver://db1.cinuuz0ecpzz.us-west-2.rds.amazonaws.com:1433
# The database name. Replace "master" with the database name you created for your app
pervasync.server.db.database.name=myappdb
# Name and password of a DB user with root privileges.
 # This user/account should be pre-existent.
 # For AWS RDS, use the master user
pervasync.server.db.system.user=master
 pervasync.server.db.system.password=welcome1234!
# PervaSync server admin user name and password. You would need to use this
 # user/password pair to login to the web-based Pervasync admin console.
 # If not already exist, a database user/schema with this name will be created
 # in Pervasync server DB repository at setup time. At un-install time, the user/schema
 # will be dropped.
pervasync.server.admin.user=pvsadmin
pervasync.server.admin.password=welcome1234!

6. Run the setup
./pervasync_server_mssql_setup.sh

7. Install Pervasync server web app to Tomcat
chown -R tomcat:tomcat /usr/share/pervasync_server-5.0.3
cp /usr/share/pervasync_server-5.0.3/config/Catalina/localhost/pervasync.xml /usr/share/tomcat7/conf/Catalina/localhost/
vi /usr/share/tomcat7/conf/Catalina/localhost/pervasync.xml

8. Log on to Pervasync web admin console
http://default-environment-ddffdffd.elasticbeanstalk.com/pervasync

If not working, check Tomcat log:
vi /usr/share/tomcat7/logs/catalina.out

Check Pervasync log: vi /usr/share/pervasync_server-5.0.3/log/pervasync_server_mssql.log

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.

 

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);

  2. 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.
  3. 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, for Oracle database you do the following in your client application.

    INSERT INTO pvcdemo.table1(c1, c2) VALUES

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

     

For MySQL database you do the following in your client application.

INSERT INTO pvcdemo.table1(c1, c2) VALUES

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

For SQLite databases on Android or Blackberry, use the SyncClient method to retrieve the sequence next value and use it as key value for a new record. Note that the first argument is the logical sync schema name, not the client schema/database name. See API Javadoc for more details.

long idValue = pevasync.client.SyncClient.sequenceNextval(“schema1”, “task_id_seq”);

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.