go.temporal.io/server@v1.23.0/common/persistence/visibility/store/sql/query_converter_mysql.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  // Permission is hereby granted, free of charge, to any person obtaining a copy
     8  // of this software and associated documentation files (the "Software"), to deal
     9  // in the Software without restriction, including without limitation the rights
    10  // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    11  // copies of the Software, and to permit persons to whom the Software is
    12  // furnished to do so, subject to the following conditions:
    13  //
    14  // The above copyright notice and this permission notice shall be included in
    15  // all copies or substantial portions of the Software.
    16  //
    17  // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    18  // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    19  // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    20  // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    21  // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    22  // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
    23  // THE SOFTWARE.
    24  
    25  package sql
    26  
    27  import (
    28  	"encoding/json"
    29  	"fmt"
    30  	"strings"
    31  
    32  	"github.com/temporalio/sqlparser"
    33  	"go.temporal.io/server/common/namespace"
    34  	"go.temporal.io/server/common/persistence/sql/sqlplugin"
    35  	"go.temporal.io/server/common/persistence/visibility/store/query"
    36  	"go.temporal.io/server/common/searchattribute"
    37  )
    38  
    39  type (
    40  	castExpr struct {
    41  		sqlparser.Expr
    42  		Value sqlparser.Expr
    43  		Type  *sqlparser.ConvertType
    44  	}
    45  
    46  	memberOfExpr struct {
    47  		sqlparser.Expr
    48  		Value   sqlparser.Expr
    49  		JSONArr sqlparser.Expr
    50  	}
    51  
    52  	jsonOverlapsExpr struct {
    53  		sqlparser.Expr
    54  		JSONDoc1 sqlparser.Expr
    55  		JSONDoc2 sqlparser.Expr
    56  	}
    57  
    58  	mysqlQueryConverter struct{}
    59  )
    60  
    61  var (
    62  	convertTypeDatetime = &sqlparser.ConvertType{Type: "datetime"}
    63  	convertTypeJSON     = &sqlparser.ConvertType{Type: "json"}
    64  )
    65  
    66  var _ sqlparser.Expr = (*castExpr)(nil)
    67  var _ sqlparser.Expr = (*memberOfExpr)(nil)
    68  var _ sqlparser.Expr = (*jsonOverlapsExpr)(nil)
    69  
    70  var _ pluginQueryConverter = (*mysqlQueryConverter)(nil)
    71  
    72  func (node *castExpr) Format(buf *sqlparser.TrackedBuffer) {
    73  	buf.Myprintf("cast(%v as %v)", node.Value, node.Type)
    74  }
    75  
    76  func (node *memberOfExpr) Format(buf *sqlparser.TrackedBuffer) {
    77  	buf.Myprintf("%v member of (%v)", node.Value, node.JSONArr)
    78  }
    79  
    80  func (node *jsonOverlapsExpr) Format(buf *sqlparser.TrackedBuffer) {
    81  	buf.Myprintf("json_overlaps(%v, %v)", node.JSONDoc1, node.JSONDoc2)
    82  }
    83  
    84  func newMySQLQueryConverter(
    85  	namespaceName namespace.Name,
    86  	namespaceID namespace.ID,
    87  	saTypeMap searchattribute.NameTypeMap,
    88  	saMapper searchattribute.Mapper,
    89  	queryString string,
    90  ) *QueryConverter {
    91  	return newQueryConverterInternal(
    92  		&mysqlQueryConverter{},
    93  		namespaceName,
    94  		namespaceID,
    95  		saTypeMap,
    96  		saMapper,
    97  		queryString,
    98  	)
    99  }
   100  
   101  func (c *mysqlQueryConverter) getDatetimeFormat() string {
   102  	return "2006-01-02 15:04:05.999999"
   103  }
   104  
   105  func (c *mysqlQueryConverter) getCoalesceCloseTimeExpr() sqlparser.Expr {
   106  	return newFuncExpr(
   107  		coalesceFuncName,
   108  		closeTimeSaColName,
   109  		&castExpr{
   110  			Value: newUnsafeSQLString(maxDatetimeValue.Format(c.getDatetimeFormat())),
   111  			Type:  convertTypeDatetime,
   112  		},
   113  	)
   114  }
   115  
   116  func (c *mysqlQueryConverter) convertKeywordListComparisonExpr(
   117  	expr *sqlparser.ComparisonExpr,
   118  ) (sqlparser.Expr, error) {
   119  	if !isSupportedKeywordListOperator(expr.Operator) {
   120  		return nil, query.NewConverterError(
   121  			"%s: operator '%s' not supported for KeywordList type search attribute in `%s`",
   122  			query.InvalidExpressionErrMessage,
   123  			expr.Operator,
   124  			formatComparisonExprStringForError(*expr),
   125  		)
   126  	}
   127  
   128  	var negate bool
   129  	var newExpr sqlparser.Expr
   130  	switch expr.Operator {
   131  	case sqlparser.EqualStr, sqlparser.NotEqualStr:
   132  		newExpr = &memberOfExpr{
   133  			Value:   expr.Right,
   134  			JSONArr: expr.Left,
   135  		}
   136  		negate = expr.Operator == sqlparser.NotEqualStr
   137  	case sqlparser.InStr, sqlparser.NotInStr:
   138  		var err error
   139  		newExpr, err = c.convertToJsonOverlapsExpr(expr)
   140  		if err != nil {
   141  			return nil, err
   142  		}
   143  		negate = expr.Operator == sqlparser.NotInStr
   144  	default:
   145  		// this should never happen since isSupportedKeywordListOperator should already fail
   146  		return nil, query.NewConverterError(
   147  			"%s: operator '%s' not supported for KeywordList type search attribute in `%s`",
   148  			query.InvalidExpressionErrMessage,
   149  			expr.Operator,
   150  			formatComparisonExprStringForError(*expr),
   151  		)
   152  	}
   153  
   154  	if negate {
   155  		newExpr = &sqlparser.NotExpr{Expr: newExpr}
   156  	}
   157  	return newExpr, nil
   158  }
   159  
   160  func (c *mysqlQueryConverter) convertToJsonOverlapsExpr(
   161  	expr *sqlparser.ComparisonExpr,
   162  ) (*jsonOverlapsExpr, error) {
   163  	valTuple, isValTuple := expr.Right.(sqlparser.ValTuple)
   164  	if !isValTuple {
   165  		return nil, query.NewConverterError(
   166  			"%s: unexpected value type (expected tuple of strings, got %s)",
   167  			query.InvalidExpressionErrMessage,
   168  			sqlparser.String(expr.Right),
   169  		)
   170  	}
   171  	values, err := getUnsafeStringTupleValues(valTuple)
   172  	if err != nil {
   173  		return nil, err
   174  	}
   175  	jsonValue, err := json.Marshal(values)
   176  	if err != nil {
   177  		return nil, err
   178  	}
   179  	return &jsonOverlapsExpr{
   180  		JSONDoc1: expr.Left,
   181  		JSONDoc2: &castExpr{
   182  			Value: newUnsafeSQLString(string(jsonValue)),
   183  			Type:  convertTypeJSON,
   184  		},
   185  	}, nil
   186  }
   187  
   188  func (c *mysqlQueryConverter) convertTextComparisonExpr(
   189  	expr *sqlparser.ComparisonExpr,
   190  ) (sqlparser.Expr, error) {
   191  	if !isSupportedTextOperator(expr.Operator) {
   192  		return nil, query.NewConverterError(
   193  			"%s: operator '%s' not supported for Text type search attribute in `%s`",
   194  			query.InvalidExpressionErrMessage,
   195  			expr.Operator,
   196  			formatComparisonExprStringForError(*expr),
   197  		)
   198  	}
   199  	// build the following expression:
   200  	// `match ({expr.Left}) against ({expr.Right} in natural language mode)`
   201  	var newExpr sqlparser.Expr = &sqlparser.MatchExpr{
   202  		Columns: []sqlparser.SelectExpr{&sqlparser.AliasedExpr{Expr: expr.Left}},
   203  		Expr:    expr.Right,
   204  		Option:  sqlparser.NaturalLanguageModeStr,
   205  	}
   206  	if expr.Operator == sqlparser.NotEqualStr {
   207  		newExpr = &sqlparser.NotExpr{Expr: newExpr}
   208  	}
   209  	return newExpr, nil
   210  }
   211  
   212  func (c *mysqlQueryConverter) buildSelectStmt(
   213  	namespaceID namespace.ID,
   214  	queryString string,
   215  	pageSize int,
   216  	token *pageToken,
   217  ) (string, []any) {
   218  	var whereClauses []string
   219  	var queryArgs []any
   220  
   221  	whereClauses = append(
   222  		whereClauses,
   223  		fmt.Sprintf("%s = ?", searchattribute.GetSqlDbColName(searchattribute.NamespaceID)),
   224  	)
   225  	queryArgs = append(queryArgs, namespaceID.String())
   226  
   227  	if len(queryString) > 0 {
   228  		whereClauses = append(whereClauses, queryString)
   229  	}
   230  
   231  	if token != nil {
   232  		whereClauses = append(
   233  			whereClauses,
   234  			fmt.Sprintf(
   235  				"((%s = ? AND %s = ? AND %s > ?) OR (%s = ? AND %s < ?) OR %s < ?)",
   236  				sqlparser.String(c.getCoalesceCloseTimeExpr()),
   237  				searchattribute.GetSqlDbColName(searchattribute.StartTime),
   238  				searchattribute.GetSqlDbColName(searchattribute.RunID),
   239  				sqlparser.String(c.getCoalesceCloseTimeExpr()),
   240  				searchattribute.GetSqlDbColName(searchattribute.StartTime),
   241  				sqlparser.String(c.getCoalesceCloseTimeExpr()),
   242  			),
   243  		)
   244  		queryArgs = append(
   245  			queryArgs,
   246  			token.CloseTime,
   247  			token.StartTime,
   248  			token.RunID,
   249  			token.CloseTime,
   250  			token.StartTime,
   251  			token.CloseTime,
   252  		)
   253  	}
   254  
   255  	queryArgs = append(queryArgs, pageSize)
   256  
   257  	return fmt.Sprintf(
   258  		`SELECT %s
   259  		FROM executions_visibility ev
   260  		LEFT JOIN custom_search_attributes
   261  		USING (%s, %s)
   262  		WHERE %s
   263  		ORDER BY %s DESC, %s DESC, %s
   264  		LIMIT ?`,
   265  		strings.Join(addPrefix("ev.", sqlplugin.DbFields), ", "),
   266  		searchattribute.GetSqlDbColName(searchattribute.NamespaceID),
   267  		searchattribute.GetSqlDbColName(searchattribute.RunID),
   268  		strings.Join(whereClauses, " AND "),
   269  		sqlparser.String(c.getCoalesceCloseTimeExpr()),
   270  		searchattribute.GetSqlDbColName(searchattribute.StartTime),
   271  		searchattribute.GetSqlDbColName(searchattribute.RunID),
   272  	), queryArgs
   273  }
   274  
   275  func (c *mysqlQueryConverter) buildCountStmt(
   276  	namespaceID namespace.ID,
   277  	queryString string,
   278  	groupBy []string,
   279  ) (string, []any) {
   280  	var whereClauses []string
   281  	var queryArgs []any
   282  
   283  	whereClauses = append(
   284  		whereClauses,
   285  		fmt.Sprintf("(%s = ?)", searchattribute.GetSqlDbColName(searchattribute.NamespaceID)),
   286  	)
   287  	queryArgs = append(queryArgs, namespaceID.String())
   288  
   289  	if len(queryString) > 0 {
   290  		whereClauses = append(whereClauses, queryString)
   291  	}
   292  
   293  	groupByClause := ""
   294  	if len(groupBy) > 0 {
   295  		groupByClause = fmt.Sprintf("GROUP BY %s", strings.Join(groupBy, ", "))
   296  	}
   297  
   298  	return fmt.Sprintf(
   299  		`SELECT %s
   300  		FROM executions_visibility ev
   301  		LEFT JOIN custom_search_attributes
   302  		USING (%s, %s)
   303  		WHERE %s
   304  		%s`,
   305  		strings.Join(append(groupBy, "COUNT(*)"), ", "),
   306  		searchattribute.GetSqlDbColName(searchattribute.NamespaceID),
   307  		searchattribute.GetSqlDbColName(searchattribute.RunID),
   308  		strings.Join(whereClauses, " AND "),
   309  		groupByClause,
   310  	), queryArgs
   311  }