DB작업

CREATE SCHEMA IF NOT EXISTS ATTENDANCE AUTHORIZATION LMS;
 
CREATE OR REPLACE FUNCTION PUBLIC.CUSTOM_RANDOM(START_NUMBER NUMERIC, END_NUMBER NUMERIC) RETURNS NUMERIC  
    STRICT    LANGUAGE PLPGSQL  
AS  
$$  
BEGIN  
    RETURN TRUNC(RANDOM() * ((END_NUMBER + 1) - START_NUMBER) + START_NUMBER);  
END;  
$$;  
  
ALTER FUNCTION PUBLIC.CUSTOM_RANDOM(NUMERIC, NUMERIC) OWNER TO LMS;
 
DROP TABLE IF EXISTS ATTENDANCE.DOMAIN_LIST CASCADE;  
  
CREATE TABLE ATTENDANCE.DOMAIN_LIST  
(  
    DOMAIN_SEQ_ID    NUMERIC NOT NULL,  
    DOMAIN_NM    CHARACTER VARYING(100),  
    DOMAIN_ADDRS    CHARACTER VARYING(200),  
    DOMAIN_DESC    CHARACTER VARYING(4000),  
    USE_YN    CHARACTER VARYING(1) DEFAULT 'Y',  
    CRT_DTTM    TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'ASIA/SEOUL')  
);  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.DOMAIN_SEQ_ID IS '도메인순번아이디';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.DOMAIN_NM IS '도메인명';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.DOMAIN_ADDRS IS '도메인주소';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.DOMAIN_DESC IS '도메인설명';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.USE_YN IS '사용여부';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_LIST.CRT_DTTM IS '생성일시';  
  
COMMENT ON TABLE ATTENDANCE.DOMAIN_LIST IS '도메인목록';  
  
CREATE UNIQUE INDEX DOMAIN_LIST_PK ON ATTENDANCE.DOMAIN_LIST  
    ( DOMAIN_SEQ_ID );  
  
ALTER TABLE ATTENDANCE.DOMAIN_LIST  
    ADD CONSTRAINT DOMAIN_LIST_PK PRIMARY KEY  
        USING INDEX DOMAIN_LIST_PK;  
  
CREATE SEQUENCE ATTENDANCE.DOMAIN_LIST_ATTENDANCE_DOMAIN_SEQ_ID_SEQ;  
  
ALTER SEQUENCE ATTENDANCE.DOMAIN_LIST_ATTENDANCE_DOMAIN_SEQ_ID_SEQ OWNER TO LMS;  
  
ALTER SEQUENCE ATTENDANCE.DOMAIN_LIST_ATTENDANCE_DOMAIN_SEQ_ID_SEQ OWNED BY ATTENDANCE.DOMAIN_LIST.DOMAIN_SEQ_ID;
DROP TABLE IF EXISTS ATTENDANCE.DOMAIN_ACCNT_LIST CASCADE;  
 
CREATE TABLE ATTENDANCE.DOMAIN_ACCNT_LIST  
(  
    DOMAIN_SEQ_ID    NUMERIC NOT NULL,  
    DOMAIN_ACCNT_ID    CHARACTER VARYING(50) NOT NULL,  
    DOMAIN_ACCNT_PSWRD    CHARACTER VARYING(4000),  
    LAST_ATNDNC_DTTM    TIMESTAMP,
    MESRMNT_STRT_TM    NUMERIC(2) DEFAULT 8,  
    MESRMNT_END_TM    NUMERIC(2) DEFAULT 20,  
    MESRMNT_TM_INCLSN_PRBLTY    NUMERIC(3) DEFAULT 95,  
    ATNDNC_STRT_DTTM    TIMESTAMP WITHOUT TIME ZONE,  
    ATNDNC_STTS_CD    CHARACTER VARYING(1),  
    DOMAIN_ACCNT_DESC    CHARACTER VARYING(4000),  
    USE_YN    CHARACTER VARYING(1) DEFAULT 'Y',  
    CRT_DTTM    TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'ASIA/SEOUL')  
);  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.DOMAIN_SEQ_ID IS '도메인순번아이디';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.DOMAIN_ACCNT_ID IS '도메인계정아이디';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.DOMAIN_ACCNT_PSWRD IS '도메인계정비밀번호';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.LAST_ATNDNC_DTTM IS '마지막출석일자';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.MESRMNT_STRT_TM IS '측정시작시간';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.MESRMNT_END_TM IS '측정종료시간';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.MESRMNT_TM_INCLSN_PRBLTY IS '측정시간포함확률';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.ATNDNC_STRT_DTTM IS '출석시작일시';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.ATNDNC_STTS_CD IS '출석상태코드';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.DOMAIN_ACCNT_DESC IS '도메인계정설명';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.USE_YN IS '사용여부';  
  
