From e5a28b13be1e71eda33a1e3f5e8e56e5dad6c063 Mon Sep 17 00:00:00 2001 From: LMBishop <13875753+LMBishop@users.noreply.github.com> Date: Thu, 3 Jun 2021 18:04:28 +0100 Subject: Initial SQL storage implementation --- build.gradle | 2 + .../java/com/leonardobishop/quests/Quests.java | 5 +- .../quests/player/QPlayerManager.java | 4 +- .../quests/storage/MySqlStorageProvider.java | 258 +++++++++++++++++++++ src/main/resources/config.yml | 18 +- 5 files changed, 274 insertions(+), 13 deletions(-) create mode 100644 src/main/java/com/leonardobishop/quests/storage/MySqlStorageProvider.java diff --git a/build.gradle b/build.gradle index d9f61c7b..a6a0f745 100644 --- a/build.gradle +++ b/build.gradle @@ -85,6 +85,8 @@ dependencies { implementation 'org.bstats:bstats-bukkit-lite:1.8' // HikariCP implementation 'com.zaxxer:HikariCP:4.0.3' + // slf4j + implementation 'org.slf4j:slf4j-simple:1.7.30' compileOnly fileTree(dir: 'libs', includes: ['*.jar']) } diff --git a/src/main/java/com/leonardobishop/quests/Quests.java b/src/main/java/com/leonardobishop/quests/Quests.java index cab89f6a..7d74283f 100644 --- a/src/main/java/com/leonardobishop/quests/Quests.java +++ b/src/main/java/com/leonardobishop/quests/Quests.java @@ -166,13 +166,14 @@ public class Quests extends JavaPlugin { questsLogger = new QuestsLogger(this, QuestsLogger.LoggingLevel.INFO); questCompleter = new QuestCompleter(this); + this.generateConfigurations(); + this.setupVersionSpecific(); + taskTypeManager = new TaskTypeManager(this); questManager = new QuestManager(this); qPlayerManager = new QPlayerManager(this); menuController = new MenuController(this); - this.generateConfigurations(); - this.setupVersionSpecific(); super.getCommand("quests").setExecutor(new QuestsCommand(this)); Bukkit.getPluginManager().registerEvents(new PlayerJoinListener(this), this); diff --git a/src/main/java/com/leonardobishop/quests/player/QPlayerManager.java b/src/main/java/com/leonardobishop/quests/player/QPlayerManager.java index f521d2de..bd7ccf37 100644 --- a/src/main/java/com/leonardobishop/quests/player/QPlayerManager.java +++ b/src/main/java/com/leonardobishop/quests/player/QPlayerManager.java @@ -6,6 +6,7 @@ import com.leonardobishop.quests.player.questprogressfile.QPlayerPreferences; import com.leonardobishop.quests.player.questprogressfile.QuestProgress; import com.leonardobishop.quests.player.questprogressfile.QuestProgressFile; import com.leonardobishop.quests.player.questprogressfile.TaskProgress; +import com.leonardobishop.quests.storage.MySqlStorageProvider; import com.leonardobishop.quests.storage.StorageProvider; import com.leonardobishop.quests.storage.YamlStorageProvider; import com.leonardobishop.quests.util.Options; @@ -24,7 +25,8 @@ public class QPlayerManager { private StorageProvider storageProvider; public QPlayerManager(Quests plugin) { - this.storageProvider = new YamlStorageProvider(plugin); + this.storageProvider = new MySqlStorageProvider(plugin, plugin.getConfig().getConfigurationSection("options.storage.database-settings")); + storageProvider.init(); this.plugin = plugin; } diff --git a/src/main/java/com/leonardobishop/quests/storage/MySqlStorageProvider.java b/src/main/java/com/leonardobishop/quests/storage/MySqlStorageProvider.java new file mode 100644 index 00000000..83147a19 --- /dev/null +++ b/src/main/java/com/leonardobishop/quests/storage/MySqlStorageProvider.java @@ -0,0 +1,258 @@ +package com.leonardobishop.quests.storage; + +import com.leonardobishop.quests.Quests; +import com.leonardobishop.quests.player.questprogressfile.QuestProgress; +import com.leonardobishop.quests.player.questprogressfile.QuestProgressFile; +import com.leonardobishop.quests.player.questprogressfile.TaskProgress; +import com.zaxxer.hikari.HikariConfig; +import com.zaxxer.hikari.HikariDataSource; +import org.bukkit.configuration.ConfigurationSection; +import org.bukkit.configuration.MemorySection; +import org.bukkit.configuration.file.YamlConfiguration; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Collections; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.UUID; +import java.util.function.Function; + +public class MySqlStorageProvider implements StorageProvider { + + private static final String CREATE_TABLE_QUEST_PROGRESS = + "CREATE TABLE IF NOT EXISTS `{prefix}quest_progress` (" + + " `id` INT NOT NULL AUTO_INCREMENT," + + " `uuid` VARCHAR(36) NOT NULL," + + " `quest_id` VARCHAR(50) NOT NULL," + + " `started` BOOL NOT NULL," + + " `completed` BOOL NOT NULL," + + " `completed_before` BOOL NOT NULL," + + " `completion_date` BIGINT NOT NULL," + + " PRIMARY KEY (`id`));"; + private static final String CREATE_TABLE_TASK_PROGRESS = + "CREATE TABLE IF NOT EXISTS `{prefix}task_progress` (" + + " `id` INT NOT NULL AUTO_INCREMENT," + + " `uuid` VARCHAR(36) NOT NULL," + + " `quest_id` VARCHAR(50) NOT NULL," + + " `task_id` VARCHAR(50) NOT NULL," + + " `completed` BOOL NOT NULL," + + " `progress` VARCHAR(64) NULL," + + " PRIMARY KEY (`id`));"; + private static final String SELECT_PLAYER_QUEST_PROGRESS = + "SELECT quest_id, started, completed, completed_before, completion_date FROM `{prefix}quest_progress` WHERE uuid=?;"; + private static final String SELECT_PLAYER_TASK_PROGRESS = + "SELECT quest_id, task_id, completed, progress FROM `{prefix}task_progress` WHERE uuid=?;"; + private static final String SELECT_KNOWN_PLAYER_QUEST_PROGRESS = + "SELECT quest_id FROM `{prefix}quest_progress` WHERE uuid=?;"; + private static final String SELECT_KNOWN_PLAYER_TASK_PROGRESS = + "SELECT quest_id, task_id FROM `{prefix}task_progress` WHERE uuid=?;"; + private static final String INSERT_PLAYER_QUEST_PROGRESS = + "INSERT INTO `{prefix}quest_progress` (uuid, quest_id, started, completed, completed_before, completion_date) VALUES (?,?,?,?,?,?)"; + private static final String INSERT_PLAYER_TASK_PROGRESS = + "INSERT INTO `{prefix}task_progress` (uuid, quest_id, task_id, completed, progress) VALUES (?,?,?,?,?)"; + private static final String UPDATE_PLAYER_QUEST_PROGRESS = + "UPDATE `{prefix}quest_progress` SET started=?, completed=?, completed_before=?, completion_date=? WHERE uuid=? AND quest_id=?"; + private static final String UPDATE_PLAYER_TASK_PROGRESS = + "UPDATE `{prefix}task_progress` SET completed=?, progress=? WHERE uuid=? AND quest_id=? AND task_id=?"; + + private final HikariDataSource hikari; + private final String prefix; + private final Quests plugin; + private final Function statementProcessor; + + public MySqlStorageProvider(Quests plugin, ConfigurationSection configuration) { + this.plugin = plugin; + if (configuration == null) { + configuration = new YamlConfiguration(); + } + + String address = configuration.getString("network.address", "localhost:3306"); + String database = configuration.getString("network.database", "minecraft"); + String url = "jdbc:mysql://" + address + "/" + database; + + HikariConfig config = new HikariConfig(); + config.setPoolName("quests-hikari"); + + config.setUsername(configuration.getString("network.username", "root")); + config.setPassword(configuration.getString("network.password", "")); + config.setJdbcUrl(url); + config.setMaximumPoolSize(configuration.getInt("connection-pool-settings.maximum-pool-size", 8)); + config.setMinimumIdle(configuration.getInt("connection-pool-settings.minimum-idle", 8)); + config.setMaxLifetime(configuration.getInt("connection-pool-settings.maximum-lifetime", 1800000)); + config.setConnectionTimeout(configuration.getInt("connection-pool-settings.connection-timeout", 5000)); + + config.addDataSourceProperty("cachePrepStmts", true); + config.addDataSourceProperty("prepStmtCacheSize", 250); + config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048); + config.addDataSourceProperty("useServerPrepStmts", true); + config.addDataSourceProperty("useLocalSessionState", true); + config.addDataSourceProperty("rewriteBatchedStatements", true); + config.addDataSourceProperty("cacheResultSetMetadata", true); + config.addDataSourceProperty("cacheServerConfiguration", true); + config.addDataSourceProperty("elideSetAutoCommits", true); + config.addDataSourceProperty("maintainTimeStats", false); + + this.hikari = new HikariDataSource(config); + this.prefix = configuration.getString("database-settings.table-prefix", "quests_"); + this.statementProcessor = s -> s.replace("{prefix}", prefix); + } + + @Override + public void init() { + try (Connection connection = hikari.getConnection()) { + try (Statement s = connection.createStatement()) { + plugin.getQuestsLogger().debug("Creating default tables"); + s.addBatch(this.statementProcessor.apply(CREATE_TABLE_QUEST_PROGRESS)); + s.addBatch(this.statementProcessor.apply(CREATE_TABLE_TASK_PROGRESS)); + + s.executeBatch(); + } + } catch (SQLException e) { + e.printStackTrace(); + } + } + + @Override + public QuestProgressFile loadProgressFile(UUID uuid) { + QuestProgressFile questProgressFile = new QuestProgressFile(uuid, plugin); + try (Connection connection = hikari.getConnection()) { + plugin.getQuestsLogger().debug("Querying player " + uuid); + Map questProgressMap = new HashMap<>(); + try (PreparedStatement ps = connection.prepareStatement(this.statementProcessor.apply(SELECT_PLAYER_QUEST_PROGRESS))) { + ps.setString(1, uuid.toString()); + + try (ResultSet rs = ps.executeQuery()) { + while (rs.next()) { + String questId = rs.getString(1); + boolean started = rs.getBoolean(2); + boolean completed = rs.getBoolean(3); + boolean completedBefore = rs.getBoolean(4); + long completionDate = rs.getLong(5); + + QuestProgress questProgress = new QuestProgress(plugin, questId, completed, completedBefore, completionDate, uuid, started); + questProgressMap.put(questId, questProgress); + } + } + } + try (PreparedStatement ps = connection.prepareStatement(this.statementProcessor.apply(SELECT_PLAYER_TASK_PROGRESS))) { + ps.setString(1, uuid.toString()); + + try (ResultSet rs = ps.executeQuery()) { + while (rs.next()) { + String questId = rs.getString(1); + String taskId = rs.getString(2); + boolean completed = rs.getBoolean(3); + Object progress = rs.getObject(4); + + QuestProgress linkedQuestProgress = questProgressMap.get(questId); + if (linkedQuestProgress == null) continue; // lost quest progress ? + TaskProgress questProgress = new TaskProgress(linkedQuestProgress, taskId, progress, uuid, completed); + linkedQuestProgress.addTaskProgress(questProgress); + } + } + } + for (QuestProgress questProgress : questProgressMap.values()) { + questProgressFile.addQuestProgress(questProgress); + } + } catch (SQLException e) { + plugin.getQuestsLogger().severe("Failed to load player: " + uuid + "!"); + e.printStackTrace(); + } + return questProgressFile; + } + + @Override + public void saveProgressFile(UUID uuid, QuestProgressFile questProgressFile) { + try (Connection connection = hikari.getConnection()) { + plugin.getQuestsLogger().debug("Saving player " + uuid); + List knownQuestIds = new ArrayList<>(); + Map> knownTaskIds = new HashMap<>(); + try (PreparedStatement ps = connection.prepareStatement(this.statementProcessor.apply(SELECT_KNOWN_PLAYER_QUEST_PROGRESS))) { + ps.setString(1, uuid.toString()); + + try (ResultSet rs = ps.executeQuery()) { + while (rs.next()) { + knownQuestIds.add(rs.getString(0)); + } + } + } + try (PreparedStatement ps = connection.prepareStatement(this.statementProcessor.apply(SELECT_KNOWN_PLAYER_TASK_PROGRESS))) { + ps.setString(1, uuid.toString()); + + try (ResultSet rs = ps.executeQuery()) { + while (rs.next()) { + String questId = rs.getString(0); + String taskId = rs.getString(1); + + knownTaskIds.putIfAbsent(questId, new ArrayList<>()); + knownTaskIds.get(questId).add(taskId); + } + } + } + + try (PreparedStatement insertQuestProgress = connection.prepareStatement(this.statementProcessor.apply(INSERT_PLAYER_QUEST_PROGRESS)); + PreparedStatement insertTaskProgress = connection.prepareStatement(this.statementProcessor.apply(INSERT_PLAYER_TASK_PROGRESS)); + PreparedStatement updateQuestProgress = connection.prepareStatement(this.statementProcessor.apply(UPDATE_PLAYER_QUEST_PROGRESS)); + PreparedStatement updateTaskProgress = connection.prepareStatement(this.statementProcessor.apply(UPDATE_PLAYER_TASK_PROGRESS))) { + + List questProgressValues = new ArrayList<>(questProgressFile.getAllQuestProgress()); + for (QuestProgress questProgress : questProgressValues) { + String questId = questProgress.getQuestId(); + if (knownQuestIds.contains(questId)) { + updateQuestProgress.setBoolean(1, questProgress.isStarted()); + updateQuestProgress.setBoolean(2, questProgress.isCompleted()); + updateQuestProgress.setBoolean(3, questProgress.isCompletedBefore()); + updateQuestProgress.setLong(4, questProgress.getCompletionDate()); + updateQuestProgress.setString(5, uuid.toString()); + updateQuestProgress.setString(6, questId); + updateQuestProgress.addBatch(); + } else { + insertQuestProgress.setString(1, uuid.toString()); + insertQuestProgress.setString(2, questProgress.getQuestId()); + insertQuestProgress.setBoolean(3, questProgress.isStarted()); + insertQuestProgress.setBoolean(4, questProgress.isCompleted()); + insertQuestProgress.setBoolean(5, questProgress.isCompletedBefore()); + insertQuestProgress.setLong(6, questProgress.getCompletionDate()); + insertQuestProgress.addBatch(); + } + List taskIds = knownTaskIds.getOrDefault(questProgress.getQuestId(), Collections.emptyList()); + for (TaskProgress taskProgress : questProgress.getTaskProgress()) { + if (taskIds.contains(taskProgress.getTaskId())) { + updateTaskProgress.setBoolean(1, taskProgress.isCompleted()); + updateTaskProgress.setObject(2, taskProgress.getProgress()); + updateTaskProgress.setString(3, uuid.toString()); + updateTaskProgress.setString(4, questProgress.getQuestId()); + updateTaskProgress.setString(5, taskProgress.getTaskId()); + updateTaskProgress.addBatch(); + } else { + insertTaskProgress.setString(1, uuid.toString()); + insertTaskProgress.setString(2, questProgress.getQuestId()); + insertTaskProgress.setString(3, taskProgress.getTaskId()); + insertTaskProgress.setBoolean(4, taskProgress.isCompleted()); + insertTaskProgress.setObject(5, taskProgress.getProgress()); + insertTaskProgress.addBatch(); + } + } + } + + System.out.println(insertQuestProgress); + insertQuestProgress.executeBatch(); + System.out.println(insertTaskProgress); + insertTaskProgress.executeBatch(); + System.out.println(updateQuestProgress); + updateQuestProgress.executeBatch(); + System.out.println(updateTaskProgress); + updateTaskProgress.executeBatch(); + } + } catch (SQLException e) { + plugin.getQuestsLogger().severe("Failed to save player: " + uuid + "!"); + e.printStackTrace(); + } + } +} diff --git a/src/main/resources/config.yml b/src/main/resources/config.yml index b4c94332..b2ab5871 100644 --- a/src/main/resources/config.yml +++ b/src/main/resources/config.yml @@ -240,25 +240,23 @@ options: provider: "yaml" # The following is only applicable for database storage providers (e.g. mysql) database-settings: - # The name of the database. This database should already exist! - database-name: "minecraft" network: + # The name of the database. This database should already exist! + database: "minecraft" username: "root" password: "" - address: "localhost" - port: 3306 + # Address should be in the format ip:port (just like the game itself) + address: "localhost:3306" # This plugin uses 'HikariCP' for connection management, the pooling configuration can be changed here connection-pool-settings: # The maximum number of connections to keep open with the database (def=8) maximum-pool-size: 8 # The minimum number of connections to keep open with the database (def=8) - minimum-pool-size: 8 + minimum-idle: 8 # The maximum time (in milliseconds) to keep a single connection open (def=1800000 - 30 min) - connection-lifetime: 1800000 - # The time (in milliseconds) to ping the database (0 to disable, def=0) - heartbeat-time: 0 - # The time (in milliseconds) the plugin will wait for a response by the database (def=0) - timeout: 5000 + maximum-lifetime: 1800000 + # The time (in milliseconds) the plugin will wait for a response by the database (def=500) + connection-timeout: 5000 # The prefix each table will use table-prefix: "quests_" -- cgit v1.2.3-70-g09d2