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