[SW 정글 106일차] 나만의 무기 14일차 (새로운 API 설계)

오늘은 로그인을 하지 않은 유저가 welcome page(첫 페이지)에서 볼 수 있는 생명체들을 위한 데이터를 던져주는 api를 설계했다.
우리의 프로젝트에서는 첫 페이지에서 기존에 우리의 서비스를 이용하는 고객들이 목표달성을 하면서 키우는 각자의 생명체를 50마리 정도를 보여주려고 한다.
50마리를 선정하는 기준은 아직 확실하게 정해져있지는 않지만 지금은 일단 목표달성 인증 횟수가 가장 많은 생명체를 보여주도록 설계했다.
음.. 약간 인기글을 보여주는 것과 비슷하다고 말해야할까?
아직 우리의 서비스에 가입하지 않은 사용자에게 어떠한 목표 달성 컨텐츠가 있는지와 다른 사람들이 얼마나 열심히 사는지를 보여주는 목적이 있다.
이를 위해서는 생명체에 대한 정보를 프론트에 보내는 api가 필요했고 나는 오늘 그 부분을 담당하여 만들었다.

 

 

 

 

1. 생명체 정보 보내는 API

보내려는 데이터를 가지고 있는 테이블은 사용자가 현재 키우고 있는 생명체 데이터를 가지고 있는 alien테이블과 졸업시킨 생명체 데이터를 가지고 있는 alien_graduated 테이블이다.
여기서 말하는 졸업이라는 단어는 우리끼리 가칭으로 정한 것인데 간단하게 설명하면 목표달성을 끝내고 생명체를 소장하거나 목표달성 중에 새로운 생명체를 다시 키우고자 할 때 쓰는 기능이다.
내가 해결해야할 포인트는 2개의 테이블을 합쳐서 인증횟수가 가장 많은 생명체 50마리에 대한 rows를 보내야한다는 것이였다.
2개의 테이블은 같은 column을 가지고 있어서 가로로 합쳐야했다.
sql문을 모두 알고 있지는 않아서 구글링을 통해 UNION을 사용하면 된다는 것을 알았다.

SELECT * FROM Alien UNION SELECT * FROM Alien_graduated ORDER BY accuredAuthCnt DESC LIMIT 50;

아직 서비스를 오픈한 것이 아니여서 더미데이터를 insert시켜줘야하는데 이 작업이 생각보다 시간을 많이 잡아먹었다...
그리고 00시 00분에 scheduler가 돌면서 어떠한 조건에 맞으면 alien table에 있는 데이터를 삭제시켜주는데 다른 팀원이 local에서 서버를 켜고 있었고 배포도 된 상태라 데이터가 삭제되어서 한 번 더 삭제된 데이터를 넣어주는 작업을 했다...

API를 다 만들고 postman으로 get 요청을 보내서 결과를 확인해봤는데 정상적으로 동작했다.
프론트에서 다른 요구사항이 있을 수 있지만 일단은 해결완료!

 

 

 

 

 

2. 사망, 졸업 기능 개선

우리의 서비스에서는 정해진 시간 안에 목표 달성 인증을 하지 않으면 생명체를 죽이고 위에서 설명한 졸업기능을 auto로 켜놓으면 사용자가 예약을 할 수 있는 기능이 있다.
이 기능을 위해서 shceduler를 돌려서 00시 00분이면 사망처리해야 할 생명체와 졸업시켜야 할 생명체를 테이블에서 조회해서 처리해야한다.

지난 주에 내가 기능을 구현했던 것은 위와 같은 flow였다.
(그림은 사망처리만 나타냈고 졸업도 같은 flow이다.)
alien테이블과 authentification 테이블이 foregin key로 관계가 형성되어 있어 alien에 있는 row를 먼저 삭제할 수 없어서 위 순서가 중요했다.

 

여기까지하면 다 됐다고 생각했는데 구현을 하고 계속 코드를 보면서 '놓친 부분이 없나' 생각을 했다.

그리고 오늘 놓친 것을 하나 생각했다.

사망이나 졸업을 했으면 챌린저참여인원이 줄어야한다는 것이다!!

