SchemaCrawler插件开发指南：如何扩展自定义数据库连接器

【免费下载链接】SchemaCrawler Free database schema discovery and comprehension tool 项目地址: https://gitcode.com/gh_mirrors/sc/SchemaCrawler

SchemaCrawler是一款功能强大的数据库模式发现和理解工具，支持多种数据库系统。本文将详细介绍如何为SchemaCrawler开发自定义数据库连接器插件，帮助开发者轻松扩展对新数据库的支持。

什么是数据库连接器？

数据库连接器是SchemaCrawler的核心组件，负责处理特定数据库的连接细节、元数据检索和SQL语法适配。每个数据库连接器都封装了该数据库特有的连接参数、JDBC URL格式和信息模式查询等。

SchemaCrawler已内置多种数据库连接器，如：

• SQLiteDatabaseConnector
• MySQLDatabaseConnector
• PostgreSQLDatabaseConnector

开发自定义数据库连接器的基本步骤

1. 创建项目结构

首先，创建一个新的Maven项目，推荐的目录结构如下：

schemacrawler-customdb/
├── src/
│   ├── main/
│   │   ├── java/
│   │   │   └── schemacrawler/
│   │   │       └── server/
│   │   │           └── customdb/
│   │   │               └── CustomDBDatabaseConnector.java
│   │   └── resources/
│   │       └── customdb.information_schema/
│   │           └── views.sql
│   └── test/
│       └── java/
│           └── schemacrawler/
│               └── integration/
│                   └── test/
│                       └── CustomDBTest.java
└── pom.xml

2. 实现DatabaseConnector接口

所有数据库连接器都需要继承抽象类DatabaseConnector，并实现必要的配置方法。以下是一个基本的实现框架：

package schemacrawler.server.customdb;

import schemacrawler.tools.databaseconnector.DatabaseConnector;
import schemacrawler.tools.databaseconnector.DatabaseConnectorOptions;
import schemacrawler.tools.databaseconnector.DatabaseConnectorOptionsBuilder;
import schemacrawler.tools.executable.commandline.PluginCommand;
import us.fatehi.utility.datasource.DatabaseConnectionSourceBuilder;
import us.fatehi.utility.datasource.DatabaseServerType;

public final class CustomDBDatabaseConnector extends DatabaseConnector {

  private static DatabaseConnectorOptions databaseConnectorOptions() {
    // 定义数据库服务器类型
    final DatabaseServerType dbServerType = new DatabaseServerType("customdb", "CustomDB");

    // 配置JDBC连接源
    final DatabaseConnectionSourceBuilder connectionSourceBuilder =
        DatabaseConnectionSourceBuilder.builder("jdbc:customdb://${host}:${port}/${database}")
            .withDefaultPort(1234)
            .withDefaultUrlx("key", "value");

    // 配置命令行选项
    final PluginCommand pluginCommand = PluginCommand.newDatabasePluginCommand(dbServerType);
    pluginCommand
        .addOption(
            "server", String.class, "--server=customdb%n" + "Loads SchemaCrawler plug-in for CustomDB")
        .addOption("host", String.class, "Host name%n" + "Optional, defaults to localhost")
        .addOption("port", Integer.class, "Port number%n" + "Optional, defaults to 1234")
        .addOption("database", String.class, "Database name");

    // 构建数据库连接器选项
    return DatabaseConnectorOptionsBuilder.builder(dbServerType)
        .withHelpCommand(pluginCommand)
        .withUrlStartsWith("jdbc:customdb:")
        .withInformationSchemaViewsFromResourceFolder("/customdb.information_schema")
        .withDatabaseConnectionSourceBuilder(() -> connectionSourceBuilder)
        .build();
  }

  public CustomDBDatabaseConnector() {
    super(databaseConnectorOptions());
  }
}

3. 配置信息模式视图

信息模式是数据库元数据的关键来源。创建src/main/resources/customdb.information_schema/views.sql文件，定义数据库特定的信息模式查询：

-- 自定义数据库的信息模式查询
CREATE VIEW IF NOT EXISTS tables AS
SELECT ... FROM system_tables;

CREATE VIEW IF NOT EXISTS columns AS
SELECT ... FROM system_columns;

4. 处理特殊数据类型

对于数据库特有的数据类型，需要实现EnumDataTypeHelper接口：

package schemacrawler.server.customdb;

import schemacrawler.schema.Column;
import schemacrawler.schemacrawler.SchemaRetrievalOptions;
import schemacrawler.tools.databaseconnector.enumdatatype.EnumDataTypeHelper;

public class CustomDBEnumDataTypeHelper implements EnumDataTypeHelper {

  @Override
  public String getEnumerationValues(final Column column, final SchemaRetrievalOptions options) {
    // 实现获取枚举值的逻辑
    return "ENUM('value1', 'value2')";
  }
}

然后在连接器中注册：

.withSchemaRetrievalOptionsBuilder(
    (schemaRetrievalOptionsBuilder, connection) ->
        schemaRetrievalOptionsBuilder.withEnumDataTypeHelper(new CustomDBEnumDataTypeHelper()))

