Files
usdt/sql/rent_onchain_pending_migration.sql

25 lines
1.4 KiB
SQL

-- MySQL 5.7/8.0:同网络同地址仅允许一笔未终结的地址上链订单。
-- 升级期间暂停下单;不修改历史订单、交易或资金。
DELIMITER $$
DROP PROCEDURE IF EXISTS migrate_rent_onchain_pending$$
CREATE PROCEDURE migrate_rent_onchain_pending()
BEGIN
IF EXISTS (SELECT 1 FROM rent_order
WHERE order_type='ADDRESS_ONCHAIN' AND status NOT IN ('SUCCESS','FAILED','CANCELLED')
GROUP BY network,receiver_address HAVING COUNT(*)>1) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Duplicate pending onchain orders: reconcile orders and funds before migration';
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema=DATABASE()
AND table_name='rent_order' AND column_name='onchain_pending_slot') THEN
ALTER TABLE rent_order ADD COLUMN onchain_pending_slot TINYINT GENERATED ALWAYS AS
(CASE WHEN order_type='ADDRESS_ONCHAIN' AND status NOT IN ('SUCCESS','FAILED','CANCELLED') THEN 1 ELSE NULL END);
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE()
AND table_name='rent_order' AND index_name='uk_order_onchain_pending') THEN
ALTER TABLE rent_order ADD UNIQUE KEY uk_order_onchain_pending(network,receiver_address,onchain_pending_slot);
END IF;
END$$
CALL migrate_rent_onchain_pending()$$
DROP PROCEDURE migrate_rent_onchain_pending$$
DELIMITER ;