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