1package exp 2 3import ( 4 "fmt" 5 6 "github.com/doug-martin/goqu/v9/internal/sb" 7) 8 9// Behaviors 10type ( 11 12 // Interface that an expression should implement if it can be aliased. 13 Aliaseable interface { 14 // Returns an AliasedExpression 15 // I("col").As("other_col") //"col" AS "other_col" 16 // I("col").As(I("other_col")) //"col" AS "other_col" 17 As(interface{}) AliasedExpression 18 } 19 20 // Interface that an expression should implement if it can be casted to another SQL type . 21 Castable interface { 22 // Casts an expression to the specified type 23 // I("a").Cast("numeric")//CAST("a" AS numeric) 24 Cast(val string) CastExpression 25 } 26 27 Inable interface { 28 // Creates a Boolean expression for IN clauses 29 // I("col").In([]string{"a", "b", "c"}) //("col" IN ('a', 'b', 'c')) 30 In(...interface{}) BooleanExpression 31 // Creates a Boolean expression for NOT IN clauses 32 // I("col").NotIn([]string{"a", "b", "c"}) //("col" NOT IN ('a', 'b', 'c')) 33 NotIn(...interface{}) BooleanExpression 34 } 35 36 Isable interface { 37 // Creates an Boolean expression IS clauses 38 // ds.Where(I("a").Is(nil)) //("a" IS NULL) 39 // ds.Where(I("a").Is(true)) //("a" IS TRUE) 40 // ds.Where(I("a").Is(false)) //("a" IS FALSE) 41 Is(interface{}) BooleanExpression 42 // Creates an Boolean expression IS NOT clauses 43 // ds.Where(I("a").IsNot(nil)) //("a" IS NOT NULL) 44 // ds.Where(I("a").IsNot(true)) //("a" IS NOT TRUE) 45 // ds.Where(I("a").IsNot(false)) //("a" IS NOT FALSE) 46 IsNot(interface{}) BooleanExpression 47 // Shortcut for Is(nil) 48 IsNull() BooleanExpression 49 // Shortcut for IsNot(nil) 50 IsNotNull() BooleanExpression 51 // Shortcut for Is(true) 52 IsTrue() BooleanExpression 53 // Shortcut for IsNot(true) 54 IsNotTrue() BooleanExpression 55 // Shortcut for Is(false) 56 IsFalse() BooleanExpression 57 // Shortcut for IsNot(false) 58 IsNotFalse() BooleanExpression 59 } 60 61 Likeable interface { 62 // Creates an Boolean expression for LIKE clauses 63 // ds.Where(I("a").Like("a%")) //("a" LIKE 'a%') 64 Like(interface{}) BooleanExpression 65 // Creates an Boolean expression for NOT LIKE clauses 66 // ds.Where(I("a").NotLike("a%")) //("a" NOT LIKE 'a%') 67 NotLike(interface{}) BooleanExpression 68 // Creates an Boolean expression for case insensitive LIKE clauses 69 // ds.Where(I("a").ILike("a%")) //("a" ILIKE 'a%') 70 ILike(interface{}) BooleanExpression 71 // Creates an Boolean expression for case insensitive NOT LIKE clauses 72 // ds.Where(I("a").NotILike("a%")) //("a" NOT ILIKE 'a%') 73 NotILike(interface{}) BooleanExpression 74 75 // Creates an Boolean expression for REGEXP LIKE clauses 76 // ds.Where(I("a").RegexpLike("a%")) //("a" ~ 'a%') 77 RegexpLike(interface{}) BooleanExpression 78 // Creates an Boolean expression for REGEXP NOT LIKE clauses 79 // ds.Where(I("a").RegexpNotLike("a%")) //("a" !~ 'a%') 80 RegexpNotLike(interface{}) BooleanExpression 81 // Creates an Boolean expression for case insensitive REGEXP ILIKE clauses 82 // ds.Where(I("a").RegexpILike("a%")) //("a" ~* 'a%') 83 RegexpILike(interface{}) BooleanExpression 84 // Creates an Boolean expression for case insensitive REGEXP NOT ILIKE clauses 85 // ds.Where(I("a").RegexpNotILike("a%")) //("a" !~* 'a%') 86 RegexpNotILike(interface{}) BooleanExpression 87 } 88 89 // Interface that an expression should implement if it can be compared with other values. 90 Comparable interface { 91 // Creates a Boolean expression comparing equality 92 // I("col").Eq(1) //("col" = 1) 93 Eq(interface{}) BooleanExpression 94 // Creates a Boolean expression comparing in-equality 95 // I("col").Neq(1) //("col" != 1) 96 Neq(interface{}) BooleanExpression 97 // Creates a Boolean expression for greater than comparisons 98 // I("col").Gt(1) //("col" > 1) 99 Gt(interface{}) BooleanExpression 100 // Creates a Boolean expression for greater than or equal to than comparisons 101 // I("col").Gte(1) //("col" >= 1) 102 Gte(interface{}) BooleanExpression 103 // Creates a Boolean expression for less than comparisons 104 // I("col").Lt(1) //("col" < 1) 105 Lt(interface{}) BooleanExpression 106 // Creates a Boolean expression for less than or equal to comparisons 107 // I("col").Lte(1) //("col" <= 1) 108 Lte(interface{}) BooleanExpression 109 } 110 111 // Interface that an expression should implement if it can be used in a DISTINCT epxression. 112 Distinctable interface { 113 // Creates a DISTINCT clause 114 // I("a").Distinct() //DISTINCT("a") 115 Distinct() SQLFunctionExpression 116 } 117 118 // Interface that an expression should implement if it can be ORDERED. 119 Orderable interface { 120 // Creates an Ordered Expression for sql ASC order 121 // ds.Order(I("a").Asc()) //ORDER BY "a" ASC 122 Asc() OrderedExpression 123 // Creates an Ordered Expression for sql DESC order 124 // ds.Order(I("a").Desc()) //ORDER BY "a" DESC 125 Desc() OrderedExpression 126 } 127 128 Rangeable interface { 129 // Creates a Range expression for between comparisons 130 // I("col").Between(RangeVal{Start:1, End:10}) //("col" BETWEEN 1 AND 10) 131 Between(RangeVal) RangeExpression 132 // Creates a Range expression for between comparisons 133 // I("col").NotBetween(RangeVal{Start:1, End:10}) //("col" NOT BETWEEN 1 AND 10) 134 NotBetween(RangeVal) RangeExpression 135 } 136 137 Updateable interface { 138 // Used internally by update sql 139 Set(interface{}) UpdateExpression 140 } 141 142 Bitwiseable interface { 143 // Creates a Bit Operation Expresion for sql ~ 144 // I("col").BitiInversion() // (~ "col") 145 BitwiseInversion() BitwiseExpression 146 // Creates a Bit Operation Expresion for sql | 147 // I("col").BitOr(1) // ("col" | 1) 148 BitwiseOr(interface{}) BitwiseExpression 149 // Creates a Bit Operation Expresion for sql & 150 // I("col").BitAnd(1) // ("col" & 1) 151 BitwiseAnd(interface{}) BitwiseExpression 152 // Creates a Bit Operation Expresion for sql ^ 153 // I("col").BitXor(1) // ("col" ^ 1) 154 BitwiseXor(interface{}) BitwiseExpression 155 // Creates a Bit Operation Expresion for sql << 156 // I("col").BitLeftShift(1) // ("col" << 1) 157 BitwiseLeftShift(interface{}) BitwiseExpression 158 // Creates a Bit Operation Expresion for sql >> 159 // I("col").BitRighttShift(1) // ("col" >> 1) 160 BitwiseRightShift(interface{}) BitwiseExpression 161 } 162) 163 164type ( 165 Vals []interface{} 166 // Parent of all expression types 167 Expression interface { 168 Clone() Expression 169 Expression() Expression 170 } 171 // An Expression that generates its own sql (e.g Dataset) 172 SQLExpression interface { 173 Expression 174 ToSQL() (string, []interface{}, error) 175 IsPrepared() bool 176 } 177 178 AppendableExpression interface { 179 Expression 180 AppendSQL(b sb.SQLBuilder) 181 // Returns the alias value as an identiier expression 182 GetAs() IdentifierExpression 183 184 // Returns true if this expression returns columns. 185 // Used to determine if a Select, Update, Insert, or Delete query returns columns 186 ReturnsColumns() bool 187 } 188 // Expression for Aliased expressions 189 // I("a").As("b") -> "a" AS "b" 190 // SUM("a").As(I("a_sum")) -> SUM("a") AS "a_sum" 191 AliasedExpression interface { 192 Expression 193 // Returns the Epxression being aliased 194 Aliased() Expression 195 // Returns the alias value as an identiier expression 196 GetAs() IdentifierExpression 197 198 // Returns a new IdentifierExpression with the specified schema 199 Schema(string) IdentifierExpression 200 // Returns a new IdentifierExpression with the specified table 201 Table(string) IdentifierExpression 202 // Returns a new IdentifierExpression with the specified column 203 Col(interface{}) IdentifierExpression 204 // Returns a new IdentifierExpression with the column set to * 205 // I("my_table").All() //"my_table".* 206 All() IdentifierExpression 207 } 208 209 BooleanOperation int 210 BooleanExpression interface { 211 Expression 212 // Returns the operator for the expression 213 Op() BooleanOperation 214 // The left hand side of the expression (e.g. I("a") 215 LHS() Expression 216 // The right hand side of the expression could be a primitive value, dataset, or expression 217 RHS() interface{} 218 } 219 220 BitwiseOperation int 221 BitwiseExpression interface { 222 Expression 223 Aliaseable 224 Comparable 225 Isable 226 Inable 227 Likeable 228 Rangeable 229 Orderable 230 Distinctable 231 // Returns the operator for the expression 232 Op() BitwiseOperation 233 // The left hand side of the expression (e.g. I("a") 234 LHS() Expression 235 // The right hand side of the expression could be a primitive value, dataset, or expression 236 RHS() interface{} 237 } 238 239 // An Expression that represents another Expression casted to a SQL type 240 CastExpression interface { 241 Expression 242 Aliaseable 243 Comparable 244 Inable 245 Isable 246 Likeable 247 Orderable 248 Distinctable 249 Rangeable 250 // The exression being casted 251 Casted() Expression 252 // The the SQL type to cast the expression to 253 Type() LiteralExpression 254 } 255 // A list of columns. Typically used internally by Select, Order, From 256 ColumnListExpression interface { 257 Expression 258 // Returns the list of columns 259 Columns() []Expression 260 // Returns true if the column list is empty 261 IsEmpty() bool 262 // Returns a new ColumnListExpression with the columns appended. 263 Append(...Expression) ColumnListExpression 264 } 265 CompoundType int 266 CompoundExpression interface { 267 Expression 268 Type() CompoundType 269 RHS() AppendableExpression 270 } 271 // An Expression that the ON CONFLICT/ON DUPLICATE KEY portion of an INSERT statement 272 ConflictAction int 273 ConflictExpression interface { 274 Expression 275 Action() ConflictAction 276 } 277 ConflictUpdateExpression interface { 278 ConflictExpression 279 TargetColumn() string 280 Where(expressions ...Expression) ConflictUpdateExpression 281 WhereClause() ExpressionList 282 Update() interface{} 283 } 284 CommonTableExpression interface { 285 Expression 286 IsRecursive() bool 287 // Returns the alias name for the extracted expression 288 Name() LiteralExpression 289 // Returns the Expression being extracted 290 SubQuery() Expression 291 } 292 ExpressionListType int 293 // A list of expressions that should be joined together 294 // And(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) AND ("b" = 11)) 295 // Or(I("a").Eq(10), I("b").Eq(11)) //(("a" = 10) OR ("b" = 11)) 296 ExpressionList interface { 297 Expression 298 // Returns type (e.g. OR, AND) 299 Type() ExpressionListType 300 // Slice of expressions that should be joined together 301 Expressions() []Expression 302 // Returns a new expression list with the given expressions appended to the current Expressions list 303 Append(...Expression) ExpressionList 304 305 IsEmpty() bool 306 } 307 // An Identifier that can contain schema, table and column identifiers 308 IdentifierExpression interface { 309 Expression 310 Aliaseable 311 Comparable 312 Inable 313 Isable 314 Likeable 315 Rangeable 316 Orderable 317 Updateable 318 Distinctable 319 Castable 320 Bitwiseable 321 // returns true if this identifier has more more than on part (Schema, Table or Col) 322 // "schema" -> true //cant qualify anymore 323 // "schema.table" -> true 324 // "table" -> false 325 // "schema"."table"."col" -> true 326 // "table"."col" -> true 327 // "col" -> false 328 IsQualified() bool 329 // Returns a new IdentifierExpression with the specified schema 330 Schema(string) IdentifierExpression 331 // Returns the current schema 332 GetSchema() string 333 // Returns a new IdentifierExpression with the specified table 334 Table(string) IdentifierExpression 335 // Returns the current table 336 GetTable() string 337 // Returns a new IdentifierExpression with the specified column 338 Col(interface{}) IdentifierExpression 339 // Returns the current column 340 GetCol() interface{} 341 // Returns a new IdentifierExpression with the column set to * 342 // I("my_table").All() //"my_table".* 343 All() IdentifierExpression 344 345 // Returns true if schema table and identifier are all zero values. 346 IsEmpty() bool 347 } 348 InsertExpression interface { 349 Expression 350 IsEmpty() bool 351 IsInsertFrom() bool 352 From() AppendableExpression 353 Cols() ColumnListExpression 354 SetCols(cols ColumnListExpression) InsertExpression 355 Vals() [][]interface{} 356 SetVals([][]interface{}) InsertExpression 357 } 358 359 JoinType int 360 JoinExpression interface { 361 Expression 362 JoinType() JoinType 363 IsConditioned() bool 364 Table() Expression 365 } 366 // Parent type for join expressions 367 ConditionedJoinExpression interface { 368 JoinExpression 369 Condition() JoinCondition 370 IsConditionEmpty() bool 371 } 372 LateralExpression interface { 373 Expression 374 Aliaseable 375 Table() AppendableExpression 376 } 377 378 // Expression for representing "literal" sql. 379 // L("col = 1") -> col = 1) 380 // L("? = ?", I("col"), 1) -> "col" = 1 381 LiteralExpression interface { 382 Expression 383 Aliaseable 384 Comparable 385 Isable 386 Inable 387 Likeable 388 Rangeable 389 Orderable 390 Bitwiseable 391 // Returns the literal sql 392 Literal() string 393 // Arguments to be replaced within the sql 394 Args() []interface{} 395 } 396 397 NullSortType int 398 SortDirection int 399 // An expression for specifying sort order and options 400 OrderedExpression interface { 401 Expression 402 // The expression being sorted 403 SortExpression() Expression 404 // Sort direction (e.g. ASC, DESC) 405 IsAsc() bool 406 // If the adapter supports it null sort type (e.g. NULLS FIRST, NULLS LAST) 407 NullSortType() NullSortType 408 // Returns a new OrderedExpression with NullSortType set to NULLS_FIRST 409 NullsFirst() OrderedExpression 410 // Returns a new OrderedExpression with NullSortType set to NULLS_LAST 411 NullsLast() OrderedExpression 412 } 413 414 RangeOperation int 415 RangeExpression interface { 416 Expression 417 // Returns the operator for the expression 418 Op() RangeOperation 419 // The left hand side of the expression (e.g. I("a") 420 LHS() Expression 421 // The right hand side of the expression could be a primitive value, dataset, or expression 422 RHS() RangeVal 423 } 424 RangeVal interface { 425 Start() interface{} 426 End() interface{} 427 } 428 429 Windowable interface { 430 Over(WindowExpression) SQLWindowFunctionExpression 431 OverName(IdentifierExpression) SQLWindowFunctionExpression 432 } 433 434 // Expression for representing a SQLFunction(e.g. COUNT, SUM, MIN, MAX...) 435 SQLFunctionExpression interface { 436 Expression 437 Aliaseable 438 Rangeable 439 Comparable 440 Orderable 441 Isable 442 Inable 443 Likeable 444 Windowable 445 // The function name 446 Name() string 447 // Arguments to be passed to the function 448 Args() []interface{} 449 } 450 451 UpdateExpression interface { 452 Col() IdentifierExpression 453 Val() interface{} 454 } 455 456 SQLWindowFunctionExpression interface { 457 Expression 458 Aliaseable 459 Rangeable 460 Comparable 461 Orderable 462 Isable 463 Inable 464 Likeable 465 Func() SQLFunctionExpression 466 467 Window() WindowExpression 468 WindowName() IdentifierExpression 469 470 HasWindow() bool 471 HasWindowName() bool 472 } 473 474 WindowExpression interface { 475 Expression 476 477 Name() IdentifierExpression 478 HasName() bool 479 480 Parent() IdentifierExpression 481 HasParent() bool 482 PartitionCols() ColumnListExpression 483 HasPartitionBy() bool 484 OrderCols() ColumnListExpression 485 HasOrder() bool 486 487 Inherit(parent string) WindowExpression 488 PartitionBy(cols ...interface{}) WindowExpression 489 OrderBy(cols ...interface{}) WindowExpression 490 } 491 CaseElse interface { 492 Result() interface{} 493 } 494 CaseWhen interface { 495 Condition() interface{} 496 Result() interface{} 497 } 498 CaseExpression interface { 499 Expression 500 Aliaseable 501 Orderable 502 GetValue() interface{} 503 GetWhens() []CaseWhen 504 GetElse() CaseElse 505 Value(val interface{}) CaseExpression 506 When(condition, result interface{}) CaseExpression 507 Else(result interface{}) CaseExpression 508 } 509) 510 511const ( 512 UnionCompoundType CompoundType = iota 513 UnionAllCompoundType 514 IntersectCompoundType 515 IntersectAllCompoundType 516 517 DoNothingConflictAction ConflictAction = iota 518 DoUpdateConflictAction 519 520 AndType ExpressionListType = iota 521 OrType 522 523 InnerJoinType JoinType = iota 524 FullOuterJoinType 525 RightOuterJoinType 526 LeftOuterJoinType 527 FullJoinType 528 RightJoinType 529 LeftJoinType 530 NaturalJoinType 531 NaturalLeftJoinType 532 NaturalRightJoinType 533 NaturalFullJoinType 534 CrossJoinType 535 536 UsingJoinCondType JoinConditionType = iota 537 OnJoinCondType 538 539 // Default null sort type with no null sort order 540 NoNullsSortType NullSortType = iota 541 // NULLS FIRST 542 NullsFirstSortType 543 // NULLS LAST 544 NullsLastSortType 545 // ASC 546 AscDir SortDirection = iota 547 // DESC 548 DescSortDir 549 550 // BETWEEN 551 BetweenOp RangeOperation = iota 552 // NOT BETWEEN 553 NotBetweenOp 554 555 // = 556 EqOp BooleanOperation = iota 557 // != or <> 558 NeqOp 559 // IS 560 IsOp 561 // IS NOT 562 IsNotOp 563 // > 564 GtOp 565 // >= 566 GteOp 567 // < 568 LtOp 569 // <= 570 LteOp 571 // IN 572 InOp 573 // NOT IN 574 NotInOp 575 // LIKE, LIKE BINARY... 576 LikeOp 577 // NOT LIKE, NOT LIKE BINARY... 578 NotLikeOp 579 // ILIKE, LIKE 580 ILikeOp 581 // NOT ILIKE, NOT LIKE 582 NotILikeOp 583 // ~, REGEXP BINARY 584 RegexpLikeOp 585 // !~, NOT REGEXP BINARY 586 RegexpNotLikeOp 587 // ~*, REGEXP 588 RegexpILikeOp 589 // !~*, NOT REGEXP 590 RegexpNotILikeOp 591 592 betweenStr = "between" 593 594 BitwiseInversionOp BitwiseOperation = iota 595 BitwiseOrOp 596 BitwiseAndOp 597 BitwiseXorOp 598 BitwiseLeftShiftOp 599 BitwiseRightShiftOp 600) 601 602var ( 603 ConditionedJoinTypes = map[JoinType]bool{ 604 InnerJoinType: true, 605 FullOuterJoinType: true, 606 RightOuterJoinType: true, 607 LeftOuterJoinType: true, 608 FullJoinType: true, 609 RightJoinType: true, 610 LeftJoinType: true, 611 } 612 // used internally for inverting operators 613 operatorInversions = map[BooleanOperation]BooleanOperation{ 614 IsOp: IsNotOp, 615 EqOp: NeqOp, 616 GtOp: LteOp, 617 GteOp: LtOp, 618 LtOp: GteOp, 619 LteOp: GtOp, 620 InOp: NotInOp, 621 LikeOp: NotLikeOp, 622 ILikeOp: NotILikeOp, 623 RegexpLikeOp: RegexpNotLikeOp, 624 RegexpILikeOp: RegexpNotILikeOp, 625 IsNotOp: IsOp, 626 NeqOp: EqOp, 627 NotInOp: InOp, 628 NotLikeOp: LikeOp, 629 NotILikeOp: ILikeOp, 630 RegexpNotLikeOp: RegexpLikeOp, 631 RegexpNotILikeOp: RegexpILikeOp, 632 } 633) 634 635func (bo BooleanOperation) String() string { 636 switch bo { 637 case EqOp: 638 return "eq" 639 case NeqOp: 640 return "neq" 641 case IsOp: 642 return "is" 643 case IsNotOp: 644 return "isnot" 645 case GtOp: 646 return "gt" 647 case GteOp: 648 return "gte" 649 case LtOp: 650 return "lt" 651 case LteOp: 652 return "lte" 653 case InOp: 654 return "in" 655 case NotInOp: 656 return "notin" 657 case LikeOp: 658 return "like" 659 case NotLikeOp: 660 return "notlike" 661 case ILikeOp: 662 return "ilike" 663 case NotILikeOp: 664 return "notilike" 665 case RegexpLikeOp: 666 return "regexplike" 667 case RegexpNotLikeOp: 668 return "regexpnotlike" 669 case RegexpILikeOp: 670 return "regexpilike" 671 case RegexpNotILikeOp: 672 return "regexpnotilike" 673 } 674 return fmt.Sprintf("%d", bo) 675} 676 677func (bi BitwiseOperation) String() string { 678 switch bi { 679 case BitwiseInversionOp: 680 return "Inversion" 681 case BitwiseOrOp: 682 return "OR" 683 case BitwiseAndOp: 684 return "AND" 685 case BitwiseXorOp: 686 return "XOR" 687 case BitwiseLeftShiftOp: 688 return "Left Shift" 689 case BitwiseRightShiftOp: 690 return "Right Shift" 691 } 692 return fmt.Sprintf("%d", bi) 693} 694 695func (ro RangeOperation) String() string { 696 switch ro { 697 case BetweenOp: 698 return betweenStr 699 case NotBetweenOp: 700 return "not between" 701 } 702 return fmt.Sprintf("%d", ro) 703} 704 705func (jt JoinType) String() string { 706 switch jt { 707 case InnerJoinType: 708 return "InnerJoinType" 709 case FullOuterJoinType: 710 return "FullOuterJoinType" 711 case RightOuterJoinType: 712 return "RightOuterJoinType" 713 case LeftOuterJoinType: 714 return "LeftOuterJoinType" 715 case FullJoinType: 716 return "FullJoinType" 717 case RightJoinType: 718 return "RightJoinType" 719 case LeftJoinType: 720 return "LeftJoinType" 721 case NaturalJoinType: 722 return "NaturalJoinType" 723 case NaturalLeftJoinType: 724 return "NaturalLeftJoinType" 725 case NaturalRightJoinType: 726 return "NaturalRightJoinType" 727 case NaturalFullJoinType: 728 return "NaturalFullJoinType" 729 case CrossJoinType: 730 return "CrossJoinType" 731 } 732 return fmt.Sprintf("%d", jt) 733} 734