gameserver_db.h

   1 #ifndef GAMESERVER_DB_H
   2 #define GAMESERVER_DB_H
   3
   4 #include "vg/vg_log.h"
   5 #include "network_common.h"
   6 #include "dep/sqlite3/sqlite3.h"
   7 #include "highscores.h"
   8
   9 #define DB_COURSE_UID_MAX 32
  10 #define DB_TABLE_UID_MAX (ADDON_UID_MAX+DB_COURSE_UID_MAX+32)
  11 #define DB_CRASH_ON_SQLITE_ERROR
  12 #define DB_LOG_SQL_STATEMENTS
  13
  14 struct {
  15    sqlite3 *db;
  16 }
  17 static database;
  18
  19 /*
  20  * Log the error code (or carry on if its OK).
  21  */
  22 static void log_sqlite3( int code ){
  23    if( code == SQLITE_OK ) return;
  24    vg_print_backtrace();
  25    vg_error( "sqlite3(%d): %s\n", code, sqlite3_errstr(code) );
  26
  27 #ifdef DB_CRASH_ON_SQLITE_ERROR
  28    int crash = *((int*)2);
  29 #endif
  30 }
  31
  32 /*
  33  * Perpare statement and auto throw away if fails. Returns NULL on failure.
  34  */
  35 static sqlite3_stmt *db_stmt( const char *code ){
  36 #ifdef DB_LOG_SQL_STATEMENTS
  37    vg_low( code );
  38 #endif
  39
  40    sqlite3_stmt *stmt;
  41    int fc = sqlite3_prepare_v2( database.db, code, -1, &stmt, NULL );
  42
  43    if( fc != SQLITE_OK ){
  44       log_sqlite3( fc );
  45       sqlite3_finalize( stmt );
  46       return NULL;
  47    }
  48
  49    return stmt;
  50 }
  51
  52 /*
  53  * bind zero terminated string
  54  */
  55 static int db_sqlite3_bind_sz( sqlite3_stmt *stmt, int pos, const char *sz ){
  56    return sqlite3_bind_text( stmt, pos, sz, -1, SQLITE_STATIC );
  57 }
  58
  59 /*
  60  * Allowed characters in sqlite table names. We use "" as delimiters.
  61  */
  62 static int db_verify_charset( const char *str, int mincount ){
  63    for( int i=0; ; i++ ){
  64       char c = str[i];
  65       if( c == '\0' ){
  66          if( i < mincount ) return 0;
  67          else return 1;
  68       }
  69
  70       if( !((c==' ')||(c=='!')||(c>='#'&&c<='~')) ) return 0;
  71    }
  72
  73    return 0;
  74 }
  75
  76 /*
  77  * Find table name from mod UID and course UID, plus the week number
  78  */
  79 static int db_get_highscore_table_name( char mod_uid[ADDON_UID_MAX],
  80                                         char run_uid[DB_COURSE_UID_MAX],
  81                                         u32 week,
  82                                         char table_name[DB_TABLE_UID_MAX] ){
  83    if( !db_verify_charset( mod_uid, 13 ) ||
  84        !db_verify_charset( run_uid, 1 ) ) return 0;
  85
  86    vg_str a;
  87    vg_strnull( &a, table_name, DB_TABLE_UID_MAX );
  88    vg_strcat( &a, mod_uid );
  89    vg_strcat( &a, ":" );
  90    vg_strcat( &a, run_uid );
  91
  92    if( week ){
  93       vg_strcat( &a, "#" );
  94       vg_strcati32( &a, week );
  95    }
  96
  97    return vg_strgood( &a );
  98 }
  99
 100 /*
 101  * Read value from highscore table. If not found or error, returns 0
 102  */
 103 static i32 db_readusertime( char table[DB_TABLE_UID_MAX], u64 steamid ){
 104    char buf[ 512 ];
 105    vg_str q;
 106    vg_strnull( &q, buf, 512 );
 107    vg_strcat( &q, "SELECT time FROM \"" );
 108    vg_strcat( &q, table );
 109    vg_strcat( &q, "\" WHERE steamid = ?;" );
 110    if( !vg_strgood(&q) ) return 0;
 111
 112    sqlite3_stmt *stmt = db_stmt( q.buffer );
 113    sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
 114
 115    if( stmt ){
 116       int fc = sqlite3_step( stmt );
 117
 118       i32 result = 0;
 119
 120       if( fc == SQLITE_ROW )
 121          result = sqlite3_column_int( stmt, 0 );
 122       else if( fc != SQLITE_DONE )
 123          log_sqlite3(fc);
 124
 125       sqlite3_finalize( stmt );
 126       return result;
 127    }
 128    else return 0;
 129 }
 130
 131 /*
 132  * Write to highscore table
 133  */
 134 static int db_writeusertime( char table[DB_TABLE_UID_MAX], u64 steamid,
 135                              i32 score, int only_if_faster ){
 136    /* auto create table
 137     * ------------------------------------------*/
 138    char buf[ 512 ];
 139    vg_str q;
 140    vg_strnull( &q, buf, 512 );
 141    vg_strcat( &q, "CREATE TABLE IF NOT EXISTS \n \"" );
 142    vg_strcat( &q, table );
 143    vg_strcat( &q, "\"\n (steamid BIGINT PRIMARY KEY, time INT);" );
 144    if( !vg_strgood(&q) ) return 0;
 145
 146    vg_str str;
 147    sqlite3_stmt *create_table = db_stmt( q.buffer );
 148
 149    if( create_table ){
 150       db_sqlite3_bind_sz( create_table, 1, table );
 151
 152       int fc = sqlite3_step( create_table );
 153       sqlite3_finalize( create_table );
 154       if( fc != SQLITE_DONE )
 155          return 0;
 156    }
 157    else return 0;
 158
 159    if( only_if_faster ){
 160       i32 current = db_readusertime( table, steamid );
 161       if( (current != 0) && (score > current) )
 162          return 1;
 163    }
 164
 165    /* insert score
 166     * -------------------------------------------------*/
 167    vg_strnull( &q, buf, 512 );
 168    vg_strcat( &q, "REPLACE INTO \"" );
 169    vg_strcat( &q, table );
 170    vg_strcat( &q, "\"(steamid,time)\n VALUES (?,?);" );
 171    if( !vg_strgood(&q) ) return 0;
 172
 173    sqlite3_stmt *stmt = db_stmt( q.buffer );
 174
 175    if( stmt ){
 176       sqlite3_bind_int64( stmt, 1, *((i64 *)&steamid) );
 177       sqlite3_bind_int( stmt, 2, score );
 178
 179       int fc = sqlite3_step( stmt );
 180       sqlite3_finalize( stmt );
 181       if( fc != SQLITE_DONE )
 182          return 0;
 183       else
 184          return 1;
 185    }
 186    else return 0;
 187 }
 188
 189 /*
 190  * Set username and type
 191  */
 192 static int db_updateuser( u64 steamid, const char *username, int admin ){
 193    sqlite3_stmt *stmt = db_stmt(
 194          "INSERT OR REPLACE INTO users (steamid, name, type) "
 195          "VALUES (?,?,?);" );
 196
 197    if( stmt ){
 198       sqlite3_bind_int64( stmt, 1, *((i64*)(&steamid)) );
 199       db_sqlite3_bind_sz( stmt, 2, username );
 200       sqlite3_bind_int( stmt, 3, admin );
 201
 202       int fc = sqlite3_step( stmt );
 203       sqlite3_finalize(stmt);
 204
 205       if( fc == SQLITE_DONE ){
 206          vg_success( "Inserted %lu (%s), type: %d\n",
 207                      steamid, username, admin );
 208          return 1;
 209       }
 210       else{
 211          log_sqlite3( fc );
 212          return 0;
 213       }
 214    }
 215    else return 0;
 216 }
 217
 218 /*
 219  * Create database connection and users table
 220  */
 221 static int db_init(void){
 222    int rc = sqlite3_open( "highscores.db", &database.db );
 223
 224    if( rc ){
 225       vg_error( "database failure: %s\n", sqlite3_errmsg(database.db) );
 226       sqlite3_close( database.db );
 227       return 0;
 228    }
 229
 230    sqlite3_stmt *stmt = db_stmt(
 231          "CREATE TABLE IF NOT EXISTS \n"
 232          " users(steamid BIGINT PRIMARY KEY, name VARCHAR(128), type INT);" );
 233
 234    if( stmt ){
 235       int fc = sqlite3_step( stmt );
 236       sqlite3_finalize(stmt);
 237
 238       if( fc == SQLITE_DONE ){
 239          vg_success( "Created users table\n" );
 240          db_updateuser( 76561198072130043, "harry", 2 );
 241          return 1;
 242       }
 243       else{
 244          log_sqlite3( fc );
 245          return 0;
 246       }
 247    }
 248    else return 0;
 249 }
 250
 251 static void db_free(void){
 252    sqlite3_close( database.db );
 253 }
 254
 255 #endif /* GAMESERVER_DB_H */