60 lines
2.5 KiB
SQL
60 lines
2.5 KiB
SQL
-- 开放API回调能力升级
|
|
|
|
SET @schema_name = DATABASE();
|
|
|
|
SET @column_exists = (
|
|
SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'xxk_open_api_app'
|
|
AND COLUMN_NAME = 'callback_url'
|
|
);
|
|
SET @sql = IF(
|
|
@column_exists = 0,
|
|
'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_url` VARCHAR(255) DEFAULT NULL COMMENT ''订单结果回调地址'' AFTER `allow_ip_list`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @column_exists = (
|
|
SELECT COUNT(1)
|
|
FROM INFORMATION_SCHEMA.COLUMNS
|
|
WHERE TABLE_SCHEMA = @schema_name
|
|
AND TABLE_NAME = 'xxk_open_api_app'
|
|
AND COLUMN_NAME = 'callback_secret'
|
|
);
|
|
SET @sql = IF(
|
|
@column_exists = 0,
|
|
'ALTER TABLE `xxk_open_api_app` ADD COLUMN `callback_secret` VARCHAR(128) DEFAULT NULL COMMENT ''订单结果回调签名密钥'' AFTER `callback_url`',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS `xxk_open_api_callback_log` (
|
|
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
|
`open_api_app_id` BIGINT NOT NULL COMMENT '开放应用ID',
|
|
`member_user_id` BIGINT NOT NULL COMMENT '会员ID',
|
|
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
|
`order_no` VARCHAR(64) NOT NULL COMMENT '订单号',
|
|
`callback_url` VARCHAR(255) DEFAULT NULL COMMENT '回调地址',
|
|
`event_type` VARCHAR(64) NOT NULL COMMENT '事件类型',
|
|
`request_body` LONGTEXT DEFAULT NULL COMMENT '请求报文',
|
|
`response_body` LONGTEXT DEFAULT NULL COMMENT '响应报文',
|
|
`response_status` INT DEFAULT NULL COMMENT '响应HTTP状态码',
|
|
`delivery_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '投递状态(PENDING/SUCCESS/FAIL/SKIPPED)',
|
|
`error_message` VARCHAR(500) DEFAULT NULL COMMENT '错误信息',
|
|
`attempt_no` INT NOT NULL DEFAULT 1 COMMENT '尝试次数',
|
|
`trigger_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '触发时间',
|
|
`finish_time` DATETIME DEFAULT NULL COMMENT '完成时间',
|
|
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_xxk_open_api_callback_log_app_id` (`open_api_app_id`),
|
|
KEY `idx_xxk_open_api_callback_log_order_no` (`order_no`),
|
|
KEY `idx_xxk_open_api_callback_log_status` (`delivery_status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='开放API订单回调日志表';
|