| name | sql-to-osc |
| description | SQL to OSC (Online Schema Change) conversion expert for Flyway migration scripts.
Use when: (1) Converting SQL migration files to OSC format, (2) User mentions "OSC", "轉換 OSC", "osc.txt", or "Online Schema Change", (3) Working with Flyway ALTER TABLE/CREATE INDEX statements that need OSC conversion.
|
SQL to OSC Conversion Expert
Convert Flyway SQL migration scripts to OSC format following project conventions.
Quick Reference
OSC Format: {database}<TAB>{table}<TAB>{operations};
| SQL | OSC |
|---|
USE db; | Remove (db in column 1) |
ALTER TABLE tbl | Remove (tbl in column 2) |
NULL | DEFAULT NULL |
CREATE INDEX idx ON tbl (col) | ADD INDEX idx (col) |
varchar | VARCHAR |
| Multiple operations | Comma-joined, no space |
Conversion Workflow
- Read source SQL from
src/main/resources/db/migration/
- Parse statements: Extract database, table, operations
- Transform:
- Remove
USE and ALTER TABLE wrappers
- Convert
NULL → DEFAULT NULL
- Convert
CREATE INDEX...ON table → ADD INDEX...
- Uppercase data types
- Format output:
{db}\t{table}\t{op1},{op2},...;
- Write to
src/main/resources/db/osc/osc-{YYYYMMDD}.txt (e.g., osc-20251212.txt)
Output Requirements
- Encoding: UTF-8 (no BOM)
- Line ending: LF (
\n)
- Separator: TAB (
\t) between columns
- Operations: Comma (
,) joined, NO space after comma
- Line ending: Semicolon (
;)
Example
Input (V1.0__alter_my_table.sql):
USE mydb;
ALTER TABLE MY_TABLE
ADD COLUMN NEW_COL bigint(20) NULL AFTER EXISTING_COL;
CREATE INDEX MY_TABLE_NEW_COL_IDX ON MY_TABLE (NEW_COL);
Output (osc-{YYYYMMDD}.txt):
mydb MY_TABLE ADD COLUMN NEW_COL BIGINT(20) DEFAULT NULL AFTER EXISTING_COL,ADD INDEX MY_TABLE_NEW_COL_IDX (NEW_COL);
Conversion Summary Template
✓ 轉換完成
來源: {source_file}
輸出: src/main/resources/db/osc/osc-{YYYYMMDD}.txt
影響資料表: {tables}
操作統計:
- ADD COLUMN: {count}
- ADD INDEX: {count}
- MODIFY COLUMN: {count}
