All Downloads are FREE. Search and download functionalities are using the official Maven repository.

enn-lab.ddul.0.0.1.source-code.ddul-schema.sql Maven / Gradle / Ivy

Go to download

DDul means "ground" in korean prounance. It helps to you when concreate a java projects. And support with database access, cache abstraction, system code generation, hierarchical data handling, article management and etc.

There is a newer version: 0.3.0
Show newest version
DROP SEQUENCE IF EXISTS ID_GENERATOR_SQ;
CREATE SEQUENCE IF NOT EXISTS ID_GENERATOR_SQ START WITH 1;


--
-- User entity
--
DROP TABLE IF EXISTS USER CASCADE;
CREATE TABLE IF NOT EXISTS USER
(
    ID           BIGINT PRIMARY KEY,
    USERNAME     VARCHAR(32)  NOT NULL UNIQUE,
    PASSWORD     VARCHAR(256),
    PASSWORD_EXP TIMESTAMP,
    EMAIL        VARCHAR(128),
    NAME         VARCHAR(64),
    DEPT         BIGINT,
    GRADE        VARCHAR(64),
    HIRED        DATE,
    LOCK         CHAR(1)   DEFAULT 'N' CHECK (LOCK = 'Y' OR LOCK = 'N'),
    -- auditors
    REMOVAL      CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR      VARCHAR(32) COMMENT '생성자',
    UPDATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER      VARCHAR(32) COMMENT '수정자'
);
COMMENT ON TABLE USER IS '사용자';
COMMENT ON COLUMN USER.USERNAME IS '사용자명(로그인 ID)';
COMMENT ON COLUMN USER.PASSWORD_EXP IS '비밀번호 만료일시';
COMMENT ON COLUMN USER.NAME IS '성명';
COMMENT ON COLUMN USER.DEPT IS '부서';
COMMENT ON COLUMN USER.GRADE IS '직급';
COMMENT ON COLUMN USER.LOCK IS '잠김';

INSERT INTO USER (ID, USERNAME, PASSWORD, EMAIL, NAME, PASSWORD_EXP)
VALUES (-1, 'tester', '{noop}test123$', '[email protected]', 'foo',
        DATEADD('MONTH', 3, CURRENT_DATE));
INSERT INTO USER (ID, USERNAME, PASSWORD, EMAIL, NAME, PASSWORD_EXP)
VALUES (-2, 'leader', '{noop}lead123$', '[email protected]', 'bar',
        DATEADD('MONTH', 3, CURRENT_DATE));

--
-- Team Entity
--
DROP TABLE IF EXISTS TEAM CASCADE;
CREATE TABLE IF NOT EXISTS TEAM
(
    ID      BIGINT PRIMARY KEY,
    PID     BIGINT,
    NAME    VARCHAR(64),
    ORD     INT       DEFAULT 0,
    -- auditors
    REMOVAL CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR VARCHAR(32) COMMENT '생성자',
    UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER VARCHAR(32) COMMENT '수정자'
);
COMMENT ON TABLE TEAM IS '부서';
COMMENT ON COLUMN TEAM.ORD IS '정렬순서';
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-900, NULL, '회장실', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-910, -900, '미래전략실', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-911, -910, '실장실', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-912, -910, '차장실', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-913, -912, '준법경영실', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-914, -912, '전략1팀', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-915, -912, '전략2팀', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-916, -912, '경영지원실', 2);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-920, -900, 'DS', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-921, -920, '메모리 사업부', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-922, -920, '시스템 SI 사업부', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-923, -920, '파운드리 사업부', 2);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-930, -900, 'CE', 3);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-931, -930, '디스플레이 사업부', 1);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-932, -930, '생활가전 사업부', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-933, -930, '의료기기 사업부', 2);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-940, -900, 'IM', 2);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-941, -940, '무선 사업부', 0);
INSERT INTO TEAM (ID, PID, NAME, ORD) VALUES (-942, -940, '네트워크 사업부', 1);

