|
|
|
|
@ -25,6 +25,8 @@ import net.william278.husksync.HuskSync;
|
|
|
|
|
import net.william278.husksync.adapter.DataAdapter;
|
|
|
|
|
import net.william278.husksync.data.DataSnapshot;
|
|
|
|
|
import net.william278.husksync.user.User;
|
|
|
|
|
import net.william278.husksync.util.OptionalUtil;
|
|
|
|
|
import org.inksnow.cputil.db.AuroraDatabase;
|
|
|
|
|
import org.jetbrains.annotations.Blocking;
|
|
|
|
|
import org.jetbrains.annotations.NotNull;
|
|
|
|
|
|
|
|
|
|
@ -33,6 +35,7 @@ import java.sql.*;
|
|
|
|
|
import java.time.OffsetDateTime;
|
|
|
|
|
import java.util.*;
|
|
|
|
|
import java.util.logging.Level;
|
|
|
|
|
import java.util.stream.Collectors;
|
|
|
|
|
|
|
|
|
|
import static net.william278.husksync.config.Settings.DatabaseSettings;
|
|
|
|
|
|
|
|
|
|
@ -40,7 +43,7 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
|
|
|
|
|
private static final String DATA_POOL_NAME = "HuskSyncHikariPool";
|
|
|
|
|
private final String flavor;
|
|
|
|
|
private final String driverClass;
|
|
|
|
|
private final org.inksnow.cputil.db.Database databaseType;
|
|
|
|
|
private HikariDataSource dataSource;
|
|
|
|
|
|
|
|
|
|
public PostgresDatabase(@NotNull HuskSync plugin) {
|
|
|
|
|
@ -48,7 +51,7 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
|
|
|
|
|
final Type type = plugin.getSettings().getDatabase().getType();
|
|
|
|
|
this.flavor = type.getProtocol();
|
|
|
|
|
this.driverClass = "org.postgresql.Driver";
|
|
|
|
|
this.databaseType = new org.inksnow.cputil.db.postgres.PostgresqlDatabase();
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
/**
|
|
|
|
|
@ -71,48 +74,44 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
public void initialize() throws IllegalStateException {
|
|
|
|
|
// Initialize the Hikari pooled connection
|
|
|
|
|
final DatabaseSettings.DatabaseCredentials credentials = plugin.getSettings().getDatabase().getCredentials();
|
|
|
|
|
dataSource = new HikariDataSource();
|
|
|
|
|
dataSource.setDriverClassName(driverClass);
|
|
|
|
|
dataSource.setJdbcUrl(String.format("jdbc:%s://%s:%s/%s%s",
|
|
|
|
|
flavor,
|
|
|
|
|
credentials.getHost(),
|
|
|
|
|
credentials.getPort(),
|
|
|
|
|
credentials.getDatabase(),
|
|
|
|
|
credentials.getParameters()
|
|
|
|
|
));
|
|
|
|
|
|
|
|
|
|
// Authenticate with the database
|
|
|
|
|
dataSource.setUsername(credentials.getUsername());
|
|
|
|
|
dataSource.setPassword(credentials.getPassword());
|
|
|
|
|
|
|
|
|
|
// Set connection pool options
|
|
|
|
|
final DatabaseSettings.PoolSettings pool = plugin.getSettings().getDatabase().getConnectionPool();
|
|
|
|
|
dataSource.setMaximumPoolSize(pool.getMaximumPoolSize());
|
|
|
|
|
dataSource.setMinimumIdle(pool.getMinimumIdle());
|
|
|
|
|
dataSource.setMaxLifetime(pool.getMaximumLifetime());
|
|
|
|
|
dataSource.setKeepaliveTime(pool.getKeepaliveTime());
|
|
|
|
|
dataSource.setConnectionTimeout(pool.getConnectionTimeout());
|
|
|
|
|
dataSource.setPoolName(DATA_POOL_NAME);
|
|
|
|
|
|
|
|
|
|
// Set additional connection pool properties
|
|
|
|
|
final Properties properties = new Properties();
|
|
|
|
|
properties.putAll(
|
|
|
|
|
Map.of("cachePrepStmts", "true",
|
|
|
|
|
"prepStmtCacheSize", "250",
|
|
|
|
|
"prepStmtCacheSqlLimit", "2048",
|
|
|
|
|
"useServerPrepStmts", "true",
|
|
|
|
|
"useLocalSessionState", "true",
|
|
|
|
|
"useLocalTransactionState", "true"
|
|
|
|
|
));
|
|
|
|
|
properties.putAll(
|
|
|
|
|
Map.of(
|
|
|
|
|
"rewriteBatchedStatements", "true",
|
|
|
|
|
"cacheResultSetMetadata", "true",
|
|
|
|
|
"cacheServerConfiguration", "true",
|
|
|
|
|
"elideSetAutoCommits", "true",
|
|
|
|
|
"maintainTimeStats", "false")
|
|
|
|
|
);
|
|
|
|
|
dataSource.setDataSourceProperties(properties);
|
|
|
|
|
|
|
|
|
|
try {
|
|
|
|
|
dataSource = AuroraDatabase.builder()
|
|
|
|
|
.databaseType(databaseType)
|
|
|
|
|
.jdbcUrl(String.format("jdbc:%s://%s:%s/%s%s",
|
|
|
|
|
flavor,
|
|
|
|
|
credentials.getHost(),
|
|
|
|
|
credentials.getPort(),
|
|
|
|
|
credentials.getDatabase(),
|
|
|
|
|
credentials.getParameters()
|
|
|
|
|
))
|
|
|
|
|
.extension(dataSource -> {
|
|
|
|
|
final DatabaseSettings.PoolSettings pool = plugin.getSettings().getDatabase().getConnectionPool();
|
|
|
|
|
dataSource.setMaximumPoolSize(pool.getMaximumPoolSize());
|
|
|
|
|
dataSource.setMinimumIdle(pool.getMinimumIdle());
|
|
|
|
|
dataSource.setMaxLifetime(pool.getMaximumLifetime());
|
|
|
|
|
dataSource.setKeepaliveTime(pool.getKeepaliveTime());
|
|
|
|
|
dataSource.setConnectionTimeout(pool.getConnectionTimeout());
|
|
|
|
|
dataSource.setPoolName(DATA_POOL_NAME);
|
|
|
|
|
})
|
|
|
|
|
.username(credentials.getUsername())
|
|
|
|
|
.password(credentials.getPassword())
|
|
|
|
|
.driverProperty("cachePrepStmts", "true")
|
|
|
|
|
.driverProperty("prepStmtCacheSize", "250")
|
|
|
|
|
.driverProperty("prepStmtCacheSqlLimit", "2048")
|
|
|
|
|
.driverProperty("useServerPrepStmts", "true")
|
|
|
|
|
.driverProperty("useLocalSessionState", "true")
|
|
|
|
|
.driverProperty("useLocalTransactionState", "true")
|
|
|
|
|
|
|
|
|
|
.driverProperty("rewriteBatchedStatements", "true")
|
|
|
|
|
.driverProperty("cacheResultSetMetadata", "true")
|
|
|
|
|
.driverProperty("cacheServerConfiguration", "true")
|
|
|
|
|
.driverProperty("elideSetAutoCommits", "true")
|
|
|
|
|
.driverProperty("maintainTimeStats", "false")
|
|
|
|
|
.build();
|
|
|
|
|
} catch (IOException e) {
|
|
|
|
|
throw new IllegalStateException("Failed to initialize the Aurora database", e);
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
// Prepare database schema; make tables if they don't exist
|
|
|
|
|
try (Connection connection = dataSource.getConnection()) {
|
|
|
|
|
@ -134,15 +133,16 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Blocking
|
|
|
|
|
@Override
|
|
|
|
|
public void ensureUser(@NotNull User user) {
|
|
|
|
|
getUser(user.getUuid()).ifPresentOrElse(
|
|
|
|
|
OptionalUtil.ifPresentOrElse(getUser(user.getUuid()),
|
|
|
|
|
existingUser -> {
|
|
|
|
|
if (!existingUser.getUsername().equals(user.getUsername())) {
|
|
|
|
|
// Update a user's name if it has changed in the database
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
UPDATE "%users_table%"
|
|
|
|
|
SET "username"=?
|
|
|
|
|
WHERE "uuid"=?"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"UPDATE \"%users_table%\" " +
|
|
|
|
|
"SET \"username\"=? " +
|
|
|
|
|
"WHERE \"uuid\"=?"
|
|
|
|
|
))) {
|
|
|
|
|
|
|
|
|
|
statement.setString(1, user.getUsername());
|
|
|
|
|
statement.setObject(2, existingUser.getUuid());
|
|
|
|
|
@ -157,9 +157,10 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
() -> {
|
|
|
|
|
// Insert new player data into the database
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
INSERT INTO "%users_table%" ("uuid","username")
|
|
|
|
|
VALUES (?,?);"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"INSERT INTO \"%users_table%\" (\"uuid\",\"username\") " +
|
|
|
|
|
"VALUES (?,?);"
|
|
|
|
|
))) {
|
|
|
|
|
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.setString(2, user.getUsername());
|
|
|
|
|
@ -176,10 +177,11 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public Optional<User> getUser(@NotNull UUID uuid) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
SELECT "uuid", "username"
|
|
|
|
|
FROM "%users_table%"
|
|
|
|
|
WHERE "uuid"=?"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"SELECT \"uuid\", \"username\" " +
|
|
|
|
|
"FROM \"%users_table%\" " +
|
|
|
|
|
"WHERE \"uuid\"=?"
|
|
|
|
|
))) {
|
|
|
|
|
|
|
|
|
|
statement.setObject(1, uuid);
|
|
|
|
|
|
|
|
|
|
@ -199,10 +201,11 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public Optional<User> getUserByName(@NotNull String username) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
SELECT "uuid", "username"
|
|
|
|
|
FROM "%users_table%"
|
|
|
|
|
WHERE "username"=?"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"SELECT \"uuid\", \"username\" " +
|
|
|
|
|
"FROM \"%users_table%\" " +
|
|
|
|
|
"WHERE \"username\"=?"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setString(1, username);
|
|
|
|
|
|
|
|
|
|
final ResultSet resultSet = statement.executeQuery();
|
|
|
|
|
@ -221,12 +224,13 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public Optional<DataSnapshot.Packed> getLatestSnapshot(@NotNull User user) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
SELECT "version_uuid", "timestamp", "data"
|
|
|
|
|
FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=?
|
|
|
|
|
ORDER BY "timestamp" DESC
|
|
|
|
|
LIMIT 1;"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"SELECT \"version_uuid\", \"timestamp\", \"data\" " +
|
|
|
|
|
"FROM \"%user_data_table%\" " +
|
|
|
|
|
"WHERE \"player_uuid\"=? " +
|
|
|
|
|
"ORDER BY \"timestamp\" DESC " +
|
|
|
|
|
"LIMIT 1;"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
final ResultSet resultSet = statement.executeQuery();
|
|
|
|
|
if (resultSet.next()) {
|
|
|
|
|
@ -250,11 +254,12 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
public List<DataSnapshot.Packed> getAllSnapshots(@NotNull User user) {
|
|
|
|
|
final List<DataSnapshot.Packed> retrievedData = Lists.newArrayList();
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
SELECT "version_uuid", "timestamp", "data"
|
|
|
|
|
FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=?
|
|
|
|
|
ORDER BY "timestamp" DESC;"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"SELECT \"version_uuid\", \"timestamp\", \"data\" " +
|
|
|
|
|
"FROM \"%user_data_table%\" " +
|
|
|
|
|
"WHERE \"player_uuid\"=? " +
|
|
|
|
|
"ORDER BY \"timestamp\" DESC;"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
final ResultSet resultSet = statement.executeQuery();
|
|
|
|
|
while (resultSet.next()) {
|
|
|
|
|
@ -277,12 +282,13 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public Optional<DataSnapshot.Packed> getSnapshot(@NotNull User user, @NotNull UUID versionUuid) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
SELECT "version_uuid", "timestamp", "data"
|
|
|
|
|
FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=? AND "version_uuid"=?
|
|
|
|
|
ORDER BY "timestamp" DESC
|
|
|
|
|
LIMIT 1;"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"SELECT \"version_uuid\", \"timestamp\", \"data\" " +
|
|
|
|
|
"FROM \"%user_data_table%\" " +
|
|
|
|
|
"WHERE \"player_uuid\"=? AND \"version_uuid\"=? " +
|
|
|
|
|
"ORDER BY \"timestamp\" DESC " +
|
|
|
|
|
"LIMIT 1;"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.setObject(2, versionUuid);
|
|
|
|
|
final ResultSet resultSet = statement.executeQuery();
|
|
|
|
|
@ -304,17 +310,18 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
protected void rotateSnapshots(@NotNull User user) {
|
|
|
|
|
final List<DataSnapshot.Packed> unpinnedUserData = getAllSnapshots(user).stream()
|
|
|
|
|
.filter(dataSnapshot -> !dataSnapshot.isPinned()).toList();
|
|
|
|
|
.filter(dataSnapshot -> !dataSnapshot.isPinned())
|
|
|
|
|
.collect(Collectors.toList());
|
|
|
|
|
final int maxSnapshots = plugin.getSettings().getSynchronization().getMaxUserDataSnapshots();
|
|
|
|
|
if (unpinnedUserData.size() > maxSnapshots) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
DELETE FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=?
|
|
|
|
|
AND "pinned" = FALSE
|
|
|
|
|
ORDER BY "timestamp" ASC
|
|
|
|
|
LIMIT %entry_count%;""".replace("%entry_count%",
|
|
|
|
|
Integer.toString(unpinnedUserData.size() - maxSnapshots))))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"DELETE FROM \"%user_data_table%\"\n" +
|
|
|
|
|
"WHERE \"player_uuid\"=?\n" +
|
|
|
|
|
"AND \"pinned\" = FALSE\n" +
|
|
|
|
|
"ORDER BY \"timestamp\" ASC\n" +
|
|
|
|
|
"LIMIT " + (unpinnedUserData.size() - maxSnapshots) + ";"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.executeUpdate();
|
|
|
|
|
}
|
|
|
|
|
@ -328,10 +335,11 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public boolean deleteSnapshot(@NotNull User user, @NotNull UUID versionUuid) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
DELETE FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=? AND "version_uuid"=?
|
|
|
|
|
LIMIT 1;"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"DELETE FROM \"%user_data_table%\" " +
|
|
|
|
|
"WHERE \"player_uuid\"=? AND \"version_uuid\"=? " +
|
|
|
|
|
"LIMIT 1;"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.setString(2, versionUuid.toString());
|
|
|
|
|
return statement.executeUpdate() > 0;
|
|
|
|
|
@ -346,15 +354,16 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
protected void rotateLatestSnapshot(@NotNull User user, @NotNull OffsetDateTime within) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
DELETE FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=? AND "timestamp" = (
|
|
|
|
|
SELECT "timestamp"
|
|
|
|
|
FROM "%user_data_table%"
|
|
|
|
|
WHERE "player_uuid"=? AND "timestamp" > ? AND "pinned" = FALSE
|
|
|
|
|
ORDER BY "timestamp" ASC
|
|
|
|
|
LIMIT 1
|
|
|
|
|
);"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"DELETE FROM \"%user_data_table%\" " +
|
|
|
|
|
"WHERE \"player_uuid\"=? AND \"timestamp\" = ( " +
|
|
|
|
|
" SELECT \"timestamp\" " +
|
|
|
|
|
" FROM \"%user_data_table%\" " +
|
|
|
|
|
" WHERE \"player_uuid\"=? AND \"timestamp\" > ? AND \"pinned\" = FALSE " +
|
|
|
|
|
" ORDER BY \"timestamp\" ASC " +
|
|
|
|
|
" LIMIT 1 " +
|
|
|
|
|
");"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.setObject(2, user.getUuid());
|
|
|
|
|
statement.setTimestamp(3, Timestamp.from(within.toInstant()));
|
|
|
|
|
@ -369,10 +378,11 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
protected void createSnapshot(@NotNull User user, @NotNull DataSnapshot.Packed data) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
INSERT INTO "%user_data_table%"
|
|
|
|
|
("player_uuid","version_uuid","timestamp","save_cause","pinned","data")
|
|
|
|
|
VALUES (?,?,?,?,?,?);"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"INSERT INTO \"%user_data_table%\" " +
|
|
|
|
|
"(\"player_uuid\",\"version_uuid\",\"timestamp\",\"save_cause\",\"pinned\",\"data\") " +
|
|
|
|
|
"VALUES (?,?,?,?,?,?);"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setObject(1, user.getUuid());
|
|
|
|
|
statement.setObject(2, data.getId());
|
|
|
|
|
statement.setTimestamp(3, Timestamp.from(data.getTimestamp().toInstant()));
|
|
|
|
|
@ -390,11 +400,12 @@ public class PostgresDatabase extends Database {
|
|
|
|
|
@Override
|
|
|
|
|
public void updateSnapshot(@NotNull User user, @NotNull DataSnapshot.Packed data) {
|
|
|
|
|
try (Connection connection = getConnection()) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables("""
|
|
|
|
|
UPDATE "%user_data_table%"
|
|
|
|
|
SET "save_cause"=?,"pinned"=?,"data"=?
|
|
|
|
|
WHERE "player_uuid"=? AND "version_uuid"=?
|
|
|
|
|
LIMIT 1;"""))) {
|
|
|
|
|
try (PreparedStatement statement = connection.prepareStatement(formatStatementTables(
|
|
|
|
|
"UPDATE \"%user_data_table%\" " +
|
|
|
|
|
"SET \"save_cause\"=?,\"pinned\"=?,\"data\"=? " +
|
|
|
|
|
"WHERE \"player_uuid\"=? AND \"version_uuid\"=? " +
|
|
|
|
|
"LIMIT 1;"
|
|
|
|
|
))) {
|
|
|
|
|
statement.setString(1, data.getSaveCause().name());
|
|
|
|
|
statement.setBoolean(2, data.isPinned());
|
|
|
|
|
statement.setBytes(3, data.asBytes(plugin));
|
|
|
|
|
|
InkerBot