MigrateCommand.java

package dev.dbos.transact.cli;

import dev.dbos.transact.migrations.MigrationManager;

import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.concurrent.Callable;

import picocli.CommandLine.Command;
import picocli.CommandLine.Mixin;
import picocli.CommandLine.Model.CommandSpec;
import picocli.CommandLine.Option;
import picocli.CommandLine.Spec;

@Command(name = "migrate", description = "Create DBOS system tables")
public class MigrateCommand implements Callable<Integer> {

  @Option(
      names = {"-r", "--app-role"},
      description = "The role with which you will run your DBOS application")
  String appRole;

  @Mixin DatabaseOptions dbOptions;

  @Option(
      names = {"-h", "--help"},
      usageHelp = true,
      description = "Display this help message")
  boolean help;

  @Spec CommandSpec spec;

  @Override
  public Integer call() throws Exception {
    var out = spec.commandLine().getOut();
    out.println("Starting DBOS migrations");
    out.format("  System Database: %s\n", dbOptions.url());
    out.format("  System Database User: %s\n", dbOptions.user());

    // TODO: add option for useListenNotify
    MigrationManager.runMigrations(
        dbOptions.url(), dbOptions.user(), dbOptions.password(), dbOptions.schema(), true);
    grantDBOSSchemaPermissions(out, dbOptions.schema());
    return 0;
  }

  void grantDBOSSchemaPermissions(PrintWriter out, String schema) throws SQLException {

    if (appRole == null || appRole.isEmpty()) {
      return;
    }

    out.format(
        "Granting permissions for the %s schema to %s in database %s\n",
        schema, appRole, dbOptions.url());

    String[] queries = {
      "GRANT USAGE ON SCHEMA %s TO %s",
      "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %s TO %s",
      "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %s TO %s",
      "GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA %s TO %s",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT ALL ON TABLES TO %s",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT ALL ON SEQUENCES TO %s",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA %s GRANT EXECUTE ON FUNCTIONS TO %s"
    };
    try (var conn =
            DriverManager.getConnection(dbOptions.url(), dbOptions.user(), dbOptions.password());
        var stmt = conn.createStatement()) {
      for (var query : queries) {
        query = query.formatted(schema, appRole);
        stmt.execute(query);
      }
    }
  }
}