SQL in QuakeC

[TimeServ]

I probably shouldn't even be asking this considering what engine I submit code to (FTEQW), but how sensible is it to have SQL in QuakeC? I saw some discussion on here recently and considering my attempt at it I figured I'd open this topic.

For example, SQLite is stated as being a replacement for fopen. So for QuakeC's case that would mean a substitute for FRIK_FILE and string operations. The problems I see with FRIK_FILE/string ops is that a parser is needed for input files (made awkward by QuakeC's string manipulation or lack of) and non-asynchronous behavior (file read/write stalls in the middle of game play for instance).

The main use that came to my mind when I did my experimental SQL extension was logins/stats. Some mods already kind of do this and I suspect there's a few that were thinking of integrating their mod with some sort of web interface. Might want to wait discussing that whole thing about storing passwords/hashes in databases for now though.. that's another can of worms.

I'm making some really big assumptions here.. that people will know SQL or can learn SQL and people actually use current file access for things that are complex. I'd like to hear thoughts/flames though as it has been a while since I've heavily engaged in Quake and I've always been disconnected with the average QuakeC coder.

[frag.machine]

Look at TF2 and you'll see how Valve extensively uses their database backend: achievements, player statistics, persistent inventories, etc. Even some sort of cheat evidence collection can be imagined having SQL support to QuakeC.

<paranoid>Also, from the security point of view is better to use a sandboxed persistence system under the form of a SQL backend than granting file I/O access to QuakeC. </paranoid>

[GiffE]

I actually had the same wish. I was told however that the threaded nature of DP would have an issue with an SQL implementation. Not that I know why.

Databases would make stat tracking any any sort of persistent data much easier!

[Error]

thought someone added sql to quake once... was it DrLabman?

[Spike]

I was told however that the threaded nature of DP would have an issue with an SQL implementation.

I think you mean the non-threaded nature of QC in general.
If its not threaded, any sort of access will freeze the server for the duration, including any disk access. And that will induce stalls.
I did experiment with threading a while back, and it really could work to use it for sql, but tempstrings will likely still remain somewhat hacky.

[TimeServ]

I'm sure that other people have tried implementing SQL in QuakeC so if they have I want to hear how they did it or tried. I couldn't find any other info about actual implementations though just demands for it within the engine. Also tried looking through QMB and the best link I could find was stuff about doing Java game code and maybe through that you'd access JDBC? Maybe?

I don't think QuakeC needs to be threaded to handle an SQL extension. My implementation had a query thread handle linked lists and builtins would push queries to these lists and every server frame the result list would be polled and appropriate QuakeC callbacks would be ran. So really, only the actual query processing would be the problem. But during game play, I can't imagine too many situations where you would want to handle large amounts of rows without pagination or having the data preloaded beforehand.

My design assumes an engine can do threads though, which is not a good assumption. SQLite's async stuff might be sufficient in doing the same thing though. I haven't looked at this yet.

I guess I need to look through my code/builtins and write up something about it.

[Arkage]

I added sql to darkplaces to track kills and other stats, for each kill I call a built in and add it to the in memory db and I havent noticed any stalls at all.

[frag.machine]

As the name says, SQLite is... light. Of course, one trying to build complex queries using half a dozen or more tables and outer joins will suffer a massive with in performance; however, usually an online game will basically *feed* one or two tables with inserts, and the cost of such sequential inserts is negligible, if any. Let the onus of complex queries to an offline, dedicated app and everything will be ok.

[Spirit]

I do not want to believe that. SQLite is very fast, it just requires a bit of thinking (which I have not done for my site yet...), especially for INSERTs.

[frag.machine]

The actual problem with SQL support isn't in the SQL engine you embed per se, but the eventual inefficient queries submitted. As long we are not talking about complex tables with lots of indexes and constraints, insert operations are quite light.

[Chip]

The actual problem with SQL support isn't in the SQL engine you embed per se, but the eventual inefficient queries submitted. As long we are not talking about complex tables with lots of indexes and constraints, insert operations are quite light.

There are many CMS's out there that are doing great with only INSERT, UPDATE, SELECT and DELETE. Why would a Quake server/game need more?

For multiplayer:

Provided the database is updated once the game has ended, we only have a while() loop for all players, to update the current database stats.

For singleplayer:

One INSERT per game action, such as picking up an item or increasing/decreasing health would be enough. With some smart delay and/or updating the database upon ending the level, this would be a child's play.

