Thought Leadership

Android File Sync in SymmetricDS 3.8

SymmetricDS has supported syncing data to and from Android SQLite databases for some time now. But with the 3.8 series, we now also have support for pushing and pulling files from your Android devices. This could be useful to collect things like signature files captured on the device, log files, or to push files down to your devices as well. Maybe you have a company materials you want to make available offline for instance. Whatever your use case, SymmetricDS now supports bi-directional file sync on the Android platform.

For the remainder of this blog, I want to take you through a step by step (more or less) guide to set up an Android client with SymmetricDS, and to show how to configure and test file sync. If you’re only interested in the file sync portion, skip all the way down to step 10.

You can download the entire project and source code from the example here.

Please make sure to go this this tutorial with SymmetricDS 3.8.4 or greater. 

  1. Download the SymmetricDS package for Android. 
    https://sourceforge.net/projects/symmetricds/files/symmetricds/symmetricds-3.8/symmetric-android-3.8.7.zip/download
  2. Create a new, empty project in Android Studio.

I target a lower SDK for more compatibility and less issues with file system permissions.

  1. Add the .jar files from the SymmetricDS Android package to your Android studio project. In my case, this meant copying the libs into the my ./app/libs directory.

  1. Add the following permissions to the AndroidManifest.xml.
    <uses-permission android:name="android.permission.INTERNET" />
    <uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
    <uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />
    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

  1. Next, we’ll add the DbProvider and start hooking things up with the local SQLite database. This class is also responsible for configuring SymmetricDS.
        <provider android:name="DbProvider"
            android:authorities="com.jumpmind.symmetric.DbProvider"
            android:exported="false">
            <grant-uri-permission android:pathPattern=".*" />
        </provider>

Add the corresponding DbProvider class.

package com.jumpmind.symds3;


import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.Uri;
import android.support.annotation.Nullable;

import org.jumpmind.symmetric.android.SQLiteOpenHelperRegistry;
import org.jumpmind.symmetric.android.SymmetricService;
import org.jumpmind.symmetric.common.ParameterConstants;

import java.util.Properties;

public class DbProvider extends ContentProvider  {

    private final String REGISTRATION_URL = "http://192.168.1.103:31415/sync/corp-000";
    private final String NODE_ID = "android-003";
    private final String NODE_GROUP = "store";

    final String SQL_CREATE_TABLE_ITEM = "CREATE TABLE IF NOT EXISTS ITEM(\n" +
            "    ITEM_ID INTEGER NOT NULL PRIMARY KEY ,\n" +
            "    NAME VARCHAR\n" +
            ");";

    final String SQL_CREATE_TABLE_ITEM_SELLING_PRICE = "CREATE TABLE IF NOT EXISTS ITEM_SELLING_PRICE(\n" +
            "    ITEM_ID INTEGER NOT NULL,\n" +
            "    STORE_ID VARCHAR NOT NULL,\n" +
            "    PRICE DECIMAL NOT NULL,\n" +
            "    COST DECIMAL,\n" +
            "    PRIMARY KEY (ITEM_ID, STORE_ID)\n" +
            ");";

    final String SQL_CREATE_TABLE_SALE_TRANSACTION = "CREATE TABLE IF NOT EXISTS SALE_TRANSACTION(\n" +
            "    TRAN_ID INTEGER NOT NULL PRIMARY KEY ,\n" +
            "    STORE_ID VARCHAR NOT NULL,\n" +
            "    WORKSTATION VARCHAR NOT NULL,\n" +
            "    DAY VARCHAR NOT NULL,\n" +
            "    SEQ INTEGER NOT NULL\n" +
            ");\n";

    final String SQL_CREATE_TABLE_SALE_RETURN_LINE_ITEM = "CREATE TABLE IF NOT EXISTS SALE_RETURN_LINE_ITEM(\n" +
            "    TRAN_ID INTEGER NOT NULL PRIMARY KEY ,\n" +
            "    ITEM_ID INTEGER NOT NULL,\n" +
            "    PRICE DECIMAL NOT NULL,\n" +
            "    QUANTITY INTEGER NOT NULL,\n" +
            "    RETURNED_QUANTITY INTEGER\n" +
            ");\n";

