Temporary Tablespace Group은 잘 알려지지 않는 오라클 기능 중 하나로, Oracle 10g에 추가된 기능이다.


일반적으로 temporary tablespace는 유저에게 지정하여, 특정 유저는 단 하나의 temporary tablespace만을 사용할 수 있었으나, Temporary Tablespace Group은 유저에게 임시 테이블스페이스 그룹을 지정하여 동시에 여러개의 임시 테이블스페이스를 사용하여 정렬 등의 작업 효율을 향상시키는 기능이다.



특징

  • 하나 이상의 temporary tablespace 구성
  • temporary tablespace로만 구성됨.
  • 명시적으로 생성하지 않고, 첫번째 temporary tablespace 할당되면 암시적으로 그룹이 생성되고,
  • 마지막 temporary table space 그룹에서 제거되면 삭제됨.


장점

  • 데이터베이스 레벨에서 여러 개의 default temporary tablespace 지정할 있다.

  • 사용자는 동시에 여러 세션에서 여러 개의 temporary tablespace 사용할 있다.

  • 단일 SQL 조작으로 여러 개의 temporary tablespace 사용하여 정렬할 있다.


->  하나의 임시 파일에 비해 여러 파일에 I/O 분산시킴으로써 정렬, hash join 등의 작업에 상당한 성능 향상을 기대할 수 있음



추가사항

  • temporary tablespace group에 추가 있는  temporary tablespace 의 개수 제한은 없다.
  • 하나의  temporary tablespace 는 하나의  temporary tablespace group에만 속할 있다.

  • temporary tablespace group 모든 정렬 문제를 개선한다.


예제



  • 기존에 생성된 임시 테이블스페이스를 그룹에 할당(그룹 생성)

ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;


  • 새로운 임시 테이블스페이스를 생성하여 그룹에 추가

CREATE TEMPORARY TABLESPACE temp2

  TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M

  TABLESPACE GROUP temp_ts_group;


  • 할당된 임시 테이블스페이스그룹을 조회할 수 있는 dba_tablespace_groups 딕셔너리 

SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_TS_GROUP                  TEMP

TEMP_TS_GROUP                  TEMP2

2 rows selected.



그룹이 생성되면 테이블스페이스와 동일하게 그룹 자체를 유저나 디폴트 임시 테이블스페이스로 지정이 가능하다. 


  • 사용자에게 임시 테이블스페이스그룹을 할당

ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;


  • 디폴트 임시 테이블스페이스로 그룹을 할당

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;


  • 그룹에서 테이블스페이스 제거

ALTER TABLESPACE temp2 TABLESPACE GROUP '';


SELECT * FROM dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_TS_GROUP                  TEMP

1 row selected.




★★★ 참고자료


아래 사이트는 temporary tablespace group과 하나의 임시 테이블스페이스 파일을 사용했을때의 성능 비교 자료를 제공.

https://dbakevlar.com/2011/08/warp-speed-with-temp-tablespace-groups/


요약한다면... 

temporary tablespace group은 하나의 temporary tablespace 만을 사용할때 보다 reads : 66% 성능향상, writes - 29% 성능향상이 됨.


※ temporary tablespace group을 구성할때 각각의 temporary tablespace 파일 크기를 동일하게, 위치는 다르게  위치시키는게 I/O 분산 효과가 더 좋다.


