[Database] 9.DB programming
REF: fundamentals of database systems 7th edition
Overview
Approaches
1. ⭐️Embedded commands
- db statement들이 host programming language에 Embedded되어있음.
- SQL이 대표적인 예시이며,
⭐️EXEC SQL같은 특수 접두사로 구분된다. ⭐️precompiler, preproccessor가 이를 보고 분리컴파일을한다.
2. Library of database functions
- host programming language 내에서 database를 직접 호출할 수 있다.
⭐️API(Application Programming Interface): 응용프로그램에서 DB를 직접 접속한다.- SQL/CLI, ODBC등이 있다.
- OOPLs(객체지향언어)에서는 클래스 라이브러리가 사용된다.
3. Designing a brand-new language
- DB 프로그래밍언어는 처음부터 DB모델과 질의어와 호환되도록 만들어졌다.
- 반복문, 조건문 등이 DB 언어에 추가되어 완전한 프로그래밍 언어로 변환된다.
- Impedance mismatch라고 불리는 DB model과 프로그래밍 언어와의 차이로부터 나오는 문제를 줄인다.
Sequence
architecture
- top-tier : client program -> Web client, mobile app
- middle-tier : application program -> business software application의 logic구현, 백엔드 서버
- bottom-tier : database servers -> data를 실제로 접근, 업데이트
Sequence
- application program(백엔드 서버)이 DB server와
connection을 시작.- connection
CONNECT TO <server-name> AS <connection-name> AUTHORIZATION <user-account-info>; - connection change
SET CONNECTION <connection-name>;
- connection
- application 프로그램이 DB server에 쿼리를 보내 db를 업데이트
- DB access가 더이상 필요하지 않을때, program은 connection을 종료
- Disconnection
DISCONNECT <connection-name>;
- Disconnection
Embedded SQL
- 대부분의 SQL Statement들은 범용목적 host programming language에 embedded될 수 있다.
Usage
EXEC SQL을 통해⭐️preprocessor(precompiler)가 SQL statements와 host language를 구분한다.
END-EXEC,semicolon을 통해 statement를 끝맺는다.
SQLCODE/SQLSTATE- program과 SQL간 통신
⭐️0 || 00000-> SQL이 성공적으로 실행되었다.- other values -> 실패
SQLSTATE는 이후 버전에 추가되어00000으로 에러를 표현한다.(ex, 02000 : no more data)
Shared Variables- C program과 embedded SQL statements와 동시에 사용될 수 있음.
- SQL statement내에서 host변수와 구분하기위해
:(colon)으로 구분.
single tuple 예시
loop = 1;
while(loop)
{
prompt("Enter a SSN", ssn);
EXEC SQL //SQL 시작
select FNAME, MINIT, LNAME, ADDRESS, SALARY
into :fname, :minit, :lname, :address, :salary //shared variable선언
from EMPLOYEE where SSN = :ssn;
if (SQLCODE == 0)
printf(fname, minit, lname, address, salary);
else
printf("no ssn : %d", ssn);
prompt("need more ? 1 for yes, 0 for no", loop);
}
- 단일 tuple만을 결과로 받을 수 있다.
- ssn을 사용자로부터 입력받는다.
- INTO 절로 shared variable에 값을 받는다.
- SQLCODE가 0일때 정상실행된것이므로, 결과값이 출력된다.
multiple tuple 예시
⭐️CURSOR
- embedded SQL에서는
CURSOR를 통해 multiple tuple을 가져올 수 있다. - query의 결과로 반환된 tuple 집합에서 1개의 tuple을 가리키는 변수
DECLARE를 통해 CURSOR 변수를 선언.OPEN CURSOR를 통해결과 쿼리의 ⭐️첫 번째 줄 이전의 줄에 위치시킨다.CLOSE CURSOR로 결과 쿼리를 더이상 사용하지 않는다고 알린다.FETCH로 현재 결과를 변수에 복사하고, CURSOR를 다음 줄로 옮긴다.
prompt("enter a dname", dname);
EXEC SQL
SELECT Dnumber INTO :dnumber
FROM DEPARTMENT WHERE Dname = :dname;
EXEC SQL DECLARE EMP CURSOR FOR //Cursor Declare
SELECT Ssn, Fname, Minit, Lname, Salary
FROM EMPLOYEE WHERE Dno = :dnumber
FOR UPDATE OF Salary;
EXEC SQL OPEN EMP;//Cursor Open, before first line
EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary;//Fetch into first line
while (SQLCODE == 0)
{
printf("Emp name is", Fname, Minit, Lname);
prompt("Enter raise", raise);
EXEC SQL
UPDATE EMPLOYEE
SET Salary = Salary + raise
WHERE CURRENT OF EMP;
EXEC SQL FETCH FROM EMP INTO :ssn, :fname, :minit, :lname, :salary;//Fetch into next line
}
EXEC SQL CLOSE EMP;//end line
SQLJ
- SQLJ는
JAVA에서의 Embedded SQL command이다. SQLJ translator가 SQL 구문을 Java로 변환해준다.- java.sql의 특정 클래스를 사용한다.
- Retrieving Multiple Tuples in SQLJ
- SQLJ는 CURSOR같은 iterator를 2개 지원한다.
⭐️named iterator- query result 내에서 속성의이름과 타입까지 받아온다.⭐️positional iterator- 속성의타입만 받아온다.
⭐️FETCH: query result로부터 다음 tuple로 이동.FETCH <iterator-variable> INTO <program-variables>
- SQLJ는 CURSOR같은 iterator를 2개 지원한다.
Dynamic SQL
정의
- embedded는
staticdb programming approach이다.(SQL 구절이 한번 작성되면 다시 프로그램 컴파일전까지는 바뀌지 않는다는 것 때문에.) - Dynamic SQL은
runtime에 쿼리를 정의한다.- 프로그램을 실행하면 실행도중에 쿼리를 사용자로부터 입력받는다.
API function call은dynamic approach에 가깝다.- 단점
- runtime에 문법오류를 확인해야한다.
- 이전에 type과 속성수를 알고있지 않다면 프로그래밍이 어려워진다.
examples
⭐️PREPARE: 사용자로부터 입력받은 문자열을 query로 변환, 컴파일한다.⭐️EXECUTE: 변환한 query를 실행한다.⭐️EXECUTE IMMEDIATE:PREPARE+EXECUTE
EXEC SQL BEGIN DECLARE SECTION;
varchar sqlupdatestring [256];
EXEC SQL END DECLARE SECTION;
...
prompt("Enter the Update Command: ", sqlupdatestring); //runtime에 사용자로부터 쿼리를 입력받는다.
EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring; //query compiler가 쿼리를 컴파일한다.
EXEC SQL EXECUTE sqlcommand; //실행
ODBC
정의
- ODBC(open database connectivity)는 DB에 접근하는 공개 표준 API이다.
⭐️프로그래밍 언어 상에서, 서로 다른 종류의 DB에 접근할 수 있게 해준다.
구조
- Application : ODBC function을 호출한다.
- ⭐️
Driver Manager:- Load and unload drivers
- ODBC function call을 처리하거나, Driver에 넘긴다.
- ⭐️
Driver:ODBC function call을 처리SQL request를 data source에 넘긴다.결과를 AP에게 되돌린다.
- ⭐️Data source :
- user가 access하고자 하는 data와 data 관련 OS, DBMS, network platform으로 구성된다.
Handle
- ODBC가 지원해준다.
- handle자체는 특정 item을 식별하기 위한 값이다.
- 4가지 type의 handle이 있다.
Environment handle유효 연결 핸들이나,현재 가동 중인 연결 핸들 등 광역 정보를 위한 기억장소를 식별한다.ODBC를 사용하는 환경에 대한 정보를 저장
Connection handle특정 연결에 관한 정보를 위한 기억장소 식별한다.
Statement handleSQL문에 관한 정보를 위한 기억장소 식별
Descriptor handleSQL statement에 대한 여러 정보를 위한 기억장소 식별- SQL문의 길이, 주소 등에 대한 정보와 데이터 유형, null-terminated인지여부 등에 대한 정보를 저장한다.
자동, 명시적할당.
수행단계
- 아래 절차를 ODBC가 알아서 해준다.
- SQLAllocENV :
Environment handle메모리 할당 - SQLAllocConnect :
Connection handle메모리 할당 - SQLConnect :
Driver 로드, data source와 연결 - SQLAllocStmt :
Statement handle메모리 할당 - Process SQL Stmt : SQL statement 처리
- Receive Result : 결과를 받아옴 ——–4의 역순
- SQLFreeStmt : Stmt 메모리 해제
- SQLDisconnect : data source와 연결 해제
- SQLFreeConnect : Connection handle 메모리 해제
- SQLFreeEnv : Environment handle 메모리 해제
JDBC
-
ODBC중, JAVA를 사용할때를 의미한다.
- JDBC Driver
- 특정 Vendor가 제공하는 RDBMS에 대해 JDBC에 지정된 클래스, 관련 객체, 함수를 구현한 것.
- 프로그램이 JDBC driver가 있는 모든 RDBMS에 접근 가능하게끔 한다.
- CLI와 구문적으로 차이가 있음(non-semantic)
SQL/CLi
- 1990년대 말, 표준화된 방법.
API function call을 사용하는 방법 중 하나이다.ODBC처럼 같은 프로그램 내에서 여러 DB에 접속할 수 있게해준다.sqlcli.h같이 특정 라이브러리가 설치되어야한다.- 호출 시, SQL statement는
- 사용자에게 특정 값을 입력받는다.
컴파일 타임에 SQLPrepare이 프로그램에 static하게 입력된 query를 컴파일해 static binding해놓는다.- 사용자가 특정 값을 입력하면 이 값이
SQLBindParameter를 통해 이 쿼리와 binding된다.
Components
-
ODBC의 일반화
Environment record(SQLHENV):1개 이상의 DB connection 추적에 사용되며,환경정보를 세팅한다.- 앞서봤듯, SQL/CLI를 사용하는 환경에대한 변수를 설정하는 것이다.
Connection record(SQLHDBC):특정 DB 연결에 필요한 정보추적- 특정 DB와 연결했을때 이 연결을 식별한다.
Statement record(SQLHSTMT): 1개 SQL 구문에 필요한 정보 추적Description record(SQLHDEC): tuple 추적
Steps
- SQLAllocHandle이 embedded SQL 에서의
SQLAllocENV, SQLAllocConnect, SQLAllocStmt를 처리한다.
- load SQL/CLI library
- record handle variable 선언
(SQLHENV, SQLHDBC, SQLHSTMT, SQLHDEC) - set
environment record<-SQLAllocHandle - set
connection record<-SQLAllocHandle - set
statement record<-SQLAllocHandle - statement 준비 <- SQLPrepare
- program variable에 parameter 바인딩 <-
SQLBindParameter - SQL statement execute <-
SQLExecute - Bound columns in a query to C variable <-
SQLBindCol10.1줄씩 column value를 C variable로 받아옴 <-SQlFetch
example code
#include "sqlcli.h"
void printsal()
{
SQLHENV env1; //여러개의 DB 연결 위한 pointer
SQLHDBC con1; //1개 DB 연결 식별 pointer
SQLHSTMT stmt1; //statement pointer
SQLRETURN ret1, ret2, ret3, ret4;
//set environment record -> 1개이상의 DB 연결 관리 및 환경 설정
ret1 = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env1);
//set connection record -> 특정 DB와의 연결 추적
if (!ret1)
ret2 = SQLAllocHandle(SQL_HANDLE_DBC, env1, &con1);
else
exit(1);
//연결
if (!ret2)//dbserver 이름, 계정이름, 비밀번호
ret3 = SQLConnect(con1, "dbs", SQL_NTS, "js", SQL_NTS, "xyz");
else
exit(1);
//set statement record
if (!ret3)
ret4 = SQLAllocHandle(SQL_HANDLE_STMT, con1, &stmt1);
else
exit(1);
//SQLPREPARE -> query를 컴파일하고, static binding해서 stmt1에 할당한다.
SLQPrepare(stmt1, "SELECT Lname, Salary FROM EMPLOYEE WHERE Ssn = ?", SQL_NTS);
prompt("Enter Ssn", ssn);
//SQLBindParameter -> query의 n번째 parameter(?)에 특정 값을 binding한다.
SQLBindParameter(stmt1, 1, SQL_CHAR, &ssn, 9, &fetchlen1);
//SQLExecute -> EXECUTE한 결과를 stmt1에 저장.
ret1 = SQLExecute(stmt1);
if (!ret1)
{
//SQLBindCol -> stmt1의 Column값을 각 lname, salary라는 C변수에 바인딩한다.
SQLBindCol(stmt1, 1, SQL_CHAR, &lname, 15, &fetchlen1);
SQLBindCol(stmt1, 2, SQL_FLOAT, &salary, 4, &fetchlen2);
//SQLFetch -> 다음 줄로 이동
ret2 = SQLFetch(stmt1);
if (!ret2)
printf(ssn, lanme, salary);
else
printf("SSN NOT EXITS");
}
}
- pointers
- 각 함수별 원형은 MS ODBC DOCS에서 참고
Database Stored Procedures
three-tier architecture
- presentation tier : 사용자 인터페이스, GUI만 처리한다. 흔히 말하는 프론트엔드가 여기에 속한다.
- application tier : 비즈니스 로직을 수행한다. 요청되는 정보를 가공하고 처리한다. 벡엔드라고도 불리며,
middleware라고도 불린다. - data tier : DB, DBMS에 접근해 데이터를 관리하는 것을 의미. 백엔드를 말할때, 이를 포함하기도 한다.
정의
- 사람들이 흔히 말하는 백엔드는 웹서버 + DB를 포함하지만, 보통
웹서버와 DB server가 돌아가는 머신은 다르다. - 그래서 가끔은, DB를 직접 가공하고 처리하는
함수나 procedure을 웹서버가 아닌DB server에 저장하는게 유용하다. - 이렇게 저장된 db program module을
stored procedures라고 부른다. - SQL 표준에서는 이렇게 DB server측에 저장된 db program module을
persistent stored module이라고 부른다.
장점
- db program이 복수의 application에 필요하다면,
db server측에 저장될 수 있으며, db server에 연결된 모든 application program들이 사용할 수 있다. - db program은 1개의 application만 사용하진 않는다.
⭐️modularity가 높아지고, ⭐️duplication이 낮아진다.
⭐️데이터 전송시간과 통신비용이 감소한다.- db server와 application program(web server)간의 요청이 간단하게 처리될 수 있기때문.
⭐️view가 제공하는 modeling 장점을 강화- stored procedures를 통해 db 사용자들이
복잡한 derived data를 만들어지게 할 수 있다.
- stored procedures를 통해 db 사용자들이
사용법
- stored procedure
CREATE PROCEDURE <procedure name> (<parameters>) <local declarations> <procedure body>;
- stored function
CREATE FUNCTION <function name> (<parameters>) RETURNS <return type> <local declarations> <function body>;
- call persistent stored module
CALL <function or procedure name> (<arguments>)
SQL/PSM
- SQL standard의 일부로,
psm(persistent stored modules)를 작성하는 법을 정의한다. -
위에서 정의한 PSM에 더해
⭐️loop, branching, 즉조건문과 분기문을 작성할 수 있게한다. - 조건문
IF <condition> THEN <statement list> ELSEIF <condition> THEN <statement list> ELSEIF <condition> THEN <statement list> ... ELSE <statement list> END IF;- shell의 조건문과 비슷하게 생겼다.
- 반복문
WHILE <condition> DO <statement list> END WHILE; REPEAT <statement list> UNTIL <condition> END REPEAT;
댓글남기기