복잡한 로직은 복잡한 DAO로 직결되는가?

아니지 않을까… 비즈니스 로직이 복잡하다고 해서 SQL이 복잡해 질 필요는 없다.

재고 마감 로직을 생각해보자. 모든 창고에 들어있는 모든 상품들의 전일 재고량, 금일 입고량, 금일 출고량을 가져와서 금일 재고량을 계산한다고 치자. 이걸 가지도 또 상품 카테고리 별로 묶고 창고 별로 묶어서 집계를 내야 하는데.. 일단은 그 전 단계까지만 해보자.
Location – Inventory
Item – Inventory
Item – InvIn
Item – InvOut
Item – InvDailyClosing
이렇게 관계가 맺어져 있으니 SQL로 join을 하던 sub select를 하던 어떻게든 SQL 한방으로 어떤 창고에 있는 어떤 상품의 ‘전일 재고량’, ‘금일 입고량’, ‘금일 출고량’을 한방에 가져올 수 있을 것 같다.
근데 난 그렇게 하지 않는다. 사실 그렇게 복잡한걸 만들지도 못한다. 만들라면 공부해가면서 만들겠지만 무지 오래 걸릴 것 같다. 그래서 난 그냥 자바 코드로 SQL을 대신한다.
        dao.delelteAllAt(today);
        for(Location location : locationDao.getAll()) {
            // 모든 상품 마다
            for(Item item : itemDao.getAll()){
                // 전일 재고량 조사
                double qtyStart = inventoryDao.getQtyOf(yesterday(today), item, location);
                // 금일 입고량 조사
                double qtyIn = invInDao.getQtyOf(today, item, location);
                // 금일 출고량 조사
                double qtyOut = invOutDao.getQtyOf(today, item, location);
                // 금일 재고량 계산
                double qtyEnd = qtyStart + qtyIn – qtyOut;
               나머진 생략…
            }
        }
즉 이런식으로 짠다. 굵은 글씨 부분은 DAO test를 해서 해당 기능이 내가 원한대로 동작하는지 일일히 확인해다. DBUnit을 사용해서 테스트용 데이터를 넣고 저 기능을 실행해보는 방식이다. 이렇게 구현하니까 상당히 마음이 놓인다. 저걸 만약 한방에 SQL로 구현했다던지.. 테스트 없이 구현했다면.. 글쎄…. 그렇겐 못했을 것 같다.
암튼 이래서.. 복잡한 로직을 처리하는 DAO 코드라 할지라도 단순해진다. 이런걸 디바이드 앤 퀀쿼 라고 하던가.. 몰겠다. 머라하든.
    public double getQtyOf(Date date, Item item, Location location) {
        Object result = getSession().createQuery(“select sum(qty) from InvIn where date = :date and item = :item and location = :location group by item“)
                .setDate(“date”, DateUtils.getDateOnly(date))
                .setEntity(“item”, item)
                .setEntity(“location”, location)
                .uniqueResult();
        if(result == null)
            return 0.0;
        return (Double) result;
    }
대충 이정도 코드가 생기는데.. 이정도야 뭐.. 초간단 SQL 수준 아닌가..
이렇게 짜면 걱정되는게.. 성능인데… DB에 자주 다녀올수록 더 많은 부하가 생기는건 사실이지만 어차피 복잡한 쿼리 자체도 join 여러번 하면서 부하가 생길테니.. SQL 도사가 아닌 이상 차라리 성능을 조금 포기하고 유지 보수 가능하고 알아보기 쉬운 코드로 나눠서 작성하는게 더 좋치 않을까 싶다.

Chapter 14: Case Study: Building a Product Catalog

참조 : The Programmer’s Guide to SQL

오라클에서 테이블 만들기

CREATE TABLE Category (
CategoryID INT NOT NULL PRIMARY KEY,
DepartmentID INT NOT NULL,
Name VARCHAR(50) NOT NULL,
Description VARCHAR (200) NULL,
FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID));

CREATE SEQUENCE CategoryIDSeq;

CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
BEFORE INSERT ON Category
FOR EACH ROW
BEGIN
   SELECT CategoryIDSeq.NEXTVAL
   INTO :NEW.CategoryID FROM DUAL;