이전 글 Real-Time SQL Monitoring - v$sql_plan_mointor 조회하기 ( http://overoid.tistory.com/40?category=357137 ) 에서 사용된 SQL의 경우. LONG SQL 수행 후 문제가 있어 중지하고, 재 수행했다면... 이전에 수행했던 실행계획이 겹쳐서 나오는 단점이 있다.

대신 쿼리가 심플하고 Version 2에 비해서 조회 성능이 더 우수하다. 


Version 1의 중복 이슈를 해결한 Ver2 - v$sql_plan_mointor 조회 쿼리.


WITH SQL_PLAN_INF AS (

    SELECT /*+ materialize ordered */

           SQL_ID, PLAN_LINE_ID, STATUS, PROCESS_NAME, PLAN_DEPTH, PLAN_OPERATION, PLAN_OPTIONS, PLAN_OBJECT_OWNER, PLAN_OBJECT_NAME, OUTPUT_ROWS, LAST_CHANGE_TIME, FIRST_CHANGE_TIME, SQL_EXEC_START

      FROM V$SQL_PLAN_MONITOR A

     WHERE SQL_ID IN (SELECT SQL_ID

                        FROM (SELECT SQL_ID, ROW_NUMBER() OVER(ORDER BY SQL_EXEC_START DESC) AS RN

                               FROM V$SESSION

                               WHERE MODULE = :module_name)

                       WHERE RN = 1

                      )

       AND STATUS = 'EXECUTING'

),

SQL_PLAN_INFO AS (

    SELECT /*+ materialize */* FROM (

        SELECT A.*, RANK() OVER(PARTITION BY SQL_ID ORDER BY SQL_EXEC_START DESC) AS RN

          FROM SQL_PLAN_INF A


      ) X

     WHERE RN = 1

)

SELECT  LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS, PLAN_OBJECT_OWNER,PLAN_OBJECT_NAME,  B.OUTPUT_ROWS,B.LAST_CHANGE_TIME, B.FIRST_CHANGE_TIME,

        ROUND(((B.LAST_CHANGE_TIME - B.FIRST_CHANGE_TIME)*24*60*60),2)  AS ELAPSED

  FROM SQL_PLAN_INFO A

     ,( SELECT PLAN_LINE_ID,SQL_ID,SUM(OUTPUT_ROWS) OUTPUT_ROWS,MAX(LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(FIRST_CHANGE_TIME) AS FIRST_CHANGE_TIME

          FROM SQL_PLAN_INFO

         WHERE 1=1

          AND STATUS = 'EXECUTING'

          AND PROCESS_NAME <> 'ora'

        GROUP BY PLAN_LINE_ID,SQL_ID

       ) B

 WHERE A.SQL_ID = B.SQL_ID

   AND A.PLAN_LINE_ID = B.PLAN_LINE_ID

   AND A.PROCESS_NAME = 'ora'

ORDER BY A.PLAN_LINE_ID



사용법 및 설명은 이전글(http://overoid.tistory.com/40?category=357137 )을 참고하자.



오래동안 수행되는 SQL이 얼마나 진행되었는지 알 수 있을까?

예상실행계획으로 확인된 실행계획 그대로 수행되고 있는지를 쿼리 수행중에 확인할 수 있을까?


수억에서 수십, 수백억 레코드를 조인하여 처리하는데.. 언제 끝날지 모른다면 정말로 답답하다. 

수분에서 수십, 수백분이 소요되는  SQL 대량 작업의 경우 잘못된 실행계획으로 쿼리가 실행되고 있다는 것을 실행 초반에 알 수 있다면, 

SQL을 중지하고 수정하여 재실행함으로써 소중한 시간을 아끼고 일찍 퇴근할 수도 있을것이다.  



다행히 오라클에서는 11g부터 Real-Time SQL Monitoring 기능을 제공한다.

물론 쿼리가 수행완료된 이후 dbms_sqltune.report_sql_monitor 로 레포팅을 뽑아 결과를 확인할 수 도 있지만, 

실행중에도 v$sql_mointor, v$sql_plan_mointor 에 값이 변경되므로, 이 뷰를 잘 조회하면 얼마나 실행되었는지? 어떤 실행계획으로 실행되고 있는지 쉽게 파악할 수 있다.



SELECT LPAD(' ', a.plan_DEPTH * 4 ,' ')||A.plan_OPERATION||' '||a.PLAN_OPTIONS plan_text, 

       PLAN_OBJECT_OWNER,PLAN_OBJECT_NAME,  

       B.OUTPUT_ROWS,B.LAST_CHANGE_TIME,  B.FIRST_CHANGE_TIME,

       ROUND(((B.LAST_CHANGE_TIME - B.FIRST_CHANGE_TIME)*24*60*60),2)  AS ELAPSED

FROM V$SQL_PLAN_MONITOR A,

    (

    SELECT PLAN_LINE_ID,SQL_ID,SUM(OUTPUT_ROWS) OUTPUT_ROWS,

           MAX(LAST_CHANGE_TIME) LAST_CHANGE_TIME, 

           MIN(FIRST_CHANGE_TIME) AS FIRST_CHANGE_TIME

     FROM V$SQL_PLAN_MONITOR a

     WHERE 1=1

       AND STATUS = 'EXECUTING'

       AND PROCESS_NAME <> 'ora'

    GROUP BY PLAN_LINE_ID,SQL_ID

    ) B

WHERE A.SQL_ID = B.SQL_ID

  AND A.PLAN_LINE_ID = B.PLAN_LINE_ID

  AND A.PROCESS_NAME = 'ora'

  AND A.SQL_ID IN (SELECT SQL_ID  FROM V$SESSION

                    WHERE MODULE = :module_name

                      AND STATUS = 'ACTIVE'

                   )

ORDER BY  A.PLAN_LINE_ID

;



:module_name에 원하는 모듈을 입력하거나, 해당 부분을 AND A.SQL_ID = :sql_id로 수정하여 sql_id를 직접 입력하면 된다.


sql_id는 어떻게 찾느냐고? 

v$sql을 조회해보자.

일반적으로는 SQL 작성시 SQL을 유일하게 구별할 수 있는 ID를 주석으로 기술한다. 

그 텍스트로 select sql_id from v$sql where sql_text like '%찾는문자열%'로 조회하면 sql_id를 알 수 있다.




아래 그림은 위 SQL 실행결과의 일부분이다. 

이쿼리를 2~3초에 한번씩 실행하면 실행계획을 확인할 수 있으며, 실행계획 순서에 따라서 OUTPUT_ROWS에 처리되고 있는 레코드 값이 변경되는 것을 확인할 수 있다. 그 부분이 현재 수행되고 있는 부분이다.

또한 보여주는 실행계획이 현재 수행중인 실행계획이므로, 쿼리 수행전 확인한 예상실행계획과 동일하게 실행되고 있는지 확인하면 된다.




Real-Time SQL Monitoring(v$sql_mointor, v$sql_plan_mointor)은 Oracle 11g에서 추가된 기능으로 아래 조건에서 모니터링 된다.


  • 실행시간이 5초 ( '_sqlmon_threshold' 히든 파라미터로 조정)이상 느린 쿼리의 실행이력
  • 병렬 쿼리의 실행 이력
  • monitor 힌트를 사용한 쿼리에 대한 추적



위 조건을 만족하는데도 모니터링 되지 않는 다면????,  v$sql_monitor에 나타나지 않는다면????



만일, 모니터링 하는 SQL 문장의 실행계획 라인수가 300 라인이 넘는 복잡한 쿼리는 모니터링에서 누락된다.


오라클 히든 파라미터 중 '_sqlmon_max_planlines'=300 으로 초기 설정되어 있다.

이 값을 늘리면 문제 해결!!!



SQL> alter system set "_sqlmon_max_planlines"=800 scope=both;




[참고]


1. 모니터링된 SQL은  dbms_sqltune.report_sql_monitor로 레포팅

    

select dbms_sqltune.report_sql_monitor(sql_id=>'bpfu171khfbg6') from dual



2. 오라클 히든 파라미터 조회 (Oracle Hidden Parameter View)


select a.ksppinm name, b.ksppstvl value,b.ksppstdf deflt,

decode (a.ksppity, 1, 'boolean',

                2, 'string',

                3, 'number',

                4, 'file',

                a.ksppity) type,

a.ksppdesc description

from sys.x$ksppi a, sys.x$ksppcv b

where a.indx = b.indx and a.ksppinm like '\_%' escape '\'

order by name



Toad TeamCoding part 1에 이어서...

이전 글을 못보신 분은 아래링크 클릭!!
2011/05/24 - [데이터베이스/Toad] - Toad TeamCoding - Integration with SubVersion - part1

4. Toad Team Configuration

자… 이제 필요한 SW는 모두 설치했으므로 Toad Team Coding을 위한 설정 작업만 하면 된다.

4-1. Toad Team configuration

Server-side Object Install 과정에서 생성한 TOAD 계정으로 로그인 한다. TOAD 계정은 Team Coding에 필요한 Role을 모두 가지고 있기 때문이다.

상단 툴바에서 우측 마우스 클릭 > Team Coding 을 선택해 관련 툴바를 활성화 시킨다. 물론 Menu > Utils > Team Coding > 하위 메뉴를 이용해도 동일하다.



툴바 중 체크 아이콘인 “View Team Coding Status with this Session” 버튼을 클릭한 후 나타난 화면에서 Setting 버튼을 누른다.



위 화면처럼 체크하여 Team Coding 과 버전컨트롤 기능을 활성화 한다. 버전 컨트롤 Provider로 Subversion을 선택한 후 하단의 Provider Options 버튼을 클릭한다



Subversion 설정화면에서 SVN Executable 항목에는 이전 화면에서 설치한 SlikSvn 설치폴더의 svn.exe를 선택한다.



Server 설정탭에서는 SVN 서버에 대한 정보(서버명만)를 입력한다. 별도의 서버가 있다면 그 서버에 대한 정보를 입력하고, 나는 위에서 로컬에 설치한 Visual SVN 서버를 사용할 것이므로 나의 로컬 컴퓨터 명을 서버항목에 입력하였다.



옵션탭에서는 기본설정을 그대로 사용해도 무방할 듯 하다. OK>

4.2. local repository setting & init sync

실제 소스코드가 저장될 로컬 디렉토리를 생성한다. 그리고 해당 디렉토리와 svn 레파지토리와 한번은 싱크를 맞춰야 하므로 check out 한다.

먼저, 로컬에 프로젝트 소스를 저장할 디렉토리를 생성한다. 예제: C:\project_ws

해당 디렉토리를 선택한 후 우측 마우스 클릭 > SVN CheckOut… 클릭

(* 필자의 경우에는 위에서 Tortoisesvn을 설치했기 때문에 탐색기와 SVN이 통합되어 있다. 다른 툴을 사용한다면 여하튼 svn repository와 최초 한번 Checkout을 해야 한다는 것만 잊지말자)


4.3 Subversion Login

이제 마지막 작업만 남았다. Toad에서 Team Coding 툴바 중 “Log on To VCS Provider” 아이콘은 선택하자.



 

로그인 창에서 Repositorys는 그림처럼 입력한다. VCS Provider 설정화면에서 서버명까지 입력했기에, Repository의 나머지 이름을 입력한다. 프로젝트명은 빼고 레파지토리명까지만 입력한다. Working Directory 항목에는 좀전에 생성하고 Checkout한 로컬 폴더 경로를 입력한다.

유저명과 패스워드는 SVN Server에 생성한 계정정보를 입력한 후 OK 버튼을 누르면 Toad에서 SVN 서버로 로그인한다.


 


로그인이 정상적인지 확인하려면 Team Coding Toolbar 중 “View Team Coding Status for this session” 툴바를 클릭해서 위 그림처럼 모든게 녹색 체크이면 정상인 것이다.

4.4. Configure Code Control Groups

이제 정말 마지막 작업들만 남았다. Toad Team Coding Toolbar에서 “View/Configure Code Control Groups” 버튼을 클릭한다.

Code Control Group에서는 버전관리나 Team Coding할 Oracle Object와 Subversion Project와 매핑을 해준다.

Code Control Group 팝업창에서 첫번째 아이콘인 “Add Group” 아이콘을 클릭한다.


하단의 VCS Project를 우리가 생성한 SVN 프로젝트를 선택하고 Group name 항목은 프로젝트명과 동일하게 입력한다. (사실 아무거나 입력해도 상관없다.)


추가한 Code Control Group에서 관리할 Oracle Object를 지정하는 화면이 실행된다. 관리할 스키마 등의 정보를 추가 하자.


끝으로 이미 Code Control Group 화면에서 관리하고자 지정했던 스키마가 가진 PL/SQL Object를 VCS Provider 및 Team Coding 환경에 Export 하면 Toad, File, SVN 모두 싱크가 맞아진다.

Code Control Groups 화면에서 Group 명을 선택한 후 “Export to VCS” 버튼을 클릭한다.



클릭하면 미리 매핑한 스키마가 소유한 package, function, trigger, procedure, type 등의 object 목록이 나타나며 OK 버튼을 누르면 아래 그림처럼 SVN에 Commit할 때 버전을 어떻게 처리할 것인지 묻는 대화상자가 나온 후 동기화 작업이 진행된다.



두번째 옵션을 선택하자.



해당 Oracle Object가 정상적으로 Subversion에 추가되었다는 메시지가 출력된다.

모든 설정 작업은 끝났다.

5. Test & Others View

5.1. Version Control Browser

Version Control Browser는 Subversion에 저장된 파일(Oracle Object 스크립트) 목록을 조회하고, 버전 히스토리를 볼 수 있으며 해당 파일을 체크아웃/체크인 할 수 있는 일종의 VCS Provider Client이다.


아래 버전별 소스 비교 화면은 위의 “Version Control Browser” 화면의 우측 하단의 History 목록에서 우측마우스 클릭> “View Differences” 메뉴 선택 후 나타난 비교화면이다. Compare 기능도 다양하고 아주 쓸만하다.


5.2. Team Coding Options

Menu > View > Toad Options 항목 중 Source Control 부분은 Team Coding에 대한 몇 가지 설정을 할 수 있다.


5.3. Team Coding Viewer

Team Coding Viewer는 버전관리하는 oracle object에 대한 status를 조회하는 화면이다. Team Coding Viewer는 위 화면의 Version Control Browser 보다 훨씬 로딩 속도가 빠르다.

 

5.4. Source 편집.

5.4.1. 체크아웃

스키마 브라우저나 에디터에 열리는 스크립트를 편집하려고 시도해 보라. Code Control Group에 지정된 Object는 편집이 안된다. 편집을 하기 위해서는 Team Coding Toolbar에서 Check Out 버튼을 클릭해야한다. 체크아웃하면 토드는 SVN에서 소스를 가져와 파일에 싱크를 맞춘후 에디터에 로드한다.

만일 신규 스크립트의 경우에는 스크립트를 작성 후 컴파일하여 Oracle에 반영한 후 체크아웃 하면 토드는 신규이므로 SVN에 먼저 Commit하여 저장한 후 로컬레파지토리 경로로 파일을 생성한다.

체크아웃을 하면 SVN과는 달리 Toad는 Oracle 객체를 Lock을 한다. 다른 사람은 Lock한 사람이 체크인 하지 않는 한 소스를 편집할 수 없다.

5.4.2. 체크인

소스 개발 및 수정이 끝났으면 컴파일하여 Oracle에 반영하고 체크인 버튼을 눌러 SVN에 Commit 작업이 이루어 지도록 한다. 체크인시에도 토드는 로컬 레파지토리의 파일을 먼저 싱크하고, svn에 commit 하게 된다. 체크인 된 소스는 다른 사람이 편집할 수가 있다.

 


Conclusion

Toad Team Coding을 이용 하기 위해서 이렇게 많은 작업을 해야 한다니 힘이 들긴 한다.

구성이 복잡한 것 외에 또 다른 단점을 꼽으라면 체크아웃/체크인시에 생각보다 Load가 많이 발생한다. 파일과 SVN, Toad Repository까지 모두 싱크 작업을 하려니 시간이 조금 걸릴 듯 싶기는 하지만.. 그래도 빠르게 팍팍 소스가 로드되던 팀 환경 없는 방식과 비교하면 느린 것은 틀림이 없다.

하지만, PL/SQL 소스 관리는 정말 편리하다. SVN의 모든 잇점을 모두 다 누릴 수는 없지만 핵심적인 기능은 전부 이용가능하며, Toad Team Coding이 주는 혜택까지.. 반드시 사용해 볼만한 기능임에는 틀림없다.




* subversion이 아닌 cvs와 연동시 command-line tool 지정시 cvs.exe는 나만 그런지는 모르겠지만 cvs 서버(유닉스 환경)에 접속이 제대로 되지 않았다. cvsnt를 설치하여 cvsnt 폴더에 있는 cvs.exe를 지정하니 cvs 서버 접속이 정상적으로 잘 되었다. 이것을 몰라 한참 고생했던거라  cvs와 연동하실 분은 먼저 cvs.exe로 접속해보고, 잘 안되면 바로 cvsnt로 갈아타시길..


현대 프로그램 개발환경에서 CVS, SVN 같은 버전관리 툴은 필수 툴이 되어 버렸다. 그러나 DB 개발언어인 Oracle의 PL/SQL 언어로 함수나 패키지, 프로시저 등을 개발할 때 버전관리 툴을 사용할까? 대부분 잘 사용하지 않는다.

사용하지 않는 원인을 보면 거의 모든 버전관리시스템이 파일기준으로 동작을 하는데 비해 PL/SQL 개발은 따로 파일에 저장을 하지 않고 DB에 바로 저장을 하기 때문일 것이다. 물론 부지런한 개발자는 항상 PL/SQL 소스를 로컬 파일에 저장해 두고, 로컬 파일 정보를 별도의 버전관리 시스템의 클라이언트를 이용해서 버전관리를 할 수는 있을 것이다. 그렇지만, 그게 얼마나 귀찮은  일인가? 개발할 때 마다 수많은 PL/SQL 코드를 매번 파일로 저장하고, 별도로 COMMIT을 해야 한다면? 혹시라도 단 하나의 파일이라도 COMMIT해야 하는걸 잊어버린다면?
이런 귀찮은 작업을 DB 클라이언트 툴이 다 해준다면 쉽게 PL/SQL 같은 프로그램도 버전관리를 할 수 있을 텐데.. 그런 툴이 없다 보니 대부분의 DBA나 DB 개발자들은 버전관리를 잘하지 않는것이다.

그런데, 그런 기능을 지원해주는 툴이 예전부터 있었다.
“Toad for Oracle” 대부분의 Oracle 개발자들이 알고 있는 두꺼비. 그러나 이런 기능이 있는지 알고 있는 사람은 정말 몇 안되며 이 Team Coding 기능을 필드에 적용하는 곳은 더더욱 적을 것으로 생각된다.

Toad에서 Team Coding 기능을 활성화하고, 서드파티 버전관리 시스템(CVS, SVN 등)과 연동한 후 Toad의 스키마 브라우저에서 Oracle에 저장된 프로그램을 편집하려고 에디터로 오픈하면 (정확히는 수정을 위해 소스를 Toad에서 체크아웃하면) Toad는 버전관리시스템에서 최신의 소스를 가져와 로컬 파일 정보도 수정 반영한 후 에디터에 로드한다. 그리고 소스 내용을 Toad에서 수정한 후 컴파일하여 Oracle에 반영한 다음 소스를 체크인하면 Toad는 자동으로 로컬파일을 수정하고 수정된 내용을 버전관리시스템에 COMMIT 한다. 버전관리시스템과 연동이 얼마나 편리한가? 별도로 파일저장 버튼 한번 클릭하지 않고 Toad에서 체크인과 체크아웃만 하면 된다. (Toad는 두 명 이상이 동시에 PL/SQL 소스를 수정하지 못하게 하도록 Team Coding 기능 lock을 사용하여 막는다. 체크아웃 한 사람만 수정이 가능하다)

Toad Team Coding Overview

Toad의 초기 제품은 Microsoft의 Microsoft SCC API를 지원하는 툴만 버전관리를 지원 했었다.
그 후 Microsoft SCC (Source Code Control)API로 CVS와 SVN을 지원하는 Component가 나와  CVS나 SVN과 연동은 가능하도록 변경되었다.

Toad v10.5에 이르러 CVS와 SVN의 경우 별도의 SCC 지원 컴포넌트 없이 command-line client만 설치하면 쉽게 연동되도록 코드가 개선되었으며, 이전보다는 쉽게 형상관리 툴과 연동을 할 수 있게 되었다.

Toad v10.6 현재 Direct Supported되는 버전관리 툴은 다음과 같다.

 Version Control Provider  Version
 Concurrent Versions System (CVS)*  CVS 1.11.9 and later
CVSNT 2.08 and later
 Microsoft Team Foundation Server  2005, 2008, and 2010
 Microsoft Visual SourceSafe  5.0, 6.0, and 2005 (8.0)
 Perforce*  2009.2 (command-line client)
 PVCS  Note: PVCS 6.6.1 and 6.8.0 are specifically not supported with Team Coding.
 Subversion*  1.6.5 and later

* 표기가 있는 버전 컨트롤러는 command-line client를 이용하여 접속함.

이 목록에 없는 제품이라 하더라도 Microsoft SCC API를 지원한다면 Toad를 통해 버전관리를 할 수 있다.

Toad에서는 Team Coding 전략을 아래 표에 나와있는 세가지 방식 중 하나를 선택해서 사용할 수 있다. Toad의 팀 코딩 기능과 서드 파티 버전관리 시스템 연동의 조합에 따라 달라지며, 그에 따라 Setup 자체가 다르므로 개발팀에게 가장 잘 맞는 방식으로 설정하는 것이 좋을 것이다

 소스 컨트롤 방식 장점   단점
 Toad 팀 코딩 (X)

서드파티 소스컨트롤 (O)

버전 히스토리 관리 가능

추가적인 database object 설치가 필요 없음.
Editor와 Project Manager에서만 사용이 가능

Database Object를 lock 할 수 없음.
 Toad 팀 코딩 (O)

서드파티 소스컨트롤 (X)

Check out을 통해 database object를 lock 할 수 있음.

Code Control Group을 통해 lock 관리해야 될 객체를 지정할 수 있음.

Toad내의 database object가 열리는 어떤 화면에서도 사용가능.
Team Coding을 위해 추가적인 Object를 설치해야 함.

버전 히스토리 관리가 불가능.
 
Toad 팀 코딩 (O)

서드파티 소스컨트롤 (O)
 위 두 방식의 장점을 모두 가짐.  설치 및 설정이 가장 복잡함.

*Toad Team Coding : 팀 코딩은 Oracle Object를 lock/unlock 하여 의도하지 않는 변경을 예방할 수 있는 기능. 이 기능을 위해 Toad Server-side Object(테이블, 패키지 등)을 설치해야 한다.

*서드파티 소스컨트롤 : svn, cvs, Team Foundation Server 같은 서드파티 버전 컨트롤 시스템을 이용하여 일반적인 소스의 버전 관리 기능을 이용할 수 있다.


Toad Team Coding & Version Control Integration Setting

Toad에서 Team Coding과 서드파티 소스컨트롤을 모두 이용하기 위해서는 아래와 같이 크게 4가지 단계에 따라 설치 및 설정을 해야 한다.

1. Toad Server Side Object Install
2. SVN Server Install & Setting
3. SVN Command Client Install
4. Toad Team Configuration

Team Coding을 위해 좀 많은 설치 및 설정을 해야 하는 게 귀찮기는 하다. 하지만, 투입되는 비용과 노력에 비해 얻는 효과가 크다면 한 번 해 볼만 하지 않을까?

1. Toad Server Side Object Install

Toad Team Coding을 이용하기 위해서는 Oracle에 Toad 계정과 몇 개의 테이블 및 관련된 Object  등이 생성되어야 한다. 이 것을 해주는 것이 Toad Server-side Objects Wizard다. Oracle 서버에 테이블 및 Object를 설치함으로써 Team Coding 뿐만 아니라 보안관리, 튜닝 등 Toad의 고급 기능을 활용할 수 있으므로 Team Coding과 상관없이 구성하는 것도 좋다.

Server Side Objects Wizard 실행 시 일반적으로 System 계정으로 로그인하여 실행해야 한다. System 계정이 꼭 필요한 것은 아니며 꼭 필요한 몇 개의 권한만 가지고 있다면 어떤 계정으로도 실행해도 상관없다.

메뉴 > Database > Administrator > Server Side Objects Wizard 클릭

첫번째 항목을 선택. Next >

기존에 이미 TOAD 스키마가 있는 경우 업그레이드하며, 신규 설치도 할 수 있는 첫번째 옵션을 선택. NEXT>

TOAD 계정이 없다고 생성을 한다는 메시지가 나온다. OK>



TOAD 스키마에 생성할 기능들을 선택하는 화면이다. Team Coding을 위해서는 Team Coding 항목만 선택하면 되지만, 추후 Toad의 다양한 기능을 경험하기 위해서 전부 다 선택하도록 하겠다. Next >



TOAD Space Manager 기능을 사용하기 위해서 권한이 필요하다는 메시지가 출력된다. 그림처럼 선택 후 Next >

 

Toad Profiler 기능을 사용하기 위해서 몇몇 권한이 필요하다고 메시지가 출력됨. 그림처럼 선택 후 Next >



이 화면에서는 TOAD 계정의 패스워드와 테이블스페이스를 지정해야 한다 Next >

테이블 스페이스가 생성되지 않았거나 Toad 전용으로 테이블 스페이스를 하나 생성해야 한다면, 현 화면을 그대로 둔 채 SQL Editor를 열어 아래 그램처럼 간단한 테이블 스페이스를 생성하면 된다.
* 데이터파일 경로 및 테이블 스페이스 명은 각자의 시스템에 맞게 수정해야 한다.


 


Toad Team Coding을 위한 Role 생성 화면. 신규 생성이니 전부 Create New를 선택한 후 Next >



Toad Server Side Objects 나머지 기능 설치를 위한 테이블 스페이스 지정화면. 전부 이전 화면에서 생성한 테이블스페이스 “TOAD_DS”를 선택. Next >



앞에서 설정한 모든 것들이 스크립트로 만들어 졌다.. Run Script 버튼을 누르면 바로 실행이 된다.
Run Script >

에러 없이 스크립트가 실행되었다. 확인>



스크립트가 실행될 때 생성된 메시지가 출력된다. 성공적으로 설치되었으니 그냥 Next >


Toad Security Administrator를 지정하는 화면. 일단 이 화면은 Next >
추후 필요할 때 설정하도록 하겠다.


Setup 이 끝났다.



현 상태에서 TOAD 계정으로 로그인하여 Menu > Utilities > Team Coding >Team Coding Status 선택 시 나타난 화면이다. Team Coding에 대한 필요사항이 Install 되었다고 나오며, VCS(Version Control System)에 대한 정보가 설치되지 않았다고 보여주고 있다.

화면이 많아서 복잡한 듯 보이지만 실제는 TOAD 계정 생성하고 몇몇 테이블 및 패키지 설치하고, TOAD 계정에 몇몇 권한만 할당해주는 단순 작업이다.

2. SVN Server Install & Setting

이제 Team Coding 을 위한 서브버전 서버를 설치한다. 로컬이든 네트워크 환경이든 상관없다. 필자는 로컬 환경에 Subversion Server를 설치 할 것이다.

윈도우용 Subversion Server로는 가장 사용이 편리한 VisualSVN Server를 설치할 것이다. 서브버전 서버로 다른 어떤 툴을 사용해도 무방하다.

http://www.visualsvn.com/server/download/ 이곳에서 VisualSVN Server를 다운받아서 설치한다.

2-1 SVN Server Install


VisualSVN Server와 관리콘솔 모두 설치를 선택한 후 Next>


설치위치, SVN Server과 관리할 Repository 위치, 접근 포트를 설정한다. 인증은 서브버전 인증을 사용하도록 설정한다. Next 하시면 설치 완료..

2-2. SVN Server Setting

먼저 SVN에 접속할 유저 계정을 하나 생성한다.
좌측 트리에서 Users를 우측마우스 클릭 > Create Users.. 선택. 계정명과 패스워드를 입력한 후 OK 버튼 클릭.


다음은 그룹 생성 단계.

유저 생성과 동일한 방식으로 Groups를 우측 마우스 클릭한 후 Create Group.. 선택. 그룹명을 입력한 후 멤버를 Add 버튼을 클릭하여 생성한 계정을 멤버로 등록한다.


이제 VisualSVN 내에서 Repository를 생성해야 한다.
VisualSVN 관리콘솔에서 좌측메뉴의 Repositories 우측 마우스 클릭 > Create New Repository.. 선택.


Repository 명을 입력합니다.
Create default Structure를 선택하시면 Oracle 폴더 하위에 trunk, branches, tags같은 서브폴더가 생성된다.

일반적으로 SVN의 레파지토리 구성은 레파지토리 > 프로젝트 > trunk/branches/tags 같은 방식으로 구성을 하지만 Toad는 프로젝트 하위에 디렉토리를(예: trunk 등) 만들어서 해당 디렉토리 레벨을 Check Out하면 제대로 인식하지 못하는 문제가 있다. 
그러므로 레파지토리나 하위 프로젝트 생성시 “Create default structure” 체크를 반드시 끄고 만든다.


프로젝트 생성:
생성된 레파지토리명(Oracle) 우측 클릭 > 새로 만들기 > Project Structure를 클릭.


프로젝트명을 입력하고 OK버튼을 클릭.

이렇게 프로젝트를 생성하면 프로젝트 폴더 하위에 trunk, branches, tags 세 폴더가 생성된다.
위에서 언급했듯이 이렇게 폴더가 있더라도 Toad에서 제대로 처리하지 못하므로 VisualSVN Sever의 좌측 트리에서 각각의 trunk, branches, tags 폴더를 삭제한다.
물론, 삭제하지 않아도 사용상의 문제는 전혀 없다.

3. SVN Command Client Install 

3-1. SVN Command Client Install (sliksvn)

Toad는 command-line subversion client tool이 반드시 필요하다. SVN command tool이 여러가지 있지만 필자는 그 중 silksvn를 설치하여 팀개발 환경을 구성할 것이다.

http://www.sliksvn.com/en/download 에서 자신의 컴퓨터 환경(x32, x64)에 맞는 sw를 다운받아서 설치하면 된다. 설치는 간단하다.

Choose Setup Type에서 Custom을 선택한다.


 


 

Subversion Client를 제외하고는 다 설치하지 않도록 설정을 바꾼 다음 Next를 눌러 설치를 진행한다. Team Coding 환경 구성 시 Toad svn.exe만 필요로 한다.

그리고 sliksvn command-line tool이므로 조작이 불편해 추가적으로 GUI Client tool tortoisesvn을 설치할 것이므로 다른 설치 옵션은 모두 빼고 설치한다. 물론 별도의 .GUI 클라이언트를 설치하지 않고 sliksvn만으로 사용한다면 좀 더 많은 옵션으로 설치하는 게 좋을 것이다.

 

끝으로 GUI 환경의 subversion client도 하나 설치한다. 여기서는 가장 많이 사용하는 tortoisesvn 을 설치한다. 별도의 svn client 는 최초 checkout 할 때만 사용하므로 tortoisesvn 설치여부는 각자 판단해서 설치하면 된다.

 

http://tortoisesvn.net/downloads.html 이곳에서 버전에 맞는 설치파일을 다운받아서 설치하시면 된다. 워낙 단순한 install이라서 별도의 설명은 생략하도록 하겠다.


한참을 달렸지만, 아직 중요한 Toad에서의 Team 환경 및 SVN 연동을 위한 설정 부분이 남았다. 이 부분은 part2 에서 살펴보도록 하겠다.

 


비트컬럼으로 속성레벨의 데이터 통합

이번 포스트에서는 비트값으로 구성된 컬럼으로 컬럼 수준의 데이터 통합에 대한 가이드 및 비트 컬럼을 SQL로 조작하는 방안에 대해 살펴볼까 합니다.

먼저, 일반적인 샘플 ERD를 하나 보겠습니다.


좌측은 Logical 엔터티이며 우측은 Physical 테이블 설계안 입니다.

지극히 간단한 테이블입니다. 웹 화면을 구성하는 메뉴가 있고, 메뉴의 속성으로는 ID, 명, 그리고 화면을 제어하는 4개의 여부 컬럼이 있습니다. 간단하지만 전혀 문제가 없습니다. 요구사항도 명확히 반영했다고 한다면 정말 문제가 없는 모델링 입니다.

프로젝트팀이 프로젝트를 완료하고 난 후 운영팀에서 운영중에 새로운 고객 요구사항들이 나오기시작합니다. 메뉴ID를 화면에 무조건 보여주지 말고 사용자가 보여줄지 말지를 선택하게 해달라는 군요. 그러면 컬럼을 추가하면 됩니다 “메뉴ID디스플레이여부” 란 컬럼이 추가됩니다.

또 다른 고객이 이와 유사한 요구를 해 옵니다.
얼마 지나지 않아 “~여부” 컬럼만 6개나 추가되었군요. 그래도 컬럼만 추가하고 프로그램 코드를 수정하면 요구사항을 반영하는 데에는 큰 문제는 없습니다.

하지만 일 하기 싫은 DBA가 고민합니다.(바로 저 입니다.)
“이런 식의 고객 요구사항이 들어와도 컬럼 수정, 테이블 수정 없이 처리할 순 없을까?”

방법은 아래 그림과 같이 모든 여부 컬럼을 비트값의 합으로 표현하여 하나의 컬럼으로 통합해버리면 됩니다.


4개의 여부 컬럼을 “디스플레이비트값” NUMBER 하나의 컬럼으로 통합했습니다. 기존 모델의 각각의 컬럼은 2진수로 표현했을 때 하나의 비트값에 대응됩니다.

예를 들어
“배너디스플레이여부” 컬럼은 2^0 = 1로 설정하면 되며,
“메뉴이미지사용여부” 컬럼은 2^1 = 2,
“컨텐츠표시여부” 2^2 = 4,
“Hybrid메뉴여부” 2^3 = 8 이 됩니다.

그리고 실제 디스플레이비트값은 각각의 값을 BITWISE OR 연산을 하여 저장을 하게 됩니다.

예를 들어
“배너디스플레이여부” = “Y” ,
“메뉴이미지사용여부”= “N”,
“컨텐츠표시여부” = “Y”,
“Hybrid메뉴여부” = “Y” 값을 갖는 다면 실제 디스플레이비트값 컬럼에 저장되는 값은
1(2^0) + 4(2^2) + 8(2^3) = 13이 됩니다.

각각의 통합전 컬럼에 해당 되는 비트값을 부여할 때 2의 승수로만 부여하기 때문에 실제 bitwise or 연산의 결과나 단순 더하기 결과나 결과는 동일해 집니다.

0001(1) + 0010(2) + 1000(8) = 1011(13)

이 모델의 장점은 고객의 새로운 요구사항이 있을 때 컬럼의 추가 없이 프로그램만 수정하면 됩니다. 좀 있어보이는 말로는 “시스템의 유연성을 높이는 설계 방법” 이라고 하죠. 쉽게 말해 DBA가 편해지는 군요. 제가 바라는 바입니다.

제가 운영하는 시스템의 사례를 얘기할까 합니다.

제가 운영하는 시스템은 마케팅 사이트로 Global 시스템이지만 국내 한 서버에서 전세계 사이트를 모두 서비스 합니다. 시스템 사양에 비해서 Request가 많기 때문에 서버는 항상 바쁩니다. 글로벌 사이트라서 고객은 일년의 단 10분도 시스템이 정지되는걸 원하지 않습니다.

그럼에도 불구하고 위의 사례처럼 자잘 하지만 “~여부” 같은 컬럼을 추가해야만 처리될 수 있는 요구를 합니다. 컬럼 추가하고 개발하면 되겠죠. 하지만 문제가 있습니다. 대부분의 테이블은 문제가 없지만 시스템의 Main Entity인 2~3개의 테이블은 거의 모든 SQL이 집중되고, 수많은 PL/SQL이 참조하고 있어서 컬럼을 추가하는 순간 library cache pin 및 lock 등의 waiting이 걸리면서 오라클이 처리를 빨리 하지 못하게 되며, WAS에서는 DB 요청에 대한 리턴을 빠르게 받지 못하게 되니 계속계속 새로운 요청마다 새로운 Oracle Connection을 맺다가 Connection Pool Max size를 넘어서게 될 때 시스템이 거의 죽어버린답니다.

이런 상황에서는 컬럼 추가 작업 자체가 online에서 쉽게 alter table 명령어로 처리할 수 없으며, 컬럼을 추가하기 위해서는 수많은 결재와 보고를 해야 하고 휴일에 특정 시간대에 시스템을 내리고 컬럼 추가하고 다시 시스템을 기동하는 식으로 처리를 합니다.

어휴~ 컬럼 추가 작업이 장난이 아닙니다. 그러니, 제가 컬럼 추가 안하고 설계할 수 있는 방안을 고민하지 않을 수 없습니다.

하지만 이 모델의 단점도 만만치 않습니다.
단점은 첫째, 가독성이 떨어집니다. DA#으로 모델링을 하면 통합전 컬럼의 비트값을 서브타입으로 표현할 수 있어서 그나마 낫지만 Erwin 같은 툴을 쓰면 속성설명 정도에만 표현할 수 밖에 없어 모델의 가독성이 떨어져 문제가 됩니다.
두번째 단점은 SQL이 복잡해 집니다. 초급 개발자들은 설명을 해줘도 잘 이해를 못하더군요.

간단하지만 위 두 모델에 대해 무엇이 좋고 나쁘다라고는 말할 수 없을 것 같습니다. 다만, 비트값으로 속성을 표현하여 통합할 수 있는 방법도 있다라는 것을 알고, 시스템의 상황에 맞게 가장 적합한 방법을 사용하시면 됩니다. 저 같은 경우는 위의 컬럼 추가가 문제가 되는 테이블, 그리고 자주 업무 요건이 변경되는 테이블은 비트값으로 컬럼을 통합하는 방식을 많이 사용하며, 그렇지 않는 테이블은 사용하지 않습니다.

비트연산 기초

비트값 형식으로 속성을 모델링 했을 때 화면 UI와 이 UI를 처리하는 DML에 대해 살펴보도록 하겠습니다. 그전에 기초로 돌아가서 비트 연산에 대해 알아보겠습니다. 요즘 개발자 분들이 처음부터 웹 개발을 하신 분들은 비트 연산을 할 일이 없어서인지 비트연산을 헷갈려 하더군요.

비트 연산(Bitwise operation)은 한 개 혹은 두 개의 이진수에 대해 비트 단위로 적용되는 연산입니다.

NOT : NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산입니다.

NOT 0111
= 1000

OR : OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0으로 계산됩니다.

0101
OR 0011
= 0111

XOR : XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1으로 계산 합니다.

0101
XOR 0011
= 0110

AND : AND 연산은 두 값의 각 자릿수를 비교해, 두 값 모두에 1이 있을 때에만 1을, 나머지 경우에는 0으로 계산합니다

0101
AND 0011
= 0001


예전에 공부했던 게 기억이 나십니까?

비트속성 컬럼값 DML 가이드

화면 UI에서 비트컬럼값을 처리하는 DML 가이드입니다.



다음과 같은 화면이 있다고 가정합니다. 위 메뉴 테이블을 관리하는 화면입니다.

각 비트값에 해당되는 항목마다 라디오 버튼을 가진 하나의 항목으로 UI는 구성될 것입니다. 물론 다른 방식으로도 가능하겠지만요. 일단 이렇게 된다고 가정합니다.

이 화면의 특징은 한 화면에서 비트컬럼값이 사용하는 모든 비트값을 다 관리를 한다는게 특징입니다.

이런 케이스에 대해서는 처리 로직은 간단합니다.

먼저, 화면을 구성하기 위한 SELECT 문을 보겠습니다.

SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,'Y','N') AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,'Y','N') AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,'Y','N') AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,'Y','N') AS "Hybrid메뉴여부"
FROM MENU2;

