게시판 구현 #2

• #1에서 게시글 작성후 MySQL서버에 저장했다면 이번엔 클라이언트에서 아래의 DB를 클라이언트에서 페이지별로 확인하도록 하겠다.

전체 코드

• https://github.com/kon6443/aws

🔽 server.js 🔽

// server.js

const express = require('express');
const app = express();

app.use(express.static(__dirname + ''));

// importing body-parser to create bodyParser object
const bodyParser = require('body-parser');
// allows you to use req.body var when you use http post method.
app.use(bodyParser.urlencoded({ extended: true }));

// Cookies.
const cookieParser = require('cookie-parser');
app.use(cookieParser());

// allows you to ejs view engine.
app.set('view engine', 'ejs');

// Socket.
const connectSocket = require('./controllers/chat/connectSocket');
// MongoDB.
const { connectMongoDB } = require('./models/connectMongoDB');

// Routers.
const homeRouter = require('./routes/homeRouter');
const game2048Router = require('./routes/2048Router');
const gameTetrisRouter = require('./routes/tetrisRouter');
const userRouter = require('./routes/userRouter');
const chatRouter = require('./routes/chatRouter');
const boardRouter = require('./routes/boardRouter');

const port = 80;
const server = app.listen(port, function() {
    console.log('Listening on '+port);
});

connectSocket(server);
connectMongoDB();

app.use('/', homeRouter);
app.use('/2048', game2048Router);
app.use('/tetris', gameTetrisRouter);
app.use('/user', userRouter);
app.use('/chat', chatRouter);
app.use('/board', boardRouter);

🔽 boardRouter.js 🔽

// boardRouter.js

const express = require("express");
const router = express.Router();

// Importing controller
const boardMiddleWare = require('../controllers/board/board.controller');

const auth = require("../controllers/authMiddleware");

// path: /board/
router.use('/', auth);

router.get('/', boardMiddleWare.showMain);
router.get('/', boardMiddleWare.searchByTitle);
router.get('/write', boardMiddleWare.boardWrite);
router.get('/:id', boardMiddleWare.showPost);
router.get('/:keyStroke', boardMiddleWare.autoComplete);
router.delete('/:id', boardMiddleWare.deleteArticle);
router.post('/article', boardMiddleWare.insertArticle);
router.get('/article/:id', boardMiddleWare.editArticle);
router.put('/article/:id', boardMiddleWare.submitEditedArticle);

module.exports = router;

🔽 board.controller.js 🔽

// board.controller.js

const express = require("express");
const app = express();

// importing body-parser to create bodyParser object
const bodyParser = require('body-parser');
// allows you to use req.body var when you use http post method.
app.use(bodyParser.urlencoded({ extended: true }));

const path = require('path');

// allows you to ejs view engine.
app.set('view engine', 'ejs');
    
const dbMySQLModel = require('../../models/boardDBController');

function getTitlesIncludeString(titles, search) {
    let result = [];
    for(let i=0;i<titles.length;i++) {
        if(titles[i].includes(search)) result.push(titles[i]);
    }
    return result;
}

async function getPageItems(articles_length, page, limit) {
    page = Math.max(1, parseInt(page));
    limit = Math.max(1, parseInt(limit));
    page = !isNaN(page)?page:1;
    limit = !isNaN(limit)?limit:10;
    let last_page = Math.ceil(articles_length/limit);
    const obj = {
        page: page, 
        limit: limit,
        last_page: last_page,
        range_min: (page-1)*limit, 
        range_max: (page === last_page) ? (articles_length) : (page*limit)
    }
    return obj;
}

// Main login page.
exports.showMain = async (req, res, next) => {
    if(req.query.search) return next();
    let { search, page, limit } = req.query;
    const articles = await dbMySQLModel.showTable();
    const boardObject = await getPageItems(articles.length, page, limit);
    return res.render(path.join(__dirname, '../../views/board/board'), {
        articles: articles, 
        user: (req.decoded) ? (req.decoded.id) : ('Guest'),
        page_current: boardObject.page, 
        last_page: boardObject.last_page, 
        length: articles.length, 
        limit: boardObject.limit, 
        range_min: boardObject.range_min,
        range_max: boardObject.range_max,
        search: search
    });
}

