• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1/**
2 * @file Describe the file
3 * Copyright (c) 2023 Huawei Device Co., Ltd.
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 *     http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17import { Log } from "@ohos/common/src/main/ets/utils/Log"
18import { isEmptyStr } from '@ohos/common/src/main/ets/utils/TextUtils';
19
20import { EventColumns, SEARCH_COLUMNS } from '@ohos/datastructure/src/main/ets/events/EventColumns';
21import { InstancesColumns } from '@ohos/datastructure/src/main/ets/instances/InstancesColumns';
22
23import { InstancesQueryParams } from './InstanceQueryParams';
24
25const TAG = 'InstancesQuerySqlGenerator';
26
27/**
28 * generate instances associated query SQL from columns and query params(begin&end、searchText)
29 *
30 * @param columns Columns to be queried
31 * @param params Parameter object containing begin&end or searchText
32 */
33export function generateInstancesQuerySql(columns: Array<string>, params: InstancesQueryParams, callerName: string): string | undefined {
34  if (params === null || params === undefined) {
35    Log.error(TAG, `generateInstancesQuerySql get invalid params`);
36    return undefined;
37  }
38
39  // 1.generate main body
40  let sql = getSelectMainBody(columns);
41
42  // 2.process begin&end fragment
43  const hasBeginEnd = params.isValidBeginEnd();
44  if (hasBeginEnd) {
45    sql += getBeginEndSqlFragment(params.getBegin(), params.getEnd());
46  }
47
48  // 3.if by low authority, process creator fragment use callerName
49  let hasCaller: boolean;
50  if (hasCaller = !isEmptyStr(callerName)) {
51    if (hasBeginEnd) {
52      sql += ` AND`;
53    }
54    sql += getCreatorSqlFragment(callerName);
55  }
56
57  // 4.process searchText fragment
58  const searchText = params.getSearchText();
59  const hasSearch = !isEmptyStr(searchText);
60  if (hasSearch) {
61    if (hasBeginEnd || hasCaller) {
62      sql += ` AND`;
63    }
64    sql += getSearchSqlFragment(searchText);
65  }
66
67  // 5.process orderBy fragment
68  const orderByFragment: string = getOrderByFragment(params) as string;
69  if (!isEmptyStr(orderByFragment)) {
70    sql += orderByFragment;
71  }
72
73  // 6.return the full sql
74  return sql;
75}
76
77function getSelectMainBody(columns: Array<string>): string {
78  let sql = 'SELECT ';
79
80  // set select columns
81  if (columns === null || columns === undefined || columns.length === 0) {
82    sql += '*';
83  } else {
84    for (let index = 0; index < columns.length; index++) {
85      const element = columns[index];
86      if (index !== 0) {
87        sql += ',';
88      }
89      sql += element;
90    }
91  }
92
93  // inner join tables
94  const events = EventColumns.TABLE_NAME;
95  const instances = InstancesColumns.TABLE_NAME;
96  sql += ` FROM ${instances} INNER JOIN ${events}`;
97  sql += ` ON (${instances}.${InstancesColumns.EVENT_ID}=${events}.${EventColumns.ID}) WHERE`;
98  return sql;
99}
100
101function getCreatorSqlFragment(creator: string): string {
102  const fragment = ` (${InstancesColumns.TABLE_NAME}.${InstancesColumns.CREATOR} = '${creator}')`;
103  return fragment;
104}
105
106function getBeginEndSqlFragment(begin: number, end: number): string {
107  const beginRangeFragment = ` (${InstancesColumns.BEGIN} BETWEEN ${begin} AND ${end})`;
108  const endRangeFragment = `(${InstancesColumns.END} BETWEEN ${begin} AND ${end})`;
109  const unionRangeFragment = `(${InstancesColumns.BEGIN} <= ${begin} AND ${InstancesColumns.END} >= ${end})`;
110  let fragment = `(${beginRangeFragment} OR ${endRangeFragment} OR ${unionRangeFragment})`;
111  return fragment;
112}
113
114function getSearchSqlFragment(searchText: string): string {
115  let fragment = ` (`;
116  for (let i = 0, len = SEARCH_COLUMNS.length; i < len; i++) {
117    if (i != 0) {
118      fragment += ` OR `;
119    }
120
121    // add ' character to avoid SQL injection
122    fragment += `(${SEARCH_COLUMNS[i]} like '%${searchText}%')`;
123  }
124  fragment += `)`;
125  return fragment;
126}
127
128function getOrderByFragment(params: InstancesQueryParams): string | undefined {
129  const orderByAsc = params.getOrderByAsc();
130  const orderByDesc = params.getOrderByDesc();
131  if (isEmptyStr(orderByAsc) && isEmptyStr(orderByDesc)) {
132    return undefined;
133  }
134  let fragment = ' ORDER BY ';
135  const hasAsc = !isEmptyStr(orderByAsc);
136  const hasDesc = !isEmptyStr(orderByDesc);
137  if (hasAsc) {
138    fragment += `${orderByAsc} ASC`;
139  }
140  if (hasAsc && hasDesc) {
141    fragment += `,`;
142  }
143  if (hasDesc) {
144    fragment += `${orderByDesc} DESC`;
145  }
146  return fragment;
147}