Project

General

Profile

Wiki » History » Version 40

Tomek Dziemidowicz, 2019-07-18 09:23 PM

1 2 Tomek Dziemidowicz
h1. SQLite-sync documentation
2
3
*Owner* : AMPLIFIER sp. z o.o.
4
*Contact* : support (at) ampliapps.com
5
*website* : https://ampliapps.com
6
7 3 Tomek Dziemidowicz
{{>toc}}
8
9 9 Tomek Dziemidowicz
Welcome to the AMPLI-SYNC documentation!
10
We have language bindings in JavaScript, .NET C#, Java and Objective-C! 
11 1 Tomek Dziemidowicz
12 9 Tomek Dziemidowicz
h2. AMPLI-SYNC concept
13
14
h3. Solution diagram
15
16 8 Tomek Dziemidowicz
!{width:700px}SQLite-sync-structure.png!
17 1 Tomek Dziemidowicz
18 13 Tomek Dziemidowicz
* Devices communicate with server using HTTP/HTTPS protocol. 
19
* Proxy Load Balancer. If necessary, proxy can redirect request to another instance of SQLite-core.
20
* Authorization provider will generate token based on response from authorization provider.
21 9 Tomek Dziemidowicz
22
h3. Proxy Balancer flowchart
23
24
!{width:400px}SQLite-sync-proxy-balancer.png!
25
26
h3. Authorization Provider flowchart
27
28
!{width:350px}SQLite-sync-authentication-process.png!
29
30 2 Tomek Dziemidowicz
h2. REST API (server API)
31
32 10 Tomek Dziemidowicz
h3. Protocol version
33
34
This document describes integration with the REST API 3.x protocol.
35
36
h3. Service description
37
38
There are two stages to processing a request:
39
40 12 Tomek Dziemidowicz
* Device places an request.
41
* SQLite-sync server confirms the request has been processed successfully and sends confirmation with eventual conflict list that need to be resolved.
42 10 Tomek Dziemidowicz
43 11 Tomek Dziemidowicz
h3. Synchronization flowchart
44
45
!{width:200px;}Device-synchronization-diagram.png!
46 10 Tomek Dziemidowicz
47 23 Tomek Dziemidowicz
h3. Request URL format
48 22 Tomek Dziemidowicz
49
Sample REST API call:
50
<pre>
51
https://example.com/API3/__method___
52
</pre>
53
Explanation:
54
*https://example.com/* - adres of REST API service 
55
*API3* - version of synchronization
56
*__method___* - method/action
57
58 24 Tomek Dziemidowicz
h3. API methods
59
60 28 Tomek Dziemidowicz
h4. *API3* - control method
61
62
*Method* : GET
63
*Path* : “/API3”
64
*Produces* : TEXT_HTML
65
*Description* : control method. Returns “API[v3] SQLite-Sync.COM is working correctly!” if web service is correctly configured.
66
67 29 Tomek Dziemidowicz
h4. *InitializeSubscriber* - Reinitialize subscriber
68 28 Tomek Dziemidowicz
69
*Method* : GET
70
*Path* : “/InitializeSubscriber/{subscriberUUID}”
71
*Produces* : TEXT_PLAIN
72
*Description* : Reinitialize subscriber, create empty schema on device, prepare master database for new subscriber.
73
74
Implementation examples: 
75 29 Tomek Dziemidowicz
* [[InitializeSubscriber Objective-C]]
76
* [[InitializeSubscriber .NET C#]]
77
* [[InitializeSubscriber JAVA]]
78
* [[InitializeSubscriber JavaScript]]
79 28 Tomek Dziemidowicz
80 25 Tomek Dziemidowicz
h4. *Sync* - gets changes for table
81 26 Tomek Dziemidowicz
82 1 Tomek Dziemidowicz
*Method* : GET
83 26 Tomek Dziemidowicz
*Path* : /Sync/{subscriberUUID}/{tableName}
84 1 Tomek Dziemidowicz
*Produces* : TEXT_PLAIN
85 26 Tomek Dziemidowicz
*Description* : Get changed data. 
86
Params:
87 1 Tomek Dziemidowicz
*subscriberUUID* - identifier of subscriber. By default device unique ID is used. But we can place there any value (also #user.UUID)
88 26 Tomek Dziemidowicz
*tableName* - name of table from database (without schema)
89 1 Tomek Dziemidowicz
Response:
90 26 Tomek Dziemidowicz
<pre><code class="xml">
91
<?xml version="1.0" encoding="utf-8"?>
92
<records>
93
  <r a="1">
94
    <c>2</c>
95
    <c>Document</c>
96
    <c>75541</c>
97
    <c>2014-02-13 00:00:00</c>
98
    <c>665.000</c>
99
    <c>2c93d64e-cc72-11e3-87e0-f82fa8e587f9</c>
100
  </r>
101
  <r a="2">
102
    <c>4</c>
103
    <c>Document 4</c>
104
    <c>4879</c>
105
    <c>2014-04-23 13:44:48</c>
106
    <c>4875.000</c>
107
    <c>2c93d765-cc72-11e3-87e0-f82fa8e587f9</c>
108
  </r>
109
</records>
110
</code></pre>
111
<records> - section contains records 
112
<r a=”1”> - here starts record. 
113
</r> - here record ends
114
Attribute “a” (action type)
115
1 - new record
116
2 - update for record
117 1 Tomek Dziemidowicz
118 27 Tomek Dziemidowicz
Implementation examples: 
119 26 Tomek Dziemidowicz
* [[Objective-C]]
120
* [[.NET C#]]
121
* [[JAVA]]
122
* [[JavaScript]]
123 25 Tomek Dziemidowicz
124 34 Tomek Dziemidowicz
h4. *CommitSync* - control method
125 33 Tomek Dziemidowicz
126
*Method* : GET
127
*Path* : “/CommitSync/{syncId}”
128
*Produces* : TEXT_PLAIN
129
*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
130
131
Implementation examples: 
132
* [[CommitSync Objective-C]]
133
* [[CommitSync .NET C#]]
134
* [[CommitSync JAVA]]
135
* [[CommitSync JavaScript]]
136
137 35 Tomek Dziemidowicz
h4. *Send* - control method
138
139 36 Tomek Dziemidowicz
*Method* : POST
140
*Path* : “/Send”
141
*Consumes* : JSON
142
*Produces* : TEXT_PLAIN
143
*Description* : Send changes from device to master database.
144 1 Tomek Dziemidowicz
145 36 Tomek Dziemidowicz
For sample data format with changes see XML code sample.
146
147
Implementation examples: 
148
* [[Send Objective-C]]
149
* [[Send .NET C#]]
150
* [[Send JAVA]]
151
* [[Send JavaScript]]
152 35 Tomek Dziemidowicz
153 37 Tomek Dziemidowicz
[[XML message format]]
154
155 38 Tomek Dziemidowicz
h4. *AddTable* - control method
156
157
*Method* : GET
158
*Path* : “/AddTable/{tableName}”
159
*Produces* : TEXT_PLAIN
160
*Description* : Add table to synchronization.
161
162
Implementation examples: 
163
* [[AddTable Objective-C]]
164
* [[AddTable .NET C#]]
165
* [[AddTable JAVA]]
166
* [[AddTable JavaScript]]
167
168
h4. *RemoveTable* - control method
169
170
*Method* : GET
171
*Path:* “/RemoveTable/{tableName}”
172
*Produces* : TEXT_PLAIN
173
*Description* : Remove table from synchronization.
174
175
Implementation examples: 
176
* [[RemoveTable Objective-C]]
177
* [[RemoveTable .NET C#]]
178
* [[RemoveTable JAVA]]
179
* [[RemoveTable JavaScript]]
180
181 2 Tomek Dziemidowicz
h2. Conflict Resolution
182
183 39 Tomek Dziemidowicz
!{width:550px}Conflict-resolution-decision-diagram.png!
184
185 2 Tomek Dziemidowicz
h2. Update procedure
186
187 40 Tomek Dziemidowicz
!{width:650px;}Updating-client-process.png!
188
189
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.
190
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. 
191
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. 
192
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.
193
194
195 2 Tomek Dziemidowicz
h2. Data filtering
196
197 1 Tomek Dziemidowicz
h2. Installation
198 9 Tomek Dziemidowicz
199 18 Tomek Dziemidowicz
h3. Server Prerequisites
200
201
To make ampli-sync server work you need:
202
* Apache Tomcat 8.
203
* Java
204
* Linux/Windows environment. 
205
206 9 Tomek Dziemidowicz
h3. Manual
207
208 14 Tomek Dziemidowicz
Steps needed to install AMPLI-SYNC manually on Ubuntu.
209
210 15 Tomek Dziemidowicz
# Install Tomcat on Ubuntu:
211 14 Tomek Dziemidowicz
https://www.digitalocean.com/community/tutorials/how-to-install-apache-tomcat-8-on-ubuntu-16-04
212
# Create new user
213
<pre>
214
sudo adduser amplisync
215
</pre>
216
System will ask you for password for newly created user.
217
# Add user to group ‘tomcat’
218
<pre>
219
sudo usermod -a -G tomcat amplisync
220
</pre>
221
# Chang in web.xml path variable to 
222
<pre>
223
\home/sqlitesync/demo
224
</pre>
225
# Install new application in Tomcat. Start with switching to amplisync user.
226
<pre>
227
su amplisync
228
</pre>
229
# Create new folder /home/sqlitesync/demo
230 16 Tomek Dziemidowicz
# 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.
231 17 Tomek Dziemidowicz
# Restart Tomcat:
232 14 Tomek Dziemidowicz
<pre>
233
service tomcat restart
234
</pre>
235
Now you can access your installation using link:
236
<pre>
237
http://your_ip:8080/amplisync-app-name/API3
238
</pre>
239
# Setup permissions:
240
<pre>
241
chown -R sqlitesync:tomcat /home/amplisync/demo/
242
</pre>
243
244 9 Tomek Dziemidowicz
h3. Docker
245 2 Tomek Dziemidowicz
246 20 Tomek Dziemidowicz
h3. Configuring AMPLI-SYNC service
247 19 Tomek Dziemidowicz
248
First you need to adjust website configuration file (web.xml), then you need to change main configuration file (sync.properties).
249
*Service configuration (web.xml)*
250
Go to your_webapps_folder/SqliteSync/WEB-INF/web.xml and open for edit. Navigate to section:
251
<pre><code class="xml">
252
<env-entry>
253
<env-entry-name>working-dir</env-entry-name>
254
<env-entry-type>java.lang.String</env-entry-type>
255
<env-entry-value>/your/working/dir/sqlite-sync/</env-entry-value>
256
</env-entry>
257
</code></pre>
258
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:
259
<pre>
260
\working_dir\config\sync.properties
261
</pre>
262
*IMPORTANT* Restart service after changing web.xml. Make sure that Tomcat has read/write access to working dir.
263
Sample configurations for MySQL server
264
<pre>
265
DB_ENGINE = mysql
266
DBURL = jdbc:mysql://server:3306/dbname?rewriteBatchedStatements=true
267
DBUSER = user
268
DBPASS = pass
269
DBDRIVER = com.mysql.cj.jdbc.Driver
270
DATE_FORMAT = yyyy-MM-dd HH:mm:ss
271
HISTORY_DAYS = 7
272
LOG_LEVEL = 4
273
</pre>
274
If you faced a timezone error after configuration in `sync.properties` add at the end of connection string:
275
<pre>
276
&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
277
</pre>
278
*Keys explanation*
279 21 Tomek Dziemidowicz
*DB_ENGINE* - type of database engine. Available options:
280
* mysql
281
* mssql
282
* postgresql
283
* oracle
284
285 19 Tomek Dziemidowicz
*LOG_LEVEL* - defain details level for log
286
> 4: TRACE, DEBUG, INFO, WARN; (default)
287
> 3: DEBUG, INFO, WARN; 
288
> 2: INFO,WARN; 
289
> 1: WARN; 
290
> 0 - disable logs
291
*DATE_FORMAT* - set format of date 
292
default format: yyyy-MM-dd HH:mm:ss 
293
*HISTORY_DAYS* - How long files with sync data will be kept
294
default value: 7
295
When you use MySQL database DO NOT remove from the end of the connection string:
296
<pre>
297
?rewriteBatchedStatements=true
298 20 Tomek Dziemidowicz
</pre>
299 19 Tomek Dziemidowicz
300 2 Tomek Dziemidowicz
h2. Supported databases
301
302 30 Tomek Dziemidowicz
AMPLI-SYNC supports those databases:
303
304
* MySQL
305
* Microsoft SQL Server (2005 and newer)
306
* Oracle
307
* PostgreSQL
308
309 31 Tomek Dziemidowicz
h3. Supported columns data types
310
311
SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.
312
* blob
313
* longblob
314
* varbinary
315
* binary
316
* image
317
* mediumblob
318
* varbinarymax
319
* byte[]
320
* longtext
321
* varchar
322
* nvarchar
323
* char
324
* varcharmax
325
* enum
326
* mediumtext
327
* text
328
* string
329
* geography
330
* geometry
331
* hierarchyid
332
* nchar
333
* ntext
334
* nvarcharmax
335
* userdefineddatatype
336
* userdefinedtabletype
337
* userdefinedtype
338
* variant
339
* xml
340
* tinytext
341
* set
342
* time
343
* timestamp
344
* year
345
* datetime
346
* uniqueidentifier
347
* datetime2
348
* date
349
* mediumint
350
* bit
351
* tinyint
352
* smallint
353
* bigint
354
* int
355
* boolean
356
* byte
357
* long
358
* int64
359
* serial
360
* int32
361
* smalldatetime
362
* double
363
* float
364
* numeric
365
* decimal
366
* real
367
* money
368
369 32 Tomek Dziemidowicz
h3. Primary Key requirements
370
371
Single and mupltiple columns are supported as primary key.
372
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. 
373 30 Tomek Dziemidowicz
374 2 Tomek Dziemidowicz
h2. Samples