Categories
Game Development

SQL queries with varidic argument lists

It takes 11 lines of code to do this query in PostgreSQL with binary parameters, such as non-escaped strings or binary data. Here, I pass a parameter as a string, so have to escape it to prevent SQL injection attacks.

char *outTemp[3];
int outLengths[3];
int formats[3];
formats[0]=PQEXECPARAM_FORMAT_TEXT;
formats[1]=PQEXECPARAM_FORMAT_BINARY; // Always happens to be binary
formats[2]=PQEXECPARAM_FORMAT_BINARY; // Always happens to be binary
sprintf(query, “INSERT INTO FileVersionHistory(applicationID, filename, createFile, changeSetID, userName) VALUES (%i, $1::text,FALSE,%i,’%s’);”, applicationID, changeSetId, GetEscapedString(userName).C_String());
outTemp[0]=deletedFiles.fileList[fileListIndex].filename;
outLengths[0]=(int)strlen(deletedFiles.fileList[fileListIndex].filename);
formats[0]=PQEXECPARAM_FORMAT_TEXT;
result = PQexecParams(pgConn, query,1,0,outTemp,outLengths,formats,PQEXECPARAM_FORMAT_BINARY);

With my new function, ExecVaridic, it’s one line of code.

result = ExecVaridic(“INSERT INTO FileVersionHistory(applicationID, filename, createFile, changeSetID, userName) VALUES (%i, %s, FALSE,%i,%s);”, applicationID, deletedFiles.fileList[fileListIndex].filename, changeSetId, userName.C_String());

Why has no one thought of this before?

I support binary data too.

result = ExecVaridic(“INSERT INTO fileVersionHistory(binaryData) VALUES (%c)”, binaryData, binaryDataLength);

%c is my own extension, to mean binary data followed by length.

No string escaping necessary.

Here’s the code:
AutopatcherPostgreRepository.cpp

See PostgreSQLInterface::ExecVaridic

Categories
Game Development

Lobby 2 update

I’m continuing work on my rewrite of RakNet’s Lobby system. The reason I’m rewriting it is because the original architecture over-duplicates functionality, resulting in a tremendous amount of work.

For every function I had to:

1. Expose an interface
2. Serialize the user’s parameters
3. Validate the user’s parameters on the client
4. Do the send call
5. Parse the received packet on the server.
6. Check incoming parameters
7. Do C++ processing before the database call. For example, checking that a user is a member of a clan before doing a clan operation.
8. Pack the parameters into a functor
9. Process the functor (which itself required writing, and a unit test).
10. Parse the results from the functor
11. Do more C++, such as notifications, and update state memory
12. Send the result to the client
13. Process the result on the client, updating state memory
14. Return the result to the user.

It takes like 15 minutes of concentrated typing and copy/paste to do all this. State memory was potentially duplicated in the database, the server, and the client. I was going to add a GUI on top of it, which is yet another level of processing for each function.

The new design packs pretty much all 14 of those steps into one structure. The same structure:

Holds input and output parameters
Serializes and deserializes those parameters
Has as a member function the database procedure.
It itself a callback
Has a factory class that can create instances of itself

It uses defines to automatically add in functionality where appropriate.

All of the functionality will be in a single thread, spawned and separate from the main thread. Only rooms will be stored and processed in memory – clans, email, message boards, etc. are all done as stored procedures. This is to avoid the need to duplicate data both in memory and in the database, which I had to do with clans and in various other cases.

Furthermore, I’ve split the rooms functionality into its own module, which I can unit test without the headache of trying to test it in the context of a larger system. Unlike my old system, both the client and the server will use this same module to represent rooms. The only difference is the server has more information, meaning that I don’t have to write custom rooms code for the client.

I think all these changes will drop the code size by 75%. Speed will theoretically be slower, as I’m doing some stuff in the database that I could have done in C++. However, with so much less code, the compiler may be able to optimize it better.

For improvements, I’ve learned a lot working on Jeopardy’s lobby system. I’m going to improve on their system as follows:

1. Support a command to automatically create a room if joining fails. Rather than requiring the user to join OR create.
2. Store which room you just left, and don’t rejoin that room if another room can be joined instead.
3. Quick join means join a room as soon as doing so with other quick join members will fill that room based on your search filters.
4. Spectator support (old system had this too)
5. Operation result callbacks always specify which room / user they are referring to, so you can reject the message if it is no longer relevant.
7. Room invitations are emails, but are automatically cleared when the room no longer exists.
8. Room specific operations are automatically canceled when you shut down the system or leave the room (this is just common sense).
9. Support for text-based chat between room members. The client has the ability to filter profanity. The client also has a persistent mute list.
10. Server based ready states for room members (old system had this too). Less useful for peer to peer, but good for client/server.

I was also thinking of an enemies list – people you don’t play with. The problem is while you can stay out of rooms that have your enemies, it’s not fair to keep your enemies out of rooms you are already in. Otherwise, if one player was hated enough he may never be able to join rooms. If the enemies list was one-way, you’d wonder why your enemies could still join your room. So I dropped that idea.