root
42 lines
1.8 KiB
SQL
42 lines
1.8 KiB
SQL
-- Asset-aware balances migration.
|
|
-- Back up the database before running this on an existing installation.
|
|
|
|
CREATE TABLE IF NOT EXISTS user_balances (
|
|
user_id INT NOT NULL,
|
|
asset_type VARCHAR(16) NOT NULL,
|
|
balance DECIMAL(32, 12) NOT NULL DEFAULT 0.000000000000,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (user_id, asset_type),
|
|
CONSTRAINT user_balances_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
ALTER TABLE deposits ADD COLUMN asset_type VARCHAR(16) NOT NULL DEFAULT 'SAL1';
|
|
ALTER TABLE tips ADD COLUMN asset_type VARCHAR(16) NOT NULL DEFAULT 'SAL1';
|
|
ALTER TABLE withdrawals ADD COLUMN asset_type VARCHAR(16) NOT NULL DEFAULT 'SAL1';
|
|
|
|
-- Existing production data was classified as SAL1, except known token deposits.
|
|
-- For this deployment deposits.id 41 and 42 were salCULT.
|
|
UPDATE deposits SET asset_type = 'SAL1';
|
|
UPDATE deposits SET asset_type = 'salCULT' WHERE id IN (41, 42);
|
|
UPDATE tips SET asset_type = 'SAL1';
|
|
UPDATE withdrawals SET asset_type = 'SAL1';
|
|
|
|
-- Replace the old txid-only uniqueness with per-user, per-asset uniqueness.
|
|
ALTER TABLE deposits DROP INDEX txid;
|
|
ALTER TABLE deposits ADD UNIQUE KEY uniq_deposit_tx_user_asset (txid, user_id, asset_type);
|
|
|
|
-- Seed SAL1 balances from the legacy users.tip_balance column after any manual corrections.
|
|
INSERT INTO user_balances (user_id, asset_type, balance)
|
|
SELECT id, 'SAL1', COALESCE(tip_balance, 0)
|
|
FROM users
|
|
WHERE COALESCE(tip_balance, 0) <> 0;
|
|
|
|
-- Seed token balances from non-SAL1 deposits.
|
|
INSERT INTO user_balances (user_id, asset_type, balance)
|
|
SELECT user_id, asset_type, SUM(amount)
|
|
FROM deposits
|
|
WHERE asset_type <> 'SAL1'
|
|
GROUP BY user_id, asset_type
|
|
ON DUPLICATE KEY UPDATE balance = VALUES(balance);
|