exports.searchByTitle = async (req, res) => {
    let { search, page, limit } = req.query;
    let articles = await dbMySQLModel.getMatchingArticles(search);
    if(articles.length === 0) {
        return res.send("<script>alert('No matching article.'); window.location.href = '/board';</script>");
    }
    const boardObject = await getPageItems(articles.length, page, limit);
    return res.render(path.join(__dirname, '../../views/board/board'), {
        articles: articles,
        user: (req.decoded) ? (req.decoded.id) : ('Guest'),
        page_current: boardObject.page,
        last_page: boardObject.last_page, 
        length: articles.length, 
        limit: boardObject.limit, 
        range_min: boardObject.range_min,
        range_max: boardObject.range_max,
        search: search
    });
}

exports.showPost = async (req, res, next) => {
    if(req.query.keyStroke) return next();
    if(req.query.search) return next();
    const user = req.decoded;
    if(user) {
        const article_num = req.params.id;
        let article = await dbMySQLModel.showArticleByNum(article_num);
        return res.render(path.join(__dirname, '../../views/board/article'), {user:user, article: article});
    } else {
        return res.sendFile(path.join(__dirname, '../../views/board/login.html'));
    }
}

exports.autoComplete = async (req, res, next) => {
    if(req.query.search) return next();
    const keyStroke = req.query.keyStroke;
    const titles = await dbMySQLModel.getAllTitles();
    const result = await getTitlesIncludeString(titles, keyStroke);
    return res.status(200).send(result).end();
}

// Writing page.
exports.boardWrite = (req, res) => {
    const user = req.decoded;
    if(user) {
        return res.render(path.join(__dirname, '../../views/board/boardWrite'), {user:user});
    } else {
        return res.sendFile(path.join(__dirname, '../../views/board/login.html'));
    }
}

exports.insertArticle = async (req, res) => {
    const user = req.decoded;
    const { title, content } = req.body;
    if(user) {
        const author = user.id;
        await dbMySQLModel.insert(title, content, author);
        return res.status(200).send('Article has been posted.').end(); 
    } else {
        return res.sendFile(path.join(__dirname, '../../views/board/login.html'));
    }
}

exports.deleteArticle = async (req, res) => {
    const user = req.decoded;
    const { article_num } = req.body;
    const article = await dbMySQLModel.showArticleByNum(article_num);
    if(user.id === article.AUTHOR) {
        await dbMySQLModel.deleteByNum(article_num);
        return res.status(200).send('Article has been removed.').end(); 
    } else {
        return res.status(200).send('Account not matched.').end();
    }
}

exports.editArticle = async (req, res) => {
    const user = req.decoded;
    const article_num = req.params.id;
    const article = await dbMySQLModel.showArticleByNum(article_num);
    if(user.id === article.AUTHOR) {
        return res.render(path.join(__dirname, '../../views/board/editArticle'), {user:user, article:article});
    }
}

exports.submitEditedArticle = async (req, res) => {
    const user = req.decoded;
    const article_num = req.body.id;
    const title = req.body.title;
    const content = req.body.content;
    let article = await dbMySQLModel.showArticleByNum(article_num);
    const date_obj = new Date();
    article.UPDATE_DATE = date_obj.getFullYear() +"-"+ parseInt(date_obj.getMonth()+1) +"-"+ date_obj.getDate();
    await dbMySQLModel.editArticle(article_num, title, content, article.UPDATE_DATE);
    return res.status(200).send('Your article has been editied.');
}

🔽 boardDBController.js 🔽

// boardDBController.js
// connecting MySQL

const path = require('path');

require('dotenv').config({ path: path.resolve(__dirname, '../.env') }); 
const conn = require('../models/connectMySQL');

const util = require('util');
// node native promisify
const query = util.promisify(conn.query).bind(conn);

