Postgresql 프로그래밍

Postgresql 프로그래밍

윤 상배

dreamyun@yahoo.co.kr

---

차례

1절. Postgresql 에 대한 소개

2절. Postgresql 프로그래밍

2.1절. Postgresql 설치하기

2.1.1절. 다운받기

2.1.2절. 컴파일 하기

2.1.3절. 환경설정

2.1.4절. 서버실행

2.2절. 프로그램 제작

2.2.1절. 테스트용 DB 만들기

2.2.2절. 어떤 프로그램을 만들것인가

2.2.3절. libpq (C library)

2.2.3.1절. DB 연결/해제관련

2.2.3.2절. 쿼리실행 관련

2.2.3.3절. select 쿼리 결과 정보를 가져오기

2.2.3.4절. select 결과 가져오기

2.2.3.5절. non select 결과 정보 가져오기

2.3절. 예제

3절. 결론

---

1절. Postgresql 에 대한 소개

Postgresql(이하 Psql)은 Mysql 과 함께 공개진영에서 가장 사랑받고 있는 대표적인 RDBMS 이다. 상당히 오래된 역사를 가지고 있으며, 완성도 역시 상당히 높다. 속도를 중요시해서 몇가지 표준적인 기능을 제외시킨 MySql 과는달리, Psql 은 SQL92 의 모든 표준을 지키고 있다.

그런이유로 중규모서 대규모 사이의 데이타를 유지하고 관리하는데 유용하게 쓰일수 있다.

또한 공개이면서도 SQL92 표준을 준수하며, 비교적 쉽게 접근가능하다는 이유때문에 교육용으로써의 용도로도 훌륭하게 사용할수 있으며, 그런이유로 필자의 경우 SQL 을 배우기 위한툴(교육용)로 mysql 보다는 Psql 을 추천한다. psql 에 대한 자세한 내용은 PostGreSQL 사이트 와 DB 사랑넷 을 참고하기 바란다.

---

2절. Postgresql 프로그래밍

2.1절. Postgresql 설치하기

보통 필자의 기사는 프로그래밍 자체에 촛점을 맞추고, 그 환경을 만드는 것에 대해서는 다루고 있지 않지만, 특별히 Pgsql 에 대해서는 설치하는것과 환경만드는 것까지 다룰것이다. 이유는 개인적으로 Pgsql 을 무척 좋아하기 때문이다.

---

2.1.1절. 다운받기

이 글에서는 source 파일을 이용한 설치방법에 대해서 다룰것이다. 먼제 http://www.ca.postgresql.org/ftpsite 에서 최신버젼의 psql 을 다운받도록 한다. 최신버젼인 postgresql-7.2.2.tar.gz 를 다운받도록 하자.

다운받았다면 적당한 디렉토리로 옮겨서 압축을 풀도록 한다. Linux 에서는 관례적으로 쏘쓰파일의 컴파일은 /usr/src 에서 한다.

[root@localhost src]# tar -xvzf postgresql-7.2.2.tar.gz........

---

2.1.2절. 컴파일 하기

일반적인 ./configure 스크립트실행 -> make -> make install-> 를 따른다.

우선 ./configure 스크립트를 이용해서 자기 환경에 맞도록 Makefile 을 생성시켜줘야 한다. ./configure 를 실행할때 여러가지 옵션을 줌으로써 pgsql 의 환경에 변화를 줄수 있는데, 기본적으로 멀티바이트를 지원할수 있어야 하며, odbc 지원과, perl, python, c++ 등을 위한 프로그래밍 인테페이스를 지원하도록 만들것이다. 그러므로 대충 아래와 같은 스크립트 실행화면을 보여줄것이다.

[root@localhost src]# ./configure --enable-multibyte --enable-locale \> --enable-nls=ko --with-CXX --with-python --enable-odbc......

이제 Makefile 이 만들어졌을것이다. 그럼 make 를 이용해서 쏘쓰를 컴파일 하도록 한다. 완료되었다면 make install 을 이용해서 설치를 종료한다.

---

2.1.3절. 환경설정

설치를 마쳤다면 pgsql 을 가동시키기 위한 몇가지 환경설정을 해주어야 한다.

