SimplyVanilla / SimplyRank / #56

Committed 15 Mar 2024 11:40PM UTC coverage: 19.562% (-0.4%) from 19.974%

Build # #56

Build Type

Pull #100

github

Committed by

web-flow

Commit Message

Merge 76693d761 into 1e8d87bba

Pull Request Pull Request #100: Fix mysql connection #2

Run Details

0 of 17 new or added lines in 2 files covered. (0.0%)

1 existing line in 1 file now uncovered.

152 of 777 relevant lines covered (19.56%)

0.2 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/src/main/java/net/simplyvanilla/simplyrank/database/sql/MySqlClient.java

package net.simplyvanilla.simplyrank.database.sql;

import net.simplyvanilla.simplyrank.SimplyRankPlugin;

import java.sql.*;

public class MySqlClient {

    public static final String TABLE_PLAYERS_NAME = "player";
    public static final String TABLE_GROUPS_NAME = "group";

    private static final int MAX_TRIES = 10;
    private static final long WAIT_TIME = 3000;

    private final String url;
    private final String username;
    private final String password;

    private Connection connection;

    public MySqlClient(String url, String user, String password) {
        this.url = url;
        this.username = user;
        this.password = password;

        connect();
        initTables();
    }

    public void testConnection() {
        for (int i = 0; i < MAX_TRIES; i++) {
            if (testConnectionSingle()) return;
            try {
                Thread.sleep(WAIT_TIME);
            } catch (InterruptedException ignored) {
                break;
            }
        }
        throw new RuntimeException(String.format("It was not possible to recover Database connection after %s tries", MAX_TRIES));
    }

    private boolean testConnectionSingle() {
        try {
            connection = DriverManager.getConnection(url, username, password);
            return connection.isValid(1);
        } catch (SQLException e) {
            return false;
        }
    }

    public void connect() {
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not connect to MySQL database", e);
        }
    }

    public void close() {
        try {
            if (connection != null) connection.close();
        } catch (SQLException e) {
            SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not close MySQL connection", e);
        }
    }

    public void update(PreparedStatement statement) throws SQLException {
        testConnection();
        statement.executeUpdate();
        statement.close();
    }

    public ResultSet query(PreparedStatement statement) throws SQLException {
        testConnection();
        ResultSet rs;
        rs = statement.executeQuery();

        statement.closeOnCompletion();

        return rs;
    }

    private void executeRawStatement(String cmd) throws SQLException {
        testConnection();
        try (Statement st = connection.createStatement()) {
            st.execute(cmd);
        }
    }

    private void initTables() {

        String cmdPlayers =
            String.format(
                """
                      CREATE TABLE if not exists `%s` (
                         `id` BINARY(16) NOT NULL,
                         `data` text NOT NULL,
                         `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                         PRIMARY KEY (`id`)
                       )
                    """,
                TABLE_PLAYERS_NAME);

        String cmdGroups =
            String.format(
                """
                       CREATE TABLE if not exists `%s` (
                         `id` int unsigned NOT NULL AUTO_INCREMENT,
                         `name` varchar(255) NOT NULL,
                         `data` TEXT NOT NULL,
                         `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                         PRIMARY KEY (`id`),
                         UNIQUE KEY `name` (`name`)
                       )
                    """,
                TABLE_GROUPS_NAME);

        // table has id, address, type, proxy, fetched_at
        String proxyCacheTable = """
                CREATE TABLE IF NOT EXISTS `proxy_cache` (
                    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    `address` VARBINARY(16) NOT NULL,
                    `type` VARCHAR(255) NOT NULL,
                    `proxy` TINYINT(1) NOT NULL,
                    `fetched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `address` (`address`),
                    INDEX `fetched_at` (`fetched_at`)
                )
            """;

        // table has id, address (unique), invoker_id, created_at
        String addressWhitelistTable = """
                CREATE TABLE IF NOT EXISTS `address_whitelist` (
                    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
                    `address` VARBINARY(16) NOT NULL,
                    `invoker_id` BINARY(16) NOT NULL,
                    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `address` (`address`)
                )
            """;

        try {
            executeRawStatement(cmdPlayers);
            executeRawStatement(cmdGroups);
            executeRawStatement(proxyCacheTable);
            executeRawStatement(addressWhitelistTable);
        } catch (SQLException e) {
            SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not create tables", e);
        }
    }

    public PreparedStatement prepareStatement(String qry) throws SQLException {
        testConnection();
        return connection.prepareStatement(qry);
    }

    public PreparedStatement prepareStatement(String qry, String... parameters) throws SQLException {
        var statement = prepareStatement(qry);

        for (int i = 0; i < parameters.length; i++) {
            String s = parameters[i];
            statement.setString(i + 1, s);
        }

        return statement;
    }
}

