· 6 years ago · Oct 18, 2019, 06:56 PM
1package org.valdi.jmusicbot.data;
2
3import com.google.gson.Gson;
4import com.google.gson.GsonBuilder;
5import org.apache.logging.log4j.LogManager;
6import org.apache.logging.log4j.Logger;
7import org.valdi.SuperApiX.common.databases.DatabaseException;
8import org.valdi.SuperApiX.common.databases.types.H2Database;
9import org.valdi.jmusicbot.data.music.Playlist;
10import org.valdi.jmusicbot.data.music.Track;
11import org.valdi.jmusicbot.data.music.TrackInfo;
12
13import java.io.File;
14import java.sql.Connection;
15import java.sql.PreparedStatement;
16import java.sql.ResultSet;
17import java.sql.SQLException;
18import java.util.HashMap;
19import java.util.Map;
20
21public class BotDatabase extends H2Database {
22 private final Logger logger;
23
24 private final DataManager manager;
25 private final long id;
26 private final Gson gson;
27
28 private final Map<String, Track> tracks;
29 private final Map<String, Playlist> playlists;
30
31 public BotDatabase(DataManager manager, long id, File file) throws DatabaseException {
32 super(manager.getBot().getMain().getLoader(), file, ";MODE=MYSQL", "username",
33 "password", 5, "JMusicBot-Bot-" + id);
34 this.logger = LogManager.getLogger("Bot Database (" + id + ")");
35
36 this.manager = manager;
37 this.id = id;
38
39 // excludeFieldsWithoutExposeAnnotation - this means that every field to be stored should use @Expose
40 // enableComplexMapKeySerialization - forces GSON to use TypeAdapters even for Map keys
41 GsonBuilder builder = new GsonBuilder()
42 .excludeFieldsWithoutExposeAnnotation()
43 .enableComplexMapKeySerialization();
44 // Register adapters
45 // Keep null in the database
46 builder.serializeNulls();
47 // Allow characters like < or > without escaping them
48 builder.disableHtmlEscaping();
49 this.gson = builder.create();
50
51 this.tracks = new HashMap<>();
52 this.playlists = new HashMap<>();
53 }
54
55 public void createTables() throws DatabaseException {
56 this.createStorageFilesTable();
57 this.createPlaylistsTable();
58 this.createPlaylistsFilesTable();
59 }
60
61 public void createStorageFilesTable() throws DatabaseException {
62 String query = "CREATE TABLE IF NOT EXISTS storage_files(uuid VARCHAR(36) PRIMARY KEY, trackinfo LONGTEXT, created DATETIME, createdby BIGINT, playcount INT)";
63 try(Connection conn = this.getConnection();
64 PreparedStatement statement = conn.prepareStatement(query)) {
65 statement.execute();
66 } catch(Exception e) {
67 throw new DatabaseException(e);
68 }
69 }
70
71 public void createPlaylistsTable() throws DatabaseException {
72 String query = "CREATE TABLE IF NOT EXISTS playlist(uuid VARCHAR(36) PRIMARY KEY, name VARCHAR(32), created DATETIME, createdby BIGINT)";
73 try(Connection conn = this.getConnection();
74 PreparedStatement statement = conn.prepareStatement(query)) {
75 statement.execute();
76 } catch(Exception e) {
77 throw new DatabaseException(e);
78 }
79 }
80
81 public void createPlaylistsFilesTable() throws DatabaseException {
82 String query = "CREATE TABLE IF NOT EXISTS playlist_files(playlist VARCHAR(36), file VARCHAR(36), position INT, primary key (playlist, file))";
83 try(Connection conn = this.getConnection();
84 PreparedStatement statement = conn.prepareStatement(query)) {
85 statement.execute();
86 } catch(Exception e) {
87 throw new DatabaseException(e);
88 }
89 }
90
91 public void loadStorageFiles() {
92 String query = "SELECT * FROM storage_files";
93 try(Connection conn = this.getConnection();
94 PreparedStatement statement = conn.prepareStatement(query)) {
95 ResultSet result = statement.executeQuery();
96 while(result.next()) {
97 Track track = new Track(manager, id, result.getString("uuid"));
98 track.setInfo(gson.fromJson(result.getString("config"), TrackInfo.class));
99 track.setCreated(result.getDate("created"));
100 track.setCreatedby(result.getLong("createdby"));
101 track.setPlaycount(result.getInt("playcount"));
102 tracks.put(track.getId(), track);
103 }
104 } catch (DatabaseException | SQLException e) {
105 logger.error("Failed load settings from the database...", e);
106 }
107 }
108
109 public void loadPlaylists() {
110 String query = "SELECT * FROM playlist";
111 try(Connection conn = this.getConnection();
112 PreparedStatement statement = conn.prepareStatement(query)) {
113 ResultSet result = statement.executeQuery();
114 while(result.next()) {
115 Playlist playlist = new Playlist(manager, id, result.getString("uuid"));
116 playlist.setName(result.getString("name"));
117 playlist.setCreated(result.getDate("created"));
118 playlist.setCreatedby(result.getLong("createdby"));
119 playlists.put(playlist.getId(), playlist);
120 }
121 } catch (DatabaseException | SQLException e) {
122 logger.error("Failed load settings from the database...", e);
123 }
124 }
125
126 public void loadPlaylistsFiles() {
127 String query = "SELECT * FROM playlist_files";
128 try(Connection conn = this.getConnection();
129 PreparedStatement statement = conn.prepareStatement(query)) {
130 ResultSet result = statement.executeQuery();
131 while(result.next()) {
132 Playlist playlist = playlists.get(result.getString("playlist"));
133 playlist.addTrack(result.getString("file"), result.getInt("position"));
134 }
135 } catch (DatabaseException | SQLException e) {
136 logger.error("Failed load settings from the database...", e);
137 }
138 }
139
140 public void saveStorageFiles() {
141 tracks.values().forEach(this::saveStorageFile);
142 }
143
144 public void saveStorageFile(Track track) {
145 String query = "INSERT INTO storage_files(uuid, trackinfo, created, createdby, playcount) VALUES (?, ?, ?, ?, ?) " +
146 "ON DUPLICATE KEY UPDATE trackinfo = VALUES(trackinfo), created = VALUES(created), " +
147 "createdby = VALUES(createdby), playcount = VALUES(playcount)";
148 try(Connection conn = this.getConnection();
149 PreparedStatement statement = conn.prepareStatement(query)) {
150 statement.setString(1, track.getId());
151 statement.setString(2, gson.toJson(track.getInfo(), TrackInfo.class));
152 statement.setDate(3, track.getCreated());
153 statement.setLong(4, track.getCreatedby());
154 statement.setInt(5, track.getPlaycount());
155 statement.execute();
156 } catch (DatabaseException | SQLException e) {
157 logger.error("Failed load settings from the database...", e);
158 }
159 }
160
161 public void savePlaylists() {
162 playlists.values().forEach(this::savePlaylist);
163 }
164
165 public void savePlaylist(Playlist playlist) {
166 String query1 = "INSERT INTO playlist(uuid, name, created, createdby) VALUES (?, ?, ?, ?) " +
167 "ON DUPLICATE KEY UPDATE name = VALUES(name), created = VALUES(created), createdby = VALUES(createdby)";
168 try(Connection conn = this.getConnection();
169 PreparedStatement statement = conn.prepareStatement(query1)) {
170 statement.setString(1, playlist.getId());
171 statement.setString(2, playlist.getName());
172 statement.setDate(3, playlist.getCreated());
173 statement.setLong(4, playlist.getCreatedby());
174 statement.execute();
175 } catch (DatabaseException | SQLException e) {
176 logger.error("Failed load settings from the database...", e);
177 }
178
179 for(Map.Entry<Integer, Track> entry : playlist.getTracks().entrySet()) {
180 String query2 = "INSERT INTO playlist_files(playlist, file, position) VALUES (?, ?, ?) " +
181 "ON DUPLICATE KEY UPDATE position = VALUES(position)";
182 try (Connection conn = this.getConnection();
183 PreparedStatement statement = conn.prepareStatement(query2)) {
184 statement.setString(1, playlist.getId());
185 statement.setString(2, entry.getValue().getId());
186 statement.setInt(3, entry.getKey());
187 statement.execute();
188 } catch (DatabaseException | SQLException e) {
189 logger.error("Failed load settings from the database...", e);
190 }
191 }
192 }
193
194 public void deleteStorageFiles(String file) {
195 String query1 = "DELETE * FROM storage_files WHERE uuid = ?";
196 try(Connection conn = this.getConnection();
197 PreparedStatement statement = conn.prepareStatement(query1)) {
198 statement.setString(1, file);
199 statement.execute();
200 } catch (DatabaseException | SQLException e) {
201 logger.error("Failed load settings from the database...", e);
202 }
203
204 String query2 = "DELETE * FROM playlist_files WHERE file = ?";
205 try(Connection conn = this.getConnection();
206 PreparedStatement statement = conn.prepareStatement(query2)) {
207 statement.setString(1, file);
208 statement.execute();
209 } catch (DatabaseException | SQLException e) {
210 logger.error("Failed load settings from the database...", e);
211 }
212 }
213
214 public void deletePlaylist(String playlist) {
215 String query1 = "DELETE * FROM playlist WHERE uuid = ?";
216 try(Connection conn = this.getConnection();
217 PreparedStatement statement = conn.prepareStatement(query1)) {
218 statement.setString(1, playlist);
219 statement.execute();
220 } catch (DatabaseException | SQLException e) {
221 logger.error("Failed load settings from the database...", e);
222 }
223
224 String query2 = "DELETE * FROM playlist_files WHERE uuid = ?";
225 try(Connection conn = this.getConnection();
226 PreparedStatement statement = conn.prepareStatement(query2)) {
227 statement.setString(1, playlist);
228 statement.execute();
229 } catch (DatabaseException | SQLException e) {
230 logger.error("Failed load settings from the database...", e);
231 }
232 }
233
234 public Map<String, Track> getTracks() {
235 return tracks;
236 }
237
238 public Map<String, Playlist> getPlaylists() {
239 return playlists;
240 }
241}