H2

테이블 목록 확인하기

sql> SHOW TABLES;

컬럼 목록 확인하기

sql> SHOW COLUMNS FROM T_LOG_SYSTEM;

Related

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)
);

Youtube

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)
);

Facebook

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
);

Twitter

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
);

Instagram

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
);

컬럼 크기 변경(TOTAL_SIZE, USED_SIZE, NAME)

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);

컬럼 추가(INFLOW)

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;

인덱스(INDEX) 추가, 조회

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 ='{테이블명}';

H2 실행 및 설정방법 (할당된 사용자 김토피아. 마감일 2020-02-10)