Skip to content

Commit b7f7dc6

Browse files
author
Dynxsty
authored
Merge pull request Java-Discord#41 from Java-Discord/andrew/db-admin
Added migration commands.
2 parents 896cd9e + 0c2db92 commit b7f7dc6

6 files changed

Lines changed: 164 additions & 1 deletion

File tree

‎src/main/java/com/javadiscord/javabot/data/MigrationUtils.java‎

Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
package com.javadiscord.javabot.data;
2+
3+
import com.javadiscord.javabot.data.commands.MigrationsListSubcommand;
4+
5+
import java.io.IOException;
6+
import java.net.URISyntaxException;
7+
import java.nio.file.FileSystemNotFoundException;
8+
import java.nio.file.FileSystems;
9+
import java.nio.file.Path;
10+
import java.nio.file.Paths;
11+
import java.util.HashMap;
12+
13+
public class MigrationUtils {
14+
public static Path getMigrationsDirectory() throws URISyntaxException, IOException {
15+
var resource = MigrationsListSubcommand.class.getResource("/migrations/");
16+
if (resource == null) throw new IOException("Missing resource /migrations/");
17+
var uri = resource.toURI();
18+
Path dirPath;
19+
try {
20+
dirPath = Paths.get(uri);
21+
} catch (FileSystemNotFoundException e) {
22+
var env = new HashMap<String, String>();
23+
dirPath = FileSystems.newFileSystem(uri, env).getPath("/migrations/");
24+
}
25+
return dirPath;
26+
}
27+
}

‎src/main/java/com/javadiscord/javabot/data/commands/DbAdminCommandHandler.java‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,5 +5,7 @@
55
public class DbAdminCommandHandler extends DelegatingCommandHandler {
66
public DbAdminCommandHandler() {
77
this.addSubcommand("export-schema", new ExportSchemaSubcommand());
8+
this.addSubcommand("migrations-list", new MigrationsListSubcommand());
9+
this.addSubcommand("migrate", new MigrateSubcommand());
810
}
911
}

‎src/main/java/com/javadiscord/javabot/data/commands/MigrateSubcommand.java‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,72 @@
1+
package com.javadiscord.javabot.data.commands;
2+
3+
import com.javadiscord.javabot.Bot;
4+
import com.javadiscord.javabot.commands.Responses;
5+
import com.javadiscord.javabot.commands.SlashCommandHandler;
6+
import com.javadiscord.javabot.data.MigrationUtils;
7+
import net.dv8tion.jda.api.events.interaction.SlashCommandEvent;
8+
import net.dv8tion.jda.api.requests.restaction.interactions.ReplyAction;
9+
10+
import java.io.IOException;
11+
import java.net.URISyntaxException;
12+
import java.nio.file.Files;
13+
import java.nio.file.Path;
14+
import java.sql.SQLException;
15+
import java.util.Objects;
16+
17+
/**
18+
* This subcommand is responsible for executing SQL migrations on the bot's
19+
* schema.
20+
* <p>
21+
* It uses the given name (adding .sql if it's not already there) to look
22+
* for a matching file in the /migrations/ resource directory. Once it's
23+
* found the file, it will split it up into a list of statements by the ';'
24+
* character, and then proceed to execute each statement.
25+
* </p>
26+
*/
27+
public class MigrateSubcommand implements SlashCommandHandler {
28+
@Override
29+
public ReplyAction handle(SlashCommandEvent event) {
30+
String migrationName = Objects.requireNonNull(event.getOption("name")).getAsString();
31+
if (!migrationName.endsWith(".sql")) {
32+
migrationName = migrationName + ".sql";
33+
}
34+
try {
35+
Path migrationsDir = MigrationUtils.getMigrationsDirectory();
36+
Path migrationFile = migrationsDir.resolve(migrationName);
37+
if (Files.notExists(migrationFile)) {
38+
return Responses.warning(event, "The specified migration `" + migrationName + "` does not exist.");
39+
}
40+
String sql = Files.readString(migrationFile);
41+
String[] statements = sql.split("\\s*;\\s*");
42+
if (statements.length == 0) {
43+
return Responses.warning(event, "The migration `" + migrationName + "` does not contain any statements. Please remove or edit it before running again.");
44+
}
45+
Bot.asyncPool.submit(() -> {
46+
try (var con = Bot.dataSource.getConnection()) {
47+
for (int i = 0; i < statements.length; i++) {
48+
if (statements[i].isBlank()) {
49+
event.getChannel().sendMessage("Skipping statement " + i + "; it is blank.").complete();
50+
continue;
51+
}
52+
try (var stmt = con.createStatement()){
53+
int rowsUpdated = stmt.executeUpdate(statements[i]);
54+
event.getChannel().sendMessageFormat(
55+
"Executed statement %d of %d:\n```\n%s\n```\nRows Updated: `%d`", i, statements.length, statements[i], rowsUpdated
56+
).complete();
57+
} catch (SQLException e) {
58+
e.printStackTrace();
59+
event.getChannel().sendMessage("Error while executing statement " + i + ": " + e.getMessage()).queue();
60+
return;
61+
}
62+
}
63+
} catch (SQLException e) {
64+
event.getChannel().sendMessage("Could not obtain a connection to the database.").queue();
65+
}
66+
});
67+
return Responses.info(event, "Migration Started", "Execution of the migration `" + migrationName + "` has been started. " + statements.length + " statements will be executed.");
68+
} catch (IOException | URISyntaxException e) {
69+
return Responses.error(event, e.getMessage());
70+
}
71+
}
72+
}

