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