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