world startup/shutdown timestamps + adjust a few column names

4 files changed, 143 insertions, 37 deletions

diff --git a/src/database_postgres.cc b/src/database_postgres.cc
index 80076c9..241637a 100644
--- a/src/database_postgres.cc
+++ b/src/database_postgres.cc
@@ -1325,7 +1325,7 @@ bool GetWorlds(TDatabase *Database, DynamicArray<TWorld> *Worlds){
 				"SELECT WorldID, COUNT(*) FROM OnlineCharacters GROUP BY WorldID"
 			")"
 			" SELECT W.Name, W.Type, COALESCE(N.NumPlayers, 0), W.MaxPlayers,"
-				" W.OnlineRecord, W.OnlineRecordTimestamp"
+				" W.OnlinePeak, W.OnlinePeakTimestamp, W.LastStartup, W.LastShutdown"
 			" FROM Worlds AS W"
 			" LEFT JOIN N ON W.WorldID = N.WorldID");
 	if(Stmt == NULL){
@@ -1347,8 +1347,10 @@ bool GetWorlds(TDatabase *Database, DynamicArray<TWorld> *Worlds){
 		World.Type = GetResultInt(Result, Row, 1);
 		World.NumPlayers = GetResultInt(Result, Row, 2);
 		World.MaxPlayers = GetResultInt(Result, Row, 3);
-		World.OnlineRecord = GetResultInt(Result, Row, 4);
-		World.OnlineRecordTimestamp = GetResultTimestamp(Result, Row, 5);
+		World.OnlinePeak = GetResultInt(Result, Row, 4);
+		World.OnlinePeakTimestamp = GetResultTimestamp(Result, Row, 5);
+		World.LastStartup = GetResultTimestamp(Result, Row, 6);
+		World.LastShutdown = GetResultTimestamp(Result, Row, 7);
 		Worlds->Push(World);
 	}
 
@@ -3299,12 +3301,12 @@ bool InsertOnlineCharacters(TDatabase *Database, int WorldID,
 	return true;
 }
 
-bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool *NewRecord){
-	ASSERT(Database != NULL && NewRecord != NULL);
+bool CheckOnlinePeak(TDatabase *Database, int WorldID, int NumCharacters, bool *NewPeak){
+	ASSERT(Database != NULL && NewPeak != NULL);
 	const char *Stmt = PrepareQuery(Database,
-			"UPDATE Worlds SET OnlineRecord = $2::INTEGER,"
-				" OnlineRecordTimestamp = CURRENT_TIMESTAMP"
-			" WHERE WorldID = $1::INTEGER AND OnlineRecord < $2::INTEGER");
+			"UPDATE Worlds SET OnlinePeak = $2::INTEGER,"
+				" OnlinePeakTimestamp = CURRENT_TIMESTAMP"
+			" WHERE WorldID = $1::INTEGER AND OnlinePeak < $2::INTEGER");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -3322,7 +3324,55 @@ bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool
 		return false;
 	}
 
-	*NewRecord = (ResultAffectedRows(Result) > 0);
+	*NewPeak = (ResultAffectedRows(Result) > 0);
+	return true;
+}
+
+bool CheckWorldStartupTime(TDatabase *Database, int WorldID){
+	ASSERT(Database != NULL);
+	const char *Stmt = PrepareQuery(Database,
+			"UPDATE Worlds SET LastStartup = CURRENT_TIMESTAMP"
+			" WHERE WorldID = $1::INTEGER AND LastStartup <= LastShutdown");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	ParamBuffer Params = {};
+	ParamBegin(&Params, 1, 1);
+	ParamInt(&Params, WorldID);
+	PGresult *Result = PQexecPrepared(Database->Handle, Stmt, Params.NumParams,
+							Params.Values, Params.Lengths, Params.Formats, 1);
+	AutoResultClear ResultGuard(Result);
+	if(PQresultStatus(Result) != PGRES_COMMAND_OK){
+		LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle));
+		return false;
+	}
+
+	return true;
+}
+
+bool CheckWorldShutdownTime(TDatabase *Database, int WorldID){
+	ASSERT(Database != NULL);
+	const char *Stmt = PrepareQuery(Database,
+			"UPDATE Worlds SET LastShutdown = CURRENT_TIMESTAMP"
+			" WHERE WorldID = $1::INTEGER AND LastShutdown <= LastStartup");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	ParamBuffer Params = {};
+	ParamBegin(&Params, 1, 1);
+	ParamInt(&Params, WorldID);
+	PGresult *Result = PQexecPrepared(Database->Handle, Stmt, Params.NumParams,
+							Params.Values, Params.Lengths, Params.Formats, 1);
+	AutoResultClear ResultGuard(Result);
+	if(PQresultStatus(Result) != PGRES_COMMAND_OK){
+		LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle));
+		return false;
+	}
+
 	return true;
 }
 
