github.com/bingoohuang/gg@v0.0.0-20240325092523-45da7dee9335/pkg/elasticsql/elasticsql_test.go (about) 1 package elasticsql 2 3 import ( 4 "encoding/json" 5 "reflect" 6 "testing" 7 ) 8 9 var selectCaseMap = map[string]string{ 10 "process_id= 1": `{"query" : {"bool" : {"must" : [{"match" : {"process_id" : {"query" : "1"}}}]}}}`, 11 "(process_id= 1)": `{"query" : {"bool" : {"must" : [{"match" : {"process_id" : {"query" : "1"}}}]}}}`, 12 "((process_id= 1))": `{"query" : {"bool" : {"must" : [{"match" : {"process_id" : {"query" : "1"}}}]}}}`, 13 "(process_id = 1 and status=1)": `{"query" : {"bool" : {"must" : [{"match" : {"process_id" : {"query" : "1"}}},{"match" : {"status" : {"query" : "1"}}}]}}}`, 14 "process_id > 1": `{"query" : {"bool" : {"must" : [{"range" : {"process_id" : {"gt" : "1"}}}]}}}`, 15 "process_id < 1": `{"query" : {"bool" : {"must" : [{"range" : {"process_id" : {"lt" : "1"}}}]}}}`, 16 "process_id <= 1": `{"query" : {"bool" : {"must" : [{"range" : {"process_id" : {"to" : "1"}}}]}}}`, 17 "process_id >= '1'": `{"query" : {"bool" : {"must" : [{"range" : {"process_id" : {"from" : "1"}}}]}}}`, 18 "process_id != 1": `{"query" : {"bool" : {"must" : [{"bool" : {"must_not" : [{"match" : {"process_id" : {"query" : "1"}}}]}}]}}}`, 19 "process_id = 0 and status= 1 and channel = 4": `{"query" : {"bool" : {"must" : [{"match" : {"process_id" : {"query" : "0"}}},{"match" : {"status" : {"query" : "1"}}},{"match" : {"channel" : {"query" : "4"}}}]}}}`, 20 "create_time between '2015-01-01 00:00:00' and '2015-01-01 00:00:00'": `{"query" : {"bool" : {"must" : [{"range" : {"create_time" : {"from" : "2015-01-01 00:00:00", "to" : "2015-01-01 00:00:00"}}}]}}}`, 21 "process_id > 1 and status = 1": `{"query" : {"bool" : {"must" : [{"range" : {"process_id" : {"gt" : "1"}}},{"match" : {"status" : {"query" : "1"}}}]}}}`, 22 "create_time between '2015-01-01T00:00:00+0800' and '2017-01-01T00:00:00+0800' and process_id = 0 and status >= 1 and content = '三个男人' and phone = '15810324322'": `{"query" : {"bool" : {"must" : [{"range" : {"create_time" : {"from" : "2015-01-01T00:00:00+0800", "to" : "2017-01-01T00:00:00+0800"}}},{"match" : {"process_id" : {"query" : "0"}}},{"range" : {"status" : {"from" : "1"}}},{"match" : {"content" : {"query" : "三个男人"}}},{"match" : {"phone" : {"query" : "15810324322"}}}]}}}`, 23 "id > 1 or process_id = 0": `{"query" : {"bool" : {"should" : [{"range" : {"id" : {"gt" : "1"}}},{"match" : {"process_id" : {"query" : "0"}}}]}}}`, 24 "id > 1 and d = 1 or process_id = 0 and x = 2": `{"query" : {"bool" : {"should" : [{"bool" : {"must" : [{"range" : {"id" : {"gt" : "1"}}},{"match" : {"d" : {"query" : "1"}}}]}},{"bool" : {"must" : [{"match" : {"process_id" : {"query" : "0"}}},{"match" : {"x" : {"query" : "2"}}}]}}]}}}`, 25 "id > 1 order by id asc, order_id desc": `{"query" : {"bool" : {"must" : [{"range" : {"id" : {"gt" : "1"}}}]}},"sort" : [{"id": "asc"},{"order_id": "desc"}]}`, 26 "(id > 1 and d = 1)": `{"query" : {"bool" : {"must" : [{"range" : {"id" : {"gt" : "1"}}},{"match" : {"d" : {"query" : "1"}}}]}}}`, 27 "(id > 1 and d = 1) or (c=1)": `{"query" : {"bool" : {"should" : [{"bool" : {"must" : [{"range" : {"id" : {"gt" : "1"}}},{"match" : {"d" : {"query" : "1"}}}]}},{"match" : {"c" : {"query" : "1"}}}]}}}`, 28 "id > 1 or (process_id = 0)": `{"query" : {"bool" : {"should" : [{"range" : {"id" : {"gt" : "1"}}},{"match" : {"process_id" : {"query" : "0"}}}]}}}`, 29 "id in (1,2,3,4)": `{"query" : {"bool" : {"must" : [{"terms" : {"id" : [1, 2, 3, 4]}}]}}}`, 30 "id in ('232', '323') and content = 'aaaa'": `{"query" : {"bool" : {"must" : [{"terms" : {"id" : ["232", "323"]}},{"match" : {"content" : {"query" : "aaaa"}}}]}}}`, 31 "create_time between '2015-01-01 00:00:00' and '2014-02-02 00:00:00'": `{"query" : {"bool" : {"must" : [{"range" : {"create_time" : {"from" : "2015-01-01 00:00:00", "to" : "2014-02-02 00:00:00"}}}]}}}`, 32 "a like '%a%'": `{"query" : {"bool" : {"must" : [{"match" : {"a" : {"query" : "a"}}}]}}}`, 33 "`by` = 1": `{"query" : {"bool" : {"must" : [{"match" : {"by" : {"query" : "1"}}}]}}}`, 34 "id not like '%aaa%'": `{"query" : {"bool" : {"must" : [{"bool" : {"must_not" : {"match" : {"id" : {"query" : "aaa"}}}}}]}}}`, 35 "id not in (1,2,3)": `{"query" : {"bool" : {"must" : [{"bool" : {"must_not" : {"terms" : {"id" : [1, 2, 3]}}}}]}}}`, 36 "limit 10,10": `{"query" : {"bool" : {"must": [{"match_all" : {}}]}},"from" : 10,"size" : 10}`, 37 "limit 10": `{"query" : {"bool" : {"must": [{"match_all" : {}}]}},"size" : 10}`, 38 "id != missing": `{"query" : {"bool" : {"must" : [{"bool" : {"must_not" : [{"missing":{"field":"id"}}]}}]}}}`, 39 "id = missing": `{"query" : {"bool" : {"must" : [{"missing":{"field":"id"}}]}}}`, 40 "order by `order`.abc": `{"query" : {"bool" : {"must": [{"match_all" : {}}]}},"sort" : [{"order.abc": "asc"}]}`, 41 "multi_match(query='this is a test', fields=(title,title.origin))": `{"query" : {"multi_match" : {"query" : "this is a test", "fields" : ["title","title.origin"]}}}`, 42 "a= 1 and multi_match(query='this is a test', fields=(title,title.origin))": `{"query" : {"bool" : {"must" : [{"match" : {"a" : {"query" : "1"}}},{"multi_match" : {"query" : "this is a test", "fields" : ["title","title.origin"]}}]}}}`, 43 "a= 1 and multi_match(query='this is a test', fields=(title,title.origin), type=phrase)": `{"query" : {"bool" : {"must" : [{"match" : {"a" : {"query" : "1"}}},{"multi_match" : {"query" : "this is a test", "type" : "phrase", "fields" : ["title","title.origin"]}}]}}}`, 44 } 45 46 func TestSupported(t *testing.T) { 47 for k, v := range selectCaseMap { 48 var dslMap map[string]interface{} 49 err := json.Unmarshal([]byte(v), &dslMap) 50 if err != nil { 51 println(v) 52 t.Error("test case json unmarshal err!") 53 } 54 55 // test convert 56 dsl, err := Convert(k) 57 var dslConvertedMap map[string]interface{} 58 err = json.Unmarshal([]byte(dsl), &dslConvertedMap) 59 if err != nil { 60 t.Error("the generated dsl json unmarshal error!", k) 61 } 62 63 if !reflect.DeepEqual(dslMap, dslConvertedMap) { 64 t.Error("the generated dsl is not equal to expected", k) 65 } 66 } 67 } 68 69 var unsupportedCaseList = []string{ 70 "insert into a values(1,2)", 71 "update a set id = 1", 72 "delete from a where id=1", 73 "select * from ak where NOT(id=1)", 74 "select * from ak where 1 = 1", 75 "1=a", 76 "id is null", 77 " a= 1 and multi_match(zz=1, query='this is a test', fields=(title,title.origin), type=phrase)", 78 "zz(k=2)", 79 } 80 81 func TestUnsupported(t *testing.T) { 82 for _, v := range unsupportedCaseList { 83 if _, err := Convert(v); err == nil { 84 t.Error("can not be true, these cases are not supported!", v) 85 } 86 } 87 } 88 89 var badSQLList = []string{ 90 //"select aaac fr", 91 "delete", 92 "update x", 93 "insert ", 94 } 95 96 func TestBadSQL(t *testing.T) { 97 for _, v := range badSQLList { 98 dsl, err := Convert(v) 99 if err == nil { 100 t.Error("can not be true, these cases are not supported!", v, dsl) 101 } 102 } 103 }