Add Source Support Smt

Skill: Add New Database Source to Spanner Migration Tool (SMT)

Description: This skill provides comprehensive, step-by-step instructions for an agent or LLM to implement end-to-end support for a new database engine in the Spanner Migration Tool.

Overview

Adding a new database requires two main phases:

1. Backend Engine: Implementing the logic to read the source schema and translate its data types to Cloud Spanner types.
2. End-to-End Integration: Wiring the new engine into SMT’s connection profiles, API routes, telemetry, protobuf state, and Angular UI.

[!TIP] Incremental Validation (Fail Fast): Always run go build ./... and go test ./... incrementally after implementing new components, rather than waiting for Phase 3. This catches test suite panics (like forgetting flag.Parse() calls before testing.Short()) before they cascade into the final deliverable!

1. Prerequisites

1.1 Required Input Files

CRITICAL HALT RULE: Before writing any code, check if the user provided ALL of the following: a data-type mapping matrix file, a feature support matrix file, and a testing environment setup path (e.g., testing_execution.env). If the user did not provide these files and test credentials, you must STOP execution immediately and politely ask the user to provide them before proceeding. If the user does not have the mapping files, advise them to formally execute the source-baseline-matrix-research-skill to generate baseline mapping files, upon which the user can perform their own research to update and achieve the final mapping files. Do not proceed blind; physical End-to-End validation is strictly required. Do not create your own files as the user's provided files are your Absolute Ground Truth. Analyze input files: Read the Mapping Matrix (containing GoogleSQL and PostgreSQL Dialect mapping structures, including both Default and Alternative assignments) and the Feature Matrix (outlining what system features the target database supports).

Sample Structures and Header Validation: You MUST strictly validate that the column headers in the user's provided matrices identically match the structural header layouts found in the official SMT MySQL samples. If the user's matrices are missing required columns (e.g., missing specific PostgreSQL edge case columns), you must halt and require the user to structurally fix their inputs using these templates:

• Data-Type Mapping Matrix Sample: https://github.com/GoogleCloudPlatform/spanner-migration-tool/blob/master/.agents/skills/source_research_helper/sampleOutput/mysql_datatype_mapping_matrix.csv
• Feature Mapping Matrix Sample: https://github.com/GoogleCloudPlatform/spanner-migration-tool/blob/master/.agents/skills/source_research_helper/sampleOutput/mysql_feature_mapping_matrix.csv

1.2 Execution Configuration

The agent must look for a testing_execution.env file in the workspace root with the following schema:

REMOTE_WORKSPACE_PATH=
REMOTE_EXEC_TEMPLATE=
REMOTE_SYNC_TEMPLATE=

You MUST load these variables and route any physical server commands (compilation, backend boot-up, UI server) through the REMOTE_EXEC_TEMPLATE by replacing <COMMAND> with your desired command.

Phase 1: Backend Extraction Engine

[!IMPORTANT] Use MySQL as your Architectural Reference: Whenever you implement a new folder or file, study how MySQL implemented it to understand the required Go structs and SMT API contracts. You do not need absolute functional parity (especially for non-relational databases), but you must use the MySQL directory as your primary learning model for how to satisfy the internal SMT Go interfaces.

Step 1: Implement the Core GUI Schema Reader (sources/<database>/infoschema.go)

Create a new directory (e.g., sources/sqlite/) and implement the common.InfoSchema interface. This interface populates the interactive UI flow.

1. Target Matrix Categories: From the Feature Mapping Matrix, you must only extract features from the Storage (Tables, Columns), Constraints (Foreign Keys, Primary Keys, Check constraints), Indexes (Secondary Indexes), and Identity (Sequences) categories. CRITICAL RULE: If a sub-category or feature is explicitly marked as Unsupported in the Feature Mapping Matrix, you MUST explicitly exclude it from extraction so the engine does not improperly ingest restricted definitions!
2. Unsupported Spanner Features: If a generally supported feature applies to a table but violates a Spanner rule (e.g., partial Indexes), you must still write the SQL to fetch them, map them to their parent table, and attach an internal.SchemaIssue warning so it correctly flags the user on the UI's warnings tab!
3. Data Extraction Edge Cases: Implement GetRowCount and GetRowsFromTable. If the database has binary types that cannot be fetched natively (e.g., Spatial types, LOB locators), wrap those columns in appropriate conversion functions (e.g., ST_AsText(col)) inside your SELECT queries.

