mirror of
https://github.com/btcpayserver/btcpayserver.git
synced 2025-01-18 13:26:47 +01:00
63 lines
2.5 KiB
SQL
63 lines
2.5 KiB
SQL
-- Rename column
|
|
ALTER TABLE "Payouts" RENAME COLUMN "PaymentMethodId" TO "PayoutMethodId";
|
|
|
|
-- Add Currency column, guessed from the PaymentMethodId
|
|
ALTER TABLE "Payouts" ADD COLUMN "Currency" TEXT;
|
|
UPDATE "Payouts" SET
|
|
"Currency" = split_part("PayoutMethodId", '_', 1),
|
|
"PayoutMethodId"=
|
|
CASE
|
|
WHEN ("Blob"->>'Amount')::NUMERIC < 0 THEN 'TOPUP'
|
|
WHEN split_part("PayoutMethodId", '_', 2) = 'LightningLike' THEN split_part("PayoutMethodId", '_', 1) || '-LN'
|
|
ELSE split_part("PayoutMethodId", '_', 1) || '-CHAIN'
|
|
END;
|
|
ALTER TABLE "Payouts" ALTER COLUMN "Currency" SET NOT NULL;
|
|
|
|
-- Remove Currency and Limit from PullPayment Blob, and put it into the columns in the table
|
|
ALTER TABLE "PullPayments" ADD COLUMN "Currency" TEXT;
|
|
UPDATE "PullPayments" SET "Currency" = "Blob"->>'Currency';
|
|
ALTER TABLE "PullPayments" ALTER COLUMN "Currency" SET NOT NULL;
|
|
ALTER TABLE "PullPayments" ADD COLUMN "Limit" NUMERIC;
|
|
UPDATE "PullPayments" SET "Limit" = ("Blob"->>'Limit')::NUMERIC;
|
|
ALTER TABLE "PullPayments" ALTER COLUMN "Limit" SET NOT NULL;
|
|
|
|
-- Remove unused properties, rename SupportedPaymentMethods, and fix legacy payment methods IDs
|
|
UPDATE "PullPayments" SET
|
|
"Blob" = jsonb_set(
|
|
"Blob" - 'SupportedPaymentMethods' - 'Limit' - 'Currency' - 'Period',
|
|
'{SupportedPayoutMethods}',
|
|
(SELECT jsonb_agg(to_jsonb(
|
|
CASE
|
|
WHEN split_part(value::TEXT, '_', 2) = 'LightningLike' THEN split_part(value::TEXT, '_', 1) || '-LN'
|
|
ELSE split_part(value::TEXT, '_', 1) || '-CHAIN'
|
|
END))
|
|
FROM jsonb_array_elements_text("Blob"->'SupportedPaymentMethods') AS value
|
|
));
|
|
|
|
--Remove "Amount" and "CryptoAmount" from Payout Blob, and put it into the columns in the table
|
|
-- Respectively "OriginalAmount" and "Amount"
|
|
|
|
ALTER TABLE "Payouts" ADD COLUMN "Amount" NUMERIC;
|
|
UPDATE "Payouts" SET "Amount" = ("Blob"->>'CryptoAmount')::NUMERIC;
|
|
|
|
ALTER TABLE "Payouts" ADD COLUMN "OriginalAmount" NUMERIC;
|
|
UPDATE "Payouts" SET "OriginalAmount" = ("Blob"->>'Amount')::NUMERIC;
|
|
ALTER TABLE "Payouts" ALTER COLUMN "OriginalAmount" SET NOT NULL;
|
|
|
|
ALTER TABLE "Payouts" ADD COLUMN "OriginalCurrency" TEXT;
|
|
|
|
|
|
UPDATE "Payouts" p
|
|
SET
|
|
"OriginalCurrency" = "Currency",
|
|
"Blob" = "Blob" - 'Amount' - 'CryptoAmount'
|
|
WHERE "PullPaymentDataId" IS NULL AND "OriginalCurrency" IS NULL;
|
|
|
|
UPDATE "Payouts" p
|
|
SET
|
|
"OriginalCurrency" = pp."Currency"
|
|
FROM "PullPayments" pp
|
|
WHERE "OriginalCurrency" IS NULL AND pp."Id"=p."PullPaymentDataId";
|
|
|
|
ALTER TABLE "Payouts" ALTER COLUMN "OriginalCurrency" SET NOT NULL;
|