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( char mod_uid
[ADDON_UID_MAX
],
96 char run_uid
[DB_COURSE_UID_MAX
],
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
);
129 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
132 int fc
= sqlite3_step( stmt
);
136 if( fc
== SQLITE_ROW
)
137 result
= sqlite3_column_int( stmt
, 0 );
138 else if( fc
!= SQLITE_DONE
)
141 sqlite3_finalize( stmt
);
148 * Write to highscore table
150 static int db_writeusertime( char table
[DB_TABLE_UID_MAX
], u64 steamid
,
151 i32 score
, int only_if_faster
){
153 * ------------------------------------------*/
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;
163 sqlite3_stmt
*create_table
= db_stmt( q
.buffer
);
166 db_sqlite3_bind_sz( create_table
, 1, table
);
168 int fc
= sqlite3_step( create_table
);
169 sqlite3_finalize( create_table
);
170 if( fc
!= SQLITE_DONE
)
175 if( only_if_faster
){
176 i32 current
= db_readusertime( table
, steamid
);
177 if( (current
!= 0) && (score
> current
) )
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;
189 sqlite3_stmt
*stmt
= db_stmt( q
.buffer
);
192 sqlite3_bind_int64( stmt
, 1, *((i64
*)&steamid
) );
193 sqlite3_bind_int( stmt
, 2, score
);
195 int fc
= sqlite3_step( stmt
);
196 sqlite3_finalize( stmt
);
197 if( fc
!= SQLITE_DONE
)
206 * Set username and type
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) "
214 sqlite3_bind_int64( stmt
, 1, *((i64
*)(&steamid
)) );
215 db_sqlite3_bind_sz( stmt
, 2, username
);
216 sqlite3_bind_int( stmt
, 3, admin
);
218 int fc
= sqlite3_step( stmt
);
219 sqlite3_finalize(stmt
);
221 if( fc
== SQLITE_DONE
){
222 vg_success( "Inserted %lu (%s), type: %d\n",
223 steamid
, username
, admin
);
234 static void _db_thread_end(void){
235 pthread_mutex_lock( &database
.mux
);
237 pthread_mutex_unlock( &database
.mux
);
238 sqlite3_close( database
.db
);
239 pthread_mutex_destroy( &database
.mux
);
242 static void *db_loop(void *_
){
243 int rc
= sqlite3_open( "highscores.db", &database
.db
);
246 vg_error( "database failure: %s\n", sqlite3_errmsg(database
.db
) );
251 sqlite3_stmt
*stmt
= db_stmt(
252 "CREATE TABLE IF NOT EXISTS \n"
253 " users(steamid BIGINT PRIMARY KEY, name VARCHAR(128), type INT);" );
256 int fc
= sqlite3_step( stmt
);
257 sqlite3_finalize(stmt
);
259 if( fc
== SQLITE_DONE
){
260 vg_success( "Created users table\n" );
261 db_updateuser( 76561198072130043, "harry", 2 );
275 * Request processing loop
278 pthread_mutex_lock( &database
.mux
);
281 pthread_mutex_unlock( &database
.mux
);
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
);
295 pthread_mutex_unlock( &database
.mux
);
302 pthread_mutex_lock( &database
.mux
);
303 vg_queue_pop( &database
.queue
);
307 vg_low( "Processed %u database requests.\n", processed
);
312 vg_low( "Database thread terminates.\n" );
317 * Create database connection and users table
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
;
324 if( pthread_mutex_init( &database
.mux
, NULL
) )
327 if( pthread_create( &database
.thread
, NULL
, db_loop
, NULL
) )
333 static int db_killed(void){
334 pthread_mutex_lock( &database
.mux
);
335 int result
= database
.kill
;
336 pthread_mutex_unlock( &database
.mux
);
340 static void db_free(void){
341 pthread_mutex_lock( &database
.mux
);
343 pthread_mutex_unlock( &database
.mux
);
346 static db_request
*db_alloc_request( u32 size
){
347 u32 total
= sizeof(db_request
) + vg_align8(size
);
349 pthread_mutex_lock( &database
.mux
);
350 vg_queue_frame
*frame
= vg_queue_alloc( &database
.queue
, size
);
353 db_request
*req
= (db_request
*)frame
->data
;
358 pthread_mutex_unlock( &database
.mux
);
363 static void db_send_request( db_request
*request
){
364 pthread_mutex_unlock( &database
.mux
);
367 #endif /* GAMESERVER_DB_H */