오라클에서 제공하는 BITAND 함수를 가지고 해당비트값이 1인지를 판단할 수 있습니다.

이 경우 값을 INSERT, UPDATE 할 때는 각각의 항목에 대한 값(Y/N)을 판단해서 아래와 같이 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = DECODE('배너디스플레이여부','Y',1,0)
                                + DECODE('메뉴이미지사용여부','Y',2,0)
                                + DECODE('컨텐츠표시여부','Y',4,0)
                                + DECODE('Hybrid메뉴여부','Y',8,0)
 WHERE MENU_ID = :MENU_ID;

하지만, 위의 경우처럼 Y/N으로 INPUT TYPE=”RADIO”의 값을 구성하는 것 보다는 VALUE로 각각의 컬럼이 갖는 비트값으로 바로 지정하는게 효율적일 수 있습니다. 웹으로 구성한다면 value >0 면 checked를 지정하면 될 것 같군요.


SELECT MENU_ID
            ,MENU_NM
            ,DECODE(BITAND(DISPLAY_BV,1),1,1,0) AS "배너디스플레이여부"
            ,DECODE(BITAND(DISPLAY_BV,2),2,2,0) AS "메뉴이미지사용여부"
            ,DECODE(BITAND(DISPLAY_BV,4),4,4,0) AS "컨텐츠표시여부"
            ,DECODE(BITAND(DISPLAY_BV,8),8,8,0) AS "Hybrid메뉴여부"
  FROM MENU2;

