#if DATABASE_SQLITE

#include "querymanager.hh"

#include "sqlite3.h"

#include <errno.h>

#include <dirent.h>

// NOTE(fusion): SQLite's application id, used to identify an existing database.

// It is currently being set to ASCII "TiDB" for "Tibia Database".

#define SQLITE_APPLICATION_ID 0x54694442

// NOTE(fusion): SQLite's user version, used to track the current schema version.

// It is hardcoded because schema changes will usually result in query changes.

#define SQLITE_USER_VERSION 1

struct TCachedStatement{

sqlite3_stmt *Stmt;

int LastUsed;

uint32 Hash;

};

struct TDatabase{

sqlite3 *Handle;

int MaxCachedStatements;

TCachedStatement *CachedStatements;

};

// Statement Cache

//==============================================================================

// IMPORTANT(fusion): Prepared statements that are not reset after use may keep

// transactions open in which case an older view to the database is held, making

// changes from other processes, including the sqlite shell, not visible. I have

// not found anything on the SQLite docs but here's a stack overflow question:

// `https://stackoverflow.com/questions/43949228`

struct AutoStmtReset{

private:

sqlite3_stmt *m_Stmt;

public:

AutoStmtReset(sqlite3_stmt *Stmt){

m_Stmt = Stmt;

}

~AutoStmtReset(void){

if(m_Stmt != NULL){

sqlite3_reset(m_Stmt);

m_Stmt = NULL;

}

}

};

static void EnsureStatementCache(TDatabase *Database){

ASSERT(Database != NULL);

if(Database->CachedStatements == NULL){

ASSERT(g_Config.SQLite.MaxCachedStatements > 0);

Database->MaxCachedStatements = g_Config.SQLite.MaxCachedStatements;

Database->CachedStatements = (TCachedStatement*)calloc(

Database->MaxCachedStatements, sizeof(TCachedStatement));

}

}

static void DeleteStatementCache(TDatabase *Database){

ASSERT(Database != NULL);

if(Database->CachedStatements != NULL){

ASSERT(Database->MaxCachedStatements > 0);

for(int i = 0; i < Database->MaxCachedStatements; i += 1){

TCachedStatement *Entry = &Database->CachedStatements[i];

if(Entry->Stmt != NULL){

sqlite3_finalize(Entry->Stmt);

Entry->Stmt = NULL;

}

Entry->LastUsed = 0;

Entry->Hash = 0;

}

free(Database->CachedStatements);

Database->MaxCachedStatements = 0;

Database->CachedStatements = NULL;

}

}

static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){

ASSERT(Database != NULL);

EnsureStatementCache(Database);

sqlite3_stmt *Stmt = NULL;

int LeastRecentlyUsed = 0;

int LeastRecentlyUsedTime = Database->CachedStatements[0].LastUsed;

uint32 Hash = HashString(Text);

for(int i = 0; i < Database->MaxCachedStatements; i += 1){

TCachedStatement *Entry = &Database->CachedStatements[i];

if(Entry->LastUsed < LeastRecentlyUsedTime){

LeastRecentlyUsed = i;

LeastRecentlyUsedTime = Entry->LastUsed;

}

if(Entry->Stmt != NULL && Entry->Hash == Hash){

const char *EntryText = sqlite3_sql(Entry->Stmt);

ASSERT(EntryText != NULL);

if(StringEq(EntryText, Text)){

Stmt = Entry->Stmt;

Entry->LastUsed = GetMonotonicUptime();

break;

}

}

}

if(Stmt == NULL){

if(sqlite3_prepare_v3(Database->Handle, Text, -1,

SQLITE_PREPARE_PERSISTENT, &Stmt, NULL) != SQLITE_OK){

LOG_ERR("Failed to prepare query: %s", sqlite3_errmsg(Database->Handle));

return NULL;

}

TCachedStatement *Entry = &Database->CachedStatements[LeastRecentlyUsed];

if(Entry->Stmt != NULL){

sqlite3_finalize(Entry->Stmt);

}

Entry->Stmt = Stmt;

Entry->LastUsed = GetMonotonicUptime();

Entry->Hash = Hash;

#if DEBUG_STATEMENT_CACHE

{

char Preview[30];

StringBufCopyEllipsis(Preview, Text);

LOG("New statement cached: \"%s\"", Preview);

}

#endif

}else{

if(sqlite3_stmt_busy(Stmt) != 0){

char Preview[30];

StringBufCopyEllipsis(Preview, Text);

LOG_WARN("Statement \"%s\" wasn't properly reset. Use the"

" `AutoStmtReset` wrapper or manually reset it after usage"

" to avoid it holding onto an older view of the database,"

" making changes from other processes not visible.",

Preview);

sqlite3_reset(Stmt);

}

sqlite3_clear_bindings(Stmt);

}

return Stmt;

}

