5. 想在代碼中驗(yàn)證sql的正確性？

1. 簡(jiǎn)介

我們?cè)谄綍r(shí)的開(kāi)發(fā)中可能會(huì)遇到需要驗(yàn)證一下sql是否正確，也就是需要check一下sql。

判斷sql是否正確一般包含一下幾點(diǎn)：

1. sql中使用的列是否存在
2. sql語(yǔ)法是否正確
3. sql中使用到的操作符/函數(shù)是否存在，有沒(méi)有正確的使用

我們可以用以下的sql示例來(lái)探究一下使用calcite如何校驗(yàn)sql

select 
	u.sex,
    max(u.age)
from user u
         inner join role r on u.role_id = r.id
where r.id = 1
group by u.sex

2. Maven

<dependency>
    <groupId>org.apache.calcite</groupId>
    <artifactId>calcite-core</artifactId>
    <version>1.37.0</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

3. 驗(yàn)證

首先在calcite中驗(yàn)證sql的正確性是通過(guò)使用calcite中的SqlValidator類進(jìn)行校驗(yàn)的，但SqlValidator是一個(gè)接口，通常是通過(guò)SqlValidatorUtil.newValidator(...)方法進(jìn)行實(shí)例化的, 如下：

public static SqlValidatorWithHints newValidator(SqlOperatorTable opTab,
                                                 SqlValidatorCatalogReader catalogReader, 
                                                 RelDataTypeFactory typeFactory, 
                                                 SqlValidator.Config config) 
{
    return new SqlValidatorImpl(opTab, catalogReader, typeFactory, config);    
}

SqlOperatorTable：用來(lái)提供sql驗(yàn)證所需的操作符（SqlOperator）和函數(shù)（SqlFunction）例如：>, <, = 或max(),in()
SqlValidatorCatalogReader：用來(lái)提供驗(yàn)證所需的元數(shù)據(jù)信息例如： schema, table, column
RelDataTypeFactory：處理數(shù)據(jù)類型的工廠類，用來(lái)提供類型、java類型和集合類型的創(chuàng)建和轉(zhuǎn)化。針對(duì)不同的接口形式，calcite支持sql和java兩種實(shí)現(xiàn)（SqlTypeFactoryImpl和JavaTypeFactoryImpl），當(dāng)然這里用戶可以針對(duì)不同的情況自行擴(kuò)展
SqlValidator.Config：可以自定義一些配置，例如是否開(kāi)啟類型隱式轉(zhuǎn)換、是否開(kāi)啟 SQL 重寫(xiě)等等

3.1 創(chuàng)建SqlValidator

創(chuàng)建SqlValidator之前需要先實(shí)例化上述的四個(gè)入?yún)?duì)象，好在calcite提供了對(duì)應(yīng)屬性的默認(rèn)實(shí)現(xiàn)，使得我們能很方便的創(chuàng)建SqlValidator對(duì)象

SqlValidator validator = SqlValidatorUtil.newValidator(
    SqlStdOperatorTable.instance(),
    catalogReader, // catalog信息需要自己手動(dòng)創(chuàng)建
    new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT),
    SqlValidator.Config.DEFAULT);

這里除了SqlValidatorCatalogReader對(duì)象需要額外的自己創(chuàng)建，如果沒(méi)有特殊的需求，我們都可以使用calcite提供的默認(rèn)實(shí)現(xiàn)。

我們這里著重講一下SqlValidatorCatalogReader對(duì)象如何創(chuàng)建

首先SqlValidatorCatalogReader使用來(lái)提供驗(yàn)證所需的catalog信息的，那我們就需要提供一下catalog信息（因?yàn)閏alcite需要做元數(shù)據(jù)的驗(yàn)證，比如表，字段是否存在，不提供元數(shù)據(jù)calcite談何驗(yàn)證）

創(chuàng)建SqlValidatorCatalogReader有兩種方式：

通過(guò)數(shù)據(jù)源的方式，也就是我們知道執(zhí)行sql的server信息，把連接信息給calcite，讓calcite自己去獲取元信息并進(jìn)行驗(yàn)證，也就是這個(gè)時(shí)候需要去連接db才能進(jìn)行驗(yàn)證

