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