import { DbLoadCallback , IDatabase } from './IDatabase' ; import { Game , GameId , GameOptions , Score } from '../Game' ; import { IGameData } from './IDatabase' ; import { SerializedGame } from '../SerializedGame' ; import { Pool , ClientConfig , QueryResult } from 'pg' ; export class PostgreSQL implements IDatabase { private client : Pool ; constructor ( ) { const config = { connectionString : process . env . POSTGRES_HOST , } ; if ( config . connectionString !== undefined && config . connectionString . startsWith ( 'postgres' ) ) { config . ssl = { rejectUnauthorized : false , } ; } this . client = new Pool ( config ) ; this . client . query ( 'CREATE TABLE IF NOT EXISTS games(game_id varchar, players integer, save_id integer, game text, status text default \'running\', created_time timestamp default now(), PRIMARY KEY (game_id, save_id))' , ( err ) => { if ( err ) { throw err ; } } ) ; this . client . query ( '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))' , ( err ) => { if ( err ) { throw err ; } } ) ; this . client . query ( 'CREATE INDEX IF NOT EXISTS games_i1 on games(save_id)' , ( err ) => { if ( err ) { throw err ; } } ) ; this . client . query ( 'CREATE INDEX IF NOT EXISTS games_i2 on games(created_time )' , ( err ) => { if ( err ) { throw err ; } } ) ; } async initialize ( ) { } 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 . client . query ( sql , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:getClonableGames' , err ) ; cb ( err , [ ] ) ; return ; } for ( const row of res . rows ) { const gameId = row . game_id ; const playerCount = row . players ; const gameData = { gameId , playerCount , } ; allGames . push ( gameData ) ; } cb ( undefined , allGames ) ; } ) ; } getGames ( cb ) { const allGames = [ ] ; const sql = 'SELECT games.game_id FROM games, (SELECT max(save_id) save_id, game_id FROM games WHERE status=\'running\' GROUP BY game_id) a WHERE games.game_id = a.game_id AND games.save_id = a.save_id ORDER BY created_time DESC' ; this . client . query ( sql , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:getGames' , err ) ; cb ( err , [ ] ) ; return ; } for ( const row of res . rows ) { allGames . push ( row . game_id ) ; } cb ( undefined , allGames ) ; } ) ; } loadCloneableGame ( game_id , cb ) { this . client . query ( 'SELECT game_id game_id, game game FROM games WHERE game_id = $1 AND save_id = 0' , [ game_id ] , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:restoreReferenceGame' , err ) ; return cb ( err , undefined ) ; } if ( res . rows . length === 0 ) { return cb ( new Error ( `Game ${ game_id } not found` ) , undefined ) ; } try { const json = JSON . parse ( res . rows [ 0 ] . game ) ; return cb ( undefined , json ) ; } catch ( exception ) { const error = exception instanceof Error ? exception : new Error ( String ( exception ) ) ; console . error ( `Unable to restore game ${ game_id }` , error ) ; cb ( error , undefined ) ; return ; } } ) ; } getGame ( game_id , cb ) { this . client . query ( 'SELECT game game FROM games WHERE game_id = $1 ORDER BY save_id DESC LIMIT 1' , [ game_id ] , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:getGame' , err ) ; return cb ( err ) ; } if ( res . rows . length === 0 ) { return cb ( new Error ( 'Game not found' ) ) ; } cb ( undefined , JSON . parse ( res . rows [ 0 ] . game ) ) ; } ) ; } getGameId ( playerId , cb ) { const sql = `SELECT game_id
      FROM games, json_array_elements(CAST(game AS JSON)->'players') AS e
      WHERE save_id = 0 AND e->>'id' = $1` ; this . client . query ( sql , [ playerId ] , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:getGameId' , err ) ; return cb ( err ?? undefined ) ; } if ( res . rowCount === 0 ) { return cb ( new Error ( 'Game not found' ) ) ; } const gameId = res . rows [ 0 ] . game_id ; cb ( undefined , gameId ) ; } ) ; } getGameVersion ( game_id , save_id , cb ) { this . client . query ( 'SELECT game game FROM games WHERE game_id = $1 and save_id = $2' , [ game_id , save_id ] , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:getGameVersion' , err ) ; return cb ( err , undefined ) ; } cb ( undefined , JSON . parse ( res . rows [ 0 ] . game ) ) ; } ) ; } saveGameResults ( game_id , players , generations , gameOptions , scores ) { this . client . query ( '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 , gameOptions , JSON . stringify ( scores ) ] , ( err ) => { if ( err ) { console . error ( 'PostgreSQL:saveGameResults' , err ) ; throw err ; } } ) ; } cleanSaves ( game_id , save_id ) { this . client . query ( 'DELETE FROM games WHERE game_id = $1 AND save_id < $2 AND save_id > 0' , [ game_id , save_id ] , ( err ) => { if ( err ) { console . error ( 'PostgreSQL:cleanSaves' , err ) ; throw err ; } this . client . query ( 'UPDATE games SET status = \'finished\' WHERE game_id = $1' , [ game_id ] , ( err2 ) => { if ( err2 ) { console . error ( 'PostgreSQL:cleanSaves2' , err2 ) ; throw err2 ; } } ) ; } ) ; this . purgeUnfinishedGames ( ) ; } purgeUnfinishedGames ( ) { const envDays = parseInt ( process . env . MAX_GAME_DAYS || '' ) ; const days = Number . isInteger ( envDays ) ? envDays : 10 ; this . client . query ( 'DELETE FROM games WHERE created_time < now() - interval \'1 day\' * $1' , [ days ] , function ( err ? , res ? ) { if ( res ) { console . log ( `Purged ${ res . rowCount } rows` ) ; } if ( err ) { return console . warn ( err . message ) ; } } ) ; } restoreGame ( game_id , save_id , cb ) { this . client . query ( 'SELECT game game FROM games WHERE game_id = $1 AND save_id = $2 ORDER BY save_id DESC LIMIT 1' , [ game_id , save_id ] , ( err , res ) => { if ( err ) { console . error ( 'PostgreSQL:restoreGame' , err ) ; cb ( err , undefined ) ; return ; } if ( res . rows . length === 0 ) { console . error ( 'PostgreSQL:restoreGame' , `Game ${ game_id } not found` ) ; cb ( err , undefined ) ; return ; } try { const json = JSON . parse ( res . rows [ 0 ] . game ) ; const game = Game . deserialize ( json ) ; cb ( undefined , game ) ; } catch ( e ) { const error = e instanceof Error ? e : new Error ( String ( e ) ) ; cb ( error , undefined ) ; } } ) ; } saveGame ( game ) { const gameJSON = game . toJSON ( ) ; this . client . query ( 'INSERT INTO games (game_id, save_id, game, players) VALUES ($1, $2, $3, $4) ON CONFLICT (game_id, save_id) DO UPDATE SET game = $3' , [ game . id , game . lastSaveId , gameJSON , game . getPlayers ( ) . length ] , ( err ) => { if ( err ) { console . error ( 'PostgreSQL:saveGame' , err ) ; return ; } } ) ; game . lastSaveId ++ ; return Promise . resolve ( ) ; } deleteGameNbrSaves ( game_id , rollbackCount ) { if ( rollbackCount > 0 ) { this . client . query ( 'DELETE FROM games WHERE ctid IN (SELECT ctid FROM games WHERE game_id = $1 ORDER BY save_id DESC LIMIT $2)' , [ game_id , rollbackCount ] , ( err ) => { if ( err ) { return console . warn ( err . message ) ; } } ) ; } } }