INSERT, UPDATE문은 각각의 항목값을 모두 더해서 처리하면 됩니다.


UPDATE MENU2
      SET DISPLAY_BV = 1 + 4 + 0 + 8
 WHERE MENU_ID = :MENU_ID;

이번에는 좀 더 난이도가 높은 UI를 보겠습니다.

원칙적으로는 비트속성값 모두를 한 화면에서 핸들링 해야 하지만, 업무요건상 그렇게 될 수 없다고 가정합니다. 아래 그림을 보시면 메뉴관리 화면에서는 Menu Image Use 항목과 Is Hybrid menu 항목은 메뉴관리 화면에서 관리하고, 나머지 2개의 컬럼은 다른 화면에서 관리한다고 가정합니다.



이 경우에는 2^1(2) 과 2^3(8)만 화면에서 관리하므로 다른 비트값 2^0(1), 2^2(4) 값은 이 화면에서 핸들링하면 안됩니다.

SELECT문과 INSERT문은 이렇게 화면을 구성하더라도 기존과 동일하게 처리하면 된다. 하지만 UPDATE문은 다른쪽 화면에서 나머지 값에 대한 핸들링을 하므로 영향을 받지 않도록 다르게 처리해야 합니다.

현재 DIPLAY_BV 값을 7 (2^0(1) + 2^1(2) + 2^2(4))이라고 가정하겠습니다.

즉, 배너디스플레이여부=Y, 메뉴이미지사용여부=Y, 컨텐츠표시여부=Y, Hybrid메뉴여부=N인 상태입니다.
위의 메뉴화면에서 사용자가 Save 버튼 클릭시 값이 메뉴이미지사용여부=N, Hybrid메뉴여부=Y로 상태값이 변경되었다고 하면


현재값:                                      0111 (7)
화면에서 관리안하는 비트값의 합 : 0101 (5)
----------------------------------------------
두값의 BITAND 값                     : 0101 (5)

현재 설정값(비관리항목은0으로)  : 1000 (8)
----------------------------------------------
두값의 BITOR                           : 1101 (13)

먼저 현재값과 화면에서 관리하지 않는 항목에 해당되는 비트값의 BITWISE OR 값 (단순 더하기)을 구해서 두 값을 BITAND 합니다.
즉, 현재값인 7인 값과 화면에서 관리하지 않는 항목의 값 (배너디스플레이여부 2^0(1) + 컨텐츠표시여부 2^2(4))을 더한 값과 서로 BITAND 연산을 합니다. 결과가 0101 (5)가 나오는군요.
이 값과 현재화면에서 설정값(단, 관리안하는 UI항목에 대해서는 전부 0으로 비트값처리, 메뉴이미지사용여부=N이므로 0, Hybrid메뉴여부=Y이므로 8)의 합을 서로 BITOR 연산합니다. 5 BITWISE OR 8 = 13가 나오는군요.
13값을 저장하면 됩니다.

다음과 같은 UPDATE문이 나올 수 있습니다.


UPDATE MENU2
   SET DISPLAY_BV = PKG_UTILS.BITOR(BITAND(DISPLAY_BV,5),DECODE('메뉴이미지사용여부','Y',2,0) + DECODE('Hybrid메뉴여부','Y',8,0))
 WHERE MENU_ID = 1;

* DECODE 부분은 UI 값을 대응되는 비트값으로 구성했다면 DECODE없이 바로 더하면 됩니다.

 BITOR 함수는 오라클에서는 DEFAULT로 제공하지 않습니다. 그래서 아래와 같은 PL/SQL 패키지에서 BIT관련 함수를 구현했습니다.

그리고 패키지 주석을 보시면 이해하시겠지만, 특정 비트자리수의 값을 0으로 혹은 1로 설정할 수 있는 BIT_SET 함수도 만들어 두었습니다.
이 함수를 위 사례에 적용해서 만일 현재값과 상관없이 Hybrid메뉴여부(2^3=8) 항목을 CHECKED로 만들려고 하면 BIT_SET(컬럼값, 8, 1)로 함수를 호출하면 됩니다. 마지막 인자값은 두번째 인자값에 해당되는 비트자리수의 값을 1 혹은 0으로 설정하도록 하는 값(1/0)이 인자로 넘어갑니다.

좀 전에 사용했던 UPDATE문을 동일하게 BIT_SET 함수로 처리할 수 있습니다.

UPDATE MENU2
      SET DISPLAY_BV = PKG_UTILS.BIT_SET(PKG_UTILS.BIT_SET(DISPLAY_BV,2,0), 8,1)
 WHERE MENU_ID = 1;


BITWISE 관련 오라클 유저 함수 (패키지)

다음의 패키지(스크립트)는 오라클에서 BITAND 함수만을 제공하기 때문에 원할한 BIT연산을 위해서 관련 함수를 구현한 코드입니다. 참고하시기 바랍니다.


CREATE OR REPLACE PACKAGE PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, Z NUMBER)
    RETURN NUMBER DETERMINISTIC;
   
END PKG_UTILS;
/


