chenyanlann / apijsondemo_clickhouse Goto Github PK

关于"`"的问题

（使用JDBC驱动clickhouse4j时)

在ClickHouse中是可以使用"`"的

例如查询

SELECT `Comment`.*, `User`.`id`,`User`.`name` FROM `sys`.`Comment` AS `Comment`  
   INNER JOIN `sys`.`apijson_user` AS `User` ON `User`.`id` = `Comment`.`userId` WHERE  (  (  (match(`Comment`.`content`, 'a'))  )  AND  (  (  (match(`User`.`name`, 'a') OR match(`User`.`name`, 't'))  )  )  )  ORDER BY `Comment`.`date` DESC  LIMIT 10

ClickHouse可以正确返回结果，但是最后的请求结果却是：

 {

    "ok": true ,
    "code": 200 ,
    "msg": "success" ,
    "sql:generate|cache|execute|maxExecute": "1|0|1|200" ,
    "depth:count|max": "3|5" ,
    "time:start|duration|end": "1627816512477|605|1627816513082" 

}

看一下控制台输出的日志记录，发现AbstractParser提前结束了：

2021-08-01 07:15:13.082: AbstractParser.DEBUG: putQueryResult queryResultMap.containsKey(valuePath) >> queryResultMap.put(path, result);
2021-08-01 07:15:13.082:

AbstractParser.DEBUG: <<<<<<<<<<<<<<<<<<<<<<< close >>>>>>>>>>>>>>>>>>>>>>>

排查发现apijson.orm.AbstractSQLExecutor.execute中的代码：

} else if (!config.getSQLTable().equalsIgnoreCase(rsmd.getTableName(i))) {
    viceColumnStart = i;
}

在执行时rsmd.getTableName返回"‘Comment’",而和它比较的字符串却是"Comment"

由于在ClickHouse中可以不使用"`"

在getQuote()中去掉"`"可以返回正确结果

在SQLConfig和AbstractSQLConfig增加相应关键字和函数

正则表达式适配

用match函数代替REGEXP，用lower函数代替BINARY

测试

请求：

{
    "User[]": {
        "count": 3,
        "User": {
            "name~": "^[0-9]+$"
        }
    }
}

返回：

name:007,name:568599

请求：

"name~": "^[H-J]|n$"

返回：

name:TommyLemon,name:Happy~,name:Jan

json_contains适配

将json_contains替换为

has(JSONExtractArrayRaw(assumeNotNull(),()))

JSONHas('[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]', '99')=0，JSONHas无法处理JSONArray

JSONExtractArrayRaw将String解析为Array(String),assumeNotNull避免报错(Array(String) cannot be inside Nullable type)

JSONExtractArrayRaw('[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]')=
['99','{"id":"HK500","cost":75.99}','["hot","cold"]']

请求：

{
    "User[]": {
        "count": 3,
        "User": {
            "contactIdList<>": 38710
        }
    }
}

json_length适配

将json_length替换为JSONLength

可以在AbstractSQLConfig初始化时在SQL_FUNCTION_MAP中直接添加JSONLength，也可以留待用户自行添加

APIJSON连接ClickHouse的问题

ClickHouse不支持REGEXP关键字，ClickHouse默认区分大小写

请求：

{
    "[]": {
        "join": "&/User/id@",
        "Moment": {
            "@column": "id,userId,content"
        },
        "User": {
            "name~": [
                "a",
                "t"
            ],
            "@column": "id,name",
            "id@": "/Moment/userId"
        }
    }
}

生成SQL:

SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment　   INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE  (  (  (  (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't')  )  )  )  LIMIT 10

ClickHouse执行出错：

Syntax error: failed at position 229 ('REGEXP') (line 2, col 112):

SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment
INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10

Expected one of: DoubleColon, LIKE, GLOBAL NOT IN, AS, DIV, IS, UUID, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN

ClickHouse不支持json_length，json_contains等函数

"ClickHouse exception, code: 46, host: null, port: 0; Code: 46, e.displayText() = DB::Exception: Unknown function json_contains. Maybe you meant: ['mapContains']: While processing SELECT id, sex, name, tag, head, contactIdList, pictureList, date FROM sys.apijson_user WHERE isNotNull(contactIdList) AND json_contains(contactIdList, '38710') LIMIT 3 (version 21.7.3.14 (official build)) "

