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 <gtest/gtest.h>
17 #include <sys/stat.h>
18 #include <sys/types.h>
19 #include <unistd.h>
20 #include <string>
21 #include <vector>
22 #include <sstream>
23 #include <algorithm>
24 #include <ctime>
25
26 #include "common.h"
27 #include "rdb_errno.h"
28 #include "rdb_helper.h"
29 #include "rdb_open_callback.h"
30 #include "rdb_predicates.h"
31 #include "abs_predicates.h"
32
33 using namespace testing::ext;
34 using namespace OHOS::NativeRdb;
35
36 class RdbStorePredicateJoinTest : public testing::Test {
37 public:
38 static void SetUpTestCase(void);
39 static void TearDownTestCase(void);
40 void SetUp();
41 void TearDown();
42 void GenerateAllTables();
43 void InsertUserDates();
44 void InsertBookDates();
45 int ResultSize(std::shared_ptr<ResultSet> &resultSet);
46
47 static const std::string DATABASE_NAME;
48 static std::shared_ptr<RdbStore> store;
49 };
50
51 const std::string RdbStorePredicateJoinTest::DATABASE_NAME = RDB_TEST_PATH + "predicates_join_test.db";
52 std::shared_ptr<RdbStore> RdbStorePredicateJoinTest::store = nullptr;
53 const std::string CREATE_TABLE_USER_SQL = "CREATE TABLE IF NOT EXISTS user "
54 "(userId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT,"
55 "age INTEGER , balance REAL NOT NULL)";
56 const std::string CREATE_TABLE_BOOK_SQL = "CREATE TABLE IF NOT EXISTS book "
57 "(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, userId INTEGER,"
58 "FOREIGN KEY (userId) REFERENCES user (userId) "
59 "ON UPDATE NO ACTION ON DELETE CASCADE)";
60
61 class PredicateJoinTestOpenCallback : public RdbOpenCallback {
62 public:
63 int OnCreate(RdbStore &store) override;
64 int OnUpgrade(RdbStore &store, int oldVersion, int newVersion) override;
65 };
66
67
OnCreate(RdbStore & store)68 int PredicateJoinTestOpenCallback::OnCreate(RdbStore &store)
69 {
70 return E_OK;
71 }
72
OnUpgrade(RdbStore & store,int oldVersion,int newVersion)73 int PredicateJoinTestOpenCallback::OnUpgrade(RdbStore &store, int oldVersion, int newVersion)
74 {
75 return E_OK;
76 }
77
SetUpTestCase(void)78 void RdbStorePredicateJoinTest::SetUpTestCase(void) {}
79
TearDownTestCase(void)80 void RdbStorePredicateJoinTest::TearDownTestCase(void) {}
81
SetUp(void)82 void RdbStorePredicateJoinTest::SetUp(void)
83 {
84 int errCode = E_OK;
85 RdbStoreConfig config(RdbStorePredicateJoinTest::DATABASE_NAME);
86 PredicateJoinTestOpenCallback helper;
87 RdbStorePredicateJoinTest::store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
88 EXPECT_NE(RdbStorePredicateJoinTest::store, nullptr);
89 RdbStorePredicateJoinTest::GenerateAllTables();
90 }
91
TearDown(void)92 void RdbStorePredicateJoinTest::TearDown(void)
93 {
94 RdbHelper::DeleteRdbStore(RdbStorePredicateJoinTest::DATABASE_NAME);
95 }
96
GenerateAllTables()97 void RdbStorePredicateJoinTest::GenerateAllTables()
98 {
99 RdbStorePredicateJoinTest::store->ExecuteSql(CREATE_TABLE_USER_SQL);
100 RdbStorePredicateJoinTest::InsertUserDates();
101
102 RdbStorePredicateJoinTest::store->ExecuteSql(CREATE_TABLE_BOOK_SQL);
103 RdbStorePredicateJoinTest::InsertBookDates();
104 }
105
InsertUserDates()106 void RdbStorePredicateJoinTest::InsertUserDates()
107 {
108 int64_t id;
109 ValuesBucket values;
110
111 values.PutInt("userId", 1);
112 values.PutString("firstName", std::string("Zhang"));
113 values.PutString("lastName", std::string("San"));
114 values.PutInt("age", 29);
115 values.PutDouble("balance", 100.51);
116 store->Insert(id, "user", values);
117
118 values.Clear();
119 values.PutInt("userId", 2);
120 values.PutString("firstName", std::string("Li"));
121 values.PutString("lastName", std::string("Si"));
122 values.PutInt("age", 30);
123 values.PutDouble("balance", 200.51);
124 store->Insert(id, "user", values);
125
126 values.Clear();
127 values.PutInt("userId", 3);
128 values.PutString("firstName", std::string("Wang"));
129 values.PutString("lastName", std::string("Wu"));
130 values.PutInt("age", 30);
131 values.PutDouble("balance", 300.51);
132 store->Insert(id, "user", values);
133
134 values.Clear();
135 values.PutInt("userId", 4);
136 values.PutString("firstName", std::string("Sun"));
137 values.PutString("lastName", std::string("Liu"));
138 values.PutInt("age", 31);
139 values.PutDouble("balance", 400.51);
140 store->Insert(id, "user", values);
141
142 values.Clear();
143 values.PutInt("userId", 5);
144 values.PutString("firstName", std::string("Ma"));
145 values.PutString("lastName", std::string("Qi"));
146 values.PutInt("age", 32);
147 values.PutDouble("balance", 500.51);
148 store->Insert(id, "user", values);
149 }
150
InsertBookDates()151 void RdbStorePredicateJoinTest::InsertBookDates()
152 {
153 int64_t id;
154 ValuesBucket values;
155
156 values.PutInt("id", 1);
157 values.PutString("name", std::string("SanGuo"));
158 values.PutInt("userId", 1);
159 store->Insert(id, "book", values);
160
161 values.Clear();
162 values.PutInt("id", 2);
163 values.PutString("name", std::string("XiYouJi"));
164 values.PutInt("userId", 2);
165 store->Insert(id, "book", values);
166
167 values.Clear();
168 values.PutInt("id", 3);
169 values.PutString("name", std::string("ShuiHuZhuan"));
170 values.PutInt("userId", 3);
171 store->Insert(id, "book", values);
172 }
173
ResultSize(std::shared_ptr<ResultSet> & resultSet)174 int RdbStorePredicateJoinTest::ResultSize(std::shared_ptr<ResultSet> &resultSet)
175 {
176 if (resultSet->GoToFirstRow() != E_OK) {
177 return 0;
178 }
179 int count = 1;
180 while (resultSet->GoToNextRow() == E_OK) {
181 count++;
182 }
183 return count;
184 }
185
186 /* *
187 * @tc.name: RdbStore_CrossJoin_001
188 * @tc.desc: Normal testCase of RdbPredicates for CrossJoin
189 * @tc.type: FUNC
190 */
191 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_CrossJoin_001, TestSize.Level1)
192 {
193 RdbPredicates predicates("user");
194
195 std::vector<std::string> clauses;
196 clauses.push_back("user.userId = book.userId");
197 predicates.CrossJoin("book")->On(clauses);
198
199 std::vector<std::string> joinTypes;
200 joinTypes.push_back("CROSS JOIN");
201 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
202 EXPECT_EQ("book", predicates.GetJoinTableNames()[0]);
203 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
204 EXPECT_EQ("user CROSS JOIN book ON(user.userId = book.userId)", predicates.GetJoinClause());
205
206 std::vector<std::string> columns;
207 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
208 EXPECT_EQ(3, ResultSize(allDataTypes));
209
210 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
211 int userId;
212 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
213 EXPECT_EQ(1, userId);
214
215 std::string firstName;
216 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
217 EXPECT_EQ("Zhang", firstName);
218
219 std::string lastName;
220 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
221 EXPECT_EQ("San", lastName);
222
223 int age;
224 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
225 EXPECT_EQ(29, age);
226
227 double balance;
228 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
229 EXPECT_EQ(100.51, balance);
230
231 int id;
232 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
233 EXPECT_EQ(1, id);
234
235 std::string name;
236 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
237 EXPECT_EQ("SanGuo", name);
238
239 int userId_1;
240 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, userId_1));
241 EXPECT_EQ(1, userId_1);
242 }
243
244 /* *
245 * @tc.name: RdbStore_InnerJoin_002
246 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
247 * @tc.type: FUNC
248 */
249 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_InnerJoin_002, TestSize.Level1)
250 {
251 RdbPredicates predicates("user");
252
253 std::vector<std::string> clauses;
254 clauses.push_back("user.userId = book.userId");
255 predicates.InnerJoin("book")->On(clauses)->EqualTo("book.name", "SanGuo");
256
257 std::vector<std::string> joinTypes;
258 joinTypes.push_back("INNER JOIN");
259 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
260 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
261
262 std::vector<std::string> columns;
263 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
264 EXPECT_EQ(1, ResultSize(allDataTypes));
265 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
266
267 int userId;
268 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
269 EXPECT_EQ(1, userId);
270
271 std::string firstName;
272 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
273 EXPECT_EQ("Zhang", firstName);
274
275 std::string lastName;
276 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
277 EXPECT_EQ("San", lastName);
278
279 int age;
280 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
281 EXPECT_EQ(29, age);
282
283 double balance;
284 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
285 EXPECT_EQ(100.51, balance);
286
287 int id;
288 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
289 EXPECT_EQ(1, id);
290
291 std::string name;
292 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
293 EXPECT_EQ("SanGuo", name);
294
295 int userId_1;
296 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, userId_1));
297 EXPECT_EQ(1, userId_1);
298 }
299
300 /* *
301 * @tc.name: RdbStore_LeftOuterJoin_003
302 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
303 * @tc.type: FUNC
304 */
305 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_003, TestSize.Level1)
306 {
307 RdbPredicates predicates("user");
308
309 std::vector<std::string> fields;
310 fields.push_back("userId");
311 predicates.LeftOuterJoin("book")->Using(fields)->EqualTo("name", "SanGuo");
312
313 std::vector<std::string> joinTypes;
314 joinTypes.push_back("LEFT OUTER JOIN");
315 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
316 EXPECT_EQ("USING(userId)", predicates.GetJoinConditions()[0]);
317
318 std::vector<std::string> columns;
319 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
320 EXPECT_EQ(1, ResultSize(allDataTypes));
321
322 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
323
324 int userId;
325 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
326 EXPECT_EQ(1, userId);
327
328 std::string firstName;
329 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
330 EXPECT_EQ("Zhang", firstName);
331
332 std::string lastName;
333 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
334 EXPECT_EQ("San", lastName);
335
336 int age;
337 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
338 EXPECT_EQ(29, age);
339
340 double balance;
341 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
342 EXPECT_EQ(100.51, balance);
343
344 int id;
345 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
346 EXPECT_EQ(1, id);
347
348 std::string name;
349 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
350 EXPECT_EQ("SanGuo", name);
351 }
352
353 /* *
354 * @tc.name: RdbStore_LeftOuterJoin_004
355 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
356 * @tc.type: FUNC
357 */
358 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_004, TestSize.Level1)
359 {
360 RdbPredicates predicates("user");
361
362 std::vector<std::string> clauses;
363 clauses.push_back("user.userId = book.userId");
364 std::vector<std::string> joinTypes;
365 joinTypes.push_back("LEFT OUTER JOIN");
366
367 predicates.LeftOuterJoin("book")->On(clauses);
368 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
369 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
370
371 std::vector<std::string> columns;
372 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
373 EXPECT_EQ(5, ResultSize(allDataTypes));
374 }
375
376 /* *
377 * @tc.name: RdbStore_LeftOuterJoin_005
378 * @tc.desc: Abnormal testCase of RdbPredicates for LeftOuterJoin, if tableName is ""
379 * @tc.type: FUNC
380 */
381 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_005, TestSize.Level1)
382 {
383 RdbPredicates predicates("user");
384
385 std::vector<std::string> clauses;
386 clauses.push_back("user.userId = book.userId");
387 std::vector<std::string> joinTypes;
388
389 predicates.LeftOuterJoin("")->On(clauses);
390 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
391 EXPECT_EQ(joinTypes, predicates.GetJoinConditions());
392
393 std::vector<std::string> columns;
394 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
395 EXPECT_EQ(5, ResultSize(allDataTypes));
396 allDataTypes->Close();
397 }
398
399 /* *
400 * @tc.name: RdbStore_LeftOuterJoin_006
401 * @tc.desc: Abnormal testCase of RdbPredicates for LeftOuterJoin, if the join condition is []
402 * @tc.type: FUNC
403 */
404 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_006, TestSize.Level1)
405 {
406 RdbPredicates predicates("user");
407
408 std::vector<std::string> clauses;
409 std::vector<std::string> joinTypes;
410 joinTypes.push_back("LEFT OUTER JOIN");
411
412 predicates.LeftOuterJoin("book")->On(clauses);
413 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
414 EXPECT_EQ(clauses, predicates.GetJoinConditions());
415 }
416
417 /* *
418 * @tc.name: RdbStore_LeftOuterJoin_007
419 * @tc.desc: Abnormal testCase of RdbPredicates for LeftOuterJoin, if fields is []
420 * @tc.type: FUNC
421 */
422 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_007, TestSize.Level1)
423 {
424 RdbPredicates predicates("user");
425
426 std::vector<std::string> fields;
427 predicates.LeftOuterJoin("book")->Using(fields)->EqualTo("name", "SanGuo");
428
429 std::vector<std::string> joinTypes;
430 joinTypes.push_back("LEFT OUTER JOIN");
431 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
432 EXPECT_EQ(fields, predicates.GetJoinConditions());
433 }
434
435 /* *
436 * @tc.name: RdbStore_LeftOuterJoin_008
437 * @tc.desc: Abnormal testCase of RdbPredicates for LeftOuterJoin, if tableName is ""
438 * @tc.type: FUNC
439 */
440 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_008, TestSize.Level1)
441 {
442 RdbPredicates predicates("user");
443
444 std::vector<std::string> fields;
445 fields.push_back("userId");
446 predicates.LeftOuterJoin("")->Using(fields)->EqualTo("name", "SanGuo");
447
448 std::vector<std::string> joinTypes;
449 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
450 EXPECT_EQ(joinTypes, predicates.GetJoinConditions());
451
452 std::vector<std::string> columns;
453 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
454 EXPECT_EQ(0, ResultSize(allDataTypes));
455 allDataTypes->Close();
456 }
457
458 /* *
459 * @tc.name: RdbStore_LeftOuterJoin_009
460 * @tc.desc: Abnormal testCase of RdbPredicates for LeftOuterJoin, if join count rather than 1
461 * @tc.type: FUNC
462 */
463 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_009, TestSize.Level1)
464 {
465 RdbPredicates predicates("user");
466
467 std::vector<std::string> fields;
468 fields.push_back("userId");
469 predicates.LeftOuterJoin("book")->LeftOuterJoin("book");
470 EXPECT_EQ(2, predicates.GetJoinCount());
471 predicates.Using(fields)->EqualTo("name", "SanGuo");
472
473 std::vector<std::string> joinTypes{"LEFT OUTER JOIN", "LEFT OUTER JOIN"};
474 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
475 EXPECT_EQ(0, predicates.GetJoinCount());
476
477 std::vector<std::string> columns;
478 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
479 EXPECT_NE(allDataTypes, nullptr);
480 allDataTypes->Close();
481 }