enn-lab.ddul.0.0.1.source-code.ddul-schema.sql Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of ddul Show documentation
Show all versions of ddul Show documentation
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.
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