Project

General

Profile

Wiki » History » Version 33

Tomek Dziemidowicz, 2019-07-18 09:00 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 33 Tomek Dziemidowicz
h4. * CommitSync* - control method
125
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
138 2 Tomek Dziemidowicz
h2. Conflict Resolution
139
140
h2. Update procedure
141
142
h2. Data filtering
143
144 1 Tomek Dziemidowicz
h2. Installation
145 9 Tomek Dziemidowicz
146 18 Tomek Dziemidowicz
h3. Server Prerequisites
147
148
To make ampli-sync server work you need:
149
* Apache Tomcat 8.
150
* Java
151
* Linux/Windows environment. 
152
153 9 Tomek Dziemidowicz
h3. Manual
154
155 14 Tomek Dziemidowicz
Steps needed to install AMPLI-SYNC manually on Ubuntu.
156
157 15 Tomek Dziemidowicz
# Install Tomcat on Ubuntu:
158 14 Tomek Dziemidowicz
https://www.digitalocean.com/community/tutorials/how-to-install-apache-tomcat-8-on-ubuntu-16-04
159
# Create new user
160
<pre>
161
sudo adduser amplisync
162
</pre>
163
System will ask you for password for newly created user.
164
# Add user to group ‘tomcat’
165
<pre>
166
sudo usermod -a -G tomcat amplisync
167
</pre>
168
# Chang in web.xml path variable to 
169
<pre>
170
\home/sqlitesync/demo
171
</pre>
172
# Install new application in Tomcat. Start with switching to amplisync user.
173
<pre>
174
su amplisync
175
</pre>
176
# Create new folder /home/sqlitesync/demo
177 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.
178 17 Tomek Dziemidowicz
# Restart Tomcat:
179 14 Tomek Dziemidowicz
<pre>
180
service tomcat restart
181
</pre>
182
Now you can access your installation using link:
183
<pre>
184
http://your_ip:8080/amplisync-app-name/API3
185
</pre>
186
# Setup permissions:
187
<pre>
188
chown -R sqlitesync:tomcat /home/amplisync/demo/
189
</pre>
190
191 9 Tomek Dziemidowicz
h3. Docker
192 2 Tomek Dziemidowicz
193 20 Tomek Dziemidowicz
h3. Configuring AMPLI-SYNC service
194 19 Tomek Dziemidowicz
195
First you need to adjust website configuration file (web.xml), then you need to change main configuration file (sync.properties).
196
*Service configuration (web.xml)*
197
Go to your_webapps_folder/SqliteSync/WEB-INF/web.xml and open for edit. Navigate to section:
198
<pre><code class="xml">
199
<env-entry>
200
<env-entry-name>working-dir</env-entry-name>
201
<env-entry-type>java.lang.String</env-entry-type>
202
<env-entry-value>/your/working/dir/sqlite-sync/</env-entry-value>
203
</env-entry>
204
</code></pre>
205
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:
206
<pre>
207
\working_dir\config\sync.properties
208
</pre>
209
*IMPORTANT* Restart service after changing web.xml. Make sure that Tomcat has read/write access to working dir.
210
Sample configurations for MySQL server
211
<pre>
212
DB_ENGINE = mysql
213
DBURL = jdbc:mysql://server:3306/dbname?rewriteBatchedStatements=true
214
DBUSER = user
215
DBPASS = pass
216
DBDRIVER = com.mysql.cj.jdbc.Driver
217
DATE_FORMAT = yyyy-MM-dd HH:mm:ss
218
HISTORY_DAYS = 7
219
LOG_LEVEL = 4
220
</pre>
221
If you faced a timezone error after configuration in `sync.properties` add at the end of connection string:
222
<pre>
223
&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
224
</pre>
225
*Keys explanation*
226 21 Tomek Dziemidowicz
*DB_ENGINE* - type of database engine. Available options:
227
* mysql
228
* mssql
229
* postgresql
230
* oracle
231
232 19 Tomek Dziemidowicz
*LOG_LEVEL* - defain details level for log
233
> 4: TRACE, DEBUG, INFO, WARN; (default)
234
> 3: DEBUG, INFO, WARN; 
235
> 2: INFO,WARN; 
236
> 1: WARN; 
237
> 0 - disable logs
238
*DATE_FORMAT* - set format of date 
239
default format: yyyy-MM-dd HH:mm:ss 
240
*HISTORY_DAYS* - How long files with sync data will be kept
241
default value: 7
242
When you use MySQL database DO NOT remove from the end of the connection string:
243
<pre>
244
?rewriteBatchedStatements=true
245 20 Tomek Dziemidowicz
</pre>
246 19 Tomek Dziemidowicz
247 2 Tomek Dziemidowicz
h2. Supported databases
248
249 30 Tomek Dziemidowicz
AMPLI-SYNC supports those databases:
250
251
* MySQL
252
* Microsoft SQL Server (2005 and newer)
253
* Oracle
254
* PostgreSQL
255
256 31 Tomek Dziemidowicz
h3. Supported columns data types
257
258
SQLite-sync.com uses own conversion table to match column data types when schema from master database is converted to sqlite database.
259
* blob
260
* longblob
261
* varbinary
262
* binary
263
* image
264
* mediumblob
265
* varbinarymax
266
* byte[]
267
* longtext
268
* varchar
269
* nvarchar
270
* char
271
* varcharmax
272
* enum
273
* mediumtext
274
* text
275
* string
276
* geography
277
* geometry
278
* hierarchyid
279
* nchar
280
* ntext
281
* nvarcharmax
282
* userdefineddatatype
283
* userdefinedtabletype
284
* userdefinedtype
285
* variant
286
* xml
287
* tinytext
288
* set
289
* time
290
* timestamp
291
* year
292
* datetime
293
* uniqueidentifier
294
* datetime2
295
* date
296
* mediumint
297
* bit
298
* tinyint
299
* smallint
300
* bigint
301
* int
302
* boolean
303
* byte
304
* long
305
* int64
306
* serial
307
* int32
308
* smalldatetime
309
* double
310
* float
311
* numeric
312
* decimal
313
* real
314
* money
315
316 32 Tomek Dziemidowicz
h3. Primary Key requirements
317
318
Single and mupltiple columns are supported as primary key.
319
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. 
320 30 Tomek Dziemidowicz
321 2 Tomek Dziemidowicz
h2. Samples