function convertDateFormat(date) {
    date = date.toLocaleString('default', {year:'numeric', month:'2-digit', day:'2-digit'});
    let year = date.substr(6,4);
    let month = date.substr(0,2);
    let day = date.substr(3,2);
    let convertedDate = `${year}-${month}-${day}`;
    return convertedDate;
}

function convertTableDateFormat(table) {
    for(let i=0;i<table.length;i++) {
        table[i].POST_DATE = convertDateFormat(table[i].POST_DATE);
        table[i].UPDATE_DATE = convertDateFormat(table[i].UPDATE_DATE);
    }
    return table;
}

function convertArticleDateFormat(article) {
    article.POST_DATE = convertDateFormat(article.POST_DATE);
    article.UPDATE_DATE = convertDateFormat(article.UPDATE_DATE);
    return article;
}

exports.showTable = async () => {
    let table = await query("SELECT * FROM BOARD ORDER BY BOARD_NO DESC;");
    table = convertTableDateFormat(table);
    return table;
}

exports.getAllTitles = async () => {
    let titles = await query("SELECT TITLE FROM BOARD ORDER BY BOARD_NO DESC;");
    for(let i=0;i<titles.length;i++) titles[i] = titles[i].TITLE;
    return titles;
}

exports.getMatchingArticles = async (title) => {
    let articles = await query("SELECT * FROM BOARD WHERE TITLE LIKE '%"+title+"%';");
    return convertTableDateFormat(articles);
}

exports.showArticleByNum = async (article_num) => {
    const sql = "SELECT * FROM BOARD WHERE BOARD_NO="+article_num+";";
    let article = await query(sql);
    article = article[0];
    article = convertArticleDateFormat(article);
    return article;
}

exports.insert = async (title, content, author) => {
    // Query to insert multiple rows
    let query = `INSERT INTO BOARD (TITLE, content, POST_DATE, UPDATE_DATE, AUTHOR) VALUES ?;`;
    
    const date_obj = new Date();
    let post_date = date_obj.getFullYear() +"-"+ parseInt(date_obj.getMonth()+1) +"-"+ date_obj.getDate();
    const update_date = post_date;
    // Values to be inserted
    let values = [
        [title, content, post_date, update_date, author]
    ];
    // Executing the query
    await conn.query(query, [values]);
}

exports.deleteByNum = async (article_num) => {
    let query = `DELETE FROM BOARD WHERE BOARD_NO=`+article_num+`;`;
    await conn.query(query);
}

exports.editArticle = async (article_num, title, content, update) => {
    let query = "UPDATE BOARD SET TITLE='"+title+"', content='"+content+"', UPDATE_DATE='"+update+"' WHERE BOARD_NO="+article_num+";";
    await conn.query(query);
}

• 본인 정보로 교체하시면 됩니다.
  🔽 .env 🔽

# For user account DB in mongodb
MONGO_URI = mongodb+srv://'YourID':'YourPW'@cluster0.cefr7.mongodb.net/'YourDB'?retryWrites=true&w=majority

# For jsonwebtoken secret key
SECRET_KEY = 'YourjwtSecret';

# For MySQL board DB
SQL_USER = 'YourID'
SQL_PASSWORD = 'YourPW'

🔽 board.ejs 🔽

