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(const std::string & subscribeId,TriggerModeEnum mode,std::string & subscribeCondition)891 int SqliteQueryHelper::GetSubscribeSql(const std::string &subscribeId, TriggerModeEnum mode,
892 std::string &subscribeCondition)
893 {
894 if (!isValid_) {
895 return -E_INVALID_QUERY_FORMAT;
896 }
897 int errCode = E_OK;
898 switch (mode) {
899 case TriggerModeEnum::INSERT:
900 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_NEW, subscribeCondition);
901 break;
902 case TriggerModeEnum::UPDATE:
903 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_OLD, subscribeCondition);
904 if (errCode != E_OK) {
905 break;
906 }
907 subscribeCondition += " OR ";
908 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_NEW, subscribeCondition);
909 break;
910 case TriggerModeEnum::DELETE:
911 errCode = GetSubscribeCondition(DBConstant::TRIGGER_REFERENCES_OLD, subscribeCondition);
912 break;
913 default:
914 errCode = -INVALID_ARGS;
915 }
916 if (errCode != E_OK) {
917 LOGD("Get subscribe query condition failed. %d", errCode);
918 }
919 return errCode;
920 }
921
GetRelationalMissQuerySql(const std::vector<std::string> & fieldNames,std::string & sql)922 int SqliteQueryHelper::GetRelationalMissQuerySql(const std::vector<std::string> &fieldNames, std::string &sql)
923 {
924 if (!isValid_) {
925 return -E_INVALID_QUERY_FORMAT;
926 }
927
928 if (hasPrefixKey_) {
929 LOGE("For relational DB query, prefix key is not supported.");
930 return -E_NOT_SUPPORT;
931 }
932
933 sql = GetSelectAndFromClauseForRDB(tableName_, fieldNames);
934 sql += GetMissQueryFlagClauseForRDB();
935 sql += GetTimeRangeClauseForRDB();
936 sql += "ORDER BY " + DBConstant::TIMESTAMP_ALIAS + " ASC;";
937 return E_OK;
938 }
939
GetRelationalSyncDataQuerySql(std::string & sql,bool hasSubQuery,const std::vector<std::string> & fieldNames)940 int SqliteQueryHelper::GetRelationalSyncDataQuerySql(std::string &sql, bool hasSubQuery,
941 const std::vector<std::string> &fieldNames)
942 {
943 if (!isValid_) {
944 return -E_INVALID_QUERY_FORMAT;
945 }
946
947 if (hasPrefixKey_) {
948 LOGE("For relational DB query, prefix key is not supported.");
949 return -E_NOT_SUPPORT;
950 }
951
952 sql = AssembleSqlForSuggestIndex(GetSelectAndFromClauseForRDB(tableName_, fieldNames), GetFlagClauseForRDB());
953 sql = hasSubQuery ? sql : (sql + GetTimeRangeClauseForRDB());
954
955 querySql_.clear(); // clear local query sql format
956 int errCode = ToQuerySyncSql(hasSubQuery, true);
957 if (errCode != E_OK) {
958 LOGE("To query sql fail! errCode[%d]", errCode);
959 return errCode;
960 }
961 sql += querySql_;
962 if (hasSubQuery) {
963 // The last timestamp in one query will be stored in continue token and used for next query.
964 // Therefore all query data must be ordered by timestamp.
965 // When there is limit in SQL, data should be ordered by key in sub query, and timestamp is ordered by outside.
966 sql = GetOuterQueryClauseForRDB(sql);
967 }
968 return errCode;
969 }
970
GetRelationalMissQueryStatement(sqlite3 * dbHandle,uint64_t beginTime,uint64_t endTime,const std::vector<std::string> & fieldNames,sqlite3_stmt * & statement)971 int SqliteQueryHelper::GetRelationalMissQueryStatement(sqlite3 *dbHandle, uint64_t beginTime, uint64_t endTime,
972 const std::vector<std::string> &fieldNames, sqlite3_stmt *&statement)
973 {
974 std::string sql;
975 int errCode = GetRelationalMissQuerySql(fieldNames, sql);
976 if (errCode != E_OK) {
977 LOGE("[Query] Get SQL fail!");
978 return -E_INVALID_QUERY_FORMAT;
979 }
980
981 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
982 if (errCode != E_OK) {
983 LOGE("[Query] Get statement fail!");
984 return -E_INVALID_QUERY_FORMAT;
985 }
986
987 int index = 1; // begin with 1.
988 return BindTimeRange(statement, index, beginTime, endTime);
989 }
990
991 namespace {
GetRelationalSyncDataQueryHeader(const std::vector<std::string> & fieldNames)992 std::string GetRelationalSyncDataQueryHeader(const std::vector<std::string> &fieldNames)
993 {
994 std::string sql = "SELECT b.data_key,"
995 "b.device,"
996 "b.ori_device,"
997 "b.timestamp as " + DBConstant::TIMESTAMP_ALIAS + ","
998 "b.wtimestamp,"
999 "b.flag,"
1000 "b.hash_key,";
1001 if (fieldNames.empty()) { // For query check. If column count changed, can be discovered.
1002 sql += "a.*";
1003 } else {
1004 for (const auto &fieldName : fieldNames) { // For query data.
1005 sql += "a." + fieldName + ",";
1006 }
1007 sql.pop_back();
1008 }
1009 return sql;
1010 }
1011
GetRelationalCloudSyncDataQueryHeader(const std::vector<Field> & fields)1012 std::string GetRelationalCloudSyncDataQueryHeader(const std::vector<Field> &fields)
1013 {
1014 std::string sql = "SELECT b.data_key,"
1015 "b.device,"
1016 "b.ori_device,"
1017 "b.timestamp as " + DBConstant::TIMESTAMP_ALIAS + ","
1018 "b.wtimestamp,"
1019 "b.flag,"
1020 "b.hash_key,"
1021 "b.cloud_gid,"
1022 "b.version,"
1023 "b.status,";
1024 if (fields.empty()) { // For query check. If column count changed, can be discovered.
1025 sql += "a.*";
1026 } else {
1027 for (const auto &field : fields) { // For query data.
1028 sql += "a." + field.colName + ",";
1029 }
1030 sql.pop_back();
1031 }
1032 return sql;
1033 }
1034 }
1035
GetRelationalSyncDataQuerySqlWithLimit(const std::vector<std::string> & fieldNames,std::string & sql)1036 int SqliteQueryHelper::GetRelationalSyncDataQuerySqlWithLimit(const std::vector<std::string> &fieldNames,
1037 std::string &sql)
1038 {
1039 if (!isValid_) {
1040 return -E_INVALID_QUERY_FORMAT;
1041 }
1042
1043 if (hasPrefixKey_) {
1044 LOGE("For relational DB query, prefix key is not supported.");
1045 return -E_NOT_SUPPORT;
1046 }
1047 sql = GetRelationalSyncDataQueryHeader(fieldNames);
1048 sql += " FROM '" + tableName_ + "' AS a INNER JOIN ";
1049 sql += DBConstant::RELATIONAL_PREFIX + tableName_ + "_log";
1050 sql += " AS b ON (a." + std::string(DBConstant::SQLITE_INNER_ROWID) + " = b.data_key)";
1051 sql += " WHERE (b.flag&0x03=0x02)";
1052
1053 querySql_.clear(); // clear local query sql format
1054 int errCode = ToQuerySyncSql(true, true);
1055 if (errCode != E_OK) {
1056 LOGE("To query sql fail! errCode[%d]", errCode);
1057 return errCode;
1058 }
1059 sql += querySql_;
1060 return E_OK;
1061 }
1062
GetRelationalQueryStatement(sqlite3 * dbHandle,uint64_t beginTime,uint64_t endTime,const std::vector<std::string> & fieldNames,sqlite3_stmt * & statement)1063 int SqliteQueryHelper::GetRelationalQueryStatement(sqlite3 *dbHandle, uint64_t beginTime, uint64_t endTime,
1064 const std::vector<std::string> &fieldNames, sqlite3_stmt *&statement)
1065 {
1066 int errCode = E_OK;
1067 bool hasSubQuery = false;
1068 std::string sql;
1069 if (hasLimit_ && !hasOrderBy_) { // Query with limit and no order by
1070 hasSubQuery = true; // Need sub query.
1071 errCode = GetRelationalSyncDataQuerySqlWithLimit(fieldNames, sql);
1072 } else if (hasLimit_ || hasOrderBy_) {
1073 hasSubQuery = true; // Need sub query.
1074 errCode = GetRelationalSyncDataQuerySql(sql, hasSubQuery, fieldNames);
1075 } else {
1076 isNeedOrderbyKey_ = false; // Need order by timestamp.
1077 errCode = GetRelationalSyncDataQuerySql(sql, hasSubQuery, fieldNames);
1078 }
1079 if (errCode != E_OK) {
1080 LOGE("[Query] Get SQL fail!");
1081 return -E_INVALID_QUERY_FORMAT;
1082 }
1083
1084 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
1085 if (errCode != E_OK) {
1086 LOGE("[Query] Get statement fail!");
1087 return -E_INVALID_QUERY_FORMAT;
1088 }
1089
1090 int index = 1; // begin with 1.
1091 if (hasSubQuery) {
1092 /**
1093 * SELECT b.data_key,b.device,b.ori_device,b.timestamp as naturalbase_rdb_timestamp,
1094 * b.wtimestamp,b.flag,b.hash_key,a.*
1095 * FROM tableName AS a INNER JOIN naturalbase_rdb_log AS b
1096 * ON a.rowid=b.data_key
1097 * WHERE (b.flag&0x03=0x02)
1098 * ORDER BY a.xxx
1099 * LIMIT ? OFFSET ? )
1100 */
1101 errCode = BindObjNodes(statement, index);
1102 if (errCode != E_OK) {
1103 return errCode;
1104 }
1105 if (!hasLimit_ || hasOrderBy_) {
1106 errCode = BindTimeRange(statement, index, beginTime, endTime);
1107 }
1108 } else {
1109 /**
1110 * SELECT b.data_key,b.device,b.ori_device,b.timestamp as naturalbase_rdb_timestamp,
1111 * b.wtimestamp,b.flag,b.hash_key,a.*
1112 * FROM tableName AS a INNER JOIN naturalbase_rdb_log AS b
1113 * ON a.rowid=b.data_key
1114 * WHERE (b.flag&0x03=0x02) AND (naturalbase_rdb_timestamp>=? AND naturalbase_rdb_timestamp<?)
1115 * ORDER BY naturalbase_rdb_timestamp ASC;
1116 */
1117 errCode = BindTimeRange(statement, index, beginTime, endTime);
1118 if (errCode != E_OK) {
1119 return errCode;
1120 }
1121 errCode = BindObjNodes(statement, index);
1122 }
1123 return errCode;
1124 }
1125
MapKeysInToSql(size_t keysNum) const1126 std::string SqliteQueryHelper::MapKeysInToSql(size_t keysNum) const
1127 {
1128 std::string resultSql = "key IN (";
1129 for (size_t i = 0; i < keysNum; i++) {
1130 if (i != 0) {
1131 resultSql += ", ";
1132 }
1133 resultSql += "? ";
1134 }
1135 resultSql += ") ";
1136 return resultSql;
1137 }
1138
BindKeysToStmt(const std::set<Key> & keys,sqlite3_stmt * & statement,int & index) const1139 int SqliteQueryHelper::BindKeysToStmt(const std::set<Key> &keys, sqlite3_stmt *&statement, int &index) const
1140 {
1141 if (!keys_.empty()) {
1142 int errCode = E_OK;
1143 for (const auto &key : keys) {
1144 errCode = SQLiteUtils::BindBlobToStatement(statement, index, key);
1145 if (errCode != E_OK) {
1146 LOGE("[Query] Get statement when bind keys failed, errCode = %d", errCode);
1147 return errCode;
1148 }
1149 index++;
1150 }
1151 }
1152 return E_OK;
1153 }
1154
GetRelationalCloudQuerySql(const std::vector<Field> & fields,const bool & isCloudForcePush,bool isCompensatedTask,CloudWaterType mode)1155 std::string SqliteQueryHelper::GetRelationalCloudQuerySql(const std::vector<Field> &fields,
1156 const bool &isCloudForcePush, bool isCompensatedTask, CloudWaterType mode)
1157 {
1158 std::string sql = GetRelationalCloudSyncDataQueryHeader(fields);
1159 AppendCloudQuery(isCloudForcePush, isCompensatedTask, sql, mode);
1160 AppendCloudQueryToGetDiffData(sql, mode);
1161 return sql;
1162 }
1163
GetCountRelationalCloudQuerySql(bool isCloudForcePush,bool isCompensatedTask,CloudWaterType mode)1164 std::string SqliteQueryHelper::GetCountRelationalCloudQuerySql(bool isCloudForcePush, bool isCompensatedTask,
1165 CloudWaterType mode)
1166 {
1167 std::string sql = "SELECT COUNT(*) ";
1168 AppendCloudQuery(isCloudForcePush, isCompensatedTask, sql, mode);
1169 return sql;
1170 }
1171
GetGidRelationalCloudQuerySql(const std::vector<Field> & fields,bool isCloudForcePush,bool isCompensatedTask)1172 std::string SqliteQueryHelper::GetGidRelationalCloudQuerySql(const std::vector<Field> &fields,
1173 bool isCloudForcePush, bool isCompensatedTask)
1174 {
1175 std::string sql = GetRelationalCloudSyncDataQueryHeader(fields);
1176 AppendCloudGidQuery(isCloudForcePush, isCompensatedTask, sql);
1177 return sql;
1178 }
1179
AppendCloudQuery(bool isCloudForcePush,bool isCompensatedTask,std::string & sql,CloudWaterType mode)1180 void SqliteQueryHelper::AppendCloudQuery(bool isCloudForcePush, bool isCompensatedTask, std::string &sql,
1181 CloudWaterType mode)
1182 {
1183 sql += CloudStorageUtils::GetLeftJoinLogSql(tableName_, false);
1184 sql += " WHERE ";
1185 // let data after remove device data at flag_only and logic delete mode and deleted by others to upload to cloud.
1186 if (mode == CloudWaterType::INSERT) {
1187 sql += "(b.cloud_gid == '' and (b.flag & 0x20 != 0) and (b.flag & 0x02 = 0) and (b.flag & 0x08 != 0x08) and";
1188 sql += " (b.flag & 0x01 = 0) and (b.status = 0)) OR ";
1189 }
1190 if (isCompensatedTask && mode == CloudWaterType::DELETE) {
1191 // deleted data does not have primary key, requires gid to compensate sync
1192 sql += "(b.status = 1 AND (b.flag & 0x01 = 0x01) AND b.cloud_gid != '') OR ";
1193 } else if (queryObjNodes_.empty() && mode != CloudWaterType::INSERT) { // means unPriorityTask and not insert
1194 sql += "(b.status != 1) AND ";
1195 }
1196 if (isCloudForcePush) {
1197 sql += " (b.flag & 0x04 != 0x04)";
1198 } else {
1199 sql += "(b.flag & 0x02 = 0x02)";
1200 if (!isCompensatedTask) {
1201 // local data and flag is not upload finished.
1202 sql += " AND (b.flag & 0x400 != 0x400)";
1203 }
1204 }
1205 sql += " AND (b.flag & 0x08 != 0x08) AND (b.cloud_gid != '' or"; // actually, b.cloud_gid will not be null.
1206 sql += " (b.cloud_gid == '' and (b.flag & 0x01 = 0))) ";
1207 }
1208
AppendCloudQueryToGetDiffData(std::string & sql,const CloudWaterType mode,bool isKv)1209 void SqliteQueryHelper::AppendCloudQueryToGetDiffData(std::string &sql, const CloudWaterType mode, bool isKv)
1210 {
1211 switch (mode) {
1212 case DistributedDB::CloudWaterType::DELETE:
1213 sql += isKv ? " AND (flag & 0x01 == 0x01) " :
1214 " AND (b.flag & 0x01 == 0x01) ";
1215 break;
1216 case DistributedDB::CloudWaterType::UPDATE:
1217 sql += isKv ? " AND (flag & 0x01 == 0 AND cloud_gid != '' AND cloud_gid IS NOT NULL) " :
1218 " AND (b.flag & 0x01 == 0 AND b.cloud_gid != '') ";
1219 break;
1220 case DistributedDB::CloudWaterType::INSERT:
1221 sql += isKv ? " AND (flag & 0x01 == 0 AND (cloud_gid == '' OR cloud_gid IS NULL)) " :
1222 " AND (b.flag & 0x01 == 0 AND b.cloud_gid == '') ";
1223 break;
1224 default:
1225 break;
1226 }
1227 }
1228
AppendCloudGidQuery(bool isCloudForcePush,bool isCompensatedTask,std::string & sql)1229 void SqliteQueryHelper::AppendCloudGidQuery(bool isCloudForcePush, bool isCompensatedTask, std::string &sql)
1230 {
1231 sql += CloudStorageUtils::GetLeftJoinLogSql(tableName_, false);
1232 sql += " WHERE ";
1233 if (isCompensatedTask) {
1234 // deleted data does not have primary key, requires gid to compensate sync
1235 sql += " (b.status = 1 AND (b.flag & 0x01 = 0x01)) OR ";
1236 }
1237 // actually, b.cloud_gid will not be null.
1238 sql += isCloudForcePush ? " (b.flag & 0x04 != 0x04) AND (b.cloud_gid != '') " : " (b.cloud_gid != '') ";
1239 }
1240
GetCloudQueryStatement(bool useTimestampAlias,sqlite3 * dbHandle,uint64_t beginTime,std::string & sql,sqlite3_stmt * & statement)1241 int SqliteQueryHelper::GetCloudQueryStatement(bool useTimestampAlias, sqlite3 *dbHandle, uint64_t beginTime,
1242 std::string &sql, sqlite3_stmt *&statement)
1243 {
1244 querySql_.clear(); // clear local query sql format
1245 int errCode = ToQuerySyncSql(false, useTimestampAlias);
1246 if (errCode != E_OK) {
1247 LOGE("To query sql fail! errCode[%d]", errCode);
1248 return errCode;
1249 }
1250 sql += querySql_;
1251 errCode = SQLiteUtils::GetStatement(dbHandle, sql, statement);
1252 if (errCode != E_OK) {
1253 LOGE("[Query] Get statement fail!");
1254 return -E_INVALID_QUERY_FORMAT;
1255 }
1256 int index = 1;
1257 errCode = BindObjNodes(statement, index);
1258 if (errCode != E_OK) {
1259 LOGE("[Query] BindObjNodes failed %d", errCode);
1260 int resetRet = E_OK;
1261 SQLiteUtils::ResetStatement(statement, true, errCode);
1262 if (resetRet != E_OK) {
1263 LOGW("[Query] reset statement failed %d", resetRet);
1264 }
1265 }
1266 return errCode;
1267 }
1268
GetKvCloudQueryStmt(sqlite3 * db,bool forcePush,const CloudWaterType mode,int64_t timeStamp,const std::string & user)1269 std::pair<int, sqlite3_stmt *> SqliteQueryHelper::GetKvCloudQueryStmt(sqlite3 *db, bool forcePush,
1270 const CloudWaterType mode, int64_t timeStamp, const std::string &user)
1271 {
1272 std::pair<int, sqlite3_stmt *> res;
1273 sqlite3_stmt *&stmt = res.second;
1274 int &errCode = res.first;
1275 std::string sql = GetKvCloudQuerySql(false, forcePush);
1276 AppendCloudQueryToGetDiffData(sql, mode, true);
1277 AppendKvQueryObjectOnSql(sql);
1278 sql += "order by modify_time asc";
1279 errCode = SQLiteUtils::GetStatement(db, sql, stmt);
1280 if (errCode != E_OK) {
1281 LOGE("[SqliteQueryHelper] Get kv cloud query stmt failed %d", errCode);
1282 return res;
1283 }
1284 int ret = E_OK;
1285 errCode = SQLiteUtils::BindTextToStatement(stmt, BIND_CLOUD_USER, user);
1286 if (errCode != E_OK) {
1287 SQLiteUtils::ResetStatement(stmt, true, ret);
1288 LOGE("[SqliteQueryHelper] Bind user failed %d reset %d", errCode, ret);
1289 return res;
1290 }
1291 errCode = SQLiteUtils::BindInt64ToStatement(stmt, BIND_CLOUD_TIMESTAMP, timeStamp);
1292 if (errCode != E_OK) {
1293 SQLiteUtils::ResetStatement(stmt, true, ret);
1294 LOGE("[SqliteQueryHelper] Bind begin time failed %d reset %d", errCode, ret);
1295 return res;
1296 }
1297 int index = BIND_CLOUD_TIMESTAMP + 1;
1298 errCode = BindKeysToStmt(keys_, stmt, index);
1299 if (errCode != E_OK) {
1300 SQLiteUtils::ResetStatement(stmt, true, ret);
1301 LOGE("[SqliteQueryHelper] Bind user failed %d reset %d", errCode, ret);
1302 }
1303 return res;
1304 }
1305
GetKvCloudQuerySql(bool countOnly,bool forcePush)1306 std::string SqliteQueryHelper::GetKvCloudQuerySql(bool countOnly, bool forcePush)
1307 {
1308 std::string sql;
1309 if (countOnly) {
1310 sql = QUERY_COUNT_HEAD;
1311 } else {
1312 sql = QUERY_CLOUD_SYNC_DATA_HEAD;
1313 }
1314 sql += QUERY_CLOUD_SYNC_DATA_DETAIL;
1315 sql += QUERY_CLOUD_SYNC_DATA_CONDITION;
1316 sql += " AND flag & 0x02 != 0 "; // get all data which is local
1317 if (forcePush) {
1318 sql += " AND flag & 0x04 != 0x04 "; // get all data which hasn't pushed
1319 } else {
1320 sql += " AND (cloud_flag is null OR cloud_flag & " +
1321 std::to_string(static_cast<uint32_t>(LogInfoFlag::FLAG_UPLOAD_FINISHED)) + " = 0) ";
1322 }
1323 return sql;
1324 }
1325
GetKvCloudRecordSql()1326 std::string SqliteQueryHelper::GetKvCloudRecordSql()
1327 {
1328 std::string sql = QUERY_CLOUD_SYNC_DATA_HEAD;
1329 sql += QUERY_CLOUD_SYNC_DATA_DETAIL;
1330 sql += QUERY_CLOUD_VERSION_RECORD_CONDITION;
1331 return sql;
1332 }
1333
GetCloudVersionRecordSql(bool isDeviceEmpty)1334 std::string SqliteQueryHelper::GetCloudVersionRecordSql(bool isDeviceEmpty)
1335 {
1336 std::string sql = QUERY_CLOUD_VERSION_RECORD_SQL_HEAD;
1337 if (!isDeviceEmpty) {
1338 sql += QUERY_CLOUD_VERSION_RECORD_SQL_DEVICE_CONDITION;
1339 } else {
1340 sql += QUERY_CLOUD_VERSION_RECORD_SQL_EMPTY_DEVICE_CONDITION;
1341 }
1342 return sql;
1343 }
1344
GetAndBindGidKvCloudQueryStatement(const std::string & user,sqlite3 * dbHandle,sqlite3_stmt * & stmt)1345 int SqliteQueryHelper::GetAndBindGidKvCloudQueryStatement(const std::string &user, sqlite3 *dbHandle,
1346 sqlite3_stmt *&stmt)
1347 {
1348 std::string sql = SELECT_CLOUD_GID_SQL;
1349 AppendKvQueryObjectOnSql(sql);
1350 int errCode = SQLiteUtils::GetStatement(dbHandle, sql, stmt);
1351 if (errCode != E_OK) {
1352 LOGE("[SqliteQueryHelper] Get gid stmt failed %d", errCode);
1353 return errCode;
1354 }
1355 int index = 1;
1356 int ret = E_OK;
1357 errCode = SQLiteUtils::BindTextToStatement(stmt, index++, user);
1358 if (errCode != E_OK) {
1359 SQLiteUtils::ResetStatement(stmt, true, ret);
1360 LOGE("[SqliteQueryHelper] Bind user failed %d when query gid", errCode);
1361 return errCode;
1362 }
1363 errCode = BindKeysToStmt(keys_, stmt, index);
1364 if (errCode != E_OK) {
1365 LOGE("[SqliteQueryHelper] Bind keys to query gid stmt failed %d", errCode);
1366 SQLiteUtils::ResetStatement(stmt, true, ret);
1367 }
1368 return errCode;
1369 }
1370
GetCountKvCloudDataStatement(sqlite3 * db,bool forcePush,const CloudWaterType mode,sqlite3_stmt * & stmt)1371 int SqliteQueryHelper::GetCountKvCloudDataStatement(sqlite3 *db, bool forcePush, const CloudWaterType mode,
1372 sqlite3_stmt *&stmt)
1373 {
1374 std::string sql = SqliteQueryHelper::GetKvCloudQuerySql(true, forcePush);
1375 SqliteQueryHelper::AppendCloudQueryToGetDiffData(sql, mode, true);
1376 AppendKvQueryObjectOnSql(sql);
1377 int errCode = SQLiteUtils::GetStatement(db, sql, stmt);
1378 if (errCode != E_OK) {
1379 LOGE("[SqliteQueryHelper] Count data stmt failed %d", errCode);
1380 }
1381 return errCode;
1382 }
1383
BindCountKvCloudDataStatement(sqlite3 * db,bool isMemory,const Timestamp & timestamp,const std::string & user,sqlite3_stmt * & stmt)1384 std::pair<int, int64_t> SqliteQueryHelper::BindCountKvCloudDataStatement(sqlite3 *db, bool isMemory,
1385 const Timestamp ×tamp, const std::string &user, sqlite3_stmt *&stmt)
1386 {
1387 ResFinalizer finalizer([stmt]() {
1388 sqlite3_stmt *statement = stmt;
1389 int ret = E_OK;
1390 SQLiteUtils::ResetStatement(statement, true, ret);
1391 if (ret != E_OK) {
1392 LOGW("[SqliteCloudKvExecutorUtils] Reset log stmt failed %d when get upload count", ret);
1393 }
1394 });
1395 std::pair<int, int64_t> res = { E_OK, 0 };
1396 auto &[errCode, count] = res;
1397 errCode = SQLiteUtils::BindTextToStatement(stmt, BIND_CLOUD_USER, user);
1398 if (errCode != E_OK) {
1399 LOGE("[SqliteQueryHelper] Bind user failed %d when get upload count", errCode);
1400 return res;
1401 }
1402 errCode = SQLiteUtils::BindInt64ToStatement(stmt, BIND_CLOUD_TIMESTAMP, static_cast<int64_t>(timestamp));
1403 if (errCode != E_OK) {
1404 LOGE("[SqliteQueryHelper] Bind begin time failed %d when get upload count", errCode);
1405 return res;
1406 }
1407 int keysIndex = BIND_CLOUD_TIMESTAMP + 1;
1408 errCode = BindKeysToStmt(keys_, stmt, keysIndex);
1409 if (errCode != E_OK) {
1410 LOGE("[SqliteQueryHelper] Bind keys failed %d when get upload count", errCode);
1411 return res;
1412 }
1413 errCode = SQLiteUtils::StepNext(stmt, isMemory);
1414 if (errCode == -E_FINISHED) {
1415 count = 0;
1416 return res;
1417 }
1418 count = sqlite3_column_int64(stmt, CLOUD_QUERY_COUNT_INDEX);
1419 LOGD("[SqliteCloudKvExecutorUtils] Get total upload count %" PRId64, count);
1420 return res;
1421 }
1422
AppendKvQueryObjectOnSql(std::string & sql)1423 void SqliteQueryHelper::AppendKvQueryObjectOnSql(std::string &sql)
1424 {
1425 if (!keys_.empty()) {
1426 sql += " AND " + MapKeysInToSql(keys_.size());
1427 }
1428 }
1429 }