DDL2KUNLUN

In the scenario of migrating business data to Klustron, if the source database is not Postgresql, the DDL migration will fail due to incompatible syntax. In order to solve such problems, the Klustron team developed the ddl2kunlun syntax conversion tool. The main function of this tool is to convert DDL statements in MySQL syntax to DDL statements in Postgresql syntax. The basic principles and usage of the tool are introduced below.

1 Working principle

The main process of ddl2kunlun work can be divided into two parts, parsing and rewriting. The parsing part is to convert the DDL statement into a syntax tree structure. The rewriting part is to analyze the grammar book generated in the previous stage, and rewrite the DDL according to the syntax of PostgreSQL, so as to generate a DDL statement that can be executed by Klustron. The transformed DDL is fully semantically equivalent to the source DDL.

1.1 DDL analysis

ddl2kunlun can convert DDL into a syntax tree in two different modes. The first is to build a syntax tree by linking the source library and querying the source library to obtain table-related meta information. It includes column information (column name, data type, data precision), index information, table constraint information, etc. The second is to convert DDL into a syntax tree directly through text syntax analysis. After conversion into a syntax tree, the modified syntax tree is used as input for the next stage of rewriting.

1.2 DDL rewriting

The main work of the rewriting part is to extract the information of the language tree, and then rewrite the DDL according to the Klustron compatible method and output it.

1.2.1 Type mapping

Among them, the type mapping is carried out according to the following rules:

MySQL source type	Klustron target type	precision mapping
tinyint, smallint, int, year	integer
bigint	bigint
float	double prescision
enum	enum
set	varchar
blob/long	bytea
text	text
varchar	varchar	Compatible with original precision
datetime/timestamp/time	timestamp without time zone	Compatible with original precision
date	date
bit/json/decimal	bit/json/decimal	Compatible with original precision
binary	varbinary

• After the time type is mapped, it does not contain time zone information. The reason is that the static data exported by MySQL does not contain time zone information, and when downstream Klustron writes time type data with time zone information, it needs to explicitly specify the time zone information. Therefore, from the perspective of compatibility, the time zone information will be removed during the type conversion process.

1.2.2 Auto-increment key, index and other constraint mapping

The auto-increment key is mapped to a Klustron sequence, and converted into an alter table statement to be executed on the target table. The unique index is mapped to the column unique constraint of Klustron, and the secondary index is mapped to a common index. Primary key constraints and unique key constraints are converted into alter table statements and executed on the target table.

2 Example of use

Usage of ./bin/ddl2kunlun-darwin:
  -database string
        Database Or Schema Name
  -dumpView
        Dump all view define only, this will discard the 'database' and 'table' value if specified
  -host string
        Host IP address (default "127.0.0.1")
  -password string
        Password
  -port string
        Host Port Number (default "3306")
  -sourceType string
        Data Source Instance type (default "mysql")
  -table string
        Table Name
  -user string
        User Name

ddl2kunlun can also get SQL from standard input.

$ echo "
create table \`p_&&_a\`.\`t1\`(a tinyint unsigned auto_increment,
b smallint(3), c mediumint unsigned, d integer(15),
e bigint unsigned,
primary key (a),
unique index uk1(c),
index ik2(b))
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
" | ./bin/ddl2kunlun-darwin

CREATE TABLE `p_&&_a`.`t1` (    
        `a` int ,
        `b` int ,
        `c` bigint ,
        `d` bigint ,
        `e` bigint ,
        constraint t1_pkey primary key ( a )
);

alter table p_&&_a.t1 alter column a add auto_increment;

alter table `t1` add constraint `uk1` unique (
        `c`
);
create index `ik2` on `t1` (
        `b`
);

END