path: root/core/src/ch/asynk/rustanddust/util/DB.java

package ch.asynk.rustanddust.util;

import java.util.Date;
import java.text.SimpleDateFormat;
import java.security.MessageDigest;
import java.math.BigInteger;

import com.badlogic.gdx.sql.Database;
import com.badlogic.gdx.sql.DatabaseCursor;
import com.badlogic.gdx.sql.DatabaseFactory;
import com.badlogic.gdx.sql.SQLiteGdxException;

import ch.asynk.rustanddust.RustAndDust;
import ch.asynk.rustanddust.game.Config.GameMode;

public class DB
{
    private static final int DB_SCHEMA_VERSION = 2;

    public static final int NO_RECORD = -1;
    private static final String DIGEST = "SHA-256";
    private static final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private boolean debug;
    private Database db;
    private MessageDigest md;

    private static final String TBL_CFG_CRT = "create table if not exists"
        + " config ( key text primary key, value text not null);";

    private static final String TBL_PLAYERS_CRT = "create table if not exists"
        + " players ( _id integer primary key autoincrement"
        + " ,hash text unique not null, gmail text unique not null, name text not null"
        + ");";

    private static final String TBL_BATTLES_CRT = "create table if not exists"
        + " battles ( _id integer primary key, name text"
        + ");";

    private static final String TBL_GAMES_CRT = "create table if not exists"
        + " games ( _id integer primary key autoincrement"
        + " ,mode integer not null, battle integer not null, opponent integer not null"
        + " ,turn integer not null, currentPlayer integer default null"
        + " ,ts datetime default current_timestamp, synched integer default 0"
        + " ,players text default null, playersH text default null"
        + " ,map text default null, mapH text default null"
        + " ,orders text default null, ordersH text default null"
        + " ,foreign key (battle) references battles(_id)"
        + " ,foreign key (opponent) references players(_id)"
        + " ,foreign key (currentPlayer) references players(_id)"
        + " unique (mode, battle, opponent)"
        + ");";

    private static final String TBL_TURNS_CRT = "create table if not exists"
        + " turns ( _id integer primary key autoincrement"
        + " ,game integer not null, turn integer not null, currentPlayer integer not null"
        + " ,players text default null, playersH text default null"
        + " ,map text default null, mapH text default null"
        + " ,orders text default null, ordersH text default null"
        + " ,foreign key (game) references games(_id)"
        + " ,foreign key (currentPlayer) references players(_id)"
        + " unique (game, turn)"
        + ");";

    private static final String FEED_CONFIG = " insert or ignore into config values(\"version\", " + DB_SCHEMA_VERSION + ");";

    public DB(String dbPath, boolean debug)
    {
        this.db = DatabaseFactory.getNewDatabase(dbPath, DB_SCHEMA_VERSION, null, null);
        this.db.setupDatabase();
        this.debug = debug;
    }

    public void setup()
    {
        try {
            md = MessageDigest.getInstance(DIGEST);
        } catch (java.security.NoSuchAlgorithmException e) { RustAndDust.error("NoSuchAlgorithm"); }

        try {
            db.openOrCreateDatabase();
        } catch (SQLiteGdxException e) { RustAndDust.error("openOrCreateDatabase"); }

        Boolean version = checkVersion();
        if(version == null)
            createTables();
        else if (version == false) {
            dropTables();
            createTables();
        }
    }

    private void createTables()
    {
        try {
            exec(TBL_CFG_CRT);
            exec(TBL_PLAYERS_CRT);
            exec(TBL_BATTLES_CRT);
            exec(TBL_GAMES_CRT);
            exec(TBL_TURNS_CRT);
            exec(FEED_CONFIG);
        } catch (SQLiteGdxException e) {
            RustAndDust.error("table creation error " + e.getMessage());
        }
    }

    private void dropTables()
    {
        try {
            exec("drop table if exists turns");
            exec("drop table if exists states");
            exec("drop table if exists games");
            exec(String.format("update config set value=%d where key='version';", DB_SCHEMA_VERSION));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("table creation error " + e.getMessage());
        }
    }

