쿼리 작성 가이드 (QueryParser Library)

• CodeIgniter에서는 쿼리를 Model에서 PHP코드로 작성 할 수 있도록 제공한다. 그러나 PHP 상에서 쿼리를 작성 할 경우 SQL문의 가독성이 현저하게 떨어지며, 향후 DBA 혹은 기타 담당자가 쿼리 튜닝 진행을 할 수 없게 된다.
• 이에, KTK CRM에서는 QueryParser Library를 제작하여, 별도 xml을 통해 SQL을 관리하며, 제약적이나마 조건 문 / include 등의 기능을 제공하여 dynamic query 및 변수 바인딩을 지원한다.

• CI에 해당 라이브러리를 적용하기 위해서는 다음의 두 가지 작업이 필요하다
  1. /application/libraries 에 QueryParser.php 파일을 적용한다.
  2. /application/config/autoload.php 에서 다음과 같이 queryParser를 자동 로드하도록 설정한다.

// 기존 library load항목에 'queryParser' 추가
$autoload['libraries'] = array( ... , 'queryParser'); 

QueryParser는 CI의 Model Layer에서 호출하여 사용 할 수 있도록 설계되어 있다.

• /application/sql/ 에 user.xml 과 같이 XML형태의 파일로 저장하는 것이 원칙이다.
• 다만, Query Parser의 sql_sub_path 멤버변수 값을 변경하여 application하의 subpath를 조정 할 수 있다.(기본 “sql”)

XML문서는 기본적으로 다음과 같이 작성한다

QueryParser_용_XML_Lauout.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!--SQL 그룹 및 namespace 정의, 현재 QueryParser에서 namespace 는 활용하지 않음 -->
<mapper namespace="sql.product">
    <!-- 개별 SQL문 지정, id는 실제 프로그램에서 query를 불러 올 때 지정하는 key 값으로, 동일 XML파일 내에서 중복되지 않도록 주의가 필요하다 -->
    <sql id="get_price_change_history">
        ... query ...
    </sql>
</mapper>

• SQL문에서 변수는 #{page} 와 같은 형태로 지정하며, 실제 PHP 에서는 query('쿼리ID', array('page'⇒1)) 과 같은 형태로 호출하게 된다.

변수_바인딩_예제.xml

<?xml version="1.0" encoding="UTF-8" ?>
<mapper namespace="sql.product">
    <sql id="get_price_change_history">
        call xone_ktk_new.sp_s_sss1101_30_Search (
              #{company}
            , '30'
            , #{brand}
            , #{style}
            , ''
            , #{page}
            , #{rows}
        )
    </sql>
</mapper>

• 위와 같은 경우 #{company}, #{brand} 등에 실제 변수 값이 할당되며, 만약 해당 변수의 type이 String일 경우 자동으로 앞 뒤에 따옴표(')를 붙이게 된다. (만약 이미 앞뒤에 따옴표가 있을 경우 중복하여 붙이지는 않는다.)
• 쿼리문에 따른 바인딩 예제는 다음과 같다

변수할당.php

 $data['param1'] = '1234';
 $data['param2'] = 1234;
 $data['param3'] = '테스트1';
 $data['param4'] = '테스트2';
 $data['param5'] = null;

바인딩결과.sql

