IF NOT EXISTS (
|
SELECT 1
|
FROM sys.objects
|
WHERE object_id = OBJECT_ID(N'[dbo].[asr_iot_publish_record]')
|
AND type = N'U'
|
)
|
BEGIN
|
CREATE TABLE [dbo].[asr_iot_publish_record] (
|
[id] BIGINT IDENTITY(1,1) NOT NULL,
|
[instruction_id] VARCHAR(64) NOT NULL,
|
[direction] VARCHAR(16) NOT NULL,
|
[message_type] VARCHAR(16) NOT NULL,
|
[receive_topic] VARCHAR(255) NULL,
|
[publish_topic] VARCHAR(255) NULL,
|
[container_id] VARCHAR(128) NULL,
|
[reference_id] VARCHAR(128) NULL,
|
[source_location_id] VARCHAR(128) NULL,
|
[destination_location_ids] VARCHAR(500) NULL,
|
[message_creation_time] BIGINT NULL,
|
[raw_payload] VARCHAR(MAX) NULL,
|
[publish_payload] VARCHAR(MAX) NULL,
|
[process_status] VARCHAR(32) NULL,
|
[publish_status] VARCHAR(32) NULL,
|
[feedback_status] VARCHAR(16) NULL,
|
[error_code] VARCHAR(64) NULL,
|
[error_message] VARCHAR(500) NULL,
|
[ack_payload] VARCHAR(MAX) NULL,
|
[ack_time] DATETIME NULL,
|
[wrk_no] INT NULL,
|
[order_no] VARCHAR(128) NULL,
|
[create_time] DATETIME NULL,
|
[update_time] DATETIME NULL,
|
CONSTRAINT [PK_asr_iot_publish_record] PRIMARY KEY CLUSTERED ([id] ASC),
|
CONSTRAINT [UK_asr_iot_publish_record_instruction_id] UNIQUE NONCLUSTERED ([instruction_id] ASC)
|
);
|
|
CREATE NONCLUSTERED INDEX [IDX_asr_iot_publish_record_container_type]
|
ON [dbo].[asr_iot_publish_record] ([container_id] ASC, [message_type] ASC, [message_creation_time] DESC);
|
|
CREATE NONCLUSTERED INDEX [IDX_asr_iot_publish_record_publish_status]
|
ON [dbo].[asr_iot_publish_record] ([publish_status] ASC, [create_time] ASC);
|
|
EXEC sys.sp_addextendedproperty
|
@name = N'MS_Description', @value = N'ASRS IoT 发布与回执记录',
|
@level0type = N'SCHEMA', @level0name = N'dbo',
|
@level1type = N'TABLE', @level1name = N'asr_iot_publish_record';
|
END;
|
