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
16import {describe, beforeAll, beforeEach, afterEach, afterAll, it, expect} from 'deccjsunit/index'
17import dataRdb from '@ohos.data.rdb';
18
19const USER_TABLE = "CREATE TABLE IF NOT EXISTS user "
20    + "(userId INTEGER PRIMARY KEY AUTOINCREMENT, firstName TEXT , lastName TEXT ,"
21    + "age INTEGER , balance REAL  NOT NULL)";
22
23const BOOK_TABLE = "CREATE TABLE IF NOT EXISTS book"
24    + "(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT, userId INTEGER , "
25    + "FOREIGN KEY (userId) REFERENCES user (userId) ON UPDATE NO ACTION ON DELETE CASCADE)";
26
27const USER_BULK_INSERT_STATEMENT = "INSERT INTO user"
28    + "(userId, firstName, lastName, age, balance) VALUES "
29    + "(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?),(?,?,?,?,?)";
30
31const BOOK_BULK_INSERT_STATEMENT = "INSERT INTO book (id, name, userId) "
32    + "VALUES (?,?,?),(?,?,?),(?,?,?)";
33
34const STORE_CONFIG = { name: "RdbJoinTest.db" }
35
36const CURRENT_STORE_VERSION = 1;
37
38const TAG = 'RDB_TEST ';
39
40var rdbStore = undefined;
41
42describe('rdbStorePredicatesJoinTest', function () {
43    beforeAll(async function () {
44        console.info(TAG + 'beforeAll')
45        rdbStore = await dataRdb.getRdbStore(STORE_CONFIG, CURRENT_STORE_VERSION);
46        await generateUserTable();
47        await generateBookTable();
48        console.info(TAG + 'beforeAll end')
49    })
50
51    beforeEach(async function () {
52        console.info(TAG + 'beforeEach end')
53    })
54
55    afterEach(async function () {
56        console.info(TAG + 'afterEach end')
57    })
58
59    afterAll(async function () {
60        console.info(TAG + 'afterAll')
61        rdbStore = null
62        await dataRdb.deleteRdbStore("RdbJoinTest.db");
63        console.info(TAG + 'afterAll end')
64    })
65
66    async function generateUserTable() {
67        console.info(TAG + 'generateUserTable')
68        await rdbStore.executeSql(USER_TABLE);
69
70        const users = [
71            {userId:1, firstName:"Zhang", lastName:"San", age:29, balance:100.51},
72            {userId:2, firstName:"Li", lastName:"Si", age:30, balance:200.51},
73            {userId:3, firstName:"wang", lastName:"wu", age:30, balance:300.51},
74            {userId:4, firstName:"sun", lastName:"liu", age:30, balance:400.51},
75            {userId:5, firstName:"ma", lastName:"qi", age:32, balance:500.51},
76        ];
77
78        var objects = new Array();
79        users.forEach((user) => {
80            objects.push(user.userId);
81            objects.push(user.firstName);
82            objects.push(user.lastName);
83            objects.push(user.age);
84            objects.push(user.balance);
85        });
86
87        await rdbStore.executeSql(USER_BULK_INSERT_STATEMENT, objects);
88        console.info(TAG + 'generateUserTable end')
89    }
90
91    async function generateBookTable() {
92        console.info(TAG + 'generateBookTable')
93        await rdbStore.executeSql(BOOK_TABLE);
94
95        var books = [
96            {id:1, name:"SanGuo", userId:1},
97            {id:2, name:"XiYouJi", userId:2},
98            {id:3, name:"ShuiHuZhuan", userId:3},
99        ]
100
101        var objects = new Array();
102        books.forEach(book => {
103            objects.push(book.id);
104            objects.push(book.name);
105            objects.push(book.userId);
106        });
107
108        await rdbStore.executeSql(BOOK_BULK_INSERT_STATEMENT, objects);
109        console.info(TAG + 'generateBookTable end')
110    }
111
112    console.log(TAG + "*************Unit Test Begin*************");
113
114    /**
115     * @tc.name: testRdbJoin001
116     * @tc.desc: normal testcase of Rdb_Inner_Join
117     * @tc.type: FUNC
118     * @tc.require: I4NZP6
119     */
120    it('testRdbJoin001', 0, async function (done) {
121        console.log(TAG + "testRdbJoin001 begin.");
122        let resultSet = await rdbStore.querySql(
123            "SELECT * FROM user INNER JOIN book ON user.userId = Book.userId WHERE book.name = 'SanGuo'")
124
125        expect(1).assertEqual(resultSet.rowCount);
126        expect(true).assertEqual(resultSet.goToFirstRow());
127        expect(1).assertEqual(resultSet.getInt(0));
128        expect("Zhang").assertEqual(resultSet.getString(1));
129        expect("San").assertEqual(resultSet.getString(2));
130        expect(29).assertEqual(resultSet.getInt(3));
131        expect(100.51).assertEqual(resultSet.getDouble(4));
132        expect(1).assertEqual(resultSet.getInt(5));
133        expect("SanGuo").assertEqual(resultSet.getString(6));
134        expect(1).assertEqual(resultSet.getInt(7));
135        resultSet.close();
136        done();
137    })
138
139    /**
140     * @tc.name: testRdbJoin002
141     * @tc.desc: normal testcase of Rdb_Cross_Join
142     * @tc.type: FUNC
143     * @tc.require: I4NZP6
144     */
145    it('testRdbJoin002', 0, async function (done) {
146        console.log(TAG + "testRdbJoin002 begin.");
147        let resultSet = await rdbStore.querySql(
148            "SELECT * FROM user CROSS JOIN book ON user.userId = book.userId");
149
150        expect(3).assertEqual(resultSet.rowCount);
151        expect(true).assertEqual(resultSet.goToFirstRow());
152        expect(1).assertEqual(resultSet.getInt(0));
153        expect("Zhang").assertEqual(resultSet.getString(1));
154        expect("San").assertEqual(resultSet.getString(2));
155        expect(29).assertEqual(resultSet.getInt(3));
156        expect(100.51).assertEqual(resultSet.getDouble(4));
157        expect(1).assertEqual(resultSet.getInt(5));
158        expect("SanGuo").assertEqual(resultSet.getString(6));
159        expect(1).assertEqual(resultSet.getInt(7));
160        resultSet.close();
161        done();
162    })
163
164    /**
165     * @tc.name: testRdbJoin003
166     * @tc.desc: normal testcase of Rdb_Left_Outer_Join
167     * @tc.type: FUNC
168     * @tc.require: I4NZP6
169     */
170    it('testRdbJoin003', 0, async function (done) {
171        console.log(TAG + "testRdbJoin003 begin.");
172        let resultSet = await rdbStore.querySql(
173            "SELECT * FROM user LEFT OUTER JOIN book ON user.userId = book.userId");
174
175        expect(5).assertEqual(resultSet.rowCount);
176        resultSet.close();
177        done();
178    })
179
180    /**
181     * @tc.name: testRdbJoin004
182     * @tc.desc: normal testcase of Rdb_Inner_Join
183     * @tc.type: FUNC
184     * @tc.require: I4NZP6
185     */
186    it('testRdbJoin004', 0, async function (done) {
187        console.log(TAG + "testRdbJoin004 begin.");
188        let resultSet = await rdbStore.querySql(
189            "SELECT * FROM user LEFT OUTER JOIN book USING(userId) WHERE book.name = 'SanGuo'");
190
191        expect(1).assertEqual(resultSet.rowCount);
192
193        expect(true).assertEqual(resultSet.goToFirstRow());
194        expect(1).assertEqual(resultSet.getInt(0));
195        expect("Zhang").assertEqual(resultSet.getString(1));
196        expect("San").assertEqual(resultSet.getString(2));
197        expect(29).assertEqual(resultSet.getInt(3));
198        expect(100.51).assertEqual(resultSet.getDouble(4));
199        expect(1).assertEqual(resultSet.getInt(5));
200        expect("SanGuo").assertEqual(resultSet.getString(6));
201        resultSet.close();
202        done();
203    })
204
205    console.log(TAG + "*************Unit Test End*************");
206})