package io.github.papercore.database; import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import io.github.papercore.PaperCore; import io.github.papercore.models.*; import io.github.papercore.util.LocationUtil; import org.bukkit.Location; import java.util.function.Supplier; import java.io.File; import java.sql.*; import java.time.Instant; import java.util.*; import java.util.concurrent.*; public class SQLiteDatabase implements Database { private final PaperCore plugin; private HikariDataSource dataSource; private final ExecutorService dbExecutor = Executors.newSingleThreadExecutor(r -> { Thread t = new Thread(r, "PaperCore-DB"); t.setDaemon(true); return t; }); public SQLiteDatabase(PaperCore plugin) { this.plugin = plugin; } @Override public void initialize() { try { File folder = plugin.getDataFolder(); if (!folder.exists()) { folder.mkdirs(); } File dbFile = new File(folder, plugin.getConfigManager().getSQLiteFile()); plugin.getLogger().info("Database file location: " + dbFile.getAbsolutePath()); HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:sqlite:" + dbFile.getAbsolutePath()); config.setDriverClassName("org.sqlite.JDBC"); config.setMaximumPoolSize(1); // REQUIRED for SQLite config.setConnectionTimeout(30000); config.setPoolName("PaperCore-SQLite"); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); dataSource = new HikariDataSource(config); plugin.getLogger().info("HikariCP connection pool initialized successfully"); createTables(); plugin.getLogger().info("Database initialized successfully"); } catch (Exception e) { plugin.getLogger().severe("Database initialization error: " + e.getMessage()); e.printStackTrace(); throw new RuntimeException("Failed to initialize database", e); } } private void createTables() { try (Connection c = dataSource.getConnection(); Statement s = c.createStatement()) { s.executeUpdate(""" CREATE TABLE IF NOT EXISTS temp_bans ( player_id TEXT PRIMARY KEY, reason TEXT, banner TEXT, banned_at INTEGER, expires_at INTEGER ) """); s.executeUpdate(""" CREATE TABLE IF NOT EXISTS mutes ( player_id TEXT PRIMARY KEY, reason TEXT, muter TEXT, muted_at INTEGER, expires_at INTEGER ) """); s.executeUpdate(""" CREATE TABLE IF NOT EXISTS homes ( player_id TEXT, name TEXT, location TEXT, PRIMARY KEY (player_id, name) ) """); s.executeUpdate(""" CREATE TABLE IF NOT EXISTS warps ( name TEXT PRIMARY KEY, location TEXT ) """); s.executeUpdate(""" CREATE TABLE IF NOT EXISTS spawn ( id INTEGER PRIMARY KEY, location TEXT ) """); s.executeUpdate(""" CREATE TABLE IF NOT EXISTS moderation_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, type TEXT, target TEXT, moderator TEXT, reason TEXT, timestamp INTEGER ) """); } catch (SQLException e) { throw new RuntimeException(e); } } @Override public void close() { dbExecutor.shutdown(); if (dataSource != null) dataSource.close(); } /* ================= EXEC HELPERS ================= */ private CompletableFuture run(Runnable r) { return CompletableFuture.runAsync(r, dbExecutor); } private CompletableFuture supply(Supplier s) { return CompletableFuture.supplyAsync(s, dbExecutor); } private void exec(String sql, SQLConsumer consumer) { try (Connection c = dataSource.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { if (consumer != null) consumer.accept(ps); ps.executeUpdate(); } catch (SQLException e) { throw new CompletionException(e); } } private T queryOne(String sql, SQLConsumer consumer, SQLMapper mapper) { try (Connection c = dataSource.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) { if (consumer != null) consumer.accept(ps); ResultSet rs = ps.executeQuery(); return rs.next() ? mapper.map(rs) : null; } catch (SQLException e) { throw new CompletionException(e); } } private List queryList(String sql, SQLMapper mapper) { List list = new ArrayList<>(); try (Connection c = dataSource.getConnection(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) list.add(mapper.map(rs)); } catch (SQLException e) { throw new CompletionException(e); } return list; } /* ================= TEMP BANS ================= */ @Override public CompletableFuture saveTempBan(TempBan b) { return run(() -> exec( "REPLACE INTO temp_bans VALUES (?,?,?,?,?)", ps -> { ps.setString(1, b.getPlayerId().toString()); ps.setString(2, b.getReason()); ps.setString(3, b.getBanner()); ps.setLong(4, b.getBannedAt().getEpochSecond()); ps.setLong(5, b.getExpiresAt().getEpochSecond()); } )); } @Override public CompletableFuture getTempBan(UUID id) { return supply(() -> queryOne( "SELECT * FROM temp_bans WHERE player_id=?", ps -> ps.setString(1, id.toString()), rs -> TempBan.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .reason(rs.getString("reason")) .banner(rs.getString("banner")) .bannedAt(Instant.ofEpochSecond(rs.getLong("banned_at"))) .expiresAt(Instant.ofEpochSecond(rs.getLong("expires_at"))) .build() )); } @Override public CompletableFuture removeTempBan(UUID id) { return run(() -> exec( "DELETE FROM temp_bans WHERE player_id=?", ps -> ps.setString(1, id.toString()) )); } @Override public CompletableFuture> getActiveTempBans() { return supply(() -> queryList( "SELECT * FROM temp_bans", rs -> TempBan.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .reason(rs.getString("reason")) .banner(rs.getString("banner")) .bannedAt(Instant.ofEpochSecond(rs.getLong("banned_at"))) .expiresAt(Instant.ofEpochSecond(rs.getLong("expires_at"))) .build() )); } /* ================= HOMES ================= */ @Override public CompletableFuture saveHome(Home home) { return run(() -> exec( "REPLACE INTO homes VALUES (?,?,?)", ps -> { ps.setString(1, home.getPlayerId().toString()); ps.setString(2, home.getName()); ps.setString(3, LocationUtil.locationToString(home.getLocation())); } )); } @Override public CompletableFuture getHome(UUID playerId, String name) { return supply(() -> queryOne( "SELECT * FROM homes WHERE player_id=? AND name=?", ps -> { ps.setString(1, playerId.toString()); ps.setString(2, name); }, rs -> Home.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .name(rs.getString("name")) .location(LocationUtil.stringToLocation(rs.getString("location"))) .build() )); } @Override public CompletableFuture> getHomes(UUID playerId) { return supply(() -> { List list = new ArrayList<>(); try (Connection c = dataSource.getConnection(); PreparedStatement ps = c.prepareStatement("SELECT * FROM homes WHERE player_id=?")) { ps.setString(1, playerId.toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { list.add(Home.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .name(rs.getString("name")) .location(LocationUtil.stringToLocation(rs.getString("location"))) .build()); } } catch (SQLException e) { throw new CompletionException(e); } return list; }); } @Override public CompletableFuture deleteHome(UUID playerId, String name) { return run(() -> exec( "DELETE FROM homes WHERE player_id=? AND name=?", ps -> { ps.setString(1, playerId.toString()); ps.setString(2, name); } )); } @Override public CompletableFuture getHomeCount(UUID playerId) { return supply(() -> { try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT COUNT(*) FROM homes WHERE player_id = ?")) { stmt.setString(1, playerId.toString()); ResultSet rs = stmt.executeQuery(); return rs.next() ? rs.getInt(1) : 0; } catch (SQLException e) { throw new CompletionException(e); } }); } /* ================= MUTES ================= */ @Override public CompletableFuture saveMute(Mute mute) { return run(() -> exec( "REPLACE INTO mutes VALUES (?,?,?,?,?)", ps -> { ps.setString(1, mute.getPlayerId().toString()); ps.setString(2, mute.getReason()); ps.setString(3, mute.getMuter()); ps.setLong(4, mute.getMutedAt().getEpochSecond()); ps.setLong(5, mute.getExpiresAt().getEpochSecond()); } )); } @Override public CompletableFuture getMute(UUID playerId) { return supply(() -> queryOne( "SELECT * FROM mutes WHERE player_id=?", ps -> ps.setString(1, playerId.toString()), rs -> Mute.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .reason(rs.getString("reason")) .muter(rs.getString("muter")) .mutedAt(Instant.ofEpochSecond(rs.getLong("muted_at"))) .expiresAt(Instant.ofEpochSecond(rs.getLong("expires_at"))) .build() )); } @Override public CompletableFuture removeMute(UUID playerId) { return run(() -> exec( "DELETE FROM mutes WHERE player_id=?", ps -> ps.setString(1, playerId.toString()) )); } public CompletableFuture> getActiveMutes() { return supply(() -> queryList( "SELECT * FROM mutes", rs -> Mute.builder() .playerId(UUID.fromString(rs.getString("player_id"))) .reason(rs.getString("reason")) .muter(rs.getString("muter")) .mutedAt(Instant.ofEpochSecond(rs.getLong("muted_at"))) .expiresAt(Instant.ofEpochSecond(rs.getLong("expires_at"))) .build() )); } /* ================= WARPS ================= */ @Override public CompletableFuture saveWarp(Warp w) { return run(() -> exec( "REPLACE INTO warps VALUES (?,?)", ps -> { ps.setString(1, w.getName()); ps.setString(2, LocationUtil.locationToString(w.getLocation())); } )); } @Override public CompletableFuture getWarp(String name) { return supply(() -> queryOne( "SELECT * FROM warps WHERE name=?", ps -> ps.setString(1, name), rs -> Warp.builder() .name(rs.getString("name")) .location(LocationUtil.stringToLocation(rs.getString("location"))) .build() )); } @Override public CompletableFuture> getWarps() { return supply(() -> queryList( "SELECT * FROM warps", rs -> Warp.builder() .name(rs.getString("name")) .location(LocationUtil.stringToLocation(rs.getString("location"))) .build() )); } @Override public CompletableFuture deleteWarp(String name) { return run(() -> exec( "DELETE FROM warps WHERE name=?", ps -> ps.setString(1, name) )); } /* ================= SPAWN ================= */ @Override public CompletableFuture saveSpawn(Location loc) { return run(() -> exec( "REPLACE INTO spawn VALUES (1,?)", ps -> ps.setString(1, LocationUtil.locationToString(loc)) )); } @Override public CompletableFuture getSpawn() { return supply(() -> queryOne( "SELECT location FROM spawn WHERE id=1", null, rs -> LocationUtil.stringToLocation(rs.getString("location")) )); } /* ================= LOGS ================= */ @Override public CompletableFuture logModeration(ModerationLog log) { return run(() -> exec( "INSERT INTO moderation_logs (type,target,moderator,reason,timestamp) VALUES (?,?,?,?,?)", ps -> { ps.setString(1, log.getType()); ps.setString(2, log.getTarget()); ps.setString(3, log.getModerator()); ps.setString(4, log.getReason()); ps.setLong(5, log.getTimestamp().getEpochSecond()); } )); } /* ================= FUNCTIONAL ================= */ private interface SQLConsumer { void accept(T t) throws SQLException; } private interface SQLMapper { T map(ResultSet rs) throws SQLException; } }