thingsboard-aplcache

diff --git a/application/pom.xml b/application/pom.xml
index df6195f..eb1971f 100644
--- a/application/pom.xml
+++ b/application/pom.xml
@@ -249,11 +249,6 @@
             <artifactId>spring-test-dbunit</artifactId>
             <scope>test</scope>
         </dependency>
-        <dependency>
-            <groupId>ru.yandex.qatools.embed</groupId>
-            <artifactId>postgresql-embedded</artifactId>
-            <scope>test</scope>
-        </dependency>
     </dependencies>
 
     <build>

diff --git a/application/src/main/resources/thingsboard.yml b/application/src/main/resources/thingsboard.yml
index a7c93a2..a06b9d3 100644
--- a/application/src/main/resources/thingsboard.yml
+++ b/application/src/main/resources/thingsboard.yml
@@ -106,7 +106,7 @@ coap:
   timeout: "${COAP_TIMEOUT:10000}"
 
 database:
-  type: "${DATABASE_TYPE:cassandra}" # cassandra OR postgres
+  type: "${DATABASE_TYPE:cassandra}" # cassandra OR sql
 
 # Cassandra driver configuration parameters
 cassandra:

diff --git a/application/src/test/java/org/thingsboard/server/system/SystemSqlTestSuite.java b/application/src/test/java/org/thingsboard/server/system/SystemSqlTestSuite.java
index 726b205..6a46ede 100644
--- a/application/src/test/java/org/thingsboard/server/system/SystemSqlTestSuite.java
+++ b/application/src/test/java/org/thingsboard/server/system/SystemSqlTestSuite.java
@@ -18,7 +18,7 @@ package org.thingsboard.server.system;
 import org.junit.ClassRule;
 import org.junit.extensions.cpsuite.ClasspathSuite;
 import org.junit.runner.RunWith;
-import org.thingsboard.server.dao.CustomPostgresUnit;
+import org.thingsboard.server.dao.CustomSqlUnit;
 
 import java.util.Arrays;
 
@@ -30,9 +30,10 @@ import java.util.Arrays;
 public class SystemSqlTestSuite {
 
     @ClassRule
-    public static CustomPostgresUnit postgresUnit = new CustomPostgresUnit(
-            Arrays.asList("postgres/schema.sql", "postgres/system-data.sql"),
-            "postgres-embedded-test.properties");
+    public static CustomSqlUnit sqlUnit = new CustomSqlUnit(
+            Arrays.asList("sql/schema.sql", "sql/system-data.sql"),
+            "sql-test.properties",
+            "sql/drop-all-tables.sql");
 
 
 }

diff --git a/dao/pom.xml b/dao/pom.xml
index 2bb7751..ff4237c 100644
--- a/dao/pom.xml
+++ b/dao/pom.xml
@@ -170,8 +170,8 @@
             <scope>test</scope>
         </dependency>
         <dependency>
-            <groupId>ru.yandex.qatools.embed</groupId>
-            <artifactId>postgresql-embedded</artifactId>
+            <groupId>org.hsqldb</groupId>
+            <artifactId>hsqldb</artifactId>
             <scope>test</scope>
         </dependency>
     </dependencies>

diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/alarm/JpaAlarmDao.java b/dao/src/main/java/org/thingsboard/server/dao/sql/alarm/JpaAlarmDao.java
index 5f902bf..79779e8 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/sql/alarm/JpaAlarmDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/alarm/JpaAlarmDao.java
@@ -73,7 +73,6 @@ public class JpaAlarmDao extends JpaAbstractDao<AlarmEntity, Alarm> implements A
     }
 
     @Override
