agent-authoring

Agent Authoring Guide

This guide defines the expected shape of a DBX agent. Treat it as the checklist for adding or reviewing an agent.

Agent Contract

Every agent is a standalone JVM process that:

• Implements `com.dbx.agent.DatabaseAgent`.
• Prefer extending `com.dbx.agent.ConfiguredJdbcAgent` for standard JDBC agents.
• Extend `com.dbx.agent.AbstractJdbcAgent` when the database needs custom metadata SQL but can still share lifecycle and execution behavior.
• Starts with `new JsonRpcServer(new <Agent>()).run()` in its `main` method.
• Talks to DBX over stdin/stdout JSON-RPC 2.0.
• Uses JDBC for database access unless the module is explicitly designed around a non-JDBC protocol.
• Produces one shadow JAR named `dbx-agent-<agent-name>.jar`.

The public behavior should be consistent across agents even when each database has different SQL dialects.

Required Methods

Each agent must implement these capabilities:

• `connect(params)`: handled by the shared JDBC foundation for JDBC agents.
• `testConnection(params)`: handled by the shared JDBC foundation for JDBC agents.
• `listDatabases()`: return visible catalogs/databases when the database supports them; otherwise return one sensible default database.
• `listSchemas()`: return schemas in stable order.
• `listTables(schema)`: return table-like objects for the selected schema, with normalized `type` values where possible.
• `getColumns(schema, table)`: return column metadata, nullability, defaults, key flags, numeric precision/scale, character length, and comments when available.
• `listIndexes(schema, table)`: return one `IndexInfo` per index, preserving column order.
• `listForeignKeys(schema, table)`: return outbound foreign keys when available.
• `listTriggers(schema, table)`: return triggers when available; return an empty list if the database has no trigger metadata.
• `executeQuery(sql, schema, options)`: inherited from the shared JDBC foundation unless the agent has a documented custom execution behavior.
• `disconnect()`: inherited from the shared JDBC foundation.
• `getConnection()`: inherited from the shared JDBC foundation.

Throw `IllegalStateException("Not connected")` when a method requires a connection and none exists.

SQL Execution Rules

Do not classify statements by SQL prefix inside individual agents.

For most JDBC agents, inherit this from `AbstractJdbcAgent` or `ConfiguredJdbcAgent`. Do not copy the execution method into the agent class. If a custom value reader is required, override `resultValue(...)`:

@Override
protected Object resultValue(ResultSet rs, int index, int sqlType) {
    return unchecked(() -> rs.getString(index));
}

For simple standard JDBC metadata agents, start with a profile:

public final class ExampleAgent extends ConfiguredJdbcAgent {
    public static final JdbcAgentProfile EXAMPLE_PROFILE = new JdbcAgentProfile(
        "com.example.Driver",
        "jdbc:example://{host}:{port}/{database}",
        1234
    );

    public ExampleAgent() {
        super(EXAMPLE_PROFILE);
    }
}

`JdbcExecutor` owns these behaviors:

• Trims a trailing semicolon before execution.
• Handles `BEGIN`, `COMMIT`, and `ROLLBACK`.
• Runs schema switching SQL before the user statement.
• Executes statements with `Statement.execute(...)`.
• Reads `ResultSet` output for any statement type, not only `SELECT`.
• Returns update counts for update statements.
• Caps result rows at `options.maxRows`, defaulting to `JdbcExecutor.DEFAULT_MAX_ROWS`.
• Applies `options.fetchSize` to the JDBC statement when provided.
• Marks `truncated = true` only when more rows exist beyond the cap.

An agent must not reintroduce local copies of:

• `QUERY_PREFIXES`
• `MAX_ROWS`
• `executeUpdate(trimmedSql)` in `executeQuery`
• result truncation based on `rows.size >= MAX_ROWS`
• `Class.forName(...)` / `DriverManager.getConnection(...)` lifecycle boilerplate outside shared foundation extension points
• local `disconnect()` copies for JDBC agents
• local `JdbcExecutor.INSTANCE.execute(...)` copies for standard query execution

Schema And Identifier Rules

Override `setSchemaSQL(schema)` for the database dialect, or configure the profile dialect options when standard quoting is enough.

Use `JdbcIdentifiers` helpers when quoting identifiers:

@Override
public String setSchemaSQL(String schema) {
    return "SET SCHEMA " + JdbcIdentifiers.INSTANCE.doubleQuote(schema);
}

If the database does not support a schema switching statement, return an empty string:

@Override
public String setSchemaSQL(String schema) {
    return "";
}

Never concatenate unquoted user-provided schema names into schema-switching SQL unless the target database requires unquoted identifiers and the value has already been validated.

For metadata queries, prefer prepared statements for `schema`, `table`, and other user-controlled values.

Shared JDBC Foundation

Use the smallest shared base that matches the database:

• `ConfiguredJdbcAgent`: standard JDBC lifecycle, execution, and standard `DatabaseMetaData`-based metadata.
• `PostgresLikeAgent`: PostgreSQL-family metadata with shared lifecycle and execution.
• `AbstractJdbcAgent`: custom metadata SQL with shared lifecycle, execution, paging, transactions, result conversion, and DDL fallback.

If a database cannot yet use the shared foundation, add it to the validation allowlist with a specific reason and tests covering the custom behavior. Remove the allowlist entry when the module migrates.

Driver Packaging

Each module chooses one of two driver modes.

Bundled Driver

Use this when the driver is redistributable from Maven Central or another permitted repository:

dependencies {
    implementation 'com.example:example-jdbc:1.2.3'
}

The root Gradle convention supplies `project(':common')`, `project(':test-support')`, JUnit, Java toolchains, the Shadow plugin, and the `dbx-agent-<module>` archive name for included agent modules.

No man