Slow walking man

앞선 포스트에서 노원구 주민 테이블을 가지고 이야기 했으니, 편의상 요번에도 주민 테이블을 가지고 예를 들어 보겠다..

상황은 이렇다.. 어느날 노원구에서 살고 있던 수지가 동작구로 이사를 간다고 했다. 이것을 테이블 관점에서 본다면, 노원구 주민 테이블에서 수지를 빼고, 동작구 주민 테이블에 수지를 넣는 작업이 진행되어야 한다.

1 DELETE FROM 노원구 주민 테이블 WHERE 주민테이블.주민이름='수지';

2 INSERT INTO 동작구 주민 테이블 VALUES ('수지');

그런데 이런 상황이 발생해 버리면 어떨까?

1번 상황을 진행하는 동안 알수 없는 에러로 1번줄을 수행하지 못하고 2번과정으로 넘어가 동작구 주민 테이블에 수지가 추가되었다. 그러면 이제 가상의 수지 한명이 동작구에 살게 된다. 그러나 이 동작구 수지의 실체는 없다.

뭐 이런건 단순한 실수로 넘어가 줄 수 있겠지만, 은행이라면 어떨까? 잘못된 오류의 금액이 몇억원이라면?

이런 문제점을 해결하기 위해 TRANSACTION은 복수의 명령어로 이뤄진 쿼리를 모두 실행하고 오류가 없을 때 비로소 실제 DB에 적용한다. 실행 도중에 하나의 오류라도 발견되면, 전체 과정중 일부가 성공했다 하더라도 실제 DB에는 반영하지 않는다.

따라서 동시에 진행되는 TRANSACTION작업은 서로에 의한 처리 결과를 처리가 완료되기 이전에 볼 수 없다. 일련의 모든 작업이 성공으로 끝나고 COMMIT이 이뤄 질 때 비로소 처리 결과가 DB에 반영되어 다른 작업들도 이 반영된 결과를 볼 수 있다.

Postgresql에서는 "BEGIN"과 "COMMIT"이라는 단어로 query를 감싸므로써 이런 TRANSACTION 기능을 사용 할 수 있다.

PRIMARY KEY

먼저 주 키 (PRIMARY KEY) 는 테이블에서

1. 레코드들이 가지고 있는 값들 중에 각 레코드들 별로 유니크하게 가지고 있는 항목

2. 중복이 불가능하고, NULL을 입력하는 것도 불가능하다.

예를 들어 국민의 개인정보를 관리하는 테이블에서는 주민등록번호가 이런 PRIMARY KEY역할을 한다고 볼 수 있겠다. 등록번호와 이름만 관리하는 임의의 주민 TABLE을 만들어보자면 아래와 같다. (주민등록번호를 PRIMARY키로 설정하겠다)

memID를 PRIMARY KEY로 설정하여 테이블을 생성했다. pgAdmin III에서 해당 테이블을 열어 보면 memID옆에 [PK]라고 PRIMARY KEY임을 표시하고 있다.

예를 들어서 위와같이 memid=1 인 아이유가 이미 테이블에 들어 있는데 같은 id로 수지를 입력하려고 하면 아래 output과 같이 PRIMARY KEY라서 중복된 키 값을 입력할 수 없다는 출력이 뜨면에 에러가 발생한다.

또한 memID를 NULL로 설정해놓고 입력을 할려고 해도, NULL이 허용되지 않는  PRIMARY KEY의 조건때문에 에러가 발생하게 된다.

FOREIGN KEY

FOREIGN KEY는 다음과 같은 경우라고 생각하면 된다.

일단 앞서 만든 주민 테이블이 노원구 주민 테이블이라고 생각해보자. 노원 도서관을 활용하는 사람들의 이용 실적 테이블을 만드는데 사실 노원 도서관은 노원 구민뿐 아니라 다른 구 시민도 사용한다. 따라서 노원구 사람들의 노원도서관 이용실적을 만들기 위해서는 도서관을 이용하는 사람들의 명부를 작성하되 그 사람이 노원구민일 때만 명부를 작성하면 된다.

이런 방법을 위해서는 명부를 작성할 때 그 사람의 주민 번호가 주민 테이블에 있는 번호인지 확인해야 한다. 이런 경우 주민 테이블의 memID를 FOREIGN KEY라고 한다.

그럼 실제로 사용실적을 작성할 테이블 usageTable을 만들어보자.

앞서 아이유, 수지, 박보영이 살고 있는 구의 주민 테이블을 다음과 같이 작성하였다.

그리고 아래 쿼리 문으로 usageTable을 만들었다. usageTable의 memID는 membershipTable의 memID 열에 REFERENCES를 두었다.

자.. 이제 테스트를 한번 해보자..

다른 구에 살고 있는 id 4번의 미란다 커가 노원구 도서관에 12월 5일에 왔다.

그래서 이용 실적에 넣어 보려고 하다보니까, 노원구 주민 테이블에 미란다 커의 주민등록 번호 4번이 없기 때문에 아래와 같이 에러가 발생하게 되었다.

그럼 다음 테스트를 보자..

