[개발일지#005] 데이터베이스 수정작업 그리고 그에 따른 XML 수정

작업을 하면 할 수록 필요한 컬럼들과 테이블들 그리고 수정해야 하는 제약조건들이 늘어난다. 오늘은 데이터베이스를 수정해보기로 하고 그에 따른 XML을 수정하기로 한다.

 

[개발목표]

1. 필요한 테이블, 컬럼, 제약조건 수정 및 삭제
2. xml 쿼리 수정

 

 

[시작하는 말]

간단하게 하니까 크게 수정할게 없을 것 같다고 생각했고

또 나름 테이블 설계를 잘했다고 생각했는데 수정할게 생각보다 많았다. 

후하 그럼 시작!

 

---

1. 필요한 테이블, 컬럼, 제약조건 수정 및 삭제

BOOK 테이블

• BOOK_WRITER (저자) 컬럼이 추가되었다. 출판사는 없어도 책제목, 저자 정도는 있어야 할것 같았다. 
• USE_AT (사용여부) 컬럼이 추가되었다. 책을 등록하고나서 나중에 삭제하고 싶을 수도 있다. 삭제하는 기능을 만들기 위해 사용여부 컬럼을 만들었다. 물리 삭제가 아닌 논리삭제로 진행할 예정이다.
• BOOK_STATE_CODE 는 변경된 것은 없지만, 코드의 의미를 변경했다. BOOK 테이블의 책상태코드는 대여를 할 수 있는지, 없는지에 대한 상태를 나타내기로 했다. 그래서 대여가 됐다면 '대여불가' 반납이 됐거나 빌려간 사람이 없다면 '대여가능'으로 넣기로 했다. 물론 코드명이 아닌 코드로 넣는다. 

 

BOOK_STATE_CODE, RETAL_STATE_CODE 테이블

• 두 테이블 모두 컬럼명과 데이터타입, 제약조건들이 거의 일치한다. 
• 원래 BOOK_STATE_CODE 테이블만 있었는데 책 상태에 대한 정의가 부족해서 책이 '대여가능' , '대여불가능' 여부에 대한 관리만 했다가 BOOK_RENTAL 테이블에서는 이 책을 반납했는지 대여했는지에 대한 상태도 필요해서 테이블을 하나 더 생성했다.
• 그러다보니 두 개의 테이블 명이나 의미가 모호해진 것 같아 아쉬움이 남는다.
• 그리고 대형 프로젝트에서는 보통 이렇게 코드를 따로 관리를 하는데 이렇게 작은 프로젝트에서 코드를 따로 테이블로 관리할 필요가 있을까 하는 의구심은 든다. 

 

BOOK_RENTAL 테이블

• 이 책을 누가 빌려갔고 언제 반납했는지에 대한 기록을 남겨야 해서 만든 테이블인데 가장 고민이 많았다.
• 처음에는 대여할 때 Row 한 줄, 반납할 때 row 한 줄 이런식으로 추가하는 방식으로 데이터를 관리하려고 했는데 그러니까 여러 조회화면에서 각이 안나오는 거다. 고민 끝에 RETURN_DATE 컬럼을 추가로 생성해서, 대여할 때는 BOOK_STATE_CODE에는 'RENTAL' (대여) RENTAL_DATE에 데이터가 들어가고, 반납할 때는 같은 로우에 RETURN_DATE에는 반납시간 BOOK_STATE_CODE에 'RETURN' 코드가 수정되도록 변경했다. 
• 그리고 처음에 BOOK_ID를 기본키로 지정해놨었는데 아주 바보같은 생각이였다. 이 테이블에는 대여/반납에 대한 이력관리 테이블과 흡사하기 때문에 기본키이면 안된다. 한 권에 책을 여러명이 빌릴 수도 있고, 한 사람이 한 책을 여러번 빌릴 수도 있기 때문이다. 그래서 기본키에 대한 제약조건을 모두해제했다. 

 

 

---

2. xml 쿼리 수정

데이터베이스 구조에 여러 변경사항이 있으니 xml도 그에 맞춰 수정하기로 한다.

memberMapper.xml

    <select id="makingBookId" resultType="java.lang.String">
        SELECT
            IFNULL(CONCAT('BOOK', LPAD(MAX(SUBSTR(BOOK_ID, 5))+1, 3, '0')), 'BOOK001')
        FROM BOOK
    </select>

    <select id="findByBookId" parameterType="String"
            resultType="seulgi.bookRentalSystem.domain.book.Book">
        SELECT
               BOOK_ID AS bookId
             , BOOK_NAME AS bookName
             , BOOK_WRITER AS bookWriter
             , AUTHOR_ID AS authorId
             , (SELECT MEMBER_NAME
                FROM MEMBER_TB
                WHERE MEMBER_ID = AUTHOR_ID ) AS authorName
             , BOOK_STATE_CODE AS bookStateCode
             , (SELECT STATE_CODE_NAME
                FROM BOOK_STATE_CODE
                WHERE STATE_CODE = BOOK_STATE_CODE) AS bookStateCodeName
             , CREATE_DATE AS createDate
        FROM BOOK
        WHERE BOOK_ID = #{bookId}
          AND USE_AT = 'Y'
        ORDER BY createDate DESC;
    </select>

    <select id="allBookList" resultType="seulgi.bookRentalSystem.domain.book.Book">
        SELECT
               BOOK_ID AS bookId
             , BOOK_NAME AS bookName
             , BOOK_WRITER AS bookWriter
             , AUTHOR_ID AS authorId
             , (SELECT MEMBER_NAME
                FROM MEMBER_TB
                WHERE MEMBER_ID = AUTHOR_ID ) AS authorName
             , BOOK_STATE_CODE AS bookStateCode
             , (SELECT STATE_CODE_NAME
                FROM BOOK_STATE_CODE
                WHERE STATE_CODE = BOOK_STATE_CODE) AS bookStateCodeName
             , CREATE_DATE AS createDate
        FROM BOOK
        WHERE USE_AT = 'Y'
        ORDER BY createDate DESC;
    </select>

    <insert id="addBook" parameterType="seulgi.bookRentalSystem.domain.book.Book">
        INSERT INTO BOOK
                    ( BOOK_ID
                    , BOOK_NAME
                    , BOOK_WRITER
                    , AUTHOR_ID
                    , BOOK_STATE_CODE
                    , CREATE_DATE
                    , USE_AT
        ) VALUES (   #{book.bookId}
                   , #{book.bookName}
                   , #{book.bookWriter}
                   , #{book.authorId}
                   , #{book.bookStateCode}
                   , now()
                   , 'Y'
                   )
    </insert>

    <update id="editBook" parameterType="java.util.Map">
        UPDATE BOOK
        SET BOOK_NAME = #{book.bookName}
          , BOOK_WRITER = #{book.bookWriter}
          , BOOK_STATE_CODE =#{book.bookStateCode}
        WHERE BOOK_ID = #{bookId}
          AND USE_AT = 'Y'
    </update>

BOOK_ID에 논리삭제 구조인 USE_AT이 들어갔으니 모든 쿼리에도 USE_AT조건을 넣어주기로 한다. 

 

(+) IFNULL 추가

    <select id="makingBookId" resultType="java.lang.String">
        SELECT
            IFNULL(CONCAT('BOOK', LPAD(MAX(SUBSTR(BOOK_ID, 5))+1, 3, '0')), 'BOOK001')
        FROM BOOK
    </select>

참고로 책 아이디를 생성하는 쿼리에는 IFNULL을 추가했다. 데이터가 없을 땐 쿼리에러가 난다.  오라클 nvl만 쓰다가 mySQL쓰닌 IFNULL 사용!