Thingsboard源码分析-设备表设计 - 图1

device(设备表)

属性

字段 类型 描述
id uuid 设备编号
created_time bigint 创建时间
additional_info varchar 额外信息
customer_id uuid 客户id
type varchar(255) 类型
name varchar(255) 名称
label varchar(255) 标签
search_text varchar(255) 搜素关键字
tenant_id uuid 租户编号

数据

id created_time additional_info customer_id type name label search_text tenant_id
84818cf0-fe46-11ea-8587-91857935e717 1600938941247 {“gateway”:false,”description”:”测试设备A - 空调”} 13814000-1dd2-11b2-8080-808080808080 test 测试设备A air conditioner 测试设备a e102f1b0-fcb8-11ea-82f8-9b35f0369853
bb348a90-fe46-11ea-8587-91857935e717 1600939033017 {“gateway”:false,”description”:”测试设备B - 空调”} 13814000-1dd2-11b2-8080-808080808080 test 测试设备B air conditioner 测试设备b e102f1b0-fcb8-11ea-82f8-9b35f0369853

DDL

  1. CREATE TABLE IF NOT EXISTS device (
  2.     id uuid NOT NULL CONSTRAINT device_pkey PRIMARY KEY,
  3.     created_time bigint NOT NULL,
  4.     additional_info varchar,
  5.     customer_id uuid,
  6.     type varchar(255),
  7.     name varchar(255),
  8.     label varchar(255),
  9.     search_text varchar(255),
  10.     tenant_id uuid,
  11.     CONSTRAINT device_name_unq_key UNIQUE (tenant_id, name)
  12. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图2

device_credentials(设备认证令牌表)

属性

字段 类型 描述
id uuid 编号
created_time bigint 创建时间
credentials_id varchar 认证令牌编号
credentials_type varchar(255) 认证令牌类型
credentials_value varchar 认证令牌值
device_id uuid 设备编号

数据

id created_time credentials_id credentials_type credentials_value device_id
8499a8d0-fe46-11ea-8587-91857935e717 1600938941405 wBUv3DVP15v857dg8De5 ACCESS_TOKEN 84818cf0-fe46-11ea-8587-91857935e717
bb3b4150-fe46-11ea-8587-91857935e717 1600939033061 x4aAaXU0dIkE8jR9ANus ACCESS_TOKEN bb348a90-fe46-11ea-8587-91857935e717

DDL

  1. CREATE TABLE IF NOT EXISTS device_credentials (
  2.     id uuid NOT NULL CONSTRAINT device_credentials_pkey PRIMARY KEY,
  3.     created_time bigint NOT NULL,
  4.     credentials_id varchar,
  5.     credentials_type varchar(255),
  6.     credentials_value varchar,
  7.     device_id uuid,
  8.     CONSTRAINT device_credentials_id_unq_key UNIQUE (credentials_id),
  9.     CONSTRAINT device_credentials_device_id_unq_key UNIQUE (device_id)
  10. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图3

ts_kv_dictionary(遥测键值字典表)

属性

字段 类型 描述
key varchar(255) 遥测数据键名称
key_id serial 遥测数据键编号

数据

key key_id
totalMsgs 1
successfulMsgs 2
failedMsgs 3
tmpFailed 4
tmpTimeout 5
timeoutMsgs 6
key1 7
key2 8

DDL

  1. CREATE TABLE IF NOT EXISTS ts_kv_dictionary
  2. (
  3.     key    varchar(255) NOT NULL,
  4.     key_id serial UNIQUE,
  5.     CONSTRAINT ts_key_id_pkey PRIMARY KEY (key)
  6. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图4

ts_kv(遥测历史数据表)

属性

字段 类型 描述
entity_id uuid 实体编号
key int 遥测数据键编号
ts bigint 遥测Unix时间
bool_v boolean 布尔值
str_v varchar(10000000) 字符串
long_v bigint 长整型数值
dbl_v double 浮点型数值
json_v json json类型数值

数据

entity_id key ts bool_v str_v long_v dbl_v json_v
95a6b470-09d0-11eb-b4d3-bd0dad4ce224 7 1602208438113 value1
95a6b470-09d0-11eb-b4d3-bd0dad4ce224 8 1602208438113 value2

DDL

  1. CREATE TABLE IF NOT EXISTS ts_kv
  2. (
  3.     entity_id uuid   NOT NULL,
  4.     key       int    NOT NULL,
  5.     ts        bigint NOT NULL,
  6.     bool_v    boolean,
  7.     str_v     varchar(10000000),
  8.     long_v    bigint,
  9.     dbl_v     double precision,
  10.     json_v    json,
  11.     CONSTRAINT ts_kv_pkey PRIMARY KEY (entity_id, key, ts)
  12. ) PARTITION BY RANGE (ts);Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图5

ts_kv_latest(遥测最新数据表)

属性

字段 类型 描述
entity_id uuid 实体编号
key int 遥测数据键编号
ts bigint 遥测Unix时间
bool_v boolean 布尔值
str_v varchar(10000000) 字符串
long_v bigint 长整型数值
dbl_v double 浮点型数值
json_v json json类型数值

数据

entity_id key ts bool_v str_v long_v dbl_v json_v
95a6b470-09d0-11eb-b4d3-bd0dad4ce224 7 1602208438113 value1
95a6b470-09d0-11eb-b4d3-bd0dad4ce224 8 1602208438113 value2
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 1 1602209098199 1
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 6 1602209098199 0
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 5 1602209098199 0
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 2 1602209098199 1
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 4 1602209098199 0
7a1b0210-09d0-11eb-b4d3-bd0dad4ce224 3 1602209098199 0

DDL

  1. CREATE TABLE IF NOT EXISTS ts_kv_latest
  2. (
  3.     entity_id uuid   NOT NULL,
  4.     key       int    NOT NULL,
  5.     ts        bigint NOT NULL,
  6.     bool_v    boolean,
  7.     str_v     varchar(10000000),
  8.     long_v    bigint,
  9.     dbl_v     double precision,
  10.     json_v    json,
  11.     CONSTRAINT ts_kv_latest_pkey PRIMARY KEY (entity_id, key)
  12. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图6

attribute_kv

属性

字段 类型 描述
entity_type varchar(255) 实体类型
entity_id uuid 实体编号
attribute_type varchar(255) 属性类型
attribute_key varchar(255) 属性键名称
bool_v boolean 布尔值
str_v varchar(10000000) 字符串
long_v bigint 长整型数值
dbl_v double 浮点型数值
json_v json json类型数值
last_update_ts bigint 最新更新时间戳

数据

entity_type entity_id attribute_type attribute_key bool_v str_v long_v dbl_v json_v last_update_ts
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 CLIENT_SCOPE attribute1 value1 1602207938654
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 CLIENT_SCOPE attribute3 42.0 1602207938654
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 CLIENT_SCOPE attribute4 73 1602207938654
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 CLIENT_SCOPE attribute5 {“someNumber”:42,”someArray”:[1,2,3],”someNestedObject”:{“key”:”value”}} 1602207938654
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 CLIENT_SCOPE attribute2 true 1602207938654
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 SERVER_SCOPE lastActivityTime 1602208438125 1602208438158
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 SERVER_SCOPE inactivityAlarmTime 1602209086297 1602209086305
DEVICE 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 SERVER_SCOPE active false 1602209086307

DDL

  1. CREATE TABLE IF NOT EXISTS attribute_kv (
  2.   entity_type varchar(255),
  3.   entity_id uuid,
  4.   attribute_type varchar(255),
  5.   attribute_key varchar(255),
  6.   bool_v boolean,
  7.   str_v varchar(10000000),
  8.   long_v bigint,
  9.   dbl_v double precision,
  10.   json_v json,
  11.   last_update_ts bigint,
  12.   CONSTRAINT attribute_kv_pkey PRIMARY KEY (entity_type, entity_id, attribute_type, attribute_key)
  13. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图7

asset

属性

字段 类型 描述
id uuid 资产编号
created_time bigint 创建时间戳
additional_info varchar 额外信息
customer_id uuid 顾客编号
name varchar(255) 名称
label varchar(255) 标签
search_text varchar(255) 搜索关键字
tenant_id uuid 租户编号
type varchar(255) 类型

数据

id created_time additional_info customer_id name label search_text tenant_id type
fbdd3c40-09db-11eb-be0c-ffc759c3351b 1602212599300 {“description”:”智慧大楼-测试”} 13814000-1dd2-11b2-8080-808080808080 智慧大楼 building 智慧大楼 e8fdc2c0-008f-11eb-b578-a96e41c6df33 building

DDL

  1. CREATE TABLE IF NOT EXISTS asset (
  2.     id uuid NOT NULL CONSTRAINT asset_pkey PRIMARY KEY,
  3.     created_time bigint NOT NULL,
  4.     additional_info varchar,
  5.     customer_id uuid,
  6.     name varchar(255),
  7.     label varchar(255),
  8.     search_text varchar(255),
  9.     tenant_id uuid,
  10.     type varchar(255),
  11.     CONSTRAINT asset_name_unq_key UNIQUE (tenant_id, name)
  12. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图8

entity_view

属性

字段 类型 描述
id uuid 实体视图编号
created_time bigint 创建时间戳
entity_id uuid 实体编号
entity_type varchar(255) 实体类型
tenant_id uuid 租户编号
customer_id uuid 顾客编号
type varchar(255) 类型
name varchar(255) 名称
keys varchar(10000000) 关联设备属性/遥测关键字等集合
start_ts bigint 开始时间时间戳
end_ts bigint 结束时间时间戳
search_text varchar(255) 搜索关键字
additional_info varchar 额外信息

数据

id created_time entity_id entity_type tenant_id customer_id type name keys start_ts end_ts search_text additional_info
a6289f60-09db-11eb-be0c-ffc759c3351b 1602212455510 95a6b470-09d0-11eb-b4d3-bd0dad4ce224 DEVICE e8fdc2c0-008f-11eb-b578-a96e41c6df33 13814000-1dd2-11b2-8080-808080808080 test 测试实体视图 {“timeseries”:[“key1”,”key2”],”attributes”:{“cs”:[“attribute1”,”attribute2”],”ss”:[“active”],”sh”:[“attribute3”]}} 1602212340000 1602385140000 测试实体视图 {“description”:”实体视图测试”}

DDL

  1. CREATE TABLE IF NOT EXISTS entity_view (
  2.     id uuid NOT NULL CONSTRAINT entity_view_pkey PRIMARY KEY,
  3.     created_time bigint NOT NULL,
  4.     entity_id uuid,
  5.     entity_type varchar(255),
  6.     tenant_id uuid,
  7.     customer_id uuid,
  8.     type varchar(255),
  9.     name varchar(255),
  10.     keys varchar(10000000),
  11.     start_ts bigint,
  12.     end_ts bigint,
  13.     search_text varchar(255),
  14.     additional_info varchar
  15. );Copy to clipboardErrorCopied

页面显示

Thingsboard源码分析-设备表设计 - 图9