Programming with SQL

원본출처 : http://myscan.org/~mahajjh/article/sql_programming.html

1998년 4월 28일 13시

written by 전정호

이 자료는 scan을 위한 것으로 신입생들에게 필요 이상이라고 생각되는 부분은 과감히 생략되었습니다. 참고로 이 글은 몇 부분을 제외하고는 생각나는 데로 적은 관계로 정정해야 할 사항이 있을 수도 있습니다. 정정사항은 빨리 알려주세요.

내용

1.개요

2.postgresql

3.SQL

4.Programming with postgresql in c

5.Programming with postgresql in Java

1. 개요

프로그래밍을 할 때, 아주 간단한 프로그램(학교 숙제 정도)이 아닌 경우 대부분 많은 자료를 처리해야 한다. 이런 작업을 각각 프로그램에서 독립적으로 처리하는 것은 여러 가지 면으로 비효율적이다. 그래서 우리는 Postgresql, Oracle, MS SQL 등 database management system(이하 DMS)을 쓰게 된다. 이것을 써서 우리는 다음과 같은 이득을 얻게 된다.

독립적으로 자료관리 루틴을 작성할 필요가 없다.

대부분의 경우 우리가 작성한 코드보다 효율적이다.

데이터만의 관리(백업 등)가 편해진다.(프로그램에서 각각 interface를 작성할 필요가 없다.)

이러한 장점에도 불구하고 전에는 이런 database간의 호환성이 없어서 불편하였다. 이런 불편을 없애고, 이런 database간의 표준 interface를 만들려는 노력이 계속되었고 그 결과 SQL(structured query language)가 생겨나게 되었다. 우리는 이 언어를 배움으로써 거의 대다수의 DMS를 쓸 수 있게 된다.(물론 DMS가 자체 query language를 가지고 있더라도)

우리는 이제 공개용으로 많이 쓰이고 있는 postgresql을 가지고 SQL을 배워보자.

2. postgresql

