38 lines
1.9 KiB
SQL
38 lines
1.9 KiB
SQL
DROP TABLE IF EXISTS `mqtt_server`;
|
||
|
||
CREATE TABLE `mqtt_server` (
|
||
`mqtt_server_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`server_url` VARCHAR(200) NOT NULL COMMENT 'MQTT服务器地址',
|
||
`username` VARCHAR(100) NOT NULL COMMENT '连接账号',
|
||
`password` VARCHAR(200) NOT NULL COMMENT '连接密码',
|
||
`topic_filter` VARCHAR(200) NOT NULL COMMENT '订阅主题过滤器',
|
||
`publish_topic` VARCHAR(200) NOT NULL COMMENT '发布主题',
|
||
`creator_id` BIGINT NULL DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT NULL DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`mqtt_server_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='MQTT服务配置表';
|
||
|
||
|
||
ALTER TABLE `smart_cabinet`
|
||
ADD COLUMN `mqtt_server_id` BIGINT NULL COMMENT 'MQTT服务ID'
|
||
AFTER `cabinet_type`;
|
||
|
||
DROP TABLE IF EXISTS `shop`;
|
||
|
||
CREATE TABLE `shop` (
|
||
`shop_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`shop_name` VARCHAR(100) NOT NULL COMMENT '商店名称',
|
||
`creator_id` BIGINT NULL DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT NULL DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`shop_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商店表，每个柜子属于一个商店';
|
||
|
||
ALTER TABLE `smart_cabinet`
|
||
ADD COLUMN `shop_id` BIGINT NULL COMMENT '归属商店ID'
|
||
AFTER `cabinet_type`; |