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;
37 import java.util.stream.Collectors;
38 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.data.SqlPropertyInfo;
39 import org.onap.ccsdk.features.sdnr.wt.dataprovider.database.sqldb.query.filters.DBKeyValuePair;
40 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.YangToolsMapper;
41 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.YangToolsMapperHelper;
42 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.mapperextensions.YangToolsBuilderAnnotationIntrospector;
43 import org.onap.ccsdk.features.sdnr.wt.yang.mapper.mapperextensions.YangToolsDeserializerModifier;
44 import org.opendaylight.yang.gen.v1.urn.ietf.params.xml.ns.yang.ietf.yang.types.rev130715.DateAndTime;
45 import org.opendaylight.yang.gen.v1.urn.opendaylight.params.xml.ns.yang.data.provider.rev201110.Entity;
46 import org.opendaylight.yangtools.yang.binding.DataObject;
47 import org.opendaylight.yangtools.yang.binding.EnumTypeObject;
48 import org.opendaylight.yangtools.yang.common.Uint16;
49 import org.opendaylight.yangtools.yang.common.Uint32;
50 import org.opendaylight.yangtools.yang.common.Uint64;
51 import org.opendaylight.yangtools.yang.common.Uint8;
52 import org.slf4j.Logger;
53 import org.slf4j.LoggerFactory;
55 public class SqlDBMapper {
57 private static final Logger LOG = LoggerFactory.getLogger(SqlDBMapper.class);
59 private static final Map<Class<?>, String> mariaDBTypeMap = initTypeMap();
60 private static final String ODLID_DBTYPE = "VARCHAR(40)";
61 private static final String STRING_DBTYPE = "VARCHAR(255)";
62 private static final String ENUM_DBTYPE = "VARCHAR(100)";
63 private static final String BIGINT_DBTYPE = "BIGINT";
64 public static final String ODLID_DBCOL = "controller-id";
65 public static final String ID_DBCOL = "id";
66 private static List<Class<?>> numericClasses = Arrays.asList(Byte.class, Integer.class, Long.class,
67 BigInteger.class, Uint8.class, Uint16.class, Uint32.class, Uint64.class);
68 private static final YangToolsMapper mapper = new YangToolsMapper();
69 public static final String TABLENAME_CONTROLLER = "controller";
70 private static final String DEFAULTID_DBTYPE = "int(11)";
72 private SqlDBMapper() {
76 public static String createTableOdl() {
77 return "CREATE TABLE IF NOT EXISTS " + TABLENAME_CONTROLLER + " (`" + ID_DBCOL + "` " + ODLID_DBTYPE + " "
78 + getColumnOptions(ID_DBCOL, ODLID_DBTYPE) + "," + "`desc` " + STRING_DBTYPE + " "
79 + getColumnOptions("description", STRING_DBTYPE) + "," + "primary key(" + ID_DBCOL + "))";
82 public static <T> String createTable(Class<T> clazz, Entity e) throws UnableToMapClassException {
83 return createTable(clazz, e, "", false, true);
86 public static <T> String createTable(Class<T> clazz, Entity e, String suffix) throws UnableToMapClassException {
87 return createTable(clazz, e, suffix, false, true);
90 public static <T> String createTable(Class<T> clazz, Entity e, boolean autoIndex) throws UnableToMapClassException {
91 return createTable(clazz, e, "", false, true);
94 public static <T> String createTable(Class<T> clazz, Entity e, String suffix, boolean autoIndex,
95 boolean withControllerId) throws UnableToMapClassException {
96 StringBuilder sb = new StringBuilder();
97 sb.append("CREATE TABLE IF NOT EXISTS `" + e.getName() + suffix + "` (\n");
99 sb.append("`" + ID_DBCOL + "` " + DEFAULTID_DBTYPE + " " + getColumnOptions(ID_DBCOL, DEFAULTID_DBTYPE)
102 sb.append("`" + ID_DBCOL + "` " + STRING_DBTYPE + " " + getColumnOptions(ID_DBCOL, STRING_DBTYPE) + ",\n");
104 if (withControllerId) {
105 sb.append("`" + ODLID_DBCOL + "` " + ODLID_DBTYPE + " " + getColumnOptions(ODLID_DBCOL, ODLID_DBTYPE) + ",\n");
107 for (Method method : getFilteredMethods(clazz, true)) {
108 Class<?> valueType = method.getReturnType();
109 String colName = getColumnName(method);
110 if (ID_DBCOL.equals(colName)) {
113 String dbType = getDBType(valueType);
114 String options = getColumnOptions(colName, dbType);
115 sb.append("`" + colName + "` " + dbType + " " + options + ",\n");
117 sb.append("primary key(" + ID_DBCOL + ")");
118 if (withControllerId) {
119 sb.append(",foreign key(`" + ODLID_DBCOL + "`) references " + TABLENAME_CONTROLLER + "(" + ID_DBCOL + ")");
123 return sb.toString();
126 private static String getColumnOptions(String colName, String dbType) {
127 StringBuilder options = new StringBuilder();
128 if (dbType.contains("VARCHAR")) {
129 options.append("CHARACTER SET utf8 ");
131 if (ID_DBCOL.equals(colName) || ODLID_DBCOL.equals(colName)) {
132 if (dbType.equals(DEFAULTID_DBTYPE)) {
133 options.append("NOT NULL AUTO_INCREMENT");
135 options.append("NOT NULL");
138 return options.toString();
143 * @param clazz Class to scan for methods for their properties
144 * @param getterOrSetter true for using only getters, false using setters
147 public static List<Method> getFilteredMethods(Class<?> clazz, boolean getterOrSetter) {
148 Method[] methods = clazz.getMethods();
149 List<Method> list = new ArrayList<>();
150 for (Method method : methods) {
151 if (getterOrSetter) {
152 if (!isGetter(method)) {
156 if (!isSetter(method)) {
160 if (ignoreMethod(method, methods, getterOrSetter)) {
169 private static Map<Class<?>, String> initTypeMap() {
170 Map<Class<?>, String> map = new HashMap<>();
171 map.put(String.class, STRING_DBTYPE);
172 map.put(Boolean.class, "BOOLEAN");
173 map.put(Byte.class, "TINYINT");
174 map.put(Integer.class, "INTEGER");
175 map.put(Long.class, "BIGINT");
176 map.put(BigInteger.class, "BIGINT");
177 map.put(Uint8.class, "SMALLINT");
178 map.put(Uint16.class, "INTEGER");
179 map.put(Uint32.class, BIGINT_DBTYPE);
180 map.put(Uint64.class, BIGINT_DBTYPE); //????
181 map.put(DateAndTime.class, "DATETIME(3)");
185 private static boolean ignoreMethod(Method method, Method[] classMehtods, boolean getterOrSetter) {
186 final String name = method.getName();
187 if (name.equals("getAugmentations") || name.equals("getImplementedInterface")
188 || name.equals("implementedInterface") || name.equals("getClass")) {
191 for (Method cm : classMehtods) {
192 if (!cm.equals(method) && cm.getName().equals(name)) {
194 return !resolveConflict(method, cm, getterOrSetter);
196 //silicon fix for deprecated is-... and getIs- methods for booleans
197 if (method.getReturnType().equals(Boolean.class) && getterOrSetter && name.startsWith("get")
198 && cm.getName().startsWith("is") && cm.getName().endsWith(name.substring(3))) {
205 private static boolean resolveConflict(Method m1, Method m2, boolean getterOrSetter) {
206 Class<?> p1 = getterOrSetter ? m1.getReturnType() : m1.getParameterTypes()[0];
207 Class<?> p2 = getterOrSetter ? m2.getReturnType() : m2.getParameterTypes()[0];
208 if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Map.class, List.class)) {
209 return p1.isAssignableFrom(List.class); //prefer List setter
210 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint64.class, BigInteger.class)) {
211 return p1.isAssignableFrom(Uint64.class);
212 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint32.class, Long.class)) {
213 return p1.isAssignableFrom(Uint32.class);
214 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint16.class, Integer.class)) {
215 return p1.isAssignableFrom(Uint16.class);
216 } else if (YangToolsBuilderAnnotationIntrospector.isAssignable(p1, p2, Uint8.class, Short.class)) {
217 return p1.isAssignableFrom(Uint8.class);
222 public static String getColumnName(Method method) {
223 String camelName = (method.getName().startsWith("get") || method.getName().startsWith("set"))
224 ? method.getName().substring(3)
225 : method.getName().substring(2);
226 return convertCamelToKebabCase(camelName);
229 public static String getDBType(Class<?> valueType) throws UnableToMapClassException {
230 String type = mariaDBTypeMap.getOrDefault(valueType, null);
232 if (implementsInterface(valueType, DataObject.class) || implementsInterface(valueType, List.class)
233 || implementsInterface(valueType, Map.class) || implementsInterface(valueType, Set.class)) {
236 if (implementsInterface(valueType, EnumTypeObject.class)) {
239 throw new UnableToMapClassException("no mapping for " + valueType.getName() + " found");
244 private static boolean implementsInterface(Class<?> valueType, Class<?> iftoImpl) {
245 return iftoImpl.isAssignableFrom(valueType);
248 private static boolean isGetter(Method method) {
249 return method.getName().startsWith("get") || method.getName().startsWith("is")
250 || method.getName().startsWith("do");
253 private static boolean isSetter(Method method) {
254 return method.getName().startsWith("set");
258 * @param input string in Camel Case
259 * @return String in Kebab case Inspiration from KebabCaseStrategy class of com.fasterxml.jackson.databind with an
260 * additional condition to handle numbers as well Using QNAME would have been a more fool proof solution,
261 * however it can lead to performance problems due to usage of Java reflection
263 private static String convertCamelToKebabCase(String input) {
265 return input; // garbage in, garbage out
266 int length = input.length();
271 StringBuilder result = new StringBuilder(length + (length >> 1));
275 for (int i = 0; i < length; ++i) {
276 char ch = input.charAt(i);
277 char lc = Character.toLowerCase(ch);
279 if (lc == ch) { // lower-case letter means we can get new word
280 // but need to check for multi-letter upper-case (acronym), where assumption
281 // is that the last upper-case char is start of a new word
282 if ((upperCount > 1)) {
283 // so insert hyphen before the last character now
284 result.insert(result.length() - 1, '-');
285 } else if ((upperCount == 1) && Character.isDigit(ch) && i != length - 1) {
290 // Otherwise starts new word, unless beginning of string
291 if ((upperCount == 0) && (i > 0)) {
298 return result.toString();
301 public static List<SqlPropertyInfo> getProperties(Class<? extends DataObject> clazz) {
302 return getFilteredMethods(clazz, true).stream().map(e -> {
304 return new SqlPropertyInfo(e);
305 } catch (UnableToMapClassException ex) {
306 throw new RuntimeException(ex);
308 }).collect(Collectors.toList());
311 public static class UnableToMapClassException extends Exception {
313 private static final long serialVersionUID = 1L;
315 public UnableToMapClassException(String message) {
321 public static String escape(Object o) {
322 return escape(o.toString());
325 public static String escape(String o) {
326 return o.replace("'", "\\'");
329 public static boolean isComplex(Class<?> valueType) {
330 return DataObject.class.isAssignableFrom(valueType) || List.class.isAssignableFrom(valueType)
331 || Set.class.isAssignableFrom(valueType);
334 public static Object getNumericValue(Object value, Class<?> valueType) {
335 if (valueType.equals(Byte.class) || valueType.equals(Integer.class) || valueType.equals(Long.class)) {
338 if (valueType.equals(Uint8.class) || valueType.equals(Uint16.class) || valueType.equals(Uint32.class)
339 || valueType.equals(Uint64.class)) {
340 return ((Number) value).longValue();
345 public static Object bool2int(Object invoke) {
346 return Boolean.TRUE.equals(invoke) ? 1 : 0;
349 public static boolean isBoolean(Class<?> valueType) {
350 return valueType.equals(Boolean.class);
353 public static boolean isNumeric(Class<?> valueType) {
354 return numericClasses.contains(valueType);
358 private static boolean isDateTime(Class<?> valueType) {
359 return valueType.equals(DateAndTime.class);
362 private static boolean isYangEnum(Class<?> valueType) {
363 return YangToolsMapperHelper.implementsInterface(valueType, EnumTypeObject.class);
366 public static <T extends DataObject> List<T> read(ResultSet data, Class<T> clazz)
367 throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException,
368 SecurityException, NoSuchMethodException, JsonProcessingException, SQLException {
369 return read(data, clazz, null);
372 @SuppressWarnings("unchecked")
373 public static <S, T> List<T> read(ResultSet data, Class<T> clazz, String column)
374 throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, SQLException,
375 InstantiationException, SecurityException, NoSuchMethodException, JsonProcessingException {
377 return Arrays.asList();
379 S builder = findPOJOBuilder(clazz);
380 if (builder == null && column == null) {
381 throw new InstantiationException("unable to find builder for class " + clazz.getName());
384 List<T> list = new ArrayList<>();
385 while (data.next()) {
386 if (column == null) {
389 for (Method m : getFilteredMethods(builder.getClass(), false)) {
390 argType = m.getParameterTypes()[0];
391 col = getColumnName(m);
392 m.setAccessible(true);
393 m.invoke(builder, getValueOrDefault(data, col, argType, null));
395 list.add(callBuild(builder));
397 Object value = getValueOrDefault(data, column, clazz, null);
406 @SuppressWarnings("unchecked")
407 private static <S, T> T callBuild(S builder) throws NoSuchMethodException, SecurityException,
408 IllegalAccessException, IllegalArgumentException, InvocationTargetException {
409 Method method = builder.getClass().getMethod("build");
410 return (T) method.invoke(builder);
413 @SuppressWarnings("unchecked")
414 private static <S, T> S findPOJOBuilder(Class<T> ac) throws InstantiationException, IllegalAccessException,
415 IllegalArgumentException, InvocationTargetException, SecurityException, NoSuchMethodException {
417 String builder = null;
419 if (ac.isInterface()) {
420 String clsName = ac.getName();
421 if (clsName.endsWith("Entity")) {
422 clsName = clsName.substring(0, clsName.length() - 6);
424 builder = clsName + "Builder";
426 if (builder != null) {
427 Class<?> innerBuilder = YangToolsMapperHelper.findClass(builder);
428 // Class<Builder<T>> builderClass = (Class<Builder<T>>) innerBuilder;
429 return (S) innerBuilder.getDeclaredConstructor().newInstance();
431 } catch (ClassNotFoundException e) {
437 private static Object getValueOrDefault(ResultSet data, String col, Class<?> dstType, Object defaultValue)
438 throws SQLException, JsonMappingException, JsonProcessingException {
439 if (isBoolean(dstType)) {
440 return data.getBoolean(col);
441 } else if (isNumeric(dstType)) {
442 return getNumeric(dstType, data.getLong(col));
443 } else if (String.class.equals(dstType)) {
444 return data.getString(col);
445 } else if (isYangEnum(dstType)) {
446 return getYangEnum(data.getString(col), dstType);
447 } else if (isDateTime(dstType)) {
448 String v = data.getString(col);
449 return v == null || v.equals("null") ? null : DateAndTime.getDefaultInstance(v.replace(" ", "T") + "Z");
450 } else if (isComplex(dstType)) {
451 String v = data.getString(col);
453 return (v == null || v.equalsIgnoreCase("null")) ? null : mapper.readValue(v, dstType);
460 private static Object getYangEnum(String value, Class<?> dstType) {
461 if (value == null || value.equals("null")) {
465 return YangToolsDeserializerModifier.parseEnum(value, dstType);
466 } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException | NoSuchMethodException
467 | SecurityException e) {
468 LOG.warn("unable to parse enum value '{}' to class {}: ", value, dstType, e);
473 private static Object getNumeric(Class<?> dstType, long value) {
474 if (dstType.equals(Uint64.class)) {
475 return Uint64.valueOf(value);
476 } else if (dstType.equals(Uint32.class)) {
477 return Uint32.valueOf(value);
478 } else if (dstType.equals(Uint16.class)) {
479 return Uint16.valueOf(value);
480 } else if (dstType.equals(Uint8.class)) {
481 return Uint8.valueOf(value);
482 } else if (dstType.equals(Long.class)) {
484 } else if (dstType.equals(Integer.class)) {
486 } else if (dstType.equals(Byte.class)) {
492 public static DBKeyValuePair<String> getEscapedKeyValue(Method m, String col, Object value)
493 throws JsonProcessingException {
494 Class<?> valueType = m.getReturnType();
495 String svalue = null;
496 if (isBoolean(valueType)) {
497 svalue = String.valueOf(bool2int(value));
498 } else if (isNumeric(valueType)) {
499 svalue = String.valueOf(getNumericValue(value, valueType));
500 } else if (isDateTime(valueType)) {
501 svalue = "'" + getDateTimeValue((DateAndTime) value) + "'";
502 } else if (isComplex(valueType)) {
503 svalue = "'" + escape(mapper.writeValueAsString(value)) + "'";
505 svalue = "'" + escape(value) + "'";
507 return new DBKeyValuePair<>("`" + col + "`", svalue);
510 private static String getDateTimeValue(DateAndTime value) {
511 String s = value.getValue();
512 if (s.endsWith("Z")) {
513 s = s.substring(0, s.length() - 1).replace("T", " ");
514 } else if (s.contains("+")) {
515 s = s.substring(0, s.indexOf("+")).replace("T", " ");