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 constexpr int CYCLENUM = 13;
37
38 class RdbStorePredicateJoinBTest : public testing::Test {
39 public:
40 static void SetUpTestCase(void);
41 static void TearDownTestCase(void);
42 void SetUp();
43 void TearDown();
44 void GenerateAllTables();
45 void InsertDeptDates();
46 void InsertJobDates();
47 void InsertEmpDates();
48 void InsertSalarygradeDates();
49 int ResultSize(std::shared_ptr<ResultSet> &resultSet);
50
51 static const std::string DATABASE_NAME;
52 static std::shared_ptr<RdbStore> store;
53 };
54
55 const std::string RdbStorePredicateJoinBTest::DATABASE_NAME = RDB_TEST_PATH + "predicates_join_b_test.db";
56 std::shared_ptr<RdbStore> RdbStorePredicateJoinBTest::store = nullptr;
57 const std::string CREATE_TABLE_DEPT_SQL = std::string("CREATE TABLE IF NOT EXISTS dept ") +
58 std::string("(id INTEGER PRIMARY KEY , dName TEXT , loc TEXT)");
59 const std::string CREATE_TABLE_JOB_SQL = std::string("CREATE TABLE IF NOT EXISTS job ") +
60 std::string("(id INTEGER PRIMARY KEY , jName TEXT , description TEXT)");
61 const std::string CREATE_TABLE_EMP_SQL = std::string("CREATE TABLE IF NOT EXISTS emp ") +
62 std::string("(id INTEGER PRIMARY KEY ,eName TEXT, jobId INTEGER , ") +
63 std::string("mgr INTEGER, joinDate TEXT, salary REAL, bonus REAL, deptId INTEGER,") +
64 std::string("FOREIGN KEY (jobId) REFERENCES job (id) ON UPDATE NO ACTION ON DELETE CASCADE,") +
65 std::string("FOREIGN KEY (deptId) REFERENCES dept (id) ON UPDATE NO ACTION ON DELETE CASCADE)");
66 const std::string CREATE_TABLE_SALARYGRADE_SQL = std::string("CREATE TABLE IF NOT EXISTS salarygrade") +
67 std::string("(grade INTEGER PRIMARY KEY,loSalary INTEGER, hiSalary INTEGER)");
68
69
70 class PredicateJoinBTestOpenCallback : public RdbOpenCallback {
71 public:
72 int OnCreate(RdbStore &store) override;
73 int OnUpgrade(RdbStore &store, int oldVersion, int newVersion) override;
74 };
75
76
OnCreate(RdbStore & store)77 int PredicateJoinBTestOpenCallback::OnCreate(RdbStore &store)
78 {
79 return E_OK;
80 }
81
OnUpgrade(RdbStore & store,int oldVersion,int newVersion)82 int PredicateJoinBTestOpenCallback::OnUpgrade(RdbStore &store, int oldVersion, int newVersion)
83 {
84 return E_OK;
85 }
86
SetUpTestCase(void)87 void RdbStorePredicateJoinBTest::SetUpTestCase(void) {}
88
TearDownTestCase(void)89 void RdbStorePredicateJoinBTest::TearDownTestCase(void) {}
90
SetUp(void)91 void RdbStorePredicateJoinBTest::SetUp(void)
92 {
93 int errCode = E_OK;
94 RdbStoreConfig config(RdbStorePredicateJoinBTest::DATABASE_NAME);
95 PredicateJoinBTestOpenCallback helper;
96 RdbStorePredicateJoinBTest::store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
97 EXPECT_NE(RdbStorePredicateJoinBTest::store, nullptr);
98 RdbStorePredicateJoinBTest::GenerateAllTables();
99 }
100
TearDown(void)101 void RdbStorePredicateJoinBTest::TearDown(void)
102 {
103 RdbHelper::DeleteRdbStore(RdbStorePredicateJoinBTest::DATABASE_NAME);
104 }
105
GenerateAllTables()106 void RdbStorePredicateJoinBTest::GenerateAllTables()
107 {
108 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_DEPT_SQL);
109 RdbStorePredicateJoinBTest::InsertDeptDates();
110
111 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_JOB_SQL);
112 RdbStorePredicateJoinBTest::InsertJobDates();
113
114 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_EMP_SQL);
115 RdbStorePredicateJoinBTest::InsertEmpDates();
116
117 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_SALARYGRADE_SQL);
118 RdbStorePredicateJoinBTest::InsertSalarygradeDates();
119 }
120
InsertDeptDates()121 void RdbStorePredicateJoinBTest::InsertDeptDates()
122 {
123 int64_t id;
124 ValuesBucket values;
125
126 values.PutInt("id", 10);
127 values.PutString("dName", std::string("JiaoYanBU"));
128 values.PutString("loc", std::string("BeiJing"));
129 store->Insert(id, "dept", values);
130
131 values.Clear();
132 values.PutInt("id", 20);
133 values.PutString("dName", std::string("XueGongBu"));
134 values.PutString("loc", std::string("ShangHai"));
135 store->Insert(id, "dept", values);
136
137 values.Clear();
138 values.PutInt("id", 30);
139 values.PutString("dName", std::string("XiaoShouBu"));
140 values.PutString("loc", std::string("GuangZhou"));
141 store->Insert(id, "dept", values);
142
143 values.Clear();
144 values.PutInt("id", 40);
145 values.PutString("dName", std::string("CaiWuBu"));
146 values.PutString("loc", std::string("ShenZhen"));
147 store->Insert(id, "dept", values);
148 }
149
InsertJobDates()150 void RdbStorePredicateJoinBTest::InsertJobDates()
151 {
152 int64_t id;
153 ValuesBucket values;
154
155 values.PutInt("id", 1);
156 values.PutString("jName", std::string("Chairman"));
157 values.PutString("description", std::string("ManageTheEntireCompany"));
158 store->Insert(id, "job", values);
159
160 values.Clear();
161 values.PutInt("id", 2);
162 values.PutString("jName", std::string("Manager"));
163 values.PutString("description", std::string("ManageEmployeesOfTheDepartment"));
164 store->Insert(id, "job", values);
165
166 values.Clear();
167 values.PutInt("id", 3);
168 values.PutString("jName", std::string("Salesperson"));
169 values.PutString("description", std::string("SellingProductsToCustomers"));
170 store->Insert(id, "job", values);
171
172 values.Clear();
173 values.PutInt("id", 4);
174 values.PutString("jName", std::string("Clerk"));
175 values.PutString("description", std::string("UseOfficeSoftware"));
176 store->Insert(id, "job", values);
177 }
178
InsertEmpDates()179 void RdbStorePredicateJoinBTest::InsertEmpDates()
180 {
181 int64_t id;
182
183 int ret = 0;
184 for (int i = 0; i <= CYCLENUM; i++) {
185 ret = store->Insert(id, "emp", UTUtils::SetRowDatas(UTUtils::gRowDatas[i]));
186 }
187 EXPECT_EQ(ret, E_OK);
188 }
189
InsertSalarygradeDates()190 void RdbStorePredicateJoinBTest::InsertSalarygradeDates()
191 {
192 int64_t id;
193 ValuesBucket values;
194
195 values.PutInt("grade", 1);
196 values.PutInt("loSalary", 7000);
197 values.PutInt("hiSalary", 12000);
198 store->Insert(id, "salarygrade", values);
199
200 values.Clear();
201 values.PutInt("grade", 2);
202 values.PutInt("loSalary", 12010);
203 values.PutInt("hiSalary", 14000);
204 store->Insert(id, "salarygrade", values);
205
206 values.Clear();
207 values.PutInt("grade", 3);
208 values.PutInt("loSalary", 14010);
209 values.PutInt("hiSalary", 20000);
210 store->Insert(id, "salarygrade", values);
211
212 values.Clear();
213 values.PutInt("grade", 4);
214 values.PutInt("loSalary", 20010);
215 values.PutInt("hiSalary", 30000);
216 store->Insert(id, "salarygrade", values);
217
218 values.Clear();
219 values.PutInt("grade", 5);
220 values.PutInt("loSalary", 30010);
221 values.PutInt("hiSalary", 99990);
222 store->Insert(id, "salarygrade", values);
223 }
224
ResultSize(std::shared_ptr<ResultSet> & resultSet)225 int RdbStorePredicateJoinBTest::ResultSize(std::shared_ptr<ResultSet> &resultSet)
226 {
227 if (resultSet->GoToFirstRow() != E_OK) {
228 return 0;
229 }
230 int count;
231 resultSet->GetRowCount(count);
232 return count;
233 }
234
235 /* *
236 * @tc.name: RdbStore_InnerJoinB_001
237 * @tc.desc: Normal testCase of RdbPredicates for CrossJoin
238 * @tc.type: FUNC
239 */
240 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_CrossJoinB_001, TestSize.Level1)
241 {
242 RdbPredicates predicates("emp");
243
244 std::vector<std::string> clauses;
245 clauses.push_back("emp.deptId = dept.id");
246 predicates.CrossJoin("dept")->On(clauses);
247
248 std::vector<std::string> joinTypes;
249 joinTypes.push_back("CROSS JOIN");
250
251 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
252 EXPECT_EQ("dept", predicates.GetJoinTableNames()[0]);
253 EXPECT_EQ("ON(emp.deptId = dept.id)", predicates.GetJoinConditions()[0]);
254 EXPECT_EQ("emp CROSS JOIN dept ON(emp.deptId = dept.id)", predicates.GetJoinClause());
255
256 std::vector<std::string> columns;
257 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
258 EXPECT_EQ(14, ResultSize(allDataTypes));
259
260 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
261
262 int id;
263 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, id));
264 EXPECT_EQ(1001, id);
265
266 std::string eName;
267 EXPECT_EQ(E_OK, allDataTypes->GetString(1, eName));
268 EXPECT_EQ("SunWuKong", eName);
269
270 int jobId;
271 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, jobId));
272 EXPECT_EQ(4, jobId);
273
274 int mgr;
275 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, mgr));
276 EXPECT_EQ(1004, mgr);
277
278 std::string joinDate;
279 EXPECT_EQ(E_OK, allDataTypes->GetString(4, joinDate));
280 EXPECT_EQ("2000-12-17", joinDate);
281
282 double salary;
283 EXPECT_EQ(E_OK, allDataTypes->GetDouble(5, salary));
284 EXPECT_EQ(8000.00, salary);
285
286 bool bonus;
287 EXPECT_EQ(E_OK, allDataTypes->IsColumnNull(6, bonus));
288 EXPECT_EQ(true, bonus);
289
290 int deptId;
291 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, deptId));
292 EXPECT_EQ(20, deptId);
293
294 int id_1;
295 EXPECT_EQ(E_OK, allDataTypes->GetInt(8, id_1));
296 EXPECT_EQ(20, id_1);
297
298 std::string dName;
299 EXPECT_EQ(E_OK, allDataTypes->GetString(9, dName));
300 EXPECT_EQ("XueGongBu", dName);
301
302 std::string loc;
303 EXPECT_EQ(E_OK, allDataTypes->GetString(10, loc));
304 EXPECT_EQ("ShangHai", loc);
305 }
306
307 /* *
308 * @tc.name: RdbStore_InnerJoinB_002
309 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
310 * @tc.type: FUNC
311 */
312 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_002, TestSize.Level1)
313 {
314 RdbPredicates predicates("emp t1");
315
316 std::vector<std::string> clauses;
317 clauses.push_back("t1.jobId = t2.id");
318 predicates.InnerJoin("job t2")->On(clauses)->EqualTo("t1.eName", "SunWuKong");
319
320 std::vector<std::string> joinTypes;
321 joinTypes.push_back("INNER JOIN");
322 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
323 EXPECT_EQ("ON(t1.jobId = t2.id)", predicates.GetJoinConditions()[0]);
324
325 std::vector<std::string> columns;
326 columns.push_back("t1.id");
327 columns.push_back("t1.eName");
328 columns.push_back("t1.salary");
329 columns.push_back("t2.jName");
330 columns.push_back("t2.description");
331 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
332 EXPECT_EQ(1, ResultSize(allDataTypes));
333 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
334
335 int id;
336 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, id));
337 EXPECT_EQ(1001, id);
338
339 std::string eName;
340 EXPECT_EQ(E_OK, allDataTypes->GetString(1, eName));
341 EXPECT_EQ("SunWuKong", eName);
342
343 double salary;
344 EXPECT_EQ(E_OK, allDataTypes->GetDouble(2, salary));
345 EXPECT_EQ(8000.00, salary);
346
347 std::string jName;
348 EXPECT_EQ(E_OK, allDataTypes->GetString(3, jName));
349 EXPECT_EQ("Clerk", jName);
350
351 std::string description;
352 EXPECT_EQ(E_OK, allDataTypes->GetString(4, description));
353 EXPECT_EQ("UseOfficeSoftware", description);
354 }
355
356 /* *
357 * @tc.name: RdbStore_InnerJoinB_003
358 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
359 * @tc.type: FUNC
360 */
361 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_003, TestSize.Level1)
362 {
363 RdbPredicates predicates("emp t1");
364
365 std::vector<std::string> clauses;
366 clauses.push_back("t1.salary BETWEEN t2.losalary AND t2.hisalary");
367 predicates.InnerJoin("salarygrade t2")->On(clauses);
368
369 std::vector<std::string> joinTypes;
370 joinTypes.push_back("INNER JOIN");
371 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
372
373 std::vector<std::string> columns;
374 columns.push_back("t1.eName");
375 columns.push_back("t1.salary");
376 columns.push_back("t2.*");
377 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
378 EXPECT_EQ(14, ResultSize(allDataTypes));
379 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
380
381 std::string eName;
382 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
383 EXPECT_EQ("SunWuKong", eName);
384
385 double salary;
386 EXPECT_EQ(E_OK, allDataTypes->GetDouble(1, salary));
387 EXPECT_EQ(8000.00, salary);
388
389 int grade;
390 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, grade));
391 EXPECT_EQ(1, grade);
392
393 int loSalary;
394 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, loSalary));
395 EXPECT_EQ(7000, loSalary);
396
397 int hiSalary;
398 EXPECT_EQ(E_OK, allDataTypes->GetInt(4, hiSalary));
399 EXPECT_EQ(12000, hiSalary);
400 }
401
402
403 /* *
404 * @tc.name: RdbStore_InnerJoinB_004
405 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
406 * @tc.type: FUNC
407 */
408 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_004, TestSize.Level1)
409 {
410 RdbPredicates predicates("emp t1");
411
412 std::vector<std::string> clauses;
413 clauses.push_back("t1.jobId = t2.id");
414 clauses.push_back("t1.deptId = t3.id");
415 clauses.push_back("t1.salary BETWEEN t4.losalary AND t4.hisalary");
416 predicates.InnerJoin("job t2")->InnerJoin("dept t3")->InnerJoin("salarygrade t4")->On(clauses);
417
418 std::vector<std::string> joinTypes;
419 joinTypes.push_back("INNER JOIN");
420 joinTypes.push_back("INNER JOIN");
421 joinTypes.push_back("INNER JOIN");
422 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
423 EXPECT_EQ("", predicates.GetJoinConditions()[0]);
424 EXPECT_EQ("", predicates.GetJoinConditions()[1]);
425 EXPECT_EQ("ON(t1.jobId = t2.id AND t1.deptId = t3.id AND t1.salary BETWEEN "
426 "t4.losalary AND t4.hisalary)",
427 predicates.GetJoinConditions()[2]);
428
429 std::vector<std::string> columns;
430 columns.push_back("t1.eName");
431 columns.push_back("t1.salary");
432 columns.push_back("t2.jName");
433 columns.push_back("t2.description");
434 columns.push_back("t3.dName");
435 columns.push_back("t3.loc");
436 columns.push_back("t4.grade");
437 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
438 EXPECT_EQ(14, ResultSize(allDataTypes));
439 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
440
441 std::string eName;
442 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
443 EXPECT_EQ("SunWuKong", eName);
444
445 double salary;
446 EXPECT_EQ(E_OK, allDataTypes->GetDouble(1, salary));
447 EXPECT_EQ(8000.00, salary);
448
449 std::string jName;
450 EXPECT_EQ(E_OK, allDataTypes->GetString(2, jName));
451 EXPECT_EQ("Clerk", jName);
452
453 std::string description;
454 EXPECT_EQ(E_OK, allDataTypes->GetString(3, description));
455 EXPECT_EQ("UseOfficeSoftware", description);
456
457 std::string dName;
458 EXPECT_EQ(E_OK, allDataTypes->GetString(4, dName));
459 EXPECT_EQ("XueGongBu", dName);
460
461 std::string loc;
462 EXPECT_EQ(E_OK, allDataTypes->GetString(5, loc));
463 EXPECT_EQ("ShangHai", loc);
464
465 int grade;
466 EXPECT_EQ(E_OK, allDataTypes->GetInt(6, grade));
467 EXPECT_EQ(1, grade);
468 }
469
470
471 /* *
472 * @tc.name: RdbStore_LeftOuterJoinB_005
473 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
474 * @tc.type: FUNC
475 */
476 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_LeftOuterJoinB_005, TestSize.Level1)
477 {
478 RdbPredicates predicates("emp t1");
479
480 std::vector<std::string> clauses;
481 clauses.push_back("t1.mgr = t2.id");
482 std::vector<std::string> joinTypes;
483 joinTypes.push_back("LEFT OUTER JOIN");
484
485 predicates.LeftOuterJoin("emp t2")->On(clauses);
486 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
487 EXPECT_EQ("ON(t1.mgr = t2.id)", predicates.GetJoinConditions()[0]);
488
489 std::vector<std::string> columns;
490 columns.push_back("t1.eName");
491 columns.push_back("t1.mgr");
492 columns.push_back("t2.id");
493 columns.push_back("t2.eName");
494 std::shared_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
495 EXPECT_EQ(14, ResultSize(allDataTypes));
496 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
497
498 std::string eName;
499 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
500 EXPECT_EQ("SunWuKong", eName);
501
502 int mgr;
503 EXPECT_EQ(E_OK, allDataTypes->GetInt(1, mgr));
504 EXPECT_EQ(1004, mgr);
505
506 int id;
507 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, id));
508 EXPECT_EQ(1004, id);
509
510 std::string eName_1;
511 EXPECT_EQ(E_OK, allDataTypes->GetString(3, eName_1));
512 EXPECT_EQ("TangCeng", eName_1);
513 }
514