diff --git a/src/database_sqlite.cc b/src/database_sqlite.cc
index 5b3bbc0..c2afa83 100644
--- a/src/database_sqlite.cc
+++ b/src/database_sqlite.cc
@@ -473,7 +473,7 @@ bool GetWorlds(TDatabase *Database, DynamicArray<TWorld> *Worlds){
 				"SELECT WorldID, COUNT(*) FROM OnlineCharacters GROUP BY WorldID"
 			")"
 			" SELECT W.Name, W.Type, COALESCE(N.NumPlayers, 0), W.MaxPlayers,"
-				" W.OnlineRecord, W.OnlineRecordTimestamp"
+				" W.OnlinePeak, W.OnlinePeakTimestamp, W.LastStartup, W.LastShutdown"
 			" FROM Worlds AS W"
 			" LEFT JOIN N ON W.WorldID = N.WorldID");
 	if(Stmt == NULL){
@@ -488,8 +488,10 @@ bool GetWorlds(TDatabase *Database, DynamicArray<TWorld> *Worlds){
 		World.Type = sqlite3_column_int(Stmt, 1);
 		World.NumPlayers = sqlite3_column_int(Stmt, 2);
 		World.MaxPlayers = sqlite3_column_int(Stmt, 3);
-		World.OnlineRecord = sqlite3_column_int(Stmt, 4);
-		World.OnlineRecordTimestamp = sqlite3_column_int(Stmt, 5);
+		World.OnlinePeak = sqlite3_column_int(Stmt, 4);
+		World.OnlinePeakTimestamp = sqlite3_column_int(Stmt, 5);
+		World.LastStartup = sqlite3_column_int(Stmt, 6);
+		World.LastShutdown = sqlite3_column_int(Stmt, 7);
 		Worlds->Push(World);
 	}
 
@@ -964,8 +966,8 @@ bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharac
 			" LEFT JOIN Accounts AS A ON A.AccountID = C.AccountID"
 			" LEFT JOIN CharacterRights AS R"
 				" ON R.CharacterID = C.CharacterID"
-				" AND R.Right = 'NO_STATISTICS'"
-			" WHERE C.Name = ?1 AND R.Right IS NULL");
+				" AND R.Name = 'NO_STATISTICS'"
+			" WHERE C.Name = ?1 AND R.Name IS NULL");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -1007,7 +1009,7 @@ bool GetCharacterRight(TDatabase *Database, int CharacterID, const char *Right,
 	ASSERT(Database != NULL && Right != NULL && HasRight != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
 			"SELECT 1 FROM CharacterRights"
-			" WHERE CharacterID = ?1 AND Right = ?2");
+			" WHERE CharacterID = ?1 AND Name = ?2");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -1033,7 +1035,7 @@ bool GetCharacterRight(TDatabase *Database, int CharacterID, const char *Right,
 bool GetCharacterRights(TDatabase *Database, int CharacterID, DynamicArray<TCharacterRight> *Rights){
 	ASSERT(Database != NULL && Rights != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
-			"SELECT Right FROM CharacterRights WHERE CharacterID = ?1");
+			"SELECT Name FROM CharacterRights WHERE CharacterID = ?1");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -2464,12 +2466,12 @@ bool InsertOnlineCharacters(TDatabase *Database, int WorldID,
 	return true;
 }
 
-bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool *NewRecord){
-	ASSERT(Database != NULL && NewRecord != NULL);
+bool CheckOnlinePeak(TDatabase *Database, int WorldID, int NumCharacters, bool *NewPeak){
+	ASSERT(Database != NULL && NewPeak != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
-			"UPDATE Worlds SET OnlineRecord = ?2,"
-				" OnlineRecordTimestamp = UNIXEPOCH()"
-			" WHERE WorldID = ?1 AND OnlineRecord < ?2");
+			"UPDATE Worlds SET OnlinePeak = ?2,"
+				" OnlinePeakTimestamp = UNIXEPOCH()"
+			" WHERE WorldID = ?1 AND OnlinePeak < ?2");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -2487,7 +2489,55 @@ bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool
 		return false;
 	}
 
