Examples with TableItem com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem used on opensource projects

Example 1 with TableItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem in project canal by alibaba.

the class ESSyncService method insert.

/**
 * 插入操作dml
 *
 * @param config es配置
 * @param dml dml数据
 */
private void insert(ESSyncConfig config, Dml dml) {
    List<Map<String, Object>> dataList = dml.getData();
    if (dataList == null || dataList.isEmpty()) {
        return;
    }
    SchemaItem schemaItem = config.getEsMapping().getSchemaItem();
    for (Map<String, Object> data : dataList) {
        if (data == null || data.isEmpty()) {
            continue;
        }
        if (schemaItem.getAliasTableItems().size() == 1 && schemaItem.isAllFieldsSimple()) {
            // ------单表 & 所有字段都为简单字段------
            singleTableSimpleFiledInsert(config, dml, data);
        } else {
            // ------是主表 查询sql来插入------
            if (schemaItem.getMainTable().getTableName().equalsIgnoreCase(dml.getTable())) {
                mainTableInsert(config, dml, data);
            }
            // 从表的操作
            for (TableItem tableItem : schemaItem.getAliasTableItems().values()) {
                if (tableItem.isMain()) {
                    continue;
                }
                if (!tableItem.getTableName().equals(dml.getTable())) {
                    continue;
                }
                // 关联条件出现在主表查询条件是否为简单字段
                boolean allFieldsSimple = true;
                for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                    if (fieldItem.isMethod() || fieldItem.isBinaryOp()) {
                        allFieldsSimple = false;
                        break;
                    }
                }
                // 所有查询字段均为简单字段
                if (allFieldsSimple) {
                    // 不是子查询
                    if (!tableItem.isSubQuery()) {
                        // ------关联表简单字段插入------
                        Map<String, Object> esFieldData = new LinkedHashMap<>();
                        for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                            Object value = esTemplate.getValFromData(config.getEsMapping(), data, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                            esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), value);
                        }
                        joinTableSimpleFieldOperation(config, dml, data, tableItem, esFieldData);
                    } else {
                        // ------关联子表简单字段插入------
                        subTableSimpleFieldOperation(config, dml, data, null, tableItem);
                    }
                } else {
                    // ------关联子表复杂字段插入 执行全sql更新es------
                    wholeSqlOperation(config, dml, data, null, tableItem);
                }
            }
        }
    }
}

Example 2 with TableItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem in project canal by alibaba.

the class ESSyncService method update.

/**
 * 更新操作dml
 *
 * @param config es配置
 * @param dml dml数据
 */