// Database Management

//==============================================================================

// NOTE(fusion): From `https://www.sqlite.org/pragma.html`:

// "Some pragmas take effect during the SQL compilation stage, not the execution

// stage. This means if using the C-language sqlite3_prepare(), sqlite3_step(),

// sqlite3_finalize() API (or similar in a wrapper interface), the pragma may run

// during the sqlite3_prepare() call, not during the sqlite3_step() call as normal

// SQL statements do. Or the pragma might run during sqlite3_step() just like normal

// SQL statements. Whether or not the pragma runs during sqlite3_prepare() or

// sqlite3_step() depends on the pragma and on the specific release of SQLite."

//

// Depending on the pragma, queries will fail in the sqlite3_prepare() stage when

// using bound parameters. This means we need to assemble the entire query before

// hand with snprintf or other similar formatting functions. In particular, this

// rule apply for `application_id` and `user_version` which we modify.

static bool ExecFile(TDatabase *Database, const char *FileName){

FILE *File = fopen(FileName, "rb");

if(File == NULL){

LOG_ERR("Failed to open file \"%s\"", FileName);

return false;

}

fseek(File, 0, SEEK_END);

usize FileSize = (usize)ftell(File);

fseek(File, 0, SEEK_SET);

bool Result = true;

if(FileSize > 0){

char *Text = (char*)malloc(FileSize + 1);

Text[FileSize] = 0;

if(Result && fread(Text, 1, FileSize, File) != FileSize){

LOG_ERR("Failed to read \"%s\" (ferror: %d, feof: %d)",

FileName, ferror(File), feof(File));

Result = false;

}

if(Result && sqlite3_exec(Database->Handle, Text, NULL, NULL, NULL) != SQLITE_OK){

LOG_ERR("Failed to execute \"%s\": %s",

FileName, sqlite3_errmsg(Database->Handle));

Result = false;

}

free(Text);

}

fclose(File);

return Result;

}

static bool ExecInternal(TDatabase *Database, const char *Format, ...) ATTR_PRINTF(2, 3);

static bool ExecInternal(TDatabase *Database, const char *Format, ...){

va_list ap;

va_start(ap, Format);

char Text[1024];

int Written = vsnprintf(Text, sizeof(Text), Format, ap);

va_end(ap);

if(Written >= (int)sizeof(Text)){

LOG_ERR("Query is too long");

return false;

}

if(sqlite3_exec(Database->Handle, Text, NULL, NULL, NULL) != SQLITE_OK){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

return true;

}

static bool QueryInternal(TDatabase *Database, int *OutValue, const char *Format, ...) ATTR_PRINTF(3, 4);

static bool QueryInternal(TDatabase *Database, int *OutValue, const char *Format, ...){

va_list ap;

va_start(ap, Format);

char Text[1024];

int Written = vsnprintf(Text, sizeof(Text), Format, ap);

va_end(ap);

if(Written >= (int)sizeof(Text)){

LOG_ERR("Query is too long");

return false;

}

sqlite3_stmt *Stmt;

if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK){

LOG_ERR("Failed to prepare query \"%s\": %s",

Text, sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query \"%s\": %s",

Text, sqlite3_errmsg(Database->Handle));

sqlite3_finalize(Stmt);

return false;

}

if(OutValue != NULL){

if(ErrorCode == SQLITE_DONE || sqlite3_data_count(Stmt) == 0){

LOG_ERR("Query \"%s\" returned no data", Text);

sqlite3_finalize(Stmt);

return false;

}

*OutValue = sqlite3_column_int(Stmt, 0);

}

sqlite3_finalize(Stmt);

return true;

}