DROP TABLE IF EXISTS USER_TEAM CASCADE;
CREATE TABLE IF NOT EXISTS USER_TEAM
(
    ID      BIGINT PRIMARY KEY,
    USER_ID BIGINT,
    TEAM_ID BIGINT,
    -- auditors
    REMOVAL CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR VARCHAR(32) COMMENT '생성자',
    UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE USER_TEAM
	ADD CONSTRAINT USER_TEAM_FK1
		FOREIGN KEY (USER_ID) REFERENCES USER;

ALTER TABLE USER_TEAM
	ADD CONSTRAINT USER_TEAM_FK2
		FOREIGN KEY (TEAM_ID) REFERENCES TEAM;

COMMENT ON TABLE USER_TEAM IS '사용자-부서 매핑';


--
-- Authority Entity
--
DROP TABLE IF EXISTS AUTHORITY CASCADE;
CREATE TABLE IF NOT EXISTS AUTHORITY
(
    ID      BIGINT PRIMARY KEY,
    PID     INT,
    ROLE    VARCHAR(32) NOT NULL UNIQUE,
    -- auditors
    REMOVAL CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR VARCHAR(32) COMMENT '생성자',
    UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER VARCHAR(32) COMMENT '수정자'
);
COMMENT ON TABLE AUTHORITY IS '권한';
COMMENT ON COLUMN AUTHORITY.ROLE IS '권한 이름';

INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (0, NULL, 'ADMIN');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-1, 0, 'BOSS');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-2, -1, 'OFFICER');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-3, -2, 'MINION');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-4, -3, 'ROOKIE');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-5, -3, 'INTERN');
INSERT INTO AUTHORITY (ID, PID, ROLE)
VALUES (-6, -4, 'NOVICE');