END;
/
– 시퀀스 만들고, 트리거 만들어서 자동 증가값처럼 사용하기.

CREATE TABLE ProductCategory (
ProductID INT NOT NULL,
CategoryID INT NOT NULL,
PRIMARY KEY (ProductID, CategoryID),
FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID)
);
– Junction 테이블.

데이터 가져오기

SELECT Name, Price FROM
      (SELECT Name, Price
       FROM Product
       ORDER BY Price DESC, Name ASC)
WHERE ROWNUM<=5;
– 상위 다섯개의 데이터 가져오기.
– id를 사용하지 않는다. id가 꼭 순서대로 매겨진다는 보장이 없으니까..

SELECT ProductID, Name FROM
(
SELECT RANK() OVER (ORDER BY ProductID) As Ranking, ProductID, Name
FROM Product
ORDER BY PRODUCTID
)
WHERE Ranking BETWEEN 6 AND 8;
– 특정 범위의 값 가져오기.

SELECT C.Name as “Category Name”, P.Name as “Product Name”
FROM Product P
INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
INNER JOIN Category C ON PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> WHERE 절을 사용하도록 변경하면…
SELECT C.Name as “Category Name”, P.Name as “Product Name”
FROM Product P, ProductCategory PC, Category C
WHERE P.ProductID = PC.ProductID AND PC.CategoryID = C.CategoryID
ORDER BY C.Name, P.Name;
=> 연관된 서브쿼리를 사용하도록 변경하면..
SELECT C.Name as “Category Name”, Product.Name as “Product Name”
FROM Product, Category C
WHERE Product.ProductID IN
  (SELECT ProductID FROM ProductCategory
   WHERE ProductCategory.CategoryID = C.CategoryID)
ORDER BY C.Name, Product.Name;

데이터 검색

SELECT Name, Description
FROM Product
WHERE (Description || Name LIKE ‘%devil%’)
  AND (Description || Name LIKE ‘%mask%’);
– 여러 컬럼에서 특정 문자열 검색할 때 꼼수로 모든 검색 대상 필드를 문자열로 변환하여 붙인다음에 검색.

The Programmer’s Guide to SQL

사용자 삽입 이미지

Chapter 1: Understanding SQL and Relational Databases
Chapter 2: Retrieving Data with SQL
Chapter 3: Modifying Data
Chapter 4: Summarizing and Grouping Data
Chapter 5: Performing Calculations and using Functions
Chapter 6: Combining SQL Queries
Chapter 7: Querying Multiple Tables
Chapter 8: Hiding Complex SQL with Views
Chapter 10: Transactions
Chapter 11: Users and Security
Chapter 12: Working with Database Objects

프로시져(9장), 트리거(13장), 예제(14, 15장)은 빼고 오라클 중심으로 봤더니 빨리 볼 수 있었습니다.

SQL 기본 서적으로 매우 좋은 것 같습니다. 쉽게 잘 설명해줘서 머리에 잘 들어옵니다. SQL문을 그냥 외우는 거에 비하면 훨씬 재밌습니다.

그리고 각종 DB 벤더들의 RDBMS 특징들도 다루고 있어서 오라클에서는 이런게 되는데 MySQL에서는 이런게 안되고 이렇게 해야 한다는 등의 내용이 들어있습니다. 다만 2003년 책이라서 그동안 바뀐 내용들이 엄청 많을 것 같습니다. 개정판이 나오면 좋겠습니다. PostgreSQL이나 HSQL은 다루고 있질 않아서 조금 아쉽습니다.