5. 实现连接初始化器

对于需要特殊初始化的数据库连接，可以实现ConnectionInitializer：

package schemacrawler.server.customdb;

import java.sql.Connection;
import java.sql.SQLException;
import schemacrawler.tools.databaseconnector.ConnectionInitializer;

public class CustomDBConnectionInitializer implements ConnectionInitializer {

  @Override
  public void initializeConnection(final Connection connection) throws SQLException {
    // 执行初始化SQL
    try (final Statement statement = connection.createStatement()) {
      statement.execute("SET SPECIAL_OPTION = 'value'");
    }
  }
}

在连接器中配置：

.withConnectionInitializer(new CustomDBConnectionInitializer())

6. 编写测试用例

创建测试类验证连接器功能：

package schemacrawler.integration.test;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.greaterThan;
import static schemacrawler.test.utility.SchemaCrawlerTestUtility.executeScriptFromResource;

import java.sql.Connection;
import schemacrawler.schema.Catalog;
import schemacrawler.schemacrawler.SchemaCrawlerOptions;
import schemacrawler.schemacrawler.SchemaCrawlerOptionsBuilder;
import schemacrawler.test.utility.BaseDatabaseTest;
import schemacrawler.test.utility.TestContext;
import schemacrawler.tools.catalogloader.SchemaCrawlerCatalogLoader;
import schemacrawler.tools.databaseconnector.DatabaseConnector;
import schemacrawler.tools.databaseconnector.DatabaseConnectorRegistry;

public class CustomDBTest extends BaseDatabaseTest {

  @Test
  public void testCustomDB() throws Exception {
    try (final Connection connection = createConnection()) {
      executeScriptFromResource(connection, "/customdb-test.sql");

      final DatabaseConnectorRegistry registry = new DatabaseConnectorRegistry();
      final DatabaseConnector connector = registry.lookupDatabaseConnector("customdb");
      
      final SchemaCrawlerOptions options = SchemaCrawlerOptionsBuilder.newSchemaCrawlerOptions();
      final SchemaCrawlerCatalogLoader catalogLoader = connector.getCatalogLoader();
      catalogLoader.setConnection(connection);
      
      final Catalog catalog = catalogLoader.loadCatalog(options);
      
      assertThat("Catalog should not be empty", catalog.getSchemas().size(), greaterThan(0));
    }
  }

  @Override
  protected TestContext getTestContext() {
    return TestContext.builder()
        .withDatabaseServerType("customdb")
        .withConnectionString("jdbc:customdb://localhost:1234/testdb")
        .build();
  }
}

7. 配置Maven依赖

在pom.xml中添加必要的依赖：

<dependencies>
  <dependency>
    <groupId>us.fatehi</groupId>
    <artifactId>schemacrawler-tools</artifactId>
    <version>16.21.1</version>
  </dependency>
  <dependency>
    <groupId>us.fatehi</groupId>
    <artifactId>schemacrawler-testdb</artifactId>
    <version>16.21.1</version>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>com.customdb</groupId>
    <artifactId>customdb-jdbc-driver</artifactId>
    <version>1.0.0</version>
  </dependency>
</dependencies>

打包和部署插件

1. 打包插件

使用Maven打包插件JAR：

mvn clean package

2. 安装插件

将生成的JAR文件复制到SchemaCrawler的插件目录：

cp target/schemacrawler-customdb-1.0.0.jar ~/.schemacrawler/plugins/

3. 验证插件

运行SchemaCrawler命令验证自定义连接器：

schemacrawler --server=customdb --host=localhost --port=1234 --database=testdb --user=sa --password=password --info-level=standard --command=list

高级技巧

处理数据库特定限制

某些数据库有特殊的限制或行为，可以通过LimitOptionsBuilder进行配置：

.withLimitOptionsBuilder(
    limitOptionsBuilder ->
        limitOptionsBuilder.includeSchemas(new RegularExpressionExclusionRule("system|sys")))

自定义URL验证

对于复杂的URL格式，可以自定义验证逻辑：

.withUrlSupportPredicate(
    url -> url != null && url.startsWith("jdbc:customdb:") && url.contains(";version="))

提供额外的命令行选项

可以为连接器添加特定的命令行选项：

pluginCommand.addOption(
    "ssl", Boolean.class, "Enable SSL connection%n" + "Optional, defaults to false");

结语

通过本文介绍的步骤，您可以轻松为SchemaCrawler开发自定义数据库连接器。这不仅可以扩展SchemaCrawler的功能，还能为特定数据库提供优化的元数据检索体验。

开发完成后，您可以将插件贡献给SchemaCrawler社区，帮助更多用户。详细的贡献指南可以参考项目的贡献文档。

希望本文对您开发SchemaCrawler数据库连接器有所帮助！如有任何问题，欢迎在项目的issue tracker中提问。

【免费下载链接】SchemaCrawler Free database schema discovery and comprehension tool 项目地址: https://gitcode.com/gh_mirrors/sc/SchemaCrawler