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( const char *mod_uid,
  96                                         const char *run_uid,
  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    if( stmt ){
 130       sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
 131       int fc = sqlite3_step( stmt );
 132
 133       i32 result = 0;
 134
 135       if( fc == SQLITE_ROW )
 136          result = sqlite3_column_int( stmt, 0 );
 137       else if( fc != SQLITE_DONE )
 138          log_sqlite3(fc);
 139
 140       sqlite3_finalize( stmt );
 141       return result;
 142    }
 143    else return 0;
 144 }
 145
 146 /*
 147  * Write to highscore table
 148  */
 149 static int db_writeusertime( char table[DB_TABLE_UID_MAX], u64 steamid,
 150                              i32 score, int only_if_faster ){
 151    /* auto create table
 152     * ------------------------------------------*/
 153    char buf[ 512 ];
 154    vg_str q;
 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;
 160
 161    vg_str str;
 162    sqlite3_stmt *create_table = db_stmt( q.buffer );
 163
 164    if( create_table ){
 165       db_sqlite3_bind_sz( create_table, 1, table );
 166
 167       int fc = sqlite3_step( create_table );
 168       sqlite3_finalize( create_table );
 169       if( fc != SQLITE_DONE )
 170          return 0;
 171    }
 172    else return 0;
 173
 174    if( only_if_faster ){
 175       i32 current = db_readusertime( table, steamid );
 176       if( (current != 0) && (score > current) )
 177          return 1;
 178    }
 179
 180    /* insert score
 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;
 187
 188    sqlite3_stmt *stmt = db_stmt( q.buffer );
 189
 190    if( stmt ){
 191       sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
 192       sqlite3_bind_int( stmt, 2, score );
 193
 194       int fc = sqlite3_step( stmt );
 195       sqlite3_finalize( stmt );
 196       if( fc != SQLITE_DONE )
 197          return 0;
 198       else
 199          return 1;
 200    }
 201    else return 0;
 202 }
 203
 204 /*
 205  * Set username and type
 206  */
 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) "
 210          "VALUES (?,?,?);" );
 211
 212    if( stmt ){
 213       sqlite3_bind_int64( stmt, 1, *((i64*)(&steamid)) );
 214       db_sqlite3_bind_sz( stmt, 2, username );
 215       sqlite3_bind_int( stmt, 3, admin );
 216
 217       int fc = sqlite3_step( stmt );
 218       sqlite3_finalize(stmt);
 219
 220       if( fc == SQLITE_DONE ){
 221          vg_success( "Inserted %lu (%s), type: %d\n",
 222                      steamid, username, admin );
 223          return 1;
 224       }
 225       else{
 226          log_sqlite3( fc );
 227          return 0;
 228       }
 229    }
 230    else return 0;
 231 }
 232
 233 /*
 234  * Get user info
 235  */
 236 static int db_getuserinfo( u64 steamid, char *out_username, u32 username_max,
 237                            i32 *out_type ){
 238    sqlite3_stmt *stmt = db_stmt( "SELECT * FROM users WHERE steamid = ?;" );
 239    if( !stmt ) return 0;
 240
 241    sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
 242    int fc = sqlite3_step( stmt );
 243
 244    if( fc != SQLITE_ROW ){
 245       log_sqlite3( fc );
 246       sqlite3_finalize( stmt );
 247       return 0;
 248    }
 249
 250    if( out_username ){
 251       const char *name = (const char *)sqlite3_column_text( stmt, 1 );
 252       vg_strncpy( name, out_username, username_max, k_strncpy_allow_cutoff );
 253    }
 254
 255    if( out_type )
 256       *out_type = sqlite3_column_int( stmt, 2 );
 257
 258    sqlite3_finalize( stmt );
 259    return 1;
 260 }
 261
 262 static void _db_thread_end(void){
 263    pthread_mutex_lock( &database.mux );
 264    database.kill = 1;
 265    pthread_mutex_unlock( &database.mux );
 266    sqlite3_close( database.db );
 267 }
 268
 269 static void *db_loop(void *_){
 270    int rc = sqlite3_open( "highscores.db", &database.db );
 271
 272    if( rc ){
 273       vg_error( "database failure: %s\n", sqlite3_errmsg(database.db) );
 274       _db_thread_end();
 275       return NULL;
 276    }
 277
 278    sqlite3_stmt *stmt = db_stmt(
 279          "CREATE TABLE IF NOT EXISTS \n"
 280          " users(steamid BIGINT UNIQUE, name VARCHAR(128), type INT);" );
 281
 282    if( stmt ){
 283       int fc = sqlite3_step( stmt );
 284       sqlite3_finalize(stmt);
 285
 286       if( fc == SQLITE_DONE ){
 287          vg_success( "Created users table\n" );
 288          db_updateuser( 76561198072130043, "harry", 2 );
 289       }
 290       else{
 291          log_sqlite3( fc );
 292          _db_thread_end();
 293          return NULL;
 294       }
 295    }
 296    else {
 297       _db_thread_end();
 298       return NULL;
 299    }
 300
 301    /*
 302     * Request processing loop
 303     */
 304    while(1){
 305       pthread_mutex_lock( &database.mux );
 306
 307       if( database.kill ){
 308          pthread_mutex_unlock( &database.mux );
 309          _db_thread_end();
 310          break;
 311       }
 312
 313       u32 processed = 0;
 314
 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 );
 320          }
 321          else{
 322             pthread_mutex_unlock( &database.mux );
 323             break;
 324          }
 325
 326          req->handler( req );
 327          processed ++;
 328
 329          pthread_mutex_lock( &database.mux );
 330          vg_queue_pop( &database.queue );
 331       }
 332
 333       if( processed )
 334          vg_low( "Processed %u database requests.\n", processed );
 335
 336       usleep(50000);
 337    }
 338
 339    vg_low( "Database thread terminates.\n" );
 340    return NULL;
 341 }
 342
 343 /*
 344  * Create database connection and users table
 345  */
 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;
 350
 351    if( pthread_mutex_init( &database.mux, NULL ) )
 352       return 0;
 353
 354    if( pthread_create( &database.thread, NULL, db_loop, NULL ) )
 355       return 0;
 356
 357    return 1;
 358 }
 359
 360 static int db_killed(void){
 361    pthread_mutex_lock( &database.mux );
 362    int result = database.kill;
 363    pthread_mutex_unlock( &database.mux );
 364    return result;
 365 }
 366
 367 static void db_kill(void){
 368    pthread_mutex_lock( &database.mux );
 369    database.kill = 1;
 370    pthread_mutex_unlock( &database.mux );
 371    pthread_join( database.thread, NULL );
 372 }
 373
 374 static void db_free(void){
 375    pthread_mutex_destroy( &database.mux );
 376 }
 377
 378 static db_request *db_alloc_request( u32 size ){
 379    u32 total = sizeof(db_request) + size;
 380
 381    pthread_mutex_lock( &database.mux );
 382    vg_queue_frame *frame = vg_queue_alloc( &database.queue, total );
 383
 384    if( frame ){
 385       db_request *req = (db_request *)frame->data;
 386       req->size = size;
 387       return req;
 388    }
 389    else {
 390       pthread_mutex_unlock( &database.mux );
 391       return NULL;
 392    }
 393 }
 394
 395 static void db_send_request( db_request *request ){
 396    pthread_mutex_unlock( &database.mux );
 397 }
 398
 399 #endif /* GAMESERVER_DB_H */