51 lines
2.4 KiB
SQL
51 lines
2.4 KiB
SQL
-- 增量安装:已有支付库先执行payment_topup_upgrade.sql,再执行本文件;不删除任何历史数据。
|
|
CREATE TABLE IF NOT EXISTS rent_topup_order (
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|
topup_no VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
account_id BIGINT NOT NULL,
|
|
network VARCHAR(16) NOT NULL,
|
|
client_request_no VARCHAR(64) NOT NULL,
|
|
requested_amount DECIMAL(30,6) NOT NULL,
|
|
payment_amount DECIMAL(30,6) NULL,
|
|
credited_amount DECIMAL(30,6) NOT NULL DEFAULT 0,
|
|
payment_channel VARCHAR(16) NOT NULL,
|
|
payment_api_key VARCHAR(128) NOT NULL,
|
|
payment_public_key TEXT NOT NULL,
|
|
payment_base_url VARCHAR(512) NOT NULL,
|
|
notify_url VARCHAR(128) NOT NULL,
|
|
payment_trade_id VARCHAR(64) NULL,
|
|
payment_address VARCHAR(64) NULL,
|
|
checkout_url VARCHAR(1024) NULL,
|
|
transaction_id VARCHAR(64) NULL,
|
|
log_index INT NULL,
|
|
status VARCHAR(32) NOT NULL DEFAULT 'CREATING',
|
|
expires_at DATETIME(3) NULL,
|
|
credited_at DATETIME(3) NULL,
|
|
next_query_at DATETIME(3) NOT NULL,
|
|
lease_token VARCHAR(32) NULL COMMENT '补偿执行租约所有者,阻止超时旧任务回写',
|
|
query_attempts INT NOT NULL DEFAULT 0,
|
|
last_error VARCHAR(255) NULL,
|
|
version BIGINT NOT NULL DEFAULT 0,
|
|
created_at DATETIME(3) NOT NULL,
|
|
updated_at DATETIME(3) NOT NULL,
|
|
PRIMARY KEY(id), UNIQUE KEY uk_topup_no(topup_no),
|
|
UNIQUE KEY uk_topup_request(user_id,client_request_no),
|
|
UNIQUE KEY uk_topup_trade(payment_channel,payment_trade_id),
|
|
UNIQUE KEY uk_topup_transfer(network,transaction_id,log_index),
|
|
KEY idx_topup_query(status,next_query_at), KEY idx_topup_user(user_id,network,id)
|
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='租赁支付充值订单;网络及凭证公钥快照,不存Secret';
|
|
|
|
CREATE TABLE IF NOT EXISTS rent_topup_notify (
|
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
|
notification_hash VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
|
|
topup_no VARCHAR(32) NOT NULL,
|
|
payment_trade_id VARCHAR(64) NOT NULL,
|
|
transaction_id VARCHAR(64) NULL,
|
|
payment_status INT NOT NULL,
|
|
status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
|
|
created_at DATETIME(3) NOT NULL,
|
|
updated_at DATETIME(3) NOT NULL,
|
|
PRIMARY KEY(id), UNIQUE KEY uk_topup_notify(notification_hash), KEY idx_topup_notify_order(topup_no,status)
|
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='已验签的支付通知收件箱;不保存Secret或签名';
|