CREATE OR REPLACE PACKAGE BODY PKG_UTILS AS
/******************************************************************************
   NAME        : PKG_UTILS
   PURPOSE     : 시스템 공통으로 사용되는 함수, 프로시저 등을 제공하는 패키지
   DESCRIPTION : UTILITY OPERATION PACKAGE
   REVISIONS   : 1.0
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2011-04-01  Jinook,lee       1. Created this package.
******************************************************************************/
 
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT OR 연산한다.
    -- OR 연산은 두 값의 각 자릿수를 비교해, 둘 중 하나라도 1이 있다면 1을, 아니면 0을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : bit or 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN x + y - BITAND(x, y);
    END;
   
    -------------------------------------------------------
    -- DESC : X, Y 값을 서로 BIT XOR 연산한다.
    -- XOR 연산은 두 값의 각 자릿수를 비교해, 값이 같으면 0, 다르면 1을 계산한다
    -- x, y : 비트 연산할 숫자
    -- return   : BIT XOR 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITXOR(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITOR(x,y) - BITAND(x,y);
    END;
   
    -------------------------------------------------------
    -- DESC : X 값을 BIT NOT 연산한다.
    -- NOT 연산은 각 자릿수의 값을 반대로 바꾸는 연산이다.
    -- x : 비트 연산할 숫자
    -- return   : BIT NOT 연산한 결과값
    --------------------------------------------------------
    FUNCTION BITNOT(x NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN (0 - x) - 1;
    END;
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 0으로 만드는 함수이다.
    -- 내부적으로 X & ^Y 로 연산한다.
    -- x, y : 비트 연산할 숫자
    -- return   : BIT 마스크 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_MASK(x NUMBER, y NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        RETURN BITAND(x, BITNOT(y));
    END;
   
   
    -------------------------------------------------------
    -- DESC : 닷컴의 비트 컬럼값의 특정비트를 주어진 값으로 만드는 함수이다.
    -- x,y : 비트 연산할 숫자
    -- z : x값의 y자리수를 값을 설정할 값 (0 or 1)
    -- return   : BIT SET 연산한 결과값
    --------------------------------------------------------
    FUNCTION BIT_SET(x NUMBER, y NUMBER, z NUMBER)
    RETURN NUMBER DETERMINISTIC
    IS
    BEGIN
        IF z = 1 THEN
            RETURN BITOR(x, y);
        ELSIF z = 0 THEN
            RETURN BITAND(x, BITNOT(y));
        ELSE
            RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER;
        END IF;
    END;
END PKG_UTILS;
/

* BIT_SET 함수에서 RAISE PKG_GLOBAL.ERR_WRONG_PARAMETER; 부분은 제가 자주 사용하는 ERROR를 별도의 GLOBAL 패키지에 선언해두고 참조해서 사용할 수 있도록 구성한 부분입니다.  해당 부분 삭제하시고 패키지 컴파일 하시거나 일반적인 PL/SQL ERROR RAISE 하는 구문으로 대체하시면 됩니다.


  1. 신.. 2011.09.09 01:09

    참기발한 방법이군요.
    여부컬럼하면 11100111 혹은 YYYNNYYY 만 알고 있는데 이런 방법도 있군요.
    잘 봅니ㄴ다..

  2. 땅콩맨 2012.05.14 15:36

    안녕하세요
    MSSQL에서 SQLite로 쿼리를 바꾸려고 하는데
    sql = "declare @num245(4), @num345 char(4) select @num245=autogubun from staff.dbo.ONR_TV_Station_tbl where num=245 select @num345=autogubun from staff.dbo.ONR_TV_Station_tbl where num=345 거든요. 원래 쿼리가...

    바꾸려고 하니깐 declare도 그렇고 select 문 2개 이어서 쓰는방식도 그렇고
    바꾸는 방법을 못찾겠는데, 혹시 이것에 대해서 답변좀 부탁드릴께요~

    이럴줄 알았으면 SQLite 공부좀 해둘껄 그랬어요... 홀홀홀...

  3. 천일몽 2018.05.10 13:57 신고

    만들어주신 패키지를 잘 이용 중에 있습니다.
    감사합니다.

    암호화,복호화 패키지에 이용하였습니다.
    (http://userpark.net/126)


Toad의 강력한 기능을 Toad를 실행하지 않고 eclipse내에서 이용할 수 있다면 개발자들에게는 유용할 것이다. Quest사의 Toad Extension(freeware)를 이용하면 eclipse 뿐만 아니라, Visual Studio에서도 툴 환경 내에서 Toad의 강력함을 이용할 수 있다.

Toad Extension for eclipse를 설치해보자.

1. Eclipse 실행 – Help > Install New Software 메뉴 선택

2. Install 대화상자의 Work with 부분에 다음의 주소를 입력

http://toaddownload.quest.com/toadextensions/eclipse/freeware/
다음, 다음 눌러서 설치하시고 re-start 하면 설치가 끝납니다.

Toad Extension은 Toad 없이도 구동 가능합니다. 다만, 기존에 Toad가 설치되어 있다면 toad User Files 디렉토리를 읽어서 현재 설정된 Connection정보를 Connection window에 자동으로 설정해 줍니다. 다만, 저장된 패스워드 정보를 읽어오지 못해서 패스워드는 다시 설정해 줘야 합니다.

Toad Extension 실행해 보자.

Eclipse 실행 후 window > Open Perspective > Other 선택 후 Toad Extension을 선택하면 됩니다.


Toad Extension Overview

Connections View

Toad Connections View에서는 신규 접속을 만들거나 기존 Connection 정보를 이용하여 Oracle에 접속할 수 있습니다. Oracle 접속은 Direct TCP/IP를 이용해서 접속할 수도 있으며 Oracle Tnsname 정보를 이용하여 접속할 수도 있습니다.

Schema Browser & Detail Browser

좌측의 Schema Browser에서 Database Object 항목들을 선택하면 우측에 상세정보가 나타납니다.
기본적인 Object만 나오는군요. Freeware라 그런지 dba들이 사용하기에는 좀 기능이 약해 보입니다. 그렇지만 왠만한 프로젝트에서는 이 툴 하나만 있어도 무방할 듯 보입니다.

좌측 Schema browser에서는 기본적으로 트리구조로 보이지만 Tree/Multi Tab/Category 방식의 UI를 지원합니다. (스키마 브라우저의 트리 아이콘을 클릭해 보시면 레이아웃이 바뀐답니다.)

스키마 브라우저에서 각각의 Object를 클릭하면 Detail View에 선택한 Object에 대한 상세한 내용이 나옵니다. 나름 필요한 정보는 모두 잘 나오는 것 같습니다.

SQL Editor


SQL Editor에서는 SQL을 실행하고 결과 및 실행계획 등을 볼 수 있습니다. 단축키가 eclipse 기준으로 설정되어 있어서 토드 사용자에게는 약간 헷갈릴 수 있습니다. 익숙한 키를 사용하려면 eclipse 설정에서 단축키 매핑 정보를 변경하시면 됩니다.

SQL Editor에서는 무엇보다 Code Intelligence 기능이 토드보다 빠릅니다. .(dot)을 입력하면 바로 바로 Object 목록이 나타나는 군요.  무척 맘에 듭니다.

PL/SQL Editor


 PL/SQL 뷰어에서 Edit를 누르면 수정화면으로 넘어갑니다.
제가 사용하는 쪽의 DB는 Character Set이 UTF-8 환경인데.. PL/SQL 뷰어에서는 주석문이 안깨지고 잘 보이는데.. 수정화면에서는 스크립트의 주석 부분이 깨지는 군요. Eclipse의 character set을 UTF-8로 변경해도 여전히 깨지는 군요. 이 부분은 약간 이슈가 있는 듯 합니다.
한글 환경의 DB를 사용하는 곳은 별 문제가 없을것보입없을 것, 테스트를 못해봐서.. 장담은 못하겠습니다.

SQL Monitor


하단의 SQL Monitor View에서 Enable Output 아이콘을 클릭하신 후 detail view나 Schema Browser를 클릭하면 Toad Extension이 해당 데이터를 화면에 뿌리기 위해서 오라클에 보내는 SQL을 모니터 할 수 있습니다. Toad의 spool 기능과 동일한 기능이라고 보면 될 것 같습니다.

Session Browser


개발용 Toad Extension임에도 불구하고 Session Browser가 있다니 놀랍습니다. 해당 세션을 선택하고 세션을 Kill 시킬 수도 있군요.

Oracle Parameter View


오라클 파라미터를 조회할 수 있는 뷰도 제공 됩니다.


Toad Extension은 자바 개발시에 Toad와 Eclipse를 번갈아 가면서 개발하지 않고 eclipse 환경 내에서 왠만한 개발을 할 수 있다는 점은 매우 매력적인 요소입니다. 다만, Toad와 같이 많은 기능을 담고 있지 않아서 DBA가 사용하기에는 조금 무리가 따릅니다.
Eclipse 환경이라서 토드에 익숙하지 않은 개발자들에게 매우 유용할 듯 보이며, 특히 프리웨어라는 점은 ㅋㅋ 무지 좋습니다.
프로젝트에서 DBA만 정품 Toad를 구매하고, 나머지 개발자들은 Toad Extension을 사용한다면 비용이 많이 절감되겠네요.

데이터를 조회하고 핸들링 할 수 있는 다른 eclipse plug-in 들과 비교해 보더라도 개인적으로는Toad Extension이 가장 맘에 드는 툴입니다. 물론 아직은 버그도 있고 기능도 부족하지만 점점 더 좋아지리라 막연한 기대를 해봅니다.

제 글을 읽고 맘에 드신다면… 한번 사용해 보세요.^^

SQLite Databae에 대한 일곱번째 포스트입니다. 이번 포스트에서는 SQLite의 Trigger에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

CREATE TRIGGER

CREATE TRIGGER 문으로 트리거를 생성할 수 있습니다. 다들 아시겠지만, 트리거는 INSERT, UPDATE, DELETE 문장이 실행될 때 자동으로 실행되는 코드입니다. 현재 SQLite는 FOR EACH ROW (각 레코드 마다 트리거 코드가 실행됨) 만 지원합니다. 즉, SQL 문장 단위의 트리거는 지원하지 않습니다.

트리거 코드에서는 각 레코드가 INSERT/UPDATE/DELETE 될때 OLD 및 NEW 키워드를 사용하여 변경전 데이터 혹은 변경되는 데이터를 참조할 수 있습니다. 물론 INSERT에서는 NEW 키워드만, DELETE에서는 OLD 키워드만 유효합니다.

특이하게도 SQLite에서는 View에도 트리거를 설정할 수 있는데, INSTEAD OF 트리거로 만들 수 있습니다. SQLite에서는 View가 Read Only이므로 뷰에 대한 I/U/D 작업이 불가능 하지만,  View에 INSTEAD OF 트리거를 만들어서 트리거에서 I/U/D 작업을 수행하는 것은 가능합니다.

SQLite에서는 트리거를 I/U/D에 대해 각각 만들어야 합니다. 오라클 같은 경우는 하나의 트리거에서 모든 것을 처리할 수 있지만 SQLite에서는 그렇지 않아서 조금 귀챦기는 할 것 같습니다.

샘플 코드를 보면서 트리거에 대해서 확인하도록 하겠습니다.

/* SQLite Trigger Test Code */

-- Trigger test를 위해서 user table과 user_log  table을 생성합니다.
create table user (
  id integer primary key autoincrement,
  name varchar(10),
  password varchar(10)
);

create table user_log (
  log_no integer primary key autoincrement,
  id integer,
  name_before varchar(10),
  name_after varchar(10),
  password_before varchar(10),
  password_after varchar(10),
  change_date text, 
  flag varchar(1)
); 

-- update에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_update BEFORE UPDATE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, new.name, old.password, new.password, datetime('now','localtime'),'U');
END;

-- insert에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_insert BEFORE INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- delete에 대한 before trigger를 생성합니다.
CREATE TRIGGER user_trigger_before_delete BEFORE DELETE ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(old.id, old.name, null, old.password, null, datetime('now','localtime'),'D');
END;

-- test를 위한 데이터 insert
INSERT INTO USER(name, password) VALUES('overoid','1234');
INSERT INTO USER(name, password) VALUES('test','12345');
UPDATE USER SET NAME = '보고픈'  WHERE ID = 2;
UPDATE USER SET password = 'abcd' WHERE ID =1;
DELETE FROM USER WHERE id = 2;

-- 데이터 확인 (null 데이터를 명확하게 표기하기 위해서..ifnull 함수를 사용함)
SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D

-- 데이터는 정상적으로 나옵니다.
-- 다만, autoincrement로 생성한 컬럼에 대해서 insert 작업시 실행되는 before trigger에서 NEW.ID 값을
-- 제대로 가져오지 못하고 -1로 입력되는 것을 볼 수 있습니다.

-- 위 문제를 해결하기 위해서 INSERT에 대해서 AFTER 트리거를 생성합니다.
CREATE TRIGGER user_trigger_before_after AFTER INSERT ON user
BEGIN
     INSERT INTO user_log(id, name_before,name_after, password_before, password_after, change_date, flag)     
     VALUES(new.id, null, new.name, null, new.password, datetime('now','localtime'),'I');
END;

-- 데이터를 삽입합니다.
INSERT INTO USER(name, password) VALUES('after','1234');

SELECT log_no, id, ifnull(name_before,'<null>') name_before, ifnull(name_after,'<null>') name_after,
       ifnull(password_before,'<null>') password_before, ifnull(password_after,'<null>') password_after, change_date, flag
  FROM user_log;

-- result
log_no id name_before name_after password_before password_after change_date flag
1 -1 <null> overoid <null> 1234 2010-08-25 17:02:00 I
2 -1 <null> test <null> 12345 2010-08-25 17:02:02 I
3 2 test 보고픈  12345 12345 2010-08-25 17:02:04 U
4 1 overoid overoid 1234 abcd 2010-08-25 17:02:05 U
5 2 보고픈 <null> 12345 <null> 2010-08-25 17:02:07 D
6 -1 <null> after <null> 1234 2010-08-25 17:03:26 I
<null> after <null> 1234 2010-08-25 17:03:26 I

-- after trigger로 변경했더니, autoincrement 컬럼에 대해서 값을 제대로 가져오는 군요.

이것으로 SQLite Trigger에 대한 일곱번째 포스트를 마칩니다. 다음번 포스트에서는 SQLite의 Tranaction 및 Lock에 기능에 대해서 살펴보도록 하겠습니다.

 

SQLite Databae에 대한 여섯번째 포스트입니다. 이번 포스트에서는 SQLite의 DateTime 관련 함수 및 Formatting 에 대해서 다뤄보도록 하겠습니다. 이 부분은 다른 DBMS와 많이 다르고 자주 사용하는 함수들이라 몇가지 표현법은 꼭 익혀야 될 점인것 같습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)


1. DateTime 관련 Keyword

먼저 SQLite에서는 DateTime관련해서 다음과 같은 세가지 키워드가 있습니다.
CURRENT_TIME : 현재 시간 (형식: 03:22:56) 다만, UTC 기준입니다.
CURRENT_DATE: 현재 날짜 (형식: 2010-08-25) UTC 기준.
CURRENT_DATETIME : 현재 날자 및 시간 (형식: 2010-08-25 03:23:37) UTC 기준.

UTC 기준 날짜 및 시간이라서 사용할때는 로컬시간으로 다시 변환작업이 필요할 듯 보입니다. DATETIME 관련 함수들이 있어 크게 유용해 보이지는 않습니다.

2. DateTime 관련 함수.

DateTime 관련 함수는 다음의 5가지가 있습니다.

date(timestring, modifier, modifier, ...)  : 날짜 함수
time(timestring, modifier, modifier, ...)  : 시간 함수
datetime(timestring, modifier, modifier, ...)  :날짜/시간 함수
julianday(timestring, modifier, modifier, ...)  : 율리우스력 함수
strftime(format, timestring, modifier, modifier, ...)  : 날짜 포맷팅 함수


위 다섯개의 함수 인자 중 timestring 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다.

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

strftime 함수에 사용되는 format에 사용할 수 있는 값들은 다음과 같습니다.

%d    day of month: 00
%f     fractional seconds: SS.SSS
%H    hour: 00-24
%j     day of year: 001-366
%J     Julian day number
%m    month: 01-12
%M    minute: 00-59
%s     seconds since 1970-01-01
%S     seconds: 00-59
%w     day of week 0-6 with sunday==0
%W     week of year: 00-53
%Y     year: 0000-9999
%%    %

함수의 modifer에 들어갈 수 있는 값들은 다음과 같습니다.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc


잘 안 와 닿는듯 합니다. 아래 샘플 코드를 보면 이해가 가실 겁니다.

--UTC 기준의 현재 날짜/시간
select datetime('now');
2010-08-25 04:01:46

-- 로컬 기준의 현재 날짜/시간
select datetime('now','localtime');
2010-08-25 13:02:30

--현재 로컬 기준 시간에서 10분 3.5초를 더한 시간.
select datetime('now','localtime','+3.5 seconds','+10 minutes');
2010-08-25 13:14:15

--현재 로컬 시간에 3.5초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일입니다.)
select datetime('now','localtime','+3.5 seconds','weekday 2');
2010-08-31 13:05:39

--현재 달의 마지막 날짜
SELECT date('now','start of month','+1 month','-1 day','localtime');
2010-08-31

--2004-01-01 02:34:56초부터 현재까지의 총 초
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
209785028

--현재날짜/시간 기준에서 올해 9번째달의 첫번째 화요일
SELECT date('now','start of year','+9 months','weekday 2');
2010-10-05

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');
2010/08/27 09:17:22




 

  1. 김승배 2011.03.19 15:18

    DateTime 쿼리 관련해서..
    Select date(birthday) From TableA 와 같이 테이블의 DateTime column을 넣어주면 값이 안나오던데요..
    혹시 해결 방법을 아시나요?


SQLite Databae에 대한 다섯번째 포스트입니다. 이번 포스트에서는 SQLite의 Core 함수에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1.Core Function

 coalesce(X,Y,...) coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
 ifnull(X,Y) ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
 length(X) 길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
 like(X,Y)
 like(X,Y,Z)
Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
 lower(x) 소문자로 변환 합니다.
 upper(X) 대문자로 변환 합니다.
 ltrimX)
 ltrim(X,Y)
ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다.
ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.
 
select ltrim(" ZZZZabcZZ ", " aZ")
=> 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다.
ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
 rtrim(X)
 rtrim(X,Y)
rtrim(X)는 우측편 공백 제거
rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
 trim(X)
 trim(X,Y)
trim(X)는 양쪽 공백 제거
trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
 max(X,Y,..) 인자값들 중 최대값을 리턴합니다.

create table t1 (coll integer, col2 integer, col3 integer);

insert into t1 values(1,2,3);
insert into t1 values(5,3,1);

select max(col1, col2, col3) from t1;
 min(X,Y,...) 인자값들 중 최소값을 리턴합니다.
 nullif(X,Y) 두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다.
nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
 quote(X) Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다.