다른 구에 살고 있는 테일러 스위프트가 노원도서관에 왔다. 그런데 테일러 스위프트가 외국인이다 보니 아직 주민등록 번호가 없단다. 그래서 다음과 같이 memID에 NULL을 입력했다.

결과는...? 

읭?

등록이 되었다.

사실 이건 의도한 바가 아니긴 한데, 이런 경우는 usageTable의 memid를 NULL값이 오지 못하도록 따로 설정을 해주어야 한다. 이것은 pgAdmin III에서 쉽게 할 수 있는데, usageTable의 column으로 가서 해당 memid의 NULLable항목을 체크해제 해주면 여기에 NULL을 넣은 레코드가 생성되지 못하게 막을 수 있다.

이런 개념을 maintainning data integrity라고 한단다. 형식상 잘못된 데이터를 허용하지 않는다는 개념이다.

최근에 다른 회사랑 일하면서 특정 파일을 다른 서버로 옮겨야 할 경우가 생겼다. 그런데 그 다른 서버가 사용하는 OS가 리눅스라서 윈도우 공유폴더를 못쓰는 바람에 OS와 상관없이 소켓만 열 수 있으면 파일 교환이 가능한 FTP에 대해서 공부하게 되었다.

처음에는 이 FTP규약을 배워서 직접 FTP서버를 구현할려고 햇는데 공부하다보니 구지 내가 바퀴를 발명해서 수레를 만들 필요는 없다는 것을 깨닳았다. 이미 너무 잘 구현된 FTP서버가 많이 있어서 설치하고 사용만 하면된다.

특히나 요번에는 FileZilla를 사용하려고 하는데, 윈도우 서비스 형태로 돌아가는 프로그램으로 구지 윈도우 로그인을 하지 않아도 돌아가는 점, 무엇보다도 무료 라이센스를 가진 프리웨어로 상업적인 용도로 사용해도 쇠고랑을 차지 않는다는 점이 마음에 들었다.

FileZilla 설치하고 설정하는 것은 뭐.. 인터넷에 그냥 한번만 검색해도 쭈루룩 나오는 내용이고 설명창에 설명이 잘되어 있어서 설정하기 쉬우니까 ( 관련사항에 대해서 알고 있다면..) 여기서는 넘어가기로 한다..

어찌됐든, 이 FIleZilla로 FTP를 한번 구성해 보았다.

FTP 서버의 구성은 아래와 같은데, 이 구성에 기인하여 425 에러가 발생하므로 잘 봐야한다.

간단히 설명하자면 FileZilla가 설치된 서버는 공유기를 통해 인터넷에 연결되어 있고 공유기는 공인 IP 125.7.198.200을 DDNS testSvr.iptime.org에 매칭해서 사용하고 있다. 그리고 공유기 설정을 통해 DMZ 기능을 사용하고 있는데, 이것은 예를 들어, 어떤 호스트에서 testSvr.iptime.org:6658 에 접속을 하게 되면 이 접속을 Port Forwarding하여 실제로는 내부망의 192.168.1.99 :6658에 접속이 되도록 하는 기능이다.

그리고 FTP서버에 접근하려고 하는 클라이언트는 공유기를 통해 인터넷에 접속이 가능한 상태이나, 클라이언트쪽 공유기에는 별다른 설정이 되어있지 않아 외부망에서 이 클라이언트 단의 포트에 직접적으로 접근 할 수는 없는 상태이다.

문제 원인을 파악하기 위해서는 FTP 작동방식 중에 passive, active mode. command connection, data connection에 대한 이해도. 필요하다. 아래 그림을 참조해보자..

위에 있는 그림은 Active-Connection 방식이고 아래 방식은 Passive Connection 방식이다.

FTP는 TCP/IP기반의 통신규약으로 총 2개의 TCP/IP 소켓 커넥션을 맺는다.

그 두개의 커넥션중 하나는 FTP서버와 클라이언트 단에 통신을 위한 Command Connection,

나머지 하나는 실제 파일이나 검색결과 전송을 위한 Data Connection이다.

전화로 다른 부서 사람에게 "누구누구 씨. 그 표좀 팩스로 보내주세요" 라고 전화했더니, 팩스로 표가 그려진 종이가 전달되었다면, 전화가 command Connection, 팩스가 Data Connection이라고 할 수 있겠다..

어찌 되었든 passive와 active 방식은 이런 Data Connection을 어떻게 맺는 가를 기준으로 구분된다. Passive와 Active라는 의미는 FTP 서버에 접속하는 Client 단을 기준으로 생각하면 되는데, 먼저 Active Connection  방식을 살펴보자면...

먼저 클라이언트가 21번 포트에 command connection을 맺을 수 있도록 FTP서버가 소켓을 열어놓고 기다리고 있으면, 클라이언트는 이 소켓에 연결요청을 하여 command Connection을 맺는다. 그리고 이 command connection을 이용하여 어떤 파일을 전송받기 위해 클라이언트는 새로운 포트 5151을 열고 PORT 명령을 통해 FTP서버단에 자기가 개설한 5151포트번호를 알려준다. 그러면 FTP 서버는 이 5151 포트에 접속하여 파일을 건내준다.

즉, active 방식은 클라이언트가 주체적으로 (active) 포트를 만들고 FPT서버가 이 포트로 들어오길 기다린다. (드루와 드루와)

