# auto-ddl

**Repository Path**: linanlin/auto-ddl

## Basic Information

- **Project Name**: auto-ddl
- **Description**: 自动生成 数据库 DDL 语句；支持 jpa、mybaits-plus及自定义注解
- **Primary Language**: Unknown
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2024-08-07
- **Last Updated**: 2024-08-07

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# auto-ddl

根据实体类生成数据库表结构维护语句 （DDL） 包括：添加列、修改列、删除列、创建表、添加索引、删除索引等；目前仅支持 MySql 8.0
数据库，但是兼容 JPA 及 MyBatis Plus 注解。

开发环境中表结构经常需要变更时可以使用；生产环境建议维护语句输出到文件空控制台，通过一次人工审核后执行。

- 引入依赖

```xml

<dependency>
    <groupId>design.donkey</groupId>
    <artifactId>auto-ddl</artifactId>
    <version>1.0.0</version>
</dependency>
```

- 使用方式

自动获取方言及解析器

```java
import design.donkey.auto.ddl.AutoDdl;

import javax.sql.DataSource;

class DdlTest {

    @Test
    void testDdlFile() {
        DataSource dataSource = null;
        AutoDdl.factory(dataSource, new HashSet<>(Arrays.asList(DdlOperation.ADD_COLUMN, DdlOperation.ADD_INDEX)), true, true);
    }
}
```

DEMO 代码可查看 src/test/java 目录下