RDBMS 서버를 root 로 실행시킬경우 여러가지 보안문제등을 일으킬수 있음으로, pgsql 은 이를 원천적으로 봉쇄하고 있다. 반드시 DB관리를 위환 일반유저를 하나 생성한다음에, 이 유저를 통해서 서버관리를 해야만 한다. 일반유저를 하나 만들도록 하자 이름은 관리자 마음인데, 필자는 postgres 이름으로 만들었다.

[root@localhost src]# adduser postgres[root@localhost src]# passwd postgresChanging password for user postgresNew UNIX password: ...

유저 생성을 해주었다면 이제는 pgsql 이 데이타들을 관리할 (테이블과, 테이블내용들이 파일로 정리되는) 디렉토리를 만들어주고 이 디렉토리에 대한 권한 설정을 해주어야 한다.

우리는 pgsql 을 컴파일할적에 어디에 설치될지 디렉토리를 지정해 주지 않았다. 이럴경우에는 기본디렉토리로 설정되는데, 기본 디렉토리는 /usr/local/pgsql 이다. 그러니 데이타베이스 파일이 쌓이는 디렉토리는 /usr/local/pgsql/data 로 하도록 하겠다. 디렉토리를 만든다음에 소유자를 postgres 로 해주고, 보안을 위해서 권한을 700 으로 하도록 하자.

[root@localhost src]# mkdir /usr/local/pgsql/data[root@localhost src]# chown postgres.postgres /usr/local/pgsql/data[root@localhost src]# chmod 700 /usr/local/pgsql/data

만들어진 postgres 유저로 변경한다음에, DB 관리자로써 필요한 환경설정을 하도록 하자.

우선은 실행파일 경로를 지정해줘야 한다. 실행파일은 /usr/local/pgsql/bin 이다. ".bash_profile" 을 편집해서 실행파일 찾기 경로, 라이브러리 찾기경로를 지정해 주어야 한다.

PATH=$PATH:/usr/local/pgsql/binLD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/libexport LD_LIBRARY_PATH PATH

.bash_profile 를 수정했다면, 다시 로그인을 하던지, source 명령을 이용해서 환경변수를 다시 등록시키도록 하자.

[root@localhost test]# source /root/.bash_profile

---

2.1.4절. 서버실행

서버 실행을 위해서는 root 가 아닌 일반계정자로 변환해야 한다. DB 관리자를 위해서 postgres 를 만들어 두었으니 postgres 로 유저 변경하도록 한다.

[root@localhost src]# su - postgres

스위칭 유저를 했다면 DB 초기화를 해야한다. DB 초기화라고 해서 머 거창하게 어려울것으로 생각할 필요 없다. 그냥 initdb 를 한번만 실행시켜주면 된다.

[postgres@localhost src]$ initdb .......Success. You can now start the database server using: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/dataor /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start[postgres@localhost src]$ postmaster -i&

그다음에 postmaster 를 이용해서 서버를 띄우면 된다. 이때 인터넷 서비스를 가능하게 하고 싶다면 "-i" 옵션을 줘야 한다.

---

2.2절. 프로그램 제작

이제 psql 의 설치를 마쳤음으로 pgsql 에서 제공하는 c/c++ API 를 이용해서 프로그래밍을 해보기로 하자.

---

2.2.1절. 테스트용 DB 만들기

프로그램을 만들려면 무언가 엑세스가 가능한 자료가 있어야 할것이다.

이번 프로그램에서 사용될 DB정보는 우편번호로 하기로 하겠다. 우편번호 데이타는 Kylix3 를 이용한 어플리케이션제작 에서 이용해던데이타를 제사용하도록 하겠다.

먼제 createdb 를 이용해서 post 라는 이름의 데이타베이스를 만들도록 하자.

[postgres@localhost src]$ createdb post CREATE DATABASE

그다음에 우편번호 데이타를 입력해야 할건데, 이것은 위의 kylix3 를 이용한 어플리케이션 제작의 문서를 참고하기 바란다.

---

2.2.2절. 어떤 프로그램을 만들것인가

만들 프로그램은 "Kylix3 를 이용한 어플리케이션 제작" 에서 만든 GUI 버젼의 프로그램을 pgsql 에서 제공하는 C API 를 이용해서 제작성한 프로그램으로, 단순히 리스트만 보여주는 데에서 탈피하여 사용자가 원하는 지역의 우편번호를 찾을수 있도록 인터페이스도 제공할것이다.