반대로 passive 방식은 FTP서버 포트에 접속해서 command connection을 맺는 것 까지는 동일하나 데이터 전송을 위한 소켓을 수동적 (passsive)으로 FTP 서버가 열기를 기다렸다가 이 포트에 접속하여 데이터를 전송 받는다. (pasv명령을 통해 FTP서버가 클라이언트에게 서버가 개설한 포트 정보를 건내준다.)

아참.. 그러고보니 제일 중요한 문제가 뭔지 이야기를 안했군..

문제가 뭐였냐 하면.. FTP서버를 위에 그림처럼 구성하고, 클라이언트 PC에서 윈도우 cmd창에 ftp를 쳐서 FTP 루트 폴더에 뭐가 들었는지 dir명령어로 살펴 볼려고 하면, 아래와 같이 에러 메시지가 뜨고서는 data connection이 만들어지지 않았다고 하는 것이다.

이 말을 할려고 참 멀리도 돌아왔다.

뭐, 문제의 원인은 간단했다. 위에도 설명했지만 클라이언트가 active 모드로 FTP에 접속해서 data connection을 맺으려고 포트를 개설하고 그 정보를 FTP서버에 전송하면 서버는 이 포트로 드루와 드루와를 할 수 없다.

왜? FTP서버는 클라이언트가 개설한 포트 번호와 FTP클라이언트가 실행되고 있는 호스트의 IP정보를 가지고서는 인터넷상에 연결된 PC중에 어떤놈이 이 PC인지 알아낼 도리가 없다. 클라이언트 에서 제공하는 주소 정보는 단지 로컬 IP와 개설한 포트 정보이기 때문에...

즉 아래와 같이 PORT명령으로 전송되는 data용 소켓 정보는 인터넷 상에서 이 PC를 찾는데 아무 도움이 안된다. (멍청하게 로컬 IP 192.168.1.100와 포트 정보 2자리를 넘겨준다.)

따라서 FTP 서버는 data connection을 만들수 없어서 에러가 발생한다.

그럼 어떻게 해야 할까? 그렇다 Passive 모드로 data connection을 맺도록 하면 된다.

그래서 인터넷을 찾아보면

quote pasv를 치세요~

라는 답변을 볼 수 있는데...

무슨 고약한 심보인지는 모르겠는데, 결론부터 말하자면 윈도우 cmd창에서 실행 할 수 있는 ftp.exe에서는 Passive 모드가 지원이 안된다. -_-;

심지어 "여러분 cmd창에서 passive 모드가 안된다고 알고 있는데 사실 됨. 잘못된 사실을 알고 계신거임" 하는 어떤 서양인의 답변을 보기도 했는데 안된다. 안된다. 안된다. 이 답변 때문에 반나절을 더 구글링 했다.

그런 글들에서 시도 해보라고 하는건

quote pasv

를 명령행에 쳐보라는 건데 이 quote pasv 명령의 의미는 FTP규약에 정의되어 있는 명령 단위 조각을 직접 실행하겠다는 의미이고, pasv는 서버에서 포트하나 열고 포트정보를 전송한다는 의미이다. (ftp 콘솔에 쳐 넣는 명령어는 실제로 pasv나 post와 같은 단위 조각 명령어들의 집합이다.)

따라서 quote pasv를 치고 dir명령을 치면 다음과 같은 일이 벌어진다.

ftp>quote pasv

   서버에서 포트 열고 포트정보 전송

ftp>dir

   근데 뭐 어쩌라고 나는 active로 내가 포트 열고 전송할꺼야

   POST 포트정보 (POST는 서버로 클라이언트 단의 포트정보 전송하는 단위 명령)

   서버에서는 이 주소는 뭐지? 접속 안되네 에러 내자.

...

처음엔 나도 설마 어떤 인간이 "여러분 cmd창에서 passive 모드가 안된다고 알고 있는데 사실 됨. 잘못된 사실을 알고 계신거임" 이라는 글까지 올렸는데 이게 안될까 하고 계속 다른 곳을 검색해 봤는데, 이게 사실이다. -_-

위에 로그를 보자. quote pasv로 pasv 소켓은 열렸지만, 다음 dir 명령 (PORT + LIST)는 active 모드로 실행이 된다. ㅋㅋㅋㅋㅋㅋㅋㅋㅋ....ㅋㅋ.......ㅋ

하지만 내 반나절이 아까워서 애써 위 로그를 무시하고, 구글에서 검색 하다가 결국 이런 곳까지 찾고 나서야 비로소 나의 노력이 부질 없었구나 하고 인정 할 수 잇었다.

http://passive-ftp.com 이라고 ftp.exe와 동일한 방식으로 작동하되 passive 모드를 지원하는 콘솔을 파는 웹싸이트다.. 허허.. 무려 29.9$에 이 프로그램을 팔고 있다..

ftp.exe가 passive 모드가 됐으면 이런걸 개발해서 파는 싸이트가 있었을까? 허허.. 

