drop table test_table;

CREATE TABLE `TEST_TABLE` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`p_id` int(10) unsigned default '0',
`nm` varchar(50),
primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 insert into TEST_TABLE(p_id, nm) values ( 0, '왕');

insert into TEST_TABLE(p_id, nm) values ( 1, '왕자');

insert into TEST_TABLE(p_id, nm) values ( 1, '공주');

insert into TEST_TABLE(p_id, nm) values ( 2, '왕자아들');

insert into TEST_TABLE(p_id, nm) values ( 2, '왕자딸');

insert into TEST_TABLE(p_id, nm) values ( 3, '공주아들');

insert into TEST_TABLE(p_id, nm) values ( 6, '공주아들의 딸');

insert into TEST_TABLE(p_id, nm) values ( 1, '배다른 형제');

 DROP FUNCTION IF EXISTS get_lvl_test_table;

DELIMITER $$
CREATE FUNCTION  get_lvl_test_table() RETURNS INT
    NOT DETERMINISTIC
    READS SQL DATA

BEGIN
    DECLARE v_id INT;
    DECLARE  v_parent INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

    SET v_parent = @id;
    SET v_id = -1;

    IF @id IS NULL THEN
        RETURN NULL;
    END IF;

    LOOP
        SELECT MIN(id) INTO @id
          FROM TEST_TABLE
         WHERE p_id = v_parent
           AND id > v_id;

        IF @id IS NOT NULL OR v_parent = @start_with THEN
            SET @level = @level + 1;
            RETURN @id;
        END IF;

        SET @level := @level - 1;

        SELECT id, p_id
          INTO v_id , v_parent
          FROM TEST_TABLE
         WHERE id = v_parent;
    END LOOP;
END
$$
DELIMITER ;

 SELECT
    CONCAT(REPEAT('  ', level  - 1), d.nm) AS nm
    ,d.id
    ,d.p_id
    ,func.level
