import Logger from "@/app/www/system/Logger"; import { ESSAY_ENTRY_PATH } from "@/app/www/utilities/Path"; import { equalCipher, isSU } from "@/app/www/utilities/Utility"; import { readdir, unlink } from "fs/promises"; import { Connection, createConnection, createPool, Pool, SqlError, } from "mariadb"; import { join } from "path"; let pool: Pool; if (process.env.NODE_ENV === "production") { pool = createPool(process.env.DB as string); } Logger.info("Loaded DB"); await handleStatementUnit(async (db) => { await db.query(`CREATE TABLE IF NOT EXISTS tn_avatar ( Avatar_ID VARCHAR(20), Avatar_Cipher TEXT, Avatar_Name 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_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`); }); for (const fileName of await readdir(ESSAY_ENTRY_PATH)) { if (!(await isValidFile(fileName))) { await unlink(join(ESSAY_ENTRY_PATH, fileName)); Logger.info(`Wiped ${fileName}`); } } export async function isValidFile(fileName: string) { return getDB( async (db) => !!( await db.query( `SELECT File_Name FROM tn_file WHERE File_Name = ?`, [fileName], ) )[0], ); } export async function handleStatementUnit<T>( onHandle: (db: Connection) => Promise<T>, ) { return getDB<T>(async (db) => { try { await db.beginTransaction(); const handled = onHandle(db); await db.commit(); return handled; } catch (e) { await db.rollback(); throw e; } }); } export async function getDB<T>(onGetDB: (db: Connection) => Promise<T>) { let db; if (pool) { try { db = await pool.getConnection(); return onGetDB(db); } finally { await db?.release(); } } else { try { db = await createConnection(process.env.DB as string); return await onGetDB(db); } finally { await db?.end(); } } } export async function getAvatar(avatarID: string) { return getDB(async (db) => { const data = ( await db.query< { Avatar_ID: string; Avatar_Cipher: string; Avatar_Name: string; Level: number; Fax: string; Avatar_Intro: string; }[] >( `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"], }; } }); } export async function setLastDate(avatarID: string, lastDate: string) { return getDB( async (db) => await db.query( `UPDATE tn_avatar SET Last_Date = ? WHERE Avatar_ID = ?`, [lastDate, avatarID], ), ); } export async function doModifyAvatar( avatarID: string, avatarCipher: string, avatarCipherModified: string, avatarName: string, fax: string, avatarIntro: string, ) { return handleStatementUnit(async (db) => { const defaultAvatarCipher = ( await db.query<{ Avatar_Cipher: string }[]>( `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 ); }); } export async function doModifyAvatarIntro( avatarID: string, avatarIntro: string, ) { return getDB(async (db) => { return ( ( await db.query( `UPDATE tn_avatar SET Avatar_Intro = ? WHERE Avatar_ID = ?`, [avatarIntro, avatarID], ) ).affectedRows > 0 ); }); } export async function postAvatar( avatarID: string, avatarCipher: string, avatarName: string, fax: string, datetime: string, ) { return getDB(async (db) => { return ( ( await db.query( `INSERT INTO tn_avatar VALUES(?, ?, ?, ?, ?, ?, ?, ?)`, [avatarID, avatarCipher, avatarName, fax, 1, datetime, "", datetime], ) ).affectedRows > 0 ); }); } export async function getForum(db: Connection, forumID: string) { const data = ( await db.query< { Title: string; Title_1042: string; Level: number; }[] >( `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"], }; } } export async function getForums(forumGroup?: string) { return getDB(async (db) => { return ( forumGroup ? await db.query< { Forum_ID: string; Title: string; Title_1042: string; }[] >( `SELECT Forum_ID, Title, Title_1042 FROM tn_forum WHERE Forum_Group = ?`, [forumGroup], ) : await db.query< { Forum_ID: string; Title: string; Title_1042: string; }[] >( `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"], })); }); } export async function getMostEssays(forumID: string) { return getEssays(forumID, 1, 5); } export async function getBeforeEssay(forumID: string, datetime: string) { return getDB(async (db) => { const dataBefore = ( await db.query< { Essay_ID: string; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; }[] >( `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) WHERE Forum_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 getCommentCount(essayIDBefore)), hitCount: Number(await getHitCount(essayIDBefore)), }; } }); } export async function getLaterEssay(forumID: string, datetime: string) { return getDB(async (db) => { const dataLater = ( await db.query< { Essay_ID: string; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; }[] >( `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) WHERE Forum_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 getCommentCount(essayIDLater)), hitCount: Number(await getHitCount(essayIDLater)), }; } }); } export async function getLatestEssays() { return getDB(async (db) => { const data = await db.query< { Forum_ID: string; Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; Text: string; }[] >( `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, tn_essay.Text FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) ORDER BY tn_essay.Date DESC LIMIT 5`, ); return Promise.all( data.map(async (data) => { const forumID = data["Forum_ID"]; const forumTitles = await getForumTitles(forumID); if (!forumTitles) { throw new Error(forumID); } 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 getCommentCount(essayID)), hitCount: Number(await getHitCount(essayID)), }; }), ); }); } export async function getCommentCount(essayID: number) { return 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"]; } }); } export async function getHitCount(essayID: number) { return 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"]; } }); } export async function wipeFileNames(fileNames: string[]) { await handleStatementUnit(async (db) => { for await (const fileName of fileNames) { await db.query( `DELETE FROM tn_file WHERE File_Name = ?`, [fileName], ); } }); } export async function postFileNames( avatarID: string, essayID: number, fileNames: [string, string][], ) { return getDB(async (db) => { for await (const [fileName, postedFileName] of fileNames) { await db.query( `INSERT INTO tn_file VALUES(?, ?, ?, ?)`, [avatarID, fileName, essayID, postedFileName], ); } }); } export async function getFileNames(db: Connection, essayID: number) { return ( await db.query<{ File_Name: string }[]>( `SELECT File_Name FROM tn_file WHERE Essay_ID = ?`, [essayID], ) ).map((data) => data["File_Name"]); } export async function postEssay( db: Connection, forumID: string, avatarID: string, datetime: string, title: string, text: string, ) { const { insertId } = await db.query( `INSERT INTO tn_essay(Forum_ID, Avatar_ID, Date, Title, Text) VALUES(?, ?, ?, ?, ?)`, [forumID, avatarID, datetime, title, text], ); return Number(insertId); } export async function doModifyEssay( db: Connection, essayID: number, avatarID: string, title: string, text: string, level: number, ) { const { affectedRows } = isSU(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; } export async function wipeEssay( essayID: number, avatarID: string, level: number, ) { return getDB(async (db) => { const { affectedRows } = isSU(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; }); } export async function getForumTitles(forumID: string) { return getDB(async (db) => { const data = ( await db.query<{ Title: string; Title_1042: string }[]>( `SELECT Title, Title_1042 FROM tn_forum WHERE Forum_ID = ?`, [forumID], ) )[0]; if (data) { return { title: data["Title"], title1042: data["Title_1042"], }; } }); } export async function getEssay(essayID: number) { return getDB(async (db) => { const data = ( await db.query< { Forum_ID: string; Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; Text: string; }[] >( `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title, Text FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) WHERE Essay_ID = ?`, [essayID], ) )[0]; if (data) { const forumID = data["Forum_ID"]; const forumTitles = await getForumTitles(forumID); if (forumTitles) { const essayID = 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 getHitCount(essayID)), essayBefore: await getBeforeEssay(forumID, date), essayLater: await getLaterEssay(forumID, date), }; } } }); } export async function getEssayCount(forumID: string) { return getDB(async (db) => { return ( await db.query<{ Essay_Count: number }[]>( `SELECT COUNT(Essay_ID) AS Essay_Count FROM tn_essay WHERE Forum_ID = ?`, [forumID], ) )[0]?.["Essay_Count"]; }); } export async function getEssays( forumID: string, page: number, viewUnit: number, ) { return getDB(async (db) => { const data = await db.query< { Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; }[] >( `SELECT Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) WHERE Forum_ID = ? ORDER BY tn_essay.Date DESC LIMIT ?, ?`, [forumID, (page - 1) * viewUnit, viewUnit], ); return Promise.all( data.map(async (data) => { 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 getCommentCount(essayID)), hitCount: Number(await getHitCount(essayID)), }; }), ); }); } export async function wantEssay( wantInput: string, page: number, viewUnit: number, ) { return getDB(async (db) => { return Promise.all( ( await db.query< { Forum_ID: string; Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Title: string; }[] >( `SELECT Forum_ID, Essay_ID, tn_essay.Avatar_ID, Avatar_Name, tn_essay.Date, Title FROM tn_essay INNER JOIN tn_avatar USING(Avatar_ID) WHERE MATCH(Text) AGAINST(? IN BOOLEAN MODE) ORDER BY tn_essay.Date DESC LIMIT ?, ?`, [`${wantInput}*`, (page - 1) * viewUnit, viewUnit], ) ).map(async (data) => { 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 getCommentCount(essayID)), hitCount: Number(await getHitCount(essayID)), }; }), ); }); } export async function wantComment( wantInput: string, page: number, viewUnit: number, ) { return getDB(async (db) => ( await db.query< { Forum_ID: string; Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Text: string; }[] >( `SELECT Forum_ID, tn_comment.Essay_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, tn_comment.Text FROM tn_comment INNER JOIN tn_essay USING(Essay_ID) INNER JOIN tn_avatar USING(Avatar_ID) WHERE MATCH(tn_comment.Text) AGAINST(? IN BOOLEAN MODE) ORDER BY tn_comment.Date DESC LIMIT ?, ?`, [`${wantInput}*`, (page - 1) * viewUnit, viewUnit], ) ).map((data) => ({ forumID: data["Forum_ID"], essayID: Number(data["Essay_ID"]), avatarID: data["Avatar_ID"], avatarName: data["Avatar_Name"], date: data["Date"], text: data["Text"], })), ); } export async function postEssayHit( essayID: number, avatarIP: string, date: string, ) { return 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; } } }); } ``; export async function postComment( targetCommentID: number, essayID: number, avatarID: string, datetime: string, text: string, ) { return 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; }); } export async function doModifyComment( commentID: number, avatarID: string, text: string, level: number, ) { return getDB(async (db) => { const { affectedRows } = isSU(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; }); } export async function wipeComment( commentID: number, avatarID: string, level: number, ) { return getDB(async (db) => { const { affectedRows } = isSU(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; }); } export async function getComments(essayID: number) { return getDB(async (db) => { const data = await db.query< { Comment_ID: number; Target_Comment_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Text: string; }[] >( `SELECT Comment_ID, Target_Comment_ID, tn_comment.Avatar_ID, Avatar_Name, tn_comment.Date, Text FROM tn_comment INNER JOIN tn_avatar USING(Avatar_ID) WHERE Essay_ID = ? ORDER BY tn_comment.Date`, [essayID], ); return data.map((data) => ({ commentID: data["Comment_ID"], targetCommentID: data["Target_Comment_ID"], avatarID: data["Avatar_ID"], avatarName: data["Avatar_Name"], date: data["Date"], text: data["Text"], })); }); } export async function getLatestComments() { return getDB(async (db) => { const data = await db.query< { Comment_ID: number; Forum_ID: string; Essay_ID: number; Avatar_ID: string; Avatar_Name: string; Date: string; Text: string; }[] >( `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 INNER JOIN tn_essay USING(Essay_ID) INNER JOIN tn_avatar ON tn_comment.Avatar_ID = tn_avatar.Avatar_ID ORDER BY tn_comment.Date DESC LIMIT 5`, ); return data.map((data) => ({ 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"], })); }); } export async function getCommentary() { return getDB(async (db) => { const data = await db.query< { Commentary_ID: number; Avatar_Name: string; Date: string; Text: string; }[] >( `SELECT Commentary_ID, Avatar_Name, Date, Text FROM tn_commentary ORDER BY Date DESC`, ); return data.map((data) => ({ commentaryID: data["Commentary_ID"], avatarName: data["Avatar_Name"], date: data["Date"], text: data["Text"], })); }); } export async function postCommentary( avatarName: string, avatarCipher: string, avatarIP: string, datetime: string, text: string, ) { return 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; }); } export async function doModifyCommentary( commentaryID: number, avatarCipher: string, text: string, ) { return 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; }); } export async function wipeCommentary( commentaryID: number, avatarCipher: string, ) { return getDB(async (db) => { const { affectedRows } = await db.query( `DELETE FROM tn_commentary WHERE Commentary_ID = ? AND Avatar_Cipher = ?`, [commentaryID, avatarCipher], ); return affectedRows > 0; }); } export async function getHit(date: string, dateBefore: string) { return getDB(async (db) => { const data = 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( data.find(({ Date }: { Date: string }) => Date === dateBefore)?.[ "Count" ] ?? 0, ), Number( data.find(({ Date }: { Date: string }) => Date === date)?.["Count"] ?? 0, ), ]; }); } export async function postHit(avatarIP: string, date: string) { return 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; } }); } export async function postFileHit( fileName: string, avatarIP: string, date: string, ) { return 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; } } }); } export async function getPostedFileName(fileName: string) { return getDB( async (db) => ( await db.query<{ Posted_File_Name: string }[]>( `SELECT Posted_File_Name FROM tn_file WHERE File_Name = ?`, [fileName], ) )[0]?.["Posted_File_Name"], ); } export async function getFileHit(fileName: string) { return 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, ); }); } export async function getLatestLoggedInAvatars() { return getDB(async (db) => { return ( await db.query< { Avatar_ID: string; Avatar_Name: string; Last_Date: string }[] >( `SELECT Avatar_ID, Avatar_Name, Last_Date FROM tn_avatar ORDER BY Last_Date DESC LIMIT 5`, ) ).map(({ Avatar_ID, Avatar_Name, Last_Date }) => ({ avatarID: Avatar_ID, avatarName: Avatar_Name, date: Last_Date, })); }); } export async function getLatestEnrolledAvatars() { return getDB(async (db) => { return ( await db.query< { Avatar_ID: string; Avatar_Name: string; Date: string }[] >( `SELECT Avatar_ID, Avatar_Name, Date FROM tn_avatar ORDER BY Date DESC LIMIT 5`, ) ).map(({ Avatar_ID, Avatar_Name, Date }) => ({ avatarID: Avatar_ID, avatarName: Avatar_Name, date: Date, })); }); } export async function postAutoEssay( forumID: string, avatarID: string, title: string, text: string, datetime: string, ) { return 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 Number(insertId); }); } export async function getAutoEssays(forumID: string, avatarID: string) { return getDB(async (db) => { return ( await db.query< { Auto_Essay_ID: number; Title: string; Text: string; Date: string; }[] >( `SELECT Auto_Essay_ID, Title, Text, Date FROM tn_auto_essay WHERE Avatar_ID = ? AND Forum_ID = ?`, [avatarID, forumID], ) ).map(({ Auto_Essay_ID, Title, Text, Date }) => ({ autoEssayID: Auto_Essay_ID, title: Title, text: Text, date: Date, })); }); } export async function doModifyAutoEssay( autoEssayID: number, avatarID: string, title: string, text: string, datetime: string, ) { return 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; }); } export async function wipeAutoEssay(autoEssayID: number, avatarID: string) { return 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; }); }