====== 쿼리 작성 가이드 (QueryParser Library) ======
===== 개요 =====
* CodeIgniter에서는 쿼리를 Model에서 PHP코드로 작성 할 수 있도록 제공한다. 그러나 PHP 상에서 쿼리를 작성 할 경우 SQL문의 가독성이 현저하게 떨어지며, 향후 DBA 혹은 기타 담당자가 쿼리 튜닝 진행을 할 수 없게 된다.
* 이에, KTK CRM에서는 QueryParser Library를 제작하여, 별도 xml을 통해 SQL을 관리하며, 제약적이나마 조건 문 / include 등의 기능을 제공하여 dynamic query 및 변수 바인딩을 지원한다.
===== 라이브러리 세팅 방법 (향후 업그레이드 대응) =====
* CI에 해당 라이브러리를 적용하기 위해서는 다음의 두 가지 작업이 필요하다
- /application/libraries 에 QueryParser.php 파일을 적용한다.
- /application/config/autoload.php 에서 다음과 같이 queryParser를 자동 로드하도록 설정한다.
// 기존 library load항목에 'queryParser' 추가
$autoload['libraries'] = array( ... , 'queryParser');
===== QueryParser 라이브러리 사용 법 =====
QueryParser는 CI의 Model Layer에서 호출하여 사용 할 수 있도록 설계되어 있다.
==== SQL문 작성 ====
=== SQL문 위치 ===
* /application/sql/ 에 user.xml 과 같이 XML형태의 파일로 저장하는 것이 원칙이다.
* 다만, Query Parser의 sql_sub_path 멤버변수 값을 변경하여 application하의 subpath를 조정 할 수 있다.(기본 "sql")
=== XML문서 layout ===
XML문서는 기본적으로 다음과 같이 작성한다
... query ...
=== 변수 바인딩 ===
* SQL문에서 변수는 #{page} 와 같은 형태로 지정하며, 실제 PHP 에서는 query('쿼리ID', array('page'=>1)) 과 같은 형태로 호출하게 된다.
call xone_ktk_new.sp_s_sss1101_30_Search (
#{company}
, '30'
, #{brand}
, #{style}
, ''
, #{page}
, #{rows}
)
* 위와 같은 경우 #{company}, #{brand} 등에 실제 변수 값이 할당되며, 만약 해당 변수의 type이 String일 경우 자동으로 앞 뒤에 따옴표(')를 붙이게 된다. (만약 이미 앞뒤에 따옴표가 있을 경우 중복하여 붙이지는 않는다.)
* 쿼리문에 따른 바인딩 예제는 다음과 같다
$data['param1'] = '1234';
$data['param2'] = 1234;
$data['param3'] = '테스트1';
$data['param4'] = '테스트2';
$data['param5'] = null;
// 요청
call sp_1(#{param1}, #{param2}, #{param3}, '#{param4}', #{param5}, #{param6});
// 결과
call sp_1('1234', 1234, '테스트1', '테스트2', null, null);
=== 동적 쿼리 작성 ===
== IF문 사용 ==
* QueryParser는 현재 if 문을 통한 dynamic query를 지원한다. (단 else, else if 는 아직 구현되어 있지 않다)
* IF구문은 php와 용법이 동일하며(함수 또한 사용 가능), 변수를 $가 아닌 #{} 으로 표현한다는 점만 다르다.
* 사용 방식은 성공시 표시될 쿼리 이다.
SELECT *
FROM table
WHERE type = #{type}
AND userId = #{userId}
AND empNo = #{empNo}
$data['type'] = 'T001';
$data['empNo'] = 2017062201;
parse('sample.1', $data);
SELECT count(1) as cnt
FROM table
WHERE type = 'T001'
AND empNo = 2017062201
== Include 사용 ==
* Include는 SQL구문에서 다른 query문을 재사용 할 수 있도록 하는 기능으로 코드 재사용을 지원한다.
* include할 대상 sql은 편의상 ref 태그로 선언하나 sql 태그 또한 include 할 수 있다.
SELECT *
FROM table
WHERE
SELECT count(1) AS cnt
FROM (
) t
[
type = #{type}
AND userId = #{userId}
AND empNo = #{empNo}
// 부등호 등의 식은 CDATA 를 사용하여 태그로 인식되지 않도록 보호한다.
= #{regdate}
]]>
]
SELECT *
FROM table
WHERE type = 'T001'
AND empNo = 2017062201
SELECT count(1) AS cnt
FROM (
SELECT *
FROM table
WHERE type = 'T001'
AND empNo = 2017062201
) t
==== 쿼리 로드(Load) ====
* 쿼리 로드는, 이미 작성된 SQL문(XML 파일)을 불러오기 위한 작업이다.
* 주로 Model의 생성자(construct()) 영역에서 해당 모델에 해당되는 XML을 부르게 되며,
* 각 Method에서 필요에 따라 다른 XML파일을 로드 할 수 있다.
function __construct() {
parent::__construct();
// 생성자에서 product.xml을 호출하여 모든 method가 별도 선언 없이 XML 내 쿼리를 사용 할 수 있도록 함
$this->queryparser->load("product");
}
function someMethod() {
$this->usersQuery = $this->queryparser->load("user");
}
==== 쿼리 호출 ====
* QueryParser 라이브러리에서는 최종 해석 / 바인딩 된 쿼리를 string객체로 리턴 해 주는 것이 본연의 목적이나, 사용자의 편의 및 코드 간결성을 위해 최종 쿼리 구문을 DB로 전송하여 resultset 을 반환 해 주는 기능 또한 제공 해 주고 있다.
* 세부 사항은 아래의 구현 예제 참고
=== KTK_Model 사용 시 ===
Model에서 아래와 같이 KTK_Model 을 extends 할 경우에는 아래와 같이 간결하게 사용 할 수 있다.
// 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();
=== KTK_Model 미 사용 시 ===
// $data를 이용, get_price_change_history 분석 후 퀴리 실행 (대상 DB 명시 선택)
$query=$this->queryparser->query("get_price_change_history", $data, 'DB1');
$result['list'] = $query->result_array();
=== 공통 ===
위에서 기술한 바와 같이 바로 DB호출이 아닌 해석된 Query문을 String으로 받을 수 있다.
// $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();