API 쿼리 구현

세자보의 기능을 수행하기 위한 쿼리문을 서술한다.

쿼리 작성에 사용될 뷰 정의

모든 게시물에 대한 정보 및 좋아요/싫어요 수, 각 건물 배치 여부

설명

• 등록된 호감도가 없더라도 0으로 명시할 것.

• 각 건물의 배치 여부는 1 or 0으로 명시할 것.

논리적 명칭	물리적 명칭	타입	비고
게시물 아이디	post_id	INT
기존의 게시물 모든 데이터	복수	복수	기존 post 테이블의 모든 데이터
좋아요 수	up	INT
싫어요 수	down	INT
율곡관 배치 여부	yul	INT	1:True 0:False
대양AI센터 배치 여부	dae	INT	1:True 0:False
학술정보원 배치 여부	hak	INT	1:True 0:False
광개토관 배치 여부	gwang	INT	1:True 0:False
총 건물 배치 개수	build_count	INT

쿼리

CREATE view v_post 
AS 
  SELECT * 
  FROM   (SELECT a.post_id, 
                 author, 
                 reg_date, 
                 exp_date, 
                 title, 
                 content, 
                 url, 
                 img_url, 
                 view_count, 
                 size, 
                 Ifnull(up, 0)               "up", 
                 Ifnull(down, 0)             "down", 
                 IF(yul IS NULL, 0, yul)     "yul", 
                 IF(dae IS NULL, 0, dae)     "dae", 
                 IF(hak IS NULL, 0, hak)     "hak", 
                 IF(gwang IS NULL, 0, gwang) "gwang", 
                 Ifnull(co, 0)               AS num 
          FROM   (SELECT post_id, 
                         author, 
                         reg_date, 
                         exp_date, 
                         title, 
                         content, 
                         url, 
                         img_url, 
                         view_count, 
                         size, 
                         Ifnull(up, 0)   "up", 
                         Ifnull(down, 0) "down" 
                  FROM   post 
                         LEFT JOIN (SELECT * 
                                    FROM   (SELECT post_id, 
                                                   Count(*) AS 'up' 
                                            FROM   like_dislike 
                                            GROUP  BY post_id, 
                                                      interest 
                                            HAVING interest = 1) AS good 
                                           LEFT JOIN (SELECT post_id, 
                                                             Ifnull(Count(*), 0) 
                                                             AS 
                                                             'down' 
                                                      FROM   like_dislike 
                                                      GROUP  BY post_id, 
                                                                interest 
                                                      HAVING interest = 0) AS 
                                                     bad 
                                           USING( 
                                           post_id) 
                                    UNION 
                                    SELECT post_id, 
                                           up, 
                                           down 
                                    FROM   (SELECT post_id, 
                                                   Count(*) AS 'up' 
                                            FROM   like_dislike 
                                            GROUP  BY post_id, 
                                                      interest 
                                            HAVING interest = 1) AS good 
                                           RIGHT JOIN (SELECT 
                                           post_id, 
                                           Ifnull(Count(*), 0) 
                                           AS 
                                           'down' 
                                                       FROM   like_dislike 
                                                       GROUP  BY post_id, 
                                                                 interest 
                                                       HAVING interest = 0) AS 
                                                      bad 
                                           USING( 
                                           post_id) 
                                   ) AS p 
                         USING(post_id)) AS a 
                 LEFT JOIN (SELECT DISTINCT( post_id ), 
                                           IF(yul IS NULL, 0, 1)   "yul", 
                                           IF(dae IS NULL, 0, 1)   "dae", 
                                           IF(hak IS NULL, 0, 1)   "hak", 
                                           IF(gwang IS NULL, 0, 1) "gwang" 
                            FROM   (SELECT DISTINCT( post_id ) 
                                    FROM   post_building) AS pb 
                                   LEFT JOIN (SELECT post_id, 
                                                     building_code AS yul 
                                              FROM   post_building 
                                              WHERE  building_code = 104) AS 
                                             yulb 
                                   USING( 
                                   post_id) 
                                   LEFT JOIN (SELECT post_id, 
                                                     building_code AS dae 
                                              FROM   post_building 
                                              WHERE  building_code = 101) AS 
                                             daeb 
                                   USING( 
                                   post_id) 
                                   LEFT JOIN (SELECT post_id, 
                                                     building_code AS hak 
                                              FROM   post_building 
                                              WHERE  building_code = 103) AS 
                                             hakb 
                                   USING( 
                                   post_id) 
                                   LEFT JOIN (SELECT post_id, 
                                                     building_code AS gwang 
                                              FROM   post_building 
                                              WHERE  building_code = 102) AS 
                                             gwangb USING 
                                   (post_id 
                                   )) AS b USING(post_id) 
                 LEFT JOIN (SELECT post_id, 
                                   Count(*) AS co 
                            FROM   post_building 
                            GROUP  BY post_id) AS c USING(post_id)) AS d 
         JOIN (SELECT student_id "author", 
                      u.name     "user_name", 
                      m.name     "major_name", 
                      color, 
                      major_code 
               FROM   user AS u 
                      JOIN major AS m USING(major_code)) AS e USING(author); 

