private static final void mysqlFind(
final Plugin plugin,
final String playerName,
final Location location,
final int radius,
final WorldManager manager,
final ArrayList<Player> players) {
BBPlayerInfo hunted = BBUsersTable.getInstance().getUserByName(playerName);
PreparedStatement ps = null;
ResultSet rs = null;
HashMap<Integer, Integer> creations = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> destructions = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> explosions = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> burns = new HashMap<Integer, Integer>();
Connection conn = null;
try {
conn = ConnectionManager.getConnection();
if (conn == null) return;
// TODO maybe more customizable actions?
String actionString =
"action IN('"
+ Action.BLOCK_BROKEN.ordinal()
+ "', '"
+ Action.BLOCK_PLACED.ordinal()
+ "', '"
+ Action.LEAF_DECAY.ordinal()
+ "', '"
+ Action.TNT_EXPLOSION.ordinal()
+ "', '"
+ Action.CREEPER_EXPLOSION.ordinal()
+ "', '"
+ Action.MISC_EXPLOSION.ordinal()
+ "', '"
+ Action.LAVA_FLOW.ordinal()
+ "', '"
+ Action.BLOCK_BURN.ordinal()
+ "')";
ps =
conn.prepareStatement(
"SELECT action, type FROM "
+ BBDataTable.getInstance().getTableName()
+ " WHERE "
+ actionString
+ " AND rbacked = 0 AND x < ? AND x > ? AND y < ? AND y > ? AND z < ? AND z > ? AND player = ? AND world = ? order by date desc");
ps.setInt(1, location.getBlockX() + radius);
ps.setInt(2, location.getBlockX() - radius);
ps.setInt(3, location.getBlockY() + radius);
ps.setInt(4, location.getBlockY() - radius);
ps.setInt(5, location.getBlockZ() + radius);
ps.setInt(6, location.getBlockZ() - radius);
ps.setInt(7, hunted.getID());
ps.setInt(8, manager.getWorld(location.getWorld().getName()));
rs = ps.executeQuery();
conn.commit();
int size = 0;
while (rs.next()) {
Action action = Action.values()[rs.getInt("action")];
int type = rs.getInt("type");
switch (action) {
case BLOCK_BROKEN:
case LEAF_DECAY:
if (destructions.containsKey(type)) {
destructions.put(type, destructions.get(type) + 1);
size++;
} else {
destructions.put(type, 1);
size++;
}
break;
case BLOCK_PLACED:
if (creations.containsKey(type)) {
creations.put(type, creations.get(type) + 1);
size++;
} else {
creations.put(type, 1);
size++;
}
break;
case TNT_EXPLOSION:
case CREEPER_EXPLOSION:
case MISC_EXPLOSION:
if (explosions.containsKey(type)) {
explosions.put(type, explosions.get(type) + 1);
size++;
} else {
explosions.put(type, 1);
size++;
}
case BLOCK_BURN:
if (burns.containsKey(type)) {
burns.put(type, burns.get(type) + 1);
size++;
} else {
burns.put(type, 1);
size++;
}
break;
case LAVA_FLOW:
if (creations.containsKey(type)) {
creations.put(type, creations.get(type) + 1);
size++;
} else {
creations.put(type, 1);
size++;
}
break;
}
}
if (size > 0) {
StringBuilder creationList = new StringBuilder();
creationList.append(ChatColor.AQUA.toString());
creationList.append("Placed Blocks: ");
creationList.append(ChatColor.WHITE.toString());
for (Entry<Integer, Integer> entry : creations.entrySet()) {
creationList.append(Material.getMaterial(entry.getKey()));
creationList.append(" (");
creationList.append(entry.getValue());
creationList.append("), ");
}
if (creationList.toString().contains(",")) {
creationList.delete(creationList.lastIndexOf(","), creationList.length());
}
StringBuilder brokenList = new StringBuilder();
brokenList.append(ChatColor.RED.toString());
brokenList.append("Broken Blocks: ");
brokenList.append(ChatColor.WHITE.toString());
for (Entry<Integer, Integer> entry : destructions.entrySet()) {
brokenList.append(Material.getMaterial(entry.getKey()));
brokenList.append(" (");
brokenList.append(entry.getValue());
brokenList.append("), ");
}
if (brokenList.toString().contains(",")) {
brokenList.delete(brokenList.lastIndexOf(","), brokenList.length());
}
StringBuilder explodeList = new StringBuilder();
explodeList.append(ChatColor.RED.toString());
explodeList.append("Exploded Blocks: ");
explodeList.append(ChatColor.WHITE.toString());
for (Entry<Integer, Integer> entry : explosions.entrySet()) {
explodeList.append(Material.getMaterial(entry.getKey()));
explodeList.append(" (");
explodeList.append(entry.getValue());
explodeList.append("), ");
}
if (explodeList.toString().contains(",")) {
explodeList.delete(explodeList.lastIndexOf(","), explodeList.length());
}
StringBuilder burnList = new StringBuilder();
burnList.append(ChatColor.RED.toString());
burnList.append("Burned Blocks: ");
burnList.append(ChatColor.WHITE.toString());
for (Entry<Integer, Integer> entry : burns.entrySet()) {
burnList.append(Material.getMaterial(entry.getKey()));
burnList.append(" (");
burnList.append(entry.getValue());
burnList.append("), ");
}
if (burnList.toString().contains(",")) {
burnList.delete(burnList.lastIndexOf(","), burnList.length());
}
for (Player player : players) {
player.sendMessage(
BigBrother.premessage + playerName + " has made " + size + " modifications");
if (creations.entrySet().size() > 0) {
player.sendMessage(creationList.toString());
}
if (destructions.entrySet().size() > 0) {
player.sendMessage(brokenList.toString());
}
if (explosions.entrySet().size() > 0) {
player.sendMessage(explodeList.toString());
}
if (burns.entrySet().size() > 0) {
player.sendMessage(burnList.toString());
}
}
} else {
for (Player player : players) {
player.sendMessage(
BigBrother.premessage + playerName + " has no modifications in this area.");
}
}
} catch (SQLException ex) {
BBLogging.severe("Find SQL Exception", ex);
} finally {
ConnectionManager.cleanup("Find SQL", conn, ps, rs);
}
}
private static final void mysqlFind(
final Plugin plugin,
final Location location,
final int radius,
final WorldManager manager,
final ArrayList<Player> players) {
PreparedStatement ps = null;
ResultSet rs = null;
Connection conn = null;
HashMap<BBPlayerInfo, Integer> modifications = new HashMap<BBPlayerInfo, Integer>();
try {
conn = ConnectionManager.getConnection();
if (conn == null) return;
// TODO maybe more customizable actions?
String actionString =
"action IN('"
+ Action.BLOCK_BROKEN.ordinal()
+ "', '"
+ Action.BLOCK_PLACED.ordinal()
+ "', '"
+ Action.LEAF_DECAY.ordinal()
+ "', '"
+ Action.TNT_EXPLOSION.ordinal()
+ "', '"
+ Action.CREEPER_EXPLOSION.ordinal()
+ "', '"
+ Action.MISC_EXPLOSION.ordinal()
+ "', '"
+ Action.LAVA_FLOW.ordinal()
+ "', '"
+ Action.BLOCK_BURN.ordinal()
+ "')";
/*
* org.h2.jdbc.JdbcSQLException: Column "ID" must be in the GROUP BY
* list; SQL statement:
*/
if (BBSettings.usingDBMS(DBMS.H2) || BBSettings.usingDBMS(DBMS.POSTGRES)) {
ps =
conn.prepareStatement(
"SELECT player, count(player) AS modifications FROM "
+ BBDataTable.getInstance().getTableName()
+ " WHERE "
+ actionString
+ " AND rbacked = '0' AND x < ? AND x > ? AND y < ? AND y > ? AND z < ? AND z > ? AND world = ? GROUP BY id,player ORDER BY id DESC");
} else {
ps =
conn.prepareStatement(
"SELECT player, count(player) AS modifications FROM "
+ BBDataTable.getInstance().getTableName()
+ " WHERE "
+ actionString
+ " AND rbacked = '0' AND x < ? AND x > ? AND y < ? AND y > ? AND z < ? AND z > ? AND world = ? GROUP BY player ORDER BY id DESC");
}
ps.setInt(1, location.getBlockX() + radius);
ps.setInt(2, location.getBlockX() - radius);
ps.setInt(3, location.getBlockY() + radius);
ps.setInt(4, location.getBlockY() - radius);
ps.setInt(5, location.getBlockZ() + radius);
ps.setInt(6, location.getBlockZ() - radius);
ps.setInt(7, manager.getWorld(location.getWorld().getName()));
rs = ps.executeQuery();
conn.commit();
int size = 0;
while (rs.next()) {
BBPlayerInfo player = BBUsersTable.getInstance().getUserByID(rs.getInt("player"));
int mods = rs.getInt("modifications");
modifications.put(player, mods);
size++;
}
if (size > 0) {
StringBuilder playerList = new StringBuilder();
for (Entry<BBPlayerInfo, Integer> entry : modifications.entrySet()) {
if (entry.getKey() != null) {
playerList.append(entry.getKey().getName());
playerList.append(" (");
playerList.append(entry.getValue());
playerList.append("), ");
}
}
if (playerList.indexOf(",") != -1) {
playerList.delete(playerList.lastIndexOf(","), playerList.length());
}
// TODO Put into sync'd runnable
for (Player player : players) {
player.sendMessage(
BigBrother.premessage + playerList.length() + " player(s) have modified this area:");
player.sendMessage(playerList.toString());
}
} else {
for (Player player : players) {
player.sendMessage(BigBrother.premessage + "No modifications in this area.");
}
}
} catch (SQLException ex) {
BBLogging.severe("Find SQL Exception", ex);
} finally {
ConnectionManager.cleanup("Find SQL", conn, ps, rs);
}
}
// TODO use IN(1,2,3)
private void mysqlFind(boolean sqlite) {
PreparedStatement ps = null;
ResultSet rs = null;
Connection conn = null;
HashMap<String, Integer> modifications = new HashMap<String, Integer>();
try {
conn = ConnectionManager.getConnection();
// TODO maybe more customizable actions?
String actionString =
"action IN('"
+ Action.BLOCK_BROKEN.ordinal()
+ "', '"
+ Action.BLOCK_PLACED.ordinal()
+ "', '"
+ Action.LEAF_DECAY.ordinal()
+ "', '"
+ Action.TNT_EXPLOSION.ordinal()
+ "', '"
+ Action.CREEPER_EXPLOSION.ordinal()
+ "', '"
+ Action.MISC_EXPLOSION.ordinal()
+ "', '"
+ Action.BLOCK_BURN.ordinal()
+ "')";
ps =
conn.prepareStatement(
"SELECT player, count(player) AS modifications FROM "
+ BBDataBlock.BBDATA_NAME
+ " WHERE "
+ actionString
+ " AND rbacked = '0' AND x < ? AND x > ? AND y < ? AND y > ? AND z < ? AND z > ? AND world = ? GROUP BY player ORDER BY id DESC");
ps.setInt(1, location.getBlockX() + radius);
ps.setInt(2, location.getBlockX() - radius);
ps.setInt(3, location.getBlockY() + radius);
ps.setInt(4, location.getBlockY() - radius);
ps.setInt(5, location.getBlockZ() + radius);
ps.setInt(6, location.getBlockZ() - radius);
ps.setInt(7, worlds.indexOf(location.getWorld()));
rs = ps.executeQuery();
conn.commit();
int size = 0;
while (rs.next()) {
String player = rs.getString("player");
int mods = rs.getInt("modifications");
modifications.put(player, mods);
size++;
}
if (size > 0) {
StringBuilder playerList = new StringBuilder();
for (Entry<String, Integer> entry : modifications.entrySet()) {
playerList.append(entry.getKey());
playerList.append(" (");
playerList.append(entry.getValue());
playerList.append("), ");
}
playerList.delete(playerList.lastIndexOf(","), playerList.length());
for (Player player : players) {
player.sendMessage(BigBrother.premessage + size + " player(s) have modified this area:");
player.sendMessage(playerList.toString());
}
} else {
for (Player player : players) {
player.sendMessage(BigBrother.premessage + "No modifications in this area.");
}
}
} catch (SQLException ex) {
BigBrother.log.log(Level.SEVERE, "[BBROTHER]: Find SQL Exception", ex);
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (conn != null) conn.close();
} catch (SQLException ex) {
BigBrother.log.log(Level.SEVERE, "[BBROTHER]: Find SQL Exception (on close)");
}
}
}
@Override
protected void do_addOrUpdatePlayer(BBPlayerInfo pi) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = ConnectionManager.getConnection();
if (conn == null) return;
if (pi.getNew() && getUserFromDB(pi.getName()) == null) {
/*String statement = "IF EXISTS (SELECT \"name\" FROM \""+getTableName()+"\" WHERE \"name\" = ?) THEN"
+ " UPDATE \""+getTableName()+"\" SET \"flags\" = ? WHERE \"name\" = ?;"
+ " ELSE "
+ " INSERT INTO \""+getTableName()+"\" (name,flags) VALUES(?,?)"
+ " END IF;";
ps = conn.prepareStatement(statement);*/
ps = conn.prepareStatement("INSERT INTO " + getTableName() + " (name,flags) VALUES (?,?)");
ps.setString(1, pi.getName());
ps.setInt(2, pi.getFlags());
} else {
ps = conn.prepareStatement("UPDATE " + getTableName() + " SET flags = ? WHERE id=?");
ps.setInt(1, pi.getFlags());
ps.setInt(2, pi.getID());
}
BBLogging.debug(ps.toString());
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
BBLogging.severe("Can't update the user `" + pi.getName() + "`.", e);
} finally {
ConnectionManager.cleanup("BBUsersMySQL.do_addOrUpdatePlayer", conn, ps, null);
}
}
@Override
public BBPlayerInfo getUserFromDB(int id) {
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
String sql = "SELECT id,name,flags FROM " + getTableName() + " WHERE \"id\"=?;";
conn = ConnectionManager.getConnection();
if (conn == null) return null;
BBLogging.debug(sql);
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
conn.commit();
if (!rs.next()) return null;
return new BBPlayerInfo(rs.getInt("id"), rs.getString("name"), rs.getInt("flags"));
} catch (SQLException e) {
BBLogging.severe("Can't find user #" + id + ".", e);
} finally {
ConnectionManager.cleanup("BBUsersMySQL.getUserFromDB(int)", conn, ps, rs);
}
return null;
}
private void cleanByAge() {
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionManager.getConnection();
stmt = conn.createStatement();
long start = System.currentTimeMillis() / 1000;
String cleansql =
"DELETE FROM `bbdata` WHERE date < " + Long.valueOf(Time.ago(BBSettings.cleanseAge));
if (BBSettings.deletesPerCleansing > 0)
cleansql += " LIMIT " + Long.valueOf(BBSettings.deletesPerCleansing);
cleansql += ";";
cleanedSoFarAge = stmt.executeUpdate(cleansql);
String timespent = Time.formatDuration(System.currentTimeMillis() / 1000 - start);
String words =
String.format(
"Cleaned out %d records because of age in %s.", cleanedSoFarAge, timespent);
if (player == null) BBLogging.info(words);
else {
synchronized (player) {
player.sendMessage(ChatColor.BLUE + words);
}
}
conn.commit();
} catch (SQLException ex) {
BBLogging.severe("Cleanse SQL exception (by age)", ex);
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
BBLogging.severe("Cleanse SQL exception (by age) (on close)", ex);
}
}
}
private static boolean updateTable(boolean sqlite) {
// SQLite sucks, just skip it.
if (sqlite) {
return true;
} else {
Connection conn = null;
Statement st = null;
try {
conn = ConnectionManager.getConnection();
st = conn.createStatement();
for (String update : UPDATE_SQL) {
st.executeUpdate(update);
}
conn.commit();
return true;
} catch (SQLException e) {
BBLogging.severe(
"[Fix4] Unable to remove/create new indices. However, this shouldn't be a problem except performance-wise.");
return false;
} finally {
ConnectionManager.cleanup("Fix 4", conn, st, null);
}
}
}
@SuppressWarnings("unused")
private void cleanByNumber() {
if (BBSettings.mysql) {
if (BBSettings.maxRecords < 0) {
// Fix exception caused when trying to delete -1 records.
BBLogging.info("Skipping; max-records is negative.");
return;
}
Connection conn = null;
Statement stmt = null;
try {
conn = ConnectionManager.getConnection();
stmt = conn.createStatement();
long start = System.currentTimeMillis() / 1000;
String cleansql =
"DELETE FROM `bbdata` as b LEFT OUTER JOIN (SELECT `id` FROM `bbdata` ORDER BY `id` DESC LIMIT 0,"
+ Long.valueOf(BBSettings.maxRecords)
+ ") as j on j.id=b.id where j.id is null";
if (BBSettings.deletesPerCleansing > 0)
cleansql += " LIMIT " + Long.valueOf(BBSettings.deletesPerCleansing);
cleansql += ";";
cleanedSoFarNumber = stmt.executeUpdate(cleansql);
String timespent = Time.formatDuration(System.currentTimeMillis() / 1000 - start);
String words =
String.format(
"Cleaned out %d records because of number in %s.", cleanedSoFarNumber, timespent);
if (player == null) BBLogging.info(words);
else {
synchronized (player) {
player.sendMessage(ChatColor.BLUE + words);
}
}
conn.commit();
} catch (SQLException ex) {
BBLogging.severe("Cleanse SQL exception (by #)", ex);
if (player != null) {
synchronized (player) {
player.sendMessage(
ChatColor.RED + "Action failed, read server log for the gory details.");
}
}
} finally {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
BBLogging.severe("Cleanse SQL exception (by #) (on close)", ex);
}
}
} else {
String words = "SQLite can't cleanse by # of records.";
if (player == null) BBLogging.info(words);
else {
synchronized (player) {
player.sendMessage(ChatColor.RED + words);
}
}
}
}
private void mysqlFind(boolean sqlite, String playerName) {
PreparedStatement ps = null;
ResultSet rs = null;
HashMap<Integer, Integer> creations = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> destructions = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> explosions = new HashMap<Integer, Integer>();
HashMap<Integer, Integer> burns = new HashMap<Integer, Integer>();
Connection conn = null;
try {
conn = ConnectionManager.getConnection();
// TODO maybe more customizable actions?
String actionString =
"action IN('"
+ Action.BLOCK_BROKEN.ordinal()
+ "', '"
+ Action.BLOCK_PLACED.ordinal()
+ "', '"
+ Action.LEAF_DECAY.ordinal()
+ "', '"
+ Action.TNT_EXPLOSION.ordinal()
+ "', '"
+ Action.CREEPER_EXPLOSION.ordinal()
+ "', '"
+ Action.MISC_EXPLOSION.ordinal()
+ "', '"
+ Action.BLOCK_BURN.ordinal()
+ "')";
ps =
conn.prepareStatement(
"SELECT action, type FROM "
+ BBDataBlock.BBDATA_NAME
+ " WHERE "
+ actionString
+ " AND rbacked = 0 AND x < ? AND x > ? AND y < ? AND y > ? AND z < ? AND z > ? AND player = ? AND world = ? order by date desc");
ps.setInt(1, location.getBlockX() + radius);
ps.setInt(2, location.getBlockX() - radius);
ps.setInt(3, location.getBlockY() + radius);
ps.setInt(4, location.getBlockY() - radius);
ps.setInt(5, location.getBlockZ() + radius);
ps.setInt(6, location.getBlockZ() - radius);
ps.setString(7, playerName);
ps.setInt(8, worlds.indexOf(location.getWorld()));
rs = ps.executeQuery();
conn.commit();
int size = 0;
while (rs.next()) {
Action action = Action.values()[rs.getInt("action")];
int type = rs.getInt("type");
switch (action) {
case BLOCK_BROKEN:
case LEAF_DECAY:
if (destructions.containsKey(type)) {
destructions.put(type, destructions.get(type) + 1);
size++;
} else {
destructions.put(type, 1);
size++;
}
break;
case BLOCK_PLACED:
if (creations.containsKey(type)) {
creations.put(type, creations.get(type) + 1);
size++;
} else {
creations.put(type, 1);
size++;
}
break;
case TNT_EXPLOSION:
case CREEPER_EXPLOSION:
case MISC_EXPLOSION:
if (explosions.containsKey(type)) {
explosions.put(type, explosions.get(type) + 1);
size++;
} else {
explosions.put(type, 1);
size++;
}
case BLOCK_BURN:
if (burns.containsKey(type)) {
burns.put(type, burns.get(type) + 1);
size++;
} else {
burns.put(type, 1);
size++;
}
break;
}
}
if (size > 0) {
StringBuilder creationList = new StringBuilder();
// creationList.append(Color.AQUA);
creationList.append("Placed Blocks: ");
// creationList.append(Color.WHITE);
for (Entry<Integer, Integer> entry : creations.entrySet()) {
creationList.append(Material.getMaterial(entry.getKey()));
creationList.append(" (");
creationList.append(entry.getValue());
creationList.append("), ");
}
if (creationList.toString().contains(","))
creationList.delete(creationList.lastIndexOf(","), creationList.length());
StringBuilder brokenList = new StringBuilder();
// brokenList.append(Color.RED);
brokenList.append("Broken Blocks: ");
// brokenList.append(Color.WHITE);
for (Entry<Integer, Integer> entry : destructions.entrySet()) {
brokenList.append(Material.getMaterial(entry.getKey()));
brokenList.append(" (");
brokenList.append(entry.getValue());
brokenList.append("), ");
}
if (brokenList.toString().contains(","))
brokenList.delete(brokenList.lastIndexOf(","), brokenList.length());
StringBuilder explodeList = new StringBuilder();
// brokenList.append(Color.RED);
explodeList.append("Exploded Blocks: ");
// brokenList.append(Color.WHITE);
for (Entry<Integer, Integer> entry : explosions.entrySet()) {
explodeList.append(Material.getMaterial(entry.getKey()));
explodeList.append(" (");
explodeList.append(entry.getValue());
explodeList.append("), ");
}
if (explodeList.toString().contains(","))
explodeList.delete(explodeList.lastIndexOf(","), explodeList.length());
StringBuilder burnList = new StringBuilder();
// brokenList.append(Color.RED);
burnList.append("Burned Blocks: ");
// brokenList.append(Color.WHITE);
for (Entry<Integer, Integer> entry : burns.entrySet()) {
burnList.append(Material.getMaterial(entry.getKey()));
burnList.append(" (");
burnList.append(entry.getValue());
burnList.append("), ");
}
if (burnList.toString().contains(","))
burnList.delete(burnList.lastIndexOf(","), burnList.length());
for (Player player : players) {
player.sendMessage(
BigBrother.premessage + playerName + " has made " + size + " modifications");
if (creations.entrySet().size() > 0) player.sendMessage(creationList.toString());
if (destructions.entrySet().size() > 0) player.sendMessage(brokenList.toString());
if (explosions.entrySet().size() > 0) player.sendMessage(explodeList.toString());
if (burns.entrySet().size() > 0) player.sendMessage(burnList.toString());
}
} else {
for (Player player : players) {
player.sendMessage(
BigBrother.premessage + playerName + " has no modifications in this area.");
}
}
} catch (SQLException ex) {
BigBrother.log.log(Level.SEVERE, "[BBROTHER]: Find SQL Exception", ex);
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
if (conn != null) conn.close();
} catch (SQLException ex) {
BigBrother.log.log(Level.SEVERE, "[BBROTHER]: Find SQL Exception (on close)");
}
}
}