====== H2 ======
<WRAP left notice 80%>
  * description : H2 데이터 베이스 사용 방법
  * author      : 주레피
  * email       : dhan@repia.com
  * lastupdate  : 2020-04-02
</WRAP>
<WRAP clear/>

===== DML =====
테이블 목록 확인하기
<code sql>
sql> show tables;
</code>
\\
컬럼 목록 확인하기
<code sql>
sql> show columns from T_LOG_SYSTEM;
</code>

===== RSA DDL =====
Related
<code sql>
CREATE TABLE PUBLIC.TB_RELATED (
	ID INTEGER NOT NULL,
	MORPH VARCHAR(200),
	KEYWORD VARCHAR(512),
	DISPLAY_KEYWORD VARCHAR(255),
	DISPLAY_EXCEPT_KEYWORD VARCHAR(255),
	EXCEPT_KEYWORD VARCHAR(100),
	ONLY_DISPLAY_KEYWORD VARCHAR(255),
	USE_YN CHAR(1),
	ADD_KEYWORD VARCHAR(255)
);
</code>

Youtube
<code sql>
CREATE CACHED TABLE PUBLIC.TB_YOUTUBE
(
    VIDEOID VARCHAR(64) NOT NULL,
    VIDEOTITLE VARCHAR(512),
    VIDEODESCRIPTION TEXT,
    THUMBNAIL VARCHAR(512),
    NOTE VARCHAR(1024),
    LINK VARCHAR(512),
    CHANNELID VARCHAR(64),
    PUBLISHEDAT VARCHAR(64)
);
</code>

Facebook
<code sql>
CREATE TABLE PUBLIC.TB_FACEBOOK (
	ID VARCHAR(64) NOT NULL,
	FROMNAME VARCHAR(64) DEFAULT NULL,
	FROMCATEGORY VARCHAR(64) DEFAULT NULL,
	FROMID VARCHAR(64) DEFAULT NULL,
	MESSAGE CLOB,
	PICTURE VARCHAR(2048),
	LINK VARCHAR(512) DEFAULT NULL,
	NAME VARCHAR(255) DEFAULT NULL,
	"TYPE" VARCHAR(64) DEFAULT NULL,
	OBJECTID VARCHAR(64) DEFAULT NULL,
	APPLICATIONNAME VARCHAR(64) DEFAULT NULL,
	APPLICATIONID VARCHAR(64) DEFAULT NULL,
	CREATEDTIME VARCHAR(64) DEFAULT NULL,
	UPDATEDTIME VARCHAR(64) DEFAULT NULL
);
</code>

Twitter
<code sql>
CREATE TABLE PUBLIC.TB_TWITTER (
	ID VARCHAR(64) NOT NULL,
	TEXT VARCHAR(255) DEFAULT NULL,
	CREATED_AT VARCHAR(64) DEFAULT NULL,
	"SOURCE" VARCHAR(256) DEFAULT NULL,
	USERID VARCHAR(64) DEFAULT NULL,
	USERSCREENNAME VARCHAR(64) DEFAULT NULL,
	USERNAME VARCHAR(64) DEFAULT NULL,
	USERURL VARCHAR(255) DEFAULT NULL,
	USERLOCATION VARCHAR(64) DEFAULT NULL,
	USERPROFILEIMAGEURL VARCHAR(255) DEFAULT NULL,
	USERSTATUSESCOUNT VARCHAR(11) DEFAULT NULL
);
</code>

Instagram
<code sql>
CREATE TABLE PUBLIC.TB_INSTAGRAM (
	ID VARCHAR(64) NOT NULL,
	PERMALINK VARCHAR(1024) DEFAULT NULL,
	CAPTION TEXT,
	CREATED_TIME VARCHAR(64) DEFAULT NULL,
	MEDIA_TYPE VARCHAR(64) DEFAULT NULL,
	MEDIA_URL VARCHAR(1024) DEFAULT NULL,
	THUMBNAIL_URL VARCHAR(1024) DEFAULT NULL,
	USERNAME VARCHAR(64) DEFAULT NULL
);
</code>

===== Case Study =====

컬럼 크기 변경(TOTAL_SIZE, USED_SIZE, NAME)
<code sql>
ALTER TABLE T_LOG_SYSTEM ALTER COLUMN TOTAL_SIZE VARCHAR(32) NOT NULL;
ALTER TABLE T_LOG_SYSTEM ALTER COLUMN USED_SIZE VARCHAR(32) NOT NULL;
ALTER TABLE T_LOG_SYSTEM ALTER COLUMN NAME VARCHAR(64);
</code>

컬럼 추가(INFLOW)
<code sql>
ALTER TABLE T_ADDON_TOPKEYWORD ADD COLUMN INFLOW VARCHAR(64) DEFAULT '';
UPDATE T_ADDON_TOPKEYWORD SET INFLOW='inflow' WHERE INFLOW IS NULL;

ALTER TABLE T_ADDON_TOPKEYWORD_EXCEPT ADD COLUMN INFLOW VARCHAR(64) DEFAULT '';
UPDATE T_ADDON_TOPKEYWORD_EXCEPT SET INFLOW='inflow' WHERE INFLOW IS NULL;
</code>


인덱스(INDEX) 추가, 조회
<code sql>
CREATE INDEX {인덱스명} ON {테이블명} ({컬럼명})
-- 예시(온나라) --
CREATE INDEX TB_BMS_DCT_ENF_2021_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (DOCID,ENFDOCID);
CREATE INDEX TB_BMS_DCT_ENF_2021_DOCID_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (DOCID);
CREATE INDEX TB_BMS_DCT_ENF_2021_ENFDOCID_IDX ON PUBLIC.TB_BMS_DCT_ENF_2021 (ENFDOCID)

-- 인덱스 조회
SELECT * FROM information_schema.CONSTRAINTS c WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME='{테이블명}'
or
SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME ='{테이블명}';
</code>
===== Backup =====


===== Tip =====
<do 2020-02-10 김토피아>H2 실행 및 설정방법</do>


===== Troubleshooting =====


===== Ref =====

{{tag>주레피 DBMS h2 h2-tip}}