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::unique_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 = std::string("CREATE TABLE IF NOT EXISTS user ") +
54 std::string("(userId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT, lastName TEXT,") +
55 std::string("age INTEGER , balance REAL NOT NULL)");
56 const std::string CREATE_TABLE_BOOK_SQL = std::string("CREATE TABLE IF NOT EXISTS book ") +
57 std::string("(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, userId INTEGER,") +
58 std::string("FOREIGN KEY (userId) REFERENCES user (userId) ON UPDATE NO ACTION ON DELETE CASCADE)");
59
60 class PredicateJoinTestOpenCallback : public RdbOpenCallback {
61 public:
62 int OnCreate(RdbStore &rdbStore) override;
63 int OnUpgrade(RdbStore &rdbStore, int oldVersion, int newVersion) override;
64 };
65
66
OnCreate(RdbStore & store)67 int PredicateJoinTestOpenCallback::OnCreate(RdbStore &store)
68 {
69 return E_OK;
70 }
71
OnUpgrade(RdbStore & store,int oldVersion,int newVersion)72 int PredicateJoinTestOpenCallback::OnUpgrade(RdbStore &store, int oldVersion, int newVersion)
73 {
74 return E_OK;
75 }
76
SetUpTestCase(void)77 void RdbStorePredicateJoinTest::SetUpTestCase(void) {}
78
TearDownTestCase(void)79 void RdbStorePredicateJoinTest::TearDownTestCase(void) {}
80
SetUp(void)81 void RdbStorePredicateJoinTest::SetUp(void)
82 {
83 int errCode = E_OK;
84 RdbStoreConfig config(RdbStorePredicateJoinTest::DATABASE_NAME);
85 PredicateJoinTestOpenCallback helper;
86 RdbStorePredicateJoinTest::store = RdbHelper::GetRdbStore(config, 1, helper, errCode);
87 EXPECT_NE(RdbStorePredicateJoinTest::store, nullptr);
88 RdbStorePredicateJoinTest::GenerateAllTables();
89 }
90
TearDown(void)91 void RdbStorePredicateJoinTest::TearDown(void)
92 {
93 RdbHelper::DeleteRdbStore(RdbStorePredicateJoinTest::DATABASE_NAME);
94 }
95
GenerateAllTables()96 void RdbStorePredicateJoinTest::GenerateAllTables()
97 {
98 RdbStorePredicateJoinTest::store->ExecuteSql(CREATE_TABLE_USER_SQL);
99 RdbStorePredicateJoinTest::InsertUserDates();
100
101 RdbStorePredicateJoinTest::store->ExecuteSql(CREATE_TABLE_BOOK_SQL);
102 RdbStorePredicateJoinTest::InsertBookDates();
103 }
104
InsertUserDates()105 void RdbStorePredicateJoinTest::InsertUserDates()
106 {
107 int64_t id;
108 ValuesBucket values;
109
110 values.PutInt("userId", 1);
111 values.PutString("firstName", std::string("Zhang"));
112 values.PutString("lastName", std::string("San"));
113 values.PutInt("age", 29);
114 values.PutDouble("balance", 100.51);
115 store->Insert(id, "user", values);
116
117 values.Clear();
118 values.PutInt("userId", 2);
119 values.PutString("firstName", std::string("Li"));
120 values.PutString("lastName", std::string("Si"));
121 values.PutInt("age", 30);
122 values.PutDouble("balance", 200.51);
123 store->Insert(id, "user", values);
124
125 values.Clear();
126 values.PutInt("userId", 3);
127 values.PutString("firstName", std::string("Wang"));
128 values.PutString("lastName", std::string("Wu"));
129 values.PutInt("age", 30);
130 values.PutDouble("balance", 300.51);
131 store->Insert(id, "user", values);
132
133 values.Clear();
134 values.PutInt("userId", 4);
135 values.PutString("firstName", std::string("Sun"));
136 values.PutString("lastName", std::string("Liu"));
137 values.PutInt("age", 31);
138 values.PutDouble("balance", 400.51);
139 store->Insert(id, "user", values);
140
141 values.Clear();
142 values.PutInt("userId", 5);
143 values.PutString("firstName", std::string("Ma"));
144 values.PutString("lastName", std::string("Qi"));
145 values.PutInt("age", 32);
146 values.PutDouble("balance", 500.51);
147 store->Insert(id, "user", values);
148 }
149
InsertBookDates()150 void RdbStorePredicateJoinTest::InsertBookDates()
151 {
152 int64_t id;
153 ValuesBucket values;
154
155 values.PutInt("id", 1);
156 values.PutString("name", std::string("SanGuo"));
157 values.PutInt("userId", 1);
158 store->Insert(id, "book", values);
159
160 values.Clear();
161 values.PutInt("id", 2);
162 values.PutString("name", std::string("XiYouJi"));
163 values.PutInt("userId", 2);
164 store->Insert(id, "book", values);
165
166 values.Clear();
167 values.PutInt("id", 3);
168 values.PutString("name", std::string("ShuiHuZhuan"));
169 values.PutInt("userId", 3);
170 store->Insert(id, "book", values);
171 }
172
ResultSize(std::unique_ptr<ResultSet> & resultSet)173 int RdbStorePredicateJoinTest::ResultSize(std::unique_ptr<ResultSet> &resultSet)
174 {
175 if (resultSet->GoToFirstRow() != E_OK) {
176 return 0;
177 }
178 int count = 1;
179 while (resultSet->GoToNextRow() == E_OK) {
180 count++;
181 }
182 return count;
183 }
184
185 /* *
186 * @tc.name: RdbStore_CrossJoin_001
187 * @tc.desc: Normal testCase of RdbPredicates for CrossJoin
188 * @tc.type: FUNC
189 */
190 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_CrossJoin_001, TestSize.Level1)
191 {
192 RdbPredicates predicates("user");
193
194 std::vector<std::string> clauses;
195 clauses.push_back("user.userId = book.userId");
196 predicates.CrossJoin("book")->On(clauses);
197
198 std::vector<std::string> joinTypes;
199 joinTypes.push_back("CROSS JOIN");
200 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
201 EXPECT_EQ("book", predicates.GetJoinTableNames()[0]);
202 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
203 EXPECT_EQ("user CROSS JOIN book ON(user.userId = book.userId)", predicates.GetJoinClause());
204
205 std::vector<std::string> columns;
206 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
207 EXPECT_EQ(3, ResultSize(allDataTypes));
208
209 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
210 int userId;
211 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
212 EXPECT_EQ(1, userId);
213
214 std::string firstName;
215 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
216 EXPECT_EQ("Zhang", firstName);
217
218 std::string lastName;
219 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
220 EXPECT_EQ("San", lastName);
221
222 int age;
223 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
224 EXPECT_EQ(29, age);
225
226 double balance;
227 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
228 EXPECT_EQ(100.51, balance);
229
230 int id;
231 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
232 EXPECT_EQ(1, id);
233
234 std::string name;
235 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
236 EXPECT_EQ("SanGuo", name);
237
238 int userId_1;
239 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, userId_1));
240 EXPECT_EQ(1, userId_1);
241 }
242
243 /* *
244 * @tc.name: RdbStore_InnerJoin_002
245 * @tc.desc: Normal testCase of RdbPredicates for InnerJoin
246 * @tc.type: FUNC
247 */
248 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_InnerJoin_002, TestSize.Level1)
249 {
250 RdbPredicates predicates("user");
251
252 std::vector<std::string> clauses;
253 clauses.push_back("user.userId = book.userId");
254 predicates.InnerJoin("book")->On(clauses)->EqualTo("book.name", "SanGuo");
255
256 std::vector<std::string> joinTypes;
257 joinTypes.push_back("INNER JOIN");
258 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
259 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
260
261 std::vector<std::string> columns;
262 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
263 EXPECT_EQ(1, ResultSize(allDataTypes));
264 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
265
266 int userId;
267 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
268 EXPECT_EQ(1, userId);
269
270 std::string firstName;
271 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
272 EXPECT_EQ("Zhang", firstName);
273
274 std::string lastName;
275 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
276 EXPECT_EQ("San", lastName);
277
278 int age;
279 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
280 EXPECT_EQ(29, age);
281
282 double balance;
283 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
284 EXPECT_EQ(100.51, balance);
285
286 int id;
287 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
288 EXPECT_EQ(1, id);
289
290 std::string name;
291 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
292 EXPECT_EQ("SanGuo", name);
293
294 int userId_1;
295 EXPECT_EQ(E_OK, allDataTypes->GetInt(7, userId_1));
296 EXPECT_EQ(1, userId_1);
297 }
298
299 /* *
300 * @tc.name: RdbStore_LeftOuterJoin_003
301 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
302 * @tc.type: FUNC
303 */
304 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_003, TestSize.Level1)
305 {
306 RdbPredicates predicates("user");
307
308 std::vector<std::string> fields;
309 fields.push_back("userId");
310 predicates.LeftOuterJoin("book")->Using(fields)->EqualTo("name", "SanGuo");
311
312 std::vector<std::string> joinTypes;
313 joinTypes.push_back("LEFT OUTER JOIN");
314 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
315 EXPECT_EQ("USING(userId)", predicates.GetJoinConditions()[0]);
316
317 std::vector<std::string> columns;
318 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
319 EXPECT_EQ(1, ResultSize(allDataTypes));
320
321 EXPECT_EQ(E_OK, allDataTypes->GoToFirstRow());
322
323 int userId;
324 EXPECT_EQ(E_OK, allDataTypes->GetInt(0, userId));
325 EXPECT_EQ(1, userId);
326
327 std::string firstName;
328 EXPECT_EQ(E_OK, allDataTypes->GetString(1, firstName));
329 EXPECT_EQ("Zhang", firstName);
330
331 std::string lastName;
332 EXPECT_EQ(E_OK, allDataTypes->GetString(2, lastName));
333 EXPECT_EQ("San", lastName);
334
335 int age;
336 EXPECT_EQ(E_OK, allDataTypes->GetInt(3, age));
337 EXPECT_EQ(29, age);
338
339 double balance;
340 EXPECT_EQ(E_OK, allDataTypes->GetDouble(4, balance));
341 EXPECT_EQ(100.51, balance);
342
343 int id;
344 EXPECT_EQ(E_OK, allDataTypes->GetInt(5, id));
345 EXPECT_EQ(1, id);
346
347 std::string name;
348 EXPECT_EQ(E_OK, allDataTypes->GetString(6, name));
349 EXPECT_EQ("SanGuo", name);
350 }
351
352 /* *
353 * @tc.name: RdbStore_LeftOuterJoin_004
354 * @tc.desc: Normal testCase of RdbPredicates for LeftOuterJoin
355 * @tc.type: FUNC
356 */
357 HWTEST_F(RdbStorePredicateJoinTest, RdbStore_LeftOuterJoin_004, TestSize.Level1)
358 {
359 RdbPredicates predicates("user");
360
361 std::vector<std::string> clauses;
362 clauses.push_back("user.userId = book.userId");
363 std::vector<std::string> joinTypes;
364 joinTypes.push_back("LEFT OUTER JOIN");
365
366 predicates.LeftOuterJoin("book")->On(clauses);
367 EXPECT_EQ(joinTypes, predicates.GetJoinTypes());
368 EXPECT_EQ("ON(user.userId = book.userId)", predicates.GetJoinConditions()[0]);
369
370 std::vector<std::string> columns;
371 std::unique_ptr<ResultSet> allDataTypes = RdbStorePredicateJoinTest::store->Query(predicates, columns);
372 EXPECT_EQ(5, ResultSize(allDataTypes));
373 }
374