// 요청
CALL sp_1(#{param1}, #{param2}, #{param3}, '#{param4}', #{param5}, #{param6});
// 결과
CALL sp_1('1234', 1234, '테스트1', '테스트2', NULL, NULL);

• QueryParser는 현재 if 문을 통한 dynamic query를 지원한다. (단 else, else if 는 아직 구현되어 있지 않다)
• IF구문은 php와 용법이 동일하며(함수 또한 사용 가능), 변수를 $가 아닌 #{} 으로 표현한다는 점만 다르다.
• 사용 방식은 <if test=“검증할 내용”>성공시 표시될 쿼리</if> 이다.

if_구문_사용_예제.xml

<sql id="sample.1">
    SELECT *
      FROM table
     WHERE type = #{type}
     <if test="#{userId} != ''">
       AND userId = #{userId}
     </if>
     <if test="#{empNo} != ''">
       AND empNo = #{empNo}
     </if>
</sql>

해당_SQL_바인딩_예제_요청.php

$data['type'] = 'T001';
$data['empNo'] = 2017062201;
parse('sample.1', $data);

해당_SQL_바인딩_예제_결과.sql

    SELECT COUNT(1) AS cnt
      FROM TABLE
     WHERE TYPE = 'T001'
       AND empNo = 2017062201

• Include는 SQL구문에서 다른 query문을 재사용 할 수 있도록 하는 기능으로 코드 재사용을 지원한다.
• include할 대상 sql은 편의상 ref 태그로 선언하나 sql 태그 또한 include 할 수 있다.

include_구문_사용_예제.xml

<sql id="sample.2.list">
    SELECT *
      FROM table
     WHERE
       <include refid="sample.2.where" />
</sql>
<sql id="sample.2.count">
  SELECT count(1) AS cnt
    FROM (
      <include refid="sample.2.list" />
    ) t
</sql>
<ref id="sample.2.where">
    type = #{type}
    <if test="#{userId} != ''">
      AND userId = #{userId}
    </if>
    <if test="#{empNo} != ''">
      AND empNo = #{empNo}
    </if>
    // 부등호 등의 식은 CDATA 를 사용하여 태그로 인식되지 않도록 보호한다.
    <![CDATA[
        AND regdate >= #{regdate}
    ]]>
</ref>

sample.2.list_바인딩_예제_결과.sql

    SELECT *
      FROM TABLE
     WHERE TYPE = 'T001'
       AND empNo = 2017062201

sample.2.count_바인딩_예제_결과.sql

    SELECT COUNT(1) AS cnt
      FROM (
        SELECT *
          FROM TABLE
         WHERE TYPE = 'T001'
           AND empNo = 2017062201
      ) t

• 쿼리 로드는, 이미 작성된 SQL문(XML 파일)을 불러오기 위한 작업이다.
• 주로 Model의 생성자(construct()) 영역에서 해당 모델에 해당되는 XML을 부르게 되며,
• 각 Method에서 필요에 따라 다른 XML파일을 로드 할 수 있다.

constructer에서_해당Model용_공통_XML_로드.php

function __construct() {
    parent::__construct();
    // 생성자에서 product.xml을 호출하여 모든 method가 별도 선언 없이 XML 내 쿼리를 사용 할 수 있도록 함
    $this->queryparser->load("product");
}

Method에서_필요에_따라_별도_XML_로드.php

function someMethod() {
    $this->usersQuery = $this->queryparser->load("user");
}

• QueryParser 라이브러리에서는 최종 해석 / 바인딩 된 쿼리를 string객체로 리턴 해 주는 것이 본연의 목적이나, 사용자의 편의 및 코드 간결성을 위해 최종 쿼리 구문을 DB로 전송하여 resultset 을 반환 해 주는 기능 또한 제공 해 주고 있다.
• 세부 사항은 아래의 구현 예제 참고

Model에서 아래와 같이 KTK_Model 을 extends 할 경우에는 아래와 같이 간결하게 사용 할 수 있다.

쿼리_해석_및_DB호출_결과_수신_1.php

// parsing 할 데이터 임시 생성(예제용)
$data['page'] = 1;
$data['rows'] = 15;
 
// $data를 이용, get_price_change_history 분석 후 퀴리 실행
$query=$this->query("get_price_change_history", $param);
// 만약 대상 DB가 $this->db가 아닐 경우, 아래와 같이 manual하게 대상 DB를 선택 할 수 있음 (conf의 DB 이름 기준)
// $query=$this->query("get_price_change_history", $param, 'DB1');
$result['list'] = $query->result_array();

쿼리_해석_및_DB호출_결과_수신_2.php

// $data를 이용, get_price_change_history 분석 후 퀴리 실행 (대상 DB 명시 선택)
$query=$this->queryparser->query("get_price_change_history", $data, 'DB1');
$result['list'] = $query->result_array();

위에서 기술한 바와 같이 바로 DB호출이 아닌 해석된 Query문을 String으로 받을 수 있다.

쿼리를_텍스트_구문으로_받아_Manual하게_DB호출.php

// $data를 이용, 기존 로드된 XML의 id='get_price_change_history' SQL을 해석하여 바인딩
$query = $this->queryparser->parse('get_price_change_history', $data);
// 퀴리 실행하여 결과 바인딩
$query = $this->db->query($query);
$result['list'] = $query->result_array();