결론적으로..어떤인간이 passive모드 지원한다고 달콤하게 틀린 정보를 써놨지만, 안된다. 즉, 이번 오류의 원인은 ftp 구성상 passive 모드를 사용해야 하는데 active mode를 사용했다는 것이고, 이걸 해결하려면 passive 모드를 사용하는 다른 프로그램을 사용해야 한다. ftp.exe는 passive모드를 지원하지 않는다.

ODBC는 아마도.. Open Data Base Capability 의 약자...로?

윈도우 상에서 돌아가는 프로그램들이 DB프로그램에 종속적이지 않도록 DB와 프로그램 사이에서 인터페이스를 해주는 하나의 계층정도의 기능을 한다.

이 ODBC를 통해서 개발자는 동일한 방식으로 코딩을 해도, 변화하는 여러 DBMS에 따라 소스코드를 변경할 필요가 없어지게 되었다. 즉, 나는 ODBC계층에 명령을 내리고 ODBC는 각 DBMS 프로그램에 따라 그 명령을 해석에서 DBMS에 적합한 명령을 내려준다.

다시 말해, ODBC라는 하인에게 "가서 SELECT * FROM table 하고 와라" 라고 시키면 ODBC는 DBMS에게 가서 각 DBMS가 알아 먹을 수 있는 언어로 "주인님이 이거 하라는데요" 라고 말하는 구조다.

어찌 되었든 간에 postgresql또한 이런 ODBC계층의 업무를 수행할 드라이버를 제공하는데 이름이 psqlODBC이다. ODBC의 실체는 dll파일로 이 dll 파일에 들어 있는 바이너리 코드가 사용자의 명령을 해석하여 postgresql서버에 명령을 전송한다.

이 드라이버는 msi파일로 제공되어 그냥 더블클릭만 하면 드라이버 설치가 되고, 심지어 뭐 설정할 것도 없다. 멍하니 Next만 누르다 보면 언제깔았는지도 모를 이상한 프로그램들이 깔리는 다른 로그램과 달리 딸로 깔리는 것도 없어 Next만 눌러도 된다.

psqlOBDC는 아래 링크에서 다운 받을 수 있다.

http://www.postgresql.org/ftp/odbc/

현재 2014년 10월 버전까지 나왔는데, 그냥 최신버전을 받아 설치하면 된다.

참고로 프로그램 작성시 32비트로 코딩 했는데, 64비트 OBDC만 설치 되어 있다면 에러가 발생하니 주의하자. 64비트 ODBC만 설치 되긴 하던데 이 문제를 어떻게 해결할지는 또 열심히 찾아서 포스트 해야지...

OBDC 사용방법

OBDC를 사용함에 있어서 일단 프로그래밍 언어는 C#을 기준으로 삼겠다. 별 이유 없다. C#이 C++보다 프로그래밍을 많이 해봐서 그런지 좀더 편한것 같다. 그런데 뭐 C++로 해도 뭐 별것 없다. 필요한 헤더 포함시키고, 라이브러리 링크시키고 비슷한 함수 가지고 와서 쓰는거니 별 차이 없다.

뭐 일단, OBDC를 사용하는 방법은 크게 2가지 이다.

1번째는 DSN (Data Source Name). 즉, 데이터 원본이름을 설정하여 사용하는 방법

2번째는 DSN을 이용하지 않고 코드상에서 직접 postgresql에 접근하는 방법

1번째 방법은 윈도우 상에서 DSN을 설정해 주어야 한다.

제어판 -> 관리도구 -> 데이터 원본 (ODBC)를 더블 클릭 해보면 아래와 같이 DSN을 설정 할 수 있는 대화상자가 나타난다.

ODBC 드라이버가 정상적으로 깔려있는 상태에서 추가 버튼을 누르면, 아래와 같이 현재 컴퓨터상에 깔려 있는 Postgresql의 ODBC드라이버가 보인다.

Postgresql DB설정할 때 ANSI로 했으면 ANSI를, UNICODE로 했으면 UNICODE를 선택하면 된다. 선택이 끝나면 해당 Postgresql DB에 접근하기 위한 설정값들을 입력해야 한다.

이것도 뭐.. 직관적이다. Data Source는 DSN을 분별하는 이름으로, 이걸 가지고 DSN을 코드상에서 구분한다. 자기가 쓰기 쉬운 이름으로 정하면 되고, Description은 이 DSN에 대한 설명, Database는 Postgresql에서 설정한 DB의 이름이다. Server는 Postgresql서버의 주소이고 User Name은 해당 DB에 접근 가능한 계정정보로 Password와 함께 써 넣으면 된다. 마지막으로 port는 DB생성할 때 사용하기로 한 포트 정보를 입력해 주면 된다.

나는 envisiontest_postgres라는 이름으로 DSN을 설정했는데, 이 DSN을 가지고 이제 코드 상에서 해당 DB를 향해서 어떤 명령을 내릴 수 있다.

