postgre ddl

테스트용 ddl 

select *
  from TEST_TEAM;
 
/* 1. 팀정보 시퀀스 생성 */
DROP SEQUENCE SQ_N_TEST_TEAM_01;
CREATE SEQUENCE SQ_N_TEST_TEAM_01 START 1;

 /* 1.테스트 테이블 팀정보 */
CREATE TABLE TEST_TEAM (
	TEAM_SN 	NUMERIC(12) NOT NULL,
	TEAM_NM 	VARCHAR(60) NOT NULL,
	TEAM_DC 	VARCHAR(200),
	SORT    	NUMERIC(4),
	DEL_YN      CHAR(1)         NOT NULL,
    REGISTER_ID VARCHAR(20)     NOT NULL,
    REG_DT      TIMESTAMP       NOT NULL,
    UPDUSR_ID   VARCHAR(20)     NOT NULL,
    MDFCN_DT    TIMESTAMP       NOT NULL,
	CONSTRAINT  TEST_TEAM_PK PRIMARY KEY (TEAM_SN)
);

COMMENT ON TABLE TEST_TEAM IS '팀정보';
COMMENT ON COLUMN TEST_TEAM.TEAM_SN IS '팀일련번호';
COMMENT ON COLUMN TEST_TEAM.TEAM_NM IS '팀명';
COMMENT ON COLUMN TEST_TEAM.TEAM_DC IS '팀설명';
COMMENT ON COLUMN TEST_TEAM.SORT IS '팀순서'; 
COMMENT ON COLUMN TEST_TEAM.DEL_YN IS '삭제여부';
COMMENT ON COLUMN TEST_TEAM.REGISTER_ID IS '등록자ID';
COMMENT ON COLUMN TEST_TEAM.REG_DT IS '등록일시';
COMMENT ON COLUMN TEST_TEAM.UPDUSR_ID IS '수정자ID';
COMMENT ON COLUMN TEST_TEAM.MDFCN_DT IS '수정일시';

