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