go.temporal.io/server@v1.23.0/common/persistence/visibility/store/query/converter.go (about) 1 // The MIT License 2 // 3 // Copyright (c) 2020 Temporal Technologies Inc. All rights reserved. 4 // 5 // Copyright (c) 2020 Uber Technologies, Inc. 6 // 7 // Copyright (c) 2017 Xargin 8 // 9 // Permission is hereby granted, free of charge, to any person obtaining a copy 10 // of this software and associated documentation files (the "Software"), to deal 11 // in the Software without restriction, including without limitation the rights 12 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 13 // copies of the Software, and to permit persons to whom the Software is 14 // furnished to do so, subject to the following conditions: 15 // 16 // The above copyright notice and this permission notice shall be included in 17 // all copies or substantial portions of the Software. 18 // 19 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 20 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 21 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 22 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 23 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 24 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN 25 // THE SOFTWARE. 26 27 // Package query is inspired and partially copied from by github.com/cch123/elasticsql. 28 package query 29 30 import ( 31 "errors" 32 "fmt" 33 "strconv" 34 "strings" 35 36 "github.com/olivere/elastic/v7" 37 "github.com/temporalio/sqlparser" 38 ) 39 40 type ( 41 ExprConverter interface { 42 Convert(expr sqlparser.Expr) (elastic.Query, error) 43 } 44 45 Converter struct { 46 fnInterceptor FieldNameInterceptor 47 whereConverter ExprConverter 48 } 49 50 WhereConverter struct { 51 And ExprConverter 52 Or ExprConverter 53 RangeCond ExprConverter 54 ComparisonExpr ExprConverter 55 Is ExprConverter 56 } 57 58 andConverter struct { 59 where ExprConverter 60 } 61 62 orConverter struct { 63 where ExprConverter 64 } 65 66 rangeCondConverter struct { 67 fnInterceptor FieldNameInterceptor 68 fvInterceptor FieldValuesInterceptor 69 notBetweenSupported bool 70 } 71 72 comparisonExprConverter struct { 73 fnInterceptor FieldNameInterceptor 74 fvInterceptor FieldValuesInterceptor 75 allowedOperators map[string]struct{} 76 } 77 78 isConverter struct { 79 fnInterceptor FieldNameInterceptor 80 } 81 82 notSupportedExprConverter struct{} 83 84 QueryParams struct { 85 Query elastic.Query 86 Sorter []elastic.Sorter 87 GroupBy []string 88 } 89 ) 90 91 func NewConverter(fnInterceptor FieldNameInterceptor, whereConverter ExprConverter) *Converter { 92 if fnInterceptor == nil { 93 fnInterceptor = &NopFieldNameInterceptor{} 94 } 95 return &Converter{ 96 fnInterceptor: fnInterceptor, 97 whereConverter: whereConverter, 98 } 99 } 100 101 func NewWhereConverter( 102 and ExprConverter, 103 or ExprConverter, 104 rangeCond ExprConverter, 105 comparisonExpr ExprConverter, 106 is ExprConverter) ExprConverter { 107 if and == nil { 108 and = ¬SupportedExprConverter{} 109 } 110 111 if or == nil { 112 or = ¬SupportedExprConverter{} 113 } 114 115 if rangeCond == nil { 116 rangeCond = ¬SupportedExprConverter{} 117 } 118 119 if comparisonExpr == nil { 120 comparisonExpr = ¬SupportedExprConverter{} 121 } 122 123 if is == nil { 124 is = ¬SupportedExprConverter{} 125 } 126 127 return &WhereConverter{ 128 And: and, 129 Or: or, 130 RangeCond: rangeCond, 131 ComparisonExpr: comparisonExpr, 132 Is: is, 133 } 134 } 135 136 func NewAndConverter(whereConverter ExprConverter) ExprConverter { 137 return &andConverter{ 138 where: whereConverter, 139 } 140 } 141 142 func NewOrConverter(whereConverter ExprConverter) ExprConverter { 143 return &orConverter{ 144 where: whereConverter, 145 } 146 } 147 148 func NewRangeCondConverter( 149 fnInterceptor FieldNameInterceptor, 150 fvInterceptor FieldValuesInterceptor, 151 notBetweenSupported bool, 152 ) ExprConverter { 153 if fnInterceptor == nil { 154 fnInterceptor = &NopFieldNameInterceptor{} 155 } 156 if fvInterceptor == nil { 157 fvInterceptor = &NopFieldValuesInterceptor{} 158 } 159 return &rangeCondConverter{ 160 fnInterceptor: fnInterceptor, 161 fvInterceptor: fvInterceptor, 162 notBetweenSupported: notBetweenSupported, 163 } 164 } 165 166 func NewComparisonExprConverter( 167 fnInterceptor FieldNameInterceptor, 168 fvInterceptor FieldValuesInterceptor, 169 allowedOperators map[string]struct{}, 170 ) ExprConverter { 171 if fnInterceptor == nil { 172 fnInterceptor = &NopFieldNameInterceptor{} 173 } 174 if fvInterceptor == nil { 175 fvInterceptor = &NopFieldValuesInterceptor{} 176 } 177 return &comparisonExprConverter{ 178 fnInterceptor: fnInterceptor, 179 fvInterceptor: fvInterceptor, 180 allowedOperators: allowedOperators, 181 } 182 } 183 184 func NewIsConverter(fnInterceptor FieldNameInterceptor) ExprConverter { 185 return &isConverter{ 186 fnInterceptor: fnInterceptor, 187 } 188 } 189 190 func NewNotSupportedExprConverter() ExprConverter { 191 return ¬SupportedExprConverter{} 192 } 193 194 // ConvertWhereOrderBy transforms WHERE SQL statement to Elasticsearch query. 195 // It also supports ORDER BY clause. 196 func (c *Converter) ConvertWhereOrderBy(whereOrderBy string) (*QueryParams, error) { 197 whereOrderBy = strings.TrimSpace(whereOrderBy) 198 199 if whereOrderBy != "" && 200 !strings.HasPrefix(strings.ToLower(whereOrderBy), "order by ") && 201 !strings.HasPrefix(strings.ToLower(whereOrderBy), "group by ") { 202 whereOrderBy = "where " + whereOrderBy 203 } 204 // sqlparser can't parse just WHERE clause but instead accepts only valid SQL statement. 205 sql := fmt.Sprintf("select * from table1 %s", whereOrderBy) 206 return c.ConvertSql(sql) 207 } 208 209 // ConvertSql transforms SQL to Elasticsearch query. 210 func (c *Converter) ConvertSql(sql string) (*QueryParams, error) { 211 stmt, err := sqlparser.Parse(sql) 212 if err != nil { 213 return nil, NewConverterError("%s: %v", MalformedSqlQueryErrMessage, err) 214 } 215 216 selectStmt, isSelect := stmt.(*sqlparser.Select) 217 if !isSelect { 218 return nil, NewConverterError("%s: statement must be 'select' not %T", NotSupportedErrMessage, stmt) 219 } 220 221 return c.convertSelect(selectStmt) 222 } 223 224 func (c *Converter) convertSelect(sel *sqlparser.Select) (*QueryParams, error) { 225 if sel.Limit != nil { 226 return nil, NewConverterError("%s: 'limit' clause", NotSupportedErrMessage) 227 } 228 229 queryParams := &QueryParams{} 230 if sel.Where != nil { 231 query, err := c.whereConverter.Convert(sel.Where.Expr) 232 if err != nil { 233 return nil, wrapConverterError("unable to convert filter expression", err) 234 } 235 // Result must be BoolQuery. 236 if _, isBoolQuery := query.(*elastic.BoolQuery); !isBoolQuery { 237 query = elastic.NewBoolQuery().Filter(query) 238 } 239 queryParams.Query = query 240 } 241 242 if len(sel.GroupBy) > 1 { 243 return nil, NewConverterError("%s: 'group by' clause supports only a single field", NotSupportedErrMessage) 244 } 245 for _, groupByExpr := range sel.GroupBy { 246 colName, err := convertColName(c.fnInterceptor, groupByExpr, FieldNameGroupBy) 247 if err != nil { 248 return nil, wrapConverterError("unable to convert 'group by' column name", err) 249 } 250 queryParams.GroupBy = append(queryParams.GroupBy, colName) 251 } 252 253 for _, orderByExpr := range sel.OrderBy { 254 colName, err := convertColName(c.fnInterceptor, orderByExpr.Expr, FieldNameSorter) 255 if err != nil { 256 return nil, wrapConverterError("unable to convert 'order by' column name", err) 257 } 258 fieldSort := elastic.NewFieldSort(colName) 259 if orderByExpr.Direction == sqlparser.DescScr { 260 fieldSort = fieldSort.Desc() 261 } 262 queryParams.Sorter = append(queryParams.Sorter, fieldSort) 263 } 264 265 if len(queryParams.GroupBy) > 0 && len(queryParams.Sorter) > 0 { 266 return nil, NewConverterError( 267 "%s: 'order by' clause is not supported with 'group by' clause", 268 NotSupportedErrMessage, 269 ) 270 } 271 272 return queryParams, nil 273 } 274 275 func (w *WhereConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 276 if expr == nil { 277 return nil, errors.New("cannot be nil") 278 } 279 280 switch e := (expr).(type) { 281 case *sqlparser.AndExpr: 282 return w.And.Convert(e) 283 case *sqlparser.OrExpr: 284 return w.Or.Convert(e) 285 case *sqlparser.ComparisonExpr: 286 return w.ComparisonExpr.Convert(e) 287 case *sqlparser.RangeCond: 288 return w.RangeCond.Convert(e) 289 case *sqlparser.ParenExpr: 290 return w.Convert(e.Expr) 291 case *sqlparser.IsExpr: 292 return w.Is.Convert(e) 293 case *sqlparser.NotExpr: 294 return nil, NewConverterError("%s: 'not' expression", NotSupportedErrMessage) 295 case *sqlparser.FuncExpr: 296 return nil, NewConverterError("%s: function expression", NotSupportedErrMessage) 297 case *sqlparser.ColName: 298 return nil, NewConverterError("incomplete expression") 299 default: 300 return nil, NewConverterError("%s: expression of type %T", NotSupportedErrMessage, expr) 301 } 302 } 303 304 func (a *andConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 305 andExpr, ok := expr.(*sqlparser.AndExpr) 306 if !ok { 307 return nil, NewConverterError("%v is not an 'and' expression", sqlparser.String(expr)) 308 } 309 310 leftExpr := andExpr.Left 311 rightExpr := andExpr.Right 312 leftQuery, err := a.where.Convert(leftExpr) 313 if err != nil { 314 return nil, err 315 } 316 rightQuery, err := a.where.Convert(rightExpr) 317 if err != nil { 318 return nil, err 319 } 320 321 // If left or right is a BoolQuery built from AndExpr then reuse it w/o creating new BoolQuery. 322 lqBool, isLQBool := leftQuery.(*elastic.BoolQuery) 323 _, isLEAnd := leftExpr.(*sqlparser.AndExpr) 324 if isLQBool && isLEAnd { 325 return lqBool.Filter(rightQuery), nil 326 } 327 328 rqBool, isRQBool := rightQuery.(*elastic.BoolQuery) 329 _, isREAnd := rightExpr.(*sqlparser.AndExpr) 330 if isRQBool && isREAnd { 331 return rqBool.Filter(leftQuery), nil 332 } 333 334 return elastic.NewBoolQuery().Filter(leftQuery, rightQuery), nil 335 } 336 337 func (o *orConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 338 orExpr, ok := expr.(*sqlparser.OrExpr) 339 if !ok { 340 return nil, NewConverterError("%v is not an 'or' expression", sqlparser.String(expr)) 341 } 342 343 leftExpr := orExpr.Left 344 rightExpr := orExpr.Right 345 leftQuery, err := o.where.Convert(leftExpr) 346 if err != nil { 347 return nil, err 348 } 349 rightQuery, err := o.where.Convert(rightExpr) 350 if err != nil { 351 return nil, err 352 } 353 354 // If left or right is a BoolQuery built from OrExpr then reuse it w/o creating new BoolQuery. 355 lqBool, isLQBool := leftQuery.(*elastic.BoolQuery) 356 _, isLEOr := leftExpr.(*sqlparser.OrExpr) 357 if isLQBool && isLEOr { 358 return lqBool.Should(rightQuery), nil 359 } 360 361 rqBool, isRQBool := rightQuery.(*elastic.BoolQuery) 362 _, isREOr := rightExpr.(*sqlparser.OrExpr) 363 if isRQBool && isREOr { 364 return rqBool.Should(leftQuery), nil 365 } 366 367 return elastic.NewBoolQuery().Should(leftQuery, rightQuery), nil 368 } 369 370 func (r *rangeCondConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 371 rangeCond, ok := expr.(*sqlparser.RangeCond) 372 if !ok { 373 return nil, NewConverterError("%v is not a range condition", sqlparser.String(expr)) 374 } 375 376 colName, err := convertColName(r.fnInterceptor, rangeCond.Left, FieldNameFilter) 377 if err != nil { 378 return nil, wrapConverterError("unable to convert left part of 'between' expression", err) 379 } 380 381 fromValue, err := ParseSqlValue(sqlparser.String(rangeCond.From)) 382 if err != nil { 383 return nil, err 384 } 385 toValue, err := ParseSqlValue(sqlparser.String(rangeCond.To)) 386 if err != nil { 387 return nil, err 388 } 389 390 values, err := r.fvInterceptor.Values(colName, fromValue, toValue) 391 if err != nil { 392 return nil, wrapConverterError("unable to convert values of 'between' expression", err) 393 } 394 fromValue = values[0] 395 toValue = values[1] 396 397 var query elastic.Query 398 switch rangeCond.Operator { 399 case "between": 400 query = elastic.NewRangeQuery(colName).Gte(fromValue).Lte(toValue) 401 case "not between": 402 if !r.notBetweenSupported { 403 return nil, NewConverterError("%s: 'not between' expression", NotSupportedErrMessage) 404 } 405 query = elastic.NewBoolQuery().MustNot(elastic.NewRangeQuery(colName).Gte(fromValue).Lte(toValue)) 406 default: 407 return nil, NewConverterError("%s: range condition operator must be 'between' or 'not between'", InvalidExpressionErrMessage) 408 } 409 return query, nil 410 } 411 412 func (i *isConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 413 isExpr, ok := expr.(*sqlparser.IsExpr) 414 if !ok { 415 return nil, NewConverterError("%v is not an 'is' expression", sqlparser.String(expr)) 416 } 417 418 colName, err := convertColName(i.fnInterceptor, isExpr.Expr, FieldNameFilter) 419 if err != nil { 420 return nil, wrapConverterError("unable to convert left part of 'is' expression", err) 421 } 422 423 var query elastic.Query 424 switch isExpr.Operator { 425 case "is null": 426 query = elastic.NewBoolQuery().MustNot(elastic.NewExistsQuery(colName)) 427 case "is not null": 428 query = elastic.NewExistsQuery(colName) 429 default: 430 return nil, NewConverterError("%s: 'is' operator can be used with 'null' and 'not null' only", InvalidExpressionErrMessage) 431 } 432 433 return query, nil 434 } 435 436 func (c *comparisonExprConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 437 comparisonExpr, ok := expr.(*sqlparser.ComparisonExpr) 438 if !ok { 439 return nil, NewConverterError("%v is not a comparison expression", sqlparser.String(expr)) 440 } 441 442 colName, err := convertColName(c.fnInterceptor, comparisonExpr.Left, FieldNameFilter) 443 if err != nil { 444 return nil, wrapConverterError( 445 fmt.Sprintf("unable to convert left side of %q", sqlparser.String(expr)), 446 err, 447 ) 448 } 449 450 colValue, err := convertComparisonExprValue(comparisonExpr.Right) 451 if err != nil { 452 return nil, wrapConverterError( 453 fmt.Sprintf("unable to convert right side of %q", sqlparser.String(expr)), 454 err, 455 ) 456 } 457 458 if comparisonExpr.Operator == "like" || comparisonExpr.Operator == "not like" { 459 colValue, err = cleanLikeValue(colValue) 460 if err != nil { 461 return nil, err 462 } 463 } 464 465 colValues, isArray := colValue.([]interface{}) 466 // colValue should be an array only for "in (1,2,3)" queries. 467 if !isArray { 468 colValues = []interface{}{colValue} 469 } 470 471 colValues, err = c.fvInterceptor.Values(colName, colValues...) 472 if err != nil { 473 return nil, wrapConverterError("unable to convert values of comparison expression", err) 474 } 475 476 if _, ok := c.allowedOperators[comparisonExpr.Operator]; !ok { 477 return nil, NewConverterError("operator '%v' not allowed in comparison expression", comparisonExpr.Operator) 478 } 479 480 var query elastic.Query 481 switch comparisonExpr.Operator { 482 case sqlparser.GreaterEqualStr: 483 query = elastic.NewRangeQuery(colName).Gte(colValues[0]) 484 case sqlparser.LessEqualStr: 485 query = elastic.NewRangeQuery(colName).Lte(colValues[0]) 486 case sqlparser.GreaterThanStr: 487 query = elastic.NewRangeQuery(colName).Gt(colValues[0]) 488 case sqlparser.LessThanStr: 489 query = elastic.NewRangeQuery(colName).Lt(colValues[0]) 490 case sqlparser.EqualStr, sqlparser.LikeStr: // The only difference is that "%" is removed for LIKE queries. 491 // Not elastic.NewTermQuery to support partial word match for String custom search attributes. 492 query = elastic.NewMatchQuery(colName, colValues[0]) 493 case sqlparser.NotEqualStr, sqlparser.NotLikeStr: 494 // Not elastic.NewTermQuery to support partial word match for String custom search attributes. 495 query = elastic.NewBoolQuery().MustNot(elastic.NewMatchQuery(colName, colValues[0])) 496 case sqlparser.InStr: 497 query = elastic.NewTermsQuery(colName, colValues...) 498 case sqlparser.NotInStr: 499 query = elastic.NewBoolQuery().MustNot(elastic.NewTermsQuery(colName, colValues...)) 500 case sqlparser.StartsWithStr: 501 v, ok := colValues[0].(string) 502 if !ok { 503 return nil, NewConverterError("right-hand side of '%v' must be a string", comparisonExpr.Operator) 504 } 505 query = elastic.NewPrefixQuery(colName, v) 506 case sqlparser.NotStartsWithStr: 507 v, ok := colValues[0].(string) 508 if !ok { 509 return nil, NewConverterError("right-hand side of '%v' must be a string", comparisonExpr.Operator) 510 } 511 query = elastic.NewBoolQuery().MustNot(elastic.NewPrefixQuery(colName, v)) 512 } 513 514 return query, nil 515 } 516 517 // convertComparisonExprValue returns a string, int64, float64, bool or 518 // a slice with each value of one of those types. 519 func convertComparisonExprValue(expr sqlparser.Expr) (interface{}, error) { 520 switch e := expr.(type) { 521 case *sqlparser.SQLVal: 522 v, err := ParseSqlValue(sqlparser.String(e)) 523 if err != nil { 524 return nil, err 525 } 526 return v, nil 527 case sqlparser.BoolVal: 528 return bool(e), nil 529 case sqlparser.ValTuple: 530 // This is "in (1,2,3)" case. 531 exprs := []sqlparser.Expr(e) 532 var result []interface{} 533 for _, expr := range exprs { 534 v, err := convertComparisonExprValue(expr) 535 if err != nil { 536 return nil, err 537 } 538 result = append(result, v) 539 } 540 return result, nil 541 case *sqlparser.GroupConcatExpr: 542 return nil, NewConverterError("%s: 'group_concat'", NotSupportedErrMessage) 543 case *sqlparser.FuncExpr: 544 return nil, NewConverterError("%s: nested func", NotSupportedErrMessage) 545 case *sqlparser.ColName: 546 return nil, NewConverterError( 547 "%s: column name on the right side of comparison expression (did you forget to quote %q?)", 548 NotSupportedErrMessage, 549 sqlparser.String(expr), 550 ) 551 default: 552 return nil, NewConverterError("%s: unexpected value type %T", InvalidExpressionErrMessage, expr) 553 } 554 } 555 556 func cleanLikeValue(colValue interface{}) (string, error) { 557 colValueStr, isString := colValue.(string) 558 if !isString { 559 return "", NewConverterError("%s: 'like' operator value must be a string but was %T", InvalidExpressionErrMessage, colValue) 560 } 561 return strings.ReplaceAll(colValueStr, "%", ""), nil 562 } 563 564 func (n *notSupportedExprConverter) Convert(expr sqlparser.Expr) (elastic.Query, error) { 565 return nil, NewConverterError("%s: expression of type %T", NotSupportedErrMessage, expr) 566 } 567 568 // ParseSqlValue returns a string, int64 or float64 if the parsing succeeds. 569 func ParseSqlValue(sqlValue string) (interface{}, error) { 570 if sqlValue == "" { 571 return "", nil 572 } 573 574 if sqlValue[0] == '\'' && sqlValue[len(sqlValue)-1] == '\'' { 575 strValue := strings.Trim(sqlValue, "'") 576 return strValue, nil 577 } 578 579 // Unquoted value must be a number. Try int64 first. 580 if intValue, err := strconv.ParseInt(sqlValue, 10, 64); err == nil { 581 return intValue, nil 582 } 583 584 // Then float64. 585 if floatValue, err := strconv.ParseFloat(sqlValue, 64); err == nil { 586 return floatValue, nil 587 } 588 589 return nil, NewConverterError("%s: unable to parse %s", InvalidExpressionErrMessage, sqlValue) 590 } 591 592 func convertColName(fnInterceptor FieldNameInterceptor, colNameExpr sqlparser.Expr, usage FieldNameUsage) (string, error) { 593 colName, isColName := colNameExpr.(*sqlparser.ColName) 594 if !isColName { 595 return "", NewConverterError("%s: must be a column name but was %T", InvalidExpressionErrMessage, colNameExpr) 596 } 597 598 colNameStr := sqlparser.String(colName) 599 colNameStr = strings.ReplaceAll(colNameStr, "`", "") 600 return fnInterceptor.Name(colNameStr, usage) 601 }