다음은 읽으면서 생각했던 것들입니다.

  • 트랜잭션 상태 바꾸기: SET TRANSACTION …
  • 서브쿼리에서 ALL, ANY 사용하기: 뭐가 햇갈렸더라..
  • 서브쿼리와 JOIN의 성능차이: 서브쿼리는 외부 쿼리의 레코드 갯수마다 서브쿼리를 수행해야 하기 때문에,
    외부 쿼리 결과 레코드의 갯수와 서브쿼리 소요시간에 비례하여 전체 소요 시간이 증가할 것이다. JOIN은 데카르트 곱을 사용하여
    관계를 맺는 테이블들의 레코드 갯수에 비례하여 소요되는 시간이 증가할 것이다. 단순비교를 할 수는 없겠다. 실제 쿼리와 레코드
    갯수를 가지고 비교해봐야겠다.
  • INNER JOIN과 OUTER JOIN 성능차이: 거의 차이가 없을 것이다. 부가작업(대응하는 필드가 없는
    경우 NULL로 채워주는 일)이 필요한 OUTER JOIN의 경우 조금 더 오래 걸릴 수도 있겠지만, 어차피 둘 다 데카르트 곱
    연산은 수행하기 때문이다.
  • 뷰 만들 때 옵셥주기: CREATE VIEW AS 쿼리 옵션. 여기서 옵션을 어떻게 주는건지는 책에 안
    나왔네..나중에 찾아봅세. 그리고 VIEW를 조작하려면 지켜야 하는 조건들이 있는데, 거기서 벗어나더라도 옵션으로 수정 가능하게
    하면 테이블에 영향을 줄 수 있는건가? 그러진 않을 것 같다.

Chapter 12: Working with Database Objects

참조 : The Programmer’s Guide to SQL

데이터베이스 만들기
    – CREATE DATABASE db이름
    – 오라클은 보통 DB를 한 개만 사용하고 스키마를 이용해서 관련있는 테이블들과 다른 객체를 그룹핑한다.
데이터베이스 제거
    – 오라클에서는DROP DATABASE가 아니라 CREATE DATABASE 로 제거

테이블 만들기
    – CREATE TABLE <table name>
   (<column name> <column data type> [<column constraints>]);
테이블 제거
    – DROP TABLE <table name>
    – FOREIGN KEY 제약으로 인해 참조 되고 있는 테이블이면 제거가 되지 않는다. 제거하기 전에 이 제약부터 제거해야 한다.

컬럼에 기본값 주기
    – DEFAULT <기본값>
    – 컬럼 기본값을 함수나 키워드를 사용할 수 있다.
    GETDATE() = SYSDATE = CURRENT_DATE
주키 설정하기
    – 주키 이름 설정하고 싶을 때

  CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   CONSTRAINT MyPrimaryKey PRIMARY KEY (Name));

    – 주키 이름에 관심없을 때

   CREATE TABLE Friend (
   Name    VARCHAR(50) NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL,
   PRIMARY KEY (Name));

    – 이것을 좀 더 간단하게

  CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’ NOT NULL);

    – 주키 제약을 설정하면 자동으로 고유한 인덱스가 생성된다.
UNIQUE 설정
    – 오라클에서는 기본으로 UNIQUE 속성의 컬럼에 다수의 NULL 넣을 수 있슴.
컬럼 제약 설정
    – CHECK 사용하기

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT,
   CONSTRAINT CheckAge CHECK (Age BETWEEN 10 and 100));

    – 제약 사항의 이름이 필요 없다면

    CREATE TABLE Friend (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL,
   PhoneNo VARCHAR(15) DEFAULT ‘Unknown Phone’,
   Age     INT CHECK (Age BETWEEN 10 and 100));

테이블 복사 하기
    – CREATE TABLE My_Friends AS SELECT * FROM Friend;
    – 단 제약 사항들(주키나 유니크 같은 것들)은 복사 되지 않는다. 그렇기 때문에 테이블을 만들 때는 기본 구조만 정의하고 Alter Table로 실행할 스크립트를 만들어 두는 것이 유용하다.
    – 데이터는 빼고 구조만 복사하고 싶다면 WHERE 절에 false가 되도록 설정해준다.
    CREATE TABLE My_Friends AS SELECT * FROM Friend
    WHERE 1=0;

테이블 변경하기
    – 컬럼 추가하기 : ALTER TABLE Friend ADD (EMail VARCHAR(25), ICQ VARCHAR(15));

        – NOT NULL 인 컬럼을 추가하려면, 테이블에 데이터가 들어있지 않아야 한다.
    – 컬럼 없애기 : ALTER TABLE Friend DROP COLUMN PhoneNo;

    – 제약 사항 삭제 : ALTER TABLE Friend DROP CONSTRAINT unq_name;
    – 제약 사항 추가 : ALTER TABLE Friend ADD CONSTRAINT PK_FriendName PRIMARY KEY (Name);