    public String getDigest(String str)
    {
        String hash = null;
        try {
            hash = new BigInteger(1, md.digest(str.getBytes("UTF-8"))).toString(16);
        } catch (java.io.UnsupportedEncodingException e) { RustAndDust.error("getDigest"); }

        return hash;
    }

    private boolean checkDigest(String what, int id, String payload, String digest)
    {
        if (payload == null) return true;
        if (digest.equals(getDigest(payload)))
            return true;
        RustAndDust.error(String.format("corrupted %s(%d)", what, id));
        return false;
    }

    private static final String CHECK_VERSION = "select (value=%d) from config where key='version';";

    public Boolean checkVersion()
    {
        Boolean ret = false;
        try {
            DatabaseCursor cursor = query(String.format(CHECK_VERSION, DB_SCHEMA_VERSION ));
            if (cursor.getCount() > 0) {
                cursor.next();
                ret = (cursor.getInt(0) == 1);
            }
        } catch (SQLiteGdxException e) {
            RustAndDust.error("checkVersion");
            return null;
        }
        return ret;
    }

    private static final String INSERT_CONFIG = "insert or replace into config(key, value) values ('config','%s');";

    public boolean storeConfig(String config)
    {
        try {
            exec(String.format(INSERT_CONFIG, config));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storeConfig");
            return false;
        }
        return true;
    }

    private static final String GET_CONFIG = "select value from config where key='config';";

    public String loadConfig()
    {
        String ret = null;
        try {
            DatabaseCursor cursor = query(GET_CONFIG);
            if (cursor.getCount() > 0) {
                cursor.next();
                ret = cursor.getString(0);
            }
        } catch (SQLiteGdxException e) { RustAndDust.error("loadConfig"); }
        return ret;
    }

    public void storePlayer(String gmail, String name)
    {
        String hash = getDigest(String.format("#%s@%s*", gmail, df.format(new Date())));
        if (hash != null)
            storePlayer(gmail, name, hash);
    }

    private static final String INSERT_PLAYER = "insert or ignore into players(hash,gmail,name) values ('%s','%s','%s');";

    public void storePlayer(String gmail, String name, String hash)
    {
        try {
            exec(String.format(INSERT_PLAYER, hash, gmail, name));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storePlayer");
        }
    }

    private static final String GET_PLAYER_ID_FROM_HASH = "select _id from players where hash='%s';";
    private static final String GET_PLAYER_ID_FROM_GMAIL = "select _id from players where gmail='%s';";

    public int getPlayerId(boolean hash, String s)
    {
        int ret = NO_RECORD;
        String sql = (hash ? GET_PLAYER_ID_FROM_HASH : GET_PLAYER_ID_FROM_GMAIL);
        try {
            DatabaseCursor cursor = query(String.format(sql, s));
            if (cursor.getCount() > 0) {
                cursor.next();
                ret = cursor.getInt(0);
            }
        } catch (SQLiteGdxException e) { RustAndDust.error("getPlayerId"); }
        return ret;
    }

    public int storePlayerGetId(String gmail, String name)
    {
        storePlayer(gmail, name);
        return getPlayerId(false, gmail);
    }

    private static final String UPDATE_BATTLE = "insert or replace into battles values (%d,'%s');";

    public void storeBattle(int id, String name)
    {
        try {
            exec(String.format(UPDATE_BATTLE, id, name));
        } catch (SQLiteGdxException e) { RustAndDust.error("storeBattle"); }
    }

    private static final String INSERT_GAME = "insert or ignore into games(opponent,battle,mode,turn) values (%d,%d,%d,0);";

    public void storeGame(int opponent, int battle, int mode)
    {
        try {
            exec(String.format(INSERT_GAME, opponent, battle, mode));
        } catch (SQLiteGdxException e) { RustAndDust.error("storeGame"); }
    }

    private static final String GET_GAME_ID = "select _id from games where opponent=%d and battle=%d and mode=%d;";

    public int getGameId(int opponent, int battle, int mode)
    {
        int ret = NO_RECORD;
        try {
            DatabaseCursor cursor = query(String.format(GET_GAME_ID, opponent, battle, mode));
            if (cursor.getCount() > 0) {
                cursor.next();
                ret = cursor.getInt(0);
            }
        } catch (SQLiteGdxException e) { RustAndDust.error("getGameId"); }
        return ret;
    }

