g82tt/SecMPS
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Compare commits

base: g82tt:phase/0-infrastructure
Branches Tags
g82tt:master g82tt:gateway-dev g82tt:phase/0-infrastructure
...
compare: g82tt:8dde75f7ec704baf3102cc0e0759cfb39be5ea2a
Branches Tags
g82tt:gateway-dev g82tt:phase/0-infrastructure g82tt:master

2 Commits
phase/0-in ... 8dde75f7ec

Author SHA1 Message Date
g82tt
8dde75f7ec Phase0_pk_int_auto_incr 2026-05-15 23:45:53 +08:00
g82tt
29fedc8701 Phase0_db_init_comments 2026-05-15 23:36:03 +08:00
1 changed files with 145 additions and 129 deletions
Expand all files Collapse all files

274
doc/db_init.sql
View File

@@ -1,168 +1,184 @@
-- ============================================ -- ============================================
-- SecMPS v2.0 数据库建表脚本 -- SecMPS v2.0 数据库建表脚本
-- 数据库: gljs_main -- 数据库: gljs_main
-- 所有主键: INT AUTO_INCREMENT
-- ============================================ -- ============================================
USE gljs_main; USE gljs_main;
-- 1. 统一设备主表 -- 1. 统一设备主表
CREATE TABLE IF NOT EXISTS Base_Device ( DROP TABLE IF EXISTS Base_Device;
DeviceId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE Base_Device (
DeviceName NVARCHAR(100) NOT NULL, DeviceId INT AUTO_INCREMENT COMMENT '设备ID',
AdapterCode NVARCHAR(50) NOT NULL, DeviceName NVARCHAR(100) NOT NULL COMMENT '设备名称',
SourceId NVARCHAR(100) NOT NULL, AdapterCode NVARCHAR(50) NOT NULL COMMENT '来源适配器(owl/mc4)',
DeviceCategory INT NOT NULL DEFAULT 1, SourceId NVARCHAR(100) NOT NULL COMMENT '源系统设备ID',
DeviceType NVARCHAR(50), DeviceCategory INT NOT NULL DEFAULT 1 COMMENT '设备大类(1视频/2IoT/3门禁/4道闸/5报警)',
RegionId INT NULL, DeviceType NVARCHAR(50) COMMENT '设备细分类型(GB28181/TempSensor)',
IsParent TINYINT NOT NULL DEFAULT 0, RegionId INT NULL COMMENT '所属区域ID',
ParentDeviceId CHAR(36) NULL, IsParent TINYINT NOT NULL DEFAULT 0 COMMENT '是否父设备(0叶子/1可展开)',
IsOnline TINYINT NOT NULL DEFAULT 0, ParentDeviceId INT NULL COMMENT '父设备ID',
IpAddress NVARCHAR(50), IsOnline TINYINT NOT NULL DEFAULT 0 COMMENT '在线状态(0离线/1在线)',
Port INT, IpAddress NVARCHAR(50) COMMENT 'IP地址',
Location NVARCHAR(200), Port INT COMMENT '端口',
Lat DOUBLE, Location NVARCHAR(200) COMMENT '安装位置',
Lng DOUBLE, Lat DOUBLE COMMENT '纬度',
MapModelId NVARCHAR(100), Lng DOUBLE COMMENT '经度',
MapModelScale FLOAT DEFAULT 1.0, MapModelId NVARCHAR(100) COMMENT '三维地图模型ID',
MapModelRotation NVARCHAR(100), MapModelScale FLOAT DEFAULT 1.0 COMMENT '模型缩放比例',
ExtraData TEXT, MapModelRotation NVARCHAR(100) COMMENT '模型旋转角度(JSON)',
LocalOverrides TEXT, ExtraData TEXT COMMENT '源系统原始数据JSON',
SyncVersion BIGINT DEFAULT 0, LocalOverrides TEXT COMMENT '本地覆盖字段JSON',
LastSyncTime DATETIME, SyncVersion BIGINT DEFAULT 0 COMMENT '同步版本号',
Enable TINYINT DEFAULT 1, LastSyncTime DATETIME COMMENT '上次同步时间',
Remark NVARCHAR(500), Enable TINYINT DEFAULT 1 COMMENT '启用(0禁用/1启用)',
CreateID INT, Remark NVARCHAR(500) COMMENT '备注',
Creator NVARCHAR(50), CreateID INT COMMENT '创建人ID',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, Creator NVARCHAR(50) COMMENT '创建人',
ModifyID INT, CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
Modifier NVARCHAR(50), ModifyID INT COMMENT '修改人ID',
ModifyDate DATETIME, Modifier NVARCHAR(50) COMMENT '修改人',
ModifyDate DATETIME COMMENT '修改时间',
PRIMARY KEY (DeviceId),
UNIQUE INDEX IX_Base_Device_Adapter_Source (AdapterCode, SourceId), UNIQUE INDEX IX_Base_Device_Adapter_Source (AdapterCode, SourceId),
INDEX IX_Base_Device_RegionId (RegionId), INDEX IX_Base_Device_RegionId (RegionId),
INDEX IX_Base_Device_ParentId (ParentDeviceId) INDEX IX_Base_Device_ParentId (ParentDeviceId)
); ) COMMENT '统一设备主表';
-- 2. 视频设备扩展表 -- 2. 视频设备扩展表
CREATE TABLE IF NOT EXISTS Device_Video_Ext ( DROP TABLE IF EXISTS Device_Video_Ext;
ExtId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE Device_Video_Ext (
DeviceId CHAR(36) NOT NULL, ExtId INT AUTO_INCREMENT COMMENT '扩展记录ID',
OwlDeviceId NVARCHAR(64) NOT NULL, DeviceId INT NOT NULL COMMENT '关联设备ID',
Protocol INT DEFAULT 1, OwlDeviceId NVARCHAR(64) NOT NULL COMMENT 'Owl系统设备ID',
Manufacturer NVARCHAR(100), Protocol INT DEFAULT 1 COMMENT '接入协议(1GB28181/2ONVIF/3RTMP/4RTSP)',
Model NVARCHAR(100), Manufacturer NVARCHAR(100) COMMENT '厂商',
ChannelCount INT DEFAULT 0, Model NVARCHAR(100) COMMENT '设备型号',
OwlStatus NVARCHAR(500), ChannelCount INT DEFAULT 0 COMMENT '通道数量',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, OwlStatus NVARCHAR(500) COMMENT 'Owl原始状态JSON',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (ExtId),
UNIQUE INDEX IX_VideoExt_Owl (OwlDeviceId), UNIQUE INDEX IX_VideoExt_Owl (OwlDeviceId),
INDEX IX_VideoExt_Device (DeviceId) INDEX IX_VideoExt_Device (DeviceId)
); ) COMMENT '视频设备扩展表';
-- 3. 视频通道表 -- 3. 视频通道表
CREATE TABLE IF NOT EXISTS Video_Channel ( DROP TABLE IF EXISTS Video_Channel;
ChannelId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE Video_Channel (
OwlChannelId NVARCHAR(64) NOT NULL, ChannelId INT AUTO_INCREMENT COMMENT '通道ID',
DeviceId CHAR(36) NOT NULL, OwlChannelId NVARCHAR(64) NOT NULL COMMENT 'Owl系统通道ID',
ChannelName NVARCHAR(100) NOT NULL, DeviceId INT NOT NULL COMMENT '关联父设备ID',
ChannelNo INT DEFAULT 0, ChannelName NVARCHAR(100) NOT NULL COMMENT '通道名称',
OwlStreamApp NVARCHAR(50), ChannelNo INT DEFAULT 0 COMMENT '通道编号',
OwlStreamName NVARCHAR(100), OwlStreamApp NVARCHAR(50) COMMENT 'Owl流应用名',
HasPtz TINYINT DEFAULT 0, OwlStreamName NVARCHAR(100) COMMENT 'Owl流名称',
HasRecording TINYINT DEFAULT 0, HasPtz TINYINT DEFAULT 0 COMMENT '是否支持云台(0否/1是)',
RecordMode INT DEFAULT 0, HasRecording TINYINT DEFAULT 0 COMMENT '是否支持录像(0否/1是)',
IsOnline TINYINT DEFAULT 0, RecordMode INT DEFAULT 0 COMMENT '录像模式(0不录像/1设备录像/2中心录像)',
SnapshotUrl NVARCHAR(500), IsOnline TINYINT DEFAULT 0 COMMENT '在线状态(0离线/1在线)',
Location NVARCHAR(200), SnapshotUrl NVARCHAR(500) COMMENT '快照地址',
Lat DOUBLE, Location NVARCHAR(200) COMMENT '安装位置',
Lng DOUBLE, Lat DOUBLE COMMENT '纬度',
Enable TINYINT DEFAULT 1, Lng DOUBLE COMMENT '经度',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, Enable TINYINT DEFAULT 1 COMMENT '启用(0禁用/1启用)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (ChannelId),
UNIQUE INDEX IX_Channel_Owl (OwlChannelId), UNIQUE INDEX IX_Channel_Owl (OwlChannelId),
INDEX IX_Channel_Device (DeviceId) INDEX IX_Channel_Device (DeviceId)
); ) COMMENT '视频通道表';
-- 4. 录像记录表 -- 4. 录像记录表
CREATE TABLE IF NOT EXISTS Video_Record ( DROP TABLE IF EXISTS Video_Record;
RecordId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE Video_Record (
ChannelId CHAR(36) NOT NULL, RecordId INT AUTO_INCREMENT COMMENT '录像记录ID',
OwlRecordId INT NOT NULL, ChannelId INT NOT NULL COMMENT '关联通道ID',
App NVARCHAR(50), OwlRecordId INT NOT NULL COMMENT 'Owl录像记录ID',
Stream NVARCHAR(100), App NVARCHAR(50) COMMENT '应用名',
StartedAt DATETIME NOT NULL, Stream NVARCHAR(100) COMMENT '流ID',
EndedAt DATETIME, StartedAt DATETIME NOT NULL COMMENT '录像开始时间',
Duration DOUBLE DEFAULT 0, EndedAt DATETIME COMMENT '录像结束时间',
FilePath NVARCHAR(500), Duration DOUBLE DEFAULT 0 COMMENT '录像时长(秒)',
FileSize BIGINT DEFAULT 0, FilePath NVARCHAR(500) COMMENT '文件路径',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, FileSize BIGINT DEFAULT 0 COMMENT '文件大小(字节)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (RecordId),
INDEX IX_Record_Channel (ChannelId), INDEX IX_Record_Channel (ChannelId),
INDEX IX_Record_Time (StartedAt) INDEX IX_Record_Time (StartedAt)
); ) COMMENT '录像记录表';
-- 5. IoT设备扩展表 -- 5. IoT设备扩展表
CREATE TABLE IF NOT EXISTS Device_IoT_Ext ( DROP TABLE IF EXISTS Device_IoT_Ext;
ExtId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE Device_IoT_Ext (
DeviceId CHAR(36) NOT NULL, ExtId INT AUTO_INCREMENT COMMENT '扩展记录ID',
Mc4DeviceId INT NOT NULL, DeviceId INT NOT NULL COMMENT '关联设备ID',
ObjectType INT, Mc4DeviceId INT NOT NULL COMMENT 'MC4.0设备ID',
Tag NVARCHAR(100), ObjectType INT COMMENT 'MC4.0对象类型',
ParentId INT, Tag NVARCHAR(100) COMMENT '设备标签',
Mc4Option NVARCHAR(500), ParentId INT COMMENT 'MC4.0父级ID',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, Mc4Option NVARCHAR(500) COMMENT 'MC4.0原始配置JSON',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (ExtId),
UNIQUE INDEX IX_IoTExt_Mc4 (Mc4DeviceId), UNIQUE INDEX IX_IoTExt_Mc4 (Mc4DeviceId),
INDEX IX_IoTExt_Device (DeviceId) INDEX IX_IoTExt_Device (DeviceId)
); ) COMMENT '采集设备扩展表';
-- 6. 设备点位表 -- 6. 设备点位表
CREATE TABLE IF NOT EXISTS IoT_DevicePoint ( DROP TABLE IF EXISTS IoT_DevicePoint;
PointId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE IoT_DevicePoint (
DeviceId CHAR(36) NOT NULL, PointId INT AUTO_INCREMENT COMMENT '点位ID',
Mc4DeviceId INT NOT NULL, DeviceId INT NOT NULL COMMENT '关联设备ID',
PointIndex INT NOT NULL, Mc4DeviceId INT NOT NULL COMMENT 'MC4.0设备ID',
PointType INT, PointIndex INT NOT NULL COMMENT '点位索引',
PointTag NVARCHAR(100), PointType INT COMMENT '点位类型',
PointName NVARCHAR(100) NOT NULL, PointTag NVARCHAR(100) COMMENT '点位标签',
PointDesc NVARCHAR(200), PointName NVARCHAR(100) NOT NULL COMMENT '点位名称',
Unit NVARCHAR(50), PointDesc NVARCHAR(200) COMMENT '点位描述',
IsControlPoint TINYINT DEFAULT 0, Unit NVARCHAR(50) COMMENT '单位(℃/%/V)',
Mc4Option NVARCHAR(500), IsControlPoint TINYINT DEFAULT 0 COMMENT '是否控制点(0只读/1可写)',
Enable TINYINT DEFAULT 1, Mc4Option NVARCHAR(500) COMMENT 'MC4.0原始配置JSON',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, Enable TINYINT DEFAULT 1 COMMENT '启用(0禁用/1启用)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (PointId),
UNIQUE INDEX IX_Point_Mc4 (Mc4DeviceId, PointIndex), UNIQUE INDEX IX_Point_Mc4 (Mc4DeviceId, PointIndex),
INDEX IX_Point_Device (DeviceId) INDEX IX_Point_Device (DeviceId)
); ) COMMENT '设备点位表';
-- 7. 设备数据归档表（仅存快照，实时不入库） -- 7. 设备数据归档表
CREATE TABLE IF NOT EXISTS IoT_DeviceData ( DROP TABLE IF EXISTS IoT_DeviceData;
DataId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE IoT_DeviceData (
DeviceId CHAR(36) NOT NULL, DataId INT AUTO_INCREMENT COMMENT '数据记录ID',
PointId CHAR(36) NOT NULL, DeviceId INT NOT NULL COMMENT '关联设备ID',
PointValue DOUBLE, PointId INT NOT NULL COMMENT '关联点位ID',
UpdateTime DATETIME NOT NULL, PointValue DOUBLE COMMENT '点位数值',
`Interval` INT DEFAULT 0, UpdateTime DATETIME NOT NULL COMMENT '数据更新时间',
ArchiveType INT DEFAULT 1, `Interval` INT DEFAULT 0 COMMENT '采集间隔(毫秒)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, ArchiveType INT DEFAULT 1 COMMENT '归档类型(1小时/2日)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (DataId),
INDEX IX_Data_Device (DeviceId), INDEX IX_Data_Device (DeviceId),
INDEX IX_Data_Time (CreateDate) INDEX IX_Data_Time (CreateDate)
); ) COMMENT '设备数据归档表(仅存历史快照)';
-- 8. 告警记录表 -- 8. 告警记录表
CREATE TABLE IF NOT EXISTS IoT_Alarm ( DROP TABLE IF EXISTS IoT_Alarm;
AlarmId CHAR(36) NOT NULL PRIMARY KEY, CREATE TABLE IoT_Alarm (
Mc4AlarmId NVARCHAR(64) NOT NULL, AlarmId INT AUTO_INCREMENT COMMENT '告警ID',
DeviceId CHAR(36), Mc4AlarmId NVARCHAR(64) NOT NULL COMMENT 'MC4.0告警ID',
PointId CHAR(36), DeviceId INT COMMENT '关联设备ID',
AlarmType INT DEFAULT 0, PointId INT COMMENT '关联点位ID',
AlarmLevel INT DEFAULT 1, AlarmType INT DEFAULT 0 COMMENT '告警类型',
AlarmDesc NVARCHAR(500), AlarmLevel INT DEFAULT 1 COMMENT '告警等级(1提示/2普通/3重要/4紧急)',
AlarmValue DOUBLE, AlarmDesc NVARCHAR(500) COMMENT '告警描述',
StartTime DATETIME NOT NULL, AlarmValue DOUBLE COMMENT '触发值',
EndTime DATETIME, StartTime DATETIME NOT NULL COMMENT '告警开始时间',
ConfirmTime DATETIME, EndTime DATETIME COMMENT '告警结束时间',
ConfirmUser NVARCHAR(50), ConfirmTime DATETIME COMMENT '确认时间',
State INT DEFAULT 1, ConfirmUser NVARCHAR(50) COMMENT '确认人',
AdapterCode NVARCHAR(50), State INT DEFAULT 1 COMMENT '状态(1未确认/2已确认/3已结束)',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP, AdapterCode NVARCHAR(50) COMMENT '来源适配器',
CreateDate DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (AlarmId),
UNIQUE INDEX IX_Alarm_Mc4 (Mc4AlarmId), UNIQUE INDEX IX_Alarm_Mc4 (Mc4AlarmId),
INDEX IX_Alarm_Device (DeviceId), INDEX IX_Alarm_Device (DeviceId),
INDEX IX_Alarm_Time (StartTime) INDEX IX_Alarm_Time (StartTime)
); ) COMMENT '告警记录表';