com.anji.plus.gaea.archiver.sqlbuilder.builders.MysqlBuilder Maven / Gradle / Ivy

Go to download
package com.anji.plus.gaea.archiver.sqlbuilder.builders;

import com.anji.plus.gaea.archiver.config.ArchiverTable;
import com.anji.plus.gaea.archiver.sqlbuilder.ISQLBuilder;
import com.anji.plus.gaea.archiver.utils.DateUtil;
import com.anji.plus.gaea.archiver.utils.SystemUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.io.File;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * mysql相关sql语句
 *
 * @author 木子李·De
 * @since 2021/2/3 14:16
 */
public class MysqlBuilder implements ISQLBuilder {

    private Logger log = LoggerFactory.getLogger(MysqlBuilder.class);

    @Override
    public String existTable(String tableName) {
        String sql = String.format("desc %s", tableName);
        return sql;
    }

    @Override
    public String existTableField(String tableName, String fieldName) {
        String sql = String.format("desc %s", tableName);
        return sql;
    }

    @Override
    public String getNow() {
        String sql = "select now() as now";
        return sql;
    }

    @Override
    public String countRows(String sourceTableName) {
        return null;
    }

    @Override
    public String getCrossMonthList(String tablename, String timefield, Integer maxDaysBeforeArchive) {
        String sql = String.format("select date_format(%s,'%%Y-%%m') month from %s where %s < DATE_SUB(NOW(), interval %d day) group by date_format(%s,'%%Y-%%m')", timefield, tablename, timefield, maxDaysBeforeArchive, timefield);
        return sql;
    }

    @Override
    public String createArchiverTableIfNotExist(String archiveTableName, String sourceTableName) {
        String sql = String.format("create table %s like %s", archiveTableName, sourceTableName);
        return sql;
    }


    @Override
    public String doArchiver(String sourceTableName, Date startTime, Date endTime) {
        return null;
    }

    /**
     * 扫描以表名为前缀的所有归档表
     *
     * @param sourceTableName
     * @return
     */
    @Override
    public String scanArchive(String sourceTableName, String dbName) {
        String sql = String.format("SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = '%s' and table_name like '%s'", dbName, sourceTableName + "_%");
        return sql;
    }

    /**
     * 执行dump命令
     * mysqldump --set-gtid-purged=OFF --single-transaction -h10.108.26.197 -P3306 -uroot -pappuser@anji --databases gaea_auth --tables  gaea_log gaea_log_202003 gaea_log_202007 > db.sql
     *
     * @param archiverTable
     * @return
     */
    @Override
    public String executeDump(ArchiverTable archiverTable) {
        StringBuilder builder = new StringBuilder();
        String dumpFile = archiverTable.getDumpFile();
        File file = new File(dumpFile + File.separator + DateUtil.now() + ".sql");
        File fileParent = file.getParentFile();
        if(!fileParent.exists()){
            fileParent.mkdirs();
        }
        builder.append("mysqldump --set-gtid-purged=OFF --single-transaction -h").append(archiverTable.getDumpHost())
                .append(" -P").append(archiverTable.getDumpPort())
                .append(" -u").append(archiverTable.getDumpUser())
                .append(" -p").append(archiverTable.getDumpPasswd())
                .append(" --databases ").append(archiverTable.getDbName())
                .append(" --tables ");
        archiverTable.getDumpTableList().forEach(tableName -> {
            builder.append(tableName).append(" ");
        });
        builder.append(" > ").append(file.getPath());
        log.info("mysql execute dump：{}", builder);
        String result = SystemUtils.exeCmd(builder.toString());
        log.info("execute result：{}", result);
        return result;
    }

    /**
     * drop 表
     *
     * @param archiverTable
     * @return
     */
    @Override
    public String dropTable(ArchiverTable archiverTable) {
        List list = archiverTable.getDumpTableList();
        StringBuilder sb = new StringBuilder();
        sb.append("drop table ");
        for (int i = 0; i < list.size(); i++) {
            if (i < list.size() - 1) {
                sb.append(list.get(i));
                sb.append(",");
            } else {
                sb.append(list.get(i));
            }
        }
        log.info("drop sql：{}", sb);
        return sb.toString();
    }
}