@SneakyThrows
private static CalciteCatalogReader createCatalogReaderWithDataSource() {
    Connection connection = DriverManager.getConnection("jdbc:calcite:");
    CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
    SchemaPlus rootSchema = calciteConnection.getRootSchema();
    DataSource dataSource = JdbcSchema.dataSource(
        "jdbc:mysql://localhost:3306/test",
        "com.mysql.cj.jdbc.Driver",
        "root",
        "123456"
    );
    JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "my_mysql", dataSource, null, null);
    rootSchema.add("my_mysql", jdbcSchema);
    calciteConnection.setSchema("my_mysql");
    CalciteServerStatement statement = connection.createStatement().unwrap(CalciteServerStatement.class);
    CalcitePrepare.Context prepareContext = statement.createPrepareContext();
    SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);

    return new CalciteCatalogReader(
        prepareContext.getRootSchema(),
        prepareContext.getDefaultSchemaPath(),
        factory,
        calciteConnection.config());
}

手動(dòng)添加catalog信息，不需要連庫(kù)就能驗(yàn)證

private static CalciteCatalogReader createCatalogReaderWithMeta() {
    SchemaPlus rootSchema = Frameworks.createRootSchema(true);
    RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT;
    RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem);
    rootSchema.add("user", new AbstractTable() {
        @Override
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
            RelDataTypeFactory.Builder builder = typeFactory.builder();
            // 這種方式似乎更簡(jiǎn)單
            // builder.add("id", typeFactory.createSqlType(SqlTypeName.INTEGER));
            builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
            builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
            builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
            builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
            builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
            return builder.build();
        }
    });
    rootSchema.add("role", new AbstractTable() {
        @Override
        public RelDataType getRowType(RelDataTypeFactory typeFactory) {
            RelDataTypeFactory.Builder builder = typeFactory.builder();
            builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
            builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
            return builder.build();
        }
    });
    CalciteConnectionConfig connectionConfig = CalciteConnectionConfig.DEFAULT;

    return new CalciteCatalogReader(
        CalciteSchema.from(rootSchema),
        CalciteSchema.from(rootSchema).path(null),
        typeFactory,
        connectionConfig);
}

ok，至此創(chuàng)建SqlValidator所需的參數(shù)都已備齊，但是當(dāng)執(zhí)行驗(yàn)證方法的時(shí)候所需的參數(shù)并不是sql字符串而是SqlValidator.validate(SqlNode topNode), 那么SqlNode又要怎么創(chuàng)建 ?

3.2 解析Sql

SqlNode 顧名思義就是sql節(jié)點(diǎn)對(duì)象，直接通過(guò)SqlParser對(duì)象創(chuàng)建，如下

SqlParser.Config config = SqlParser.config()
                                     // 解析工廠
                                     .withParserFactory(SqlParserImpl.FACTORY)
                                     // 也可以直接設(shè)置為對(duì)應(yīng)數(shù)據(jù)庫(kù)的詞法分析器
                                     // .withLex(Lex.MYSQL) 
                                     // 不區(qū)分大小寫(xiě)
                                     .withCaseSensitive(false)
                                     // 引用符號(hào)為反引號(hào)
                                     .withQuoting(Quoting.BACK_TICK)
                                     // 未加引號(hào)的標(biāo)識(shí)符在解析時(shí)不做處理
                                     .withUnquotedCasing(Casing.UNCHANGED)
                                     // 加引號(hào)的標(biāo)識(shí)符在解析時(shí)不做處理
                                     .withQuotedCasing(Casing.UNCHANGED)
                                     // 使用默認(rèn)的語(yǔ)法規(guī)則
                                     .withConformance(SqlConformanceEnum.DEFAULT);
// sql解析器
final SqlParser parser = SqlParser.create(SQL, config);
// 將sql轉(zhuǎn)換為calcite的SqlNode
SqlNode sqlNode = parser.parseQuery();

3.3 執(zhí)行驗(yàn)證

通過(guò)上述的步驟我們已經(jīng)能創(chuàng)建SqlValidator對(duì)象并且能創(chuàng)建其驗(yàn)證時(shí)需要的SqlNode對(duì)象，其實(shí)很簡(jiǎn)單，只要驗(yàn)證時(shí)不報(bào)錯(cuò)，即sql是正確的

