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 DEPT_TABLE = "CREATE TABLE IF NOT EXISTS dept"
20    + "(id INTEGER PRIMARY KEY , dName TEXT , loc TEXT)";
21
22const JOB_TABLE = "CREATE TABLE IF NOT EXISTS job"
23    + "(id INTEGER PRIMARY KEY , jName TEXT , description TEXT)";
24
25const EMP_TABLE = "CREATE TABLE IF NOT EXISTS emp"
26    + "(id INTEGER PRIMARY KEY ,eName TEXT, jobId INTEGER , "
27    + "mgr INTEGER, joinDate TEXT, salary REAL, bonus REAL, deptId INTEGER,"
28    + "FOREIGN KEY (jobId) REFERENCES job (id) ON UPDATE NO ACTION ON DELETE CASCADE,"
29    + "FOREIGN KEY (deptId) REFERENCES dept (id) ON UPDATE NO ACTION ON DELETE CASCADE)";
30
31const SALARYGRADE_TABLE = "CREATE TABLE IF NOT EXISTS salarygrade"
32    + "(grade INTEGER PRIMARY KEY,loSalary INTEGER, hiSalary INTEGER)";
33
34const STORE_CONFIG = { name: "RdbJoinBTest.db" }
35
36const CURRENT_STORE_VERSION = 1;
37
38const TAG = 'RDB_TEST ';
39
40var rdbStore = undefined;
41
42describe('rdbStorePredicatesJoinBTest', function () {
43    beforeAll(async function () {
44        console.info(TAG + 'beforeAll');
45        console.info(TAG + 'beforeAll end');
46    })
47
48    beforeEach(async function () {
49        console.info(TAG + 'beforeEach');
50        rdbStore = await dataRdb.getRdbStore(STORE_CONFIG, CURRENT_STORE_VERSION);
51        await generateJobTable();
52        await generateSalarygradeTable();
53        await generateDeptTable();
54        await generateEmpTable();
55        console.info(TAG + 'beforeEach end');
56    })
57
58    afterEach(async function () {
59        console.info(TAG + 'afterEach');
60        rdbStore = null;
61        await dataRdb.deleteRdbStore("RdbJoinBTest.db");
62        console.info(TAG + 'afterEach end');
63    })
64
65    afterAll(async function () {
66        console.info(TAG + 'afterAll');
67        console.info(TAG + 'afterAll end');
68    })
69
70    async function generateDeptTable() {
71        console.info(TAG + 'generateDeptTable');
72        await rdbStore.executeSql(DEPT_TABLE);
73
74        const depts = [
75            {id:10, dName:"JiaoYanBU", loc:"BeiJing"},
76            {id:20, dName:"XueGongBu", loc:"ShangHai"},
77            {id:30, dName:"XiaoShouBu", loc:"GuangZhou"},
78            {id:40, dName:"CaiWuBu", loc:"ShenZhen"},
79        ];
80
81        await rdbStore.batchInsert("dept", depts);
82        console.info(TAG + 'generateDeptTable end');
83    }
84
85    async function generateJobTable() {
86        console.info(TAG + 'generateJobTable')
87        await rdbStore.executeSql(JOB_TABLE);
88
89        var jobs = [
90            {id:1, jName:"Chairman", description:"ManageTheEntireCompany"},
91            {id:2, jName:"Manager", description:"ManageEmployeesOfTheDepartment"},
92            {id:3, jName:"Salesperson", description:"SellingProductsToCustomers"},
93            {id:4, jName:"Clerk", description:"UseOfficeSoftware"},
94        ]
95
96        await rdbStore.batchInsert("job", jobs);
97        console.info(TAG + 'generateJobTable end')
98    }
99
100    async function generateEmpTable() {
101        console.info(TAG + 'generateEmpTable')
102        await rdbStore.executeSql(EMP_TABLE);
103
104        var emps = [
105            {id:1001, eName:"SunWuKong", jobId:4, mgr:1004, joinDate:"2000-12-17", salary:8000.00, bonus:null, deptId:20},
106            {id:1002, eName:"LuJunYi", jobId:3, mgr:1006, joinDate:"2001-02-20", salary:16000.00, bonus:3000.00, deptId:30},
107            {id:1003, eName:"LinChong", jobId:3, mgr:1006, joinDate:"2001-02-22", salary:12500.00, bonus:5000.00, deptId:30},
108            {id:1004, eName:"TangCeng", jobId:2, mgr:1009, joinDate:"2001-04-02", salary:29750.00, bonus:null, deptId:20},
109            {id:1005, eName:"LiKui", jobId:4, mgr:1006, joinDate:"2001-09-28", salary:12500.00, bonus:14000.00, deptId:30},
110            {id:1006, eName:"SongJiang", jobId:2, mgr:1009, joinDate:"2001-05-01", salary:28500.00, bonus:null, deptId:30},
111            {id:1007, eName:"LiuBei", jobId:2, mgr:1009, joinDate:"2001-09-01", salary:24500.00, bonus:null, deptId:10},
112            {id:1008, eName:"ZhuBaJie", jobId:4, mgr:1004, joinDate:"2007-04-19", salary:30000.00, bonus:null, deptId:20},
113            {id:1009, eName:"LuoGuanZhong", jobId:1, mgr:null, joinDate:"2001-11-17", salary:50000.00, bonus:null, deptId:10},
114            {id:1010, eName:"WuYong", jobId:3, mgr:1006, joinDate:"2001-09-08", salary:15000.00, bonus:0.00, deptId:30},
115            {id:1011, eName:"ShaCeng", jobId:4, mgr:1004, joinDate:"2007-05-23", salary:11000.00, bonus:null, deptId:20},
116            {id:1012, eName:"LiKui", jobId:4, mgr:1006, joinDate:"2001-12-03", salary:9500.00, bonus:null, deptId:30},
117            {id:1013, eName:"XiaoBaiLong", jobId:4, mgr:1004, joinDate:"2001-12-03", salary:30000.00, bonus:null, deptId:20},
118            {id:1014, eName:"GuanYu", jobId:4, mgr:1007, joinDate:"2002-01-23", salary:13000.00, bonus:null, deptId:10},
119        ];
120
121        await rdbStore.batchInsert("emp", emps);
122        console.info(TAG + 'generateEmpTable end');
123    }
124
125
126    async function generateSalarygradeTable() {
127        console.info(TAG + 'generateSalarygradeTable')
128        await rdbStore.executeSql(SALARYGRADE_TABLE);
129
130        var salarygrades = [
131            {grade:1, loSalary:7000, hiSalary:12000},
132            {grade:2, loSalary:12010, hiSalary:14000},
133            {grade:3, loSalary:14010, hiSalary:20000},
134            {grade:4, loSalary:20010, hiSalary:30000},
135            {grade:5, loSalary:30010, hiSalary:99990},
136        ];
137
138        await rdbStore.batchInsert("salarygrade", salarygrades);
139        console.info(TAG + 'generateSalarygradeTable end')
140    }
141
142    console.log(TAG + "*************Unit Test Begin*************");
143    /**
144     * @tc.name: testRdbJoinB001
145     * @tc.desc: normal testcase of Rdb_Cross_Join
146     * @tc.type: FUNC
147     * @tc.require: I4NZP6
148     */
149    it('testRdbJoinB001', 0, async function (done) {
150        console.log(TAG + "testRdbJoinB001 begin.");
151        let resultSet = await rdbStore.querySql(
152            "SELECT * FROM emp CROSS JOIN dept ON emp.deptId = dept.id");
153
154        expect(14).assertEqual(resultSet.rowCount);
155        expect(true).assertEqual(resultSet.goToFirstRow());
156        expect(1001).assertEqual(resultSet.getInt(0));
157        expect("SunWuKong").assertEqual(resultSet.getString(1));
158        expect(4).assertEqual(resultSet.getInt(2));
159        expect(1004).assertEqual(resultSet.getInt(3));
160        expect("2000-12-17").assertEqual(resultSet.getString(4));
161        expect(8000.00).assertEqual(resultSet.getDouble(5));
162        expect(true).assertEqual(resultSet.isColumnNull(6));
163        expect(20).assertEqual(resultSet.getInt(7));
164        expect(20).assertEqual(resultSet.getInt(8));
165        expect("XueGongBu").assertEqual(resultSet.getString(9));
166        expect("ShangHai").assertEqual(resultSet.getString(10));
167        resultSet.close();
168        done();
169    })
170
171    /**
172     * @tc.name: testRdbJoinB002
173     * @tc.desc: normal testcase of Rdb_Inner_Join
174     * @tc.type: FUNC
175     * @tc.require: I4NZP6
176     */
177    it('testRdbJoinB002', 0, async function (done) {
178        console.log(TAG + "testRdbJoinB002 begin.");
179        let resultSet = await rdbStore.querySql("SELECT  t1.id, t1.eName, t1.salary, t2.jName, t2.description" +
180            " FROM emp t1 INNER JOIN  job t2 ON t1.`jobId` = t2.`id` WHERE t1.eName = 'SunWuKong'")
181
182        expect(1).assertEqual(resultSet.rowCount);
183        expect(true).assertEqual(resultSet.goToFirstRow());
184        expect(1001).assertEqual(resultSet.getInt(0));
185        expect("SunWuKong").assertEqual(resultSet.getString(1));
186        expect(8000.00).assertEqual(resultSet.getDouble(2));
187        expect("Clerk").assertEqual(resultSet.getString(3));
188        expect("UseOfficeSoftware").assertEqual(resultSet.getString(4));
189        resultSet.close();
190        done();
191    })
192
193
194    /**
195     * @tc.name: testRdbJoinB003
196     * @tc.desc: normal testcase of Rdb_Inner_Join
197     * @tc.type: FUNC
198     * @tc.require: I4NZP6
199     */
200    it('testRdbJoinB003', 0, async function (done) {
201        console.log(TAG + "testRdbJoinB003 begin.");
202        let resultSet = await rdbStore.querySql("SELECT  t1.eName, t1.salary, t2.* " +
203            "FROM emp t1 INNER JOIN salarygrade t2 WHERE t1.salary BETWEEN t2.losalary AND t2.hisalary")
204
205        expect(14).assertEqual(resultSet.rowCount);
206        expect(true).assertEqual(resultSet.goToFirstRow());
207        expect("SunWuKong").assertEqual(resultSet.getString(0));
208        expect(8000.00).assertEqual(resultSet.getDouble(1));
209        expect(1).assertEqual(resultSet.getInt(2));
210        expect(7000).assertEqual(resultSet.getInt(3));
211        expect(12000).assertEqual(resultSet.getInt(4));
212        resultSet.close();
213        done();
214    })
215
216    /**
217     * @tc.name: testRdbJoinB004
218     * @tc.desc: normal testcase of Rdb_Inner_Join
219     * @tc.type: FUNC
220     * @tc.require: I4NZP6
221     */
222    it('testRdbJoinB004', 0, async function (done) {
223        console.log(TAG + "testRdbJoinB004 begin.");
224        let resultSet = await rdbStore.querySql("SELECT t1.eName, t1.salary, t2.jName, t2.description, t3.dName, " +
225            "t3.loc, t4.grade FROM emp t1 INNER JOIN job t2 INNER JOIN dept t3 INNER JOIN salarygrade t4 " +
226            "ON t1.jobId = t2.id AND t1.deptId = t3.id AND t1.salary BETWEEN t4.loSalary AND t4.hiSalary");
227
228        expect(14).assertEqual(resultSet.rowCount);
229        expect(true).assertEqual(resultSet.goToFirstRow());
230        expect("SunWuKong").assertEqual(resultSet.getString(0));
231        expect(8000.00).assertEqual(resultSet.getDouble(1));
232        expect("Clerk").assertEqual(resultSet.getString(2));
233        expect("UseOfficeSoftware").assertEqual(resultSet.getString(3));
234        expect("XueGongBu").assertEqual(resultSet.getString(4));
235        expect("ShangHai").assertEqual(resultSet.getString(5));
236        expect(1).assertEqual(resultSet.getInt(6));
237        resultSet.close();
238        done();
239    })
240
241    /**
242     * @tc.name: testRdbJoinB005
243     * @tc.desc: normal testcase of Rdb_Left_Outer_Join
244     * @tc.type: FUNC
245     * @tc.require: I4NZP6
246     */
247    it('testRdbJoinB005', 0, async function (done) {
248        console.log(TAG + "testRdbJoinB005 begin.");
249        let resultSet = await rdbStore.querySql(
250            "SELECT t1.eName, t1.mgr, t2.id, t2.eName FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgr = t2.id");
251
252        expect(14).assertEqual(resultSet.rowCount);
253        expect(true).assertEqual(resultSet.goToFirstRow());
254        expect("SunWuKong").assertEqual(resultSet.getString(0));
255        expect(1004).assertEqual(resultSet.getInt(1));
256        expect(1004).assertEqual(resultSet.getInt(2));
257        expect("TangCeng").assertEqual(resultSet.getString(3));
258        resultSet.close();
259        done();
260    })
261
262    console.log(TAG + "*************Unit Test End*************");
263})