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_DEV_DATA_VERSION_BY_DEVID_SQL = 259 "DELETE FROM sync_data WHERE device=? AND key LIKE 'naturalbase_cloud_version_%';"; 260 261 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_SQL = 262 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0);"; 263 264 constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_USERID_SQL = 265 "DELETE FROM sync_data WHERE (flag&0x100!=0) AND hash_key IN" \ 266 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 267 268 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_USERID_SQL = 269 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE (flag&0x100!=0) AND hash_key IN" \ 270 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 271 272 constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL = 273 "DELETE FROM sync_data WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \ 274 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);"; 275 276 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL = 277 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \ 278 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);"; 279 280 constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_DEVID_SQL = 281 "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE hash_key IN" \ 282 "(SELECT hash_key FROM sync_data WHERE device =?);"; 283 284 constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_USERID_SQL = 285 "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =?;"; 286 287 constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL = 288 "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 289 "(SELECT hash_key FROM sync_data WHERE device =?);"; 290 291 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_DEVID_SQL = 292 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE hash_key IN" \ 293 "(SELECT hash_key FROM sync_data WHERE device =?);"; 294 295 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL = 296 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 297 "(SELECT hash_key FROM sync_data WHERE device =?);"; 298 299 // Check whether the hashKey is the same but the userId is different 300 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_HASHKEY_SQL = 301 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 302 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid !=?);"; 303 304 constexpr const char *SELECT_CLOUD_DEV_DATA_BY_USERID_SQL = 305 "SELECT * FROM sync_data WHERE (flag&0x100!=0) AND hash_key IN" \ 306 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 307 308 constexpr const char *REMOVE_CLOUD_ALL_HWM_DATA_SQL = 309 "DELETE FROM meta_data WHERE KEY LIKE 'naturalbase_cloud_meta_sync_data_%';"; 310 311 constexpr const char *REMOVE_CLOUD_HWM_DATA_BY_USERID_SQL = 312 "DELETE FROM meta_data WHERE KEY =?;"; 313 314 constexpr const char *UPDATE_SYNC_DATA_KEY_SQL = 315 "UPDATE sync_data SET key=translate_key(key), hash_key=cal_hash_key(key) WHERE flag&0x01=0"; 316 317 constexpr const char *FUNC_NAME_TRANSLATE_KEY = "translate_key"; 318 319 constexpr const char *FUNC_NAME_CAL_HASH_KEY = "cal_hash_key"; 320 321 constexpr const char *QUERY_COUNT_HEAD = "SELECT count(1) "; 322 323 constexpr const char *QUERY_CLOUD_SYNC_DATA_HEAD = "SELECT key, value, flag, device, ori_device, " 324 "sync_data.hash_key, w_timestamp, modify_time, create_time, cloud_gid, version, sync_data.rowid, cloud_flag "; 325 326 constexpr const char *QUERY_CLOUD_SYNC_DATA_DETAIL = "FROM sync_data LEFT JOIN " 327 "(SELECT userid, cloud_gid, version, hash_key, cloud_flag FROM naturalbase_kv_aux_sync_data_log WHERE userid=?)" 328 " AS log_table ON sync_data.hash_key = log_table.hash_key "; 329 330 constexpr const char *QUERY_CLOUD_SYNC_DATA_CONDITION = 331 "WHERE modify_time > ? AND ((cloud_gid is not null and cloud_gid != '') OR " 332 "((cloud_gid is null OR cloud_gid = '') and flag&0x01=0)) AND flag&0x200=0"; 333 334 constexpr const char *QUERY_CLOUD_VERSION_RECORD_CONDITION = "WHERE key = ? AND flag & 0x200 != 0"; 335 336 constexpr const char *QUERY_CLOUD_SYNC_DATA_LOG = "SELECT sync_data.rowid, flag, device, ori_device, " 337 "modify_time, create_time, cloud_gid, sync_data.hash_key, sync_data.key, version, cloud_flag FROM " 338 "sync_data LEFT JOIN naturalbase_kv_aux_sync_data_log ON " 339 "sync_data.hash_key = naturalbase_kv_aux_sync_data_log.hash_key "; 340 341 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_HEAD = "SELECT key, value, flag, device, sync_data.hash_key " 342 "FROM sync_data WHERE key LIKE 'naturalbase_cloud_version_%' "; 343 344 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_DEVICE_CONDITION = "AND device = ? AND flag&0x200 != 0"; 345 346 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_EMPTY_DEVICE_CONDITION = "AND flag&0x200 != 0"; 347 348 constexpr const char *INSERT_CLOUD_SYNC_DATA_LOG = "INSERT OR REPLACE INTO naturalbase_kv_aux_sync_data_log " 349 "VALUES(?,?,?,?,?)"; 350 constexpr const char *UPSERT_CLOUD_SYNC_DATA_LOG = "INSERT INTO naturalbase_kv_aux_sync_data_log VALUES(?,?,?,?,0)" 351 "ON CONFLICT(userid, hash_key) DO UPDATE SET cloud_gid=?, version=?"; 352 353 constexpr const char *UPDATE_CLOUD_SYNC_DATA_LOG = "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_gid=?, " 354 "version=?,cloud_flag=? WHERE userid=? AND hash_key=?"; 355 356 constexpr const char *SET_SYNC_DATA_NO_FORCE_PUSH = "UPDATE sync_data SET flag=flag|0x40 WHERE hash_key=?"; 357 358 constexpr const char *SET_SYNC_DATA_FORCE_PUSH = "UPDATE sync_data SET flag=flag&(~0x40) WHERE hash_key=?"; 359 360 constexpr const char *UPDATE_TIMESTAMP = "UPDATE sync_data SET timestamp=?, modify_time=? WHERE hash_key=?"; 361 362 constexpr const char *SELECT_SYNC_ENTRIES_BY_DEVICE_SQL = 363 "SELECT key,value FROM sync_data WHERE device=? AND flag&0x200=0"; 364 365 constexpr const char *SELECT_COMPENSATE_SYNC_KEY_SQL = 366 "SELECT key FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table on sync_data.hash_key" 367 "= log_table.hash_key WHERE log_table.cloud_flag=log_table.cloud_flag|0x10"; 368 369 constexpr const char *SELECT_CLOUD_GID_SQL = 370 "SELECT cloud_gid FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table" 371 " on sync_data.hash_key = log_table.hash_key WHERE log_table.userid=?"; 372 373 constexpr const char *MARK_UPLOAD_SUCCESS = 374 "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_flag=cloud_flag|0x400 " 375 "WHERE hash_key=? AND userid=? "; 376 377 constexpr const char *CHECK_DATA_CHANGED = 378 "SELECT COUNT(1) FROM sync_data WHERE modify_time=? AND hash_key=?"; 379 380 constexpr const char *NATURALBASE_KV_AUX_SYNC_DATA_LOG_TABLE_NAME = "naturalbase_kv_aux_sync_data_log"; 381 382 const int BIND_KV_KEY_INDEX = 1; 383 const int BIND_KV_VAL_INDEX = 2; 384 const int BIND_LOCAL_TIMESTAMP_INDEX = 3; 385 const int BIND_LOCAL_HASH_KEY_INDEX = 4; 386 387 // binding index just for the get sync data sql 388 const int BIND_BEGIN_STAMP_INDEX = 1; 389 const int BIND_END_STAMP_INDEX = 2; 390 391 // mainDB 392 const int BIND_SYNC_KEY_INDEX = 1; 393 const int BIND_SYNC_VAL_INDEX = 2; 394 const int BIND_SYNC_STAMP_INDEX = 3; 395 const int BIND_SYNC_FLAG_INDEX = 4; 396 const int BIND_SYNC_DEV_INDEX = 5; 397 const int BIND_SYNC_ORI_DEV_INDEX = 6; 398 const int BIND_SYNC_HASH_KEY_INDEX = 7; 399 const int BIND_SYNC_W_TIME_INDEX = 8; 400 const int BIND_SYNC_MODIFY_TIME_INDEX = 9; 401 const int BIND_SYNC_CREATE_TIME_INDEX = 10; 402 403 const int BIND_SYNC_UPDATE_W_TIME_INDEX = 7; 404 const int BIND_SYNC_UPDATE_MODIFY_TIME_INDEX = 8; 405 const int BIND_SYNC_UPDATE_CREATE_TIME_INDEX = 9; 406 const int BIND_SYNC_UPDATE_HASH_KEY_INDEX = 10; 407 408 // cacheDB 409 const int BIND_CACHE_LOCAL_KEY_INDEX = 1; 410 const int BIND_CACHE_LOCAL_VAL_INDEX = 2; 411 const int BIND_CACHE_LOCAL_TIMESTAMP_INDEX = 3; 412 const int BIND_CACHE_LOCAL_HASH_KEY_INDEX = 4; 413 const int BIND_CACHE_LOCAL_FLAG_INDEX = 5; 414 415 const int BIND_CACHE_SYNC_KEY_INDEX = 1; 416 const int BIND_CACHE_SYNC_VAL_INDEX = 2; 417 const int BIND_CACHE_SYNC_STAMP_INDEX = 3; 418 const int BIND_CACHE_SYNC_FLAG_INDEX = 4; 419 const int BIND_CACHE_SYNC_DEV_INDEX = 5; 420 const int BIND_CACHE_SYNC_ORI_DEV_INDEX = 6; 421 const int BIND_CACHE_SYNC_HASH_KEY_INDEX = 7; 422 const int BIND_CACHE_SYNC_W_TIME_INDEX = 8; 423 const int BIND_CACHE_SYNC_VERSION_INDEX = 9; 424 425 // select result index for the item for sync database 426 const int SYNC_RES_KEY_INDEX = 0; 427 const int SYNC_RES_VAL_INDEX = 1; 428 const int SYNC_RES_TIME_INDEX = 2; 429 const int SYNC_RES_FLAG_INDEX = 3; 430 const int SYNC_RES_DEVICE_INDEX = 4; 431 const int SYNC_RES_ORI_DEV_INDEX = 5; 432 const int SYNC_RES_HASH_KEY_INDEX = 6; 433 const int SYNC_RES_W_TIME_INDEX = 7; 434 const int SYNC_RES_VERSION_INDEX = 8; // Available in cacheDB. 435 436 // get kv data Response index 437 const int GET_KV_RES_LOCAL_TIME_INDEX = 1; 438 const int GET_KV_RES_SYNC_TIME_INDEX = 1; 439 440 const int BIND_ORI_DEVICE_ID = 0; 441 const int BIND_PRE_DEVICE_ID = 1; 442 443 constexpr int BIND_CLOUD_USER = 1; 444 constexpr int BIND_CLOUD_TIMESTAMP = 2; 445 446 constexpr int CLOUD_QUERY_KEY_INDEX = 0; 447 constexpr int CLOUD_QUERY_VALUE_INDEX = 1; 448 constexpr int CLOUD_QUERY_FLAG_INDEX = 2; 449 constexpr int CLOUD_QUERY_DEV_INDEX = 3; 450 constexpr int CLOUD_QUERY_ORI_DEV_INDEX = 4; 451 constexpr int CLOUD_QUERY_HASH_KEY_INDEX = 5; 452 constexpr int CLOUD_QUERY_DEV_CREATE_TIME_INDEX = 6; 453 constexpr int CLOUD_QUERY_MODIFY_TIME_INDEX = 7; 454 constexpr int CLOUD_QUERY_CREATE_TIME_INDEX = 8; 455 constexpr int CLOUD_QUERY_CLOUD_GID_INDEX = 9; 456 constexpr int CLOUD_QUERY_VERSION_INDEX = 10; 457 constexpr int CLOUD_QUERY_ROW_ID_INDEX = 11; 458 constexpr int CLOUD_QUERY_CLOUD_FLAG_INDEX = 12; 459 460 constexpr int CLOUD_QUERY_COUNT_INDEX = 0; 461 462 constexpr int BIND_CLOUD_VERSION_RECORD_USER_INDEX = 1; 463 constexpr int BIND_CLOUD_VERSION_RECORD_KEY_INDEX = 2; 464 465 constexpr int BIND_CLOUD_VERSION_DEVICE_INDEX = 1; 466 467 constexpr int BIND_INSERT_USER_INDEX = 1; 468 constexpr int BIND_INSERT_HASH_KEY_INDEX = 2; 469 constexpr int BIND_INSERT_CLOUD_GID_INDEX = 3; 470 constexpr int BIND_INSERT_VERSION_INDEX = 4; 471 constexpr int BIND_INSERT_CLOUD_FLAG_INDEX = 5; 472 473 // use in get entries by device sql 474 constexpr int BIND_GET_ENTRIES_DEVICE_INDEX = 1; 475 // use in remove cloud flag 476 constexpr int BIND_HASH_KEY_INDEX = 1; 477 const Key REMOVE_DEVICE_DATA_KEY = {'r', 'e', 'm', 'o', 'v', 'e'}; 478 } // namespace DistributedDB 479 480 #endif // SQLITE_SINGLE_VER_STORAGE_EXECUTOR_SQL_H 481