将SQL文件转Liquibase Changelog.xml的实现

SQL文件转Liquibase Changelog.xml的实现

项目实践中使用了Liquibase对数据库版本进行管理，每次创建表和表结构的改变，都需要编写相应的Changelog.xml文件，如果需求涉及的表很多，那直接不要太酸爽。而SQL是有一定规范的文件，Changelog.xml也一样，这样就尝试想做一个转换的工具。

思路

将SQL文件格式化成结构化对象
然后对应结构化Change对象
闲话少说上代码

依赖

两个包
jsqlparser：把sql文件格式化成结构化对象
liquibase-maven-plugin：结构化Change对象生成xml

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.2</version>
</dependency>
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>4.6.2</version>
</dependency>

代码实现

package com.lyu.generator;

import liquibase.change.Change;
import liquibase.change.ColumnConfig;
import liquibase.change.ConstraintsConfig;
import liquibase.change.core.AddPrimaryKeyChange;
import liquibase.change.core.CreateTableChange;
import liquibase.change.core.SetColumnRemarksChange;
import liquibase.change.core.SetTableRemarksChange;
import liquibase.changelog.ChangeSet;
import liquibase.changelog.DatabaseChangeLog;
import liquibase.serializer.core.xml.XMLChangeLogSerializer;
import net.sf.jsqlparser.parser.CCJSqlParser;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.parser.ParseException;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;
import java.util.stream.Collectors;

/**
 * @Author: Lyu
 * @Description: sql文件转换Liquibase Changelog.xml
 * @Date: Created in 11:01 2022/8/12
 * @Modified By:
 */
