출처 : http://woowabros.github.io/experience/2018/10/12/new_point_story_1.html
위 출처를 보면 우아한형제들에서 포인트 시스템을 구축할 때 생각했던 점들이 나온다.
나도 어느 순간 포인트 시스템을 구현해야 했는데 많은 도움을 받았다.
위 도표를 보고 DB로만 구현한 내용을 공유해보고자 한다.
MariaDB 10.4 버전을 사용하였으나 어지간하면 다 동작할 것이다.
point.sql-- 사용자
CREATE TABLE IF NOT EXISTS `users` (
`Id` binary(16) NOT NULL,
`Name` varchar(50) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 포인트 이벤트
CREATE TABLE IF NOT EXISTS `point_event` (
`Id` binary(16) NOT NULL,
`UserId` binary(16) NOT NULL,
`Type` char(1) NOT NULL DEFAULT 'A',
`Point` int(11) NOT NULL DEFAULT 0,
`Comment` varchar(64) NOT NULL DEFAULT '',
`ExpireDate` date DEFAULT NULL,
`CreateDate` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`Id`),
KEY `FK_point_event_userId_users_id` (`UserId`),
CONSTRAINT `FK_point_event_userId_users_id` FOREIGN KEY (`UserId`)
REFERENCES `users` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 포인트 상세
CREATE TABLE IF NOT EXISTS `point_detail` (
`Id` binary(16) NOT NULL,
`PointId` binary(16) NOT NULL,
`GroupId` binary(16) NOT NULL,
`Type` char(1) NOT NULL DEFAULT 'A',
`Point` int(11) NOT NULL DEFAULT 0,
`CreateDate` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`Id`),
KEY `FK_point_detail_PointId_reward_point_id` (`PointId`),
CONSTRAINT `FK_point_detail_PointId_reward_point_id` FOREIGN KEY (`PointId`)
REFERENCES `point_event` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 사용한 포인트를 취소하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_use_cancel`(
IN iPointId BINARY(16),
IN iComment VARCHAR(64)
)
COMMENT '포인트 사용 취소'
BEGIN
DECLARE mNewPointId BINARY(16);
DECLARE mType char(1);
SET mNewPointId = fn_create_key();
-- U 타입만 취소 가능함
select `Type` into mType
FROM point_event
WHERE Id = iPointId;
if mType = 'U' then
INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
select mNewPointId, UserId, 'C', (`Point` * -1), iComment
from point_event
where Id = iPointId;
INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
select fn_create_key(), mNewPointId, 'C', (`Point` * -1), GroupId
from point_detail
WHERE PointId = iPointId;
end if;
END//
DELIMITER ;
-- 만료일이 지난 포인트 정리하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_expire`(
IN `iWorkDate` DATE
)
COMMENT '포인트 만료 정리'
BEGIN
DECLARE mDone INT DEFAULT 0;
DECLARE mUserId BINARY(16);
DECLARE mGroupId BINARY(16);
DECLARE mPoint INT;
DECLARE mPointId BINARY(16);
DECLARE mDetailId BINARY(16);
DECLARE cur CURSOR FOR
SELECT t.GroupId, t.`Point`
FROM (
SELECT d.GroupId, SUM(d.`Point`) AS `Point`
FROM point_event AS p
JOIN point_detail AS d ON p.Id = d.PointId
WHERE p.ExpireDate < iWorkDate OR p.ExpireDate IS NULL
GROUP BY d.GroupId
) AS t
WHERE `Point` > 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET mDone = 1;
OPEN cur;
read_loop : LOOP
FETCH cur INTO mGroupId, mPoint;
IF mDone THEN
LEAVE read_loop;
END IF;
SELECT p.UserId INTO mUserId
FROM point_detail AS d
JOIN point_event AS p ON d.PointId = p.Id
WHERE d.GroupId = mGroupId
LIMIT 1;
SET mPointId = fn_create_key();
INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
VALUES(mPointId, mUserId, 'E', (mPoint * -1), '유효기간만료');
INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
VALUES(fn_create_key(), mPointId, 'E', (mPoint * -1), mGroupId);
END LOOP;
CLOSE cur;
END//
DELIMITER ;
-- 포인트 적립하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_add`(
IN iUserId binary(16),
IN iPoint Int,
IN iExpireDate Date,
IN iComment varchar(64)
)
COMMENT '포인트 적립'
BEGIN
declare mPointId binary(16);
DECLARE mDetailId BINARY(16);
set mPointId = fn_create_key();
SET mDetailId = fn_create_key();
insert into point_event(Id, UserId, `Point`, `Comment`, ExpireDate)
values(mPointId, iUserId, iPoint, iComment, iExpireDate);
INSERT INTO point_detail(Id, PointId, `Point`, GroupId)
values(mDetailId, mPointId, iPoint, mDetailId);
END//
DELIMITER ;
-- 포인트 사용하는 프로시저
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_point_use`(
IN iUserId BINARY(16),
IN iPoint INT,
IN iComment VARCHAR(64),
OUT oResultCode char(1),
out oPointId BINARY(16)
)
COMMENT '포인트 사용'
BEGIN
/*
* oResultCode : A-승인, F-잔여포인트 부족, E-Exception
*/
DECLARE mDone INT DEFAULT 0;
DECLARE mPointId BINARY(16);
DECLARE mDetailId BINARY(16);
DECLARE mGroupId BINARY(16);
Declare mTotalPoint INT;
declare mCurrentPoint INT;
DECLARE mPoint INT;
DECLARE cur CURSOR FOR
SELECT t.GroupId, t.`Point`
FROM (
SELECT d.GroupId, SUM(d.`Point`) AS `Point`
FROM point_event AS p
JOIN point_detail AS d ON p.Id = d.PointId
WHERE p.UserId = iUserId
-- AND p.ExpireDate >= CURRENT_DATE
GROUP BY d.GroupId
ORDER BY p.ExpireDate
) AS t
WHERE `Point` > 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET mDone = 1;
/* 만약 SQL에러라면 ROLLBACK 처리한다. */
DECLARE exit handler for SQLEXCEPTION
BEGIN
SET oResultCode = 'E';
ROLLBACK;
END;
set mCurrentPoint = iPoint;
START TRANSACTION;
-- 총 포인트를 구해본다
SELECT IFNULL(SUM(`point`), 0) INTO mTotalPoint
FROM users AS u
left join point_event AS p ON u.Id = p.UserId
WHERE u.Id = iUserId
GROUP BY p.UserId;
if(mTotalPoint < iPoint) then
set oResultCode = 'F';
else
-- point 를 먼저 넣고
SET mPointId = fn_create_key();
INSERT INTO point_event(Id, UserId, `Type`, `Point`, `Comment`)
VALUES(mPointId, iUserId, 'U', (iPoint * -1), iComment);
OPEN cur;
read_loop : LOOP
FETCH cur INTO mGroupId, mPoint;
IF mDone OR mCurrentPoint < 1 THEN
LEAVE read_loop;
END IF;
SET mDetailId = fn_create_key();
if mCurrentPoint > mPoint then
-- 빼야하는게 더 크면 통째로 뺀다
INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
VALUES(mDetailId, mPointId, 'U', (mPoint * -1), mGroupId);
set mCurrentPoint = mCurrentPoint - mPoint;
else
-- 빼야하는게 같거나 작다면 일부만 뺀다
INSERT INTO point_detail(Id, PointId, `Type`, `Point`, GroupId)
VALUES(mDetailId, mPointId, 'U', (mCurrentPoint * -1), mGroupId);
SET mCurrentPoint = 0;
end if;
END LOOP;
CLOSE cur;
-- 결과값 반환
SET oResultCode = 'A';
set oPointId = mPointId;
end If;
COMMIT;
END//
DELIMITER ;
-- binary key 생성
DELIMITER //
CREATE DEFINER=`root`@`%` FUNCTION `fn_create_key`() RETURNS binary(16)
COMMENT '순차적 BINARY ID 생성'
BEGIN
SET @UUID = UUID();
RETURN
UNHEX(CONCAT(
SUBSTR(@UUID, 15, 4),
SUBSTR(@UUID, 10, 4),
SUBSTR(@UUID, 1, 8),
SUBSTR(@UUID, 20, 4),
SUBSTR(@UUID, 25) ));
END//
DELIMITER ;