static bool InitDatabaseSchema(TDatabase *Database){

TransactionScope Tx("SchemaInit");

if(!Tx.Begin(Database)){

return false;

}

// IMPORTANT(fusion): The schema init script should set`application_id` and

// `user_version` appropriately.

if(!ExecFile(Database, "sqlite/schema.sql")){

LOG_ERR("Failed to execute \"sqlite/schema.sql\"");

return false;

}

return Tx.Commit();

}

static bool GetPatchTimestamp(TDatabase *Database, const char *FileName, int *Timestamp){

sqlite3_stmt *Stmt;

const char *Text = "SELECT Timestamp FROM Patches WHERE FileName = ?1";

if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK

|| sqlite3_bind_text(Stmt, 1, FileName, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to prepare query: %s", sqlite3_errmsg(Database->Handle));

sqlite3_finalize(Stmt);

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

sqlite3_finalize(Stmt);

return false;

}

*Timestamp = (ErrorCode == SQLITE_ROW ? sqlite3_column_int(Stmt, 0) : 0);

sqlite3_finalize(Stmt);

return true;

}

static bool InsertPatch(TDatabase *Database, const char *FileName){

sqlite3_stmt *Stmt = NULL;

const char *Text = "INSERT INTO Patches (FileName, Timestamp) VALUES (?1, UNIXEPOCH())";

if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK

|| sqlite3_bind_text(Stmt, 1, FileName, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to prepare query: %s", sqlite3_errmsg(Database->Handle));

sqlite3_finalize(Stmt);

return false;

}

if(sqlite3_step(Stmt) != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

sqlite3_finalize(Stmt);

return false;

}

sqlite3_finalize(Stmt);

return true;

}

static bool ApplyDatabasePatches(TDatabase *Database, const char *DirName){

LOG("Looking for patches at \"%s\"...", DirName);

TransactionScope Tx("SchemaPatches");

if(!Tx.Begin(Database)){

return false;

}

DIR *PatchDir = opendir(DirName);

if(PatchDir == NULL && errno == ENOENT){

LOG("Directory \"%s\" not found, skipping...", DirName);

return true;

}

if(PatchDir == NULL){

LOG_ERR("Failed to open directory \"%s\": (%d) %s",

DirName, errno, strerrordesc_np(errno));

return false;

}

bool Abort = false;

DynamicArray<struct dirent> PatchList;

while(struct dirent *DirEntry = readdir(PatchDir)){

if(DirEntry->d_type != DT_REG || !StringEndsWithCI(DirEntry->d_name, ".sql")){

continue;

}

int PatchTimestamp;

if(!GetPatchTimestamp(Database, DirEntry->d_name, &PatchTimestamp)){

Abort = true;

break;

}

if(PatchTimestamp > 0){

char DateString[256];

StringBufFormatTime(DateString, "%Y-%m-%d", PatchTimestamp);

LOG("\"%s\": patch already applied on %s", DirEntry->d_name, DateString);

}else{

PatchList.Push(*DirEntry);

}

}

if(!Abort && !PatchList.Empty()){

std::sort(PatchList.begin(), PatchList.end(),

[](const struct dirent &A, const struct dirent &B) -> bool {

return strcmp(A.d_name, B.d_name) < 0;

});

for(const struct dirent &DirEntry: PatchList){

LOG("\"%s\": applying patch...", DirEntry.d_name);

char FilePath[4096];

StringBufFormat(FilePath, "%s/%s", DirName, DirEntry.d_name);

if(!ExecFile(Database, FilePath) || !InsertPatch(Database, DirEntry.d_name)){

Abort = true;

break;

}

}

}

closedir(PatchDir);

return !Abort && Tx.Commit();

}

