thingsboard-memoizeit
Changes
dao/src/main/resources/postgres/schema.sql 12(+12 -0)
Details
diff --git a/application/src/main/resources/thingsboard.yml b/application/src/main/resources/thingsboard.yml
index 855e9c8..7976035 100644
--- a/application/src/main/resources/thingsboard.yml
+++ b/application/src/main/resources/thingsboard.yml
@@ -107,7 +107,7 @@ coap:
# Cassandra driver configuration parameters
cassandra:
- enabled: "${CASSANDRA_ENABLED:true}"
+ enabled: "${CASSANDRA_ENABLED:false}"
# Thingsboard cluster name
cluster_name: "${CASSANDRA_CLUSTER_NAME:Thingsboard Cluster}"
# Thingsboard keyspace name
@@ -226,7 +226,7 @@ spring.mvc.cors:
# SQL DAO Configuration
sql:
- enabled: "${SQL_ENABLED:false}"
+ enabled: "${SQL_ENABLED:true}"
spring:
data:
@@ -244,6 +244,6 @@ spring:
url: "${SPRING_DATASOURCE_URL:jdbc:postgresql://localhost:5432/thingsboard}"
username: "${SPRING_DATASOURCE_USERNAME:postgres}"
password: "${SPRING_DATASOURCE_PASSWORD:postgres}"
- autoconfigure:
- exclude:
- - org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration
\ No newline at end of file
+# autoconfigure:
+# exclude:
+# - org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration
\ No newline at end of file
diff --git a/common/data/src/main/java/org/thingsboard/server/common/data/relation/EntityRelation.java b/common/data/src/main/java/org/thingsboard/server/common/data/relation/EntityRelation.java
index 8dab589..478d5c8 100644
--- a/common/data/src/main/java/org/thingsboard/server/common/data/relation/EntityRelation.java
+++ b/common/data/src/main/java/org/thingsboard/server/common/data/relation/EntityRelation.java
@@ -18,8 +18,6 @@ package org.thingsboard.server.common.data.relation;
import com.fasterxml.jackson.databind.JsonNode;
import org.thingsboard.server.common.data.id.EntityId;
-import java.util.Objects;
-
public class EntityRelation {
private static final long serialVersionUID = 2807343040519543363L;
diff --git a/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationCompositeKey.java b/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationCompositeKey.java
new file mode 100644
index 0000000..4af848c
--- /dev/null
+++ b/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationCompositeKey.java
@@ -0,0 +1,42 @@
+/**
+ * Copyright © 2016-2017 The Thingsboard Authors
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.thingsboard.server.dao.model.sql;
+
+import lombok.AllArgsConstructor;
+import org.thingsboard.server.common.data.relation.EntityRelation;
+
+import java.io.Serializable;
+import java.util.UUID;
+
+@AllArgsConstructor
+public class RelationCompositeKey implements Serializable {
+
+ private UUID fromId;
+ private String fromType;
+ private UUID toId;
+ private String toType;
+ private String relationTypeGroup;
+ private String relationType;
+
+ public RelationCompositeKey(EntityRelation relation) {
+ this.fromId = relation.getFrom().getId();
+ this.fromType = relation.getFrom().getEntityType().name();
+ this.toId = relation.getTo().getId();
+ this.toType = relation.getTo().getEntityType().name();
+ this.relationType = relation.getType();
+ this.relationTypeGroup = relation.getTypeGroup().name();
+ }
+}
diff --git a/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationEntity.java b/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationEntity.java
new file mode 100644
index 0000000..1b15aa1
--- /dev/null
+++ b/dao/src/main/java/org/thingsboard/server/dao/model/sql/RelationEntity.java
@@ -0,0 +1,183 @@
+/**
+ * Copyright © 2016-2017 The Thingsboard Authors
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.thingsboard.server.dao.model.sql;
+
+import com.fasterxml.jackson.databind.JsonNode;
+import lombok.Data;
+import org.hibernate.annotations.Type;
+import org.thingsboard.server.common.data.id.EntityIdFactory;
+import org.thingsboard.server.common.data.relation.EntityRelation;
+import org.thingsboard.server.common.data.relation.RelationTypeGroup;
+import org.thingsboard.server.dao.model.ToData;
+
+import javax.persistence.*;
+import java.util.UUID;
+
+import static org.thingsboard.server.dao.model.ModelConstants.*;
+
+@Data
+@Entity
+@Table(name = RELATION_COLUMN_FAMILY_NAME)
+@IdClass(RelationCompositeKey.class)
+public final class RelationEntity implements ToData<EntityRelation> {
+
+ @Transient
+ private static final long serialVersionUID = -4089175869616037592L;
+
+ @Id
+ @Column(name = RELATION_FROM_ID_PROPERTY)
+ private UUID fromId;
+
+ @Id
+ @Column(name = RELATION_FROM_TYPE_PROPERTY)
+ private String fromType;
+
+ @Id
+ @Column(name = RELATION_TO_ID_PROPERTY)
+ private UUID toId;
+
+ @Id
+ @Column(name = RELATION_TO_TYPE_PROPERTY)
+ private String toType;
+
+ @Id
+ @Column(name = RELATION_TYPE_GROUP_PROPERTY)
+ private String relationTypeGroup;
+
+ @Id
+ @Column(name = RELATION_TYPE_PROPERTY)
+ private String relationType;
+
+ @Type(type = "jsonb")
+ @Column(name = ADDITIONAL_INFO_PROPERTY, columnDefinition = "jsonb")
+ private JsonNode additionalInfo;
+
+ public RelationEntity() {
+ super();
+ }
+
+ public RelationEntity(EntityRelation relation) {
+ if (relation.getTo() != null) {
+ this.toId = relation.getTo().getId();
+ this.toType = relation.getTo().getEntityType().name();
+ }
+ if (relation.getFrom() != null) {
+ this.fromId = relation.getFrom().getId();
+ this.fromType = relation.getFrom().getEntityType().name();
+ }
+ this.relationType = relation.getType();
+ this.relationTypeGroup = relation.getTypeGroup().name();
+ this.additionalInfo = relation.getAdditionalInfo();
+ }
+
+ @Override
+ public int hashCode() {
+ final int prime = 31;
+ int result = 1;
+ result = prime * result + ((additionalInfo == null) ? 0 : additionalInfo.hashCode());
+ result = prime * result + ((toId == null) ? 0 : toId.hashCode());
+ result = prime * result + ((toType == null) ? 0 : toType.hashCode());
+ result = prime * result + ((fromId == null) ? 0 : fromId.hashCode());
+ result = prime * result + ((fromType == null) ? 0 : fromType.hashCode());
+ result = prime * result + ((relationType == null) ? 0 : relationType.hashCode());
+ result = prime * result + ((relationTypeGroup == null) ? 0 : relationTypeGroup.hashCode());
+ return result;
+ }
+
+ @Override
+ public boolean equals(Object obj) {
+ if (this == obj)
+ return true;
+ if (obj == null)
+ return false;
+ if (getClass() != obj.getClass())
+ return false;
+ RelationEntity other = (RelationEntity) obj;
+ if (additionalInfo == null) {
+ if (other.additionalInfo != null)
+ return false;
+ } else if (!additionalInfo.equals(other.additionalInfo))
+ return false;
+ if (toId == null) {
+ if (other.toId != null)
+ return false;
+ } else if (!toId.equals(other.toId))
+ return false;
+ if (fromId == null) {
+ if (other.fromId != null)
+ return false;
+ } else if (!fromId.equals(other.fromId))
+ return false;
+ if (toType == null) {
+ if (other.toType != null)
+ return false;
+ } else if (!toType.equals(other.toType))
+ return false;
+ if (fromType == null) {
+ if (other.fromType != null)
+ return false;
+ } else if (!fromType.equals(other.fromType))
+ return false;
+ if (relationType == null) {
+ if (other.relationType != null)
+ return false;
+ } else if (!relationType.equals(other.relationType))
+ return false;
+ if (relationTypeGroup == null) {
+ if (other.relationTypeGroup != null)
+ return false;
+ } else if (!relationTypeGroup.equals(other.relationTypeGroup))
+ return false;
+ return true;
+ }
+
+ @Override
+ public String toString() {
+ StringBuilder builder = new StringBuilder();
+ builder.append("AssetEntity [toId=");
+ builder.append(toId);
+ builder.append(", toType=");
+ builder.append(toType);
+ builder.append(", fromId=");
+ builder.append(fromId);
+ builder.append(", fromType=");
+ builder.append(fromType);
+ builder.append(", relationType=");
+ builder.append(relationType);
+ builder.append(", relationTypeGroup=");
+ builder.append(relationTypeGroup);
+ builder.append(", additionalInfo=");
+ builder.append(additionalInfo);
+ builder.append("]");
+ return builder.toString();
+ }
+
+ @Override
+ public EntityRelation toData() {
+ EntityRelation relation = new EntityRelation();
+ if (toId != null && toType != null) {
+ relation.setTo(EntityIdFactory.getByTypeAndUuid(toType, toId));
+ }
+ if (fromId != null && fromType != null) {
+ relation.setFrom(EntityIdFactory.getByTypeAndUuid(fromType, fromId));
+ }
+ relation.setType(relationType);
+ relation.setTypeGroup(RelationTypeGroup.valueOf(relationTypeGroup));
+ relation.setAdditionalInfo(additionalInfo);
+ return relation;
+ }
+
+}
\ No newline at end of file
diff --git a/dao/src/main/java/org/thingsboard/server/dao/model/sql/TenantDeviceTypeProjection.java b/dao/src/main/java/org/thingsboard/server/dao/model/sql/TenantDeviceTypeProjection.java
new file mode 100644
index 0000000..c38e68f
--- /dev/null
+++ b/dao/src/main/java/org/thingsboard/server/dao/model/sql/TenantDeviceTypeProjection.java
@@ -0,0 +1,23 @@
+/**
+ * Copyright © 2016-2017 The Thingsboard Authors
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.thingsboard.server.dao.model.sql;
+
+public interface TenantDeviceTypeProjection {
+
+ String getTenantId();
+
+ String getType();
+}
diff --git a/dao/src/main/java/org/thingsboard/server/dao/relation/BaseRelationDao.java b/dao/src/main/java/org/thingsboard/server/dao/relation/BaseRelationDao.java
index ff4b2f8..8415bc9 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/relation/BaseRelationDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/relation/BaseRelationDao.java
@@ -30,8 +30,8 @@ import org.thingsboard.server.common.data.id.EntityId;
import org.thingsboard.server.common.data.id.EntityIdFactory;
import org.thingsboard.server.common.data.page.TimePageLink;
import org.thingsboard.server.common.data.relation.EntityRelation;
-import org.thingsboard.server.dao.CassandraAbstractAsyncDao;
import org.thingsboard.server.common.data.relation.RelationTypeGroup;
+import org.thingsboard.server.dao.CassandraAbstractAsyncDao;
import org.thingsboard.server.dao.CassandraAbstractSearchTimeDao;
import org.thingsboard.server.dao.model.ModelConstants;
import org.thingsboard.server.dao.model.type.RelationTypeGroupCodec;
@@ -43,7 +43,6 @@ import java.util.Arrays;
import java.util.List;
import static com.datastax.driver.core.querybuilder.QueryBuilder.eq;
-import static org.thingsboard.server.dao.model.ModelConstants.RELATION_COLUMN_FAMILY_NAME;
/**
* Created by ashvayka on 25.04.17.
diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/device/DeviceRepository.java b/dao/src/main/java/org/thingsboard/server/dao/sql/device/DeviceRepository.java
index dde7faf..343efc4 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/sql/device/DeviceRepository.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/device/DeviceRepository.java
@@ -19,9 +19,11 @@ import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
-import org.thingsboard.server.common.data.TenantDeviceType;
+import org.springframework.stereotype.Repository;
import org.thingsboard.server.dao.model.sql.DeviceEntity;
+import org.thingsboard.server.dao.model.sql.TenantDeviceTypeProjection;
+import java.util.Collection;
import java.util.List;
import java.util.UUID;
@@ -72,8 +74,12 @@ public interface DeviceRepository extends CrudRepository<DeviceEntity, UUID> {
@Param("textSearch") String textSearch,
@Param("idOffset") UUID idOffset);
- @Query(nativeQuery = true, value = "SELECT DISTINCT TYPE, TENANT_ID FROM DEVICE")
- List<TenantDeviceType> findTenantDeviceTypes();
+ // TODO: CAST was used because in other case when you try convert directly UUID field to UUID java object error throwed:
+ // org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
+ // I suppose that Spring Projection doesn't support correct mapping for this type of column
+ // and only Entity at the moment supports UUID
+ @Query(value = "SELECT DISTINCT CAST(TENANT_ID as VARCHAR) as tenantId, TYPE as type FROM DEVICE", nativeQuery = true)
+ List<TenantDeviceTypeProjection> findTenantDeviceTypes();
DeviceEntity findByTenantIdAndName(UUID tenantId, String name);
diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/device/JpaDeviceDao.java b/dao/src/main/java/org/thingsboard/server/dao/sql/device/JpaDeviceDao.java
index cc16dbc..5904183 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/sql/device/JpaDeviceDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/device/JpaDeviceDao.java
@@ -22,15 +22,15 @@ import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Component;
import org.thingsboard.server.common.data.Device;
import org.thingsboard.server.common.data.TenantDeviceType;
+import org.thingsboard.server.common.data.id.TenantId;
import org.thingsboard.server.common.data.page.TextPageLink;
import org.thingsboard.server.dao.DaoUtil;
import org.thingsboard.server.dao.device.DeviceDao;
import org.thingsboard.server.dao.model.sql.DeviceEntity;
+import org.thingsboard.server.dao.model.sql.TenantDeviceTypeProjection;
import org.thingsboard.server.dao.sql.JpaAbstractSearchTextDao;
-import java.util.List;
-import java.util.Optional;
-import java.util.UUID;
+import java.util.*;
import static org.thingsboard.server.dao.model.ModelConstants.NULL_UUID;
@@ -121,6 +121,17 @@ public class JpaDeviceDao extends JpaAbstractSearchTextDao<DeviceEntity, Device>
@Override
public ListenableFuture<List<TenantDeviceType>> findTenantDeviceTypesAsync() {
- return service.submit(() -> deviceRepository.findTenantDeviceTypes());
+ return service.submit(() -> convertTenantDeviceTypeToDto(deviceRepository.findTenantDeviceTypes()));
+ }
+
+ private List<TenantDeviceType> convertTenantDeviceTypeToDto(List<TenantDeviceTypeProjection> resultSet) {
+ List<TenantDeviceType> list = Collections.emptyList();
+ if (resultSet != null && !resultSet.isEmpty()) {
+ list = new ArrayList<>();
+ for (TenantDeviceTypeProjection object : resultSet) {
+ list.add(new TenantDeviceType(object.getType(), new TenantId(UUID.fromString(object.getTenantId()))));
+ }
+ }
+ return list;
}
}
diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/relation/JpaRelationDao.java b/dao/src/main/java/org/thingsboard/server/dao/sql/relation/JpaRelationDao.java
index 7841463..b320f96 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/sql/relation/JpaRelationDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/relation/JpaRelationDao.java
@@ -5,7 +5,7 @@
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
- * http://www.apache.org/licenses/LICENSE-2.0
+ * http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
@@ -16,17 +16,27 @@
package org.thingsboard.server.dao.sql.relation;
import com.google.common.util.concurrent.ListenableFuture;
+import com.google.common.util.concurrent.ListeningExecutorService;
+import com.google.common.util.concurrent.MoreExecutors;
import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
+import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Component;
import org.thingsboard.server.common.data.EntityType;
import org.thingsboard.server.common.data.id.EntityId;
import org.thingsboard.server.common.data.page.TimePageLink;
import org.thingsboard.server.common.data.relation.EntityRelation;
import org.thingsboard.server.common.data.relation.RelationTypeGroup;
+import org.thingsboard.server.dao.DaoUtil;
+import org.thingsboard.server.dao.model.sql.RelationCompositeKey;
+import org.thingsboard.server.dao.model.sql.RelationEntity;
import org.thingsboard.server.dao.relation.RelationDao;
+import org.thingsboard.server.dao.sql.JpaAbstractDao;
import java.util.List;
+import java.util.UUID;
+import java.util.concurrent.Executors;
/**
* Created by Valerii Sosliuk on 5/29/2017.
@@ -36,64 +46,119 @@ import java.util.List;
@ConditionalOnProperty(prefix = "sql", value = "enabled", havingValue = "true", matchIfMissing = false)
public class JpaRelationDao implements RelationDao {
+ @Autowired
+ private RelationRepository relationRepository;
+
+ private ListeningExecutorService executorService = MoreExecutors.listeningDecorator(Executors.newFixedThreadPool(10));
@Override
public ListenableFuture<List<EntityRelation>> findAllByFrom(EntityId from, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ return executorService.submit(() -> DaoUtil.convertDataList(
+ relationRepository.findAllByFromIdAndFromTypeAndRelationTypeGroup(
+ from.getId(),
+ from.getEntityType().name(),
+ typeGroup.name())));
}
@Override
public ListenableFuture<List<EntityRelation>> findAllByFromAndType(EntityId from, String relationType, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ return executorService.submit(() -> DaoUtil.convertDataList(
+ relationRepository.findAllByFromIdAndFromTypeAndRelationTypeAndRelationTypeGroup(
+ from.getId(),
+ from.getEntityType().name(),
+ relationType,
+ typeGroup.name())));
}
@Override
public ListenableFuture<List<EntityRelation>> findAllByTo(EntityId to, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ return executorService.submit(() -> DaoUtil.convertDataList(
+ relationRepository.findAllByToIdAndToTypeAndRelationTypeGroup(
+ to.getId(),
+ to.getEntityType().name(),
+ typeGroup.name())));
}
@Override
public ListenableFuture<List<EntityRelation>> findAllByToAndType(EntityId to, String relationType, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ return executorService.submit(() -> DaoUtil.convertDataList(
+ relationRepository.findAllByToIdAndToTypeAndRelationTypeAndRelationTypeGroup(
+ to.getId(),
+ to.getEntityType().name(),
+ relationType,
+ typeGroup.name())));
}
@Override
public ListenableFuture<Boolean> checkRelation(EntityId from, EntityId to, String relationType, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ RelationCompositeKey key =
+ new RelationCompositeKey(from.getId(),
+ from.getEntityType().name(),
+ to.getId(),
+ to.getEntityType().name(),
+ relationType,
+ typeGroup.name());
+ return executorService.submit(() -> relationRepository.findOne(key) != null);
}
@Override
public ListenableFuture<Boolean> saveRelation(EntityRelation relation) {
- // TODO: Implement
- return null;
+ return executorService.submit(() -> relationRepository.save(new RelationEntity(relation)) != null);
}
@Override
public ListenableFuture<Boolean> deleteRelation(EntityRelation relation) {
- // TODO: Implement
- return null;
+ RelationCompositeKey key = new RelationCompositeKey(relation);
+ return executorService.submit(
+ () -> {
+ relationRepository.delete(key);
+ return !relationRepository.exists(key);
+ });
}
@Override
public ListenableFuture<Boolean> deleteRelation(EntityId from, EntityId to, String relationType, RelationTypeGroup typeGroup) {
- // TODO: Implement
- return null;
+ RelationCompositeKey key =
+ new RelationCompositeKey(from.getId(),
+ from.getEntityType().name(),
+ to.getId(),
+ to.getEntityType().name(),
+ relationType,
+ typeGroup.name());
+ return executorService.submit(
+ () -> {
+ boolean result = relationRepository.exists(key);
+ relationRepository.delete(key);
+ return result;
+ });
}
@Override
public ListenableFuture<Boolean> deleteOutboundRelations(EntityId entity) {
- // TODO: Implement
- return null;
+ RelationEntity relationEntity = new RelationEntity();
+ relationEntity.setFromId(entity.getId());
+ relationEntity.setFromType(entity.getEntityType().name());
+
+ return executorService.submit(
+ () -> {
+ boolean result = relationRepository
+ .findAllByFromIdAndFromType(relationEntity.getFromId(), relationEntity.getFromType())
+ .size() > 0;
+ relationRepository.delete(relationEntity);
+ return result;
+ });
}
@Override
- public ListenableFuture<List<EntityRelation>> findRelations(EntityId from, String relationType, RelationTypeGroup typeGroup, EntityType toType, TimePageLink pageLink) {
- // TODO: Implement
+ public ListenableFuture<List<EntityRelation>> findRelations(EntityId from, String relationType, RelationTypeGroup typeGroup, EntityType childType, TimePageLink pageLink) {
+// executorService.submit(() -> DaoUtil.convertDataList(
+// relationRepository.findRelations(
+// to.getId(),
+// to.getEntityType().name(),
+// relationType,
+// typeGroup.name())));
return null;
}
+
+
}
diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/relation/RelationRepository.java b/dao/src/main/java/org/thingsboard/server/dao/sql/relation/RelationRepository.java
new file mode 100644
index 0000000..fff147d
--- /dev/null
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/relation/RelationRepository.java
@@ -0,0 +1,107 @@
+/**
+ * Copyright © 2016-2017 The Thingsboard Authors
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.thingsboard.server.dao.sql.relation;
+
+import com.datastax.driver.core.querybuilder.QueryBuilder;
+import com.datastax.driver.core.querybuilder.Select;
+import com.google.common.util.concurrent.ListenableFuture;
+import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.CrudRepository;
+import org.springframework.data.repository.query.Param;
+import org.thingsboard.server.common.data.EntityType;
+import org.thingsboard.server.common.data.id.EntityId;
+import org.thingsboard.server.common.data.page.TimePageLink;
+import org.thingsboard.server.common.data.relation.EntityRelation;
+import org.thingsboard.server.common.data.relation.RelationTypeGroup;
+import org.thingsboard.server.dao.CassandraAbstractSearchTimeDao;
+import org.thingsboard.server.dao.model.ModelConstants;
+import org.thingsboard.server.dao.model.sql.RelationCompositeKey;
+import org.thingsboard.server.dao.model.sql.RelationEntity;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.UUID;
+
+import static com.datastax.driver.core.querybuilder.QueryBuilder.eq;
+
+@ConditionalOnProperty(prefix = "sql", value = "enabled", havingValue = "true")
+public interface RelationRepository extends CrudRepository<RelationEntity, RelationCompositeKey> {
+
+ List<RelationEntity> findAllByFromIdAndFromTypeAndRelationTypeGroup(UUID fromId,
+ String fromType,
+ String relationTypeGroup);
+
+ List<RelationEntity> findAllByFromIdAndFromTypeAndRelationTypeAndRelationTypeGroup(UUID fromId,
+ String fromType,
+ String relationType,
+ String relationTypeGroup);
+
+ List<RelationEntity> findAllByToIdAndToTypeAndRelationTypeGroup(UUID toId,
+ String toType,
+ String relationTypeGroup);
+
+ List<RelationEntity> findAllByToIdAndToTypeAndRelationTypeAndRelationTypeGroup(UUID toId,
+ String toType,
+ String relationType,
+ String relationTypeGroup);
+
+ List<RelationEntity> findAllByFromIdAndFromType(UUID fromId,
+ String fromType);
+
+ @Query(nativeQuery = true, value = "SELECT * FROM DEVICE WHERE TENANT_ID = :tenantId " +
+ "AND CUSTOMER_ID = :customerId " +
+ "AND LOWER(SEARCH_TEXT) LIKE LOWER(CONCAT(:searchText, '%')) " +
+ "AND ID > :idOffset ORDER BY ID LIMIT :limit")
+ List<RelationEntity> findRelations(@Param("fromId") UUID fromId,
+ @Param("fromType") String fromType,
+ @Param("toType") String toType,
+ @Param("relationType") String relationType,
+ @Param("relationTypeGroup") String relationTypeGroup,
+ TimePageLink pageLink);
+
+
+// Select.Where query = CassandraAbstractSearchTimeDao.buildQuery(ModelConstants.RELATION_BY_TYPE_AND_CHILD_TYPE_VIEW_NAME,
+// Arrays.asList(eq(ModelConstants.RELATION_FROM_ID_PROPERTY, from.getId()),
+// eq(ModelConstants.RELATION_FROM_TYPE_PROPERTY, from.getEntityType().name()),
+// eq(ModelConstants.RELATION_TYPE_GROUP_PROPERTY, typeGroup.name()),
+// eq(ModelConstants.RELATION_TYPE_PROPERTY, relationType),
+// eq(ModelConstants.RELATION_TO_TYPE_PROPERTY, childType.name())),
+// Arrays.asList(QueryBuilder.asc(ModelConstants.RELATION_TYPE_GROUP_PROPERTY),
+// QueryBuilder.asc(ModelConstants.RELATION_TYPE_PROPERTY),
+// QueryBuilder.asc(ModelConstants.RELATION_TO_TYPE_PROPERTY)),
+// pageLink, ModelConstants.RELATION_TO_ID_PROPERTY);
+}
+
+// private UUID fromId;
+// private String fromType;
+// private UUID toId;
+// private String toType;
+// private String relationTypeGroup;
+// private String relationType;
+//
+//
+// ListenableFuture<Boolean> checkRelation(EntityId from, EntityId to, String relationType, RelationTypeGroup typeGroup);
+//
+// ListenableFuture<Boolean> saveRelation(EntityRelation relation);
+//
+// ListenableFuture<Boolean> deleteRelation(EntityRelation relation);
+//
+// ListenableFuture<Boolean> deleteRelation(EntityId from, EntityId to, String relationType, RelationTypeGroup typeGroup);
+//
+// ListenableFuture<Boolean> deleteOutboundRelations(EntityId entity);
+//
+// ListenableFuture<List<EntityRelation>> findRelations(EntityId from, String relationType, RelationTypeGroup typeGroup, EntityType toType, TimePageLink pageLink);
\ No newline at end of file
dao/src/main/resources/postgres/schema.sql 12(+12 -0)
diff --git a/dao/src/main/resources/postgres/schema.sql b/dao/src/main/resources/postgres/schema.sql
index 3a2891a..afaf936 100644
--- a/dao/src/main/resources/postgres/schema.sql
+++ b/dao/src/main/resources/postgres/schema.sql
@@ -51,6 +51,18 @@ CREATE TABLE IF NOT EXISTS alarm (
);
ALTER TABLE alarm OWNER TO postgres;
+CREATE TABLE IF NOT EXISTS relation (
+ from_id uuid,
+ from_type character varying(255),
+ to_id uuid,
+ to_type character varying(255),
+ relation_type_group character varying(255),
+ relation_type character varying(255),
+ additional_info jsonb,
+ CONSTRAINT relation_unq_key UNIQUE (from_id, from_type, relation_type_group, relation_type, to_id, to_type)
+);
+ALTER TABLE relation OWNER TO postgres;
+
CREATE TABLE IF NOT EXISTS asset (
id uuid NOT NULL CONSTRAINT asset_pkey PRIMARY KEY,
additional_info jsonb,