From 1a1bddf6bf5de71604771ccf7a11ddab4ab7ceb5 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?J=C3=A9r=C3=A9my=20Zurcher?= Date: Tue, 22 Mar 2016 15:09:40 +0100 Subject: SQL: schema rewrite --- core/src/ch/asynk/rustanddust/game/ctrl/Solo.java | 29 +++- core/src/ch/asynk/rustanddust/util/DB.java | 185 ++++++++++----------- core/src/ch/asynk/rustanddust/util/GameRecord.java | 14 +- core/src/ch/asynk/rustanddust/util/TurnRecord.java | 72 -------- 4 files changed, 121 insertions(+), 179 deletions(-) delete mode 100644 core/src/ch/asynk/rustanddust/util/TurnRecord.java diff --git a/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java b/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java index 77bcc23..d7cda5c 100644 --- a/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java +++ b/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java @@ -27,37 +27,50 @@ public class Solo extends Ctrl battle.initialDeployment(); } else { GameRecord r = game.db.loadGame(gameId); - battle.load(Marshal.Mode.FULL, r.turn, r.payload); - r.dispose(); + if (r != null) { + battle.load(Marshal.Mode.STATE, r.turn, r.state); + battle.load(Marshal.Mode.ORDERS, r.turn, r.orders); + battle.getMap().clearMarshalUnits(); + r.dispose(); + } else + System.err.println("TODO : null GameRecord"); } } @Override public void orderProcessedCb() { - // TODO + // FIXME what about multible orders actions, HQ actiovations + if (!isLoading()) + storeOrders(); } @Override protected void actionDoneCb() { - storeGame(); + storeState(); } @Override protected void turnDoneCb() { - storeGame(); + storeState(); + storeOrders(); storeTurn(); } - private void storeGame() + private void storeState() { - game.db.storeGame(gameId, battle.getTurnCount(), battle.getPlayer().id, battle.unload(Marshal.Mode.FULL)); + game.db.storeGameState(gameId, battle.getTurnCount(), battle.getPlayer().id, battle.unload(Marshal.Mode.STATE)); + } + + private void storeOrders() + { + game.db.storeGameOrders(gameId, battle.getTurnCount(), battle.getPlayer().id, battle.unload(Marshal.Mode.ORDERS)); } private void storeTurn() { - game.db.storeTurn(gameId); + game.db.storeLastTurn(gameId); } } diff --git a/core/src/ch/asynk/rustanddust/util/DB.java b/core/src/ch/asynk/rustanddust/util/DB.java index df2a64c..4492591 100644 --- a/core/src/ch/asynk/rustanddust/util/DB.java +++ b/core/src/ch/asynk/rustanddust/util/DB.java @@ -39,21 +39,24 @@ public class DB private static final String TBL_GAMES_CRT = "create table if not exists" + " games ( _id integer primary key autoincrement" - + " ,opponent integer not null, battle integer not null, mode integer not null" - + " ,turn integer not null, ts datetime default current_timestamp" - + " ,player integer default null, hash text default null, payload text default null" - + " ,foreign key (opponent) references players(_id)" + + " ,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" + + " ,state text default null, stateH text default null" + + " ,orders text default null, ordersH text default null" + " ,foreign key (battle) references battles(_id)" - + " ,foreign key (player) references players(_id)" - + " unique (opponent, battle, mode)" + + " ,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, player integer not null" - + " ,hash text not null, payload text not null" + + " ,game integer not null, turn integer not null, currentPlayer integer not null" + + " ,state text default null, stateH text default null" + + " ,orders text default null, ordersH text default null" + " ,foreign key (game) references games(_id)" - + " ,foreign key (player) references players(_id)" + + " ,foreign key (currentPlayer) references players(_id)" + " unique (game, turn)" + ");"; @@ -255,86 +258,113 @@ public class DB return getGameId(opponent, battle, mode); } - private static final String COPY_TURN = "insert into turns(game,turn,player,hash,payload) select _id, turn, player, hash, payload from games where _id=%d;"; + private static final String STORE_GAME_STATE = "update games set ts=current_timestamp, turn=%d, currentPlayer=%d, state='%s', stateH='%s', synched=1 where _id=%d;"; - public boolean storeTurn(int game) + public boolean storeGameState(int game, int turn, int player, String state) { + RustAndDust.debug("storeStateGame"); try { - exec(String.format(COPY_TURN, game)); + String hash = getDigest(state); + if (hash == null) return false; + exec(String.format(STORE_GAME_STATE, turn, player, state, hash, game)); } catch (SQLiteGdxException e) { - RustAndDust.error("storeTurn"); + RustAndDust.error("storeStateGame"); return false; } return true; } - private static final String INSERT_TURN = "insert into turns(game,turn,player,hash,payload) values (%d,%d,%d,'%s','%s');"; + private static final String STORE_GAME_ORDERS = "update games set ts=current_timestamp, turn=%d, currentPlayer=%d, orders='%s', ordersH='%s', synched=0 where _id=%d;"; - public boolean storeTurn(int game, int turn, int player, String payload) + public boolean storeGameOrders(int game, int turn, int player, String orders) { + RustAndDust.debug("storeGameOrders"); try { - String hash = getDigest(payload); + String hash = getDigest(orders); if (hash == null) return false; - exec(String.format(INSERT_TURN, game, turn, player, hash, payload)); + exec(String.format(STORE_GAME_ORDERS, turn, player, orders, hash, game)); + } catch (SQLiteGdxException e) { + RustAndDust.error("storeGameOrders"); + return false; + } + return true; + } + + private static final String COPY_TURN = "insert into turns(game, turn, currentPlayer, state, stateH, orders, ordersH)" + + " select _id, turn, currentPlayer, state, stateH, orders, ordersH from games where _id=%d;"; + + public boolean storeLastTurn(int game) + { + try { + exec(String.format(COPY_TURN, game)); } catch (SQLiteGdxException e) { - RustAndDust.error("storeTurn"); + RustAndDust.error("storeLastTurn"); return false; } return true; } - private static final String GET_TURNS = "select payload from turns where game=%d order by _id;"; + private static final String LOAD_BASE = "select g._id, g.mode, g.battle, g.opponent, g.turn, g.currentPlayer, g.ts, g.synched"; - public void loadTurns(int game) + private static final String LOAD_GAMES = LOAD_BASE + ", 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);"; + + public void loadGames() { - RustAndDust.debug("loadTurns"); - TurnRecord.clearList(); + RustAndDust.debug("loadGames"); + GameRecord.clearList(); try { - DatabaseCursor cursor = query(GET_GAMES); + DatabaseCursor cursor = query(LOAD_GAMES); if (cursor.getCount() > 0) { while(cursor.next()) { - TurnRecord r = turnFrom(cursor); + GameRecord r = gameRecordFrom(cursor); if (r != null) - TurnRecord.list.add(r); + GameRecord.list.add(r); } } - } catch (SQLiteGdxException e) { RustAndDust.error("loadTurns"); } + } catch (SQLiteGdxException e) { RustAndDust.error("loadGames"); } } - private static final String UPDATE_GAME = "update games set ts=current_timestamp, turn=%d, player=%d, hash='%s', payload='%s' where _id=%d;"; + private static final String LOAD_GAME = LOAD_BASE + ", g.state, g.stateH, g.orders, g.ordersH, null, null from games g where g._id=%d;"; - public boolean storeGame(int game, int turn, int player, String payload) + public GameRecord loadGame(int game) { - RustAndDust.debug("storeGame"); + RustAndDust.debug("loadGame"); + GameRecord r = null; try { - String hash = getDigest(payload); - if (hash == null) return false; - exec(String.format(UPDATE_GAME, turn, player, hash, payload, game)); - } catch (SQLiteGdxException e) { - RustAndDust.error("storeGame"); - return false; - } - return true; + DatabaseCursor cursor = query(String.format(LOAD_GAME, game)); + if (cursor.getCount() > 0) { + cursor.next(); + r = gameRecordFrom(cursor); + if (!checkDigest("GameState", game, r.state, r.stateH)) + r = null; + else if(!checkDigest("GameOrders", game, r.orders, r.ordersH)) + r = null; + } + } catch (SQLiteGdxException e) { RustAndDust.error("loadGame"); } + if (r == null) + deleteGame(game); + return r; } - private static final String LOAD_GAME = "select g._id, g.opponent, g.battle, g.mode, g.turn, g.ts, g.player, '', '', g.hash, g.payload" - + " from games g where g._id=%d;"; + private static final String LOAD_LAST_TURN = "select g._id, g.mode, g.battle, g.opponent, t.turn, t.currentPlayer, g.ts, 0" + + ", t.state, t.stateH, g.orders, g.ordersH, null, null from games g inner join turns t on (g._id = t.game) where g._id=%d order by t.turn desc limit 1;"; - public GameRecord loadGame(int game) + public GameRecord loadLastTurn(int game) { - RustAndDust.debug("loadGame"); + RustAndDust.debug("loadLastTurn"); GameRecord r = null; try { - DatabaseCursor cursor = query(String.format(LOAD_GAME, game)); + DatabaseCursor cursor = query(String.format(LOAD_LAST_TURN, game)); if (cursor.getCount() > 0) { cursor.next(); - r = gameFrom(cursor); - if (!r.hash.equals(getDigest(r.payload))) { - RustAndDust.error(String.format("corrupted game %d", game)); + r = gameRecordFrom(cursor); + if (!checkDigest("GameState", game, r.state, r.stateH)) + r = null; + else if(!checkDigest("GameOrders", game, r.orders, r.ordersH)) r = null; - } } - } catch (SQLiteGdxException e) { RustAndDust.error("loadGame"); } + } catch (SQLiteGdxException e) { RustAndDust.error("loadLastTurn"); } if (r == null) deleteGame(game); return r; @@ -357,45 +387,30 @@ public class DB } - private static final String GET_GAMES = "select g._id, g.opponent, g.battle, g.mode, g.turn, g.ts, g.player, p.name, b.name, null, null" - + " from games g inner join players p on (p._id=g.opponent) inner join battles b on (b._id=g.battle);"; - - public void loadGames() - { - RustAndDust.debug("loadGames"); - GameRecord.clearList(); - try { - DatabaseCursor cursor = query(GET_GAMES); - if (cursor.getCount() > 0) { - while(cursor.next()) { - GameRecord r = gameFrom(cursor); - if (r != null) - GameRecord.list.add(r); - } - } - } catch (SQLiteGdxException e) { RustAndDust.error("loadGames"); } - } - - private GameRecord gameFrom(DatabaseCursor cursor) + private GameRecord gameRecordFrom(DatabaseCursor cursor) { GameRecord r = GameRecord.get(); try { r.id = cursor.getInt(0); - r.opponent = cursor.getInt(1); + r.mode = GameMode.from(cursor.getInt(1)); r.battle = cursor.getInt(2); - r.mode = GameMode.from(cursor.getInt(3)); + r.opponent = cursor.getInt(3); r.turn = cursor.getInt(4); - try { r.ts = df.parse(cursor.getString(5)); } + 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(5))); + RustAndDust.error(String.format("can't parse", cursor.getString(6))); } - r.currentPlayer = cursor.getInt(6); - r.oName = cursor.getString(7); - r.bName = cursor.getString(8); - r.hash = cursor.getString(9); - r.payload = cursor.getString(10); + r.synched = ((cursor.getInt(7) == 1) ? true : false); + r.state = cursor.getString(8); + r.stateH = cursor.getString(9); + r.orders = cursor.getString(10); + r.ordersH = cursor.getString(11); + // + r.oName = cursor.getString(12); + r.bName = cursor.getString(13); } catch (Exception e) { r.dispose(); RustAndDust.error("GameRecord from cursor"); } @@ -403,24 +418,6 @@ public class DB return r; } - private TurnRecord turnFrom(DatabaseCursor cursor) - { - TurnRecord r = TurnRecord.get(); - - try { - r.id = cursor.getInt(0); - r.game = cursor.getInt(1); - r.turn = cursor.getInt(2); - r.player = cursor.getInt(3); - r.hash = cursor.getString(4); - r.payload = cursor.getString(5); - } catch (Exception e) { - r.dispose(); RustAndDust.error("TurnRecord from cursor"); - } - - return r; - } - private void exec(String sql) throws SQLiteGdxException { if (debug) RustAndDust.debug(" SQL " + sql); diff --git a/core/src/ch/asynk/rustanddust/util/GameRecord.java b/core/src/ch/asynk/rustanddust/util/GameRecord.java index 8a1a4eb..5693a3e 100644 --- a/core/src/ch/asynk/rustanddust/util/GameRecord.java +++ b/core/src/ch/asynk/rustanddust/util/GameRecord.java @@ -14,17 +14,21 @@ import ch.asynk.rustanddust.engine.util.IterableArray; public class GameRecord implements List.ListElement, Disposable, Pool.Poolable { public int id; - public int opponent; + public GameMode mode; public int battle; + public int opponent; public int turn; - public GameMode mode; - public Date ts; public int currentPlayer; + public Date ts; + public boolean synched; + public String state; + public String stateH; + public String orders; + public String ordersH; + // public String oName; public String bName; public String s; - public String hash; - public String payload; public static Collection list = new IterableArray(10); diff --git a/core/src/ch/asynk/rustanddust/util/TurnRecord.java b/core/src/ch/asynk/rustanddust/util/TurnRecord.java deleted file mode 100644 index e7ed117..0000000 --- a/core/src/ch/asynk/rustanddust/util/TurnRecord.java +++ /dev/null @@ -1,72 +0,0 @@ -package ch.asynk.rustanddust.util; - -import com.badlogic.gdx.utils.Pool; -import com.badlogic.gdx.utils.Disposable; - -import ch.asynk.rustanddust.ui.List; -import ch.asynk.rustanddust.engine.util.Collection; -import ch.asynk.rustanddust.engine.util.IterableArray; - -public class TurnRecord implements List.ListElement, Disposable, Pool.Poolable -{ - public int id; - public int game; - public int turn; - public int player; - public String hash; - public String payload; - - public static Collection list = new IterableArray(10); - - private static final Pool turnRecordPool = new Pool() - { - @Override - protected TurnRecord newObject() { - return new TurnRecord(); - } - }; - - public static void clearPool() - { - turnRecordPool.clear(); - } - - public static TurnRecord get() - { - TurnRecord r = turnRecordPool.obtain(); - return r; - } - - public static TurnRecord get(int idx) - { - return (TurnRecord) list.get(idx); - } - - public static void clearList() - { - for(List.ListElement r : list) - ((TurnRecord) r).dispose(); - list.clear(); - } - - public TurnRecord() - { - } - - @Override - public void reset() - { - } - - @Override - public void dispose() - { - turnRecordPool.free(this); - } - - @Override - public String s() - { - return String.format("turn(id): g:%d t:%d p:%d", id, game, turn, player); - } -} -- cgit v1.1-2-g2b99