Primavera Chap03

Primavera 프로젝트 개요

최대한 실무에서 경험한 내용을 바탕으로 Primavera 프로젝트를 만들고자 하였습니다. 프로젝트를 만들어 가는 과정에서 많은 경험을 할 수 있을 거라 믿습니다. 커뮤니티사이트를 만들기 간략한 기능명세와 ERD를 간략하게 정리하였습니다. 참고로 아래 기술명세와 기능명세는 계속 업데이트 될 예정입니다.

기술명세

  • Java 12 (Switch expressions)
  • IntelliJ IDEA (2019.2)
  • MariaDB 10.3.14
  • Springboot 2.1.6.RELEASE
  • Springboot Security 2.1.6.RELEASE
  • Thymeleaf
  • Mybatis
  • JPA, ModelMapper
  • Lombok
  • Lucy-xss
  • Flyway
  • Bootstrap

기능명세

  • Social 정보를 이용한 회원 가입
  • Social 정보를 이용한 로그인, 로그아웃, 탈퇴
  • 게시글 등록, 수정, 삭제, 조회 기능
  • 게시글 답글 등록, 수정, 삭제 기능
  • 게시글 댓글 등록, 수정, 삭제 기능 및 대댓글 등록, 수정, 삭제
  • 게시글 관련 첨부파일 등록, 삭제
  • 게시글 편집 기능

ERD 및 DDL

ERD

ERD

DDL


CREATE DATABASE primavera DEFAULT CHARACTER SET utf8mb4;
CREATE USER 'primavera'@'localhost' IDENTIFIED BY 'primavera';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON primavera.* TO 'primavera'@'localhost';

