User:Danda

Thoughts on databases and the like

5 COH servers currently use SQL Server for their database. Those servers are:

• DbServer
• ChatServer
• AccountServer
• AuthServer
• AuctionServer

Other important topics I'm going to touch on are

• sqlconn

sqlconn and sqltask

sqlconn is under Common\sql. It's used by all servers except AuthServer.

It's a global manager for database connections. The servers call sqlConnInit with the number of connections they want to allocate (up to SQLCONN_MAX of 65) and database handles are allocated and stored in an array of size SQLCONN_MAX in a struct called sqlGlobal. sqlConnDatabaseConnect is called after this which just iterates through the array of database handles, disconnects and connects them.

From then on, you just exec SQL related stuff through other sqlconn functions, passing SqlConn (typedef of an int) which just gets the database connection from the global struct using the variable as the index for the array.

Concurrency:

AccountServer and DbServer are the only servers who use multiple database connections. Chat and Auction only use 1 (or 2, have to check).

This doesn't matter because the number of queues running sql transactions is the same as the number of connections - ASQL_WORKERS for AccountServer, NUM_SQL_WORKERS for DbServer -- both 65. So they all have their own db connection (I think).

Problems:

I'm not certain about what happens if the database closes a connection. Does the server try to open a new one? Connections are all initiated when the server starts and kept idle forever

Interesting functionality (mostly error printing, moving data retrieved from query into buffer, converting commands to utf8 before exec) is all tied into the global state.

Limit of 65 database connections/65 sql task queues (doubt this is really a problem, I don't know how worked either DbServer or AccountServer are, but I don't think they're hit hard)

A server can't connect to different databases as the connections are all initialised once with the same setup

What could be interesting to do in the long run

Getting rid of the global connections and having servers open a database connection only when they need one, using the SQLENV handle setup when the server starts plus a connection string. The ODBC driver would handle connection pooling and caching on the client side to prevent actually initialising a connection to the database every time. This is just what I think would be good though, and the current code would all have to be changed to close connections when they're done with their query (releasing them to the pool).

Problems you might have when porting databases

Calling Stored Procedures

The way the code calls stored procedures currently is with the syntax {CALL procedure_name(?,?,?)}. This is the correct way and follows the standard.

PostgreSQL only recently gained stored procedures officially (in 11) and https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us tells how the way CALL is able to be used doesn't follow the standards.

PostgreSQL can call stored procedures instead with syntax CALL procedure_name(?,?,?); (no curly braces)

Some calls to stored procedures specify the param name with the value e.g. CALL procedure_name(@param=?, @param2=?); which uses the SQL Server syntax. PostgreSQL uses CALL procedure_name(param := ?, param2 := ?);. Either separate the calls based on the DB you're calling or don't specify the param names and go by argument orders (CALL procedure_name(?, ?, ?);)

Other

Queries are checked in FULL_DEBUG. the string must start and finish with { and }, or finish with ;.

AccountServer uses some MERGE INTO. SQL Server only (I think) so write replacements.

AccountServer creates temporary tables for its MERGE INTOs. The syntax for that is CREATE TABLE #table_name where the # signifies the table as temporary in SQL Server. Change that based on the syntax for temporary tables of your DB (e.g. PostgreSQL is CREATE TEMPORARY TABLE table_name)

Columns: tinyint is used in a few places and has no corresponding type on PostgreSQL. I changed it to use smallint (short).

Strings -- I'm not really knowledgeable enough. Right now I'm storing everything as is given in a UTF8 db but I'm not doing any conversions or anything.