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