    public static final String DATABASE_NAME = "symmetric-demo.db";

    // Handle to a new DatabaseHelper.
    private DatabaseHelper mOpenHelper;

    /**
     *
     * This class helps open, create, and upgrade the database file. Set to package visibility
     * for testing purposes.
     */
    static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {

            // calls the super constructor, requesting the default cursor factory.
            super(context, DATABASE_NAME, null, 2);
        }


        @Override
        public void onCreate(SQLiteDatabase db) {
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            onCreate(db);
        }
    }

    /**
     *
     * Initializes the provider by creating a new DatabaseHelper. onCreate() is called
     * automatically when Android creates the provider in response to a resolver request from a
     * client.
     */
    @Override
    public boolean onCreate() {

        // Creates a new helper object. Note that the database itself isn't opened until
        // something tries to access it, and it's only created if it doesn't already exist.
        mOpenHelper = new DatabaseHelper(getContext());

        // Init the DB here
        mOpenHelper.getWritableDatabase().execSQL(SQL_CREATE_TABLE_ITEM);
        mOpenHelper.getWritableDatabase().execSQL(SQL_CREATE_TABLE_ITEM_SELLING_PRICE);
        mOpenHelper.getWritableDatabase().execSQL(SQL_CREATE_TABLE_SALE_TRANSACTION);
        mOpenHelper.getWritableDatabase().execSQL(SQL_CREATE_TABLE_SALE_RETURN_LINE_ITEM);

        // Register the database helper, so it can be shared with the SymmetricService
        SQLiteOpenHelperRegistry.register(DATABASE_NAME, mOpenHelper);

        Intent intent = new Intent(getContext(), SymmetricService.class);

        intent.putExtra(SymmetricService.INTENTKEY_SQLITEOPENHELPER_REGISTRY_KEY, DATABASE_NAME);
        intent.putExtra(SymmetricService.INTENTKEY_REGISTRATION_URL, REGISTRATION_URL);
        intent.putExtra(SymmetricService.INTENTKEY_EXTERNAL_ID, NODE_ID);
        intent.putExtra(SymmetricService.INTENTKEY_NODE_GROUP_ID, NODE_GROUP);
        intent.putExtra(SymmetricService.INTENTKEY_START_IN_BACKGROUND, true);


        Properties properties = new Properties();
        properties.put(ParameterConstants.FILE_SYNC_ENABLE, "true");
        properties.put("start.file.sync.tracker.job", "true");
        properties.put("start.file.sync.push.job", "true");
        properties.put("start.file.sync.pull.job", "true");
        properties.put("job.file.sync.pull.period.time.ms", "10000");

        intent.putExtra(SymmetricService.INTENTKEY_PROPERTIES, properties);

        getContext().startService(intent);

        // Assumes that any failures will be reported by a thrown exception.
        return true;
    }

    @Nullable
    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
        return null;
    }

    public String getType(Uri uri) {

        throw new IllegalArgumentException("Unknown URI " + uri);

    }

    @Nullable
    @Override
    public Uri insert(Uri uri, ContentValues values) {
        return null;
    }

    @Override
    public int delete(Uri uri, String selection, String[] selectionArgs) {
        return 0;
    }

    @Override
    public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
        return 0;
    }
}
  1. Now let’s add the user interface. Open the “activity_main.xml” drawable, of the main, initial screen of the app, and we’ll add some UI elements. I’ll call them sqlTextField, runButton, insertButton, newFileButton, and outputTextView.

  1. Now we’ll add some code to the MainActivity, to wire together the application. Here we add the code to run whatever SQL statement is entered.
        Button runButton = (Button)findViewById(R.id.runButton);
        runButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                String sql = sqlTextField.getText().toString();

                SQLiteDatabase db = SQLiteOpenHelperRegistry.lookup(DbProvider.DATABASE_NAME).getReadableDatabase();
                Cursor cursor = null;
                try {
                    cursor = db.rawQuery(sql, null);
                } catch (Exception ex) {}