private void update(ESSyncConfig config, Dml dml) {
    List<Map<String, Object>> dataList = dml.getData();
    List<Map<String, Object>> oldList = dml.getOld();
    if (dataList == null || dataList.isEmpty() || oldList == null || oldList.isEmpty()) {
        return;
    }
    SchemaItem schemaItem = config.getEsMapping().getSchemaItem();
    int i = 0;
    for (Map<String, Object> data : dataList) {
        Map<String, Object> old = oldList.get(i);
        if (data == null || data.isEmpty() || old == null || old.isEmpty()) {
            continue;
        }
        if (schemaItem.getAliasTableItems().size() == 1 && schemaItem.isAllFieldsSimple()) {
            // ------单表 & 所有字段都为简单字段------
            singleTableSimpleFiledUpdate(config, dml, data, old);
        } else {
            // ------主表 查询sql来更新------
            if (schemaItem.getMainTable().getTableName().equalsIgnoreCase(dml.getTable())) {
                ESMapping mapping = config.getEsMapping();
                String idFieldName = mapping.get_id() == null ? mapping.getPk() : mapping.get_id();
                FieldItem idFieldItem = schemaItem.getSelectFields().get(idFieldName);
                boolean idFieldSimple = true;
                if (idFieldItem.isMethod() || idFieldItem.isBinaryOp()) {
                    idFieldSimple = false;
                }
                boolean allUpdateFieldSimple = true;
                out: for (FieldItem fieldItem : schemaItem.getSelectFields().values()) {
                    for (ColumnItem columnItem : fieldItem.getColumnItems()) {
                        if (old.containsKey(columnItem.getColumnName())) {
                            if (fieldItem.isMethod() || fieldItem.isBinaryOp()) {
                                allUpdateFieldSimple = false;
                                break out;
                            }
                        }
                    }
                }
                // 不支持主键更新!!
                // 判断是否有外键更新
                boolean fkChanged = false;
                for (TableItem tableItem : schemaItem.getAliasTableItems().values()) {
                    if (tableItem.isMain()) {
                        continue;
                    }
                    boolean changed = false;
                    for (List<FieldItem> fieldItems : tableItem.getRelationTableFields().values()) {
                        for (FieldItem fieldItem : fieldItems) {
                            if (old.containsKey(fieldItem.getColumn().getColumnName())) {
                                fkChanged = true;
                                changed = true;
                                break;
                            }
                        }
                    }
                    // 如果外键有修改,则更新所对应该表的所有查询条件数据
                    if (changed) {
                        for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                            fieldItem.getColumnItems().forEach(columnItem -> old.put(columnItem.getColumnName(), null));
                        }
                    }
                }
                // 判断主键和所更新的字段是否全为简单字段
                if (idFieldSimple && allUpdateFieldSimple && !fkChanged) {
                    singleTableSimpleFiledUpdate(config, dml, data, old);
                } else {
                    mainTableUpdate(config, dml, data, old);
                }
            }
            // 从表的操作
            for (TableItem tableItem : schemaItem.getAliasTableItems().values()) {
                if (tableItem.isMain()) {
                    continue;
                }
                if (!tableItem.getTableName().equals(dml.getTable())) {
                    continue;
                }
                // 关联条件出现在主表查询条件是否为简单字段
                boolean allFieldsSimple = true;
                for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                    if (fieldItem.isMethod() || fieldItem.isBinaryOp()) {
                        allFieldsSimple = false;
                        break;
                    }
                }
                // 所有查询字段均为简单字段
                if (allFieldsSimple) {
                    // 不是子查询
                    if (!tableItem.isSubQuery()) {
                        // ------关联表简单字段更新------
                        Map<String, Object> esFieldData = new LinkedHashMap<>();
                        for (FieldItem fieldItem : tableItem.getRelationSelectFieldItems()) {
                            if (old.containsKey(fieldItem.getColumn().getColumnName())) {
                                Object value = esTemplate.getValFromData(config.getEsMapping(), data, fieldItem.getFieldName(), fieldItem.getColumn().getColumnName());
                                esFieldData.put(Util.cleanColumn(fieldItem.getFieldName()), value);
                            }
                        }
                        joinTableSimpleFieldOperation(config, dml, data, tableItem, esFieldData);
                    } else {
                        // ------关联子表简单字段更新------
                        subTableSimpleFieldOperation(config, dml, data, old, tableItem);
                    }
                } else {
                    // ------关联子表复杂字段更新 执行全sql更新es------
                    wholeSqlOperation(config, dml, data, old, tableItem);
                }
            }
        }
        i++;
    }
}

Example 3 with TableItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem in project canal by alibaba.

the class SqlParseTest method parseTest.

@Test
public void parseTest() {
    String sql = "select a.id, CASE WHEN a.id <= 500 THEN '1' else '2' end as id2, " + "concat(a.name,'_test') as name, a.role_id, b.name as role_name, c.labels from user a " + "left join role b on a.role_id=b.id " + "left join (select user_id, group_concat(label,',') as labels from user_label " + "group by user_id) c on c.user_id=a.id";
    SchemaItem schemaItem = SqlParser.parse(sql);
    // 通过表名找 TableItem
    List<TableItem> tableItems = schemaItem.getTableItemAliases().get("user_label".toLowerCase());
    tableItems.forEach(tableItem -> Assert.assertEquals("c", tableItem.getAlias()));
    TableItem tableItem = tableItems.get(0);
    Assert.assertFalse(tableItem.isMain());
    Assert.assertTrue(tableItem.isSubQuery());
    // 通过字段名找 FieldItem
    List<FieldItem> fieldItems = schemaItem.getColumnFields().get(tableItem.getAlias() + ".labels".toLowerCase());
    fieldItems.forEach(fieldItem -> Assert.assertEquals("c.labels", fieldItem.getOwner() + "." + fieldItem.getFieldName()));
    // 获取当前表关联条件字段
    Map<FieldItem, List<FieldItem>> relationTableFields = tableItem.getRelationTableFields();
    relationTableFields.keySet().forEach(fieldItem -> Assert.assertEquals("user_id", fieldItem.getColumn().getColumnName()));
// 获取关联字段在select中的对应字段
// List<FieldItem> relationSelectFieldItem =
// tableItem.getRelationKeyFieldItems();
// relationSelectFieldItem.forEach(fieldItem -> Assert.assertEquals("c.labels",
// fieldItem.getOwner() + "." + fieldItem.getColumn().getColumnName()));
}