Insert나 update 시에 사용하면 유용할 듯 합니다.
select quote("girl's mouse") => 'girl''s mouse'
 random(*) -9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
 randomblob(N) N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
 hex(X) 바이너리 값을 hex 값으로 변경합니다.
select hex(randomblob(16))
 replace(X,Y,Z) X 문자열 중에서 Y문자열을 Z로 변경합니다.
select replace('1/12/2009','1','x') => "x/x2/2009"
 round(X)
 round(X,Y)
반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다.
Round(3.5) => 4, round(2.555, 2) => 2.56
 substr(X,Y)
 substr(X,Y,Z)
substr()함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다.
select substr("string", 1, 3) => str
select substr("string", 0, 3) => st
select substr("string", -1, 3) => g
select substr("string", -3, 3) => ing
select substr("string", 2) => tring
 typeof(X)  X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

2. Aggregation Function (집합 함수)

avg(X) 그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*)
count(X)
count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다.
count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X)
group_concat(X,Y)
X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X) 그룹내의 값들중 최대값을 리턴합니다.
min(X) 그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X)
total(X)
Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

/* Aggregation Function Test */

create table t1 (
type integer,
id integer,
name varchar(10),
value real,
data text
);

insert into t1 values(1,1,'a',0.0,'1');
insert into t1 values(1,2,'b',3.5,NULL);
insert into t1 values(1,3,NULL,3.1,'2');
insert into t1 values(1,4,'c',3.5,'10');
insert into t1 values(1,5,'d',NULL,NULL);

-- count(*)와 count(X)의 차이 비교
select count(*), count(name), count(data)
from t1
group by type

-- result
count(*) count(name) count(data)
5 4 3

-- group_concat test query
select group_concat(id,' > ') as exp
from t1;

-- result
exp
1 > 2 > 3 > 4 > 5

select group_concat(data,' > ') exp
from t1
group by type

-- result
exp
1 > 2 > 10

/* sum(), tatal() function test */
select sum(data), total(data)
from t1
where id in (2,5)
group by type;

-- result
sum(data) total(data)
<null>    0

생각보다 SQLite에서 지원되는 내장 함수가 너무 적습니다. SQLite는 각 언어별로 확장 기능을 가지고 있긴 하지만, 쉽게 사용하는게 아니라서.. 한번의 SQL로 처리 가능한 것을 상당 부분 프로그램 코드에서 처리해야 하지 않을까 싶습니다. 그럼에도 불구하고 group_concat() 같은 기능의 함수는 Oracle에서도 11g가 되어서야 내장 함수로 제공할 정도로 대부분의 DB에서 제공하지 않는 함수인데.. SQLite에서 구현이 되어 있다는 게 놀라울 따름입니다.

이것으로 SQLite Function 부분을 마치며, 다음 포스트에서는 DateTime 및 DateTime Formatting에 대해서 알아보도록 하겠습니다.

  1. suroMind 2011.04.06 17:00 신고

    좋은 정보 감사합니다. 출처 남기고 퍼가겠습니다.
    http://blog.suromind.com/43


SQLite Databae에 대한 네번째 포스트입니다. DML은 대부분의 개발자들이 기본 SELECT/INSERT/UPDATE/DELETE에 대해서 워낙 잘 들 알고 계시므로 이번 포스트에서는 DML 관련하여 몇가지만 다루도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1. EXPLAIN / EXPLAIN QUERY PLAN

실행계획을 볼 수 있는 명령어입니다.
EXPLAIN 명령어는 세부적인 VDBE의 operation code를 볼 수 있습니다.
EXPLAIN QUERY PLAN 은 실행계획을 보여 줍니다.

예제 테스트는 SQLite 첫번째 포스트에서 알려드린 SQLite Expert Personal 버전의 demodb를 이용해서 테스트하도록 하겠습니다.

예제)
EXPLAIN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

-- result
addr opcode p1 p2 p3 p4 p5 comment
0 Trace 0 0 0 "" 00 
1 String8 0 1 0 Canada 00 
2 Integer 145 2 0 "" 00 
3 Goto 0 53 0 "" 00 
4 OpenRead 5 5 0 keyinfo(1,BINARY) 00 
5 OpenRead 4 27 0 0 00 
6 OpenRead 1 9 0 8 00 
7 OpenRead 2 18 0 5 00 
8 OpenRead 6 41 0 keyinfo(1,BINARY) 00 
9 OpenRead 3 13 0 3 00 
10 OpenRead 7 39 0 keyinfo(1,BINARY) 00 
11 SeekGe 5 45 1 1 00 
12 IdxGE 5 45 1 1 01 
13 MustBeInt 2 44 0 "" 00 
14 NotExists 4 44 2 "" 00 
-- 중략.

EXPLAIN QUERY PLAN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

order from detail
0 0 TABLE COUNTRY AS K WITH INDEX sqlite_autoindex_country_1
1 4 TABLE EMPLOYEE AS E USING PRIMARY KEY
2 1 TABLE CUSTOMER AS C
3 2 TABLE ORDERS AS O WITH INDEX idx_orders_CustNo
4 3 TABLE ITEMS AS I WITH INDEX idx_items_ByOrderNo

VDBE의 OP코드 분석(EXPLAIN 결과) 및 실행계획에 대해서는 향후 포스트에서 상세하게 다루도록 하겠습니다.
맘이 급하십니까? 당장 알기 원하시면 다음의 링크를 참고 하시기 바랍니다.

실행계획/옵티마이저.
http://www.sqlite.org/optoverview.html
http://www.sqlite.org/queryplanner.html

VDBE의 OP코드
http://www.sqlite.org/opcode.html

2. SELECT

SELECT 는 워낙 잘 들 아시는 부분이라  한 두가지만 테스트 해 보도록 하겠습니다.
먼저, 오라클의 dual 테이블 같이 처리되는 구문은 MS-SQL 처럼 FROM 절 없이 사용하면 될 것 같습니다.
예)
SELECT 'A' a

페이징 처리 (ROWNUM, TOP과 유사한 기능)

페이징 처리시 오라클에서는 ROWNUM을 사용하고 MS-SQL에서는 TOP을 주로 사용들 합니다. 물론 요즘은 ROW_NUMBER() 함수를 더 많이 사용하신다구요?
SQLITE에서는 페이징 처리하기가 더 편리합니다. LIMIT와 OFFSET이 그것입니다.

SELECT CUSTNO, COMPANY, CITY
  FROM CUSTOMER 
 ORDER BY CUSTNO
 LIMIT 5 OFFSET 5;

ORDER BY와 상관없이 사용이 가능합니다. LIMIT에 값을 지정하면 화면에 출력할 레코드 개수를 지정할 수 있습니다. OFFSET은 건너뛸 레코드 수를 나타냅니다. 즉, 위의 쿼리는 CUSTNO로 정렬해서 나온 순서에서 6번째부터 5개의 레코드를 가져오는 SELECT문입니다. OFFSET은 생략도 가능합니다.

페이징은 편리하나 오라클의 ROWNUM 처럼 결과에 수치값을 나타낼 방법이 있다면 여러모로 편리한데 그런 기능은 아쉽습니다. 필요시에는 별도의 NUMBER를 가진 테이블을 하나 만들어서 조인해서 사용해야 할 듯 합니다.

USING 구문

일반적으로 EQUAL JOIN 구문에서 ON 이하는 두 테이블간의 조인조건의 컬럼을 기술하는데, 두 컬럼의 컬럼명이 동일한 경우가 많습니다. 이때 간략하게 USING을 사용하여 쿼리를 줄일수 있습니다.

SELECT * FROM A INNER JOIN B ON A.COL1 = B.COL1;

-- 아래와 같이 위 문장을 USING을 사용하여 기술할 수 있음.
SELECT * FROM A INNER JOIN B USING(COL1);

-- 여러 테이블 조인에서의 USING 사용 예)
SELECT * FROM A JOIN B USING (c) JOIN C USING (e) JOIN D USING (g)

3. UPDATE (JOIN)

UPDATE 구문도 워낙 잘 아시니 여기서는 JOIN UPDATE에 대해서만 확인해 보도록 하겠습니다.
테스트를 위해 CUSTOMER 테이블에 컬럼을 2개 추가했습니다.

/* JOIN UPDATE TEST */
-- ALTER TABLE에서 한번여 여러 컬럼 추가도 안됩니다.

ALTER TABLE CUSTOMER
ADD CAPITAL TEXT;

ALTER TABLE CUSTOMER
ADD COLUMN CONTINENT TEXT;

-- 오라클 스타일 JOIN UPDATE TEST => ERROR
UPDATE CUSTOMER C
   SET (CAPITAL, CONTINENT) = (SELECT CAPTIAL, CONTINENT  
                                WHERE COUNTRY K                               
                                  AND K.[Name] = C.COUNTRY)                                 
 WHERE COUNTRY IN (SELECT NAME FROM COUNTRY);

-- MS-SQL 스타일 JOIN UPDATE => ERROR
UPDATE CUSTOMER
   SET CAPITAL = K.CAPITAL,  
       CONTINENT = K.CONTINENT      
FROM CUSTOMER C JOIN COUNTRY K ON C.Country = K.NAME;

찾아보니 JOIN UPDATE 자체를 지원하지 않는답니다. 에구, 이게 안되는 건 좀 치명적인듯. 업데이트시에 어플에서 처리하는 방식밖에 안될 것 같습니다.

4. DELETE

DELETE시에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다. 이 부분은 편리한 듯~ , 참 UPDATE 구문에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다.

DELETE문도 단순 스타일은 워낙 잘 들 아시니 중복 레코드 제거 쿼리 테스트만 해보도록 하겠습니다.


/* 중복 제거 DELETE 문 테스트 */

-- 임시 테이블 생성.
create table t1 (
id integer,
name varchar(10)
);

-- 테스트 데이터 삽입.
insert into t1 values(1,'a');
insert into t1 values(2,'a');
insert into t1 values(2,'b');
insert into t1 values(1,'b');
insert into t1 values(1,'C');

-- 오라클에서 주로 사용하는 방식으로 테스트.
-- ANY 키워드를 지원하지 않아서 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > ANY (SELECT ROWID
                                     FROM t1 B
                                    WHERE A.id = B.id);

-- DELETE문에서 서브쿼리로 조인을 지원안해서 이것도 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > (SELECT MIN(ROWID)
                              FROM    t1 B
                             WHERE A.id = B.id);

-- 조금은 부하가 있지만, GROUP BY절을 이용한 NOT IN 서브쿼리로 중복 제거 가능.
DELETE FROM t1
WHERE ROWID NOT IN (SELECT MIN(ROWID)
                      FROM T1                     
                     GROUP BY ID);

5. COMMENT

SQLITE에서는 주석구문으로 블록주석인 /* */ 구문과 단일 행 주석인 -- 을 사용합니다.

6. NULL 처리

NULL값에 대한 처리는 Oracle과 거의 유사하게 동작합니다. SQLITE에서 NULL에 대한 상세한 정보를 얻으실려면 http://www.sqlite.org/nulls.html 를 참조하시기 바랍니다. 샘플코드 따라서 해보시면 금방 이해가 가실듯 합니다.

7. INDEXED BY

실행계획 편에서나 다룰 내용이지만, 여기서 INDEXED BY에 대해서 조금 다루도록 하겠습니다.


테이블명 뒤에 INDEXED BY 구문을 사용하면 지정된 인덱스를 쿼리가 무조건 사용합니다.
지정한 인덱스의 분포도나 이런거 상관없습니다. 무조건 사용합니다. 오라클의 INDEX 힌트와 유사해 보이지만 다릅니다. 오라클의 힌트는 힌트를 잘못 사용하더라도 에러가 발생하지 않지만, SQLITE에서는 SQL 구문이 FAIL 됩니다. 인덱스명을 잘못 사용하는 경우는 물론이고, 인덱스를 타지 않는 상황에서 인덱스를 지정해도 에러가 발생합니다.
아래 쿼리를 보겠습니다.

PRAGMA INDEX_LIST(COUNTRY); /* COUNTRY 테이블의 인덱스 현황입니다. */

seq name unique
0 sqlite_autoindex_country_1 1
--AUTOINCREMENT로 지정된 UNIQUE 인덱스가 하나 존재합니다.

--현재 상황에서 테스트할 쿼리의 실행계획을 보도록 하겠습니다.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

order from detail
0 0 TABLE COUNTRY
--테이블 FULL SCAN하는 실행계획이 생성되었습니다.

-- 인덱스를 추가하겠습니다.
CREATE INDEX COUNTRY_IX1 ON COUNTRY (NAME);
CREATE INDEX COUNTRY_IX2 ON COUNTRY (CAPITAL);

PRAGMA INDEX_LIST(COUNTRY);

seq name unique
0 COUNTRY_IX2 0
1 COUNTRY_IX1 0
2 sqlite_autoindex_country_1 1
-- 추가된 인덱스가 보입니다.
-- 이전에 실행한 쿼리를 다시 실행해 보도록 하겠습니다.

EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME = 'c'
   AND CAPITAL = 'a';

order from detail
0 0 TABLE COUNTRY WITH INDEX sqlite_autoindex_country_1
-- 인덱스를 이용하지만, AUTOINCREMENT 컬럼에 대한 인덱스를 타고 검색합니다. 아마도 순차적으로 검색하려고 그런가 봅니다.

-- COUNTRY_IX2를 사용하도록 쿼리를 짠후 실행계획을 보면..
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX1
 WHERE NAME = 'c'
   AND CAPITAL = 'a'

-- 인덱스를 사용하는 쿼리로 변경이 되었습니다.
order from detail
0 0 TABLE COUNTRY WITH INDEX COUNTRY_IX1

-- 재미있는 상황이 있군요.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX2
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

