import { DbLoadCallback , IDatabase } from './IDatabase' ; import { Game , GameId , GameOptions , Score } from '../Game' ; import { IGameData } from './IDatabase' ; import { SerializedGame } from '../SerializedGame' ; import sqlite3 = require ( 'sqlite3' ) ; const path = require ( 'path' ) ; const fs = require ( 'fs' ) ; const dbFolder = path . resolve ( process . cwd ( ) , './db' ) ; const dbPath = path . resolve ( dbFolder , 'game.db' ) ; export const IN_MEMORY_SQLITE_PATH = ':memory:' ; export class SQLite implements IDatabase { protected db : sqlite3 . Database ; constructor ( filename = dbPath , private throwQuietFailures = false ) { if ( filename !== IN_MEMORY_SQLITE_PATH ) { if ( ! fs . existsSync ( dbFolder ) ) { fs . mkdirSync ( dbFolder ) ; } } this . db = new sqlite3 . Database ( filename ) ; } initialize ( ) { return new Promise ( ( resolve , reject ) => { this . db . run ( 'CREATE TABLE IF NOT EXISTS games(game_id varchar, players integer, save_id integer, game text, status text default \'running\', created_time timestamp default (strftime(\'%s\', \'now\')), PRIMARY KEY (game_id, save_id))' , ( err ) => { if ( err ) { reject ( err ) ; return ; } this . db . run ( 'CREATE TABLE IF NOT EXISTS game_results(game_id varchar not null, seed_game_id varchar, players integer, generations integer, game_options text, scores text, PRIMARY KEY (game_id))' , ( err2 ) => { if ( err2 ) { reject ( err2 ) ; return ; } this . db . run ( `
          CREATE TABLE IF NOT EXISTS purges(
            game_id varchar not null,
            last_save_id number not null,
            completed_time timestamp not null default (strftime('%s', 'now')),
            PRIMARY KEY (game_id)
          )` , ( err3 ) => { if ( err3 ) { reject ( err3 ) ; return ; } resolve ( ) ; } ) ; } ) ; } ) ; } ) ; } getClonableGames ( cb ) { const allGames = [ ] ; const sql = 'SELECT distinct game_id game_id, players players FROM games WHERE save_id = 0 order by game_id asc' ; this . db . all ( sql , [ ] , ( err , rows ) => { if ( rows ) { rows . forEach ( ( row ) => { const gameId = row . game_id ; const playerCount = row . players ; const gameData = { gameId , playerCount , } ; allGames . push ( gameData ) ; } ) ; return cb ( err ?? undefined , allGames ) ; } } ) ; } getGames ( cb ) { const allGames = [ ] ; const sql = 'SELECT distinct game_id game_id FROM games WHERE status = \'running\'' ; this . db . all ( sql , [ ] , ( err , rows ) => { if ( rows ) { rows . forEach ( ( row ) => { allGames . push ( row . game_id ) ; } ) ; } return cb ( err ?? undefined , allGames ) ; } ) ; } loadCloneableGame ( game_id , cb ) { this . db . get ( 'SELECT game_id game_id, game game FROM games WHERE game_id = ? AND save_id = 0' , [ game_id ] , ( err , row ) => { if ( row . game_id === undefined ) { return cb ( new Error ( 'Game not found' ) , undefined ) ; } try { const json = JSON . parse ( row . game ) ; return cb ( err ?? undefined , json ) ; } catch ( exception ) { console . error ( `unable to load game ${ game_id } at save point 0` , exception ) ; const error = exception instanceof Error ? exception : new Error ( String ( exception ) ) ; cb ( error , undefined ) ; return ; } } ) ; } saveGameResults ( game_id , players , generations , gameOptions , scores ) { this . db . run ( 'INSERT INTO game_results (game_id, seed_game_id, players, generations, game_options, scores) VALUES($1, $2, $3, $4, $5, $6)' , [ game_id , gameOptions . clonedGamedId , players , generations , JSON . stringify ( gameOptions ) , JSON . stringify ( scores ) ] , ( err ) => { if ( err ) { console . error ( 'SQLite:saveGameResults' , err ) ; throw err ; } } ) ; } getGame ( game_id , cb ) { this . db . get ( 'SELECT game game FROM games WHERE game_id = ? ORDER BY save_id DESC LIMIT 1' , [ game_id ] , ( err , row ) => { if ( err ) { return cb ( err ?? undefined ) ; } cb ( undefined , JSON . parse ( row . game ) ) ; } ) ; } getGameId ( playerId , cb ) { const sql = 'SELECT game_id from games, json_each(games.game, \'$.players\') e where json_extract(e.value, \'$.id\') = ?' ; this . db . get ( sql , [ playerId ] , ( err , row ) => { if ( err ) { return cb ( err ?? undefined ) ; } cb ( undefined , row . gameId ) ; } ) ; } getGameVersion ( game_id , save_id , cb ) { this . db . get ( 'SELECT game game FROM games WHERE game_id = ? and save_id = ?' , [ game_id , save_id ] , ( err , row ) => { if ( err ) { return cb ( err ?? undefined , undefined ) ; } cb ( undefined , JSON . parse ( row . game ) ) ; } ) ; } cleanSaves ( game_id , save_id ) { this . runQuietly ( 'INSERT into purges (game_id, last_save_id) values (?, ?)' , [ game_id , save_id ] ) ; this . db . run ( 'DELETE FROM games WHERE game_id = ? AND save_id < ? AND save_id > 0' , [ game_id , save_id ] , function ( err ) { if ( err ) { return console . warn ( err . message ) ; } } ) ; this . db . run ( 'UPDATE games SET status = \'finished\' WHERE game_id = ?' , [ game_id ] , function ( err ) { if ( err ) { return console . warn ( err . message ) ; } } ) ; this . purgeUnfinishedGames ( ) ; } purgeUnfinishedGames ( ) { if ( process . env . MAX_GAME_DAYS ) { this . runQuietly ( `DELETE FROM games WHERE created_time < strftime('%s',date('now', '-' || ? || ' day')) and status = 'running'` , [ process . env . MAX_GAME_DAYS ] ) ; } } restoreGame ( game_id , save_id , cb ) { this . db . get ( 'SELECT game game FROM games WHERE game_id = ? AND save_id = ? ORDER BY save_id DESC LIMIT 1' , [ game_id , save_id ] , ( err , row ) => { if ( err ) { console . error ( err . message ) ; cb ( err , undefined ) ; return ; } try { const json = JSON . parse ( row . game ) ; const game = Game . deserialize ( json ) ; cb ( undefined , game ) ; } catch ( e ) { const error = e instanceof Error ? e : new Error ( String ( e ) ) ; cb ( error , undefined ) ; } } ) ; } async saveGame ( game ) { const gameJSON = game . toJSON ( ) ; await this . runQuietly ( 'INSERT INTO games (game_id, save_id, game, players) VALUES (?, ?, ?, ?) ON CONFLICT (game_id, save_id) DO UPDATE SET game = ?' , [ game . id , game . lastSaveId , gameJSON , game . getPlayers ( ) . length , gameJSON ] ) ; game . lastSaveId ++ ; } deleteGameNbrSaves ( game_id , rollbackCount ) { if ( rollbackCount > 0 ) { this . runQuietly ( 'DELETE FROM games WHERE rowid IN (SELECT rowid FROM games WHERE game_id = ? ORDER BY save_id DESC LIMIT ?)' , [ game_id , rollbackCount ] ) ; } } runQuietly ( sql , params ) { return new Promise ( ( resolve , reject ) => { this . db . run ( sql , params , ( err ) => { if ( err ) { console . error ( err ) ; console . error ( 'for sql: ' + sql ) ; if ( this . throwQuietFailures ) { reject ( err ) ; return ; } } else { resolve ( ) ; } } ) ; } ) ; } }