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 #include "sqlite_query_helper.h"
16
17 #include <iomanip>
18
19 #include "db_common.h"
20 #include "db_constant.h"
21 #include "db_errno.h"
22 #include "log_print.h"
23 #include "macro_utils.h"
24 #include "sqlite_utils.h"
25 #include "sqlite_single_ver_storage_executor_sql.h"
26 #include "cloud/cloud_storage_utils.h"
27 #include "res_finalizer.h"
28
29 namespace DistributedDB {
30 using namespace TriggerMode;
31 namespace {
32 const std::string PRE_QUERY_KV_SQL = "SELECT key, value FROM sync_data ";
33 const std::string PRE_QUERY_ITEM_SQL = "SELECT * FROM ";
34 const std::string PRE_QUERY_ROWID_SQL = "SELECT rowid FROM sync_data ";
35 const std::string PRE_GET_COUNT_SQL = "SELECT count(*) FROM sync_data ";
36 const std::string FILTER_NATIVE_DATA_SQL = "WHERE (flag&0x01=0) ";
37 const std::string FILTER_REMOTE_QUERY = "WHERE (flag&0x03=0x02)";
38 const std::string USING_INDEX = "INDEXED BY ";
39 const int MAX_SQL_LEN = 1024 * 1024; // 1M bytes
40 const int SINGLE_FIELD_VALUE_SIZE = 1;
41 const int MAX_CONDITIONS_SIZE = 128;
42 const int MAX_SQLITE_BIND_SIZE = 50000;
43 const uint32_t SYMBOL_TYPE_MASK = 0xff00;
44
45 const std::map<QueryObjType, std::string> RELATIONAL_SYMBOL_TO_SQL {
46 {QueryObjType::EQUALTO, "= "},
47 {QueryObjType::NOT_EQUALTO, "!= "},
48 {QueryObjType::GREATER_THAN, "> "},
49 {QueryObjType::LESS_THAN, "< "},
50 {QueryObjType::GREATER_THAN_OR_EQUALTO, ">= "},
51 {QueryObjType::LESS_THAN_OR_EQUALTO, "<= "},
52 {QueryObjType::LIKE, " LIKE "},
53 {QueryObjType::NOT_LIKE, " NOT LIKE "},
54 {QueryObjType::IS_NULL, " IS NULL "},
55 {QueryObjType::IS_NOT_NULL, " IS NOT NULL "},
56 {QueryObjType::IN, " IN ("},
57 {QueryObjType::NOT_IN, " NOT IN ("},
58 };
59
60 const std::map<QueryObjType, std::string> LOGIC_SYMBOL_TO_SQL {
61 {QueryObjType::AND, " AND "},
62 {QueryObjType::OR, " OR "},
63 {QueryObjType::BEGIN_GROUP, "("},
64 {QueryObjType::END_GROUP, ")"},
65 };
66
FieldValue2String(const FieldValue & val,QueryValueType type)67 std::string FieldValue2String(const FieldValue &val, QueryValueType type)
68 {
69 std::stringstream ss;
70 switch (type) {
71 case QueryValueType::VALUE_TYPE_NULL:
72 return "NULL";
73 case QueryValueType::VALUE_TYPE_BOOL:
74 return val.boolValue ? "1" : "0";
75 case QueryValueType::VALUE_TYPE_INTEGER:
76 return std::to_string(val.integerValue);
77 case QueryValueType::VALUE_TYPE_LONG:
78 return std::to_string(val.longValue);
79 case QueryValueType::VALUE_TYPE_DOUBLE:
80 ss << std::setprecision(DBConstant::DOUBLE_PRECISION) << val.doubleValue;
81 return ss.str();
82 case QueryValueType::VALUE_TYPE_STRING:
83 return "'" + val.stringValue + "'";
84 case QueryValueType::VALUE_TYPE_INVALID:
85 default:
86 return "";
87 }
88 }
89
GetSelectAndFromClauseForRDB(const std::string & tableName,const std::vector<std::string> & fieldNames)90 std::string GetSelectAndFromClauseForRDB(const std::string &tableName, const std::vector<std::string> &fieldNames)
91 {
92 std::string sql = "SELECT b.data_key,"
93 "b.device,"
94 "b.ori_device,"
95 "b.timestamp as " + DBConstant::TIMESTAMP_ALIAS + ","
96 "b.wtimestamp,"
97 "b.flag,"
98 "b.hash_key,";
99 if (fieldNames.empty()) { // For query check. If column count changed, can be discovered.
100 sql += "a.*";
101 } else {
102 for (const auto &fieldName : fieldNames) { // For query data.
103 sql += "a.'" + fieldName + "',";
104 }
105 sql.pop_back();
106 }
107 sql += " FROM '" + tableName + "' AS a INNER JOIN " + DBConstant::RELATIONAL_PREFIX + tableName + "_log AS b "
108 "ON a." + std::string(DBConstant::SQLITE_INNER_ROWID) + "=b.data_key ";
109 return sql;
110 }
111
GetTimeRangeClauseForRDB()112 std::string GetTimeRangeClauseForRDB()
113 {
114 return " AND (" + DBConstant::TIMESTAMP_ALIAS + ">=? AND " + DBConstant::TIMESTAMP_ALIAS + "<?) ";
115 }
116
GetOuterQueryClauseForRDB(const std::string & subQueryClause)117 std::string GetOuterQueryClauseForRDB(const std::string &subQueryClause)
118 {
119 return "SELECT * "
120 "FROM ( " + subQueryClause + " ) "
121 "WHERE (" + DBConstant::TIMESTAMP_ALIAS + ">=? AND " + DBConstant::TIMESTAMP_ALIAS + "<?) "
122 "ORDER BY " + DBConstant::TIMESTAMP_ALIAS + ";";
123 }
124
GetFlagClauseForRDB()125 std::string GetFlagClauseForRDB()
126 {
127 return "WHERE (b.flag&0x03=0x02)";
128 }
129
GetMissQueryFlagClauseForRDB()130 std::string GetMissQueryFlagClauseForRDB()
131 {
132 return "WHERE (b.flag&0x23=0x22)";
133 }
134 }
135
SqliteQueryHelper(const QueryObjInfo & info)136 SqliteQueryHelper::SqliteQueryHelper(const QueryObjInfo &info)
137 : schema_(info.schema_),
138 queryObjNodes_(info.queryObjNodes_),
139 prefixKey_(info.prefixKey_),
140 suggestIndex_(info.suggestIndex_),
141 tableName_(info.tableName_),
142 keys_(info.keys_),
143 orderByCounts_(info.orderByCounts_),
144 isValid_(info.isValid_),
145 transformed_(false),
146 hasOrderBy_(info.hasOrderBy_),
147 hasLimit_(info.hasLimit_),
148 isOrderByAppeared_(false),
149 hasPrefixKey_(info.hasPrefixKey_),
150 isNeedOrderbyKey_(false),
151 isRelationalQuery_(info.isRelationalQuery_),
152 sortType_(info.sortType_)
153 {}
154
GetSymbolType(const QueryObjType & queryObjType)155 SymbolType SqliteQueryHelper::GetSymbolType(const QueryObjType &queryObjType)
156 {
157 return static_cast<SymbolType>(static_cast<uint32_t>(queryObjType) & SYMBOL_TYPE_MASK);
158 }
159
FilterSymbolToAddBracketLink(std::string & querySql,bool isNeedLink) const160 bool SqliteQueryHelper::FilterSymbolToAddBracketLink(std::string &querySql, bool isNeedLink) const
161 {
162 bool isNeedEndBracket = false;
163 for (const auto &iter : queryObjNodes_) {
164 SymbolType symbolType = GetSymbolType(iter.operFlag);
165 if (symbolType == SymbolType::COMPARE_SYMBOL || symbolType == SymbolType::RELATIONAL_SYMBOL ||
166 symbolType == SymbolType::RANGE_SYMBOL) {
167 querySql += isNeedLink ? " AND (" : " (";
168 isNeedEndBracket = true;
169 break;
170 } else if (symbolType == SymbolType::LOGIC_SYMBOL || symbolType == SymbolType::PREFIXKEY_SYMBOL ||
171 symbolType == SymbolType::IN_KEYS_SYMBOL) {
172 continue;
173 } else {
174 break;
175 }
176 }
177 return isNeedEndBracket;
178 }
179
180
ParseQueryObjNodeToSQL(bool isQueryForSync)181 int SqliteQueryHelper::ParseQueryObjNodeToSQL(bool isQueryForSync)
182 {
183 if (queryObjNodes_.empty()) {
184 if (!isQueryForSync) {
185 querySql_ += ";";
186 }
187 return E_OK;
188 }
189
190 bool isNeedEndBracket = FilterSymbolToAddBracketLink(querySql_);
191
192 int errCode = E_OK;
193 for (const QueryObjNode &objNode : queryObjNodes_) {
194 SymbolType symbolType = GetSymbolType(objNode.operFlag);
195 if (symbolType == SymbolType::SPECIAL_SYMBOL && isNeedEndBracket) {
196 querySql_ += ") ";
197 isNeedEndBracket = false;
198 }
199 errCode = ParseQueryExpression(objNode, querySql_);
200 if (errCode != E_OK) {
201 querySql_.clear();
202 return errCode;
203 }
204 }
205
206 if (isNeedEndBracket) {
207 querySql_ += ") ";
208 }
209
210 return errCode;
211 }
212
ToQuerySql()213 int SqliteQueryHelper::ToQuerySql()
214 {
215 int errCode = ParseQueryObjNodeToSQL(false);
216 if (errCode != E_OK) {
217 return errCode;
218 }
219
220 // Limit needs to be placed after orderBy and processed separately in the limit branch
221 if (hasPrefixKey_ && !hasOrderBy_ && !hasLimit_ && isNeedOrderbyKey_) {
222 LOGD("Need add order by key at last when has prefixKey no need order by value and limit!");
223 querySql_ += "ORDER BY key ASC";
224 }
225 querySql_ += ";";
226 return errCode;
227 }
228
ToQuerySyncSql(bool hasSubQuery,bool useTimestampAlias)229 int SqliteQueryHelper::ToQuerySyncSql(bool hasSubQuery, bool useTimestampAlias)
230 {
231 int errCode = ParseQueryObjNodeToSQL(true);
232 if (errCode != E_OK) {
233 return errCode;
234 }
235
236 // Order by time when no order by and no limit and no need order by key.
237 if (!hasOrderBy_ && !hasLimit_ && !isNeedOrderbyKey_) {
238 querySql_ += (useTimestampAlias ?
239 ("ORDER BY " + DBConstant::TIMESTAMP_ALIAS + " ASC") :
240 "ORDER BY timestamp ASC");
241 }
242
243 if (!hasSubQuery) {
244 querySql_ += ";";
245 }
246 return errCode;
247 }
248
ToGetCountSql()249 int SqliteQueryHelper::ToGetCountSql()
250 {
251 countSql_.clear();
252 if (queryObjNodes_.empty()) {
253 countSql_ += ";";
254 return E_OK;
255 }
256 bool isNeedEndBracket = FilterSymbolToAddBracketLink(countSql_);
257
258 int errCode = E_OK;
259 for (const QueryObjNode &objNode : queryObjNodes_) {
260 SymbolType symbolType = GetSymbolType(objNode.operFlag);
261 if (symbolType == SymbolType::SPECIAL_SYMBOL && isNeedEndBracket) {
262 countSql_ += ") ";
263 isNeedEndBracket = false;
264 }
265
266 if (objNode.operFlag == QueryObjType::LIMIT) {
267 hasLimit_ = true;
268 continue;
269 }
270 if (objNode.operFlag == QueryObjType::ORDERBY) {
271 hasOrderBy_ = true;
272 continue;
273 }
274 errCode = ParseQueryExpression(objNode, countSql_);
275 if (errCode != E_OK) {
276 countSql_.clear();
277 return errCode;
278 }
279 }
280
281 if (isNeedEndBracket) {
282 countSql_ += ") ";
283 }
284
285 // Limit needs to be placed after orderBy and processed separately in the limit branch
286 if (hasPrefixKey_ && !hasOrderBy_ && !hasLimit_ && isNeedOrderbyKey_) {
287 LOGD("Need add order by key at last when has prefixKey no need order by value and limit!");
288 countSql_ += "ORDER BY key ASC";
289 }
290 countSql_ += ";";
291 return errCode;
292 }
293
GetQuerySql(std::string & sql,bool onlyRowid)294 int SqliteQueryHelper::GetQuerySql(std::string &sql, bool onlyRowid)
295 {
296 if (!isValid_) {
297 return -E_INVALID_QUERY_FORMAT;
298 }
299
300 const std::string &querySqlForUse = (onlyRowid ? PRE_QUERY_ROWID_SQL : PRE_QUERY_KV_SQL);
301 sql = AssembleSqlForSuggestIndex(querySqlForUse, FILTER_NATIVE_DATA_SQL);
302 sql = !hasPrefixKey_ ? sql : (sql + " AND (key>=? AND key<=?) ");
303 sql = keys_.empty() ? sql : (sql + " AND " + MapKeysInToSql(keys_.size()));
304 sql += " AND (flag&0x200=0) ";
305 if (sortType_ != SortType::NONE) {
306 sql += (sortType_ == SortType::TIMESTAMP_ASC) ? "ORDER BY timestamp asc " : "ORDER BY timestamp desc ";
307 }
308 if (transformed_) {
309 LOGD("This query object has been parsed.");
310 sql += querySql_;
311 return E_OK;
312 }
313 int errCode = ToQuerySql();
314 if (errCode != E_OK) {
315 LOGE("Transfer to query sql failed! errCode[%d]", errCode);
316 return errCode;
317 }
318 transformed_ = true;
319 sql += querySql_;
320 return errCode;
321 }
322
GetSyncDataCheckSql(std::string & sql)323 int SqliteQueryHelper::GetSyncDataCheckSql(std::string &sql)
324 {
325 int errCode = E_OK;
326 if (!isValid_) {
327 return -E_INVALID_QUERY_FORMAT;
328 }
329 sql = PRE_QUERY_ITEM_SQL + tableName_ + " WHERE hash_key=? AND (flag&0x01=0) ";
330 sql += hasPrefixKey_ ? " AND (key>=? AND key<=?) " : "";
331 sql = keys_.empty() ? sql : (sql + " AND " + MapKeysInToSql(keys_.size()));
332 if (!transformed_) {
333 errCode = ToQuerySql();
334 if (errCode != E_OK) {
335 LOGE("Transfer query to sync data check sql failed! errCode[%d]", errCode);
336 return errCode;
337 }
338 transformed_ = true;
339 }
340 sql += querySql_;
341 return errCode;
342 }
343
BindSyncDataCheckStmt(sqlite3_stmt * statement,const Key & hashKey) const344 int SqliteQueryHelper::BindSyncDataCheckStmt(sqlite3_stmt *statement, const Key &hashKey) const
345 {
346 if (statement == nullptr) {
347 return -E_INVALID_ARGS;
348 }
349 int index = 1; // bind statement start index 1
350 int errCode = SQLiteUtils::BindBlobToStatement(statement, index++, hashKey, false);
351 if (errCode != E_OK) {
352 LOGE("Get sync data check statement failed when bind hash key, errCode = %d", errCode);
353 return errCode;
354 }
355 if (hasPrefixKey_) {
356 // bind the prefix key for the first and second args.
357 errCode = SQLiteUtils::BindPrefixKey(statement, index, prefixKey_);
358 if (errCode != E_OK) {
359 LOGE("Get sync data check statement failed when bind prefix key, errCode = %d", errCode);
360 return errCode;
361 }
362 index += 2; // prefixKey takes 2 position
363 }
364
365 errCode = BindKeysToStmt(keys_, statement, index);
366 if (errCode != E_OK) {
367 SQLiteUtils::ResetStatement(statement, true, errCode);
368 return errCode;
369 }
370
371 for (const QueryObjNode &objNode : queryObjNodes_) {
372 errCode = BindFieldValue(statement, objNode, index);
373 if (errCode != E_OK) {
374 LOGE("Get sync data check statement failed when bind field value, errCode = %d", errCode);
375 return errCode;
376 }
377 }
378 return errCode;
379 }
380
GetCountQuerySql(std::string & sql)381 int SqliteQueryHelper::GetCountQuerySql(std::string &sql)
382 {
383 if (!isValid_) {
384 return -E_INVALID_QUERY_FORMAT;
385 }
386
387 int errCode = ToGetCountSql();
388 if (errCode != E_OK) {
389 return errCode;
390 }
391 sql = AssembleSqlForSuggestIndex(PRE_GET_COUNT_SQL, FILTER_NATIVE_DATA_SQL);
392 sql = !hasPrefixKey_ ? sql : (sql + " AND (key>=? AND key<=?) ");
393 sql = keys_.empty() ? sql : (sql + " AND " + MapKeysInToSql(keys_.size()));
394 sql += " AND (flag&0x200=0) ";
395 sql += countSql_;
396 return E_OK;
397 }
398
GetQuerySqlStatement(sqlite3 * dbHandle,const std::string & sql,sqlite3_stmt * & statement)399 int SqliteQueryHelper::GetQuerySqlStatement(sqlite3 *dbHandle, const std::string &sql, sqlite3_stmt *&statement)
400 {
401 int errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
402 if (errCode != E_OK) {
403 LOGE("[Query] Get statement fail!");
404 return -E_INVALID_QUERY_FORMAT;
405 }
406 int index = 1;
407 if (hasPrefixKey_) {
408 // bind the prefix key for the first and second args.
409 errCode = SQLiteUtils::BindPrefixKey(statement, 1, prefixKey_);
410 if (errCode != E_OK) {
411 SQLiteUtils::ResetStatement(statement, true, errCode);
412 LOGE("[Query] Get statement when bind prefix key, errCode = %d", errCode);
413 return errCode;
414 }
415 index = 3; // begin from 3rd args
416 }
417
418 errCode = BindKeysToStmt(keys_, statement, index);
419 if (errCode != E_OK) {
420 SQLiteUtils::ResetStatement(statement, true, errCode);
421 return errCode;
422 }
423
424 for (const QueryObjNode &objNode : queryObjNodes_) {
425 errCode = BindFieldValue(statement, objNode, index);
426 if (errCode != E_OK) {
427 SQLiteUtils::ResetStatement(statement, true, errCode);
428 LOGE("[Query] Get statement fail when bind field value, errCode = %d", errCode);
429 return errCode;
430 }
431 }
432 return errCode;
433 }
434
GetQuerySqlStatement(sqlite3 * dbHandle,bool onlyRowid,sqlite3_stmt * & statement)435 int SqliteQueryHelper::GetQuerySqlStatement(sqlite3 *dbHandle, bool onlyRowid, sqlite3_stmt *&statement)
436 {
437 std::string sql;
438 int errCode = GetQuerySql(sql, onlyRowid);
439 if (errCode != E_OK) {
440 return errCode;
441 }
442
443 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
444 if (errCode != E_OK) {
445 LOGE("[Query] Get statement fail!");
446 return -E_INVALID_QUERY_FORMAT;
447 }
448 int index = 1;
449 if (hasPrefixKey_) {
450 // bind the prefix key for the first and second args.
451 errCode = SQLiteUtils::BindPrefixKey(statement, 1, prefixKey_);
452 if (errCode != E_OK) {
453 LOGE("[Query] Get statement when bind prefix key, errCode = %d", errCode);
454 return errCode;
455 }
456 index = 3; // begin from 3rd args
457 }
458
459 errCode = BindKeysToStmt(keys_, statement, index);
460 if (errCode != E_OK) {
461 return errCode;
462 }
463
464 for (const QueryObjNode &objNode : queryObjNodes_) {
465 errCode = BindFieldValue(statement, objNode, index);
466 if (errCode != E_OK) {
467 LOGE("[Query] Get statement fail when bind field value, errCode = %d", errCode);
468 return errCode;
469 }
470 }
471 return errCode;
472 }
473
GetCountSqlStatement(sqlite3 * dbHandle,sqlite3_stmt * & countStmt)474 int SqliteQueryHelper::GetCountSqlStatement(sqlite3 *dbHandle, sqlite3_stmt *&countStmt)
475 {
476 std::string countSql;
477 int errCode = GetCountQuerySql(countSql);
478 if (errCode != E_OK) {
479 return errCode;
480 }
481
482 // bind statement for count
483 errCode = SQLiteUtils::GetStatement(dbHandle, countSql, countStmt);
484 if (errCode != E_OK) {
485 LOGE("Get count statement error:%d", errCode);
486 return -E_INVALID_QUERY_FORMAT;
487 }
488 int index = 1;
489 if (hasPrefixKey_) {
490 // bind the prefix key for the first and second args.
491 errCode = SQLiteUtils::BindPrefixKey(countStmt, 1, prefixKey_);
492 if (errCode != E_OK) {
493 LOGE("[Query] Get count statement fail when bind prefix key, errCode = %d", errCode);
494 return errCode;
495 }
496 index = 3; // begin from 3rd args
497 }
498
499 errCode = BindKeysToStmt(keys_, countStmt, index);
500 if (errCode != E_OK) {
501 return errCode;
502 }
503
504 for (const QueryObjNode &objNode : queryObjNodes_) {
505 if (GetSymbolType(objNode.operFlag) == SymbolType::SPECIAL_SYMBOL) {
506 continue;
507 }
508 errCode = BindFieldValue(countStmt, objNode, index);
509 if (errCode != E_OK) {
510 LOGE("[Query] Get count statement fail when bind field value, errCode = %d", errCode);
511 return errCode;
512 }
513 }
514 return errCode;
515 }
516
GetSyncDataQuerySql(std::string & sql,bool hasSubQuery)517 int SqliteQueryHelper::GetSyncDataQuerySql(std::string &sql, bool hasSubQuery)
518 {
519 if (!isValid_) {
520 return -E_INVALID_QUERY_FORMAT;
521 }
522
523 if (hasLimit_) {
524 hasSubQuery = true; // Need sub query.
525 } else {
526 isNeedOrderbyKey_ = false; // Need order by timestamp.
527 }
528
529 sql = AssembleSqlForSuggestIndex(PRE_QUERY_ITEM_SQL + tableName_ + " ", FILTER_REMOTE_QUERY);
530 sql = !hasPrefixKey_ ? sql : (sql + " AND (key>=? AND key<=?) ");
531 sql = keys_.empty() ? sql : (sql + " AND " + MapKeysInToSql(keys_.size()));
532 sql = hasSubQuery ? sql : (sql + " AND (timestamp>=? AND timestamp<?) ");
533 sql += " AND (flag&0x200=0) ";
534
535 querySql_.clear(); // clear local query sql format
536 int errCode = ToQuerySyncSql(hasSubQuery);
537 if (errCode != E_OK) {
538 LOGE("To query sql fail! errCode[%d]", errCode);
539 return errCode;
540 }
541 sql += querySql_;
542 if (hasSubQuery) {
543 // The last timestamp in one query will be stored in continue token and used for next query.
544 // Therefore all query data must be ordered by timestamp.
545 // When there is limit in SQL, data should be ordered by key in sub query, and timestamp is ordered by outside.
546 sql = "SELECT * FROM ( " + sql + " ) WHERE (timestamp >= ? AND timestamp < ?) ORDER BY timestamp;";
547 }
548 return errCode;
549 }
550
BindTimeRange(sqlite3_stmt * & statement,int & index,uint64_t beginTime,uint64_t endTime) const551 int SqliteQueryHelper::BindTimeRange(sqlite3_stmt *&statement, int &index, uint64_t beginTime, uint64_t endTime) const
552 {
553 int errCode = SQLiteUtils::BindInt64ToStatement(statement, index++, beginTime);
554 if (errCode != E_OK) {
555 SQLiteUtils::ResetStatement(statement, true, errCode);
556 return errCode;
557 }
558
559 errCode = SQLiteUtils::BindInt64ToStatement(statement, index++, endTime);
560 if (errCode != E_OK) {
561 SQLiteUtils::ResetStatement(statement, true, errCode);
562 }
563 return errCode;
564 }
565
BindObjNodes(sqlite3_stmt * & statement,int & index) const566 int SqliteQueryHelper::BindObjNodes(sqlite3_stmt *&statement, int &index) const
567 {
568 int errCode = E_OK;
569 for (const QueryObjNode &objNode : queryObjNodes_) {
570 errCode = BindFieldValue(statement, objNode, index);
571 if (errCode != E_OK) {
572 SQLiteUtils::ResetStatement(statement, true, errCode);
573 LOGE("[Query] Get statement fail when bind field value, errCode = %d", errCode);
574 break;
575 }
576 }
577 return errCode;
578 }
579
GetQuerySyncStatement(sqlite3 * dbHandle,uint64_t beginTime,uint64_t endTime,sqlite3_stmt * & statement)580 int SqliteQueryHelper::GetQuerySyncStatement(sqlite3 *dbHandle, uint64_t beginTime, uint64_t endTime,
581 sqlite3_stmt *&statement)
582 {
583 bool hasSubQuery = false;
584 if (hasLimit_) {
585 hasSubQuery = true; // Need sub query.
586 } else {
587 isNeedOrderbyKey_ = false; // Need order by timestamp.
588 }
589 std::string sql;
590 int errCode = GetSyncDataQuerySql(sql, hasSubQuery);
591 if (errCode != E_OK) {
592 LOGE("[Query] Get SQL fail!");
593 return -E_INVALID_QUERY_FORMAT;
594 }
595
596 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
597 if (errCode != E_OK) {
598 LOGE("[Query] Get statement fail!");
599 return -E_INVALID_QUERY_FORMAT;
600 }
601
602 int index = 1; // begin with 1.
603 if (hasPrefixKey_) {
604 // bind the prefix key for the first and second args.
605 errCode = SQLiteUtils::BindPrefixKey(statement, index, prefixKey_);
606 if (errCode != E_OK) {
607 SQLiteUtils::ResetStatement(statement, true, errCode);
608 LOGE("[Query] Get statement when bind prefix key, errCode = %d", errCode);
609 return errCode;
610 }
611 index = 3; // begin with 3 next if prefix key exists.
612 }
613
614 errCode = BindKeysToStmt(keys_, statement, index);
615 if (errCode != E_OK) {
616 SQLiteUtils::ResetStatement(statement, true, errCode);
617 return errCode;
618 }
619
620 if (hasSubQuery) {
621 // For sub query SQL, timestamp must be last : (prefix key), (objNodes), timestamp.
622 // SQL: SELECT * FROM ( SELECT * FROM sync_data WHERE (flag&0x03=0x02) LIMIT 10 OFFSET 0 ) WHERE (timestamp>=?
623 // AND timestamp<?) ORDER BY timestamp;
624 errCode = BindObjNodes(statement, index);
625 if (errCode != E_OK) {
626 return errCode;
627 }
628 errCode = BindTimeRange(statement, index, beginTime, endTime);
629 } else {
630 // For normal SQL(no sub query), timestamp is just behind prefix key : (prefix key), timestamp, (ojbNodes).
631 // SQL: SELECT * FROM sync_data WHERE (timestamp>=? AND timestamp<?) AND (flag&0x03=0x02) ORDER BY timestamp;
632 errCode = BindTimeRange(statement, index, beginTime, endTime);
633 if (errCode != E_OK) {
634 return errCode;
635 }
636 errCode = BindObjNodes(statement, index);
637 }
638 return errCode;
639 }
640
MapRelationalSymbolToSql(const QueryObjNode & queryNode,bool placeholder) const641 std::string SqliteQueryHelper::MapRelationalSymbolToSql(const QueryObjNode &queryNode, bool placeholder) const
642 {
643 if (RELATIONAL_SYMBOL_TO_SQL.find(queryNode.operFlag) == RELATIONAL_SYMBOL_TO_SQL.end()) {
644 return "";
645 };
646 std::string sql = RELATIONAL_SYMBOL_TO_SQL.at(queryNode.operFlag) + MapValueToSql(queryNode, placeholder);
647 if (GetSymbolType(queryNode.operFlag) == SymbolType::RANGE_SYMBOL) {
648 sql += ")";
649 }
650 return sql;
651 }
652
MapLogicSymbolToSql(const QueryObjNode & queryNode) const653 std::string SqliteQueryHelper::MapLogicSymbolToSql(const QueryObjNode &queryNode) const
654 {
655 if (LOGIC_SYMBOL_TO_SQL.find(queryNode.operFlag) == LOGIC_SYMBOL_TO_SQL.end()) {
656 return "";
657 }
658 return LOGIC_SYMBOL_TO_SQL.at(queryNode.operFlag);
659 }
660
MapKeywordSymbolToSql(const QueryObjNode & queryNode)661 std::string SqliteQueryHelper::MapKeywordSymbolToSql(const QueryObjNode &queryNode)
662 {
663 std::string sql;
664 switch (queryNode.operFlag) {
665 case QueryObjType::ORDERBY:
666 if (queryNode.fieldValue.size() == SINGLE_FIELD_VALUE_SIZE) {
667 if (!isOrderByAppeared_) {
668 sql += "ORDER BY ";
669 }
670 sql += GetFieldShape(queryNode);
671 sql += queryNode.fieldValue[0].boolValue ? "ASC," : "DESC,";
672 orderByCounts_--;
673 if (orderByCounts_ == 0) {
674 sql.pop_back();
675 }
676 isOrderByAppeared_ = true;
677 }
678 return sql;
679 case QueryObjType::LIMIT:
680 if (hasPrefixKey_ && !hasOrderBy_ && isNeedOrderbyKey_) {
681 sql += "ORDER BY key ASC ";
682 }
683 sql += " LIMIT " + std::to_string(queryNode.fieldValue[0].integerValue);
684 sql += " OFFSET " + std::to_string(queryNode.fieldValue[1].integerValue);
685 return sql;
686 default:
687 return "";
688 }
689 }
690
MapValueToSql(const QueryObjNode & queryNode,bool placeholder) const691 std::string SqliteQueryHelper::MapValueToSql(const QueryObjNode &queryNode, bool placeholder) const
692 {
693 std::string resultSql;
694 for (size_t i = 0; i < queryNode.fieldValue.size(); i++) {
695 if (i != 0) {
696 resultSql += ", ";
697 }
698 resultSql += placeholder ? "? " : FieldValue2String(queryNode.fieldValue[i], queryNode.type);
699 }
700 return resultSql;
701 }
702
IsNeedCastWitEmptyValue(const QueryObjNode & queryNode)703 static bool IsNeedCastWitEmptyValue(const QueryObjNode &queryNode)
704 {
705 return (queryNode.operFlag == QueryObjType::IS_NULL || queryNode.operFlag == QueryObjType::IS_NOT_NULL ||
706 queryNode.operFlag == QueryObjType::IN || queryNode.operFlag == QueryObjType::NOT_IN);
707 }
708
MapCastFuncSql(const QueryObjNode & queryNode,const std::string & accessStr)709 std::string SqliteQueryHelper::MapCastFuncSql(const QueryObjNode &queryNode, const std::string &accessStr)
710 {
711 std::string resultSql;
712 if (queryNode.fieldValue.empty() && !IsNeedCastWitEmptyValue(queryNode)) {
713 return resultSql;
714 }
715 // fieldPath and isQueryable had been checked ok in the previous code, So here parse path and get type won't fail.
716 FieldPath fieldPath;
717 SchemaUtils::ParseAndCheckFieldPath(queryNode.fieldName, fieldPath);
718 FieldType fieldType = FieldType::LEAF_FIELD_INTEGER;
719 schema_.CheckQueryableAndGetFieldType(fieldPath, fieldType);
720 resultSql += SchemaObject::GenerateExtractSQL(schema_.GetSchemaType(), fieldPath, fieldType,
721 schema_.GetSkipSize(), accessStr);
722 isNeedOrderbyKey_ = false; // When index by value, No need order by key!
723 return resultSql;
724 }
725
BindFieldValue(sqlite3_stmt * statement,const QueryObjNode & queryNode,int & index) const726 int SqliteQueryHelper::BindFieldValue(sqlite3_stmt *statement, const QueryObjNode &queryNode, int &index) const
727 {
728 SymbolType symbolType = GetSymbolType(queryNode.operFlag);
729 if (symbolType != SymbolType::COMPARE_SYMBOL && symbolType != SymbolType::RELATIONAL_SYMBOL &&
730 symbolType != SymbolType::RANGE_SYMBOL) {
731 return E_OK;
732 }
733
734 int errCode = SQLITE_OK;
735 for (size_t i = 0; i < queryNode.fieldValue.size(); i++) {
736 if (queryNode.type == QueryValueType::VALUE_TYPE_BOOL) {
737 errCode = sqlite3_bind_int(statement, index, queryNode.fieldValue[i].boolValue);
738 } else if (queryNode.type == QueryValueType::VALUE_TYPE_INTEGER) {
739 errCode = sqlite3_bind_int(statement, index, queryNode.fieldValue[i].integerValue);
740 } else if (queryNode.type == QueryValueType::VALUE_TYPE_LONG) {
741 errCode = sqlite3_bind_int64(statement, index, queryNode.fieldValue[i].longValue);
742 } else if (queryNode.type == QueryValueType::VALUE_TYPE_DOUBLE) {
743 errCode = sqlite3_bind_double(statement, index, queryNode.fieldValue[i].doubleValue);
744 } else {
745 if (queryNode.fieldValue[i].stringValue.size() > MAX_SQLITE_BIND_SIZE) {
746 return -E_MAX_LIMITS;
747 }
748 errCode = sqlite3_bind_text(statement, index, queryNode.fieldValue[i].stringValue.c_str(),
749 queryNode.fieldValue[i].stringValue.size(), SQLITE_TRANSIENT);
750 }
751 if (errCode != SQLITE_OK) {
752 break;
753 }
754 index++;
755 }
756 return SQLiteUtils::MapSQLiteErrno(errCode);
757 }
758
GetFieldShape(const QueryObjNode & queryNode,const std::string & accessStr)759 std::string SqliteQueryHelper::GetFieldShape(const QueryObjNode &queryNode, const std::string &accessStr)
760 {
761 if (isRelationalQuery_) {
762 // For relational query, $. prefix is not permitted, so need not extract json. Return directly will be OK.
763 return "a.'" + queryNode.fieldName + "' ";
764 }
765 return MapCastFuncSql(queryNode, accessStr);
766 }
767
ParseQueryExpression(const QueryObjNode & queryNode,std::string & querySql,const std::string & accessStr,bool placeholder)768 int SqliteQueryHelper::ParseQueryExpression(const QueryObjNode &queryNode, std::string &querySql,
769 const std::string &accessStr, bool placeholder)
770 {
771 SymbolType symbolType = GetSymbolType(queryNode.operFlag);
772 if (symbolType == SymbolType::RANGE_SYMBOL && queryNode.fieldValue.size() > MAX_CONDITIONS_SIZE) {
773 LOGE("[Query][Parse][Expression] conditions is too many!");
774 return -E_MAX_LIMITS;
775 }
776
777 if (symbolType == SymbolType::COMPARE_SYMBOL || symbolType == SymbolType::RELATIONAL_SYMBOL ||
778 symbolType == SymbolType::RANGE_SYMBOL) {
779 querySql += GetFieldShape(queryNode, accessStr);
780 querySql += MapRelationalSymbolToSql(queryNode, placeholder);
781 } else if (symbolType == SymbolType::LOGIC_SYMBOL || symbolType == SymbolType::LINK_SYMBOL) {
782 querySql += MapLogicSymbolToSql(queryNode);
783 } else {
784 querySql += MapKeywordSymbolToSql(queryNode);
785 }
786
787 if (querySql.size() > MAX_SQL_LEN) {
788 LOGE("[Query][Parse][Expression] Sql is too long!");
789 return -E_MAX_LIMITS;
790 }
791 return E_OK;
792 }
793
AssembleSqlForSuggestIndex(const std::string & baseSql,const std::string & filter) const794 std::string SqliteQueryHelper::AssembleSqlForSuggestIndex(const std::string &baseSql, const std::string &filter) const
795 {
796 std::string formatIndex = CheckAndFormatSuggestIndex();
797 if (formatIndex.empty()) {
798 return baseSql + filter;
799 }
800
801 return baseSql + USING_INDEX + "'" + formatIndex + "' " + filter;
802 }
803
CheckAndFormatSuggestIndex() const804 std::string SqliteQueryHelper::CheckAndFormatSuggestIndex() const
805 {
806 if (suggestIndex_.empty()) {
807 return "";
808 }
809 IndexName indexName;
810 int errCode = SchemaUtils::ParseAndCheckFieldPath(suggestIndex_, indexName);
811 if (errCode != E_OK) {
812 LOGW("Check and format suggest index failed! %d", errCode);
813 return "";
814 }
815
816 if (!schema_.IsIndexExist(indexName)) {
817 LOGW("The suggest index not exist!");
818 return "";
819 }
820 return SchemaUtils::FieldPathString(indexName);
821 }
822
MapKeysInSubCondition(const std::string & accessStr) const823 std::string SqliteQueryHelper::MapKeysInSubCondition(const std::string &accessStr) const
824 {
825 std::string resultStr = "hex(" + accessStr + "key) IN (";
826 for (auto iter = keys_.begin(); iter != keys_.end(); iter++) {
827 if (iter != keys_.begin()) {
828 resultStr += ", ";
829 }
830 resultStr += "'" + DBCommon::VectorToHexString(*iter) + "' ";
831 }
832 resultStr += ")";
833 return resultStr;
834 }
835
GetSubscribeCondition(const std::string & accessStr,std::string & conditionStr)836 int SqliteQueryHelper::GetSubscribeCondition(const std::string &accessStr, std::string &conditionStr)
837 {
838 if (queryObjNodes_.empty()) {
839 conditionStr += " (1 = 1) ";
840 return E_OK;
841 }
842
843 bool hasQueryByValue = std::any_of(queryObjNodes_.begin(), queryObjNodes_.end(), [](const QueryObjNode &it) {
844 return GetSymbolType(it.operFlag) == SymbolType::COMPARE_SYMBOL ||
845 GetSymbolType(it.operFlag) == SymbolType::RELATIONAL_SYMBOL ||
846 GetSymbolType(it.operFlag) == SymbolType::RANGE_SYMBOL;
847 });
848 if (hasQueryByValue) {
849 // json_extract_by_path function will return error when value is empty, check it before when query by value
850 conditionStr += "((length(" + accessStr + "value) != 0 AND " + accessStr + "value IS NOT NULL) AND ";
851 } else {
852 conditionStr += "(";
853 }
854
855 if (hasPrefixKey_) {
856 conditionStr += "(hex(" + accessStr + "key) LIKE '" + DBCommon::VectorToHexString(prefixKey_) + "%')";
857 }
858
859 if (!keys_.empty()) {
860 if (hasPrefixKey_) {
861 conditionStr += " AND ";
862 }
863 conditionStr += "(" + MapKeysInSubCondition(accessStr) + ")";
864 }
865
866 bool isNeedEndBracket = FilterSymbolToAddBracketLink(conditionStr, hasPrefixKey_ || !keys_.empty());
867 int errCode = E_OK;
868 for (const QueryObjNode &objNode : queryObjNodes_) {
869 SymbolType symbolType = GetSymbolType(objNode.operFlag);
870 if (symbolType == SymbolType::SPECIAL_SYMBOL && isNeedEndBracket) {
871 conditionStr += ") ";
872 isNeedEndBracket = false;
873 }
874 errCode = ParseQueryExpression(objNode, conditionStr, accessStr, false);
875 if (errCode != E_OK) {
876 conditionStr.clear();
877 return errCode;
878 }
879 }
880
881 if (isNeedEndBracket) {
882 conditionStr += ") ";
883 }
884 conditionStr += ")";
885 return errCode;
886 }
887
GetSubscribeSql(const std::string & subscribeId,TriggerModeEnum mode,std::string & subscribeCondition)888 int SqliteQueryHelper::GetSubscribeSql(const std::string &subscribeId, TriggerModeEnum mode,
889 std::string &subscribeCondition)
890 {
891 if (!isValid_) {
892 return -E_INVALID_QUERY_FORMAT;
893 }
894 int errCode = E_OK;
895 switch (mode) {
896 case TriggerModeEnum::INSERT:
897 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_NEW, subscribeCondition);
898 break;
899 case TriggerModeEnum::UPDATE:
900 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_OLD, subscribeCondition);
901 if (errCode != E_OK) {
902 break;
903 }
904 subscribeCondition += " OR ";
905 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_NEW, subscribeCondition);
906 break;
907 case TriggerModeEnum::DELETE:
908 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_OLD, subscribeCondition);
909 break;
910 default:
911 errCode = -INVALID_ARGS;
912 }
913 if (errCode != E_OK) {
914 LOGD("Get subscribe query condition failed. %d", errCode);
915 }
916 return errCode;
917 }
918
GetRelationalMissQuerySql(const std::vector<std::string> & fieldNames,std::string & sql)919 int SqliteQueryHelper::GetRelationalMissQuerySql(const std::vector<std::string> &fieldNames, std::string &sql)
920 {
921 if (!isValid_) {
922 return -E_INVALID_QUERY_FORMAT;
923 }
924
925 if (hasPrefixKey_) {
926 LOGE("For relational DB query, prefix key is not supported.");
927 return -E_NOT_SUPPORT;
928 }
929
930 sql = GetSelectAndFromClauseForRDB(tableName_, fieldNames);
931 sql += GetMissQueryFlagClauseForRDB();
932 sql += GetTimeRangeClauseForRDB();
933 sql += "ORDER BY " + DBConstant::TIMESTAMP_ALIAS + " ASC;";
934 return E_OK;
935 }
936
GetRelationalSyncDataQuerySql(std::string & sql,bool hasSubQuery,const std::vector<std::string> & fieldNames)937 int SqliteQueryHelper::GetRelationalSyncDataQuerySql(std::string &sql, bool hasSubQuery,
938 const std::vector<std::string> &fieldNames)
939 {
940 if (!isValid_) {
941 return -E_INVALID_QUERY_FORMAT;
942 }
943
944 if (hasPrefixKey_) {
945 LOGE("For relational DB query, prefix key is not supported.");
946 return -E_NOT_SUPPORT;
947 }
948
949 sql = AssembleSqlForSuggestIndex(GetSelectAndFromClauseForRDB(tableName_, fieldNames), GetFlagClauseForRDB());
950 sql = hasSubQuery ? sql : (sql + GetTimeRangeClauseForRDB());
951
952 querySql_.clear(); // clear local query sql format
953 int errCode = ToQuerySyncSql(hasSubQuery, true);
954 if (errCode != E_OK) {
955 LOGE("To query sql fail! errCode[%d]", errCode);
956 return errCode;
957 }
958 sql += querySql_;
959 if (hasSubQuery) {
960 // The last timestamp in one query will be stored in continue token and used for next query.
961 // Therefore all query data must be ordered by timestamp.
962 // When there is limit in SQL, data should be ordered by key in sub query, and timestamp is ordered by outside.
963 sql = GetOuterQueryClauseForRDB(sql);
964 }
965 return errCode;
966 }
967
GetRelationalMissQueryStatement(sqlite3 * dbHandle,uint64_t beginTime,uint64_t endTime,const std::vector<std::string> & fieldNames,sqlite3_stmt * & statement)968 int SqliteQueryHelper::GetRelationalMissQueryStatement(sqlite3 *dbHandle, uint64_t beginTime, uint64_t endTime,
969 const std::vector<std::string> &fieldNames, sqlite3_stmt *&statement)
970 {
971 std::string sql;
972 int errCode = GetRelationalMissQuerySql(fieldNames, sql);
973 if (errCode != E_OK) {
974 LOGE("[Query] Get SQL fail!");
975 return -E_INVALID_QUERY_FORMAT;
976 }
977
978 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
979 if (errCode != E_OK) {
980 LOGE("[Query] Get statement fail!");
981 return -E_INVALID_QUERY_FORMAT;
982 }
983
984 int index = 1; // begin with 1.
985 return BindTimeRange(statement, index, beginTime, endTime);
986 }
987
988 namespace {
GetRelationalSyncDataQueryHeader(const std::vector<std::string> & fieldNames)989 std::string GetRelationalSyncDataQueryHeader(const std::vector<std::string> &fieldNames)
990 {
991 std::string sql = "SELECT b.data_key,"
992 "b.device,"
993 "b.ori_device,"
994 "b.timestamp as " + DBConstant::TIMESTAMP_ALIAS + ","
995 "b.wtimestamp,"
996 "b.flag,"
997 "b.hash_key,";
998 if (fieldNames.empty()) { // For query check. If column count changed, can be discovered.
999 sql += "a.*";
1000 } else {
1001 for (const auto &fieldName : fieldNames) { // For query data.
1002 sql += "a." + fieldName + ",";
1003 }
1004 sql.pop_back();
1005 }
1006 return sql;
1007 }
1008
GetRelationalCloudSyncDataQueryHeader(const std::vector<Field> & fields)1009 std::string GetRelationalCloudSyncDataQueryHeader(const std::vector<Field> &fields)
1010 {
1011 std::string sql = "SELECT b.data_key,"
1012 "b.device,"
1013 "b.ori_device,"
1014 "b.timestamp as " + DBConstant::TIMESTAMP_ALIAS + ","
1015 "b.wtimestamp,"
1016 "b.flag,"
1017 "b.hash_key,"
1018 "b.cloud_gid,"
1019 "b.version,"
1020 "b.status,";
1021 if (fields.empty()) { // For query check. If column count changed, can be discovered.
1022 sql += "a.*";
1023 } else {
1024 for (const auto &field : fields) { // For query data.
1025 sql += "a." + field.colName + ",";
1026 }
1027 sql.pop_back();
1028 }
1029 return sql;
1030 }
1031 }
1032
GetRelationalSyncDataQuerySqlWithLimit(const std::vector<std::string> & fieldNames,std::string & sql)1033 int SqliteQueryHelper::GetRelationalSyncDataQuerySqlWithLimit(const std::vector<std::string> &fieldNames,
1034 std::string &sql)
1035 {
1036 if (!isValid_) {
1037 return -E_INVALID_QUERY_FORMAT;
1038 }
1039
1040 if (hasPrefixKey_) {
1041 LOGE("For relational DB query, prefix key is not supported.");
1042 return -E_NOT_SUPPORT;
1043 }
1044 sql = GetRelationalSyncDataQueryHeader(fieldNames);
1045 sql += " FROM '" + tableName_ + "' AS a INNER JOIN ";
1046 sql += DBConstant::RELATIONAL_PREFIX + tableName_ + "_log";
1047 sql += " AS b ON (a." + std::string(DBConstant::SQLITE_INNER_ROWID) + " = b.data_key)";
1048 sql += " WHERE (b.flag&0x03=0x02)";
1049
1050 querySql_.clear(); // clear local query sql format
1051 int errCode = ToQuerySyncSql(true, true);
1052 if (errCode != E_OK) {
1053 LOGE("To query sql fail! errCode[%d]", errCode);
1054 return errCode;
1055 }
1056 sql += querySql_;
1057 return E_OK;
1058 }
1059
GetRelationalQueryStatement(sqlite3 * dbHandle,uint64_t beginTime,uint64_t endTime,const std::vector<std::string> & fieldNames,sqlite3_stmt * & statement)1060 int SqliteQueryHelper::GetRelationalQueryStatement(sqlite3 *dbHandle, uint64_t beginTime, uint64_t endTime,
1061 const std::vector<std::string> &fieldNames, sqlite3_stmt *&statement)
1062 {
1063 int errCode = E_OK;
1064 bool hasSubQuery = false;
1065 std::string sql;
1066 if (hasLimit_ && !hasOrderBy_) { // Query with limit and no order by
1067 hasSubQuery = true; // Need sub query.
1068 errCode = GetRelationalSyncDataQuerySqlWithLimit(fieldNames, sql);
1069 } else if (hasLimit_ || hasOrderBy_) {
1070 hasSubQuery = true; // Need sub query.
1071 errCode = GetRelationalSyncDataQuerySql(sql, hasSubQuery, fieldNames);
1072 } else {
1073 isNeedOrderbyKey_ = false; // Need order by timestamp.
1074 errCode = GetRelationalSyncDataQuerySql(sql, hasSubQuery, fieldNames);
1075 }
1076 if (errCode != E_OK) {
1077 LOGE("[Query] Get SQL fail!");
1078 return -E_INVALID_QUERY_FORMAT;
1079 }
1080
1081 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
1082 if (errCode != E_OK) {
1083 LOGE("[Query] Get statement fail!");
1084 return -E_INVALID_QUERY_FORMAT;
1085 }
1086
1087 int index = 1; // begin with 1.
1088 if (hasSubQuery) {
1089 /**
1090 * SELECT b.data_key,b.device,b.ori_device,b.timestamp as naturalbase_rdb_timestamp,
1091 * b.wtimestamp,b.flag,b.hash_key,a.*
1092 * FROM tableName AS a INNER JOIN naturalbase_rdb_log AS b
1093 * ON a.rowid=b.data_key
1094 * WHERE (b.flag&0x03=0x02)
1095 * ORDER BY a.xxx
1096 * LIMIT ? OFFSET ? )
1097 */
1098 errCode = BindObjNodes(statement, index);
1099 if (errCode != E_OK) {
1100 return errCode;
1101 }
1102 if (!hasLimit_ || hasOrderBy_) {
1103 errCode = BindTimeRange(statement, index, beginTime, endTime);
1104 }
1105 } else {
1106 /**
1107 * SELECT b.data_key,b.device,b.ori_device,b.timestamp as naturalbase_rdb_timestamp,
1108 * b.wtimestamp,b.flag,b.hash_key,a.*
1109 * FROM tableName AS a INNER JOIN naturalbase_rdb_log AS b
1110 * ON a.rowid=b.data_key
1111 * WHERE (b.flag&0x03=0x02) AND (naturalbase_rdb_timestamp>=? AND naturalbase_rdb_timestamp<?)
1112 * ORDER BY naturalbase_rdb_timestamp ASC;
1113 */
1114 errCode = BindTimeRange(statement, index, beginTime, endTime);
1115 if (errCode != E_OK) {
1116 return errCode;
1117 }
1118 errCode = BindObjNodes(statement, index);
1119 }
1120 return errCode;
1121 }
1122
MapKeysInToSql(size_t keysNum) const1123 std::string SqliteQueryHelper::MapKeysInToSql(size_t keysNum) const
1124 {
1125 std::string resultSql = "key IN (";
1126 for (size_t i = 0; i < keysNum; i++) {
1127 if (i != 0) {
1128 resultSql += ", ";
1129 }
1130 resultSql += "? ";
1131 }
1132 resultSql += ") ";
1133 return resultSql;
1134 }
1135
BindKeysToStmt(const std::set<Key> & keys,sqlite3_stmt * & statement,int & index) const1136 int SqliteQueryHelper::BindKeysToStmt(const std::set<Key> &keys, sqlite3_stmt *&statement, int &index) const
1137 {
1138 if (!keys_.empty()) {
1139 int errCode = E_OK;
1140 for (const auto &key : keys) {
1141 errCode = SQLiteUtils::BindBlobToStatement(statement, index, key);
1142 if (errCode != E_OK) {
1143 LOGE("[Query] Get statement when bind keys failed, errCode = %d", errCode);
1144 return errCode;
1145 }
1146 index++;
1147 }
1148 }
1149 return E_OK;
1150 }
1151
GetRelationalCloudQuerySql(const std::vector<Field> & fields,const bool & isCloudForcePush,bool isCompensatedTask,CloudWaterType mode)1152 std::string SqliteQueryHelper::GetRelationalCloudQuerySql(const std::vector<Field> &fields,
1153 const bool &isCloudForcePush, bool isCompensatedTask, CloudWaterType mode)
1154 {
1155 std::string sql = GetRelationalCloudSyncDataQueryHeader(fields);
1156 AppendCloudQuery(isCloudForcePush, isCompensatedTask, sql, mode);
1157 AppendCloudQueryToGetDiffData(sql, mode);
1158 return sql;
1159 }
1160
GetCountRelationalCloudQuerySql(bool isCloudForcePush,bool isCompensatedTask,CloudWaterType mode)1161 std::string SqliteQueryHelper::GetCountRelationalCloudQuerySql(bool isCloudForcePush, bool isCompensatedTask,
1162 CloudWaterType mode)
1163 {
1164 std::string sql = "SELECT COUNT(*) ";
1165 AppendCloudQuery(isCloudForcePush, isCompensatedTask, sql, mode);
1166 return sql;
1167 }
1168
GetGidRelationalCloudQuerySql(const std::vector<Field> & fields,bool isCloudForcePush,bool isCompensatedTask)1169 std::string SqliteQueryHelper::GetGidRelationalCloudQuerySql(const std::vector<Field> &fields,
1170 bool isCloudForcePush, bool isCompensatedTask)
1171 {
1172 std::string sql = GetRelationalCloudSyncDataQueryHeader(fields);
1173 AppendCloudGidQuery(isCloudForcePush, isCompensatedTask, sql);
1174 return sql;
1175 }
1176
AppendCloudQuery(bool isCloudForcePush,bool isCompensatedTask,std::string & sql,CloudWaterType mode)1177 void SqliteQueryHelper::AppendCloudQuery(bool isCloudForcePush, bool isCompensatedTask, std::string &sql,
1178 CloudWaterType mode)
1179 {
1180 sql += CloudStorageUtils::GetLeftJoinLogSql(tableName_, false);
1181 sql += " WHERE ";
1182 // let data after remove device data at flag_only and logic delete mode and deleted by others to upload to cloud.
1183 if (mode == CloudWaterType::INSERT) {
1184 sql += "(b.cloud_gid == '' and (b.flag & 0x20 != 0) and (b.flag & 0x02 = 0) and (b.flag & 0x08 != 0x08) and";
1185 sql += " (b.flag & 0x01 = 0) and (b.status = 0)) OR ";
1186 }
1187 if (isCompensatedTask && mode == CloudWaterType::DELETE) {
1188 // deleted data does not have primary key, requires gid to compensate sync
1189 sql += "(b.status = 1 AND (b.flag & 0x01 = 0x01) AND b.cloud_gid != '') OR ";
1190 } else if (queryObjNodes_.empty() && mode != CloudWaterType::INSERT) { // means unPriorityTask and not insert
1191 sql += "(b.status != 1) AND ";
1192 }
1193 if (isCloudForcePush) {
1194 sql += " (b.flag & 0x04 != 0x04)";
1195 } else {
1196 sql += "(b.flag & 0x02 = 0x02)";
1197 if (!isCompensatedTask) {
1198 // local data and flag is not upload finished.
1199 sql += " AND (b.flag & 0x400 != 0x400)";
1200 }
1201 }
1202 sql += " AND (b.flag & 0x08 != 0x08) AND (b.cloud_gid != '' or"; // actually, b.cloud_gid will not be null.
1203 sql += " (b.cloud_gid == '' and (b.flag & 0x01 = 0))) ";
1204 }
1205
AppendCloudQueryToGetDiffData(std::string & sql,const CloudWaterType mode,bool isKv)1206 void SqliteQueryHelper::AppendCloudQueryToGetDiffData(std::string &sql, const CloudWaterType mode, bool isKv)
1207 {
1208 switch (mode) {
1209 case DistributedDB::CloudWaterType::DELETE:
1210 sql += isKv ? " AND (flag & 0x01 == 0x01) " :
1211 " AND (b.flag & 0x01 == 0x01) ";
1212 break;
1213 case DistributedDB::CloudWaterType::UPDATE:
1214 sql += isKv ? " AND (flag & 0x01 == 0 AND cloud_gid != '' AND cloud_gid IS NOT NULL) " :
1215 " AND (b.flag & 0x01 == 0 AND b.cloud_gid != '') ";
1216 break;
1217 case DistributedDB::CloudWaterType::INSERT:
1218 sql += isKv ? " AND (flag & 0x01 == 0 AND (cloud_gid == '' OR cloud_gid IS NULL)) " :
1219 " AND (b.flag & 0x01 == 0 AND b.cloud_gid == '') ";
1220 break;
1221 default:
1222 break;
1223 }
1224 }
1225
AppendCloudGidQuery(bool isCloudForcePush,bool isCompensatedTask,std::string & sql)1226 void SqliteQueryHelper::AppendCloudGidQuery(bool isCloudForcePush, bool isCompensatedTask, std::string &sql)
1227 {
1228 sql += CloudStorageUtils::GetLeftJoinLogSql(tableName_, false);
1229 sql += " WHERE ";
1230 if (isCompensatedTask) {
1231 // deleted data does not have primary key, requires gid to compensate sync
1232 sql += " (b.status = 1 AND (b.flag & 0x01 = 0x01)) OR ";
1233 }
1234 // actually, b.cloud_gid will not be null.
1235 sql += isCloudForcePush ? " (b.flag & 0x04 != 0x04) AND (b.cloud_gid != '') " : " (b.cloud_gid != '') ";
1236 }
1237
GetCloudQueryStatement(bool useTimestampAlias,sqlite3 * dbHandle,uint64_t beginTime,std::string & sql,sqlite3_stmt * & statement)1238 int SqliteQueryHelper::GetCloudQueryStatement(bool useTimestampAlias, sqlite3 *dbHandle, uint64_t beginTime,
1239 std::string &sql, sqlite3_stmt *&statement)
1240 {
1241 querySql_.clear(); // clear local query sql format
1242 int errCode = ToQuerySyncSql(false, useTimestampAlias);
1243 if (errCode != E_OK) {
1244 LOGE("To query sql fail! errCode[%d]", errCode);
1245 return errCode;
1246 }
1247 sql += querySql_;
1248 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
1249 if (errCode != E_OK) {
1250 LOGE("[Query] Get statement fail!");
1251 return -E_INVALID_QUERY_FORMAT;
1252 }
1253 int index = 1;
1254 errCode = BindObjNodes(statement, index);
1255 if (errCode != E_OK) {
1256 LOGE("[Query] BindObjNodes failed %d", errCode);
1257 int resetRet = E_OK;
1258 SQLiteUtils::ResetStatement(statement, true, errCode);
1259 if (resetRet != E_OK) {
1260 LOGW("[Query] reset statement failed %d", resetRet);
1261 }
1262 }
1263 return errCode;
1264 }
1265
GetKvCloudQueryStmt(sqlite3 * db,bool forcePush,const CloudWaterType mode,int64_t timeStamp,const std::string & user)1266 std::pair<int, sqlite3_stmt *> SqliteQueryHelper::GetKvCloudQueryStmt(sqlite3 *db, bool forcePush,
1267 const CloudWaterType mode, int64_t timeStamp, const std::string &user)
1268 {
1269 std::pair<int, sqlite3_stmt *> res;
1270 sqlite3_stmt *&stmt = res.second;
1271 int &errCode = res.first;
1272 std::string sql = GetKvCloudQuerySql(false, forcePush);
1273 AppendCloudQueryToGetDiffData(sql, mode, true);
1274 AppendKvQueryObjectOnSql(sql);
1275 sql += "order by modify_time asc";
1276 errCode = SQLiteUtils::GetStatement(db, sql, stmt);
1277 if (errCode != E_OK) {
1278 LOGE("[SqliteQueryHelper] Get kv cloud query stmt failed %d", errCode);
1279 return res;
1280 }
1281 int ret = E_OK;
1282 errCode = SQLiteUtils::BindTextToStatement(stmt, BIND_CLOUD_USER, user);
1283 if (errCode != E_OK) {
1284 SQLiteUtils::ResetStatement(stmt, true, ret);
1285 LOGE("[SqliteQueryHelper] Bind user failed %d reset %d", errCode, ret);
1286 return res;
1287 }
1288 errCode = SQLiteUtils::BindInt64ToStatement(stmt, BIND_CLOUD_TIMESTAMP, timeStamp);
1289 if (errCode != E_OK) {
1290 SQLiteUtils::ResetStatement(stmt, true, ret);
1291 LOGE("[SqliteQueryHelper] Bind begin time failed %d reset %d", errCode, ret);
1292 return res;
1293 }
1294 int index = BIND_CLOUD_TIMESTAMP + 1;
1295 errCode = BindKeysToStmt(keys_, stmt, index);
1296 if (errCode != E_OK) {
1297 SQLiteUtils::ResetStatement(stmt, true, ret);
1298 LOGE("[SqliteQueryHelper] Bind user failed %d reset %d", errCode, ret);
1299 }
1300 return res;
1301 }
1302
GetKvCloudQuerySql(bool countOnly,bool forcePush)1303 std::string SqliteQueryHelper::GetKvCloudQuerySql(bool countOnly, bool forcePush)
1304 {
1305 std::string sql;
1306 if (countOnly) {
1307 sql = QUERY_COUNT_HEAD;
1308 } else {
1309 sql = QUERY_CLOUD_SYNC_DATA_HEAD;
1310 }
1311 sql += QUERY_CLOUD_SYNC_DATA_DETAIL;
1312 sql += QUERY_CLOUD_SYNC_DATA_CONDITION;
1313 sql += " AND flag & 0x02 != 0 "; // get all data which is local
1314 if (forcePush) {
1315 sql += " AND flag & 0x04 != 0x04 "; // get all data which hasn't pushed
1316 } else {
1317 sql += " AND (cloud_flag is null OR cloud_flag & " +
1318 std::to_string(static_cast<uint32_t>(LogInfoFlag::FLAG_UPLOAD_FINISHED)) + " = 0) ";
1319 }
1320 return sql;
1321 }
1322
GetKvCloudRecordSql()1323 std::string SqliteQueryHelper::GetKvCloudRecordSql()
1324 {
1325 std::string sql = QUERY_CLOUD_SYNC_DATA_HEAD;
1326 sql += QUERY_CLOUD_SYNC_DATA_DETAIL;
1327 sql += QUERY_CLOUD_VERSION_RECORD_CONDITION;
1328 return sql;
1329 }
1330
GetCloudVersionRecordSql(bool isDeviceEmpty)1331 std::string SqliteQueryHelper::GetCloudVersionRecordSql(bool isDeviceEmpty)
1332 {
1333 std::string sql = QUERY_CLOUD_VERSION_RECORD_SQL_HEAD;
1334 if (!isDeviceEmpty) {
1335 sql += QUERY_CLOUD_VERSION_RECORD_SQL_DEVICE_CONDITION;
1336 } else {
1337 sql += QUERY_CLOUD_VERSION_RECORD_SQL_EMPTY_DEVICE_CONDITION;
1338 }
1339 return sql;
1340 }
1341
GetAndBindGidKvCloudQueryStatement(const std::string & user,sqlite3 * dbHandle,sqlite3_stmt * & stmt)1342 int SqliteQueryHelper::GetAndBindGidKvCloudQueryStatement(const std::string &user, sqlite3 *dbHandle,
1343 sqlite3_stmt *&stmt)
1344 {
1345 std::string sql = SELECT_CLOUD_GID_SQL;
1346 AppendKvQueryObjectOnSql(sql);
1347 int errCode = SQLiteUtils::GetStatement(dbHandle, sql, stmt);
1348 if (errCode != E_OK) {
1349 LOGE("[SqliteQueryHelper] Get gid stmt failed %d", errCode);
1350 return errCode;
1351 }
1352 int index = 1;
1353 int ret = E_OK;
1354 errCode = SQLiteUtils::BindTextToStatement(stmt, index++, user);
1355 if (errCode != E_OK) {
1356 SQLiteUtils::ResetStatement(stmt, true, ret);
1357 LOGE("[SqliteQueryHelper] Bind user failed %d when query gid", errCode);
1358 return errCode;
1359 }
1360 errCode = BindKeysToStmt(keys_, stmt, index);
1361 if (errCode != E_OK) {
1362 LOGE("[SqliteQueryHelper] Bind keys to query gid stmt failed %d", errCode);
1363 SQLiteUtils::ResetStatement(stmt, true, ret);
1364 }
1365 return errCode;
1366 }
1367
GetCountKvCloudDataStatement(sqlite3 * db,bool forcePush,const CloudWaterType mode,sqlite3_stmt * & stmt)1368 int SqliteQueryHelper::GetCountKvCloudDataStatement(sqlite3 *db, bool forcePush, const CloudWaterType mode,
1369 sqlite3_stmt *&stmt)
1370 {
1371 std::string sql = SqliteQueryHelper::GetKvCloudQuerySql(true, forcePush);
1372 SqliteQueryHelper::AppendCloudQueryToGetDiffData(sql, mode, true);
1373 AppendKvQueryObjectOnSql(sql);
1374 int errCode = SQLiteUtils::GetStatement(db, sql, stmt);
1375 if (errCode != E_OK) {
1376 LOGE("[SqliteQueryHelper] Count data stmt failed %d", errCode);
1377 }
1378 return errCode;
1379 }
1380
BindCountKvCloudDataStatement(sqlite3 * db,bool isMemory,const Timestamp & timestamp,const std::string & user,sqlite3_stmt * & stmt)1381 std::pair<int, int64_t> SqliteQueryHelper::BindCountKvCloudDataStatement(sqlite3 *db, bool isMemory,
1382 const Timestamp ×tamp, const std::string &user, sqlite3_stmt *&stmt)
1383 {
1384 ResFinalizer finalizer([stmt]() {
1385 sqlite3_stmt *statement = stmt;
1386 int ret = E_OK;
1387 SQLiteUtils::ResetStatement(statement, true, ret);
1388 if (ret != E_OK) {
1389 LOGW("[SqliteCloudKvExecutorUtils] Reset log stmt failed %d when get upload count", ret);
1390 }
1391 });
1392 std::pair<int, int64_t> res = { E_OK, 0 };
1393 auto &[errCode, count] = res;
1394 errCode = SQLiteUtils::BindTextToStatement(stmt, BIND_CLOUD_USER, user);
1395 if (errCode != E_OK) {
1396 LOGE("[SqliteQueryHelper] Bind user failed %d when get upload count", errCode);
1397 return res;
1398 }
1399 errCode = SQLiteUtils::BindInt64ToStatement(stmt, BIND_CLOUD_TIMESTAMP, static_cast<int64_t>(timestamp));
1400 if (errCode != E_OK) {
1401 LOGE("[SqliteQueryHelper] Bind begin time failed %d when get upload count", errCode);
1402 return res;
1403 }
1404 int keysIndex = BIND_CLOUD_TIMESTAMP + 1;
1405 errCode = BindKeysToStmt(keys_, stmt, keysIndex);
1406 if (errCode != E_OK) {
1407 LOGE("[SqliteQueryHelper] Bind keys failed %d when get upload count", errCode);
1408 return res;
1409 }
1410 errCode = SQLiteUtils::StepNext(stmt, isMemory);
1411 if (errCode == -E_FINISHED) {
1412 count = 0;
1413 return res;
1414 }
1415 count = sqlite3_column_int64(stmt, CLOUD_QUERY_COUNT_INDEX);
1416 LOGD("[SqliteCloudKvExecutorUtils] Get total upload count %" PRId64, count);
1417 return res;
1418 }
1419
AppendKvQueryObjectOnSql(std::string & sql)1420 void SqliteQueryHelper::AppendKvQueryObjectOnSql(std::string &sql)
1421 {
1422 if (!keys_.empty()) {
1423 sql += " AND " + MapKeysInToSql(keys_.size());
1424 }
1425 }
1426 }