Loading...   

[Show Table of Contents]


The database API change I'm proposing is an effort to separate database code from game logic and packet manipulation as well as to simplify and consolidate database access into logical constructs.

The new API should be usable as a true API, one that would be useful for more than just EQEmu the server itself, but emphasis will be placed on making the API useful for the server first, and others second.

 

The API works as a layered construct. At the bottom layer we have sets of interfaces for accessing a generic database like resource. this will be an abstraction of some of the methods in DBCore, mysql_request_result, and mysql_request_row

At the next layer up we have Interfaces which take the IDatabase concrete object and act as a facade for specific elements of the system. A Trap database access object. an Object database access object, etc etc etc. Each of these facade's has an internal storage of a IDatabase pointer and uses it to run queries against.

At the start we will be just creating the one IDatabase and facade concrete implementation which will be mostly just be a rearrangment of the current MySQL DB code.

Eventually we may add other facades and concrete implementations, each in their own namespace.

At the end I'll give examples of concrete IDatabase implementations we will definitely want.

Database::IDatabase

bool Connect(void)

The database interface is abstracted so Connect can't have the specifics of the connection information like host, user, password, database name, port, etc since we may (and should) build interfaces which do not have these properties. Concrete database implementations should implement a connection information setters.

ConnectionStatus GetStatus(void)

Renamed eStatus in dbcore, but it's the same thing still.

We should probably consider if we want other types of statuses here or if we want to go further and put error number, error message inforrmation and make ConnectionStatus a class. not a big deal at the moment.

Database::IQueryResult QueryDatabase(std::string query, bool retryOnFailureOnce = true) This is the abstracted form of the new DB Query style. IQueryResult should be a query result (similar to mysql_request_result) 
void Ping(void) as the name implies. ping the database. Some concrete implementations will probably just have an empty method here.

 

Various facades 'Getters', how many and the exact names are not fully decided yet. some potential examples:

 

Database::ISkills& GetSkills(void) const Anything having to do with accessing Skills.
Database::IAccount& GetAccount(void) const Anything having to do with accessing Accounts.
Database::ICharacter& GetCharacter(void) const Anything having to do with accessing Characters.
Database::IIP& GetIP(void) const Anything having to do with IP addresses.

In standard code you would probably never write a QueryDatabase call again. It should only be used within the rest of the Database API itself, and only within the facade objects. This also means you will not be working with MySQLRequestResult, or MySQLRequestRow objects, nor their abstracted forms outside of the database API either!

 

Database::IQueryResult

bool Success(void) const Did the request work or not.
std::string ErrorMessage(void) const Error message for the failure if we had a failure or the string "" if we did not have a problem
const char* CErrormessage(void) const Many of our functions end up going into non std::string aware methods, it's useful to have a char * version of ErrorMessage
uint32 ErrorNumber(void) const Error number if we had a problem. zero otherwise
uint32 RowsAffected(void) const rows affected from an insert query, zero default
uint32 RowCount(void) const rows in the results, 0 on failure or non select query
uint32 ColumnCount(void) const columns in the select query result, 0 on failure or non select query
uint32 LastInsertedID(void) const id of last insertion, 0 on error or non insertion query
uint32 LengthOfColumn(int columnIndex = 0) const size of a specific columns data
const std::string FieldName(int columnIndex) const database name of a specific column in a query
Database::IQueryRow begin() stl compatible (means it can be used with algorithm, range based for, etc) iterator for the re
Database::IQueryRow end() stl compatible (means it can be used with algorithm, range based for, etc) iterator for the re
Database::IQueryResult& operator=(IQueryResult&& other) move operator so we have nice semantics and performance
Database::IQueryResult(IQueryResult&& moveItem) move constructor so we have nice semantics and performance

IQueryResults give you IQueryRow's which are the work horse of the database system returning the actual database values, it an abstracted version of the current MySQLRequestRow.

 

Database::IQueryRow

Database::IQueryRow& operator=(Database::IQueryRow& moveItem) move and assignment, you probably won't use this.
Database::IQueryRow& operator++() allows for row++ (don't do this though! it's bad practice! use ++row)
Database::IQueryRow operator++(int) allows for ++row (why does it have the 'int' there? one of those weird quirks of c++ is why)

Database::IQueryRow operator*()

needed for range based for.
bool operator==(const Database::IQueryRow& rhs) equality checking
bool operator!=(const Database::IQueryRow& rhs) not equal checking. used in row != results.end() checking and stl algorithms
char* operator[](int index)

Gets the actual data elements. 

We may add UIn8_At(int index) and Int_32(int index), etc etc etc indexers if it proves to be useful.

 

ยงFacades

 

 

Database::ISkills

bool Usable(int skill_ID, int class_ID) const

Is this skill usable.

We may want the last value to be an enum for classes rather than a class_id integer.

Roughly equivalent to:

bool SkillUsable(SharedDatabase *db, int skill_id, int class_id)

in client_files/export/main.cpp

int Cap(int skill_ID, int class_ID, int level) const

Cap for this skill. 0 for not available or if an error occured.

We may want the last value to be an enum for classes rather than a class_id integer.

Roughly qquivalent to:

bool GetSkill(SharedDatabase *db, int skill_id, int class_id, level)

in client_files/export/main.cpp

 

 

Database::IAccount
uint32 CheckLogin(const std::string& accountName, const std::string& password, int16* const status) const

Check if there is an account with name "name" and password "password".

Gives the account id or 0 if no account matches of if there is a database error.

Roughly equivalent to:

uint32 Database::CheckLogin(const char* name, const char* password, int16* oStatus)

in common/database.cpp

int16 GetStatus(uint32 account_ID) const

status for this account id.

0 if no such account ID or an error.