public class GeneratorChangeLogXmlUtils {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入SQL的路径，具体到文件，回车结束");
        String inputPath = scanner.nextLine();
        System.out.println("请输入作者，回车结束");
        String author = scanner.nextLine();
        System.out.println("请输入生成的xml存放路径，回车结束");
        String outputPath = scanner.nextLine();
//        execute("D:\\szewec_code\\lei-hr\\src\\main\\resourcesliguibaseltest.sql","author","D:\\2019_szewec");
        execute(inputPath, author, outputPath);
        scanner.close();
    }

    /**
     * 执行方法
     *
     * @param inputPath  输入SQL路径
     * @param author     作者
     * @param outputPath 输出xml路径
     */
    private static void execute(String inputPath, String author, String outputPath) {
        if (!inputPath.endsWith(".sql")) {
            throw new IllegalStateException("文件格式不正确，不是SQL文件");
        }
        File file = new File(inputPath);
        String fileName = file.getName();

        try {
            DatabaseChangeLog databaseChangeLog = genaratorChangeLog(file, author);
            // write output
            changeLogXmlWrite(databaseChangeLog, inputPath, outputPath, fileName);
        } catch (IOException | ParseException e) {
            e.printStackTrace();
        }

    }


    /**
     * 生成changelog
     *
     * @param file   SQL文件
     * @param author 作者
     * @return DatabaseChangeLog
     */
    private static DatabaseChangeLog genaratorChangeLog(File file, String author) throws IOException, ParseException {
        // 读SQL
        CCJSqlParser ccjSqlParser = CCJSqlParserUtil.newParser(new FileInputStream(file));

        // 解析成statements
        List<Statement> statements = ccjSqlParser.Statements().getStatements();

        // ChangeLog
        DatabaseChangeLog databaseChangeLog = new DatabaseChangeLog();
        List<ChangeSet> changeSets = new ArrayList<>();

        for (Statement statement : statements) {
            System.out.println("parse statement :" + statement.toString());
            if (statement instanceof CreateTable) {
                // 创建changeSet
                ChangeSet changeSet = getInstanceChangeSet(null, author, databaseChangeLog);
                // create table statement
                CreateTable createTable = (CreateTable) statement;
                CreateTableChange createTableChange = new CreateTableChange();
                createTableChange.setTableName(createTable.getTable().getName().replace("\"", "").replace("\'", ""));
                createTableChange.setSchemaName(createTable.getTable().getSchemaName().replace("\"", "").replace("\'", ""));

                // 处理column
                List<ColumnConfig> columnConfigs = createTable.getColumnDefinitions().stream().map(m -> {
                    ColumnConfig columnConfig = new ColumnConfig();
                    columnConfig.setName(m.getColumnName().replace("\"", "").replace("\'", ""));
                    columnConfig.setType(m.getColDataType().toString());

                    if (CollectionUtils.isNotEmpty(m.getColumnSpecs())) {
                        ConstraintsConfig constraintsConfig = null;
                        // primary key
                        if (hasAttr(m.getColumnSpecs(), "primary") && hasAttr(m.getColumnSpecs(), "key")) {
                            if (constraintsConfig == null) {
                                constraintsConfig = new ConstraintsConfig();
                            }
                            constraintsConfig.setPrimaryKey(true);
                            columnConfig.setConstraints(constraintsConfig);
                        }

                        // not null
                        if (hasAttr(m.getColumnSpecs(), "not") && hasAttr(m.getColumnSpecs(), "null")) {
                            if (constraintsConfig == null) {
                                constraintsConfig = new ConstraintsConfig();
                            }
                            constraintsConfig.setNullable(false);
                            columnConfig.setConstraints(constraintsConfig);
                        }
                    }

                    return columnConfig;
                }).collect(Collectors.toList());

                createTableChange.setColumns(columnConfigs);
                changeSet.addChange(createTableChange);

                // 判断是否有primary key，默认设置第一列
                if (!isPrimaryKeyCheck(columnConfigs)) {
                    AddPrimaryKeyChange addPrimaryKeyChange = new AddPrimaryKeyChange();
                    addPrimaryKeyChange.setColumnNames(columnConfigs.get(0).getName());
                    addPrimaryKeyChange.setConstraintName("pk_".concat(createTableChange.getTableName()));
                    addPrimaryKeyChange.setTableName(createTableChange.getTableName());
                    changeSet.addChange(addPrimaryKeyChange);
                }

                changeSets.add(changeSet);
            } else if (statement instanceof Comment) {
                Comment comment = (Comment) statement;
                SetTableRemarksChange setTableRemarksChange = null;
                SetColumnRemarksChange setColumnRemarksChange = null;
                String tableName;
                // 设置表comment
                if (comment.getTable() != null && StringUtils.isNotBlank(comment.getTable().getName())) {
                    setTableRemarksChange = new SetTableRemarksChange();
                    setTableRemarksChange.setTableName(comment.getTable().getName().replace("\"", "").replace("\'", ""));
                    setTableRemarksChange.setRemarks(comment.getComment().toString().replace("\"", "").replace("\'", ""));
                    tableName = setTableRemarksChange.getTableName();
                } else {
                    // 设置column comment
                    setColumnRemarksChange = new SetColumnRemarksChange();
                    setColumnRemarksChange.setTableName(comment.getTable().getName().replace("\"", "").replace("\'", ""));
                    setColumnRemarksChange.setColumnName(comment.getComment().toString().replace("\"", "").replace("\'", ""));
                    tableName = setColumnRemarksChange.getTableName();
                }

                // 存在create 相同table加入相同的changeSet，否则新建
                if (isEqualsTable(changeSets, tableName)) {
                    if (setTableRemarksChange != null) {
                        addExistChange(changeSets, setTableRemarksChange, tableName);
                    }
                    if (setColumnRemarksChange != null) {
                        addExistChange(changeSets, setColumnRemarksChange, tableName);
                    }
                } else {
                    ChangeSet changeSet = getInstanceChangeSet(null, author, databaseChangeLog);
                    changeSet.addChange(setTableRemarksChange);
                    changeSet.addChange(setColumnRemarksChange);
                    changeSets.add(changeSet);
                }
            }
        }

        changeSets.forEach(databaseChangeLog::addChangeSet);

        return databaseChangeLog;
    }

    /**
     * 写change文件
     * @param databaseChangeLog databaseChangeLog
     * @param inputPath 输入路径
     * @param outputPath 输出路径
     * @param fileName 文件名
     */
    private static void changeLogXmlWrite(DatabaseChangeLog databaseChangeLog, String inputPath, String outputPath, String fileName) throws IOException {
        // write
        XMLChangeLogSerializer xmlChangeLogSerializer = new XMLChangeLogSerializer();
        String outName = StringUtils.isBlank(outputPath) ? inputPath.replace(fileName, "").concat(".xml") : outputPath + "\\" + fileName.substring(0, fileName.indexOf(".")).concat(".xml");
        FileOutputStream fileOutputStream = new FileOutputStream(new File(outName));

        xmlChangeLogSerializer.write(databaseChangeLog.getChangeSets(), fileOutputStream);
    }

    /**
     * 创建ChangeSet对象
     *
     * @param id                key
     * @param author            作者
     * @param databaseChangeLog databaseChangeLog
     * @return ChangeSet
     */
    private static ChangeSet getInstanceChangeSet(String id, String author, DatabaseChangeLog databaseChangeLog) {
        String key = StringUtils.isBlank(id) ? String.valueOf(System.currentTimeMillis()) : id;
        return new ChangeSet(key, author, false, false, "", null, null, true, null, databaseChangeLog);
    }

    /**
     * 相同表归入一个changeSet
     * @param changeSets 集合
     * @param setTableRemarksChange remark
     * @param tableName 表名
     */
    private static void addExistChange(List<ChangeSet> changeSets, Change setTableRemarksChange, String tableName) {
        if (CollectionUtils.isEmpty(changeSets) || StringUtils.isBlank(tableName)) {
            return;
        }
        ChangeSet temp = null;
        Iterator iterator = changeSets.iterator();
        while (iterator.hasNext()) {
            ChangeSet changeSet = (ChangeSet) iterator.next();
            for (Change change : changeSet.getChanges()) {
                if (change instanceof CreateTableChange) {
                    CreateTableChange createTableChange = (CreateTableChange) change;
                    if (tableName.toLowerCase().equals(createTableChange.getTableName().toLowerCase())) {
                        temp = getInstanceChangeSet(changeSet.getId(), changeSet.getAuthor(), changeSet.getChangeLog());
                        for (Change changeSetChange : changeSet.getChanges()) {
                            temp.addChange(changeSetChange);
                        }
                        iterator.remove();
                        break;
                    }
                }
            }
        }

        if (temp != null) {
            temp.addChange(setTableRemarksChange);
            changeSets.add(temp);
        }
    }

    /**
     * 判断是否相同table
     * @param changeSets 集合
     * @param tableName 表名
     * @return 布尔值
     */
    private static boolean isEqualsTable(List<ChangeSet> changeSets, String tableName) {
        if (CollectionUtils.isEmpty(changeSets) || StringUtils.isBlank(tableName)) {
            return false;
        }

        for (ChangeSet changeSet : changeSets) {
            for (Change change : changeSet.getChanges()) {
                if (change instanceof CreateTableChange) {
                    CreateTableChange createTableChange = (CreateTableChange) change;
                    if (tableName.toLowerCase().equals(createTableChange.getTableName().toLowerCase())) {
                        return true;
                    }
                }
            }
        }

        return false;
    }

    /**
     * 处理 columnSpecs
     *
     * @param columnSpecs columnSpecs
     * @param attr        目标字符
     * @return 布尔值
     */
    private static boolean hasAttr(List<String> columnSpecs, String attr) {
        return columnSpecs.stream().anyMatch(item -> item.toLowerCase().endsWith(attr));
    }

    /**
     * 判断是否存在 primary key
     *
     * @param columnConfigs columns
     * @return 布尔值
     */
    private static boolean isPrimaryKeyCheck(List<ColumnConfig> columnConfigs) {
        return columnConfigs.stream().anyMatch(x -> x.getConstraints() != null && x.getConstraints().isPrimaryKey() != null && x.getConstraints().isPrimaryKey());
    }

}

使用任何你熟悉的方式打包成jar，做成批处理bat脚本

%cd%
java -Dfile.encoding=utf-8 -jar GeneratorChangeLogXml.jar
pause

执行效果

最后

以上就是将SQL文件转换成Liquibase Changelog.xml的一种尝试。