-	*NewRecord = (sqlite3_changes(Database->Handle) > 0);
+	*NewPeak = (sqlite3_changes(Database->Handle) > 0);
+	return true;
+}
+
+bool CheckWorldStartupTime(TDatabase *Database, int WorldID){
+	ASSERT(Database != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(Database,
+			"UPDATE Worlds SET LastStartup = UNIXEPOCH()"
+			" WHERE WorldID = ?1 AND LastStartup <= LastShutdown");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	AutoStmtReset StmtReset(Stmt);
+	if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
+		LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
+	return true;
+}
+
+bool CheckWorldShutdownTime(TDatabase *Database, int WorldID){
+	ASSERT(Database != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(Database,
+			"UPDATE Worlds SET LastShutdown = UNIXEPOCH()"
+			" WHERE WorldID = ?1 AND LastShutdown <= LastStartup");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	AutoStmtReset StmtReset(Stmt);
+	if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
+		LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
+	if(sqlite3_step(Stmt) != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
 	return true;
 }
 
diff --git a/src/query.cc b/src/query.cc
index afcf664..52bda66 100644
--- a/src/query.cc
+++ b/src/query.cc
@@ -29,7 +29,7 @@ static int g_NumWorkers;
 static TWorker *g_Workers;
 static TQueryQueue *g_QueryQueue;
 
-// Query Name
+// Query Queue and Workers
 //==============================================================================
 const char *QueryName(int QueryType){
 	const char *Name = "";
@@ -81,8 +81,6 @@ const char *QueryName(int QueryType){
 	return Name;
 }
 
-// Query Queue and Workers
-//==============================================================================
 TQuery *QueryNew(void){
 	TQuery *Query = (TQuery*)calloc(1, sizeof(TQuery));
 	AtomicStore(&Query->RefCount, 1);
@@ -1251,11 +1249,7 @@ void ProcessCreatePlayerlist(TDatabase *Database, TQuery *Query){
 	QUERY_STOP_IF(!Tx.Begin(Database));
 	QUERY_STOP_IF(!DeleteOnlineCharacters(Database, Query->WorldID));
 
-	// TODO(fusion): I think `NumCharacters` may be used to signal that the
-	// server is going OFFLINE, in which case we'd have to add an `Online`
-	// column to `Worlds` and update it here.
-
-	bool NewRecord = false;
+	bool NewPeak = false;
 	int NumCharacters = (int)Request.Read16();
 	if(NumCharacters != 0xFFFF && NumCharacters > 0){
 		TOnlineCharacter *Characters = (TOnlineCharacter*)alloca(NumCharacters * sizeof(TOnlineCharacter));
@@ -1266,13 +1260,19 @@ void ProcessCreatePlayerlist(TDatabase *Database, TQuery *Query){
 		}
 
 		QUERY_STOP_IF(!InsertOnlineCharacters(Database, Query->WorldID, NumCharacters, Characters));
-		QUERY_STOP_IF(!CheckOnlineRecord(Database, Query->WorldID, NumCharacters, &NewRecord));
+		QUERY_STOP_IF(!CheckOnlinePeak(Database, Query->WorldID, NumCharacters, &NewPeak));
+	}
+
+	if(NumCharacters == 0xFFFF){
+		QUERY_STOP_IF(!CheckWorldShutdownTime(Database, Query->WorldID));
+	}else{
+		QUERY_STOP_IF(!CheckWorldStartupTime(Database, Query->WorldID));
 	}
 
 	QUERY_STOP_IF(!Tx.Commit());
 
 	TWriteBuffer *Response = QueryBeginResponse(Query, QUERY_STATUS_OK);
-	Response->WriteFlag(NewRecord);
+	Response->WriteFlag(NewPeak);
 	QueryFinishResponse(Query);
 }
 
@@ -1509,8 +1509,10 @@ void ProcessGetWorlds(TDatabase *Database, TQuery *Query){
 		Response->Write8((uint8)Worlds[i].Type);
 		Response->Write16((uint16)Worlds[i].NumPlayers);
 		Response->Write16((uint16)Worlds[i].MaxPlayers);
-		Response->Write16((uint16)Worlds[i].OnlineRecord);
-		Response->Write32((uint32)Worlds[i].OnlineRecordTimestamp);
+		Response->Write16((uint16)Worlds[i].OnlinePeak);
+		Response->Write32((uint32)Worlds[i].OnlinePeakTimestamp);
+		Response->Write32((uint32)Worlds[i].LastStartup);
+		Response->Write32((uint32)Worlds[i].LastShutdown);
 	}
 	QueryFinishResponse(Query);
 }
diff --git a/src/querymanager.hh b/src/querymanager.hh
index e33bcc0..99cea94 100644
--- a/src/querymanager.hh
+++ b/src/querymanager.hh
@@ -825,8 +825,10 @@ struct TWorld{
 	int Type;
 	int NumPlayers;
 	int MaxPlayers;
-	int OnlineRecord;
-	int OnlineRecordTimestamp;
+	int OnlinePeak;
+	int OnlinePeakTimestamp;
+	int LastStartup;
+	int LastShutdown;
 };
 
 struct TWorldConfig{
@@ -1083,7 +1085,9 @@ bool GetOnlineCharacters(TDatabase *Database, int WorldID, DynamicArray<TOnlineC
 bool DeleteOnlineCharacters(TDatabase *Database, int WorldID);
 bool InsertOnlineCharacters(TDatabase *Database, int WorldID,
 		int NumCharacters, TOnlineCharacter *Characters);
-bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool *NewRecord);
+bool CheckOnlinePeak(TDatabase *Database, int WorldID, int NumCharacters, bool *NewPeak);
+bool CheckWorldStartupTime(TDatabase *Database, int WorldID);
+bool CheckWorldShutdownTime(TDatabase *Database, int WorldID);
 
 // query.cc
 //==============================================================================