import { createPool, Pool, PoolConnection, SqlError } from "mariadb";
import { v4 } from "uuid";
import Configure from "src/system/Configure";
import { Comment } from "src/Comment";
import { Commentary } from "src/Commentry";
import { equalCipher, isTaehui } from "src/Utility";
import Logger from "src/system/Logger";
class DB {
pool: Pool;
constructor() {
this.pool = createPool({
host: Configure.db.remote,
user: Configure.db.avatar,
password: Configure.db.auth,
database: Configure.db.db,
});
}
async setBaseDB() {
await this.getDB(async (db) => {
await db.query(`CREATE TABLE IF NOT EXISTS tn_avatar (
Avatar_ID VARCHAR(20),
Avatar_Cipher TEXT,
Avatar_Name TEXT,
Avatar_IP TEXT,
Fax TEXT,
Level INTEGER,
Date DATETIME,
Avatar_Intro TEXT,
Last_Date DATETIME,
PRIMARY KEY (Avatar_ID)
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_totem (
Totem CHAR(36),
Avatar_ID VARCHAR(20),
Date DATETIME,
PRIMARY KEY (Totem),
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_forum (
Forum_ID VARCHAR(20),
Title TEXT,
Title_1042 TEXT,
Level INTEGER,
PRIMARY KEY (Forum_ID)
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_essay (
Essay_ID INTEGER AUTO_INCREMENT,
Forum_ID VARCHAR(20),
Avatar_ID VARCHAR(20),
Date DATETIME,
Title TEXT,
Text TEXT,
PRIMARY KEY (Essay_ID),
FOREIGN KEY (Forum_ID) REFERENCES tn_forum(Forum_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_auto_essay (
Auto_Essay_ID INTEGER AUTO_INCREMENT,
Forum_ID VARCHAR(20),
Avatar_ID VARCHAR(20),
Title TEXT,
Text TEXT,
Date DATETIME,
PRIMARY KEY (Auto_Essay_ID),
FOREIGN KEY (Forum_ID) REFERENCES tn_forum(Forum_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_essay_hit (
Essay_ID INTEGER,
Date DATE,
Avatar_IP VARCHAR(255),
PRIMARY KEY (Essay_ID, Date, Avatar_IP),
FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_file (
Avatar_ID VARCHAR(20),
File_Name VARCHAR(255),
Essay_ID INTEGER,
Posted_File_Name TEXT,
PRIMARY KEY (File_Name),
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_file_hit (
File_Name VARCHAR(255),
Date DATE,
Avatar_IP VARCHAR(255),
PRIMARY KEY (File_Name, Date, Avatar_IP),
FOREIGN KEY (File_Name) REFERENCES tn_file(File_Name) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_comment (
Comment_ID INTEGER AUTO_INCREMENT,
Target_Comment_ID INTEGER,
Essay_ID INTEGER,
Avatar_ID VARCHAR(20),
Date DATETIME,
Text TEXT,
PRIMARY KEY (Comment_ID),
FOREIGN KEY (Essay_ID) REFERENCES tn_essay(Essay_ID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Avatar_ID) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_commentary (
Commentary_ID INTEGER AUTO_INCREMENT,
Avatar_Name TEXT,
Avatar_Cipher TEXT,
Avatar_IP TEXT,
Date DATETIME,
Text TEXT,
PRIMARY KEY (Commentary_ID)
) ENGINE=InnoDB`);
await db.query(`CREATE TABLE IF NOT EXISTS tn_hit (
Date DATE,
Avatar_IP VARCHAR(255),
PRIMARY KEY (Avatar_IP, Date)
) ENGINE=InnoDB`);
});
Logger.info("Loaded DB");
}
async isValidFile(fileName: string) {
return this.getDB(
async (db) =>
!!(
await db.query(
`SELECT File_Name
FROM tn_file
WHERE File_Name = ?`,
[fileName],
)
)[0],
);
}
async ta<T>(
onHandle: (db: PoolConnection) => Promise<T>,
): Promise<T | undefined> {
return this.getDB<T | undefined>(async (db) => {
try {
await db.beginTransaction();
const handled = onHandle(db);
await db.commit();
return handled;
} catch (e) {
await db.rollback();
throw e;
}
});
}
async getDB<T>(onHandle: (db: PoolConnection) => Promise<T>) {
let db;
try {
db = await this.pool.getConnection();
return onHandle(db);
} finally {
await db?.release();
}
}
async getAvatarAsAvatarID(db: PoolConnection, avatarID: string) {
const data = (
await db.query(
`SELECT Avatar_ID, Avatar_Cipher, Avatar_Name, Level, Fax, Avatar_Intro
FROM tn_avatar
WHERE Avatar_ID = ?`,
[avatarID],
)
)[0];
if (data) {
return {
avatarID: data["Avatar_ID"],
avatarCipher: data["Avatar_Cipher"],
avatarName: data["Avatar_Name"],
level: data["Level"],
fax: data["Fax"],
avatarIntro: data["Avatar_Intro"],
};
}
}
async setLastDate(db: PoolConnection, avatarID: string, lastDate: string) {
return db.query(
`UPDATE tn_avatar
SET Last_Date = ?
WHERE Avatar_ID = ?`,
[lastDate, avatarID],
);
}
async getTotem(db: PoolConnection, avatarID: string) {
const data = (
await db.query(
`SELECT Totem, Date
FROM tn_totem
WHERE Avatar_ID = ?`,
[avatarID],
)
)[0];
if (data) {
return {
totem: data["Totem"],
date: data["Date"],
};
}
}
async setTotemDateAsAvatarID(
db: PoolConnection,
avatarID: string,
datetime: string,
): Promise<void> {
await db.query(
`UPDATE tn_totem
SET Date = ?
WHERE Avatar_ID = ?`,
[datetime, avatarID],
);
}
async putTotem(
db: PoolConnection,
avatarID: string,
datetime: string,
): Promise<string> {
const totem = v4();
await db.query(
`REPLACE INTO tn_totem
VALUES(?, ?, ?)`,
[totem, avatarID, datetime],
);
return totem;
}
async doModifyAvatar(
db: PoolConnection,
avatarID: string,
avatarCipher: string,
avatarCipherModified: string,
avatarName: string,
fax: string,
avatarIntro: string,
) {
const defaultAvatarCipher = (
await db.query(
`SELECT Avatar_Cipher
FROM tn_avatar
WHERE Avatar_ID = ?`,
[avatarID],
)
)[0]?.["Avatar_Cipher"];
return (
defaultAvatarCipher &&
(await equalCipher(defaultAvatarCipher, avatarCipher)) &&
(
await db.query(
`UPDATE tn_avatar
SET Avatar_Cipher = ?, Avatar_Name = ?, Fax = ?, Avatar_Intro = ?
WHERE Avatar_ID = ?`,
[
avatarCipherModified || defaultAvatarCipher,
avatarName,
fax,
avatarIntro,
avatarID,
],
)
).affectedRows > 0
);
}
async doModifyAvatarIntro(avatarID: string, avatarIntro: string) {
return this.getDB(async (db) => {
return (
(
await db.query(
`UPDATE tn_avatar
SET Avatar_Intro = ?
WHERE Avatar_ID = ?`,
[avatarIntro, avatarID],
)
).affectedRows > 0
);
});
}
async postAvatar(
avatarID: string,
avatarCipher: string,
avatarName: string,
avatarIP: string,
fax: string,
datetime: string,
) {
return this.getDB(async (db) => {
return (
(
await db.query(
`INSERT INTO tn_avatar
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)`,
[
avatarID,
avatarCipher,
avatarName,
avatarIP,
fax,
1,
datetime,
"",
datetime,
],
)
).affectedRows > 0
);
});
}
async getAvatarAsTotem(db: PoolConnection, totem: string) {
const data = (
await db.query(
`SELECT Avatar_ID, Date
FROM tn_totem
WHERE Totem = ?`,
[totem],
)
)[0];
if (data) {
return {
avatarID: data["Avatar_ID"],
date: data["Date"],
};
}
}
async setTotemDateAsTotem(
db: PoolConnection,
totem: string,
datetime: string,
) {
await db.query(
`UPDATE tn_totem
SET Date = ?
WHERE Totem = ?`,
[datetime, totem],
);
}
async wipeTotem(db: PoolConnection, avatarID: string) {
const { affectedRows } = await db.query(
`DELETE
FROM tn_totem
WHERE Avatar_ID = ?`,
[avatarID],
);
return affectedRows;
}
async getForum(
db: PoolConnection,
forumID: string,
): Promise<
| {
title: string;
title1042: string;
level: number;
}
| undefined
> {
const data = (
await db.query(
`SELECT Title, Title_1042, Level
FROM tn_forum
WHERE Forum_ID = ?`,
[forumID],
)
)[0];
if (data) {
return {
title: data["Title"],
title1042: data["Title_1042"],
level: data["Level"],
};
}
}
async getForums(forumGroup?: string): Promise<
{
forumID: string;
title: string;
title1042: string;
}[]
> {
return this.getDB(async (db) => {
return (
forumGroup
? await db.query(
`SELECT Forum_ID, Title, Title_1042
FROM tn_forum
WHERE Forum_Group = ?`,
[forumGroup],
)
: await db.query(
`SELECT Forum_ID, Title, Title_1042
FROM tn_forum`,
)
).map(
(data: { Forum_ID: string; Title: string; Title_1042: string }) => ({
forumID: data["Forum_ID"],
title: data["Title"],
title1042: data["Title_1042"],
}),
);
});
}
async getMostEssays(forumID: string) {
return this.getEssays(forumID, 1, 5);
}
async getBeforeEssay(forumID: string, datetime: string) {
return this.getDB(async (db) => {
const dataBefore = (
await db.query(
`SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
FROM tn_essay, tn_avatar
WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID AND tn_essay.Date < ?
ORDER BY tn_essay.Date DESC
LIMIT 1`,
[forumID, datetime],
)
)[0];
if (dataBefore) {
const essayIDBefore = Number(dataBefore["Essay_ID"]);
return {
essayID: essayIDBefore,
avatarID: dataBefore["Avatar_ID"],
avatarName: dataBefore["Avatar_Name"],
date: dataBefore["Date"],
title: dataBefore["Title"],
commentCount: Number(await this.getCommentCount(essayIDBefore)),
hitCount: Number(await this.getHitCount(essayIDBefore)),
};
}
});
}
async getLaterEssay(forumID: string, datetime: string) {
return this.getDB(async (db) => {
const dataLater = (
await db.query(
`SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
FROM tn_essay, tn_avatar
WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID AND tn_essay.Date > ?
ORDER BY tn_essay.Date
LIMIT 1`,
[forumID, datetime],
)
)[0];
if (dataLater) {
const essayIDLater = Number(dataLater["Essay_ID"]);
return {
essayID: essayIDLater,
avatarID: dataLater["Avatar_ID"],
avatarName: dataLater["Avatar_Name"],
date: dataLater["Date"],
title: dataLater["Title"],
commentCount: Number(await this.getCommentCount(essayIDLater)),
hitCount: Number(await this.getHitCount(essayIDLater)),
};
}
});
}
async getLatestEssays() {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, tn_essay.Text
FROM tn_essay, tn_avatar
WHERE tn_avatar.Avatar_ID = tn_essay.Avatar_ID
ORDER BY tn_essay.Date DESC
LIMIT 5`,
);
return Promise.all(
rs.map(
async (data: {
Forum_ID: string;
Essay_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Title: string;
Text: string;
}) => {
const forumID = data["Forum_ID"];
const forumTitles = await this.getForumTitles(forumID);
if (forumTitles) {
const essayID = Number(data["Essay_ID"]);
return {
forumID,
forumTitle: forumTitles.title,
forumTitle1042: forumTitles.title1042,
essayID,
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
title: data["Title"],
text: data["Text"],
commentCount: Number(await this.getCommentCount(essayID)),
hitCount: Number(await this.getHitCount(essayID)),
};
}
},
),
);
});
}
async getCommentCount(essayID: number) {
return this.getDB(async (db) => {
const data = (
await db.query(
`SELECT COUNT(Comment_ID) AS Comment_Count
FROM tn_comment
WHERE Essay_ID = ?`,
[essayID],
)
)[0];
if (data) {
return data["Comment_Count"];
}
});
}
async getHitCount(essayID: number) {
return this.getDB(async (db) => {
const data = (
await db.query(
`SELECT COUNT(Avatar_IP) AS Hit_Count
FROM tn_essay_hit
WHERE Essay_ID = ?`,
[essayID],
)
)[0];
if (data) {
return data["Hit_Count"];
}
});
}
async wipeFileNames(fileNames: string[]) {
await this.ta(async (db) => {
for await (const fileName of fileNames) {
await db.query(
`DELETE
FROM tn_file
WHERE File_Name = ?`,
[fileName],
);
}
});
}
async putFileNames(
avatarID: string,
essayID: number,
fileNames: [string, string][],
) {
return this.getDB(async (db) => {
for await (const [fileName, postedFileName] of fileNames) {
await db.query(
`INSERT INTO tn_file
VALUES(?, ?, ?, ?)`,
[avatarID, fileName, essayID, postedFileName],
);
}
});
}
async getFileNames(db: PoolConnection, essayID: number): Promise<string[]> {
return (
await db.query(
`SELECT File_Name
FROM tn_file
WHERE Essay_ID = ?`,
[essayID],
)
).map((data: { File_Name: string }) => data["File_Name"]);
}
async postEssay(
db: PoolConnection,
forumID: string,
avatarID: string,
datetime: string,
title: string,
text: string,
): Promise<number> {
const { insertId } = await db.query(
`INSERT INTO tn_essay(Forum_ID, Avatar_ID, Date, Title, Text)
VALUES(?, ?, ?, ?, ?)`,
[forumID, avatarID, datetime, title, text],
);
return insertId;
}
async doModifyEssay(
db: PoolConnection,
essayID: number,
avatarID: string,
title: string,
text: string,
level: number,
): Promise<boolean> {
const { affectedRows } = isTaehui(level)
? await db.query(
`UPDATE tn_essay
SET Title = ?, Text = ?
WHERE Essay_ID = ?`,
[title, text, essayID],
)
: await db.query(
`UPDATE tn_essay
SET Title = ?, Text = ?
WHERE Essay_ID = ? AND Avatar_ID = ?`,
[title, text, essayID, avatarID],
);
return affectedRows > 0;
}
async wipeEssay(
essayID: number,
avatarID: string,
level: number,
): Promise<boolean> {
return this.getDB(async (db) => {
const { affectedRows } = isTaehui(level)
? await db.query(
`DELETE
FROM tn_essay
WHERE Essay_ID = ?`,
[essayID],
)
: await db.query(
`DELETE
FROM tn_essay
WHERE Essay_ID = ? AND Avatar_ID = ?`,
[essayID, avatarID],
);
return affectedRows > 0;
});
}
async getForumTitles(forumID: string) {
return this.getDB(async (db) => {
const data = (
await db.query(
`SELECT Title, Title_1042
FROM tn_forum
WHERE Forum_ID = ?`,
[forumID],
)
)[0];
if (data) {
return {
title: data["Title"],
title1042: data["Title_1042"],
};
}
});
}
async getEssay(essayID: number) {
return this.getDB(async (db) => {
const data = (
await db.query(
`SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, Text
FROM tn_essay, tn_avatar
WHERE Essay_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID`,
[essayID],
)
)[0];
if (data) {
const forumID = data["Forum_ID"];
const forumTitles = await this.getForumTitles(forumID);
if (forumTitles) {
const essayID = Number(data["Essay_ID"]);
const date = data["Date"];
return {
forumTitle: forumTitles.title,
forumTitle1042: forumTitles.title1042,
essayID,
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date,
title: data["Title"],
text: data["Text"],
hitCount: Number(await this.getHitCount(essayID)),
essayBefore: await this.getBeforeEssay(forumID, date),
essayLater: await this.getLaterEssay(forumID, date),
};
}
}
});
}
async getEssayCount(forumID: string) {
return this.getDB(async (db) => {
const data = (
await db.query(
`SELECT COUNT(Essay_ID) AS Essay_Count
FROM tn_essay
WHERE Forum_ID = ?`,
[forumID],
)
)[0];
if (data) {
return data["Essay_Count"];
}
});
}
async getEssays(forumID: string, page: number, viewUnit: number) {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
FROM tn_essay, tn_avatar
WHERE Forum_ID = ? AND tn_avatar.Avatar_ID = tn_essay.Avatar_ID
ORDER BY tn_essay.Date DESC
LIMIT ?, ?`,
[forumID, (page - 1) * viewUnit, viewUnit],
);
return Promise.all(
rs.map(
async (data: {
Essay_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Title: string;
}) => {
const essayID = Number(data["Essay_ID"]);
return {
essayID: essayID,
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
title: data["Title"],
commentCount: Number(await this.getCommentCount(essayID)),
hitCount: Number(await this.getHitCount(essayID)),
};
},
),
);
});
}
async wantEssay(wantInput: string, page: number, viewUnit: number) {
return this.getDB(async (db) => {
return Promise.all(
(
await db.query(
`SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title
FROM tn_essay, tn_avatar
WHERE tn_avatar.Avatar_ID = tn_essay.Avatar_ID AND MATCH(Text) AGAINST(? IN BOOLEAN MODE)
ORDER BY tn_essay.Date DESC
LIMIT ?, ?`,
[wantInput, (page - 1) * viewUnit, viewUnit],
)
).map(
async (data: {
Forum_ID: string;
Essay_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Title: string;
}) => {
const essayID = Number(data["Essay_ID"]);
return {
forumID: data["Forum_ID"],
essayID: essayID,
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
title: data["Title"],
commentCount: Number(await this.getCommentCount(essayID)),
hitCount: Number(await this.getHitCount(essayID)),
};
},
),
);
});
}
async wantComment(wantInput: string, page: number, viewUnit: number) {
return this.getDB(async (db) => {
return Promise.all(
(
await db.query(
`SELECT Forum_ID, tn_comment.Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, tn_comment.Text
FROM tn_essay, tn_comment, tn_avatar
WHERE tn_essay.Essay_ID = tn_comment.Essay_ID AND tn_comment.Avatar_ID = tn_avatar.Avatar_ID AND MATCH(tn_comment.Text) AGAINST(? IN BOOLEAN MODE)
ORDER BY tn_comment.Date DESC
LIMIT ?, ?`,
[wantInput, (page - 1) * viewUnit, viewUnit],
)
).map(
async (data: {
Forum_ID: string;
Essay_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Text: string;
}) => {
return {
forumID: data["Forum_ID"],
essayID: Number(data["Essay_ID"]),
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
text: data["Text"],
};
},
),
);
});
}
async postEssayHit(essayID: number, avatarIP: string, date: string) {
return this.getDB(async (db) => {
try {
await db.query(
`INSERT INTO tn_essay_hit
VALUES(?, ?, ?)`,
[essayID, date, avatarIP],
);
} catch (e) {
if (!(e instanceof SqlError) || e.errno !== 1062) {
throw e;
}
}
});
}
async getEssayHit(essayID: number) {
return this.getDB(async (db) => {
return Number(
(
await db.query(
`SELECT COUNT(Avatar_IP) AS Count
FROM tn_essay_hit
WHERE Essay_ID = ?`,
[essayID],
)
)[0]?.Count ?? 0,
);
});
}
async postComment(
targetCommentID: number,
essayID: number,
avatarID: string,
datetime: string,
text: string,
): Promise<boolean> {
return this.getDB(async (db) => {
const { insertId } = await db.query(
`INSERT INTO tn_comment(Target_Comment_ID, Essay_ID, Avatar_ID, Date, Text)
VALUES(?, ?, ?, ?, ?)`,
[targetCommentID, essayID, avatarID, datetime, text],
);
return insertId > 0;
});
}
async doModifyComment(
commentID: number,
avatarID: string,
text: string,
level: number,
): Promise<boolean> {
return this.getDB(async (db) => {
const { affectedRows } = isTaehui(level)
? await db.query(
`UPDATE tn_comment
SET Text = ?
WHERE Comment_ID = ?`,
[text, commentID],
)
: await db.query(
`UPDATE tn_comment
SET Text = ?
WHERE Comment_ID = ? AND Avatar_ID = ?`,
[text, commentID, avatarID],
);
return affectedRows > 0;
});
}
async wipeComment(
commentID: number,
avatarID: string,
level: number,
): Promise<boolean> {
return this.getDB(async (db) => {
const { affectedRows } = isTaehui(level)
? await db.query(
`DELETE
FROM tn_comment
WHERE Comment_ID = ?`,
[commentID],
)
: await db.query(
`DELETE
FROM tn_comment
WHERE Comment_ID = ? AND Avatar_ID = ?`,
[commentID, avatarID],
);
return affectedRows > 0;
});
}
async getComments(essayID: number): Promise<Comment[]> {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT Comment_ID, Target_Comment_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, Text
FROM tn_comment, tn_avatar
WHERE Essay_ID = ? AND tn_avatar.Avatar_ID = tn_comment.Avatar_ID
ORDER BY tn_comment.Date`,
[essayID],
);
return rs.map(
(data: {
Comment_ID: number;
Target_Comment_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Text: string;
}) => ({
commentID: data["Comment_ID"],
targetCommentID: data["Target_Comment_ID"],
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
text: data["Text"],
}),
);
});
}
async getLatestComments() {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT Comment_ID, Forum_ID, tn_comment.Essay_ID AS Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, tn_comment.Text
FROM tn_comment, tn_essay, tn_avatar
WHERE tn_comment.Essay_ID = tn_essay.Essay_ID AND tn_avatar.Avatar_ID = tn_comment.Avatar_ID
ORDER BY tn_comment.Date DESC
LIMIT 5`,
);
return rs.map(
(data: {
Comment_ID: number;
Forum_ID: string;
Essay_ID: number;
Avatar_ID: string;
Avatar_Name: string;
Date: string;
Text: string;
}) => ({
commentID: data["Comment_ID"],
forumID: data["Forum_ID"],
essayID: data["Essay_ID"],
avatarID: data["Avatar_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
text: data["Text"],
}),
);
});
}
async getCommentary(): Promise<Commentary[]> {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT Commentary_ID, Avatar_Name, Date, Text
FROM tn_commentary
ORDER BY Date DESC`,
);
return rs.map(
(data: {
Commentary_ID: number;
Avatar_Name: string;
Date: string;
Text: string;
}) => ({
commentaryID: data["Commentary_ID"],
avatarName: data["Avatar_Name"],
date: data["Date"],
text: data["Text"],
}),
);
});
}
async postCommentary(
avatarName: string,
avatarCipher: string,
avatarIP: string,
datetime: string,
text: string,
) {
return this.getDB(async (db) => {
const { insertId } = await db.query(
`INSERT INTO tn_commentary(Avatar_Name, Avatar_Cipher, Avatar_IP, Date, Text)
VALUES(?, ?, ?, ?, ?)`,
[avatarName, avatarCipher, avatarIP, datetime, text],
);
return insertId > 0;
});
}
async doModifyCommentary(
commentaryID: number,
avatarCipher: string,
text: string,
): Promise<boolean> {
return this.getDB(async (db) => {
const { affectedRows } = await db.query(
`UPDATE tn_commentary
SET Text = ?
WHERE Commentary_ID = ? AND Avatar_Cipher = ?`,
[text, commentaryID, avatarCipher],
);
return affectedRows > 0;
});
}
async wipeCommentary(
commentaryID: number,
avatarCipher: string,
): Promise<boolean> {
return this.getDB(async (db) => {
const { affectedRows } = await db.query(
`DELETE
FROM tn_commentary
WHERE Commentary_ID = ? AND Avatar_Cipher = ?`,
[commentaryID, avatarCipher],
);
return affectedRows > 0;
});
}
async getHit(date: string, dateBefore: string) {
return this.getDB(async (db) => {
const rs = await db.query(
`SELECT DATE_FORMAT(Date, "%Y-%m-%d") AS Date, COUNT(Avatar_IP) AS Count
FROM tn_hit
GROUP BY Date
HAVING Date = ? OR DATE = ?`,
[dateBefore, date],
);
return [
Number(
rs.find(({ Date }: { Date: string }) => Date === dateBefore)?.Count ??
0,
),
Number(
rs.find(({ Date }: { Date: string }) => Date === date)?.Count ?? 0,
),
];
});
}
async postHit(avatarIP: string, date: string) {
return this.getDB(async (db) => {
try {
await db.query(
`INSERT INTO tn_hit
VALUES(?, ?)`,
[date, avatarIP],
);
return true;
} catch (e) {
if (!(e instanceof SqlError) || e.errno !== 1062) {
throw e;
}
return false;
}
});
}
async postFileHit(fileName: string, avatarIP: string, date: string) {
return this.getDB(async (db) => {
try {
await db.query(
`INSERT INTO tn_file_hit
VALUES(?, ?, ?)`,
[fileName, date, avatarIP],
);
} catch (e) {
if (!(e instanceof SqlError) || e.errno !== 1062) {
throw e;
}
}
});
}
async getPostedFileName(fileName: string) {
return this.getDB(
async (db) =>
(
await db.query(
`SELECT Posted_File_Name
FROM tn_file
WHERE File_Name = ?`,
[fileName],
)
)[0]?.["Posted_File_Name"],
);
}
async getFileHit(fileName: string) {
return this.getDB(async (db) => {
return Number(
(
await db.query(
`SELECT COUNT(Avatar_IP) AS Count
FROM tn_file_hit
WHERE File_Name = ?`,
[fileName],
)
)[0]?.Count ?? 0,
);
});
}
async getLatestAvatars() {
return this.getDB(async (db) => {
return Promise.all(
["Date", "Last_Date"].map(async (date) => {
return (
await db.query(
`SELECT Avatar_ID, Avatar_Name, ${date} AS Date
FROM tn_avatar
ORDER BY Date DESC
LIMIT 5`,
)
).map(
({
Avatar_ID,
Avatar_Name,
Date,
}: {
Avatar_ID: string;
Avatar_Name: string;
Date: string;
}) => ({
avatarID: Avatar_ID,
avatarName: Avatar_Name,
date: Date,
}),
);
}),
);
});
}
async postAutoEssay(
forumID: string,
avatarID: string,
title: string,
text: string,
datetime: string,
) {
return this.getDB(async (db) => {
const { insertId } = await db.query(
`INSERT INTO tn_auto_essay(Forum_ID, Avatar_ID, Title, Text, Date)
VALUES(?, ?, ?, ?, ?)`,
[forumID, avatarID, title, text, datetime],
);
return insertId;
});
}
async getAutoEssays(forumID: string, avatarID: string) {
return this.getDB(async (db) => {
const data = await db.query(
`SELECT Auto_Essay_ID, Title, Text, Date
FROM tn_auto_essay
WHERE Forum_ID = ? AND Avatar_ID = ?`,
[forumID, avatarID],
);
return data.map(
({
Auto_Essay_ID,
Title,
Text,
Date,
}: {
Auto_Essay_ID: number;
Title: string;
Text: string;
Date: string;
}) => ({
autoEssayID: Auto_Essay_ID,
title: Title,
text: Text,
date: Date,
}),
);
});
}
async doModifyAutoEssay(
autoEssayID: number,
avatarID: string,
title: string,
text: string,
datetime: string,
) {
return this.getDB(async (db) => {
const { affectedRows } = await db.query(
`UPDATE tn_auto_essay
SET Title = ?, Text = ?, Date = ?
WHERE Auto_Essay_ID = ? AND Avatar_ID = ?`,
[title, text, datetime, autoEssayID, avatarID],
);
return affectedRows > 0;
});
}
async wipeAutoEssay(autoEssayID: number, avatarID: string) {
return this.getDB(async (db) => {
const { affectedRows } = await db.query(
`DELETE
FROM tn_auto_essay
WHERE Auto_Essay_ID = ? AND Avatar_ID = ?`,
[autoEssayID, avatarID],
);
return affectedRows > 0;
});
}
}
export default new DB();