static bool CheckDatabaseSchema(TDatabase *Database){

int ApplicationID, NumObjects;

if(!QueryInternal(Database, &ApplicationID, "PRAGMA application_id")

|| !QueryInternal(Database, &NumObjects, "SELECT COUNT(*) FROM sqlite_master")){

return false;

}

// IMPORTANT(fusion): Only initialize the schema if the database has no

// application id and has no objects defined (tables, indexes, views, or

// triggers).

if(ApplicationID == 0 && NumObjects == 0){

if(!InitDatabaseSchema(Database)){

LOG_ERR("Failed to initialize database schema");

return false;

}

// NOTE(fusion): Refresh database information after initalizing schema.

if(!QueryInternal(Database, &ApplicationID, "PRAGMA application_id")

|| !QueryInternal(Database, &NumObjects, "SELECT COUNT(*) FROM sqlite_master")){

return false;

}

}

if(ApplicationID != SQLITE_APPLICATION_ID){

LOG_ERR("Application ID mismatch (expected %08X, got %08X)",

SQLITE_APPLICATION_ID, ApplicationID);

return false;

}

// IMPORTANT(fusion): We want to apply patches before checking user version,

// just in case some migration needs to take place.

if(!ApplyDatabasePatches(Database, "sqlite/patches")){

LOG_ERR("Failed to apply database patches");

return false;

}

int UserVersion;

if(!QueryInternal(Database, &UserVersion, "PRAGMA user_version")){

return false;

}

if(UserVersion != SQLITE_USER_VERSION){

LOG_ERR("User Version mismatch (expected %d, got %d)",

SQLITE_USER_VERSION, UserVersion);

return false;

}

return true;

}

void DatabaseClose(TDatabase *Database){

if(Database != NULL){

DeleteStatementCache(Database);

// NOTE(fusion): `sqlite3_close` can only fail if there are associated

// prepared statements, blob handles, or backup objects that have not

// been finalized. It should NEVER happen unless there is a BUG.

if(Database->Handle != NULL){

if(sqlite3_close(Database->Handle) != SQLITE_OK){

PANIC("Failed to close database: %s", sqlite3_errmsg(Database->Handle));

}

Database->Handle = NULL;

}

free(Database);

}

}

TDatabase *DatabaseOpen(void){

TDatabase *Database = (TDatabase*)calloc(1, sizeof(TDatabase));

int Flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX;

if(sqlite3_open_v2(g_Config.SQLite.File, &Database->Handle, Flags, NULL) != SQLITE_OK){

LOG_ERR("Failed to open database at \"%s\": %s\n",

g_Config.SQLite.File, sqlite3_errmsg(Database->Handle));

DatabaseClose(Database);

return NULL;

}

if(sqlite3_db_readonly(Database->Handle, NULL)){

LOG_ERR("Failed to open database file \"%s\" with WRITE PERMISSIONS."

" Make sure it has the appropriate permissions and is owned"

" by the same user running the query manager.",

g_Config.SQLite.File);

DatabaseClose(Database);

return NULL;

}

if(!CheckDatabaseSchema(Database)){

LOG_ERR("Failed to check database schema");

DatabaseClose(Database);

return NULL;

}

return Database;

}

bool DatabaseCheckpoint(TDatabase *Database){

// IMPORTANT(fusion): Since SQLite is a local database, we don't need to check

// whether the connection is still valid or needs reconnecting.

ASSERT(Database != NULL);

return true;

}

int DatabaseMaxConcurrency(void){

// IMPORTANT(fusion): Running queries from separate threads is possible with

// different database handles, but there is an inherent limit because access

// to the underlying database file must be synchronized by the operating system.

// Also, there can only be one writer, which may cause spurious `SQLITE_BUSY`

// errors to happen if the database wasn't available for reading/writing.

return 1;

}