시퀀스 사용하기
    – CREATE SEQUENCE FriendIDSeq INCREMENT BY 1 START WITH 1000;
    – 테이블에서 사용하기
    INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, ‘Mike’, ‘123’);
    – 시퀀즈 제거하기 : DROP SEQUENCE FriendIDSeq;
    – 현재 시퀀스 값 가져오기 : SELECT FriendIDSeq.CurrVal FROM DUAL;

자동 증가 사용하기
    – 오라클에서는 트리거를 이용해야 함.

    CREATE OR REPLACE TRIGGER AUTONUMBER
    BEFORE INSERT ON Friend
    FOR EACH ROW
    BEGIN
       SELECT FriendIDSeq .NEXTVAL
       INTO :NEW.FriendID FROM DUAL;
    END;
    /

    – 시퀀스에서 값을 가져온 다음 새로운 row 생성 될 때 주키에 값을 집어넣는 트리거인듯.

인덱스 사용하기
    – 인덱스를 사용하면 SELECT 문의 속도는 증가하지만, INSERT, DELELTE, UPDATE의 속도는 느려진다.
    – 조회가 자주 발생하는 테이블의 경우 인덱스를 사용하는 것이 단점에 비해 눈에띄게 좋다.
    – 따라서 WHERE 나 ORDER BY 절에서 자주 사용하는 컬럼의 인덱스를 만들어 두는 것이 좋다.
    – 제약사항(주키나 유니크) 때문에 자동으로 생성된 인덱스는 제약사항이 제거 되면 같이 제거 된다.
    – 인덱스에는 유니크 인덱스와 논 유니크 인덱스 두 종류가 있다.
    – 인덱스 만들기 : CREATE UNIQUE INDEX NameIndex ON Friend (Name);
    – 유니크 인덱스를 사용하는 컬럼에 중복되는 값을 넣을 수 없다.
    – 텍스트에 인덱스는 단어의 왼쪽 글자부터 오른쪽 글자순으로 찾는다. 따라서 ‘%기선’ 과 같은 검색어는 데이블의 모든 데이터를 찾아보게 한다.
    – 인덱스 삭제하기 : DROP INDEX NameIndex;

참조 무결성
    – 외례키 제약 설정하기

    CREATE TABLE Friend (FriendID INT PRIMARY KEY NOT NULL,
                      Name VARCHAR(50));

    CREATE TABLE Phone (
       PhoneID INT PRIMARY KEY NOT NULL,
       FriendID INT,
       PhoneNo VARCHAR(20),
       CONSTRAINT FID_FK FOREIGN KEY(FriendID)
          REFERENCES Friend(FriendID));

Chapter 11: Users and Security

참조 : The Programmer’s Guide to SQL

인증 : 이부분에 대한 SQL-99 표준은 없다. 따라서 DBMS마다 각기 다른 방법을 사용하지만 대부분 비슷하다.
권한 : SLQ-99 표준에 두 개의 키워드를 정의하고 있다. GRANT, REVOKE

사용자 계정 만들기
    CREATE USER username IDENTIFIED BY password;
ROLE 종류
    CONNECTION : DB에 연결 가능
    RESOURCE
    DBA
ROLE 또는 권한 주기
    GRANT 권한 TO username
ROLE 또는 권한 제거
    REVOKE 권한 FROM username
사용자 정의 ROLE 만들기
    CREATE ROLE rolename
ROLE 삭제하기
     DROP ROLE rolename
Statement 단위로 권한 부여하기
    GRANT <privilege type> ON <resource> TO <username>
Statement 단위로 권한 취소하기
    – REVOKE <privilege type> ON <resource> FROM <username>
    – Role을 사용하여 부여된 여러 권한 중에서 특정 권한만 취소할 경우에 유용하게 사용할 수 있음.
    – RESOURCE로 인해 부여받는 권한 중에 테이블이 디스크의 공간을 무한대로 사용할 수 있는(UNLIMITED TABLESPACE)데, 이 권한을 취소할 수 있음.