string connectionString = "DSN=envisiontest_postgres;"; OdbcConnection ocn = new OdbcConnection(connectionString); ocn.Open(); #region UPDATE table SET col=value WHERE criteria OdbcCommand updateCommand = new OdbcCommand("UPDATE usrTable SET usrID = 5 where usrName='아이우';", ocn); updateCommand.ExecuteNonQuery(); #endregion #region SELECT * FROM table OdbcCommand selectCommand = new OdbcCommand("SELECT * FROM usrTable", ocn); OdbcDataReader selectReader = selectCommand.ExecuteReader(); if (selectReader.HasRows) { for (int i = 0; i < selectReader.FieldCount; i++) { Console.Write(selectReader.GetName(i)+"\t"); } Console.WriteLine(""); while (selectReader.Read()) //read one record. { Console.WriteLine("{0}\t{1}\t{2}", selectReader.GetInt32(0), selectReader.GetString(1), selectReader.GetString(2)); } } else { Console.WriteLine("No rows found."); } selectReader.Close(); #endregion

그냥 ConnectionString을 "DSN=[DSN이름]"으로 해놓고 OdbcConnection 클래스의 객체를 만들 때 ConnectionString넣어 open만 해주면 미리 지정해 놓은 DSN의 DB로 접속이 된다.

참 쉽죠잉?

이 ConnectionString은 Odbc가 여러 DBMS를 관리하기 위해 여러 옵션을 두어 설정 할 수 있는데, 이 DSN을 이용한 방법은 사용자가 윈도우 상에서 DSN을 설정하고 만들어 두면 단순히 DSN이름만 가지고 어떤 DB에 접근 할 수 있다는 장점이 있지만, DSN에 설정해 놓은 DB에만 접근 할 수 있다는 단점이 있어 여러 DB에 접근해야 하는 상황이라면, 해당 DB에 대한 DSN을 일일히 구성 해 두어야 한다는 단점 또한 가지고 있다.

그럼 두번째 방법을 알아볼까?

DSN을 이용하지 않고서도 어떤 DB에 접근 할 수 있는 방법이 있다.

아까도 말했는데 바로 ConnectionString을 이용하는 방법이다.

임의의 DB에 접근해야 하므로 각 DBMS 프로그램마다 약간씩의 Connection 스트링에 차이가 있으나, 일단은 Postgresql만 살펴보자면..

"Driver={ODBC드라이버이름};

 Server=Postgresql 서버주소;

 Port=DB포트;

 Database=DB이름;

 UID=DB유저 ID;

 PWD=DB유저 PW";

먼저 Driver는 ODBC 드라이버 이름을 말한다. 아까 DSN설정할 때 봤던 ODBC 드라이버의 이름을 그대로 넣어주면 되겠다. 요번경우에는 PostgreSQL Unicode (x64)

그리고 아래 항목들은 모두 직관적인 파라미터 이니까 그냥 보면 되겠다.

이렇게 ConnectionString을 바꾸면, 그 이후 과정은 모두 동일하다. OdbcConnection을 이 ConnectionString을 인자로 생성하여 open하고 query 날리고..

복잡한 sql 쿼리 구문은 앞으로 차차 공부하여 사용하면 되고, 일단은 DB접속과 간단한 소스코드상의 사용은 이것으로 마무리 하고자 한다. 최종 목표로 pgAdmin III와 같은 매니지먼트 프로그램을 직접 작성해 보면 재미있을 것 같다.

[LEFT,RIGHT,FULL] OUTER JOIN은 각각 OUTER JOIN을 기점으로 왼쪽 오른쪽, 두가지 테이블 모두의 데이터 행중에서 ON이후에 오는 조건에 부합하지 않아도 일단은 출력행에 포함시키겠다는 의미이다.

다음을 보자.. 아참 그리고 편의상 이 테스트에 사용한 테스트용 테이블을 다시 첨부한다. 

아래 쿼리는 usrTable.usrID와 buyTable.usrID가 같은 데이터 행을 결합하여 새로운 행을 만들어 출력하되 LEFT OUTER JOIN 명령어를 기준으로 왼쪽에 있는 테이블 usrTable의 데이터 행은 무조건 다 출력하겠다는 의미이다. (붙일 다른 테이블의 데이터 행이 없으니까 그 열을 null로 열을 채워서라도..) 

따라서 아래의 출력결과와 같듯이 주문하지 않은 김언아, 손니은도 같이 출력이 되었다.

물론 나머지 컬럼은 NULL인채로..

그리고 한가지 더 유념해서 볼 것은 JOIN의 결과로 탄생한 출력 데이터 행의 갯수가 두 테이블의 조건을 만족하는 데이터 행 이 각각 a,b 라고 했을 때 max(a,b)만큼 생성된다는 것이다. 어찌보면 당연한 이야기 이지만 처음엔 좀 의아한 부분이라 적어 놓고 넘어간다.  

OUTER JOIN또한 INNER JOIN과 마찬가지로 복수개의 조건이 결합되어 사용될 수 있는데, 기본적인 방식은 INNER JOIN과 동일하다. 방법이 동일하니까 굳이 여기에 다시 적지는 않겠고, 대신에 회원 가입은 해놓고 주문은 안한 사람들의 명단을 뽑는 것으로 대신 예를 보이겠다. 여기서는 WHERE prdTable.prdID is NULL이라는 새로운 구문이 들어가 있는데 NULL인 칼럼 값을 가진 데이터 행을 찾는 구문이다.

김언아와 손니은이 주문을 안했다.

먼저, join에 관련된 학습을 하기 위해서 아래와 같은 테스트용 테이블을 작성 했다.