EDIT: I don't know how to implement SQL into Quake, but I've been working with SQL databases for a long time now.

[leileilol]

Turn Quake into a forum?

As in.........

ARGH! soldier/sight1.wav

Turn to monster_army
impulse 4
+attack

where you sap up precious traffic by playing quake via spamming.



..... on second thought, maybe the versa. Dynamically log game actions and stats as a forum itself using SQL stuff. Ingame player messages become posts, and obituaries become these kinds of 'in-between' posts you find some forums use for crawler bots.

[GiffE]

Actually would be kind of cool if you could get a "Live" feed of player chat and frags.
The lag would be unbearable though.
I would use sql for game over stat saving.

[TimeServ]

Alright I (badly) wrote up a quasi-specification for the prototype FTE_SQL as it is. The original target database was MySQL and this influenced things like the asynchronous behavior and the partial query result thing. I can write up example code uses if necessary.

Also to add to the query stall thing, note that with this design you don't stall QuakeC execution with complex queries, there's just more latency on the callback and the cost or stall with the actual QuakeC query processing. So, it's actually more beneficial with this design to write more complex queries to reduce the amount of QuakeC logic or computation needed.

Cvars:
sv_sql_driver - Database driver (ex. mysql, postgresql, sqlite3)
sv_sql_host - Database host address (for sqlite3 this would be filename)
sv_sql_username - User name for connection
sv_sql_password - Password for connection
sv_sql_defaultdb - Default database for connection

Builtins:
#250 - float([string host], [string user], [string pass], [string defaultdb], [string driver]) sqlconnect
- Connect to a database. Omitted parameters use cvar values listed above. Returns server index.
#251 - void(float serveridx) sqldisconnect
- Disconnect database connection. Note that servers are disconnected automatically on map cycle.
#252 - float(float serveridx, void(float serveridx, float queryidx, float rows, float columns, float eof) callback, float querytype, string query) sqlopenquery
- Executes a query, using a callback for incoming query data. A querytype of 1 signifies a persistant query.
- Global variables self and other are saved at the time of the sqlopenquery call and restored for the callback.
#253 - void(float serveridx, float queryidx) sqlclosequery
- Closes a persistant or incomplete query. Non-persistant queries are closed automatically.
#254 - string(float serveridx, float queryidx, float row, float column) sqlreadfield
- If row is non-positive, returns field name for column number. Otherwise returns data at row/column.
- This call is only valid during the callback, or after the callback for persistant queries.
#255 - string(float serveridx, [float queryidx]) sqlerror
- Returns last error for connection, or error for query. Only valid for queries during callback.
#256 - string(float serveridx, string data) sqlescape
- Escapes a string for use in a query.
#257 - string(float serveridx) sqlversion
- Returns server information for a server connection.
#258 - float(float serveridx, float queryidx, float row, float column) sqlreadfloat
- Same as sqlreadfield except as a float.

Callback:
void(float serveridx, float queryidx, float rows, float columns, float eof) callback
Called back when query results are done. The callback mechanism is meant to be asynchronous--QuakeC code may execute between query builtin and callback. Rows are rows in the current query results. Columns are number of fields. End of file denotes when a query result is the final set given from a query call. On queries returning no set, rows set to number of affected rows, and columns set to 0. Rows/columns set to -1 on error.

Caveats:
- Nulls probably aren't handled sufficiently
- Queries aren't parameterized (the 8 parameter limit for QuakeC is problematic)
- No transactions/manual commit
- Multiple server connections are probably overkill
- Would be nice to abstract out database type but can't really
- Partial query results/EOF is awkward.. should probably rely on query pagination instead
- The partial query stuff isn't even used in implementation, full query tables are just stored in and one result callback is called
- Callback-less or callback only on error queries aren't supported (would be useful for INSERTs)
- Behavior not defined for queries not executed yet when a map change occurs
- Persistant queries could cost alot of memory

[frag.machine]

TBH I think it's a little overkill.

Asynchronous SQL result is kinda weird in practice: run the query now, get the result one or more frames later ? I can understand you're worried about stalling the VM, but usual response time for modern databases are usually as low as 50 milliseconds or even less (assuming reasonably complex queries and tables with a good amount of data in). I would just try to not worry about this at least from the start.

Also, I'd suggest to let the connection stuff to be completely handled outside the QuakeC code via configuration files, so mods don't need to be recompiled just because the database name or the port number changed. Check how Java or .NET deal with this, using just connection alias inside code to refer to connection configurations defined externally.