계층적 질의를 통한 계층현 게시판 설계

여기서 말하는 계층형 게시판이란 게시글의 답글, 답글의 답글 등 계층적인 구조를 가진 게시판을 말하고, 구현하는 로직이 복잡하여 현재 대부분의 포털 사이트에서 사용되는 게시판에서는 지원되지 않고 있다.

계층적 질의

관계 형 데이터베이스는 2차원 테이블 구조에 의해 모든 데이터를 저장한다. 하지만 실무에서는 기업의 조직도나 게시판 구조와 같은 계층적인 데이터 구조를 많이 사용하고 있다. 따라서 평면적인 구조를 가지는 테이블에서도 계층적인 데이터를 조회할 수 있는 방법이 필요하다.

특히, 게시판 구조에서 특정 게시물의 답글이나 답글의 답글 등을 구현하고자 할 때, 복잡한 연산을 필요로 한다. 하지만 CUBRID나 Oracle, MS-SQL에서 지원하는 계층적 질의를 사용하면 매우 간단하게 구현할 수 있다.

다음 질의에서 article 테이블에 삽입될 데이터들의 구조는 아래 [그림 1]과 같다.

[그림 1]

구문

SELECT column_list
FROM table_joins | tables
[WHERE join_conditions and/or filtering_conditions]
[START WITH condition]
CONNECT BY [NOCYCLE] condition

아래는 게시물의 정보를 담을 수 있는 post 테이블을 생성하고, 관련된 post 정보를 입력하는 예제이다.

-- 게시글 테이블 생성
CREATE TABLE "article"(
     "mgrid" integer,
     "id" INTEGER AUTO_INCREMENT NOT NULL,
     "subject" character varying(50) NOT NULL,
     "writer" character varying(10) NOT NULL,
     "date" DATE NOT NULL,
     "count" INTEGER DEFAULT 0 NOT NULL
);

