파이어버드 SQL 팁

파이어버드 DBMS를 사용하면서 다른 DBMS와 차이가 있는 몇가지를 메모해 둡니다. 파이어버드 또한 SQL 표준을 준수하고 있으므로 핵심적인 SQL 문법은 비슷하지만 차이가 나는 몇가지를 다루어 보겠습니다.

■ 테이블/컬럼 스키마 조회하기

MySQL이나 SQL Server에서 지원하는 INFORMATION_SCHEMA나 오라클의 ALL_TABLES, ALL_TAB_COLUMNS를 통한 테이블 및 컬럼 정보 조회는 파이어버드에서는 다른 형태로 지원합니다.

SQL> show table RDB$RELATIONS;

RDB$VIEW_BLR                   (RDB$VIEW_BLR) BLOB segment 80, subtype BLR Nullable

RDB$VIEW_SOURCE                (RDB$SOURCE) BLOB segment 80, subtype TEXT CHARACTER SET UNICODE_FSS Nullable

RDB$DESCRIPTION                (RDB$DESCRIPTION) BLOB segment 80, subtype TEXT CHARACTER SET UNICODE_FSS Nullable

RDB$RELATION_ID                (RDB$RELATION_ID) SMALLINT Nullable

RDB$SYSTEM_FLAG                (RDB$SYSTEM_FLAG) SMALLINT Nullable

RDB$DBKEY_LENGTH               (RDB$DBKEY_LENGTH) SMALLINT Nullable

RDB$FORMAT                     (RDB$FORMAT) SMALLINT Nullable

RDB$FIELD_ID                   (RDB$FIELD_ID) SMALLINT Nullable

RDB$RELATION_NAME              (RDB$RELATION_NAME) CHAR(31) CHARACTER SET UNICODE_FSS Nullable

RDB$SECURITY_CLASS             (RDB$SECURITY_CLASS) CHAR(31) CHARACTER SET UNICODE_FSS Nullable

RDB$EXTERNAL_FILE              (RDB$FILE_NAME) VARCHAR(255) Nullable

RDB$RUNTIME                    (RDB$RUNTIME) BLOB segment 80, subtype SUMMARY Nullable

RDB$EXTERNAL_DESCRIPTION       (RDB$EXTERNAL_DESCRIPTION) BLOB segment 80, subtype EXTERNAL_FILE_DESCRIPTION Nullable

RDB$OWNER_NAME                 (RDB$USER) CHAR(31) CHARACTER SET UNICODE_FSS Nullable

RDB$DEFAULT_CLASS              (RDB$SECURITY_CLASS) CHAR(31) CHARACTER SET UNICODE_FSS Nullable

RDB$FLAGS                      (RDB$SYSTEM_FLAG) SMALLINT Nullable

RDB$RELATION_TYPE              (RDB$RELATION_TYPE) SMALLINT Nullable

SQL>

위의 인용문은 RDB$RELATIONS의 스키마 내용을 조회한 것으로 RDB$RELATIONS를 사용하면 현재 특정 테이블이 존재하는지 확인할 수 있습니다. 다음과 같이 테이블 존재 여부를 확인할 수 있습니다.

SELECT COUNT(*) cnt FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = '테이블이름'

■ SQL Server/MySQL 데이터타입 변환

DBMS 들간의 차이는 데이터타입에서 쉽게 발견할 수 있는 편입니다. 다른 DBMS의 데이터타입과의 비교를 통해서 보다 선명하게 파이어버드의 데이터타입을 인식할 수 있는 측면도 있습니다.

SQL Server	MySQL	Firebird
SMALLINT	SMALLINT	SMALLINT
TINYINT	TINYINT	SMALLINT
INT	INT	INTEGER
BIGINT	BIGINT	INT64
NUMERIC	DECIMAL	NUMERIC
MONEY	DECIMAL	DECIMAL(18, 4)
SMALLMONEY	DECIMAL	DECIMAL(10, 4)
DECIMAL	DECIMAL	DECIMAL
FLOAT	FLOAT	FLOAT
REAL	FLOAT	DOUBLE
TIMESTAMP	TIMESTAMP	INTEGER
DATETIME	DATETIME	TIMESTAMP
SMALLDATETIME	DATETIME	TIMESTAMP
CHAR	CHAR/LONGTEXT	CHAR
NCHAR	CHAR/LONGTEXT	CHAR(x) CHARACTER SET UNICODE_FSS
NTEXT	VARCHAR/MEDIUMTEXT/LONGTEXT	BLOB SUB_TYPE TEXT
NVARCHAR	VARCHAR/MEDIUMTEXT/LONGTEXT	VARCHAR(x) CHARACTER SET UNICODE_FSS
TEXT	VARCHAR/MEDIUMTEXT/LONGTEXT	BLOB SUB_TYPE TEXT
VARBINARY	VARBINARY/MEDIUMBLOB/LONGBLOB	CHAR
VARCHAR	VARCHAR/MEDIUMTEXT/LONGTEXT	VARCHAR
IMAGE	TINYBLOB/MEDIUMBLOB/LONGBLOB	BLOB
BINARY	BINARY/MEDIUMBLOB/LONGBLOB	CHAR