<!doctype html>
<html lang="en">
	<head>
		<meta charset="UTF-8">
		<meta name="viewport" content="width=device-width, initial-scale=1">
		<link rel="stylesheet" type="text/css" href="/public/css/styles.css">
		<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-F3w7mX95PdgyTmZZMECAngseQB83DfGTowi0iMjiWaeVhAn4FJkqJByhZMI3AhiU" crossorigin="anonymous">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
		<title>Onam Kwon</title>
		<body class="body" style="background-color: #1c1c1e; color: rgb(221,221,221); font-family: Sans-serif; margin:0 auto;"></body>
	</head>
	
	<body>
		<div class="navbar">
			<a class="active" href="/">Home</a> 
			<a href="/dev">Dev</a> 
			<a href="/private">Private</a> 
			<a href="/user">My page</a>
		</div>  
		<div class="wrapper">
			<ul class="item-list mySelf">
				<ul class="about">
                    <div>
                        <h1>
                            <a href="/board" style="color: white; text-decoration: none; display: inline-block;"> Board </a>
                            <a style="display: inline-block; float: right; margin-right: 3%;"> Welcome <%= user %>! </a>
                        </h1>
                        <div >
                            <input type="text" list="titles" id="search" name="search" placeholder="Title: " oninput="detectKeystroke()" style="display: inline-block;"/>
                            <datalist id="titles">

                            </datalist>
                            <button type="button" id="searchButton" onclick="search()">Search</button> <br><br>
                        </div>
                        <table id="board">
                            <tr>
                                <th style="height: 6vh; width: 15%;"> Article Number </th>
                                <th style="height: 6vh;"> Title </th>
                                <th style="height: 6vh; width: 10%;"> Posted date </th>
                                <th style="height: 6vh; width: 10%;"> Author </th>
                            </tr>
                            <% for(let i=range_min; i< range_max; i++) { %>
                                <tr>
                                    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].BOARD_NO %></a></td>
                                    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].TITLE %></a></td>
                                    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].POST_DATE %></a></td>
                                    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].AUTHOR %></a></td>
                                </tr>
                            <% } %>
                        </table >
                    </div>
                    <button type="button" id="new" onclick="location.href='/board/write'">New</button>
                    <center>
                        <nav class="col-8">
                            <% 
                                var offset = 2;
                                var previousBtnEnabled = page_current>1;
                                var nextBtnEnabled = page_current< last_page; 
                            %>
                            <ul class="pagination pagination-sm justify-content-center align-items-center h-100 mb-0">            
                                <li class="page-item <%= previousBtnEnabled?'':'disabled' %>">
                                    <a class="page-link" href="/board?page=<%= page_current-1 %>&limit=<%= limit %>&search=<%= search %>" <%= previousBtnEnabled?'':'tabindex=-1' %>>«</a>
                                </li>
                                <% for(i=1;i<=last_page;i++){ %>
                                    <% if(i==1 || i==last_page || (i>=page_current-offset && i<=page_current+offset)){ %>
                                        <li class="page-item <%= page_current==i?'active':'' %>"><a class="page-link" href="/board?page=<%= i %>&limit=<%= limit %>&search=<%= search %>"> <%= i %> </a></li>
                                    <% } else if(i==2 || i==last_page-1){ %>
                                        <li><a class="page-link">...</a></li>
                                    <% } %>
                                <% } %>
                                <li class="page-item <%= nextBtnEnabled?'':'disabled' %>">
                                    <a class="page-link" href="/board?page=<%= page_current+1 %>&limit=<%= limit %>&search=<%= search %>" <%= nextBtnEnabled?'':'tabindex=-1' %>>»</a>
                                </li>
                            </ul>
                        </nav>
                    </center>
				</ul>
			</ul>
		</div>
        <script>
            function search() {
                const search = document.getElementById('search').value;
                window.location.href = "/board?search="+search+"&page=<%= page_current %>&limit=<%= limit %>";
                // window.location.href = '/board?search='+search;
            }
            function detectKeystroke() {
                const keyStroke = document.getElementById('search').value;
                if(keyStroke==='') {
                    $("#titles").empty();
                    return ;
                }
                $.ajax({
                    type: "get",
                    url: '/board/:keyStroke',
                    data: {keyStroke:keyStroke},
                    dataType:'json',
                    success: function(res) {
                        let temp = '';
                        for(let i=0;i<res.length;i++) {
                            temp += `<option value="${res[i]}" id="title">`;
                        }
                        $('#titles').empty().append(temp);
                    }
                });
            }
            function signOut() {
                $.ajax({
                    type: "delete",
                    url: '/user/logout',
                    data: {},
                    dataType:'text',
                    success: function(res) {
                        location.reload();
                    }
                });
            } 
        </script>
        <style>
            .ribht {
                float: right;
            }
            #new, {
                float: right;
                margin-right: 3%;
            }
            table {
                width: 97%;
            }
            tr {;
                height: 30px;
            }
            table, tr, td, th {
                border: 1px solid;
                text-align: center;
            }
            #new, #searchButton {
                margin-top: 2%;
                width: 19%;
                height: 34px;
                border-radius: 50px;
                background: black;
                color: white;
                display: inline-block;
            }
        </style>
		<!-- Option 1: Bootstrap Bundle with Popper -->
		<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.1/dist/js/bootstrap.bundle.min.js" integrity="sha384-/bQdsTh/da6pkI1MST/rWKFNjaCP5gBSY4sEBT38Q/9RBh9AH40zEOg7Hlq2THRZ" crossorigin="anonymous"></script>
	</body>