-1 if suspended.

otherwise the status

Roughly equivalent to:

int16 Database::CheckStatus(uint32 account_id)

in common/database.cpp

bool SetStatus(const std::string& accountName, int16 status) const

Sets status for the account.

Roughly equivalent to:

bool Database::SetAccountStatus(const char* name, int16 status)

in common/database.cpp

uint32 CreateAccount(const std::string& accountName, const std::string& password, const int16 status) const

Creates an account with the given name and password with the given status.

0 for failure.

empty password is acceptable.

Roughly equivalent to:

uint32 Database::CreateAccount(const char* name, const char* password, int16 status, uint32 lsaccount_id)

in common/database.cpp

bool DeleteAccount(const std::string& accountName) const

Deletes an account with the given name.

Roughly equivalent to:

uint32 Database::DeleteAccount(const char* name) 

in common/database.cpp

bool SetLocalPassword(uint32 account_ID, const std::string& password) const

Sets local password for the given account ID

Roughly equivalent to:

bool Database::SetAccountStatus(const char* name, int16 status)

in common/database.cpp

uint32 GetAccountID(const std::string& characterName) const

Get account ID matching character name or 0 on failure.

Roughly equivalent to:

uint32 Database::GetAccountIDByChar(const char* charname, uint32* oCharID)

in common/database.cpp

uint32 GetAccountID(uint32 character_ID) const

Get account ID matching character name or 0 on failure.

Roughly equivalent to:

uint32 Database::GetAccountIDByChar(uint32 char_id)

in common/database.cpp

 

 

Database::ICharacter
bool ReserveName(uint32 account_ID, const std::string& characterName) const

This initially creates the character during character create, reserving this name for the character on the given account.

Roughly equivalent to:

bool Database::ReserveName(uint32 account_id, char* name)

in common/database.cpp

uint32 GetCharacterID(const std::string& characterName) const

Character ID for this character name or 0 if failure.

Roughly equivalent to:

uint32 Database::GetCharacterID(const char *name)

in common/database.cpp

bool DeleteCharacter(const std::string characterName) const

Removes character matching the character name. true on success, false on failure.

Roughly equivalent to:

bool Database::DeleteCharacter(char *name)

in common/database.cpp

bool SetPlayerProfile(uint32 account_ID, uint32 character_ID, const PlayerProfile_Struct& playerProfile) const

Saves the player profile to the character_ID belonging on this account_ID.
*note the order of account_ID and character_ID, these will always be in this order if they are both required.

Roughly equivalent to:

bool Database::SaveCharacterCreate(uint32 character_id, uint32 account_id, PlayerProfile_Struct* pp)

in common/database.cpp

bool SetCharacter(uint32 account_ID, uint32 character_ID, const PlayerProfile_Struct& playerProfile, Inventory& inventory) const

Saves both the player profile and the inventory to the character_ID belonging to account_ID

Use both:

ICharacter::SetPlayerProfile and IInventory::SetInventory internally. do not repeat both of these functions again.

Roughly equivalent to:

bool Database::StoreCharacter(uint32 account_id, PlayerProfile_Struct* pp, Inventory* inv) 

in common/database.cpp

 

 

Database::IIP
bool IsAcceptableIP(const std::string& ip) const

returns false in all cases *except* when the IP address is acceptable (ie, false on errors, on inclusion on banned list, etc)

Roughly equivalent to inverse of :

bool Database::CheckBannedIPs(const char* loginIP)

in common/database.cpp

bool SetBannedIP(const std::string& bannedIP, const std::string& notes) const

true if we managed to add the banned IP address, false otherwise

Roughly equivalent to:

bool Database::AddBannedIP(char* bannedIP, const char* notes)

in common/database.cpp

bool IsGMIP(const std::string& ipAddress, uint32 account_ID) const

resturns true if the IP matches the account_ID and is set to be a GM.

Roughly equivalent to:

bool Database::CheckGMIPs(const char* ip_address, uint32 account_id)

in common/database.cpp

bool SetGMIP(const std::string& ipAddress, const std::string& accountName) const

Roughly equivalent to:

bool Database::AddGMIP(char* ip_address, char* name)

in common/database.cpp

 

 


Ok, so these are the abstract interfaces for accessing the database, but this is what we are currently using in a concrete form, the real fun comes from the facades and how the facades and alternate concrete implementations can interact.

here is my current vision:

Database::MySQL Namespace containing concrete implementation of the above interfaces and the relative facades. Basically the de-obfusticated implementaion we have now.
Database::Logging Namespace containing Logging implementation of the above interfaces. This implementation takes a Database::IDatabase and forwards all calls onto that internal object. It logs errors when we get failures or even on success depending on the logging settings.

This will separate the logging code from the Database Query code completely, further it allows us to set logging levels from run time and allows us to get rid of a bunch of compiler flags. and optional compiler code.
Database::Performance Namespace containing concrete implementation of Performance interface which is like Logging in that it takes some other concrete implementation and forwards all calls onto the internal database implementation, but it will set up high performance timers for each query. This can be logged or be accessed from the Performance object. This could be used for performance tuning queries or as a part of a suite of tests to ensure performance is maintained.
Database::WebSocket Namespace containing a database implementation which, like Performance and Logging, send all queries, errors, etc on to a web socket in order to be displayed, filtered, etc etc
Database::[insert new db here] Want a SQLite db? go for it. write the basic SQLite concrete implementations of the above interfaces, the same for the facades, and you get performance, testing, logging, and websockets for free.

Want to convert your old MySQL database to an SQLite database? write a utility which takes two IDatabase concrete implementations and will transfer from one to the other using the approriate facades. each new alternate database concrete implementation allows for any of the old database to be converted from the old to the new implementation and visa-versa.