■ 상수값의 질의 요령

현재 데이터베이스 시스템의 시스템 일시를 읽는다던가 단순히 데이터베이스와의 연결 확인을 위해 사용하는 상수값 질의의 경우 "SELECT ...." 처럼 사용할 수 없고 반드시 FROM절을 통해 특정 테이블을 지정해야 합니다. SQL Server의 경우에는 "SELECT 1"과 같은 문법도 지원하기는 합니다. 그렇지만 오라클이나 파이어버드의 경우에는 이런 구문은 오류를 발생 시킵니다. 그래서 오라클의 경우에는 DUAL 이라는 시스템에서 기본 제공하는 테이블을 활용하여 "SELECT 1 FROM DUAL"과 같이 사용할 수 있습니다. 파이어버드의 경우에는 RDB$DATABASE를 사용할 수 있습니다. 

RDB$DATABASE는 데이터베이스별로 기본적으로 생성되는 테이블로 오라클의 DUAL 처럼 단 1개의 행만 갖기 때문에 상수 질의등에 적절하게 활요할 수 있습니다.

SELECT 1 FROM RDB$DATABASE;

■ Prepared statement

사용자가 SQL문을 DBMS에 던지면 파싱, 질의 계획작성, 실행등의 과정을 거치게 되는데 값만 다르고 문장의 형태가 같다면 문자열 기준으로 달라보여도 파싱을 거쳐 질의 계획(Query plan) 작성 단계에서는 동일한 패턴을 가지게 됩니다. 이런 경우 값 설정을 제외한 나머지 단계를 미리 수행해 놓고 해당 패턴을 재사용하는 과정을 통해서 성능을 높일 수 있는데 이런 SQL 문장을 "Prepared statement"라 합니다. Prepared statement를 사용하면 해커가 서버의 질의를 변경시켜 서비스를 공격하는 시도 자체를 차단하는 부수적인 효과도 얻을 수 있습니다.

cmdhistory = new FbCommand("INSERT INTO alarm_history (tagname, dt, almtype, almlevel, stat, msg) " +
    "VALUES (@name, @dt, @almtype, @almlevel, @stat, @msg)", conn);
cmdhistory.Parameters.Add("@name", FbDbType.VarChar);
cmdhistory.Parameters.Add("@dt", FbDbType.TimeStamp);
cmdhistory.Parameters.Add("@almtype", FbDbType.SmallInt);
cmdhistory.Parameters.Add("@almlevel", FbDbType.SmallInt);
cmdhistory.Parameters.Add("@stat", FbDbType.SmallInt);
cmdhistory.Parameters.Add("@msg", FbDbType.VarChar);
cmdhistory.Prepare();

위의 코드 예제는 C#에서 파이어버드 ADO.Net 드라이버를 통해서 Prepared statement를 사용한 것으로 주목할 점은 SQL내의 각 변수를 "@"로 지정한 것입니다. 추후 실행 시점에서는 "cmdhistory.Parameters[0].Value=..." 처럼 값을 설정하고 "cmdhistory.ExecuteNonQuery();"로 실행하면 됩니다.

■ 페이징

인터넷의 발달 과정에서 DBMS가 가장 크게 영향받은 부분 중의 하나가 바로 페이징 지원과 연관된 기능일 것입니다. 그만큼 DBMS별로 지원 방법 또한 각양각색입니다. 파이어버드에서는 "FIRST ...SKIP ..."구문을 사용합니다.

SELECT FIRST 15 SKIP 15 * FROM alarm_history ORDER BY dt DESC, tagname;

MySQL의 경우 LIMIT 문장을 SQL 끝부분에 기술해서 "LIMIT 시작위치, 개수"의 형식으로 사용하지만 파이어버드의 경우에는 SELECT 바로 다음에 "FIRST 개수 SKIP 시작위치"의 형식으로 변환해서 사용할 수 있습니다.