Back to Repositories

Validating SQL Parser Schema Mapping in Canal's Elasticsearch Adapter

This test suite validates SQL parsing functionality in Canal’s Elasticsearch 6.x adapter, focusing on complex query parsing and schema mapping. It ensures accurate handling of SQL queries with joins, subqueries, and conditional expressions while maintaining proper schema relationships.

Test Coverage Overview

The test suite provides comprehensive coverage of SQL parsing capabilities in Canal’s ES adapter.

Key areas tested include:

Complex SQL query parsing with CASE statements
Multi-table JOIN operations
Subquery handling and aliasing
Schema relationship mapping
Column and table alias resolution

Implementation Analysis

The testing approach employs JUnit to validate the SqlParser component’s ability to process complex SQL structures. It implements systematic verification of parsed schema items, table relationships, and field mappings using assertion-based testing patterns. The implementation specifically focuses on validating the correct parsing of table aliases, subquery detection, and relationship field mapping.

Technical Details

Testing tools and components:

JUnit 4 testing framework
Canal ES Core SqlParser utility
SchemaItem configuration classes
TableItem and FieldItem mapping structures
Assertion-based validation methods

Best Practices Demonstrated

The test demonstrates several testing best practices including:

Comprehensive validation of complex SQL parsing scenarios
Structured assertion chains for thorough verification
Clear separation of test cases and assertions
Detailed verification of schema relationships
Robust alias and relationship mapping validation

alibaba/canal

client-adapter/es6x/src/test/java/com/alibaba/otter/canal/client/adapter/es6x/test/SqlParseTest.java

            
package com.alibaba.otter.canal.client.adapter.es6x.test;

import java.util.List;
import java.util.Map;

import org.junit.Assert;
import org.junit.Test;

import com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem;
import com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.FieldItem;
import com.alibaba.otter.canal.client.adapter.es.core.config.SchemaItem.TableItem;
import com.alibaba.otter.canal.client.adapter.es.core.config.SqlParser;

public class SqlParseTest {

    @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()));
    }
}