게시글 카테고리
- 게시판별로 게시글 카테고리를 가지고 있다.
- 편의상 각 게시판별로 일반, 질문, 정보, 공지 카테고리를 만든다.
- 일반 : general, 작성 허용, 수정/삭제 허용, 댓글 허용, 좋아요 허용, 싫어요 허용
- 질문 : question, 작성 허용, 수정/삭제 불가, 댓글 허용, 좋아요 허용, 싫어요 허용
- 정보 : tip, 작성 허용, 수정/삭제 허용, 댓글 허용, 좋아요 허용, 싫어요 불가
- 공지 : notice, 작성 불가, 수정/삭제 불가, 댓글 불가, 좋아요 불가, 싫어요 불가
테이블 생성
CREATE TABLE IF NOT EXISTS article_categories(
article_category_id BIGINT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
slug VARCHAR(8) NOT NULL,
board_id BIGINT NOT NULL,
allow_write BOOLEAN NOT NULL,
allow_self_edit_delete BOOLEAN NOT NULL,
allow_comment BOOLEAN NOT NULL,
allow_like BOOLEAN NOT NULL,
allow_dislike BOOLEAN NOT NULL,
created_at DATETIME NOT NULL,
CONSTRAINT uq_board_id_and_name UNIQUE (board_id, name),
CONSTRAINT uq_board_id_and_slug UNIQUE (board_id, slug)
);
인덱스 제거
ALTER TABLE article_categories DROP INDEX uq_board_id_and_name;
ALTER TABLE article_categories DROP INDEX uq_board_id_and_slug;
csv 파일 생성
package com.ttasjwi.board.system.app.board.data
import org.junit.jupiter.api.DisplayName
import org.junit.jupiter.api.Test
import java.io.BufferedWriter
import java.io.File
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
class ArticleCategoryDataInitializer {
companion object {
private val formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
}
@Test
@DisplayName("게시글 카테고리 벌크 삽입 csv 파일 생성")
fun initialize() {
val csvFile = File("article_categories.csv")
csvFile.bufferedWriter().use { writer ->
for (boardId in 1L..500L) {
makeGeneralCategory(writer, boardId, (boardId - 1) * 4 + 1)
makeQuestionCategory(writer, boardId, (boardId - 1) * 4 + 2)
makeTipCategory(writer, boardId, (boardId - 1) * 4 + 3)
makeNoticeCategory(writer, boardId, (boardId - 1) * 4 + 4)
}
}
}
private fun makeGeneralCategory(writer: BufferedWriter, boardId: Long, articleCategoryId: Long) {
val name = "일반"
val slug = "general"
val allowWrite = 1
val allowSelfEditDelete = 1
val allowComment = 1
val allowLike = 1
val allowDislike = 1
val createdAt = LocalDateTime.now().format(formatter)
writer.write("$articleCategoryId,$name,$slug,$boardId,$allowWrite,$allowSelfEditDelete,$allowComment,$allowLike,$allowDislike,$createdAt\n")
if (articleCategoryId % 100 == 0L) {
println("Written $articleCategoryId lines...")
}
}
private fun makeQuestionCategory(writer: BufferedWriter, boardId: Long, articleCategoryId: Long) {
val name = "질문"
val slug = "question"
val allowWrite = 1
val allowSelfEditDelete = 0
val allowComment = 1
val allowLike = 1
val allowDislike = 1
val createdAt = LocalDateTime.now().format(formatter)
writer.write("$articleCategoryId,$name,$slug,$boardId,$allowWrite,$allowSelfEditDelete,$allowComment,$allowLike,$allowDislike,$createdAt\n")
if (articleCategoryId % 100 == 0L) {
println("Written $articleCategoryId lines...")
}
}
private fun makeTipCategory(writer: BufferedWriter, boardId: Long, articleCategoryId: Long) {
val name = "정보"
val slug = "tip"
val allowWrite = 1
val allowSelfEditDelete = 1
val allowComment = 1
val allowLike = 1
val allowDislike = 0
val createdAt = LocalDateTime.now().format(formatter)
writer.write("$articleCategoryId,$name,$slug,$boardId,$allowWrite,$allowSelfEditDelete,$allowComment,$allowLike,$allowDislike,$createdAt\n")
if (articleCategoryId % 100 == 0L) {
println("Written $articleCategoryId lines...")
}
}
private fun makeNoticeCategory(writer: BufferedWriter, boardId: Long, articleCategoryId: Long) {
val name = "공지"
val slug = "notice"
val allowWrite = 0
val allowSelfEditDelete = 0
val allowComment = 0
val allowLike = 0
val allowDislike = 0
val createdAt = LocalDateTime.now().format(formatter)
writer.write("$articleCategoryId,$name,$slug,$boardId,$allowWrite,$allowSelfEditDelete,$allowComment,$allowLike,$allowDislike,$createdAt\n")
if (articleCategoryId % 100 == 0L) {
println("Written $articleCategoryId lines...")
}
}
}
데이터 삽입
docker cp data/article_categories.csv mysql:/tmp/article_categories.csv
LOAD DATA INFILE '/tmp/article_categories.csv'
INTO TABLE article_categories
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(article_category_id, name, slug, board_id, allow_write, allow_self_edit_delete, allow_comment, allow_like, allow_dislike, created_at);
데이터 조회
mysql> select count(*) from article_categories;
+----------+
| count(*) |
+----------+
| 2000 |
+----------+
1 row in set (0.01 sec)
mysql> select * from article_categories order by article_category_id asc limit 30;
+---------------------+--------+----------+----------+-------------+------------------------+---------------+------------+---------------+---------------------+
| article_category_id | name | slug | board_id | allow_write | allow_self_edit_delete | allow_comment | allow_like | allow_dislike | created_at |
+---------------------+--------+----------+----------+-------------+------------------------+---------------+------------+---------------+---------------------+
| 1 | 일반 | general | 1 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 2 | 질문 | question | 1 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 3 | 정보 | tip | 1 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 4 | 공지 | notice | 1 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 5 | 일반 | general | 2 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 6 | 질문 | question | 2 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 7 | 정보 | tip | 2 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 8 | 공지 | notice | 2 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 9 | 일반 | general | 3 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 10 | 질문 | question | 3 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 11 | 정보 | tip | 3 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 12 | 공지 | notice | 3 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 13 | 일반 | general | 4 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 14 | 질문 | question | 4 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 15 | 정보 | tip | 4 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 16 | 공지 | notice | 4 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 17 | 일반 | general | 5 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 18 | 질문 | question | 5 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 19 | 정보 | tip | 5 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 20 | 공지 | notice | 5 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 21 | 일반 | general | 6 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 22 | 질문 | question | 6 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 23 | 정보 | tip | 6 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 24 | 공지 | notice | 6 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 25 | 일반 | general | 7 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 26 | 질문 | question | 7 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 27 | 정보 | tip | 7 | 1 | 1 | 1 | 1 | 0 | 2025-06-09 15:59:28 |
| 28 | 공지 | notice | 7 | 0 | 0 | 0 | 0 | 0 | 2025-06-09 15:59:28 |
| 29 | 일반 | general | 8 | 1 | 1 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
| 30 | 질문 | question | 8 | 1 | 0 | 1 | 1 | 1 | 2025-06-09 15:59:28 |
+---------------------+--------+----------+----------+-------------+------------------------+---------------+------------+---------------+---------------------+
30 rows in set (0.00 sec)
제약조건 다시 추가
ALTER TABLE article_categories
ADD CONSTRAINT uq_board_id_and_name UNIQUE (board_id, name),
ADD CONSTRAINT uq_board_id_and_slug UNIQUE (board_id, slug);