그렇지만 완벽한 프로그램을 만들지는 않을것이다. 단지 이런식으로 pgsql API 를 이용해서 프로그램제작이 가능하다 라는 정도만을 보여주는 수준으로 제작하게 될것이다.

---

2.2.3절. libpq (C library)

pgsql 은 C 프로그래밍 인터페이스를 위한 libpq C 라이브러리를 제공한다. 이 라이브러리의 위치는 /usr/local/pgsql/lib 에 위치하고 있다. 이번장에서는 libpq 에서 제공하는 기본적인 몇가지 함수에 대해서 알아볼 것이다.

그렇지만 모든 함수에 대해서 설명을하진 않을것이며, 단지 자주 사용되는 중요한 몇몇함수에 우선순위를 두고 설명할것이다. 다른 여러가지 함수들에 대해서 알고 싶다면 /usr/local/pgsql/doc/html 아래에 있는 html 문서들을 참고하기 바란다.

---

2.2.3.1절. DB 연결/해제관련

DB연결과 관련되어서는 다음과 같이 2가지의 중요한 함수들이 제공된다.

#include <libpq-fe.h>PGconn *PQsetdb(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName);PGconn *PQsetdbLogin(char *pghost, char *pgport, char *pgoptions, char *pgtty, char *dbName, char *login, char *pwd);

2개 함수는 같은일을 한다. 다만 Login 이 붙은 함수의 경우 권한을 획득하기 위해서 로긴이름과 패스워드를 입력해야할 필요가 있을때 사용한다.

PQsetdb 의 경우는 권한획득이 필요 없을 경우 예를 들자면 모두에게 열린 db 나 혹은 postgres 와 같은 DB관리자 권한을 가지고 있을때 사용할수 있을 것이다.

리턴값은 DB 연결을 가르키는 PGconn 이라는 DB 연결 정보 객체이다.

아규먼트는 각각 접근하고자 하는 "호스트", "포트", "옵션", "디버깅용 tty", "데이타베이스 이름", "로기인 ID", "패스워드" 를 나타낸다. pgsql 은 포트로 5432 를 사용한다.

연결을 했다면 DB 연결을 끊어주는 함수도 있어야 할것이다.

PQfinish(conn);

아규먼트로는 PQsetdb(혹은 PQsetdbLogin)을 통해서 얻은 PGconn 값을 넣어주면 된다.

libpq 는 각각의 함수실행에 대해서 그결과를 알수 있는 별도의 함수를 제공한다. 이경우에는 DB연결이 제대로 이루어졌는지, 제대로 이루어지지 않았다면 어떤 문제가 있는지를 알려주는 연결검사 함수를 제공한다.

ConnStatusType PQstatus(const PGconn *conn);

예를들어서 연결에 문제가 있는지 확인해 보고 싶다면

if (PQstatus(conn) == CONNECTION_BAD){ fprintf(stderr, "%s", PQerrMessage(conn)); exit(0);}

과 같은 방법으로 연결 체크를 할수가 있다.

---

2.2.3.2절. 쿼리실행 관련

여기에서는 Query 를 실행하고 그 값을 가져오는 함수들에 대해서 알아보도록 하겠다.

Query 를 실행하기 위한 가장 간단한 함수는 PQexec 이다.

PGresult *PQexec(PGconn *conn, const char *query);

사용방법은간단하다. PQsetdb 등의 함수를 통해 넘겨받은 연결정보를 첫번째 아규먼트로 하고, 2번째 아규먼트에 실행시키고자하는 쿼리를 입력 하면된다.

또한 PQresultStatus() 함수를 사용해서 쿼리를 실행한 결과에 대한 상태를 알아낼수 있다.

ExecStatusType PQresultStatus(const PGresult *res);

다음과 같은 방법으로 알아낼수 있을것이다.

