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"
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)
17 typedef struct db_request db_request
;
19 void (*handler
)( db_request
*req
);
35 * Log the error code (or carry on if its OK).
37 static void log_sqlite3( int code
){
38 if( code
== SQLITE_OK
) return;
40 vg_error( "sqlite3(%d): %s\n", code
, sqlite3_errstr(code
) );
42 #ifdef DB_CRASH_ON_SQLITE_ERROR
43 int crash
= *((int*)2);
48 * Perpare statement and auto throw away if fails. Returns NULL on failure.
50 static sqlite3_stmt
*db_stmt( const char *code
){
51 #ifdef DB_LOG_SQL_STATEMENTS
56 int fc
= sqlite3_prepare_v2( database
.db
, code
, -1, &stmt
, NULL
);
58 if( fc
!= SQLITE_OK
){
60 sqlite3_finalize( stmt
);
68 * bind zero terminated string
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
);
75 * Allowed characters in sqlite table names. We use "" as delimiters.
77 static int db_verify_charset( const char *str
, int mincount
){
78 for( int i
=0; ; i
++ ){
81 if( i
< mincount
) return 0;
85 if( !((c
==' ')||(c
=='!')||(c
>='#'&&c
<='~')) ) return 0;
92 * Find table name from mod UID and course UID, plus the week number
94 static int db_get_highscore_table_name( const char *mod_uid
,
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;
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
);
108 vg_strcat( &a
, "#" );
109 vg_strcati32( &a
, week
);
112 return vg_strgood( &a
);
116 * Read value from highscore table. If not found or error, returns 0
118 static i32
db_readusertime( char table
[DB_TABLE_UID_MAX
], u64 steamid
){
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;
127 sqlite3_stmt
*stmt
= db_stmt( q
.buffer
);
129 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
130 int fc
= sqlite3_step( stmt
);
134 if( fc
== SQLITE_ROW
)
135 result
= sqlite3_column_int( stmt
, 0 );
136 else if( fc
!= SQLITE_DONE
)
139 sqlite3_finalize( stmt
);
146 * Write to highscore table
148 static int db_writeusertime( char table
[DB_TABLE_UID_MAX
], u64 steamid
,
149 i32 score
, int only_if_faster
){
151 * ------------------------------------------*/
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;
161 sqlite3_stmt
*create_table
= db_stmt( q
.buffer
);
164 db_sqlite3_bind_sz( create_table
, 1, table
);
166 int fc
= sqlite3_step( create_table
);
167 sqlite3_finalize( create_table
);
168 if( fc
!= SQLITE_DONE
)
173 if( only_if_faster
){
174 i32 current
= db_readusertime( table
, steamid
);
175 if( (current
!= 0) && (score
> current
) )
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;
187 sqlite3_stmt
*stmt
= db_stmt( q
.buffer
);
190 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
191 sqlite3_bind_int( stmt
, 2, score
);
193 int fc
= sqlite3_step( stmt
);
194 sqlite3_finalize( stmt
);
195 if( fc
!= SQLITE_DONE
)
204 * Set username and type
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) "
212 sqlite3_bind_int64( stmt
, 1, *((i64
*)(&steamid
)) );
213 db_sqlite3_bind_sz( stmt
, 2, username
);
214 sqlite3_bind_int( stmt
, 3, admin
);
216 int fc
= sqlite3_step( stmt
);
217 sqlite3_finalize(stmt
);
219 if( fc
== SQLITE_DONE
){
220 vg_success( "Inserted %lu (%s), type: %d\n",
221 steamid
, username
, admin
);
235 static int db_getuserinfo( u64 steamid
, char *out_username
, u32 username_max
,
237 sqlite3_stmt
*stmt
= db_stmt( "SELECT * FROM users WHERE steamid = ?;" );
238 if( !stmt
) return 0;
240 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
241 int fc
= sqlite3_step( stmt
);
243 if( fc
!= SQLITE_ROW
){
245 sqlite3_finalize( stmt
);
250 const char *name
= (const char *)sqlite3_column_text( stmt
, 1 );
251 vg_strncpy( name
, out_username
, username_max
, k_strncpy_allow_cutoff
);
255 *out_type
= sqlite3_column_int( stmt
, 2 );
257 sqlite3_finalize( stmt
);
261 static void _db_thread_end(void){
262 pthread_mutex_lock( &database
.mux
);
264 pthread_mutex_unlock( &database
.mux
);
265 sqlite3_close( database
.db
);
268 static void *db_loop(void *_
){
269 int rc
= sqlite3_open( "highscores.db", &database
.db
);
272 vg_error( "database failure: %s\n", sqlite3_errmsg(database
.db
) );
277 sqlite3_stmt
*stmt
= db_stmt(
278 "CREATE TABLE IF NOT EXISTS \n"
279 " users(steamid BIGINT UNIQUE, name VARCHAR(128), type INT);" );
282 int fc
= sqlite3_step( stmt
);
283 sqlite3_finalize(stmt
);
285 if( fc
== SQLITE_DONE
){
286 vg_success( "Created users table\n" );
287 db_updateuser( 76561198072130043, "harry", 2 );
301 * Request processing loop
304 pthread_mutex_lock( &database
.mux
);
307 pthread_mutex_unlock( &database
.mux
);
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
);
321 pthread_mutex_unlock( &database
.mux
);
328 pthread_mutex_lock( &database
.mux
);
329 vg_queue_pop( &database
.queue
);
333 vg_low( "Processed %u database requests.\n", processed
);
338 vg_low( "Database thread terminates.\n" );
343 * Create database connection and users table
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
;
350 if( pthread_mutex_init( &database
.mux
, NULL
) )
353 if( pthread_create( &database
.thread
, NULL
, db_loop
, NULL
) )
359 static int db_killed(void){
360 pthread_mutex_lock( &database
.mux
);
361 int result
= database
.kill
;
362 pthread_mutex_unlock( &database
.mux
);
366 static void db_kill(void){
367 pthread_mutex_lock( &database
.mux
);
369 pthread_mutex_unlock( &database
.mux
);
370 pthread_join( database
.thread
, NULL
);
373 static void db_free(void){
374 pthread_mutex_destroy( &database
.mux
);
377 static db_request
*db_alloc_request( u32 size
){
378 u32 total
= sizeof(db_request
) + size
;
380 pthread_mutex_lock( &database
.mux
);
381 vg_queue_frame
*frame
= vg_queue_alloc( &database
.queue
, total
);
384 db_request
*req
= (db_request
*)frame
->data
;
389 pthread_mutex_unlock( &database
.mux
);
394 static void db_send_request( db_request
*request
){
395 pthread_mutex_unlock( &database
.mux
);
398 #endif /* GAMESERVER_DB_H */