Plugin SQL Target Executor Design

Context

P2-D8e2a makes Transition execution durable and recoverable, but the only installed Executor is dry-run:v1. P2-D8e2b adds the first real target adapter without turning the Plugin system into an unrestricted database administration channel.

The adapter is disabled by default and points at one explicitly configured isolated MySQL catalog. It must never connect to the RuoYi control catalog, must not accept cross-catalog SQL, and must make an ambiguous Outbox redelivery safe through both a stable step idempotency key and a target-side receipt lease.

Approaches Considered

Dynamic Flyway migrations provide mature history semantics, but arbitrary in-memory Plugin payload registration would add a new runtime/dependency model and still requires policy around failed non-transactional MySQL 5.7 DDL.
Transactional DML only gives strong rollback but cannot validate the database-migration path that this stage is intended to establish.
A strict idempotent SQL subset plus a target-side receipt ledger uses the existing Druid AST parser, supports a small real migration surface, and makes MySQL 5.7 implicit DDL commits recoverable by safe repetition. This is the selected approach.

Payload Contract

PluginContributionPayload and immutable Transition steps gain idempotent. Transition Plan schema becomes 1.2. Publication fingerprints include this declaration, and execution re-resolution verifies that the installed payload still has the recorded value.

sql-jdbc:v1 accepts only contributions with all of these properties:

type DATABASE_MIGRATION;
target sql;
idempotent=true recorded in the Transition Plan and trusted installed payload;
non-empty UTF-8 content accepted by the strict MySQL parser.

Historical 1.0 and 1.1 plans remain usable with dry-run:v1. They cannot be promoted to real SQL execution because they do not carry the immutable idempotence declaration. Schema 1.1 and 1.2 both carry compensation identity and remain eligible for compensation through dry-run.

SQL Policy

Druid 1.2.23 parses the complete payload into MySQL AST statements. The Executor never splits SQL by string delimiters.

The first policy allows no more than 100 statements and 1 MiB of SQL. It accepts only:

CREATE TABLE IF NOT EXISTS;
DROP TABLE IF EXISTS;
MySQL INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE;
DELETE with a non-empty WHERE clause.

It rejects ALTER, UPDATE, TRUNCATE, CREATE/DROP DATABASE, USE, SET, transaction control, account/GRANT statements, stored programs, and every other AST type. Every SQLExprTableSource must be unqualified or explicitly use the configured allowed catalog; cross-catalog references are rejected.

This is intentionally not a general migration engine. Broader operations require a future versioned Executor code and a stronger verification contract.

Isolated Target Configuration

factory.plugin-execution.sql-target contains:

enabled, default false;
environment-code, required safe identifier when enabled;
jdbc-url, required and restricted to jdbc:mysql:;
username and password;
driver-class-name, default com.mysql.cj.jdbc.Driver;
allowed-catalog, required safe catalog identifier;
receipt-lease-seconds, default 300 and bounded to 30..3600.

Opening a connection verifies the target connection catalog matches allowed-catalog. It also opens the configured RuoYi master DataSource and rejects an equal normalized JDBC URL or equal control catalog. The target connection is closed before propagating any validation failure.

No target secret is returned by Registry status or execution history. The admin status exposes only enabled/readiness, environment code, allowed catalog, and configured Executor code.

Target Receipt Ledger

The isolated target database must run sql/factory_plugin_sql_target.sql before sql-jdbc:v1 is enabled. It creates factory_plugin_target_receipt with a unique step_idempotency_key and these durable fields:

environment, Plugin/version, direction and content hash identity;
RUNNING, SUCCEEDED, or FAILED status;
target lease token/deadline;
execution count, external receipt, last error, and timestamps.

Claiming a new key inserts RUNNING and commits before executing migration statements. A duplicate key is locked and checked:

SUCCEEDED with matching identity returns the existing external receipt without executing SQL.
RUNNING with an unexpired target lease is rejected and cannot be stolen.
FAILED or expired RUNNING with matching identity is claimed with a new token and execution count.
Any direction, content hash, Plugin release, or environment mismatch is rejected.

After all parsed statements execute, the Executor updates SUCCEEDED only when the lease token still matches. On SQL failure it rolls back what MySQL can roll back, then records FAILED with the same token. MySQL 5.7 DDL may have committed before a process interruption; safe recovery therefore depends on the immutable idempotent=true contract and strict repeatable SQL subset.

The returned external receipt is deterministic: sql:<environment-code>:<step-idempotency-key>.

Execution Flow

The D8e2a Worker passes the trusted resolved contribution and stable step key to sql-jdbc:v1 outside its database transaction.
The Executor validates target configuration, immutable idempotence, SQL AST policy, and isolated connection identity.
The target ledger returns existing success, rejects an active lease, or grants a target lease.
The Executor runs normalized AST statements on the target connection.
The target receipt is completed and returned to the control-side step receipt.

The control Outbox lease and target receipt lease are independent. A stale control Worker cannot overwrite control state, while duplicate target calls converge on the target receipt key.

UI And Operations

Registry status displays the configured Executor and a concise SQL target state: disabled, incomplete, or ready. It never displays JDBC URL, username, or password. The execution dialog continues to show the concrete Executor code and external receipt.

Operators must apply both the control Outbox migration and the isolated target receipt script before enabling:

FACTORY_PLUGIN_EXECUTOR_CODE=sql-jdbc:v1
FACTORY_PLUGIN_SQL_TARGET_ENABLED=true
FACTORY_PLUGIN_SQL_TARGET_ENVIRONMENT_CODE=preview
FACTORY_PLUGIN_SQL_TARGET_JDBC_URL=jdbc:mysql://.../ruoyi_plugin_preview
FACTORY_PLUGIN_SQL_TARGET_USERNAME=...
FACTORY_PLUGIN_SQL_TARGET_PASSWORD=...
FACTORY_PLUGIN_SQL_TARGET_ALLOWED_CATALOG=ruoyi_plugin_preview

Testing

Contract tests cover bounded configuration, schema 1.2 propagation, and historical compensation compatibility.
Parser tests use real Druid AST parsing, including semicolons inside literals, whitelist enforcement, and cross-catalog rejection.
Target connection tests use mocked JDBC metadata to prove allowlist and control-catalog isolation.
Executor tests use H2 1.4.200 in MySQL mode from the existing Spring Boot BOM to execute a reversible migration, prove duplicate SUCCEEDED delivery does not re-run SQL, exercise failed/expired receipt recovery, and verify compensation.
Existing Plugin, generator, admin, and frontend regressions remain required. No production target connection is attempted during tests.

Scope

P2-D8e2b implements only DATABASE_MIGRATION + sql. Permission and menu adapters remain on dry-run:v1. Existing PageBlock plugins still carry no real delivery payload. A production Plugin must opt in with a reviewed idempotent payload before real SQL is executed.

7.5 KiB Raw Permalink Blame History