</html>

코드 설명

• 아래 부터는 설명의 편의를 위해 코드의 일부만 가져오므로 코드 복붙은 위에서 해주세요.

서버

🔽 server.js 🔽

const boardRouter = require('./routes/boardRouter');

app.use('/board', boardRouter);

• /board경로는 게시판의 메인 화면이다.
• 아래 사진처럼 생김.
• 해당 경로로 오는 요청은 boardRouter로 보내주도록 한다.

🔽 boardRouter.js 🔽

// Importing controller
const boardMiddleWare = require('../controllers/board/board.controller');
const auth = require("../controllers/authMiddleware");

// path: /board/
router.use('/', auth);
router.get('/', boardMiddleWare.showMain);

module.exports = router;

• /board라우터에서 메인 경로로 들어온 GET요청은 boardMiddleWare.showMain 함수를 호출해 보내주도록 한다.

🔽 board.controller.js 🔽

app.set('view engine', 'ejs');
const dbMySQLModel = require('../../models/boardDBController');

// Main login page.
exports.showMain = async (req, res, next) => {
    if(req.query.search) return next();
    let { search, page, limit } = req.query;
    const articles = await dbMySQLModel.showTable();
    const boardObject = await getPageItems(articles.length, page, limit);
    return res.render(path.join(__dirname, '../../views/board/board'), {
        articles: articles, 
        user: (req.decoded) ? (req.decoded.id) : ('Guest'),
        page_current: boardObject.page, 
        last_page: boardObject.last_page, 
        length: articles.length, 
        limit: boardObject.limit, 
        range_min: boardObject.range_min,
        range_max: boardObject.range_max,
        search: search
    });
}

• showMain 미들웨어 내부 첫번째 줄의 if(req.query.search) return next();는 제목 검색 기능을 구현할때 오는 요청을 다음 미들웨어로 보내기 위한 용도이다.
  • req.query.search값이 있다면 그 아래 코드는 실행되지 않는다.
    
    
• req.query.search값이 없을 경우 전체 테이블을 MySQL서버로 부터 받아와 articles변수에 넣는다.
• 그 후 페이지 기능을 구현하는데 필요한 변수를 인자로 getPageItems()함수에 전달해준 후 리턴값을 클라이언트에 응답해준다.
  • getPageItems() 설명은 아래에.

async function getPageItems(articles_length, page, limit) {
    page = Math.max(1, parseInt(page));
    limit = Math.max(1, parseInt(limit));
    page = !isNaN(page)?page:1;
    limit = !isNaN(limit)?limit:10;
    let last_page = Math.ceil(articles_length/limit);
    const obj = {
        page: page, 
        limit: limit,
        last_page: last_page,
        range_min: (page-1)*limit, 
        range_max: (page === last_page) ? (articles_length) : (page*limit)
    }
    return obj;
}

• range_min range_max값은 특정 페이지에서의 게시글 범위를 나타낸다. 예를들어 페이지당 10개의 게시물을 가지는 2번째 페이지라면 articles[10]~articles[19]의 범위를 나타내며 range_min range_max는 각각 10과 19를 나타낸다.
• 나머지 변수들은 기본값 설정과 예외처리 작업.

🔽 boardDBController.js 🔽

