MariaDB Dynamic Column Test

MariaDB 책을 보다 Dynamic Column 이란는게 있어 테스트 한번 해봤음

/**
    MariaDB Dynamic Column Test 
    
    func:   지원하는 함수 
            column_create
            column_add
            column_get
            column_delete
            column_list
            column_join
            column_exists
            column_check
            
   Type:         
            지원되는 타입
             Type  Description
       BINARY[(N)] A variable-length binary string
         CHAR[(N)] A variable-length string
              DATE A 3-byte date
     DATETIME[(D)] A 9-byte date and time. Microseconds are supported
  DECIMAL[(M[,D])] A variable-length binary decimal
           INTEGER A variable-length signed integer, up to 64 bits in length
  SIGNED [INTEGER] A variable-length signed integer, up to 64 bits in length
         TIME[(D)] A 6-byte time. Microseconds are supported and it may be  negative
UNSIGNED [INTEGER] A variable-length unsigned integer, up to 64 bits in length

*/

--
-- 01. Test Table Create
--
drop database if exists test;
create database test;

drop table if exists tbl_dyn_col_test;
CREATE TABLE `test`.`tbl_dyn_col_test`
(
 `id`  int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `dyn_cols` BLOB comment '동적 칼럼'
) engine = innodb default charset=utf8 comment='동적칼럼 테스트 테이블'
;

--
-- 02. Test Data Insert
--

INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'blue' AS CHAR,
      'size',
      'XL' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'blue' AS CHAR,
      'size',
      'L' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'black' AS CHAR,
      'size',
      'M' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      'flashlight',
      'color',
      'black' AS CHAR,
      'size',
      'AAA' AS CHAR,
      'num',
      2 AS INT
    )
  ),
  (
    COLUMN_CREATE ('name', 'shovel', 'length', '5')
  );

--
-- 02.1 결과 검증
--

SELECT
  id,
  column_list (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  column_list(dyn_cols)        
------  -----------------------------
     1  `name`,`size`,`color`        
     2  `name`,`size`,`color`        
     3  `name`,`size`,`color`        
     4  `num`,`name`,`size`,`color`  
     5  `name`,`length`            ]
     
--
-- 03.칼럼 추가 
--  

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_ADD (dyn_cols, 'name', 'torch')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'flashlight';

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_ADD (dyn_cols, 'length', 6)
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'torch';


    id  column_list (dyn_cols)                
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`,`length`      
     
--
-- 04.칼럼 삭제
--

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_DELETE (dyn_cols, 'length')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'shovel';


    id  column_list (dyn_cols)                
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`                                                 

--
-- 05.칼럼 조회 
--


SELECT
  id,
  COLUMN_GET (dyn_cols, 'name' AS CHAR) AS 'name',
  COLUMN_GET (dyn_cols, 'color' AS CHAR) AS 'color',
  COLUMN_GET (dyn_cols, 'size' AS CHAR) AS 'size',
  COLUMN_GET (dyn_cols, 'num' AS INT) AS 'num'
FROM
  tbl_dyn_col_test;
  
  
    id  name     color   size       num  
------  -------  ------  ------  --------
     1  t-shirt  blue    XL        (NULL)
     2  t-shirt  blue    L         (NULL)
     3  t-shirt  black   M         (NULL)
     4  torch    black   AAA            2
     5  shovel   (NULL)  (NULL)    (NULL)


--
-- 05.2 Json 형식
--
     
SELECT
  id,
  COLUMN_JSON (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  COLUMN_JSON (dyn_cols)                                            
------  ------------------------------------------------------------------
     1  {"name":"t-shirt","size":"XL","color":"blue"}                     
     2  {"name":"t-shirt","size":"L","color":"blue"}                      
     3  {"name":"t-shirt","size":"M","color":"black"}                     
     4  {"num":2,"name":"torch","size":"AAA","color":"black","length":6}  
     5  {"name":"shovel"}                                                 
     
--
-- 05.3 칼럼 존재 여부 확인
--

SELECT
  id,
  COLUMN_EXISTS (dyn_cols, 'num')
FROM
  tbl_dyn_col_test;

    id  COLUMN_EXISTS (dyn_cols, 'num')  
------  ---------------------------------
     1                                  0
     2                                  0
     3                                  0
     4                                  1
     5                                  0
     

SELECT
  id,
  COLUMN_CHECK (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  COLUMN_CHECK (dyn_cols)  
------  -------------------------
     1                          1
     2                          1
     3                          1
     4                          1
     5                          1
     
--
-- 06. 내부 동적 칼럼
--

INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
  (
    COLUMN_CREATE (
      'type',
      'parent',
      'name',
      'Mary',
      'child1',
      COLUMN_CREATE ('name', 'Sue', 'eyes', 'brown'),
      'child2',
      COLUMN_CREATE (
        'name',
        'Bob',
        'grandchild',
        COLUMN_CREATE ('name', 'baby')
      )
    )
  );

select
    id,
    column_list(dyn_cols)
from
    tbl_dyn_col_test
;

    id  COLUMN_list(dyn_cols)                 
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`                                
     6  `name`,`type`,`child1`,`child2`  


--
-- 06.2 조회 방법
--     

SELECT
  COLUMN_GET (dyn_cols, 'child1' AS CHAR) AS 'child1',
  COLUMN_GET (dyn_cols, 'child2' AS CHAR) AS 'child2'
FROM
  tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';


child1                           child2                                             
-------------------------------  ---------------------------------------------------
                                                                                    

SELECT
  COLUMN_JSON (dyn_cols)
FROM
  tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';

COLUMN_JSON (dyn_cols)                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------
{"name":"Mary","type":"parent","child1":{"eyes":"brown","name":"Sue"},"child2":{"name":"Bob","grandchild":{"name":"baby"}}}