    public int storeGameGetId(int opponent, int battle, int mode)
    {
        storeGame(opponent, battle, mode);
        return getGameId(opponent, battle, mode);
    }

    private static final String STORE_GAME_STATE = "update games set ts=current_timestamp, turn=%d, currentPlayer=%d,"
        + " players='%s', playersH='%s', map='%s', mapH='%s', synched=1 where _id=%d;";

    public boolean storeGameState(int game, int turn, int player, String players, String map)
    {
        RustAndDust.debug("storeGameState");
        try {
            String playersH = getDigest(players);
            if (playersH == null) return false;
            String mapH = getDigest(map);
            if (mapH == null) return false;
            exec(String.format(STORE_GAME_STATE, turn, player, players, playersH, map, mapH, game));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storeGameState");
            return false;
        }
        return true;
    }

    private static final String STORE_GAME_ORDERS = "update games set ts=current_timestamp, orders='%s', ordersH='%s', synched=0 where _id=%d;";

    public boolean storeGameOrders(int game, String orders)
    {
        RustAndDust.debug("storeGameOrders");
        try {
            String ordersH = getDigest(orders);
            if (ordersH == null) return false;
            exec(String.format(STORE_GAME_ORDERS, orders, ordersH, game));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storeGameOrders");
            return false;
        }
        return true;
    }

    private static final String STORE_TURN_ORDERS = "update turns set orders=(select orders from games where _id=%d), ordersH=(select ordersH from games where _id=%d)"
        + " where game=%d and turn =%d;";

    public boolean storeTurnOrders(int game, int turn, String orders)
    {
        RustAndDust.debug("storeTurnOrders");
        try {
            String ordersH = getDigest(orders);
            if (ordersH == null) return false;
            exec(String.format(STORE_TURN_ORDERS, game, game, game, turn));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storeTurnOrders");
            return false;
        }
        return true;
    }

    private static final String STORE_TURN_STATE = "insert into turns(game, turn, currentPlayer, players, playersH, map, mapH, orders, ordersH)"
        + " select _id, turn, currentPlayer, players, playersH, map, mapH, null, null from games where _id=%d;";

    public boolean storeTurnState(int game)
    {
        RustAndDust.debug("storeTurnState");
        try {
            exec(String.format(STORE_TURN_STATE, game));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("storeTurnState");
            return false;
        }
        return true;
    }

    public boolean clearGameOrders(int game)
    {
        RustAndDust.debug("clearGameOrders");
        try {
            exec(String.format("update games set orders=null, ordersH=null, synched=1 where _id=%d;", game));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("clearGameOrders");
            return false;
        }
        return true;
    }

    private static final String LOAD_BASE = "select g._id, g.mode, g.battle, g.opponent, g.turn, g.currentPlayer, g.ts, g.synched";

    private static final String LOAD_GAMES = LOAD_BASE + ", null, null, null, null, null, null, p.name, b.name"
        + " from games g inner join players p on (p._id=g.opponent) inner join battles b on (b._id=g.battle) where g.mode=%d;";

    public void loadGames(int mode)
    {
        RustAndDust.debug("loadGames");
        GameRecord.clearList();
        try {
            DatabaseCursor cursor = query(String.format(LOAD_GAMES, mode));
            if (cursor.getCount() > 0) {
                while(cursor.next()) {
                    GameRecord r = gameRecordFrom(cursor);
                    if (r != null)
                        GameRecord.list.add(r);
                }
            }
        } catch (SQLiteGdxException e) { RustAndDust.error("loadGames"); }
    }

    private static final String LOAD_GAME = LOAD_BASE + ", g.players, g.playersH, g.map, g.mapH, g.orders, g.ordersH, null, null from games g where g._id=%d;";

    public GameRecord loadGame(int game)
    {
        RustAndDust.debug("loadGame");
        return loadGame(game, String.format(LOAD_GAME, game), "loadGAME");
    }

