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