Java tutorial
/* * WDLStats * Copyright (C) 2015 Chris K * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package ca.wumbo.wdl.database; import static com.google.common.base.Preconditions.*; import java.io.FileNotFoundException; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Savepoint; import java.sql.Statement; import java.util.ArrayList; import java.util.HashSet; import java.util.Set; import javax.xml.bind.JAXBException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.google.common.collect.BiMap; import ca.wumbo.wdl.parser.Parser; import ca.wumbo.wdl.parser.Player; import ca.wumbo.wdl.parser.RoundParser; import ca.wumbo.wdl.parser.events.Event; /** * Performs the handling of log files and pushes them to the database. * * @author Chris */ public class LogEventSubmitter extends MySqlConnection { /** * The logger for this class. */ private static final Logger log = LoggerFactory.getLogger(LogEventSubmitter.class); /** * The savepoint name for this transaction. */ private static final String TRANSACTION_SAVEPOINT = "MySqlConnectionSavepoint"; /** * Starts a brand new connection to a MySQL server, whereby it uses the * configuration file default location (which is just "wdlconfig.xml"). * This will handle all log pushing to the database. * * @throws JAXBException * If JAXB has issues loading the config xml. * * @throws FileNotFoundException * If the config XML cannot be found. * * @throws SQLException * If the connection fails. * * @throws SQLTimeoutException * If there is a timeout trying to connect. */ public LogEventSubmitter() throws FileNotFoundException, JAXBException, SQLException { } /** * Takes a parser log and pushes all the data into the database. * * @param dbGameId * The ID of the primary key for the game table. * * @param parser * The parser to push the events in with. * * @param idToPlayerMap * A bi-directional map of ID to players. * * @param redTeamId * The team ID in the database for the red team. * * @param blueTeamId * The team ID in the database for the blue team. * * @throws MissingGameInDatabaseException * If the game is missing from the database. * * @throws SQLException * If any exceptions occur while pushing data to the database, or this * is called on a closed connection. * * @throws NullPointerException * If any argument is null. * * @throws IllegalArgumentException * If the database game id is negative, or the map is empty, or if the * team numbers are negative. */ public void pushEventsToDatabase(int dbGameId, Parser parser, BiMap<Integer, String> idToPlayerMap, int redTeamId, int blueTeamId) throws SQLException { checkArgument(dbGameId >= 0); checkNotNull(parser); checkNotNull(idToPlayerMap); checkArgument(idToPlayerMap.size() > 0); checkArgument(redTeamId >= 0); checkArgument(blueTeamId >= 0); log.info("Pushing game to database for id {}.", dbGameId); // Remember this location before the transaction. log.trace("Creating savepoint."); Savepoint savePoint = connection.setSavepoint(TRANSACTION_SAVEPOINT); // We have to make sure we are putting in valid foreign keys. log.debug("Checking database values for game id {}.", dbGameId); try { checkDatabaseValues(dbGameId, idToPlayerMap); } catch (SQLException | MissingGameInDatabaseException e) { log.error("Error checking database values.", e); connection.rollback(savePoint); throw e; } // Create a demo entry if one does not exist for the players. log.debug("Creating demos if they are not present in the database."); try { createDemoEntriesIfNotPresent(dbGameId, idToPlayerMap); } catch (SQLException e) { log.error("Error creating demos.", e); connection.rollback(savePoint); throw e; } // Now that the integrity is verified, we need to create the rounds and // remember those IDs for committing each round. This should have been // cleaned up if the rounds were removed, so it will error if there are // any rounds detected that should not be there. ArrayList<Integer> roundIds = null; int numRounds = parser.getRoundParsers().size(); try { roundIds = createRoundsOrError(dbGameId, parser, numRounds); log.trace("Round IDs: {}", roundIds.toString()); } catch (SQLException e) { log.error("Error creating demos.", e); connection.rollback(savePoint); throw e; } // Go through the information and put in the many statements to push. try { for (int roundIndex = 0; roundIndex < numRounds; roundIndex++) { RoundParser rp = parser.getRoundParsers().get(roundIndex); int roundId = roundIds.get(roundIndex); log.trace("Committing round {} from parser to event database.", roundId); pushEventsBatch(rp, idToPlayerMap, roundId, redTeamId, blueTeamId); } } catch (SQLException e) { log.error("Error adding events to the database when trying to push.", e); log.error("Likely have malformed round columns that need to manually be deleted."); connection.rollback(savePoint); throw e; } log.info("Events pushed to database successfully."); } /** * Checks that the game ID and players are valid before committing them. * If not, an exception will be thrown. * * @param dbGameId * The ID to check. * * @param idToPlayerMap * The ID to players to check. * * @throws SQLException * If the database has an error submitting statements. * * @throws MissingGameInDatabaseException * If the game is missing from the database. */ private void checkDatabaseValues(int dbGameId, BiMap<Integer, String> idToPlayerMap) throws SQLException { assert dbGameId >= 0; assert idToPlayerMap != null; assert idToPlayerMap.size() > 0; // Check that the game exists. if (!hasRowsFromQuery("SELECT COUNT(*) FROM `wdl`.`games` WHERE game_id = " + dbGameId)) { throw new MissingGameInDatabaseException("Game " + dbGameId + " already exists in the database."); } log.debug("Game {} exists in the database, proceeding to add data.", dbGameId); // Check if player numbers are valid and in the database. int idCount = -1; try (Statement stmt = connection.createStatement()) { // Convert all the numbers to a string so we can join it to the query. ArrayList<String> stringNumbers = new ArrayList<>(); idToPlayerMap.keySet().stream().forEach(i -> stringNumbers.add(Integer.toString(i))); String query = "SELECT COUNT(*) FROM `wdl`.`players` WHERE player_id = " + String.join(" OR player_id = ", stringNumbers); log.trace("Looking for players: {}", query); ResultSet rs = stmt.executeQuery(query); while (rs.next()) { idCount = rs.getInt("COUNT(*)"); } } catch (SQLException e) { log.error("Error trying to find if a player ID is in the database.", e); throw e; } // If we are missing any players, error out. if (idCount != idToPlayerMap.size()) { log.error( "Missing some player from the database, got {} counted players but expected {} (from idToNameMap).", idCount, idToPlayerMap.size()); throw new SQLException("Unexpected player count size: " + idCount + " vs " + idToPlayerMap.size()); } } /** * Checks if the demo places for the games are present in the table, and if * they are not, it creates them. This is needed since we want to preseve the * player demos if the database has to be wiped, without losing the data * of who uploaded it or if it was not uploaded... etc. <br> * <br> * Note that the game ID should be legitimate by the time this is reached, * operating on a non-existing game ID will likely yield SQLExceptions. * * @param dbGameId * The game ID in the database. * * @param idToPlayerMap * The ID to player map. * * @throws SQLException * If the database has trouble being accessed. */ private void createDemoEntriesIfNotPresent(int dbGameId, BiMap<Integer, String> idToPlayerMap) throws SQLException { assert dbGameId >= 0; assert idToPlayerMap != null; assert idToPlayerMap.size() > 0; // Find all the players who already have a demo entry present. Set<Integer> idsWithoutDemos = new HashSet<>(); idToPlayerMap.keySet().forEach(idsWithoutDemos::add); String query = "SELECT fk_player_id FROM `wdl`.`demos` " + "WHERE fk_game_id = " + dbGameId; try (Statement stmt = connection.createStatement()) { ResultSet rs = stmt.executeQuery(query); while (rs.next()) { Integer id = rs.getInt("fk_player_id"); if (idsWithoutDemos.contains(id)) { idsWithoutDemos.remove(id); } } } catch (SQLException e) { log.error("Error searching for players who may have a demo present.", e); throw e; } // For all the players who do not have a demo entry, make one. ArrayList<String> queries = new ArrayList<>(); idsWithoutDemos.forEach(id -> { final String queryInsert = "INSERT INTO `wdl`.`demos`(fk_game_id, fk_player_id) VALUES (" + dbGameId + "," + id.intValue() + ")"; log.trace(">>> {}", queryInsert); queries.add(queryInsert); }); // Submit the queries now. log.debug("Executing game addition queries for {} players.", queries.size()); try (Statement stmt = connection.createStatement()) { for (String queryStr : queries) { log.trace(">>> {}", queryStr); stmt.addBatch(queryStr); } stmt.executeBatch(); } catch (SQLException e) { log.error("Error creating demo entries.", e); throw e; } } /** * Creates the rounds that refer to the game ID provided. If there are * already rounds that exist, this will error out. * * @param dbGameId * The ID to reference for the rounds. * * @param parser * The parser with all the data. * * @param numRounds * The number of rounds to create. * * @return * A list of the new ID's. Creation order is not guaranteed. * * @throws SQLException * If there is any SQL errors or the rounds already exist in the * database. */ private ArrayList<Integer> createRoundsOrError(int dbGameId, Parser parser, int numRounds) throws SQLException { assert dbGameId >= 0; assert numRounds > 0; assert parser != null; // If there are already rounds referencing this game ID, error out. if (hasRowsFromQuery("SELECT round_id FROM `wdl`.`rounds` WHERE fk_game_id = " + dbGameId)) { log.error("Rounds are already in the database."); throw new SQLException("Additional rounds already in database."); } log.debug("No rounds found for game, safe to proceed to make new rounds."); // Create the new rounds now. ArrayList<Integer> roundIds = new ArrayList<>(); try (Statement stmt = connection.createStatement()) { for (int i = 0; i < numRounds; i++) { int mapId = parser.getRoundParsers().get(i).getMapId(); String query = "INSERT INTO `wdl`.`rounds`(fk_game_id, fk_map_id) VALUES (" + dbGameId + "," + mapId + ")"; log.trace(">>> {}", query); stmt.addBatch(query); } stmt.executeBatch(); ResultSet rs = stmt.getGeneratedKeys(); while (rs.next()) { int newId = rs.getInt(1); // First and only column. roundIds.add(newId); log.trace("Round ID retrieved: {}", newId); } } catch (SQLException e) { log.error("Error creating round IDs.", e); throw e; } return roundIds; } /** * Adds all the data to insert into the database into the statement in a * batch format such that at the end of everything, the commands can all * be sent as one. * * @param rp * The stat round parser to extract events from. * * @param idToPlayerMap * A map of id to player names that will be used for matching log * names to a player. * * @param roundId * The round ID of the event data. * * @param redTeamId * The ID of the red team. * * @throws SQLException * If there is any exception calling the query or the database fails * to create the necessary rows. */ private void pushEventsBatch(RoundParser rp, BiMap<Integer, String> idToPlayerMap, int roundId, int redTeamId, int blueTeamId) throws SQLException { assert rp != null; assert idToPlayerMap != null; assert idToPlayerMap.size() > 0; assert roundId >= 0; assert redTeamId >= 0; assert blueTeamId >= 0; try (Statement stmt = connection.createStatement()) { int eventLogId = -1; // Put in the header and remember the event log ID. String query = rp.getHeaderData().getSqlInsertionQuery("wdl", "event_logs", roundId, redTeamId, blueTeamId); log.trace(">>> {}", query); stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); if (!rs.next()) { throw new SQLException( "Unexpected end of result set, should have gotten back the key for the event log ID."); } eventLogId = rs.getInt(1); log.trace("Event log ID retrieved: {}", eventLogId); if (eventLogId == -1) { throw new SQLException("Commit did not yield an event log."); } // Everything from now on in can go into a single batch command. // Put in the players. log.debug("Inserting players into event player table."); BiMap<String, Integer> playerToIdMap = idToPlayerMap.inverse(); log.trace("PlayerToIdMap = {}", playerToIdMap.toString()); for (Player player : rp.getPlayers()) { // Getting the player ID shouldn't fail because we've checked // previously to make sure the player name and ID are valid. String playerLogName = player.getName(); int playerId = playerToIdMap.get(playerLogName); int colorNumber = player.getTeam().ordinal(); String insertQuery = String.format("INSERT INTO `%s`.`%s`(%s) VALUES(%d, %d, \"%s\", %d)", "wdl", "event_players", "fk_event_log_id, fk_player_id, player_log_name, color_number", eventLogId, playerId, playerLogName, colorNumber); log.trace(">>> {}", insertQuery); stmt.addBatch(insertQuery); } log.debug("Committing player queries..."); stmt.executeBatch(); log.debug("Committed player batch events."); // Put in the events. log.debug("Inserting events into event table."); for (Event e : rp.getEvents()) { String queryStr = e.getSqlInsertionQuery("wdl", "events", idToPlayerMap.inverse(), eventLogId); log.trace(">>> {}", queryStr); stmt.addBatch(queryStr); } // Everything is in, deploy it now. log.debug("Committing event queries..."); stmt.executeBatch(); log.debug("All event query events done."); } } }