thingsboard-aplcache
Changes
application/src/main/java/org/thingsboard/server/service/install/CassandraAbstractDatabaseSchemaService.java 56(+56 -0)
application/src/main/java/org/thingsboard/server/service/install/CassandraDatabaseSchemaService.java 28(+7 -21)
application/src/main/java/org/thingsboard/server/service/install/CassandraEntityDatabaseSchemaService.java 25(+25 -0)
application/src/main/java/org/thingsboard/server/service/install/CassandraTsDatabaseSchemaService.java 25(+25 -0)
application/src/main/java/org/thingsboard/server/service/install/HybridDatabaseSchemaService.java 44(+44 -0)
application/src/main/java/org/thingsboard/server/service/install/SqlAbstractDatabaseSchemaService.java 65(+65 -0)
application/src/main/java/org/thingsboard/server/service/install/SqlDatabaseSchemaService.java 34(+6 -28)
application/src/main/java/org/thingsboard/server/service/install/SqlEntityDatabaseSchemaService.java 25(+25 -0)
application/src/main/java/org/thingsboard/server/service/install/SqlTsDatabaseSchemaService.java 25(+25 -0)
dao/src/main/resources/cassandra/schema-entities.cql 605(+605 -0)
dao/src/main/resources/sql/schema-entities.sql 229(+229 -0)
dao/src/main/resources/sql/schema-ts.sql 39(+39 -0)
Details
diff --git a/application/src/main/java/org/thingsboard/server/install/ThingsboardInstallService.java b/application/src/main/java/org/thingsboard/server/install/ThingsboardInstallService.java
index 884afc0..f863d0b 100644
--- a/application/src/main/java/org/thingsboard/server/install/ThingsboardInstallService.java
+++ b/application/src/main/java/org/thingsboard/server/install/ThingsboardInstallService.java
@@ -116,7 +116,7 @@ public class ThingsboardInstallService {
log.info("Installing DataBase schema...");
- databaseSchemaService.createDatabaseSchema();//TODO issue 1005 - create both SQL and C* schemas in hybrid mode
+ databaseSchemaService.createDatabaseSchema();
log.info("Loading system data...");
diff --git a/application/src/main/java/org/thingsboard/server/service/install/CassandraAbstractDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/CassandraAbstractDatabaseSchemaService.java
new file mode 100644
index 0000000..d8d4744
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/CassandraAbstractDatabaseSchemaService.java
@@ -0,0 +1,56 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.thingsboard.server.dao.cassandra.CassandraInstallCluster;
+import org.thingsboard.server.service.install.cql.CQLStatementsParser;
+
+import java.nio.file.Path;
+import java.nio.file.Paths;
+import java.util.List;
+
+@Slf4j
+public abstract class CassandraAbstractDatabaseSchemaService /*implements DatabaseSchemaService*/ {
+
+ private static final String CASSANDRA_DIR = "cassandra";
+
+ @Autowired
+ private CassandraInstallCluster cluster;
+
+ @Autowired
+ private InstallScripts installScripts;
+
+ private final String schemaCql;
+
+ protected CassandraAbstractDatabaseSchemaService(String schemaCql) {
+ this.schemaCql = schemaCql;
+ }
+
+ //@Override
+ public void createDatabaseSchema() throws Exception {
+ log.info("Installing Cassandra DataBase schema part: " + schemaCql);
+ Path schemaFile = Paths.get(installScripts.getDataDir(), CASSANDRA_DIR, schemaCql);
+ loadCql(schemaFile);
+
+ }
+
+ private void loadCql(Path cql) throws Exception {
+ List<String> statements = new CQLStatementsParser(cql).getStatements();
+ statements.forEach(statement -> cluster.getSession().execute(statement));
+ }
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/CassandraDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/CassandraDatabaseSchemaService.java
index 6eeeb0a..0735a59 100644
--- a/application/src/main/java/org/thingsboard/server/service/install/CassandraDatabaseSchemaService.java
+++ b/application/src/main/java/org/thingsboard/server/service/install/CassandraDatabaseSchemaService.java
@@ -19,39 +19,25 @@ import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Service;
-import org.thingsboard.server.dao.cassandra.CassandraInstallCluster;
-import org.thingsboard.server.dao.util.NoSqlAnyDao;
-import org.thingsboard.server.service.install.cql.CQLStatementsParser;
-
-import java.nio.file.Path;
-import java.nio.file.Paths;
-import java.util.List;
+import org.thingsboard.server.dao.util.NoSqlDao;
@Service
-@NoSqlAnyDao
+@NoSqlDao
@Profile("install")
@Slf4j
public class CassandraDatabaseSchemaService implements DatabaseSchemaService {
- private static final String CASSANDRA_DIR = "cassandra";
- private static final String SCHEMA_CQL = "schema.cql";
-
@Autowired
- private CassandraInstallCluster cluster;
+ private CassandraEntityDatabaseSchemaService cassandraEntityDatabaseSchemaService;
@Autowired
- private InstallScripts installScripts;
+ private CassandraTsDatabaseSchemaService cassandraTsDatabaseSchemaService;
+
@Override
public void createDatabaseSchema() throws Exception {
log.info("Installing Cassandra DataBase schema...");
- Path schemaFile = Paths.get(installScripts.getDataDir(), CASSANDRA_DIR, SCHEMA_CQL);
- loadCql(schemaFile);
-
- }
-
- private void loadCql(Path cql) throws Exception {
- List<String> statements = new CQLStatementsParser(cql).getStatements();
- statements.forEach(statement -> cluster.getSession().execute(statement));
+ cassandraEntityDatabaseSchemaService.createDatabaseSchema();
+ cassandraTsDatabaseSchemaService.createDatabaseSchema();
}
}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/CassandraEntityDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/CassandraEntityDatabaseSchemaService.java
new file mode 100644
index 0000000..9e4afb1
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/CassandraEntityDatabaseSchemaService.java
@@ -0,0 +1,25 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import org.springframework.stereotype.Service;
+
+@Service
+public class CassandraEntityDatabaseSchemaService extends CassandraAbstractDatabaseSchemaService {
+ public CassandraEntityDatabaseSchemaService() {
+ super("schema-entities.cql");
+ }
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/CassandraTsDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/CassandraTsDatabaseSchemaService.java
new file mode 100644
index 0000000..addc180
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/CassandraTsDatabaseSchemaService.java
@@ -0,0 +1,25 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import org.springframework.stereotype.Service;
+
+@Service
+public class CassandraTsDatabaseSchemaService extends CassandraAbstractDatabaseSchemaService {
+ public CassandraTsDatabaseSchemaService() {
+ super("schema-ts.cql");
+ }
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/HybridDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/HybridDatabaseSchemaService.java
new file mode 100644
index 0000000..e689c70
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/HybridDatabaseSchemaService.java
@@ -0,0 +1,44 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.context.annotation.Profile;
+import org.springframework.stereotype.Service;
+import org.thingsboard.server.dao.util.HybridDao;
+
+@Service
+@Profile("install")
+@Slf4j
+@HybridDao
+public class HybridDatabaseSchemaService implements DatabaseSchemaService {
+
+ @Autowired
+ private SqlEntityDatabaseSchemaService sqlEntityDatabaseSchemaService;
+
+ @Autowired
+ private CassandraTsDatabaseSchemaService cassandraTsDatabaseSchemaService;
+
+
+ @Override
+ public void createDatabaseSchema() throws Exception {
+ log.info("Installing Hybrid SQL/Cassandra DataBase schema...");
+ sqlEntityDatabaseSchemaService.createDatabaseSchema();
+ cassandraTsDatabaseSchemaService.createDatabaseSchema();
+ }
+
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/SqlAbstractDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/SqlAbstractDatabaseSchemaService.java
new file mode 100644
index 0000000..f468a6e
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/SqlAbstractDatabaseSchemaService.java
@@ -0,0 +1,65 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import lombok.extern.slf4j.Slf4j;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.beans.factory.annotation.Value;
+
+import java.nio.charset.Charset;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.nio.file.Paths;
+import java.sql.Connection;
+import java.sql.DriverManager;
+
+@Slf4j
+public abstract class SqlAbstractDatabaseSchemaService /*implements DatabaseSchemaService*/ {
+
+ private static final String SQL_DIR = "sql";
+
+ @Value("${spring.datasource.url}")
+ private String dbUrl;
+
+ @Value("${spring.datasource.username}")
+ private String dbUserName;
+
+ @Value("${spring.datasource.password}")
+ private String dbPassword;
+
+ @Autowired
+ private InstallScripts installScripts;
+
+ private final String schemaSql;
+
+ protected SqlAbstractDatabaseSchemaService(String schemaSql) {
+ this.schemaSql = schemaSql;
+ }
+
+ //@Override
+ public void createDatabaseSchema() throws Exception {
+
+ log.info("Installing SQL DataBase schema part: " + schemaSql);
+
+ Path schemaFile = Paths.get(installScripts.getDataDir(), SQL_DIR, schemaSql);
+ try (Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword)) {
+ String sql = new String(Files.readAllBytes(schemaFile), Charset.forName("UTF-8"));
+ conn.createStatement().execute(sql); //NOSONAR, ignoring because method used to load initial thingsboard database schema
+ }
+
+ }
+
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/SqlDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/SqlDatabaseSchemaService.java
index 1daf660..8544b8b 100644
--- a/application/src/main/java/org/thingsboard/server/service/install/SqlDatabaseSchemaService.java
+++ b/application/src/main/java/org/thingsboard/server/service/install/SqlDatabaseSchemaService.java
@@ -17,50 +17,28 @@ package org.thingsboard.server.service.install;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
-import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Service;
import org.thingsboard.server.dao.util.SqlDao;
-import java.nio.charset.Charset;
-import java.nio.file.Files;
-import java.nio.file.Path;
-import java.nio.file.Paths;
-import java.sql.Connection;
-import java.sql.DriverManager;
-
@Service
@Profile("install")
@Slf4j
@SqlDao
public class SqlDatabaseSchemaService implements DatabaseSchemaService {
- private static final String SQL_DIR = "sql";
- private static final String SCHEMA_SQL = "schema.sql";
-
- @Value("${spring.datasource.url}")
- private String dbUrl;
-
- @Value("${spring.datasource.username}")
- private String dbUserName;
-
- @Value("${spring.datasource.password}")
- private String dbPassword;
+ @Autowired
+ private SqlEntityDatabaseSchemaService sqlEntityDatabaseSchemaService;
@Autowired
- private InstallScripts installScripts;
+ private SqlTsDatabaseSchemaService sqlTsDatabaseSchemaService;
+
@Override
public void createDatabaseSchema() throws Exception {
-
log.info("Installing SQL DataBase schema...");
-
- Path schemaFile = Paths.get(installScripts.getDataDir(), SQL_DIR, SCHEMA_SQL);
- try (Connection conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword)) {
- String sql = new String(Files.readAllBytes(schemaFile), Charset.forName("UTF-8"));
- conn.createStatement().execute(sql); //NOSONAR, ignoring because method used to load initial thingsboard database schema
- }
-
+ sqlEntityDatabaseSchemaService.createDatabaseSchema();
+ sqlTsDatabaseSchemaService.createDatabaseSchema();
}
}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/SqlEntityDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/SqlEntityDatabaseSchemaService.java
new file mode 100644
index 0000000..0826099
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/SqlEntityDatabaseSchemaService.java
@@ -0,0 +1,25 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import org.springframework.stereotype.Service;
+
+@Service
+public class SqlEntityDatabaseSchemaService extends SqlAbstractDatabaseSchemaService {
+ public SqlEntityDatabaseSchemaService() {
+ super("schema-entities.sql");
+ }
+}
diff --git a/application/src/main/java/org/thingsboard/server/service/install/SqlTsDatabaseSchemaService.java b/application/src/main/java/org/thingsboard/server/service/install/SqlTsDatabaseSchemaService.java
new file mode 100644
index 0000000..6c8f8b5
--- /dev/null
+++ b/application/src/main/java/org/thingsboard/server/service/install/SqlTsDatabaseSchemaService.java
@@ -0,0 +1,25 @@
+/**
+ * Copyright © 2016-2018 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.service.install;
+
+import org.springframework.stereotype.Service;
+
+@Service
+public class SqlTsDatabaseSchemaService extends SqlAbstractDatabaseSchemaService {
+ public SqlTsDatabaseSchemaService() {
+ super("schema-ts.sql");
+ }
+}
\ No newline at end of file
diff --git a/dao/src/main/java/org/thingsboard/server/dao/util/HybridDao.java b/dao/src/main/java/org/thingsboard/server/dao/util/HybridDao.java
new file mode 100644
index 0000000..2caf8cc
--- /dev/null
+++ b/dao/src/main/java/org/thingsboard/server/dao/util/HybridDao.java
@@ -0,0 +1,22 @@
+/**
+ * Copyright © 2016-2018 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.util;
+
+import org.springframework.boot.autoconfigure.condition.ConditionalOnExpression;
+
+@ConditionalOnExpression("'${database.entity.type}'=='sql' && '${database.ts.type}'=='cassandra'")
+public @interface HybridDao {
+}
dao/src/main/resources/cassandra/schema-entities.cql 605(+605 -0)
diff --git a/dao/src/main/resources/cassandra/schema-entities.cql b/dao/src/main/resources/cassandra/schema-entities.cql
new file mode 100644
index 0000000..e1a21eb
--- /dev/null
+++ b/dao/src/main/resources/cassandra/schema-entities.cql
@@ -0,0 +1,605 @@
+--
+-- Copyright © 2016-2018 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.
+--
+
+CREATE KEYSPACE IF NOT EXISTS thingsboard
+WITH replication = {
+ 'class' : 'SimpleStrategy',
+ 'replication_factor' : 1
+};
+
+CREATE TABLE IF NOT EXISTS thingsboard.user (
+ id timeuuid,
+ tenant_id timeuuid,
+ customer_id timeuuid,
+ email text,
+ search_text text,
+ authority text,
+ first_name text,
+ last_name text,
+ additional_info text,
+ PRIMARY KEY (id, tenant_id, customer_id, authority)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_by_email AS
+ SELECT *
+ from thingsboard.user
+ WHERE email IS NOT NULL AND tenant_id IS NOT NULL AND customer_id IS NOT NULL AND id IS NOT NULL AND authority IS NOT
+ NULL
+ PRIMARY KEY ( email, tenant_id, customer_id, id, authority );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.user
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND authority IS NOT NULL AND search_text IS NOT NULL AND id
+ IS NOT NULL
+ PRIMARY KEY ( tenant_id, customer_id, authority, search_text, id )
+ WITH CLUSTERING ORDER BY ( customer_id DESC, authority DESC, search_text ASC, id DESC );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_by_customer_and_search_text AS
+ SELECT *
+ from thingsboard.user
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND authority IS NOT NULL AND search_text IS NOT NULL AND id
+ IS NOT NULL
+ PRIMARY KEY ( customer_id, tenant_id, authority, search_text, id )
+ WITH CLUSTERING ORDER BY ( tenant_id DESC, authority DESC, search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.user_credentials (
+ id timeuuid PRIMARY KEY,
+ user_id timeuuid,
+ enabled boolean,
+ password text,
+ activate_token text,
+ reset_token text
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_credentials_by_user AS
+ SELECT *
+ from thingsboard.user_credentials
+ WHERE user_id IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( user_id, id );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_credentials_by_activate_token AS
+ SELECT *
+ from thingsboard.user_credentials
+ WHERE activate_token IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( activate_token, id );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.user_credentials_by_reset_token AS
+ SELECT *
+ from thingsboard.user_credentials
+ WHERE reset_token IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( reset_token, id );
+
+CREATE TABLE IF NOT EXISTS thingsboard.admin_settings (
+ id timeuuid PRIMARY KEY,
+ key text,
+ json_value text
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.admin_settings_by_key AS
+ SELECT *
+ from thingsboard.admin_settings
+ WHERE key IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( key, id )
+ WITH CLUSTERING ORDER BY ( id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.tenant (
+ id timeuuid,
+ title text,
+ search_text text,
+ region text,
+ country text,
+ state text,
+ city text,
+ address text,
+ address2 text,
+ zip text,
+ phone text,
+ email text,
+ additional_info text,
+ PRIMARY KEY (id, region)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.tenant_by_region_and_search_text AS
+ SELECT *
+ from thingsboard.tenant
+ WHERE region IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( region, search_text, id )
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.customer (
+ id timeuuid,
+ tenant_id timeuuid,
+ title text,
+ search_text text,
+ country text,
+ state text,
+ city text,
+ address text,
+ address2 text,
+ zip text,
+ phone text,
+ email text,
+ additional_info text,
+ PRIMARY KEY (id, tenant_id)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.customer_by_tenant_and_title AS
+ SELECT *
+ from thingsboard.customer
+ WHERE tenant_id IS NOT NULL AND title IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, title, id )
+ WITH CLUSTERING ORDER BY ( title ASC, id DESC );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.customer_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.customer
+ WHERE tenant_id IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id )
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.device (
+ id timeuuid,
+ tenant_id timeuuid,
+ customer_id timeuuid,
+ name text,
+ type text,
+ search_text text,
+ additional_info text,
+ PRIMARY KEY (id, tenant_id, customer_id, type)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_by_tenant_and_name AS
+ SELECT *
+ from thingsboard.device
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND name IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, name, id, customer_id, type)
+ WITH CLUSTERING ORDER BY ( name ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.device
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id, customer_id, type)
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_by_tenant_by_type_and_search_text AS
+ SELECT *
+ from thingsboard.device
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, type, search_text, id, customer_id)
+ WITH CLUSTERING ORDER BY ( type ASC, search_text ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_by_customer_and_search_text AS
+ SELECT *
+ from thingsboard.device
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( customer_id, tenant_id, search_text, id, type )
+ WITH CLUSTERING ORDER BY ( tenant_id DESC, search_text ASC, id DESC );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_by_customer_by_type_and_search_text AS
+ SELECT *
+ from thingsboard.device
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( customer_id, tenant_id, type, search_text, id )
+ WITH CLUSTERING ORDER BY ( tenant_id DESC, type ASC, search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.device_credentials (
+ id timeuuid PRIMARY KEY,
+ device_id timeuuid,
+ credentials_type text,
+ credentials_id text,
+ credentials_value text
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_credentials_by_device AS
+ SELECT *
+ from thingsboard.device_credentials
+ WHERE device_id IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( device_id, id );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.device_credentials_by_credentials_id AS
+ SELECT *
+ from thingsboard.device_credentials
+ WHERE credentials_id IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( credentials_id, id );
+
+CREATE TABLE IF NOT EXISTS thingsboard.asset (
+ id timeuuid,
+ tenant_id timeuuid,
+ customer_id timeuuid,
+ name text,
+ type text,
+ search_text text,
+ additional_info text,
+ PRIMARY KEY (id, tenant_id, customer_id, type)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.asset_by_tenant_and_name AS
+ SELECT *
+ from thingsboard.asset
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND name IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, name, id, customer_id, type)
+ WITH CLUSTERING ORDER BY ( name ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.asset_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.asset
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id, customer_id, type)
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.asset_by_tenant_by_type_and_search_text AS
+ SELECT *
+ from thingsboard.asset
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, type, search_text, id, customer_id)
+ WITH CLUSTERING ORDER BY ( type ASC, search_text ASC, id DESC, customer_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.asset_by_customer_and_search_text AS
+ SELECT *
+ from thingsboard.asset
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( customer_id, tenant_id, search_text, id, type )
+ WITH CLUSTERING ORDER BY ( tenant_id DESC, search_text ASC, id DESC );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.asset_by_customer_by_type_and_search_text AS
+ SELECT *
+ from thingsboard.asset
+ WHERE tenant_id IS NOT NULL AND customer_id IS NOT NULL AND type IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( customer_id, tenant_id, type, search_text, id )
+ WITH CLUSTERING ORDER BY ( tenant_id DESC, type ASC, search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.entity_subtype (
+ tenant_id timeuuid,
+ entity_type text, // (DEVICE, ASSET)
+ type text,
+ PRIMARY KEY (tenant_id, entity_type, type)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.alarm (
+ id timeuuid,
+ tenant_id timeuuid,
+ type text,
+ originator_id timeuuid,
+ originator_type text,
+ severity text,
+ status text,
+ start_ts bigint,
+ end_ts bigint,
+ ack_ts bigint,
+ clear_ts bigint,
+ details text,
+ propagate boolean,
+ PRIMARY KEY ((tenant_id, originator_id, originator_type), type, id)
+) WITH CLUSTERING ORDER BY ( type ASC, id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.alarm_by_id AS
+ SELECT *
+ from thingsboard.alarm
+ WHERE tenant_id IS NOT NULL AND originator_id IS NOT NULL AND originator_type IS NOT NULL AND type IS NOT NULL
+ AND type IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY (id, tenant_id, originator_id, originator_type, type)
+ WITH CLUSTERING ORDER BY ( tenant_id ASC, originator_id ASC, originator_type ASC, type ASC);
+
+CREATE TABLE IF NOT EXISTS thingsboard.relation (
+ from_id timeuuid,
+ from_type text,
+ to_id timeuuid,
+ to_type text,
+ relation_type_group text,
+ relation_type text,
+ additional_info text,
+ PRIMARY KEY ((from_id, from_type), relation_type_group, relation_type, to_id, to_type)
+) WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, to_id ASC, to_type ASC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.relation_by_type_and_child_type AS
+ SELECT *
+ from thingsboard.relation
+ WHERE from_id IS NOT NULL AND from_type IS NOT NULL AND relation_type_group IS NOT NULL AND relation_type IS NOT NULL AND to_id IS NOT NULL AND to_type IS NOT NULL
+ PRIMARY KEY ((from_id, from_type), relation_type_group, relation_type, to_type, to_id)
+ WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, to_type ASC, to_id DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.reverse_relation AS
+ SELECT *
+ from thingsboard.relation
+ WHERE from_id IS NOT NULL AND from_type IS NOT NULL AND relation_type_group IS NOT NULL AND relation_type IS NOT NULL AND to_id IS NOT NULL AND to_type IS NOT NULL
+ PRIMARY KEY ((to_id, to_type), relation_type_group, relation_type, from_id, from_type)
+ WITH CLUSTERING ORDER BY ( relation_type_group ASC, relation_type ASC, from_id ASC, from_type ASC);
+
+CREATE TABLE IF NOT EXISTS thingsboard.widgets_bundle (
+ id timeuuid,
+ tenant_id timeuuid,
+ alias text,
+ title text,
+ search_text text,
+ image blob,
+ PRIMARY KEY (id, tenant_id)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.widgets_bundle_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.widgets_bundle
+ WHERE tenant_id IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id )
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC );
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.widgets_bundle_by_tenant_and_alias AS
+ SELECT *
+ from thingsboard.widgets_bundle
+ WHERE tenant_id IS NOT NULL AND alias IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, alias, id )
+ WITH CLUSTERING ORDER BY ( alias ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.widget_type (
+ id timeuuid,
+ tenant_id timeuuid,
+ bundle_alias text,
+ alias text,
+ name text,
+ descriptor text,
+ PRIMARY KEY (id, tenant_id, bundle_alias)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.widget_type_by_tenant_and_aliases AS
+ SELECT *
+ from thingsboard.widget_type
+ WHERE tenant_id IS NOT NULL AND bundle_alias IS NOT NULL AND alias IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, bundle_alias, alias, id )
+ WITH CLUSTERING ORDER BY ( bundle_alias ASC, alias ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.dashboard (
+ id timeuuid,
+ tenant_id timeuuid,
+ title text,
+ search_text text,
+ assigned_customers text,
+ configuration text,
+ PRIMARY KEY (id, tenant_id)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.dashboard_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.dashboard
+ WHERE tenant_id IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id )
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.attributes_kv_cf (
+ entity_type text, // (DEVICE, CUSTOMER, TENANT)
+ entity_id timeuuid,
+ attribute_type text, // (CLIENT_SIDE, SHARED, SERVER_SIDE)
+ attribute_key text,
+ bool_v boolean,
+ str_v text,
+ long_v bigint,
+ dbl_v double,
+ last_update_ts bigint,
+ PRIMARY KEY ((entity_type, entity_id, attribute_type), attribute_key)
+) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };
+
+CREATE TABLE IF NOT EXISTS thingsboard.component_descriptor (
+ id timeuuid,
+ type text,
+ scope text,
+ name text,
+ search_text text,
+ clazz text,
+ configuration_descriptor text,
+ actions text,
+ PRIMARY KEY (clazz, id, type, scope)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.component_desc_by_type_search_text AS
+ SELECT *
+ from thingsboard.component_descriptor
+ WHERE type IS NOT NULL AND scope IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL AND clazz IS NOT NULL
+ PRIMARY KEY ( type, search_text, id, clazz, scope)
+ WITH CLUSTERING ORDER BY ( search_text DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.component_desc_by_scope_type_search_text AS
+ SELECT *
+ from thingsboard.component_descriptor
+ WHERE type IS NOT NULL AND scope IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL AND clazz IS NOT NULL
+ PRIMARY KEY ( (scope, type), search_text, id, clazz)
+ WITH CLUSTERING ORDER BY ( search_text DESC);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.component_desc_by_id AS
+ SELECT *
+ from thingsboard.component_descriptor
+ WHERE type IS NOT NULL AND scope IS NOT NULL AND id IS NOT NULL AND clazz IS NOT NULL
+ PRIMARY KEY ( id, clazz, scope, type )
+ WITH CLUSTERING ORDER BY ( clazz ASC, scope ASC, type DESC);
+
+CREATE TABLE IF NOT EXISTS thingsboard.event (
+ tenant_id timeuuid, // tenant or system
+ id timeuuid,
+ event_type text,
+ event_uid text,
+ entity_type text,
+ entity_id timeuuid,
+ body text,
+ PRIMARY KEY ((tenant_id, entity_type, entity_id), event_type, event_uid)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.event_by_type_and_id AS
+ SELECT *
+ FROM thingsboard.event
+ WHERE tenant_id IS NOT NULL AND entity_type IS NOT NULL AND entity_id IS NOT NULL AND id IS NOT NULL
+ AND event_type IS NOT NULL AND event_uid IS NOT NULL
+ PRIMARY KEY ((tenant_id, entity_type, entity_id), event_type, id, event_uid)
+ WITH CLUSTERING ORDER BY (event_type ASC, id ASC, event_uid ASC);
+
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.event_by_id AS
+ SELECT *
+ FROM thingsboard.event
+ WHERE tenant_id IS NOT NULL AND entity_type IS NOT NULL AND entity_id IS NOT NULL AND id IS NOT NULL
+ AND event_type IS NOT NULL AND event_uid IS NOT NULL
+ PRIMARY KEY ((tenant_id, entity_type, entity_id), id, event_type, event_uid)
+ WITH CLUSTERING ORDER BY (id ASC, event_type ASC, event_uid ASC);
+
+CREATE TABLE IF NOT EXISTS thingsboard.audit_log_by_entity_id (
+ tenant_id timeuuid,
+ id timeuuid,
+ customer_id timeuuid,
+ entity_id timeuuid,
+ entity_type text,
+ entity_name text,
+ user_id timeuuid,
+ user_name text,
+ action_type text,
+ action_data text,
+ action_status text,
+ action_failure_details text,
+ PRIMARY KEY ((tenant_id, entity_id, entity_type), id)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.audit_log_by_customer_id (
+ tenant_id timeuuid,
+ id timeuuid,
+ customer_id timeuuid,
+ entity_id timeuuid,
+ entity_type text,
+ entity_name text,
+ user_id timeuuid,
+ user_name text,
+ action_type text,
+ action_data text,
+ action_status text,
+ action_failure_details text,
+ PRIMARY KEY ((tenant_id, customer_id), id)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.audit_log_by_user_id (
+ tenant_id timeuuid,
+ id timeuuid,
+ customer_id timeuuid,
+ entity_id timeuuid,
+ entity_type text,
+ entity_name text,
+ user_id timeuuid,
+ user_name text,
+ action_type text,
+ action_data text,
+ action_status text,
+ action_failure_details text,
+ PRIMARY KEY ((tenant_id, user_id), id)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.audit_log_by_tenant_id (
+ tenant_id timeuuid,
+ id timeuuid,
+ partition bigint,
+ customer_id timeuuid,
+ entity_id timeuuid,
+ entity_type text,
+ entity_name text,
+ user_id timeuuid,
+ user_name text,
+ action_type text,
+ action_data text,
+ action_status text,
+ action_failure_details text,
+ PRIMARY KEY ((tenant_id, partition), id)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.audit_log_by_tenant_id_partitions (
+ tenant_id timeuuid,
+ partition bigint,
+ PRIMARY KEY (( tenant_id ), partition)
+) WITH CLUSTERING ORDER BY ( partition ASC )
+AND compaction = { 'class' : 'LeveledCompactionStrategy' };
+
+CREATE TABLE IF NOT EXISTS thingsboard.msg_queue (
+ node_id timeuuid,
+ cluster_partition bigint,
+ ts_partition bigint,
+ ts bigint,
+ msg blob,
+ PRIMARY KEY ((node_id, cluster_partition, ts_partition), ts))
+WITH CLUSTERING ORDER BY (ts DESC)
+AND compaction = {
+ 'class': 'org.apache.cassandra.db.compaction.DateTieredCompactionStrategy',
+ 'min_threshold': '5',
+ 'base_time_seconds': '43200',
+ 'max_window_size_seconds': '43200',
+ 'tombstone_threshold': '0.9',
+ 'unchecked_tombstone_compaction': 'true'
+};
+
+CREATE TABLE IF NOT EXISTS thingsboard.msg_ack_queue (
+ node_id timeuuid,
+ cluster_partition bigint,
+ ts_partition bigint,
+ msg_id timeuuid,
+ PRIMARY KEY ((node_id, cluster_partition, ts_partition), msg_id))
+WITH CLUSTERING ORDER BY (msg_id DESC)
+AND compaction = {
+ 'class': 'org.apache.cassandra.db.compaction.DateTieredCompactionStrategy',
+ 'min_threshold': '5',
+ 'base_time_seconds': '43200',
+ 'max_window_size_seconds': '43200',
+ 'tombstone_threshold': '0.9',
+ 'unchecked_tombstone_compaction': 'true'
+};
+
+CREATE TABLE IF NOT EXISTS thingsboard.processed_msg_partitions (
+ node_id timeuuid,
+ cluster_partition bigint,
+ ts_partition bigint,
+ PRIMARY KEY ((node_id, cluster_partition), ts_partition))
+WITH CLUSTERING ORDER BY (ts_partition DESC)
+AND compaction = {
+ 'class': 'org.apache.cassandra.db.compaction.DateTieredCompactionStrategy',
+ 'min_threshold': '5',
+ 'base_time_seconds': '43200',
+ 'max_window_size_seconds': '43200',
+ 'tombstone_threshold': '0.9',
+ 'unchecked_tombstone_compaction': 'true'
+};
+
+CREATE TABLE IF NOT EXISTS thingsboard.rule_chain (
+ id uuid,
+ tenant_id uuid,
+ name text,
+ search_text text,
+ first_rule_node_id uuid,
+ root boolean,
+ debug_mode boolean,
+ configuration text,
+ additional_info text,
+ PRIMARY KEY (id, tenant_id)
+);
+
+CREATE MATERIALIZED VIEW IF NOT EXISTS thingsboard.rule_chain_by_tenant_and_search_text AS
+ SELECT *
+ from thingsboard.rule_chain
+ WHERE tenant_id IS NOT NULL AND search_text IS NOT NULL AND id IS NOT NULL
+ PRIMARY KEY ( tenant_id, search_text, id )
+ WITH CLUSTERING ORDER BY ( search_text ASC, id DESC );
+
+CREATE TABLE IF NOT EXISTS thingsboard.rule_node (
+ id uuid,
+ rule_chain_id uuid,
+ type text,
+ name text,
+ debug_mode boolean,
+ search_text text,
+ configuration text,
+ additional_info text,
+ PRIMARY KEY (id)
+);
diff --git a/dao/src/main/resources/cassandra/schema-ts.cql b/dao/src/main/resources/cassandra/schema-ts.cql
new file mode 100644
index 0000000..a5c5ec2
--- /dev/null
+++ b/dao/src/main/resources/cassandra/schema-ts.cql
@@ -0,0 +1,55 @@
+--
+-- Copyright © 2016-2018 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.
+--
+
+CREATE KEYSPACE IF NOT EXISTS thingsboard
+WITH replication = {
+ 'class' : 'SimpleStrategy',
+ 'replication_factor' : 1
+};
+
+CREATE TABLE IF NOT EXISTS thingsboard.ts_kv_cf (
+ entity_type text, // (DEVICE, CUSTOMER, TENANT)
+ entity_id timeuuid,
+ key text,
+ partition bigint,
+ ts bigint,
+ bool_v boolean,
+ str_v text,
+ long_v bigint,
+ dbl_v double,
+ PRIMARY KEY (( entity_type, entity_id, key, partition ), ts)
+);
+
+CREATE TABLE IF NOT EXISTS thingsboard.ts_kv_partitions_cf (
+ entity_type text, // (DEVICE, CUSTOMER, TENANT)
+ entity_id timeuuid,
+ key text,
+ partition bigint,
+ PRIMARY KEY (( entity_type, entity_id, key ), partition)
+) WITH CLUSTERING ORDER BY ( partition ASC )
+ AND compaction = { 'class' : 'LeveledCompactionStrategy' };
+
+CREATE TABLE IF NOT EXISTS thingsboard.ts_kv_latest_cf (
+ entity_type text, // (DEVICE, CUSTOMER, TENANT)
+ entity_id timeuuid,
+ key text,
+ ts bigint,
+ bool_v boolean,
+ str_v text,
+ long_v bigint,
+ dbl_v double,
+ PRIMARY KEY (( entity_type, entity_id ), key)
+) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };
dao/src/main/resources/sql/schema-entities.sql 229(+229 -0)
diff --git a/dao/src/main/resources/sql/schema-entities.sql b/dao/src/main/resources/sql/schema-entities.sql
new file mode 100644
index 0000000..20efec8
--- /dev/null
+++ b/dao/src/main/resources/sql/schema-entities.sql
@@ -0,0 +1,229 @@
+--
+-- Copyright © 2016-2018 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.
+--
+
+
+CREATE TABLE IF NOT EXISTS admin_settings (
+ id varchar(31) NOT NULL CONSTRAINT admin_settings_pkey PRIMARY KEY,
+ json_value varchar,
+ key varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS alarm (
+ id varchar(31) NOT NULL CONSTRAINT alarm_pkey PRIMARY KEY,
+ ack_ts bigint,
+ clear_ts bigint,
+ additional_info varchar,
+ end_ts bigint,
+ originator_id varchar(31),
+ originator_type integer,
+ propagate boolean,
+ severity varchar(255),
+ start_ts bigint,
+ status varchar(255),
+ tenant_id varchar(31),
+ type varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS asset (
+ id varchar(31) NOT NULL CONSTRAINT asset_pkey PRIMARY KEY,
+ additional_info varchar,
+ customer_id varchar(31),
+ name varchar(255),
+ search_text varchar(255),
+ tenant_id varchar(31),
+ type varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS audit_log (
+ id varchar(31) NOT NULL CONSTRAINT audit_log_pkey PRIMARY KEY,
+ tenant_id varchar(31),
+ customer_id varchar(31),
+ entity_id varchar(31),
+ entity_type varchar(255),
+ entity_name varchar(255),
+ user_id varchar(31),
+ user_name varchar(255),
+ action_type varchar(255),
+ action_data varchar(1000000),
+ action_status varchar(255),
+ action_failure_details varchar(1000000)
+);
+
+CREATE TABLE IF NOT EXISTS attribute_kv (
+ entity_type varchar(255),
+ entity_id varchar(31),
+ attribute_type varchar(255),
+ attribute_key varchar(255),
+ bool_v boolean,
+ str_v varchar(10000000),
+ long_v bigint,
+ dbl_v double precision,
+ last_update_ts bigint,
+ CONSTRAINT attribute_kv_unq_key UNIQUE (entity_type, entity_id, attribute_type, attribute_key)
+);
+
+CREATE TABLE IF NOT EXISTS component_descriptor (
+ id varchar(31) NOT NULL CONSTRAINT component_descriptor_pkey PRIMARY KEY,
+ actions varchar(255),
+ clazz varchar,
+ configuration_descriptor varchar,
+ name varchar(255),
+ scope varchar(255),
+ search_text varchar(255),
+ type varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS customer (
+ id varchar(31) NOT NULL CONSTRAINT customer_pkey PRIMARY KEY,
+ additional_info varchar,
+ address varchar,
+ address2 varchar,
+ city varchar(255),
+ country varchar(255),
+ email varchar(255),
+ phone varchar(255),
+ search_text varchar(255),
+ state varchar(255),
+ tenant_id varchar(31),
+ title varchar(255),
+ zip varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS dashboard (
+ id varchar(31) NOT NULL CONSTRAINT dashboard_pkey PRIMARY KEY,
+ configuration varchar(10000000),
+ assigned_customers varchar(1000000),
+ search_text varchar(255),
+ tenant_id varchar(31),
+ title varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS device (
+ id varchar(31) NOT NULL CONSTRAINT device_pkey PRIMARY KEY,
+ additional_info varchar,
+ customer_id varchar(31),
+ type varchar(255),
+ name varchar(255),
+ search_text varchar(255),
+ tenant_id varchar(31)
+);
+
+CREATE TABLE IF NOT EXISTS device_credentials (
+ id varchar(31) NOT NULL CONSTRAINT device_credentials_pkey PRIMARY KEY,
+ credentials_id varchar,
+ credentials_type varchar(255),
+ credentials_value varchar,
+ device_id varchar(31)
+);
+
+CREATE TABLE IF NOT EXISTS event (
+ id varchar(31) NOT NULL CONSTRAINT event_pkey PRIMARY KEY,
+ body varchar,
+ entity_id varchar(31),
+ entity_type varchar(255),
+ event_type varchar(255),
+ event_uid varchar(255),
+ tenant_id varchar(31),
+ CONSTRAINT event_unq_key UNIQUE (tenant_id, entity_type, entity_id, event_type, event_uid)
+);
+
+CREATE TABLE IF NOT EXISTS relation (
+ from_id varchar(31),
+ from_type varchar(255),
+ to_id varchar(31),
+ to_type varchar(255),
+ relation_type_group varchar(255),
+ relation_type varchar(255),
+ additional_info varchar,
+ CONSTRAINT relation_unq_key UNIQUE (from_id, from_type, relation_type_group, relation_type, to_id, to_type)
+);
+
+CREATE TABLE IF NOT EXISTS tb_user (
+ id varchar(31) NOT NULL CONSTRAINT tb_user_pkey PRIMARY KEY,
+ additional_info varchar,
+ authority varchar(255),
+ customer_id varchar(31),
+ email varchar(255) UNIQUE,
+ first_name varchar(255),
+ last_name varchar(255),
+ search_text varchar(255),
+ tenant_id varchar(31)
+);
+
+CREATE TABLE IF NOT EXISTS tenant (
+ id varchar(31) NOT NULL CONSTRAINT tenant_pkey PRIMARY KEY,
+ additional_info varchar,
+ address varchar,
+ address2 varchar,
+ city varchar(255),
+ country varchar(255),
+ email varchar(255),
+ phone varchar(255),
+ region varchar(255),
+ search_text varchar(255),
+ state varchar(255),
+ title varchar(255),
+ zip varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS user_credentials (
+ id varchar(31) NOT NULL CONSTRAINT user_credentials_pkey PRIMARY KEY,
+ activate_token varchar(255) UNIQUE,
+ enabled boolean,
+ password varchar(255),
+ reset_token varchar(255) UNIQUE,
+ user_id varchar(31) UNIQUE
+);
+
+CREATE TABLE IF NOT EXISTS widget_type (
+ id varchar(31) NOT NULL CONSTRAINT widget_type_pkey PRIMARY KEY,
+ alias varchar(255),
+ bundle_alias varchar(255),
+ descriptor varchar(1000000),
+ name varchar(255),
+ tenant_id varchar(31)
+);
+
+CREATE TABLE IF NOT EXISTS widgets_bundle (
+ id varchar(31) NOT NULL CONSTRAINT widgets_bundle_pkey PRIMARY KEY,
+ alias varchar(255),
+ search_text varchar(255),
+ tenant_id varchar(31),
+ title varchar(255)
+);
+
+CREATE TABLE IF NOT EXISTS rule_chain (
+ id varchar(31) NOT NULL CONSTRAINT rule_chain_pkey PRIMARY KEY,
+ additional_info varchar,
+ configuration varchar(10000000),
+ name varchar(255),
+ first_rule_node_id varchar(31),
+ root boolean,
+ debug_mode boolean,
+ search_text varchar(255),
+ tenant_id varchar(31)
+);
+
+CREATE TABLE IF NOT EXISTS rule_node (
+ id varchar(31) NOT NULL CONSTRAINT rule_node_pkey PRIMARY KEY,
+ rule_chain_id varchar(31),
+ additional_info varchar,
+ configuration varchar(10000000),
+ type varchar(255),
+ name varchar(255),
+ debug_mode boolean,
+ search_text varchar(255)
+);
dao/src/main/resources/sql/schema-ts.sql 39(+39 -0)
diff --git a/dao/src/main/resources/sql/schema-ts.sql b/dao/src/main/resources/sql/schema-ts.sql
new file mode 100644
index 0000000..53bc15a
--- /dev/null
+++ b/dao/src/main/resources/sql/schema-ts.sql
@@ -0,0 +1,39 @@
+--
+-- Copyright © 2016-2018 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.
+--
+
+CREATE TABLE IF NOT EXISTS ts_kv (
+ entity_type varchar(255) NOT NULL,
+ entity_id varchar(31) NOT NULL,
+ key varchar(255) NOT NULL,
+ ts bigint NOT NULL,
+ bool_v boolean,
+ str_v varchar(10000000),
+ long_v bigint,
+ dbl_v double precision,
+ CONSTRAINT ts_kv_unq_key UNIQUE (entity_type, entity_id, key, ts)
+);
+
+CREATE TABLE IF NOT EXISTS ts_kv_latest (
+ entity_type varchar(255) NOT NULL,
+ entity_id varchar(31) NOT NULL,
+ key varchar(255) NOT NULL,
+ ts bigint NOT NULL,
+ bool_v boolean,
+ str_v varchar(10000000),
+ long_v bigint,
+ dbl_v double precision,
+ CONSTRAINT ts_kv_latest_unq_key UNIQUE (entity_type, entity_id, key)
+);