opensource/iot-dc3
3
0
Fork 0
mirror of https://gitee.com/pnoker/iot-dc3.git synced 2026-11-02 11:11:57 +08:00
Code Issues Packages Projects Releases Wiki Activity
Files
develop
iot-dc3/dc3/dependencies/postgres/initdb/04-iot-dc3-manager.sql
T
pnoker 72e315ea96 style(db): normalize initdb formatting and order ddl before seeds 
Join 1242 keyword-split statement lines (COMMENT/CREATE/ALTER/LOAD), move the dc3_identity_audit_log table section and the schema fingerprint INSERT so every script orders DDL before seed data, and drop one duplicated COMMENT ON COLUMN. Statement multiset verified identical apart from the intended dedup. The canonical fingerprint changes to e81e3562... and is synchronized via schema_fingerprint.py --sync into the embedded ddl_hash and all four distribution points; existing initialized databases require re-initialization (flag day).
2026-09-03 09:02:47 +08:00

1343 lines
82 KiB
PL/PgSQL
Raw Permalink Blame History

/*
* Copyright 2016-present the IoT DC3 original author or authors.
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
CREATE SCHEMA IF NOT EXISTS dc3_manager;
SET search_path TO dc3_manager;
-- ----------------------------
-- Function for update operate time
-- ----------------------------
CREATE OR REPLACE FUNCTION update_operate_time()
RETURNS TRIGGER AS
$$
BEGIN
NEW.operate_time = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
-- ----------------------------
-- Table structure for dc3_label
-- ----------------------------
CREATE TABLE dc3_label
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
label_name TEXT DEFAULT ''::TEXT NOT NULL, -- Label name
label_code TEXT DEFAULT ''::TEXT NOT NULL, -- Label code
label_color TEXT DEFAULT '#F4F4F5' NOT NULL, -- Label color
entity_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Entity type flag
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_label_entity_type_flag CHECK (entity_type_flag BETWEEN 0 AND 8),
CONSTRAINT chk_label_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_label_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_label_active_unique ON dc3_label (tenant_id, entity_type_flag, label_name) WHERE deleted = 0 AND label_name <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_label
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_label IS 'Label table';
COMMENT ON COLUMN dc3_label.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_label.label_name IS 'Label name';
COMMENT ON COLUMN dc3_label.label_code IS 'Label code';
COMMENT ON COLUMN dc3_label.label_color IS 'Label color';
COMMENT ON COLUMN dc3_label.entity_type_flag IS 'Entity type flag';
COMMENT ON COLUMN dc3_label.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_label.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_label.remark IS 'Description';
COMMENT ON COLUMN dc3_label.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_label.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_label.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_label.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_label.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_label.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_label.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_label_bind
-- ----------------------------
CREATE TABLE dc3_label_bind
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
entity_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Entity type flag
label_id BIGINT DEFAULT 0 NOT NULL, -- Label ID
entity_id BIGINT DEFAULT 0 NOT NULL, -- Entity ID
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_label_bind_entity_type_flag CHECK (entity_type_flag BETWEEN 0 AND 8),
CONSTRAINT chk_label_bind_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_label_bind_active_unique ON dc3_label_bind (tenant_id, entity_type_flag, label_id, entity_id) WHERE deleted = 0;
CREATE INDEX idx_label_bind_entity ON dc3_label_bind (tenant_id, entity_type_flag, entity_id) WHERE deleted = 0;
-- Delete guards and label detail paths check whether a label is still bound.
CREATE INDEX idx_label_bind_label_id ON dc3_label_bind (label_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_label_bind
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_label_bind IS 'Binding table between labels and entities';
COMMENT ON COLUMN dc3_label_bind.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_label_bind.entity_type_flag IS 'Entity type flag';
COMMENT ON COLUMN dc3_label_bind.label_id IS 'Label ID';
COMMENT ON COLUMN dc3_label_bind.entity_id IS 'Entity ID';
COMMENT ON COLUMN dc3_label_bind.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_label_bind.remark IS 'Description';
COMMENT ON COLUMN dc3_label_bind.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_label_bind.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_label_bind.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_label_bind.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_label_bind.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_label_bind.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_label_bind.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_group
-- ----------------------------
CREATE TABLE dc3_group
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
parent_group_id BIGINT DEFAULT 0 NOT NULL, -- Parent group ID
group_name TEXT DEFAULT ''::TEXT NOT NULL, -- Group name
group_code TEXT DEFAULT ''::TEXT NOT NULL, -- Group code
group_level SMALLINT DEFAULT 0 NOT NULL, -- Group level
group_index SMALLINT DEFAULT 0 NOT NULL, -- Group order
entity_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Entity type flag
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_group_entity_type_flag CHECK (entity_type_flag BETWEEN 0 AND 8),
CONSTRAINT chk_group_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_group_deleted CHECK (deleted IN (0, 1))
);
CREATE INDEX idx_group_parent_id ON dc3_group (tenant_id, parent_group_id) WHERE deleted = 0;
CREATE UNIQUE INDEX idx_group_active_unique ON dc3_group (tenant_id, entity_type_flag, parent_group_id, group_name) WHERE deleted = 0 AND group_name <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_group
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_group IS 'Group table';
COMMENT ON COLUMN dc3_group.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_group.parent_group_id IS 'Parent group ID';
COMMENT ON COLUMN dc3_group.group_name IS 'Group name';
COMMENT ON COLUMN dc3_group.group_code IS 'Group code';
COMMENT ON COLUMN dc3_group.group_level IS 'Group level';
COMMENT ON COLUMN dc3_group.group_index IS 'Group order';
COMMENT ON COLUMN dc3_group.entity_type_flag IS 'Entity type flag';
COMMENT ON COLUMN dc3_group.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_group.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_group.remark IS 'Description';
COMMENT ON COLUMN dc3_group.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_group.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_group.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_group.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_group.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_group.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_group.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_group_bind
-- ----------------------------
CREATE TABLE dc3_group_bind
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
entity_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Entity type flag
group_id BIGINT DEFAULT 0 NOT NULL, -- Group ID
entity_id BIGINT DEFAULT 0 NOT NULL, -- Entity ID
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_group_bind_entity_type_flag CHECK (entity_type_flag BETWEEN 0 AND 8),
CONSTRAINT chk_group_bind_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_group_bind_entity_unique ON dc3_group_bind (tenant_id, entity_type_flag, entity_id) WHERE deleted = 0;
CREATE INDEX idx_group_bind_group_id ON dc3_group_bind (group_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_group_bind
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_group_bind IS 'Binding table between groups and entities';
COMMENT ON COLUMN dc3_group_bind.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_group_bind.entity_type_flag IS 'Entity type flag';
COMMENT ON COLUMN dc3_group_bind.group_id IS 'Group ID';
COMMENT ON COLUMN dc3_group_bind.entity_id IS 'Entity ID';
COMMENT ON COLUMN dc3_group_bind.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_group_bind.remark IS 'Description';
COMMENT ON COLUMN dc3_group_bind.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_group_bind.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_group_bind.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_group_bind.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_group_bind.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_group_bind.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_group_bind.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_driver
-- ----------------------------
CREATE TABLE dc3_driver
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
driver_name TEXT DEFAULT ''::TEXT NOT NULL, -- Driver name
driver_code TEXT DEFAULT ''::TEXT NOT NULL, -- Driver code
service_name TEXT DEFAULT ''::TEXT NOT NULL, -- Service name
service_host TEXT DEFAULT ''::TEXT NOT NULL, -- Service host
driver_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Driver type flag
driver_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Driver extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_driver_driver_type_flag CHECK (driver_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_driver_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_driver_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_driver_tenant_code_active_unique ON dc3_driver (tenant_id, driver_code) WHERE deleted = 0 AND driver_code <> ''::TEXT;
CREATE INDEX idx_driver_tenant_service_name ON dc3_driver (tenant_id, service_name) WHERE deleted = 0 AND service_name <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_driver
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_driver IS 'Driver table';
COMMENT ON COLUMN dc3_driver.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_driver.driver_name IS 'Driver name';
COMMENT ON COLUMN dc3_driver.driver_code IS 'Driver code';
COMMENT ON COLUMN dc3_driver.service_name IS 'Service name';
COMMENT ON COLUMN dc3_driver.service_host IS 'Service host';
COMMENT ON COLUMN dc3_driver.driver_type_flag IS 'Driver type flag';
COMMENT ON COLUMN dc3_driver.driver_ext IS 'Driver extension information';
COMMENT ON COLUMN dc3_driver.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_driver.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver.remark IS 'Description';
COMMENT ON COLUMN dc3_driver.signature IS 'Signature';
COMMENT ON COLUMN dc3_driver.version IS 'Version';
COMMENT ON COLUMN dc3_driver.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_driver.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_driver.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_driver.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_driver.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_driver.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_driver.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Driver runtime instances and device ownership
-- ----------------------------
-- dc3_driver is the logical protocol definition. Runtime replicas are recorded
-- separately so registering a second pod never overwrites the first pod's identity.
CREATE TABLE dc3_driver_instance
(
tenant_id BIGINT NOT NULL, -- Tenant ID
driver_id BIGINT NOT NULL, -- Logical driver ID
node_id TEXT NOT NULL, -- Stable runtime node ID
client_id TEXT NOT NULL, -- Unique messaging client ID
service_host TEXT NOT NULL, -- Runtime service host
started_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Runtime start time
last_heartbeat TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Last successful heartbeat time
lease_until TIMESTAMPTZ NOT NULL, -- Runtime lease expiration time
PRIMARY KEY (tenant_id, driver_id, node_id),
UNIQUE (tenant_id, client_id)
);
CREATE INDEX idx_driver_instance_active
ON dc3_driver_instance (tenant_id, driver_id, lease_until DESC);
CREATE TABLE dc3_device_lease
(
tenant_id BIGINT NOT NULL, -- Tenant ID
driver_id BIGINT NOT NULL, -- Logical driver ID
device_id BIGINT NOT NULL, -- Device ID
owner_node TEXT NOT NULL, -- Owning runtime node ID
fencing_token BIGINT DEFAULT 1 NOT NULL, -- Monotonic ownership fencing token (row-local increment)
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (tenant_id, device_id)
);
CREATE INDEX idx_device_lease_owner
ON dc3_device_lease (tenant_id, driver_id, owner_node, device_id)
INCLUDE
(fencing_token);
CREATE TABLE dc3_driver_device_revision
(
tenant_id BIGINT NOT NULL, -- Tenant ID
driver_id BIGINT NOT NULL, -- Logical driver ID
revision BIGINT NOT NULL, -- Monotonic device-set revision
PRIMARY KEY (tenant_id, driver_id)
);
CREATE TABLE dc3_driver_lease_state
(
tenant_id BIGINT NOT NULL, -- Tenant ID
driver_id BIGINT NOT NULL, -- Logical driver ID
membership_hash VARCHAR(64) NOT NULL, -- SHA-256 hash of active runtime membership
device_revision BIGINT NOT NULL, -- Last assigned device-set revision
assignment_version BIGINT DEFAULT 1 NOT NULL, -- Monotonic assignment generation (row-local increment)
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
PRIMARY KEY (tenant_id, driver_id)
);
COMMENT ON TABLE dc3_driver_instance IS 'Leased runtime replicas of a logical driver definition';
COMMENT ON COLUMN dc3_driver_instance.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver_instance.driver_id IS 'Logical driver ID';
COMMENT ON COLUMN dc3_driver_instance.node_id IS 'Stable runtime node ID';
COMMENT ON COLUMN dc3_driver_instance.client_id IS 'Unique messaging client ID within the tenant';
COMMENT ON COLUMN dc3_driver_instance.service_host IS 'Runtime service host';
COMMENT ON COLUMN dc3_driver_instance.started_at IS 'Runtime start time';
COMMENT ON COLUMN dc3_driver_instance.last_heartbeat IS 'Last successful heartbeat time';
COMMENT ON COLUMN dc3_driver_instance.lease_until IS 'Runtime lease expiration time';
COMMENT ON TABLE dc3_device_lease IS 'Single-owner device assignments with monotonic fencing tokens';
COMMENT ON COLUMN dc3_device_lease.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_device_lease.driver_id IS 'Logical driver ID';
COMMENT ON COLUMN dc3_device_lease.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_device_lease.owner_node IS 'Owning runtime node ID';
COMMENT ON COLUMN dc3_device_lease.fencing_token IS 'Monotonic token that rejects stale owners';
COMMENT ON COLUMN dc3_device_lease.operate_time IS 'Operation time';
COMMENT ON TABLE dc3_driver_device_revision IS 'O(1) change detector for each logical driver device set';
COMMENT ON COLUMN dc3_driver_device_revision.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver_device_revision.driver_id IS 'Logical driver ID';
COMMENT ON COLUMN dc3_driver_device_revision.revision IS 'Monotonic device-set revision';
COMMENT ON TABLE dc3_driver_lease_state IS 'Membership fingerprint and assignment version for incremental driver heartbeats';
COMMENT ON COLUMN dc3_driver_lease_state.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver_lease_state.driver_id IS 'Logical driver ID';
COMMENT ON COLUMN dc3_driver_lease_state.membership_hash IS 'SHA-256 hash of active runtime membership';
COMMENT ON COLUMN dc3_driver_lease_state.device_revision IS 'Last assigned device-set revision';
COMMENT ON COLUMN dc3_driver_lease_state.assignment_version IS 'Monotonic assignment generation';
COMMENT ON COLUMN dc3_driver_lease_state.operate_time IS 'Operation time';
-- ----------------------------
-- Table structure for dc3_driver_attribute
-- ----------------------------
CREATE TABLE dc3_driver_attribute
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_name TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute name
attribute_code TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute code
attribute_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Attribute type flag
default_value TEXT DEFAULT ''::TEXT NOT NULL, -- Default value
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
attribute_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Driver attribute extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_driver_attribute_attribute_type_flag CHECK (attribute_type_flag BETWEEN 0 AND 7),
CONSTRAINT chk_driver_attribute_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_driver_attribute_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_driver_attribute_active_unique ON dc3_driver_attribute (tenant_id, driver_id, attribute_code) WHERE deleted = 0 AND attribute_code <> ''::TEXT;
CREATE INDEX idx_driver_attribute_driver_id ON dc3_driver_attribute (driver_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_driver_attribute
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_driver_attribute IS 'Driver attribute table';
COMMENT ON COLUMN dc3_driver_attribute.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_driver_attribute.attribute_name IS 'Attribute name';
COMMENT ON COLUMN dc3_driver_attribute.attribute_code IS 'Attribute code';
COMMENT ON COLUMN dc3_driver_attribute.attribute_type_flag IS 'Attribute type flag';
COMMENT ON COLUMN dc3_driver_attribute.default_value IS 'Default value';
COMMENT ON COLUMN dc3_driver_attribute.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_driver_attribute.attribute_ext IS 'Driver attribute extension information';
COMMENT ON COLUMN dc3_driver_attribute.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_driver_attribute.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver_attribute.remark IS 'Description';
COMMENT ON COLUMN dc3_driver_attribute.signature IS 'Signature';
COMMENT ON COLUMN dc3_driver_attribute.version IS 'Version';
COMMENT ON COLUMN dc3_driver_attribute.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_driver_attribute.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_driver_attribute.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_driver_attribute.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_driver_attribute.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_driver_attribute.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_driver_attribute.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_point_attribute
-- ----------------------------
CREATE TABLE dc3_point_attribute
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_name TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute name
attribute_code TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute code
attribute_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Attribute type flag
default_value TEXT DEFAULT ''::TEXT NOT NULL, -- Default value
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
attribute_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Point attribute extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_point_attribute_attribute_type_flag CHECK (attribute_type_flag BETWEEN 0 AND 7),
CONSTRAINT chk_point_attribute_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_point_attribute_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_point_attribute_active_unique ON dc3_point_attribute (tenant_id, driver_id, attribute_code) WHERE deleted = 0 AND attribute_code <> ''::TEXT;
CREATE INDEX idx_point_attribute_driver_id ON dc3_point_attribute (driver_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_point_attribute
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_point_attribute IS 'Point attribute table';
COMMENT ON COLUMN dc3_point_attribute.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_point_attribute.attribute_name IS 'Attribute name';
COMMENT ON COLUMN dc3_point_attribute.attribute_code IS 'Attribute code';
COMMENT ON COLUMN dc3_point_attribute.attribute_type_flag IS 'Attribute type flag';
COMMENT ON COLUMN dc3_point_attribute.default_value IS 'Default value';
COMMENT ON COLUMN dc3_point_attribute.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_point_attribute.attribute_ext IS 'Point attribute extension information';
COMMENT ON COLUMN dc3_point_attribute.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_point_attribute.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_point_attribute.remark IS 'Description';
COMMENT ON COLUMN dc3_point_attribute.signature IS 'Signature';
COMMENT ON COLUMN dc3_point_attribute.version IS 'Version';
COMMENT ON COLUMN dc3_point_attribute.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_point_attribute.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_point_attribute.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_point_attribute.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_point_attribute.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_point_attribute.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_point_attribute.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_command_attribute
-- ----------------------------
CREATE TABLE dc3_command_attribute
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_name TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute name
attribute_code TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute code
attribute_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Attribute type flag
default_value TEXT DEFAULT ''::TEXT NOT NULL, -- Default value
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
attribute_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Command attribute extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_command_attribute_attribute_type_flag CHECK (attribute_type_flag BETWEEN 0 AND 7),
CONSTRAINT chk_command_attribute_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_command_attribute_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_command_attribute_active_unique ON dc3_command_attribute (tenant_id, driver_id, attribute_code) WHERE deleted = 0 AND attribute_code <> ''::TEXT;
CREATE INDEX idx_command_attribute_driver_id ON dc3_command_attribute (driver_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_command_attribute
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_command_attribute IS 'Command attribute table';
COMMENT ON COLUMN dc3_command_attribute.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_command_attribute.attribute_name IS 'Attribute name';
COMMENT ON COLUMN dc3_command_attribute.attribute_code IS 'Attribute code';
COMMENT ON COLUMN dc3_command_attribute.attribute_type_flag IS 'Attribute type flag';
COMMENT ON COLUMN dc3_command_attribute.default_value IS 'Default value';
COMMENT ON COLUMN dc3_command_attribute.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_command_attribute.attribute_ext IS 'Command attribute extension information';
COMMENT ON COLUMN dc3_command_attribute.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_command_attribute.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_command_attribute.remark IS 'Description';
COMMENT ON COLUMN dc3_command_attribute.signature IS 'Signature';
COMMENT ON COLUMN dc3_command_attribute.version IS 'Version';
COMMENT ON COLUMN dc3_command_attribute.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_command_attribute.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_command_attribute.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_command_attribute.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_command_attribute.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_command_attribute.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_command_attribute.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_event_attribute
-- ----------------------------
CREATE TABLE dc3_event_attribute
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_name TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute name
attribute_code TEXT DEFAULT ''::TEXT NOT NULL, -- Attribute code
attribute_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Attribute type flag
default_value TEXT DEFAULT ''::TEXT NOT NULL, -- Default value
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
attribute_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Event attribute extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_event_attribute_attribute_type_flag CHECK (attribute_type_flag BETWEEN 0 AND 7),
CONSTRAINT chk_event_attribute_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_event_attribute_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_event_attribute_active_unique ON dc3_event_attribute (tenant_id, driver_id, attribute_code) WHERE deleted = 0 AND attribute_code <> ''::TEXT;
CREATE INDEX idx_event_attribute_driver_id ON dc3_event_attribute (driver_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_event_attribute
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_event_attribute IS 'Event attribute table';
COMMENT ON COLUMN dc3_event_attribute.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_event_attribute.attribute_name IS 'Attribute name';
COMMENT ON COLUMN dc3_event_attribute.attribute_code IS 'Attribute code';
COMMENT ON COLUMN dc3_event_attribute.attribute_type_flag IS 'Attribute type flag';
COMMENT ON COLUMN dc3_event_attribute.default_value IS 'Default value';
COMMENT ON COLUMN dc3_event_attribute.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_event_attribute.attribute_ext IS 'Event attribute extension information';
COMMENT ON COLUMN dc3_event_attribute.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_event_attribute.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_event_attribute.remark IS 'Description';
COMMENT ON COLUMN dc3_event_attribute.signature IS 'Signature';
COMMENT ON COLUMN dc3_event_attribute.version IS 'Version';
COMMENT ON COLUMN dc3_event_attribute.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_event_attribute.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_event_attribute.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_event_attribute.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_event_attribute.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_event_attribute.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_event_attribute.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_profile
-- ----------------------------
CREATE TABLE dc3_profile
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
profile_name TEXT DEFAULT ''::TEXT NOT NULL, -- Profile name
profile_code TEXT DEFAULT ''::TEXT NOT NULL, -- Profile code
profile_share_flag SMALLINT DEFAULT 0 NOT NULL, -- Profile sharing type flag
profile_type_flag SMALLINT DEFAULT 2 NOT NULL, -- Profile type flag
profile_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Profile extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_profile_profile_share_flag CHECK (profile_share_flag BETWEEN 0 AND 2),
CONSTRAINT chk_profile_profile_type_flag CHECK (profile_type_flag BETWEEN 0 AND 2),
CONSTRAINT chk_profile_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_profile_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_profile_tenant_code_active_unique ON dc3_profile (tenant_id, profile_code) WHERE deleted = 0 AND profile_code <> ''::TEXT;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_profile
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_profile IS 'Profile table';
COMMENT ON COLUMN dc3_profile.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_profile.profile_name IS 'Profile name';
COMMENT ON COLUMN dc3_profile.profile_code IS 'Profile code';
COMMENT ON COLUMN dc3_profile.profile_share_flag IS 'Profile sharing type flag';
COMMENT ON COLUMN dc3_profile.profile_type_flag IS 'Profile type flag';
COMMENT ON COLUMN dc3_profile.profile_ext IS 'Profile extension information';
COMMENT ON COLUMN dc3_profile.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_profile.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_profile.remark IS 'Description';
COMMENT ON COLUMN dc3_profile.signature IS 'Signature';
COMMENT ON COLUMN dc3_profile.version IS 'Version';
COMMENT ON COLUMN dc3_profile.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_profile.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_profile.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_profile.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_profile.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_profile.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_profile.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_point
-- ----------------------------
CREATE TABLE dc3_point
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
point_name TEXT DEFAULT ''::TEXT NOT NULL, -- Point name
point_code TEXT DEFAULT ''::TEXT NOT NULL, -- Point code
point_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Point type flag
rw_flag SMALLINT DEFAULT 0 NOT NULL, -- Read/write flag
base_value REAL DEFAULT 0 NOT NULL, -- Base value
multiple REAL DEFAULT 1 NOT NULL, -- Multiplier
value_decimal SMALLINT DEFAULT 6 NOT NULL, -- Value precision
unit TEXT DEFAULT ''::TEXT NOT NULL, -- Unit
profile_id BIGINT DEFAULT 0 NOT NULL, -- Profile ID
point_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Point extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_point_point_type_flag CHECK (point_type_flag BETWEEN 0 AND 7),
CONSTRAINT chk_point_rw_flag CHECK (rw_flag BETWEEN 0 AND 2),
CONSTRAINT chk_point_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_point_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_point_profile_code_active_unique ON dc3_point (tenant_id, profile_id, point_code) WHERE deleted = 0 AND point_code <> ''::TEXT;
CREATE INDEX idx_point_profile_id ON dc3_point (profile_id) WHERE deleted = 0;
-- Supports listByProfileId queries with tenant scoping.
CREATE INDEX idx_point_tenant_profile ON dc3_point (tenant_id, profile_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_point
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_point IS 'Point table';
COMMENT ON COLUMN dc3_point.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_point.point_name IS 'Point name';
COMMENT ON COLUMN dc3_point.point_code IS 'Point code';
COMMENT ON COLUMN dc3_point.point_type_flag IS 'Point type flag';
COMMENT ON COLUMN dc3_point.rw_flag IS 'Read/write flag';
COMMENT ON COLUMN dc3_point.base_value IS 'Base value';
COMMENT ON COLUMN dc3_point.multiple IS 'Multiplier';
COMMENT ON COLUMN dc3_point.value_decimal IS 'Value precision';
COMMENT ON COLUMN dc3_point.unit IS 'Unit';
COMMENT ON COLUMN dc3_point.profile_id IS 'Profile ID';
COMMENT ON COLUMN dc3_point.point_ext IS 'Point extension information';
COMMENT ON COLUMN dc3_point.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_point.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_point.remark IS 'Description';
COMMENT ON COLUMN dc3_point.signature IS 'Signature';
COMMENT ON COLUMN dc3_point.version IS 'Version';
COMMENT ON COLUMN dc3_point.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_point.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_point.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_point.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_point.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_point.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_point.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_device
-- ----------------------------
CREATE TABLE dc3_device
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
device_name TEXT DEFAULT ''::TEXT NOT NULL, -- Device name
device_code TEXT DEFAULT ''::TEXT NOT NULL, -- Device code
driver_id BIGINT DEFAULT 0 NOT NULL, -- Driver ID
profile_id BIGINT, -- Profile ID (device belongs to single profile)
device_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Device extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_device_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_device_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_device_tenant_code_active_unique ON dc3_device (tenant_id, device_code) WHERE deleted = 0 AND device_code <> ''::TEXT;
CREATE UNIQUE INDEX idx_device_tenant_name_active_unique ON dc3_device (tenant_id, device_name) WHERE deleted = 0 AND device_name <> ''::TEXT;
CREATE INDEX idx_device_driver_id ON dc3_device (driver_id) WHERE deleted = 0;
CREATE INDEX idx_device_profile ON dc3_device (tenant_id, profile_id) WHERE deleted = 0;
-- Supports listByDriverId queries with tenant scoping.
CREATE INDEX idx_device_tenant_driver ON dc3_device (tenant_id, driver_id) WHERE deleted = 0;
CREATE INDEX idx_device_active_driver_assignment
ON dc3_device (tenant_id, driver_id, id) WHERE deleted = 0 AND enable_flag = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_device
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
-- Device ownership is recomputed only when the active device set changes. The
-- row-level trigger turns that change into a single-row revision bump on every
-- driver heartbeat instead of scanning all devices and leases. Row-local
-- revision = revision + 1 keeps per-(tenant, driver) monotonicity without a
-- sequence object (MySQL has none), and mirrors the adapter's fencing/version
-- increments.
CREATE OR REPLACE FUNCTION track_driver_device_revision_change()
RETURNS TRIGGER AS
$$
BEGIN
IF TG_OP IN ('UPDATE', 'DELETE') AND OLD.deleted = 0 AND OLD.enable_flag = 0
AND (TG_OP = 'DELETE'
OR NEW.tenant_id IS DISTINCT FROM OLD.tenant_id
OR NEW.driver_id IS DISTINCT FROM OLD.driver_id
OR NEW.deleted IS DISTINCT FROM OLD.deleted
OR NEW.enable_flag IS DISTINCT FROM OLD.enable_flag)
THEN
INSERT INTO dc3_manager.dc3_driver_device_revision (tenant_id, driver_id, revision)
VALUES (OLD.tenant_id, OLD.driver_id, 1)
ON CONFLICT
(tenant_id, driver_id) DO
UPDATE SET
revision = dc3_manager.dc3_driver_device_revision.revision + 1;
END IF;
IF TG_OP IN ('INSERT', 'UPDATE') AND NEW.deleted = 0 AND NEW.enable_flag = 0
AND (TG_OP = 'INSERT'
OR NEW.tenant_id IS DISTINCT FROM OLD.tenant_id
OR NEW.driver_id IS DISTINCT FROM OLD.driver_id
OR NEW.deleted IS DISTINCT FROM OLD.deleted
OR NEW.enable_flag IS DISTINCT FROM OLD.enable_flag)
THEN
INSERT INTO dc3_manager.dc3_driver_device_revision (tenant_id, driver_id, revision)
VALUES (NEW.tenant_id, NEW.driver_id, 1)
ON CONFLICT
(tenant_id, driver_id) DO
UPDATE SET
revision = dc3_manager.dc3_driver_device_revision.revision + 1;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER track_driver_device_revision_insert_trigger
AFTER INSERT
ON dc3_device
FOR EACH ROW
EXECUTE FUNCTION track_driver_device_revision_change();
CREATE TRIGGER track_driver_device_revision_delete_trigger
AFTER DELETE
ON dc3_device
FOR EACH ROW
EXECUTE FUNCTION track_driver_device_revision_change();
CREATE TRIGGER track_driver_device_revision_update_trigger
AFTER UPDATE
ON dc3_device
FOR EACH ROW
EXECUTE FUNCTION track_driver_device_revision_change();
COMMENT ON TABLE dc3_device IS 'Device table';
COMMENT ON COLUMN dc3_device.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_device.device_name IS 'Device name';
COMMENT ON COLUMN dc3_device.device_code IS 'Device code';
COMMENT ON COLUMN dc3_device.driver_id IS 'Driver ID';
COMMENT ON COLUMN dc3_device.profile_id IS 'Profile ID';
COMMENT ON COLUMN dc3_device.device_ext IS 'Device extension information';
COMMENT ON COLUMN dc3_device.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_device.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_device.remark IS 'Description';
COMMENT ON COLUMN dc3_device.signature IS 'Signature';
COMMENT ON COLUMN dc3_device.version IS 'Version';
COMMENT ON COLUMN dc3_device.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_device.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_device.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_device.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_device.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_device.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_device.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_driver_attribute_config
-- ----------------------------
CREATE TABLE dc3_driver_attribute_config
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_id BIGINT DEFAULT 0 NOT NULL, -- Driver attribute ID
config_value TEXT DEFAULT ''::TEXT NOT NULL, -- Driver configuration value
device_id BIGINT DEFAULT 0 NOT NULL, -- Device ID
config_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Driver configuration information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_driver_attribute_config_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_driver_attribute_config_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_driver_attribute_config_active_unique ON dc3_driver_attribute_config (tenant_id, device_id, attribute_id) WHERE deleted = 0;
CREATE INDEX idx_driver_attribute_config_attribute_id ON dc3_driver_attribute_config (attribute_id) WHERE deleted = 0;
-- Driver metadata assembly lists all driver attribute configs for a device.
CREATE INDEX idx_driver_attribute_config_device_id ON dc3_driver_attribute_config (device_id) WHERE deleted = 0;
CREATE INDEX idx_driver_attr_config_device ON dc3_driver_attribute_config (tenant_id, device_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_driver_attribute_config
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_driver_attribute_config IS 'Driver attribute configuration table';
COMMENT ON COLUMN dc3_driver_attribute_config.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_driver_attribute_config.attribute_id IS 'Driver attribute ID';
COMMENT ON COLUMN dc3_driver_attribute_config.config_value IS 'Driver configuration value';
COMMENT ON COLUMN dc3_driver_attribute_config.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_driver_attribute_config.config_ext IS 'Driver configuration information';
COMMENT ON COLUMN dc3_driver_attribute_config.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_driver_attribute_config.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_driver_attribute_config.remark IS 'Description';
COMMENT ON COLUMN dc3_driver_attribute_config.signature IS 'Signature';
COMMENT ON COLUMN dc3_driver_attribute_config.version IS 'Version';
COMMENT ON COLUMN dc3_driver_attribute_config.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_driver_attribute_config.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_driver_attribute_config.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_driver_attribute_config.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_driver_attribute_config.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_driver_attribute_config.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_driver_attribute_config.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_point_attribute_config
-- ----------------------------
CREATE TABLE dc3_point_attribute_config
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_id BIGINT DEFAULT 0 NOT NULL, -- Point attribute ID
config_value TEXT DEFAULT ''::TEXT NOT NULL, -- Point configuration value
device_id BIGINT DEFAULT 0 NOT NULL, -- Device ID
config_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Point configuration information
point_id BIGINT DEFAULT 0 NOT NULL, -- Point ID
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_point_attribute_config_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_point_attribute_config_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_point_attribute_config_active_unique ON dc3_point_attribute_config (tenant_id, device_id, point_id, attribute_id) WHERE deleted = 0;
CREATE INDEX idx_point_attribute_config_attribute_id ON dc3_point_attribute_config (attribute_id) WHERE deleted = 0;
CREATE INDEX idx_point_attribute_config_device_point ON dc3_point_attribute_config (device_id, point_id) WHERE deleted = 0;
CREATE INDEX idx_point_attr_config_device ON dc3_point_attribute_config (tenant_id, device_id, point_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_point_attribute_config
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_point_attribute_config IS 'Point attribute configuration table';
COMMENT ON COLUMN dc3_point_attribute_config.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_point_attribute_config.attribute_id IS 'Point attribute ID';
COMMENT ON COLUMN dc3_point_attribute_config.config_value IS 'Point configuration value';
COMMENT ON COLUMN dc3_point_attribute_config.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_point_attribute_config.config_ext IS 'Point configuration information';
COMMENT ON COLUMN dc3_point_attribute_config.point_id IS 'Point ID';
COMMENT ON COLUMN dc3_point_attribute_config.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_point_attribute_config.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_point_attribute_config.remark IS 'Description';
COMMENT ON COLUMN dc3_point_attribute_config.signature IS 'Signature';
COMMENT ON COLUMN dc3_point_attribute_config.version IS 'Version';
COMMENT ON COLUMN dc3_point_attribute_config.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_point_attribute_config.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_point_attribute_config.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_point_attribute_config.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_point_attribute_config.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_point_attribute_config.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_point_attribute_config.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_command_attribute_config
-- ----------------------------
CREATE TABLE dc3_command_attribute_config
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_id BIGINT DEFAULT 0 NOT NULL, -- Command attribute ID
config_value TEXT DEFAULT ''::TEXT NOT NULL, -- Command configuration value
device_id BIGINT DEFAULT 0 NOT NULL, -- Device ID
config_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Command configuration information
command_id BIGINT DEFAULT 0 NOT NULL, -- Command ID
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_command_attribute_config_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_command_attribute_config_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_command_attribute_config_active_unique ON dc3_command_attribute_config (tenant_id, device_id, command_id, attribute_id) WHERE deleted = 0;
CREATE INDEX idx_command_attribute_config_attribute_id ON dc3_command_attribute_config (attribute_id) WHERE deleted = 0;
CREATE INDEX idx_command_attribute_config_device_command ON dc3_command_attribute_config (device_id, command_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_command_attribute_config
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_command_attribute_config IS 'Command attribute configuration table';
COMMENT ON COLUMN dc3_command_attribute_config.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_command_attribute_config.attribute_id IS 'Command attribute ID';
COMMENT ON COLUMN dc3_command_attribute_config.config_value IS 'Command configuration value';
COMMENT ON COLUMN dc3_command_attribute_config.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_command_attribute_config.config_ext IS 'Command configuration information';
COMMENT ON COLUMN dc3_command_attribute_config.command_id IS 'Command ID';
COMMENT ON COLUMN dc3_command_attribute_config.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_command_attribute_config.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_command_attribute_config.remark IS 'Description';
COMMENT ON COLUMN dc3_command_attribute_config.signature IS 'Signature';
COMMENT ON COLUMN dc3_command_attribute_config.version IS 'Version';
COMMENT ON COLUMN dc3_command_attribute_config.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_command_attribute_config.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_command_attribute_config.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_command_attribute_config.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_command_attribute_config.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_command_attribute_config.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_command_attribute_config.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_event_attribute_config
-- ----------------------------
CREATE TABLE dc3_event_attribute_config
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
attribute_id BIGINT DEFAULT 0 NOT NULL, -- Event attribute ID
config_value TEXT DEFAULT ''::TEXT NOT NULL, -- Event configuration value
device_id BIGINT DEFAULT 0 NOT NULL, -- Device ID
config_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Event configuration information
event_id BIGINT DEFAULT 0 NOT NULL, -- Event ID
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_event_attribute_config_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_event_attribute_config_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_event_attribute_config_active_unique ON dc3_event_attribute_config (tenant_id, device_id, event_id, attribute_id) WHERE deleted = 0;
CREATE INDEX idx_event_attribute_config_attribute_id ON dc3_event_attribute_config (attribute_id) WHERE deleted = 0;
CREATE INDEX idx_event_attribute_config_device_event ON dc3_event_attribute_config (device_id, event_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_event_attribute_config
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_event_attribute_config IS 'Event attribute configuration table';
COMMENT ON COLUMN dc3_event_attribute_config.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_event_attribute_config.attribute_id IS 'Event attribute ID';
COMMENT ON COLUMN dc3_event_attribute_config.config_value IS 'Event configuration value';
COMMENT ON COLUMN dc3_event_attribute_config.device_id IS 'Device ID';
COMMENT ON COLUMN dc3_event_attribute_config.config_ext IS 'Event configuration information';
COMMENT ON COLUMN dc3_event_attribute_config.event_id IS 'Event ID';
COMMENT ON COLUMN dc3_event_attribute_config.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_event_attribute_config.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_event_attribute_config.remark IS 'Description';
COMMENT ON COLUMN dc3_event_attribute_config.signature IS 'Signature';
COMMENT ON COLUMN dc3_event_attribute_config.version IS 'Version';
COMMENT ON COLUMN dc3_event_attribute_config.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_event_attribute_config.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_event_attribute_config.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_event_attribute_config.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_event_attribute_config.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_event_attribute_config.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_event_attribute_config.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_command
-- ----------------------------
CREATE TABLE dc3_command
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
command_name TEXT DEFAULT ''::TEXT NOT NULL, -- Command name
command_code TEXT DEFAULT ''::TEXT NOT NULL, -- Command code
command_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Command type flag, 0: custom, 1: config, 2: action
call_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Call type flag, 0: sync, 1: async
timeout INTEGER DEFAULT 30 NOT NULL, -- Timeout in seconds
command_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Command extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
profile_id BIGINT DEFAULT 0 NOT NULL, -- Profile ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_command_command_type_flag CHECK (command_type_flag BETWEEN 0 AND 2),
CONSTRAINT chk_command_call_type_flag CHECK (call_type_flag BETWEEN 0 AND 1),
CONSTRAINT chk_command_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_command_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_command_tenant_profile_code_active_unique ON dc3_command (tenant_id, profile_id, command_code) WHERE deleted = 0 AND command_code <> ''::TEXT;
-- Profile and device detail pages load command metadata by profile, then tenant and enable flag.
CREATE INDEX idx_command_profile_id ON dc3_command (profile_id, tenant_id, enable_flag) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_command
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_command IS 'Command table';
COMMENT ON COLUMN dc3_command.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_command.command_name IS 'Command name';
COMMENT ON COLUMN dc3_command.command_code IS 'Command code';
COMMENT ON COLUMN dc3_command.command_type_flag IS 'Command type flag, 0: custom, 1: config, 2: action';
COMMENT ON COLUMN dc3_command.call_type_flag IS 'Call type flag, 0: sync, 1: async';
COMMENT ON COLUMN dc3_command.timeout IS 'Timeout in seconds';
COMMENT ON COLUMN dc3_command.command_ext IS 'Command extension information';
COMMENT ON COLUMN dc3_command.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_command.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_command.profile_id IS 'Profile ID';
COMMENT ON COLUMN dc3_command.remark IS 'Description';
COMMENT ON COLUMN dc3_command.signature IS 'Signature';
COMMENT ON COLUMN dc3_command.version IS 'Version';
COMMENT ON COLUMN dc3_command.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_command.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_command.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_command.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_command.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_command.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_command.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_command_param
-- ----------------------------
CREATE TABLE dc3_command_param
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
param_name TEXT DEFAULT ''::TEXT NOT NULL, -- Param name
param_code TEXT DEFAULT ''::TEXT NOT NULL, -- Param code
param_direction_flag SMALLINT DEFAULT 0 NOT NULL, -- Param direction flag, 0: input, 1: output
param_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Param type flag
required_flag SMALLINT DEFAULT 0 NOT NULL, -- Required flag, 0: no, 1: yes
default_value TEXT DEFAULT ''::TEXT NOT NULL, -- Default value
param_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Param extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
command_id BIGINT DEFAULT 0 NOT NULL, -- Command ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_command_param_param_direction_flag CHECK (param_direction_flag BETWEEN 0 AND 1),
CONSTRAINT chk_command_param_required_flag CHECK (required_flag BETWEEN 0 AND 1),
CONSTRAINT chk_command_param_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_command_param_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_command_param_active_unique ON dc3_command_param (tenant_id, command_id, param_code) WHERE deleted = 0 AND param_code <> ''::TEXT;
CREATE UNIQUE INDEX idx_command_param_active_name_unique ON dc3_command_param (tenant_id, command_id, param_name) WHERE deleted = 0 AND param_name <> ''::TEXT;
-- Command detail and cascade-delete paths list params by command_id.
CREATE INDEX idx_command_param_command_id ON dc3_command_param (command_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_command_param
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_command_param IS 'Command param table';
COMMENT ON COLUMN dc3_command_param.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_command_param.param_name IS 'Param name';
COMMENT ON COLUMN dc3_command_param.param_code IS 'Param code';
COMMENT ON COLUMN dc3_command_param.param_direction_flag IS 'Param direction flag, 0: input, 1: output';
COMMENT ON COLUMN dc3_command_param.param_type_flag IS 'Param type flag';
COMMENT ON COLUMN dc3_command_param.required_flag IS 'Required flag, 0: no, 1: yes';
COMMENT ON COLUMN dc3_command_param.default_value IS 'Default value';
COMMENT ON COLUMN dc3_command_param.param_ext IS 'Param extension information';
COMMENT ON COLUMN dc3_command_param.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_command_param.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_command_param.command_id IS 'Command ID';
COMMENT ON COLUMN dc3_command_param.remark IS 'Description';
COMMENT ON COLUMN dc3_command_param.signature IS 'Signature';
COMMENT ON COLUMN dc3_command_param.version IS 'Version';
COMMENT ON COLUMN dc3_command_param.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_command_param.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_command_param.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_command_param.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_command_param.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_command_param.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_command_param.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_event
-- ----------------------------
CREATE TABLE dc3_event
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
event_name TEXT DEFAULT ''::TEXT NOT NULL, -- Event name
event_code TEXT DEFAULT ''::TEXT NOT NULL, -- Event code
event_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Event type flag, 0: info, 1: alert, 2: fault, 3: lifecycle
event_level_flag SMALLINT DEFAULT 0 NOT NULL, -- Event level flag, 0: low, 1: medium, 2: high, 3: critical
event_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Event extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
profile_id BIGINT DEFAULT 0 NOT NULL, -- Profile ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_event_event_type_flag CHECK (event_type_flag BETWEEN 0 AND 3),
CONSTRAINT chk_event_event_level_flag CHECK (event_level_flag BETWEEN 0 AND 3),
CONSTRAINT chk_event_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_event_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_event_tenant_profile_code_active_unique ON dc3_event (tenant_id, profile_id, event_code) WHERE deleted = 0 AND event_code <> ''::TEXT;
-- Profile and device detail pages load event metadata by profile, then tenant and enable flag.
CREATE INDEX idx_event_profile_id ON dc3_event (profile_id, tenant_id, enable_flag) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_event
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_event IS 'Event table';
COMMENT ON COLUMN dc3_event.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_event.event_name IS 'Event name';
COMMENT ON COLUMN dc3_event.event_code IS 'Event code';
COMMENT ON COLUMN dc3_event.event_type_flag IS 'Event type flag, 0: info, 1: alert, 2: fault, 3: lifecycle';
COMMENT ON COLUMN dc3_event.event_level_flag IS 'Event level flag, 0: low, 1: medium, 2: high, 3: critical';
COMMENT ON COLUMN dc3_event.event_ext IS 'Event extension information';
COMMENT ON COLUMN dc3_event.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_event.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_event.profile_id IS 'Profile ID';
COMMENT ON COLUMN dc3_event.remark IS 'Description';
COMMENT ON COLUMN dc3_event.signature IS 'Signature';
COMMENT ON COLUMN dc3_event.version IS 'Version';
COMMENT ON COLUMN dc3_event.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_event.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_event.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_event.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_event.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_event.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_event.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
-- ----------------------------
-- Table structure for dc3_event_param
-- ----------------------------
CREATE TABLE dc3_event_param
(
id BIGINT PRIMARY KEY NOT NULL, -- Primary key ID
param_name TEXT DEFAULT ''::TEXT NOT NULL, -- Param name
param_code TEXT DEFAULT ''::TEXT NOT NULL, -- Param code
param_type_flag SMALLINT DEFAULT 0 NOT NULL, -- Param type flag
param_ext JSONB DEFAULT '{}'::JSONB NOT NULL, -- Param extension information
enable_flag SMALLINT DEFAULT 0 NOT NULL, -- Enable flag, 0: enabled, 1: disabled
tenant_id BIGINT DEFAULT 0 NOT NULL, -- Tenant ID
event_id BIGINT DEFAULT 0 NOT NULL, -- Event ID
remark TEXT DEFAULT ''::TEXT NOT NULL, -- Description
signature TEXT DEFAULT ''::TEXT NOT NULL, -- Signature
version INTEGER DEFAULT 0 NOT NULL, -- Version
creator_id BIGINT DEFAULT 0 NOT NULL, -- Creator ID
creator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Creator name
create_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Creation time
operator_id BIGINT DEFAULT 0 NOT NULL, -- Operator ID
operator_name TEXT DEFAULT ''::TEXT NOT NULL, -- Operator name
operate_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Operation time
deleted SMALLINT DEFAULT 0 NOT NULL, -- Logical delete flag, 0: not deleted, 1: deleted
CONSTRAINT chk_event_param_enable_flag CHECK (enable_flag IN (0, 1)),
CONSTRAINT chk_event_param_deleted CHECK (deleted IN (0, 1))
);
CREATE UNIQUE INDEX idx_event_param_active_unique ON dc3_event_param (tenant_id, event_id, param_code) WHERE deleted = 0 AND param_code <> ''::TEXT;
CREATE UNIQUE INDEX idx_event_param_active_name_unique ON dc3_event_param (tenant_id, event_id, param_name) WHERE deleted = 0 AND param_name <> ''::TEXT;
-- Event detail and cascade-delete paths list params by event_id.
CREATE INDEX idx_event_param_event_id ON dc3_event_param (event_id) WHERE deleted = 0;
CREATE TRIGGER update_operate_time_trigger
BEFORE UPDATE
ON dc3_event_param
FOR EACH ROW
EXECUTE FUNCTION update_operate_time();
COMMENT ON TABLE dc3_event_param IS 'Event param table';
COMMENT ON COLUMN dc3_event_param.id IS 'Primary key ID';
COMMENT ON COLUMN dc3_event_param.param_name IS 'Param name';
COMMENT ON COLUMN dc3_event_param.param_code IS 'Param code';
COMMENT ON COLUMN dc3_event_param.param_type_flag IS 'Param type flag';
COMMENT ON COLUMN dc3_event_param.param_ext IS 'Param extension information';
COMMENT ON COLUMN dc3_event_param.enable_flag IS 'Enable flag, 0: enabled, 1: disabled';
COMMENT ON COLUMN dc3_event_param.tenant_id IS 'Tenant ID';
COMMENT ON COLUMN dc3_event_param.event_id IS 'Event ID';
COMMENT ON COLUMN dc3_event_param.remark IS 'Description';
COMMENT ON COLUMN dc3_event_param.signature IS 'Signature';
COMMENT ON COLUMN dc3_event_param.version IS 'Version';
COMMENT ON COLUMN dc3_event_param.creator_id IS 'Creator ID';
COMMENT ON COLUMN dc3_event_param.creator_name IS 'Creator name';
COMMENT ON COLUMN dc3_event_param.create_time IS 'Creation time';
COMMENT ON COLUMN dc3_event_param.operator_id IS 'Operator ID';
COMMENT ON COLUMN dc3_event_param.operator_name IS 'Operator name';
COMMENT ON COLUMN dc3_event_param.operate_time IS 'Operation time';
COMMENT ON COLUMN dc3_event_param.deleted IS 'Logical delete flag, 0: not deleted, 1: deleted';
Reference in New Issue View Git Blame Copy Permalink