왜 psql.exe를 사용하지 않느냐고?

콘솔은 멋있기는 한데 사용하기가 역시 불편하다.. 테이블 확인하기도 어렵고 해서

일단은 pgAdmin III를 이용해서 간단한 sql문법은 테스트해 볼 예정이다.

뭐 어쨋든 위와 같은 테이블을 작성 해 보았다.

카페에서 음료를 판매하는 상황이라고 가정하고 아래와 같은 3개의 테이블을 작성했다.

이름, 주소등의 간단한 유저정보를 가지고 있는 usrTable,

판매하는 음료 상품정보를 가지고 있는 prdTable,

어떤 유저가 어떤 음료를 주문했는지 정보를 가지고 있는 buyTable.

INNER JOIN의 기능은 두 테이블을 합치는 기능이란다.

기본 문법은 아래와 같다.

INNER JOIN되어 형성된 테이블이 FROM다음에 들어가게 된다고 생각해도 좋을 것 같다.

그리고 INNER JOIN은 중첩되어 요청되도 된다.

위 문법에 보면 조건이 이것 저것 여러개 있는데, 적용되는 조건의 순서로 따지면

조건 1 -> 조건 2 -> 조건 3

순서로 조건이 적용된다.

조건 1, 2를 가지고 테이블을 조인하여 테이블을 만들고 이 테이블에서 where의 조건 3을

이용하여 마지막 select 문장이 실행되는 거라고 생각하면 된다.

예를 들어 김태흐가 뭘 시켜 먹었는지 알고 싶은 경우에 다음과 같은 쿼리를 요청 할 수 있다.

결과는 아래와 같다..

또 하나의 예로, 서울사람들은 어떤 음료를 마시는지 알고 싶다면..

아래와 같은 구문을 써서 결과를 알아 볼 수 도 있다.

서울 사람들은 카페모카와 아포가토를 주문했다.

이렇듯 INNER JOIN은 두 테이블이 가진 데이터 행중에 특정 조건을 만족하는 행을 이어 붙여 새로운 데이터 행을 만들어 내는 용도라고 생각하면 좋을 것 같다.

테이블 생성하기 

테이블 weather와 cities를 생성한다. varchar(80)은 길이 80짜리 문자열을 의미하고 나머지 데이터 타입은 뭐.. 직관적이다. 물론 point와 같은 자료형은 postgresql에만 있는 특이 데이터 타입으로 지리정보 표현을 위해 선언된 데이터 타입이다.

테이블에 값 입력하기

weather라는 테이블에 weather의 컬럼 순서대로 각 값들을 입력한다.

그런데 일반적인 숫자 데이터 타입을 제외한 복합적인 데이터 타입, 예를 들어 date나 varchar(80) 그리고 뒤에 나오겠지만, point와 같은 데이터 타입들은 입력시에 single qoute ' ' '으로 인용부호를 붙여야 한다.

그리고 테이블의 컬럼 순으로 데이터를 입력할 수도 있지만, 아래와 같이 특정 데이터를 지정해서 새로운 데이터 행을 추가 할 수도 있다.

텍스트 파일로부터 특정 테이블에 행 추가하기

아래와 같은 텍스트 파일이 있다고 하면 copy [table name] from '[파일경로]'; 명령어를 이용하여 한번에 많은 량의 행을 특정 테이블에 추가 할 수 잇다.

뭔가 이상한 점이 생겼다. 그렇다 콘솔창이 파래졌다. 이 이상한점의 원인은 특정테이블에 자료 행을 추가하는 작업을 누가 하느냐라는 문제와 맞 닿아 있다. 생각해 보면 이 작업은 psql.exe를 이용해 postgresql 서버에 접속한 client단이 하는 것이 아니고, postgresql이 이 작업을 수행한다.

따라서 copy 명령에 인자로 주어지는 파일경로는 postgresql서버가 접근이 가능한 경로여야 한다. 즉, 이 copy 명령은 반드시 postgresql 서버단에서 수행되어야만 자료행 추가가 가능하다.

테이블 쿼리하기

.

테이블 쿼리는 특정 테이블에 들어 있는 열을 선택하여 보는 기능을 의미한다. 정확히는 테이블에 있는 열만 가능한 것은 아니고 psql.exe를 사용하는 사용자가 정의한 열을 선택하여 테이블을 쿼리하는 것도 가능하다.

가장 기본적인 문법은 select 열,열,열 from 테이블명

변형은 select 열, 수식 as 열이름 from 테이블명

이런 식이다.

(참고로 여기서 as는 정확히는 출력 칼럼의 이름을 재정의 (relabel) 하는 의미이다.)

그리고 아래와 같이 출력요청 명령 이후에 where (boolean) 을 넣어서 출력물을 필터링 할수도 있다. boolean은 AND OR NOT 등의 연산자로 연산이 가능하다.

한가지 더 추가로 order by를 통해 정렬된 결과값을 얻을 수도 있는데 아래와 같이 사용이 가능하다. 그리고 order by 명령어 뒤에 오는 인자또한 ',' 으로 구분한 여러 인자가 될 수 있으며, 정렬인자가 여러개면 앞에 있는것으로 1차 정령하고 동 순위가 생기는 경우 2번째 인자로 정렬된다.

