A3iqGXgy

1import com.general_hello.commands.Config;
2import com.zaxxer.hikari.HikariConfig;
3import com.zaxxer.hikari.HikariDataSource;
4import org.slf4j.Logger;
5import org.slf4j.LoggerFactory;
6import java.io.File;
7import java.io.IOException;
8import java.sql.*;
9
10public class SQLiteDataSource implements DatabaseManager {
11    private static final Logger LOGGER = LoggerFactory.getLogger(SQLiteDataSource.class);
12    public static HikariDataSource ds;
13
14    public SQLiteDataSource() {
15        try {
16            final File dbFile = new File("database.db");
17
18            if (!dbFile.exists()) {
19                if (dbFile.createNewFile()) {
20                    LOGGER.info("Created database file");
21                } else {
22                    LOGGER.info("Could not create database file");
23                }
24            }
25
26        } catch (IOException e) {
27            e.printStackTrace();
28        }
29
30        HikariConfig config = new HikariConfig();
31        config.setJdbcUrl("jdbc:sqlite:database.db");
32        config.setConnectionTestQuery("SELECT 1");
33        config.addDataSourceProperty("cachePrepStmts", "true");
34        config.addDataSourceProperty("prepStmtCacheSize", "250");
35        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
36
37        ds = new HikariDataSource(config);
38
39        try (final Statement statement = getConnection().createStatement()) {
40            final String defaultPrefix = Config.get("prefix");
41
42            // language=SQLite
43            statement.execute("CREATE TABLE IF NOT EXISTS guild_settings (" +
44                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
45                    "guild_id VARCHAR(20) NOT NULL," +
46                    "prefix VARCHAR(255) NOT NULL DEFAULT '" + defaultPrefix + "'" +
47                    ");");
48
49            statement.execute("CREATE TABLE IF NOT EXISTS UserData ( UserId INTEGER NOT NULL, " +
50                    "UserName TEXT NOT NULL, " +
51                    "UserProfilePicLink TEXT, " +
52                    "PRIMARY KEY(UserId) ) WITHOUT ROWID");
53            
54            statement.execute("CREATE TABLE IF NOT EXISTS levels (guildID BIGINT," +
55                    " userID BIGINT," +
56                    " totalXP BIGINT," +
57                    " name VARCHAR(256)," +
58                    " discriminator VARCHAR(4)," +
59                    " PRIMARY KEY(guildID, userID))");
60
61            statement.execute("CREATE TABLE IF NOT EXISTS xpAlerts (guildID BIGINT PRIMARY KEY, " +
62                    "mode VARCHAR(128))");
63
64            statement.execute("CREATE TABLE IF NOT EXISTS guildSettings (guildID BIGINT PRIMARY KEY, data JSON CHECK (JSON_VALID(data)))");
65            
66            LOGGER.info("Table initialised");
67        } catch (SQLException e) {
68            e.printStackTrace();
69        }
70    }
71
72    @Override
73    public String getPrefix(long guildId) {
74        try (final PreparedStatement preparedStatement = getConnection()
75                // language=SQLite
76                .prepareStatement("SELECT prefix FROM guild_settings WHERE guild_id = ?")) {
77
78            preparedStatement.setString(1, String.valueOf(guildId));
79
80            try (final ResultSet resultSet = preparedStatement.executeQuery()) {
81                if (resultSet.next()) {
82                    return resultSet.getString("prefix");
83                }
84            }
85
86            try (final PreparedStatement insertStatement = getConnection()
87                    // language=SQLite
88                    .prepareStatement("INSERT INTO guild_settings(guild_id) VALUES(?)")) {
89
90                insertStatement.setString(1, String.valueOf(guildId));
91
92                insertStatement.execute();
93            }
94        } catch (SQLException e) {
95            e.printStackTrace();
96        }
97
98        return Config.get("prefix");
99    }
100
101    @Override
102    public void setPrefix(long guildId, String newPrefix) {
103        try (final PreparedStatement preparedStatement = getConnection()
104                // language=SQLite
105                .prepareStatement("UPDATE guild_settings SET prefix = ? WHERE guild_id = ?")) {
106
107            preparedStatement.setString(1, newPrefix);
108            preparedStatement.setString(2, String.valueOf(guildId));
109
110            preparedStatement.executeUpdate();
111        } catch (SQLException e) {
112            e.printStackTrace();
113        }
114    }
115
116    @Override
117    public String getName(long userId) {
118        try (final PreparedStatement preparedStatement = getConnection()
119                // language=SQLite
120                .prepareStatement("SELECT UserName FROM UserData WHERE UserId = ?")) {
121
122            preparedStatement.setString(1, String.valueOf(userId));
123
124            try (final ResultSet resultSet = preparedStatement.executeQuery()) {
125                if (resultSet.next()) {
126                    return resultSet.getString("UserName");
127                }
128            }
129        } catch (SQLException e) {
130            e.printStackTrace();
131        }
132
133        return null;
134    }
135
136    @Override
137    public String getProfilePictureLink(long userId) {
138        try (final PreparedStatement preparedStatement = getConnection()
139                // language=SQLite
140                .prepareStatement("SELECT UserProfilePicLink FROM UserData WHERE UserId = ?")) {
141
142            preparedStatement.setString(1, String.valueOf(userId));
143
144            try (final ResultSet resultSet = preparedStatement.executeQuery()) {
145                if (resultSet.next()) {
146                    return resultSet.getString("UserProfilePicLink");
147                }
148            }
149        } catch (SQLException e) {
150            e.printStackTrace();
151        }
152
153        return null;
154    }
155
156    @Override
157    public void newInfo(long userId, String userName, String profilePictureLink) {
158        try (final PreparedStatement preparedStatement = getConnection()
159             // language=SQLite
160             .prepareStatement("INSERT INTO UserData" +
161                     "(UserId, UserName, UserProfilePicLink)" +
162                     "VALUES (?, ?, ?);")) {
163
164            preparedStatement.setString(1, String.valueOf(userId));
165            preparedStatement.setString(2, userName);
166            preparedStatement.setString(3, profilePictureLink);
167
168            preparedStatement.executeUpdate();
169
170            System.out.println("Added the user to the database successfully!");
171        } catch (SQLException e) {
172            e.printStackTrace();
173        }
174    }
175
176
177    @Override
178    public void setName(long userId, String name) {
179        try (final PreparedStatement preparedStatement = getConnection()
180                // language=SQLite
181                .prepareStatement("UPDATE UserData SET UserName=? WHERE UserId=?"
182                )) {
183
184            preparedStatement.setString(2, String.valueOf(userId));
185            preparedStatement.setString(1, name);
186
187            preparedStatement.executeUpdate();
188        } catch (SQLException e) {
189            e.printStackTrace();
190        }
191    }
192
193    @Override
194    public void setProfilePictureLink(long userId, String link) {
195        try (final PreparedStatement preparedStatement = getConnection()
196                // language=SQLite
197                .prepareStatement("UPDATE UserData SET UserProfilePicLink=? WHERE UserId=?"
198                )) {
199
200            preparedStatement.setString(2, String.valueOf(userId));
201            preparedStatement.setString(1, link);
202
203            preparedStatement.executeUpdate();
204        } catch (SQLException e) {
205            e.printStackTrace();
206        }
207    }
208
209    public static Connection getConnection() throws SQLException {
210        return ds.getConnection();
211    }
212}
213