AMPLI-SYNC

h1. SQLite-sync documentation

*Owner* : AMPLIFIER sp. z o.o.

*Contact* : support (at) ampliapps.com

*website* : https://ampliapps.com

{{>toc}}

Welcome to the AMPLI-SYNC documentation!

We have language bindings in JavaScript, .NET C#, Java and Objective-C! 

h2. AMPLI-SYNC concept

h3. Solution diagram

!{width:700px}SQLite-sync-structure.png!

* Devices communicate with server using HTTP/HTTPS protocol. 

* Proxy Load Balancer. If necessary, proxy can redirect request to another instance of SQLite-core.

* Authorization provider will generate token based on response from authorization provider.

h3. Proxy Balancer flowchart

!{width:400px}SQLite-sync-proxy-balancer.png!

h3. Authorization Provider flowchart

!{width:350px}SQLite-sync-authentication-process.png!

h2. REST API (server API)

h3. Protocol version

This document describes integration with the REST API 3.x protocol.

h3. Service description

There are two stages to processing a request:

* Device places an request.

* SQLite-sync server confirms the request has been processed successfully and sends confirmation with eventual conflict list that need to be resolved.

h3. Synchronization flowchart

!{width:200px;}Device-synchronization-diagram.png!

h3. Request URL format

Sample REST API call:

<pre>

https://example.com/API3/__method___

</pre>

Explanation:

*https://example.com/* - adres of REST API service 

*API3* - version of synchronization

*__method___* - method/action

h3. API methods

h4. *API3* - control method

*Method* : GET

*Path* : “/API3”

*Produces* : TEXT_HTML

*Description* : control method. Returns “API[v3] SQLite-Sync.COM is working correctly!” if web service is correctly configured.

h4. *InitializeSubscriber* - Reinitialize subscriber

*Method* : GET

*Path* : “/InitializeSubscriber/{subscriberUUID}”

*Produces* : TEXT_PLAIN

*Description* : Reinitialize subscriber, create empty schema on device, prepare master database for new subscriber.

Implementation examples: 

* [[InitializeSubscriber Objective-C]]

