From 83494c7fbd83fafa28c5a434a1c83f56c17e3d04 Mon Sep 17 00:00:00 2001
From: Benjamin Auder <benjamin.auder@somewhere>
Date: Thu, 16 Jan 2020 18:46:59 +0100
Subject: [PATCH] Implement cleaning methods (CRON tasks)

---
 server/bin/www             |  4 ++--
 server/db/create.sql       |  1 +
 server/models/Challenge.js | 40 +++++++++++++++++--------------
 server/models/Game.js      |  1 +
 server/models/User.js      | 48 +++++++++++++++++++++-----------------
 5 files changed, 52 insertions(+), 42 deletions(-)

diff --git a/server/bin/www b/server/bin/www
index 94f39e13..44751991 100755
--- a/server/bin/www
+++ b/server/bin/www
@@ -31,8 +31,8 @@ var ChallengeModel = require("../models/Challenge");
 var GameModel = require("../models/Game");
 cron.schedule('0 0 0 * * *', function() {
 	// Remove some old users, challenges and games every 24h
-	UserModel.cleanUsersDb(); //TODO: write this
-	ChallengeModel.removeOld(); //TODO: this too
+	UserModel.cleanUsersDb();
+	ChallengeModel.removeOld();
 	GameModel.cleanGamesDb();
 });
 
diff --git a/server/db/create.sql b/server/db/create.sql
index a8c8d687..9a8f785c 100644
--- a/server/db/create.sql
+++ b/server/db/create.sql
@@ -13,6 +13,7 @@ create table Users (
   loginToken varchar,
   loginTime datetime,
   sessionToken varchar,
+  created datetime,
   notify boolean
 );
 
diff --git a/server/models/Challenge.js b/server/models/Challenge.js
index ba805aa1..01de6b54 100644
--- a/server/models/Challenge.js
+++ b/server/models/Challenge.js
@@ -92,24 +92,28 @@ const ChallengeModel =
       });
     });
   },
-}
 
-// TODO: adapt
-// Remove challenges older than 1 month, and 1to1 older than 36h
-//exports.removeOld = function()
-//{
-//	var tsNow = new Date().getTime();
-//	// 86400000 = 24 hours in milliseconds
-//	var day = 86400000;
-//	db.challenges.find({}, (err,challengeArray) => {
-//		challengeArray.forEach( c => {
-//			if (c._id.getTimestamp() + 30*day < tsNow //automatch
-//				|| (!!c.to && c._id.getTimestamp() + 1.5*day < tsNow)) //1 to 1
-//			{
-//				db.challenges.remove({"_id": c._id});
-//			}
-//		});
-//	});
-//}
+  // Remove challenges older than 1 month, and 1to1 older than 2 days
+  removeOld: function()
+  {
+    const tsNow = Date.now();
+    // 86400000 = 24 hours in milliseconds
+    const day = 86400000;
+    db.serialize(function() {
+      const query =
+        "SELECT id, target " +
+        "FROM Challenges";
+      db.all(query, (err, challenges) => {
+        challenges.forEach(c => {
+          if ((!c.target && tsNow - c.added > 30*day) ||
+            (!!c.target && tsNow - c.added > 2*day))
+          {
+            db.run("DELETE FROM CHallenges WHERE id = " + c.id);
+          }
+        });
+      });
+    });
+  },
+}
 
 module.exports = ChallengeModel;
diff --git a/server/models/Game.js b/server/models/Game.js
index 3d2184b8..77bbd27a 100644
--- a/server/models/Game.js
+++ b/server/models/Game.js
@@ -8,6 +8,7 @@ var db = require("../utils/database");
  *   fen: varchar (current position)
  *   timeControl: string
  *   score: varchar (result)
+ *   created: datetime
  *
  * Structure table Players:
  *   gid: ref game id
diff --git a/server/models/User.js b/server/models/User.js
index c2e78837..ee4b0566 100644
--- a/server/models/User.js
+++ b/server/models/User.js
@@ -12,6 +12,7 @@ var sendEmail = require('../utils/mailer');
  *   loginTime: datetime (validity)
  *   sessionToken: token in cookies for authentication
  *   notify: boolean (send email notifications for corr games)
+ *   created: datetime
  */
 
 const UserModel =
@@ -40,8 +41,8 @@ const UserModel =
 		db.serialize(function() {
 			const insertQuery =
 				"INSERT INTO Users " +
-				"(name, email, notify) VALUES " +
-				"('" + name + "', '" + email + "', " + notify + ")";
+				"(name, email, notify, created) VALUES " +
+				"('" + name + "', '" + email + "', " + notify + "," + Date.now() + ")";
 			db.run(insertQuery, err => {
 				if (!!err)
 					return callback(err);
@@ -139,26 +140,29 @@ const UserModel =
         res.json(err || {});
       });
     });
-  }
-}
+  },
+
+  ////////////
+  // CLEANING
 
-// TODO: adapt
-//exports.cleanUsersDb = function()
-//{
-//	var tsNow = new Date().getTime();
-//	// 86400000 = 24 hours in milliseconds
-//	var day = 86400000;
-//
-//	db.users.find({}, (err,userArray) => {
-//		userArray.forEach( u => {
-//			if ((u.sessionTokens.length==0 &&
-//					u._id.getTimestamp().getTime() + day < tsNow) //unlogged
-//				|| u.updated + 365*day < tsNow) //inactive for one year
-//			{
-//				db.users.remove({"_id": u._id});
-//			}
-//		});
-//	});
-//}
+  cleanUsersDb: function()
+  {
+    const tsNow = Date.now();
+    // 86400000 = 24 hours in milliseconds
+    const day = 86400000;
+    db.serialize(function() {
+      const query =
+        "SELECT id, sessionToken, created " +
+        "FROM Users";
+      db.all(query, (err, users) => {
+        users.forEach(u => {
+          // Remove unlogged users for >1 day
+          if (!u.sessionToken && tsNow - u.created > day)
+            db.run("DELETE FROM Users WHERE id = " + u.id);
+        });
+      });
+    });
+  },
+}
 
 module.exports = UserModel;
-- 
2.44.0