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 *COUNT_SYNC_ENTRIES_SQL = 141 "SELECT count(key) FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x02=0x02) " 142 "AND (flag&0x200=0);"; 143 144 constexpr const char *COUNT_SYNC_DELETED_ENTRIES_SQL = 145 "SELECT count(key) FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x03=0x03) " 146 "AND (flag&0x200=0);"; 147 148 constexpr const char *SELECT_SYNC_MODIFY_SQL = 149 "SELECT * FROM sync_data WHERE timestamp >= ? AND timestamp < ? AND (flag&0x03=0x02) AND (flag&0x200=0) " 150 "ORDER BY timestamp ASC;"; 151 152 constexpr const char *SELECT_SYNC_PREFIX_SQL = 153 "SELECT key, value FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) " 154 "ORDER BY key ASC;"; 155 156 constexpr const char *SELECT_SYNC_KEY_PREFIX_SQL = 157 "SELECT key FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) ORDER BY key ASC;"; 158 159 constexpr const char *SELECT_SYNC_ROWID_PREFIX_SQL = 160 "SELECT rowid FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0) ORDER BY key ASC;"; 161 162 constexpr const char *SELECT_SYNC_DATA_BY_ROWID_SQL = 163 "SELECT key, value FROM sync_data WHERE rowid=?;"; 164 165 constexpr const char *SELECT_LOCAL_PREFIX_SQL = 166 "SELECT key, value FROM local_data WHERE key>=? AND key<=? ORDER BY key ASC;"; 167 168 constexpr const char *SELECT_COUNT_SYNC_PREFIX_SQL = 169 "SELECT count(key) FROM sync_data WHERE key>=? AND key<=? AND (flag&0x01=0) AND (flag&0x200=0);"; 170 171 constexpr const char *REMOVE_DEV_DATA_SQL = 172 "DELETE FROM sync_data WHERE device=? AND (flag&0x02=0);"; 173 174 constexpr const char *REMOVE_ALL_DEV_DATA_SQL = 175 "DELETE FROM sync_data WHERE (flag&0x02=0);"; 176 177 constexpr const char *REMOVE_DEV_DATA_SQL_FROM_CACHEHANDLE = 178 "DELETE FROM maindb.sync_data WHERE device=? AND (flag&0x02=0);"; 179 180 constexpr const char *REMOVE_ALL_DEV_DATA_SQL_FROM_CACHEHANDLE = 181 "DELETE FROM maindb.sync_data WHERE (flag&0x02=0);"; 182 183 constexpr const char *SELECT_ENTRY_DEVICE = 184 "SELECT ori_device, device FROM sync_data WHERE key=?;"; 185 186 // sql for migrating data 187 constexpr const char *MIGRATE_LOCAL_SQL_FROM_CACHEHANDLE = 188 "INSERT OR REPLACE INTO maindb.local_data select key, value, timestamp, hash_key from main.local_data;"; 189 constexpr const char *MIGRATE_LOCAL_SQL_FROM_MAINHANDLE = 190 "INSERT OR REPLACE INTO main.local_data select key, value, timestamp, hash_key from cache.local_data;"; 191 192 constexpr const char *MIGRATE_VACUUM_LOCAL_SQL_FROM_CACHEHANDLE = 193 "DELETE FROM maindb.local_data where hash_key in (select hash_key FROM maindb.local_data where key is null);"; 194 constexpr const char *MIGRATE_VACUUM_LOCAL_SQL_FROM_MAINHANDLE = 195 "DELETE FROM main.local_data where hash_key in (select hash_key FROM main.local_data where key is null);"; 196 197 // version is index, order by better than MIN() 198 constexpr const char *MIGRATE_SELECT_MIN_VER_CACHEDATA_FROM_CACHEHANDLE = 199 "SELECT * FROM sync_data where version = (select version from sync_data order by version limit 1);"; 200 constexpr const char *MIGRATE_SELECT_MIN_VER_CACHEDATA_FROM_MAINHANDLE = 201 "SELECT * FROM cache.sync_data where version = (select version from cache.sync_data order by version limit 1);"; 202 203 constexpr const char *GET_MAX_VER_CACHEDATA_FROM_CACHEHANDLE = 204 "select version from sync_data order by version DESC limit 1;"; 205 constexpr const char *GET_MAX_VER_CACHEDATA_FROM_MAINHANDLE = 206 "select version from cache.sync_data order by version DESC limit 1;"; 207 208 constexpr const char *MIGRATE_INSERT_DATA_TO_MAINDB_FROM_CACHEHANDLE = 209 "INSERT INTO maindb.sync_data VALUES(?,?,?,?,?,?,?,?,?,?);"; 210 constexpr const char *MIGRATE_UPDATE_DATA_TO_MAINDB_FROM_CACHEHANDLE = 211 "UPDATE maindb.sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=?," 212 "modify_time=?,create_time=? WHERE hash_key=?;"; 213 214 constexpr const char *MIGRATE_INSERT_DATA_TO_MAINDB_FROM_MAINHANDLE = 215 "INSERT INTO sync_data VALUES(?,?,?,?,?,?,?,?,?,?);"; 216 constexpr const char *MIGRATE_UPDATE_DATA_TO_MAINDB_FROM_MAINHANDLE = 217 "UPDATE sync_data SET key=?,value=?,timestamp=?,flag=?,device=?,ori_device=?,w_timestamp=?," 218 "modify_time=?,create_time=? WHERE hash_key=?;"; 219 220 constexpr const char *MIGRATE_DEL_DATA_BY_VERSION_FROM_CACHEHANDLE = 221 "DELETE FROM sync_data WHERE version=?;"; 222 constexpr const char *MIGRATE_DEL_DATA_BY_VERSION_FROM_MAINHANDLE = 223 "DELETE FROM cache.sync_data WHERE version=?;"; 224 225 constexpr const char *SELECT_MAIN_SYNC_HASH_SQL_FROM_CACHEHANDLE = 226 "SELECT * FROM maindb.sync_data WHERE hash_key=?;"; 227 228 constexpr const char *REMOVE_META_VALUE_SQL = 229 "DELETE FROM meta_data WHERE key=?;"; 230 constexpr const char *REMOVE_ATTACH_META_VALUE_SQL = 231 "DELETE FROM meta.meta_data WHERE key=?;"; 232 233 constexpr const char *CHECK_DB_INTEGRITY_SQL = "PRAGMA integrity_check;"; 234 235 constexpr const char *REMOVE_META_VALUE_BY_KEY_PREFIX_SQL = 236 "DELETE FROM meta_data WHERE key>=? AND key<=?;"; 237 constexpr const char *REMOVE_ATTACH_META_VALUE_BY_KEY_PREFIX_SQL = 238 "DELETE FROM meta.meta_data WHERE key>=? AND key<=?;"; 239 240 constexpr const char *DELETE_SYNC_DATA_WITH_HASHKEY = "DELETE FROM sync_data where hash_key = ?;"; 241 242 constexpr const char *DELETE_SYNC_DATA_WITH_HASHKEY_FROM_CACHEHANDLE = 243 "DELETE FROM maindb.sync_data where hash_key = ?;"; 244 245 constexpr const char *GET_SYNC_DATA_TIRGGER_SQL = 246 "SELECT name FROM SQLITE_MASTER WHERE TYPE = 'trigger' AND TBL_NAME = 'sync_data' AND name like ?;"; 247 248 constexpr const char *REMOVE_CLOUD_ALL_LOG_DATA_SQL = 249 "DELETE FROM naturalbase_kv_aux_sync_data_log;"; 250 251 constexpr const char *REMOVE_ALL_DEV_SYNC_DATA_SQL = 252 "DELETE FROM sync_data WHERE (flag&0x02=0) AND (flag&0x100=0);"; 253 254 constexpr const char *REMOVE_DEV_SYNC_DATA_BY_DEV_ID_SQL = 255 "DELETE FROM sync_data WHERE device=? AND (flag&0x02=0) AND (flag&0x100=0);"; 256 257 constexpr const char *REMOVE_CLOUD_ALL_DEV_DATA_SQL = 258 "DELETE FROM sync_data WHERE (flag&0x100!=0);"; 259 260 constexpr const char *UPDATE_CLOUD_ALL_DEV_DATA_SQL = 261 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE (flag&0x100!=0);"; 262 263 constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_DEVID_SQL = 264 "DELETE FROM sync_data WHERE device=? AND (flag&0x100!=0);"; 265 266 constexpr const char *REMOVE_CLOUD_ALL_DEV_DATA_VERSION_SQL = 267 "DELETE FROM sync_data WHERE key LIKE 'naturalbase_cloud_version_%' AND length(device)!=0;"; 268 269 constexpr const char *REMOVE_CLOUD_DEV_DATA_VERSION_BY_DEVID_SQL = 270 "DELETE FROM sync_data WHERE device=? AND key LIKE 'naturalbase_cloud_version_%';"; 271 272 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_SQL = 273 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0);"; 274 275 constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_USERID_SQL = 276 "DELETE FROM sync_data WHERE (flag&0x100!=0) AND hash_key IN" \ 277 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 278 279 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_USERID_SQL = 280 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE (flag&0x100!=0) AND hash_key IN" \ 281 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 282 283 constexpr const char *REMOVE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL = 284 "DELETE FROM sync_data WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \ 285 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);"; 286 287 constexpr const char *UPDATE_CLOUD_DEV_DATA_BY_DEVID_HASHKEY_NOTIN_SQL = 288 "UPDATE sync_data SET flag=(flag|0x02)&(~0x100) WHERE device=? AND (flag&0x100!=0) AND hash_key NOT IN" \ 289 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log);"; 290 291 constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_DEVID_SQL = 292 "DELETE 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 *REMOVE_CLOUD_LOG_DATA_BY_USERID_SQL = 296 "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =?;"; 297 298 constexpr const char *REMOVE_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL = 299 "DELETE FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 300 "(SELECT hash_key FROM sync_data WHERE device =?);"; 301 302 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_DEVID_SQL = 303 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE hash_key IN" \ 304 "(SELECT hash_key FROM sync_data WHERE device =?);"; 305 306 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_DEVID_SQL = 307 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 308 "(SELECT hash_key FROM sync_data WHERE device =?);"; 309 310 // Check whether the hashKey is the same but the userId is different 311 constexpr const char *SELECT_CLOUD_LOG_DATA_BY_USERID_HASHKEY_SQL = 312 "SELECT * FROM naturalbase_kv_aux_sync_data_log WHERE userid =? AND hash_key IN" \ 313 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid !=?);"; 314 315 constexpr const char *SELECT_CLOUD_DEV_DATA_BY_USERID_SQL = 316 "SELECT * FROM sync_data WHERE hash_key IN" \ 317 "(SELECT hash_key FROM naturalbase_kv_aux_sync_data_log WHERE userid =?);"; 318 319 constexpr const char *REMOVE_CLOUD_ALL_HWM_DATA_SQL = 320 "DELETE FROM meta_data WHERE KEY LIKE 'naturalbase_cloud_meta_sync_data_%';"; 321 322 constexpr const char *REMOVE_CLOUD_HWM_DATA_BY_USERID_SQL = 323 "DELETE FROM meta_data WHERE KEY =?;"; 324 325 constexpr const char *UPDATE_SYNC_DATA_KEY_SQL = 326 "UPDATE sync_data SET key=translate_key(key), hash_key=cal_hash_key(key) WHERE flag&0x01=0"; 327 328 constexpr const char *FUNC_NAME_TRANSLATE_KEY = "translate_key"; 329 330 constexpr const char *FUNC_NAME_CAL_HASH_KEY = "cal_hash_key"; 331 332 constexpr const char *QUERY_COUNT_HEAD = "SELECT count(1) "; 333 334 constexpr const char *QUERY_CLOUD_SYNC_DATA_HEAD = "SELECT key, value, flag, device, ori_device, " 335 "sync_data.hash_key, w_timestamp, modify_time, create_time, cloud_gid, version, sync_data.rowid, cloud_flag "; 336 337 constexpr const char *QUERY_CLOUD_SYNC_DATA_DETAIL = "FROM sync_data LEFT JOIN " 338 "(SELECT userid, cloud_gid, version, hash_key, cloud_flag FROM naturalbase_kv_aux_sync_data_log WHERE userid=?)" 339 " AS log_table ON sync_data.hash_key = log_table.hash_key "; 340 341 constexpr const char *QUERY_CLOUD_SYNC_DATA_CONDITION = 342 "WHERE modify_time > ? AND ((cloud_gid is not null and cloud_gid != '') OR " 343 "((cloud_gid is null OR cloud_gid = '') and flag&0x01=0)) AND flag&0x200=0"; 344 345 constexpr const char *QUERY_CLOUD_VERSION_RECORD_CONDITION = "WHERE key = ? AND flag & 0x200 != 0"; 346 347 constexpr const char *QUERY_CLOUD_SYNC_DATA_LOG = "SELECT sync_data.rowid, flag, device, ori_device, " 348 "modify_time, create_time, cloud_gid, sync_data.hash_key, sync_data.key, version, cloud_flag FROM " 349 "sync_data LEFT JOIN naturalbase_kv_aux_sync_data_log ON " 350 "sync_data.hash_key = naturalbase_kv_aux_sync_data_log.hash_key "; 351 352 constexpr const char *QUERY_CLOUD_SYNC_DATA_LOG_WITH_USERID = "SELECT sync_data.rowid, flag, device, ori_device, " 353 "modify_time, create_time, cloud_gid, sync_data.hash_key, sync_data.key, version, cloud_flag FROM " 354 "sync_data LEFT JOIN (select * from naturalbase_kv_aux_sync_data_log where userid=?) as " 355 "naturalbase_kv_aux_sync_data_log ON sync_data.hash_key = naturalbase_kv_aux_sync_data_log.hash_key "; 356 357 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_HEAD = "SELECT key, value, flag, device, sync_data.hash_key " 358 "FROM sync_data WHERE key LIKE 'naturalbase_cloud_version_%' "; 359 360 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_DEVICE_CONDITION = "AND device = ? AND flag&0x200 != 0"; 361 362 constexpr const char *QUERY_CLOUD_VERSION_RECORD_SQL_EMPTY_DEVICE_CONDITION = "AND flag&0x200 != 0"; 363 364 constexpr const char *INSERT_CLOUD_SYNC_DATA_LOG = "INSERT OR REPLACE INTO naturalbase_kv_aux_sync_data_log " 365 "VALUES(?,?,?,?,?)"; 366 constexpr const char *UPSERT_CLOUD_SYNC_DATA_LOG = "INSERT INTO naturalbase_kv_aux_sync_data_log VALUES(?,?,?,?,0)" 367 "ON CONFLICT(userid, hash_key) DO UPDATE SET cloud_gid=?, version=?"; 368 369 constexpr const char *UPDATE_CLOUD_SYNC_DATA_LOG = "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_gid=?, " 370 "version=?,cloud_flag=? WHERE userid=? AND hash_key=?"; 371 372 constexpr const char *SET_SYNC_DATA_NO_FORCE_PUSH = "UPDATE sync_data SET flag=flag|0x40 WHERE hash_key=?"; 373 374 constexpr const char *SET_SYNC_DATA_FORCE_PUSH = "UPDATE sync_data SET flag=flag&(~0x40) WHERE hash_key=?"; 375 376 constexpr const char *UPDATE_TIMESTAMP = "UPDATE sync_data SET timestamp=?, modify_time=? WHERE hash_key=?"; 377 378 constexpr const char *SELECT_SYNC_ENTRIES_BY_DEVICE_SQL = 379 "SELECT key,value FROM sync_data WHERE device=? AND flag&0x200=0"; 380 381 constexpr const char *SELECT_COMPENSATE_SYNC_KEY_SQL = 382 "SELECT key FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table on sync_data.hash_key" 383 "= log_table.hash_key WHERE log_table.cloud_flag=log_table.cloud_flag|0x10"; 384 385 constexpr const char *SELECT_COMPENSATE_SYNC_USERID_SQL = 386 "SELECT userid FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table on sync_data.hash_key" 387 "= log_table.hash_key WHERE log_table.cloud_flag=log_table.cloud_flag|0x10"; 388 389 constexpr const char *SELECT_CLOUD_GID_SQL = 390 "SELECT cloud_gid FROM sync_data left join naturalbase_kv_aux_sync_data_log as log_table" 391 " on sync_data.hash_key = log_table.hash_key WHERE log_table.userid=?"; 392 393 constexpr const char *MARK_UPLOAD_SUCCESS = 394 "UPDATE naturalbase_kv_aux_sync_data_log SET cloud_flag=cloud_flag|0x400 " 395 "WHERE hash_key=? AND userid=? "; 396 397 constexpr const char *CHECK_DATA_CHANGED = 398 "SELECT COUNT(1) FROM sync_data WHERE modify_time=? AND hash_key=?"; 399 400 constexpr const char *CHECK_DATA_DELETE = 401 "SELECT COUNT(1) FROM sync_data WHERE modify_time!=? AND hash_key=? AND flag&0x01 != 0"; 402 403 constexpr const char *NATURALBASE_KV_AUX_SYNC_DATA_LOG_TABLE_NAME = "naturalbase_kv_aux_sync_data_log"; 404 405 const int BIND_KV_KEY_INDEX = 1; 406 const int BIND_KV_VAL_INDEX = 2; 407 const int BIND_LOCAL_TIMESTAMP_INDEX = 3; 408 const int BIND_LOCAL_HASH_KEY_INDEX = 4; 409 410 // binding index just for the get sync data sql 411 const int BIND_BEGIN_STAMP_INDEX = 1; 412 const int BIND_END_STAMP_INDEX = 2; 413 414 // mainDB 415 const int BIND_SYNC_KEY_INDEX = 1; 416 const int BIND_SYNC_VAL_INDEX = 2; 417 const int BIND_SYNC_STAMP_INDEX = 3; 418 const int BIND_SYNC_FLAG_INDEX = 4; 419 const int BIND_SYNC_DEV_INDEX = 5; 420 const int BIND_SYNC_ORI_DEV_INDEX = 6; 421 const int BIND_SYNC_HASH_KEY_INDEX = 7; 422 const int BIND_SYNC_W_TIME_INDEX = 8; 423 const int BIND_SYNC_MODIFY_TIME_INDEX = 9; 424 const int BIND_SYNC_CREATE_TIME_INDEX = 10; 425 426 const int BIND_SYNC_UPDATE_W_TIME_INDEX = 7; 427 const int BIND_SYNC_UPDATE_MODIFY_TIME_INDEX = 8; 428 const int BIND_SYNC_UPDATE_CREATE_TIME_INDEX = 9; 429 const int BIND_SYNC_UPDATE_HASH_KEY_INDEX = 10; 430 431 // cacheDB 432 const int BIND_CACHE_LOCAL_KEY_INDEX = 1; 433 const int BIND_CACHE_LOCAL_VAL_INDEX = 2; 434 const int BIND_CACHE_LOCAL_TIMESTAMP_INDEX = 3; 435 const int BIND_CACHE_LOCAL_HASH_KEY_INDEX = 4; 436 const int BIND_CACHE_LOCAL_FLAG_INDEX = 5; 437 438 const int BIND_CACHE_SYNC_KEY_INDEX = 1; 439 const int BIND_CACHE_SYNC_VAL_INDEX = 2; 440 const int BIND_CACHE_SYNC_STAMP_INDEX = 3; 441 const int BIND_CACHE_SYNC_FLAG_INDEX = 4; 442 const int BIND_CACHE_SYNC_DEV_INDEX = 5; 443 const int BIND_CACHE_SYNC_ORI_DEV_INDEX = 6; 444 const int BIND_CACHE_SYNC_HASH_KEY_INDEX = 7; 445 const int BIND_CACHE_SYNC_W_TIME_INDEX = 8; 446 const int BIND_CACHE_SYNC_VERSION_INDEX = 9; 447 448 // select result index for the item for sync database 449 const int SYNC_RES_KEY_INDEX = 0; 450 const int SYNC_RES_VAL_INDEX = 1; 451 const int SYNC_RES_TIME_INDEX = 2; 452 const int SYNC_RES_FLAG_INDEX = 3; 453 const int SYNC_RES_DEVICE_INDEX = 4; 454 const int SYNC_RES_ORI_DEV_INDEX = 5; 455 const int SYNC_RES_HASH_KEY_INDEX = 6; 456 const int SYNC_RES_W_TIME_INDEX = 7; 457 const int SYNC_RES_VERSION_INDEX = 8; // Available in cacheDB. 458 459 // get kv data Response index 460 const int GET_KV_RES_LOCAL_TIME_INDEX = 1; 461 const int GET_KV_RES_SYNC_TIME_INDEX = 1; 462 463 const int BIND_ORI_DEVICE_ID = 0; 464 const int BIND_PRE_DEVICE_ID = 1; 465 466 constexpr int BIND_CLOUD_USER = 1; 467 constexpr int BIND_CLOUD_TIMESTAMP = 2; 468 469 constexpr int CLOUD_QUERY_KEY_INDEX = 0; 470 constexpr int CLOUD_QUERY_VALUE_INDEX = 1; 471 constexpr int CLOUD_QUERY_FLAG_INDEX = 2; 472 constexpr int CLOUD_QUERY_DEV_INDEX = 3; 473 constexpr int CLOUD_QUERY_ORI_DEV_INDEX = 4; 474 constexpr int CLOUD_QUERY_HASH_KEY_INDEX = 5; 475 constexpr int CLOUD_QUERY_DEV_CREATE_TIME_INDEX = 6; 476 constexpr int CLOUD_QUERY_MODIFY_TIME_INDEX = 7; 477 constexpr int CLOUD_QUERY_CREATE_TIME_INDEX = 8; 478 constexpr int CLOUD_QUERY_CLOUD_GID_INDEX = 9; 479 constexpr int CLOUD_QUERY_VERSION_INDEX = 10; 480 constexpr int CLOUD_QUERY_ROW_ID_INDEX = 11; 481 constexpr int CLOUD_QUERY_CLOUD_FLAG_INDEX = 12; 482 483 constexpr int CLOUD_QUERY_COUNT_INDEX = 0; 484 485 constexpr int BIND_CLOUD_VERSION_RECORD_USER_INDEX = 1; 486 constexpr int BIND_CLOUD_VERSION_RECORD_KEY_INDEX = 2; 487 488 constexpr int BIND_CLOUD_VERSION_DEVICE_INDEX = 1; 489 490 constexpr int BIND_INSERT_USER_INDEX = 1; 491 constexpr int BIND_INSERT_HASH_KEY_INDEX = 2; 492 constexpr int BIND_INSERT_CLOUD_GID_INDEX = 3; 493 constexpr int BIND_INSERT_VERSION_INDEX = 4; 494 constexpr int BIND_INSERT_CLOUD_FLAG_INDEX = 5; 495 496 // use in get entries by device sql 497 constexpr int BIND_GET_ENTRIES_DEVICE_INDEX = 1; 498 // use in remove cloud flag 499 constexpr int BIND_HASH_KEY_INDEX = 1; 500 const Key REMOVE_DEVICE_DATA_KEY = {'r', 'e', 'm', 'o', 'v', 'e'}; 501 } // namespace DistributedDB 502 503 #endif // SQLITE_SINGLE_VER_STORAGE_EXECUTOR_SQL_H 504