· 5 years ago · Feb 05, 2020, 05:14 PM
1package com.massivecraft.factions;
2
3import com.massivecraft.factions.zcore.MPlugin;
4import org.bukkit.scheduler.BukkitRunnable;
5import org.json.JSONObject;
6
7import java.io.BufferedReader;
8import java.io.IOException;
9import java.io.InputStreamReader;
10import java.net.URL;
11import java.sql.Connection;
12import java.sql.DriverManager;
13import java.sql.PreparedStatement;
14import java.sql.SQLException;
15import java.util.UUID;
16import java.util.stream.Collectors;
17
18public class IntegrationSQL {
19
20 private Connection connection;
21
22 private String host, database, username, password;
23
24 private int port;
25
26 private MPlugin plugin;
27
28 // Database update task
29 private BukkitRunnable task;
30
31 private final String createPlayersString = "CREATE TABLE IF NOT EXISTS `players` (`uuid` varchar(36) PRIMARY KEY, `online_uuid` varchar(32), `online` boolean, `name` text, `power` int, `hasfaction` boolean, `fid` text, `frole` text)";
32 private final String createFactionsString = "CREATE TABLE IF NOT EXISTS `factions` (`id` int PRIMARY KEY, `tag` text, `description` text, `size` int, `power` int, `maxpower` int, `claims` int, `level` int, `xp` int)";
33
34 private String updateFactionsString;
35 private String updatePlayersString;
36
37 private static final char[] BANNED_CHARACTERS = {'\"', '\'', '|', '`', '´'};
38
39 public IntegrationSQL(MPlugin plugin) {
40 this.plugin = plugin;
41
42 this.host = plugin.getConfig().getString("sql.host");
43 this.database = plugin.getConfig().getString("sql.database");
44 this.username = plugin.getConfig().getString("sql.username");
45 this.password = plugin.getConfig().getString("sql.password");
46
47 this.port = plugin.getConfig().getInt("sql.port");
48
49 try {
50 openConnection();
51 } catch (SQLException | ClassNotFoundException e) {
52 e.printStackTrace();
53 }
54
55 updatePlayersString = "INSERT INTO `players` (`uuid`, `online_uuid`, `online`, `name`, `power`, `hasfaction`, `fid`, `frole`) VALUES (?, ?, ?, ?, ?, ?, ?, ?) " +
56 "ON DUPLICATE KEY UPDATE online_uuid = ?, online = ?, name = ?, power = ?, hasfaction = ?, fid = ?, frole = ?";
57
58 updateFactionsString = "INSERT INTO `factions` (`id`, `tag`, `description`, `size`, `power`, `maxpower`, `claims`, `level`, `xp`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) " +
59 "ON DUPLICATE KEY UPDATE tag = ?, description = ?, size = ?, power = ?, maxpower = ?, claims = ?, level = ?, xp = ?";
60
61 setup();
62 }
63
64 private void setup() {
65 try {
66 PreparedStatement createPlayersStatement = connection.prepareStatement(createPlayersString);
67 PreparedStatement createFactionsStatement = connection.prepareStatement(createFactionsString);
68
69 createPlayersStatement.execute();
70 createFactionsStatement.execute();
71 } catch (SQLException e) {
72 plugin.getLogger().warning("Could not create MySQL tables with IntegrationSQL.");
73 plugin.getLogger().warning("INFO: " + e.getMessage());
74 }
75 }
76
77 // Start updating the database with Factions data
78 public void startUpdate() {
79 task = new BukkitRunnable() {
80 @Override
81 public void run() {
82 if (plugin.getConfig().getBoolean("sql.inform"))
83 plugin.getLogger().info("Updating sql database..");
84
85 update();
86 }
87 };
88
89 int updateTime = plugin.getConfig().getInt("sql.update-period", 10) * 20;
90
91 task.runTaskTimerAsynchronously(plugin, 0, updateTime);
92 }
93
94 public void openConnection() throws SQLException, ClassNotFoundException {
95 if (connection != null && !connection.isClosed())
96 return;
97
98 synchronized (this) {
99 if (connection != null && !connection.isClosed())
100 return;
101
102 Class.forName("com.mysql.jdbc.Driver");
103 connection = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.port + "/" + this.database, this.username, this.password);
104 }
105 }
106
107 // Update table data
108 public void update() {
109 try (PreparedStatement updatePlayersStatement = connection.prepareStatement(updatePlayersString)) {
110
111 // Update players
112 for (FPlayer fPlayer : FPlayers.getInstance().getAllFPlayers()) {
113 String onlineUUID = getOnlineUUID(fPlayer.getName()) != null ? getOnlineUUID(fPlayer.getName()).toString() : "NULL";
114
115 // Update db
116 // (`uuid`, `online_uuid`, `online`, `name`, `power`, `hasfaction`, `fid`, `frole`)
117 updatePlayersStatement.setString(1, fPlayer.getAccountId());
118 updatePlayersStatement.setString(2, onlineUUID);
119 updatePlayersStatement.setBoolean(3, fPlayer.isOnline());
120 updatePlayersStatement.setString(4, fPlayer.getName());
121 updatePlayersStatement.setInt(5, (int) fPlayer.getPower());
122 updatePlayersStatement.setBoolean(6, fPlayer.hasFaction());
123 updatePlayersStatement.setString(7, fPlayer.hasFaction() ? fPlayer.getFactionId() : "NULL");
124 updatePlayersStatement.setString(8, fPlayer.hasFaction() ? fPlayer.getRolePrefix() : "NULL");
125 }
126 } catch (SQLException e) {
127 plugin.getLogger().warning("Could not update players in MySQL tables with IntegrationSQL.");
128 plugin.getLogger().warning("INFO: " + e.getMessage());
129 return;
130 }
131
132 try (PreparedStatement updateFactionsStatement = connection.prepareStatement(updateFactionsString)) {
133 // Update factions
134 for (Faction faction : Factions.getInstance().getAllFactions()) {
135 // Filter characters that cause problems with sql commands, dunno how to escape them
136
137 String tag = faction.getTag();
138
139 for (char c : BANNED_CHARACTERS)
140 tag = tag.replace(c, ' ');
141
142 String desc = faction.getDescription();
143
144 for (char c : BANNED_CHARACTERS)
145 desc = desc.replace(c, ' ');
146
147 updateFactionsStatement.setInt(1, Integer.parseInt(faction.getId()));
148 updateFactionsStatement.setString(2, tag);
149 updateFactionsStatement.setString(3, desc);
150 updateFactionsStatement.setInt(4, faction.getSize());
151 updateFactionsStatement.setInt(5, (int) faction.getPower());
152 updateFactionsStatement.setInt(6, (int) faction.getPowerMax());
153 updateFactionsStatement.setInt(7, faction.getLandRounded());
154 updateFactionsStatement.setInt(8, faction.getLevel());
155 updateFactionsStatement.setInt(9, faction.getXP());
156 }
157 } catch (SQLException e) {
158 plugin.getLogger().warning("Could not update factions in MySQL tables with IntegrationSQL.");
159 plugin.getLogger().warning("INFO: " + e.getMessage());
160 }
161 }
162
163 private UUID getOnlineUUID(String name) {
164 try {
165 URL url = new URL("https://api.mojang.com/users/profiles/minecraft/" + name);
166
167 BufferedReader reader = new BufferedReader(new InputStreamReader(url.openStream()));
168
169 String data = reader.lines().collect(Collectors.toList()).get(0);
170
171 JSONObject main = new JSONObject(data);
172
173 String shortUUID = main.getString("id");
174
175 return fromTrimmed(shortUUID);
176 } catch (IOException | IndexOutOfBoundsException e) {
177 // e.printStackTrace();
178 return null;
179 }
180 }
181
182 private UUID fromTrimmed(String trimmedUUID) throws IllegalArgumentException {
183 if (trimmedUUID == null) throw new IllegalArgumentException();
184 StringBuilder builder = new StringBuilder(trimmedUUID.trim());
185 /* Backwards adding to avoid index adjustments */
186 try {
187 builder.insert(20, "-");
188 builder.insert(16, "-");
189 builder.insert(12, "-");
190 builder.insert(8, "-");
191 } catch (StringIndexOutOfBoundsException e) {
192 throw new IllegalArgumentException();
193 }
194
195 return UUID.fromString(builder.toString());
196 }
197}