-    @Transactional(propagation = REQUIRES_NEW)
     public ListenableFuture<Alarm> findLatestByOriginatorAndType(TenantId tenantId, EntityId originator, String type) {
         return service.submit(() -> {
             List<AlarmEntity> latest = alarmRepository.findLatestByOriginatorAndType(

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 56eb5e8..feae4a6 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
@@ -80,7 +80,5 @@ public interface DeviceRepository extends CrudRepository<DeviceEntity, UUID> {
 
     List<DeviceEntity> findDevicesByTenantIdAndCustomerIdAndIdIn(UUID tenantId, UUID customerId, List<UUID> deviceIds);
 
-    List<DeviceEntity> findDevicesByTenantId(UUID tenantId);
-
     List<DeviceEntity> findDevicesByTenantIdAndIdIn(UUID tenantId, List<UUID> deviceIds);
 }

diff --git a/dao/src/main/java/org/thingsboard/server/dao/sql/JpaAbstractDao.java b/dao/src/main/java/org/thingsboard/server/dao/sql/JpaAbstractDao.java
index 8abab4a..2fd6c97 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/sql/JpaAbstractDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/sql/JpaAbstractDao.java
@@ -45,7 +45,7 @@ public abstract class JpaAbstractDao<E extends BaseEntity<D>, D>
     protected void setSearchText(E entity) {}
 
     @Override
-    @Transactional(propagation = REQUIRES_NEW)
+    @Transactional
     public D save(D domain) {
         E entity;
         try {
@@ -64,7 +64,6 @@ public abstract class JpaAbstractDao<E extends BaseEntity<D>, D>
     }
 
     @Override
-    @Transactional(propagation = REQUIRES_NEW)
     public D findById(UUID key) {
         log.debug("Get entity by key {}", key);
         E entity = getCrudRepository().findOne(key);
@@ -78,7 +77,7 @@ public abstract class JpaAbstractDao<E extends BaseEntity<D>, D>
     }
 
     @Override
-    @Transactional(propagation = REQUIRES_NEW)
+    @Transactional
     public boolean removeById(UUID key) {
         getCrudRepository().delete(key);
         log.debug("Remove request: {}", key);

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 365b90f..62d1a1b 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
@@ -30,12 +30,12 @@ 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.util.SqlDao;
 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.JpaAbstractDaoListeningExecutorService;
 import org.thingsboard.server.dao.sql.JpaAbstractSearchTimeDao;
+import org.thingsboard.server.dao.util.SqlDao;
 
 import javax.persistence.criteria.CriteriaBuilder;
 import javax.persistence.criteria.CriteriaQuery;

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
index 6abbbbd..2c2e636 100644
--- 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
@@ -17,11 +17,11 @@ package org.thingsboard.server.dao.sql.relation;
 
 import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
 import org.springframework.data.repository.CrudRepository;
-import org.thingsboard.server.dao.util.SqlDao;
+import org.springframework.transaction.annotation.Transactional;
 import org.thingsboard.server.dao.model.sql.RelationCompositeKey;
 import org.thingsboard.server.dao.model.sql.RelationEntity;
+import org.thingsboard.server.dao.util.SqlDao;
 
-import javax.transaction.Transactional;
 import java.util.List;
 import java.util.UUID;
 
@@ -51,5 +51,5 @@ public interface RelationRepository
                                                     String fromType);
 
     @Transactional
-    List<RelationEntity> deleteByFromIdAndFromType(UUID fromId, String fromType);
+    void deleteByFromIdAndFromType(UUID fromId, String fromType);
 }

diff --git a/dao/src/main/java/org/thingsboard/server/dao/util/SqlDao.java b/dao/src/main/java/org/thingsboard/server/dao/util/SqlDao.java
index 88a107b..1ebc271 100644
--- a/dao/src/main/java/org/thingsboard/server/dao/util/SqlDao.java
+++ b/dao/src/main/java/org/thingsboard/server/dao/util/SqlDao.java
@@ -17,6 +17,6 @@ package org.thingsboard.server.dao.util;
 
 import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
 
-@ConditionalOnProperty(prefix = "database", value = "type", havingValue = "postgres")
+@ConditionalOnProperty(prefix = "database", value = "type", havingValue = "sql")
 public @interface SqlDao {
 }

diff --git a/dao/src/test/java/org/thingsboard/server/dao/JpaDaoTestSuite.java b/dao/src/test/java/org/thingsboard/server/dao/JpaDaoTestSuite.java
index c74aeef..afa991f 100644
--- a/dao/src/test/java/org/thingsboard/server/dao/JpaDaoTestSuite.java
+++ b/dao/src/test/java/org/thingsboard/server/dao/JpaDaoTestSuite.java
@@ -24,13 +24,14 @@ import java.util.Arrays;
 
 @RunWith(ClasspathSuite.class)
 @ClassnameFilters({
-        "org.thingsboard.server.dao.sql.*Test"
+        "org.thingsboard.server.dao.sql.*ABTest"
 })
 public class JpaDaoTestSuite {
 
     @ClassRule
-    public static CustomPostgresUnit postgresUnit = new CustomPostgresUnit(
-            Arrays.asList("postgres/schema.sql", "postgres/system-data.sql"),
-            "postgres-embedded-test.properties");
+    public static CustomSqlUnit sqlUnit = new CustomSqlUnit(
+            Arrays.asList("sql/schema.sql", "sql/system-data.sql"),
+            "sql-test.properties",
+            "sql/drop-all-tables.sql");
 
 }

diff --git a/dao/src/test/java/org/thingsboard/server/dao/JpaDbunitTestConfig.java b/dao/src/test/java/org/thingsboard/server/dao/JpaDbunitTestConfig.java
index 0a0f7d8..507eb4d 100644
--- a/dao/src/test/java/org/thingsboard/server/dao/JpaDbunitTestConfig.java
+++ b/dao/src/test/java/org/thingsboard/server/dao/JpaDbunitTestConfig.java
@@ -18,6 +18,7 @@ package org.thingsboard.server.dao;
 import com.github.springtestdbunit.bean.DatabaseConfigBean;
 import com.github.springtestdbunit.bean.DatabaseDataSourceConnectionFactoryBean;
 import org.dbunit.DatabaseUnitException;
+import org.dbunit.ext.hsqldb.HsqldbDataTypeFactory;
 import org.dbunit.ext.postgresql.PostgresqlDataTypeFactory;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.context.annotation.Bean;
@@ -41,7 +42,7 @@ public class JpaDbunitTestConfig {
     @Bean
     public DatabaseConfigBean databaseConfigBean() {
         DatabaseConfigBean databaseConfigBean = new DatabaseConfigBean();
-        databaseConfigBean.setDatatypeFactory(new PostgresqlDataTypeFactory());
+        databaseConfigBean.setDatatypeFactory(new HsqldbDataTypeFactory());
         return databaseConfigBean;
     }
 

diff --git a/dao/src/test/java/org/thingsboard/server/dao/SqlDaoServiceTestSuite.java b/dao/src/test/java/org/thingsboard/server/dao/SqlDaoServiceTestSuite.java
index 6ba4e7a..0f0eacc 100644
--- a/dao/src/test/java/org/thingsboard/server/dao/SqlDaoServiceTestSuite.java
+++ b/dao/src/test/java/org/thingsboard/server/dao/SqlDaoServiceTestSuite.java
@@ -29,8 +29,9 @@ import java.util.Arrays;
 public class SqlDaoServiceTestSuite {
 
     @ClassRule
-    public static CustomPostgresUnit postgresUnit = new CustomPostgresUnit(
-            Arrays.asList("postgres/schema.sql", "postgres/system-data.sql", "postgres/system-test.sql"),
-            "postgres-embedded-test.properties");
+    public static CustomSqlUnit sqlUnit = new CustomSqlUnit(
+            Arrays.asList("sql/schema.sql", "sql/system-data.sql", "sql/system-test.sql"),
+            "sql-test.properties",
+            "sql/drop-all-tables.sql");
 
 }

diff --git a/dao/src/test/resources/sql/drop-all-tables.sql b/dao/src/test/resources/sql/drop-all-tables.sql
new file mode 100644
index 0000000..610653d
--- /dev/null
+++ b/dao/src/test/resources/sql/drop-all-tables.sql
@@ -0,0 +1,20 @@
+TRUNCATE TABLE admin_settings;
+TRUNCATE TABLE alarm;
+TRUNCATE TABLE asset;
+TRUNCATE TABLE attribute_kv;
+TRUNCATE TABLE component_descriptor;
+TRUNCATE TABLE customer;
+TRUNCATE TABLE dashboard;
+TRUNCATE TABLE device;
+TRUNCATE TABLE device_credentials;
+TRUNCATE TABLE event;
+TRUNCATE TABLE plugin;
+TRUNCATE TABLE relation;
+TRUNCATE TABLE rule;
+TRUNCATE TABLE tb_user;
+TRUNCATE TABLE tenant;
+TRUNCATE TABLE ts_kv;
+TRUNCATE TABLE ts_kv_latest;
+TRUNCATE TABLE user_credentials;
+TRUNCATE TABLE widget_type;
+TRUNCATE TABLE widgets_bundle;
\ No newline at end of file

diff --git a/dao/src/test/resources/sql-test.properties b/dao/src/test/resources/sql-test.properties
index 93402cf..16a9d8c 100644
--- a/dao/src/test/resources/sql-test.properties
+++ b/dao/src/test/resources/sql-test.properties
@@ -1,8 +1,8 @@
-database.type=postgres
+database.type=sql
 
 spring.jpa.show-sql=false
 spring.jpa.hibernate.ddl-auto=validate
 
-spring.datasource.url=jdbc:postgresql://localhost:5433/thingsboard-test
-spring.datasource.username=postgres
-spring.datasource.password=postgres
+spring.datasource.username=sa
+spring.datasource.password=
+spring.datasource.url=jdbc:hsqldb:mem:thingsboardTestDb;sql.enforce_size=false

diff --git a/pom.xml b/pom.xml
index 7b2c5f8..762d0dc 100755
--- a/pom.xml
+++ b/pom.xml
@@ -71,6 +71,7 @@
         <springfox-swagger.version>2.6.1</springfox-swagger.version>
         <bouncycastle.version>1.56</bouncycastle.version>
         <winsw.version>2.0.1</winsw.version>
+        <hsqldb.version>2.4.0</hsqldb.version>
         <dbunit.version>2.5.3</dbunit.version>
         <spring-test-dbunit.version>1.2.1</spring-test-dbunit.version>
         <postgresql.driver.version>9.4.1211</postgresql.driver.version>
@@ -747,6 +748,12 @@
                 <version>${bouncycastle.version}</version>
             </dependency>
             <dependency>
+                <groupId>org.hsqldb</groupId>
+                <artifactId>hsqldb</artifactId>
+                <version>${hsqldb.version}</version>
+                <scope>test</scope>
+            </dependency>
+            <dependency>
                 <groupId>ru.yandex.qatools.embed</groupId>
                 <artifactId>postgresql-embedded</artifactId>
                 <version>2.2</version>