//쿼리를 성공적으로 수행해서 데이타 내용을 가져왔는지 if (PQresultStatus(res) != PGRES_TUPLE_OK){}// 비어있는 쿼리를 보냈을경우if (PQresultStatus(res) != PGRES_EMPTY_QUERY){}// 명령을 성공적으로 수행했을경우 // 단 이것은 데이타를 가져오는 쿼리가 아닌 명령들에 대한 결과를 알고 싶을때. if (PQresultStatus(res) != PGRES_COMMAND_OK){}// 치명적인 오류가 발생했을경우 // 쿼리를 잘못내렸거나, 없는 테이블로 부터 자료를 요청하는등 ..if (PQresultStatis(res) != PGRES_FATAL_ERROR){}

또한 PQresultERRorMessage() 함수를 이용함으로써, 쿼리에 대해서 정확하게 어떠한 문제가 발생했는지를 알아낼수 있다.

if (PQresultStatis(res) != PGRES_FATAL_ERROR){ fprintf(stderr, "%s", PQresultErrorMessage(const PGresult *res)); }

더이상 쿼리결과 발생한 데이타를 사용할일이 없다면, 반드시 PQclear() 함수를 이용해서 메모리 해제(free) 시켜줘야 한다. 만약 그렇지 않다면 메모리 누수가 일어날수도 있다.

void PQclear(PQresult *res);

---

2.2.3.3절. select 쿼리 결과 정보를 가져오기

select 쿼리를 이용해서 데이타를 가져왔을경우, 몇개의 데이타를 가져왔는지, 가져온 데이타는 몇개의 필드로 이루어져 있는지, 등을 알아내야 할것이다.

PQntuples 를 이용해서 우리가 내린 쿼리의 결과값이 몇개인지 알아낼수 있다.

int PQntuples(const PGresult *res);

PQnfields 를 이용해서 가져온 데이타가 몇개의 필드로 이루어져 있는지 확인할수 있다.

int PQnfields(const PGresult *res);

각 필드의 필드명을 가져올수 있다.

char *PQfname(const PGresult *res, int field_index);

각필드가 몇바이트의 크기를 차지하고 있는지 알려준다. 즉 int 형이라면 4 를 되돌려줄것이다. 만약 필드가 variable 타입이라면 -1 을 되돌려줄것이다.

int PQfsize(const PGresult *res, int field_index);

---

2.2.3.4절. select 결과 가져오기

이제 select 한 결과를 가져오기로 하자. PQgetvalue 를 이용해서 쿼리 결과에 대한 값을 가져올수 있다.

int PQgetvalue(const PGresult *res, int tup_num, int field_num);

아규먼트는 PGresult 객체와, 가져오고자 하는 tuple 의 인텍스 번호 그리고 가져오고자 하는 field 의 index 번호이다. 인덱스 번호는 배열과 마찬가지로 0번부터 시작한다.

그리고 PQgetisnull 를 이용해서 데이타가 있는지 없는지 확인할수 있다.

int PQgetisnull(const PGresut *res, int tup_num, int field_num);

만약 tup_num 의 field_num 에 해당되는 데이타가 없다면 1을 반환할것이다. 이것은 필드의 값이 NULL 일경우 1 을 돌려주지 않는다는 점을 유의해야 한다. tup_num 이 field_num 자체가 없을경우, 즉 잘못된 tuple 인덱스번호 (tuple 최대 크기 번호를 초과했을경우) 등을 참조할경우 1 을 돌려준다. 이러한 특성을 사용해서 PQgetisnull 을 사용해서, 1이 될때까지 계속 증가시키면서 원하는 데이타를 가져올수 있을것이다.

