Project

General

Profile

Wiki » History » Version 32

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