1	package net.simplyvanilla.simplyrank.database.sql;
2
3	import net.simplyvanilla.simplyrank.SimplyRankPlugin;
4
5	import java.sql.*;
6
7	public class MySqlClient {
8
9	public static final String TABLE_PLAYERS_NAME = "player";
10	public static final String TABLE_GROUPS_NAME = "group";
11
12	private static final int MAX_TRIES = 10;
13	private static final long WAIT_TIME = 3000;
14
15	private final String url;
16	private final String username;
17	private final String password;
18
19	private Connection connection;
20
21	public MySqlClient(String url, String user, String password) {	×
22	this.url = url;	×
23	this.username = user;	×
24	this.password = password;	×
25
26	connect();	×
27	initTables();	×
28	}	×
29
30	public void testConnection() {
NEW 31	for (int i = 0; i < MAX_TRIES; i++) {	×
NEW 32	if (testConnectionSingle()) return;	×
33	try {
NEW 34	Thread.sleep(WAIT_TIME);	×
NEW 35	} catch (InterruptedException ignored) {	×
NEW 36	break;	×
NEW 37	}	×
38	}
NEW 39	throw new RuntimeException(String.format("It was not possible to recover Database connection after %s tries", MAX_TRIES));	×
40	}
41
42	private boolean testConnectionSingle() {
43	try {
NEW 44	connection = DriverManager.getConnection(url, username, password);	×
NEW 45	return connection.isValid(1);	×
NEW 46	} catch (SQLException e) {	×
NEW 47	return false;	×
48	}
49	}
50
51	public void connect() {
52	try {
53	connection = DriverManager.getConnection(url, username, password);	×
54	} catch (SQLException e) {	×
55	SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not connect to MySQL database", e);	×
56	}	×
57	}	×
58
59	public void close() {
60	try {
61	if (connection != null) connection.close();	×
62	} catch (SQLException e) {	×
63	SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not close MySQL connection", e);	×
64	}	×
65	}	×
66
67	public void update(PreparedStatement statement) throws SQLException {
NEW 68	testConnection();	×
69	statement.executeUpdate();	×
70	statement.close();	×
71	}	×
72
73	public ResultSet query(PreparedStatement statement) throws SQLException {
NEW 74	testConnection();	×
75	ResultSet rs;
76	rs = statement.executeQuery();	×
77
78	statement.closeOnCompletion();	×
79
80	return rs;	×
81	}
82
83	private void executeRawStatement(String cmd) throws SQLException {
NEW 84	testConnection();	×
85	try (Statement st = connection.createStatement()) {	×
86	st.execute(cmd);	×
87	}
88	}	×
89
90	private void initTables() {
91
92	String cmdPlayers =	×
93	String.format(	×
94	"""
95	CREATE TABLE if not exists `%s` (
96	`id` BINARY(16) NOT NULL,
97	`data` text NOT NULL,
98	`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
99	`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
100	PRIMARY KEY (`id`)
101	)
102	""",
103	TABLE_PLAYERS_NAME);
104
105	String cmdGroups =	×
106	String.format(	×
107	"""
108	CREATE TABLE if not exists `%s` (
109	`id` int unsigned NOT NULL AUTO_INCREMENT,
110	`name` varchar(255) NOT NULL,
111	`data` TEXT NOT NULL,
112	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
113	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
114	PRIMARY KEY (`id`),
115	UNIQUE KEY `name` (`name`)
116	)
117	""",
118	TABLE_GROUPS_NAME);
119
120	// table has id, address, type, proxy, fetched_at
121	String proxyCacheTable = """	×
122	CREATE TABLE IF NOT EXISTS `proxy_cache` (
123	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
124	`address` VARBINARY(16) NOT NULL,
125	`type` VARCHAR(255) NOT NULL,
126	`proxy` TINYINT(1) NOT NULL,
127	`fetched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
128	PRIMARY KEY (`id`),
129	UNIQUE KEY `address` (`address`),
130	INDEX `fetched_at` (`fetched_at`)
131	)
132	""";
133
134	// table has id, address (unique), invoker_id, created_at
135	String addressWhitelistTable = """	×
136	CREATE TABLE IF NOT EXISTS `address_whitelist` (
137	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
138	`address` VARBINARY(16) NOT NULL,
139	`invoker_id` BINARY(16) NOT NULL,
140	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
141	PRIMARY KEY (`id`),
142	UNIQUE KEY `address` (`address`)
143	)
144	""";
145
146	try {
147	executeRawStatement(cmdPlayers);	×
148	executeRawStatement(cmdGroups);	×
149	executeRawStatement(proxyCacheTable);	×
150	executeRawStatement(addressWhitelistTable);	×
151	} catch (SQLException e) {	×
152	SimplyRankPlugin.getInstance().getSLF4JLogger().error("Could not create tables", e);	×
153	}	×
154	}	×
155
156	public PreparedStatement prepareStatement(String qry) throws SQLException {
NEW 157	testConnection();	×
UNCOV 158	return connection.prepareStatement(qry);	×
159	}
160
161	public PreparedStatement prepareStatement(String qry, String... parameters) throws SQLException {
162	var statement = prepareStatement(qry);	×
163
164	for (int i = 0; i < parameters.length; i++) {	×
165	String s = parameters[i];	×
166	statement.setString(i + 1, s);	×
167	}
168
169	return statement;	×
170	}
171	}

SimplyVanilla / SimplyRank / #56

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous