74 lines
2.1 KiB
JavaScript
74 lines
2.1 KiB
JavaScript
const mysql = require("mysql");
|
|
|
|
const config = require("./config");
|
|
const util = require("../shared/util");
|
|
|
|
|
|
let connection = mysql.createConnection(config.database);
|
|
|
|
|
|
connection.config.queryFormat = util.mysqlQueryFormat;
|
|
|
|
exports.pageSize = config.guestbook.pageSize !== undefined
|
|
? config.guestbook.pageSize : 60;
|
|
|
|
exports.closeConnection = () => connection.end();
|
|
|
|
exports.getPosts = async (page = 1, page_size = exports.pageSize) => {
|
|
return new Promise((resolve, reject) => {
|
|
let query = `
|
|
SELECT
|
|
guestbook.post_id,
|
|
guestbook.created,
|
|
guestbook.name,
|
|
(CASE
|
|
WHEN guestbook.hide_website IS false
|
|
THEN guestbook.website
|
|
ELSE NULL
|
|
END) AS website,
|
|
guestbook.message,
|
|
guestbook_feedback.comment AS feedback,
|
|
guestbook_feedback.created AS feedback_created
|
|
FROM guestbook
|
|
LEFT JOIN guestbook_feedback ON guestbook.post_id = guestbook_feedback.post_id
|
|
ORDER BY guestbook.created DESC
|
|
LIMIT :page_size
|
|
OFFSET :page_offset;`;
|
|
|
|
connection.query(query, { page_size: page_size, page_offset: (page-1) * page_size },
|
|
(err, results) => {
|
|
if (err) reject(err);
|
|
resolve(results); });
|
|
});
|
|
};
|
|
|
|
exports.getPostsCount = async () => {
|
|
let promise = new Promise((resolve, reject) => {
|
|
connection.query("SELECT COUNT(`post_id`) AS total FROM `guestbook`;", (err, results, fields) => {
|
|
if (err) reject(err);
|
|
resolve(results[0].total); });
|
|
});
|
|
|
|
return promise.then(count => count);
|
|
};
|
|
|
|
exports.addPost = async (post) => {
|
|
return new Promise((resolve, reject) => {
|
|
let query = "INSERT INTO `guestbook` (`name`, `website`, `message`, `hide_website`) \
|
|
VALUES (:name, :website, :message, :hide_website);";
|
|
|
|
if (post.message === undefined || post.message === "")
|
|
return reject("empty message");
|
|
|
|
if (post.name === undefined || post.name === "")
|
|
post.name = "Anonymous";
|
|
|
|
post.message = post.message.trim();
|
|
post.message = post.message.replace(/(?:\r\n|\n\r|\r)/g, "\n");
|
|
post.message = post.message.replace(/(?:\n\n|\n \n)/g, "\n");
|
|
|
|
connection.query(query, post, (err, results, fields) => {
|
|
if (err) return reject(err);
|
|
return resolve(true); });
|
|
});
|
|
} |