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