"ClickHouse exception, code: 46, host: null, port: 0; Code: 46, e.displayText() = DB::Exception: Unknown function json_length. Maybe you meant: ['JSONLength','CHAR_LENGTH']: While processing SELECT userId, pictureList FROM sys.Moment WHERE (userId = 82002) AND (json_length(pictureList) > 0) LIMIT 3 (version 21.7.3.14 (official build)) "

ClickHouse不支持JSON格式存储数据

前：

 "contactIdList":     [  82005,    
                         82001,    
                         38710  ]             

后：

 "contactIdList":  "[82005, 82001, 38710]"  

解释：

ClickHouse不支持Json格式的数据类型，建表时使用了字符串类型，但使用FORMAT JSON命令可以以JSON格式输出数据

注释：

受ClickHouse支持的输入格式可用于提交给INSERT语句、从文件表(File,URL,HDFS或者外部目录)执行SELECT语句，受支持的输出格式可用于格式化SELECT语句的返回结果，或者通过INSERT写入到文件表

ClickHouse不支持REGEXP关键字，ClickHouse默认区分大小写

原SQL语句

WHERE  (  (  (  (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't')  )  )  )  LIMIT 10

修改为：

WHERE  (   (match(`User`.`name`,'a') OR match(`User`.`name`,'t')  )  )  LIMIT 10

如果不区分大小写，可以修改为

 WHERE  (  (  (  (match(lower(`User`.`name`),lower('a')) OR match(lower(`User`.`name`),lower('t'))  )  )  )  LIMIT 10

目前的简单测试方式：

在DemoSQLconfig里重写getRegExpString函数：

//由于无法判断是否为ClickHouse数据库，所以直接忽略了其他数据库    
public String getRegExpString(String key, String value, boolean ignoreCase) {

      //实现getValue私有方法的逻辑
      String getValue="";

      if (this.isPrepared()) {
         this.getPreparedValueList().add(value);
         getValue = "?";
      } else {
         getValue = (String)this.getSQLValue(value);
      }

      return "match(" + (ignoreCase ? "lower(" : "") + this.getKey(key) + (ignoreCase ? ")" : "") + ", "   + (ignoreCase ? "lower(" : "") + getValue + (ignoreCase ? ")" : "") + ")";
   }

例子

修改前

请求：

{
    "[]": {
        "join": "&/User/id@",
        "Moment": {
            "@column": "id,userId,content"
        },
        "User": {
            "name~": [
                "a",
                "t"
            ],
            "@column": "id,name",
            "id@": "/Moment/userId"
        }
    }
}

生成SQL:

SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment　   INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE  (  (  (  (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't')  )  )  )  LIMIT 10

ClickHouse执行出错：

Syntax error: failed at position 229 ('REGEXP') (line 2, col 112):

SELECT Moment.id,Moment.userId,Moment.content, User.id,User.name FROM sys.Moment AS Moment
INNER JOIN sys.apijson_user AS User ON User.id = Moment.userId WHERE ( ( ( (User.name REGEXP BINARY 'a' OR User.name REGEXP BINARY 't') ) ) ) LIMIT 10

Expected one of: DoubleColon, LIKE, GLOBAL NOT IN, AS, DIV, IS, UUID, OR, QuestionMark, BETWEEN, NOT LIKE, MOD, AND, Comma, alias, IN, ILIKE, Dot, NOT ILIKE, NOT, token, NOT IN, GLOBAL IN

修改后

请求：

{
    "[]": {
        "join": "&/User/id@",
        "Moment": {
            "@column": "id,userId,content"
        },
        "User": {
            "name~": [
                "a",
                "t"
            ],
            "@column": "id,name",
            "id@": "/Moment/userId"
        }
    }
}

结果：

{ "[]": [ { "Moment": { "id": 12 , "userId": 70793 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 15 , "userId": 70793 , "content": "APIJSON is a JSON Transmission Structure Protocol…" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 32 , "userId": 82002 , "content": "tst" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 170 , "userId": 70793 , "content": "This is a Content...-73" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 371 , "userId": 82002 , "content": "This is a Content...-371" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 551 , "userId": 70793 , "content": "test" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 1508053762227 , "userId": 82003 , "content": "我也试试" }, "User": { "id": 82003 , "name": "Wechat" } }, { "Moment": { "id": 1508072491570 , "userId": 82002 , "content": "有点冷~" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 1514017444961 , "userId": 82002 , "content": "123479589679" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 1514858533480 , "userId": 82056 , "content": "I am the Iron Man" }, "User": { "id": 82056 , "name": "IronMan" } } ], "ok": true , "msg": "success" , "code": 200 }

请求（忽略大小写）：

{
    "[]": {
        "join": "&/User/id@",
        "Moment": {
            "@column": "id,userId,content"
        },
        "User": {
            "name*~": [
                "a",
                "t"
            ],
            "@column": "id,name",
            "id@": "/Moment/userId"
        }
    }
}

结果：

{ "[]": [ { "Moment": { "id": 12 , "userId": 70793 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 15 , "userId": 70793 , "content": "APIJSON is a JSON Transmission Structure Protocol…" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 32 , "userId": 82002 , "content": "tst" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 170 , "userId": 70793 , "content": "This is a Content...-73" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 235 , "userId": 38710 , "content": "APIJSON,let interfaces and documents go to hell !" }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 371 , "userId": 82002 , "content": "This is a Content...-371" }, "User": { "id": 82002 , "name": "Happy~" } }, { "Moment": { "id": 470 , "userId": 38710 , "content": "This is a Content...-470" }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 511 , "userId": 38710 }, "User": { "id": 38710 , "name": "TommyLemon" } }, { "Moment": { "id": 551 , "userId": 70793 , "content": "test" }, "User": { "id": 70793 , "name": "Strong" } }, { "Moment": { "id": 595 , "userId": 38710 }, "User": { "id": 38710 , "name": "TommyLemon" } } ], "ok": true , "code": 200 , "msg": "success" , "sql:generate|cache|execute|maxExecute": "11|7|4|200" , "depth:count|max": "3|5" , "time:start|duration|end": "1627478385147|115|1627478385262" }