CREATE TABLE IF NOT EXISTS USER
(
    ID       BIGINT(20)   NOT NULL AUTO_INCREMENT,
    EMAIL    VARCHAR(50)  NOT NULL,
    PASSWORD VARCHAR(100) NOT NULL,
    NICKNAME VARCHAR(45)  NOT NULL,
    STATUS   CHAR(1)      NOT NULL DEFAULT 'A',
    REG_DT   DATETIME     NOT NULL,
    MOD_DT   DATETIME              DEFAULT NULL,
    PRIMARY KEY (ID),
    UNIQUE KEY EMAIL_UNIQUE (EMAIL)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS USER_CONNECTION
(
    ID           BIGINT(20)   NOT NULL AUTO_INCREMENT,
    EMAIL        VARCHAR(50)  NOT NULL,
    PROVIDER     TINYINT(11)  NOT NULL,
    PROVIDER_ID  VARCHAR(45)   NOT NULL,
    DISPLAY_NAME VARCHAR(45)  DEFAULT NULL,
    PROFILE_URL  VARCHAR(200) DEFAULT NULL,
    IMAGE_URL    VARCHAR(200) DEFAULT NULL,
    ACCESS_TOKEN VARCHAR(200) NOT NULL,
    EXPIRE_TIME  BIGINT(20)   DEFAULT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS USER_ROLE
(
    USER_ID BIGINT(20) NOT NULL,
    ROLE_ID INT(11)    NOT NULL,
    PRIMARY KEY (USER_ID, ROLE_ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS ROLE
(
    ID   INT(11)    NOT NULL AUTO_INCREMENT,
    TYPE TINYINT(3) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS POST
(
    ID        BIGINT(20)   NOT NULL AUTO_INCREMENT,
    WRITER_ID BIGINT(20)   NOT NULL,
    SUBJECT   VARCHAR(200) NOT NULL,
    CONTENTS  TEXT         NOT NULL,
    STATUS    TINYINT(3)   NOT NULL,
    REG_DT    DATETIME DEFAULT NULL,
    MOD_DT    DATETIME DEFAULT NULL,
    PRIMARY KEY (ID),
    KEY FK_WRITER_ID (WRITER_ID),
    CONSTRAINT FK_POST_WRITER_ID FOREIGN KEY (WRITER_ID) REFERENCES USER (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE
(
    ID BIGINT(20) NOT NULL AUTO_INCREMENT,
    P_ID BIGINT(20) NOT NULL DEFAULT 0,
    REFERENCE BIGINT(20) NOT NULL,
    STEP INT(11) NOT NULL,
    LEVEL INT(11) NOT NULL,
    AUTHOR BIGINT(20) NOT NULL,
    SUBJECT VARCHAR(200) NOT NULL,
    STATUS TINYINT(3) NOT NULL,
    HIT BIGINT(20) NOT NULL DEFAULT 0,
    RECOMMEND BIGINT(20) NOT NULL DEFAULT 0,
    DISAPPROVE BIGINT(20) NOT NULL DEFAULT 0,
    REG_DT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
    MOD_DT TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (ID),
    KEY FK_WRITER_ID_IDX (AUTHOR),
    CONSTRAINT FK_ARTICLE_AUTHOR_ID FOREIGN KEY (AUTHOR) REFERENCES USER (ID) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE_ATTACHMENT
(
    ID BIGINT(20) NOT NULL AUTO_INCREMENT,
    ARTICLE_ID BIGINT(20) NOT NULL,
    NAME VARCHAR(100) NOT NULL,
    PATH VARCHAR(200) NOT NULL,
    SIZE INT(11) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE  IF NOT EXISTS ARTICLE_COMMENT
(
    ID BIGINT(20) NOT NULL AUTO_INCREMENT,
    ARTICLE_ID BIGINT(20) NOT NULL,
    LEVEL INT(11) NOT NULL,
    STEP INT(11) NOT NULL,
    COMMENT TEXT NOT NULL,
    AUTHOR VARCHAR(45) NOT NULL,
    STATUS TINYINT(3) NOT NULL,
    REG_DT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
    MOD_DT TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (ID)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ARTICLE_CONTENT
(
    ID         BIGINT(20) NOT NULL AUTO_INCREMENT,
    ARTICLE_ID BIGINT(20) DEFAULT NULL,
    CONTENTS   text       DEFAULT NULL,
    PRIMARY KEY (ID),
    KEY FK_AUTHOR_IDX (ARTICLE_ID),
    CONSTRAINT FK_ARTICLE_ID FOREIGN KEY (ARTICLE_ID) REFERENCES ARTICLE (ID) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

CREATE TABLE IF NOT EXISTS WINNER
(
    ID INT(11) NOT NULL AUTO_INCREMENT,
    USER_ID INT(45) NOT NULL,
    WINNER ENUM('WINNER','LOSER') NOT NULL,
    REG_DT DATETIME NOT NULL,
    PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO ROLE (TYPE)
VALUES (1);
INSERT INTO ROLE (TYPE)
VALUES (2);
INSERT INTO ROLE (TYPE)
VALUES (3);

INSERT INTO `USER`(EMAIL, PASSWORD, NICKNAME, STATUS, REG_DT)
VALUES ('Genius Choi', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Genius', 1, NOW());
INSERT INTO `USER_CONNECTION`(EMAIL, PROVIDER, PROVIDER_ID, DISPLAY_NAME, PROFILE_URL, IMAGE_URL, ACCESS_TOKEN, EXPIRE_TIME)
VALUES ('Genius Choi', 1, 123456789, 'Genius', 'PROFILE', 'IMAGE', '1234567890', -1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 2);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (1, 3);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Son Heung-min', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Son', 1, NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 2);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (2, 3);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Lionel Messi', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Messi', 1, NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (3, 1);
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (3, 2);

INSERT INTO `USER`(`EMAIL`, `PASSWORD`, `NICKNAME`, `STATUS`, `REG_DT`)
VALUES ('Cristiano Ronaldo', '{bcrypt}$2a$10$7UEHLpn1r4gZY2qxiZFJ5.7wa3Hdz8IXgxUtFogy0Ac10fh7TG4V.', 'Ronaldo', 1,NOW());
INSERT INTO `USER_ROLE`(`USER_ID`, ROLE_ID)
VALUES (4, 1);

참고

  • ERD 표기 : http://www.dbguide.net/db.db?cmd=view&boardUid=12845&boardConfigUid=9&boardIdx=31&boardStep=1