DROP TABLE IF EXISTS USER_AUTHORITY CASCADE;
CREATE TABLE IF NOT EXISTS USER_AUTHORITY
(
    ID           BIGINT PRIMARY KEY,
    USER_ID      BIGINT NOT NULL,
    AUTHORITY_ID BIGINT NOT NULL,
    -- auditors
    REMOVAL      CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR      VARCHAR(32) COMMENT '생성자',
    UPDATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER      VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE USER_AUTHORITY
	ADD CONSTRAINT USER_AUTHORITY_FK1
		FOREIGN KEY (USER_ID) REFERENCES USER;
ALTER TABLE USER_AUTHORITY
	ADD CONSTRAINT USER_AUTHORITY_FK2
		FOREIGN KEY (AUTHORITY_ID) REFERENCES AUTHORITY;

COMMENT ON TABLE USER_AUTHORITY IS '사용자-권한 매핑';

INSERT INTO USER_AUTHORITY (ID, USER_ID, AUTHORITY_ID)
VALUES (-1, -1, -2);
INSERT INTO USER_AUTHORITY (ID, USER_ID, AUTHORITY_ID)
VALUES (-2, -1, -6);
INSERT INTO USER_AUTHORITY (ID, USER_ID, AUTHORITY_ID)
VALUES (-3, -2, -4);


DROP TABLE IF EXISTS TEAM_AUTHORITY CASCADE;
CREATE TABLE IF NOT EXISTS TEAM_AUTHORITY
(
    ID           BIGINT PRIMARY KEY,
    TEAM_ID      BIGINT NOT NULL,
    AUTHORITY_ID BIGINT NOT NULL,
    -- auditors
    REMOVAL      CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR      VARCHAR(32) COMMENT '생성자',
    UPDATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER      VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE TEAM_AUTHORITY
	ADD CONSTRAINT TEAM_AUTHORITY_FK1
		FOREIGN KEY (TEAM_ID) REFERENCES TEAM;
ALTER TABLE TEAM_AUTHORITY
	ADD CONSTRAINT TEAM_AUTHORITY_FK2
		FOREIGN KEY (AUTHORITY_ID) REFERENCES AUTHORITY;

COMMENT ON TABLE TEAM_AUTHORITY IS '부서-권한 매핑';

--
-- Menu Entity
--
DROP TABLE IF EXISTS MENU CASCADE;
CREATE TABLE IF NOT EXISTS MENU
(
    ID       BIGINT PRIMARY KEY,
    PID      BIGINT,
    NAME     VARCHAR(256) NOT NULL,
    NAME_AID VARCHAR(256),
    URI      VARCHAR(256),
    DSC      VARCHAR(1024),
    ORD      INT       DEFAULT 0,
    BADGE    VARCHAR(16),
    ICON     VARCHAR(64),
    ATTR     VARCHAR(512),
    OPENED   TIMESTAMP,
    -- auditors
    REMOVAL  CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR  VARCHAR(32) COMMENT '생성자',
    UPDATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER  VARCHAR(32) COMMENT '수정자'
);
COMMENT ON TABLE MENU IS '메뉴';
COMMENT ON COLUMN MENU.PID IS '상위(부모) ID';
COMMENT ON COLUMN MENU.NAME_AID IS '보조 이름';
COMMENT ON COLUMN MENU.DSC IS '설명';
COMMENT ON COLUMN MENU.ORD IS '순서';
COMMENT ON COLUMN MENU.ATTR IS '속성';
COMMENT ON COLUMN MENU.OPENED IS '개방일시';
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-999, 'Documentation', 'Documentation', '', 999,
        'Usage guides for everything you need to know about it', null);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-992, 'Core Features', 'Core Features', '/documentation/features', 2, '', -999);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-991, 'Guides', 'Guides', '/documentation/guides', 1, '', -999);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-990, 'Changelog', 'Changelog', '/documentation/changelog', 0, '', -999);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-906, 'Inactive Item', 'Inactive Item', '', 3, '', -900);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-905, 'Inactive Item', 'Inactive Item', '', 3, '', -900);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-904, 'Round badge', 'Round badge', '', 2, '', -901);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-903, 'Rectangle badge', 'Rectangle badge', '', 1, '', -901);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-902, 'Circle badge', 'Circle badge', '', 0, '', -901);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-901, 'badges', 'badges', '', 0, '', -900);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-900, 'Navigation Features', 'Navigation Features', '', 900,
        'Collapsable levels and badge styles at menu item', null);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-33, 'Colors', 'Colors', '/user-interface/colors', 4, '', -25);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-32, 'Cards', 'Cards', '/user-interface/cards', 3, '', -25);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-31, 'Icons', 'Icons', '/user-interface/icons', 2, '', -25);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-30, 'Wizards', 'Wizards', '/user-interface/form/wizards', 2, '', -27);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-29, 'Layouts', 'Layouts', '/user-interface/form/layouts', 1, '', -27);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-28, 'Fields', 'Fields', '/user-interface/form/fields', 0, '', -27);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-27, 'Form', 'Form', '', 1, '', -25);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-26, 'Animation', 'Animation', '/user-interface/animation', 0, '', -25);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-25, 'User Interface', 'User Interface', '', 4, 'Building elements of UI/UX', 0);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-24, 'Split screen', 'Sign up', '/pages/authentication/login/split-screen', 1, '', -22);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-23, 'Fullscreen', 'Sign up', '/pages/authentication/login/fullscreen', 0, '', -22);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-22, 'Screen Play', 'Sign up', '', 1, '', -20);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-21, 'Classic', 'Sign up', '/pages/authentication/login/classic', 0, '', -20);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-20, 'Login', 'Login', '', 1, '', -10);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-19, 'Modern', 'Modern', '/pages/authentication/sign-up/modern', 1, '', -17);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-18, 'Classic', 'Classic', '/pages/authentication/sign-up/classic', 0, '', -17);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-17, 'Sign up', 'Sign up', '', 0, '', -10);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-16, 'Internal server error', 'Internal server error', '', 1, '', -14);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-15, 'Page not found', 'Page not found', '', 0, '', -14);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-14, 'Errors', 'Errors', '', 4, '', -9);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-13, 'Support', 'Support', '/pages/support', 3, '', -9);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-12, 'Home', 'Home', '/pages/home', 2, '', -9);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-11, 'Help center', 'Help center', '/pages/help-center', 1, '', -9);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-10, 'Authentication', 'Authentication', '', 0, '', -9);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-9, 'Pages', 'Pages', '', 3, '', 0);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-8, 'Analytics', 'Analytics', '/dashboard/analytics', 0, '', -7);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-7, 'Dashboard', 'Dashboard', '', 2, 'Analystics for system performances!', 0);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-6, 'Menu', 'Menu', '/system/menu', 1, '', -4);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-5, 'User', 'User', '/system/user', 0, 'approve new user, denied, etc.', -4);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-4, 'System', 'System', '', 1, 'Operating environment of system', 0);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-3, 'Approve Process', 'Approve Process', '/work/approve-process', 1, '', -1);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-2, 'Receipt', 'Receipt', '/work/receipt', 0, '', -1);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (-1, 'Main Works', 'Main Works', '', 0, 'Remember an earning yours meal!', 0);
