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