이를 위해서 어떻게 해야할지 고민을 했다.

챌린지테이블에서 관리 중인 챌린지 참여자 수에 접근해서 -1을 해줘야하고 그럴려면 challenge_id가 필요한데...

그러면 alien_dead나 alien_graduated로 간 alien이 참여했던 challenge 정보를 알면 될 것이고..

challenge_id는 alien_dead나 alien_graduated가 가지고 있으니까 challenge 테이블과 조인해서 값을 update하면 되겠다는 생각을 했다.

하지만, 단순히 join을 하면 이미 alien_dead나 alien_graduated에 존재하는 데이터가 있기 때문에 방금 들어간 alien에 대해서만 참여자 수를 -1해줘야한다.

column을 하나 추가해서 조건을 주면 될까?

이러한 고민을 이어가면서 내가 머릿 속으로 그린 flow가 쿼리문으로 할 수 있는지를 찾아봤다.

 

일단은 challenge 테이블과 alien_dead 테이블을 join하고 challenge 테이블의 특정 컬럼값만 바꿀 수 있는지를 찾아봤다.

찾아 본 결과 아래와 같은 쿼리문을 쓰면 된다고 했다.

신기한 것이 join없이 select로 2개의 테이블을 불러와서 where로 조건만 주면 합쳐진 테이블을 볼 수 있다는 것이였다.

UPDATE (SELECT Challenge.participantNumber FROM Challenge, Alien_graduated
        WHERE Challenge.id= Alien_graduated.Challenge_id)
        SET Challenge.participantNumber = Challenge.participantNumber - 1;

 하지만 아래와 같은 에러문을 마주쳤다...

Error Code: 1248. Every derived table must have its own alias

 

그래서 구글링을 통해서 알아본 결과, alias(별칭)을 선언을 안해줘서라고 한다.

select로 만들어진 임시 테이블이 이름이 없어서 그런 것인가?해서 아래 쿼리문처럼 이름을 붙여줬다.

UPDATE (SELECT Challenge.participantNumber FROM Challenge, Alien_graduated
        WHERE Challenge.id= Alien_graduated.Challenge_id) AS T
        SET Challenge.participantNumber = Challenge.participantNumber - 1;

하지만 다시 에러를 마주쳤다...

Error Code: 1288. The target table T of the UPDATE is not updatable

 

그리고 다시 구글링을 했고 아래의 사이트에서 좋은 정보를 얻었다.

https://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=qna_db&wr_id=215693 

WWW.PHPSCHOOL.COM

임시 테이블에 이름을 줬다고 해서 업데이트를 할 수 있는 것은 아니였다.

말그대로 임시테이블이였고 다른 쿼리문을 사용해야했고 그래서 아래와 같이 수정하고 성공적으로 UPDATE를 할 수 있었다.

UPDATE Challenge challenge, Alien_dead alien
SET challenge.participantNumber = challenge.participantNumber - 1
WHERE challenge.id = alien.Challenge_id;

challenge 테이블에서 참여자 수가 -1되는 것을 확인할 수 있었고 아직 수정할 부분이 남아있다.

중복해서 -1되는 것을 방지하기 위해 alien_dead와 alien_graduated 테이블에 column을 하나 추가해서 이전에 -1해줬다는 것을 알 수 있도록 해주고 그 값을 where로 비교하여 중복해서 빼지 않도록 하는 것이다.

빼고나면 다시 alien_dead와 alien_graduated 테이블에 column의 값은 다른 값으로 수정해주는 쿼리도 날리고...

 

의식의 흐름대로 쿼리문을 작성하니까 이 기능을 위해서만 쿼리문이 5개정도 날려지는 것 같다.

더 빠른 실행속도를 위해 쿼리튜닝도 하고 테이블 개선도 해야하지만 일단은 동작하는 것을 확인하고 차 후에 개선해봐야겠다.

 

 

 

 

<해야 할 일>

DB Lock 공부하기

JOIN 공부하기

DB index 공부하기 (B+ treee도 함께 하면 좋을 듯)

redis 도입방안 생각과 알아보기