INSERT INTO MENU (ID, NAME, NAME_AID, URI, ORD, DSC, PID)
VALUES (0, '관리자메뉴', '', '', 0, '', null);


DROP TABLE IF EXISTS MENU_AUTHORITY CASCADE;
CREATE TABLE IF NOT EXISTS MENU_AUTHORITY
(
    ID           BIGINT PRIMARY KEY,
    MENU_ID      BIGINT NOT NULL,
    AUTHORITY_ID BIGINT NOT NULL,
    -- auditors
    REMOVAL      CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR      VARCHAR(32) COMMENT '생성자',
    UPDATED      TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER      VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE MENU_AUTHORITY
	ADD CONSTRAINT MENU_AUTHORITY_FK1
		FOREIGN KEY (MENU_ID) REFERENCES MENU;
ALTER TABLE MENU_AUTHORITY
	ADD CONSTRAINT MENU_AUTHORITY_FK2
		FOREIGN KEY (AUTHORITY_ID) REFERENCES AUTHORITY;

COMMENT ON TABLE MENU_AUTHORITY IS '메뉴-권한 매핑';


DROP TABLE IF EXISTS CODE CASCADE;
CREATE TABLE IF NOT EXISTS CODE
(
    ID      BIGINT PRIMARY KEY,
    GRP_COD VARCHAR(256) NOT NULL,
    COD     VARCHAR(256) NOT NULL,
    COD_NM  VARCHAR(256),
    ORD     INT       DEFAULT 0,
    DSC     VARCHAR(512),
    USE     CHAR(1)   DEFAULT 'Y' CHECK (USE = 'Y' OR USE = 'N'),
    -- auditors
    REMOVAL CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR VARCHAR(32) COMMENT '생성자',
    UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE CODE
    ADD CONSTRAINT CODE_UK1 UNIQUE (GRP_COD, COD);

COMMENT ON TABLE CODE IS '공통코드';
COMMENT ON COLUMN CODE.GRP_COD IS '그룹(상위) 코드';
COMMENT ON COLUMN CODE.COD IS '코드';
COMMENT ON COLUMN CODE.COD_NM IS '코드 이름';
COMMENT ON COLUMN CODE.ORD IS '순서';
COMMENT ON COLUMN CODE.DSC IS '설명';

INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (0, 'SYS', 'SYSTEMS', '시스템 그룹', 9999, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-1, 'SYSTEMS', 'SITE_A', '사이트 A', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-2, 'SYSTEMS', 'SITE_B', '사이트 B', 1, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-3, 'SYSTEMS', 'SITE_C', '사이트 C', 2, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-4, 'SYSTEMS', 'SITE_D', '사이트 D', 3, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-5, 'SITE_A', 'SAMPLE', '직급', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-6, 'SAMPLE', '004', 'executive director', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-7, 'SAMPLE', '005', 'director', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-8, 'SAMPLE', '006', 'department manager', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-9, 'SAMPLE', '008', 'Deputy General Manager', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-10, 'SAMPLE', '003', 'vice-president', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-11, 'SAMPLE', '002', 'representative', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-12, 'SAMPLE', '001', 'CEO', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-13, 'SAMPLE', '010', 'Assistant Manager', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-14, 'SAMPLE', '007', 'Head of team', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-15, 'SAMPLE', '012', 'Staff', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-16, 'SAMPLE', '009', 'Manager', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-17, 'SAMPLE', '011', 'Senior staff', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-18, 'SITE_A', 'FRUIT', '과일', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-19, 'FRUIT', '001', '수박', 0, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-20, 'FRUIT', '002', '참외', 1, '');
INSERT INTO CODE (ID, GRP_COD, COD, COD_NM, ORD, DSC)
VALUES (-21, 'FRUIT', '003', '복숭아', 2, '');

--
-- File entity
--
DROP TABLE IF EXISTS FILE CASCADE;
CREATE TABLE IF NOT EXISTS FILE
(
    ID      VARCHAR(128) PRIMARY KEY,
    NAME    VARCHAR(256) NOT NULL,
    PATH    VARCHAR(512) NOT NULL,
    SIZE    BIGINT    DEFAULT 0,
    MIME    VARCHAR(128),
    ACCESS  BIGINT    DEFAULT 0,
    PACK    VARCHAR(64) DEFAULT 'NONE',
    ORD     INT,
    -- auditors
    REMOVAL CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR VARCHAR(32) COMMENT '생성자',
    UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER VARCHAR(32) COMMENT '수정자'
);
CREATE INDEX FILE_PACK_IX ON FILE (PACK);
COMMENT ON TABLE FILE IS '첨부파일';
COMMENT ON COLUMN FILE.NAME IS '원본 파일명';
COMMENT ON COLUMN FILE.PATH IS '저장 경로';
COMMENT ON COLUMN FILE.SIZE IS '파일 크기';
COMMENT ON COLUMN FILE.MIME IS 'Content-Type(or Mime-Type)';
COMMENT ON COLUMN FILE.ACCESS IS '다운로드 횟수';
COMMENT ON COLUMN FILE.PACK IS '파일 묶음';
COMMENT ON COLUMN FILE.ORD IS '파일 정렬 순서';


--
-- Article Entity
--
DROP TABLE IF EXISTS ARTICLE_CATEGORY CASCADE;
CREATE TABLE IF NOT EXISTS ARTICLE_CATEGORY
(
    CATEGORY VARCHAR(64) PRIMARY KEY,
    TYPE     VARCHAR(64),
    DSC      VARCHAR(4000),
    PROPS    CLOB,
    REMOVAL  CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR  VARCHAR(32) COMMENT '생성자',
    UPDATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER  VARCHAR(32) COMMENT '수정자'
);
COMMENT ON TABLE ARTICLE_CATEGORY IS '게시판 분류';
COMMENT ON COLUMN ARTICLE_CATEGORY.TYPE IS '유형(일반게시판/공지사항/자료실 등등)';
COMMENT ON COLUMN ARTICLE_CATEGORY.DSC IS '설명';
COMMENT ON COLUMN ARTICLE_CATEGORY.PROPS IS '속성(JSON)';
INSERT INTO ARTICLE_CATEGORY (CATEGORY, TYPE, DSC, PROPS) VALUES ('NOTICE', '', '', CAST('{"page": {"size": 12, "indices": 5}, "usage": {"reply": true, "comment": true, "defaultSecret": false}}' AS CLOB));


DROP TABLE IF EXISTS ARTICLE CASCADE;
CREATE TABLE IF NOT EXISTS ARTICLE
(
    ID       BIGINT PRIMARY KEY,
    BID      BIGINT,
    ORD      INT       DEFAULT 0,
    DEPTH    INT       DEFAULT 0,
    CATEGORY VARCHAR(64) NOT NULL,
    USER_ID  BIGINT,
    AUTHOR   VARCHAR(64),
    EMAIL    VARCHAR(256),
    PASSWORD VARCHAR(256),
    SECRET   CHAR(1)   DEFAULT 'N' CHECK (SECRET = 'Y' OR SECRET = 'N'),
    READ     BIGINT    DEFAULT 0,
    TITLE    VARCHAR(1024),
    CONTENT  CLOB,
    FULLTEXT CLOB,
    EXTRA    CLOB,
    -- auditors
    REMOVAL  CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR  VARCHAR(32) COMMENT '생성자',
    UPDATED  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER  VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE ARTICLE
    ADD CONSTRAINT ARTICLE_FK1
        FOREIGN KEY (USER_ID) REFERENCES USER (ID);
COMMENT ON TABLE ARTICLE IS '게시글';
COMMENT ON COLUMN ARTICLE.BID IS '답글용 묶음 ID';
COMMENT ON COLUMN ARTICLE.ORD IS '답글 정렬 순서';
COMMENT ON COLUMN ARTICLE.DEPTH IS '답글 깊이';
COMMENT ON COLUMN ARTICLE.CATEGORY IS '게시글 분류';
COMMENT ON COLUMN ARTICLE.SECRET IS '비밀글';
COMMENT ON COLUMN ARTICLE.READ IS '조횟수';
COMMENT ON COLUMN ARTICLE.FULLTEXT IS '문자화된 내용(HTML 태그 제거, 텍스트 검색 용)';
COMMENT ON COLUMN ARTICLE.EXTRA IS '부가정보(JSON)';


DROP TABLE IF EXISTS ARTICLE_COMMENT CASCADE;
CREATE TABLE IF NOT EXISTS ARTICLE_COMMENT
(
    ID         BIGINT PRIMARY KEY,
    BID        BIGINT,
    ORD        INT       DEFAULT 0,
    DEPTH      INT       DEFAULT 0,
    ARTICLE_ID BIGINT NOT NULL,
    USER_ID    BIGINT,
    AUTHOR     VARCHAR(64),
    EMAIL      VARCHAR(256),
    PASSWORD   VARCHAR(256),
    COMMENT    VARCHAR(4000),
    SECRET     CHAR(1)   DEFAULT 'N' CHECK (SECRET = 'Y' OR SECRET = 'N'),
    -- auditors
    REMOVAL    CHAR(1)   DEFAULT 'N' COMMENT '삭제여부' CHECK (REMOVAL = 'Y' OR REMOVAL = 'N'),
    CREATED    TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성일시',
    CREATOR    VARCHAR(32) COMMENT '생성자',
    UPDATED    TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '수정일시',
    UPDATER    VARCHAR(32) COMMENT '수정자'
);
ALTER TABLE ARTICLE_COMMENT
    ADD CONSTRAINT ARTICLE_COMMENT_FK1
        FOREIGN KEY (ARTICLE_ID) REFERENCES ARTICLE;

COMMENT ON TABLE ARTICLE_COMMENT IS '게시글 댓글';
COMMENT ON COLUMN ARTICLE_COMMENT.BID IS '묶음 ID';
COMMENT ON COLUMN ARTICLE_COMMENT.ORD IS '정렬 순서';
COMMENT ON COLUMN ARTICLE_COMMENT.DEPTH IS '답글 깊이';
COMMENT ON COLUMN ARTICLE_COMMENT.ARTICLE_ID IS '게시물 ID';
COMMENT ON COLUMN ARTICLE_COMMENT.AUTHOR IS '작성자 이름';
COMMENT ON COLUMN ARTICLE_COMMENT.EMAIL IS '이메일';
COMMENT ON COLUMN ARTICLE_COMMENT.PASSWORD IS '비밀번호';
COMMENT ON COLUMN ARTICLE_COMMENT.SECRET IS '비밀글';


DROP TABLE IF EXISTS BBS_SETUP CASCADE;
CREATE TABLE IF NOT EXISTS BBS_SETUP
(
    ID BIGINT PRIMARY KEY,
    CATEGORY VARCHAR(64),
    TYPE VARCHAR(64),
    DSC VARCHAR(4000)
);
COMMENT ON TABLE BBS_SETUP IS '게시판 설정';
COMMENT ON COLUMN BBS_SETUP.CATEGORY IS '분류 이름';
COMMENT ON COLUMN BBS_SETUP.TYPE IS '형태(공지사항/미디어/...)';
COMMENT ON COLUMN BBS_SETUP.DSC IS '설명';




© 2015 - 2024 Weber Informatics LLC | Privacy Policy