update overlay to have ps controls too
[carveJwlIkooP6JGAAIwe30JlM.git] / gameserver_db.h
1 #ifndef GAMESERVER_DB_H
2 #define GAMESERVER_DB_H
3
4 #include "vg/vg_log.h"
5 #include "vg/vg_mem_queue.h"
6 #include "network_common.h"
7 #include "dep/sqlite3/sqlite3.h"
8 #include <pthread.h>
9 #include <unistd.h>
10
11 #define DB_COURSE_UID_MAX 32
12 #define DB_TABLE_UID_MAX (ADDON_UID_MAX+DB_COURSE_UID_MAX+32)
13 //#define DB_CRASH_ON_SQLITE_ERROR
14 #define DB_LOG_SQL_STATEMENTS
15 #define DB_REQUEST_BUFFER_SIZE (1024*2)
16
17 typedef struct db_request db_request;
18 struct db_request {
19 void (*handler)( db_request *req );
20 u32 size,_;
21 u8 data[];
22 };
23
24 struct {
25 sqlite3 *db;
26 pthread_t thread;
27 pthread_mutex_t mux;
28
29 vg_queue queue;
30 int kill;
31 }
32 static database;
33
34 /*
35 * Log the error code (or carry on if its OK).
36 */
37 static void log_sqlite3( int code ){
38 if( code == SQLITE_OK ) return;
39 vg_print_backtrace();
40 vg_error( "sqlite3(%d): %s\n", code, sqlite3_errstr(code) );
41
42 #ifdef DB_CRASH_ON_SQLITE_ERROR
43 int crash = *((int*)2);
44 #endif
45 }
46
47 /*
48 * Perpare statement and auto throw away if fails. Returns NULL on failure.
49 */
50 static sqlite3_stmt *db_stmt( const char *code ){
51 #ifdef DB_LOG_SQL_STATEMENTS
52 vg_low( code );
53 #endif
54
55 sqlite3_stmt *stmt;
56 int fc = sqlite3_prepare_v2( database.db, code, -1, &stmt, NULL );
57
58 if( fc != SQLITE_OK ){
59 log_sqlite3( fc );
60 sqlite3_finalize( stmt );
61 return NULL;
62 }
63
64 return stmt;
65 }
66
67 /*
68 * bind zero terminated string
69 */
70 static int db_sqlite3_bind_sz( sqlite3_stmt *stmt, int pos, const char *sz ){
71 return sqlite3_bind_text( stmt, pos, sz, -1, SQLITE_STATIC );
72 }
73
74 /*
75 * Allowed characters in sqlite table names. We use "" as delimiters.
76 */
77 static int db_verify_charset( const char *str, int mincount ){
78 for( int i=0; ; i++ ){
79 char c = str[i];
80 if( c == '\0' ){
81 if( i < mincount ) return 0;
82 else return 1;
83 }
84
85 if( !((c==' ')||(c=='!')||(c>='#'&&c<='~')) ) return 0;
86 }
87
88 return 0;
89 }
90
91 /*
92 * Find table name from mod UID and course UID, plus the week number
93 */
94 static int db_get_highscore_table_name( const char *mod_uid,
95 const char *run_uid,
96 u32 week,
97 char table_name[DB_TABLE_UID_MAX] ){
98 if( !db_verify_charset( mod_uid, 13 ) ||
99 !db_verify_charset( run_uid, 1 ) ) return 0;
100
101 vg_str a;
102 vg_strnull( &a, table_name, DB_TABLE_UID_MAX );
103 vg_strcat( &a, mod_uid );
104 vg_strcat( &a, ":" );
105 vg_strcat( &a, run_uid );
106
107 if( week ){
108 vg_strcat( &a, "#" );
109 vg_strcati32( &a, week );
110 }
111
112 return vg_strgood( &a );
113 }
114
115 /*
116 * Read value from highscore table. If not found or error, returns 0
117 */
118 static i32 db_readusertime( char table[DB_TABLE_UID_MAX], u64 steamid ){
119 char buf[ 512 ];
120 vg_str q;
121 vg_strnull( &q, buf, 512 );
122 vg_strcat( &q, "SELECT time FROM \"" );
123 vg_strcat( &q, table );
124 vg_strcat( &q, "\" WHERE steamid = ?;" );
125 if( !vg_strgood(&q) ) return 0;
126
127 sqlite3_stmt *stmt = db_stmt( q.buffer );
128 if( stmt ){
129 sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
130 int fc = sqlite3_step( stmt );
131
132 i32 result = 0;
133
134 if( fc == SQLITE_ROW )
135 result = sqlite3_column_int( stmt, 0 );
136 else if( fc != SQLITE_DONE )
137 log_sqlite3(fc);
138
139 sqlite3_finalize( stmt );
140 return result;
141 }
142 else return 0;
143 }
144
145 /*
146 * Write to highscore table
147 */
148 static int db_writeusertime( char table[DB_TABLE_UID_MAX], u64 steamid,
149 i32 score, int only_if_faster ){
150 /* auto create table
151 * ------------------------------------------*/
152 char buf[ 512 ];
153 vg_str q;
154 vg_strnull( &q, buf, 512 );
155 vg_strcat( &q, "CREATE TABLE IF NOT EXISTS \n \"" );
156 vg_strcat( &q, table );
157 vg_strcat( &q, "\"\n (steamid BIGINT UNIQUE, time INT);" );
158 if( !vg_strgood(&q) ) return 0;
159
160 vg_str str;
161 sqlite3_stmt *create_table = db_stmt( q.buffer );
162
163 if( create_table ){
164 db_sqlite3_bind_sz( create_table, 1, table );
165
166 int fc = sqlite3_step( create_table );
167 sqlite3_finalize( create_table );
168 if( fc != SQLITE_DONE )
169 return 0;
170 }
171 else return 0;
172
173 if( only_if_faster ){
174 i32 current = db_readusertime( table, steamid );
175 if( (current != 0) && (score > current) )
176 return 1;
177 }
178
179 /* insert score
180 * -------------------------------------------------*/
181 vg_strnull( &q, buf, 512 );
182 vg_strcat( &q, "REPLACE INTO \"" );
183 vg_strcat( &q, table );
184 vg_strcat( &q, "\"(steamid,time)\n VALUES (?,?);" );
185 if( !vg_strgood(&q) ) return 0;
186
187 sqlite3_stmt *stmt = db_stmt( q.buffer );
188
189 if( stmt ){
190 sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
191 sqlite3_bind_int( stmt, 2, score );
192
193 int fc = sqlite3_step( stmt );
194 sqlite3_finalize( stmt );
195 if( fc != SQLITE_DONE )
196 return 0;
197 else
198 return 1;
199 }
200 else return 0;
201 }
202
203 /*
204 * Set username and type
205 */
206 static int db_updateuser( u64 steamid, const char *username, int admin ){
207 sqlite3_stmt *stmt = db_stmt(
208 "INSERT OR REPLACE INTO users (steamid, name, type) "
209 "VALUES (?,?,?);" );
210
211 if( stmt ){
212 sqlite3_bind_int64( stmt, 1, *((i64*)(&steamid)) );
213 db_sqlite3_bind_sz( stmt, 2, username );
214 sqlite3_bind_int( stmt, 3, admin );
215
216 int fc = sqlite3_step( stmt );
217 sqlite3_finalize(stmt);
218
219 if( fc == SQLITE_DONE ){
220 vg_success( "Inserted %lu (%s), type: %d\n",
221 steamid, username, admin );
222 return 1;
223 }
224 else{
225 log_sqlite3( fc );
226 return 0;
227 }
228 }
229 else return 0;
230 }
231
232 /*
233 * Get user info
234 */
235 static int db_getuserinfo( u64 steamid, char *out_username, u32 username_max,
236 i32 *out_type ){
237 sqlite3_stmt *stmt = db_stmt( "SELECT * FROM users WHERE steamid = ?;" );
238 if( !stmt ) return 0;
239
240 sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
241 int fc = sqlite3_step( stmt );
242
243 if( fc != SQLITE_ROW ){
244 log_sqlite3( fc );
245 sqlite3_finalize( stmt );
246 return 0;
247 }
248
249 if( out_username ){
250 const char *name = (const char *)sqlite3_column_text( stmt, 1 );
251 vg_strncpy( name, out_username, username_max, k_strncpy_allow_cutoff );
252 }
253
254 if( out_type )
255 *out_type = sqlite3_column_int( stmt, 2 );
256
257 sqlite3_finalize( stmt );
258 return 1;
259 }
260
261 static void _db_thread_end(void){
262 pthread_mutex_lock( &database.mux );
263 database.kill = 1;
264 pthread_mutex_unlock( &database.mux );
265 sqlite3_close( database.db );
266 }
267
268 static void *db_loop(void *_){
269 int rc = sqlite3_open( "highscores.db", &database.db );
270
271 if( rc ){
272 vg_error( "database failure: %s\n", sqlite3_errmsg(database.db) );
273 _db_thread_end();
274 return NULL;
275 }
276
277 sqlite3_stmt *stmt = db_stmt(
278 "CREATE TABLE IF NOT EXISTS \n"
279 " users(steamid BIGINT UNIQUE, name VARCHAR(128), type INT);" );
280
281 if( stmt ){
282 int fc = sqlite3_step( stmt );
283 sqlite3_finalize(stmt);
284
285 if( fc == SQLITE_DONE ){
286 vg_success( "Created users table\n" );
287 db_updateuser( 76561198072130043, "harry", 2 );
288 }
289 else{
290 log_sqlite3( fc );
291 _db_thread_end();
292 return NULL;
293 }
294 }
295 else {
296 _db_thread_end();
297 return NULL;
298 }
299
300 /*
301 * Request processing loop
302 */
303 while(1){
304 pthread_mutex_lock( &database.mux );
305
306 if( database.kill ){
307 pthread_mutex_unlock( &database.mux );
308 _db_thread_end();
309 break;
310 }
311
312 u32 processed = 0;
313
314 for( u32 i=0; i<16; i ++ ){
315 db_request *req = NULL;
316 if( database.queue.tail ){
317 req = (db_request *)database.queue.tail->data;
318 pthread_mutex_unlock( &database.mux );
319 }
320 else{
321 pthread_mutex_unlock( &database.mux );
322 break;
323 }
324
325 req->handler( req );
326 processed ++;
327
328 pthread_mutex_lock( &database.mux );
329 vg_queue_pop( &database.queue );
330 }
331
332 if( processed )
333 vg_low( "Processed %u database requests.\n", processed );
334
335 usleep(50000);
336 }
337
338 vg_low( "Database thread terminates.\n" );
339 return NULL;
340 }
341
342 /*
343 * Create database connection and users table
344 */
345 static int db_init(void){
346 database.queue.buffer =
347 (u8 *)vg_linear_alloc( vg_mem.rtmemory, DB_REQUEST_BUFFER_SIZE ),
348 database.queue.size = DB_REQUEST_BUFFER_SIZE;
349
350 if( pthread_mutex_init( &database.mux, NULL ) )
351 return 0;
352
353 if( pthread_create( &database.thread, NULL, db_loop, NULL ) )
354 return 0;
355
356 return 1;
357 }
358
359 static int db_killed(void){
360 pthread_mutex_lock( &database.mux );
361 int result = database.kill;
362 pthread_mutex_unlock( &database.mux );
363 return result;
364 }
365
366 static void db_kill(void){
367 pthread_mutex_lock( &database.mux );
368 database.kill = 1;
369 pthread_mutex_unlock( &database.mux );
370 pthread_join( database.thread, NULL );
371 }
372
373 static void db_free(void){
374 pthread_mutex_destroy( &database.mux );
375 }
376
377 static db_request *db_alloc_request( u32 size ){
378 u32 total = sizeof(db_request) + size;
379
380 pthread_mutex_lock( &database.mux );
381 vg_queue_frame *frame = vg_queue_alloc( &database.queue, total );
382
383 if( frame ){
384 db_request *req = (db_request *)frame->data;
385 req->size = size;
386 return req;
387 }
388 else {
389 pthread_mutex_unlock( &database.mux );
390 return NULL;
391 }
392 }
393
394 static void db_send_request( db_request *request ){
395 pthread_mutex_unlock( &database.mux );
396 }
397
398 #endif /* GAMESERVER_DB_H */