1 /*
2  * Copyright (c) 2023 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 "cloud_sync_log_table_manager.h"
17 #include "cloud/cloud_db_constant.h"
18 #include "cloud/cloud_storage_utils.h"
19 #include "db_common.h"
20 
21 namespace DistributedDB {
CalcPrimaryKeyHash(const std::string & references,const TableInfo & table,const std::string & identity)22 std::string CloudSyncLogTableManager::CalcPrimaryKeyHash(const std::string &references, const TableInfo &table,
23     const std::string &identity)
24 {
25     (void)identity;
26     std::string sql;
27     FieldInfoMap fieldInfos = table.GetFields();
28     if (table.GetPrimaryKey().size() == 1) {
29         std::string pkName = table.GetPrimaryKey().at(0);
30         if (pkName == "rowid") {
31             std::string collateStr = std::to_string(static_cast<uint32_t>(CollateType::COLLATE_NONE));
32             // we use _rowid_ to reference sqlite inner rowid to avoid rowid is a user defined column,
33             // user can't create distributed table with column named "_rowid_"
34             sql = "calc_hash(" + references + "'" + std::string(DBConstant::SQLITE_INNER_ROWID) + "', " +
35                 collateStr + ")";
36         } else {
37             if (fieldInfos.find(pkName) == fieldInfos.end()) {
38                 return sql;
39             }
40             std::string collateStr = std::to_string(static_cast<uint32_t>(fieldInfos.at(pkName).GetCollateType()));
41             sql = "calc_hash(" + references + "'" + pkName  + "', " + collateStr + ")";
42         }
43     }  else {
44         std::set<std::string> primaryKeySet; // we need sort primary key by upper name
45         for (const auto &it : table.GetPrimaryKey()) {
46             primaryKeySet.emplace(DBCommon::ToUpperCase(it.second));
47         }
48         sql = "calc_hash(";
49         for (const auto &it : primaryKeySet) {
50             if (fieldInfos.find(it) == fieldInfos.end()) {
51                 return sql;
52             }
53             std::string collateStr = std::to_string(static_cast<uint32_t>(fieldInfos.at(it).GetCollateType()));
54             sql += "calc_hash(" + references + "'" + it + "', " + collateStr + ")||";
55         }
56         sql.pop_back();
57         sql.pop_back();
58         sql += ", 0)";
59     }
60     return sql;
61 }
62 
GetIndexSql(const TableInfo & table,std::vector<std::string> & schema)63 void CloudSyncLogTableManager::GetIndexSql(const TableInfo &table, std::vector<std::string> &schema)
64 {
65     const std::string tableName = GetLogTableName(table);
66 
67     std::string indexTimestampFlagGid = "CREATE INDEX IF NOT EXISTS " + tableName +
68         "_cloud_time_flag_gid_index ON " + tableName + "(timestamp, flag, cloud_gid);";
69     std::string indexGid = "CREATE INDEX IF NOT EXISTS " + tableName +
70         "_cloud_gid_index ON " + tableName + "(cloud_gid);";
71     std::string indexDataKey = "CREATE INDEX IF NOT EXISTS " + tableName +
72         "_data_key_index ON " + tableName + "(data_key);";
73     std::string indexCursor = "CREATE INDEX IF NOT EXISTS " + tableName +
74         "_cursor_index ON " + tableName + "(cursor);";
75     schema.emplace_back(indexTimestampFlagGid);
76     schema.emplace_back(indexGid);
77     schema.emplace_back(indexDataKey);
78     schema.emplace_back(indexCursor);
79 }
80 
GetPrimaryKeySql(const TableInfo & table)81 std::string CloudSyncLogTableManager::GetPrimaryKeySql(const TableInfo &table)
82 {
83     auto primaryKey = table.GetPrimaryKey();
84     if (primaryKey[0] == CloudDbConstant::ROW_ID_FIELD_NAME) {
85         return "PRIMARY KEY(hash_key, cloud_gid)";
86     }
87     return "PRIMARY KEY(hash_key)";
88 }
89 
90 // The parameter "identity" is a hash string that identifies a device. The same for the next two functions.
GetInsertTrigger(const TableInfo & table,const std::string & identity)91 std::string CloudSyncLogTableManager::GetInsertTrigger(const TableInfo &table, const std::string &identity)
92 {
93     std::string logTblName = GetLogTableName(table);
94     std::string tableName = table.GetTableName();
95     std::string insertTrigger = "CREATE TRIGGER IF NOT EXISTS ";
96     insertTrigger += "naturalbase_rdb_" + tableName + "_ON_INSERT AFTER INSERT \n";
97     insertTrigger += "ON '" + tableName + "'\n";
98     insertTrigger += "WHEN (SELECT count(*) FROM " + DBConstant::RELATIONAL_PREFIX + "metadata ";
99     insertTrigger += "WHERE key = 'log_trigger_switch' AND value = 'true')\n";
100     insertTrigger += "BEGIN\n";
101     insertTrigger += CloudStorageUtils::GetCursorIncSql(tableName) + "\n";
102     insertTrigger += "\t INSERT OR REPLACE INTO " + logTblName;
103     insertTrigger += " (data_key, device, ori_device, timestamp, wtimestamp, flag, hash_key, cloud_gid, ";
104     insertTrigger += " extend_field, cursor, version, sharing_resource, status)";
105     insertTrigger += " VALUES (new." + std::string(DBConstant::SQLITE_INNER_ROWID) + ", '', '',";
106     insertTrigger += " get_raw_sys_time(), get_raw_sys_time(), 0x02|0x20, ";
107     insertTrigger += CalcPrimaryKeyHash("NEW.", table, identity) + ", CASE WHEN (SELECT count(*)<>0 FROM ";
108     insertTrigger += logTblName + " WHERE hash_key = " + CalcPrimaryKeyHash("NEW.", table, identity);
109     insertTrigger += ") THEN (SELECT cloud_gid FROM " + logTblName + " WHERE hash_key = ";
110     insertTrigger += CalcPrimaryKeyHash("NEW.", table, identity) + ") ELSE '' END, ";
111     insertTrigger += table.GetTrackerTable().GetAssignValSql();
112     insertTrigger += ", " + CloudStorageUtils::GetSelectIncCursorSql(tableName) + ", ";
113     insertTrigger += "(SELECT CASE WHEN version IS NULL THEN '' ELSE version END FROM " + logTblName;
114     insertTrigger += " WHERE hash_key = " + CalcPrimaryKeyHash("NEW.", table, identity);
115     insertTrigger += "), '', 0);\n";
116     insertTrigger += CloudStorageUtils::GetTableRefUpdateSql(table, OpType::INSERT);
117     insertTrigger += "SELECT client_observer('" + tableName + "', NEW." + std::string(DBConstant::SQLITE_INNER_ROWID);
118     insertTrigger += ", 0, ";
119     insertTrigger += (table.GetTrackerTable().GetTrackerColNames().empty() ? "0" : "1");
120     insertTrigger += ");\n";
121     insertTrigger += "END;";
122     return insertTrigger;
123 }
124 
GetUpdateTrigger(const TableInfo & table,const std::string & identity)125 std::string CloudSyncLogTableManager::GetUpdateTrigger(const TableInfo &table, const std::string &identity)
126 {
127     (void)identity;
128     std::string logTblName = GetLogTableName(table);
129     std::string tableName = table.GetTableName();
130     std::string updateTrigger = "CREATE TRIGGER IF NOT EXISTS ";
131     updateTrigger += "naturalbase_rdb_" + tableName + "_ON_UPDATE AFTER UPDATE \n";
132     updateTrigger += "ON '" + tableName + "'\n";
133     updateTrigger += "WHEN (SELECT count(*) FROM " + DBConstant::RELATIONAL_PREFIX + "metadata ";
134     updateTrigger += "WHERE key = 'log_trigger_switch' AND value = 'true')\n";
135     updateTrigger += "BEGIN\n"; // if user change the primary key, we can still use gid to identify which one is updated
136     updateTrigger += CloudStorageUtils::GetCursorIncSql(tableName) + "\n";
137     updateTrigger += "\t UPDATE " + logTblName;
138     updateTrigger += " SET timestamp=get_raw_sys_time(), device='', flag=0x02|0x20";
139     if (!table.GetTrackerTable().GetTrackerColNames().empty()) {
140         updateTrigger += table.GetTrackerTable().GetExtendAssignValSql();
141     }
142     updateTrigger += ", cursor=" + CloudStorageUtils::GetSelectIncCursorSql(tableName) + ", ";
143     updateTrigger += CloudStorageUtils::GetUpdateLockChangedSql();
144     updateTrigger += " WHERE data_key = OLD." + std::string(DBConstant::SQLITE_INNER_ROWID) + ";\n";
145     updateTrigger += CloudStorageUtils::GetTableRefUpdateSql(table, OpType::UPDATE);
146     updateTrigger += "SELECT client_observer('" + tableName + "', OLD.";
147     updateTrigger += std::string(DBConstant::SQLITE_INNER_ROWID);
148     updateTrigger += ", 1, ";
149     updateTrigger += table.GetTrackerTable().GetDiffTrackerValSql();
150     updateTrigger += ");";
151     updateTrigger += "END;";
152     return updateTrigger;
153 }
154 
GetDeleteTrigger(const TableInfo & table,const std::string & identity)155 std::string CloudSyncLogTableManager::GetDeleteTrigger(const TableInfo &table, const std::string &identity)
156 {
157     (void)identity;
158     std::string logTblName = GetLogTableName(table);
159     std::string tableName = table.GetTableName();
160     std::string deleteTrigger = "CREATE TRIGGER IF NOT EXISTS ";
161     deleteTrigger += "naturalbase_rdb_" + tableName + "_ON_DELETE BEFORE DELETE \n";
162     deleteTrigger += "ON '" + tableName + "'\n";
163     deleteTrigger += "WHEN (SELECT count(*) FROM " + DBConstant::RELATIONAL_PREFIX + "metadata ";
164     deleteTrigger += "WHERE key = 'log_trigger_switch' AND VALUE = 'true')\n";
165     deleteTrigger += "BEGIN\n";
166     deleteTrigger += CloudStorageUtils::GetCursorIncSql(tableName) + "\n";
167     deleteTrigger += "\t UPDATE " + GetLogTableName(table);
168     deleteTrigger += " SET data_key=-1,";
169     uint32_t localDeleteFlag = static_cast<uint32_t>(LogInfoFlag::FLAG_DELETE) |
170         static_cast<uint32_t>(LogInfoFlag::FLAG_LOCAL);
171     deleteTrigger += "flag=(CASE WHEN cloud_gid='' THEN " + std::to_string(localDeleteFlag) + " ELSE " +
172         std::to_string(localDeleteFlag | static_cast<uint32_t>(LogInfoFlag::FLAG_DEVICE_CLOUD_INCONSISTENCY)) +
173         " END),";
174     deleteTrigger += "timestamp=get_raw_sys_time()";
175     if (!table.GetTrackerTable().GetTrackerColNames().empty()) {
176         deleteTrigger += table.GetTrackerTable().GetExtendAssignValSql(true);
177     }
178     deleteTrigger += ", cursor=" + CloudStorageUtils::GetSelectIncCursorSql(tableName) + ", ";
179     deleteTrigger += CloudStorageUtils::GetDeleteLockChangedSql();
180     deleteTrigger += " WHERE data_key = OLD." + std::string(DBConstant::SQLITE_INNER_ROWID) + ";\n";
181     deleteTrigger += CloudStorageUtils::GetTableRefUpdateSql(table, OpType::DELETE);
182     // -1 is rowid when data is deleted, 2 means change type is delete(ClientChangeType)
183     deleteTrigger += "SELECT client_observer('" + tableName + "', -1, 2, ";
184     deleteTrigger += table.GetTrackerTable().GetTrackerColNames().empty() ? "0" : "1";
185     deleteTrigger += ");\n";
186     deleteTrigger += "END;";
187     return deleteTrigger;
188 }
189 
GetDropTriggers(const TableInfo & table)190 std::vector<std::string> CloudSyncLogTableManager::GetDropTriggers(const TableInfo &table)
191 {
192     std::vector<std::string> dropTriggers;
193     std::string tableName = table.GetTableName();
194     std::string insertTrigger = "DROP TRIGGER IF EXISTS naturalbase_rdb_" + tableName + "_ON_INSERT; ";
195     std::string updateTrigger = "DROP TRIGGER IF EXISTS naturalbase_rdb_" + tableName + "_ON_UPDATE; ";
196     std::string deleteTrigger = "DROP TRIGGER IF EXISTS naturalbase_rdb_" + tableName + "_ON_DELETE; ";
197     dropTriggers.emplace_back(insertTrigger);
198     dropTriggers.emplace_back(updateTrigger);
199     dropTriggers.emplace_back(deleteTrigger);
200     if (table.GetTrackerTable().GetTrackerColNames().empty()) {
201         std::string clearExtendSql = "UPDATE " + GetLogTableName(table) + " SET extend_field = '';";
202         dropTriggers.emplace_back(clearExtendSql);
203     }
204     return dropTriggers;
205 }
206 } // DistributedDB