COMMENT ON COLUMN ATTENDANCE.DOMAIN_ACCNT_LIST.CRT_DTTM IS '생성일시';  
  
COMMENT ON TABLE ATTENDANCE.DOMAIN_ACCNT_LIST IS '도메인계정목록';  
  
CREATE UNIQUE INDEX DOMAIN_ACCNT_LIST_PK ON ATTENDANCE.DOMAIN_ACCNT_LIST  
    ( DOMAIN_SEQ_ID,DOMAIN_ACCNT_ID );  
  
ALTER TABLE ATTENDANCE.DOMAIN_ACCNT_LIST  
    ADD CONSTRAINT DOMAIN_ACCNT_LIST_PK PRIMARY KEY  
        USING INDEX DOMAIN_ACCNT_LIST_PK;
DROP TABLE IF EXISTS ATTENDANCE.ACT_LIST CASCADE;  
  
CREATE TABLE ATTENDANCE.ACT_LIST  
(  
    DOMAIN_SEQ_ID    NUMERIC NOT NULL,  
    ACT_ORD    NUMERIC(2) NOT NULL,  
    ACT_TYP_CD    CHARACTER VARYING(10),  
    ACT_DTL_JSON    JSON,  
    RETRY_YN    CHARACTER VARYING(1),  
    CRT_DTTM    TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'ASIA/SEOUL')  
);  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.DOMAIN_SEQ_ID IS '도메인순번아이디';  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.ACT_ORD IS '행동순서';  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.ACT_TYP_CD IS '행동유형코드';  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.ACT_DTL_JSON IS '행동상세JSON';  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.RETRY_YN IS '재시도여부';  
  
COMMENT ON COLUMN ATTENDANCE.ACT_LIST.CRT_DTTM IS '생성일시';  
  
COMMENT ON TABLE ATTENDANCE.ACT_LIST IS '행동목록';  
  
CREATE UNIQUE INDEX ACT_LIST_PK ON ATTENDANCE.ACT_LIST  
    ( DOMAIN_SEQ_ID,ACT_ORD );  
  
ALTER TABLE ATTENDANCE.ACT_LIST  
    ADD CONSTRAINT ACT_LIST_PK PRIMARY KEY  
        USING INDEX ACT_LIST_PK;
DROP TABLE IF EXISTS ATTENDANCE.ATNDNC_LOG CASCADE;  
  
CREATE TABLE ATTENDANCE.ATNDNC_LOG  
(  
    DOMAIN_SEQ_ID    NUMERIC NOT NULL,  
    DOMAIN_ACCNT_ID    CHARACTER VARYING(50) NOT NULL,  
    ATNDNC_DT    CHARACTER VARYING(8) NOT NULL,  
    LOG_SEQ_ID    NUMERIC NOT NULL,  
    LOG_MSG    CHARACTER VARYING(4000),  
    LOG_DTTM    TIMESTAMP WITHOUT TIME ZONE DEFAULT (NOW() AT TIME ZONE 'ASIA/SEOUL')  
);  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.DOMAIN_SEQ_ID IS '도메인순번아이디';  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.DOMAIN_ACCNT_ID IS '도메인계정아이디';  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.ATNDNC_DT IS '출석일자';  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.LOG_SEQ_ID IS '로그순번아이디';  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.LOG_MSG IS '로그메시지';  
  
COMMENT ON COLUMN ATTENDANCE.ATNDNC_LOG.LOG_DTTM IS '로그일시';  
  
COMMENT ON TABLE ATTENDANCE.ATNDNC_LOG IS '출석로그';  
  
CREATE UNIQUE INDEX ATNDNC_LOG_PK ON ATTENDANCE.ATNDNC_LOG  
    ( DOMAIN_SEQ_ID,DOMAIN_ACCNT_ID,ATNDNC_DT,LOG_SEQ_ID );  
  
ALTER TABLE ATTENDANCE.ATNDNC_LOG  
    ADD CONSTRAINT ATNDNC_LOG_PK PRIMARY KEY  
        USING INDEX ATNDNC_LOG_PK;