Step 2: Implement the Assessment Engine Reader (assessment/sources/<database>/infoschema.go)

SMT runs a completely independent static Risk Assessment report system. You must implement assessment/sources/common.go (InfoSchema).

1. Target Matrix Categories: From the Feature Mapping Matrix, you must only focus on extracting the "Other" architectural objects missing from Step 1: Views, Stored Procedures, Functions, and Triggers.
2. Implementation Flow: Write the dialect-specific SQL to fetch these exact objects, and populate SMT's TriggerAssessmentInfo, FunctionAssessmentInfo, ViewAssessmentInfo, etc. structs. This guarantees the Risk Assessment report accurately flags unsupported lines of code.

Step 3: Implement the Translator (toddl.go)

Implement the common.ToDdl interface, primarily the ToSpannerType method, adhering strictly to your Mapping Matrix ground truth.

1. Dual-Dialect Generation: Because Spanner supports two dialects, your implementation must explicitly branch (e.g., by evaluating the dialect flag). You must implement distinct translation logic for both the GoogleSQL mappings and PostgreSQL Dialect mappings documented in the user's mapping file.
2. Dynamic Regexp Precision Stripping: Databases often serialize precision details directly into the type payload (e.g. TIMESTAMP(6)). The translator must preemptively use a regex to strip numerical strings before routing the base type to internal switch evaluators to prevent translation panics!
3. Type Mapping: Implement the matrices parsed in Section 1.1. CRITICAL RULE: You must structurally map every single native type provided in the matrix precisely as it appears in the "Default Spanner Type" column across both internal dialects! Do not mock or truncate the logic. Do NOT generate independent fallback defaults unless explicitly mandated by the Matrix. Ensure you handle parsing for precisions, lengths, and arrays accurately.
4. Warnings: If a conversion is not 1-to-1 (e.g., mapping a larger integer to an Int64, or varying timestamp precisions), attach the appropriate internal.SchemaIssue (e.g., internal.Widened, internal.PossibleOverflow, internal.Datetime).
5. Unsupported Types: For proprietary types with no Spanner equivalent, do NOT crash. Fall back to ddl.String and attach the internal.NoGoodType warning.
6. Alternative Types (spType): The spType parameter to toSpannerTypeInternal represents a user-requested alternative Spanner type (e.g., from the UI). You MUST implement nested switch statements for every type to explicitly evaluate coercions (e.g., casting INT64 to STRING) and return internal.Widened where applicable. CRITICAL: Do NOT simply ignore spType and return the default type, as this permanently breaks the UI's Type Override Dropdown!
7. Auto-Generation: Implement GetColumnAutoGen to translate the database's auto-increment mechanisms (like sequences or identity columns).

Step 4: Document Unsupported Features

1. Feature Limits: Open internal/mapping.go. If your new database does not support certain advanced migration features (like foreign key ON DELETE or ON UPDATE actions), you must document it in this file by adding the database to the comments and logic handling unsupported sources.

Phase 2: End-to-End Integration (The "Glue Code")

Step 5: Protobufs and Constants

1. Constants: Open common/constants/constants.go and add a new constant representing your database driver.
2. Protobufs: Open proto/migration_data.proto and assign a new enum ID for the database within MigrationData_Source. CRITICAL RULE: Do NOT manually edit the generated proto/migration/migration_data.pb.go file to match this enum. Modifying generated .pb.go structs without using protoc directly breaks Go's internal checks and will cause fatal runtime panics (panic: non-contiguous repeated field). If you cannot run protoc, route new database connections to use MigrationData_SOURCE_UNSPECIFIED for telemetry tracking instead.