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