Skip to content

APIJSON正则表达式适配ClickHouse #1

@chenyanlann

Description

@chenyanlann

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" }

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions