// board.controller.jsconst express =require("express");const app =express();// importing body-parser to create bodyParser objectconst 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');functiongetTitlesIncludeString(titles, search){let result =[];for(let i=0;i<titles.length;i++){if(titles[i].includes(search)) result.push(titles[i]);}return result;}asyncfunctiongetPageItems(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)returnnext();let{ search, page, limit }= req.query;const articles =await dbMySQLModel.showTable();const boardObject =awaitgetPageItems(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 =awaitgetPageItems(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)returnnext();if(req.query.search)returnnext();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)returnnext();const keyStroke = req.query.keyStroke;const titles =await dbMySQLModel.getAllTitles();const result =awaitgetTitlesIncludeString(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 =newDate();
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 MySQLconst path =require('path');require('dotenv').config({ path: path.resolve(__dirname,'../.env')});const conn =require('../models/connectMySQL');const util =require('util');// node native promisifyconst query = util.promisify(conn.query).bind(conn);functionconvertDateFormat(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;}functionconvertTableDateFormat(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;}functionconvertArticleDateFormat(article){
article.POST_DATE=convertDateFormat(article.POST_DATE);
article.UPDATE_DATE=convertDateFormat(article.UPDATE_DATE);return article;}
exports.showTable=async()=>{let table =awaitquery("SELECT * FROM BOARD ORDER BY BOARD_NO DESC;");
table =convertTableDateFormat(table);return table;}
exports.getAllTitles=async()=>{let titles =awaitquery("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 =awaitquery("SELECT * FROM BOARD WHERE TITLE LIKE '%"+title+"%';");returnconvertTableDateFormat(articles);}
exports.showArticleByNum=async(article_num)=>{const sql ="SELECT * FROM BOARD WHERE BOARD_NO="+article_num+";";let article =awaitquery(sql);
article = article[0];
article =convertArticleDateFormat(article);return article;}
exports.insert=async(title, content, author)=>{// Query to insert multiple rowslet query =`INSERT INTO BOARD (TITLE, content, POST_DATE, UPDATE_DATE, AUTHOR) VALUES ?;`;const date_obj =newDate();let post_date = date_obj.getFullYear()+"-"+parseInt(date_obj.getMonth()+1)+"-"+ date_obj.getDate();const update_date = post_date;// Values to be insertedlet values =[[title, content, post_date, update_date, author]];// Executing the queryawait 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'
range_minrange_max값은 특정 페이지에서의 게시글 범위를 나타낸다. 예를들어 페이지당 10개의 게시물을 가지는 2번째 페이지라면 articles[10]~articles[19]의 범위를 나타내며 range_minrange_max는 각각 10과 19를 나타낸다.
나머지 변수들은 기본값 설정과 예외처리 작업.
🔽 boardDBController.js 🔽
functionconvertDateFormat(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;}functionconvertTableDateFormat(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 promisifyconst query = util.promisify(conn.query).bind(conn);
exports.showTable=async()=>{let table =awaitquery("SELECT * FROM BOARD ORDER BY BOARD_NO DESC;");
table =convertTableDateFormat(table);return table;}
dbMySQLModel.showTable();함수는 간단한 MySQL
쿼리문이다.
내림차순으로 받는 이유는 최신 게시글 일수록 나중에 저장되기 때문.
내부에 있는 convertTableDateFormat(table);함수는 MySQL에서 시간관련 컬럼을 뽑을시 형태가 글로벌하게 바뀌어 다시 원래대로 바꿔주는 작업이 필요했다.
내부에서 convertDateFormat(table[i].POST_DATE);를 호출하는데 실질적으로 형태를 바꿔주는 작업을 한다.