또한 인자명뿐 아니라 select의 칼럼 선정처럼 연산이 와도 되고, 인자뒤에 DESC를 붙이면 기본 오름차순 정렬이 아니라 역순으로 정렬도 가능하다.

그리고 어떤 칼럼을 쿼리 했을 때 중복된 결과물이 나오기도 하는데 아래와 같이 distinct 명령어를 select다음에 추가하면 해당 칼럼에 대해서 중복된 값을 제거하여 보여준다.

postgresql은 createdb.exe 이외에도 postgresql을 가지고 놀 수 있는 다양한 툴들을 제공한다. 물론 pgAdmin III 도 툴 중의 하나로, pgAdmin III이 제공하는 GUI를 이용해 직관적으로 postgresql을 제어 할 수 있다.

그러나..

GUI는 멋이 없다. 자고로 뭔지 모르는 검은 화면에 커맨드 명령을 잔뜩 써 넣는 모습은 실제로 사용하는 명령어가 "quit" "cls" 정도라고 할지라도 멋이 난다. 그래서인지 pgAdmin III보다 콘솔창에서 postgresql을 제어 할 수 있는 쪽을 선택하고자 한다.

이런 변태 취향을 가진 사람들을 위해 postgresql에서는 psql.exe라는 콘솔 입력형 DB관리 툴을 제공하는데, 여기서 sql 명령을 실행시키거나 테이블을 만들거나 하는 등의 모든 기능들을 커맨드 명령으로 제어 할 수 있다.

실행하는 방법은 createdb.exe와 거의 유사한데

psql.exe - -host=[postgresql server address] - -port=5434 -U postgres myDB

라고 윈도우 콘솔에 넣으면 특정 DB에 접근 할 수 있다.

(참고로 - - 는 잘보이게 하려고 쓴거고 가운데 띄워쓰기가 없다)

정상적으로 접근할 수 있는 서버와 포트, 그리고 계정정보와 db라면 암호를 입력하라는 창이 뜨고 제대로 된 명령어를 입력하면 해당 DB에 접근 할 수 있다. 접근이 성공했다면 아래와 같은 입력창을 볼 수 있다.

#대신 >가 나올 수도 있는데, #은 슛퍼유저 혹은 root 유저를 의미한다.

(접속할 때 사용한 계정이...)

이 콘솔 입력창에서는 엔터키를 이용해 입력하고자 하는 명령어를 몇줄에 걸쳐 나눠 쓸 수 있으며, 이 psql.exe가 인식하는 명령어의 단위는 세미콜론 ' ; '의해서만 구분된다.

그리고 " - - "는 명령어 사이에 주석을 의미한다. 뭐 일다는 커맨드 콘솔에서 주석이 무슨 소용이 있을까 생각이 들지만 나중에 사용할 수 있는 여지가 있지 않나 싶다.

(물론 여기서도 - - 는 사실 -- 이다.)

postgresql의 Documentation은 상당히 친절한 편이다. 총 2861장의 매뉴얼에 기본 sql문법, postgresql을 이용한 어플리케이션 작성 방법 등 나같이 DB초보자도 참고하여 이 프로그램을 사용 할 수 있도록 해준다.

그런데 단점이 있다면, postgresql이 초기에 리눅스 버전으로 만들어졌기 때문인지 모든 메뉴얼이 리눅스용 postgresql을 대상으로 모든 설명이 되어 있다는 점이다. 뭐 pgAdmin III라는 친절한 윈도우용 GUI가 있어서 직관적으로 DB를 다룰 수 있게 되어 있기는 하지만, bin에 들어 있는 createdb.exe를 이용해서 db를 생성해볼려고 하면 아규먼트를 어떻게 줘야 할지 그것부터 막막막 하기는 하다.

(물론 설명이 ./createdb.exe - -help 라고 치면 나온다.)

어쨋든, createdb.exe 실행파일은 윈도우 운영체제에서 원격에 있는 postgresql서버나 로컬에 있는 postgresql서버에 db를 만들수 있는 실행파일로 다음과 같이 사용하여 실제 DB를 만들 수 있다.

- -host= 이후에 오는 인자는 localhost로 명명해도 좋고 네트워크가 연결된 곳이라면, 해당 postgresql서버의 네트워크 주소를 입력해도 좋다. 해당 서버에 성공적으로 이 요청이 도달하면 해당 username의 암호를 묻는 과정이 나오는데, 올바른 암호를 입력하면 DB가 생성된다. 그리고 생성된 DB는 아래와 같이 pgAdmin III에서도 확인이 가능하다.

postgresql은 일단 9.3.5 for windows x64 버전을 사용 했다.

뭔가 "서버"라는 단어가 들어가는 기능은 모두 리눅스에서 돌려야 할 것 같은 선입관이 있기는 한데, 실무에서 많이 접하는 서버는 모조리 윈도우이고 개발자체도 윈도우에서 해본 경험이 많다보니 그냥 윈도우 버전이 이것저것 만져보기 쉬울 것 같아 구지 리눅스 버전을 타겟으로 삼지는 않았다.

어찌되었든, postgresql은 64비트 윈도우 버전의 9.3.5를 사용하기로 한다.

