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 <sys/time.h>
17 #include <gtest/gtest.h>
18
19 #include "db_common.h"
20 #include "distributeddb_data_generate_unit_test.h"
21 #include "distributeddb_tools_unit_test.h"
22 #include "relational_store_manager.h"
23
24 using namespace testing::ext;
25 using namespace DistributedDB;
26 using namespace DistributedDBUnitTest;
27 using namespace std;
28
29 namespace {
30 constexpr const char *DB_SUFFIX = ".db";
31 constexpr const char *STORE_ID = "Relational_Store_ID";
32 std::string g_dbDir;
33 std::string g_testDir;
34 DistributedDB::RelationalStoreManager g_mgr(APP_ID, USER_ID);
35
36 constexpr int E_OK = 0;
37 constexpr int E_ERROR = 1;
38 const int WAIT_TIME = 1000; // 1000ms
39 constexpr static uint64_t TO_100_NS = 10; // 1us to 100ns
40 const uint64_t MULTIPLES_BETWEEN_SECONDS_AND_MICROSECONDS = 1000000;
41
42 class DistributedDBCloudInterfacesRelationalExtTest : public testing::Test {
43 public:
44 static void SetUpTestCase(void);
45 static void TearDownTestCase(void);
46 void SetUp() override;
47 void TearDown() override;
48 };
49
SetUpTestCase(void)50 void DistributedDBCloudInterfacesRelationalExtTest::SetUpTestCase(void)
51 {
52 DistributedDBToolsUnitTest::TestDirInit(g_testDir);
53 LOGD("Test dir is %s", g_testDir.c_str());
54 g_dbDir = g_testDir + "/";
55 DistributedDBToolsUnitTest::RemoveTestDbFiles(g_testDir);
56 }
57
TearDownTestCase(void)58 void DistributedDBCloudInterfacesRelationalExtTest::TearDownTestCase(void)
59 {
60 }
61
SetUp()62 void DistributedDBCloudInterfacesRelationalExtTest::SetUp()
63 {
64 }
65
TearDown()66 void DistributedDBCloudInterfacesRelationalExtTest::TearDown()
67 {
68 DistributedDBToolsUnitTest::RemoveTestDbFiles(g_testDir);
69 }
70
GetCurrentSysTimeIn100Ns(uint64_t & outTime)71 static int GetCurrentSysTimeIn100Ns(uint64_t &outTime)
72 {
73 struct timeval rawTime;
74 int errCode = gettimeofday(&rawTime, nullptr);
75 if (errCode < 0) {
76 return -E_ERROR;
77 }
78 outTime = static_cast<uint64_t>(rawTime.tv_sec) * MULTIPLES_BETWEEN_SECONDS_AND_MICROSECONDS +
79 static_cast<uint64_t>(rawTime.tv_usec);
80 outTime *= TO_100_NS;
81 return E_OK;
82 }
83
84 /**
85 * @tc.name: GetRawSysTimeTest001
86 * @tc.desc: Test get_raw_sys_time has been registered in sqlite
87 * @tc.type: FUNC
88 * @tc.require:
89 * @tc.author: zhangshijie
90 */
91 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, GetRawSysTimeTest001, TestSize.Level0)
92 {
93 const std::string sql = "select get_raw_sys_time();";
94 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
95 EXPECT_NE(db, nullptr);
96 uint64_t curTime = 0;
97 int errCode = GetCurrentSysTimeIn100Ns(curTime);
98 EXPECT_EQ(errCode, E_OK);
__anon1d7634920202(sqlite3_stmt *stmt) 99 errCode = RelationalTestUtils::ExecSql(db, sql, nullptr, [curTime] (sqlite3_stmt *stmt) {
100 EXPECT_GT(static_cast<uint64_t>(sqlite3_column_int64(stmt, 0)), curTime);
101 return OK;
102 });
103 EXPECT_EQ(errCode, SQLITE_OK);
104 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
105 }
106
PrepareData(const std::string & tableName,bool primaryKeyIsRowId)107 void PrepareData(const std::string &tableName, bool primaryKeyIsRowId)
108 {
109 /**
110 * @tc.steps:step1. create db, create table.
111 * @tc.expected: step1. return ok.
112 */
113 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
114 EXPECT_NE(db, nullptr);
115 EXPECT_EQ(RelationalTestUtils::ExecSql(db, "PRAGMA journal_mode=WAL;"), SQLITE_OK);
116 std::string sql;
117 if (primaryKeyIsRowId) {
118 sql = "create table " + tableName + "(rowid INTEGER primary key, id int, name TEXT);";
119 } else {
120 sql = "create table " + tableName + "(rowid int, id int, name TEXT, PRIMARY KEY(id, name));";
121 }
122
123 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
124 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
125
126 /**
127 * @tc.steps:step2. create distributed table.
128 * @tc.expected: step2. return ok.
129 */
130 RelationalStoreDelegate *delegate = nullptr;
131 DBStatus status = g_mgr.OpenStore(g_dbDir + STORE_ID + DB_SUFFIX, STORE_ID, {}, delegate);
132 EXPECT_EQ(status, OK);
133 ASSERT_NE(delegate, nullptr);
134 EXPECT_EQ(delegate->CreateDistributedTable(tableName, DistributedDB::CLOUD_COOPERATION), OK);
135 EXPECT_EQ(g_mgr.CloseStore(delegate), OK);
136 delegate = nullptr;
137 }
138
139 /**
140 * @tc.name: InsertTriggerTest001
141 * @tc.desc: Test insert trigger in sqlite
142 * @tc.type: FUNC
143 * @tc.require:
144 * @tc.author: zhangshijie
145 */
146 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, InsertTriggerTest001, TestSize.Level0)
147 {
148 /**
149 * @tc.steps:step1. prepare data.
150 * @tc.expected: step1. return ok.
151 */
152 const std::string tableName = "sync_data";
153 PrepareData(tableName, false);
154
155 /**
156 * @tc.steps:step2. insert data into sync_data.
157 * @tc.expected: step2. return ok.
158 */
159 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
160 EXPECT_NE(db, nullptr);
161 std::string sql = "insert into " + tableName + " VALUES(2, 1, 'zhangsan');";
162 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
163
164 /**
165 * @tc.steps:step3. select data from log table.
166 * @tc.expected: step3. return ok.
167 */
168 sql = "select * from " + DBConstant::RELATIONAL_PREFIX + tableName + "_log;";
169 uint64_t curTime = 0;
170 int errCode = GetCurrentSysTimeIn100Ns(curTime);
171 EXPECT_EQ(errCode, E_OK);
172
173 int resultCount = 0;
__anon1d7634920302(sqlite3_stmt *stmt) 174 errCode = RelationalTestUtils::ExecSql(db, sql, nullptr, [curTime, &resultCount] (sqlite3_stmt *stmt) {
175 EXPECT_EQ(sqlite3_column_int64(stmt, 0), 2); // 2 is row id
176 std::string device = "";
177 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 1, device), E_OK);
178 EXPECT_EQ(device, "");
179 std::string oriDevice = "";
180 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 2, oriDevice), E_OK); // 2 is column index
181 EXPECT_EQ(oriDevice, "");
182
183 int64_t timestamp = sqlite3_column_int64(stmt, 3); // 3 is column index
184 int64_t wtimestamp = sqlite3_column_int64(stmt, 4); // 4 is column index
185 int64_t diff = MULTIPLES_BETWEEN_SECONDS_AND_MICROSECONDS * TO_100_NS;
186 EXPECT_TRUE(wtimestamp - timestamp < diff);
187 EXPECT_TRUE(static_cast<int64_t>(curTime - timestamp) < diff);
188 EXPECT_EQ(sqlite3_column_int(stmt, 5), 2); // 5 is column index flag == 2
189 resultCount++;
190 return OK;
191 });
192 EXPECT_EQ(errCode, SQLITE_OK);
193 EXPECT_EQ(resultCount, 1);
194 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
195 }
196
197 /**
198 * @tc.name: InsertTriggerTest002
199 * @tc.desc: Test insert trigger in sqlite when use "insert or replace"
200 * @tc.type: FUNC
201 * @tc.require:
202 * @tc.author: zhangshijie
203 */
204 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, InsertTriggerTest002, TestSize.Level1)
205 {
206 /**
207 * @tc.steps:step1. prepare data.
208 * @tc.expected: step1. return ok.
209 */
210 const std::string tableName = "sync_data";
211 PrepareData(tableName, false);
212
213 /**
214 * @tc.steps:step2. insert data into sync_data.
215 * @tc.expected: step2. return ok.
216 */
217 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
218 EXPECT_NE(db, nullptr);
219 std::string sql = "insert into " + tableName + " VALUES(2, 1, 'zhangsan1');";
220 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
221
222 // update cloud_gid in log table
223 std::string gid = "test_gid";
224 sql = "update " + DBCommon::GetLogTableName(tableName) + " set cloud_gid = '" + gid + "'";
225 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
226 // use insert or replace to update data
227 sql = "insert or replace into " + tableName + " VALUES(3, 1, 'zhangsan1');";
228 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
229
230 /**
231 * @tc.steps:step3. select data from log table.
232 * @tc.expected: step3. return ok.
233 */
234 sql = "select data_key, device, ori_device, flag, cloud_gid from " + DBCommon::GetLogTableName(tableName);
235 int resultCount = 0;
__anon1d7634920402(sqlite3_stmt *stmt) 236 int errCode = RelationalTestUtils::ExecSql(db, sql, nullptr, [&resultCount, gid] (sqlite3_stmt *stmt) {
237 EXPECT_EQ(sqlite3_column_int64(stmt, 0), 3); // 3 is row id
238 std::string device = "";
239 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 1, device), E_OK);
240 EXPECT_EQ(device, "");
241 std::string oriDevice = "";
242 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 2, oriDevice), E_OK); // 2 is column index
243 EXPECT_EQ(oriDevice, "");
244
245 EXPECT_EQ(sqlite3_column_int(stmt, 3), 2); // 3 is column index flag == 2
246 std::string gidStr;
247 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 4, gidStr), E_OK); // 4 is column index
248 EXPECT_EQ(gid, gidStr);
249 resultCount++;
250 return OK;
251 });
252 EXPECT_EQ(errCode, SQLITE_OK);
253 EXPECT_EQ(resultCount, 1);
254 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
255 }
256
UpdateTriggerTest(bool primaryKeyIsRowId)257 void UpdateTriggerTest(bool primaryKeyIsRowId)
258 {
259 /**
260 * @tc.steps:step1. prepare data.
261 * @tc.expected: step1. return ok.
262 */
263 const std::string tableName = "sync_data";
264 PrepareData(tableName, primaryKeyIsRowId);
265
266 /**
267 * @tc.steps:step2. insert data into sync_data_tmp.
268 * @tc.expected: step2. return ok.
269 */
270 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
271 EXPECT_NE(db, nullptr);
272 std::string sql = "insert into " + tableName + " VALUES(2, 1, 'zhangsan');";
273 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
274
275 /**
276 * @tc.steps:step3. update data.
277 * @tc.expected: step3. return ok.
278 */
279 std::this_thread::sleep_for(std::chrono::milliseconds(WAIT_TIME));
280 sql = "update " + tableName + " set name = 'lisi';";
281 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
282
283 /**
284 * @tc.steps:step4. select data from log table.
285 * @tc.expected: step4. return ok.
286 */
287 sql = "select * from " + DBConstant::RELATIONAL_PREFIX + tableName + "_log;";
288 uint64_t curTime = 0;
289 int errCode = GetCurrentSysTimeIn100Ns(curTime);
290 EXPECT_EQ(errCode, E_OK);
291
292 int resultCount = 0;
293 errCode = RelationalTestUtils::ExecSql(db, sql, nullptr, [curTime, &resultCount] (sqlite3_stmt *stmt) {
294 EXPECT_EQ(sqlite3_column_int64(stmt, 0), 2); // 2 is row id
295 EXPECT_EQ(sqlite3_column_int(stmt, 5), 2); // 5 is column index, flag == 2
296
297 std::string device = "";
298 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 1, device), E_OK);
299 EXPECT_EQ(device, "");
300 std::string oriDevice = "";
301 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 2, oriDevice), E_OK); // 2 is column index
302 EXPECT_EQ(oriDevice, "");
303
304 int64_t timestamp = sqlite3_column_int64(stmt, 3); // 3 is column index
305 int64_t wtimestamp = sqlite3_column_int64(stmt, 4); // 4 is column index
306 int64_t diff = MULTIPLES_BETWEEN_SECONDS_AND_MICROSECONDS * TO_100_NS;
307 EXPECT_TRUE(timestamp - wtimestamp > diff);
308 EXPECT_TRUE(static_cast<int64_t>(curTime - timestamp) < diff);
309
310 resultCount++;
311 return OK;
312 });
313 EXPECT_EQ(errCode, SQLITE_OK);
314 EXPECT_EQ(resultCount, 1);
315 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
316 }
317
318 /**
319 * @tc.name: UpdateTriggerTest001
320 * @tc.desc: Test update trigger in sqlite for primary key is not row id
321 * @tc.type: FUNC
322 * @tc.require:
323 * @tc.author: zhangshijie
324 */
325 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, UpdateTriggerTest001, TestSize.Level0)
326 {
327 UpdateTriggerTest(false);
328 }
329
330 /**
331 * @tc.name: UpdateTriggerTest002
332 * @tc.desc: Test update trigger in sqlite for primary key is row id
333 * @tc.type: FUNC
334 * @tc.require:
335 * @tc.author: zhangshijie
336 */
337 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, UpdateTriggerTest002, TestSize.Level0)
338 {
339 UpdateTriggerTest(true);
340 }
341
342 /**
343 * @tc.name: DeleteTriggerTest001
344 * @tc.desc: Test delete trigger in sqlite
345 * @tc.type: FUNC
346 * @tc.require:
347 * @tc.author: zhangshijie
348 */
349 HWTEST_F(DistributedDBCloudInterfacesRelationalExtTest, DeleteTriggerTest001, TestSize.Level0)
350 {
351 /**
352 * @tc.steps:step1. prepare data.
353 * @tc.expected: step1. return ok.
354 */
355 const std::string tableName = "sync_data";
356 PrepareData(tableName, true);
357
358 /**
359 * @tc.steps:step2. insert data into sync_data.
360 * @tc.expected: step2. return ok.
361 */
362 sqlite3 *db = RelationalTestUtils::CreateDataBase(g_dbDir + STORE_ID + DB_SUFFIX);
363 EXPECT_NE(db, nullptr);
364 std::string sql = "insert into " + tableName + " VALUES(2, 1, 'zhangsan');";
365 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
366
367 /**
368 * @tc.steps:step3. delete data.
369 * @tc.expected: step3. return ok.
370 */
371 std::this_thread::sleep_for(std::chrono::milliseconds(WAIT_TIME));
372 sql = "delete from " + tableName + " where name = 'zhangsan';";
373 EXPECT_EQ(RelationalTestUtils::ExecSql(db, sql), E_OK);
374
375 /**
376 * @tc.steps:step4. select data from log table.
377 * @tc.expected: step4. return ok.
378 */
379 sql = "select * from " + DBConstant::RELATIONAL_PREFIX + tableName + "_log;";
380 uint64_t curTime = 0;
381 int errCode = GetCurrentSysTimeIn100Ns(curTime);
382 EXPECT_EQ(errCode, E_OK);
383
384 int resultCount = 0;
__anon1d7634920602(sqlite3_stmt *stmt) 385 errCode = RelationalTestUtils::ExecSql(db, sql, nullptr, [curTime, &resultCount] (sqlite3_stmt *stmt) {
386 EXPECT_EQ(sqlite3_column_int64(stmt, 0), -1);
387 EXPECT_EQ(sqlite3_column_int(stmt, 5), 3); // 5 is column index, flag == 3
388
389 std::string device = "de";
390 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 1, device), E_OK);
391 EXPECT_EQ(device, "");
392 std::string oriDevice = "de";
393 EXPECT_EQ(SQLiteUtils::GetColumnTextValue(stmt, 2, oriDevice), E_OK); // 2 is column index
394 EXPECT_EQ(oriDevice, "");
395
396 int64_t timestamp = sqlite3_column_int64(stmt, 3); // 3 is column index
397 int64_t wtimestamp = sqlite3_column_int64(stmt, 4); // 4 is column index
398 int64_t diff = MULTIPLES_BETWEEN_SECONDS_AND_MICROSECONDS * TO_100_NS;
399 EXPECT_TRUE(timestamp - wtimestamp > diff);
400 EXPECT_TRUE(static_cast<int64_t>(curTime - timestamp) < diff);
401
402 resultCount++;
403 return OK;
404 });
405 EXPECT_EQ(errCode, SQLITE_OK);
406 EXPECT_EQ(resultCount, 1);
407 EXPECT_EQ(sqlite3_close_v2(db), E_OK);
408 }
409 }