보드 및 게시물 관련 쿼리

보드에 모든 게시물 불러오기*

1. 현재 날짜가 만료일을 넘지 않는 모든 게시물을 호출한다.

2. 호출된 게시물은 만료 날짜가 빠른 순, 좋아요가 높은 순, 조회수가 높은 순, 게시 날짜가 오래된 순, 블록 크기가 큰 순, 표출 대상 건물의 수가 적은 순으로 정렬한다.

3. 불러오는 정보는 다음과 같다. - 제목 - 내용 - 게시일 - 만료일 - 작성자 프로필(이름, 학번, 학과, 색코드) - 첨부 이미지 - 첨부 링크 - 조회수 - 좋아요/싫어요 수 - 블럭 크기 - 배치된 건물 수 및 각 건물 배치 여부

SELECT vp.post_id, 
       title, 
       content, 
       reg_date, 
       exp_date, 
       user_name, 
       author, 
       major_name, 
       color, 
       url, 
       img_url, 
       view_count, 
       up, 
       down, 
       size, 
       yul, 
       dae, 
       hak, 
       gwang, 
       num 
FROM   v_post AS vp 
WHERE  exp_date > Now() 
ORDER  BY exp_date, 
          up DESC, 
          view_count DESC, 
          reg_date, 
          size DESC, 
          num; 

보드에 특정 건물 게시물 불러오기*

1. 현재 날짜가 만료일을 넘지 않고 특정 건물에 등록된 모든 게시물을 호출한다.

2. 호출된 게시물의 정렬 순서 및 불러올 정보는 "보드에 모든 게시물 불러오기"와 일치한다.

SELECT vp.post_id, 
       title, 
       content, 
       reg_date, 
       exp_date, 
       user_name, 
       author, 
       major_name, 
       color, 
       url, 
       img_url, 
       view_count, 
       up, 
       down, 
       size, 
       yul, 
       dae, 
       hak, 
       gwang, 
       num 
FROM   v_post AS vp 
WHERE  exp_date > Now() 
       AND yul = 1 
ORDER  BY exp_date, 
          up DESC, 
          view_count DESC, 
          reg_date, 
          size DESC, 
          num; 

선택한 게시물 조회수 증가

1. 게시물 아이디가 "X"인 게시물의 조회수를 1 증가시킨다.

update post set view_count=view_count+1 where post_id=16010993;

게시물 호감 기능

1. 게시물 아이디가 "X"인 해당 게시물에 대한 좋아요/싫어요 기능을 수행한다.

2. 게시물 호감 테이블에서 현재 로그인된 사용자, 게시물 아이디, 호감/비호감(1/0) 정보를 삽입한다.

insert into like_dislike values(%s,%s,%s);

게시물 호감 변경 기능

1. 접속중인 사용자가 이미 해당 게시물에 대하여 특정 호감 표시가 등록되어 있고, 그와 반대되는 호감/비호감을 등록할 경우, 호감/비호감 정보만을 수정하여 갱신시킨다.

// 분기별 실행
update like_dislike set interest=1 where student_id=%s and interest=0 and post_id=%s;
update like_dislike set interest=0 where student_id=%s and interest=1 and post_id=%s;

