# 信创SQL兼容工具SDK接口定义 --20231009

### 请求格式

- 接口名 webChecker/processSql
- 方法 `processSql(String jsonString)`

#### 访问地址

- Jar包

  http://ip:port/webChecker/processSql

- war包

  http://ip:8080/sqlcompchecker-0.0.3.2-SNAPSHOT/webChecker/processSql

### 请求参数及说明

`jsonString`是一个`List`结构的参数，`List`里面又是对象，对象的属性是下面三个：

| 字段名         | 字段类型       | 允许空 | 层级    | 集合 | 说明    |
|-------------|------------|------|-------|----|-------|
| sql | String     | 否 | 1     | 否  | sql片段 |
| targetDBType   | String  | 否 | 1     | 是  | 支持的数据库类型 |
| descriptionLength | Integer | 是 | 1 | 否 | 匹配到SQL的长度，为空则显示30，传值，例如传100，处理字符串，首先SQL的长度，大于100，取字符串前后各50个字符，中间用... 替换,小于100,就不处理 |
| allLength | Integer | 是 | 1 | 否 | 默认值为80，会将匹配到SQL长度前移和后移（allLength-descriptionLength）的长度 |



`targetDBType`可传参数见下表格为

| 参数                         | 含义                            |
| ---------------------------- | ------------------------------- |
| Oracle-to-LightDB-Oracle     | Oracle转LightDB的Oracle模式     |
| Oracle-to-LightDB-MySQL      | Oracle转LightDB的MySQL模式      |
| Oracle-to-LightDB-PostgreSQL | Oracle转LightDB的PostgreSQL模式 |
| Oracle-to-PostgreSQL         | Oracle转PostgreSQL              |
| Oracle-to-OBOracle           | Oracle转OceanBase的Oracle版     |
| Oracle-to-OBMySQL            | Oracle转OceanBase的MySQL版      |
| Oracle-to-TDMySQL8           | Oracle转TDSQL MySQL8版          |
| Oracle-to-TDMySQL57          | Oracle转TDSQL MySQL5.7版        |
| Oracle-to-DM8                | Oracle转达梦8数据库             |
| Oracle-to-openGauss          | Oracle转openGauss数据库         |
| MySQL-to-LightDB-MySQL       | MySQL转LightDB的MySQL模式       |
| MySQL-to-DM8                 | MySQL转DM8模式                  |
| MySQL-to-OBMySQL             | MySQL转OBMySQL模式              |
| MySQL-to-openGauss           | MySQL转openGauss数据库          |



### 入参示例

```json
[
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-PostgreSQL"],
        "descriptionLength": 10,
        "allLength": 12
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-OBOracle"],
        "descriptionLength": 60
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-OBMySQL"],
        "descriptionLength": 50
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-DM8"],
        "descriptionLength": 5
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-PostgreSQL"],
        "descriptionLength": 5
    
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-PostgreSQL"],
        "descriptionLength": 50
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-TDMySQL8"],
        "descriptionLength": 50
    },
    {
        "sql":"select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname;",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-TDMySQL57"],
        "descriptionLength": 50
    },
     {
        "sql":"select JSON_OBJECT('1');",
        "sourceDbType":"",
        "targetDbType":["MySQL-to-LightDB-MySQL"],
        "descriptionLength": 50
    },
    {
        "sql":"force index",
        "sourceDbType":"",
        "targetDbType":["MySQL-to-DM8"],
        "descriptionLength": 50
    },
     {
        "sql":"select JSON_OBJECT('1') sql_cache;",
        "sourceDbType":"",
        "targetDbType":["MySQL-to-OBMySQL"],
        "descriptionLength": 50
    },
     {
        "sql":"create table t(primary int)",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    },
     {
        "sql":"create table t(a int,primary key(a))",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    },
     {
        "sql":"delete from select * from a",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    }
    ,
     {
        "sql":"selete /* UNNEST */ * from t",
        "sourceDbType":"",
        "targetDbType":["Oracle-to-LightDB-MySQL"],
        "descriptionLength": 50
    }

]
```

### 返回示例