```java

/**
 * test.Test
 *
 * @author zhangjiakung
 * @since 0.0.1
 */
class DdlTest {

    @Test
    void testDdlFile() {
        Dialect dialect = new MySql8Dialect();
        AnnotationAnalysis annotationAnalysis = new AnnotationAnalysis();
        AutoDdl autoDdl = new AutoDdl(new HashSet<>(Arrays.asList(DdlOperation.ADD_COLUMN, DdlOperation.ADD_INDEX)),
                dialect, annotationAnalysis, true, true);

        {
            Map<String, List<String>> maintenance = autoDdl.maintenance("", "");
            Assertions.assertTrue(maintenance.isEmpty());
        }

        {
            String sourceSql = """

                    """;
            String targetSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks` text COLLATE utf8mb4_bin COMMENT '备注',
                      `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`),
                      UNIQUE KEY `nickname` (`nickname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'
                                    """;
            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, targetSql);
            Assertions.assertTrue(maintenance.isEmpty());
        }

        {
            String sourceSql =
                    """
                            CREATE TABLE `security_user` (
                                             `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
                                             `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                                             `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                                             `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                                             `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                                             `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                                             `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                                             `password` text COLLATE utf8mb4_bin COMMENT '密码',
                                             `remarks` text COLLATE utf8mb4_bin COMMENT '备注',
                                             `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                                             `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                             `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                                             PRIMARY KEY (`id`),
                                             UNIQUE KEY `username` (`username`),
                                             UNIQUE KEY `nickname` (`nickname`)
                                           ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'
                                           """;
            String targetSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID 22',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks344` text COLLATE utf8mb4_bin COMMENT '备注',
                      `delEted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`,`city_code`),
                      UNIQUE KEY `nickName2` (`nicKname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='67878'
                                    """;
            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, targetSql);
            Assertions.assertEquals(1, maintenance.size());
            Assertions.assertEquals(2, maintenance.get("`security_user`").size());
        }

        {
            String sourceSql = """
                           CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID 22',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks344` text COLLATE utf8mb4_bin COMMENT '备注',
                      `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`,`city_code`),
                      UNIQUE KEY `nickname2` (`nickname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='67878'
                                           """;
            String targetSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID 22',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks344` text COLLATE utf8mb4_bin COMMENT '备注',
                      `delEted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`,`city_code`),
                      UNIQUE KEY `nickName2` (`nicKname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='67878'
                                    """;
            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, targetSql);
            Assertions.assertTrue(maintenance.isEmpty());
        }

        {
            autoDdl.maintenanceAndRun(null);
        }
    }

    @Test
    void testDdlAnnotation() {
        Dialect dialect = new MySql8Dialect();
        AnnotationAnalysis annotationAnalysis = new JpaAnnotationAnalysis();
        AutoDdl autoDdl = new AutoDdl(new HashSet<>(Arrays.asList(DdlOperation.ADD_COLUMN, DdlOperation.ADD_INDEX)),
                dialect, annotationAnalysis, true, true);

        {
            String sourceSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks` text COLLATE utf8mb4_bin COMMENT '备注',
                      `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`),
                      UNIQUE KEY `nickname` (`nickname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'
                                    """;
            List<String> targetSql = dialect.create(annotationAnalysis.analysis(SecurityUser.class));

            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, String.join(";", targetSql));
            Assertions.assertTrue(maintenance.isEmpty());
        }

    }

    @Test
    void testDdlJpa() {
        Dialect dialect = new MySql8Dialect();
        AnnotationAnalysis annotationAnalysis = new JpaAnnotationAnalysis();
        AutoDdl autoDdl = new AutoDdl(new HashSet<>(Arrays.asList(DdlOperation.ADD_COLUMN, DdlOperation.ADD_INDEX)),
                dialect, annotationAnalysis, true, true);

        {
            String sourceSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks` text COLLATE utf8mb4_bin COMMENT '备注',
                      `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`),
                      UNIQUE KEY `nickname` (`nickname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'
                                    """;
            List<String> targetSql = dialect.create(annotationAnalysis.analysis(SecurityUserJpa.class));

            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, String.join(";", targetSql));
            Assertions.assertTrue(maintenance.isEmpty());
        }

    }

    @Test
    void testDdlMyBatisPlus() {
        Dialect dialect = new MySql8Dialect();
        AnnotationAnalysis annotationAnalysis = new MyBatisPlusAnnotationAnalysis();
        AutoDdl autoDdl = new AutoDdl(new HashSet<>(Arrays.asList(DdlOperation.ADD_COLUMN, DdlOperation.ADD_INDEX)),
                dialect, annotationAnalysis, true, true);

        {
            String sourceSql = """
                    CREATE TABLE `security_user` (
                      `id` int NOT NULL AUTO_INCREMENT COMMENT '用户 ID',
                      `username` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
                      `nickname` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
                      `city_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '归属地编码',
                      `company_id` bigint DEFAULT NULL COMMENT '单位 ID',
                      `expired_date` datetime DEFAULT NULL COMMENT '过期时间',
                      `last_login_date` datetime DEFAULT NULL COMMENT '上次登录时间',
                      `password` text COLLATE utf8mb4_bin COMMENT '密码',
                      `remarks` text COLLATE utf8mb4_bin COMMENT '备注',
                      `deleted` int NOT NULL DEFAULT '0' COMMENT '是否已删除',
                      `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                      `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
                      PRIMARY KEY (`id`),
                      UNIQUE KEY `username` (`username`),
                      UNIQUE KEY `nickname` (`nickname`)
                    ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户'
                                    """;
            List<String> targetSql = dialect.create(annotationAnalysis.analysis(SecurityUserMyBatisPlus.class));

            Map<String, List<String>> maintenance = autoDdl.maintenance(sourceSql, String.join(";", targetSql));
            Assertions.assertTrue(maintenance.isEmpty());
        }

    }
}

```

## 更新日志

### V1.1.0

- 根据数据源及所使用项目的依赖自动生成 AutoDll 类
- 添加默认方言，当数据库不兼容时亦可使用

### V1.0.0

- 根据实体类生成数据库表结构维护语句 （DDL） 包括：添加列、修改列、删除列、创建表、添加索引、删除索引等。
- 支持 MySql 8.0 数据库
- 兼容 JPA 注解
- 兼容 MyBatis Plus 注解

## 发布方式

用于根据实体自动维护数据库表结果

deploy

```shell
    mvn clean deploy -f pom.xml -U  -DskipTests -DaltDeploymentRepository=ossrh::default::https://s01.oss.sonatype.org/content/repositories/snapshots -DrepoUser=user "-DrepoPass=password" -DrepoId=ossrh
```

```shell
    mvn clean deploy -f pom.xml -U  -DskipTests -DaltDeploymentRepository=ossrh::default::https://s01.oss.sonatype.org/service/local/staging/deploy/maven2/ -DrepoUser=user "-DrepoPass=password" -DrepoId=ossrh
```