try{
 // 校驗(yàn) sql
 validator.validate(sqlNode);
 log.info("sql is valid");
}
catch (Exception e) {
 log.error("sql is invalid", e);
}

4. 完整驗(yàn)證代碼

4.1 通過(guò)SqlValidator進(jìn)行驗(yàn)證

package com.ldx.calcite;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.calcite.adapter.jdbc.JdbcSchema;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.config.CalciteConnectionConfig;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.jdbc.CalcitePrepare;
import org.apache.calcite.jdbc.CalciteSchema;
import org.apache.calcite.prepare.CalciteCatalogReader;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.server.CalciteServerStatement;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.fun.SqlStdOperatorTable;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.impl.SqlParserImpl;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.tools.Frameworks;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DriverManager;

@Slf4j
public class SqlValidatorTest {
    private static final String SQL = "select u.sex, max(u.age) from `user` u inner join role r on u.role_id = r.id where r.id = 1 group by u.sex";

    @Test
    @SneakyThrows
    public void given_sql_and_meta_then_validate_sql() {
        SqlParser.Config config = SqlParser.config()
                                           // 解析工廠
                                           .withParserFactory(SqlParserImpl.FACTORY)
                                           // 也可以直接設(shè)置為對(duì)應(yīng)數(shù)據(jù)庫(kù)的詞法分析器
                                           // .withLex(Lex.MYSQL)
                                           // 不區(qū)分大小寫(xiě)
                                           .withCaseSensitive(false)
                                           // 引用符號(hào)為反引號(hào)
                                           .withQuoting(Quoting.BACK_TICK)
                                           // 未加引號(hào)的標(biāo)識(shí)符在解析時(shí)不做處理
                                           .withUnquotedCasing(Casing.UNCHANGED)
                                           // 加引號(hào)的標(biāo)識(shí)符在解析時(shí)不做處理
                                           .withQuotedCasing(Casing.UNCHANGED)
                                           // 使用默認(rèn)的語(yǔ)法規(guī)則
                                           .withConformance(SqlConformanceEnum.DEFAULT);
        // sql解析器
        final SqlParser parser = SqlParser.create(SQL, config);
        // 將SQL轉(zhuǎn)換為Calcite的SqlNode
        SqlNode sqlNode = parser.parseQuery();
        // 創(chuàng)建 SqlValidator 來(lái)進(jìn)行校驗(yàn)
        SqlValidator validator = SqlValidatorUtil.newValidator(
                SqlStdOperatorTable.instance(),
                // 使用直接提供元信息的方式
                createCatalogReaderWithMeta(),
                // 使用提供數(shù)據(jù)源的方式
                //createCatalogReaderWithDataSource(),
                new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT),
                SqlValidator.Config.DEFAULT);
        try{
            // 校驗(yàn) sql
            validator.validate(sqlNode);
            log.info("sql is valid");
        }
        catch (Exception e) {
            log.error("sql is invalid", e);
        }
    }

    private static CalciteCatalogReader createCatalogReaderWithMeta() {
        SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT;
        RelDataTypeFactory typeFactory = new SqlTypeFactoryImpl(relDataTypeSystem);
        rootSchema.add("user", new AbstractTable() {
                    @Override
                    public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                        RelDataTypeFactory.Builder builder = typeFactory.builder();
                        // 這種方式似乎更簡(jiǎn)單
                        // builder.add("id", typeFactory.createSqlType(SqlTypeName.INTEGER));
                        builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                        builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                        builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                        builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                        builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                        return builder.build();
                    }
                });
        rootSchema.add("role", new AbstractTable() {
            @Override
            public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                RelDataTypeFactory.Builder builder = typeFactory.builder();
                builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                return builder.build();
            }
        });
        CalciteConnectionConfig connectionConfig = CalciteConnectionConfig.DEFAULT;

        return new CalciteCatalogReader(
                CalciteSchema.from(rootSchema),
                CalciteSchema.from(rootSchema).path(null),
                typeFactory,
                connectionConfig);
    }

    @SneakyThrows
    private static CalciteCatalogReader createCatalogReaderWithDataSource() {
        Connection connection = DriverManager.getConnection("jdbc:calcite:");
        CalciteConnection calciteConnection = connection.unwrap(CalciteConnection.class);
        SchemaPlus rootSchema = calciteConnection.getRootSchema();
        DataSource dataSource = JdbcSchema.dataSource(
                "jdbc:mysql://localhost:3306/test",
                "com.mysql.cj.jdbc.Driver",
                "root",
                "123456"
        );
        JdbcSchema jdbcSchema = JdbcSchema.create(rootSchema, "my_mysql", dataSource, null, null);
        rootSchema.add("my_mysql", jdbcSchema);
        calciteConnection.setSchema("my_mysql");
        CalciteServerStatement statement = connection.createStatement().unwrap(CalciteServerStatement.class);
        CalcitePrepare.Context prepareContext = statement.createPrepareContext();
        SqlTypeFactoryImpl factory = new SqlTypeFactoryImpl(RelDataTypeSystem.DEFAULT);

        return new CalciteCatalogReader(
                prepareContext.getRootSchema(),
                prepareContext.getDefaultSchemaPath(),
                factory,
                calciteConnection.config());
    }
}