/*
= 검색을 LIKE 검색으로 바꾸니 Cannot use index : COUNTRY_IX2란 에러가 발생했습니다. 일반적으로 오라클이나 MS-SQL은 단방향 LIKE 검색의 경우 인덱스를 태울 수 있습니다. SQLITE에서는 에러가 나는걸 보니 LIKE 검색은 인덱스를 이용하지 않나 봅니다. (이 부분은 향후 실행계획 부분에서 다시 자세히 보도록 하겠습니다.) */

본 포스트에서는 일반적인 DML에 대한 강좌 형식 보다는 SQLite에 대한 특징적인 면들을 살펴보았습니다. 그래서 insert 구문은 아예 다루지도 않았습니다. SQLite에 대한 기본 SQL 문법에 대해 더 궁금하시면 다음 사이트를 살펴보시기 바랍니다. http://www.sqlite.org/lang.html

다음 포스트에서는 SQLite Core Function 사용법에 대해 다뤄 보도록 하겠습니다. 

SQLite User Guide 세번째 포스트입니다.  이번에는 CREATE TABLE, CREATE INDEX 등의 DDL 문에 대해 알아보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

SQLite DDL문은 특별한 게 없어서 대부분 잘 아시리라 생각됩니다. DDL문에 대해 전혀 모르신다구요? 상관없습니다. GUI 툴을 사용하셔서 테이블이나 인덱스를 만드시고 DDL문을 코드에 붙이셔도 됩니다. 본 포스트에서는 모든 DDL문을 다루기 보다는 주요한 몇가지와 SQLite DB에서의 특징적인 면을 살펴보도록 하겠습니다.

1. CREATE TABLE


예제1)
CREATE TABLE TEST2 (
     _ID INTEGER NOT NULL,    
     CLASS TEXT NOT NULL,    
     VALUE TEXT,
     CONSTRAINT TEST2_PK PRIMARY KEY (_ID, CLASS)
);

SQLite에서는 ALTER TABLE 문에서 ADD CONSTRAINT 구문이 지원되지 않기 때문에 PRIMARY KEY, UNIQUE, CHECK등의 TABLE LEVEL의 CONSTRAINT는 위 문장처럼 CREATE TABLE 문 제일 하단에 기술해야 합니다.


예제2)
CREATE TABLE TEST1 (
     _ID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
     TYPE TEXT NOT NULL DEFAULT 'HIGH',
     NAME TEX);

일련번호 채번은 위와 같이 AUTOINCREMENT 구문을 CRATE TABLE 의 해당 컬럼에 기술하면 자동 증가되는 일련번호를 사용할 수 있습니다.
AUTOINCREMENT 문을 가진 DDL이 최초 실행되면 SQLITE는 내부적으로 SQLITE_SEQUENCE 테이블을 생성합니다. 이 테이블은 NAME, SEQ 컬럼을 가진 테이블로 테이블마다 자동 증가되는 최종값을 가집니다. 재미있게도 SQLITE_SEQUENCE 테이블값을 직접 핸들링도 가능합니다.

/* SQLITE_SEQUENCE TEST */

--위에서 만든 TEST1 테이블에 데이터를 2개 넣었습니다.
INSERT INTO TEST1(NAME) VALUES('A');
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM SQLITE_SEQUENCE;

-- 현재 SEQ = 2인 SQLITE_SEQUENCE 테이블을 30으로 업데이트합니다.
UPDATE SQLITE_SEQUENCE
SET SEQ = 30
WHERE NAME = 'TEST1';

-- 다시 레코드를 INSERT한 후 TEST1 데이터를 조회해보니 마지막에 들어간 레코드값은 31로 들어갑니다.
INSERT INTO TEST1(NAME) VALUES('B');

SELECT * FROM TEST1;

_ID TYPE NAME
1 HIGH A
2 HIGH B
31 HIGH B

TEMP TABLE

/* TEMP TABLE CREATION */
CREATE TEMP TABLE COUTRY_TEMP
AS
SELECT *
  FROM COUNTRY     
 WHERE 1 = 2;

CREATE TABLE 구문 사이에 TEMP, TEMPORARY 명령어를 넣으시면 임시 테이블이 생성됩니다. 임시테이블은 Connection이 끊어지면 테이블이 없어집니다. 위 예에서 WHERE 1=2 라고 기술하시면 WHERE 조건이 거짓이 되어 테이블 생성시 COUNTRY 테이블과 동일한 테이블이 만들어지지만 데이터는 들어가지 않습니다.
앞의 PRAGMA 편 포스트에서 시스템 카탈로그를 조회할 수 있는 테이블이 SQLITE_MASTER라고 했는데, 임시테이블을 만들면 이 임시테이블은 SQLITE_MASTER로 조회해도 나타나지 않습니다.
임시테이블은 SQLITE_TEMP_MASTER를 조회하시면 조회가 가능합니다.

COLLATE

