feat(central): implement database auto migration

10 files changed, 168 insertions, 38 deletions

diff --git a/central/build.gradle b/central/build.gradle
index d95951d..af18c0f 100644
--- a/central/build.gradle
+++ b/central/build.gradle
@@ -39,6 +39,8 @@ dependencies {
     implementation "io.vertx:vertx-grpc:${project.vertxVersion}"
     implementation "io.vertx:vertx-web-api-service:${project.vertxVersion}"
     annotationProcessor "io.vertx:vertx-web-api-service:${project.vertxVersion}"
+    implementation "org.flywaydb:flyway-core:7.7.2"
+    implementation "org.mariadb.jdbc:mariadb-java-client:2.7.2"
 
     implementation project(':rpc-common')
 }
diff --git a/central/src/main/java/moe/yuuta/dn42peering/Main.java b/central/src/main/java/moe/yuuta/dn42peering/Main.java
index 8def1df..c2327e2 100644
--- a/central/src/main/java/moe/yuuta/dn42peering/Main.java
+++ b/central/src/main/java/moe/yuuta/dn42peering/Main.java
@@ -6,6 +6,8 @@ import io.vertx.core.impl.logging.LoggerFactory;
 import io.vertx.core.json.JsonObject;
 import moe.yuuta.dn42peering.asn.ASNHttpVerticle;
 import moe.yuuta.dn42peering.asn.ASNVerticle;
+import moe.yuuta.dn42peering.database.DatabaseMigration;
+import moe.yuuta.dn42peering.database.DatabaseUtils;
 import moe.yuuta.dn42peering.node.NodeVerticle;
 import moe.yuuta.dn42peering.peer.PeerVerticle;
 import moe.yuuta.dn42peering.portal.HTTPPortalVerticle;
@@ -38,6 +40,30 @@ public class Main {
                 .setConfig(config)
                 .setInstances(Runtime.getRuntime().availableProcessors() * 2);
         Logger logger = LoggerFactory.getLogger("Main");
+        // Migrate database first
+        DatabaseMigration.autoMigrate(vertx, DatabaseUtils.getConfiguration(config))
+                .onComplete(_v -> {
+                    start(vertx, options, res -> {
+                        if (res.succeeded()) {
+                            logger.info("The server started.");
+                        } else {
+                            logger.error("Cannot deploy the server.", res.cause());
+                        }
+                    });
+                })
+                .onFailure(err -> {
+                    if(err instanceof DatabaseMigration.ShutdownException) {
+                        System.exit(0);
+                        return;
+                    }
+                    logger.error("Cannot migrate database.", err);
+                    System.exit(1);
+                });
+    }
+
+    private static void start(@Nonnull Vertx vertx,
+                              @Nonnull DeploymentOptions options,
+                              @Nonnull Handler<AsyncResult<CompositeFuture>> handler) {
         CompositeFuture.all(Arrays.asList(
                 Future.<String>future(f -> vertx.deployVerticle(PeerVerticle.class.getName(), options, f)),
                 Future.<String>future(f -> vertx.deployVerticle(WhoisVerticle.class.getName(), options, f)),
@@ -46,12 +72,6 @@ public class Main {
                 Future.<String>future(f -> vertx.deployVerticle(NodeVerticle.class.getName(), options, f)),
                 Future.<String>future(f -> vertx.deployVerticle(ProvisionVerticle.class.getName(), options, f)),
                 Future.<String>future(f -> vertx.deployVerticle(HTTPPortalVerticle.class.getName(), options, f))
-        )).onComplete(res -> {
-            if (res.succeeded()) {
-                logger.info("The server started.");
-            } else {
-                logger.error("Cannot deploy the server.", res.cause());
-            }
-        });
+        )).onComplete(handler);
     }
 }
diff --git a/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseConfiguration.java b/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseConfiguration.java
index 6c83cb0..e3c3d61 100644
--- a/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseConfiguration.java
+++ b/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseConfiguration.java
@@ -4,14 +4,25 @@ import io.vertx.codegen.annotations.DataObject;
 import io.vertx.core.json.JsonObject;
 
 import javax.annotation.Nonnull;