FROM
    (SELECT
        get_lvl_TEST_TABLE() AS id, @level AS level
    FROM
        (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars
    JOIN test_table
    WHERE
        @id IS NOT NULL) func
        JOIN
    TEST_TABLE d ON func.id = d.id;

 CREATE TABLE NestedSetMenu
(
nSeq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
strName VARCHAR(30) NOT NULL,
nLeft INT(10) UNSIGNED NOT NULL,
nRight INT(10) UNSIGNED NOT NULL,
eStatus enum('NORMAL','WAIT','PTMP','NTMP') NOT NULL DEFAULT 'NORMAL'
PRIMARY KEY(`nSeq`)
)
ENGINE=MyISAM;

DROP PROCEDURE IF EXISTS showNode;
DELIMITER %%
CREATE PROCEDURE showNode()
BEGIN
	SELECT
		Node.nSeq,
		CONCAT(REPEAT("\t", (COUNT(Parent.nSeq) - 1) ), Node.strName) as strName,
		Node.nLeft,
		Node.nRight
	FROM
		NestedSetMenu AS Node,
			NestedSetMenu AS Parent
	WHERE
		Node.nLeft BETWEEN Parent.nLeft AND Parent.nRight
	GROUP BY Node.nSeq
	ORDER By Node.nLeft;
END %%
DELIMITER ;

-- 계층 구조는 범위를 포함하게 되는 왼쪽 오른쪽 값과 
-- 노드의 위치이동 및 변경시 필요한 상태값을 가지고 있다. 

CREATE TABLE NestedSetMenu
(
	nSeq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	strName VARCHAR(30) NOT NULL,
	nLeft INT(10) UNSIGNED NOT NULL,
	nRight INT(10) UNSIGNED NOT NULL,
	eStatus enum('NORMAL','WAIT') NOT NULL DEFAULT 'NORMAL'
	PRIMARY KEY(`nSeq`)
)
ENGINE=MyISAM;

-- 저장을 위한 기본 구조와 프로시져 
-- 메뉴 추가 프로시져
DROP PROCEDURE IF EXISTS addMenu;
DELIMITER %%
CREATE PROCEDURE addMenu(strMenuName CHAR(30), nParent INT)
BEGIN 
	DECLARE nNewRight INT;
    -- 선택한 부모의 우측 범위 값을 구한다.
	SELECT nRight INTO nNewRight FROM NestedSetMenu WHERE nSeq = nParent;
    -- Node 가 들어갈 자리를 마련하기 위해 들어갈 Node의 우측 범위를 포함하여 큰 값들에 + 2를 해준다.
	UPDATE NestedSetMenu SET nRight = nRight + 2 WHERE nRight >= nN
	UPDATE NestedSetMenu SET nLeft = nLeft + 2 WHERE nLeft > nNewRight;
    
    -- 실제 노드를 입력한다. 
	INSERT INTO NestedSetMenu (nSeq, strName, nLeft, nRight, eStatus) 
	VALUES (DEFAULT, strMenuName, IFNULL(nNewRight, 1), IFNULL(nNewRight + 1, 2), 'NORMAL');
END %%

DELIMITER ;

-- 노드 삭제
DROP PROCEDURE IF EXISTS deleteMenu;
DELIMITER %%
CREATE PROCEDURE deleteMenu(nSequence INT)
BEGIN
	-- 삭제할 노드의 왼쪽, 오른쪽, 범위를 구한다.
	DECLARE nTargetLeft INT;
	DECLARE nTargetRight INT;
	DECLARE nTargetRange INT;

	-- 삭제할 노드의 범위를 구한다.
	SELECT nLeft, nRight, (nRight - nLeft + 1) INTO nTargetLeft, nTargetRight, nTargetRange FROM NestedSetMenu WHERE nSeq = nSequence;
    
    -- 노드를 삭제한다.(자식을 포함하여 삭제)
	DELETE FROM NestedSetMenu WHERE nLeft BETWEEN nTargetLeft AND nTargetRight;
    
    -- 노드의 오른쪽 범위와 왼쪽 범위 보다 큰값들을 삭제 노드 범위 만큼 값을 빼준다.
	UPDATE NestedSetMenu SET nRight = nRight - nTargetRange WHERE nRight > nTargetRight;
	UPDATE NestedSetMenu SET nLeft = nLeft - nTargetRange WHERE nLeft > nTargetLeft;
END %%
DELIMITER ;

-- 노드의 순서를 바꾼다.-- 노드 순위 변경
-- 버그 수정 
-- 주의 점 : 항상 Node 는 좌측에서 우측으로 이동한다. 
-- 만약 우측이 좌측보다 left 값이 작을 경우 
-- 판별을 해 좌우를 변경한 후 실행한다. 

DROP PROCEDURE IF EXISTS changeOrder;
DELIMITER %%
CREATE PROCEDURE changeOrder(nPrevSeq INT, nNextSeq INT)
BEGIN
	DECLARE nPrevLeft INT;
	DECLARE nPrevRight INT;
	DECLARE nPrevWidth INT;
	
	DECLARE nNextLeft INT;
	DECLARE nNextRight INT;
	DECLARE nNextWidth INT;

	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nPrevLeft, nPrevRight, nPrevWidth 
	FROM NestedSetMenu WHERE nSeq = nPrevSeq;

	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nNextLeft, nNextRight, nNextWidth 
	FROM NestedSetMenu WHERE nSeq = nNextSeq;
    -- 이동할 노드의 left, right 를 (타겟 right - 이동할노드 right) 만큼 빼준다.  
    UPDATE NestedSetMenu SET eStatus = 'WAIT' , nLeft = nLeft + (nNextRight -  nPrevRight), nRight = nRight + (nNextRight -  nPrevRight)
     WHERE nLeft BETWEEN nPrevLeft AND nPrevRight; 
    -- nLeft 가 이동할 노드의 right, 타겟 right 에 포함되는것의 left,right 를 이동할 노드의 range 만큰 빼준다.
    UPDATE NestedSetMenu SET nLeft = nLeft - nPrevWidth, nRight = nRight - nPrevWidth WHERE eStatus = 'NORMAL' AND nLeft BETWEEN nPrevRight AND nNextRight; 
    -- status 값을 원래대로 변경한다.
    UPDATE NestedSetMenu SET eStatus = 'NORMAL' WHERE eStatus = 'WAIT';                                           

END %%
DELIMITER ;


-- 노드의 이동
-- 버그 수정 상위 노드로 이동시 메뉴 구조가 깨지는 현상 수정
DROP PROCEDURE IF EXISTS moveNode;
DELIMITER %%
CREATE PROCEDURE moveNode(nSelectSeq INT, nTargetSeq INT)
BEGIN
	DECLARE nSelectLeft INT;
    DECLARE nSelectRight INT;
    DECLARE nSelectWidth INT;
    
	DECLARE nTargetLeft INT;
	DECLARE nTargetRight INT;
	DECLARE nTargetWidth INT;
    
	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nSelectLeft, nSelectRight, nSelectWidth 
	FROM NestedSetMenu WHERE nSeq = nSelectSeq;
    
    -- 이동할 노드 초기화 
    UPDATE NestedSetMenu SET
    	nLeft = nLeft - (nSelectLeft - 1),
    	nRight = nRight - (nSelectLeft - 1),
        eStatus = 'WAIT'
    WHERE 
    	nLeft BETWEEN nSelectLeft AND nSelectRight;
        
    -- 이동할 노드 이외의 노드 Left, Right 값 변경 (선택된 노드 범위만큼 빼준다.)
    UPDATE NestedSetMenu SET
    	nRight = nRight - nSelectWidth
    WHERE
    	nRight > nSelectRight AND eStatus = 'NORMAL';
    UPDATE NestedSetMenu SET
    	nLeft = nLeft - nSelectWidth
    WHERE 
    	nLeft > nSelectRight AND eStatus = 'NORMAL';
    -- 이동할 노드를 삭제한것과 동일
    
    -- 대상 노드 정보 가져오기 
    
    
    
	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nTargetLeft, nTargetRight, nTargetWidth 
	FROM NestedSetMenu WHERE nSeq = nTargetSeq;
    
    -- 삭제한 노드를 타겟 노드 위치에 이동하기 위해 Left, Right 값을 변경한다.
    -- Right 값이 타겟의 Right 값보다 같거나 큰것들을 선택한 노드의 너비 만큼 더해준다.
    UPDATE NestedSetMenu SET
    	nRight = nRight + nSelectWidth
    WHERE 
    	nRight >= nTargetRight AND eStatus = 'NORMAL';
        
    -- Left 값이 타겟의 Right 값보다 큰 것들을 선택한 노드의 너비 만큼 더해준다.
    UPDATE NestedSetMenu SET
    	nLeft = nLeft + nSelectWidth
    WHERE 
    	nLeft > nTargetRight AND eStatus = 'NORMAL';
        
    -- 노드를 타겟 하위로 위치 시키기 위해 타겟의 (nTargetRight - 1) 값만큼 더해준다. 
    UPDATE NestedSetMenu SET
    	nLeft = nLeft + (nTargetRight - 1) ,
    	nRight  = nRight  + (nTargetRight - 1),
        eStatus = 'NORMAL'
    WHERE 
    	eStatus = 'WAIT';
    
    
END %%
DELIMITER ; 

drop table category;

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL,
ord int);

