1 /*
2 * Copyright (c) 2022 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 "db_common.h"
17 #include "sqlite_log_table_manager.h"
18
19 namespace DistributedDB {
20 namespace {
21 const unsigned int MAX_FIELD_NUM_IN_ONE_STATEMENT = 100u;
22 }
23
AddRelationalLogTableTrigger(sqlite3 * db,const TableInfo & table,const std::string & identity)24 int SqliteLogTableManager::AddRelationalLogTableTrigger(sqlite3 *db, const TableInfo &table,
25 const std::string &identity)
26 {
27 std::vector<std::string> sqls = GetDropTriggers(table);
28 std::string insertTrigger = GetInsertTrigger(table, identity);
29 if (!insertTrigger.empty()) {
30 sqls.emplace_back(insertTrigger);
31 }
32 std::string updateTrigger = GetUpdateTrigger(table, identity);
33 if (!updateTrigger.empty()) {
34 sqls.emplace_back(updateTrigger);
35 }
36 std::string deleteTrigger = GetDeleteTrigger(table, identity);
37 if (!deleteTrigger.empty()) {
38 sqls.emplace_back(deleteTrigger);
39 }
40 std::string updatePkTrigger = GetUpdatePkTrigger(table, identity);
41 if (!updatePkTrigger.empty()) {
42 sqls.emplace_back(updatePkTrigger);
43 }
44 // add insert,update,delete,update pk trigger
45 for (const auto &sql : sqls) {
46 int errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
47 if (errCode != E_OK) {
48 LOGE("[LogTableManager] execute create log trigger sql failed, errCode=%d", errCode);
49 return errCode;
50 }
51 }
52 return E_OK;
53 }
54
GetCreateRelationalLogTableSql(const TableInfo & table,const std::string & extendStr)55 std::string SqliteLogTableManager::GetCreateRelationalLogTableSql(const TableInfo &table, const std::string &extendStr)
56 {
57 const std::string tableName = GetLogTableName(table) + extendStr;
58 std::string primaryKey = GetPrimaryKeySql(table);
59
60 std::string createTableSql = "CREATE TABLE IF NOT EXISTS " + tableName + "(" \
61 "data_key INT NOT NULL," \
62 "device BLOB," \
63 "ori_device BLOB," \
64 "timestamp INT NOT NULL," \
65 "wtimestamp INT NOT NULL," \
66 "flag INT NOT NULL," \
67 "hash_key BLOB NOT NULL," \
68 "cloud_gid TEXT," + \
69 "extend_field BLOB," + \
70 "cursor INT DEFAULT 0," + \
71 "version TEXT DEFAULT ''," + \
72 "sharing_resource TEXT DEFAULT ''," + \
73 "status INT DEFAULT 0," + \
74 primaryKey + ");";
75 return createTableSql;
76 }
77
CreateRelationalLogTable(sqlite3 * db,const TableInfo & table)78 int SqliteLogTableManager::CreateRelationalLogTable(sqlite3 *db, const TableInfo &table)
79 {
80 std::string createTableSql = GetCreateRelationalLogTableSql(table);
81 std::vector<std::string> logTableSchema;
82 logTableSchema.emplace_back(createTableSql);
83 GetIndexSql(table, logTableSchema);
84
85 for (const auto &sql : logTableSchema) {
86 int errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
87 if (errCode != E_OK) {
88 LOGE("[LogTableManager] execute create log table schema failed, errCode=%d", errCode);
89 return errCode;
90 }
91 }
92 return E_OK;
93 }
94
CreateKvSyncLogTable(sqlite3 * db)95 int SqliteLogTableManager::CreateKvSyncLogTable(sqlite3 *db)
96 {
97 const std::string tableName = "naturalbase_kv_aux_sync_data_log";
98 const std::string primaryKey = "PRIMARY KEY(userid, hash_key)";
99 std::string createTableSql = "CREATE TABLE IF NOT EXISTS " + tableName + "(" \
100 "userid TEXT NOT NULL," + \
101 "hash_key BLOB NOT NULL," + \
102 "cloud_gid TEXT," + \
103 "version TEXT," + \
104 "cloud_flag INT DEFAULT 0," + \
105 primaryKey + ");";
106 int errCode = SQLiteUtils::ExecuteRawSQL(db, createTableSql);
107 if (errCode != E_OK) {
108 LOGE("[LogTableManager] execute create cloud log table schema failed, errCode=%d", errCode);
109 return errCode;
110 }
111 std::string createIndexSql = "CREATE INDEX IF NOT EXISTS gid_hash_key ON " + tableName + "(cloud_gid, hash_key)";
112 errCode = SQLiteUtils::ExecuteRawSQL(db, createIndexSql);
113 if (errCode != E_OK) {
114 LOGE("[LogTableManager] execute create gid index failed, errCode=%d", errCode);
115 }
116 return UpgradeKvSyncLogTable(tableName, db);
117 }
118
GetIndexSql(const TableInfo & table,std::vector<std::string> & schema)119 void SqliteLogTableManager::GetIndexSql(const TableInfo &table, std::vector<std::string> &schema)
120 {
121 const std::string tableName = GetLogTableName(table);
122
123 std::string indexTimestampFlag = "CREATE INDEX IF NOT EXISTS " +
124 tableName + "_time_flag_index ON " + tableName + "(timestamp, flag);";
125 schema.emplace_back(indexTimestampFlag);
126
127 std::string indexHashkey = "CREATE INDEX IF NOT EXISTS " +
128 tableName + "_hashkey_index ON " + tableName + "(hash_key);";
129 schema.emplace_back(indexHashkey);
130 }
131
GetLogTableName(const TableInfo & table) const132 std::string SqliteLogTableManager::GetLogTableName(const TableInfo &table) const
133 {
134 return DBConstant::RELATIONAL_PREFIX + table.GetTableName() + "_log";
135 }
136
UpgradeKvSyncLogTable(const std::string & tableName,sqlite3 * db)137 int SqliteLogTableManager::UpgradeKvSyncLogTable(const std::string &tableName, sqlite3 *db)
138 {
139 TableInfo tableInfo;
140 int errCode = SQLiteUtils::AnalysisSchemaFieldDefine(db, tableName, tableInfo);
141 if (errCode != E_OK) {
142 return errCode;
143 }
144 auto fields = tableInfo.GetFields();
145 if (fields.find("cloud_flag") != fields.end()) {
146 return CreateKvCloudFlagIndex(tableName, db);
147 }
148 std::string addFlagSql = "ALTER TABLE " + tableName + " ADD COLUMN cloud_flag INT DEFAULT 0";
149 errCode = SQLiteUtils::ExecuteRawSQL(db, addFlagSql);
150 if (errCode != E_OK) {
151 LOGE("[LogTableManager] add cloud_flag failed, errCode=%d", errCode);
152 return errCode;
153 }
154 return CreateKvCloudFlagIndex(tableName, db);
155 }
156
CreateKvCloudFlagIndex(const std::string & tableName,sqlite3 * db)157 int SqliteLogTableManager::CreateKvCloudFlagIndex(const std::string &tableName, sqlite3 *db)
158 {
159 std::string createIndexSql = "CREATE INDEX IF NOT EXISTS gid_hash_key_flag ON " + tableName +
160 "(cloud_gid, hash_key, cloud_flag)";
161 int errCode = SQLiteUtils::ExecuteRawSQL(db, createIndexSql);
162 if (errCode != E_OK) {
163 LOGE("[LogTableManager] add cloud_flag index failed, errCode=%d", errCode);
164 }
165 return errCode;
166 }
167
GetUpdatePkTrigger(const TableInfo & table,const std::string & identity)168 std::string SqliteLogTableManager::GetUpdatePkTrigger([[gnu::unused]] const TableInfo &table,
169 [[gnu::unused]] const std::string &identity)
170 {
171 return "";
172 }
173
GetUpdateTimestamp(const TableInfo & table,bool defaultNewTime)174 std::string SqliteLogTableManager::GetUpdateTimestamp(const TableInfo &table, bool defaultNewTime)
175 {
176 return GetUpdateWithAssignSql(table, "get_sys_time(0)", "get_sys_time(0)",
177 defaultNewTime ? "get_sys_time(0)" : "timestamp");
178 }
179
GetUpdateSqlWhenFieldExceedsLimit(const std::vector<std::string> & syncFields,const std::string & matchValue,const std::string & missMatchValue)180 std::string SqliteLogTableManager::GetUpdateSqlWhenFieldExceedsLimit(const std::vector<std::string> &syncFields,
181 const std::string &matchValue, const std::string &missMatchValue)
182 {
183 std::string sql = " CASE";
184 size_t index = 0;
185 size_t maxNum = syncFields.size();
186 while (index < maxNum) {
187 sql.append(" WHEN (");
188 size_t beginIndex = index;
189 for (size_t i = beginIndex; i < (beginIndex + MAX_FIELD_NUM_IN_ONE_STATEMENT) && i < maxNum; i++) {
190 auto field = syncFields[i];
191 sql.append("(").append("OLD.'").append(field).append("'!= NEW.'").append(field).append("') OR");
192 index++;
193 }
194 // pop last "OR"
195 sql.pop_back();
196 sql.pop_back();
197 sql.append(") THEN ").append(matchValue);
198 }
199 sql.append(" ELSE ").append(missMatchValue).append(" END");
200 return sql;
201 }
202
GetUpdateWithAssignSql(const TableInfo & table,const std::string & emptyValue,const std::string & matchValue,const std::string & missMatchValue)203 std::string SqliteLogTableManager::GetUpdateWithAssignSql(const TableInfo &table, const std::string &emptyValue,
204 const std::string &matchValue, const std::string &missMatchValue)
205 {
206 auto syncFields = table.GetSyncField();
207 if (syncFields.empty() || table.GetFields().size() <= syncFields.size()) {
208 return emptyValue;
209 }
210 if (syncFields.size() > MAX_FIELD_NUM_IN_ONE_STATEMENT) {
211 return GetUpdateSqlWhenFieldExceedsLimit(syncFields, matchValue, missMatchValue);
212 }
213 std::string sql = " CASE WHEN (";
214 for (const auto &field : syncFields) {
215 sql.append("(").append("OLD.'").append(field).append("'!= NEW.'").append(field).append("') OR");
216 }
217 // pop last OR
218 sql.pop_back();
219 sql.pop_back();
220 sql.append(") THEN ").append(matchValue).append(" ELSE ").append(missMatchValue).append(" END");
221 return sql;
222 }
223
CheckTriggerExist(sqlite3 * db,const TableInfo & table,const std::string & triggerType,bool & exist)224 int CheckTriggerExist(sqlite3 *db, const TableInfo &table, const std::string &triggerType, bool &exist)
225 {
226 std::string checkSql = "select count(*) from sqlite_master where type = 'trigger' and tbl_name = '" +
227 table.GetTableName() + "' and name = 'naturalbase_rdb_" + table.GetTableName() + "_ON_" + triggerType + "';";
228 int count = 0;
229 int errCode = SQLiteUtils::GetCountBySql(db, checkSql, count);
230 if (errCode != E_OK) {
231 LOGW("query trigger from db fail, errCode=%d", errCode);
232 return errCode;
233 }
234 exist = count != 0;
235 return E_OK;
236 }
237
CheckAndCreateTrigger(sqlite3 * db,const TableInfo & table,const std::string & identity)238 void SqliteLogTableManager::CheckAndCreateTrigger(sqlite3 *db, const TableInfo &table, const std::string &identity)
239 {
240 std::vector<std::string> sqls;
241 bool insertTriggerExist = false;
242 const std::string &tableName = table.GetTableName();
243 if (CheckTriggerExist(db, table, "INSERT", insertTriggerExist) == E_OK && !insertTriggerExist) {
244 LOGW("[%s [%zu]] Insert trigger does not exist, will be recreated",
245 DBCommon::StringMiddleMasking(tableName).c_str(), tableName.size());
246 std::string insertTriggerSql = GetInsertTrigger(table, identity);
247 if (!insertTriggerSql.empty()) {
248 sqls.emplace_back(insertTriggerSql);
249 }
250 }
251
252 bool updateTriggerExist = false;
253 if (CheckTriggerExist(db, table, "UPDATE", updateTriggerExist) == E_OK && !updateTriggerExist) {
254 LOGW("[%s [%zu]] Update trigger does not exist, will be recreated",
255 DBCommon::StringMiddleMasking(tableName).c_str(), tableName.size());
256 std::string updateTriggerSql = GetUpdateTrigger(table, identity);
257 if (!updateTriggerSql.empty()) {
258 sqls.emplace_back(updateTriggerSql);
259 }
260 }
261
262 bool deleteTriggerExist = false;
263 if (CheckTriggerExist(db, table, "DELETE", deleteTriggerExist) == E_OK && !deleteTriggerExist) {
264 LOGW("[%s [%zu]] Delete trigger does not exist, will be recreated",
265 DBCommon::StringMiddleMasking(tableName).c_str(), tableName.size());
266 std::string deleteTriggerSql = GetDeleteTrigger(table, identity);
267 if (!deleteTriggerSql.empty()) {
268 sqls.emplace_back(deleteTriggerSql);
269 }
270 }
271
272 for (const auto &sql : sqls) {
273 int errCode = SQLiteUtils::ExecuteRawSQL(db, sql);
274 if (errCode != E_OK) {
275 LOGW("[%s [%zu]] Failed to recreate trigger, errCode=%d", DBCommon::StringMiddleMasking(tableName).c_str(),
276 tableName.size(), errCode);
277 }
278 }
279 }
280
CalcPkHash(const std::string & references,const std::vector<std::string> & pk)281 std::string SqliteLogTableManager::CalcPkHash(const std::string &references, const std::vector<std::string> &pk)
282 {
283 std::string sql;
284 if (pk.size() == 1u) {
285 sql = "calc_hash(" + references + "'" + pk.at(0) + "', 0)";
286 } else {
287 sql = "calc_hash(";
288 for (const auto &it : pk) {
289 sql += "calc_hash(" + references + "'" + it + "', 0)||";
290 }
291 sql.pop_back();
292 sql.pop_back();
293 sql += ", 0)";
294 }
295 return sql;
296 }
297
GetConflictPkSql(const TableInfo & table)298 std::string SqliteLogTableManager::GetConflictPkSql(const TableInfo &table)
299 {
300 return "ON CONFLICT(hash_key)";
301 }
302 }