int i = 0;...while(PQgetisnull(res, i, 0) != 1){ printf("%s\n", PQgetvalue(res, i, 0); i++;}

물론 이방법대신에 PQntuples() 함수를 이용해서 데이타를 가져올수도 있을것이다.

PQgetlength() 를 이용하면 가져온 데이타의 실제 길이를 byte 단위로 돌려준다.

int PQgetlength(const PGresult *res, int tup_num, int field_num);

---

2.2.3.5절. non select 결과 정보 가져오기

select 는 직접 데이타를 가져오는 쿼리지만 그렇지 않은 쿼리들도 있다. insert, update, delete 등이 이러한 쿼리들인데, 데이타를 가져오지 않는 대신에, insert 일경우라면 insert 가 제대로 되었는지, update 라면 몇개의 데이타가 update 되었는지 등의 정보가 중요할것이다.

만약 insert, update, delete 등의 명령을 사용했고, 몇개의 데이타가 이 쿼리에 의해서 적용이 되었는지 알고 싶다면 PQcmdTuples() 함수를 사용하면 된다.

char * PQcmdTuples(const PGrsult *res);

함수가 좀 이상해 보일것이다. 언뜻 생각해보면 적용되는 데이타의(tuple)의 수를 넘겨주는 것이므로, 리턴값이 int 형이여야 할것같은데, char * 형이다. 왜 char * 로 했는지 필자로써도 알수는 없지만, 어쨋든 char * 형으로 넘어온다. 그래서 어쩔수 없이 atoi() 등의 함수사용도 해야 할것이다.

또한 PQcmdStatus() 함수를 이용해서 쿼리 상태를 검사할수도 있다.

char * PQcmdStatus(const PGresult *res);

---

2.3절. 예제

다음은 간단한 예제이다. 매우 간단하지만 어떻게 연결하고 쿼리결과를 가져오며, 에러를 처리해야 하는지에 대한 기본적인 내용을 담고 있다.

예제 : zipcode_psql.c

// libpq-fe.h 를 만드시 추가시켜야 한다. #include <libpq-fe.h>#include <nistd.h>#include <stdlib.h>#include <stdio.h>int main(int argc, char **argv){ PGconn *conn; PGresult *res; int field_num; int row_num; int i; char query[128] = "select * from zipcode"; char *pghost, *pgport, *pgoptions, *dbname, *pguser, *pgpass; pghost = "localhost"; pgport = "5432"; pgoptions = NULL; dbname = "post"; pguser = "yundream"; pgpass = "1234"; // psql 데이타 베이스에 연결한다. conn = PQsetdbLogin(pghost, pgport, pgoptions, NULL, dbname, pguser, pgpass); // 만약 연결에 오류가 발생했다면 // 오류메시지를 출력한다음에 종료한다. if (PQstatus(conn) == CONNECTION_BAD) { fprintf(stderr, "%s", PQerrorMessage(conn)); exit(0); } // 아규먼트가 있다면 sido 와 dong 이름으로 like 검색을 한다. // 그렇지 않을경우 모든 우편번호 리스트를 출력한다. if (argc == 2) sprintf(query, "%s where sido like '%%%s%%' or dong like '%%%s%%'", query, argv[1], argv[1]); // 쿼리를 발생시킨다. res = PQexec(conn, query); // 쿼리가 제대로 실행되었는지 확인한다. // 제대로 실행되지 않았다면. // res 를 clear 시키고 연결을 끊고 종료한다. if (PQresultStatus(res) != PGRES_TUPLES_OK) { PQclear(res); PQfinish(conn); exit(0); } // 몇개의 tuple 를 얻어왔는지 검사한다. row_num = PQntuples(res); // 루프를 돌면서 값을 출력한다. for (i = 0; i < row_num; i++) { printf("%s %s %-6s %-40s %s\n", PQgetvalue(res, i, 1), PQgetvalue(res, i, 2), PQgetvalue(res, i, 3), PQgetvalue(res, i, 4), PQgetvalue(res, i, 5)); } // 쿼리 결과를 위한 res 객체를 free 시켜주고 // psql 서버와 연결을 끊은후 종료한다. PQclear(res); PQfinish(conn); return 1;}

컴파일 방법은 다음과 같다.

[root@localhost test]# gcc -o zipcode_psql zipcode_psql.c \> -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lpq

아규먼트를 주지 않을경우 전체 우편번호 리스트를 출력하며, 아규먼트가 주어질 경우 도시와 동 이름으로 일치하는 우편번호 리스트를 출력한다.

컴파일이 실행되었는데도 불구하고 에러가 발생한다면, psql 서버가 떠있지 않거나 libpq 를 찾기위한 패스 경로가 지정되어 있지 않기 때문일것이다. /etc/ld.so.conf 에 패스경로를 추가시키거나 혹은 LD_LIBRARY_PATH 에 lipq 경로를 추가하기 바란다. LD_LIBRARY 패스 추가 방법은 2.1.3절 를 참조하기 바란다.

---

출처: www.joinc.co.kr

[출처] http://blog.naver.com/mickey109/60006900963