    private static final String LOAD_LAST_TURN = "select g._id, g.mode, g.battle, g.opponent, g.turn, g.currentPlayer, g.ts, g.synched"
        + ", t.players, t.playersH, t.map, t.mapH, g.orders, g.ordersH, null, null"
        +" from games g inner join turns t on (g._id=t.game and t.turn=g.turn) where g._id=%d;";

    public GameRecord loadLastTurn(int game)
    {
        RustAndDust.debug("loadLastTurn");
        return loadGame(game, String.format(LOAD_LAST_TURN, game), "loadLastTurn");
    }

    private static final String LOAD_TURN = "select g._id, g.mode, g.battle, g.opponent, t.turn, t.currentPlayer, g.ts, g.synched"
        + ", t.players, t.playersH, t.map, t.mapH, case when g.turn=t.turn then g.orders else t.orders end, case when g.turn=t.turn then g.ordersH else t.ordersH end, null, null"
        +" from games g inner join turns t on (g._id = t.game) where g._id=%d and t.turn = %d;";

    public GameRecord loadTurn(int game, int turn)
    {
        RustAndDust.debug("loadTurn");
        return loadGame(game, String.format(LOAD_TURN, game, turn), "loadTurn", false);
    }

    private GameRecord loadGame(int game, String sql, String errMsg)
    {
        return loadGame(game, sql, errMsg, true);
    }

    private GameRecord loadGame(int game, String sql, String errMsg, boolean deleteOnError)
    {
        GameRecord r = null;
        try {
            DatabaseCursor cursor = query(sql);
            if (cursor.getCount() > 0) {
                cursor.next();
                r = gameRecordFrom(cursor);
                if (!checkDigest("GameState", game, r.players, r.playersH))
                    r = null;
                if (!checkDigest("GameState", game, r.map, r.mapH))
                    r = null;
                else if(!checkDigest("GameOrders", game, r.orders, r.ordersH))
                    r = null;
            }
        } catch (SQLiteGdxException e) { RustAndDust.error(errMsg); }
        if (deleteOnError && (r == null))
            deleteGame(game);
        return r;
    }

    private static final String DELETE_GAME = "delete from games where _id=%d;";
    private static final String DELETE_TURNS = "delete from turns where game=%d;";

    public boolean deleteGame(int game)
    {
        RustAndDust.debug("deleteGame");
        try {
            exec(String.format(DELETE_TURNS, game));
            exec(String.format(DELETE_GAME, game));
        } catch (SQLiteGdxException e) {
            RustAndDust.error("deleteGame");
            return false;
        }
        return true;

    }

    private GameRecord gameRecordFrom(DatabaseCursor cursor)
    {
        GameRecord r = GameRecord.get();

        try {
            r.id = cursor.getInt(0);
            r.mode = GameMode.from(cursor.getInt(1));
            r.battle = cursor.getInt(2);
            r.opponent = cursor.getInt(3);
            r.turn = cursor.getInt(4);
            r.currentPlayer = cursor.getInt(5);
            try { r.ts = df.parse(cursor.getString(6)); }
            catch (java.text.ParseException e) {
                r.ts = null;
                RustAndDust.error(String.format("can't parse", cursor.getString(6)));
            }
            r.synched = ((cursor.getInt(7) == 1) ? true : false);
            r.players = cursor.getString(8);
            r.playersH = cursor.getString(9);
            r.map = cursor.getString(10);
            r.mapH = cursor.getString(11);
            r.orders = cursor.getString(12);
            r.ordersH = cursor.getString(13);
            //
            r.oName = cursor.getString(14);
            r.bName = cursor.getString(15);
        } catch (Exception e) {
            r.dispose(); RustAndDust.error("GameRecord from cursor");
        }

        return r;
    }

    private void exec(String sql) throws SQLiteGdxException
    {
        if (debug) RustAndDust.debug(" SQL " + sql);
        db.execSQL(sql);
    }

    private DatabaseCursor query(String sql) throws SQLiteGdxException
    {
        if (debug) RustAndDust.debug(" SQL " + sql);
        DatabaseCursor c = db.rawQuery(sql);
        if (debug) RustAndDust.debug(String.format(" SQL  -> %d", c.getCount()));
        return c;
    }
}