* [[InitializeSubscriber .NET C#]]

* [[InitializeSubscriber JAVA]]

* [[InitializeSubscriber JavaScript]]

h4. *Sync* - gets changes for table

*Method* : GET

*Path* : /Sync/{subscriberUUID}/{tableName}

*Produces* : TEXT_PLAIN

*Description* : Get changed data. 

Params:

*subscriberUUID* - identifier of subscriber. By default device unique ID is used. But we can place there any value (also #user.UUID)

*tableName* - name of table from database (without schema)

Response:

<pre><code class="xml">

<?xml version="1.0" encoding="utf-8"?>

<records>

  <r a="1">

    <c>2</c>

    <c>Document</c>

    <c>75541</c>

    <c>2014-02-13 00:00:00</c>

    <c>665.000</c>

    <c>2c93d64e-cc72-11e3-87e0-f82fa8e587f9</c>

  </r>

  <r a="2">

    <c>4</c>

    <c>Document 4</c>

    <c>4879</c>

    <c>2014-04-23 13:44:48</c>

    <c>4875.000</c>

    <c>2c93d765-cc72-11e3-87e0-f82fa8e587f9</c>

  </r>

</records>

</code></pre>

<records> - section contains records 

<r a=”1”> - here starts record. 

</r> - here record ends

Attribute “a” (action type)

1 - new record

2 - update for record

Implementation examples: 

* [[Objective-C]]

* [[.NET C#]]

* [[JAVA]]

* [[JavaScript]]

h4. *CommitSync* - control method

*Method* : GET

*Path* : “/CommitSync/{syncId}”

*Produces* : TEXT_PLAIN

*Description* : If device recieved all changes without error this method should be call to tell server that there was no errors during receiving package. Params: *syncId* - id of data package

Implementation examples: 

* [[CommitSync Objective-C]]

* [[CommitSync .NET C#]]

* [[CommitSync JAVA]]

* [[CommitSync JavaScript]]

h4. *Send* - control method

*Method* : POST

*Path* : “/Send”

*Consumes* : JSON

*Produces* : TEXT_PLAIN

*Description* : Send changes from device to master database.

For sample data format with changes see XML code sample.

Implementation examples: 

* [[Send Objective-C]]

* [[Send .NET C#]]

* [[Send JAVA]]

* [[Send JavaScript]]

[[XML message format]]

h4. *AddTable* - control method

*Method* : GET

*Path* : “/AddTable/{tableName}”

*Produces* : TEXT_PLAIN

*Description* : Add table to synchronization.

Implementation examples: 

* [[AddTable Objective-C]]

* [[AddTable .NET C#]]

* [[AddTable JAVA]]

* [[AddTable JavaScript]]

h4. *RemoveTable* - control method

*Method* : GET

*Path:* “/RemoveTable/{tableName}”

*Produces* : TEXT_PLAIN

*Description* : Remove table from synchronization.

Implementation examples: 

* [[RemoveTable Objective-C]]

* [[RemoveTable .NET C#]]

* [[RemoveTable JAVA]]

* [[RemoveTable JavaScript]]

h2. Conflict Resolution

!{width:550px}Conflict-resolution-decision-diagram.png!

h2. Update procedure

!{width:650px;}Updating-client-process.png!

When the user first starts the client application, will be forced to go online and do an initial sync with the master DB, which sends a schema used to create the local database and its tables. After that, the user can work offline.

When user is in older version will be forced to make update of schema. All updates will be sent to client and apply locally. After successfully update client will send unsync data to master database. 

A schema update usually means an update of the application is also needed, since the application will need different SQL code to deal with the new schema. 

The master DB will never receive changes from clients with the old schema, since a client always pulls before pushing changes - and in the pull it would have received and applied the new schema.

h2. Data filtering

*Goal* : send to device only documents that are main.

We will going to use the device with subscriber id 1.

In the database we have a table where we are storing documents *[dbo].[Documents]* , user data *[dbo].[Users]* and table with relations between documents and users *[dbo].[UserDocuments]*.

[dbo].[Documents] structure:

!4.png!

[dbo].[Users] structure:

!3.png!

[dbo].[UserDocuments] structure:

!2.png!

Please notice, that in table dbo.User we have a column usrSubscriberId - it tells exactly which user uses particular subscriber id. Based on this column we are able to select which user id is used while the device is undergoing the synchronization process.

The next step is to prepare view:

!1.png!

SQL code:

<pre><code class="sql">

SELECT dbo.Documents.RowId, dbo.MergeSubscribers.SubscriberId AS pdaIdent 

FROM dbo.Documents 

INNER JOIN dbo.UserDocuments ON dbo.Documents.docId = dbo.UserDocuments.usdDocId 

INNER JOIN dbo.Users ON dbo.UserDocuments.usdUsrId = dbo.Users.usrId 

INNER JOIN dbo.MergeSubscribers ON dbo.Users.usrSubscriberId = dbo.MergeSubscribers.SubscriberId

</code></pre>

The view needs to return two things:

# RowId of filtered table (dbo.Documents is this scenario) subscriberId aliased as 'pdaIdent’

# You can add extra condition in this view - it’s up to you.

Next step, we need to tell synchronization to use that filter:

<pre><code class="sql">

update [MergeTablesToSync] set TableFilter='vwMerge_Documents' where TableName='Documents' and TableSchema='dbo'

</code></pre>

where *vwMerge_Documents* is the name of your view.

h2. Installation

h3. Server Prerequisites

To make ampli-sync server work you need:

* Apache Tomcat 8.

* Java

* Linux/Windows environment. 

h3. Manual

Steps needed to install AMPLI-SYNC manually on Ubuntu.

# Install Tomcat on Ubuntu:

https://www.digitalocean.com/community/tutorials/how-to-install-apache-tomcat-8-on-ubuntu-16-04

# Create new user

<pre>

sudo adduser amplisync

</pre>

System will ask you for password for newly created user.

# Add user to group ‘tomcat’

<pre>

sudo usermod -a -G tomcat amplisync

</pre>

# Chang in web.xml path variable to 

<pre>

\home/sqlitesync/demo

</pre>

# Install new application in Tomcat. Start with switching to amplisync user.

<pre>

su amplisync

</pre>

# Create new folder /home/sqlitesync/demo

# Upload new service amplisync-demo to Tomcat. You can do that using Tomcat application manager, or you can put WAR file in Tomcat webapps folder. Name of your WAR file is app name in Tomcat environment. Remember to not place spaces and special chars in name of your WAR file.

# Restart Tomcat:

<pre>

service tomcat restart

</pre>

Now you can access your installation using link:

<pre>

http://your_ip:8080/amplisync-app-name/API3

</pre>

# Setup permissions:

<pre>

chown -R sqlitesync:tomcat /home/amplisync/demo/

</pre>

h3. Docker

h3. Configuring AMPLI-SYNC service

First you need to adjust website configuration file (web.xml), then you need to change main configuration file (sync.properties).

*Service configuration (web.xml)*

Go to your_webapps_folder/SqliteSync/WEB-INF/web.xml and open for edit. Navigate to section:

<pre><code class="xml">

<env-entry>

<env-entry-name>working-dir</env-entry-name>

<env-entry-type>java.lang.String</env-entry-type>

<env-entry-value>/your/working/dir/sqlite-sync/</env-entry-value>

</env-entry>

</code></pre>

change env-entry-value key and point to working dir where SQLite-sync.com server will store log files, temporary files and configuration. Create manually a subfolder named config. Create a text file sync.properties in folder config. The path should look like this:

<pre>

\working_dir\config\sync.properties

</pre>

*IMPORTANT* Restart service after changing web.xml. Make sure that Tomcat has read/write access to working dir.

Sample configurations for MySQL server

<pre>

DB_ENGINE = mysql

DBURL = jdbc:mysql://server:3306/dbname?rewriteBatchedStatements=true

DBUSER = user

DBPASS = pass

DBDRIVER = com.mysql.cj.jdbc.Driver

DATE_FORMAT = yyyy-MM-dd HH:mm:ss

HISTORY_DAYS = 7

LOG_LEVEL = 4

</pre>

If you faced a timezone error after configuration in `sync.properties` add at the end of connection string:

<pre>

&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

</pre>

*Keys explanation*

*DB_ENGINE* - type of database engine. Available options:

* mysql

* mssql

* postgresql

* oracle

*LOG_LEVEL* - defain details level for log

> 4: TRACE, DEBUG, INFO, WARN; (default)

> 3: DEBUG, INFO, WARN; 

> 2: INFO,WARN; 

> 1: WARN; 

> 0 - disable logs

*DATE_FORMAT* - set format of date 

default format: yyyy-MM-dd HH:mm:ss 

*HISTORY_DAYS* - How long files with sync data will be kept

default value: 7

When you use MySQL database DO NOT remove from the end of the connection string:

<pre>

?rewriteBatchedStatements=true

</pre>

h2. Supported databases

AMPLI-SYNC supports those databases:

* MySQL

* Microsoft SQL Server (2005 and newer)

* Oracle

* PostgreSQL

h3. Supported columns data types

SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.

* blob

* longblob

* varbinary

* binary

* image

* mediumblob

* varbinarymax

* byte[]

* longtext

* varchar

* nvarchar

* char

* varcharmax

* enum

* mediumtext

* text

* string

* geography

* geometry

* hierarchyid

* nchar

* ntext

* nvarcharmax

* userdefineddatatype

* userdefinedtabletype

* userdefinedtype

* variant

* xml

* tinytext

* set

* time

* timestamp

* year

* datetime

* uniqueidentifier

* datetime2

* date

* mediumint

* bit

* tinyint

* smallint

* bigint

* int

* boolean

* byte

* long

* int64

* serial

* int32

* smalldatetime

* double

* float

* numeric

* decimal

* real

* money

h3. Primary Key requirements

Single and mupltiple columns are supported as primary key.

When column is AUTO_INCREMENT/SERIAL, identity pool management is handled by AMPLI_SYNC. It means when you insert a new record onto the device, the PK will be automatically changed for the first value available for device. 

h2. Samples