package com.product.patch.service;
|
|
import com.google.common.collect.Lists;
|
import com.product.common.lang.StringUtils;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.core.transfer.Transactional;
|
import com.product.patch.service.idel.ISyncTableStructureService;
|
import com.product.tool.table.enums.FieldType;
|
import com.product.util.BaseUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import java.util.Arrays;
|
import java.util.List;
|
import java.util.stream.Collectors;
|
|
/**
|
* 实现功能：
|
*
|
* @author 作者[夜丶光]
|
* @version 1.0.00 2024-10-16 16:39
|
*/
|
@Service
|
public class SyncTableStructureService extends AbstractBaseService implements ISyncTableStructureService {
|
@Autowired
|
private BaseDao baseDao;
|
|
/**
|
* 查询获取出数据库表结构字段和缓存字段表字段存在差异的表
|
* @return
|
*/
|
@Override
|
public List<String> listDiffTable() {
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\nWITH t1 AS (");
|
sql.append("\n SELECT t.table_name,f.field_name");
|
sql.append("\n FROM product_sys_datamodel_field f");
|
sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
|
sql.append("\n),t2 AS (");
|
sql.append("\n SELECT table_name,column_name field_name");
|
sql.append("\n FROM information_schema.`COLUMNS`");
|
sql.append("\n WHERE table_schema='product_db_v3.0.0'");
|
sql.append("\n)");
|
sql.append("\nSELECT DISTINCT table_name");
|
sql.append("\nFROM (");
|
sql.append("\n SELECT t1.table_name table_name,t1.field_name field_name FROM t1 LEFT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t2.field_name IS NULL");
|
sql.append("\n UNION ALL ");
|
sql.append("\n SELECT t2.table_name table_name,t2.field_name field_name FROM t1 RIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t1.field_name IS NULL");
|
sql.append("\n) t");
|
sql.append("\nORDER BY 1");
|
DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
|
List<String> list = Lists.newArrayList();
|
for (int i = 0; i < dte.getRows(); i++) {
|
FieldSetEntity fse = dte.getFieldSetEntity(i);
|
list.add(fse.getString("table_name"));
|
}
|
System.out.println(list);
|
return list;
|
}
|
|
/**
|
* 将数据库表结构字段同步至缓存字段表字段
|
*/
|
@Override
|
@Transactional
|
public void execute() {
|
// 将字段缓存表中多余的字段清理，只能直接连接数据库执行
|
clearTableEx();
|
|
// 将数据库中表结构字段同步到字段缓存表中
|
syncDB2TableForTable();
|
syncDB2TableForField();
|
}
|
|
/**
|
* 将数据库中表结构字段同步到字段缓存表中-表
|
*/
|
public void syncDB2TableForTable() {
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\nWITH t1 AS (");
|
sql.append("\n SELECT uuid,table_name,table_description,table_type FROM product_sys_datamodel_table");
|
sql.append("\n),t2 AS (");
|
sql.append("\n SELECT TABLE_NAME table_name,TABLE_COMMENT table_description,CASE WHEN TABLE_TYPE='BASE TABLE' THEN 1 ELSE 2 END table_type");
|
sql.append("\n FROM information_schema.`TABLES`");
|
sql.append("\n WHERE table_schema='product_db_v3.0.0'");
|
sql.append("\n)");
|
sql.append("\nSELECT t2.*");
|
sql.append("\nFROM t1");
|
sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name");
|
sql.append("\nWHERE t1.table_name IS NULL");
|
DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
|
for (int i = 0; i < dte.getRows(); i++) {
|
FieldSetEntity fse = dte.getFieldSetEntity(i);
|
FieldSetEntity saveFse = new FieldSetEntity();
|
saveFse.setTableName("product_sys_datamodel_table");
|
saveFse.setValue("table_name", fse.getString("table_name"));
|
saveFse.setValue("table_description", fse.getString("table_description"));
|
saveFse.setValue("table_type", fse.getString("table_type"));
|
saveFse.setValue("sequence", 1);
|
System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
|
baseDao.add(saveFse);
|
}
|
}
|
|
/**
|
* 将数据库中表结构字段同步到字段缓存表中-字段
|
*/
|
public void syncDB2TableForField() {
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\nWITH t1 AS (");
|
sql.append("\n SELECT t.table_name,f.table_uuid,f.field_name,field_type type,field_length,field_unit,field_description");
|
sql.append("\n FROM product_sys_datamodel_field f");
|
sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
|
sql.append("\n),t2 AS (");
|
sql.append("\n SELECT table_name,column_name field_name,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
|
sql.append("\n FROM information_schema.`COLUMNS`");
|
sql.append("\n WHERE table_schema='product_db_v3.0.0'");
|
sql.append("\n)");
|
sql.append("\nSELECT t.uuid table_uuid,t2.*");
|
sql.append("\nFROM t1 ");
|
sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
|
sql.append("\nLEFT JOIN product_sys_datamodel_table t ON t2.table_name=t.table_name");
|
sql.append("\nWHERE t1.field_name IS NULL");
|
sql.append("\nAND t2.table_name NOT LIKE '%v'");
|
sql.append("\nAND t2.table_name NOT LIKE 'qrtz%'");
|
sql.append("\nORDER BY 1");
|
DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
|
for (int i = 0; i < dte.getRows(); i++) {
|
FieldSetEntity fse = dte.getFieldSetEntity(i);
|
FieldSetEntity saveFse = new FieldSetEntity();
|
saveFse.setTableName("product_sys_datamodel_field");
|
saveFse.setValue("table_uuid", fse.getString("table_uuid"));
|
saveFse.setValue("field_name", fse.getString("field_name"));
|
saveFse.setValue("field_type", getDictType(fse.getString("field_name"), fse.getString("type")));
|
saveFse.setValue("field_show_name", StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
|
saveFse.setValue("field_description", StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
|
saveFse.setValue("field_length", StringUtils.isEmpty(fse.getString("field_length")) ? 0 : fse.getString("field_length"));
|
saveFse.setValue("field_unit", StringUtils.isEmpty(fse.getString("field_unit")) ? 0 : fse.getString("field_unit"));
|
saveFse.setValue("is_required", 0);
|
saveFse.setValue("is_unique", 0);
|
saveFse.setValue("is_filter", 0);
|
saveFse.setValue("field_sequence", 0);
|
System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
|
baseDao.add(saveFse);
|
}
|
}
|
|
/**
|
* 根据数据库存储的类型获取字段缓存表的存储类型
|
* @param dbType
|
* @return
|
*/
|
private String getDictType(String fieldName, String dbType) {
|
fieldName = fieldName.trim();
|
List<FieldType> baseList = Arrays.asList(FieldType.values());
|
List<FieldType> aimList = baseList.stream().filter(o -> o.getDatabaseType().equals(dbType)).collect(Collectors.toList());
|
String result = "";
|
if (aimList.size() > 1) {
|
if ("varchar".equals(dbType)) {
|
if (Arrays.asList(
|
"district_sqe","asset_code","material_name","material_sn","supplier_uuid","uuid","version","material_uuid","use_dept",
|
"remark","name","standard_value","standard_value_1","inspect_item","inspect_remark","device_information","device_sn",
|
"fault_type","repair_applicant","origin_plan_uuid","maintenance_content","maintenance_item","id_number","id_type",
|
"sub_device_uuid","unit_uuid","high_threshold","department_uuid","manager_opinion","staff_uuid","personnel_opinion",
|
"must_read_num","file_org_level_uuid","light_mode_icon","dark_mode_icon"
|
).contains(fieldName)) {
|
result = "string";
|
} else if("org_level_uuid".equals(fieldName)) {
|
result = "orgUuid";
|
} else if (fieldName.endsWith("img") || fieldName.endsWith("photo") || fieldName.endsWith("attachment")) {
|
result = "file";
|
} else if (Arrays.asList("parent_uuid","parent_key").contains(fieldName)) {
|
result = "parentuuid";
|
} else if ("user_id".equals(fieldName)) {
|
result = "userid";
|
}
|
}
|
if ("bigint".equals(dbType)) {
|
if ("created_by".equals(fieldName)) {
|
result = "userid";
|
} else if ("id".equals(fieldName)) {
|
result = "pk";
|
} else {
|
result = "int";
|
}
|
}
|
if ("int".equals(dbType)) {
|
if (!"flow_flag".equals(fieldName)) {
|
result = "int";
|
}
|
}
|
System.out.println("result = " + result);;
|
} else if (aimList.size() == 1) {
|
result = aimList.get(0).getDictValue();
|
}
|
if (StringUtils.isEmpty(result)) {
|
throw new RuntimeException(String.format("test bug...fieldName:%s,dbType:%s", fieldName, dbType));
|
}
|
return result;
|
}
|
|
/**
|
* 将字段缓存表中多余的字段清理
|
*/
|
public void clearTableEx() {
|
StringBuilder sql = new StringBuilder(128);
|
sql.append("\nWITH t1 AS (");
|
sql.append("\n SELECT t.table_name,f.table_uuid,f.field_name,f.uuid field_uuid,field_type type,field_length,field_unit,field_description");
|
sql.append("\n FROM product_sys_datamodel_field f");
|
sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
|
sql.append("\n),t2 AS (");
|
sql.append("\n SELECT table_name,column_name field_name,null,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
|
sql.append("\n FROM information_schema.`COLUMNS`");
|
sql.append("\n WHERE table_schema='product_db_v3.0.0'");
|
sql.append("\n)");
|
sql.append("\nDELETE FROM product_sys_datamodel_field WHERE uuid IN (");
|
sql.append("\n SELECT t1.field_uuid");
|
sql.append("\n FROM t1 ");
|
sql.append("\n LEFT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
|
sql.append("\n WHERE t2.field_name IS NULL");
|
sql.append("\n AND t1.table_name NOT LIKE '%v'");
|
sql.append("\n AND t1.table_name NOT LIKE 'qrtz%'");
|
sql.append("\n)");
|
System.out.println("该sql无法通过baseDao执行，但是能够直接连接到数据库执行");
|
System.out.println("========================================================================");
|
System.out.println(sql);
|
System.out.println("========================================================================");
|
}
|
}
|