+import java.util.Locale;
 
 @DataObject
 public class DatabaseConfiguration {
+    public enum MigrateAction {
+        AUTO, // Automatically migrate database (default action)
+        AUTO_NO_BASELINE, // Automatically migrate database,
+        // but fail if the database is not empty but had not been migrated using dn42peering.
+        DISABLED, // Never migrate (use with caution)
+        REPAIR // Repair mode. Only use if AUTO fails. Follow the instruction in exceptions and
+        // choose this mode to run. The server will exit after successful repair.
+    }
+
     public final String host;
     public final int port;
     public final String database;
     public final String user;
     public final String password;
+    public final MigrateAction migrateAction;
 
     public DatabaseConfiguration(@Nonnull JsonObject json) {
         this.host = json.getString("host", "localhost");
@@ -19,6 +30,7 @@ public class DatabaseConfiguration {
         this.database = json.getString("database", "dn42peering");
         this.user = json.getString("user", "root");
         this.password = json.getString("password", "");
+        this.migrateAction = MigrateAction.valueOf(json.getString("migrate", "auto").toUpperCase(Locale.ROOT));
     }
 
     @Nonnull
@@ -28,6 +40,7 @@ public class DatabaseConfiguration {
                 .put("port", port)
                 .put("database", database)
                 .put("user", user)
-                .put("password", password);
+                .put("password", password)
+                .put("migrate", migrateAction.toString());
     }
 }
diff --git a/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseMigration.java b/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseMigration.java
new file mode 100644
index 0000000..1b401e9
--- /dev/null
+++ b/central/src/main/java/moe/yuuta/dn42peering/database/DatabaseMigration.java
@@ -0,0 +1,91 @@
+package moe.yuuta.dn42peering.database;
+
+import io.vertx.core.Future;
+import io.vertx.core.Vertx;
+import io.vertx.core.impl.logging.Logger;
+import io.vertx.core.impl.logging.LoggerFactory;
+import org.flywaydb.core.Flyway;
+import org.flywaydb.core.api.configuration.FluentConfiguration;
+import org.flywaydb.core.api.output.MigrateResult;
+import org.flywaydb.core.api.output.RepairResult;
+
+import javax.annotation.Nonnull;
+import java.util.stream.Collectors;
+
+public class DatabaseMigration {
+    private static final Logger logger = LoggerFactory.getLogger(DatabaseMigration.class.getSimpleName());
+    public static Future<Void> autoMigrate(@Nonnull Vertx vertx,
+                                           @Nonnull DatabaseConfiguration configuration) {
+        return vertx.executeBlocking(f -> {
+            if(configuration.migrateAction == DatabaseConfiguration.MigrateAction.DISABLED) {
+                logger.warn("Database migration is disabled. Use with care.");
+                f.complete();
+                return;
+            }
+            final FluentConfiguration flywayConfig = Flyway.configure()
+                    .dataSource(String.format("jdbc:mariadb://%s:%d/%s",
+                            configuration.host,
+                            configuration.port,
+                            configuration.database),
+                            configuration.user,
+                            configuration.password);
+            if(configuration.migrateAction == DatabaseConfiguration.MigrateAction.AUTO_NO_BASELINE) {
+                logger.warn("Baseline migration is disabled. The migration will fail if the " +
+                        "database is dirty.");
+            } else {
+                flywayConfig
+                        .baselineOnMigrate(true)
+                        .baselineVersion("3");
+            }
+            final Flyway flyway = flywayConfig.load();
+
+            switch (configuration.migrateAction) {
+                case AUTO:
+                case AUTO_NO_BASELINE:
+                    logger.info("Starting database migration");
+                    final MigrateResult result = flyway.migrate();
+
+                    String warningPrompt;
+                    if(result.warnings == null || result.warnings.isEmpty()) {
+                        warningPrompt = "No warnings produced.";
+                    } else {
+                        warningPrompt = result.warnings.toString();
+                    }
+
+                    logger.info(String.format("Migration completed. " +
+                                    "Processed %d migrations.\n" +
+                                    "From %s to %s.\n" +
+                                    "%s",
+                            result.migrationsExecuted,
+                            result.initialSchemaVersion,
+                            result.targetSchemaVersion,
+                            warningPrompt));
+                    f.complete();
+                    return;
+                case REPAIR:
+                    logger.warn("Repair mode enabled. Make sure " +
+                            "you had already followed the instructions to undo failed migrations.\n" +
+                            "Also remember to turn repair mode off after you finished.\n" +
+                            "Repair mode will only cleanup upgrade history. All leftover migrations (half-completed " +
+                            "changes) must be cleaned yourself. Good luck!");
+                    final RepairResult repairResult = flyway.repair();
+                    logger.warn(String.format("Repair completed.\n" +
+                            "Repair actions: %s\n" +
+                            "Removed migrations: %s\n" +
+                            "Deleted migrations: %s\n" +
+                            "Aligned migrations: %s",
+                            repairResult.repairActions,
+                            repairResult.migrationsRemoved == null ? "Empty" :
+                                repairResult.migrationsRemoved.stream().map(output -> output.version).collect(Collectors.toList()),
+                            repairResult.migrationsDeleted == null ? "Empty" :
+                                    repairResult.migrationsDeleted.stream().map(output -> output.version).collect(Collectors.toList()),
+                            repairResult.migrationsAligned == null ? "Empty" :
+                                    repairResult.migrationsAligned.stream().map(output -> output.version).collect(Collectors.toList())));
+                    f.fail(new ShutdownException());
+                    return;
+            }
+        });
+    }
+
+    public static class ShutdownException extends Exception {}
+}
diff --git a/docs/central/sql/0-init.sql b/central/src/main/resources/db/migration/V1__Init.sql
index 9717efb..17ca074 100644
--- a/docs/central/sql/0-init.sql
+++ b/central/src/main/resources/db/migration/V1__Init.sql
@@ -13,7 +13,6 @@ CREATE TABLE `node` (
   `public_ip` varchar(21) COLLATE utf8mb4_unicode_ci NOT NULL,
   `dn42_ip4` varchar(21) COLLATE utf8mb4_unicode_ci NOT NULL,
   `dn42_ip6` varchar(39) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `dn42_ip6_nonll` varchar(39) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `asn` char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
   `internal_ip` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
   `internal_port` smallint(5) UNSIGNED NOT NULL,
diff --git a/docs/central/sql/1.sql b/central/src/main/resources/db/migration/V2__Node_Add_NonLL.sql
index f4b9aff..f4b9aff 100644
--- a/docs/central/sql/1.sql
+++ b/central/src/main/resources/db/migration/V2__Node_Add_NonLL.sql
diff --git a/central/src/main/resources/db/migration/V3__Node_PublicIP_30.sql b/central/src/main/resources/db/migration/V3__Node_PublicIP_30.sql
new file mode 100644
index 0000000..55bcb59
--- /dev/null
+++ b/central/src/main/resources/db/migration/V3__Node_PublicIP_30.sql
@@ -0,0 +1 @@
+ALTER TABLE `node` CHANGE `public_ip` `public_ip` VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;
\ No newline at end of file
diff --git a/docs/central/Configuration.md b/docs/central/Configuration.md
index 05cf3bb..60942d1 100644
--- a/docs/central/Configuration.md
+++ b/docs/central/Configuration.md
@@ -11,7 +11,8 @@ The configuration format of central is JSON.
     "host": "host.name.or.ip.for.MySQL.database",
     "database": "mysql database",
     "user": "test",
-    "password": "123456"
+    "password": "123456",
+    "migrate": "auto"
   },
   "http": {
     "name": "<Site name> It will appear like <name> dn42 peering."
diff --git a/docs/central/Database.md b/docs/central/Database.md
index bfe014b..2120243 100644
--- a/docs/central/Database.md
+++ b/docs/central/Database.md
@@ -1,9 +1,36 @@
 # Database
 
-A MySQL database is required for dn42peering central to work. Before installing the central program, make sure you have a proper database with tables setup.
+A MySQL database is required for dn42peering central to work. Before installing the central program, make sure you have a proper database with necessary privileges setup.
 
-Here is the SQL used to setup a production database:
+Create a database and user, then grant it with all data and structure privileges. The server will automatically create tables and upgrade schema in the future.
 
-See [0-init.sql](sql/0-init.sql) for creating initial schema.
+## Auto Migration
 
-Note that the SQL script will update with schema updates. Please follow the docs/sql/ with the latest version.
\ No newline at end of file
+The server will migrate database schema before starts. All migration SQL files can be found in the resources.
+
+In case migration fails, the server will return an exception with instructions and exit.
+
+Follow the instructions and combine it with the SQL files to solve the problem yourself.
+
+For example, a duplicated column name may be caused by duplicated SQL statements. In this case, delete the column may fix the problem.
+
+After manually inspection, change the migration action to `repair`. In this mode, the server automatically rolls back the history and becomes ready to retry.
+
+The server will never fix database errors itself. Repair mode only rolls back history and version state.
+
+### Migrate modes
+
+```json
+{
+  "database": {
+    "migrate": "auto"
+  }
+}
+```
+
+Case-insensitive.
+
+* **auto**: Default mode. The server will automatically upgrades the schema.
+* **auto_no_baseline**: Similar to `auto`, but will not set the version to 3 (which is the last schema version before implementing migration) if the database is not empty but does not have a history record. In this case, it will fail.
+* **disabled**: Not recommended. Disable all migration and start silently. This may led to schema errors.
+* **repair**: Repair the history record after incomplete migrations. The server will only roll back history state and exit. Use this as an oneshot action after manually fixing database structure. Good luck!
\ No newline at end of file
diff --git a/docs/central/sql/agent.sql b/docs/central/sql/agent.sql
deleted file mode 100644
index 2ec84bf..0000000
--- a/docs/central/sql/agent.sql
+++ /dev/null
@@ -1,24 +0,0 @@
--- Use this script to add a new node to the database.
--- Note: Current node support is minimal: Do not edit or delete the node after creation.
--- A GUI will be provided later with full support.
-
-INSERT INTO `node`
-(public_ip,
-dn42_ip4,
-dn42_ip6,
-asn,
-internal_ip,
-internal_port,
-name,
-notice,
-vpn_type_wg)
-VALUES
-('127.0.0.1', -- The public IP address to display
-'172.23.105.1', -- The dn42 IPv4 address (No prefixes)
-'fe80:2980::1',  -- The dn42 or link local IPv6 address (No prefixes)
-'AS4242422980', -- The ASN of this node to display. It is possible to have multiple ASNs in different nodes.
-'127.0.0.1', -- The internal address for management. See agent/Configuration.md for more details. Must be the same with agent configuration.
-49200, -- Currently only support 49200
-'North America GCP', -- Display name
-'<b>North America</b> 的 GCP', -- Optional notice to display. Support HTML.
-1)
\ No newline at end of file