Project

General

Profile

Wiki » History » Version 43

Tomek Dziemidowicz, 2020-01-29 08:13 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 43 Tomek Dziemidowicz
TIMESTAMP_FORMAT = yyyy-MM-dd HH:mm:ssZ
310 19 Tomek Dziemidowicz
HISTORY_DAYS = 7
311
LOG_LEVEL = 4
312
</pre>
313
If you faced a timezone error after configuration in `sync.properties` add at the end of connection string:
314
<pre>
315
&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
316
</pre>
317
*Keys explanation*
318 21 Tomek Dziemidowicz
*DB_ENGINE* - type of database engine. Available options:
319
* mysql
320
* mssql
321
* postgresql
322
* oracle
323
324 19 Tomek Dziemidowicz
*LOG_LEVEL* - defain details level for log
325
> 4: TRACE, DEBUG, INFO, WARN; (default)
326
> 3: DEBUG, INFO, WARN; 
327
> 2: INFO,WARN; 
328
> 1: WARN; 
329 1 Tomek Dziemidowicz
> 0 - disable logs
330 43 Tomek Dziemidowicz
*DATE_FORMAT*, *TIMESTAMP_FORMAT* - set format of date 
331 19 Tomek Dziemidowicz
default format: yyyy-MM-dd HH:mm:ss 
332
*HISTORY_DAYS* - How long files with sync data will be kept
333
default value: 7
334
When you use MySQL database DO NOT remove from the end of the connection string:
335
<pre>
336
?rewriteBatchedStatements=true
337 20 Tomek Dziemidowicz
</pre>
338 19 Tomek Dziemidowicz
339 2 Tomek Dziemidowicz
h2. Supported databases
340
341 30 Tomek Dziemidowicz
AMPLI-SYNC supports those databases:
342
343
* MySQL
344
* Microsoft SQL Server (2005 and newer)
345
* Oracle
346
* PostgreSQL
347
348 31 Tomek Dziemidowicz
h3. Supported columns data types
349
350
SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.
351
* blob
352
* longblob
353
* varbinary
354
* binary
355
* image
356
* mediumblob
357
* varbinarymax
358
* byte[]
359
* longtext
360
* varchar
361
* nvarchar
362
* char
363
* varcharmax
364
* enum
365
* mediumtext
366
* text
367
* string
368
* geography
369
* geometry
370
* hierarchyid
371
* nchar
372
* ntext
373
* nvarcharmax
374
* userdefineddatatype
375
* userdefinedtabletype
376
* userdefinedtype
377
* variant
378
* xml
379
* tinytext
380
* set
381
* time
382
* timestamp
383
* year
384
* datetime
385
* uniqueidentifier
386
* datetime2
387
* date
388
* mediumint
389
* bit
390
* tinyint
391
* smallint
392
* bigint
393
* int
394
* boolean
395
* byte
396
* long
397
* int64
398
* serial
399
* int32
400
* smalldatetime
401
* double
402
* float
403
* numeric
404
* decimal
405
* real
406
* money
407
408 32 Tomek Dziemidowicz
h3. Primary Key requirements
409 1 Tomek Dziemidowicz
410 32 Tomek Dziemidowicz
Single and mupltiple columns are supported as primary key.
411 43 Tomek Dziemidowicz
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.
Go to top