```json
[
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： ,xmlagg(x不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "pg暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： (xmlparse(c不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "pg不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： .getclobval(不兼容,用于检测的正则【\\bgetclobval\\b】",
                "advise": "推荐使用lt-Oracle",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-OBOracle]函数： ect sname,xmlagg(xmlparse(c不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "ObOracle暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-OBOracle",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-OBOracle]函数： by hobby).getclobval() from st不兼容,用于检测的正则【\\bgetclobval\\b】",
                "advise": "ObOracle暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-OBOracle",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-OBMySQL]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "ObMysql暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-OBMySQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": []
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： select sname,xm...g(xmlparse(content hobby ||','wellforme不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "pg暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： select sname,xmlagg(xm...e(content hobby ||','wellformed)order b不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "pg不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-PostgreSQL]函数： obby ||','wellformed)order by hobby).ge...al() from student group by sname不兼容,用于检测的正则【\\bgetclobval\\b】",
                "advise": "推荐使用lt-Oracle",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "LightDB_mysql 暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]函数： t sname,xmlagg(xmlparse(content hobby |不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "LightDB_mysql 暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "LightDB_mysql 暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]函数： t sname,xmlagg(xmlparse(content hobby |不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "LightDB_mysql 暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-PostgreSQL]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "LightDB_pg 暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-PostgreSQL",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-TDMySQL8]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "mysql8暂不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-TDMySQL8",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-TDMySQL8]函数： t sname,xmlagg(xmlparse(content hobby |不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "mysql8不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-TDMySQL8",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-TDMySQL57]函数： select sname,xmlagg(xmlparse(conten不兼容,用于检测的正则【\\bxmlagg\\s*\\(】",
                "advise": "尝试使用GROUP_CONCAT等函数替换",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-TDMySQL57",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            },
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[Oracle-to-TDMySQL57]函数： t sname,xmlagg(xmlparse(content hobby |不兼容,用于检测的正则【\\bxmlparse\\s*\\(】",
                "advise": "尝试使用ExtractValue等函数替换",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-TDMySQL57",
                "sourceSql": "select sname,xmlagg(xmlparse(content hobby ||','wellformed)order by hobby).getclobval() from student group by sname",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[函数校验]第1个语句 包含不兼容目标数据库[MySQL-to-LightDB-MySQL]函数： select JSON_OBJECT('1')不兼容,用于检测的正则【\\bJSON_OBJECT\\s*\\(】",
                "advise": "使用jsonb_build_object",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "MySQL-to-LightDB-MySQL",
                "sourceSql": "select JSON_OBJECT('1')",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[不支持的特性]第1个语句 包含不兼容目标数据库[MySQL-to-DM8]特性： force index不兼容,用于检测的正则【\\bforce\\s+index】",
                "advise": "不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "MySQL-to-DM8",
                "sourceSql": "force index",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[不支持的特性]第1个语句 包含不兼容目标数据库[MySQL-to-OBMySQL]特性： ON_OBJECT('1') sql_cache不兼容,用于检测的正则【\\b(sql_cache|sql_no_cache)\\b】",
                "advise": "不支持",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "MySQL-to-OBMySQL",
                "sourceSql": "select JSON_OBJECT('1') sql_cache",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[不支持的特性]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]特性： create table t(primary int)不兼容,用于检测的正则【create\\s+table\\b[\\s\\S]+(\\(|,)\\s*\\bPRIMARY\\s+(?!\\s*key\\b)】",
                "advise": "PRIMARY是LightDB的关键字，不能作为列名，其余检测请忽略",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "create table t(primary int)",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": []
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[DDL]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]语法： delete from select * from a不兼容,用于检测的正则【delete\\s+from\\s+\\(?\\s*\\bselect\\b】",
                "advise": "建议改成delete from table ",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "delete from select * from a",
                "level": "intercept"
            }
        ]
    },
    {
        "success": true,
        "errorMessage": "OK",
        "issues": [
            {
                "description": "[不支持的优化器提示]第1个语句 包含不兼容目标数据库[Oracle-to-LightDB-MySQL]优化器提示(不影响逻辑正确性)： selete /* UNNEST */ * from t不兼容,用于检测的正则【/\\*+[\\s\\S]*\\bUNNEST\\b[\\s\\S]*\\*/】",
                "advise": "不兼容",
                "beginLine": null,
                "beginCol": null,
                "endLine": null,
                "endCol": null,
                "targetDb": "Oracle-to-LightDB-MySQL",
                "sourceSql": "selete /* UNNEST */ * from t",
                "level": "warning"
            }
        ]
    }
]
```

### 返回参数及说明



| 字段名          | 字段类型    | 允许空 | 层级  | 集合  | 说明      |
|--------------|---------|------|-----|-----|---------|
| success      | boolean | 否 | 2  | 否   | 是否处理成功  |
| errorMessage | String  | 否 | 2  | 否   | 错误信息    |
| issues       | String | 否 | 2  | 是   | 错误结果集 |
| description  | String  | 否 | 3  | 否   | 错误描述    |
| advise       | String  | 否 | 3  | 否   | 修复建议    |
| targetDb | String | 否 | 3 | 否 | 检测目标数据库 |
| beginLine    | Integer | 是 | 3  | 否   | 开始行号    |
| beginCol     | Integer | 是 | 3  | 否   | 开始列     |
| endLine      | Integer | 是 | 3  | 否   | 结束行号    |
| endCol       | Integer | 是 | 3  | 否   | 结束列     |
| level | String | 否 | 3 | 否 | 阻断级别 |



关于阻断级别的字典

```java
    INTERCEPT ("阻断", "intercept"),
    WARNING("警告","warning"),
```