// TransactionScope

//==============================================================================

TransactionScope::TransactionScope(const char *Context){

m_Context = (Context != NULL ? Context : "NOCONTEXT");

m_Database = NULL;

}

TransactionScope::~TransactionScope(void){

if(m_Database != NULL){

if(!ExecInternal(m_Database, "ROLLBACK")){

LOG_ERR("Failed to rollback transaction (%s)", m_Context);

}

m_Database = NULL;

}

}

bool TransactionScope::Begin(TDatabase *Database){

if(m_Database != NULL){

LOG_ERR("Transaction (%s) already running", m_Context);

return false;

}

if(!ExecInternal(Database, "BEGIN")){

LOG_ERR("Failed to begin transaction (%s)", m_Context);

return false;

}

m_Database = Database;

return true;

}

bool TransactionScope::Commit(void){

if(m_Database == NULL){

LOG_ERR("Transaction (%s) not running", m_Context);

return false;

}

if(!ExecInternal(m_Database, "COMMIT")){

LOG_ERR("Failed to commit transaction (%s)", m_Context);

return false;

}

m_Database = NULL;

return true;

}

// Primary Tables

//==============================================================================

bool GetWorldID(TDatabase *Database, const char *World, int *WorldID){

ASSERT(Database != NULL && World != NULL && WorldID != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT WorldID FROM Worlds WHERE Name = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_text(Stmt, 1, World, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to bind WorldName: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*WorldID = (ErrorCode == SQLITE_ROW ? sqlite3_column_int(Stmt, 0) : 0);

return true;

}

bool GetWorlds(TDatabase *Database, DynamicArray<TWorld> *Worlds){

ASSERT(Database != NULL && Worlds != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"WITH N (WorldID, NumPlayers) AS ("

"SELECT WorldID, COUNT(*) FROM OnlineCharacters GROUP BY WorldID"

")"

" SELECT W.Name, W.Type, COALESCE(N.NumPlayers, 0), W.MaxPlayers,"

" W.OnlinePeak, W.OnlinePeakTimestamp, W.LastStartup, W.LastShutdown"

" FROM Worlds AS W"

" LEFT JOIN N ON W.WorldID = N.WorldID");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

while(sqlite3_step(Stmt) == SQLITE_ROW){

TWorld World = {};

StringBufCopy(World.Name, (const char*)sqlite3_column_text(Stmt, 0));

World.Type = sqlite3_column_int(Stmt, 1);

World.NumPlayers = sqlite3_column_int(Stmt, 2);

World.MaxPlayers = sqlite3_column_int(Stmt, 3);

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);

}

if(sqlite3_errcode(Database->Handle) != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

return true;

}

bool GetWorldConfig(TDatabase *Database, int WorldID, TWorldConfig *WorldConfig){

ASSERT(Database != NULL && WorldConfig != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT WorldID, Type, RebootTime, Host, Port, MaxPlayers,"

" PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer"

" FROM Worlds WHERE WorldID = ?1");

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;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

memset(WorldConfig, 0, sizeof(TWorldConfig));

if(ErrorCode == SQLITE_ROW){

WorldConfig->WorldID = sqlite3_column_int(Stmt, 0);

WorldConfig->Type = sqlite3_column_int(Stmt, 1);

WorldConfig->RebootTime = sqlite3_column_int(Stmt, 2);

StringBufCopy(WorldConfig->HostName, (const char*)sqlite3_column_text(Stmt, 3));

WorldConfig->Port = sqlite3_column_int(Stmt, 4);

WorldConfig->MaxPlayers = sqlite3_column_int(Stmt, 5);

WorldConfig->PremiumPlayerBuffer = sqlite3_column_int(Stmt, 6);

WorldConfig->MaxNewbies = sqlite3_column_int(Stmt, 7);

WorldConfig->PremiumNewbieBuffer = sqlite3_column_int(Stmt, 8);

}

return true;

}

bool AccountExists(TDatabase *Database, int AccountID, const char *Email, bool *Exists){

ASSERT(Database != NULL && Email != NULL && Exists != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT 1 FROM Accounts WHERE AccountID = ?1 OR Email = ?2");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK

|| sqlite3_bind_text(Stmt, 2, Email, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*Exists = (ErrorCode == SQLITE_ROW);

return true;

}

bool AccountNumberExists(TDatabase *Database, int AccountID, bool *Exists){

ASSERT(Database != NULL && Exists != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT 1 FROM Accounts WHERE AccountID = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID)!= SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*Exists = (ErrorCode == SQLITE_ROW);

return true;

}

bool AccountEmailExists(TDatabase *Database, const char *Email, bool *Exists){

ASSERT(Database != NULL && Email != NULL && Exists != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT 1 FROM Accounts WHERE Email = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_text(Stmt, 1, Email, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to bind Email: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*Exists = (ErrorCode == SQLITE_ROW);

return true;

}

bool CreateAccount(TDatabase *Database, int AccountID, const char *Email, const uint8 *Auth, int AuthSize){

ASSERT(Database != NULL && Email != NULL

&& Auth != NULL && AuthSize > 0);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"INSERT INTO Accounts (AccountID, Email, Auth)"

" VALUES (?1, ?2, ?3)");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK

|| sqlite3_bind_text(Stmt, 2, Email, -1, NULL) != SQLITE_OK

|| sqlite3_bind_blob(Stmt, 3, Auth, AuthSize, NULL) != SQLITE_OK){

LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_DONE && ErrorCode != SQLITE_CONSTRAINT){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

// TODO(fusion): Maybe have a `ContraintError` output param?

return (ErrorCode == SQLITE_DONE);

}

bool GetAccountData(TDatabase *Database, int AccountID, TAccount *Account){

ASSERT(Database != NULL && Account != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT AccountID, Email, Auth,"

" MAX(PremiumEnd - UNIXEPOCH(), 0),"

" PendingPremiumDays, Deleted"

" FROM Accounts WHERE AccountID = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

memset(Account, 0, sizeof(TAccount));

if(ErrorCode == SQLITE_ROW){

Account->AccountID = sqlite3_column_int(Stmt, 0);

StringBufCopy(Account->Email, (const char*)sqlite3_column_text(Stmt, 1));

if(sqlite3_column_bytes(Stmt, 2) == sizeof(Account->Auth)){

memcpy(Account->Auth, sqlite3_column_blob(Stmt, 2), sizeof(Account->Auth));

}

Account->PremiumDays = RoundSecondsToDays(sqlite3_column_int(Stmt, 3));

Account->PendingPremiumDays = sqlite3_column_int(Stmt, 4);

Account->Deleted = (sqlite3_column_int(Stmt, 5) != 0);

}

return true;

}

bool GetAccountOnlineCharacters(TDatabase *Database, int AccountID, int *OnlineCharacters){

ASSERT(Database != NULL && OnlineCharacters != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT COUNT(*) FROM Characters"

" WHERE AccountID = ?1 AND IsOnline != 0");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

if(sqlite3_step(Stmt) != SQLITE_ROW){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*OnlineCharacters = sqlite3_column_int(Stmt, 0);

return true;

}

bool IsCharacterOnline(TDatabase *Database, int CharacterID, bool *Online){

ASSERT(Database != NULL && Online != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT IsOnline FROM Characters WHERE CharacterID = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){

LOG_ERR("Failed to bind CharacterID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*Online = (ErrorCode == SQLITE_ROW && sqlite3_column_int(Stmt, 0) != 0);

return true;

}

bool ActivatePendingPremiumDays(TDatabase *Database, int AccountID){

ASSERT(Database != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"UPDATE Accounts"

" SET PremiumEnd = MAX(PremiumEnd, UNIXEPOCH())"

" + PendingPremiumDays * 86400,"

" PendingPremiumDays = 0"

" WHERE AccountID = ?1 AND PendingPremiumDays > 0");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %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 GetCharacterEndpoints(TDatabase *Database, int AccountID, DynamicArray<TCharacterEndpoint> *Characters){

ASSERT(Database != NULL && Characters != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT C.Name, W.Name, W.Host, W.Port"

" FROM Characters AS C"

" INNER JOIN Worlds AS W ON W.WorldID = C.WorldID"

" WHERE C.AccountID = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

while(sqlite3_step(Stmt) == SQLITE_ROW){

TCharacterEndpoint Character = {};

StringBufCopy(Character.Name, (const char*)sqlite3_column_text(Stmt, 0));

StringBufCopy(Character.WorldName, (const char*)sqlite3_column_text(Stmt, 1));

StringBufCopy(Character.WorldHost, (const char*)sqlite3_column_text(Stmt, 2));

Character.WorldPort = sqlite3_column_int(Stmt, 3);

Characters->Push(Character);

}

if(sqlite3_errcode(Database->Handle) != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

return true;

}

bool GetCharacterSummaries(TDatabase *Database, int AccountID, DynamicArray<TCharacterSummary> *Characters){

ASSERT(Database != NULL && Characters != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT C.Name, W.Name, C.Level, C.Profession, C.IsOnline, C.Deleted"

" FROM Characters AS C"

" LEFT JOIN Worlds AS W ON W.WorldID = C.WorldID"

" WHERE C.AccountID = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){

LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(Database->Handle));

return false;

}

while(sqlite3_step(Stmt) == SQLITE_ROW){

TCharacterSummary Character = {};

StringBufCopy(Character.Name, (const char*)sqlite3_column_text(Stmt, 0));

StringBufCopy(Character.World, (const char*)sqlite3_column_text(Stmt, 1));

Character.Level = sqlite3_column_int(Stmt, 2);

StringBufCopy(Character.Profession, (const char*)sqlite3_column_text(Stmt, 3));

Character.Online = (sqlite3_column_int(Stmt, 4) != 0);

Character.Deleted = (sqlite3_column_int(Stmt, 5) != 0);

Characters->Push(Character);

}

if(sqlite3_errcode(Database->Handle) != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

return true;

}

bool CharacterNameExists(TDatabase *Database, const char *Name, bool *Exists){

ASSERT(Database != NULL && Exists != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT 1 FROM Characters WHERE Name = ?1");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_text(Stmt, 1, Name, -1, NULL) != SQLITE_OK){

LOG_ERR("Failed to bind Email: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

*Exists = (ErrorCode == SQLITE_ROW);

return true;

}

bool CreateCharacter(TDatabase *Database, int WorldID, int AccountID, const char *Name, int Sex){

ASSERT(Database != NULL && Name != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"INSERT INTO Characters (WorldID, AccountID, Name, Sex)"

" VALUES (?1, ?2, ?3, ?4)");

if(Stmt == NULL){

LOG_ERR("Failed to prepare query");

return false;

}

AutoStmtReset StmtReset(Stmt);

if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK

|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK

|| sqlite3_bind_text(Stmt, 3, Name, -1, NULL) != SQLITE_OK

|| sqlite3_bind_int(Stmt, 4, Sex) != SQLITE_OK){

LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(Database->Handle));

return false;

}

int ErrorCode = sqlite3_step(Stmt);

if(ErrorCode != SQLITE_DONE && ErrorCode != SQLITE_CONSTRAINT){

LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));

return false;

}

// TODO(fusion): Same as `CreateAccount`?

return (ErrorCode == SQLITE_DONE);

}

bool GetCharacterID(TDatabase *Database, int WorldID, const char *CharacterName, int *CharacterID){

ASSERT(Database != NULL && CharacterName != NULL && CharacterID != NULL);

sqlite3_stmt *Stmt = PrepareQuery(Database,

"SELECT CharacterID FROM Characters"

" WHERE WorldID = ?1 AND Name = ?2");

if(Stmt == NULL){