1 /*
2  * Copyright (c) 2021 Huawei Device Co., Ltd.
3  * Licensed under the Apache License, Version 2.0 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at
6  *
7  *     http://www.apache.org/licenses/LICENSE-2.0
8  *
9  * Unless required by applicable law or agreed to in writing, software
10  * distributed under the License is distributed on an "AS IS" BASIS,
11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12  * See the License for the specific language governing permissions and
13  * limitations under the License.
14  */
15 
16 #include "sqlite_utils.h"
17 
18 #include <climits>
19 #include <cstring>
20 #include <chrono>
21 #include <thread>
22 #include <mutex>
23 #include <map>
24 #include <algorithm>
25 
26 #include "sqlite_import.h"
27 #include "securec.h"
28 #include "db_constant.h"
29 #include "db_common.h"
30 #include "db_errno.h"
31 #include "log_print.h"
32 #include "value_object.h"
33 #include "schema_utils.h"
34 #include "schema_constant.h"
35 #include "time_helper.h"
36 #include "platform_specific.h"
37 #include "sqlite_relational_utils.h"
38 
39 namespace DistributedDB {
40     std::mutex SQLiteUtils::logMutex_;
41     std::string SQLiteUtils::lastErrorMsg_;
42 namespace {
43     const int BUSY_TIMEOUT_MS = 3000; // 3000ms for sqlite busy timeout.
44     const int BUSY_SLEEP_TIME = 50; // sleep for 50us
45     const int NO_SIZE_LIMIT = -1;
46     const int MAX_STEP_TIMES = 8000;
47     const int BIND_KEY_INDEX = 1;
48     const int BIND_VAL_INDEX = 2;
49     const int USING_STR_LEN = -1;
50     const int MAX_BLOB_READ_SIZE = 5 * 1024 * 1024; // 5M limit
51     const int MAX_TEXT_READ_SIZE = 5 * 1024 * 1024; // 5M limit
52     const int HEAD_SIZE = 3;
53     const int END_SIZE = 3;
54     constexpr int MIN_SIZE = HEAD_SIZE + END_SIZE + 3;
55     const std::string REPLACE_CHAIN = "***";
56     const std::string DEFAULT_ANONYMOUS = "******";
57     const std::string WAL_MODE_SQL = "PRAGMA journal_mode=WAL;";
58     const std::string SYNC_MODE_FULL_SQL = "PRAGMA synchronous=FULL;";
59     const std::string USER_VERSION_SQL = "PRAGMA user_version;";
60     const std::string BEGIN_SQL = "BEGIN TRANSACTION";
61     const std::string BEGIN_IMMEDIATE_SQL = "BEGIN IMMEDIATE TRANSACTION";
62     const std::string COMMIT_SQL = "COMMIT TRANSACTION";
63     const std::string ROLLBACK_SQL = "ROLLBACK TRANSACTION";
64     const std::string DEFAULT_ATTACH_CIPHER = "PRAGMA cipher_default_attach_cipher=";
65     const std::string DEFAULT_ATTACH_KDF_ITER = "PRAGMA cipher_default_attach_kdf_iter=5000";
66     const std::string SHA1_ALGO_SQL = "PRAGMA codec_hmac_algo=SHA1;";
67     const std::string SHA256_ALGO_SQL = "PRAGMA codec_hmac_algo=SHA256;";
68     const std::string SHA256_ALGO_REKEY_SQL = "PRAGMA codec_rekey_hmac_algo=SHA256;";
69     const std::string SHA1_ALGO_ATTACH_SQL = "PRAGMA cipher_default_attach_hmac_algo=SHA1;";
70     const std::string SHA256_ALGO_ATTACH_SQL = "PRAGMA cipher_default_attach_hmac_algo=SHA256;";
71     const std::string EXPORT_BACKUP_SQL = "SELECT export_database('backup');";
72     const std::string CIPHER_CONFIG_SQL = "PRAGMA codec_cipher=";
73     const std::string KDF_ITER_CONFIG_SQL = "PRAGMA codec_kdf_iter=";
74     const std::string BACK_CIPHER_CONFIG_SQL = "PRAGMA backup.codec_cipher=";
75     const std::string BACK_KDF_ITER_CONFIG_SQL = "PRAGMA backup.codec_kdf_iter=5000;";
76     const std::string META_CIPHER_CONFIG_SQL = "PRAGMA meta.codec_cipher=";
77     const std::string META_KDF_ITER_CONFIG_SQL = "PRAGMA meta.codec_kdf_iter=5000;";
78 
79     const constexpr char *DETACH_BACKUP_SQL = "DETACH 'backup'";
80     const constexpr char *UPDATE_META_SQL = "INSERT OR REPLACE INTO meta_data VALUES (?, ?);";
81     const constexpr char *CHECK_TABLE_CREATED = "SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE " \
82         "type='table' AND (tbl_name=? COLLATE NOCASE));";
83     const constexpr char *CHECK_META_DB_TABLE_CREATED = "SELECT EXISTS(SELECT 1 FROM meta.sqlite_master WHERE " \
84         "type='table' AND (tbl_name=? COLLATE NOCASE));";
85 
86     bool g_configLog = false;
87     std::mutex g_serverChangedDataMutex;
88     std::map<std::string, std::map<std::string, DistributedDB::ChangeProperties>> g_serverChangedDataMap;
89 }
90 
91 namespace TriggerMode {
92 const std::map<TriggerModeEnum, std::string> TRIGGER_MODE_MAP = {
93     {TriggerModeEnum::NONE,   ""},
94     {TriggerModeEnum::INSERT, "INSERT"},
95     {TriggerModeEnum::UPDATE, "UPDATE"},
96     {TriggerModeEnum::DELETE, "DELETE"},
97 };
98 
GetTriggerModeString(TriggerModeEnum mode)99 std::string GetTriggerModeString(TriggerModeEnum mode)
100 {
101     auto it = TRIGGER_MODE_MAP.find(mode);
102     return (it == TRIGGER_MODE_MAP.end()) ? "" : it->second;
103 }
104 }
105 
Anonymous(const std::string & name)106 std::string SQLiteUtils::Anonymous(const std::string &name)
107 {
108     if (name.length() <= HEAD_SIZE) {
109         return DEFAULT_ANONYMOUS;
110     }
111 
112     if (name.length() < MIN_SIZE) {
113         return (name.substr(0, HEAD_SIZE) + REPLACE_CHAIN);
114     }
115 
116     return (name.substr(0, HEAD_SIZE) + REPLACE_CHAIN + name.substr(name.length() - END_SIZE, END_SIZE));
117 }
118 
IsNeedSkipLog(const unsigned int errType,const char * msg)119 bool IsNeedSkipLog(const unsigned int errType, const char *msg)
120 {
121     return errType == SQLITE_ERROR && strstr(msg, "\"?\": syntax error in \"PRAGMA user_ve") != nullptr;
122 }
123 
SqliteLogCallback(void * data,int err,const char * msg)124 void SQLiteUtils::SqliteLogCallback(void *data, int err, const char *msg)
125 {
126     bool verboseLog = (data != nullptr);
127     auto errType = static_cast<unsigned int>(err);
128     std::string logMsg = msg == nullptr ? "NULL" : msg;
129     errType &= 0xFF;
130     if (IsNeedSkipLog(errType, logMsg.c_str())) {
131         return;
132     }
133     if (errType == 0 || errType == SQLITE_CONSTRAINT || errType == SQLITE_SCHEMA ||
134         errType == SQLITE_NOTICE || err == SQLITE_WARNING_AUTOINDEX) {
135         if (verboseLog) {
136             LOGD("[SQLite] Error[%d] sys[%d] %s ", err, errno, sqlite3_errstr(err));
137         }
138     } else if (errType == SQLITE_WARNING || errType == SQLITE_IOERR ||
139         errType == SQLITE_CORRUPT || errType == SQLITE_CANTOPEN) {
140         LOGI("[SQLite] Error[%d], sys[%d], %s, msg: %s ", err, errno,
141             sqlite3_errstr(err), SQLiteUtils::Anonymous(logMsg).c_str());
142     } else {
143         LOGE("[SQLite] Error[%d], sys[%d], msg: %s ", err, errno, logMsg.c_str());
144         return;
145     }
146 
147     const char *errMsg = sqlite3_errstr(err);
148     std::lock_guard<std::mutex> autoLock(logMutex_);
149     if (errMsg != nullptr) {
150         lastErrorMsg_ = std::string(errMsg);
151     }
152 }
153 
CreateDataBase(const OpenDbProperties & properties,sqlite3 * & dbTemp,bool setWal)154 int SQLiteUtils::CreateDataBase(const OpenDbProperties &properties, sqlite3 *&dbTemp, bool setWal)
155 {
156     uint64_t flag = SQLITE_OPEN_URI | SQLITE_OPEN_READWRITE;
157     if (properties.createIfNecessary) {
158         flag |= SQLITE_OPEN_CREATE;
159     }
160     std::string cipherName = GetCipherName(properties.cipherType);
161     if (cipherName.empty()) {
162         LOGE("[SQLite] GetCipherName failed");
163         return -E_INVALID_ARGS;
164     }
165     std::string defaultAttachCipher = DEFAULT_ATTACH_CIPHER + cipherName + ";";
166     std::vector<std::string> sqls {defaultAttachCipher, DEFAULT_ATTACH_KDF_ITER};
167     if (setWal) {
168         sqls.push_back(WAL_MODE_SQL);
169     }
170     std::string fileUrl = DBConstant::SQLITE_URL_PRE + properties.uri;
171     int errCode = sqlite3_open_v2(fileUrl.c_str(), &dbTemp, flag, nullptr);
172     if (errCode != SQLITE_OK) {
173         LOGE("[SQLite] open database failed: %d - sys err(%d)", errCode, errno);
174         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
175         goto END;
176     }
177 
178     errCode = SetDataBaseProperty(dbTemp, properties, setWal, sqls);
179     if (errCode != SQLITE_OK) {
180         LOGE("[SQLite] SetDataBaseProperty failed: %d", errCode);
181         goto END;
182     }
183 
184 END:
185     if (errCode != E_OK && dbTemp != nullptr) {
186         (void)sqlite3_close_v2(dbTemp);
187         dbTemp = nullptr;
188     }
189 
190     return errCode;
191 }
192 
OpenDatabase(const OpenDbProperties & properties,sqlite3 * & db,bool setWal)193 int SQLiteUtils::OpenDatabase(const OpenDbProperties &properties, sqlite3 *&db, bool setWal)
194 {
195     {
196         // Only for register the sqlite3 log callback
197         std::lock_guard<std::mutex> lock(logMutex_);
198         if (!g_configLog) {
199             sqlite3_config(SQLITE_CONFIG_LOG, &SqliteLogCallback, &properties.createIfNecessary);
200             g_configLog = true;
201         }
202     }
203     sqlite3 *dbTemp = nullptr;
204     int errCode = CreateDataBase(properties, dbTemp, setWal);
205     if (errCode != E_OK) {
206         goto END;
207     }
208     errCode = RegisterJsonFunctions(dbTemp);
209     if (errCode != E_OK) {
210         goto END;
211     }
212     // Set the synchroized mode, default for full mode.
213     errCode = ExecuteRawSQL(dbTemp, SYNC_MODE_FULL_SQL);
214     if (errCode != E_OK) {
215         LOGE("SQLite sync mode failed: %d", errCode);
216         goto END;
217     }
218 
219     if (!properties.isMemDb) {
220         errCode = SQLiteUtils::SetPersistWalMode(dbTemp);
221         if (errCode != E_OK) {
222             LOGE("SQLite set persist wall mode failed: %d", errCode);
223         }
224     }
225 
226 END:
227     if (errCode != E_OK && dbTemp != nullptr) {
228         (void)sqlite3_close_v2(dbTemp);
229         dbTemp = nullptr;
230     }
231     if (errCode != E_OK && errno == EKEYREVOKED) {
232         errCode = -E_EKEYREVOKED;
233     }
234     db = dbTemp;
235     return errCode;
236 }
237 
GetStatement(sqlite3 * db,const std::string & sql,sqlite3_stmt * & statement)238 int SQLiteUtils::GetStatement(sqlite3 *db, const std::string &sql, sqlite3_stmt *&statement)
239 {
240     if (db == nullptr) {
241         LOGE("Invalid db for statement");
242         return -E_INVALID_DB;
243     }
244     // Prepare the new statement only when the input parameter is not null
245     if (statement != nullptr) {
246         return E_OK;
247     }
248     int errCode = sqlite3_prepare_v2(db, sql.c_str(), NO_SIZE_LIMIT, &statement, nullptr);
249     if (errCode != SQLITE_OK) {
250         LOGE("Prepare SQLite statement failed:%d, sys:%d", errCode, errno);
251         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
252         SQLiteUtils::ResetStatement(statement, true, errCode);
253         return errCode;
254     }
255 
256     if (statement == nullptr) {
257         return -E_INVALID_DB;
258     }
259 
260     return E_OK;
261 }
262 
BindTextToStatement(sqlite3_stmt * statement,int index,const std::string & str)263 int SQLiteUtils::BindTextToStatement(sqlite3_stmt *statement, int index, const std::string &str)
264 {
265     if (statement == nullptr) {
266         return -E_INVALID_ARGS;
267     }
268 
269     int errCode = sqlite3_bind_text(statement, index, str.c_str(), str.length(), SQLITE_TRANSIENT);
270     if (errCode != SQLITE_OK) {
271         LOGE("[SQLiteUtil][Bind text]Failed to bind the value:%d", errCode);
272         return SQLiteUtils::MapSQLiteErrno(errCode);
273     }
274 
275     return E_OK;
276 }
277 
BindInt64ToStatement(sqlite3_stmt * statement,int index,int64_t value)278 int SQLiteUtils::BindInt64ToStatement(sqlite3_stmt *statement, int index, int64_t value)
279 {
280     // statement check outSide
281     int errCode = sqlite3_bind_int64(statement, index, value);
282     if (errCode != SQLITE_OK) {
283         LOGE("[SQLiteUtil][Bind int64]Failed to bind the value:%d", errCode);
284         return SQLiteUtils::MapSQLiteErrno(errCode);
285     }
286 
287     return E_OK;
288 }
289 
BindBlobToStatement(sqlite3_stmt * statement,int index,const std::vector<uint8_t> & value,bool permEmpty)290 int SQLiteUtils::BindBlobToStatement(sqlite3_stmt *statement, int index, const std::vector<uint8_t> &value,
291     bool permEmpty)
292 {
293     if (statement == nullptr) {
294         return -E_INVALID_ARGS;
295     }
296 
297     // Check empty value.
298     if (value.empty() && !permEmpty) {
299         LOGI("[SQLiteUtil][Bind blob]Invalid value");
300         return -E_INVALID_ARGS;
301     }
302 
303     int errCode;
304     if (value.empty()) {
305         errCode = sqlite3_bind_zeroblob(statement, index, -1); // -1 for zero-length blob.
306     } else {
307         errCode = sqlite3_bind_blob(statement, index, static_cast<const void *>(value.data()),
308             value.size(), SQLITE_TRANSIENT);
309     }
310 
311     if (errCode != SQLITE_OK) {
312         LOGE("[SQLiteUtil][Bind blob]Failed to bind the value:%d", errCode);
313         return SQLiteUtils::MapSQLiteErrno(errCode);
314     }
315 
316     return E_OK;
317 }
318 
ResetStatement(sqlite3_stmt * & statement,bool isNeedFinalize,int & errCode)319 void SQLiteUtils::ResetStatement(sqlite3_stmt *&statement, bool isNeedFinalize, int &errCode)
320 {
321     if (statement == nullptr) {
322         return;
323     }
324 
325     int innerCode = SQLITE_OK;
326     // if need finalize the statement, just goto finalize.
327     if (!isNeedFinalize) {
328         // reset the statement firstly.
329         innerCode = sqlite3_reset(statement);
330         if (innerCode != SQLITE_OK) {
331             LOGE("[SQLiteUtils] reset statement error:%d, sys:%d", innerCode, errno);
332             isNeedFinalize = true;
333         } else {
334             sqlite3_clear_bindings(statement);
335         }
336     }
337 
338     if (isNeedFinalize) {
339         int finalizeResult = sqlite3_finalize(statement);
340         if (finalizeResult != SQLITE_OK) {
341             LOGD("[SQLiteUtils] finalize statement error:%d, sys:%d", finalizeResult, errno);
342             innerCode = finalizeResult;
343         }
344         statement = nullptr;
345     }
346 
347     if (innerCode != SQLITE_OK) { // the sqlite error code has higher priority.
348         errCode = SQLiteUtils::MapSQLiteErrno(innerCode);
349     }
350 }
351 
StepWithRetry(sqlite3_stmt * statement,bool isMemDb)352 int SQLiteUtils::StepWithRetry(sqlite3_stmt *statement, bool isMemDb)
353 {
354     if (statement == nullptr) {
355         return -E_INVALID_ARGS;
356     }
357 
358     int errCode = E_OK;
359     int retryCount = 0;
360     do {
361         errCode = sqlite3_step(statement);
362         if ((errCode == SQLITE_LOCKED) && isMemDb) {
363             std::this_thread::sleep_for(std::chrono::microseconds(BUSY_SLEEP_TIME));
364             retryCount++;
365         } else {
366             break;
367         }
368     } while (retryCount <= MAX_STEP_TIMES);
369 
370     if (errCode != SQLITE_DONE && errCode != SQLITE_ROW) {
371         LOGE("[SQLiteUtils] Step error:%d, sys:%d", errCode, errno);
372     }
373 
374     return SQLiteUtils::MapSQLiteErrno(errCode);
375 }
376 
BindPrefixKey(sqlite3_stmt * statement,int index,const Key & keyPrefix)377 int SQLiteUtils::BindPrefixKey(sqlite3_stmt *statement, int index, const Key &keyPrefix)
378 {
379     if (statement == nullptr) {
380         return -E_INVALID_ARGS;
381     }
382 
383     const size_t maxKeySize = DBConstant::MAX_KEY_SIZE;
384     // bind the first prefix key
385     int errCode = BindBlobToStatement(statement, index, keyPrefix, true);
386     if (errCode != SQLITE_OK) {
387         LOGE("Bind the prefix first error:%d", errCode);
388         return SQLiteUtils::MapSQLiteErrno(errCode);
389     }
390 
391     // bind the second prefix key
392     uint8_t end[maxKeySize];
393     errno_t status = memset_s(end, maxKeySize, UCHAR_MAX, maxKeySize); // max byte value is 0xFF.
394     if (status != EOK) {
395         LOGE("memset error:%d", status);
396         return -E_SECUREC_ERROR;
397     }
398 
399     if (!keyPrefix.empty()) {
400         status = memcpy_s(end, maxKeySize, keyPrefix.data(), keyPrefix.size());
401         if (status != EOK) {
402             LOGE("memcpy error:%d", status);
403             return -E_SECUREC_ERROR;
404         }
405     }
406 
407     // index wouldn't be too large, just add one to the first index.
408     errCode = sqlite3_bind_blob(statement, index + 1, end, maxKeySize, SQLITE_TRANSIENT);
409     if (errCode != SQLITE_OK) {
410         LOGE("Bind the prefix second error:%d", errCode);
411         return SQLiteUtils::MapSQLiteErrno(errCode);
412     }
413     return E_OK;
414 }
415 
BeginTransaction(sqlite3 * db,TransactType type)416 int SQLiteUtils::BeginTransaction(sqlite3 *db, TransactType type)
417 {
418     if (type == TransactType::IMMEDIATE) {
419         return ExecuteRawSQL(db, BEGIN_IMMEDIATE_SQL);
420     }
421 
422     return ExecuteRawSQL(db, BEGIN_SQL);
423 }
424 
CommitTransaction(sqlite3 * db)425 int SQLiteUtils::CommitTransaction(sqlite3 *db)
426 {
427     return ExecuteRawSQL(db, COMMIT_SQL);
428 }
429 
RollbackTransaction(sqlite3 * db)430 int SQLiteUtils::RollbackTransaction(sqlite3 *db)
431 {
432     return ExecuteRawSQL(db, ROLLBACK_SQL);
433 }
434 
ExecuteRawSQL(sqlite3 * db,const std::string & sql)435 int SQLiteUtils::ExecuteRawSQL(sqlite3 *db, const std::string &sql)
436 {
437     if (db == nullptr) {
438         return -E_INVALID_DB;
439     }
440 
441     sqlite3_stmt *stmt = nullptr;
442     int errCode = SQLiteUtils::GetStatement(db, sql, stmt);
443     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_OK)) {
444         LOGE("[SQLiteUtils][ExecuteSQL] prepare statement failed(%d), sys(%d)", errCode, errno);
445         return errCode;
446     }
447 
448     do {
449         errCode = SQLiteUtils::StepWithRetry(stmt);
450         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
451             errCode = E_OK;
452             break;
453         } else if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
454             LOGE("[SQLiteUtils][ExecuteSQL] execute statement failed(%d), sys(%d)", errCode, errno);
455             break;
456         }
457     } while (true);
458 
459     int ret = E_OK;
460     SQLiteUtils::ResetStatement(stmt, true, ret);
461     return errCode != E_OK ? errCode : ret;
462 }
463 
SetKey(sqlite3 * db,CipherType type,const CipherPassword & passwd,bool setWal,uint32_t iterTimes)464 int SQLiteUtils::SetKey(sqlite3 *db, CipherType type, const CipherPassword &passwd, bool setWal, uint32_t iterTimes)
465 {
466     if (db == nullptr) {
467         return -E_INVALID_DB;
468     }
469 
470     if (passwd.GetSize() != 0) {
471 #ifndef OMIT_ENCRYPT
472         int errCode = SetKeyInner(db, type, passwd, iterTimes);
473         if (errCode != E_OK) {
474             LOGE("[SQLiteUtils][Setkey] set keyInner failed:%d", errCode);
475             return errCode;
476         }
477         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA256_ALGO_SQL);
478         if (errCode != E_OK) {
479             LOGE("[SQLiteUtils][Setkey] set sha algo failed:%d", errCode);
480             return errCode;
481         }
482         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA256_ALGO_REKEY_SQL);
483         if (errCode != E_OK) {
484             LOGE("[SQLiteUtils][Setkey] set rekey sha algo failed:%d", errCode);
485             return errCode;
486         }
487 #else
488         return -E_NOT_SUPPORT;
489 #endif
490     }
491 
492     // verify key
493     int errCode = SQLiteUtils::ExecuteRawSQL(db, USER_VERSION_SQL);
494     if (errCode != E_OK) {
495         LOGE("[SQLiteUtils][Setkey] verify version failed:%d", errCode);
496         if (errno == EKEYREVOKED) {
497             return -E_EKEYREVOKED;
498         }
499         if (errCode == -E_BUSY) {
500             return errCode;
501         }
502 #ifndef OMIT_ENCRYPT
503         errCode = UpdateCipherShaAlgo(db, setWal, type, passwd, iterTimes);
504         if (errCode != E_OK) {
505             LOGE("[SQLiteUtils][Setkey] upgrade cipher sha algo failed:%d", errCode);
506         }
507 #endif
508     }
509     return errCode;
510 }
511 
GetColumnBlobValue(sqlite3_stmt * statement,int index,std::vector<uint8_t> & value)512 int SQLiteUtils::GetColumnBlobValue(sqlite3_stmt *statement, int index, std::vector<uint8_t> &value)
513 {
514     if (statement == nullptr) {
515         return -E_INVALID_ARGS;
516     }
517 
518     int keySize = sqlite3_column_bytes(statement, index);
519     if (keySize < 0) {
520         LOGW("[SQLiteUtils][Column blob] size less than zero:%d", keySize);
521         value.resize(0);
522         return E_OK;
523     }
524     auto keyRead = static_cast<const uint8_t *>(sqlite3_column_blob(statement, index));
525     if (keySize == 0 || keyRead == nullptr) {
526         value.resize(0);
527     } else {
528         if (keySize > MAX_BLOB_READ_SIZE) {
529             LOGW("[SQLiteUtils][Column blob] size over limit:%d", keySize);
530             keySize = MAX_BLOB_READ_SIZE + 1;
531         }
532         value.resize(keySize);
533         value.assign(keyRead, keyRead + keySize);
534     }
535     return E_OK;
536 }
537 
GetColumnTextValue(sqlite3_stmt * statement,int index,std::string & value)538 int SQLiteUtils::GetColumnTextValue(sqlite3_stmt *statement, int index, std::string &value)
539 {
540     if (statement == nullptr) {
541         return -E_INVALID_ARGS;
542     }
543 
544     int valSize = sqlite3_column_bytes(statement, index);
545     if (valSize < 0) {
546         LOGW("[SQLiteUtils][Column Text] size less than zero:%d", valSize);
547         value = {};
548         return E_OK;
549     }
550     const unsigned char *val = sqlite3_column_text(statement, index);
551     if (valSize == 0 || val == nullptr) {
552         value = {};
553         return E_OK;
554     }
555     value = std::string(reinterpret_cast<const char *>(val));
556     if (valSize > MAX_TEXT_READ_SIZE) {
557         LOGW("[SQLiteUtils][Column text] size over limit:%d", valSize);
558         value.resize(MAX_TEXT_READ_SIZE + 1); // Reset value size to invalid
559     }
560     return E_OK;
561 }
562 
AttachNewDatabase(sqlite3 * db,CipherType type,const CipherPassword & password,const std::string & attachDbAbsPath,const std::string & attachAsName)563 int SQLiteUtils::AttachNewDatabase(sqlite3 *db, CipherType type, const CipherPassword &password,
564     const std::string &attachDbAbsPath, const std::string &attachAsName)
565 {
566 #ifndef OMIT_ENCRYPT
567     int errCode = SQLiteUtils::ExecuteRawSQL(db, SHA256_ALGO_ATTACH_SQL);
568     if (errCode != E_OK) {
569         LOGE("[SQLiteUtils][AttachNewDatabase] set attach sha256 algo failed:%d", errCode);
570         return errCode;
571     }
572 #endif
573     errCode = AttachNewDatabaseInner(db, type, password, attachDbAbsPath, attachAsName);
574 #ifndef OMIT_ENCRYPT
575     if (errCode == -E_INVALID_PASSWD_OR_CORRUPTED_DB) {
576         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA1_ALGO_ATTACH_SQL);
577         if (errCode != E_OK) {
578             LOGE("[SQLiteUtils][AttachNewDatabase] set attach sha1 algo failed:%d", errCode);
579             return errCode;
580         }
581         errCode = AttachNewDatabaseInner(db, type, password, attachDbAbsPath, attachAsName);
582         if (errCode != E_OK) {
583             LOGE("[SQLiteUtils][AttachNewDatabase] attach db failed:%d", errCode);
584             return errCode;
585         }
586         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA256_ALGO_ATTACH_SQL);
587         if (errCode != E_OK) {
588             LOGE("[SQLiteUtils][AttachNewDatabase] set attach sha256 algo failed:%d", errCode);
589         }
590     }
591 #endif
592     return errCode;
593 }
594 
AttachNewDatabaseInner(sqlite3 * db,CipherType type,const CipherPassword & password,const std::string & attachDbAbsPath,const std::string & attachAsName)595 int SQLiteUtils::AttachNewDatabaseInner(sqlite3 *db, CipherType type, const CipherPassword &password,
596     const std::string &attachDbAbsPath, const std::string &attachAsName)
597 {
598     // example: "ATTACH '../new.db' AS backup KEY XXXX;"
599     std::string attachSql = "ATTACH ? AS " + attachAsName + " KEY ?;"; // Internal interface not need verify alias name
600 
601     sqlite3_stmt* statement = nullptr;
602     int errCode = SQLiteUtils::GetStatement(db, attachSql, statement);
603     if (errCode != E_OK) {
604         return errCode;
605     }
606     // 1st is name.
607     errCode = sqlite3_bind_text(statement, 1, attachDbAbsPath.c_str(), attachDbAbsPath.length(), SQLITE_TRANSIENT);
608     if (errCode != SQLITE_OK) {
609         LOGE("Bind the attached db name failed:%d", errCode);
610         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
611         goto END;
612     }
613     // Passwords do not allow vector operations, so we can not use function BindBlobToStatement here.
614     errCode = sqlite3_bind_blob(statement, 2, static_cast<const void *>(password.GetData()),  // 2 means password index.
615         password.GetSize(), SQLITE_TRANSIENT);
616     if (errCode != SQLITE_OK) {
617         LOGE("Bind the attached key failed:%d", errCode);
618         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
619         goto END;
620     }
621 
622     errCode = SQLiteUtils::StepWithRetry(statement);
623     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
624         LOGE("Execute the SQLite attach failed:%d", errCode);
625         goto END;
626     }
627     errCode = SQLiteUtils::ExecuteRawSQL(db, WAL_MODE_SQL);
628     if (errCode != E_OK) {
629         LOGE("Set journal mode failed: %d", errCode);
630     }
631 
632 END:
633     SQLiteUtils::ResetStatement(statement, true, errCode);
634     return errCode;
635 }
636 
CreateMetaDatabase(const std::string & metaDbPath)637 int SQLiteUtils::CreateMetaDatabase(const std::string &metaDbPath)
638 {
639     OpenDbProperties metaProperties {metaDbPath, true, false};
640     sqlite3 *db = nullptr;
641     int errCode = SQLiteUtils::OpenDatabase(metaProperties, db);
642     if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
643         LOGE("[CreateMetaDatabase] Failed to create the meta database[%d]", errCode);
644     }
645     if (db != nullptr) { // LCOV_EXCL_BR_LINE
646         (void)sqlite3_close_v2(db);
647         db = nullptr;
648     }
649     return errCode;
650 }
CheckIntegrity(sqlite3 * db,const std::string & sql)651 int SQLiteUtils::CheckIntegrity(sqlite3 *db, const std::string &sql)
652 {
653     sqlite3_stmt *statement = nullptr;
654     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
655     if (errCode != E_OK) {
656         LOGE("Prepare the integrity check statement error:%d", errCode);
657         return errCode;
658     }
659     int resultCnt = 0;
660     bool checkResultOK = false;
661     do {
662         errCode = SQLiteUtils::StepWithRetry(statement);
663         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
664             break;
665         } else if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
666             auto result = reinterpret_cast<const char *>(sqlite3_column_text(statement, 0));
667             if (result == nullptr) {
668                 continue;
669             }
670             resultCnt = (resultCnt > 1) ? resultCnt : (resultCnt + 1);
671             if (strcmp(result, "ok") == 0) {
672                 checkResultOK = true;
673             }
674         } else {
675             checkResultOK = false;
676             LOGW("Step for the integrity check failed:%d", errCode);
677             break;
678         }
679     } while (true);
680     if (resultCnt == 1 && checkResultOK) {
681         errCode = E_OK;
682     } else {
683         errCode = -E_INVALID_PASSWD_OR_CORRUPTED_DB;
684     }
685     SQLiteUtils::ResetStatement(statement, true, errCode);
686     return errCode;
687 }
688 #ifdef RELATIONAL_STORE
689 
690 namespace { // anonymous namespace for schema analysis
AnalysisSchemaSqlAndTrigger(sqlite3 * db,const std::string & tableName,TableInfo & table,bool caseSensitive)691 int AnalysisSchemaSqlAndTrigger(sqlite3 *db, const std::string &tableName, TableInfo &table, bool caseSensitive)
692 {
693     std::string sql = "SELECT type, sql FROM sqlite_master WHERE tbl_name = ? ";
694     if (!caseSensitive) {
695         sql += "COLLATE NOCASE";
696     }
697     sqlite3_stmt *statement = nullptr;
698     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
699     if (errCode != E_OK) {
700         LOGE("[AnalysisSchema] Prepare the analysis schema sql and trigger statement error:%d", errCode);
701         return errCode;
702     }
703     errCode = SQLiteUtils::BindTextToStatement(statement, 1, tableName);
704     if (errCode != E_OK) {
705         LOGE("[AnalysisSchema] Bind table name failed:%d", errCode);
706         SQLiteUtils::ResetStatement(statement, true, errCode);
707         return errCode;
708     }
709 
710     errCode = -E_NOT_FOUND;
711     do {
712         int err = SQLiteUtils::StepWithRetry(statement);
713         if (err == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
714             break;
715         } else if (err == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
716             errCode = E_OK;
717             std::string type;
718             (void) SQLiteUtils::GetColumnTextValue(statement, 0, type);
719             if (type == "table") {
720                 std::string createTableSql;
721                 (void) SQLiteUtils::GetColumnTextValue(statement, 1, createTableSql); // 1 means create table sql
722                 table.SetCreateTableSql(createTableSql);
723             }
724         } else {
725             LOGE("[AnalysisSchema] Step for the analysis create table sql and trigger failed:%d", err);
726             errCode = SQLiteUtils::MapSQLiteErrno(err);
727             break;
728         }
729     } while (true);
730     SQLiteUtils::ResetStatement(statement, true, errCode);
731     return errCode;
732 }
733 
GetSchemaIndexList(sqlite3 * db,const std::string & tableName,std::vector<std::string> & indexList,std::vector<std::string> & uniqueList)734 int GetSchemaIndexList(sqlite3 *db, const std::string &tableName, std::vector<std::string> &indexList,
735     std::vector<std::string> &uniqueList)
736 {
737     std::string sql = "pragma index_list('" + tableName + "')";
738     sqlite3_stmt *statement = nullptr;
739     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
740     if (errCode != E_OK) {
741         LOGE("[AnalysisSchema] Prepare the get schema index list statement error:%d", errCode);
742         return errCode;
743     }
744 
745     do {
746         errCode = SQLiteUtils::StepWithRetry(statement);
747         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
748             errCode = E_OK;
749             break;
750         } else if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
751             std::string indexName;
752             (void) SQLiteUtils::GetColumnTextValue(statement, 1, indexName);  // 1 means index name
753             std::string origin;
754             (void) SQLiteUtils::GetColumnTextValue(statement, 3, origin);  // 3 means index type, whether unique
755             if (origin == "c") { // 'c' means index created by user declare
756                 indexList.push_back(indexName);
757             } else if (origin == "u") { // 'u' means an unique define
758                 uniqueList.push_back(indexName);
759             }
760         } else {
761             LOGW("[AnalysisSchema] Step for the get schema index list failed:%d", errCode);
762             break;
763         }
764     } while (true);
765     SQLiteUtils::ResetStatement(statement, true, errCode);
766     return errCode;
767 }
768 
AnalysisSchemaIndexDefine(sqlite3 * db,const std::string & indexName,CompositeFields & indexDefine)769 int AnalysisSchemaIndexDefine(sqlite3 *db, const std::string &indexName, CompositeFields &indexDefine)
770 {
771     auto sql = "pragma index_info('" + indexName + "')";
772     sqlite3_stmt *statement = nullptr;
773     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
774     if (errCode != E_OK) {
775         LOGE("[AnalysisSchema] Prepare the analysis schema index statement error:%d", errCode);
776         return errCode;
777     }
778 
779     do {
780         errCode = SQLiteUtils::StepWithRetry(statement);
781         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
782             errCode = E_OK;
783             break;
784         } else if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
785             std::string indexField;
786             (void) SQLiteUtils::GetColumnTextValue(statement, 2, indexField);  // 2 means index's column name.
787             indexDefine.push_back(indexField);
788         } else {
789             LOGW("[AnalysisSchema] Step for the analysis schema index failed:%d", errCode);
790             break;
791         }
792     } while (true);
793 
794     SQLiteUtils::ResetStatement(statement, true, errCode);
795     return errCode;
796 }
797 
AnalysisSchemaIndex(sqlite3 * db,const std::string & tableName,TableInfo & table)798 int AnalysisSchemaIndex(sqlite3 *db, const std::string &tableName, TableInfo &table)
799 {
800     std::vector<std::string> indexList;
801     std::vector<std::string> uniqueList;
802     int errCode = GetSchemaIndexList(db, tableName, indexList, uniqueList);
803     if (errCode != E_OK) {
804         LOGE("[AnalysisSchema] get schema index list failed.");
805         return errCode;
806     }
807 
808     for (const auto &indexName : indexList) {
809         CompositeFields indexDefine;
810         errCode = AnalysisSchemaIndexDefine(db, indexName, indexDefine);
811         if (errCode != E_OK) {
812             LOGE("[AnalysisSchema] analysis schema index columns failed.");
813             return errCode;
814         }
815         table.AddIndexDefine(indexName, indexDefine);
816     }
817 
818     std::vector<CompositeFields> uniques;
819     for (const auto &uniqueName : uniqueList) {
820         CompositeFields uniqueDefine;
821         errCode = AnalysisSchemaIndexDefine(db, uniqueName, uniqueDefine);
822         if (errCode != E_OK) {
823             LOGE("[AnalysisSchema] analysis schema unique columns failed.");
824             return errCode;
825         }
826         uniques.push_back(uniqueDefine);
827     }
828     table.SetUniqueDefine(uniques);
829     return E_OK;
830 }
831 
SetPrimaryKeyCollateType(const std::string & sql,FieldInfo & field)832 void SetPrimaryKeyCollateType(const std::string &sql, FieldInfo &field)
833 {
834     std::string upperFieldName = DBCommon::ToUpperCase(field.GetFieldName());
835     if (DBCommon::HasConstraint(sql, "PRIMARY KEY COLLATE NOCASE", " ", " ,)") ||
836         DBCommon::HasConstraint(sql, upperFieldName + " TEXT COLLATE NOCASE", " (,", " ,")) {
837         field.SetCollateType(CollateType::COLLATE_NOCASE);
838     } else if (DBCommon::HasConstraint(sql, "PRIMARY KEY COLLATE RTRIM", " ", " ,)") ||
839         DBCommon::HasConstraint(sql, upperFieldName + " TEXT COLLATE RTRIM", " (,", " ,")) {
840         field.SetCollateType(CollateType::COLLATE_RTRIM);
841     }
842 }
843 
SetFieldInfo(sqlite3_stmt * statement,TableInfo & table)844 int SetFieldInfo(sqlite3_stmt *statement, TableInfo &table)
845 {
846     FieldInfo field;
847     field.SetColumnId(sqlite3_column_int(statement, 0));  // 0 means column id index
848 
849     std::string tmpString;
850     (void) SQLiteUtils::GetColumnTextValue(statement, 1, tmpString);  // 1 means column name index
851     if (!DBCommon::CheckIsAlnumOrUnderscore(tmpString)) {
852         LOGE("[AnalysisSchema] unsupported field name.");
853         return -E_NOT_SUPPORT;
854     }
855     field.SetFieldName(tmpString);
856 
857     (void) SQLiteUtils::GetColumnTextValue(statement, 2, tmpString);  // 2 means datatype index
858     field.SetDataType(tmpString);
859 
860     field.SetNotNull(static_cast<bool>(sqlite3_column_int64(statement, 3)));  // 3 means whether null index
861 
862     (void) SQLiteUtils::GetColumnTextValue(statement, 4, tmpString);  // 4 means default value index
863     if (!tmpString.empty()) {
864         field.SetDefaultValue(tmpString);
865     }
866 
867     int keyIndex = sqlite3_column_int(statement, 5); // 5 means primary key index
868     if (keyIndex != 0) {  // not 0 means is a primary key
869         table.SetPrimaryKey(field.GetFieldName(), keyIndex);
870         SetPrimaryKeyCollateType(table.GetCreateTableSql(), field);
871     }
872     table.AddField(field);
873     return E_OK;
874 }
875 } // end of anonymous namespace for schema analysis
876 
AnalysisSchemaFieldDefine(sqlite3 * db,const std::string & tableName,TableInfo & table)877 int SQLiteUtils::AnalysisSchemaFieldDefine(sqlite3 *db, const std::string &tableName, TableInfo &table)
878 {
879     std::string sql = "pragma table_info('" + tableName + "')";
880     sqlite3_stmt *statement = nullptr;
881     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
882     if (errCode != E_OK) {
883         LOGE("[AnalysisSchema] Prepare the analysis schema field statement error:%d", errCode);
884         return errCode;
885     }
886 
887     do {
888         errCode = SQLiteUtils::StepWithRetry(statement);
889         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
890             errCode = E_OK;
891             break;
892         } else if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
893             errCode = SetFieldInfo(statement, table);
894             if (errCode != E_OK) {
895                 break;
896             }
897         } else {
898             LOGW("[AnalysisSchema] Step for the analysis schema field failed:%d", errCode);
899             break;
900         }
901     } while (true);
902 
903     if (table.GetPrimaryKey().empty()) {
904         table.SetPrimaryKey("rowid", 1);
905     }
906 
907     SQLiteUtils::ResetStatement(statement, true, errCode);
908     return errCode;
909 }
910 
AnalysisSchema(sqlite3 * db,const std::string & tableName,TableInfo & table,bool caseSensitive)911 int SQLiteUtils::AnalysisSchema(sqlite3 *db, const std::string &tableName, TableInfo &table, bool caseSensitive)
912 {
913     if (db == nullptr) {
914         return -E_INVALID_DB;
915     }
916 
917     if (!DBCommon::CheckIsAlnumOrUnderscore(tableName)) {
918         LOGE("[AnalysisSchema] unsupported table name.");
919         return -E_NOT_SUPPORT;
920     }
921 
922     int errCode = AnalysisSchemaSqlAndTrigger(db, tableName, table, caseSensitive);
923     if (errCode != E_OK) {
924         LOGE("[AnalysisSchema] Analysis sql and trigger failed. errCode = [%d]", errCode);
925         return errCode;
926     }
927 
928     errCode = AnalysisSchemaIndex(db, tableName, table);
929     if (errCode != E_OK) {
930         LOGE("[AnalysisSchema] Analysis index failed.");
931         return errCode;
932     }
933 
934     errCode = AnalysisSchemaFieldDefine(db, tableName, table);
935     if (errCode != E_OK) {
936         LOGE("[AnalysisSchema] Analysis field failed.");
937         return errCode;
938     }
939 
940     table.SetTableName(tableName);
941     return E_OK;
942 }
943 #endif
944 #ifndef OMIT_ENCRYPT
ExportDatabase(sqlite3 * db,CipherType type,const CipherPassword & passwd,const std::string & newDbName)945 int SQLiteUtils::ExportDatabase(sqlite3 *db, CipherType type, const CipherPassword &passwd,
946     const std::string &newDbName)
947 {
948     if (db == nullptr) {
949         return -E_INVALID_DB;
950     }
951 
952     int errCode = AttachNewDatabase(db, type, passwd, newDbName);
953     if (errCode != E_OK) {
954         LOGE("Attach New Db fail!");
955         return errCode;
956     }
957     errCode = SQLiteUtils::ExecuteRawSQL(db, EXPORT_BACKUP_SQL);
958     if (errCode != E_OK) {
959         LOGE("Execute the SQLite export failed:%d", errCode);
960     }
961 
962     int detachError = SQLiteUtils::ExecuteRawSQL(db, DETACH_BACKUP_SQL);
963     if (errCode == E_OK) {
964         errCode = detachError;
965         if (detachError != E_OK) {
966             LOGE("Execute the SQLite detach failed:%d", errCode);
967         }
968     }
969     return errCode;
970 }
971 
Rekey(sqlite3 * db,const CipherPassword & passwd)972 int SQLiteUtils::Rekey(sqlite3 *db, const CipherPassword &passwd)
973 {
974     if (db == nullptr) {
975         return -E_INVALID_DB;
976     }
977 
978     int errCode = sqlite3_rekey(db, static_cast<const void *>(passwd.GetData()), static_cast<int>(passwd.GetSize()));
979     if (errCode != E_OK) {
980         LOGE("SQLite rekey failed:(%d)", errCode);
981         return SQLiteUtils::MapSQLiteErrno(errCode);
982     }
983 
984     return E_OK;
985 }
986 #else
ExportDatabase(sqlite3 * db,CipherType type,const CipherPassword & passwd,const std::string & newDbName)987 int SQLiteUtils::ExportDatabase(sqlite3 *db, CipherType type, const CipherPassword &passwd,
988     const std::string &newDbName)
989 {
990     (void)db;
991     (void)type;
992     (void)passwd;
993     (void)newDbName;
994     return -E_NOT_SUPPORT;
995 }
996 
Rekey(sqlite3 * db,const CipherPassword & passwd)997 int SQLiteUtils::Rekey(sqlite3 *db, const CipherPassword &passwd)
998 {
999     (void)db;
1000     (void)passwd;
1001     return -E_NOT_SUPPORT;
1002 }
1003 #endif
1004 
GetVersion(const OpenDbProperties & properties,int & version)1005 int SQLiteUtils::GetVersion(const OpenDbProperties &properties, int &version)
1006 {
1007     if (properties.uri.empty()) { // LCOV_EXCL_BR_LINE
1008         return -E_INVALID_ARGS;
1009     }
1010 
1011     sqlite3 *dbTemp = nullptr;
1012     // Please make sure the database file exists and is working properly
1013     std::string fileUrl = DBConstant::SQLITE_URL_PRE + properties.uri;
1014     int errCode = sqlite3_open_v2(fileUrl.c_str(), &dbTemp, SQLITE_OPEN_URI | SQLITE_OPEN_READONLY, nullptr);
1015     if (errCode != SQLITE_OK) { // LCOV_EXCL_BR_LINE
1016         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
1017         LOGE("Open database failed: %d, sys:%d", errCode, errno);
1018         goto END;
1019     }
1020     // in memory mode no need cipher
1021     if (!properties.isMemDb) { // LCOV_EXCL_BR_LINE
1022         errCode = SQLiteUtils::SetKey(dbTemp, properties.cipherType, properties.passwd, false,
1023             properties.iterTimes);
1024         if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
1025             LOGE("Set key failed: %d", errCode);
1026             goto END;
1027         }
1028     }
1029 
1030     errCode = GetVersion(dbTemp, version);
1031 
1032 END:
1033     if (dbTemp != nullptr) { // LCOV_EXCL_BR_LINE
1034         (void)sqlite3_close_v2(dbTemp);
1035         dbTemp = nullptr;
1036     }
1037     return errCode;
1038 }
1039 
GetVersion(sqlite3 * db,int & version)1040 int SQLiteUtils::GetVersion(sqlite3 *db, int &version)
1041 {
1042     if (db == nullptr) {
1043         return -E_INVALID_DB;
1044     }
1045 
1046     std::string strSql = "PRAGMA user_version;";
1047     sqlite3_stmt *statement = nullptr;
1048     int errCode = sqlite3_prepare(db, strSql.c_str(), -1, &statement, nullptr);
1049     if (errCode != SQLITE_OK || statement == nullptr) {
1050         LOGE("[SqlUtil][GetVer] sqlite3_prepare failed.");
1051         errCode = SQLiteUtils::MapSQLiteErrno(errCode);
1052         return errCode;
1053     }
1054 
1055     if (sqlite3_step(statement) == SQLITE_ROW) {
1056         // Get pragma user_version at first column
1057         version = sqlite3_column_int(statement, 0);
1058     } else {
1059         LOGE("[SqlUtil][GetVer] Get db user_version failed.");
1060         errCode = SQLiteUtils::MapSQLiteErrno(SQLITE_ERROR);
1061     }
1062 
1063     SQLiteUtils::ResetStatement(statement, true, errCode);
1064     return errCode;
1065 }
1066 
GetJournalMode(sqlite3 * db,std::string & mode)1067 int SQLiteUtils::GetJournalMode(sqlite3 *db, std::string &mode)
1068 {
1069     if (db == nullptr) {
1070         return -E_INVALID_DB;
1071     }
1072 
1073     std::string sql = "PRAGMA journal_mode;";
1074     sqlite3_stmt *statement = nullptr;
1075     int errCode = SQLiteUtils::GetStatement(db, sql, statement);
1076     if (errCode != E_OK || statement == nullptr) {
1077         return errCode;
1078     }
1079 
1080     errCode = SQLiteUtils::StepWithRetry(statement);
1081     if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
1082         errCode = SQLiteUtils::GetColumnTextValue(statement, 0, mode);
1083     } else {
1084         LOGE("[SqlUtil][GetJournal] Get db journal_mode failed.");
1085     }
1086 
1087     SQLiteUtils::ResetStatement(statement, true, errCode);
1088     return errCode;
1089 }
1090 
SetUserVer(const OpenDbProperties & properties,int version)1091 int SQLiteUtils::SetUserVer(const OpenDbProperties &properties, int version)
1092 {
1093     if (properties.uri.empty()) { // LCOV_EXCL_BR_LINE
1094         return -E_INVALID_ARGS;
1095     }
1096 
1097     // Please make sure the database file exists and is working properly
1098     sqlite3 *db = nullptr;
1099     int errCode = SQLiteUtils::OpenDatabase(properties, db);
1100     if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
1101         return errCode;
1102     }
1103 
1104     // Set user version
1105     errCode = SQLiteUtils::SetUserVer(db, version);
1106     if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
1107         LOGE("Set user version fail: %d", errCode);
1108         goto END;
1109     }
1110 
1111 END:
1112     if (db != nullptr) { // LCOV_EXCL_BR_LINE
1113         (void)sqlite3_close_v2(db);
1114         db = nullptr;
1115     }
1116 
1117     return errCode;
1118 }
1119 
SetUserVer(sqlite3 * db,int version)1120 int SQLiteUtils::SetUserVer(sqlite3 *db, int version)
1121 {
1122     if (db == nullptr) {
1123         return -E_INVALID_DB;
1124     }
1125     std::string userVersionSql = "PRAGMA user_version=" + std::to_string(version) + ";";
1126     return SQLiteUtils::ExecuteRawSQL(db, userVersionSql);
1127 }
1128 
MapSQLiteErrno(int errCode)1129 int SQLiteUtils::MapSQLiteErrno(int errCode)
1130 {
1131     switch (errCode) {
1132         case SQLITE_OK:
1133             return E_OK;
1134         case SQLITE_IOERR:
1135             if (errno == EKEYREVOKED) {
1136                 return -E_EKEYREVOKED;
1137             }
1138             break;
1139         case SQLITE_CORRUPT:
1140         case SQLITE_NOTADB:
1141             return -E_INVALID_PASSWD_OR_CORRUPTED_DB;
1142         case SQLITE_LOCKED:
1143         case SQLITE_BUSY:
1144             return -E_BUSY;
1145         case SQLITE_ERROR:
1146             if (errno == EKEYREVOKED) {
1147                 return -E_EKEYREVOKED;
1148             }
1149             break;
1150         case SQLITE_AUTH:
1151             return -E_DENIED_SQL;
1152         case SQLITE_CONSTRAINT:
1153             return -E_CONSTRAINT;
1154         case SQLITE_CANTOPEN:
1155             return -E_SQLITE_CANT_OPEN;
1156         default:
1157             break;
1158     }
1159     return -errCode;
1160 }
1161 
SetBusyTimeout(sqlite3 * db,int timeout)1162 int SQLiteUtils::SetBusyTimeout(sqlite3 *db, int timeout)
1163 {
1164     if (db == nullptr) {
1165         return -E_INVALID_DB;
1166     }
1167 
1168     // Set the default busy handler to retry automatically before returning SQLITE_BUSY.
1169     int errCode = sqlite3_busy_timeout(db, timeout);
1170     if (errCode != SQLITE_OK) {
1171         LOGE("[SQLite] set busy timeout failed:%d", errCode);
1172     }
1173 
1174     return SQLiteUtils::MapSQLiteErrno(errCode);
1175 }
1176 
1177 #ifndef OMIT_ENCRYPT
ExportDatabase(const std::string & srcFile,CipherType type,const CipherPassword & srcPasswd,const std::string & targetFile,const CipherPassword & passwd)1178 int SQLiteUtils::ExportDatabase(const std::string &srcFile, CipherType type, const CipherPassword &srcPasswd,
1179     const std::string &targetFile, const CipherPassword &passwd)
1180 {
1181     std::vector<std::string> createTableSqls;
1182     OpenDbProperties option = {srcFile, true, false, createTableSqls, type, srcPasswd};
1183     sqlite3 *db = nullptr;
1184     int errCode = SQLiteUtils::OpenDatabase(option, db);
1185     if (errCode != E_OK) {
1186         LOGE("Open db error while exporting:%d", errCode);
1187         return errCode;
1188     }
1189 
1190     errCode = SQLiteUtils::ExportDatabase(db, type, passwd, targetFile);
1191     if (db != nullptr) {
1192         (void)sqlite3_close_v2(db);
1193         db = nullptr;
1194     }
1195     return errCode;
1196 }
1197 #else
ExportDatabase(const std::string & srcFile,CipherType type,const CipherPassword & srcPasswd,const std::string & targetFile,const CipherPassword & passwd)1198 int SQLiteUtils::ExportDatabase(const std::string &srcFile, CipherType type, const CipherPassword &srcPasswd,
1199     const std::string &targetFile, const CipherPassword &passwd)
1200 {
1201     (void)srcFile;
1202     (void)type;
1203     (void)srcPasswd;
1204     (void)targetFile;
1205     (void)passwd;
1206     return -E_NOT_SUPPORT;
1207 }
1208 #endif
1209 
SaveSchema(sqlite3 * db,const std::string & strSchema)1210 int SQLiteUtils::SaveSchema(sqlite3 *db, const std::string &strSchema)
1211 {
1212     if (db == nullptr) {
1213         return -E_INVALID_DB;
1214     }
1215 
1216     sqlite3_stmt *statement = nullptr;
1217     std::string sql = "INSERT OR REPLACE INTO meta_data VALUES(?,?);";
1218     int errCode = GetStatement(db, sql, statement);
1219     if (errCode != E_OK) {
1220         return errCode;
1221     }
1222 
1223     Key schemaKey;
1224     DBCommon::StringToVector(DBConstant::SCHEMA_KEY, schemaKey);
1225     errCode = BindBlobToStatement(statement, BIND_KEY_INDEX, schemaKey, false);
1226     if (errCode != E_OK) {
1227         ResetStatement(statement, true, errCode);
1228         return errCode;
1229     }
1230 
1231     Value schemaValue;
1232     DBCommon::StringToVector(strSchema, schemaValue);
1233     errCode = BindBlobToStatement(statement, BIND_VAL_INDEX, schemaValue, false);
1234     if (errCode != E_OK) {
1235         ResetStatement(statement, true, errCode);
1236         return errCode;
1237     }
1238 
1239     errCode = StepWithRetry(statement); // memory db does not support schema
1240     if (errCode != MapSQLiteErrno(SQLITE_DONE)) {
1241         LOGE("[SqlUtil][SetSchema] StepWithRetry fail, errCode=%d.", errCode);
1242         ResetStatement(statement, true, errCode);
1243         return errCode;
1244     }
1245     errCode = E_OK;
1246     ResetStatement(statement, true, errCode);
1247     return errCode;
1248 }
1249 
GetSchema(sqlite3 * db,std::string & strSchema)1250 int SQLiteUtils::GetSchema(sqlite3 *db, std::string &strSchema)
1251 {
1252     if (db == nullptr) {
1253         return -E_INVALID_DB;
1254     }
1255 
1256     bool isExists = false;
1257     int errCode = CheckTableExists(db, "meta_data", isExists);
1258     if (errCode != E_OK || !isExists) {
1259         LOGW("meta table may has not been created, err=%d, isExists=%d", errCode, isExists);
1260         return errCode;
1261     }
1262 
1263     sqlite3_stmt *statement = nullptr;
1264     std::string sql = "SELECT value FROM meta_data WHERE key=?;";
1265     errCode = GetStatement(db, sql, statement);
1266     if (errCode != E_OK) {
1267         return errCode;
1268     }
1269 
1270     Key schemakey;
1271     DBCommon::StringToVector(DBConstant::SCHEMA_KEY, schemakey);
1272     errCode = BindBlobToStatement(statement, 1, schemakey, false);
1273     if (errCode != E_OK) {
1274         ResetStatement(statement, true, errCode);
1275         return errCode;
1276     }
1277 
1278     errCode = StepWithRetry(statement); // memory db does not support schema
1279     if (errCode == MapSQLiteErrno(SQLITE_DONE)) {
1280         ResetStatement(statement, true, errCode);
1281         return -E_NOT_FOUND;
1282     } else if (errCode != MapSQLiteErrno(SQLITE_ROW)) {
1283         ResetStatement(statement, true, errCode);
1284         return errCode;
1285     }
1286 
1287     Value schemaValue;
1288     errCode = GetColumnBlobValue(statement, 0, schemaValue);
1289     if (errCode != E_OK) {
1290         ResetStatement(statement, true, errCode);
1291         return errCode;
1292     }
1293     DBCommon::VectorToString(schemaValue, strSchema);
1294     ResetStatement(statement, true, errCode);
1295     return errCode;
1296 }
1297 
IncreaseIndex(sqlite3 * db,const IndexName & name,const IndexInfo & info,SchemaType type,uint32_t skipSize)1298 int SQLiteUtils::IncreaseIndex(sqlite3 *db, const IndexName &name, const IndexInfo &info, SchemaType type,
1299     uint32_t skipSize)
1300 {
1301     if (db == nullptr) {
1302         LOGE("[IncreaseIndex] Sqlite DB not exists.");
1303         return -E_INVALID_DB;
1304     }
1305     if (name.empty() || info.empty()) {
1306         LOGE("[IncreaseIndex] Name or info can not be empty.");
1307         return -E_NOT_PERMIT;
1308     }
1309     std::string indexName = SchemaUtils::FieldPathString(name);
1310     std::string sqlCommand = "CREATE INDEX IF NOT EXISTS '" + indexName + "' ON sync_data (";
1311     for (uint32_t i = 0; i < info.size(); i++) {
1312         if (i != 0) {
1313             sqlCommand += ", ";
1314         }
1315         std::string extractSql = SchemaObject::GenerateExtractSQL(type, info[i].first, info[i].second,
1316             skipSize);
1317         if (extractSql.empty()) { // Unlikely
1318             LOGE("[IncreaseIndex] GenerateExtractSQL fail at field=%u.", i);
1319             return -E_INTERNAL_ERROR;
1320         }
1321         sqlCommand += extractSql;
1322     }
1323     sqlCommand += ") WHERE (flag&0x01=0);";
1324     return SQLiteUtils::ExecuteRawSQL(db, sqlCommand);
1325 }
1326 
ChangeIndex(sqlite3 * db,const IndexName & name,const IndexInfo & info,SchemaType type,uint32_t skipSize)1327 int SQLiteUtils::ChangeIndex(sqlite3 *db, const IndexName &name, const IndexInfo &info, SchemaType type,
1328     uint32_t skipSize)
1329 {
1330     // Currently we change index by drop it then create it, SQLite "REINDEX" may be used in the future
1331     int errCode = DecreaseIndex(db, name);
1332     if (errCode != E_OK) {
1333         LOGE("[ChangeIndex] Decrease fail=%d.", errCode);
1334         return errCode;
1335     }
1336     errCode = IncreaseIndex(db, name, info, type, skipSize);
1337     if (errCode != E_OK) {
1338         LOGE("[ChangeIndex] Increase fail=%d.", errCode);
1339         return errCode;
1340     }
1341     return E_OK;
1342 }
1343 
DecreaseIndex(sqlite3 * db,const IndexName & name)1344 int SQLiteUtils::DecreaseIndex(sqlite3 *db, const IndexName &name)
1345 {
1346     if (db == nullptr) {
1347         LOGE("[DecreaseIndex] Sqlite DB not exists.");
1348         return -E_INVALID_DB;
1349     }
1350     if (name.empty()) {
1351         LOGE("[DecreaseIndex] Name can not be empty.");
1352         return -E_NOT_PERMIT;
1353     }
1354     std::string indexName = SchemaUtils::FieldPathString(name);
1355     std::string sqlCommand = "DROP INDEX IF EXISTS '" + indexName + "';";
1356     return ExecuteRawSQL(db, sqlCommand);
1357 }
1358 
RegisterJsonFunctions(sqlite3 * db)1359 int SQLiteUtils::RegisterJsonFunctions(sqlite3 *db)
1360 {
1361     if (db == nullptr) {
1362         LOGE("Sqlite DB not exists.");
1363         return -E_INVALID_DB;
1364     }
1365     int errCode = sqlite3_create_function_v2(db, "calc_hash_key", 1, SQLITE_UTF8 | SQLITE_DETERMINISTIC,
1366         nullptr, &CalcHashKey, nullptr, nullptr, nullptr);
1367     if (errCode != SQLITE_OK) {
1368         LOGE("sqlite3_create_function_v2 about calc_hash_key returned %d", errCode);
1369         return MapSQLiteErrno(errCode);
1370     }
1371 #ifdef USING_DB_JSON_EXTRACT_AUTOMATICALLY
1372     // Specify need 3 parameter in json_extract_by_path function
1373     errCode = sqlite3_create_function_v2(db, "json_extract_by_path", 3, SQLITE_UTF8 | SQLITE_DETERMINISTIC,
1374         nullptr, &JsonExtractByPath, nullptr, nullptr, nullptr);
1375     if (errCode != SQLITE_OK) {
1376         LOGE("sqlite3_create_function_v2 about json_extract_by_path returned %d", errCode);
1377         return MapSQLiteErrno(errCode);
1378     }
1379 #endif
1380     return E_OK;
1381 }
1382 
1383 namespace {
SchemaObjectDestructor(SchemaObject * inObject)1384 void SchemaObjectDestructor(SchemaObject *inObject)
1385 {
1386     delete inObject;
1387     inObject = nullptr;
1388 }
1389 }
1390 #ifdef RELATIONAL_STORE
RegisterCalcHash(sqlite3 * db)1391 int SQLiteUtils::RegisterCalcHash(sqlite3 *db)
1392 {
1393     TransactFunc func;
1394     func.xFunc = &CalcHash;
1395     return SQLiteUtils::RegisterFunction(db, "calc_hash", 2, nullptr, func); // 2 is params count
1396 }
1397 
GetSysTime(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1398 void SQLiteUtils::GetSysTime(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1399 {
1400     if (ctx == nullptr || argc != 1 || argv == nullptr) {
1401         LOGE("Parameter does not meet restrictions.");
1402         return;
1403     }
1404 
1405     sqlite3_result_int64(ctx, (sqlite3_int64)TimeHelper::GetSysCurrentTime());
1406 }
1407 
GetRawSysTime(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1408 void SQLiteUtils::GetRawSysTime(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1409 {
1410     if (ctx == nullptr || argc != 0 || argv == nullptr) {
1411         LOGE("Parameter does not meet restrictions.");
1412         return;
1413     }
1414 
1415     uint64_t curTime = 0;
1416     int errCode = TimeHelper::GetSysCurrentRawTime(curTime);
1417     if (errCode != E_OK) {
1418         sqlite3_result_error(ctx, "get raw sys time failed in sqlite utils.", errCode);
1419         return;
1420     }
1421     sqlite3_result_int64(ctx, (sqlite3_int64)(curTime));
1422 }
1423 
GetLastTime(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1424 void SQLiteUtils::GetLastTime(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1425 {
1426     if (ctx == nullptr || argc != 0 || argv == nullptr) { // LCOV_EXCL_BR_LINE
1427         LOGE("Parameter does not meet restrictions.");
1428         return;
1429     }
1430     // Never used internally, just for sql prepare
1431     sqlite3_result_int64(ctx, (sqlite3_int64)TimeHelper::GetSysCurrentTime());
1432 }
1433 
CloudDataChangedObserver(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1434 void SQLiteUtils::CloudDataChangedObserver(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1435 {
1436     if (ctx == nullptr || argc != 4 || argv == nullptr) { // 4 is param counts
1437         return;
1438     }
1439     sqlite3_result_int64(ctx, static_cast<sqlite3_int64>(1));
1440 }
1441 
CloudDataChangedServerObserver(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1442 void SQLiteUtils::CloudDataChangedServerObserver(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1443 {
1444     if (ctx == nullptr || argc != 2 || argv == nullptr) { // 2 is param counts
1445         return;
1446     }
1447     sqlite3 *db = static_cast<sqlite3 *>(sqlite3_user_data(ctx));
1448     std::string fileName;
1449     if (!SQLiteRelationalUtils::GetDbFileName(db, fileName)) {
1450         return;
1451     }
1452     auto tableNameChar = reinterpret_cast<const char *>(sqlite3_value_text(argv[0]));
1453     if (tableNameChar == nullptr) {
1454         return;
1455     }
1456     std::string tableName = static_cast<std::string>(tableNameChar);
1457 
1458     uint64_t isTrackerChange = static_cast<uint64_t>(sqlite3_value_int(argv[1])); // 1 is param index
1459     LOGD("Cloud data changed, server observer callback %u", isTrackerChange);
1460     {
1461         std::lock_guard<std::mutex> lock(g_serverChangedDataMutex);
1462         auto itTable = g_serverChangedDataMap[fileName].find(tableName);
1463         if (itTable != g_serverChangedDataMap[fileName].end()) {
1464             itTable->second.isTrackedDataChange =
1465                 (static_cast<uint8_t>(itTable->second.isTrackedDataChange) | isTrackerChange) > 0;
1466         } else {
1467             DistributedDB::ChangeProperties properties = { .isTrackedDataChange = (isTrackerChange > 0) };
1468             g_serverChangedDataMap[fileName].insert_or_assign(tableName, properties);
1469         }
1470     }
1471     sqlite3_result_int64(ctx, static_cast<sqlite3_int64>(1));
1472 }
1473 
GetAndResetServerObserverData(const std::string & dbName,const std::string & tableName,ChangeProperties & changeProperties)1474 void SQLiteUtils::GetAndResetServerObserverData(const std::string &dbName, const std::string &tableName,
1475     ChangeProperties &changeProperties)
1476 {
1477     std::lock_guard<std::mutex> lock(g_serverChangedDataMutex);
1478     auto itDb = g_serverChangedDataMap.find(dbName);
1479     if (itDb != g_serverChangedDataMap.end() && !itDb->second.empty()) {
1480         auto itTable = itDb->second.find(tableName);
1481         if (itTable == itDb->second.end()) {
1482             return;
1483         }
1484         changeProperties = itTable->second;
1485         g_serverChangedDataMap[dbName].erase(itTable);
1486     }
1487 }
1488 
RegisterGetSysTime(sqlite3 * db)1489 int SQLiteUtils::RegisterGetSysTime(sqlite3 *db)
1490 {
1491     TransactFunc func;
1492     func.xFunc = &GetSysTime;
1493     return SQLiteUtils::RegisterFunction(db, "get_sys_time", 1, nullptr, func);
1494 }
1495 
RegisterGetLastTime(sqlite3 * db)1496 int SQLiteUtils::RegisterGetLastTime(sqlite3 *db)
1497 {
1498     TransactFunc func;
1499     func.xFunc = &GetLastTime;
1500     return SQLiteUtils::RegisterFunction(db, "get_last_time", 0, nullptr, func);
1501 }
1502 
RegisterGetRawSysTime(sqlite3 * db)1503 int SQLiteUtils::RegisterGetRawSysTime(sqlite3 *db)
1504 {
1505     TransactFunc func;
1506     func.xFunc = &GetRawSysTime;
1507     return SQLiteUtils::RegisterFunction(db, "get_raw_sys_time", 0, nullptr, func);
1508 }
1509 
RegisterCloudDataChangeObserver(sqlite3 * db)1510 int SQLiteUtils::RegisterCloudDataChangeObserver(sqlite3 *db)
1511 {
1512     TransactFunc func;
1513     func.xFunc = &CloudDataChangedObserver;
1514     return RegisterFunction(db, "client_observer", 4, db, func); // 4 is param counts
1515 }
1516 
RegisterCloudDataChangeServerObserver(sqlite3 * db)1517 int SQLiteUtils::RegisterCloudDataChangeServerObserver(sqlite3 *db)
1518 {
1519     TransactFunc func;
1520     func.xFunc = &CloudDataChangedServerObserver;
1521     return RegisterFunction(db, "server_observer", 2, db, func); // 2 is param counts
1522 }
1523 
CreateSameStuTable(sqlite3 * db,const TableInfo & baseTbl,const std::string & newTableName)1524 int SQLiteUtils::CreateSameStuTable(sqlite3 *db, const TableInfo &baseTbl, const std::string &newTableName)
1525 {
1526     std::string sql = "CREATE TABLE IF NOT EXISTS '" + newTableName + "' (";
1527     const FieldInfoMap &fields = baseTbl.GetFields();
1528     for (uint32_t cid = 0; cid < fields.size(); ++cid) {
1529         std::string fieldName = baseTbl.GetFieldName(cid);
1530         const auto &it = fields.find(fieldName);
1531         if (it == fields.end()) {
1532             return -E_INVALID_DB;
1533         }
1534         sql += "'" + fieldName + "' '" + it->second.GetDataType() + "'";
1535         if (it->second.IsNotNull()) {
1536             sql += " NOT NULL";
1537         }
1538         if (it->second.HasDefaultValue()) {
1539             sql += " DEFAULT " + it->second.GetDefaultValue();
1540         }
1541         sql += ",";
1542     }
1543     // base table has primary key
1544     if (!(baseTbl.GetPrimaryKey().size() == 1 && baseTbl.GetPrimaryKey().at(0) == "rowid")) {
1545         sql += " PRIMARY KEY (";
1546         for (const auto &it : baseTbl.GetPrimaryKey()) {
1547             sql += "'" + it.second + "',";
1548         }
1549         sql.pop_back();
1550         sql += "),";
1551     }
1552     sql.pop_back();
1553     sql += ");";
1554     int errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
1555     if (errCode != E_OK) {
1556         LOGE("[SQLite] execute create table sql failed");
1557     }
1558     return errCode;
1559 }
1560 
CloneIndexes(sqlite3 * db,const std::string & oriTableName,const std::string & newTableName)1561 int SQLiteUtils::CloneIndexes(sqlite3 *db, const std::string &oriTableName, const std::string &newTableName)
1562 {
1563     std::string sql =
1564         "SELECT 'CREATE ' || CASE WHEN il.'unique' THEN 'UNIQUE ' ELSE '' END || 'INDEX IF NOT EXISTS ' || '" +
1565             newTableName + "_' || il.name || ' ON ' || '" + newTableName +
1566             "' || '(' || GROUP_CONCAT(ii.name) || ');' "
1567         "FROM sqlite_master AS m,"
1568             "pragma_index_list(m.name) AS il,"
1569             "pragma_index_info(il.name) AS ii "
1570         "WHERE m.type='table' AND m.name='" + oriTableName + "' AND il.origin='c' "
1571         "GROUP BY il.name;";
1572     sqlite3_stmt *stmt = nullptr;
1573     int errCode = SQLiteUtils::GetStatement(db, sql, stmt);
1574     if (errCode != E_OK) {
1575         LOGE("Prepare the clone sql failed:%d", errCode);
1576         return errCode;
1577     }
1578 
1579     std::vector<std::string> indexes;
1580     while (true) {
1581         errCode = SQLiteUtils::StepWithRetry(stmt, false);
1582         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
1583             std::string indexSql;
1584             (void)GetColumnTextValue(stmt, 0, indexSql);
1585             indexes.emplace_back(indexSql);
1586             continue;
1587         }
1588         if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
1589             errCode = E_OK;
1590         }
1591         (void)ResetStatement(stmt, true, errCode);
1592         break;
1593     }
1594 
1595     if (errCode != E_OK) {
1596         return errCode;
1597     }
1598 
1599     for (const auto &it : indexes) {
1600         errCode = SQLiteUtils::ExecuteRawSQL(db, it);
1601         if (errCode != E_OK) {
1602             LOGE("[SQLite] execute clone index sql failed");
1603         }
1604     }
1605     return errCode;
1606 }
1607 
GetRelationalSchema(sqlite3 * db,std::string & schema,const std::string & key)1608 int SQLiteUtils::GetRelationalSchema(sqlite3 *db, std::string &schema, const std::string &key)
1609 {
1610     if (db == nullptr) {
1611         return -E_INVALID_DB;
1612     }
1613 
1614     sqlite3_stmt *statement = nullptr;
1615     std::string sql = "SELECT value FROM " + DBConstant::RELATIONAL_PREFIX + "metadata WHERE key=?;";
1616     int errCode = GetStatement(db, sql, statement);
1617     if (errCode != E_OK) {
1618         return errCode;
1619     }
1620 
1621     Key schemakey;
1622     DBCommon::StringToVector(key, schemakey);
1623     errCode = BindBlobToStatement(statement, 1, schemakey, false);
1624     if (errCode != E_OK) {
1625         ResetStatement(statement, true, errCode);
1626         return errCode;
1627     }
1628 
1629     errCode = StepWithRetry(statement);
1630     if (errCode == MapSQLiteErrno(SQLITE_DONE)) {
1631         ResetStatement(statement, true, errCode);
1632         return -E_NOT_FOUND;
1633     } else if (errCode != MapSQLiteErrno(SQLITE_ROW)) {
1634         ResetStatement(statement, true, errCode);
1635         return errCode;
1636     }
1637 
1638     Value schemaValue;
1639     errCode = GetColumnBlobValue(statement, 0, schemaValue);
1640     if (errCode != E_OK) {
1641         ResetStatement(statement, true, errCode);
1642         return errCode;
1643     }
1644     DBCommon::VectorToString(schemaValue, schema);
1645     ResetStatement(statement, true, errCode);
1646     return errCode;
1647 }
1648 
GetLogTableVersion(sqlite3 * db,std::string & version)1649 int SQLiteUtils::GetLogTableVersion(sqlite3 *db, std::string &version)
1650 {
1651     if (db == nullptr) {
1652         return -E_INVALID_DB;
1653     }
1654 
1655     sqlite3_stmt *statement = nullptr;
1656     std::string sql = "SELECT value FROM " + DBConstant::RELATIONAL_PREFIX + "metadata WHERE key=?;";
1657     int errCode = GetStatement(db, sql, statement);
1658     if (errCode != E_OK) {
1659         return errCode;
1660     }
1661 
1662     Key logTableKey;
1663     DBCommon::StringToVector(DBConstant::LOG_TABLE_VERSION_KEY, logTableKey);
1664     errCode = BindBlobToStatement(statement, 1, logTableKey, false);
1665     if (errCode != E_OK) {
1666         ResetStatement(statement, true, errCode);
1667         return errCode;
1668     }
1669 
1670     errCode = StepWithRetry(statement);
1671     if (errCode == MapSQLiteErrno(SQLITE_DONE)) {
1672         ResetStatement(statement, true, errCode);
1673         return -E_NOT_FOUND;
1674     } else if (errCode != MapSQLiteErrno(SQLITE_ROW)) {
1675         ResetStatement(statement, true, errCode);
1676         return errCode;
1677     }
1678 
1679     Value value;
1680     errCode = GetColumnBlobValue(statement, 0, value);
1681     if (errCode != E_OK) {
1682         ResetStatement(statement, true, errCode);
1683         return errCode;
1684     }
1685     DBCommon::VectorToString(value, version);
1686     ResetStatement(statement, true, errCode);
1687     return errCode;
1688 }
1689 
RegisterFunction(sqlite3 * db,const std::string & funcName,int nArg,void * uData,TransactFunc & func)1690 int SQLiteUtils::RegisterFunction(sqlite3 *db, const std::string &funcName, int nArg, void *uData, TransactFunc &func)
1691 {
1692     if (db == nullptr) {
1693         LOGE("Sqlite DB not exists.");
1694         return -E_INVALID_DB;
1695     }
1696 
1697     int errCode = sqlite3_create_function_v2(db, funcName.c_str(), nArg, SQLITE_UTF8 | SQLITE_DETERMINISTIC, uData,
1698         func.xFunc, func.xStep, func.xFinal, func.xDestroy);
1699     if (errCode != SQLITE_OK) {
1700         LOGE("sqlite3_create_function_v2 about [%s] returned %d", funcName.c_str(), errCode);
1701         return MapSQLiteErrno(errCode);
1702     }
1703     return E_OK;
1704 }
1705 #endif
RegisterFlatBufferFunction(sqlite3 * db,const std::string & inSchema)1706 int SQLiteUtils::RegisterFlatBufferFunction(sqlite3 *db, const std::string &inSchema)
1707 {
1708     if (db == nullptr) {
1709         LOGE("Sqlite DB not exists.");
1710         return -E_INVALID_DB;
1711     }
1712     auto heapSchemaObj = new (std::nothrow) SchemaObject;
1713     if (heapSchemaObj == nullptr) {
1714         return -E_OUT_OF_MEMORY;
1715     }
1716     int errCode = heapSchemaObj->ParseFromSchemaString(inSchema);
1717     if (errCode != E_OK) { // Unlikely, it has been parsed before
1718         delete heapSchemaObj;
1719         heapSchemaObj = nullptr;
1720         return -E_INTERNAL_ERROR;
1721     }
1722     if (heapSchemaObj->GetSchemaType() != SchemaType::FLATBUFFER) { // Do not need to register FlatBufferExtract
1723         delete heapSchemaObj;
1724         heapSchemaObj = nullptr;
1725         return E_OK;
1726     }
1727     errCode = sqlite3_create_function_v2(db, SchemaObject::GetExtractFuncName(SchemaType::FLATBUFFER).c_str(),
1728         3, SQLITE_UTF8 | SQLITE_DETERMINISTIC, heapSchemaObj, &FlatBufferExtractByPath, nullptr, nullptr, // 3 args
1729         reinterpret_cast<void(*)(void*)>(SchemaObjectDestructor));
1730     // About the release of heapSchemaObj: SQLite guarantee that at following case, sqlite will invoke the destructor
1731     // (that is SchemaObjectDestructor) we passed to it. See sqlite.org for more information.
1732     // The destructor is invoked when the function is deleted, either by being overloaded or when the database
1733     // connection closes. The destructor is also invoked if the call to sqlite3_create_function_v2() fails
1734     if (errCode != SQLITE_OK) {
1735         LOGE("sqlite3_create_function_v2 about flatbuffer_extract_by_path return=%d.", errCode);
1736         // As mentioned above, SQLite had invoked the SchemaObjectDestructor to release the heapSchemaObj
1737         return MapSQLiteErrno(errCode);
1738     }
1739     return E_OK;
1740 }
1741 
UpdateMetaDataWithinTrigger(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1742 void SQLiteUtils::UpdateMetaDataWithinTrigger(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1743 {
1744     if (ctx == nullptr || argc != 2 || argv == nullptr) { // 2 : Number of parameters for sqlite register function
1745         LOGE("[UpdateMetaDataWithinTrigger] Invalid parameter, argc=%d.", argc);
1746         return;
1747     }
1748     auto *handle = static_cast<sqlite3 *>(sqlite3_user_data(ctx));
1749     if (handle == nullptr) {
1750         sqlite3_result_error(ctx, "Sqlite context is invalid.", USING_STR_LEN);
1751         LOGE("Sqlite context is invalid.");
1752         return;
1753     }
1754     auto *keyPtr = static_cast<const uint8_t *>(sqlite3_value_blob(argv[0])); // 0 : first argv for key
1755     int keyLen = sqlite3_value_bytes(argv[0]); // 0 : first argv for key
1756     if (keyPtr == nullptr || keyLen <= 0 || keyLen > static_cast<int>(DBConstant::MAX_KEY_SIZE)) {
1757         sqlite3_result_error(ctx, "key is invalid.", USING_STR_LEN);
1758         LOGE("key is invalid.");
1759         return;
1760     }
1761     auto val = sqlite3_value_int64(argv[1]); // 1 : second argv for value
1762 
1763     sqlite3_stmt *stmt = nullptr;
1764     int errCode = SQLiteUtils::GetStatement(handle, UPDATE_META_SQL, stmt);
1765     if (errCode != E_OK) {
1766         sqlite3_result_error(ctx, "Get update meta_data statement failed.", USING_STR_LEN);
1767         LOGE("Get update meta_data statement failed. %d", errCode);
1768         return;
1769     }
1770 
1771     Key key(keyPtr, keyPtr + keyLen);
1772     errCode = SQLiteUtils::BindBlobToStatement(stmt, BIND_KEY_INDEX, key, false);
1773     if (errCode != E_OK) {
1774         sqlite3_result_error(ctx, "Bind key to statement failed.", USING_STR_LEN);
1775         LOGE("Bind key to statement failed. %d", errCode);
1776         goto END;
1777     }
1778 
1779     errCode = SQLiteUtils::BindInt64ToStatement(stmt, BIND_VAL_INDEX, val);
1780     if (errCode != E_OK) {
1781         sqlite3_result_error(ctx, "Bind value to statement failed.", USING_STR_LEN);
1782         LOGE("Bind value to statement failed. %d", errCode);
1783         goto END;
1784     }
1785 
1786     errCode = SQLiteUtils::StepWithRetry(stmt, false);
1787     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
1788         sqlite3_result_error(ctx, "Execute the update meta_data attach failed.", USING_STR_LEN);
1789         LOGE("Execute the update meta_data attach failed. %d", errCode);
1790     }
1791 END:
1792     SQLiteUtils::ResetStatement(stmt, true, errCode);
1793 }
1794 
RegisterMetaDataUpdateFunction(sqlite3 * db)1795 int SQLiteUtils::RegisterMetaDataUpdateFunction(sqlite3 *db)
1796 {
1797     int errCode = sqlite3_create_function_v2(db, DBConstant::UPDATE_META_FUNC.c_str(),
1798         2, // 2: argc for register function
1799         SQLITE_UTF8 | SQLITE_DETERMINISTIC, db, &SQLiteUtils::UpdateMetaDataWithinTrigger, nullptr, nullptr, nullptr);
1800     if (errCode != SQLITE_OK) {
1801         LOGE("sqlite3_create_function_v2 about %s returned %d", DBConstant::UPDATE_META_FUNC.c_str(), errCode);
1802     }
1803     return SQLiteUtils::MapSQLiteErrno(errCode);
1804 }
1805 
1806 struct ValueParseCache {
1807     ValueObject valueParsed;
1808     std::vector<uint8_t> valueOriginal;
1809 };
1810 
1811 namespace {
IsDeleteRecord(const uint8_t * valueBlob,int valueBlobLen)1812 inline bool IsDeleteRecord(const uint8_t *valueBlob, int valueBlobLen)
1813 {
1814     return (valueBlob == nullptr) || (valueBlobLen <= 0); // In fact, sqlite guarantee valueBlobLen not negative
1815 }
1816 
1817 // Use the same cache id as sqlite use for json_extract which is substituted by our json_extract_by_path
1818 // A negative cache-id enables sharing of cache between different operation during the same statement
1819 constexpr int VALUE_CACHE_ID = -429938;
1820 
ValueParseCacheFree(ValueParseCache * inCache)1821 void ValueParseCacheFree(ValueParseCache *inCache)
1822 {
1823     delete inCache;
1824     inCache = nullptr;
1825 }
1826 
1827 // We don't use cache array since we only cache value column of sqlite table, see sqlite implementation for compare.
ParseValueThenCacheOrGetFromCache(sqlite3_context * ctx,const uint8_t * valueBlob,uint32_t valueBlobLen,uint32_t offset)1828 const ValueObject *ParseValueThenCacheOrGetFromCache(sqlite3_context *ctx, const uint8_t *valueBlob,
1829     uint32_t valueBlobLen, uint32_t offset)
1830 {
1831     // Note: All parameter had already been check inside JsonExtractByPath, only called by JsonExtractByPath
1832     auto cached = static_cast<ValueParseCache *>(sqlite3_get_auxdata(ctx, VALUE_CACHE_ID));
1833     if (cached != nullptr) { // A previous cache exist
1834         if (cached->valueOriginal.size() == valueBlobLen) {
1835             if (std::memcmp(cached->valueOriginal.data(), valueBlob, valueBlobLen) == 0) {
1836                 // Cache match
1837                 return &(cached->valueParsed);
1838             }
1839         }
1840     }
1841     // No cache or cache mismatch
1842     auto newCache = new (std::nothrow) ValueParseCache;
1843     if (newCache == nullptr) {
1844         sqlite3_result_error(ctx, "[ParseValueCache] OOM.", USING_STR_LEN);
1845         LOGE("[ParseValueCache] OOM.");
1846         return nullptr;
1847     }
1848     int errCode = newCache->valueParsed.Parse(valueBlob, valueBlob + valueBlobLen, offset);
1849     if (errCode != E_OK) {
1850         sqlite3_result_error(ctx, "[ParseValueCache] Parse fail.", USING_STR_LEN);
1851         LOGE("[ParseValueCache] Parse fail, errCode=%d.", errCode);
1852         delete newCache;
1853         newCache = nullptr;
1854         return nullptr;
1855     }
1856     newCache->valueOriginal.assign(valueBlob, valueBlob + valueBlobLen);
1857     sqlite3_set_auxdata(ctx, VALUE_CACHE_ID, newCache, reinterpret_cast<void(*)(void*)>(ValueParseCacheFree));
1858     // If sqlite3_set_auxdata fail, it will immediately call ValueParseCacheFree to delete newCache;
1859     // Next time sqlite3_set_auxdata will call ValueParseCacheFree to delete newCache of this time;
1860     // At the end, newCache will be eventually deleted when call sqlite3_reset or sqlite3_finalize;
1861     // Since sqlite3_set_auxdata may fail, we have to call sqlite3_get_auxdata other than return newCache directly.
1862     auto cacheInAuxdata = static_cast<ValueParseCache *>(sqlite3_get_auxdata(ctx, VALUE_CACHE_ID));
1863     if (cacheInAuxdata == nullptr) {
1864         return nullptr;
1865     }
1866     return &(cacheInAuxdata->valueParsed);
1867 }
1868 }
1869 
JsonExtractByPath(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1870 void SQLiteUtils::JsonExtractByPath(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1871 {
1872     if (ctx == nullptr || argc != 3 || argv == nullptr) { // 3 parameters, which are value, path and offset
1873         LOGE("[JsonExtract] Invalid parameter, argc=%d.", argc);
1874         return;
1875     }
1876     auto valueBlob = static_cast<const uint8_t *>(sqlite3_value_blob(argv[0]));
1877     int valueBlobLen = sqlite3_value_bytes(argv[0]);
1878     if (IsDeleteRecord(valueBlob, valueBlobLen)) {
1879         // Currently delete records are filtered out of query and create-index sql, so not allowed here.
1880         sqlite3_result_error(ctx, "[JsonExtract] Delete record not allowed.", USING_STR_LEN);
1881         LOGE("[JsonExtract] Delete record not allowed.");
1882         return;
1883     }
1884     auto path = reinterpret_cast<const char *>(sqlite3_value_text(argv[1]));
1885     int offset = sqlite3_value_int(argv[2]); // index 2 is the third parameter
1886     if ((path == nullptr) || (offset < 0)) {
1887         sqlite3_result_error(ctx, "[JsonExtract] Path nullptr or offset invalid.", USING_STR_LEN);
1888         LOGE("[JsonExtract] Path nullptr or offset=%d invalid.", offset);
1889         return;
1890     }
1891     FieldPath outPath;
1892     int errCode = SchemaUtils::ParseAndCheckFieldPath(path, outPath);
1893     if (errCode != E_OK) {
1894         sqlite3_result_error(ctx, "[JsonExtract] Path illegal.", USING_STR_LEN);
1895         LOGE("[JsonExtract] Path illegal.");
1896         return;
1897     }
1898     // Parameter Check Done Here
1899     const ValueObject *valueObj = ParseValueThenCacheOrGetFromCache(ctx, valueBlob, static_cast<uint32_t>(valueBlobLen),
1900         static_cast<uint32_t>(offset));
1901     if (valueObj == nullptr) {
1902         return; // Necessary had been printed in ParseValueThenCacheOrGetFromCache
1903     }
1904     JsonExtractInnerFunc(ctx, *valueObj, outPath);
1905 }
1906 
1907 namespace {
IsExtractableType(FieldType inType)1908 inline bool IsExtractableType(FieldType inType)
1909 {
1910     return (inType != FieldType::LEAF_FIELD_NULL && inType != FieldType::LEAF_FIELD_ARRAY &&
1911         inType != FieldType::LEAF_FIELD_OBJECT && inType != FieldType::INTERNAL_FIELD_OBJECT);
1912 }
1913 }
1914 
JsonExtractInnerFunc(sqlite3_context * ctx,const ValueObject & inValue,const FieldPath & inPath)1915 void SQLiteUtils::JsonExtractInnerFunc(sqlite3_context *ctx, const ValueObject &inValue, const FieldPath &inPath)
1916 {
1917     FieldType outType = FieldType::LEAF_FIELD_NULL; // Default type null for invalid-path(path not exist)
1918     int errCode = inValue.GetFieldTypeByFieldPath(inPath, outType);
1919     if (errCode != E_OK && errCode != -E_INVALID_PATH) {
1920         sqlite3_result_error(ctx, "[JsonExtract] GetFieldType fail.", USING_STR_LEN);
1921         LOGE("[JsonExtract] GetFieldType fail, errCode=%d.", errCode);
1922         return;
1923     }
1924     FieldValue outValue;
1925     if (IsExtractableType(outType)) {
1926         errCode = inValue.GetFieldValueByFieldPath(inPath, outValue);
1927         if (errCode != E_OK) {
1928             sqlite3_result_error(ctx, "[JsonExtract] GetFieldValue fail.", USING_STR_LEN);
1929             LOGE("[JsonExtract] GetFieldValue fail, errCode=%d.", errCode);
1930             return;
1931         }
1932     }
1933     // FieldType null, array, object do not have value, all these FieldValue will be regarded as null in JsonReturn.
1934     ExtractReturn(ctx, outType, outValue);
1935 }
1936 
1937 // NOTE!!! This function is performance sensitive !!! Carefully not to allocate memory often!!!
FlatBufferExtractByPath(sqlite3_context * ctx,int argc,sqlite3_value ** argv)1938 void SQLiteUtils::FlatBufferExtractByPath(sqlite3_context *ctx, int argc, sqlite3_value **argv)
1939 {
1940     if (ctx == nullptr || argc != 3 || argv == nullptr) { // 3 parameters, which are value, path and offset
1941         LOGE("[FlatBufferExtract] Invalid parameter, argc=%d.", argc);
1942         return;
1943     }
1944     auto schema = static_cast<SchemaObject *>(sqlite3_user_data(ctx));
1945     if (schema == nullptr || !schema->IsSchemaValid() ||
1946         (schema->GetSchemaType() != SchemaType::FLATBUFFER)) { // LCOV_EXCL_BR_LINE
1947         sqlite3_result_error(ctx, "[FlatBufferExtract] No SchemaObject or invalid.", USING_STR_LEN);
1948         LOGE("[FlatBufferExtract] No SchemaObject or invalid.");
1949         return;
1950     }
1951     // Get information from argv
1952     auto valueBlob = static_cast<const uint8_t *>(sqlite3_value_blob(argv[0]));
1953     int valueBlobLen = sqlite3_value_bytes(argv[0]);
1954     if (IsDeleteRecord(valueBlob, valueBlobLen)) { // LCOV_EXCL_BR_LINE
1955         // Currently delete records are filtered out of query and create-index sql, so not allowed here.
1956         sqlite3_result_error(ctx, "[FlatBufferExtract] Delete record not allowed.", USING_STR_LEN);
1957         LOGE("[FlatBufferExtract] Delete record not allowed.");
1958         return;
1959     }
1960     auto path = reinterpret_cast<const char *>(sqlite3_value_text(argv[1]));
1961     int offset = sqlite3_value_int(argv[2]); // index 2 is the third parameter
1962     if ((path == nullptr) || (offset < 0) ||
1963         (static_cast<uint32_t>(offset) != schema->GetSkipSize())) { // LCOV_EXCL_BR_LINE
1964         sqlite3_result_error(ctx, "[FlatBufferExtract] Path null or offset invalid.", USING_STR_LEN);
1965         LOGE("[FlatBufferExtract] Path null or offset=%d(skipsize=%u) invalid.", offset, schema->GetSkipSize());
1966         return;
1967     }
1968     FlatBufferExtractInnerFunc(ctx, *schema, RawValue { valueBlob, valueBlobLen }, path);
1969 }
1970 
1971 namespace {
1972 constexpr uint32_t FLATBUFFER_MAX_CACHE_SIZE = 102400; // 100 KBytes
1973 
FlatBufferCacheFree(std::vector<uint8_t> * inCache)1974 void FlatBufferCacheFree(std::vector<uint8_t> *inCache)
1975 {
1976     delete inCache;
1977     inCache = nullptr;
1978 }
1979 }
1980 
FlatBufferExtractInnerFunc(sqlite3_context * ctx,const SchemaObject & schema,const RawValue & inValue,RawString inPath)1981 void SQLiteUtils::FlatBufferExtractInnerFunc(sqlite3_context *ctx, const SchemaObject &schema, const RawValue &inValue,
1982     RawString inPath)
1983 {
1984     // All parameter had already been check inside FlatBufferExtractByPath, only called by FlatBufferExtractByPath
1985     if (schema.GetSkipSize() % SchemaConstant::SECURE_BYTE_ALIGN == 0) { // LCOV_EXCL_BR_LINE
1986         TypeValue outExtract;
1987         int errCode = schema.ExtractValue(ValueSource::FROM_DBFILE, inPath, inValue, outExtract, nullptr);
1988         if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
1989             sqlite3_result_error(ctx, "[FlatBufferExtract] ExtractValue fail.", USING_STR_LEN);
1990             LOGE("[FlatBufferExtract] ExtractValue fail, errCode=%d.", errCode);
1991             return;
1992         }
1993         ExtractReturn(ctx, outExtract.first, outExtract.second);
1994         return;
1995     }
1996     // Not byte-align secure, we have to make a cache for copy. Check whether cache had already exist.
1997     auto cached = static_cast<std::vector<uint8_t> *>(sqlite3_get_auxdata(ctx, VALUE_CACHE_ID)); // Share the same id
1998     if (cached == nullptr) { // LCOV_EXCL_BR_LINE
1999         // Make the cache
2000         auto newCache = new (std::nothrow) std::vector<uint8_t>;
2001         if (newCache == nullptr) {
2002             sqlite3_result_error(ctx, "[FlatBufferExtract] OOM.", USING_STR_LEN);
2003             LOGE("[FlatBufferExtract] OOM.");
2004             return;
2005         }
2006         newCache->resize(FLATBUFFER_MAX_CACHE_SIZE);
2007         sqlite3_set_auxdata(ctx, VALUE_CACHE_ID, newCache, reinterpret_cast<void(*)(void*)>(FlatBufferCacheFree));
2008         // If sqlite3_set_auxdata fail, it will immediately call FlatBufferCacheFree to delete newCache;
2009         // Next time sqlite3_set_auxdata will call FlatBufferCacheFree to delete newCache of this time;
2010         // At the end, newCache will be eventually deleted when call sqlite3_reset or sqlite3_finalize;
2011         // Since sqlite3_set_auxdata may fail, we have to call sqlite3_get_auxdata other than return newCache directly.
2012         // See sqlite.org for more information.
2013         cached = static_cast<std::vector<uint8_t> *>(sqlite3_get_auxdata(ctx, VALUE_CACHE_ID));
2014     }
2015     if (cached == nullptr) { // LCOV_EXCL_BR_LINE
2016         LOGW("[FlatBufferExtract] Something wrong with Auxdata, but it is no matter without cache.");
2017     }
2018     TypeValue outExtract;
2019     int errCode = schema.ExtractValue(ValueSource::FROM_DBFILE, inPath, inValue, outExtract, cached);
2020     if (errCode != E_OK) { // LCOV_EXCL_BR_LINE
2021         sqlite3_result_error(ctx, "[FlatBufferExtract] ExtractValue fail.", USING_STR_LEN);
2022         LOGE("[FlatBufferExtract] ExtractValue fail, errCode=%d.", errCode);
2023         return;
2024     }
2025     ExtractReturn(ctx, outExtract.first, outExtract.second);
2026 }
2027 
ExtractReturn(sqlite3_context * ctx,FieldType type,const FieldValue & value)2028 void SQLiteUtils::ExtractReturn(sqlite3_context *ctx, FieldType type, const FieldValue &value)
2029 {
2030     if (ctx == nullptr) {
2031         return;
2032     }
2033     switch (type) {
2034         case FieldType::LEAF_FIELD_BOOL:
2035             sqlite3_result_int(ctx, (value.boolValue ? 1 : 0));
2036             break;
2037         case FieldType::LEAF_FIELD_INTEGER:
2038             sqlite3_result_int(ctx, value.integerValue);
2039             break;
2040         case FieldType::LEAF_FIELD_LONG:
2041             sqlite3_result_int64(ctx, value.longValue);
2042             break;
2043         case FieldType::LEAF_FIELD_DOUBLE:
2044             sqlite3_result_double(ctx, value.doubleValue);
2045             break;
2046         case FieldType::LEAF_FIELD_STRING:
2047             // The SQLITE_TRANSIENT value means that the content will likely change in the near future and
2048             // that SQLite should make its own private copy of the content before returning.
2049             sqlite3_result_text(ctx, value.stringValue.c_str(), -1, SQLITE_TRANSIENT); // -1 mean use the string length
2050             break;
2051         default:
2052             // All other type regard as null
2053             sqlite3_result_null(ctx);
2054     }
2055     return;
2056 }
2057 
CalcHashFunc(sqlite3_context * ctx,sqlite3_value ** argv)2058 static void CalcHashFunc(sqlite3_context *ctx, sqlite3_value **argv)
2059 {
2060     auto keyBlob = static_cast<const uint8_t *>(sqlite3_value_blob(argv[0]));
2061     if (keyBlob == nullptr) {
2062         sqlite3_result_error(ctx, "Parameters is invalid.", USING_STR_LEN);
2063         LOGE("Parameters is invalid.");
2064         return;
2065     }
2066     int blobLen = sqlite3_value_bytes(argv[0]);
2067     std::vector<uint8_t> value(keyBlob, keyBlob + blobLen);
2068     std::vector<uint8_t> hashValue;
2069     int errCode = DBCommon::CalcValueHash(value, hashValue);
2070     if (errCode != E_OK) {
2071         sqlite3_result_error(ctx, "Get hash value error.", USING_STR_LEN);
2072         LOGE("Get hash value error.");
2073         return;
2074     }
2075     sqlite3_result_blob(ctx, hashValue.data(), hashValue.size(), SQLITE_TRANSIENT);
2076 }
2077 
CalcHashKey(sqlite3_context * ctx,int argc,sqlite3_value ** argv)2078 void SQLiteUtils::CalcHashKey(sqlite3_context *ctx, int argc, sqlite3_value **argv)
2079 {
2080     // 1 means that the function only needs one parameter, namely key
2081     if (ctx == nullptr || argc != 1 || argv == nullptr) {
2082         LOGE("Parameter does not meet restrictions.");
2083         return;
2084     }
2085     CalcHashFunc(ctx, argv);
2086 }
2087 
CalcHash(sqlite3_context * ctx,int argc,sqlite3_value ** argv)2088 void SQLiteUtils::CalcHash(sqlite3_context *ctx, int argc, sqlite3_value **argv)
2089 {
2090     if (ctx == nullptr || argc != 2 || argv == nullptr) { // 2 is params count
2091         LOGE("Parameter does not meet restrictions.");
2092         return;
2093     }
2094     CalcHashFunc(ctx, argv);
2095 }
2096 
2097 
GetDbSize(const std::string & dir,const std::string & dbName,uint64_t & size)2098 int SQLiteUtils::GetDbSize(const std::string &dir, const std::string &dbName, uint64_t &size)
2099 {
2100     std::string dataDir = dir + "/" + dbName + DBConstant::DB_EXTENSION;
2101     uint64_t localDbSize = 0;
2102     int errCode = OS::CalFileSize(dataDir, localDbSize);
2103     if (errCode != E_OK) {
2104         LOGD("Failed to get the db file size, errCode:%d", errCode);
2105         return errCode;
2106     }
2107 
2108     std::string shmFileName = dataDir + "-shm";
2109     uint64_t localshmFileSize = 0;
2110     errCode = OS::CalFileSize(shmFileName, localshmFileSize);
2111     if (errCode != E_OK) {
2112         localshmFileSize = 0;
2113     }
2114 
2115     std::string walFileName = dataDir + "-wal";
2116     uint64_t localWalFileSize = 0;
2117     errCode = OS::CalFileSize(walFileName, localWalFileSize);
2118     if (errCode != E_OK) {
2119         localWalFileSize = 0;
2120     }
2121 
2122     // 64-bit system is Suffice. Computer storage is less than uint64_t max
2123     size += (localDbSize + localshmFileSize + localWalFileSize);
2124     return E_OK;
2125 }
2126 
SetDataBaseProperty(sqlite3 * db,const OpenDbProperties & properties,bool setWal,const std::vector<std::string> & sqls)2127 int SQLiteUtils::SetDataBaseProperty(sqlite3 *db, const OpenDbProperties &properties, bool setWal,
2128     const std::vector<std::string> &sqls)
2129 {
2130     // Set the default busy handler to retry automatically before returning SQLITE_BUSY.
2131     int errCode = SetBusyTimeout(db, BUSY_TIMEOUT_MS);
2132     if (errCode != E_OK) {
2133         return errCode;
2134     }
2135     if (!properties.isMemDb) {
2136         errCode = SQLiteUtils::SetKey(db, properties.cipherType, properties.passwd, setWal,
2137             properties.iterTimes);
2138         if (errCode != E_OK) {
2139             LOGD("SQLiteUtils::SetKey fail!!![%d]", errCode);
2140             return errCode;
2141         }
2142     }
2143 
2144     for (const auto &sql : sqls) {
2145         errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
2146         if (errCode != E_OK) {
2147             LOGE("[SQLite] execute sql failed: %d", errCode);
2148             return errCode;
2149         }
2150     }
2151     // Create table if not exist according the sqls.
2152     if (properties.createIfNecessary) {
2153         for (const auto &sql : properties.sqls) {
2154             errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
2155             if (errCode != E_OK) {
2156                 LOGE("[SQLite] execute preset sqls failed");
2157                 return errCode;
2158             }
2159         }
2160     }
2161     return E_OK;
2162 }
2163 
2164 #ifndef OMIT_ENCRYPT
SetCipherSettings(sqlite3 * db,CipherType type,uint32_t iterTimes)2165 int SQLiteUtils::SetCipherSettings(sqlite3 *db, CipherType type, uint32_t iterTimes)
2166 {
2167     if (db == nullptr) {
2168         return -E_INVALID_DB;
2169     }
2170     std::string cipherName = GetCipherName(type);
2171     if (cipherName.empty()) {
2172         return -E_INVALID_ARGS;
2173     }
2174     std::string cipherConfig = CIPHER_CONFIG_SQL + cipherName + ";";
2175     int errCode = SQLiteUtils::ExecuteRawSQL(db, cipherConfig);
2176     if (errCode != E_OK) {
2177         LOGE("[SQLiteUtils][SetCipherSettings] config cipher failed:%d", errCode);
2178         return errCode;
2179     }
2180     errCode = SQLiteUtils::ExecuteRawSQL(db, KDF_ITER_CONFIG_SQL + std::to_string(iterTimes));
2181     if (errCode != E_OK) {
2182         LOGE("[SQLiteUtils][SetCipherSettings] config iter failed:%d", errCode);
2183     }
2184     return errCode;
2185 }
2186 
GetCipherName(CipherType type)2187 std::string SQLiteUtils::GetCipherName(CipherType type)
2188 {
2189     if (type == CipherType::AES_256_GCM || type == CipherType::DEFAULT) {
2190         return "'aes-256-gcm'";
2191     }
2192     return "";
2193 }
2194 #endif
2195 
DropTriggerByName(sqlite3 * db,const std::string & name)2196 int SQLiteUtils::DropTriggerByName(sqlite3 *db, const std::string &name)
2197 {
2198     const std::string dropTriggerSql = "DROP TRIGGER " + name + ";";
2199     int errCode = SQLiteUtils::ExecuteRawSQL(db, dropTriggerSql);
2200     if (errCode != E_OK) {
2201         LOGE("Remove trigger failed. %d", errCode);
2202     }
2203     return errCode;
2204 }
2205 
ExpandedSql(sqlite3_stmt * stmt,std::string & basicString)2206 int SQLiteUtils::ExpandedSql(sqlite3_stmt *stmt, std::string &basicString)
2207 {
2208     if (stmt == nullptr) {
2209         return -E_INVALID_ARGS;
2210     }
2211     char *eSql = sqlite3_expanded_sql(stmt);
2212     if (eSql == nullptr) {
2213         LOGE("expand statement to sql failed.");
2214         return -E_INVALID_DATA;
2215     }
2216     basicString = std::string(eSql);
2217     sqlite3_free(eSql);
2218     return E_OK;
2219 }
2220 
ExecuteCheckPoint(sqlite3 * db)2221 void SQLiteUtils::ExecuteCheckPoint(sqlite3 *db)
2222 {
2223     if (db == nullptr) {
2224         return;
2225     }
2226 
2227     int chkResult = sqlite3_wal_checkpoint_v2(db, nullptr, SQLITE_CHECKPOINT_TRUNCATE, nullptr, nullptr);
2228     LOGI("SQLite checkpoint result:%d", chkResult);
2229 }
2230 
CheckTableEmpty(sqlite3 * db,const std::string & tableName,bool & isEmpty)2231 int SQLiteUtils::CheckTableEmpty(sqlite3 *db, const std::string &tableName, bool &isEmpty)
2232 {
2233     if (db == nullptr) {
2234         return -E_INVALID_ARGS;
2235     }
2236 
2237     std::string cntSql = "SELECT min(rowid) FROM '" + tableName + "';";
2238     sqlite3_stmt *stmt = nullptr;
2239     int errCode = SQLiteUtils::GetStatement(db, cntSql, stmt);
2240     if (errCode != E_OK) {
2241         return errCode;
2242     }
2243 
2244     errCode = SQLiteUtils::StepWithRetry(stmt, false);
2245     if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
2246         isEmpty = (sqlite3_column_type(stmt, 0) == SQLITE_NULL);
2247         errCode = E_OK;
2248     }
2249 
2250     SQLiteUtils::ResetStatement(stmt, true, errCode);
2251     return SQLiteUtils::MapSQLiteErrno(errCode);
2252 }
2253 
SetPersistWalMode(sqlite3 * db)2254 int SQLiteUtils::SetPersistWalMode(sqlite3 *db)
2255 {
2256     if (db == nullptr) {
2257         return -E_INVALID_ARGS;
2258     }
2259     int opCode = 1;
2260     int errCode = sqlite3_file_control(db, "main", SQLITE_FCNTL_PERSIST_WAL, &opCode);
2261     if (errCode != SQLITE_OK) {
2262         LOGE("Set persist wal mode failed. %d", errCode);
2263     }
2264     return SQLiteUtils::MapSQLiteErrno(errCode);
2265 }
2266 
GetLastRowId(sqlite3 * db)2267 int64_t SQLiteUtils::GetLastRowId(sqlite3 *db)
2268 {
2269     if (db == nullptr) {
2270         return -1;
2271     }
2272     return sqlite3_last_insert_rowid(db);
2273 }
2274 
GetLastErrorMsg()2275 std::string SQLiteUtils::GetLastErrorMsg()
2276 {
2277     std::lock_guard<std::mutex> autoLock(logMutex_);
2278     return lastErrorMsg_;
2279 }
2280 
SetAuthorizer(sqlite3 * db,int (* xAuth)(void *,int,const char *,const char *,const char *,const char *))2281 int SQLiteUtils::SetAuthorizer(sqlite3 *db,
2282     int (*xAuth)(void*, int, const char*, const char*, const char*, const char*))
2283 {
2284     return SQLiteUtils::MapSQLiteErrno(sqlite3_set_authorizer(db, xAuth, nullptr));
2285 }
2286 
GetSelectCols(sqlite3_stmt * stmt,std::vector<std::string> & colNames)2287 void SQLiteUtils::GetSelectCols(sqlite3_stmt *stmt, std::vector<std::string> &colNames)
2288 {
2289     colNames.clear();
2290     for (int i = 0; i < sqlite3_column_count(stmt); ++i) {
2291         const char *name = sqlite3_column_name(stmt, i);
2292         colNames.emplace_back(name == nullptr ? std::string() : std::string(name));
2293     }
2294 }
2295 
SetKeyInner(sqlite3 * db,CipherType type,const CipherPassword & passwd,uint32_t iterTimes)2296 int SQLiteUtils::SetKeyInner(sqlite3 *db, CipherType type, const CipherPassword &passwd, uint32_t iterTimes)
2297 {
2298 #ifndef OMIT_ENCRYPT
2299     int errCode = sqlite3_key(db, static_cast<const void *>(passwd.GetData()), static_cast<int>(passwd.GetSize()));
2300     if (errCode != SQLITE_OK) {
2301         LOGE("[SQLiteUtils][SetKeyInner] config key failed:(%d)", errCode);
2302         return SQLiteUtils::MapSQLiteErrno(errCode);
2303     }
2304 
2305     errCode = SQLiteUtils::SetCipherSettings(db, type, iterTimes);
2306     if (errCode != E_OK) {
2307         LOGE("[SQLiteUtils][SetKeyInner] set cipher settings failed:%d", errCode);
2308     }
2309     return errCode;
2310 #else
2311     return -E_NOT_SUPPORT;
2312 #endif
2313 }
2314 
BindDataValueByType(sqlite3_stmt * statement,const std::optional<DataValue> & data,int cid)2315 int SQLiteUtils::BindDataValueByType(sqlite3_stmt *statement, const std::optional<DataValue> &data, int cid)
2316 {
2317     int errCode = E_OK;
2318     StorageType type = data.value_or(DataValue()).GetType();
2319     switch (type) {
2320         case StorageType::STORAGE_TYPE_INTEGER: {
2321             int64_t intData = 0;
2322             (void)data.value().GetInt64(intData);
2323             errCode = SQLiteUtils::MapSQLiteErrno(sqlite3_bind_int64(statement, cid, intData));
2324             break;
2325         }
2326 
2327         case StorageType::STORAGE_TYPE_REAL: {
2328             double doubleData = 0;
2329             (void)data.value().GetDouble(doubleData);
2330             errCode = SQLiteUtils::MapSQLiteErrno(sqlite3_bind_double(statement, cid, doubleData));
2331             break;
2332         }
2333 
2334         case StorageType::STORAGE_TYPE_TEXT: {
2335             std::string strData;
2336             (void)data.value().GetText(strData);
2337             errCode = SQLiteUtils::BindTextToStatement(statement, cid, strData);
2338             break;
2339         }
2340 
2341         case StorageType::STORAGE_TYPE_BLOB: {
2342             Blob blob;
2343             (void)data.value().GetBlob(blob);
2344             std::vector<uint8_t> blobData(blob.GetData(), blob.GetData() + blob.GetSize());
2345             errCode = SQLiteUtils::BindBlobToStatement(statement, cid, blobData, true);
2346             break;
2347         }
2348 
2349         case StorageType::STORAGE_TYPE_NULL: {
2350             errCode = SQLiteUtils::MapSQLiteErrno(sqlite3_bind_null(statement, cid));
2351             break;
2352         }
2353 
2354         default:
2355             break;
2356     }
2357     return errCode;
2358 }
2359 
UpdateCipherShaAlgo(sqlite3 * db,bool setWal,CipherType type,const CipherPassword & passwd,uint32_t iterTimes)2360 int SQLiteUtils::UpdateCipherShaAlgo(sqlite3 *db, bool setWal, CipherType type, const CipherPassword &passwd,
2361     uint32_t iterTimes)
2362 {
2363     if (passwd.GetSize() != 0) {
2364         int errCode = SetKeyInner(db, type, passwd, iterTimes);
2365         if (errCode != E_OK) {
2366             return errCode;
2367         }
2368         // set sha1 algo for old version
2369         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA1_ALGO_SQL);
2370         if (errCode != E_OK) {
2371             LOGE("[SQLiteUtils][UpdateCipherShaAlgo] set sha algo failed:%d", errCode);
2372             return errCode;
2373         }
2374         // try to get user version
2375         errCode = SQLiteUtils::ExecuteRawSQL(db, USER_VERSION_SQL);
2376         if (errCode != E_OK) {
2377             LOGE("[SQLiteUtils][UpdateCipherShaAlgo] verify version failed:%d", errCode);
2378             if (errno == EKEYREVOKED) {
2379                 return -E_EKEYREVOKED;
2380             }
2381             if (errCode == -E_BUSY) {
2382                 return errCode;
2383             }
2384             return -E_INVALID_PASSWD_OR_CORRUPTED_DB;
2385         }
2386         // try to update rekey sha algo by rekey operation
2387         errCode = SQLiteUtils::ExecuteRawSQL(db, SHA256_ALGO_REKEY_SQL);
2388         if (errCode != E_OK) {
2389             LOGE("[SQLiteUtils][UpdateCipherShaAlgo] set rekey sha algo failed:%d", errCode);
2390             return errCode;
2391         }
2392         if (setWal) {
2393             errCode = SQLiteUtils::ExecuteRawSQL(db, WAL_MODE_SQL);
2394             if (errCode != E_OK) {
2395                 LOGE("[SQLite][UpdateCipherShaAlgo] execute wal sql failed: %d", errCode);
2396                 return errCode;
2397             }
2398         }
2399         return Rekey(db, passwd);
2400     }
2401     return -E_INVALID_PASSWD_OR_CORRUPTED_DB;
2402 }
2403 
CheckTableExists(sqlite3 * db,const std::string & tableName,bool & isCreated,bool isCheckMeta)2404 int SQLiteUtils::CheckTableExists(sqlite3 *db, const std::string &tableName, bool &isCreated, bool isCheckMeta)
2405 {
2406     if (db == nullptr) {
2407         return -1;
2408     }
2409 
2410     sqlite3_stmt *stmt = nullptr;
2411     int errCode = SQLiteUtils::GetStatement(db, isCheckMeta ? CHECK_META_DB_TABLE_CREATED : CHECK_TABLE_CREATED, stmt);
2412     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_OK)) {
2413         LOGE("Get check table statement failed. err=%d", errCode);
2414         return errCode;
2415     }
2416 
2417     errCode = SQLiteUtils::BindTextToStatement(stmt, 1, tableName);
2418     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_OK)) {
2419         LOGE("Bind table name to statement failed. err=%d", errCode);
2420         goto END;
2421     }
2422 
2423     errCode = SQLiteUtils::StepWithRetry(stmt);
2424     if (errCode != SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
2425         LOGE("Check table exists failed. err=%d", errCode); // should always return a row data
2426         goto END;
2427     }
2428     errCode = E_OK;
2429     isCreated = (sqlite3_column_int(stmt, 0) == 1);
2430 END:
2431     SQLiteUtils::ResetStatement(stmt, true, errCode);
2432     return errCode;
2433 }
2434 
StepNext(sqlite3_stmt * stmt,bool isMemDb)2435 int SQLiteUtils::StepNext(sqlite3_stmt *stmt, bool isMemDb)
2436 {
2437     if (stmt == nullptr) {
2438         return -E_INVALID_ARGS;
2439     }
2440     int errCode = SQLiteUtils::StepWithRetry(stmt, isMemDb);
2441     if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_DONE)) {
2442         errCode = -E_FINISHED;
2443     } else if (errCode == SQLiteUtils::MapSQLiteErrno(SQLITE_ROW)) {
2444         errCode = E_OK;
2445     }
2446     return errCode;
2447 }
2448 } // namespace DistributedDB
2449