Project

General

Profile

Wiki » History » Version 41

Tomek Dziemidowicz, 2019-07-19 06:39 AM

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 1 Tomek Dziemidowicz
h2. Data filtering
195 41 Tomek Dziemidowicz
196
*Goal* : send to device only documents that are main.
197
We will going to use the device with subscriber id 1.
198
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]*.
199
200
[dbo].[Documents] structure:
201
!4.png!
202
203
[dbo].[Users] structure:
204
!3.png!
205
206
[dbo].[UserDocuments] structure:
207
!2.png!
208
209
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.
210
The next step is to prepare view:
211
!1.png!
212
213
SQL code:
214
<pre><code class="sql">
215
SELECT dbo.Documents.RowId, dbo.MergeSubscribers.SubscriberId AS pdaIdent 
216
FROM dbo.Documents 
217
INNER JOIN dbo.UserDocuments ON dbo.Documents.docId = dbo.UserDocuments.usdDocId 
218
INNER JOIN dbo.Users ON dbo.UserDocuments.usdUsrId = dbo.Users.usrId 
219
INNER JOIN dbo.MergeSubscribers ON dbo.Users.usrSubscriberId = dbo.MergeSubscribers.SubscriberId
220
</code></pre>
221
222
The view needs to return two things:
223
# RowId of filtered table (dbo.Documents is this scenario) subscriberId aliased as 'pdaIdent’
224
# You can add extra condition in this view - it’s up to you.
225
226
Next step, we need to tell synchronization to use that filter:
227
228
<pre><code class="sql">
229
update [MergeTablesToSync] set TableFilter='vwMerge_Documents' where TableName='Documents' and TableSchema='dbo'
230
</code></pre>
231
where *vwMerge_Documents* is the name of your view.
232
233
234 2 Tomek Dziemidowicz
235 1 Tomek Dziemidowicz
h2. Installation
236 9 Tomek Dziemidowicz
237 18 Tomek Dziemidowicz
h3. Server Prerequisites
238
239
To make ampli-sync server work you need:
240
* Apache Tomcat 8.
241
* Java
242
* Linux/Windows environment. 
243
244 9 Tomek Dziemidowicz
h3. Manual
245
246 14 Tomek Dziemidowicz
Steps needed to install AMPLI-SYNC manually on Ubuntu.
247
248 15 Tomek Dziemidowicz
# Install Tomcat on Ubuntu:
249 14 Tomek Dziemidowicz
https://www.digitalocean.com/community/tutorials/how-to-install-apache-tomcat-8-on-ubuntu-16-04
250
# Create new user
251
<pre>
252
sudo adduser amplisync
253
</pre>
254
System will ask you for password for newly created user.
255
# Add user to group ‘tomcat’
256
<pre>
257
sudo usermod -a -G tomcat amplisync
258
</pre>
259
# Chang in web.xml path variable to 
260
<pre>
261
\home/sqlitesync/demo
262
</pre>
263
# Install new application in Tomcat. Start with switching to amplisync user.
264
<pre>
265
su amplisync
266
</pre>
267
# Create new folder /home/sqlitesync/demo
268 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.
269 17 Tomek Dziemidowicz
# Restart Tomcat:
270 14 Tomek Dziemidowicz
<pre>
271
service tomcat restart
272
</pre>
273
Now you can access your installation using link:
274
<pre>
275
http://your_ip:8080/amplisync-app-name/API3
276
</pre>
277
# Setup permissions:
278
<pre>
279
chown -R sqlitesync:tomcat /home/amplisync/demo/
280
</pre>
281
282 9 Tomek Dziemidowicz
h3. Docker
283 2 Tomek Dziemidowicz
284 20 Tomek Dziemidowicz
h3. Configuring AMPLI-SYNC service
285 19 Tomek Dziemidowicz
286
First you need to adjust website configuration file (web.xml), then you need to change main configuration file (sync.properties).
287
*Service configuration (web.xml)*
288
Go to your_webapps_folder/SqliteSync/WEB-INF/web.xml and open for edit. Navigate to section:
289
<pre><code class="xml">
290
<env-entry>
291
<env-entry-name>working-dir</env-entry-name>
292
<env-entry-type>java.lang.String</env-entry-type>
293
<env-entry-value>/your/working/dir/sqlite-sync/</env-entry-value>
294
</env-entry>
295
</code></pre>
296
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:
297
<pre>
298
\working_dir\config\sync.properties
299
</pre>
300
*IMPORTANT* Restart service after changing web.xml. Make sure that Tomcat has read/write access to working dir.
301
Sample configurations for MySQL server
302
<pre>
303
DB_ENGINE = mysql
304
DBURL = jdbc:mysql://server:3306/dbname?rewriteBatchedStatements=true
305
DBUSER = user
306
DBPASS = pass
307
DBDRIVER = com.mysql.cj.jdbc.Driver
308
DATE_FORMAT = yyyy-MM-dd HH:mm:ss
309
HISTORY_DAYS = 7
310
LOG_LEVEL = 4
311
</pre>
312
If you faced a timezone error after configuration in `sync.properties` add at the end of connection string:
313
<pre>
314
&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
315
</pre>
316
*Keys explanation*
317 21 Tomek Dziemidowicz
*DB_ENGINE* - type of database engine. Available options:
318
* mysql
319
* mssql
320
* postgresql
321
* oracle
322
323 19 Tomek Dziemidowicz
*LOG_LEVEL* - defain details level for log
324
> 4: TRACE, DEBUG, INFO, WARN; (default)
325
> 3: DEBUG, INFO, WARN; 
326
> 2: INFO,WARN; 
327
> 1: WARN; 
328
> 0 - disable logs
329
*DATE_FORMAT* - set format of date 
330
default format: yyyy-MM-dd HH:mm:ss 
331
*HISTORY_DAYS* - How long files with sync data will be kept
332
default value: 7
333
When you use MySQL database DO NOT remove from the end of the connection string:
334
<pre>
335
?rewriteBatchedStatements=true
336 20 Tomek Dziemidowicz
</pre>
337 19 Tomek Dziemidowicz
338 2 Tomek Dziemidowicz
h2. Supported databases
339
340 30 Tomek Dziemidowicz
AMPLI-SYNC supports those databases:
341
342
* MySQL
343
* Microsoft SQL Server (2005 and newer)
344
* Oracle
345
* PostgreSQL
346
347 31 Tomek Dziemidowicz
h3. Supported columns data types
348
349
SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.
350
* blob
351
* longblob
352
* varbinary
353
* binary
354
* image
355
* mediumblob
356
* varbinarymax
357
* byte[]
358
* longtext
359
* varchar
360
* nvarchar
361
* char
362
* varcharmax
363
* enum
364
* mediumtext
365
* text
366
* string
367
* geography
368
* geometry
369
* hierarchyid
370
* nchar
371
* ntext
372
* nvarcharmax
373
* userdefineddatatype
374
* userdefinedtabletype
375
* userdefinedtype
376
* variant
377
* xml
378
* tinytext
379
* set
380
* time
381
* timestamp
382
* year
383
* datetime
384
* uniqueidentifier
385
* datetime2
386
* date
387
* mediumint
388
* bit
389
* tinyint
390
* smallint
391
* bigint
392
* int
393
* boolean
394
* byte
395
* long
396
* int64
397
* serial
398
* int32
399
* smalldatetime
400
* double
401
* float
402
* numeric
403
* decimal
404
* real
405
* money
406
407 32 Tomek Dziemidowicz
h3. Primary Key requirements
408
409
Single and mupltiple columns are supported as primary key.
410
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. 
411 30 Tomek Dziemidowicz
412 2 Tomek Dziemidowicz
h2. Samples