1 /*
2  * Copyright (c) 2021 Huawei Device Co., Ltd.
3  * Licensed under the Apache License, Version 2.0 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at
6  *
7  *     http://www.apache.org/licenses/LICENSE-2.0
8  *
9  * Unless required by applicable law or agreed to in writing, software
10  * distributed under the License is distributed on an "AS IS" BASIS,
11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12  * See the License for the specific language governing permissions and
13  * limitations under the License.
14  */
15 
16 #ifndef SQLITE_SINGLE_VER_STORAGE_EXECUTOR_SQL_H
17 #define SQLITE_SINGLE_VER_STORAGE_EXECUTOR_SQL_H
18 
19 #include <string>
20 
21 #include "types_export.h"
22 
23 namespace DistributedDB {
24     //  cache.sync_data is design for migrating action after process restart
25     constexpr const char *INSERT_LOCAL_SQL =
26         "INSERT OR REPLACE INTO local_data VALUES(?,?,?,?);";
27     constexpr const char *INSERT_LOCAL_SQL_FROM_CACHEHANDLE =
28         "INSERT OR REPLACE INTO maindb.local_data VALUES(?,?,?,?);";
29 
30     constexpr const char *INSERT_CACHE_LOCAL_SQL =
31         "INSERT OR REPLACE INTO local_data VALUES(?,?,?,?,?);";
32 
33     constexpr const char *UPDATE_LOCAL_SQL_FROM_CACHEHANDLE =
34         "UPDATE maindb.local_data SET key=?,value=?,timestamp=? where hash_key=?";
35 
36     constexpr const char *UPDATE_CACHE_LOCAL_SQL =
37         "UPDATE local_data SET key=?,value=?,timestamp=? where hash_key=?";
38 
39     constexpr const char *INSERT_META_SQL =
40         "INSERT OR REPLACE INTO meta_data VALUES(?,?);";
41 
42     constexpr const char *INSERT_ATTACH_META_SQL =
43         "INSERT OR REPLACE INTO meta.meta_data VALUES(?,?);";
44 
45     constexpr const char *INSERT_SYNC_SQL =
46         "INSERT OR REPLACE INTO sync_data VALUES(?,?,?,?,?,?,?,?,?,?);";
47 
48     constexpr const char *UPDATE_SYNC_SQL =
49         "UPDATE sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=?," \
50         "modify_time=?,create_time=? WHERE hash_key=?;";
51 
52     constexpr const char *INSERT_CACHE_SYNC_SQL =
53         "INSERT OR REPLACE INTO sync_data VALUES(?,?,?,?,?,?,?,?,?);";
54     constexpr const char *INSERT_CACHE_SYNC_SQL_FROM_MAINHANDLE =
55         "INSERT OR REPLACE INTO cache.sync_data VALUES(?,?,?,?,?,?,?,?,?);";
56 
57     constexpr const char *UPDATE_CACHE_SYNC_SQL =
58         "UPDATE sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=? WHERE hash_key=?;";
59 
60     constexpr const char *UPDATE_CACHE_SYNC_SQL_FROM_MAINHANDLE =
61         "UPDATE cache.sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=? "
62         "WHERE hash_key=?;";
63 
64     constexpr const char *DELETE_LOCAL_SQL =
65         "DELETE FROM local_data WHERE key=?;";
66     constexpr const char *DELETE_LOCAL_SQL_FROM_CACHEHANDLE =
67         "DELETE FROM maindb.local_data WHERE key=?;";
68 
69     constexpr const char *SELECT_ALL_META_KEYS =
70         "SELECT key FROM meta_data;";
71 
72     constexpr const char *SELECT_ATTACH_ALL_META_KEYS =
73         "SELECT key FROM meta.meta_data;";
74 
75     constexpr const char *SELECT_META_KEYS_BY_PREFIX =
76         "SELECT key FROM meta_data where key like ?;";
77 
78     constexpr const char *SELECT_ATTACH_META_KEYS_BY_PREFIX =
79         "SELECT key FROM meta.meta_data where key like ?;";
80 
81     constexpr const char *SELECT_ALL_SYNC_ENTRIES_BY_DEV =
82         "SELECT key, value FROM sync_data WHERE device=? AND (flag&0x03=0);";
83 
84     constexpr const char *SELECT_ALL_SYNC_ENTRIES =
85         "SELECT key, value FROM sync_data WHERE (flag&0x03=0);";
86 
87     constexpr const char *SELECT_ALL_SYNC_ENTRIES_BY_DEV_FROM_CACHEHANDLE =
88         "SELECT key, value FROM maindb.sync_data WHERE device=? AND (flag&0x03=0);";
89 
90     constexpr const char *SELECT_ALL_SYNC_ENTRIES_FROM_CACHEHANDLE =
91         "SELECT key, value FROM maindb.sync_data WHERE (flag&0x03=0);";
92 
93     constexpr const char *SELECT_LOCAL_VALUE_TIMESTAMP_SQL =
94         "SELECT value, timestamp FROM local_data WHERE key=?;";
95 
96     constexpr const char *SELECT_SYNC_SQL =
97         "SELECT * FROM sync_data WHERE key=?;";
98 
99     constexpr const char *SELECT_SYNC_VALUE_WTIMESTAMP_SQL =
100         "SELECT value, w_timestamp FROM sync_data WHERE key=? AND (flag&0x200=0);";
101 
102     constexpr const char *SELECT_SYNC_HASH_SQL =
103         "SELECT * FROM sync_data WHERE hash_key=?;";
104 
105     constexpr const char *SELECT_CACHE_SYNC_HASH_SQL =
106         "SELECT * FROM sync_data WHERE hash_key=? AND version=?;";
107     constexpr const char *SELECT_CACHE_SYNC_HASH_SQL_FROM_MAINHANDLE =
108         "SELECT * FROM cache.sync_data WHERE hash_key=? AND version=?;";
109 
110     constexpr const char *SELECT_LOCAL_HASH_SQL =
111         "SELECT * FROM local_data WHERE hash_key=?;";
112 
113     constexpr const char *SELECT_CACHE_LOCAL_HASH_SQL =
114         "SELECT * FROM local_data WHERE hash_key=?;";
115 
116     constexpr const char *SELECT_META_VALUE_SQL =
117         "SELECT value FROM meta_data WHERE key=?;";
118 
119     constexpr const char *SELECT_ATTACH_META_VALUE_SQL =
120         "SELECT value FROM meta.meta_data WHERE key=?;";
121 
122     constexpr const char *SELECT_MAX_TIMESTAMP_SQL =
123         "SELECT MAX(timestamp) FROM sync_data;";
124     constexpr const char *SELECT_MAX_TIMESTAMP_SQL_FROM_CACHEHANDLE =
125         "SELECT MAX(timestamp) FROM maindb.sync_data;";
126 
127     constexpr const char *SELECT_NATIVE_MIN_TIMESTAMP_IN_CACHE_SYNC_DATA_SQL =
128         "SELECT MIN(timestamp) FROM sync_data WHERE flag&0x02=0x02;";
129     constexpr const char *SELECT_NATIVE_MIN_TIMESTAMP_IN_CACHE_SYNC_DATA_SQL_FROM_MAINHANDLE =
130         "SELECT MIN(timestamp) FROM cache.sync_data WHERE flag&0x02=0x02;";
131 
132     constexpr const char *SELECT_SYNC_ENTRIES_SQL =
133         "SELECT * FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x02=0x02) AND (flag&0x200=0) "
134         "ORDER BY timestamp ASC;";
135 
136     constexpr const char *SELECT_SYNC_DELETED_ENTRIES_SQL =
137         "SELECT * FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x03=0x03) AND (flag&0x200=0) "
138         "ORDER BY timestamp ASC;";
139 
140     constexpr const char *SELECT_SYNC_MODIFY_SQL =
141         "SELECT * FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x03=0x02) AND (flag&0x200=0) "
142         "ORDER BY timestamp ASC;";
143 
144     constexpr const char *SELECT_SYNC_PREFIX_SQL =
145         "SELECT key, value FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) "
146         "ORDER BY key ASC;";
147 
148     constexpr const char *SELECT_SYNC_KEY_PREFIX_SQL =
149         "SELECT key FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) ORDER BY key ASC;";
150 
151     constexpr const char *SELECT_SYNC_ROWID_PREFIX_SQL =
152         "SELECT rowid FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) ORDER BY key ASC;";
153 
154     constexpr const char *SELECT_SYNC_DATA_BY_ROWID_SQL =
155         "SELECT key, value FROM sync_data WHERE rowid=?;";
156 
157     constexpr const char *SELECT_LOCAL_PREFIX_SQL =
158         "SELECT key, value FROM local_data WHERE key>=? AND key<=? ORDER BY key ASC;";
159 
160     constexpr const char *SELECT_COUNT_SYNC_PREFIX_SQL =
161         "SELECT count(key) FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0);";
162 
163     constexpr const char *REMOVE_DEV_DATA_SQL =
164         "DELETE FROM sync_data WHERE device=? AND (flag&0x02=0);";
165 
166     constexpr const char *REMOVE_ALL_DEV_DATA_SQL =
167         "DELETE FROM sync_data WHERE (flag&0x02=0);";
168 
169     constexpr const char *REMOVE_DEV_DATA_SQL_FROM_CACHEHANDLE =
170         "DELETE FROM maindb.sync_data WHERE device=? AND (flag&0x02=0);";
171 
172     constexpr const char *REMOVE_ALL_DEV_DATA_SQL_FROM_CACHEHANDLE =
173         "DELETE FROM maindb.sync_data WHERE (flag&0x02=0);";
174 
175     constexpr const char *SELECT_ENTRY_DEVICE =
176         "SELECT ori_device, device FROM sync_data WHERE key=?;";
177 
178     // sql for migrating data
179     constexpr const char *MIGRATE_LOCAL_SQL_FROM_CACHEHANDLE =
180         "INSERT OR REPLACE INTO maindb.local_data select key, value, timestamp, hash_key from main.local_data;";
181     constexpr const char *MIGRATE_LOCAL_SQL_FROM_MAINHANDLE =
182         "INSERT OR REPLACE INTO main.local_data select key, value, timestamp, hash_key from cache.local_data;";
183 
184     constexpr const char *MIGRATE_VACUUM_LOCAL_SQL_FROM_CACHEHANDLE =
185         "DELETE FROM maindb.local_data where hash_key in (select hash_key FROM maindb.local_data where key is null);";
186     constexpr const char *MIGRATE_VACUUM_LOCAL_SQL_FROM_MAINHANDLE =
187         "DELETE FROM main.local_data where hash_key in (select hash_key FROM main.local_data where key is null);";
188 
189     // version is index, order by better than MIN()
190     constexpr const char *MIGRATE_SELECT_MIN_VER_CACHEDATA_FROM_CACHEHANDLE =
191         "SELECT * FROM sync_data where version = (select version from sync_data order by version limit 1);";
192     constexpr const char *MIGRATE_SELECT_MIN_VER_CACHEDATA_FROM_MAINHANDLE =
193         "SELECT * FROM cache.sync_data where version = (select version from cache.sync_data order by version limit 1);";
194 
195     constexpr const char *GET_MAX_VER_CACHEDATA_FROM_CACHEHANDLE =
196         "select version from sync_data order by version DESC limit 1;";
197     constexpr const char *GET_MAX_VER_CACHEDATA_FROM_MAINHANDLE =
198         "select version from cache.sync_data order by version DESC limit 1;";
199 
200     constexpr const char *MIGRATE_INSERT_DATA_TO_MAINDB_FROM_CACHEHANDLE =
201         "INSERT INTO maindb.sync_data VALUES(?,?,?,?,?,?,?,?,?,?);";
202     constexpr const char *MIGRATE_UPDATE_DATA_TO_MAINDB_FROM_CACHEHANDLE =
203         "UPDATE maindb.sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=?,"
204         "modify_time=?,create_time=? WHERE hash_key=?;";
205 
206     constexpr const char *MIGRATE_INSERT_DATA_TO_MAINDB_FROM_MAINHANDLE =
207         "INSERT INTO sync_data VALUES(?,?,?,?,?,?,?,?,?,?);";
208     constexpr const char *MIGRATE_UPDATE_DATA_TO_MAINDB_FROM_MAINHANDLE =
209         "UPDATE sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=?,"
210         "modify_time=?,create_time=? WHERE hash_key=?;";
211 
212     constexpr const char *MIGRATE_DEL_DATA_BY_VERSION_FROM_CACHEHANDLE =
213         "DELETE FROM sync_data WHERE version=?;";
214     constexpr const char *MIGRATE_DEL_DATA_BY_VERSION_FROM_MAINHANDLE =
215         "DELETE FROM cache.sync_data WHERE version=?;";
216 
217     constexpr const char *SELECT_MAIN_SYNC_HASH_SQL_FROM_CACHEHANDLE =
218         "SELECT * FROM maindb.sync_data WHERE hash_key=?;";
219 
220     constexpr const char *REMOVE_META_VALUE_SQL =
221         "DELETE FROM meta_data WHERE key=?;";
222     constexpr const char *REMOVE_ATTACH_META_VALUE_SQL =
223         "DELETE FROM meta.meta_data WHERE key=?;";
224 
225     constexpr const char *CHECK_DB_INTEGRITY_SQL = "PRAGMA integrity_check;";
226 
227     constexpr const char *REMOVE_META_VALUE_BY_KEY_PREFIX_SQL =
228         "DELETE FROM meta_data WHERE key>=? AND key<=?;";
229     constexpr const char *REMOVE_ATTACH_META_VALUE_BY_KEY_PREFIX_SQL =
230         "DELETE FROM meta.meta_data WHERE key>=? AND key<=?;";
231 
232     constexpr const char *DELETE_SYNC_DATA_WITH_HASHKEY = "DELETE FROM sync_data where hash_key = ?;";
233 
234     constexpr const char *DELETE_SYNC_DATA_WITH_HASHKEY_FROM_CACHEHANDLE =
235         "DELETE FROM maindb.sync_data where hash_key = ?;";
236 
237     constexpr const char *GET_SYNC_DATA_TIRGGER_SQL =
238         "SELECT name FROM SQLITE_MASTER WHERE TYPE = 'trigger' AND TBL_NAME = 'sync_data' AND name like ?;";
239 
240     constexpr const char *REMOVE_CLOUD_ALL_LOG_DATA_SQL =
241         "DELETE FROM naturalbase_kv_aux_sync_data_log;";
242 
243     constexpr const char *REMOVE_ALL_DEV_SYNC_DATA_SQL =
244         "DELETE FROM sync_data WHERE (flag&0x02=0) AND (flag&0x100=0);";
245 
246     constexpr const char *REMOVE_DEV_SYNC_DATA_BY_DEV_ID_SQL =
247         "DELETE FROM sync_data WHERE device=? AND (flag&0x02=0) AND (flag&0x100=0);";
248 
249     constexpr const char *REMOVE_CLOUD_ALL_DEV_DATA_SQL =
250         "DELETE FROM sync_data WHERE (flag&0x100!=0);";
251 
252     constexpr const char *UPDATE_CLOUD_ALL_DEV_DATA_SQL =
253         "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE (flag&0x100!=0);";
254 
255     constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_DEVID_SQL =
256         "DELETE FROM sync_data WHERE device=? AND (flag&0x100!=0);";
257 
258     constexpr const char *REMOVE_CLOUD_ALL_DEV_DATA_VERSION_SQL =
259         "DELETE FROM sync_data WHERE key LIKE 'naturalbase_cloud_version_%' AND length(device)!=0;";
260 
261     constexpr const char *REMOVE_CLOUD_DEV_DATA_VERSION_BY_DEVID_SQL =
262         "DELETE FROM sync_data WHERE device=? AND key LIKE 'naturalbase_cloud_version_%';";
263 
264     constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_SQL =
265         "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0);";
266 
267     constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_USERID_SQL =
268         "DELETE FROM sync_data WHERE (flag&0x100!=0) AND hash_key IN" \
269             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);";
270 
271     constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_USERID_SQL =
272         "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE (flag&0x100!=0) AND hash_key IN" \
273             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);";
274 
275     constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL =
276         "DELETE FROM sync_data WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \
277             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);";
278 
279     constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL =
280         "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \
281             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);";
282 
283     constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_DEVID_SQL =
284         "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE hash_key IN" \
285             "(SELECT hash_key FROM sync_data WHERE device =?);";
286 
287     constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_USERID_SQL =
288         "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =?;";
289 
290     constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL =
291         "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \
292             "(SELECT hash_key FROM sync_data WHERE device =?);";
293 
294     constexpr const char *SELECT_CLOUD_LOG_DATA_BY_DEVID_SQL =
295         "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE hash_key IN" \
296             "(SELECT hash_key FROM sync_data WHERE device =?);";
297 
298     constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL =
299         "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \
300             "(SELECT hash_key FROM sync_data WHERE device =?);";
301 
302     // Check whether the hashKey is the same but the userId is different
303     constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_HASHKEY_SQL =
304         "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \
305             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid !=?);";
306 
307     constexpr const char *SELECT_CLOUD_DEV_DATA_BY_USERID_SQL =
308         "SELECT * FROM sync_data WHERE (flag&0x100!=0) AND hash_key IN" \
309             "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);";
310 
311     constexpr const char *REMOVE_CLOUD_ALL_HWM_DATA_SQL =
312         "DELETE FROM meta_data WHERE KEY LIKE 'naturalbase_cloud_meta_sync_data_%';";
313 
314     constexpr const char *REMOVE_CLOUD_HWM_DATA_BY_USERID_SQL =
315         "DELETE FROM meta_data WHERE KEY =?;";
316 
317     constexpr const char *UPDATE_SYNC_DATA_KEY_SQL =
318         "UPDATE sync_data SET key=translate_key(key), hash_key=cal_hash_key(key) WHERE flag&0x01=0";
319 
320     constexpr const char *FUNC_NAME_TRANSLATE_KEY = "translate_key";
321 
322     constexpr const char *FUNC_NAME_CAL_HASH_KEY = "cal_hash_key";
323 
324     constexpr const char *QUERY_COUNT_HEAD = "SELECT count(1) ";
325 
326     constexpr const char *QUERY_CLOUD_SYNC_DATA_HEAD = "SELECT key, value, flag, device, ori_device, "
327         "sync_data.hash_key, w_timestamp, modify_time, create_time, cloud_gid, version, sync_data.rowid, cloud_flag ";
328 
329     constexpr const char *QUERY_CLOUD_SYNC_DATA_DETAIL = "FROM sync_data LEFT JOIN "
330         "(SELECT userid, cloud_gid, version, hash_key, cloud_flag FROM naturalbase_kv_aux_sync_data_log WHERE userid=?)"
331         " AS log_table ON sync_data.hash_key = log_table.hash_key ";
332 
333     constexpr const char *QUERY_CLOUD_SYNC_DATA_CONDITION =
334         "WHERE modify_time > ? AND ((cloud_gid is not null and cloud_gid != '') OR "
335         "((cloud_gid is null OR cloud_gid = '') and flag&0x01=0)) AND flag&0x200=0";
336 
337     constexpr const char *QUERY_CLOUD_VERSION_RECORD_CONDITION = "WHERE key = ? AND flag & 0x200 != 0";
338 
339     constexpr const char *QUERY_CLOUD_SYNC_DATA_LOG = "SELECT sync_data.rowid, flag, device, ori_device, "
340         "modify_time, create_time, cloud_gid, sync_data.hash_key, sync_data.key, version, cloud_flag FROM "
341         "sync_data LEFT JOIN naturalbase_kv_aux_sync_data_log ON "
342         "sync_data.hash_key = naturalbase_kv_aux_sync_data_log.hash_key ";
343 
344     constexpr const char *QUERY_CLOUD_SYNC_DATA_LOG_WITH_USERID = "SELECT sync_data.rowid, flag, device, ori_device, "
345         "modify_time, create_time, cloud_gid, sync_data.hash_key, sync_data.key, version, cloud_flag FROM "
346         "sync_data LEFT JOIN (select * from naturalbase_kv_aux_sync_data_log where userid=?) as "
347         "naturalbase_kv_aux_sync_data_log ON sync_data.hash_key = naturalbase_kv_aux_sync_data_log.hash_key ";
348 
349     constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_HEAD = "SELECT key, value, flag, device, sync_data.hash_key "
350         "FROM sync_data WHERE key LIKE 'naturalbase_cloud_version_%' ";
351 
352     constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_DEVICE_CONDITION = "AND device = ? AND flag&0x200 != 0";
353 
354     constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_EMPTY_DEVICE_CONDITION = "AND flag&0x200 != 0";
355 
356     constexpr const char *INSERT_CLOUD_SYNC_DATA_LOG = "INSERT OR REPLACE INTO naturalbase_kv_aux_sync_data_log "
357         "VALUES(?,?,?,?,?)";
358     constexpr const char *UPSERT_CLOUD_SYNC_DATA_LOG = "INSERT INTO naturalbase_kv_aux_sync_data_log VALUES(?,?,?,?,0)"
359         "ON CONFLICT(userid, hash_key) DO UPDATE SET cloud_gid=?, version=?";
360 
361     constexpr const char *UPDATE_CLOUD_SYNC_DATA_LOG = "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_gid=?, "
362         "version=?,cloud_flag=? WHERE userid=? AND hash_key=?";
363 
364     constexpr const char *SET_SYNC_DATA_NO_FORCE_PUSH = "UPDATE sync_data SET flag=flag|0x40 WHERE hash_key=?";
365 
366     constexpr const char *SET_SYNC_DATA_FORCE_PUSH = "UPDATE sync_data SET flag=flag&(~0x40) WHERE hash_key=?";
367 
368     constexpr const char *UPDATE_TIMESTAMP = "UPDATE sync_data SET timestamp=?, modify_time=? WHERE hash_key=?";
369 
370     constexpr const char *SELECT_SYNC_ENTRIES_BY_DEVICE_SQL =
371         "SELECT key,value FROM sync_data WHERE device=? AND flag&0x200=0";
372 
373     constexpr const char *SELECT_COMPENSATE_SYNC_KEY_SQL =
374         "SELECT key FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table on sync_data.hash_key"
375         "= log_table.hash_key WHERE log_table.cloud_flag=log_table.cloud_flag|0x10";
376 
377     constexpr const char *SELECT_COMPENSATE_SYNC_USERID_SQL =
378         "SELECT userid FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table on sync_data.hash_key"
379         "= log_table.hash_key WHERE log_table.cloud_flag=log_table.cloud_flag|0x10";
380 
381     constexpr const char *SELECT_CLOUD_GID_SQL =
382         "SELECT cloud_gid FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table"
383         " on sync_data.hash_key = log_table.hash_key WHERE log_table.userid=?";
384 
385     constexpr const char *MARK_UPLOAD_SUCCESS =
386         "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_flag=cloud_flag|0x400 "
387         "WHERE hash_key=? AND userid=? ";
388 
389     constexpr const char *CHECK_DATA_CHANGED =
390         "SELECT COUNT(1) FROM sync_data WHERE modify_time=? AND hash_key=?";
391 
392     constexpr const char *CHECK_DATA_DELETE =
393         "SELECT COUNT(1) FROM sync_data WHERE modify_time!=? AND hash_key=? AND flag&0x01 != 0";
394 
395     constexpr const char *NATURALBASE_KV_AUX_SYNC_DATA_LOG_TABLE_NAME = "naturalbase_kv_aux_sync_data_log";
396 
397     const int BIND_KV_KEY_INDEX = 1;
398     const int BIND_KV_VAL_INDEX = 2;
399     const int BIND_LOCAL_TIMESTAMP_INDEX = 3;
400     const int BIND_LOCAL_HASH_KEY_INDEX = 4;
401 
402     // binding index just for the get sync data sql
403     const int BIND_BEGIN_STAMP_INDEX = 1;
404     const int BIND_END_STAMP_INDEX = 2;
405 
406     // mainDB
407     const int BIND_SYNC_KEY_INDEX = 1;
408     const int BIND_SYNC_VAL_INDEX = 2;
409     const int BIND_SYNC_STAMP_INDEX = 3;
410     const int BIND_SYNC_FLAG_INDEX = 4;
411     const int BIND_SYNC_DEV_INDEX = 5;
412     const int BIND_SYNC_ORI_DEV_INDEX = 6;
413     const int BIND_SYNC_HASH_KEY_INDEX = 7;
414     const int BIND_SYNC_W_TIME_INDEX = 8;
415     const int BIND_SYNC_MODIFY_TIME_INDEX = 9;
416     const int BIND_SYNC_CREATE_TIME_INDEX = 10;
417 
418     const int BIND_SYNC_UPDATE_W_TIME_INDEX = 7;
419     const int BIND_SYNC_UPDATE_MODIFY_TIME_INDEX = 8;
420     const int BIND_SYNC_UPDATE_CREATE_TIME_INDEX = 9;
421     const int BIND_SYNC_UPDATE_HASH_KEY_INDEX = 10;
422 
423     // cacheDB
424     const int BIND_CACHE_LOCAL_KEY_INDEX = 1;
425     const int BIND_CACHE_LOCAL_VAL_INDEX = 2;
426     const int BIND_CACHE_LOCAL_TIMESTAMP_INDEX = 3;
427     const int BIND_CACHE_LOCAL_HASH_KEY_INDEX = 4;
428     const int BIND_CACHE_LOCAL_FLAG_INDEX = 5;
429 
430     const int BIND_CACHE_SYNC_KEY_INDEX = 1;
431     const int BIND_CACHE_SYNC_VAL_INDEX = 2;
432     const int BIND_CACHE_SYNC_STAMP_INDEX = 3;
433     const int BIND_CACHE_SYNC_FLAG_INDEX = 4;
434     const int BIND_CACHE_SYNC_DEV_INDEX = 5;
435     const int BIND_CACHE_SYNC_ORI_DEV_INDEX = 6;
436     const int BIND_CACHE_SYNC_HASH_KEY_INDEX = 7;
437     const int BIND_CACHE_SYNC_W_TIME_INDEX = 8;
438     const int BIND_CACHE_SYNC_VERSION_INDEX = 9;
439 
440     // select result index for the item for sync database
441     const int SYNC_RES_KEY_INDEX = 0;
442     const int SYNC_RES_VAL_INDEX = 1;
443     const int SYNC_RES_TIME_INDEX = 2;
444     const int SYNC_RES_FLAG_INDEX = 3;
445     const int SYNC_RES_DEVICE_INDEX = 4;
446     const int SYNC_RES_ORI_DEV_INDEX = 5;
447     const int SYNC_RES_HASH_KEY_INDEX = 6;
448     const int SYNC_RES_W_TIME_INDEX = 7;
449     const int SYNC_RES_VERSION_INDEX = 8; // Available in cacheDB.
450 
451     // get kv data Response index
452     const int GET_KV_RES_LOCAL_TIME_INDEX = 1;
453     const int GET_KV_RES_SYNC_TIME_INDEX = 1;
454 
455     const int BIND_ORI_DEVICE_ID = 0;
456     const int BIND_PRE_DEVICE_ID = 1;
457 
458     constexpr int BIND_CLOUD_USER = 1;
459     constexpr int BIND_CLOUD_TIMESTAMP = 2;
460 
461     constexpr int CLOUD_QUERY_KEY_INDEX = 0;
462     constexpr int CLOUD_QUERY_VALUE_INDEX = 1;
463     constexpr int CLOUD_QUERY_FLAG_INDEX = 2;
464     constexpr int CLOUD_QUERY_DEV_INDEX = 3;
465     constexpr int CLOUD_QUERY_ORI_DEV_INDEX = 4;
466     constexpr int CLOUD_QUERY_HASH_KEY_INDEX = 5;
467     constexpr int CLOUD_QUERY_DEV_CREATE_TIME_INDEX = 6;
468     constexpr int CLOUD_QUERY_MODIFY_TIME_INDEX = 7;
469     constexpr int CLOUD_QUERY_CREATE_TIME_INDEX = 8;
470     constexpr int CLOUD_QUERY_CLOUD_GID_INDEX = 9;
471     constexpr int CLOUD_QUERY_VERSION_INDEX = 10;
472     constexpr int CLOUD_QUERY_ROW_ID_INDEX = 11;
473     constexpr int CLOUD_QUERY_CLOUD_FLAG_INDEX = 12;
474 
475     constexpr int CLOUD_QUERY_COUNT_INDEX = 0;
476 
477     constexpr int BIND_CLOUD_VERSION_RECORD_USER_INDEX = 1;
478     constexpr int BIND_CLOUD_VERSION_RECORD_KEY_INDEX = 2;
479 
480     constexpr int BIND_CLOUD_VERSION_DEVICE_INDEX = 1;
481 
482     constexpr int BIND_INSERT_USER_INDEX = 1;
483     constexpr int BIND_INSERT_HASH_KEY_INDEX = 2;
484     constexpr int BIND_INSERT_CLOUD_GID_INDEX = 3;
485     constexpr int BIND_INSERT_VERSION_INDEX = 4;
486     constexpr int BIND_INSERT_CLOUD_FLAG_INDEX = 5;
487 
488     // use in get entries by device sql
489     constexpr int BIND_GET_ENTRIES_DEVICE_INDEX = 1;
490     // use in remove cloud flag
491     constexpr int BIND_HASH_KEY_INDEX = 1;
492     const Key REMOVE_DEVICE_DATA_KEY = {'r', 'e', 'm', 'o', 'v', 'e'};
493 } // namespace DistributedDB
494 
495 #endif // SQLITE_SINGLE_VER_STORAGE_EXECUTOR_SQL_H
496