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 <gtest/gtest.h>
17
18 #include <numeric>
19 #include <string>
20
21 #include "abs_rdb_predicates.h"
22 #include "common.h"
23 #include "rdb_errno.h"
24 #include "rdb_helper.h"
25 #include "rdb_open_callback.h"
26
27 using namespace testing::ext;
28 using namespace OHOS::NativeRdb;
29 namespace OHOS::RdbDeleteTest {
30 struct RdbTestParam {
31 std::shared_ptr<RdbStore> store;
operator std::shared_ptr<RdbStore>OHOS::RdbDeleteTest::RdbTestParam32 operator std::shared_ptr<RdbStore>()
33 {
34 return store;
35 }
36 };
37 static RdbTestParam g_store;
38 static RdbTestParam g_memDb;
39
40 class RdbDeleteTest : public testing::TestWithParam<RdbTestParam *> {
41 public:
42 static void SetUpTestCase(void);
43 static void TearDownTestCase(void);
44 void SetUp();
45 void TearDown();
46
47 std::shared_ptr<RdbStore> store_;
48 static const std::string DATABASE_NAME;
49 };
50
51 const std::string RdbDeleteTest::DATABASE_NAME = RDB_TEST_PATH + "delete_test.db";
52
53 class DeleteTestOpenCallback : public RdbOpenCallback {
54 public:
55 int OnCreate(RdbStore &store) override;
56 int OnUpgrade(RdbStore &store, int oldVersion, int newVersion) override;
57 };
58 constexpr const char *CREATE_TABLE_TEST = "CREATE TABLE IF NOT EXISTS test"
59 "(id INTEGER PRIMARY KEY AUTOINCREMENT, "
60 "name TEXT NOT NULL, age INTEGER, salary "
61 "REAL, blobType BLOB)";
OnCreate(RdbStore & store)62 int DeleteTestOpenCallback::OnCreate(RdbStore &store)
63 {
64 return store.ExecuteSql(CREATE_TABLE_TEST);
65 }
66
OnUpgrade(RdbStore & store,int oldVersion,int newVersion)67 int DeleteTestOpenCallback::OnUpgrade(RdbStore &store, int oldVersion, int newVersion)
68 {
69 return E_OK;
70 }
71
SetUpTestCase(void)72 void RdbDeleteTest::SetUpTestCase(void)
73 {
74 int errCode = E_OK;
75 RdbHelper::DeleteRdbStore(DATABASE_NAME);
76 RdbStoreConfig config(RdbDeleteTest::DATABASE_NAME);
77 DeleteTestOpenCallback helper;
78 g_store.store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
79 ASSERT_NE(g_store.store, nullptr);
80
81 config.SetStorageMode(StorageMode::MODE_MEMORY);
82 g_memDb.store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
83 ASSERT_NE(g_memDb.store, nullptr);
84 }
85
TearDownTestCase(void)86 void RdbDeleteTest::TearDownTestCase(void)
87 {
88 RdbStoreConfig config(RdbDeleteTest::DATABASE_NAME);
89 RdbHelper::DeleteRdbStore(config);
90 config.SetStorageMode(StorageMode::MODE_MEMORY);
91 RdbHelper::DeleteRdbStore(config);
92 }
93
SetUp(void)94 void RdbDeleteTest::SetUp(void)
95 {
96 store_ = *GetParam();
97 ASSERT_NE(store_, nullptr);
98 store_->ExecuteSql("DELETE FROM test");
99 }
100
TearDown(void)101 void RdbDeleteTest::TearDown(void)
102 {
103 }
104
105 /**
106 * @tc.name: RdbStore_Delete_001
107 * @tc.desc: test RdbStore update, select id and update one row
108 * @tc.type: FUNC
109 */
110 HWTEST_P(RdbDeleteTest, RdbStore_Delete_001, TestSize.Level1)
111 {
112 int64_t id;
113 int deletedRows;
114
115 int ret = store_->Insert(id, "test", UTUtils::SetRowData(UTUtils::g_rowData[0]));
116 EXPECT_EQ(ret, E_OK);
117 EXPECT_EQ(1, id);
118
119 ret = store_->Insert(id, "test", UTUtils::SetRowData(UTUtils::g_rowData[1]));
120 EXPECT_EQ(ret, E_OK);
121 EXPECT_EQ(2, id);
122
123 ret = store_->Insert(id, "test", UTUtils::SetRowData(UTUtils::g_rowData[2]));
124 EXPECT_EQ(ret, E_OK);
125 EXPECT_EQ(3, id);
126
127 ret = store_->Delete(deletedRows, "test", "id = 1");
128 EXPECT_EQ(ret, E_OK);
129 EXPECT_EQ(1, deletedRows);
130
131 std::shared_ptr<ResultSet> resultSet =
132 store_->QuerySql("SELECT * FROM test WHERE id = ?", std::vector<std::string>{ "1" });
133 EXPECT_NE(resultSet, nullptr);
134 ret = resultSet->GoToNextRow();
135 EXPECT_EQ(ret, E_ROW_OUT_RANGE);
136 ret = resultSet->Close();
137 EXPECT_EQ(ret, E_OK);
138
139 resultSet = store_->QuerySql("SELECT * FROM test WHERE id = ?", std::vector<std::string>{ "2" });
140 EXPECT_NE(resultSet, nullptr);
141 ret = resultSet->GoToFirstRow();
142 EXPECT_EQ(ret, E_OK);
143 ret = resultSet->GoToNextRow();
144 EXPECT_EQ(ret, E_ROW_OUT_RANGE);
145 ret = resultSet->Close();
146 EXPECT_EQ(ret, E_OK);
147
148 resultSet = store_->QuerySql("SELECT * FROM test WHERE id = 3", std::vector<std::string>());
149 EXPECT_NE(resultSet, nullptr);
150 ret = resultSet->GoToFirstRow();
151 EXPECT_EQ(ret, E_OK);
152 ret = resultSet->GoToNextRow();
153 EXPECT_EQ(ret, E_ROW_OUT_RANGE);
154 ret = resultSet->Close();
155 EXPECT_EQ(ret, E_OK);
156 }
157
158 /**
159 * @tc.name: RdbStore_Delete_002
160 * @tc.desc: test RdbStore update, select id and update one row
161 * @tc.type: FUNC
162 */
163 HWTEST_P(RdbDeleteTest, RdbStore_Delete_002, TestSize.Level1)
164 {
165 int64_t id;
166 ValuesBucket values;
167 int deletedRows;
168
169 values.PutInt("id", 1);
170 values.PutString("name", std::string("zhangsan"));
171 values.PutInt("age", 18);
172 values.PutDouble("salary", 100.5);
173 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
174 int ret = store_->Insert(id, "test", values);
175 EXPECT_EQ(ret, E_OK);
176 EXPECT_EQ(1, id);
177
178 values.Clear();
179 values.PutInt("id", 2);
180 values.PutString("name", std::string("lisi"));
181 values.PutInt("age", 19);
182 values.PutDouble("salary", 200.5);
183 values.PutBlob("blobType", std::vector<uint8_t>{ 4, 5, 6 });
184 ret = store_->Insert(id, "test", values);
185 EXPECT_EQ(ret, E_OK);
186 EXPECT_EQ(2, id);
187
188 values.Clear();
189 values.PutInt("id", 3);
190 values.PutString("name", std::string("wangyjing"));
191 values.PutInt("age", 20);
192 values.PutDouble("salary", 300.5);
193 values.PutBlob("blobType", std::vector<uint8_t>{ 7, 8, 9 });
194 ret = store_->Insert(id, "test", values);
195 EXPECT_EQ(ret, E_OK);
196 EXPECT_EQ(3, id);
197
198 ret = store_->Delete(deletedRows, "test");
199 EXPECT_EQ(ret, E_OK);
200 EXPECT_EQ(3, deletedRows);
201
202 std::shared_ptr<ResultSet> resultSet = store_->QuerySql("SELECT * FROM test");
203 EXPECT_NE(resultSet, nullptr);
204 ret = resultSet->GoToNextRow();
205 EXPECT_EQ(ret, E_ROW_OUT_RANGE);
206 ret = resultSet->Close();
207 EXPECT_EQ(ret, E_OK);
208 }
209
210 /**
211 * @tc.name: RdbStore_Delete_003
212 * @tc.desc: test RdbStore update, select id and update one row
213 * @tc.type: FUNC
214 */
215 HWTEST_P(RdbDeleteTest, RdbStore_Delete_003, TestSize.Level1)
216 {
217 int64_t id;
218 ValuesBucket values;
219 int deletedRows;
220
221 values.PutInt("id", 1);
222 values.PutString("name", std::string("zhangsan"));
223 values.PutInt("age", 18);
224 values.PutDouble("salary", 100.5);
225 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
226 int ret = store_->Insert(id, "test", values);
227 EXPECT_EQ(ret, E_OK);
228 EXPECT_EQ(1, id);
229
230 ret = store_->Delete(deletedRows, "", "id = ?", std::vector<std::string>{ "1" });
231 EXPECT_EQ(ret, E_EMPTY_TABLE_NAME);
232
233 ret = store_->Delete(deletedRows, "wrongTable", "id = ?", std::vector<std::string>{ "1" });
234 EXPECT_EQ(ret, E_SQLITE_ERROR);
235
236 ret = store_->Delete(deletedRows, "test", "wrong sql id = ?", std::vector<std::string>{ "1" });
237 EXPECT_EQ(ret, E_SQLITE_ERROR);
238
239 ret = store_->Delete(deletedRows, "test", "id = 1", std::vector<std::string>());
240 EXPECT_EQ(ret, E_OK);
241 EXPECT_EQ(deletedRows, 1);
242 }
243
244 /**
245 * @tc.name: RdbStore_Delete_With_Returning_001
246 * @tc.desc: normal test
247 * @tc.type: FUNC
248 */
249 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_001, TestSize.Level1)
250 {
251 int64_t id;
252 ValuesBucket values;
253 values.PutInt("id", 1);
254 values.PutString("name", std::string("zhangsan"));
255 values.PutInt("age", 18);
256 values.PutDouble("salary", 100.5);
257 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
258 int ret = store_->Insert(id, "test", values);
259 EXPECT_EQ(ret, E_OK);
260 EXPECT_EQ(1, id);
261
262 AbsRdbPredicates predicates("test");
263 auto [status, res] = store_->Delete(predicates, { "id" });
264 EXPECT_EQ(status, E_OK);
265 EXPECT_EQ(res.changed, 1);
266
267 int rowCount = -1;
268 ASSERT_EQ(res.results->GetRowCount(rowCount), E_OK);
269 ASSERT_EQ(rowCount, 1);
270 int columnIndex = -1;
271 ASSERT_EQ(res.results->GetColumnIndex("id", columnIndex), E_OK);
272 int64_t value;
273 ASSERT_EQ(res.results->GetLong(columnIndex, value), E_OK);
274 EXPECT_EQ(value, 1);
275 }
276
277 /**
278 * @tc.name: RdbStore_Delete_With_Returning_002
279 * @tc.desc: abnormal test, delete over returning limit
280 * @tc.type: FUNC
281 */
282 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_002, TestSize.Level1)
283 {
284 ValuesBuckets rows;
285 for (int i = 0; i < 1124; i++) {
286 ValuesBucket row;
287 row.Put("id", i);
288 row.Put("name", "Jim");
289 rows.Put(row);
290 }
291 auto ret = store_->BatchInsert("test", rows);
292 EXPECT_EQ(ret.first, E_OK);
293 EXPECT_EQ(ret.second, 1124);
294
295 AbsRdbPredicates predicates("test");
296 auto [code, res] = store_->Delete(predicates, { "id" });
297 EXPECT_EQ(code, E_OK);
298 EXPECT_EQ(res.changed, 1124);
299 int rowCount = -1;
300 ASSERT_EQ(res.results->GetRowCount(rowCount), E_OK);
301 ASSERT_EQ(rowCount, 1024);
302 }
303
304 /**
305 * @tc.name: RdbStore_Delete_With_Returning_003
306 * @tc.desc: abnormal test, delete with returning field is string
307 * @tc.type: FUNC
308 */
309 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_003, TestSize.Level1)
310 {
311 ValuesBuckets rows;
312 for (int i = 0; i < 15; i++) {
313 ValuesBucket row;
314 row.Put("id", i);
315 row.Put("name", "Jim" + std::to_string(i));
316 rows.Put(row);
317 }
318 auto ret = store_->BatchInsert("test", rows);
319 EXPECT_EQ(ret.first, E_OK);
320 EXPECT_EQ(ret.second, 15);
321
322 AbsRdbPredicates predicates("test");
323 auto [status, res] = store_->Delete(predicates, { "name" });
324 EXPECT_EQ(status, E_OK);
325 EXPECT_EQ(res.changed, 15);
326
327 int rowCount = -1;
328 ASSERT_EQ(res.results->GetRowCount(rowCount), E_OK);
329 ASSERT_EQ(rowCount, 15);
330 int columnIndex = -1;
331 ASSERT_EQ(res.results->GetColumnIndex("name", columnIndex), E_OK);
332 std::string value;
333 int i = 0;
334 while (i < 15) {
335 ASSERT_EQ(res.results->GetString(columnIndex, value), E_OK);
336 EXPECT_EQ(value, "Jim" + std::to_string(i++));
337 if (i < 15) {
338 ASSERT_EQ(res.results->GoToNextRow(), E_OK);
339 }
340 }
341 }
342
343 /**
344 * @tc.name: RdbStore_Delete_With_Returning_004
345 * @tc.desc: abnormal test, delete with returning field is blob
346 * @tc.type: FUNC
347 */
348 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_004, TestSize.Level1)
349 {
350 ValuesBuckets rows;
351 for (int i = 0; i < 15; i++) {
352 ValuesBucket row;
353 row.Put("id", i);
354 row.Put("name", "Jim" + std::to_string(i));
355 std::vector<uint8_t> blob(i + 1);
356 std::iota(blob.begin(), blob.end(), 1);
357 row.PutBlob("blobType", blob);
358 rows.Put(row);
359 }
360 auto ret = store_->BatchInsert("test", rows);
361 EXPECT_EQ(ret.first, E_OK);
362 EXPECT_EQ(ret.second, 15);
363
364 AbsRdbPredicates predicates("test");
365 auto [status, res] = store_->Delete(predicates, { "blobType" });
366 EXPECT_EQ(status, E_OK);
367 EXPECT_EQ(res.changed, 15);
368
369 int rowCount = -1;
370 ASSERT_EQ(res.results->GetRowCount(rowCount), E_OK);
371 ASSERT_EQ(rowCount, 15);
372 int columnIndex = -1;
373 ASSERT_EQ(res.results->GetColumnIndex("blobType", columnIndex), E_OK);
374 int i = 0;
375 while (i < 15) {
376 std::vector<uint8_t> blob;
377 ASSERT_EQ(res.results->GetBlob(columnIndex, blob), E_OK);
378 std::vector<uint8_t> expectedBlob(++i);
379 std::iota(expectedBlob.begin(), expectedBlob.end(), 1);
380 EXPECT_EQ(blob, expectedBlob);
381 if (i < 15) {
382 ASSERT_EQ(res.results->GoToNextRow(), E_OK);
383 }
384 }
385 }
386
387 /**
388 * @tc.name: RdbStore_Delete_With_Returning_005
389 * @tc.desc: abnormal test, delete with returning field is not exist
390 * @tc.type: FUNC
391 */
392 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_005, TestSize.Level1)
393 {
394 int64_t id;
395 ValuesBucket values;
396 values.PutInt("id", 1);
397 values.PutString("name", std::string("zhangsan"));
398 values.PutInt("age", 18);
399 values.PutDouble("salary", 100.5);
400 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
401 int ret = store_->Insert(id, "test", values);
402 EXPECT_EQ(ret, E_OK);
403 EXPECT_EQ(1, id);
404
405 AbsRdbPredicates predicates("test");
406 auto [status, res] = store_->Delete(predicates, { "notExist" });
407 EXPECT_EQ(status, E_SQLITE_ERROR);
408 EXPECT_EQ(res.changed, -1);
409 ASSERT_EQ(res.results, nullptr);
410 }
411
412 /**
413 * @tc.name: RdbStore_Delete_With_Returning_006
414 * @tc.desc: abnormal test, delete with returning field is not exist
415 * @tc.type: FUNC
416 */
417 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_006, TestSize.Level1)
418 {
419 int64_t id;
420 ValuesBucket values;
421 values.PutInt("id", 1);
422 values.PutString("name", std::string("zhangsan"));
423 values.PutInt("age", 18);
424 values.PutDouble("salary", 100.5);
425 values.PutBlob("blobType", std::vector<uint8_t>{ 1, 2, 3 });
426 int ret = store_->Insert(id, "test", values);
427 EXPECT_EQ(ret, E_OK);
428 EXPECT_EQ(1, id);
429
430 AbsRdbPredicates predicates("test");
431 predicates.EqualTo("id", 100);
432 auto [status, res] = store_->Delete(predicates, { "notExist" });
433 EXPECT_EQ(status, E_SQLITE_ERROR);
434 EXPECT_EQ(res.changed, -1);
435 ASSERT_EQ(res.results, nullptr);
436 }
437
438 /**
439 * @tc.name: RdbStore_Delete_With_Returning_007
440 * @tc.desc: normal test. delete from virtual table with returning
441 * @tc.type: FUNC
442 */
443 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_007, TestSize.Level1)
444 {
445 auto [execRet, execResult] =
446 store_->Execute("CREATE VIRTUAL TABLE IF NOT EXISTS articles USING fts5(title, content);");
447 ASSERT_EQ(execRet, E_OK);
448 ValuesBuckets rows;
449 ValuesBucket row;
450 row.Put("title", "fts5");
451 row.Put("content", "test virtual tables");
452 rows.Put(std::move(row));
453 auto [insertStatus, result] =
454 store_->BatchInsert("articles", rows, NativeRdb::ConflictResolution::ON_CONFLICT_IGNORE);
455 EXPECT_EQ(insertStatus, E_OK);
456
457 AbsRdbPredicates predicates("articles");
458 predicates.EqualTo("title", "fts5");
459 auto [status, res] = store_->Delete(predicates, { "title" });
460 // DELETE RETURNING is not available on virtual tables
461 EXPECT_EQ(status, E_SQLITE_ERROR);
462 EXPECT_EQ(res.changed, -1);
463
464 store_->Execute("Drop TABLE articles");
465 }
466
467 /**
468 * @tc.name: RdbStore_Delete_With_Returning_008
469 * @tc.desc: normal test. delete with returning and trigger
470 * @tc.type: FUNC
471 * @tc.require:
472 * @tc.author:
473 */
474 HWTEST_P(RdbDeleteTest, RdbStore_Delete_With_Returning_008, TestSize.Level1)
475 {
476 auto [code, result1] = store_->Execute(
477 "CREATE TRIGGER before_delete BEFORE DELETE ON test"
478 " BEGIN UPDATE test SET name = 'li' WHERE name = 'wang'; END");
479
480 EXPECT_EQ(code, E_OK);
481
482 ValuesBuckets rows;
483 ValuesBucket row;
484 row.Put("id", 200);
485 row.Put("name", "wang");
486 rows.Put(std::move(row));
487 row.Put("id", 201);
488 row.Put("name", "zhang");
489 rows.Put(std::move(row));
490
491 auto [insertStatus, result] =
492 store_->BatchInsert("test", rows, { "name" }, NativeRdb::ConflictResolution::ON_CONFLICT_IGNORE);
493 EXPECT_EQ(insertStatus, E_OK);
494 EXPECT_EQ(result.changed, 2);
495
496 AbsRdbPredicates predicates("test");
497 predicates.EqualTo("name", "zhang");
498 auto [status, res] = store_->Delete(predicates, { "name" });
499
500 EXPECT_EQ(status, E_OK);
501 EXPECT_EQ(res.changed, 1);
502 int rowCount = -1;
503 ASSERT_EQ(res.results->GetRowCount(rowCount), E_OK);
504 ASSERT_EQ(rowCount, 1);
505 int columnIndex = -1;
506 ASSERT_EQ(res.results->GetColumnIndex("name", columnIndex), E_OK);
507 std::string value;
508 ASSERT_EQ(res.results->GetString(columnIndex, value), E_OK);
509 EXPECT_EQ(value, "zhang");
510
511 // Check the trigger effect
512 auto resultSet = store_->QuerySql("select name from test where id = 200");
513
514 rowCount = -1;
515 resultSet->GetRowCount(rowCount);
516 ASSERT_EQ(rowCount, 1);
517 ASSERT_EQ(resultSet->GoToNextRow(), E_OK);
518 columnIndex = -1;
519 resultSet->GetColumnIndex("name", columnIndex);
520
521 value.clear();
522 EXPECT_EQ(E_OK, resultSet->GetString(columnIndex, value));
523 EXPECT_EQ(value, "li");
524
525 store_->Execute("DROP TRIGGER IF EXISTS before_delete");
526 }
527 INSTANTIATE_TEST_SUITE_P(DeleteTest, RdbDeleteTest, testing::Values(&g_store, &g_memDb));
528 } // namespace OHOS::RdbDeleteTest
529