From 749e3221b45cd0696a5f48551be696e75b5b6e55 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?J=C3=A9r=C3=A9my=20Zurcher?= <jeremy@asynk.ch>
Date: Sat, 12 Mar 2016 13:27:50 +0100
Subject: SQL: update schema
---
core/src/ch/asynk/rustanddust/game/ctrl/Solo.java | 16 +-
.../src/ch/asynk/rustanddust/menu/NewGameMenu.java | 2 +-
core/src/ch/asynk/rustanddust/menu/PlayMenu.java | 2 +-
core/src/ch/asynk/rustanddust/util/DB.java | 195 +++++++++++----------
core/src/ch/asynk/rustanddust/util/GameRecord.java | 29 ++-
5 files changed, 123 insertions(+), 121 deletions(-)
diff --git a/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java b/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java
index bd9f74c..51a7172 100644
--- a/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java
+++ b/core/src/ch/asynk/rustanddust/game/ctrl/Solo.java
@@ -20,34 +20,34 @@ public class Solo extends Ctrl
if (gameId == game.db.NO_RECORD) {
int me = game.backend.getMyId();
int other = game.backend.getOpponentId();
- gameId = game.db.storeGameGetId(me, other, battle.getId(), game.config.gameMode.i);
+ gameId = game.db.storeGameGetId(other, battle.getId(), game.config.gameMode.i);
battle.getPlayer().id = me;
battle.getOpponent().id = other;
battle.initialDeployment();
} else {
- battle.load(game.db.loadState(gameId));
+ battle.load(game.db.loadGame(gameId));
}
}
@Override
protected void processAction()
{
- storeState();
+ storeGame();
}
@Override
protected void processTurn()
{
- storeOrders();
- storeState();
+ storeTurn();
+ storeGame();
}
- private void storeState()
+ private void storeGame()
{
- game.db.storeState(gameId, battle.getPlayer().id, battle.getOpponent().id, battle.unload(true));
+ game.db.storeGame(gameId, battle.getPlayer().id, battle.unload(true));
}
- private void storeOrders()
+ private void storeTurn()
{
game.db.storeTurn(gameId, battle.getPlayer().id, battle.unload(false));
}
diff --git a/core/src/ch/asynk/rustanddust/menu/NewGameMenu.java b/core/src/ch/asynk/rustanddust/menu/NewGameMenu.java
index b9815ba..e68e5c3 100644
--- a/core/src/ch/asynk/rustanddust/menu/NewGameMenu.java
+++ b/core/src/ch/asynk/rustanddust/menu/NewGameMenu.java
@@ -165,7 +165,7 @@ public class NewGameMenu extends Patch implements MenuCtrl.Panel
return MenuCtrl.MenuType.OK;
}
- game.config.gameId = game.db.gameExists(game.config.battle.getId(), game.config.gameMode.i);
+ game.config.gameId = game.db.getGameId(game.backend.getOpponentId(), game.config.battle.getId(), game.config.gameMode.i);
if (game.config.gameId != game.db.NO_RECORD) {
notImplemented = false;
return MenuCtrl.MenuType.OKKO;
diff --git a/core/src/ch/asynk/rustanddust/menu/PlayMenu.java b/core/src/ch/asynk/rustanddust/menu/PlayMenu.java
index 768e778..bd89368 100644
--- a/core/src/ch/asynk/rustanddust/menu/PlayMenu.java
+++ b/core/src/ch/asynk/rustanddust/menu/PlayMenu.java
@@ -138,7 +138,7 @@ public class PlayMenu extends Patch implements MenuCtrl.Panel
return MenuCtrl.MenuType.OKKO;
} else if (resumeBtn.hit(x, y)) {
game.playType();
- game.config.gameId = GameRecord.get(getList().getIdx()).g;
+ game.config.gameId = GameRecord.get(getList().getIdx()).id;
return MenuCtrl.MenuType.BEGIN;
} else if (list.hit(x, y)) {
if (i != getList().getIdx())
diff --git a/core/src/ch/asynk/rustanddust/util/DB.java b/core/src/ch/asynk/rustanddust/util/DB.java
index aedc9fd..8de4747 100644
--- a/core/src/ch/asynk/rustanddust/util/DB.java
+++ b/core/src/ch/asynk/rustanddust/util/DB.java
@@ -26,62 +26,37 @@ public class 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);";
+ + " 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"
- + ");";
+ + " 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"
- + ");";
+ + " 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,"
- + " _p1 integer not null, _p2 integer not null, _b integer not null,"
- + " m integer not null, ts datetime default current_timestamp,"
- + " foreign key (_p1) references players(_id),"
- + " foreign key (_p2) references players(_id),"
- + " foreign key (_b) references battles(_id),"
- + " unique (_p1, _p2, _b)"
- + ");";
+ + " games ( _id integer primary key autoincrement"
+ + " ,opponent integer not null, battle integer not null, mode 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)"
+ + " ,foreign key (battle) references battles(_id)"
+ + " ,foreign key (player) references players(_id)"
+ + " unique (opponent, battle, mode)"
+ + ");";
private static final String TBL_TURNS_CRT = "create table if not exists"
- + " turns ( _id integer primary key autoincrement, _g integer not null,"
- + " _p integer not null, hash text not null, payload text not null,"
- + " foreign key (_g) references games(_id),"
- + " foreign key (_p) references players(_id)"
- + ");";
-
- private static final String TBL_STATES_CRT = "create table if not exists"
- + " states ( _g integer unique not null,"
- + " hash text not null, payload text not null,"
- + " foreign key (_g) references games(_id)"
- + ");";
+ + " turns ( _id integer primary key autoincrement"
+ + " ,game integer not null, player integer not null"
+ + " ,hash text not null, payload text not null"
+ + " ,foreign key (game) references games(_id)"
+ + " ,foreign key (player) references players(_id)"
+ + ");";
private static final String FEED_CONFIG = " insert or ignore into config values(\"version\", " + DB_SCHEMA_VERSION + ");";
- private static final String CHECK_VERSION = "select (value=%d) from config where key='version';";
- private static final String INSERT_CONFIG = "insert or replace into config(key, value) values ('options','%s');";
- private static final String GET_CONFIG = "select value from config where key='options';";
- private static final String INSERT_PLAYER = "insert or ignore into players(hash,gmail,name) values ('%s','%s','%s');";
- 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';";
- private static final String UPDATE_BATTLE = "insert or replace into battles values (%d,'%s');";
- private static final String INSERT_GAME = "insert or ignore into games(_p1,_p2,_b,m) values (%d,%d,%d,%d);";
- private static final String GET_GAME_ID = "select _id from games where _p1=%d and _p2=%d and _b=%d and m=%d;";
- private static final String GET_GAME_ID2 = "select _id from games where _b=%d and m=%d;";
- private static final String INSERT_TURN = "insert into turns(_g,_p,hash,payload) values (%d,%d,'%s','%s');";
- private static final String GET_TURNS = "select payload from turns where _g=%d order by _id;";
- private static final String INSERT_STATE = "insert or replace into states(_g,hash,payload) values (%d,'%s','%s');";
- private static final String UPDATE_GAME = "update games set _p1=%d, _p2=%d, ts=current_timestamp where _id=%d;";
- private static final String GET_STATE = "select payload from states where _g=%d;";
- private static final String GET_GAMES = "select g.*, p1.name, p2.name, b.name from games g inner join players p1 on (g._p1=p1._id) inner join players p2 on (g._p2=p2._id) inner join battles b on (g._b=b._id);";
- private static final String DELETE_GAME = "delete from games where _id=%d;";
- private static final String DELETE_STATE = "delete from states where _g=%d;";
- private static final String DELETE_TURNS = "delete from turns where _g=%d;";
-
- // private static final String DB_CRT = TBL_CFG_CRT + TBL_PLAYERS_CRT + TBL_BATTLES_CRT + TBL_GAMES_CRT + TBL_TURNS_CRT + TBL_STATES_CRT;
public DB(String dbPath, boolean debug)
{
@@ -115,7 +90,6 @@ public class DB
exec(TBL_BATTLES_CRT);
exec(TBL_GAMES_CRT);
exec(TBL_TURNS_CRT);
- exec(TBL_STATES_CRT);
exec(FEED_CONFIG);
} catch (SQLiteGdxException e) {
RustAndDust.error("table creation error " + e.getMessage());
@@ -132,6 +106,8 @@ public class DB
return hash;
}
+ private static final String CHECK_VERSION = "select (value=%d) from config where key='version';";
+
public Boolean checkVersion()
{
Boolean ret = false;
@@ -148,6 +124,8 @@ public class DB
return ret;
}
+ private static final String INSERT_CONFIG = "insert or replace into config(key, value) values ('config','%s');";
+
public boolean storeConfig(String config)
{
try {
@@ -159,6 +137,8 @@ public class DB
return true;
}
+ private static final String GET_CONFIG = "select value from config where key='config';";
+
public String loadConfig()
{
String ret = null;
@@ -179,6 +159,8 @@ public class DB
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 {
@@ -188,6 +170,9 @@ public class DB
}
}
+ 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;
@@ -208,6 +193,8 @@ public class DB
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 {
@@ -215,18 +202,22 @@ public class DB
} catch (SQLiteGdxException e) { RustAndDust.error("storeBattle"); }
}
- public void storeGame(int you, int opponent, int battle, int mode)
+ private static final String INSERT_GAME = "insert or ignore into games(opponent,battle,mode) values (%d,%d,%d);";
+
+ public void storeGame(int opponent, int battle, int mode)
{
try {
- exec(String.format(INSERT_GAME, you, opponent, battle, mode));
+ exec(String.format(INSERT_GAME, opponent, battle, mode));
} catch (SQLiteGdxException e) { RustAndDust.error("storeGame"); }
}
- public int getGameId(int you, int opponent, int battle, int mode)
+ 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, you, opponent, battle, mode));
+ DatabaseCursor cursor = query(String.format(GET_GAME_ID, opponent, battle, mode));
if (cursor.getCount() > 0) {
cursor.next();
ret = cursor.getInt(0);
@@ -235,18 +226,20 @@ public class DB
return ret;
}
- public int storeGameGetId(int you, int opponent, int battle, int mode)
+ public int storeGameGetId(int opponent, int battle, int mode)
{
- storeGame(you, opponent, battle, mode);
- return getGameId(you, opponent, battle, mode);
+ storeGame(opponent, battle, mode);
+ return getGameId(opponent, battle, mode);
}
- public boolean storeTurn(int game, int p, String payload)
+ private static final String INSERT_TURN = "insert into turns(game,player,hash,payload) values (%d,%d,'%s','%s');";
+
+ public boolean storeTurn(int game, int player, String payload)
{
try {
String hash = getDigest(payload);
if (hash == null) return false;
- exec(String.format(INSERT_TURN, game, p, hash, payload));
+ exec(String.format(INSERT_TURN, game, player, hash, payload));
} catch (SQLiteGdxException e) {
RustAndDust.error("storeTurn");
return false;
@@ -254,6 +247,8 @@ public class DB
return true;
}
+ private static final String GET_TURNS = "select payload from turns where game=%d order by _id;";
+
public String getTurns(int game)
{
String ret = null;
@@ -271,46 +266,51 @@ public class DB
}
builder.setCharAt((builder.length() - 1), ']');
ret = builder.toString();
- } catch (SQLiteGdxException e) { RustAndDust.error("getGameId"); }
+ } catch (SQLiteGdxException e) { RustAndDust.error("getTurns"); }
return ret;
}
- public boolean storeState(int game, int p1, int p2, String payload)
+ private static final String UPDATE_GAME = "update games set ts=current_timestamp, player=%d, hash='%s', payload='%s' where _id=%d;";
+
+ public boolean storeGame(int game, int player, String payload)
{
- RustAndDust.debug("storeState");
+ RustAndDust.debug("storeGame");
try {
String hash = getDigest(payload);
if (hash == null) return false;
- exec(String.format(INSERT_STATE, game, hash, payload, game));
- exec(String.format(UPDATE_GAME, p1, p2, game));
+ exec(String.format(UPDATE_GAME, player, hash, payload, game));
} catch (SQLiteGdxException e) {
- RustAndDust.error("storeState");
+ RustAndDust.error("storeGame");
return false;
}
return true;
}
- public String loadState(int game)
+ private static final String LOAD_GAME = "select g.payload from games g players g(where g._id=%d;";
+
+ public String loadGame(int game)
{
- RustAndDust.debug("loadState");
- String ret = null;
+ RustAndDust.debug("loadGame");
+ String r = null;
try {
- DatabaseCursor cursor = query(String.format(GET_STATE, game));
+ DatabaseCursor cursor = query(String.format(LOAD_GAME, game));
if (cursor.getCount() > 0) {
cursor.next();
- ret = cursor.getString(0);
+ r = cursor.getString(0);
}
- } catch (SQLiteGdxException e) { RustAndDust.error("loadState"); }
- return ret;
+ } catch (SQLiteGdxException e) { RustAndDust.error("loadGame"); }
+ 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(GameRecord game)
{
RustAndDust.debug("deleteGame");
try {
- exec(String.format(DELETE_TURNS, game.g));
- exec(String.format(DELETE_STATE, game.g));
- exec(String.format(DELETE_GAME, game.g));
+ exec(String.format(DELETE_TURNS, game.id));
+ exec(String.format(DELETE_GAME, game.id));
} catch (SQLiteGdxException e) {
RustAndDust.error("deleteGame");
return false;
@@ -319,44 +319,49 @@ public class DB
}
+ private static final String GET_GAMES = "select g._id, g.opponent, g.battle, g.mode, 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();
- GameRecord r = null;
try {
DatabaseCursor cursor = query(GET_GAMES);
if (cursor.getCount() > 0) {
while(cursor.next()) {
- // cursor.next();
- r = GameRecord.get();
- r.g = cursor.getInt(0);
- r.p1 = cursor.getInt(1);
- r.p2 = cursor.getInt(2);
- r.b = cursor.getInt(3);
- r.m = GameMode.from(cursor.getInt(4));
- try { r.ts = df.parse(cursor.getString(5)); }
- catch (java.text.ParseException e) { r.ts = null; RustAndDust.error(String.format("can't parse", cursor.getString(5))); }
- r.p1Name = cursor.getString(6);
- r.p2Name = cursor.getString(7);
- r.bName = cursor.getString(8);
- GameRecord.list.add(r);
+ GameRecord r = from(cursor);
+ if (r != null)
+ GameRecord.list.add(r);
}
}
- } catch (SQLiteGdxException e) { r.dispose(); RustAndDust.error("loadGames"); }
+ } catch (SQLiteGdxException e) { RustAndDust.error("loadGames"); }
}
- public int gameExists(int battle, int mode)
+ private GameRecord from(DatabaseCursor cursor)
{
- int ret = NO_RECORD;
+ GameRecord r = GameRecord.get();
+
try {
- DatabaseCursor cursor = query(String.format(GET_GAME_ID2, battle, mode));
- if (cursor.getCount() > 0) {
- cursor.next();
- ret = cursor.getInt(0);
+ r.id = cursor.getInt(0);
+ r.opponent = cursor.getInt(1);
+ r.battle = cursor.getInt(2);
+ r.mode = GameMode.from(cursor.getInt(3));
+ try { r.ts = df.parse(cursor.getString(4)); }
+ catch (java.text.ParseException e) {
+ r.ts = null;
+ RustAndDust.error(String.format("can't parse", cursor.getString(4)));
}
- } catch (SQLiteGdxException e) { RustAndDust.error("loadConfig"); }
- return ret;
+ r.currentPlayer = cursor.getInt(5);
+ r.oName = cursor.getString(6);
+ r.bName = cursor.getString(7);
+ r.hash = cursor.getString(8);
+ r.payload = cursor.getString(9);
+ } catch (Exception e) {
+ r.dispose(); RustAndDust.error("GameRecord from cursor");
+ }
+
+ return r;
}
private void exec(String sql) throws SQLiteGdxException
diff --git a/core/src/ch/asynk/rustanddust/util/GameRecord.java b/core/src/ch/asynk/rustanddust/util/GameRecord.java
index c1f0edb..a17e967 100644
--- a/core/src/ch/asynk/rustanddust/util/GameRecord.java
+++ b/core/src/ch/asynk/rustanddust/util/GameRecord.java
@@ -13,16 +13,17 @@ import ch.asynk.rustanddust.engine.util.IterableArray;
public class GameRecord implements List.ListElement, Disposable, Pool.Poolable
{
- public int g;
- public int p1;
- public int p2;
- public int b;
- public GameMode m;
+ public int id;
+ public int opponent;
+ public int battle;
+ public GameMode mode;
public Date ts;
- public String p1Name;
- public String p2Name;
+ public int currentPlayer;
+ public String oName;
public String bName;
public String s;
+ public String hash;
+ public String payload;
public static Collection<List.ListElement> list = new IterableArray<List.ListElement>(10);
@@ -82,21 +83,17 @@ public class GameRecord implements List.ListElement, Disposable, Pool.Poolable
public String s()
{
if (s == null) {
- if (m == GameMode.SOLO)
- s = String.format("# - %s - %s - %s - %s", m.s, bName, p1Name, DateFormat.getDateInstance().format(ts));
+ if ((mode == GameMode.SOLO) || canPlay())
+ s = String.format("# %s - %s - %s - %s", mode.s, bName, oName, DateFormat.getDateInstance().format(ts));
else {
- if (p1 == 1)
- s = String.format("# - %s - %s - %s - %s", m.s, bName, p2Name, DateFormat.getDateInstance().format(ts));
- else
- s = String.format(" - %s - %s - %s - %s", m.s, bName, p1Name, DateFormat.getDateInstance().format(ts));
+ s = String.format(" %s - %s - %s - %s", mode.s, bName, oName, DateFormat.getDateInstance().format(ts));
}
}
return s;
}
- @Override
- public String toString()
+ public boolean canPlay()
{
- return String.format("%d %d(%s) %d(%s) %d(%s) %s %s", g, p1, p1Name, p2, p2Name, b, bName, m.s, DateFormat.getDateInstance().format(ts));
+ return (opponent != currentPlayer);
}
}
--
cgit v1.1-2-g2b99