4.2 使用Planner對(duì)象進(jìn)行驗(yàn)證

其實(shí)Planner.validate方法其底層使用的還是SqlValidator對(duì)象進(jìn)行驗(yàn)證

package com.ldx.calcite;

import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.calcite.avatica.util.Casing;
import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.schema.SchemaPlus;
import org.apache.calcite.schema.impl.AbstractTable;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.impl.SqlParserImpl;
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.tools.FrameworkConfig;
import org.apache.calcite.tools.Frameworks;
import org.apache.calcite.tools.Planner;
import org.apache.calcite.tools.ValidationException;
import org.junit.jupiter.api.Test;

@Slf4j
public class SqlValidatorWithPlannerTest {
    private static final String SQL = "select u.sex, max(u.age) from `user` u inner join role r on u.role_id = r.id where r.id = 1 group by u.sex";

    @Test
    @SneakyThrows
    public void given_sql_and_meta_then_validate_sql() {
        // 創(chuàng)建Calcite配置
        FrameworkConfig config = createFrameworkConfig();
        // 創(chuàng)建Planner
        Planner planner = Frameworks.getPlanner(config);
        // 解析SQL
        final SqlNode parse = planner.parse(SQL);

        try {
            // 獲取SqlValidator進(jìn)行校驗(yàn)
            planner.validate(parse);
            log.info("sql is valid");
        } catch (ValidationException e) {
            log.error("sql is invalid", e);
        }
    }

    private static FrameworkConfig createFrameworkConfig() {
        SchemaPlus rootSchema = Frameworks.createRootSchema(true);
        RelDataTypeSystem relDataTypeSystem = RelDataTypeSystem.DEFAULT;
        rootSchema.add("user", new AbstractTable() {
            @Override
            public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                RelDataTypeFactory.Builder builder = typeFactory.builder();
                builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                builder.add("age", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                builder.add("sex", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                builder.add("role_id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                return builder.build();
            }
        });
        rootSchema.add("role", new AbstractTable() {
            @Override
            public RelDataType getRowType(RelDataTypeFactory typeFactory) {
                RelDataTypeFactory.Builder builder = typeFactory.builder();
                builder.add("id", new BasicSqlType(relDataTypeSystem, SqlTypeName.INTEGER));
                builder.add("name", new BasicSqlType(relDataTypeSystem, SqlTypeName.VARCHAR));
                return builder.build();
            }
        });
        SqlParser.Config config = SqlParser.config()
                                           .withParserFactory(SqlParserImpl.FACTORY)
                                           .withQuoting(Quoting.BACK_TICK)
                                           .withCaseSensitive(false)
                                           .withUnquotedCasing(Casing.UNCHANGED)
                                           .withQuotedCasing(Casing.UNCHANGED)
                                           .withConformance(SqlConformanceEnum.DEFAULT);
        return Frameworks
                .newConfigBuilder()
                .defaultSchema(rootSchema)
                .parserConfig(config)
                .build();
    }
}

posted @ 2025-02-22 00:09 張鐵牛閱讀(537) 評(píng)論(0) 收藏舉報(bào)

刷新頁(yè)面返回頂部

張鐵牛