게시물 검색 기능

1. 만료일이 지나지 않은 모든 게시물에 대한 검색 기능이다.

2. 입력한 문자열이 제목, 내용, 작성자 이름, 학번, 소속학과, 소속대학 중에 어느 한곳이라도 부분 문자열로 일치할 경우, 해당 게시물을 호출한다.

3. 출력할 정보 및 정렬 순서는 "보드에 게시물 불러오기"와 일치한다.

SELECT vp.post_id, 
       title, 
       content, 
       reg_date, 
       exp_date, 
       user_name, 
       author, 
       major_name, 
       vp.color, 
       url, 
       img_url, 
       view_count, 
       up, 
       down, 
       size, 
       yul, 
       dae, 
       hak, 
       gwang, 
       num 
FROM   v_post AS vp, 
       college AS col, 
       major AS m 
WHERE  col.college_code = m.college_code 
       AND m.major_code = vp.major_code 
       AND exp_date > Now() 
       AND Concat(title, content, user_name, author, major_name, col.name) LIKE 
           '%퓨터%' 
ORDER  BY exp_date, 
          up DESC, 
          view_count DESC, 
          reg_date, 
          size DESC, 
          num; 

사용자 기능 관련 쿼리

회원정보 검증하기

1. 입력된 아이디와 비밀번호가 현재 사용자 테이블 안에서 일치하는 정보가 존재할 경우, {"result":1}를 반환하며, 없을 경우 {"result":0}을 반환한다.

select * from user where student_id=%s and pw = %s;

회원가입

1. 입력받은 아이디가 존재하지 않을 경우, 사용자 API에서 도출된 정보를 바탕으로 사용자 테이블에 해당 정보를 삽입하여 회원 정보를 생성한다.

2. API를 통해 도출되는 정보는 학번, 비밀번호, 이름, 학과이다.

insert into user values(%s,%s,%s,%s);

회원정보 불러오기

1. 로그인된 사용자의 회원 정보를 호출한다.

2. 불러오는 정보는 다음과 같다. - 이름 - 학번 - 학과 - 자신이 작성한 글의 게시물 정보(제목, 내용, 만료일, 작성자 프로필, 첨부 이미지, 좋아요/싫어요 수)

//회원정보 조회
SELECT student_id, 
       m.name "major_name", 
       u.name "user_name" 
FROM   user AS u, 
       major AS m 
WHERE  u.major_code = m.major_code 
       AND student_id =% s; 
// 쓴글이 있다면 가져오기
select * from v_post where author=%s;
//좋아요 및 싫어요 누른 글 목록 가져오기
SELECT * 
FROM   (SELECT post_id 
        FROM   like_dislike 
        WHERE  student_id =% s 
               AND interest = 1) AS good 
       JOIN v_post AS vp USING(post_id); 
SELECT * 
FROM   (SELECT post_id 
        FROM   like_dislike 
        WHERE  student_id =% s 
               AND interest = 0) AS good 
       JOIN v_post AS vp USING(post_id); 

게시물 등록하기*

1. 현재 로그인 중인 아이디를 게시물 아이디로 참조하여 게시물을 등록한다.

2. 게시물 테이블의 정보는 첨부 이미지를 제외하고는 모두 NOT NULL이 되어야 한다.

3. 조회수는 0으로 초기화하며, 게시일은 현재 날짜를 등록한다.

4. 게시물 등록에 의한 2가지 쿼리는 트랜잭션으로 작동해야 한다.

insert into post values(%s,%s,now(),now()+10,%s,%s,%s,0,%s);

1. 게시물이 등록될 경우, 해당 게시물이 표출될 건물과의 상관 관계를 건물-게시글 테이블에 삽입한다.

insert into post_building values(%s,%s);

게시물 수정하기*

1. 해당 게시물의 정보를 수정한다.

2. 갱신되는 정보는 다음과 같다. - 제목 - 내용 - 첨부 이미지 - 첨부 링크

update post set title=%s, content=%s, img_url=%s, url=%s where post_id=%s;

게시물 삭제하기

1. 입력받은 사용자의 학번과 일치하는 게시물을 삭제한다.

delete from post where post_id=%s;