설치는 postgresql홈페이지에 들어가서 해당 버전의 windows intaller를 다운 받아서 실행시키고 next, next, next만 누르면 된다. 알집이나 알약 설치하는 것과 별반 다를것이 없다.

(postgresql 홈페이지 : http://www.postgresql.org/)

중간중간에 postgresql이 사용할 포트 번호를 입력하거나, 전체 DB에 대한 root 계정 ( 기본은 postgres)및 비밀번호를 입력하라는 말이 나오는데, 일단 그냥 next만 눌러도 설치는 완료된다. 설치는 매우 직관 적이라서 영어만 읽을 줄 알면, 혹은 몰라도 눈치가 빠른 사람이라면 이정도는 그냥 수정 할 수 있다.

그런데 중간에 보면 stackBuilder를 설치하겠냐고 물어보는 란이 나오는데, 이거는 아직 어디에다가 쓰는 물건인지 몰라 체크박스를 해제하고 넘어갔다. 나중에 설치가 가능하다고 하니, 넘어가도 별 문제는 없을 듯 싶다.

설치가 정상적으로 완료 된 경우는 pgAdmin III라는 일종의 DB 매니지먼트 프로그램이 같이 설치 되는데, MS SQL의 management studio와 같은 맥락이라 생각해도 좋다. 이 프로그램을 이용해서 DB관리자는 여러 오브젝트들을 생성하거나 제거 할 수 있다.

이 외에 설치된 폴더의 bin이라는 폴더를 보면 윈도우즈 cmd 창에서 직접 타이핑해가며 DB를 관리 할 수 있게 해주는, 여러가지 실행파일들이 존재해서 GUI환경보다 텍스트를 이용한 인터페이스를 선호하는 사람들은 이 실행파일들을 이용 할 수도 있다.

그럼 이제 이 DB에 접속해 보자.

접속에 필요한 것은 아까 입력했던 DB관리자 계정 (next만 눌렀다면 "postgres"), 계정의 password, 접속하고자 하는 주소, 그리고 아까 정했던 DB사용 포트다.

pgAdmin III를 실행시켜서 아래와 같이 정보들을 다 입력해주자.

그리고 연결해보면 아래와 같이 현재 연결한 physical 서버 하위에 어떤 데이터 베이스들이 있는지 쭈욱 나열된다.

그런데, 이과정을 다른 리모트 서버에서 해보면??? 아래와 같이 접속이 안되는 깝깝한 경험을 할 수 있는데 그것은 현재 이 서버에 대한 접근을 postgresql이 막고 있기 때문이다. 이걸 열기 위해서는 다음 두가지 파일을 수정해야 한다.

postgresql.conf , pg_hba.conf

postgresql.conf를 열어보면 잡다한 설정값들이 있기는 한데, 그런 것들은 나중에 차근차근 알아보도록 하고 일단 리모트 서버에서 postgresql에 접속하기 위해서는 listen_addresses, port, max_connections를 아래와 같이 수정한다.

뭐 이름만 봐도 직관적인 설정값이지만, listen_addresses는 이 sql서버가 어떤 네트워크 도메인으로 TCP 포트를 열건지에 대한 값이다. 만약 한 컴퓨터가 192.168.100. 대역과 192.168.99 대역에 모두 포함되어 있다면, 이 값을 '*'로 했을 때 두가지 IP의 5433포트에 TCP서버를 열고 listen상태로 들어가 클라이언트의 접속을 기다리게 된다.

port는 뭐.. 그냥 포트 번호다. 그리고 max_connections는 한번에 접속할 수 잇는 클라이언트를 설정하는 값으로 기본값 100으로 설정되어 있다.

그리고 두번째로 pg_hba.conf를 수정해야 된다. 여기는 앞서 설정한 TCP포트로 접속이 이뤄지고나서 어떤 IP로부터 어떤 유저가 어떤 데이터 베이스에 대한 접근권한을 가질지 설정한다. 기본으로 설정되어 있는 것이 localhost에서 시도되는 접속에 대해서만 권한을 부여하기 때문에 리모트 서버에서 시도하는 접속을 위에서처럼 막아 버린 것이다.

그래서 이번엔 이 conf파일을 수정하여 모든 호스트로부터 오는 접속을 아래와 같이 허용한다. (각 컬럼에 대한 상세 설명은 생략한다. 왜냐하면 이 텍스트 파일에 모두 있기 때문..)

간단히 설명하자면, IPv4를 기준으로 했을 모든 ADDRESS로부터의 접속을 허용한다는 의미인데.. 아직나도 잘 모르겠지만 이렇게 해주면 리모트 서버로부터의 접속을 허용하게된다.

모든 수정을 마쳤으면, 이제 파일들을 모두 저장하고 postgresql서비스를 한번 재시작해준다. pgAdmin III에서 재시작해도 되고 서비스에서 그냥 서비스를 종료했다가 다시 시작해도 좋다..그럼 이제 리모트 서버에서도 해당 postgresql에 대한 접속이 가능해진다...

다음 포스트에서는 실제로 디비를 만들고 지우고 테이블을 만들고 지우고 북치고 장구치며 이 디비를 가지고 놀아보자..