추가적으로 COLLATE에 대해서도 알아보도록 하겠습니다. TABLE 정의시 COLLATE를 지정할 수 있습니다. 값은 BINARY, RTRIM, NOCASE 구문이 가능한데, 각각의 케이스에 따라서 값을 비교하는 비교연산자의 결과에 영향을 미칠 수 있습니다. (http://www.sqlite.org/datatype3.html)

COLLATE로 지정가능 한 값.

BINARY – 내부적으로 텍스트 인코딩과 상관없이 memcmp() 함수를 이용하여 텍스트를 비교합니다.
NOCASE – BINARY와 유사하지만 영문자의 경우 대소문자를 동일하게 처리, 즉, 대소문자 구별을 하지 않습니다.
RTRIM – BINARY와 동일하지만 비교하는 문자열의 끝부분에 공백이 있는 경우 이를 제거하고 비교합니다.
디폴트값은 binary 입니다.

COLLATE에 대한 기본 처리 룰은 다음과 같습니다.
  • 이항 비교 연산자(=, <, >, <= 및 >=)의 경우 피연산자 중 하나가 열이면 해당 열의 기본 데이터 정렬 유형에 따라 비교에 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이면 왼쪽 피연산자의 데이터 정렬 유형에 따라 사용되는 데이터 정렬 시퀀스가 결정됩니다. 두 피연산자가 모두 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

  • BETWEEN...AND 연산자는 >= 및 <= 연산자가 있는 두 표현식을 사용하는 것과 같습니다. 예를 들어 x BETWEEN y AND z 표현식은 x >= y AND x <= z와 동일합니다. 따라서 BETWEEN...AND 연산자는 위와 같은 규칙에 따라 데이터 정렬 시퀀스를 결정합니다.

  • IN 연산자는 = 연산자와 같은 방법으로 사용할 데이터 정렬 시퀀스를 결정합니다. 예를 들어 x IN (y, z) 표현식에 사용되는 데이터 정렬 시퀀스는 x가 열인 경우 x의 기본 데이터 정렬 유형입니다. 그렇지 않은 경우에는 BINARY 데이터 정렬이 사용됩니다.

  • SELECT 문에 포함된 ORDER BY 절에는 정렬 작업에 사용할 데이터 정렬 시퀀스를 명시적으로 할당할 수 있습니다. 이렇게 하면 명시적인 데이터 정렬 시퀀스가 항상 사용됩니다. 그렇지 않으면 ORDER BY 절에서 정렬하는 표현식이 열인 경우 해당 열의 기본 데이터 정렬 유형에 따라 정렬 순서가 결정됩니다. 표현식이 열이 아니면 BINARY 데이터 정렬 시퀀스가 사용됩니다.

/* COLLATE TEST */

CREATE TABLE t1(
    x INTEGER PRIMARY KEY,
    a,                 /* collating sequence BINARY */
    b COLLATE BINARY,  /* collating sequence BINARY */
    c COLLATE RTRIM,   /* collating sequence RTRIM  */
    d COLLATE NOCASE   /* collating sequence NOCASE */
);

                                 /* x   a      b        c       d */
INSERT INTO t1 VALUES(1,'abc','abc', 'abc  ','abc');
INSERT INTO t1 VALUES(2,'abc','abc', 'abc',  'ABC');
INSERT INTO t1 VALUES(3,'abc','abc', 'abc ', 'Abc');
INSERT INTO t1 VALUES(4,'abc','abc ','ABC',  'abc');

/* Text comparison a=b is performed using the BINARY collating sequence. */
/* a, b 컬럼이 모두 BINARY로 정의되어 있으므로 BINARY모드로 비교합니다. */
SELECT x FROM t1 WHERE a = b ORDER BY x;
--result 1 2 3

/* Text comparison a=b is performed using the RTRIM collating sequence. */
/* 명시적으로 COLLATE를 지정하면 해당 COLLATE로 비교합니다. */
SELECT x FROM t1 WHERE a = b COLLATE RTRIM ORDER BY x;
--result 1 2 3 4

/* Text comparison d=a is performed using the NOCASE collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE d = a ORDER BY x;
--result 1 2 3 4

/* Text comparison a=d is performed using the BINARY collating sequence. */
/* 서로 컬럼의 COLLATE가 다른 경우 선행 컬럼의 COLLCATE를 사용해서 비교합니다. */
SELECT x FROM t1 WHERE a = d ORDER BY x;
--result 1 4

/* Text comparison 'abc'=c is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE 'abc' = c ORDER BY x;
--result 1 2 3

/* Text comparison c='abc' is performed using the RTRIM collating sequence. */
SELECT x FROM t1 WHERE c = 'abc' ORDER BY x;
--result 1 2 3

/* Grouping is performed using the NOCASE collating sequence (Values
** 'abc', 'ABC', and 'Abc' are placed in the same group). */
/* 그룹바이절에 지정된 컬럼의 COLLATE를 사용해서 처리합니다. */
SELECT count(*) FROM t1 GROUP BY d ORDER BY 1;
--result 4

/* Grouping is performed using the BINARY collating sequence.  'abc' and
** 'ABC' and 'Abc' form different groups */
SELECT count(*) FROM t1 GROUP BY (d || '') ORDER BY 1;
--result 1 1 2

/* Sorting or column c is performed using the RTRIM collating sequence. */
/* ORDER BY절에 선행컬럼에 지정된 컬럼의 COLLATE를 사용해서 정렬하는군요 */
SELECT x FROM t1 ORDER BY c, x;
--result 4 1 2 3

코드 출처 : http://www.sqlite.org/datatype3.html

SQLite COLLATE 처리 원칙만 알면 대충 예상은 가능합니다. 그렇다면, 다음의 쿼리는 결과가 어떻게 나올까요?

SELECT d, c FROM t1 GROUP BY d, c ;

SELECT c, d FROM t1 GROUP BY c, d ;

직접 한 번 해보시기 바랍니다. 위 쿼리의 경우 2개를 동시에 비교해서 처리합니다. 테스트 해 본 결과 Sqlite에서는 Group by가 Sort Group by 방식을 사용하는 듯 합니다. (Group By 절에 지정된 순되로 레코드가 정렬되어서 나옵니다.) Group by나 Order시에 같은 Collate이면 rowId 순으로로 처리되는 것 같네요.

에구.. 복잡합니다.
개발할 때는 디폴트 값인  binary 모드로만 DDL을 생성해서 처리하는 게 예측하기 편리하고 좋을 것 같습니다.

ROWID

CREATE TABLE 관련하여 끝으로 ROWID에 대해서 알아보도록 하겠습니다.
SQLite의 모든 Table의 열은 unique한 64bit singed integer key를 내부적으로 가집니다. 이 값을 “ROWID”, “OID“, “_ROWID_”라고 부르며, SELECT 절에 기술하면 데이터 조회도 가능합니다.

만일 테이블 생성시에 컬럼의 정의를 INTEGER PRIMARY KEY라고 기술하면 이 컬럼(INTEGER PRIMARY KEY) 컬럼을 ROWID에 대한 ALIAS로 사용하게 됩니다. 즉, 컬럼과 ROWID가 같아집니다.
다음의 테스트를 통해 확인해 보도록 하겠습니다.

/* ROWID Alias Test */
create table t
(
x INTEGER PRIMARY KEY ASC,
v text
);

insert into t values(1,'a');
insert into t values(2,'a');
insert into t values(5,'a');

select rowid, x, v from t;

x x_1 v
1 1 a
2 2 a
5 5 a


x컬럼을 INTEGER PRIMARY KEY로 정의한 테이블 생성후, 데이터를 몇개 넣고 조회를 해보면 rowid라고 SELECT절에 기술한 컬럼의 명칭은은 x 로 표기되어 있습니다. 두번째 x는 x 이름이 중복되므로 _1을 붙여서 나왔습니다.

2. ALTER TABLE



ALTER TABLE은 컬럼추가 및 테이블명 변경외에는 안됩니다. 이 점은 중요합니다. 우리가 안드로이드 개발시 SQLite를 사용해야 하며, 컬럼 추가 이외에 DB 구조를 변경해야 한다면, upgrade 처리시에 여러가지를 고려해야 할 것입니다.

3. CREATE VIEW

CREATE VIEW에서 TEMP/TEMPORARY를 지정해서 뷰를 임시로 만들 수가 있습니다. 다른 DBMS에서는 못 본 것 같은데… 이런 기능도 있군요.

4. ANALYZE



ANALYZE는 테이블의 통계정보를 수집하는 명령어입니다. 우리가 사용하는 DB의 데이터가 많거나 속도가 안 나온다면 명령어를 실행해 보시기 바랍니다. 뒤에 파라미터에 값을 주지 않고 “ANALYZE” 명령어만 실행하면 전체 테이블, 인덱스에 대해서 통계정보가 생성됩니다. 생성된 통계정보는 SQLITE_STAT1 이란 테이블이 생성되면서 이곳에 저장됩니다. 문제는 ANALYZE 명령어가 데이터베이스 컨텐츠 변경시에 자동으로 실행되지는 않습니다.


ANALYZE
SELECT * FROM sqlite_stat1;

5. REINDEX



인덱스 리빌드 하는 명령어입니다. 단편화가 많이 된 인덱스를 다시 생성해 줍니다

6. VACUUM

VACUUM 명령어는 데이터베이스를 완전히 cleanzing 하는 명령어입니다. 데이터베이스 Object를 Drop하더라도 재사용을 위해 빈 공간은 남겨집니다. 이런 빈공간 및 DML 작업으로 인한 단편화를 제거하기 위해서 VACUUM 명령어를 실행할 수 있습니다. 실행하면 파일 사이즈도 줄여줍니다.

VACUUM 명령어를 실행하면 SQLITE는 전체 OBJECT를 TEMP 공간에 복사한 후 다시 복사하여 전부 새롭게 생성합니다. 이때 ROWID는 변경될 수도 있습니다.

본 포스트에서 간략하게나마 필요한 DDL문은 대충 다 다룬 것 같습니다. 일부 DDL과 뒤에 포스트에서 별도로 다룰  TRIGGER, TRANACTION 관련 DDL은 다루지 않았습니다.
DDL에 관한 전체 명령어 리스트 및 내용은 http://www.sqlite.org/lang.html  문서를 참고하시기 바랍니다.

  1. 제눅스 2010.10.14 21:49

    좋은 글 감사합니다.


SQLite User Guide 두번째 포스트입니다.
SQLite Document를 보다 보니 PRAGMA란 지시어(확장 명령어)가 나옵니다. 오라클에서도 PL/SQL 개발할 때 자율 트랜젝션이나 에러 정의 할 때 등 몇몇 부분에 PRAGMA 지시어를 사용할 수 있긴 합니다만 SQLite만의 독특한 기능들이라서 별도로 포스트를 구성했습니다.

전체 PRAGMA 지시어 (http://www.sqlite.org/pragma.html ) 중에서 자주 사용할 법한 몇가지만 살펴보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

이전 포스트에도 밝혔듯이 테스트는 SQLite Expert Personal 제품에 들어있는 demodb를 사용하도록 하겠습니다.

PRAGMA 명령어

PRAGMA case_sensitive_like = boolean;

SQLite는 기본적으로 like 검색시에 대소문자를 무시합니다. ‘A’ LIKE ‘a’ 가 true가 됩니다. PRAGMA 에서 이 설정을 바꿀수 있습니다.

PRAGMA case_sensitive_like = true;

/* Default 설정에서 대소문자 처리에 대한 Equal(=) SELECT 테스트 */
SELECT *
  FROM COUNTRY   
 WHERE NAME = 'Brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

SELECT *
  FROM COUNTRY   
 WHERE NAME = 'brazil';

결과없음.

위 PRAGMA 설정은 LIKE 검색에 한해서만 적용되는 듯합니다. WHERE조건에 equal 검색은 대소문자를 정확히 구분합니다. (Collate 설정에 따라서 바꿀 수 있습니다. 뒤쪽에 나옴)

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 LIKE 검색 테스트*/

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

/* Default 설정 - Like 검색시 대소문자 구별 안함 에서 %없이 LIKE 검색 테스트*/
SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE 'brazil';

-- Result
Name Capital Continent Area Population
Brazil Brasilia South America 8511196 150400000

이번에는 LIKE 검색에  % 연산자를 빼고 검색했습니다.(두번째 쿼리문) 오라클의 경우 이런식의 쿼리가 날아가면 Optimzer가 query를 like 검색을 =로 변경해 버립니다. 하지만, SQLite의 경우 그렇지 않고 like 검색 되는 것 같습니다. (equal과 거의 유사하지만 대소문자만 가리지 않는 방식).

명령어로 대소문자 비교를 하도록 설정을 변경 한 후 테스트 해보도록 하겠습니다.


PRAGMA case_sensitive_like = 'TRUE'

SELECT *
  FROM COUNTRY   
 WHERE NAME LIKE '%brazil%';

-- Result 없음.

역시나 설명되로 PRAGMA 명령어로 설정을 변경했더니 대소문자를 따져서 LIKE 검색을 하는군요.

PRAGMA count_changes;
PRAGMA count_changes
= boolean;

count_changes 값이 설정되면 INSERT, UPDATE, DELETE 시에 변경된 레코드 수(Integer)값을 받으며, false로 설정되어 있으면 받지 않는다고 합니다. GUI 툴에서는 툴 자체가 메시지를 표시하기 때문에 제대로 테스트가 안되더군요. 나중에 틈날 때 코드레벨에서 테스트를 해봐야 겠습니다.

PRAGMA encoding;
PRAGMA encoding = "UTF-8";
PRAGMA encoding = "UTF-16";
PRAGMA encoding = "UTF-16le";
PRAGMA encoding = "UTF-16be";

데이터베이스의 인코딩을 바꿀수 있는 옵션입니다. 거의 쓸 일은 없을 것 같습니다.

PRAGMA table_info(table-name);
테이블 정보를 조회하는 명령어입니다.

PRAGMA table_info(COUNTRY);

cid name type notnull dflt_value pk
0 Name CHAR(24) 0  1
1 Capital CHAR(24) 0  0
2 Continent CHAR(24) 0  0
3 Area FLOAT 0  0
4 Population FLOAT 0  0


PRAGMA index_list(table-name); 인덱스 리스트를 볼 수 있습니다.
PRAGMA index_info(index-name); 인덱스 정보를 조회할 수 있습니다.
PRAGMA foreign_key_list(table-name); fk 리스틀 볼 수 있습니다.

시스템 카탈로그를 조회할 수 있는 방법은 위 PRAGMA 명령 말고도 sqlite_master 테이블을 직접 조회해도 됩니다. SQLITE_MASTER 테이블은 READ-ONLY 테이블입니다.

SELECT * FROM SQLITE_MASTER;

SQLITE_MASTER 테이블을 조회하시면 테이블, 인덱스, 트리거등 모든 정보와 DDL문까지 알 수가 있습니다.

DATATYPE

Sqlite가 지원하는 데이터 타입은 다음과 같습니다.

1. Null
2. Integer – 부호있는 정수, 실제 값에 따라 1byte에서 8byte까지 가변적으로 저장됨.
3. Real – 실수
4. Text – 문자열
5. BLOB – blob 데이터

실제적으로 저희가 테이블 생성시 DDL상에 VARCHAR(10)이라고 컬럼 사이즈를 정의해도 SQLITE는 TEXT 타입으로 만들어집니다. 그렇기 때문에 10자 이상의 데이터도 삽입이 가능합니다. 그러니, DDL 문 만들 때 구지 다른 데이터 타입을 외울 필요없이 위 타입만 알고 있으면 될 것 같습니다.

재미있는 것은 Data and Time 즉, 날짜 관련 데이터 타입이 따로 없다는 것입니다. DATETIME은 입력되는 값에 따라서 TEXT, REAL, INTEGER 타입으로 저장됩니다. http://www.sqlite.org/datatype3.html 보시면 어떤 데이터 타입이 어떤 식으로 변경되는지 알수가 있습니다.

이것으로 SQLite User Guide 두번째 포스트를 마치겠습니다. 다음 포스트에서는 SQLite 사용에 꼭 필요한 DDL문(CREATE TABLE, CRATE INDEX 등)에 대해서 알아보도록 하겠습니다.


SQLite는 Android 및 iPhone에 탑재되면서 많이들 사용하고 있는 파일베이스 RDB입니다. 공부를 하는데, 생각보다 많은 자료가 없더군요. 공부하면서 정리한 자료인데.. 많은 도움 되시면 좋겠습니다.

앞으로 제가 포스트할 User Guide의 목차는 다음과 같습니다.

SQLite User Guide 포스트 목차
1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

SQLite의 특징.

Sqlite의 특징을 먼저 살펴보도록 하겠습니다.
1. Zero Configuration – sqlite는 별도의 설치 및 설정이 필요 없습니다. 파일 하나만 있으면 모든걸 할 수 있습니다.

2. Portabiliy – 이식성. 거의 모든 OS에서 다 돌아갑니다. 최대 2 terabytes까지 지원하며, 기본 encoding으로 UTF-8과 UTF-16을 지원합니다.

3. Compactness – 아주 가볍고 작습니다. Header file, library, 관계형 데이터를 다 포함해도 사이즈가 얼마 안됩니다.

4. Simplicity – sqlite는 아주 단순하고 사용하기 편리한 programming library를 제공하고 있으면서도 다양한 언어로 확장이 가능하도록 만들어졌습니다.

그밖에 Flexibility , Liberal Licensing(공짜죠), Reliability, Convenience 등이 나와 있습니다.
에구 좋은 말은 다 있는 것 같습니다.

위 내용은 일반적인 내용인것 같고, 보다 중요한 몇 가지를 짚고 넘어가겠습니다.

SQLite는 ANSI92의 기능을 대부분 지원하지만, 아래의 사항은 지원하지 않습니다.

1. RIGHT and FULL OUTER JOIN : LEFT OUTER JOIN만 지원합니다.

2. Complete ALTER TABLE Support : RENAME TABLE과 ADD COLUMN만 지원합니다.
DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT 등 다른 기능은 지원하지 않습니다.

3. Writing to VIEWs : SQLite에서 View는 read-only입니다.

4, GRANT and REVOKE : Sqlite에서 읽기/쓰기 권한은 OS 파일 시스템 권한을 사용합니다. 
별도로 권한 부여 기능이 없습니다.


SQLite 아키텍처


SQLite의 내부 아키텍처는 위 그림과 같습니다.  코어, SQL 컴파일러, 백앤드가 주요 핵심요소이고, 이외에 액세서리 역할을 하는 유틸리티와 테스트 코드로 이뤄져 있습니다.

GUI Tools for SQLite

SQLite를 지원하는 GUI Tools 리스트는 아래 링크에 나와있습니다.
http://sb2.info/commercial-and-freeware-sqlite-tools-list-2/

무지 많습니다. 그 중 몇개를 골라서 사용해 봤는데.. 제가 추천해 드리고 싶은 툴은 다음과 같습니다.

SQLite Expert (http://www.sqliteexpert.com/)


  
여러 제품 중에서 가장 무난하게 사용할 수 있는 제품인 것 같습니다. 필요한 기능은 다 있고, UI도 그럭저럭 쓸만합니다. Personal Edition과 Professional Edition으로 나누어지며, Personal Edition은 무료입니다.
(향후 SQLite관련 포스트에서 저는 이 제품으로 테스트 하겠습니다.) Pro Edition의 Crack도 그리 어렵지 않게 구할 수 있습니다.

SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/)


아마도 제가 본 Sqlite 관련 툴 중에서 가장 강력한 제품인 것 같습니다. 다른 기능은 몰라도 Reverse로 ERD를 만들어 주는 기능은 이 제품만이 가능합니다.

여담이지만 제가 Touch Call(터치콜) App 개발할 당시 Android SDK2.0 기준의 전화번호부 DB를 이용해야 하는데, 구글의 document만으로는 이해하기가 상당히 어려웠습니다. 출판되어 있는 책들도 다 이전 데이터베이스를 기준으로 작성된 책들이고.. 그때 이 제품을 평가판으로 설치해서 애뮬레이터에 있는 contact2 데이터베이스를 가지고 ERD를 만들어 보니… 훨씬 빠르게 이해를 할 수 있었습니다. 

좋은 제품이긴 하지만 애석하게도 free 버전은 없습니다. 모두 상용버전 밖에 없으며, 한달 Trial 버전만 사용할 수 있습니다. 현재 버전의 crack은 거의 구하기 힘들며, 이전 버전은 구하실 수는 있을 겁니다.

DeZign (http://www.datanamic.com/dezign/index.html)
현재 SQLite를 Forward/Backward Engineering를 완벽하게 지원하는 모델링 툴은 datanamic의 dezign이란 모델링 툴 밖에 없습니다. 근데, 막상 평가판 설치해보니..저희 정서와 맞지 않는 툴이더군요. 저희는 한글로 논리모델을 만들고 영문으로 물리모델을 만드는데, 이 툴은 그런 개념없이 논리/물리가 함께 처리되는 툴이라 바로 지워 버렸습니다. 조금 불편하더라도 DA#이나 ERWin으로 모델링을 하고, Forward Generation한 스크립트를 수정해서 사용하는 게 더 편리할 듯 합니다.

끝으로 앞으로 저의 SQLite 관련 포스트에서 사용할 demo db에 대해서 소개를 하겠습니다. 위에서 얘기했듯이 저는 SQLlite Expert Personal 버전을 이용해서 테스트를 하도록 하겠습니다.

SQLite Expert Personal를 실행한 후 File > Open Demo Database를 실행합니다.
Dbdemos란 데이터베이스가 스키마브라우저에 나타납니다.


좌측 스키마 브라우저에 많은 테이블 리스트가 나옵니다. 저는 그중에서 아래 ERD에 나오는 몇 개의 테이블을 주로 사용해서 DML 테스트 등을 진행하도록 하겠습니다.


고객, 직원, 주문, 주문내역 등 간단한 ERD 구조입니다.
참고로 이 ERD는 Dbdemos SQLlite 데이터베이스를 SQLite Maestro를 이용하여 일부 테이블만 designer에 띄운 모습입니다.)

SQLIte User Guide 첫번째 포스트를 이만 마치며, 다음 포스트에서는 SQLite 의 PRAGMA 명령어 및 시스템 카탈로그 등에 대해서 알아보도록 하겠습니다.

 

  1. BEOM 2010.09.09 11:40

    좋은 정보 감사합니다~

  2. 고냉지채소 2010.11.10 17:52

    좋은 글 감사합니다. 덕분에.. SQLite에 대해서 궁금했던 부분들 많이 해소 했습니다.
    복 받으실꺼에요..^^

  3. 안승진 2011.03.23 11:43

    정말. 잘 배우고 갑니다.^^ 이런 글이 있다니!! 이해도 쉽고, 읽기도 편하고!! 너무 좋아요!

  4. 김병훈 2011.11.22 00:16

    감사드립니다

  5. Miyo 2012.01.13 11:50

    좋은정보 감사합니다~~

+ Recent posts