먼저 postgresql을 설치하면 (http://www.postgresql.org) 현재 최신버전은 6.3.2이다. laplace에는 6.3이 gauss(printing) 6.1이 설치되어있다.) psql이라는 프로그램이 설치된다. 이 프로그램은 사용자가 쉽게 database을 다룰 수 있는 interface를 제공한다.(우리는 아래에서 Java나 c언어에서 직접 database를 다루는 방법을 살펴볼 것이다.) 먼저 프로그램을 쓰기 전에 database와 관련된 용어를 알아보자.

database : postgres에서 가장 큰 단위로 소유자가 있으며 이 안에 있는 자료에 접근하기 위해서는 소유자에 의해 부여된 권한이 있어야 한다.

Table : database를 이루고 있는 단위로, 각각 table에도 권한이 있다.

Column : attribute라고도 불리 우며 table안에 있는 자료의 성격이다.

Row : table안에 있는 각각의 자료(instance)이다.

예로 프린팅 관리를 위해 gauss에서 관리되는 database를 살펴보자. 이 database의 이름은 account이며, 이 database안에는 account, change_log, print_log란 table이 있다. account란 table를 보면 다음과 같은 column이 있다.

id char(14)

deposit int

flag int

그리고 각 row는 PC실 사용자들의 자료들이다.

이제 이러한 내용을 하나씩 알아보자.

먼저 psql account라고 하면 account database를 보려고 한다는 것을 알려주는 것이다.(아무 argument가 없을 때는 사용자 아이디와 같은 이름의 database에 접근하려고 한다.)

그러면 다음과 같은 내용이 나온다.

Welcome to the POSTGRESQL interactive sql monitor:

Please read the file COPYRIGHT for copyright terms of POSTGRESQL

type \? for help on slash commands

type \q to quit

type \g or terminate with semicolon to execute query

You are currently connected to the database: account

account=>

여기서 마지막 줄에 account=>란 prompt는 지금 다루고 있는 database 이름이 account라는 것을 말한다. 여기서 '\?'를 치면 도움말이 나온다. 다시 말하자면 이 글은 reference가 아니다. 그러므로 이 글에서 다루어지지 않은 내용도 많다.(참고로 postgresql에 대한 제반 문서들은 postgresql 소스의 doc 디렉토리에 있다. laplace에서 ~postgres/postgresql-6.3/에 소스가 있다.)

여기서 '\dt'를 치면 이 database 안에 있는 모든 table의 이름이 나온다. 각 table에 대한 자세한 내용을 보고 싶으면 '\d table_name'을 치면 나온다. 그리고 psql을 빠져나오려면 '\q'를 입력하면 된다.

그러면 이제 새로운 database를 만들어보자. 새로운 database를 만들기 위해서는 shell에서 'createdb database_name'을 입력하면 된다. 이제 psql을 실행하여 우리가 만든 database를 건드려보자.

[B]3. SQL

이제 create, insert, select, update 같은 SQL 문장을 배워보자.

먼저 우리가 만든 database안에 table을 만들어야 한다. 이 글에서는 간단한 예를 들어 설명한다.

먼저 city란 table을 만들어보자.

create table city (
		Name		char(20),
		Temp		int
);

이제 한 줄 씩 살펴보자. 먼저 'create table'은 table을 만든다는 것이고, 그 뒤에 우리가 만들고자 하는 table인 city가 온다. 이제 city table의 각 column을 설정해야되는데, 이는 column_name column_type의 형식으로 표현된다. 먼저 name은 column_name이로 도시 이름을 나타낸다. Char(20)은 앞에서 말한 name의 type으로 영문으로 20자 까지의 문자 데이터를 담을 수 있다는 것이다. 아래의 temp는 도시의 온도를 나타내기 위한 column이고 타입으로는 int(더 정확히 하면 int4, 4byte를 차지하는 int), 즉 정수(integer)로 정했다. 이 외에 올 수 있는 타입으로는 float(소수), text(긴 글), date(날짜) 등이 있고, 자신이 원하는 타입을 정의할 수 도 있다. (더 자세한 내용은 해당 document를 참조) 마지막으로 모든 SQL문장은 위와 같이 ';'으로 끝난다.(postgresql에서는 column 이름 같은 곳에 한글문제가 있다. 물론 이에 대한 패치가 존재하지만 아직 적용하지는 않았다. 물론 데이터의 한글은 문제가 안 된다.)

이제 이 table에 데이터를 넣어보자.

insert into city values ('seoul', 30);

이 문장에서 'insert into'는 바로 다음에 나오는 table인 city에 자료를 입력한다는 뜻이다. 'values' 뒤에 ('seoul', 30)은 'seoul'을 name에 (일반적으로 문자 데이터는 '''을 사용하여 막아준다.) 30은 temp에 입력한다는 뜻이다. 이 문장도 마찬가지로 ';'을 사용하여 끝마쳤다. 이제 다른 자료도 넣어보자.

insert into city values ('pusan', 35);

insert into city values ('inchon', 25);

insert into city values ('chung-jin', 10);

(참고로, psql은 bash 처럼 GNU의 readline library를 사용하여 방향키를 이용한 history기능을 지원한다.)

이제 입력한 자료를 찾아보자. 이를 위해 SQL에는 select문장이 있다.

우선 제일 간단하게 'select * from city;'를 입력해보자. 아직까지 입력한 데이터가 다 출력될 것이다. 이제 우리가 원하는 데이터만 얻어보자. 이를 위해 'where'문을 사용하면 된다. 아래의 예들을 보자.

select * from city where temp >= 30;

(온도가 30도 이상인 자료만)

select * from city where temp >= 10 and temp < 30;

(온도가 10도 이상 30도 미만인 자료만)

select name from city where temp < 20;

(온도가 20도 미만인 도시의 이름만)

위에서 보다시피 where문에는 and, or와 같은 문장도 쓸 수 있다.

위에서 살펴본 것을 정리하면 'select (출력조건) from table_name where (찾는 조건);'과 같은 문법임을 알 수 있다. 이외에 select문에 대해서는 관련 문서를 참고하라.(order by, distinct, 복수 table 검색 등)

이제 입력한 자료를 변경해 보자. 이를 위해 update문이 쓰인다.

update city set temp = 32 where name = 'seoul';

위 문장은 'seoul'이란 도시의 온도를 30에서 32로 바꾸는 문장이다. 이와 같이 where뒤에는 바꾸고자 할 자료(1개가 아닐 수도 있다)의 조건 set뒤에는 바꿀 자료의 값이 오면 된다.(여러 값을 바꿀 경우 이를 ','로 연결하면 된다.)

이제 간단한 SQL문장들을 살펴봤다. 더 자세한 내용(grant, time stamp, large object 등)은 web에 있는 SQL관련 문서를 찾아보거나 (예 sql_ref.html), psql에서 '\h 모르는 명령어'를 치면 간단한 사용법을 알 수 있다. ('\?'은 SQL문장이 아니라 psql 자체 명령에 대한 도움말이다.)

마지막으로 만든 table을 삭제하는 명령은 'drop table table_name'이다.

4. Programming with postgresql in C

이 주제에 대해서는 여러 가지 내용이 올 수 있다. 우선 요즘 많이 알려진 ODBC(open database connectivity)가 있을 수 있다. ODBC는 다루고 있는 DMS와 독립적으로 프로그래밍을 할 수 있는 일종의 환경이다. 프로그래머가 DMS와 상관없이 프로그래밍을 하고 직접 DMS와의 연결 및 관리는 하위 driver수준에서 해결한다는 말이다. ODBC는 windows환경에서 database관련 프로그래밍을 하는데 최선의 선택일 것이다. 최근에 업데이트된 postgresql의 ODBC driver는 많은 좋은 기능을 가지고 있다고 한다. 이 외에도 embedded SQL이라고 해서 C언어의 전처리기(preprocessor)를 이용한 방법도 있다.

그러나 우리는 위의 것 만큼 화려하지 않지만 간단히 사용할 수 있는 libpq 와 libpq++를 사용할 것이다. libpq는 c언어를 위한 library이고 libpq++는 c++언어용이다. 불행히도 이는 postgresql에서 제공하는 것으로 다른 DBMS와 호환되지는 않는다. 그러나 다른 DBMS도 여기서 다룬 내용과 근본적으로 다르지 않으리라고 생각된다. 이외에도 많은 언어(perl, tcl, python 등)에 대한 library가 postgresql 소스와 같이 제공된다.(postgresql소스의 src/interfaces 디렉토리에) 이제 우리는 libpq를 사용하여 postgresql에 있는 자료를 다루는 프로그램을 작성하는 법을 전에 프린트비용을 관리하기 위해서 작성해둔 간단한 프로그램을 통해 배워보자. (원 소스는 여기에 있다. access.c)

아래서 Java를 이용하여 database 프로그래밍을 할 때도 말하겠지만, 프로그램을 크게 3가지 부분으로 나누면 DMS에 접속, SQL문 실행, 결과 가져오기를 들 수 있다.

먼저 관련된 library를 위해 #include “pgsql/libpq-fe.h”를 집어넣고, DMS에 접속부분은 PGconn이란 구조체가 결과는 PGresult란 구조체에서 관리되므로 각각 type에 대한 변수를 선언한다.

	PGconn *connection;
	PGresult *result;

이제 1번째 문제인 DMS에 접속하는 부분을 보자.

	connection = Pqsetdb(NULL,		// pghost
	// pgport
					NULL,		// pgoptions
					NULL,		// pgtty
					“mast”);	// dbName

우선 위의 코드는 다른 프로그램에서는 바꾸어 질 수 있다. 우선 NULL로 지정된 parameter는 모두 default값을 쓴다는 의미이다. 각 parameter의 의미는 주석에 있는 그대로 이다. 첫번째 parameter인 pghost는 postgresql(더 정확히는 postmaster)가 돌아가는 host이며, default값은 localhost이다. 여기서 특별히 주의해야 할 값은 마지막 parameter로 이 곳에는 우리가 다루는 database이름이 나온다. (예로, 앞의 경우에는 “city”가 되어야 한다.) 아래 문장에서 PQstatus(connection) == CONNECTION_BAD 부분은 앞의 연결이 제대로 뒀는지 확인하는 문장이다.

이제 2번째 SQL문장을 실행시키는 법을 알아보자.

이 부분은 아주 쉬운데, 코드에서 처럼 result = PQexec(connection, query); 라고 하면 된다. 물론 query는 char * 타입으로 미리 sprintf와 같은 함수로 우리가 실행하려는 SQL문장을 넣어주면 된다.

마지막 3번째로는 결과값을 읽어내는 것인데, 짐작할 수 있다 시 피, 이 부분이 가장 어렵다.

먼저 앞의 SQL실행 결과로 반환된 PGresult * 타입의 변수를 가지고 모든 작업을 수행해야 된다. 먼저 결과가 제대로 왔는지를 알아야 하는데 이는 PQresultStatus(PGresult *); 함수로 알아낼 수 있다. 이 값이 PGRES_COMMAND_OK나 PGRES_TUPLES_OK가 되면 제대로 된 것이다. PGRES_TUPLES_OK는 select같이 어떤 결과를 얻어오는 경우에 반환되고, PGRES_COMMAND_OK는 이외의 대다수 명령어의 결과로 반환된다. 그럼 이제 select와 같은 명령어를 실행시켜서 얻은 결과를 어떻게 가져오는 가란 문제가 남는다. 우선 제일 간단한 단순히 가져온 데이터를 화면에 출력하는 함수를 알아보자. (이 함수를 실제로 쓰는 경우는 드물 것이다. 그러나 프로그램이 제대로 동작하는지 알아보는데 유용하게 쓰일 수 있다.)

	PQprintTuples(result,
			Stdout,	// output stream
			TRUE,		// print attribute
			TRUE,		// print delimiter bars
			0);		// column width, variable if 0

각각 parameter의 의미는 주석에서 쉽게 알아낼 수 있을 것이다.

이제 결과를 가지고 우리가 필요로하는 정보를 얻는 함수를 하나씩 알아보자.

int PQntuples(PGresult *); 결과를 얻어진 row(개체)의 수

int PQnfields(PGresult *); 결과의 column(attributes)의 수

char *PQfname(PGresult *, int); int타입에 의해 정해진 필드(0 부터 시작)의 이름을 반환한다.

int PQfnumber(PGresult *, char *); char *타입에 의해 정해진 필드의 순서를 반환(위 함수의 반대)

Oid PQtype(PGresult *, int); 해당 필드의 type을 반환(물론 각각 integer로 mapping되어서)

int2 PQfsize(PGresult *, int); 해당 필드의 크기를 반환(변하는 길이의 경우 -1)

char * PQgetvalue(PGresult *, int, int); 해당 tuple(두 번째 parameter)의 해당 필드(세 번째 parameter)의 값을 반환한다.

int PQgetlength(PGresult *, int, int); 해당 tuple의 해당 필드의 길이를 반환한다.

c프로그램에서 특히 주의해야 할 점은 메모리 관리이다. 특히 남이 만든 library를 쓸 때는 그 만든 사람이 의도한 메모리 관리 방식을 따르지 않는 다면 나중에 크게 문제가 될 수 있다. 이는 이 library에서도 예외가 아니다. 이런 일을 하는 함수는 2개가 있는데, 하나는 PQclear(PGresult *);로 이는 결과값을 읽은 후 이 값이 더 이상 필요가 없을 때 불러야 하는 함수이다. 다음은 PQfinish(PGconn *);으로 이는 더 이상 postgresql과 작업을 할 것이 없을 때 부르는 함수이다.

이 외에도 수 많은 함수와 기능(특히 large object와 관련해서)이 있지만 여기서 마치고 더 필요한 내용은 관련 문서를 참고하라.

5. Programming with postgresql in Java

Sun사에서 Java로 database 프로그래밍을 돕기 위해 ODBC의 개념과 유사한 JDBC를 만들었다.(실제로 ODBC driver만 있는 DMS를 위해 ODBC-JDBC-bridge driver도 있다.) 이를 위해 java.sql package가 나오게 되었다. 이에 관련된 자세한 사항들은 http://java.sun.com/products/jdk/1.1/docs/api/Package-java.sql.html 에서 찾아볼 수 있다. 이를 살펴보면 알 수 있는 사실이 대다수의 class들이 interface(즉 abstract class)이며 이를 각 회사에서 제공하는 JDBC driver들이 구현하고 있다는 점이다. postgresql에서는 프로그램 소스에서 /src/interfaces/jdbc/ 디렉토리 안에 JDBC driver를 제공하고 있다.이를 jdk의 CLASSPATH에 넣거나 같은 디렉토리에 두면 된다. (편의를 위해 postgresql.jar, 참고로 이 파일은 순수하게 Java로 되어있다.)

앞에서 c로 프로그램한 것과 차이를 들자면 2가지 정도를 들 수 있다. 먼저 Java가 OOP언어인 점에서 각 함수의 명시적인 parameter(예, PGresult *)가 하나 준다. 둘째로, 앞에서 필드의 이름, 타입 등을 가져오는 함수들이 metadata로 분류되어 있다는 점이다.

먼저 c에서 하던 방식처럼 3가지 과정을 거쳐보자.

먼저 database에 접근하려면, import java.sql.*;를 프로그램 앞에 포함시키고, Class.forName(“postgresql.Driver”);와 같이 JDBC driver를 로딩시킨다. Java에서도 database 프로그래밍을 위한 타입이 있는데, Connection, Statement, ResultSet 등이다. 이중 하나인 Connection을 이용하여,

	Connection conn;
	conn = DriverManager.getConnection(String url,
			String usrname, String passwd);

와 같이 한다. 여기서 url은 localhost인 경우 "jdbc:postgresql:database_name"과 같으면 되고 아닌 경우에는 "jdbc:postgresql://remote_host_address/database_name"과 같으면 된다. 그리고 다음을 위해서

	Statement stmt;
	stmt = conn.createStatement();

을 하여 미리 Statement를 만들어 둔다.

이제 2번째로 SQL을 실행하는 것을 알아보자.

Boolean b = stmt.execute(String SQL_statement);

위와 같이 SQL문장을 실행할 수 있다.(실제로는 execute로 시작하는 함수가 몇 있다.)

마지막으로 결과를 가져와보자.

앞 처럼, SQL문장을 실행한 후, 문제가 없는 경우 결과를 얻어올 수 있다.

	ResultSet rs = stmt.getResultSet();

를 수행하여 우선 ResultSet을 얻은 후, 각 tuples마다 rs.next();를 호출하면 된다.(여러 tuples이 얻어진 경우)

얻어진 데이터에서 우리가 필요한 데이터를 가지고 오기 위해서는 getTYPE(int)나 getTYPE(String)을 사용하면 된다. 여기서 TYPE부분은 실제로 Java의 타입으로 getDouble(), getInt(), getString()과 같은 파일명이 나올 수 있다는 뜻이다. 그리고 int를 parameter로 받는 함수는 필드의 순서(1부터 시작)를 나타내고 String을 parameter로 받는 함수는 필드의 이름을 뜻한다.

이제 앞에서 간단히 말한(그리고 더 자세히 말을 안 할) metadata에 대해서 알아보자. metadata란 말 그대로 실제 data가 아닌 일종의 가상 data이다. JDBC에서는 DatabaseMetaData와 ResultSetMetaData가 있는데 이는 각각 database와 ResultSet의 자료를 얻을 수 있는 방법을 제공하고 있다. DatabaseMetaData는 DMS에 대한 여러 정보를 가지고 있고, Connection 타입의 getMetaData() method를 통해 얻을 수 있다. ResultSetMetaData는 각 필드들에 대한 정보(타입, 이름 등)를 가지고 있고, ResultSet 타입의 getMetaData() method를 가지고 얻을 수 있다. 실제로 쓸 일이 있을 경우에 한번씩 살펴보면 된다.

마지막으로 database 프로그램 도중 발생할 수 있는 Exception을 알아보자. 발생할 수 있는 Exception으로는 2가지가 있는데 우선, ClassNotFoundException은 앞에서 postgresql JDBC driver를 로딩할 때 해당 driver가 없는 경우에 일어난다. 두 번째로, SQLException이 있는데 이는 SQL문장 실행 시 발생할 수 있는 일반 에러이다.

이로 간단히 postgresql을 사용한 database 프로그래밍에 대해 살펴보았습니다. 당장 다음 강좌로는 실제로 이를 이용한 프로그램작성 (ex. mp3 search engine; 물론 c나 Java로 간단한 socket programming과 c를 이용한 cgi programming과 함께)이나 internet server programming in Java 등을 할 수 있을 것 같습니다. 혹시 좋은 의견 있으시면 알려주세요. 이제 시험도 대충 지난듯하네요. 기린이가 하는 Perl강좌 외에도 scanner(?)들(특히 97들)이 각각 공부한 것들을 서로 나누었으면 좋겠습니다.