The INSERT button is wired like this. It simply places an insert statement into the text field for convenience.

        Button insertButton = (Button)findViewById(R.id.insertButton);
        insertButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                sqlTextField.setText(INSERT_SQL);
            }
        });

  1. Now we can launch the application and try it out. First, we need to bring up a copy of SymmetricDS for the server. I am working off the demonstration configuration set described here: http://www.symmetricds.org/doc/3.8/html/tutorials.html.

First, we need to locate the SymmetricDS sync URL in the android application, and correct it for your environment. Normally you would not hardcode this, but provide it yourself with your application’s configuration mechanism.

Check the “logcat” view to see how your application is doing. In my case, I need to open registration for my new Android node. (http://www.symmetricds.org/doc/3.8/html/user-guide.html#_registration)

[If you happen to see this error at this point: “no such column: value” – make sure you are using SymmetricDS 3.8.4 or greater on the Android client. You might be running into this issue: http://www.symmetricds.org/issues/view.php?id=2776]

  1. Now let’s save a transaction, a validate that it gets captured and sync’d.

  1. Finally, let’s set up File Sync!

When initiating the SymmetricDS service on the Android side, the following properties will enable file File Sync (From DbProvider.java)

        Properties properties = new Properties();
        properties.put(ParameterConstants.FILE_SYNC_ENABLE, "true");
        properties.put("start.file.sync.tracker.job", "true");
        properties.put("start.file.sync.push.job", "true");
        properties.put("start.file.sync.pull.job", "true");
        properties.put("job.file.sync.pull.period.time.ms", "10000");

        intent.putExtra(SymmetricService.INTENTKEY_PROPERTIES, properties);

        getContext().startService(intent);

[My first pass at this resulted in this error:

Destination '/storage/emulated/0/Documents/manuals' directory cannot be created.

This can happen due to an Android permissions problem. There are a few ways to resolve this, but one way is to set the targetSdkVersion less than 23, in your build.gradle file.] Here is the configuration I set on the server for this file sync:

insert into SYM_PARAMETER (EXTERNAL_ID, NODE_GROUP_ID, PARAM_KEY, PARAM_VALUE, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) values ('ALL','ALL','file.sync.enable','true',{ts '2016-09-13 16:46:29.993'},'no_user',{ts '2016-09-13 16:46:29.993'});

insert into SYM_FILE_TRIGGER (TRIGGER_ID, CHANNEL_ID, RELOAD_CHANNEL_ID, BASE_DIR, RECURSE, INCLUDES_FILES, EXCLUDES_FILES, SYNC_ON_CREATE, SYNC_ON_MODIFIED, SYNC_ON_DELETE, SYNC_ON_CTL_FILE, DELETE_AFTER_SYNC, BEFORE_COPY_SCRIPT, AFTER_COPY_SCRIPT, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) values ('MANUALS','filesync','filesync_reload','/Users/mmichalek/Documents/manuals',0,null,null,1,1,1,0,0,null,null,{ts '2016-09-13 16:51:51.174'},'no_user',{ts '2016-09-13 16:54:16.305'});

insert into SYM_FILE_TRIGGER_ROUTER (TRIGGER_ID, ROUTER_ID, ENABLED, INITIAL_LOAD_ENABLED, TARGET_BASE_DIR, CONFLICT_STRATEGY, CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME) values ('MANUALS','corp_2_store',1,0,'${androidBaseDir}/Documents/manuals','SOURCE_WINS',{ts '2016-09-13 16:52:07.821'},'no_user',{ts '2016-09-13 16:52:07.821'});

You can use the adb console to check if your files have synced down to the device, and to create files for SymmetricDS to push back up. In my example, I was able to do these commands:

$ adb shell
$ cd /storage/emulated/0/Documents/manuals
$ ls
first-sync.pdf
second-sync.pdf
… etc.

Mark Michalek

Related Posts

Synchronizing Android Applications – Part 1
Read more