1 #ifndef GAMESERVER_DB_H
2 #define GAMESERVER_DB_H
5 #include "vg/vg_mem_queue.h"
6 #include "network_common.h"
7 #include "dep/sqlite3/sqlite3.h"
8 #include "highscores.h"
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)
18 typedef struct db_request db_request
;
20 void (*handler
)( db_request
*req
);
36 * Log the error code (or carry on if its OK).
38 static void log_sqlite3( int code
){
39 if( code
== SQLITE_OK
) return;
41 vg_error( "sqlite3(%d): %s\n", code
, sqlite3_errstr(code
) );
43 #ifdef DB_CRASH_ON_SQLITE_ERROR
44 int crash
= *((int*)2);
49 * Perpare statement and auto throw away if fails. Returns NULL on failure.
51 static sqlite3_stmt
*db_stmt( const char *code
){
52 #ifdef DB_LOG_SQL_STATEMENTS
57 int fc
= sqlite3_prepare_v2( database
.db
, code
, -1, &stmt
, NULL
);
59 if( fc
!= SQLITE_OK
){
61 sqlite3_finalize( stmt
);
69 * bind zero terminated string
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
);
76 * Allowed characters in sqlite table names. We use "" as delimiters.
78 static int db_verify_charset( const char *str
, int mincount
){
79 for( int i
=0; ; i
++ ){
82 if( i
< mincount
) return 0;
86 if( !((c
==' ')||(c
=='!')||(c
>='#'&&c
<='~')) ) return 0;
93 * Find table name from mod UID and course UID, plus the week number
95 static int db_get_highscore_table_name( const char *mod_uid
,
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;
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
);
109 vg_strcat( &a
, "#" );
110 vg_strcati32( &a
, week
);
113 return vg_strgood( &a
);
117 * Read value from highscore table. If not found or error, returns 0
119 static i32
db_readusertime( char table
[DB_TABLE_UID_MAX
], u64 steamid
){
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;
128 sqlite3_stmt
*stmt
= db_stmt( q
.buffer
);
130 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
131 int fc
= sqlite3_step( stmt
);
135 if( fc
== SQLITE_ROW
)
136 result
= sqlite3_column_int( stmt
, 0 );
137 else if( fc
!= SQLITE_DONE
)
140 sqlite3_finalize( stmt
);
147 * Write to highscore table
149 static int db_writeusertime( char table
[DB_TABLE_UID_MAX
], u64 steamid
,
150 i32 score
, int only_if_faster
){
152 * ------------------------------------------*/
155 vg_strnull( &q
, buf
, 512 );
156 vg_strcat( &q
, "CREATE TABLE IF NOT EXISTS \n \"" );
157 vg_strcat( &q
, table
);
158 vg_strcat( &q
, "\"\n (steamid BIGINT UNIQUE, time INT);" );
159 if( !vg_strgood(&q
) ) return 0;
162 sqlite3_stmt
*create_table
= db_stmt( q
.buffer
);
165 db_sqlite3_bind_sz( create_table
, 1, table
);
167 int fc
= sqlite3_step( create_table
);
168 sqlite3_finalize( create_table
);
169 if( fc
!= SQLITE_DONE
)
174 if( only_if_faster
){
175 i32 current
= db_readusertime( table
, steamid
);
176 if( (current
!= 0) && (score
> current
) )
181 * -------------------------------------------------*/
182 vg_strnull( &q
, buf
, 512 );
183 vg_strcat( &q
, "REPLACE INTO \"" );
184 vg_strcat( &q
, table
);
185 vg_strcat( &q
, "\"(steamid,time)\n VALUES (?,?);" );
186 if( !vg_strgood(&q
) ) return 0;
188 sqlite3_stmt
*stmt
= db_stmt( q
.buffer
);
191 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
192 sqlite3_bind_int( stmt
, 2, score
);
194 int fc
= sqlite3_step( stmt
);
195 sqlite3_finalize( stmt
);
196 if( fc
!= SQLITE_DONE
)
205 * Set username and type
207 static int db_updateuser( u64 steamid
, const char *username
, int admin
){
208 sqlite3_stmt
*stmt
= db_stmt(
209 "INSERT OR REPLACE INTO users (steamid, name, type) "
213 sqlite3_bind_int64( stmt
, 1, *((i64
*)(&steamid
)) );
214 db_sqlite3_bind_sz( stmt
, 2, username
);
215 sqlite3_bind_int( stmt
, 3, admin
);
217 int fc
= sqlite3_step( stmt
);
218 sqlite3_finalize(stmt
);
220 if( fc
== SQLITE_DONE
){
221 vg_success( "Inserted %lu (%s), type: %d\n",
222 steamid
, username
, admin
);
236 static int db_getuserinfo( u64 steamid
, char *out_username
, u32 username_max
,
238 sqlite3_stmt
*stmt
= db_stmt( "SELECT * FROM users WHERE steamid = ?;" );
239 if( !stmt
) return 0;
241 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
242 int fc
= sqlite3_step( stmt
);
244 if( fc
!= SQLITE_ROW
){
246 sqlite3_finalize( stmt
);
251 const char *name
= (const char *)sqlite3_column_text( stmt
, 1 );
252 vg_strncpy( name
, out_username
, username_max
, k_strncpy_allow_cutoff
);
256 *out_type
= sqlite3_column_int( stmt
, 2 );
258 sqlite3_finalize( stmt
);
262 static void _db_thread_end(void){
263 pthread_mutex_lock( &database
.mux
);
265 pthread_mutex_unlock( &database
.mux
);
266 sqlite3_close( database
.db
);
269 static void *db_loop(void *_
){
270 int rc
= sqlite3_open( "highscores.db", &database
.db
);
273 vg_error( "database failure: %s\n", sqlite3_errmsg(database
.db
) );
278 sqlite3_stmt
*stmt
= db_stmt(
279 "CREATE TABLE IF NOT EXISTS \n"
280 " users(steamid BIGINT UNIQUE, name VARCHAR(128), type INT);" );
283 int fc
= sqlite3_step( stmt
);
284 sqlite3_finalize(stmt
);
286 if( fc
== SQLITE_DONE
){
287 vg_success( "Created users table\n" );
288 db_updateuser( 76561198072130043, "harry", 2 );
302 * Request processing loop
305 pthread_mutex_lock( &database
.mux
);
308 pthread_mutex_unlock( &database
.mux
);
315 for( u32 i
=0; i
<16; i
++ ){
316 db_request
*req
= NULL
;
317 if( database
.queue
.tail
){
318 req
= (db_request
*)database
.queue
.tail
->data
;
319 pthread_mutex_unlock( &database
.mux
);
322 pthread_mutex_unlock( &database
.mux
);
329 pthread_mutex_lock( &database
.mux
);
330 vg_queue_pop( &database
.queue
);
334 vg_low( "Processed %u database requests.\n", processed
);
339 vg_low( "Database thread terminates.\n" );
344 * Create database connection and users table
346 static int db_init(void){
347 database
.queue
.buffer
=
348 (u8
*)vg_linear_alloc( vg_mem
.rtmemory
, DB_REQUEST_BUFFER_SIZE
),
349 database
.queue
.size
= DB_REQUEST_BUFFER_SIZE
;
351 if( pthread_mutex_init( &database
.mux
, NULL
) )
354 if( pthread_create( &database
.thread
, NULL
, db_loop
, NULL
) )
360 static int db_killed(void){
361 pthread_mutex_lock( &database
.mux
);
362 int result
= database
.kill
;
363 pthread_mutex_unlock( &database
.mux
);
367 static void db_kill(void){
368 pthread_mutex_lock( &database
.mux
);
370 pthread_mutex_unlock( &database
.mux
);
371 pthread_join( database
.thread
, NULL
);
374 static void db_free(void){
375 pthread_mutex_destroy( &database
.mux
);
378 static db_request
*db_alloc_request( u32 size
){
379 u32 total
= sizeof(db_request
) + size
;
381 pthread_mutex_lock( &database
.mux
);
382 vg_queue_frame
*frame
= vg_queue_alloc( &database
.queue
, total
);
385 db_request
*req
= (db_request
*)frame
->data
;
390 pthread_mutex_unlock( &database
.mux
);
395 static void db_send_request( db_request
*request
){
396 pthread_mutex_unlock( &database
.mux
);
399 #endif /* GAMESERVER_DB_H */