2 * ============LICENSE_START=======================================================
3 * ONAP : ccsdk features
4 * ================================================================================
5 * Copyright (C) 2021 highstreet technologies GmbH Intellectual Property.
7 * ================================================================================
8 * Licensed under the Apache License, Version 2.0 (the "License");
9 * you may not use this file except in compliance with the License.
10 * You may obtain a copy of the License at
12 * http://www.apache.org/licenses/LICENSE-2.0
14 * Unless required by applicable law or agreed to in writing, software
15 * distributed under the License is distributed on an "AS IS" BASIS,
16 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
17 * See the License for the specific language governing permissions and
18 * limitations under the License.
19 * ============LICENSE_END=========================================================
22 package org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query;
24 import java.util.ArrayList;
25 import java.util.Arrays;
26 import java.util.Calendar;
27 import java.util.Date;
28 import java.util.List;
29 import java.util.TimeZone;
30 import java.util.stream.Collectors;
31 import org.eclipse.jdt.annotation.Nullable;
32 import org.onap.ccsdk.features.sdnr.wt.common.database.data.DbFilter;
33 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.filters.DBFilterKeyValuePair;
34 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.filters.RangeSqlDBFilter;
35 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.filters.RegexSqlDBFilter;
36 import org.onap.ccsdk.features.sdnr.wt.dataprovider.model.NetconfTimeStamp;
37 import org.onap.ccsdk.features.sdnr.wt.dataprovider.model.types.NetconfTimeStampImpl;
38 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.entity.input.Filter;
40 public interface SqlQuery {
44 static final List<String> TIMESTAMPPROPERTYNAMES = Arrays.asList("timestamp", "time-stamp", "start", "end");
45 static final String MARIADB_TIMESTAMP_REPLACER = "0000-00-00 00:00:00.000";
46 static final String NETCONF_TIMESTAMP_REPLACER = "0000-00-00T00:00:00.000Z";
47 static final String MARIADB_TIMESTAMP_REPLACER_MIN = "0000-00-00 00:00:00";
48 static final int MARIADB_TIMESTAMP_REPLACER_MIN_LENGTH = MARIADB_TIMESTAMP_REPLACER_MIN.length();
49 static final int MARIADB_TIMESTAMP_REPLACER_MAX_LENGTH = MARIADB_TIMESTAMP_REPLACER.length();
50 static final boolean DEFAULT_IGNORE_CONTROLLERID = false;
51 static final boolean DEFAULT_IGNORE_ID_FIELD = false;
53 public static String getWhereExpression(List<Filter> filters) {
54 if (filters == null) {
57 StringBuilder sb = new StringBuilder();
58 filters = filters.stream().filter(e -> !"*".equals(e.getFiltervalue())).collect(Collectors.toList());
59 if (!filters.isEmpty()) {
61 sb.append(" WHERE (" + getFilterExpression(filters.get(0)) + ")");
62 for (int i = 1; i < filters.size(); i++) {
63 sb.append(" AND (" + getFilterExpression(filters.get(i)) + ")");
69 public static String getFilterExpression(Filter filter) {
70 String property = filter.getProperty();
71 List<String> values = collectValues(filter.getFiltervalue(), filter.getFiltervalues()).stream()
72 .filter(e -> !"*".equals(e)).collect(Collectors.toList());
73 if (values.size() == 1) {
74 return getFilterExpression(property, values.get(0));
75 } else if (values.size() > 1) {
76 StringBuilder sb = new StringBuilder();
77 sb.append(getFilterExpression(property, values.get(0)));
78 for (int i = 1; i < values.size(); i++) {
80 sb.append(getFilterExpression(property, values.get(i)));
87 public static String getFilterExpression(String property, String value) {
89 if (DbFilter.hasSearchParams(value)) {
90 if (TIMESTAMPPROPERTYNAMES.contains(property.toLowerCase())) {
91 if (DbFilter.isComparisonValid(value)) {
92 filter = getComparisonFilter(property, value, true);
94 filter = fromTimestampSearchFilter(property, value);
100 return new RegexSqlDBFilter(property, value).getFilterExpression();
101 } else if (DbFilter.isComparisonValid(value)) {
102 filter = getComparisonFilter(property, value, TIMESTAMPPROPERTYNAMES.contains(property.toLowerCase()));
103 if (filter != null) {
107 return new DBFilterKeyValuePair(property, value).getFilterExpression();
110 static List<String> collectValues(String filtervalue, List<String> filtervalues) {
111 if (filtervalues == null) {
112 return Arrays.asList(filtervalue);
114 List<String> values = new ArrayList<>();
115 if (filtervalue != null) {
116 values.add(filtervalue);
118 values.addAll(filtervalues);
122 private static String getComparisonFilter(String property, String filtervalue, boolean asTimeStamp) {
123 filtervalue = filtervalue.trim();
124 String comparator = null;
126 if (filtervalue.startsWith(">=")) {
128 filtervalue = filtervalue.substring(2).trim();
130 filtervalue = netconfToMariaDBTimestamp(fillTimeStamp(
131 filtervalue.endsWith("*") ? filtervalue : (filtervalue + "*"), MARIADB_TIMESTAMP_REPLACER));
133 } else if (filtervalue.startsWith(">")) {
135 filtervalue = filtervalue.substring(1).trim();
137 if (isFullTimestamp(filtervalue)) {
138 filtervalue = netconfToMariaDBTimestamp(filtervalue);
141 filtervalue = netconfToMariaDBTimestamp(
142 fillTimeStamp(filtervalue.endsWith("*") ? filtervalue : (filtervalue + "*"),
143 NETCONF_TIMESTAMP_REPLACER, true));
146 } else if (filtervalue.startsWith("<=")) {
148 filtervalue = filtervalue.substring(2).trim();
150 if (isFullTimestamp(filtervalue)) {
151 filtervalue = netconfToMariaDBTimestamp(filtervalue);
154 filtervalue = netconfToMariaDBTimestamp(
155 fillTimeStamp(filtervalue.endsWith("*") ? filtervalue : (filtervalue + "*"),
156 NETCONF_TIMESTAMP_REPLACER, true));
159 } else if (filtervalue.startsWith("<")) {
161 filtervalue = filtervalue.substring(1).trim();
163 filtervalue = netconfToMariaDBTimestamp(fillTimeStamp(
164 filtervalue.endsWith("*") ? filtervalue : (filtervalue + "*"), MARIADB_TIMESTAMP_REPLACER));
170 return new RangeSqlDBFilter(property, value, comparator).getFilterExpression();
173 static boolean isFullTimestamp(String v) {
174 return v.length() >= MARIADB_TIMESTAMP_REPLACER_MIN_LENGTH;
178 * Convert timestamp beginning filter expression like 2017* to a full qualified timestamp like '2017-01-01
181 * @param value filter input value
182 * @return fully qualified timestamp
184 private static String fillTimeStamp(String value) {
185 return fillTimeStamp(value, NETCONF_TIMESTAMP_REPLACER);
188 private static String fillTimeStamp(String value, String replacer) {
189 return fillTimeStamp(value, replacer, false);
192 private static String fillTimeStamp(String value, String replacer, boolean useUpperEnd) {
193 int idx = value.lastIndexOf("*");
195 if (idx > replacer.length()) {
196 s = value.substring(0, replacer.length());
198 s = value.substring(0, idx) + replacer.substring(idx);
200 //if month is zero => set to 1
201 if (Integer.parseInt(s.substring(5, 7)) == 0) {
202 s = s.substring(0, 5) + "01-" + s.substring(8);
204 //if day is zero => set to 1
205 if (Integer.parseInt(s.substring(8, 10)) == 0) {
206 s = s.substring(0, 8) + "01" + s.substring(10);
209 s = getTimestampUpperLimit(s, idx);
215 * convert timestamp with ending placeholder in filter to elasticsearch filter e.g. 2017* => gte: 2017-01-01
216 * 00:00:00, lt:2018-01-01 00:00:00Z
218 * 201* => 2010-01... 2020 .. 2018-* => 2018-01... <=> 2019-01
221 private static @Nullable String fromTimestampSearchFilter(String property, String value) {
222 if (!value.endsWith("*")) {
225 int idx = value.lastIndexOf("*");
226 String lowerEnd = fillTimeStamp(value);
227 String upperEnd = getTimestampUpperLimit(fillTimeStamp(value, "0000-00-00T00:00:00.0Z"), idx);
228 return RangeSqlDBFilter.between(property, netconfToMariaDBTimestamp(lowerEnd), true,
229 netconfToMariaDBTimestamp(upperEnd), false);
232 private static String netconfToMariaDBTimestamp(String ts) {
236 String v = ts.replace("T", " ").replace("Z", "");
237 return v.length() > MARIADB_TIMESTAMP_REPLACER_MAX_LENGTH
238 ? v.substring(0, MARIADB_TIMESTAMP_REPLACER_MAX_LENGTH)
242 private static String getTimestampUpperLimit(String lowerEnd, int idx) {
244 String upperEnd = null;
245 NetconfTimeStamp converter = NetconfTimeStampImpl.getConverter();
248 dt = converter.getDateFromNetconf(lowerEnd);
249 } catch (Exception e) {
256 Calendar c = Calendar.getInstance(TimeZone.getTimeZone("UTC"));
261 c.set(Calendar.YEAR, c.get(Calendar.YEAR) + 1000);
262 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
265 c.set(Calendar.YEAR, c.get(Calendar.YEAR) + 100);
266 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
269 c.set(Calendar.YEAR, c.get(Calendar.YEAR) + 10);
270 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
274 c.set(Calendar.YEAR, c.get(Calendar.YEAR) + 1);
275 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
277 case 6: // switch 10 months (2000-0* or 2000-1*)
278 tmpvalue = c.get(Calendar.MONTH);
280 c.set(Calendar.MONTH, 9);
282 c.set(Calendar.YEAR, c.get(Calendar.YEAR) + 1);
283 c.set(Calendar.MONTH, 0);
285 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
288 case 7: // switch one month (2018-01* or 2018-01-*)
290 c.add(Calendar.MONTH, 1);
291 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
293 case 9: // (2018-01-0*)
294 tmpvalue = c.get(Calendar.DAY_OF_MONTH);
296 c.set(Calendar.DAY_OF_MONTH, 10);
297 } else if (tmpvalue == 10) {
298 c.set(Calendar.DAY_OF_MONTH, 20);
299 } else if (tmpvalue == 20) {
300 if (c.getActualMaximum(Calendar.DAY_OF_MONTH) < 30) {
301 c.set(Calendar.DAY_OF_MONTH, 1);
302 c.add(Calendar.MONTH, 1);
304 c.set(Calendar.DAY_OF_MONTH, 30);
306 } else if (tmpvalue == 30) {
307 c.set(Calendar.DAY_OF_MONTH, 1);
308 c.add(Calendar.MONTH, 1);
312 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
314 case 10: // (2018-01-01*)
315 case 11: // (2018-01-01T*)
316 c.add(Calendar.DAY_OF_MONTH, 1);
317 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
319 case 12: // (2018-01-01T1*)
320 tmpvalue = c.get(Calendar.HOUR_OF_DAY);
321 if (tmpvalue == 20) {
322 c.set(Calendar.HOUR_OF_DAY, 0);
323 c.add(Calendar.DAY_OF_MONTH, 1);
325 c.add(Calendar.HOUR_OF_DAY, 10);
327 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
329 case 13: // (2018-01-01T11*)
330 case 14: // (2018-01-01T11-*)
331 c.add(Calendar.HOUR_OF_DAY, 1);
332 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
334 case 15: // (2018-01-01T11-3*)
335 c.add(Calendar.MINUTE, 10);
336 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
338 case 16: // (2018-01-01T11-32*)
339 case 17: // (2018-01-01T11-32-*)
340 c.add(Calendar.MINUTE, 1);
341 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
343 case 18: // (2018-01-01T11-32-1*)
344 c.add(Calendar.SECOND, 10);
345 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());
347 case 19: // (2018-01-01T11-32-11*)
348 case 20: // (2018-01-01T11-32-11.*)
349 c.add(Calendar.SECOND, 1);
350 upperEnd = converter.getTimeStampAsNetconfString(c.getTime());