aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--README.md2
-rw-r--r--postgres/init.sql2
-rw-r--r--postgres/schema.sql6
-rw-r--r--sqlite/init.sql2
-rw-r--r--sqlite/schema.sql14
-rw-r--r--src/database_postgres.cc68
-rw-r--r--src/database_sqlite.cc76
-rw-r--r--src/query.cc26
-rw-r--r--src/querymanager.hh10
9 files changed, 158 insertions, 48 deletions
diff --git a/README.md b/README.md
index 5c35257..a1b3d09 100644
--- a/README.md
+++ b/README.md
@@ -16,7 +16,7 @@ make clean # remove `build` directory
```
## Running (SQLite)
-The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data (see `sqlite/init.sql`), although you could put some insertions at the end to avoid having to manually run something like `sqlite/init.sql`. There are a few configuration options, and in particular `SQLite.*` options that could be adjusted in `config.cfg` but the defaults should work for most use cases.
+The query manager becomes the database, automatically initializing and maintaining the schema, based on the files in `sqlite/` (see `sqlite/README.txt`). The default schema file won't automatically insert any initial data (see `sqlite/init.sql`), although you could put some insertions at the end to avoid having to manually run something like `sqlite/init.sql`. There are a few configuration options, and in particular `SQLite.*` options that can be adjusted in `config.cfg` but the defaults should work for most use cases.
## Running (PostgreSQL)
The query manager becomes a relay to the actual database. And with PostgreSQL being a distributed database system, it makes no sense to have individual clients managing the schema, since there could be multiple, each with their own assumptions. For that reason there is a `SchemaInfo` table with a `VERSION` row that will be queried at startup and compared against `POSTGRESQL_SCHEMA_VERSION`, defined in `src/database_postgres.cc`, to make sure there is an agreement on the schema version. It is hardcoded because schema changes will usually result in query changes.
diff --git a/postgres/init.sql b/postgres/init.sql
index e3907bd..fbe40c7 100644
--- a/postgres/init.sql
+++ b/postgres/init.sql
@@ -112,6 +112,6 @@ INSERT INTO CharacterRights(CharacterID, Name)
('CLEANUP_FIELDS'),
('NO_STATISTICS')
) AS R (RightName)
- WHERE C.Name ILIKE 'Gamemaster%' COLLATE "default";
+ WHERE C.Name LIKE 'Gamemaster%' COLLATE "default";
COMMIT;
diff --git a/postgres/schema.sql b/postgres/schema.sql
index bbc670f..32afe2c 100644
--- a/postgres/schema.sql
+++ b/postgres/schema.sql
@@ -42,8 +42,10 @@ CREATE TABLE Worlds (
PremiumPlayerBuffer SMALLINT NOT NULL,
MaxNewbies SMALLINT NOT NULL,
PremiumNewbieBuffer SMALLINT NOT NULL,
- OnlineRecord SMALLINT NOT NULL DEFAULT 0,
- OnlineRecordTimestamp TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+ OnlinePeak SMALLINT NOT NULL DEFAULT 0,
+ OnlinePeakTimestamp TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+ LastStartup TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+ LastShutdown TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
PRIMARY KEY (WorldID),
UNIQUE (Name)
);
diff --git a/sqlite/init.sql b/sqlite/init.sql
index dc30adf..9d561fc 100644
--- a/sqlite/init.sql
+++ b/sqlite/init.sql
@@ -19,7 +19,7 @@ INSERT INTO Accounts (AccountID, Email, Auth)
INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex)
VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1);
-INSERT INTO CharacterRights (CharacterID, Right)
+INSERT INTO CharacterRights (CharacterID, Name)
VALUES
(1, 'NOTATION'),
(1, 'NAMELOCK'),
diff --git a/sqlite/schema.sql b/sqlite/schema.sql
index 106c780..ead3908 100644
--- a/sqlite/schema.sql
+++ b/sqlite/schema.sql
@@ -11,8 +11,10 @@ CREATE TABLE IF NOT EXISTS Worlds (
PremiumPlayerBuffer INTEGER NOT NULL,
MaxNewbies INTEGER NOT NULL,
PremiumNewbieBuffer INTEGER NOT NULL,
- OnlineRecord INTEGER NOT NULL DEFAULT 0,
- OnlineRecordTimestamp INTEGER NOT NULL DEFAULT 0,
+ OnlinePeak INTEGER NOT NULL DEFAULT 0,
+ OnlinePeakTimestamp INTEGER NOT NULL DEFAULT 0,
+ LastStartup INTEGER NOT NULL DEFAULT 0,
+ LastShutdown INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (WorldID),
UNIQUE (Name)
);
@@ -59,15 +61,15 @@ CREATE INDEX IF NOT EXISTS CharactersGuildIndex
ALTER TABLE Characters ADD GroupID INTEGER NOT NULL;
CREATE TABLE IF NOT EXISTS CharacterRights (
GroupID INTEGER NOT NULL,
- Right TEXT NOT NULL COLLATE NOCASE,
- PRIMARY KEY(GroupID, Right)
+ Name TEXT NOT NULL COLLATE NOCASE,
+ PRIMARY KEY(GroupID, Name)
);
*/
CREATE TABLE IF NOT EXISTS CharacterRights (
CharacterID INTEGER NOT NULL,
- Right TEXT NOT NULL COLLATE NOCASE,
- PRIMARY KEY(CharacterID, Right)
+ Name TEXT NOT NULL COLLATE NOCASE,
+ PRIMARY KEY(CharacterID, Name)
);
CREATE TABLE IF NOT EXISTS CharacterDeaths (
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
//==============================================================================
generated by cgit v1.2.3 (git 2.46.0) at 2026-07-14 22:26:46 +0000