Example 4 with TableItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem in project canal by alibaba.

the class SqlParser method visitSelectTable.

/**
 * 解析表
 *
 * @param schemaItem 视图对象
 * @param sqlTableSource sqlTableSource
 * @param tableItems 表对象列表
 * @param tableItemTmp 表对象(临时)
 */
private static void visitSelectTable(SchemaItem schemaItem, SQLTableSource sqlTableSource, List<TableItem> tableItems, TableItem tableItemTmp) {
    if (sqlTableSource instanceof SQLExprTableSource) {
        SQLExprTableSource sqlExprTableSource = (SQLExprTableSource) sqlTableSource;
        TableItem tableItem;
        if (tableItemTmp != null) {
            tableItem = tableItemTmp;
        } else {
            tableItem = new TableItem(schemaItem);
        }
        tableItem.setSchema(sqlExprTableSource.getSchema());
        tableItem.setTableName(sqlExprTableSource.getTableName());
        if (tableItem.getAlias() == null) {
            tableItem.setAlias(sqlExprTableSource.getAlias());
        }
        if (tableItems.isEmpty()) {
            // 第一张表为主表
            tableItem.setMain(true);
        }
        tableItems.add(tableItem);
    } else if (sqlTableSource instanceof SQLJoinTableSource) {
        SQLJoinTableSource sqlJoinTableSource = (SQLJoinTableSource) sqlTableSource;
        SQLTableSource leftTableSource = sqlJoinTableSource.getLeft();
        visitSelectTable(schemaItem, leftTableSource, tableItems, null);
        SQLTableSource rightTableSource = sqlJoinTableSource.getRight();
        TableItem rightTableItem = new TableItem(schemaItem);
        // 解析on条件字段
        visitOnCondition(sqlJoinTableSource.getCondition(), rightTableItem);
        visitSelectTable(schemaItem, rightTableSource, tableItems, rightTableItem);
    } else if (sqlTableSource instanceof SQLSubqueryTableSource) {
        SQLSubqueryTableSource subQueryTableSource = (SQLSubqueryTableSource) sqlTableSource;
        MySqlSelectQueryBlock sqlSelectQuery = (MySqlSelectQueryBlock) subQueryTableSource.getSelect().getQuery();
        TableItem tableItem;
        if (tableItemTmp != null) {
            tableItem = tableItemTmp;
        } else {
            tableItem = new TableItem(schemaItem);
        }
        tableItem.setAlias(subQueryTableSource.getAlias());
        tableItem.setSubQuerySql(SQLUtils.toMySqlString(sqlSelectQuery));
        tableItem.setSubQuery(true);
        tableItem.setSubQueryFields(collectSelectQueryFields(sqlSelectQuery));
        visitSelectTable(schemaItem, sqlSelectQuery.getFrom(), tableItems, tableItem);
    }
}

Example 5 with TableItem

use of com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem in project canal by alibaba.

the class SqlParser method parse.

/**
 * 解析sql
 *
 * @param sql sql
 * @return 视图对象
 */
public static SchemaItem parse(String sql) {
    try {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
        MySqlSelectQueryBlock sqlSelectQueryBlock = (MySqlSelectQueryBlock) statement.getSelect().getQuery();
        SchemaItem schemaItem = new SchemaItem();
        schemaItem.setSql(SQLUtils.toMySqlString(sqlSelectQueryBlock));
        SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
        List<TableItem> tableItems = new ArrayList<>();
        SqlParser.visitSelectTable(schemaItem, sqlTableSource, tableItems, null);
        tableItems.forEach(tableItem -> schemaItem.getAliasTableItems().put(tableItem.getAlias(), tableItem));
        List<FieldItem> fieldItems = collectSelectQueryFields(sqlSelectQueryBlock);
        fieldItems.forEach(fieldItem -> schemaItem.getSelectFields().put(fieldItem.getFieldName(), fieldItem));
        schemaItem.init();
        if (schemaItem.getAliasTableItems().isEmpty() || schemaItem.getSelectFields().isEmpty()) {
            throw new ParserException("Parse sql error");
        }
        return schemaItem;
    } catch (Exception e) {
        throw new ParserException();
    }
}