pl/java 로 Trigger 만들기

PostgreSQL 에서는 Procedural Language 로 Java를 사용하여 function 이나 trigger를 구성할 수 있습니다. 이를 사용하기 위해 윈도우에서는 설치할 때, pl/java 를 선택하여 설치하면 사용할 수 있습니다.

기본적으로 eclipse에서 java code를 작성하기 때문에, 일단,
PostgreSQL설치폴더/share/pljava에서 deploy.jar, examples.jarpljava.jar 등의 jar파일을 이클립스 프로젝트 폴더로 가져와 library path 에 추가합니다. 이 파일들은 http://pgfoundry.org/projects/pljava/ 에서 따로 배포하고 있기도 합니다. 이 pl/java 프로젝트 페이지에서 위 jar파일의 src파일도 다운받을 수 있습니다. 이클립스에서 소스 어탯치해서 보면 pl/java의 사용방법을 금방 익힐 수 있습니다. 기본적인 문서는 PostgreSQL설치폴더/doc/PlJava 폴더에 들어 있습니다.

pl/java 등의 Procedural Langauage를 사용하기 위해서는 대상 데이터베이스에서 사용할 수 있도록 install 해주아야 합니다. pl/java를 install하는 방법은 두 가지가 있습니다.
1. PostgreSQL설치폴더/share/pljava에 들어있는 install.sql을 실행시키는 방법
2. deploy.jar로 배포되어 있는 Deployer 클래스를 사용하는 방법

일단 pl/java를 사용하기 위해서는 OS의 path에 jvm.dll이 들어 있는 경로가 잡혀 있어야 합니다. 다른 Procudural Langauage를 사용할 때도 비슷한 과정이 필요합니다. 이 경로를 잡을 후에는 아마 PostgreSQL을 다시 시작해주어야 합니다.

다음 pgAdminIII에 딸려 있는 Query Tool 등을 이용해서 install.sql을 실행해주면, 'sqlj' schema가 생성된 것을 확인할 수 있습니다. 사실 schema라는 단위가 좀 생소한데, Documentation 5.7 Schemas  에 관련 내용이 잘 설명되어 있습니다.

일단 트리거 함수를 자바 코드로 작성하겠습니다.

INSERT 문이 row-level에 실행되기 전에, 자동으로 실행될 modInsert라는 trigger를 작성해보겠습니다. 대상 테이블에는 creator라는 열이 있다고 가정합니다. PostgreSQL문서의 trigger부분을 읽어보시면 금방 이해가 가시겠지만, INSERT문의 경우, 새로 열이 삽입되므로, old row는 없고, new row 만 있습니다. INSERT 문에 의해 테이블에 새로 입력될 열이 new row입니다. 이 row는 TriggerData라는 클래스의 gerNew() method에 의해 접근할 수 있습니다.
위의 코드에서 td.getNew()를 통해 new row 가 저장되어 있는 ResultSet의 reference를 가져오고 있습니다. td.getArguments() method를 통해서는 대상 테이블의 column의 이름들을 가져올 수 있습니다. str에 creator라는 column에 새로 삽입될 문자열을 가져오고 'modified'을 후미에 붙이도록 수정합니다.
그리고 코드의 서두에서는 Trigger함수를 몇가지 경우에 TriggerException을 발생시켜 trigger가 원하는 경우에는 동작하도록 합니다.

원하는 trigger함수를 작성하고 난 다음에는 jar파일로 만들어야 합니다.
example.jar파일과 그 소스, 그리고 User Guide문서를 보면 자세히 알 수 있습니다.

example.jar에 들어있는 것처럼, MANIFEST.MF파일과 test.drr파일을 작성하여 test.jar파일을 만들었습니다.

다음 과정은 이 트리거 함수를 등록하는 것입니다.
test.drr파일의 내용은 다음과 같습니다.

SQLActions[] = {
    "BEGIN INSTALL
        CREATE FUNCTION modInsert()
          RETURNS trigger
          AS 'ac.uos.dml.blob.trigger'
          LANGUAGE java;
         
        CREATE TRIGGER metadata_modInsert
          BEFORE INSERT ON metadata
          FOR EACH ROW
          EXECUTE PROCEDURE modInsert();
         
    END INSTALL",
   
    "BEGIN REMOVE
        DROP TRIGGER metadata_modInsert;
        DROP FUNCTION modInsert();
    END REMOVE"
}

이 test.drr파일이 들어있는 test.jar파일을 PostgreSQL이 접근할 수 있는 곳에 놓은 다음
sqlj schema의 install_jar function을 이용해 install하게 됩니다.

SELECT sqlj.install_jar('file:///E:/Program Files/PostgreSQL/8.3/share/pljava/test.jar', 'test', true)

install_jar의 세번째 argument가 true이므로, jar파일 내부의 ddr파일에 의해서 설치됩니다.
그리고 set_classpath function을 통해서 해당 스키마가 설치된 jar파일을 사용할 수 있도록 해주어야 합니다.

SELECT sqlj.set_classpath('public', 'test')

이제 trigger를 테스트해볼 수 있습니다.