/* 팀테스트 데이터 등록 */
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_TEAM_01'),'청팀','청색옷을 입은 청색팀',1,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_TEAM_01'),'홍팀','홍색옷을 입은 홍색팀',2,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_TEAM_01'),'백팀','백색옷을 입은 백색팀',3,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_TEAM_01'),'흑팀','흑색옷을 입은 흑색팀',4,'N','ADMIN',NOW(),'ADMIN',NOW());
INSERT INTO TEST_TEAM (TEAM_SN,TEAM_NM,TEAM_DC,SORT,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_TEAM_01'),'레드팀','빨간색옷을 입은 팀',5,'N','ADMIN',NOW(),'ADMIN',NOW());


/* 2. 멤버정보 시퀀스 생성 */
DROP SEQUENCE SQ_N_TEST_MEMBER_01;
CREATE SEQUENCE SQ_N_TEST_MEMBER_01 START 1;

 /* 2.테스트 테이블 멤버정보 */
CREATE TABLE TEST_MEMBER (
	MEMBER_SN 	NUMERIC(12) NOT NULL,
	MEMBER_NM 	VARCHAR(60) NOT NULL,
	MEMBER_DC 	VARCHAR(200),
	SORT    	NUMERIC(4),
	TEAM_SN     NUMERIC(12),
	DEL_YN      CHAR(1)         NOT NULL,
    REGISTER_ID VARCHAR(20)     NOT NULL,
    REG_DT      TIMESTAMP       NOT NULL,
    UPDUSR_ID   VARCHAR(20)     NOT NULL,
    MDFCN_DT    TIMESTAMP       NOT NULL,
	CONSTRAINT  TEST_MEMBER_PK PRIMARY KEY (MEMBER_SN),
	CONSTRAINT  TEST_MEMBER_FK1 FOREIGN KEY (TEAM_SN) REFERENCES TEST_TEAM(TEAM_SN) ON DELETE CASCADE
);

COMMENT ON TABLE TEST_MEMBER IS '멤버정보';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_SN IS '멤버일련번호';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_NM IS '멤버명';
COMMENT ON COLUMN TEST_MEMBER.MEMBER_DC IS '멤버설명';
COMMENT ON COLUMN TEST_MEMBER.SORT IS '멤버순서'; 
COMMENT ON COLUMN TEST_MEMBER.TEAM_SN IS '팀일련번호';
COMMENT ON COLUMN TEST_MEMBER.DEL_YN IS '삭제여부';
COMMENT ON COLUMN TEST_MEMBER.REGISTER_ID IS '등록자ID';
COMMENT ON COLUMN TEST_MEMBER.REG_DT IS '등록일시';
COMMENT ON COLUMN TEST_MEMBER.UPDUSR_ID IS '수정자ID';
COMMENT ON COLUMN TEST_MEMBER.MDFCN_DT IS '수정일시';

select *
  from TEST_MEMBER;
  
/* 멤버 테스트 데이터 등록 */
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_MEMBER_01'),'이순신','거북선 선장',1,1,'N','ADMIN',NOW(),'ADMIN',NOW());
			   
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_MEMBER_01'),'홍길동','전설속에 등장인물',2,NULL,'N','ADMIN',NOW(),'ADMIN',NOW());
			   
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_MEMBER_01'),'김길동','보통사람',3,1,'N','ADMIN',NOW(),'ADMIN',NOW()); 
			  
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_MEMBER_01'),'최길순','그냥보통사람',4,2,'N','ADMIN',NOW(),'ADMIN',NOW()); 			  
			  
INSERT INTO TEST_MEMBER (MEMBER_SN,MEMBER_NM,MEMBER_DC,SORT,TEAM_SN,DEL_YN,REGISTER_ID,REG_DT,UPDUSR_ID,MDFCN_DT) 
			   VALUES (nextval('SQ_N_TEST_MEMBER_01'),'이영희','그냥평범한사람',5,3,'N','ADMIN',NOW(),'ADMIN',NOW()); 					  
			  

/* 1.권한정보 */
CREATE TABLE COMTNAUTHORINFO (
	AUTHOR_CODE VARCHAR(30) NOT NULL,
	AUTHOR_NM VARCHAR(60) NOT NULL,
	AUTHOR_DC VARCHAR(200),
	AUTHOR_CREAT_DE TIMESTAMP,
	USE_AT CHAR(1),
	CONSTRAINT  COMTNAUTHORINFO_PK PRIMARY KEY (AUTHOR_CODE)
);

COMMENT ON TABLE COMTNAUTHORINFO IS '권한정보';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_CREAT_DE IS '권한생성일';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_DC IS '권한설명';
COMMENT ON COLUMN COMTNAUTHORINFO.AUTHOR_NM IS '권한명';
COMMENT ON COLUMN COMTNAUTHORINFO.USE_AT IS '사용여부';


/* 2. 롤계층구조 */

DROP SEQUENCE SQ_N_COMTNROLES_HIERARCHY_01;
CREATE SEQUENCE SQ_N_COMTNROLES_HIERARCHY_01 START 1;

CREATE TABLE COMTNROLES_HIERARCHY (
    COMTNROLES_HIERARCHY_SN numeric(12),
	PARNTS_ROLE VARCHAR(30) NOT NULL,
	CHLDRN_ROLE VARCHAR(30) NOT NULL,,
	CONSTRAINT  COMTNROLES_HIERARCHY_PK PRIMARY KEY (COMTNROLES_HIERARCHY_SN),
    CONSTRAINT  COMTNROLES_HIERARCHY_FK1 FOREIGN KEY (PARNTS_ROLE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE,
    CONSTRAINT  COMTNROLES_HIERARCHY_FK2 FOREIGN KEY (CHLDRN_ROLE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE
	
);

COMMENT ON TABLE COMTNROLES_HIERARCHY IS '롤계층구조';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.COMTNROLES_HIERARCHY_SN IS '롤계층일련번호';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.CHLDRN_ROLE IS '자식롤';
COMMENT ON COLUMN COMTNROLES_HIERARCHY.PARNTS_ROLE IS '부모롤';

CREATE UNIQUE INDEX COMTNROLES_HIERARCHY_I01 ON COMTNROLES_HIERARCHY (
	PARNTS_ROLE ASC
);


CREATE INDEX COMTNROLES_HIERARCHY_I02 ON COMTNROLES_HIERARCHY (
	CHLDRN_ROLE ASC
);

/* 3 롤정보 */
CREATE TABLE COMTNROLEINFO (
	ROLE_CODE VARCHAR(50) NOT NULL,
	ROLE_NM VARCHAR(60) NOT NULL,
	ROLE_TY VARCHAR(80),
	ROLE_PTTRN VARCHAR(300),
	ROLE_DC VARCHAR(200),
	ROLE_SORT numeric,
	ROLE_CREAT_DE TIMESTAMP,
	USE_AT CHAR(1),
	CONSTRAINT  COMTNROLEINFO_PK PRIMARY KEY (ROLE_CODE)
);

COMMENT ON TABLE COMTNROLEINFO IS '역할정보';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_CODE IS '롤코드';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_CREAT_DE IS '롤생성일';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_DC IS '롤설명';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_NM IS '롤명';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_PTTRN IS '롤패턴';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_SORT IS '롤정렬';
COMMENT ON COLUMN COMTNROLEINFO.ROLE_TY IS '롤유형';
COMMENT ON COLUMN COMTNROLEINFO.USE_AT IS '사용여부';

/* 4. 권한역할 관계 */
DROP SEQUENCE SQ_N_COMTNAUTHORROLERELATE_01;
CREATE SEQUENCE SQ_N_COMTNAUTHORROLERELATE_01 START 1;

CREATE TABLE COMTNAUTHORROLERELATE (
    COMTNAUTHORROLERELATE_SN numeric(12),
	AUTHOR_CODE VARCHAR(30) NOT NULL,
	ROLE_CODE VARCHAR(50) NOT NULL,
	CREAT_DT TIMESTAMP,
	CONSTRAINT  COMTNAUTHORROLERELATE_PK PRIMARY KEY (COMTNAUTHORROLERELATE_SN),
    CONSTRAINT  COMTNAUTHORROLERELATE_FK1 FOREIGN KEY (AUTHOR_CODE) REFERENCES COMTNAUTHORINFO(AUTHOR_CODE) ON DELETE CASCADE,
    CONSTRAINT  COMTNAUTHORROLERELATE_FK2 FOREIGN KEY (ROLE_CODE) REFERENCES COMTNROLEINFO(ROLE_CODE) ON DELETE CASCADE

);

COMMENT ON TABLE COMTNAUTHORROLERELATE IS '권한역할관계';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.COMTNAUTHORROLERELATE_SN IS '권한역할일련번호';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.CREAT_DT IS '생성일시';
COMMENT ON COLUMN COMTNAUTHORROLERELATE.ROLE_CODE IS '롤코드';

CREATE INDEX COMTNAUTHORROLERELATE_I01 ON COMTNAUTHORROLERELATE (
	AUTHOR_CODE ASC
);


CREATE INDEX COMTNAUTHORROLERELATE_I02 ON COMTNAUTHORROLERELATE (
	ROLE_CODE ASC
);




/* 5 사용자별 권한정보 */
DROP SEQUENCE SQ_N_COMTNEMPLYRSCRTYESTBS_01;
CREATE SEQUENCE SQ_N_COMTNEMPLYRSCRTYESTBS_01 START 1;

CREATE TABLE COMTNEMPLYRSCRTYESTBS (
    COMTNEMPLYRSCRTYESTBS_SN numeric(12),
	USER_ID VARCHAR(20) NOT NULL,
	AUTHOR_CODE VARCHAR(30) NOT NULL,
	INSTT_ENTRPS_MNO VARCHAR(20) NOT NULL,
	USE_AT CHAR(1),
	RMK VARCHAR(100),
	REGISTER_ID VARCHAR(20),
	REGIST_DT TIMESTAMP,
	UPDR_ID VARCHAR(20),
	UPD_DT TIMESTAMP,
	CONSTRAINT  COMTNEMPLYRSCRTYESTBS_PK PRIMARY KEY (COMTNEMPLYRSCRTYESTBS_SN)
);

COMMENT ON TABLE COMTNEMPLYRSCRTYESTBS IS '사용자별권한정보';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.COMTNEMPLYRSCRTYESTBS_SN IS '사용자별권한일련번호';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.INSTT_ENTRPS_MNO IS '기관업체관리번호';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.RMK IS '비고';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.USER_ID IS '사용자아이디';
COMMENT ON COLUMN COMTNEMPLYRSCRTYESTBS.USE_AT IS '사용여부';


/* 6 즐겨찾기메뉴 */
DROP SEQUENCE SQ_N_COMTNFAVORMENUINFO_01;
CREATE SEQUENCE SQ_N_COMTNFAVORMENUINFO_01 START 1;

CREATE TABLE COMTNFAVORMENUINFO (
    COMTNFAVORMENUINFO_SN numeric(12),
	USER_ID VARCHAR(20) NOT NULL,
	MENU_ID VARCHAR(8) NOT NULL,
	MENU_NM VARCHAR(50),
	GROUP_ID VARCHAR(8),
	SORT_NO NUMERIC,
	LVL NUMERIC,
	SYSTEM_GBN VARCHAR(8),
	AUTH VARCHAR(10),
	MENU_URL VARCHAR(100),
	CONSTRAINT  COMTNFAVORMENUINFO_1_PK PRIMARY KEY (COMTNFAVORMENUINFO_SN)
);


COMMENT ON TABLE COMTNFAVORMENUINFO IS '즐겨찾기메뉴';
COMMENT ON COLUMN COMTNFAVORMENUINFO.COMTNFAVORMENUINFO_SN IS '즐겨찾기메뉴일련번호';
COMMENT ON COLUMN COMTNFAVORMENUINFO.AUTH IS '버튼권한';
COMMENT ON COLUMN COMTNFAVORMENUINFO.GROUP_ID IS '그룹아이디';
COMMENT ON COLUMN COMTNFAVORMENUINFO.LVL IS '레벨';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_NM IS '메뉴명';
COMMENT ON COLUMN COMTNFAVORMENUINFO.MENU_URL IS '메뉴URL';
COMMENT ON COLUMN COMTNFAVORMENUINFO.SORT_NO IS '메뉴순서';
COMMENT ON COLUMN COMTNFAVORMENUINFO.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNFAVORMENUINFO.USER_ID IS '사용자아이디';


/* 7 권한별 메뉴설정 */
DROP SEQUENCE SQ_N_COMTNMENUCREATINFO_01;
CREATE SEQUENCE SQ_N_COMTNMENUCREATINFO_01 START 1;

CREATE TABLE COMTNMENUCREATINFO (
    COMTNMENUCREATINFO_SN numeric(12),
	MENU_ID VARCHAR(8) NOT NULL,
	AUTHOR_CODE VARCHAR(30) NOT NULL,
	READ_ATHR VARCHAR(1),
	REG_ATHR VARCHAR(1),
	DEL_ATHR VARCHAR(1),
	UPD_ATHR VARCHAR(1),
	EXCEL_ATHR VARCHAR(1),
	PRINT_ATHR VARCHAR(1),
	MNL_ATHR VARCHAR(1),
	USE_AT CHAR(1),
	REGISTER_ID VARCHAR(20),
	REGIST_DT TIMESTAMP,
	UPDR_ID VARCHAR(20),
	UPT_DT TIMESTAMP,
	CONSTRAINT  COMTNMENUCREATINFO_1_PK PRIMARY KEY (COMTNMENUCREATINFO_SN)
);

COMMENT ON TABLE COMTNMENUCREATINFO IS '권한별 메뉴설정';
COMMENT ON COLUMN COMTNMENUCREATINFO.COMTNMENUCREATINFO_SN IS '권한별메뉴일련번호';
COMMENT ON COLUMN COMTNMENUCREATINFO.AUTHOR_CODE IS '권한코드';
COMMENT ON COLUMN COMTNMENUCREATINFO.DEL_ATHR IS '삭제권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.EXCEL_ATHR IS '엑셀다운권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.MNL_ATHR IS '메뉴얼권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.PRINT_ATHR IS '출력권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.READ_ATHR IS '읽기권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNMENUCREATINFO.REG_ATHR IS '등록권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPD_ATHR IS '수정권한';
COMMENT ON COLUMN COMTNMENUCREATINFO.UPT_DT IS '수정일시';
COMMENT ON COLUMN COMTNMENUCREATINFO.USE_AT IS '사용여부';


/* 8 메뉴정보 */

CREATE TABLE COMTNMENUINFO (
	MENU_ID VARCHAR(8) NOT NULL,
	MENU_NM VARCHAR(50),
	GROUP_ID VARCHAR(8) NOT NULL,
	PROGRAM_ID VARCHAR(8),
	SORT_NO NUMERIC,
	MENU_DC VARCHAR(100),
	UP_MENU_ID VARCHAR(8),
	LVL NUMERIC,
	MENU_GBN VARCHAR(1),
	SYSTEM_GBN VARCHAR(8),
	USE_AT CHAR(1),
	REGISTER_ID VARCHAR(20),
	REGIST_DT TIMESTAMP,
	UPDR_ID VARCHAR(20),
	UPD_DT TIMESTAMP,
	CONSTRAINT  COMTNMENUINFO_1_PK PRIMARY KEY (MENU_ID)
);

COMMENT ON TABLE COMTNMENUINFO IS '메뉴정보';
COMMENT ON COLUMN COMTNMENUINFO.GROUP_ID IS '그룹아이디';
COMMENT ON COLUMN COMTNMENUINFO.LVL IS '레벨';
COMMENT ON COLUMN COMTNMENUINFO.MENU_DC IS '메뉴설명내용';
COMMENT ON COLUMN COMTNMENUINFO.MENU_GBN IS '메뉴사용구분';
COMMENT ON COLUMN COMTNMENUINFO.MENU_ID IS '메뉴아이디';
COMMENT ON COLUMN COMTNMENUINFO.MENU_NM IS '메뉴명';
COMMENT ON COLUMN COMTNMENUINFO.PROGRAM_ID IS '프로그램아이디';
COMMENT ON COLUMN COMTNMENUINFO.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNMENUINFO.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNMENUINFO.SORT_NO IS '메뉴순서';
COMMENT ON COLUMN COMTNMENUINFO.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNMENUINFO.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNMENUINFO.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNMENUINFO.UP_MENU_ID IS '상위메뉴번호';
COMMENT ON COLUMN COMTNMENUINFO.USE_AT IS '사용여부';

/* 9 프로그램목록 */
CREATE TABLE COMTNPROGRMLIST (
	PROGRAM_ID VARCHAR(8) NOT NULL,
	PROGRAM_NM VARCHAR(50) NOT NULL,
	PROGRAM_URL VARCHAR(100) NOT NULL,
	SYSTEM_GBN VARCHAR(8) NOT NULL,
	PROGRAM_GBN VARCHAR(8) NOT NULL,
	RMK VARCHAR(100),
	USE_AT CHAR(1),
	REGISTER_ID VARCHAR(20),
	REGIST_DT TIMESTAMP,
	UPDR_ID VARCHAR(20),
	UPD_DT TIMESTAMP,
	CONSTRAINT  COMTNAUTHORROLERELATE_1_PK PRIMARY KEY (PROGRAM_ID)
);

COMMENT ON TABLE COMTNPROGRMLIST IS '프로그램목록';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_GBN IS '프로그램구분';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_ID IS '프로그램아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_NM IS '프로그램명';
COMMENT ON COLUMN COMTNPROGRMLIST.PROGRAM_URL IS '프로그램URL';
COMMENT ON COLUMN COMTNPROGRMLIST.REGISTER_ID IS '등록자아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.REGIST_DT IS '등록일시';
COMMENT ON COLUMN COMTNPROGRMLIST.RMK IS '비고';
COMMENT ON COLUMN COMTNPROGRMLIST.SYSTEM_GBN IS '시스템구분';
COMMENT ON COLUMN COMTNPROGRMLIST.UPDR_ID IS '수정자아이디';
COMMENT ON COLUMN COMTNPROGRMLIST.UPD_DT IS '수정일시';
COMMENT ON COLUMN COMTNPROGRMLIST.USE_AT IS '사용여부';

/* 1.권한정보 */
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_ANONYMOUS'               ,'모든 사용자'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_ANONYMOUSLY' ,'스프링시큐리티 내부사용(롤부여 금지)'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_REMEMBERED'  ,'스프링시큐리티 내부사용(롤부여 금지)', '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('IS_AUTHENTICATED_FULLY'       ,'스프링시큐리티 내부사용(롤부여 금지)'    , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_USER'                    ,'일반 사용자'      , '', NOW(),'Y');
INSERT INTO COMTNAUTHORINFO VALUES ('ROLE_ADMIN'                   ,'관리자'           , '', NOW(),'Y');


/* 2. 롤계층구조 */
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'ROLE_ANONYMOUS'               ,'IS_AUTHENTICATED_ANONYMOUSLY');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_ANONYMOUSLY' ,'IS_AUTHENTICATED_REMEMBERED');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_REMEMBERED'  ,'IS_AUTHENTICATED_FULLY');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'), 'IS_AUTHENTICATED_FULLY'       ,'ROLE_USER');
INSERT INTO COMTNROLES_HIERARCHY VALUES (nextval('SQ_N_COMTNROLES_HIERARCHY_01'),'ROLE_USER'                    ,'ROLE_ADMIN');


/* 3 롤정보 */
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00001','로그인롤','url','\A/grain/mbr/.*\.do.*\Z','로그인허용을 위한 룰',1,NOW(),'Y');
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00002','접근테스트롤','url','\A/grain/main/.*\.do.*\Z','로그인메인화면 접근 허용을 위한 룰',10,NOW(),'Y');
INSERT INTO COMTNROLEINFO(ROLE_CODE,ROLE_NM,ROLE_TY,ROLE_PTTRN,ROLE_DC,ROLE_SORT,ROLE_CREAT_DE,USE_AT) VALUES('ROLE00099','모든접근제한','url','\A/.*\.do.*\Z','모든자원에 대한 접근 제한 룰',999,NOW(),'Y');

/* 4. 권한역할 관계 */
INSERT INTO COMTNAUTHORROLERELATE(COMTNAUTHORROLERELATE_SN,AUTHOR_CODE,ROLE_CODE,CREAT_DT) VALUES(nextval('SQ_N_COMTNAUTHORROLERELATE_01'),'ROLE_ANONYMOUS','ROLE00001',NOW());
INSERT INTO COMTNAUTHORROLERELATE(COMTNAUTHORROLERELATE_SN,AUTHOR_CODE,ROLE_CODE,CREAT_DT) VALUES(nextval('SQ_N_COMTNAUTHORROLERELATE_01'),'ROLE_ADMIN','ROLE00099',NOW());