package bms.player.beatoraja;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;
import bms.player.beatoraja.song.SongData;
import org.sqlite.SQLiteConfig.SynchronousMode;
/**
* スコアデータベースアクセサ
*
* @author exch
*/
public class ScoreDatabaseAccessor {
private final QueryRunner qr;
private final ResultSetHandler<List<PlayerInformation>> infoHandler = new BeanListHandler<PlayerInformation>(PlayerInformation.class);
private final ResultSetHandler<List<ScoreData>> scoreHandler = new BeanListHandler<ScoreData>(ScoreData.class);
private final ResultSetHandler<List<PlayerData>> playerHandler = new BeanListHandler<PlayerData>(PlayerData.class);
public ScoreDatabaseAccessor(String path) throws ClassNotFoundException {
Class.forName("org.sqlite.JDBC");
SQLiteConfig conf = new SQLiteConfig();
conf.setSharedCache(true);
conf.setSynchronous(SynchronousMode.OFF);
// conf.setJournalMode(JournalMode.MEMORY);
SQLiteDataSource ds = new SQLiteDataSource(conf);
ds.setUrl("jdbc:sqlite:" + path);
qr = new QueryRunner(ds);
}
public void createTable() {
try {
final MapListHandler mh = new MapListHandler();
String sql = "SELECT * FROM sqlite_master WHERE name = ? and type='table';";
// infoテーブル作成(存在しない場合)
if (qr.query(sql, mh, "info").size() == 0) {
qr.update("CREATE TABLE [info] ([id] TEXT NOT NULL,[name] TEXT NOT NULL," + "[rank] TEXT, "
+ "PRIMARY KEY(id));");
}
// playerテーブル作成(存在しない場合)
if (qr.query(sql, mh, "player").size() == 0) {
qr.update("CREATE TABLE [player] ([date] INTEGER,[playcount] INTEGER," + "[clear] INTEGER,"
+ "[epg] INTEGER," + "[lpg] INTEGER," + "[egr] INTEGER," + "[lgr] INTEGER," + "[egd] INTEGER,"
+ "[lgd] INTEGER," + "[ebd] INTEGER," + "[lbd] INTEGER," + "[epr] INTEGER," + "[lpr] INTEGER,"
+ "[ems] INTEGER," + "[lms] INTEGER," + "[playtime] INTEGER," + "[combo] INTEGER,"
+ "[maxcombo] INTEGER," + "[scorehash] TEXT," + "PRIMARY KEY(date));");
qr.update(
"insert into player "
+ "(date, playcount, clear, epg, lpg, egr, lgr, egd, lgd, ebd, lbd, epr, lpr, ems, lms, playtime, combo, maxcombo, "
+ "scorehash) " + "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, "");
}
// scoreテーブル作成(存在しない場合)
if (qr.query(sql, mh, "score").size() == 0) {
qr.update("CREATE TABLE [score] ([sha256] TEXT NOT NULL," + "[mode] INTEGER," + "[clear] INTEGER,"
+ "[epg] INTEGER," + "[lpg] INTEGER," + "[egr] INTEGER," + "[lgr] INTEGER," + "[egd] INTEGER,"
+ "[lgd] INTEGER," + "[ebd] INTEGER," + "[lbd] INTEGER," + "[epr] INTEGER," + "[lpr] INTEGER,"
+ "[ems] INTEGER," + "[lms] INTEGER," + "[notes] INTEGER," + "[combo] INTEGER,"
+ "[minbp] INTEGER," + "[playcount] INTEGER," + "[clearcount] INTEGER," + "[trophy] TEXT," + "[ghost] TEXT,"
+ "[scorehash] TEXT," + "[option] INTEGER," + "[random] INTEGER," + "[date] INTEGER,"
+ "[state] INTEGER," + "PRIMARY KEY(sha256, mode));");
}
// 過去のバージョンで作成したテーブルにカラムが存在しない場合に作成
if(qr.query("SELECT * FROM sqlite_master WHERE name = 'score' AND sql LIKE '%trophy%'", new MapListHandler()).size() == 0) {
qr.update("ALTER TABLE score ADD COLUMN trophy [TEXT]");
}
if (qr.query("SELECT * FROM sqlite_master WHERE name = 'score' AND sql LIKE '%ghost%'", new MapListHandler()).size() == 0) {
qr.update("ALTER TABLE score ADD COLUMN ghost [TEXT]");
}
} catch (SQLException e) {
Logger.getGlobal().severe("スコアデータベース初期化中の例外:" + e.getMessage());
}
}
public PlayerInformation getInformation() {
try {
List<PlayerInformation> info = qr.query("SELECT * FROM info", infoHandler);
if (info.size() > 0) {
return info.get(0);
}
} catch (Exception e) {
Logger.getGlobal().severe("スコア取得時の例外:" + e.getMessage());
}
return null;
}
public void setInformation(PlayerInformation info) {
try {
qr.update("DELETE FROM info");
qr.update("insert into info " + "(id, name, rank) " + "values(?,?,?);", info.getId(), info.getName(), info.getRank());
} catch (Exception e) {
Logger.getGlobal().severe("スコア取得時の例外:" + e.getMessage());
}
}
public ScoreData getScoreData(String hash, int mode) {
ScoreData result = null;
try {
List<ScoreData> score = Validatable.removeInvalidElements(qr.query("SELECT * FROM score WHERE sha256 = '" + hash + "' AND mode = " + mode, scoreHandler));
if (score.size() > 0) {
ScoreData sc = null;
for (ScoreData s : score) {
if (sc == null || s.getClear() > sc.getClear()) {
sc = s;
}
}
result = sc;
}
} catch (Exception e) {
Logger.getGlobal().severe("スコア取得時の例外:" + e.getMessage());
}
return result;
}
/**
* プレイヤースコアデータを取得する
*/
public void getScoreDatas(ScoreDataCollector collector, SongData[] songs, int mode) {
StringBuilder str = new StringBuilder(songs.length * 68);
getScoreDatas(collector, songs, mode, str, true);
str.setLength(0);
getScoreDatas(collector, songs, 0, str, false);
}
private void getScoreDatas(ScoreDataCollector collector, SongData[] songs, int mode, StringBuilder str, boolean hasln) {
try {
for (SongData song : songs) {
if((hasln && song.hasUndefinedLongNote()) || (!hasln && !song.hasUndefinedLongNote())) {
if (str.length() > 0) {
str.append(',');
}
str.append('\'').append(song.getSha256()).append('\'');
}
}
List<ScoreData> scores = Validatable.removeInvalidElements(qr
.query("SELECT * FROM score WHERE sha256 IN (" + str.toString() + ") AND mode = " + mode, scoreHandler));
for(SongData song : songs) {
if((hasln && song.hasUndefinedLongNote()) || (!hasln && !song.hasUndefinedLongNote())) {
boolean b = true;
for (ScoreData score : scores) {
if(song.getSha256().equals(score.getSha256())) {
collector.collect(song, score);
b = false;
break;
}
}
if(b) {
collector.collect(song, null);
}
}
}
} catch (Exception e) {
Logger.getGlobal().severe("スコア取得時の例外:" + e.getMessage());
}
}
public List<ScoreData> getScoreDatas(String sql) {
List<ScoreData> score = null;
try {
score = Validatable.removeInvalidElements(qr.query("SELECT * FROM score WHERE " + sql, scoreHandler));
} catch (Exception e) {
Logger.getGlobal().severe("スコア取得時の例外:" + e.getMessage());
}
return score;
}
public void setScoreData(ScoreData score) {
setScoreData(new ScoreData[] { score });
}
public void setScoreData(ScoreData[] scores) {
try (Connection con = qr.getDataSource().getConnection()) {
con.setAutoCommit(false);
String sql = "INSERT OR REPLACE INTO score "
+ "(sha256, mode, clear, epg, lpg, egr, lgr, egd, lgd, ebd, lbd, epr, lpr, ems, lms, notes, combo, "
+ "minbp, playcount, clearcount, trophy, ghost, scorehash, option, random, date, state)"
+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
for (ScoreData score : scores) {
qr.update(con, sql, score.getSha256(), score.getMode(), score.getClear(), score.getEpg(),
score.getLpg(), score.getEgr(), score.getLgr(), score.getEgd(), score.getLgd(), score.getEbd(),
score.getLbd(), score.getEpr(), score.getLpr(), score.getEms(), score.getLms(),
score.getNotes(), score.getCombo(), score.getMinbp(), score.getPlaycount(),
score.getClearcount(), score.getTrophy(), score.getGhost(), score.getScorehash(), score.getOption(),
score.getRandom(), score.getDate(), score.getState());
}
con.commit();
} catch (Exception e) {
Logger.getGlobal().severe("スコア更新時の例外:" + e.getMessage());
}
}
public void setScoreData(Map<String, Map<String, Object>> map) {
try (Connection con = qr.getDataSource().getConnection()) {
con.setAutoCommit(false);
for (String hash : map.keySet()) {
Map<String, Object> values = map.get(hash);
String vs = "";
for (String key : values.keySet()) {
vs += key + " = " + values.get(key) + ",";
}
if (vs.length() > 0) {
vs = vs.substring(0, vs.length() - 1) + " ";
qr.update(con, "UPDATE score SET " + vs + "WHERE sha256 = '" + hash + "'");
}
}
con.commit();
} catch (Exception e) {
Logger.getGlobal().severe("スコア更新時の例外:" + e.getMessage());
}
}
public void deleteScoreData(String sha256, int mode) {
try {
qr.update("DELETE FROM score WHERE sha256 = ? and mode = ?", sha256, mode);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* プレイヤーデータを取得する
*
* @return プレイヤーデータ
*/
public PlayerData getPlayerData() {
PlayerData[] pd = getPlayerDatas(1);
if (pd.length > 0) {
return pd[0];
}
return null;
}
public PlayerData[] getPlayerDatas(int count) {
PlayerData[] result = null;
try {
List<PlayerData> pd = qr
.query("SELECT * FROM player ORDER BY date DESC" + (count > 0 ? " limit " + count : ""), playerHandler);
result = pd.toArray(new PlayerData[0]);
} catch (Exception e) {
Logger.getGlobal().severe("プレイヤーデータ取得時の例外:" + e.getMessage());
}
return result != null ? result : new PlayerData[0];
}
public void setPlayerData(PlayerData pd) {
try (Connection con = qr.getDataSource().getConnection()) {
con.setAutoCommit(false);
Calendar cal = Calendar.getInstance(TimeZone.getDefault());
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
long unixtime = cal.getTimeInMillis() / 1000L;
qr.update(con,
"INSERT OR REPLACE INTO player "
+ "(date, playcount, clear, epg, lpg, egr, lgr, egd, lgd, ebd, lbd, epr, lpr, ems, lms, playtime, combo, maxcombo, "
+ "scorehash) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);",
unixtime, pd.getPlaycount(), pd.getClear(), pd.getEpg(), pd.getLpg(), pd.getEgr(), pd.getLgr(),
pd.getEgd(), pd.getLgd(), pd.getEbd(), pd.getLbd(), pd.getEpr(), pd.getLpr(), pd.getEms(),
pd.getLms(), pd.getPlaytime(), 0, 0, "");
con.commit();
} catch (Exception e) {
Logger.getGlobal().severe("スコア更新時の例外:" + e.getMessage());
}
}
public interface ScoreDataCollector {
public void collect(SongData hash, ScoreData score);
}
}