function convertDateFormat(date) {
    date = date.toLocaleString('default', {year:'numeric', month:'2-digit', day:'2-digit'});
    let year = date.substr(6,4);
    let month = date.substr(0,2);
    let day = date.substr(3,2);
    let convertedDate = `${year}-${month}-${day}`;
    return convertedDate;
}

function convertTableDateFormat(table) {
    for(let i=0;i<table.length;i++) {
        table[i].POST_DATE = convertDateFormat(table[i].POST_DATE);
        table[i].UPDATE_DATE = convertDateFormat(table[i].UPDATE_DATE);
    }
    return table;
}

const util = require('util');
// node native promisify
const query = util.promisify(conn.query).bind(conn);

exports.showTable = async () => {
    let table = await query("SELECT * FROM BOARD ORDER BY BOARD_NO DESC;");
    table = convertTableDateFormat(table);
    return table;
}

• dbMySQLModel.showTable();함수는 간단한 MySQL 쿼리문이다.
  • 내림차순으로 받는 이유는 최신 게시글 일수록 나중에 저장되기 때문.
• 내부에 있는 convertTableDateFormat(table);함수는 MySQL에서 시간관련 컬럼을 뽑을시 형태가 글로벌하게 바뀌어 다시 원래대로 바꿔주는 작업이 필요했다.
  • 내부에서 convertDateFormat(table[i].POST_DATE);를 호출하는데 실질적으로 형태를 바꿔주는 작업을 한다.

클라이언트

🔽 board.ejs 🔽

<table id="board">
  <tr>
    <th style="height: 6vh; width: 15%;"> Article Number </th>
    <th style="height: 6vh;"> Title </th>
    <th style="height: 6vh; width: 10%;"> Posted date </th>
    <th style="height: 6vh; width: 10%;"> Author </th>
  </tr>
  <% for(let i=range_min; i< range_max; i++) { %>
  <tr>
    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].BOARD_NO %></a></td>
    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].TITLE %></a></td>
    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].POST_DATE %></a></td>
    <td style="height: 5vh;"><a href="/board/<%= articles[i].BOARD_NO %>" style="color: white; text-decoration: none;"><%= articles[i].AUTHOR %></a></td>
  </tr>
  <% } %>
    </table >
  
  <nav class="col-8">
    <% 
       var offset = 2;
       var previousBtnEnabled = page_current>1;
      var nextBtnEnabled = page_current< last_page; 
                                                   %>
      <ul class="pagination pagination-sm justify-content-center align-items-center h-100 mb-0">            
        <li class="page-item <%= previousBtnEnabled?'':'disabled' %>">
          <a class="page-link" href="/board?page=<%= page_current-1 %>&limit=<%= limit %>&search=<%= search %>" <%= previousBtnEnabled?'':'tabindex=-1' %>>«</a>
      </li>
      <% for(i=1;i<=last_page;i++){ %>
      <% if(i==1 || i==last_page || (i>=page_current-offset && i<=page_current+offset)){ %>
        <li class="page-item <%= page_current==i?'active':'' %>"><a class="page-link" href="/board?page=<%= i %>&limit=<%= limit %>&search=<%= search %>"> <%= i %> </a></li>
          <% } else if(i==2 || i==last_page-1){ %>
            <li><a class="page-link">...</a></li>
            <% } %>
              <% } %>
                <li class="page-item <%= nextBtnEnabled?'':'disabled' %>">
                  <a class="page-link" href="/board?page=<%= page_current+1 %>&limit=<%= limit %>&search=<%= search %>" <%= nextBtnEnabled?'':'tabindex=-1' %>>»</a>
              </li>
            </ul>
        </nav>

• ejs파일에서 윗부분에 테이블을 만들어 게시글을 출력하고, 그 아래에 페이지 기능을 구현할 수 있다.
• 코드의 페이지 부분에서 링크 경로를 주의해서 만들면 구현된다.

• 브라우저 검색창에 주소를 잘보면 page limit이 현재 페이지와 나오는 게시글 갯수와 동일한걸 확인할 수 있다.