Files

72 lines
2.0 KiB
SQL

DROP PROCEDURE IF EXISTS `UpdateHotBitcoinAddressUnspentTotals`
#
DROP PROCEDURE IF EXISTS `CreateHotBitcoinAddressUnspentConditional`
#
CREATE PROCEDURE `CreateHotBitcoinAddressUnspentConditional`(
IN hotBitcoinAddressId INT,
IN amountSatoshis BIGINT,
IN transactionHash VARCHAR(128),
IN transactionOutputIndex INT,
IN confirmationCount INT
)
BEGIN
DECLARE unspentId INT;
IF ((SELECT COUNT(*) FROM HotBitcoinAddressUnspents
WHERE HotBitcoinAddressUnspents.TransactionHash=transactionHash
AND HotBitcoinAddressUnspents.TransactionOutputIndex=transactionOutputIndex
AND HotBitcoinAddressUnspents.HotBitcoinAddressId=hotBitcoinAddressId) = 0)
THEN
INSERT INTO HotBitcoinAddressUnspents
(HotBitcoinAddressId,AmountSatoshis,TransactionHash,TransactionOutputIndex,ConfirmationCount)
VALUES
(hotBitcoinAddressId,amountSatoshis,transactionHash,transactionOutputIndex,confirmationCount);
SELECT LAST_INSERT_ID() AS Identity;
ELSE
SELECT HotBitcoinAddressUnspents.HotBitcoinAddressUnspentId INTO unspentId
FROM HotBitcoinAddressUnspents
WHERE HotBitcoinAddressUnspents.TransactionHash=transactionHash
AND HotBitcoinAddressUnspents.TransactionOutputIndex=transactionOutputIndex
AND HotBitcoinAddressId=hotBitcoinAddressId;
UPDATE HotBitcoinAddressUnspents
SET HotBitcoinAddressUnspents.ConfirmationCount=confirmationCount
WHERE HotBitcoinAddressUnspents.HotBitcoinAddressUnspentId=unspentId;
SELECT -unspentId AS Identity;
END IF;
END
#
CREATE PROCEDURE `UpdateHotBitcoinAddressUnspentTotals` ()
BEGIN
UPDATE HotBitcoinAddresses
INNER JOIN
(
SELECT HotBitcoinAddressId, SUM(AmountSatoshis) AS UnspentAmountSum
FROM HotBitcoinAddressUnspents
GROUP BY HotBitcoinAddressId
) innerSelection ON HotBitcoinAddresses.HotBitcoinAddressId = innerSelection.HotBitcoinAddressId
SET HotBitcoinAddresses.BalanceSatoshis = innerSelection.UnspentAmountSum
WHERE HotBitcoinAddresses.HotBitcoinAddressId=hotBitcoinAddressId;
END