‎src/main/java/com/javadiscord/javabot/data/commands/MigrationsListSubcommand.java‎

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,49 @@
1+
package com.javadiscord.javabot.data.commands;
2+
3+
import com.javadiscord.javabot.commands.Responses;
4+
import com.javadiscord.javabot.commands.SlashCommandHandler;
5+
import com.javadiscord.javabot.data.MigrationUtils;
6+
import net.dv8tion.jda.api.EmbedBuilder;
7+
import net.dv8tion.jda.api.events.interaction.SlashCommandEvent;
8+
import net.dv8tion.jda.api.requests.restaction.interactions.ReplyAction;
9+
10+
import java.io.IOException;
11+
import java.net.URISyntaxException;
12+
import java.nio.file.Files;
13+
import java.util.stream.Collectors;
14+
15+
/**
16+
* This subcommand shows a list of all available migrations, and a short preview
17+
* of their source code.
18+
*/
19+
public class MigrationsListSubcommand implements SlashCommandHandler {
20+
@Override
21+
public ReplyAction handle(SlashCommandEvent event) {
22+
try (var s = Files.list(MigrationUtils.getMigrationsDirectory())) {
23+
EmbedBuilder embedBuilder = new EmbedBuilder()
24+
.setTitle("List of Runnable Migrations");
25+
var paths = s.filter(path -> path.getFileName().toString().endsWith(".sql")).collect(Collectors.toList());
26+
if (paths.isEmpty()) {
27+
embedBuilder.setDescription("There are no migrations to run. Please add them to the `/migrations/` resource directory.");
28+
return event.replyEmbeds(embedBuilder.build());
29+
}
30+
paths.forEach(path -> {
31+
StringBuilder sb = new StringBuilder(150);
32+
sb.append("```sql\n");
33+
try {
34+
String sql = Files.readString(path);
35+
sb.append(sql, 0, Math.min(sql.length(), 100));
36+
if (sql.length() > 100) sb.append("...");
37+
} catch (IOException e) {
38+
e.printStackTrace();
39+
sb.append("Error: Could not read SQL: ").append(e.getMessage());
40+
}
41+
sb.append("\n```");
42+
embedBuilder.addField(path.getFileName().toString(), sb.toString(), false);
43+
});
44+
return event.replyEmbeds(embedBuilder.build());
45+
} catch (IOException | URISyntaxException e) {
46+
return Responses.error(event, e.getMessage());
47+
}
48+
}
49+
}

‎src/main/resources/commands.yaml‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -630,4 +630,13 @@
630630
- name: include-data
631631
description: Should data be included in the export?
632632
required: true
633-
type: BOOLEAN
633+
type: BOOLEAN
634+
- name: migrations-list
635+
description: Show a list of all database migrations that can be run.
636+
- name: migrate
637+
description: Run a database migration.
638+
options:
639+
- name: name
640+
description: The name of the migration to run.
641+
required: true
642+
type: STRING

‎src/main/resources/migrations/test.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
// Updates Andrew's account to have the specified number of credits.
2+
UPDATE ECONOMY_ACCOUNT
3+
SET BALANCE = 420
4+
WHERE USER_ID = 235439851263098880;

0 commit comments

Comments
 (0)