• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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})