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 RdbStorePredicateJoinBTest : 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 InsertDeptDates();
44 void InsertJobDates();
45 void InsertEmpDates();
46 void InsertSalarygradeDates();
47 int ResultSize(std::unique_ptr<ResultSet> &resultSet);
48
49 static const std::string DATABASE_NAME;
50 static std::shared_ptr<RdbStore> store;
51 };
52
53 const std::string RdbStorePredicateJoinBTest::DATABASE_NAME = RDB_TEST_PATH + "predicates_join_b_test.db";
54 std::shared_ptr<RdbStore> RdbStorePredicateJoinBTest::store = nullptr;
55 const std::string CREATE_TABLE_DEPT_SQL = std::string("CREATE TABLE IF NOT EXISTS dept ") +
56 std::string("(id INTEGER PRIMARY KEY , dName TEXT , loc TEXT)");
57 const std::string CREATE_TABLE_JOB_SQL = std::string("CREATE TABLE IF NOT EXISTS job ") +
58 std::string("(id INTEGER PRIMARY KEY , jName TEXT , description TEXT)");
59 const std::string CREATE_TABLE_EMP_SQL = std::string("CREATE TABLE IF NOT EXISTS emp ") +
60 std::string("(id INTEGER PRIMARY KEY ,eName TEXT, jobId INTEGER , ") +
61 std::string("mgr INTEGER, joinDate TEXT, salary REAL, bonus REAL, deptId INTEGER,") +
62 std::string("FOREIGN KEY (jobId) REFERENCES job (id) ON UPDATE NO ACTION ON DELETE CASCADE,") +
63 std::string("FOREIGN KEY (deptId) REFERENCES dept (id) ON UPDATE NO ACTION ON DELETE CASCADE)");
64 const std::string CREATE_TABLE_SALARYGRADE_SQL = std::string("CREATE TABLE IF NOT EXISTS salarygrade") +
65 std::string("(grade INTEGER PRIMARY KEY,loSalary INTEGER, hiSalary INTEGER)");
66
67
68 class PredicateJoinBTestOpenCallback : public RdbOpenCallback {
69 public:
70 int OnCreate(RdbStore &rdbStore) override;
71 int OnUpgrade(RdbStore &rdbStore, int oldVersion, int newVersion) override;
72 };
73
74
OnCreate(RdbStore & store)75 int PredicateJoinBTestOpenCallback::OnCreate(RdbStore &store)
76 {
77 return E_OK;
78 }
79
OnUpgrade(RdbStore & store,int oldVersion,int newVersion)80 int PredicateJoinBTestOpenCallback::OnUpgrade(RdbStore &store, int oldVersion, int newVersion)
81 {
82 return E_OK;
83 }
84
SetUpTestCase(void)85 void RdbStorePredicateJoinBTest::SetUpTestCase(void) {}
86
TearDownTestCase(void)87 void RdbStorePredicateJoinBTest::TearDownTestCase(void) {}
88
SetUp(void)89 void RdbStorePredicateJoinBTest::SetUp(void)
90 {
91 int errCode = E_OK;
92 RdbStoreConfig config(RdbStorePredicateJoinBTest::DATABASE_NAME);
93 PredicateJoinBTestOpenCallback helper;
94 RdbStorePredicateJoinBTest::store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
95 EXPECT_NE(RdbStorePredicateJoinBTest::store, nullptr);
96 RdbStorePredicateJoinBTest::GenerateAllTables();
97 }
98
TearDown(void)99 void RdbStorePredicateJoinBTest::TearDown(void)
100 {
101 RdbHelper::DeleteRdbStore(RdbStorePredicateJoinBTest::DATABASE_NAME);
102 }
103
GenerateAllTables()104 void RdbStorePredicateJoinBTest::GenerateAllTables()
105 {
106 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_DEPT_SQL);
107 RdbStorePredicateJoinBTest::InsertDeptDates();
108
109 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_JOB_SQL);
110 RdbStorePredicateJoinBTest::InsertJobDates();
111
112 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_EMP_SQL);
113 RdbStorePredicateJoinBTest::InsertEmpDates();
114
115 RdbStorePredicateJoinBTest::store->ExecuteSql(CREATE_TABLE_SALARYGRADE_SQL);
116 RdbStorePredicateJoinBTest::InsertSalarygradeDates();
117 }
118
InsertDeptDates()119 void RdbStorePredicateJoinBTest::InsertDeptDates()
120 {
121 int64_t id;
122 ValuesBucket values;
123
124 values.PutInt("id", 10);
125 values.PutString("dName", std::string("JiaoYanBU"));
126 values.PutString("loc", std::string("BeiJing"));
127 store->Insert(id, "dept", values);
128
129 values.Clear();
130 values.PutInt("id", 20);
131 values.PutString("dName", std::string("XueGongBu"));
132 values.PutString("loc", std::string("ShangHai"));
133 store->Insert(id, "dept", values);
134
135 values.Clear();
136 values.PutInt("id", 30);
137 values.PutString("dName", std::string("XiaoShouBu"));
138 values.PutString("loc", std::string("GuangZhou"));
139 store->Insert(id, "dept", values);
140
141 values.Clear();
142 values.PutInt("id", 40);
143 values.PutString("dName", std::string("CaiWuBu"));
144 values.PutString("loc", std::string("ShenZhen"));
145 store->Insert(id, "dept", values);
146 }
147
InsertJobDates()148 void RdbStorePredicateJoinBTest::InsertJobDates()
149 {
150 int64_t id;
151 ValuesBucket values;
152
153 values.PutInt("id", 1);
154 values.PutString("jName", std::string("Chairman"));
155 values.PutString("description", std::string("ManageTheEntireCompany"));
156 store->Insert(id, "job", values);
157
158 values.Clear();
159 values.PutInt("id", 2);
160 values.PutString("jName", std::string("Manager"));
161 values.PutString("description", std::string("ManageEmployeesOfTheDepartment"));
162 store->Insert(id, "job", values);
163
164 values.Clear();
165 values.PutInt("id", 3);
166 values.PutString("jName", std::string("Salesperson"));
167 values.PutString("description", std::string("SellingProductsToCustomers"));
168 store->Insert(id, "job", values);
169
170 values.Clear();
171 values.PutInt("id", 4);
172 values.PutString("jName", std::string("Clerk"));
173 values.PutString("description", std::string("UseOfficeSoftware"));
174 store->Insert(id, "job", values);
175 }
176
InsertEmpDates()177 void RdbStorePredicateJoinBTest::InsertEmpDates()
178 {
179 int64_t id;
180 ValuesBucket values;
181
182 values.PutInt("id", 1001);
183 values.PutString("eName", std::string("SunWuKong"));
184 values.PutInt("jobId", 4);
185 values.PutInt("mgr", 1004);
186 values.PutString("joinDate", std::string("2000-12-17"));
187 values.PutDouble("salary", 8000.00);
188 values.PutNull("bonus");
189 values.PutInt("deptId", 20);
190 store->Insert(id, "emp", values);
191
192 values.Clear();
193 values.PutInt("id", 1002);
194 values.PutString("eName", std::string("LuJunYi"));
195 values.PutInt("jobId", 3);
196 values.PutInt("mgr", 1006);
197 values.PutString("joinDate", std::string("2001-02-20"));
198 values.PutDouble("salary", 16000.00);
199 values.PutDouble("bonus", 3000.00);
200 values.PutInt("deptId", 30);
201 store->Insert(id, "emp", values);
202
203 values.Clear();
204 values.PutInt("id", 1003);
205 values.PutString("eName", std::string("LinChong"));
206 values.PutInt("jobId", 3);
207 values.PutInt("mgr", 1006);
208 values.PutString("joinDate", std::string("2001-02-22"));
209 values.PutDouble("salary", 12500.00);
210 values.PutDouble("bonus", 5000.00);
211 values.PutInt("deptId", 30);
212 store->Insert(id, "emp", values);
213
214 values.Clear();
215 values.PutInt("id", 1004);
216 values.PutString("eName", std::string("TangCeng"));
217 values.PutInt("jobId", 2);
218 values.PutInt("mgr", 1009);
219 values.PutString("joinDate", std::string("2001-04-02"));
220 values.PutDouble("salary", 29750.00);
221 values.PutNull("bonus");
222 values.PutInt("deptId", 20);
223 store->Insert(id, "emp", values);
224
225 values.Clear();
226 values.PutInt("id", 1005);
227 values.PutString("eName", std::string("LiKui"));
228 values.PutInt("jobId", 4);
229 values.PutInt("mgr", 1006);
230 values.PutString("joinDate", std::string("2001-09-28"));
231 values.PutDouble("salary", 12500.00);
232 values.PutDouble("bonus", 14000.00);
233 values.PutInt("deptId", 30);
234 store->Insert(id, "emp", values);
235
236 values.Clear();
237 values.PutInt("id", 1006);
238 values.PutString("eName", std::string("SongJiang"));
239 values.PutInt("jobId", 2);
240 values.PutInt("mgr", 1009);
241 values.PutString("joinDate", std::string("2001-05-01"));
242 values.PutDouble("salary", 28500.00);
243 values.PutNull("bonus");
244 values.PutInt("deptId", 30);
245 store->Insert(id, "emp", values);
246
247 values.Clear();
248 values.PutInt("id", 1007);
249 values.PutString("eName", std::string("LiuBei"));
250 values.PutInt("jobId", 2);
251 values.PutInt("mgr", 1009);
252 values.PutString("joinDate", std::string("2001-09-01"));
253 values.PutDouble("salary", 24500.00);
254 values.PutNull("bonus");
255 values.PutInt("deptId", 10);
256 store->Insert(id, "emp", values);
257
258 values.Clear();
259 values.PutInt("id", 1008);
260 values.PutString("eName", std::string("ZhuBaJie"));
261 values.PutInt("jobId", 4);
262 values.PutInt("mgr", 1004);
263 values.PutString("joinDate", std::string("2007-04-19"));
264 values.PutDouble("salary", 30000.00);
265 values.PutNull("bonus");
266 values.PutInt("deptId", 20);
267 store->Insert(id, "emp", values);
268
269 values.Clear();
270 values.PutInt("id", 1009);
271 values.PutString("eName", std::string("LuoGuanZhong"));
272 values.PutInt("jobId", 1);
273 values.PutNull("mgr");
274 values.PutString("joinDate", std::string("2001-11-17"));
275 values.PutDouble("salary", 50000.00);
276 values.PutNull("bonus");
277 values.PutInt("deptId", 10);
278 store->Insert(id, "emp", values);
279
280 values.Clear();
281 values.PutInt("id", 1010);
282 values.PutString("eName", std::string("WuYong"));
283 values.PutInt("jobId", 3);
284 values.PutInt("mgr", 1006);
285 values.PutString("joinDate", std::string("2001-09-08"));
286 values.PutDouble("salary", 15000.00);
287 values.PutNull("bonus");
288 values.PutInt("deptId", 30);
289 store->Insert(id, "emp", values);
290
291 values.Clear();
292 values.PutInt("id", 1011);
293 values.PutString("eName", std::string("ShaCeng"));
294 values.PutInt("jobId", 4);
295 values.PutInt("mgr", 1004);
296 values.PutString("joinDate", std::string("2007-05-23"));
297 values.PutDouble("salary", 11000.00);
298 values.PutNull("bonus");
299 values.PutInt("deptId", 20);
300 store->Insert(id, "emp", values);
301
302 values.Clear();
303 values.PutInt("id", 1012);
304 values.PutString("eName", std::string("LiKui"));
305 values.PutInt("jobId", 4);
306 values.PutInt("mgr", 1006);
307 values.PutString("joinDate", std::string("2001-12-03"));
308 values.PutDouble("salary", 9500.00);
309 values.PutNull("bonus");
310 values.PutInt("deptId", 30);
311 store->Insert(id, "emp", values);
312
313 values.Clear();
314 values.PutInt("id", 1013);
315 values.PutString("eName", std::string("XiaoBaiLong"));
316 values.PutInt("jobId", 4);
317 values.PutInt("mgr", 1004);
318 values.PutString("joinDate", std::string("2001-12-03"));
319 values.PutDouble("salary", 30000.00);
320 values.PutNull("bonus");
321 values.PutInt("deptId", 20);
322 store->Insert(id, "emp", values);
323
324 values.Clear();
325 values.PutInt("id", 1014);
326 values.PutString("eName", std::string("GuanYu"));
327 values.PutInt("jobId", 4);
328 values.PutInt("mgr", 1007);
329 values.PutString("joinDate", std::string("2002-01-23"));
330 values.PutDouble("salary", 13000.00);
331 values.PutNull("bonus");
332 values.PutInt("deptId", 10);
333 store->Insert(id, "emp", values);
334 }
335
InsertSalarygradeDates()336 void RdbStorePredicateJoinBTest::InsertSalarygradeDates()
337 {
338 int64_t id;
339 ValuesBucket values;
340
341 values.PutInt("grade", 1);
342 values.PutInt("loSalary", 7000);
343 values.PutInt("hiSalary", 12000);
344 store->Insert(id, "salarygrade", values);
345
346 values.Clear();
347 values.PutInt("grade", 2);
348 values.PutInt("loSalary", 12010);
349 values.PutInt("hiSalary", 14000);
350 store->Insert(id, "salarygrade", values);
351
352 values.Clear();
353 values.PutInt("grade", 3);
354 values.PutInt("loSalary", 14010);
355 values.PutInt("hiSalary", 20000);
356 store->Insert(id, "salarygrade", values);
357
358 values.Clear();
359 values.PutInt("grade", 4);
360 values.PutInt("loSalary", 20010);
361 values.PutInt("hiSalary", 30000);
362 store->Insert(id, "salarygrade", values);
363
364 values.Clear();
365 values.PutInt("grade", 5);
366 values.PutInt("loSalary", 30010);
367 values.PutInt("hiSalary", 99990);
368 store->Insert(id, "salarygrade", values);
369 }
370
ResultSize(std::unique_ptr<ResultSet> & resultSet)371 int RdbStorePredicateJoinBTest::ResultSize(std::unique_ptr<ResultSet> &resultSet)
372 {
373 if (resultSet->GoToFirstRow() != E_OK) {
374 return 0;
375 }
376 int count;
377 resultSet->GetRowCount(count);
378 return count;
379 }
380
381 /* *
382 * @tc.name: RdbStore_InnerJoinB_001
383 * @tc.desc: Normal testCase of RdbPredicates for CrossJoin
384 * @tc.type: FUNC
385 */
386 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_CrossJoinB_001, TestSize.Level1)
387 {
388 RdbPredicates predicates("emp");
389
390 std::vector<std::string> clauses;
391 clauses.push_back("emp.deptId = dept.id");
392 predicates.CrossJoin("dept")->On(clauses);
393
394 std::vector<std::string> joinTypes;
395 joinTypes.push_back("CROSS JOIN");
396
397 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
398 EXPECT_EQ("dept", predicates.GetJoinTableNames()[0]);
399 EXPECT_EQ("ON(emp.deptId = dept.id)", predicates.GetJoinConditions()[0]);
400 EXPECT_EQ("emp CROSS JOIN dept ON(emp.deptId = dept.id)", predicates.GetJoinClause());
401
402 std::vector<std::string> columns;
403 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
404 EXPECT_EQ(14, ResultSize(allDataTypes));
405
406 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
407
408 int id;
409 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, id));
410 EXPECT_EQ(1001, id);
411
412 std::string eName;
413 EXPECT_EQ(E_OK, allDataTypes->GetString(1, eName));
414 EXPECT_EQ("SunWuKong", eName);
415
416 int jobId;
417 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, jobId));
418 EXPECT_EQ(4, jobId);
419
420 int mgr;
421 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, mgr));
422 EXPECT_EQ(1004, mgr);
423
424 std::string joinDate;
425 EXPECT_EQ(E_OK, allDataTypes->GetString(4, joinDate));
426 EXPECT_EQ("2000-12-17", joinDate);
427
428 double salary;
429 EXPECT_EQ(E_OK, allDataTypes->GetDouble(5, salary));
430 EXPECT_EQ(8000.00, salary);
431
432 bool bonus;
433 EXPECT_EQ(E_OK, allDataTypes->IsColumnNull(6, bonus));
434 EXPECT_EQ(true, bonus);
435
436 int deptId;
437 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, deptId));
438 EXPECT_EQ(20, deptId);
439
440 int id_1;
441 EXPECT_EQ(E_OK, allDataTypes->GetInt(8, id_1));
442 EXPECT_EQ(20, id_1);
443
444 std::string dName;
445 EXPECT_EQ(E_OK, allDataTypes->GetString(9, dName));
446 EXPECT_EQ("XueGongBu", dName);
447
448 std::string loc;
449 EXPECT_EQ(E_OK, allDataTypes->GetString(10, loc));
450 EXPECT_EQ("ShangHai", loc);
451 }
452
453 /* *
454 * @tc.name: RdbStore_InnerJoinB_002
455 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
456 * @tc.type: FUNC
457 */
458 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_002, TestSize.Level1)
459 {
460 RdbPredicates predicates("emp t1");
461
462 std::vector<std::string> clauses;
463 clauses.push_back("t1.jobId = t2.id");
464 predicates.InnerJoin("job t2")->On(clauses)->EqualTo("t1.eName", "SunWuKong");
465
466 std::vector<std::string> joinTypes;
467 joinTypes.push_back("INNER JOIN");
468 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
469 EXPECT_EQ("ON(t1.jobId = t2.id)", predicates.GetJoinConditions()[0]);
470
471 std::vector<std::string> columns;
472 columns.push_back("t1.id");
473 columns.push_back("t1.eName");
474 columns.push_back("t1.salary");
475 columns.push_back("t2.jName");
476 columns.push_back("t2.description");
477 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
478 EXPECT_EQ(1, ResultSize(allDataTypes));
479 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
480
481 int id;
482 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, id));
483 EXPECT_EQ(1001, id);
484
485 std::string eName;
486 EXPECT_EQ(E_OK, allDataTypes->GetString(1, eName));
487 EXPECT_EQ("SunWuKong", eName);
488
489 double salary;
490 EXPECT_EQ(E_OK, allDataTypes->GetDouble(2, salary));
491 EXPECT_EQ(8000.00, salary);
492
493 std::string jName;
494 EXPECT_EQ(E_OK, allDataTypes->GetString(3, jName));
495 EXPECT_EQ("Clerk", jName);
496
497 std::string description;
498 EXPECT_EQ(E_OK, allDataTypes->GetString(4, description));
499 EXPECT_EQ("UseOfficeSoftware", description);
500 }
501
502 /* *
503 * @tc.name: RdbStore_InnerJoinB_003
504 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
505 * @tc.type: FUNC
506 */
507 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_003, TestSize.Level1)
508 {
509 RdbPredicates predicates("emp t1");
510
511 std::vector<std::string> clauses;
512 clauses.push_back("t1.salary BETWEEN t2.losalary AND t2.hisalary");
513 predicates.InnerJoin("salarygrade t2")->On(clauses);
514
515 std::vector<std::string> joinTypes;
516 joinTypes.push_back("INNER JOIN");
517 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
518
519 std::vector<std::string> columns;
520 columns.push_back("t1.eName");
521 columns.push_back("t1.salary");
522 columns.push_back("t2.*");
523 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
524 EXPECT_EQ(14, ResultSize(allDataTypes));
525 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
526
527 std::string eName;
528 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
529 EXPECT_EQ("SunWuKong", eName);
530
531 double salary;
532 EXPECT_EQ(E_OK, allDataTypes->GetDouble(1, salary));
533 EXPECT_EQ(8000.00, salary);
534
535 int grade;
536 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, grade));
537 EXPECT_EQ(1, grade);
538
539 int loSalary;
540 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, loSalary));
541 EXPECT_EQ(7000, loSalary);
542
543 int hiSalary;
544 EXPECT_EQ(E_OK, allDataTypes->GetInt(4, hiSalary));
545 EXPECT_EQ(12000, hiSalary);
546 }
547
548
549 /* *
550 * @tc.name: RdbStore_InnerJoinB_004
551 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
552 * @tc.type: FUNC
553 */
554 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_InnerJoinB_004, TestSize.Level1)
555 {
556 RdbPredicates predicates("emp t1");
557
558 std::vector<std::string> clauses;
559 clauses.push_back("t1.jobId = t2.id");
560 clauses.push_back("t1.deptId = t3.id");
561 clauses.push_back("t1.salary BETWEEN t4.losalary AND t4.hisalary");
562 predicates.InnerJoin("job t2")->InnerJoin("dept t3")->InnerJoin("salarygrade t4")->On(clauses);
563
564 std::vector<std::string> joinTypes;
565 joinTypes.push_back("INNER JOIN");
566 joinTypes.push_back("INNER JOIN");
567 joinTypes.push_back("INNER JOIN");
568 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
569 EXPECT_EQ("", predicates.GetJoinConditions()[0]);
570 EXPECT_EQ("", predicates.GetJoinConditions()[1]);
571 EXPECT_EQ("ON(t1.jobId = t2.id AND t1.deptId = t3.id AND t1.salary BETWEEN "
572 "t4.losalary AND t4.hisalary)",
573 predicates.GetJoinConditions()[2]);
574
575 std::vector<std::string> columns;
576 columns.push_back("t1.eName");
577 columns.push_back("t1.salary");
578 columns.push_back("t2.jName");
579 columns.push_back("t2.description");
580 columns.push_back("t3.dName");
581 columns.push_back("t3.loc");
582 columns.push_back("t4.grade");
583 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
584 EXPECT_EQ(14, ResultSize(allDataTypes));
585 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
586
587 std::string eName;
588 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
589 EXPECT_EQ("SunWuKong", eName);
590
591 double salary;
592 EXPECT_EQ(E_OK, allDataTypes->GetDouble(1, salary));
593 EXPECT_EQ(8000.00, salary);
594
595 std::string jName;
596 EXPECT_EQ(E_OK, allDataTypes->GetString(2, jName));
597 EXPECT_EQ("Clerk", jName);
598
599 std::string description;
600 EXPECT_EQ(E_OK, allDataTypes->GetString(3, description));
601 EXPECT_EQ("UseOfficeSoftware", description);
602
603 std::string dName;
604 EXPECT_EQ(E_OK, allDataTypes->GetString(4, dName));
605 EXPECT_EQ("XueGongBu", dName);
606
607 std::string loc;
608 EXPECT_EQ(E_OK, allDataTypes->GetString(5, loc));
609 EXPECT_EQ("ShangHai", loc);
610
611 int grade;
612 EXPECT_EQ(E_OK, allDataTypes->GetInt(6, grade));
613 EXPECT_EQ(1, grade);
614 }
615
616
617 /* *
618 * @tc.name: RdbStore_LeftOuterJoinB_005
619 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
620 * @tc.type: FUNC
621 */
622 HWTEST_F(RdbStorePredicateJoinBTest, RdbStore_LeftOuterJoinB_005, TestSize.Level1)
623 {
624 RdbPredicates predicates("emp t1");
625
626 std::vector<std::string> clauses;
627 clauses.push_back("t1.mgr = t2.id");
628 std::vector<std::string> joinTypes;
629 joinTypes.push_back("LEFT OUTER JOIN");
630
631 predicates.LeftOuterJoin("emp t2")->On(clauses);
632 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
633 EXPECT_EQ("ON(t1.mgr = t2.id)", predicates.GetJoinConditions()[0]);
634
635 std::vector<std::string> columns;
636 columns.push_back("t1.eName");
637 columns.push_back("t1.mgr");
638 columns.push_back("t2.id");
639 columns.push_back("t2.eName");
640 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinBTest::store->Query(predicates, columns);
641 EXPECT_EQ(14, ResultSize(allDataTypes));
642 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
643
644 std::string eName;
645 EXPECT_EQ(E_OK, allDataTypes->GetString(0, eName));
646 EXPECT_EQ("SunWuKong", eName);
647
648 int mgr;
649 EXPECT_EQ(E_OK, allDataTypes->GetInt(1, mgr));
650 EXPECT_EQ(1004, mgr);
651
652 int id;
653 EXPECT_EQ(E_OK, allDataTypes->GetInt(2, id));
654 EXPECT_EQ(1004, id);
655
656 std::string eName_1;
657 EXPECT_EQ(E_OK, allDataTypes->GetString(3, eName_1));
658 EXPECT_EQ("TangCeng", eName_1);
659 }
660