chore: Move statistics related functions to it's own file

fix: Extract database constructor to a separate file to ensure we only create one knex instance/db
feat: Add cron for saving the stats to the database every hour
feat: Get cached stats from database (if a stat is not found in the db, generate it)

1 files changed, 40 insertions, 0 deletions

diff --git a/src/api/structures/Database.js b/src/api/structures/Database.js
new file mode 100644
index 0000000..3b256d3
--- /dev/null
+++ b/src/api/structures/Database.js
@@ -0,0 +1,40 @@
+const nodePath = require('path');
+const db = require('knex')({
+	client: process.env.DB_CLIENT,
+	connection: {
+		host: process.env.DB_HOST,
+		user: process.env.DB_USER,
+		password: process.env.DB_PASSWORD,
+		database: process.env.DB_DATABASE,
+		filename: nodePath.join(__dirname, '../../../database/database.sqlite')
+	},
+	postProcessResponse: result => {
+		/*
+			Fun fact: Depending on the database used by the user and given that I don't want
+			to force a specific database for everyone because of the nature of this project,
+			some things like different data types for booleans need to be considered like in
+			the implementation below where sqlite returns 1 and 0 instead of true and false.
+		*/
+		const booleanFields = ['enabled', 'enableDownload', 'isAdmin', 'nsfw'];
+
+		const processResponse = row => {
+			Object.keys(row).forEach(key => {
+				if (booleanFields.includes(key)) {
+					if (row[key] === 0) row[key] = false;
+					else if (row[key] === 1) row[key] = true;
+				}
+			});
+			return row;
+		};
+
+		if (Array.isArray(result)) return result.map(row => processResponse(row));
+		if (typeof result === 'object') return processResponse(result);
+		return result;
+	},
+	useNullAsDefault: process.env.DB_CLIENT === 'sqlite3',
+	userParams: {
+		lastMutationTime: null
+	}
+});
+
+module.exports = db;