-- LEVEL_1 게시글 추가
INSERT INTO "article" ("subject", "writer", "date") VALUES ('1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("subject", "writer", "date") VALUES ('2번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("subject", "writer", "date") VALUES ('3번 글', '문학청년', SYSDATE);

-- LEVEL_2 게시글 추가
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (1, '1-1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (2, '2-1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (2, '2-2번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (2, '2-3번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (3, '3-1번 글', '문학청년', SYSDATE);

-- LEVEL_3 게시글 추가
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (6, '2-2-1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (7, '2-3-1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (8, '3-1-1번 글', '문학청년', SYSDATE);

-- LEVEL_4 게시글 추가
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (9, '2-2-1-1번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (9, '2-2-1-2번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (9, '2-2-1-3번 글', '문학청년', SYSDATE);
INSERT INTO "article" ("mgrid", "subject", "writer", "date") VALUES (9, '2-2-1-4번 글', '문학청년', SYSDATE);

[그림 1]을 다시 한번 더 살펴 보면 이해가 쉬울 것이다. 그렇다면 지금 데이터들을 계층적 질의를 사용하지 않고 조회해 본 뒤, 결과 값을 확인하고 계층적 질의를 사용하여 조회해보자.

-- 계층적 질의를 사용하지 않음
SELECT * FROM article;

-- 계층적 질의를 사용함
SELECT * FROM article
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id DESC;

결과 테이블

NO	mgrid	id	subject	writer	date	count
1	(NULL)	1	1번 글	문학청년	2009-12-22	0
2	(NULL)	2	2번 글	문학청년	2009-12-22	0
3	(NULL)	3	3번 글	문학청년	2009-12-22	0
4	1	4	1-1번 글	문학청년	2009-12-22	0
5	2	5	2-1번 글	문학청년	2009-12-22	0
6	2	6	2-2번 글	문학청년	2009-12-22	0
7	2	7	2-3번 글	문학청년	2009-12-22	0
8	3	8	3-1번 글	문학청년	2009-12-22	0
9	6	9	2-2-1번 글	문학청년	2009-12-22	0
10	7	10	2-3-1번 글	문학청년	2009-12-22	0
11	8	11	3-1-1번 글	문학청년	2009-12-22	0
12	9	12	2-2-1-1번 글	문학청년	2009-12-22	0
13	9	13	2-2-1-2번 글	문학청년	2009-12-22	0
14	9	14	2-2-1-3번 글	문학청년	2009-12-22	0
15	9	15	2-2-1-4번 글	문학청년	2009-12-22	0

[계층적 질의를 사용하지 않았을 때]

NO	mgrid	id	subject	writer	date	count
1	(NULL)	3	3번 글	문학청년	2009-12-22	0
2	3	8	3-1번 글	문학청년	2009-12-22	0
3	8	11	3-1-1번 글	문학청년	2009-12-22	0
4	(NULL)	2	2번 글	문학청년	2009-12-22	0
5	2	7	2-3번 글	문학청년	2009-12-22	0
6	7	10	2-3-1번 글	문학청년	2009-12-22	0
7	2	6	2-2번 글	문학청년	2009-12-22	0
8	6	9	2-2-1번 글	문학청년	2009-12-22	0
9	9	15	2-2-1-4번 글	문학청년	2009-12-22	0
10	9	14	2-2-1-3번 글	문학청년	2009-12-22	0
11	9	13	2-2-1-2번 글	문학청년	2009-12-22	0
12	9	12	2-2-1-1번 글	문학청년	2009-12-22	0
13	2	5	2-1번 글	문학청년	2009-12-22	0
14	(NULL)	1	1번 글	문학청년	2009-12-22	0
15	1	4	1-1번 글	문학청년	2009-12-22	0

[계층적 질의를 사용했을 때]

결과 테이블에서 각 행들의 mgrid 컬럼을 보면 자신의 상위 post의 id가 저장되어 있다. 만약에 post 자신이 가장 최상위 post라면 mgrid 컬럼은 null 값을 갖는다. 단순히 질의 결과만을 보면 이해가 어려울 수도 있으니 아래 [그림 2]와 같이 실제로 게시판에 적용된 모습과 출력된 질의 결과물을 비교해보자.

[그림 2]

위의 예제는 LEVEL_4 까지만 보여지지만, 그 이상의 레벨도 쉽게 구현할 수가 있다. 이러한 기능을 구현하고자 할 때, 계층적 질의를 사용하지 않는다면 쿼리가 매우 복잡해지거나 어플리케이션 상에서 별도로 로직 처리를 해줘야 할 것이다.

그렇다면 지금부터 각 구문에 대해 좀 더 자세히 알아보도록 하자.

(1) START WITH … CONNECT BY 절

START WITH 절
START WITH 절은 계층 관계가 시작되는 루트 행(root row)을 지정하기 위한 것으로, START WITH 절 다음에 계층 관계를 검색하기 위한 조건 식을 포함한다. 만약, START WITH 절 다음에 위치하는 조건 식이 생략되면 대상 테이블 내에 존재하는 모든 행을 루트 행으로 간주한다.

CONNECT BY [NOCYCLE] PRIOR 절

	PRIOR
			CONNECT BY 조건 식은 한 쌍의 행에 대한 상-하 계층 관계(부모-자식 관계)를 정의 하기 위한 것으로, 조건 식 내에서 하나는 부모(parent)로 지정되고, 다른 하나는 자식(child)으로 지정된다. 이처럼 행 간의 부모-자식 간 계층 관계를 정의하기 위하여 CONNECT BY 조건식 내에 PRIOR 연산자를 이용하여 부모 행의 컬럼 값을 지정한다. 즉, 부모 행의 컬럼 값과 같은 컬럼 값을 가지는 모든 행은 자식 행이 된다.

	NOCYCLE
			CONNECT BY 절의 조건 식에 따른 계층 질의 결과는 루프를 포함할 수 있으며, 이것은 계층 트리를 생성할 때 무한 루프를 발생시키는 원인이 될 수 있다. 따라서, CUBRID는 루프를 발견하면 기본적으로 오류를 반환하고, 특수 연산자인 NOCYCLE이 CONNECT BY 절에 명시된 경우에는 오류를 발생시키지 않고 해당 루프에 의해 검색된 결과를 출력한다.

(2) ORDER SIBILINGS BY 절

ORDER SIBLINGS BY 절은 계층 질의 결과 값들의 계층 정보를 유지하면서 특정 컬럼을 기준으로 오름차순 또는 내림차순으로 데이터를 정렬하기 위해 사용할 수 있는 절로서, 동일한 부모를 가진 자식 행들을 정렬할 수 있다. 이처럼 계층적 질의 문에서 데이터의 계층적 순서를 파악하기 위해 사용되는 ORDER BY 절의 구문은 다음과 같다.

구문

ORDER SIBLINGS BY col_1 [ASC|DESC] [, col_2 [ASC |DESC] […[, col_n [ASC|DESC]]…]]

설명
계층 질의 결과는 기본적으로 ORDER BY 절에 명시된 컬럼 리스트를 따라 정렬된 부모와 그 부모의 자식 노드들이 연속적으로 출력되며, 동일 부모를 가지는 형제는 명시된 정렬 순서에 따라 정렬되어 출력된다.

계층 질의 문에서 사용 가능한 표현 칼럼

LEVEL

설명
LEVEL은 계층 질의 결과 행의 깊이 레벨(depth)을 나타내는 표현 컬럼이다. 루트 노드의 LEVEL은 1이며, 하위 자식 노드의 LEVEL은 2가 된다.
LEVEL 표현 컬럼은 SELECT문 내의 WHERE절, ORDER BY절, GROUP BY… HAVING절에서 사용 가능하며, 집계 함수를 이용하는 구문에서도 사용 가능하다.

SQL 문

-- LEVEL이 2인 데이터를 조회하는 질의 문

SELECT "mgrid", "id", "subject", "writer", "date", "count", LEVEL FROM article
WHERE LEVEL=2
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id DESC;

결과 테이블

NO	mgrid	id	subject	writer	date	count	level
1	3	8	3-1번 글	문학청년	2009-12-22	0	2
2	2	7	2-3번 글	문학청년	2009-12-22	0	2
3	2	6	2-2번 글	문학청년	2009-12-22	0	2
4	2	5	2-1번 글	문학청년	2009-12-22	0	2
5	1	4	1-1번 글	문학청년	2009-12-22	0	2

결과 테이블에 나온 LEVEL 칼럼을 [그림 1]의 LEVEL과 비교해서 살펴보자.

CONNECT_BY_ISLEAF

설명
CONNECT_BY_ISLEAF는 계층 질의 결과 행이 잎 노드(Leaf node)인지 가르키는 표현 컬럼이다. 계층 구조에서 현재 행이 잎 노드이면 1을 반환하고, 그렇지 않으면 0을 반환한다.

SQL 문

-- 현재 행이 잎 노드인 것만 조회하는 질의 문
SELECT "mgrid", "id", "subject", "writer", "date", "count", CONNECT_BY_ISLEAF FROM article
WHERE CONNECT_BY_ISLEAF=1
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id DESC;

결과 테이블

NO	mgrid	id	subject	writer	date	count	connect_by_isleaf
1	8	11	3-1-1번 글	문학청년	2009-12-22	0	1
2	7	10	2-3-1번 글	문학청년	2009-12-22	0	1
3	9	15	2-2-1-4번 글	문학청년	2009-12-22	0	1
4	9	14	2-2-1-3번 글	문학청년	2009-12-22	0	1
5	9	13	2-2-1-2번 글	문학청년	2009-12-22	0	1
6	9	12	2-2-1-1번 글	문학청년	2009-12-22	0	1
7	2	5	2-1번 글	문학청년	2009-12-22	0	1
8	1	4	1-1번 글	문학청년	2009-12-22	0	1

그 밖에 활용 방법
네이버나 다음에서 사용되는 게시판은 삭제 하고자 하는 게시글에 답글이 있을 경우, 삭제가 되지 않는다. 만약에 자신이 구현한 게시판에서도 그러한 기능을 구현하고자 한다면, CONNECT_BY_ISLEAF 표현 컬럼의 결과 값이 1일 경우에만 삭제가 되도록 어플리케이션 상에서 구현하면 된다.

계층 질의 문에서 사용 가능한 연산자

계층 질의 문에서는 컬럼 연산을 위해 아래와 같은 특수 연산자를 사용할 수 있으며, 이러한 특수 연산자를 이용하여 해당 컬럼의 부모 행 값 또는 루트 행의 값을 반환할 수 있다.

CONNECT_BY_ROOT 연산자

설명
CONNECT_BY_ROOT은 컬럼 값으로 루트 행의 값을 반환한다.
이 연산자는 SELECT문 내의 WHERE절 및 ORDER BY절에서 사용할 수 있다.

SQL 문

-- id가 2번인 부모 글의 자식 글들을 조회하라.
SELECT "mgrid", "id", "subject", "writer", "date", "count", CONNECT_BY_ROOT id AS parent FROM article
WHERE CONNECT_BY_ROOT id = 2
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id;

결과 테이블

NO	mgrid	id	subject	writer	date	count	parent
1	(NULL)	2	2번 글	문학청년	2009-12-22	0	2
2	2	5	2-1번 글	문학청년	2009-12-22	0	2
3	2	6	2-2번 글	문학청년	2009-12-22	0	2
4	6	9	2-2-1번 글	문학청년	2009-12-22	0	2
5	9	12	2-2-1-1번 글	문학청년	2009-12-22	0	2
6	9	13	2-2-1-2번 글	문학청년	2009-12-22	0	2
7	9	14	2-2-1-3번 글	문학청년	2009-12-22	0	2
8	9	15	2-2-1-4번 글	문학청년	2009-12-22	0	2
9	2	7	2-3번 글	문학청년	2009-12-22	0	2
10	7	10	2-3-1번 글	문학청년	2009-12-22	0	2

PRIOR 연산자

설명
PRIOR 연산자는 컬럼 값으로 부모 행의 값을 반환한다.
하지만, 루트 행에 대해서는 컬럼 값으로 NULL 값을 반환하며, SELECT문 내의 WHERE절. ORDER BY절, 및 CONNECT BY 절에서 사용할 수 있다.

SQL 문

-- id값이 14인 게시글의 상위 게시글에 대한 정보를 조회하라.
SELECT PRIOR "id", PRIOR "subject", PRIOR "writer", PRIOR "date", PRIOR "count" FROM "article"
WHERE id=14
START WITH mgrid IS NULL
CONNECT BY PRIOR id=mgrid
ORDER SIBLINGS BY id;

결과 테이블

NO	prior id	prior subject	prior writer	prior date	prior count
1	9	2-2-1번 글	문학청년	2009-12-22	0

계층 질의 문에서 사용 가능한 함수

CUBRID는 계층 구조를 가지는 데이터를 출력하기 위하여 다음의 함수를 지원한다.

SYS_CONNECT_BY_PATH 함수

구문

SYS_CONNECT_BY_PATH (column_name, separator_char)

설명
SYS_CONNECT_BY_PATH 함수는 루트 행으로부터 해당 행까지의 상-하 관계의 PATH를 문자열로 반환하는 함수이다. 이 때, 함수의 인자로 지정되는 COLUMN과 구분자는 문자형 타입이어야 하며, 각 PATH는 지정된 구분자에 의해 구분되어 연쇄적으로 출력된다.
이 함수는 SELECT문 내의 WHERE절과 ORDER BY절에서 사용할 수 있다.

SQL 문

-- 구분자를 이용하여 루트 행으로부터 해당 행까지 PATH를 확인하기
SELECT "mgrid", "id", "subject", SYS_CONNECT_BY_PATH(subject, '/') AS hierarchy FROM article
     START WITH mgrid IS NULL
     CONNECT BY PRIOR id=mgrid
     ORDER SIBLINGS BY id;

결과 테이블

NO	mgrid	id	subject	hierarchy
1	(NULL)	1	1번 글	/1번 글
2	1	4	1-1번 글	/1번 글/1-1번 글
3	(NULL)	2	2번 글	/2번 글
4	2	5	2-1번 글	/2번 글/2-1번 글
5	2	6	2-2번 글	/2번 글/2-2번 글
6	6	9	2-2-1번 글	/2번 글/2-2번 글/2-2-1번 글
7	9	12	2-2-1-1번 글	/2번 글/2-2번 글/2-2-1번 글/2-2-1-1번 글
8	9	13	2-2-1-2번 글	/2번 글/2-2번 글/2-2-1번 글/2-2-1-2번 글
9	9	14	2-2-1-3번 글	/2번 글/2-2번 글/2-2-1번 글/2-2-1-3번 글
10	9	15	2-2-1-4번 글	/2번 글/2-2번 글/2-2-1번 글/2-2-1-4번 글
11	2	7	2-3번 글	/2번 글/2-3번 글
12	7	10	2-3-1번 글	/2번 글/2-3번 글/2-3-1번 글
13	(NULL)	3	3번 글	/3번 글
14	3	8	3-1번 글	/3번 글/3-1번 글
15	8	11	3-1-1번 글	/3번 글/3-1번 글/3-1-1번 글

계층 질의 문 실전 응용

계층 질의 문은 단순히 하나의 테이블에서의 계층적 구조가 아닌 다른 테이블과 조인하여 구현할 수도 있다. 예를 들어 특정 게시글에 포함된 덧글의 계층적 구조도 매우 간단하게 표현할 수 있다.

그럼 지금, 덧글의 정보를 담는 comment 테이블을 생성하고, 2번 게시글에 포함된 덧글들을 삽입하고, 조회하는 질의 문을 작성해보자.

[그림 3]은 2번 글에 포함된 덧글의 계층 구조이다.

[그림 3]

테이블 생성 및 데이터 삽입 SQL문

-- comment 테이블 생성
CREATE TABLE "comment"(
     "artid" INTEGER NOT NULL,
     "mgrid" integer,
     "id" INTEGER AUTO_INCREMENT NOT NULL,
     "content" character varying(200) NOT NULL,
     "writer" character varying(20) NOT NULL,
     "date" DATE NOT NULL
);

-- LEVEL_1 덧글 추가
INSERT INTO "comment" ("artid", "content", "writer", "date")

VALUES (2, '1번 덧글', '문학청년', sysdate);

INSERT INTO "comment" ("artid", "content", "writer", "date")

VALUES (2, '2번 덧글', '문학청년', sysdate);

INSERT INTO "comment" ("artid", "content", "writer", "date")

VALUES (2, '3번 덧글', '문학청년', sysdate);

INSERT INTO "comment" ("artid", "content", "writer", "date")

VALUES (2, '4번 덧글', '문학청년', sysdate);

-- LEVEL_2 덧글 추가
INSERT INTO "comment" ("artid", "mgrid", "content", "writer", "date")

VALUES (2, 2, '2-1번 덧글', '문학청년', sysdate);