CREATE DATABASE portal character set utf8mb4 collate utf8mb4_unicode_ci; use portal; CREATE TABLE PT_ID ( TABLE_NAME VARCHAR(36) NOT NULL, NEXT_ID NUMERIC(30) NULL, CONSTRAINT PT_ID_PK PRIMARY KEY (TABLE_NAME) ); CREATE TABLE PT_CODE ( CODE_ID VARCHAR(6) NOT NULL, CODE_ID_NAME VARCHAR(60) NULL, CODE_ID_DC VARCHAR(200) NULL, USE_AT VARCHAR(1) NULL, REGISTERED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, CONSTRAINT PT_CODE_PK PRIMARY KEY (CODE_ID) ); CREATE TABLE PT_DETAIL_CODE ( CODE_ID VARCHAR(6) NOT NULL, CODE VARCHAR(15) NOT NULL, CODE_VALUE VARCHAR(4000) NULL, CODE_DC VARCHAR(200) NULL, USE_AT VARCHAR(1) NULL, REGISTERED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, CONSTRAINT PT_DETAIL_CODE_PK PRIMARY KEY (CODE_ID, CODE), CONSTRAINT PT_DETAIL_CODE_FK1 FOREIGN KEY (CODE_ID) REFERENCES PT_CODE (CODE_ID) ); CREATE TABLE PT_AUTHORITY ( AUTHOR_CODE VARCHAR(30) NOT NULL, AUTHOR_NAME VARCHAR(60) NOT NULL, AUTHOR_DC VARCHAR(200) NULL, REGISTERED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, CONSTRAINT PT_AUTHORITY_PK PRIMARY KEY (AUTHOR_CODE) ); CREATE TABLE PT_USER_SECURITY ( USER_ESNTL_ID VARCHAR(36) NOT NULL, MBER_TY_CODE VARCHAR(5) NULL, AUTHOR_CODE VARCHAR(30) NOT NULL, CONSTRAINT PT_USER_SECURITY_PK PRIMARY KEY (USER_ESNTL_ID) ); CREATE TABLE PT_BOARD_MASTER ( BOARD_ID VARCHAR(36) NOT NULL, BOARD_NAME VARCHAR(255) NOT NULL, BOARD_DESC VARCHAR(2400) NULL, BOARD_TYPE VARCHAR(6) NOT NULL, REPLY_POSBL_AT VARCHAR(1) NULL, FILE_ATCH_POSBL_AT VARCHAR(1) NOT NULL, ATCH_POSBL_FILE_NUMBER NUMERIC(2) NOT NULL, ATCH_POSBL_FILE_SIZE NUMERIC(8) NULL, USE_AT VARCHAR(1) NOT NULL, TMPLAT_ID VARCHAR(20) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, CONSTRAINT PT_BOARD_MASTER_PK PRIMARY KEY (BOARD_ID) ); CREATE TABLE PT_FILE ( FILE_ID VARCHAR(36) NOT NULL, USE_AT VARCHAR(1) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, CONSTRAINT PT_FILE_PK PRIMARY KEY (FILE_ID) ); CREATE TABLE PT_FILE_DETAIL ( FILE_ID VARCHAR(36) NOT NULL, FILE_SN NUMERIC(10) NOT NULL, ORIGNL_FILE_NAME VARCHAR(255) NULL, FILE_EXTSN VARCHAR(36) NULL, FILE_CN LONGTEXT NULL, FILE_SIZE NUMERIC(8) NULL, CONSTRAINT PT_FILE_PK PRIMARY KEY (FILE_ID, FILE_SN), CONSTRAINT PT_FILE_FK FOREIGN KEY (FILE_ID) REFERENCES PT_FILE (FILE_ID) ); CREATE TABLE PT_BOARD_ARTICLE ( BOARD_ID VARCHAR(36) NOT NULL, NTT_NO NUMERIC(20) NULL, NTT_ID NUMERIC(20) NOT NULL, NTT_SJ VARCHAR(2000) NULL, NTT_CN LONGTEXT NULL, ANSWER_AT VARCHAR(1) NULL, PARNTSCTT_NO NUMERIC(10) NULL, ANSWER_LC NUMERIC(8) NULL, SORT_ORDR NUMERIC(8) NULL, RDCNT NUMERIC(10) NULL, USE_AT VARCHAR(1) NOT NULL, NTCE_BGNDE VARCHAR(20) NULL, NTCE_ENDDE VARCHAR(20) NULL, NTCR_ID VARCHAR(36) NULL, NTCR_NAME VARCHAR(36) NULL, PASSWORD VARCHAR(200) NULL, FILE_ID VARCHAR(20) NULL, NOTICE_AT VARCHAR(1) NULL, SJ_BOLD_AT VARCHAR(1) NULL, SECRET_AT VARCHAR(1) NULL, REGISTERED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, CONSTRAINT PT_BOARD_ARTICLE_PK PRIMARY KEY (NTT_ID, BOARD_ID), CONSTRAINT PT_BOARD_ARTICLE_FK1 FOREIGN KEY (BOARD_ID) REFERENCES PT_BOARD_MASTER (BOARD_ID), CONSTRAINT PT_BOARD_ARTICLE_FK2 FOREIGN KEY (FILE_ID) REFERENCES PT_FILE (FILE_ID) ); CREATE TABLE PT_FAQ ( FAQ_ID VARCHAR(36) NOT NULL, TITLE VARCHAR(255) NULL, QUESTION VARCHAR(2500) NULL, ANSWER VARCHAR(2500) NULL, RDCNT NUMERIC(10) NULL, FILE_ID VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, CONSTRAINT PT_FAQ_PK PRIMARY KEY (FAQ_ID), CONSTRAINT PT_FAQ_FK FOREIGN KEY (FILE_ID) REFERENCES PT_FILE (FILE_ID) ON DELETE SET NULL ); CREATE TABLE REVINFO ( REV INT AUTO_INCREMENT PRIMARY KEY, REVTSTMP BIGINT NULL ); CREATE TABLE PT_USER ( ESNTL_ID VARCHAR(36) NOT NULL, USER_ID VARCHAR(256) NOT NULL, USER_NAME VARCHAR(60) NOT NULL, STATUS VARCHAR(15) NOT NULL, MOBILE_PHONE VARCHAR(15) NULL, PASSWORD VARCHAR(200) NOT NULL, PASSWORD_HINT VARCHAR(100) NULL, PASSWORD_CNSR VARCHAR(100) NULL, LOCK_AT VARCHAR(1) NULL, LOCK_CNT NUMERIC(3) NULL, LAST_LOCK_DATE DATETIME NULL, LAST_CHANGE_PASSWORD_DATE DATETIME NULL, REGISTERED_ON DATETIME null, REGISTERED_BY VARCHAR(36) null, LAST_MODIFIED_ON datetime null, LAST_MODIFIED_BY VARCHAR(36) null, PRIMARY KEY (ESNTL_ID) ); CREATE UNIQUE INDEX PT_USER_PK ON PT_USER ( USER_ID ) ; CREATE TABLE PT_USER_AUD ( ESNTL_ID VARCHAR(36) NOT NULL, REV integer not null, USER_ID VARCHAR(256) NOT NULL, USER_NAME VARCHAR(60) NOT NULL, STATUS VARCHAR(15) NOT NULL, MOBILE_PHONE VARCHAR(15) NULL, PASSWORD VARCHAR(200) NOT NULL, PASSWORD_HINT VARCHAR(100) NULL, PASSWORD_CNSR VARCHAR(100) NULL, LOCK_AT VARCHAR(1) NULL, LOCK_CNT NUMERIC(3) NULL, LAST_LOCK_DATE DATETIME NULL, LAST_CHANGE_PASSWORD_DATE DATETIME NULL, REGISTERED_ON DATETIME null, REGISTERED_BY VARCHAR(36) null, LAST_MODIFIED_ON datetime null, LAST_MODIFIED_BY VARCHAR(36) null, REVTYPE tinyint, PRIMARY KEY (ESNTL_ID, REV) ); CREATE TABLE PT_ENT_USER ( ESNTL_ID VARCHAR(36) NOT NULL, USER_ID VARCHAR(256) NOT NULL, USER_NAME VARCHAR(60) NOT NULL, STATUS VARCHAR(15) NOT NULL, IP_ADDRESS VARCHAR(15) NULL, MOBILE_PHONE VARCHAR(15) NULL, OFFICE_PHONE VARCHAR(15) NULL, COMPANY_NAME VARCHAR(15) NULL, CEO_NAME VARCHAR(15) NULL, BIZRNO VARCHAR(15) NULL, JURIRNO VARCHAR(15) NULL, ZIP VARCHAR(6) NULL, ADRES VARCHAR(100) NULL, DETAIL_ADRES VARCHAR(100) NULL, FXNUM VARCHAR(36) NULL, INDUTY_CODE VARCHAR(15) NULL, PASSWORD VARCHAR(200) NOT NULL, PASSWORD_HINT VARCHAR(100) NULL, PASSWORD_CNSR VARCHAR(100) NULL, GROUP_ID VARCHAR(36) NULL, ENTRPRS_SE_CODE VARCHAR(15) NULL, LOCK_AT VARCHAR(1) NULL, LOCK_CNT NUMERIC(3) NULL, LAST_LOCK_DATE DATETIME NULL, LAST_CHANGE_PASSWORD_DATE DATETIME NULL, REGISTERED_ON DATETIME null, REGISTERED_BY VARCHAR(36) null, LAST_MODIFIED_ON datetime null, LAST_MODIFIED_BY VARCHAR(36) null, PRIMARY KEY (ESNTL_ID) ); CREATE UNIQUE INDEX PT_ENT_USER_PK ON PT_ENT_USER ( USER_ID ) ; CREATE TABLE PT_ENT_USER_AUD ( ESNTL_ID VARCHAR(36) NOT NULL, REV integer not null, USER_ID VARCHAR(256) NOT NULL, USER_NAME VARCHAR(60) NOT NULL, STATUS VARCHAR(15) NOT NULL, IP_ADDRESS VARCHAR(15) NULL, MOBILE_PHONE VARCHAR(15) NULL, OFFICE_PHONE VARCHAR(15) NULL, COMPANY_NAME VARCHAR(15) NULL, CEO_NAME VARCHAR(15) NULL, BIZRNO VARCHAR(15) NULL, JURIRNO VARCHAR(15) NULL, ZIP VARCHAR(6) NULL, ADRES VARCHAR(100) NULL, DETAIL_ADRES VARCHAR(100) NULL, FXNUM VARCHAR(36) NULL, INDUTY_CODE VARCHAR(15) NULL, PASSWORD VARCHAR(200) NOT NULL, PASSWORD_HINT VARCHAR(100) NULL, PASSWORD_CNSR VARCHAR(100) NULL, GROUP_ID VARCHAR(36) NULL, ENTRPRS_SE_CODE VARCHAR(15) NULL, LOCK_AT VARCHAR(1) NULL, LOCK_CNT NUMERIC(3) NULL, LAST_LOCK_DATE DATETIME NULL, LAST_CHANGE_PASSWORD_DATE DATETIME NULL, REGISTERED_ON DATETIME null, REGISTERED_BY VARCHAR(36) null, LAST_MODIFIED_ON datetime null, LAST_MODIFIED_BY VARCHAR(36) null, REVTYPE tinyint, PRIMARY KEY (ESNTL_ID, REV) ); CREATE TABLE PT_APPROVAL ( ID BIGINT PRIMARY KEY, APPROVAL_TYPE VARCHAR(32) NULL, BODY LONGTEXT NULL, ENDED_ON DATETIME NULL, EVENT VARCHAR(255) NULL, REQUESTER_ID VARCHAR(36) NULL, STATUS VARCHAR(32) NULL, TARGET_ID VARCHAR(36) NULL, TITLE VARCHAR(255) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL ); CREATE TABLE PT_APPROVAL_APPROVER ( APPROVAL_ID BIGINT NOT NULL, ORDINAL BIGINT NOT NULL, APPROVED_ON DATETIME NULL, MESSAGE VARCHAR(255) NULL, STATUS VARCHAR(255) NULL, USER_ID VARCHAR(36) NULL, PRIMARY KEY (APPROVAL_ID, ORDINAL), CONSTRAINT FK_APPROVAL_APPROVER1 FOREIGN KEY (APPROVAL_ID) REFERENCES PT_APPROVAL (ID) ); CREATE TABLE PT_ROUTE_INFO ( ID VARCHAR(36) NOT NULL PRIMARY KEY, API_PATH VARCHAR(512) NULL, DESCRIPTION LONGTEXT NULL, LOG_CONTENT_ENCODE_YN VARCHAR(1) NULL, LOG_LEVEL VARCHAR(1) NULL, NAME VARCHAR(256) NOT NULL, ORDER_NO INT NULL, PATH VARCHAR(256) NOT NULL, METHOD VARCHAR(32) NULL, META_DATA_STR LONGTEXT NULL, RESPONSE_TYPE VARCHAR(32) NULL, MOCK_URL VARCHAR(255) NULL, SAMPLE_RESPONSE LONGTEXT NULL, STATUS VARCHAR(32) NULL, TYPE VARCHAR(2) NULL, URI VARCHAR(512) NOT NULL, USE_YN VARCHAR(1) NULL, FILE_ID VARCHAR(36) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL ); CREATE TABLE PT_API_KEY ( ID VARCHAR(36) NOT NULL PRIMARY KEY, OWNER VARCHAR(255) NULL, NAME VARCHAR(255) NULL, ENV VARCHAR(32) NULL, CLIENT_ID VARCHAR(255) NULL, CLIENT_SECRET VARCHAR(255) NULL, REDIRECT_URL VARCHAR(255) NULL, RESPONSE_TYPE VARCHAR(255) NULL, SCOPE VARCHAR(2000) NULL, STATUS VARCHAR(255) NULL, DESCRIPTION VARCHAR(2000) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL ); CREATE TABLE PT_API_KEY_API ( API_KEY_ID VARCHAR(36) NOT NULL, API_ID VARCHAR(36) NOT NULL, CONSTRAINT FK_API_KEY_API1 FOREIGN KEY (API_KEY_ID) REFERENCES PT_API_KEY (ID), CONSTRAINT FK_API_KEY_API2 FOREIGN KEY (API_ID) REFERENCES PT_ROUTE_INFO (ID) ); CREATE TABLE PT_API_KEY_AUD ( ID VARCHAR(36) NOT NULL, REV integer not null, REVTYPE tinyint, OWNER VARCHAR(255) NULL, NAME VARCHAR(255) NULL, ENV VARCHAR(32) NULL, CLIENT_ID VARCHAR(255) NULL, CLIENT_SECRET VARCHAR(255) NULL, REDIRECT_URL VARCHAR(255) NULL, RESPONSE_TYPE VARCHAR(255) NULL, SCOPE VARCHAR(2000) NULL, STATUS VARCHAR(255) NULL, DESCRIPTION VARCHAR(2000) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, PRIMARY KEY (ID, REV) ); CREATE TABLE PT_API_KEY_API_AUD ( API_KEY_ID VARCHAR(36) NOT NULL, API_ID VARCHAR(36) NOT NULL, REV integer not null, REVTYPE tinyint, PRIMARY KEY (API_KEY_ID, API_ID, REV) ); CREATE TABLE PT_GROUP_INFO ( ID VARCHAR(36) NOT NULL PRIMARY KEY, DESCRIPTION VARCHAR(512) NULL, DESCRIPTION_EN VARCHAR(512) NULL, NAME VARCHAR(256) NOT NULL, NAME_EN VARCHAR(256) NULL, SPEC LONGTEXT NULL, STATUS VARCHAR(32) NULL, USE_YN VARCHAR(1) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL ); CREATE TABLE PT_GROUP_ROUTE_INFO ( GROUP_ID VARCHAR(36) NOT NULL, ROUTE_ID VARCHAR(128) NOT NULL, CONSTRAINT FK_GROUP_ROUTE_INFO1 FOREIGN KEY (GROUP_ID) REFERENCES PT_GROUP_INFO (ID), CONSTRAINT FK_GROUP_ROUTE_INFO2 FOREIGN KEY (ROUTE_ID) REFERENCES PT_ROUTE_INFO (ID) ); CREATE TABLE PT_AGREEMENTS ( AGREEMENTS_TYPE VARCHAR(36) NOT NULL, REVISION INT NOT NULL, AGREEMENTS_NAME VARCHAR(100) NOT NULL, AGREEMENTS_CONTENTS LONGTEXT NULL, STATUS VARCHAR(36) NULL, PUBLISHED_ON DATETIME NULL, SCHEDULED_ON DATETIME NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, primary key (AGREEMENTS_TYPE, REVISION) ); CREATE TABLE PT_MESSAGE_TEMPLATE ( MESSAGE_KEY VARCHAR(32) NOT NULL, MESSAGE_TYPE VARCHAR(16) NOT NULL, SUBJECT VARCHAR(100) NOT NULL, CONTENTS LONGTEXT NULL, USE_YN VARCHAR(1) NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, primary key (MESSAGE_KEY, MESSAGE_TYPE) ); CREATE TABLE PT_MESSAGE_RECIPIENT ( MESSAGE_KEY VARCHAR(32) not null, MESSAGE_TYPE VARCHAR(16) not null, ESNTL_ID VARCHAR(36) not null, constraint PK_MESSAGE_RECIPIENT PRIMARY KEY (MESSAGE_KEY, MESSAGE_TYPE, ESNTL_ID) ) ; CREATE TABLE PT_TOKEN ( TOKEN VARCHAR(255) NOT NULL, EMAIL VARCHAR(255) NOT NULL, EXPIRES_AT DATETIME NOT NULL, primary key (TOKEN) ); CREATE TABLE PT_TWO_FACTOR_AUTH ( RECIPIENT VARCHAR(255) NOT NULL, AUTH_NUMBER VARCHAR(255), EXPIRES_AT DATETIME, PRIMARY KEY (RECIPIENT) ); CREATE TABLE PT_BOARD_AUTHORITY ( AUTHORITY_ID VARCHAR(36) NOT NULL, BBS_ID VARCHAR(36) NOT NULL, AUTHORITY_CODE VARCHAR(36) NULL, ESNTL_ID VARCHAR(36) NULL, READ_YN VARCHAR(1) NOT NULL, WRITE_YN VARCHAR(1) NOT NULL, MODIFY_YN VARCHAR(1) NOT NULL, DELETE_YN VARCHAR(1) NOT NULL, ADMIN_YN VARCHAR(1) NOT NULL, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL, primary key (AUTHORITY_ID) ); CREATE TABLE PT_CHART ( id VARCHAR(36) PRIMARY KEY, name VARCHAR(255), query LONGTEXT, options LONGTEXT, REGISTERED_BY VARCHAR(36) NULL, REGISTERED_ON DATETIME NULL, LAST_MODIFIED_BY VARCHAR(36) NULL, LAST_MODIFIED_ON DATETIME NULL ); CREATE VIEW PT_USER_VIEW (ESNTL_ID, USER_ID, PASSWORD, USER_NAME, USER_SE) AS SELECT ESNTL_ID, USER_ID, PASSWORD, USER_NAME, 'USR' AS USER_SE FROM PT_USER UNION ALL SELECT ESNTL_ID, USER_ID, PASSWORD, USER_NAME, 'ENT' AS USER_SE FROM PT_ENT_USER ORDER BY ESNTL_ID;