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 }