[bug] PUT "key<>":[] 居然转成了 key = '[]' #276

原代码

AbstractObjectParser
    
else if (method == PUT && value instanceof JSONArray && (whereList == null || whereList.contains(key) == false))

修改为

else if (method == PUT 
         && value instanceof JSONArray 
         && (whereList == null || whereList.contains(key) == false) 
         && (StringUtil.isName(key) 
             || ((key.endsWith("+") || key.endsWith("-")) && StringUtil.isName(key.substring(0, key.length()-1)))))

注释："key+"和"key-"只用于PUT请求，判断的时候兼顾这种特殊情况

原代码

AbstractSQLConfig
    
if (isWhere || (StringUtil.isName(key) == false)) {
						tableWhere.put(key, value);
						if (whereList == null || whereList.contains(key) == false) {
							andList.add(key);
						}
					}

修改为

if (isWhere || (StringUtil.isName(key) == false)) {
						tableWhere.put(key, value);
						if (whereList == null || whereList.contains(key) == false) {
							andList.add(key);
						}
					}

注释：将"key<>"和"key<"等作为条件

测试

{
    "Moment": {
        "id": 15,
        "content": "test",
        "userId>": 82001
    },
    "tag": "Moment",
    "@explain": true
}

"sql": "UPDATE sys.Moment SET content = 'test' WHERE ( (id = 15) AND (userId > 82001) ) LIMIT 1"

{
    "Moment": {
        "id": 15,
        "content": "test",
        "praiseUserIdList<>": [
            82002
        ]
    },
    "tag": "Moment",
    "@explain": true
}

"sql": "UPDATE sys.Moment SET content = 'test' WHERE ( (id = 15) AND (praiseUserIdList is NOT null AND (json_contains(praiseUserIdList, '82002'))) ) LIMIT 1"

{
    "Moment": {
        "id": 15,
        "praiseUserIdList<>": [
            82002
        ],
        "praiseUserIdList+": [
            920
        ]
    },
    "tag": "Moment",
    "@explain": true
}

"sql": "UPDATE sys.Moment SET praiseUserIdList = '[82002,70793,38710,93793,82001,960]' WHERE ( (id = 15) AND (praiseUserIdList is NOT null AND (json_contains(praiseUserIdList, '82002'))) ) LIMIT 1"

APIAuto回归测试也能通过

说明

测试的时候通过重写verify()方法去掉了登录和鉴权

@Override
public void verifyLogin() throws Exception {
   //super.verifyLogin();
}

@Override
public boolean verifyAccess(SQLConfig config) throws Exception {
   return true;
}