INSERT INTO category
VALUES
    (1,'게임',NULL, 1),
    (2,'두뇌게임/퍼즐',1,3),
    (3,'스포츠',1,2),
    (4,'아케이드',1,1),
    (5,'애플리케이션',null,2),
    (6,'교육',5,3),
    (7,'도구',5, 1),
    (8,'도서',5,2);

SELECT * FROM category ORDER BY category_id;

SELECT t1.name AS lev1, t2.name as lev2,
       concat(lpad(t1.ord, 3, 0), lpad(t2.ord, 3, 0)) ord
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
WHERE t1.parent is null
order by ord
; 

-- 카테고리 테이블을 작성한다.

DROP TABLE IF EXISTS category ;
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);

INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

-- self 조인을 이용한 DEPTH ( LEVEL ) 구현 쿼리
-- 일반적인 방법이다.
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

-- 레벨의 마지막 leaf node 만을 조회
 
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

-- 한개의 카테고리에 대해 상위 카테고리들 조회
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';
 

 drop table test_table;

CREATE TABLE `TEST_TABLE` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`p_id` int(10) unsigned default '0',
`nm` varchar(50),
primary key(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 insert into TEST_TABLE(p_id, nm) values ( 0, '왕');

insert into TEST_TABLE(p_id, nm) values ( 1, '왕자');

insert into TEST_TABLE(p_id, nm) values ( 1, '공주');

insert into TEST_TABLE(p_id, nm) values ( 2, '왕자아들');

insert into TEST_TABLE(p_id, nm) values ( 2, '왕자딸');

insert into TEST_TABLE(p_id, nm) values ( 3, '공주아들');

insert into TEST_TABLE(p_id, nm) values ( 6, '공주아들의 딸');

insert into TEST_TABLE(p_id, nm) values ( 1, '배다른 형제');

 DROP FUNCTION IF EXISTS get_lvl_test_table;

DELIMITER $$
CREATE FUNCTION  get_lvl_test_table() RETURNS INT
    NOT DETERMINISTIC
    READS SQL DATA

BEGIN
    DECLARE v_id INT;
    DECLARE  v_parent INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

    SET v_parent = @id;
    SET v_id = -1;

    IF @id IS NULL THEN
        RETURN NULL;
    END IF;

    LOOP
        SELECT MIN(id) INTO @id
          FROM TEST_TABLE
         WHERE p_id = v_parent
           AND id > v_id;

        IF @id IS NOT NULL OR v_parent = @start_with THEN
            SET @level = @level + 1;
            RETURN @id;
        END IF;

        SET @level := @level - 1;

        SELECT id, p_id
          INTO v_id , v_parent
          FROM TEST_TABLE
         WHERE id = v_parent;
    END LOOP;
END
$$
DELIMITER ;

 SELECT
    CONCAT(REPEAT('  ', level  - 1), d.nm) AS nm
    ,d.id
    ,d.p_id
    ,func.level
FROM
    (SELECT
        get_lvl_TEST_TABLE() AS id, @level AS level
    FROM
        (SELECT @start_with:=0, @id:=@start_with, @level:=0) vars
    JOIN test_table
    WHERE
        @id IS NOT NULL) func
        JOIN
    TEST_TABLE d ON func.id = d.id;

 CREATE TABLE NestedSetMenu
(
nSeq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
strName VARCHAR(30) NOT NULL,
nLeft INT(10) UNSIGNED NOT NULL,
nRight INT(10) UNSIGNED NOT NULL,
eStatus enum('NORMAL','WAIT','PTMP','NTMP') NOT NULL DEFAULT 'NORMAL'
PRIMARY KEY(`nSeq`)
)
ENGINE=MyISAM;

DROP PROCEDURE IF EXISTS showNode;
DELIMITER %%
CREATE PROCEDURE showNode()
BEGIN
	SELECT
		Node.nSeq,
		CONCAT(REPEAT("\t", (COUNT(Parent.nSeq) - 1) ), Node.strName) as strName,
		Node.nLeft,
		Node.nRight
	FROM
		NestedSetMenu AS Node,
			NestedSetMenu AS Parent
	WHERE
		Node.nLeft BETWEEN Parent.nLeft AND Parent.nRight
	GROUP BY Node.nSeq
	ORDER By Node.nLeft;
END %%
DELIMITER ;

-- 계층 구조는 범위를 포함하게 되는 왼쪽 오른쪽 값과 
-- 노드의 위치이동 및 변경시 필요한 상태값을 가지고 있다. 

CREATE TABLE NestedSetMenu
(
	nSeq INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	strName VARCHAR(30) NOT NULL,
	nLeft INT(10) UNSIGNED NOT NULL,
	nRight INT(10) UNSIGNED NOT NULL,
	eStatus enum('NORMAL','WAIT') NOT NULL DEFAULT 'NORMAL'
	PRIMARY KEY(`nSeq`)
)
ENGINE=MyISAM;

-- 저장을 위한 기본 구조와 프로시져 
-- 메뉴 추가 프로시져
DROP PROCEDURE IF EXISTS addMenu;
DELIMITER %%
CREATE PROCEDURE addMenu(strMenuName CHAR(30), nParent INT)
BEGIN 
	DECLARE nNewRight INT;
    -- 선택한 부모의 우측 범위 값을 구한다.
	SELECT nRight INTO nNewRight FROM NestedSetMenu WHERE nSeq = nParent;
    -- Node 가 들어갈 자리를 마련하기 위해 들어갈 Node의 우측 범위를 포함하여 큰 값들에 + 2를 해준다.
	UPDATE NestedSetMenu SET nRight = nRight + 2 WHERE nRight >= nN
	UPDATE NestedSetMenu SET nLeft = nLeft + 2 WHERE nLeft > nNewRight;
    
    -- 실제 노드를 입력한다. 
	INSERT INTO NestedSetMenu (nSeq, strName, nLeft, nRight, eStatus) 
	VALUES (DEFAULT, strMenuName, IFNULL(nNewRight, 1), IFNULL(nNewRight + 1, 2), 'NORMAL');
END %%

DELIMITER ;

-- 노드 삭제
DROP PROCEDURE IF EXISTS deleteMenu;
DELIMITER %%
CREATE PROCEDURE deleteMenu(nSequence INT)
BEGIN
	-- 삭제할 노드의 왼쪽, 오른쪽, 범위를 구한다.
	DECLARE nTargetLeft INT;
	DECLARE nTargetRight INT;
	DECLARE nTargetRange INT;

	-- 삭제할 노드의 범위를 구한다.
	SELECT nLeft, nRight, (nRight - nLeft + 1) INTO nTargetLeft, nTargetRight, nTargetRange FROM NestedSetMenu WHERE nSeq = nSequence;
    
    -- 노드를 삭제한다.(자식을 포함하여 삭제)
	DELETE FROM NestedSetMenu WHERE nLeft BETWEEN nTargetLeft AND nTargetRight;
    
    -- 노드의 오른쪽 범위와 왼쪽 범위 보다 큰값들을 삭제 노드 범위 만큼 값을 빼준다.
	UPDATE NestedSetMenu SET nRight = nRight - nTargetRange WHERE nRight > nTargetRight;
	UPDATE NestedSetMenu SET nLeft = nLeft - nTargetRange WHERE nLeft > nTargetLeft;
END %%
DELIMITER ;

-- 노드의 순서를 바꾼다.-- 노드 순위 변경
-- 버그 수정 
-- 주의 점 : 항상 Node 는 좌측에서 우측으로 이동한다. 
-- 만약 우측이 좌측보다 left 값이 작을 경우 
-- 판별을 해 좌우를 변경한 후 실행한다. 

DROP PROCEDURE IF EXISTS changeOrder;
DELIMITER %%
CREATE PROCEDURE changeOrder(nPrevSeq INT, nNextSeq INT)
BEGIN
	DECLARE nPrevLeft INT;
	DECLARE nPrevRight INT;
	DECLARE nPrevWidth INT;
	
	DECLARE nNextLeft INT;
	DECLARE nNextRight INT;
	DECLARE nNextWidth INT;

	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nPrevLeft, nPrevRight, nPrevWidth 
	FROM NestedSetMenu WHERE nSeq = nPrevSeq;

	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nNextLeft, nNextRight, nNextWidth 
	FROM NestedSetMenu WHERE nSeq = nNextSeq;
    -- 이동할 노드의 left, right 를 (타겟 right - 이동할노드 right) 만큼 빼준다.  
    UPDATE NestedSetMenu SET eStatus = 'WAIT' , nLeft = nLeft + (nNextRight -  nPrevRight), nRight = nRight + (nNextRight -  nPrevRight)
     WHERE nLeft BETWEEN nPrevLeft AND nPrevRight; 
    -- nLeft 가 이동할 노드의 right, 타겟 right 에 포함되는것의 left,right 를 이동할 노드의 range 만큰 빼준다.
    UPDATE NestedSetMenu SET nLeft = nLeft - nPrevWidth, nRight = nRight - nPrevWidth WHERE eStatus = 'NORMAL' AND nLeft BETWEEN nPrevRight AND nNextRight; 
    -- status 값을 원래대로 변경한다.
    UPDATE NestedSetMenu SET eStatus = 'NORMAL' WHERE eStatus = 'WAIT';                                           

END %%
DELIMITER ;


-- 노드의 이동
-- 버그 수정 상위 노드로 이동시 메뉴 구조가 깨지는 현상 수정
DROP PROCEDURE IF EXISTS moveNode;
DELIMITER %%
CREATE PROCEDURE moveNode(nSelectSeq INT, nTargetSeq INT)
BEGIN
	DECLARE nSelectLeft INT;
    DECLARE nSelectRight INT;
    DECLARE nSelectWidth INT;
    
	DECLARE nTargetLeft INT;
	DECLARE nTargetRight INT;
	DECLARE nTargetWidth INT;
    
	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nSelectLeft, nSelectRight, nSelectWidth 
	FROM NestedSetMenu WHERE nSeq = nSelectSeq;
    
    -- 이동할 노드 초기화 
    UPDATE NestedSetMenu SET
    	nLeft = nLeft - (nSelectLeft - 1),
    	nRight = nRight - (nSelectLeft - 1),
        eStatus = 'WAIT'
    WHERE 
    	nLeft BETWEEN nSelectLeft AND nSelectRight;
        
    -- 이동할 노드 이외의 노드 Left, Right 값 변경 (선택된 노드 범위만큼 빼준다.)
    UPDATE NestedSetMenu SET
    	nRight = nRight - nSelectWidth
    WHERE
    	nRight > nSelectRight AND eStatus = 'NORMAL';
    UPDATE NestedSetMenu SET
    	nLeft = nLeft - nSelectWidth
    WHERE 
    	nLeft > nSelectRight AND eStatus = 'NORMAL';
    -- 이동할 노드를 삭제한것과 동일
    
    -- 대상 노드 정보 가져오기 
    
    
    
	SELECT nLeft, nRight, (nRight - nLeft + 1) 
	INTO nTargetLeft, nTargetRight, nTargetWidth 
	FROM NestedSetMenu WHERE nSeq = nTargetSeq;
    
    -- 삭제한 노드를 타겟 노드 위치에 이동하기 위해 Left, Right 값을 변경한다.
    -- Right 값이 타겟의 Right 값보다 같거나 큰것들을 선택한 노드의 너비 만큼 더해준다.
    UPDATE NestedSetMenu SET
    	nRight = nRight + nSelectWidth
    WHERE 
    	nRight >= nTargetRight AND eStatus = 'NORMAL';
        
    -- Left 값이 타겟의 Right 값보다 큰 것들을 선택한 노드의 너비 만큼 더해준다.
    UPDATE NestedSetMenu SET
    	nLeft = nLeft + nSelectWidth
    WHERE 
    	nLeft > nTargetRight AND eStatus = 'NORMAL';
        
    -- 노드를 타겟 하위로 위치 시키기 위해 타겟의 (nTargetRight - 1) 값만큼 더해준다. 
    UPDATE NestedSetMenu SET
    	nLeft = nLeft + (nTargetRight - 1) ,
    	nRight  = nRight  + (nTargetRight - 1),
        eStatus = 'NORMAL'
    WHERE 
    	eStatus = 'WAIT';
    
    
END %%
DELIMITER ; 

drop table category;

CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL,
ord int);

INSERT INTO category
VALUES
    (1,'게임',NULL, 1),
    (2,'두뇌게임/퍼즐',1,3),
    (3,'스포츠',1,2),
    (4,'아케이드',1,1),
    (5,'애플리케이션',null,2),
    (6,'교육',5,3),
    (7,'도구',5, 1),
    (8,'도서',5,2);

SELECT * FROM category ORDER BY category_id;

SELECT t1.name AS lev1, t2.name as lev2,
       concat(lpad(t1.ord, 3, 0), lpad(t2.ord, 3, 0)) ord
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
WHERE t1.parent is null
order by ord
; 

-- 카테고리 테이블을 작성한다.

DROP TABLE IF EXISTS category ;
CREATE TABLE category(
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
parent INT DEFAULT NULL);

INSERT INTO category
VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
(7,'MP3 PLAYERS',6),(8,'FLASH',7),
(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;

-- self 조인을 이용한 DEPTH ( LEVEL ) 구현 쿼리
-- 일반적인 방법이다.
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

-- 레벨의 마지막 leaf node 만을 조회
 
SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

-- 한개의 카테고리에 대해 상위 카테고리들 조회
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';