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.database;
24 import com.fasterxml.jackson.core.JsonProcessingException;
25 import com.fasterxml.jackson.databind.JsonMappingException;
26 import java.lang.reflect.InvocationTargetException;
27 import java.lang.reflect.Method;
28 import java.math.BigInteger;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 import java.util.ArrayList;
32 import java.util.Arrays;
33 import java.util.HashMap;
34 import java.util.List;
36 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.filters.DBKeyValuePair;
37 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.YangToolsMapper;
38 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.YangToolsMapperHelper;
39 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.mapperextensions.YangToolsBuilderAnnotationIntrospector;
40 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.mapperextensions.YangToolsDeserializerModifier;
41 import org.opendaylight.yang.gen.v1.urn.ietf.params.xml.ns.yang.ietf.yang.types.rev130715.DateAndTime;
42 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.Entity;
43 import org.opendaylight.yangtools.concepts.Builder;
44 import org.opendaylight.yangtools.yang.binding.DataObject;
45 import org.opendaylight.yangtools.yang.binding.Enumeration;
46 import org.opendaylight.yangtools.yang.common.Uint16;
47 import org.opendaylight.yangtools.yang.common.Uint32;
48 import org.opendaylight.yangtools.yang.common.Uint64;
49 import org.opendaylight.yangtools.yang.common.Uint8;
50 import org.slf4j.Logger;
51 import org.slf4j.LoggerFactory;
53 public class SqlDBMapper {
55 private static final Logger LOG = LoggerFactory.getLogger(SqlDBMapper.class);
57 private static final Map<Class<?>, String> mariaDBTypeMap = initTypeMap();
58 private static final String ODLID_DBTYPE = "VARCHAR(40)";
59 private static final String STRING_DBTYPE = "VARCHAR(255)";
60 private static final String ENUM_DBTYPE = "VARCHAR(100)";
61 private static final String BIGINT_DBTYPE = "BIGINT";
62 public static final String ODLID_DBCOL = "controller-id";
63 private static final String ID_DBCOL = "id";
64 private static List<Class<?>> numericClasses = Arrays.asList(Byte.class, Integer.class, Long.class,
65 BigInteger.class, Uint8.class, Uint16.class, Uint32.class, Uint64.class);
66 private static final YangToolsMapper mapper = new YangToolsMapper();
67 public static final String TABLENAME_CONTROLLER = "controller";
68 private static final String DEFAULTID_DBTYPE = "int(11)";
70 private SqlDBMapper() {
74 public static String createTableOdl() {
75 return "CREATE TABLE IF NOT EXISTS " + TABLENAME_CONTROLLER + " (`" + ID_DBCOL + "` " + ODLID_DBTYPE + " "
76 + getColumnOptions(ID_DBCOL, ODLID_DBTYPE) + "," + "`desc` " + STRING_DBTYPE + " "
77 + getColumnOptions("description", STRING_DBTYPE) + "," + "primary key(" + ID_DBCOL + "))";
80 public static <T> String createTable(Class<T> clazz, Entity e) throws UnableToMapClassException {
81 return createTable(clazz, e, "", false);
84 public static <T> String createTable(Class<T> clazz, Entity e, String suffix) throws UnableToMapClassException {
85 return createTable(clazz, e, suffix, false);
88 public static <T> String createTable(Class<T> clazz, Entity e, boolean autoIndex) throws UnableToMapClassException {
89 return createTable(clazz, e, "", false);
92 public static <T> String createTable(Class<T> clazz, Entity e, String suffix, boolean autoIndex)
93 throws UnableToMapClassException {
94 StringBuilder sb = new StringBuilder();
95 sb.append("CREATE TABLE IF NOT EXISTS `" + e.getName() + suffix + "` (\n");
97 sb.append("`" + ID_DBCOL + "` " + DEFAULTID_DBTYPE + " " + getColumnOptions(ID_DBCOL, DEFAULTID_DBTYPE)
100 sb.append("`" + ID_DBCOL + "` " + STRING_DBTYPE + " " + getColumnOptions(ID_DBCOL, STRING_DBTYPE) + ",\n");
102 sb.append("`" + ODLID_DBCOL + "` " + ODLID_DBTYPE + " " + getColumnOptions(ODLID_DBCOL, ODLID_DBTYPE) + ",\n");
103 for (Method method : getFilteredMethods(clazz, true)) {
104 Class<?> valueType = method.getReturnType();
105 String colName = getColumnName(method);
106 if (ID_DBCOL.equals(colName)) {
109 String dbType = getDBType(valueType);
110 String options = getColumnOptions(colName, dbType);
111 sb.append("`" + colName + "` " + dbType + " " + options + ",\n");
113 sb.append("primary key(" + ID_DBCOL + "),");
114 sb.append("foreign key(`" + ODLID_DBCOL + "`) references " + TABLENAME_CONTROLLER + "(" + ID_DBCOL + ")");
117 return sb.toString();
120 private static String getColumnOptions(String colName, String dbType) {
121 StringBuilder options = new StringBuilder();
122 if (dbType.contains("VARCHAR")) {
123 options.append("CHARACTER SET utf8 ");
125 if (ID_DBCOL.equals(colName) || ODLID_DBCOL.equals(colName)) {
126 if (dbType.equals(DEFAULTID_DBTYPE)) {
127 options.append("NOT NULL AUTO_INCREMENT");
129 options.append("NOT NULL");
132 return options.toString();
135 public static List<Method> getFilteredMethods(Class<?> clazz, boolean getterOrSetter) {
136 Method[] methods = clazz.getMethods();
137 List<Method> list = new ArrayList<>();
138 for (Method method : methods) {
139 if (getterOrSetter) {
140 if (!isGetter(method)) {
144 if (!isSetter(method)) {
148 if (ignoreMethod(method, methods, getterOrSetter)) {
157 private static Map<Class<?>, String> initTypeMap() {
158 Map<Class<?>, String> map = new HashMap<>();
159 map.put(String.class, STRING_DBTYPE);
160 map.put(Boolean.class, "BOOLEAN");
161 map.put(Byte.class, "TINYINT");
162 map.put(Integer.class, "INTEGER");
163 map.put(Long.class, "BIGINT");
164 map.put(BigInteger.class, "BIGINT");
165 map.put(Uint8.class, "SMALLINT");
166 map.put(Uint16.class, "INTEGER");
167 map.put(Uint32.class, BIGINT_DBTYPE);
168 map.put(Uint64.class, BIGINT_DBTYPE); //????
169 map.put(DateAndTime.class, "DATETIME(3)");
173 private static boolean ignoreMethod(Method method, Method[] classMehtods, boolean getterOrSetter) {
174 final String name = method.getName();
175 if (name.equals("getAugmentations") || name.equals("getImplementedInterface")
176 || name.equals("implementedInterface") || name.equals("getClass")) {
179 for (Method cm : classMehtods) {
180 if (!cm.equals(method) && cm.getName().equals(name)) {
182 return !resolveConflict(method, cm, getterOrSetter);
184 //silicon fix for deprecated is-... and getIs- methods for booleans
185 if (method.getReturnType().equals(Boolean.class) && getterOrSetter && name.startsWith("get")
186 && cm.getName().startsWith("is") && cm.getName().endsWith(name.substring(3))) {
193 private static boolean resolveConflict(Method m1, Method m2, boolean getterOrSetter) {
194 Class<?> p1 = getterOrSetter ? m1.getReturnType() : m1.getParameterTypes()[0];
195 Class<?> p2 = getterOrSetter ? m2.getReturnType() : m2.getParameterTypes()[0];
196 if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Map.class, List.class)) {
197 return p1.isAssignableFrom(List.class); //prefer List setter
198 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint64.class, BigInteger.class)) {
199 return p1.isAssignableFrom(Uint64.class);
200 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint32.class, Long.class)) {
201 return p1.isAssignableFrom(Uint32.class);
202 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint16.class, Integer.class)) {
203 return p1.isAssignableFrom(Uint16.class);
204 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint8.class, Short.class)) {
205 return p1.isAssignableFrom(Uint8.class);
210 public static String getColumnName(Method method) {
211 String camelName = (method.getName().startsWith("get") || method.getName().startsWith("set"))
212 ? method.getName().substring(3)
213 : method.getName().substring(2);
214 return convertCamelToKebabCase(camelName);
217 private static String getDBType(Class<?> valueType) throws UnableToMapClassException {
218 String type = mariaDBTypeMap.getOrDefault(valueType, null);
220 if (implementsInterface(valueType, DataObject.class) || implementsInterface(valueType, List.class)
221 || implementsInterface(valueType, Map.class)) {
224 if (implementsInterface(valueType, Enumeration.class)) {
227 throw new UnableToMapClassException("no mapping for " + valueType.getName() + " found");
232 private static boolean implementsInterface(Class<?> valueType, Class<?> iftoImpl) {
233 return iftoImpl.isAssignableFrom(valueType);
236 private static boolean isGetter(Method method) {
237 return method.getName().startsWith("get") || method.getName().startsWith("is")
238 || method.getName().startsWith("do");
241 private static boolean isSetter(Method method) {
242 return method.getName().startsWith("set");
246 * @param input string in Camel Case
247 * @return String in Kebab case Inspiration from KebabCaseStrategy class of com.fasterxml.jackson.databind with an
248 * additional condition to handle numbers as well Using QNAME would have been a more fool proof solution,
249 * however it can lead to performance problems due to usage of Java reflection
251 private static String convertCamelToKebabCase(String input) {
253 return input; // garbage in, garbage out
254 int length = input.length();
259 StringBuilder result = new StringBuilder(length + (length >> 1));
263 for (int i = 0; i < length; ++i) {
264 char ch = input.charAt(i);
265 char lc = Character.toLowerCase(ch);
267 if (lc == ch) { // lower-case letter means we can get new word
268 // but need to check for multi-letter upper-case (acronym), where assumption
269 // is that the last upper-case char is start of a new word
270 if ((upperCount > 1)) {
271 // so insert hyphen before the last character now
272 result.insert(result.length() - 1, '-');
273 } else if ((upperCount == 1) && Character.isDigit(ch) && i != length - 1) {
278 // Otherwise starts new word, unless beginning of string
279 if ((upperCount == 0) && (i > 0)) {
286 return result.toString();
289 public static class UnableToMapClassException extends Exception {
291 private static final long serialVersionUID = 1L;
293 public UnableToMapClassException(String message) {
299 public static String escape(Object o) {
300 return escape(o.toString());
303 public static String escape(String o) {
304 return o.replace("'", "\'");
307 public static boolean isComplex(Class<?> valueType) {
308 return DataObject.class.isAssignableFrom(valueType) || List.class.isAssignableFrom(valueType);
311 public static Object getNumericValue(Object value, Class<?> valueType) {
312 if (valueType.equals(Byte.class) || valueType.equals(Integer.class) || valueType.equals(Long.class)) {
315 if (valueType.equals(Uint8.class) || valueType.equals(Uint16.class) || valueType.equals(Uint32.class)
316 || valueType.equals(Uint64.class)) {
317 return ((Number) value).longValue();
322 public static Object bool2int(Object invoke) {
323 return Boolean.TRUE.equals(invoke) ? 1 : 0;
326 public static boolean isBoolean(Class<?> valueType) {
327 return valueType.equals(Boolean.class);
330 public static boolean isNumeric(Class<?> valueType) {
331 return numericClasses.contains(valueType);
335 private static boolean isDateTime(Class<?> valueType) {
336 return valueType.equals(DateAndTime.class);
339 private static boolean isYangEnum(Class<?> valueType) {
340 return YangToolsMapperHelper.implementsInterface(valueType, Enumeration.class);
343 public static <T extends DataObject> List<T> read(ResultSet data, Class<T> clazz)
344 throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException,
345 SecurityException, NoSuchMethodException, JsonProcessingException, SQLException {
346 return read(data, clazz, null);
349 @SuppressWarnings("unchecked")
350 public static <T> List<T> read(ResultSet data, Class<T> clazz, String column)
351 throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, SQLException,
352 InstantiationException, SecurityException, NoSuchMethodException, JsonProcessingException {
354 Builder<T> builder = findPOJOBuilder(clazz);
355 if(builder==null && column==null) {
356 throw new InstantiationException("unable to find builder for class "+clazz.getName());
358 List<T> list = new ArrayList<>();
359 while (data.next()) {
360 if (column == null) {
363 for (Method m : getFilteredMethods(builder.getClass(), false)) {
364 argType = m.getParameterTypes()[0];
365 col = getColumnName(m);
366 m.setAccessible(true);
367 m.invoke(builder, getValueOrDefault(data, col, argType, null));
369 list.add(builder.build());
371 Object value = getValueOrDefault(data, column, clazz, null);
380 @SuppressWarnings("unchecked")
381 private static <T> Builder<T> findPOJOBuilder(Class<T> ac) throws InstantiationException, IllegalAccessException,
382 IllegalArgumentException, InvocationTargetException, SecurityException, NoSuchMethodException {
384 String builder = null;
386 if (ac.isInterface()) {
387 String clsName = ac.getName();
388 if (clsName.endsWith("Entity")) {
389 clsName = clsName.substring(0, clsName.length() - 6);
391 builder = clsName + "Builder";
393 if (builder != null) {
394 Class<?> innerBuilder = YangToolsMapperHelper.findClass(builder);
395 Class<Builder<T>> builderClass = (Class<Builder<T>>) innerBuilder;
396 return builderClass.getDeclaredConstructor().newInstance();
398 } catch (ClassNotFoundException e) {
404 private static Object getValueOrDefault(ResultSet data, String col, Class<?> dstType, Object defaultValue)
405 throws SQLException, JsonMappingException, JsonProcessingException {
406 if (isBoolean(dstType)) {
407 return data.getBoolean(col);
408 } else if (isNumeric(dstType)) {
409 return getNumeric(dstType, data.getLong(col));
410 } else if (String.class.equals(dstType)) {
411 return data.getString(col);
412 } else if (isYangEnum(dstType)) {
413 return getYangEnum(data.getString(col), dstType);
414 } else if (isDateTime(dstType)) {
415 String v = data.getString(col);
416 return v == null || v.equals("null") ? null : DateAndTime.getDefaultInstance(v.replace(" ", "T") + "Z");
417 } else if (isComplex(dstType)) {
418 String v = data.getString(col);
420 return (v == null || v.equalsIgnoreCase("null")) ? null : mapper.readValue(v, dstType);
427 private static Object getYangEnum(String value, Class<?> dstType) {
428 if (value == null || value.equals("null")) {
432 return YangToolsDeserializerModifier.parseEnum(value, dstType);
433 } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException
434 | SecurityException e) {
435 LOG.warn("unable to parse enum value '{}' to class {}: ", value, dstType, e);
440 private static Object getNumeric(Class<?> dstType, long value) {
441 if (dstType.equals(Uint64.class)) {
442 return Uint64.valueOf(value);
443 } else if (dstType.equals(Uint32.class)) {
444 return Uint32.valueOf(value);
445 } else if (dstType.equals(Uint16.class)) {
446 return Uint16.valueOf(value);
447 } else if (dstType.equals(Uint8.class)) {
448 return Uint8.valueOf(value);
449 } else if (dstType.equals(Long.class)) {
451 } else if (dstType.equals(Integer.class)) {
453 } else if (dstType.equals(Byte.class)) {
459 public static DBKeyValuePair<String> getEscapedKeyValue(Method m, String col, Object value)
460 throws JsonProcessingException {
461 Class<?> valueType = m.getReturnType();
462 String svalue = null;
463 if (isBoolean(valueType)) {
464 svalue = String.valueOf(bool2int(value));
465 } else if (isNumeric(valueType)) {
466 svalue = String.valueOf(getNumericValue(value, valueType));
467 } else if (isDateTime(valueType)) {
468 svalue = "'" + getDateTimeValue((DateAndTime) value) + "'";
469 } else if (isComplex(valueType)) {
470 svalue = "'" + escape(mapper.writeValueAsString(value)) + "'";
472 svalue = "'" + escape(value) + "'";
474 return new DBKeyValuePair<>("`" + col + "`", svalue);
477 private static String getDateTimeValue(DateAndTime value) {
478 String s = value.getValue();
479 if (s.endsWith("Z")) {
480 s = s.substring(0, s.length() - 1).replace("T", " ");
481 } else if (s.contains("+")) {
482 s = s.substring(0, s.indexOf("+")).replace("T", " ");