github.com/octohelm/storage@v0.0.0-20240516030302-1ac2cc1ea347/pkg/sqlbuilder/condition_test.go (about)

     1  package sqlbuilder_test
     2  
     3  import (
     4  	"testing"
     5  
     6  	"github.com/octohelm/storage/internal/testutil"
     7  	. "github.com/octohelm/storage/pkg/sqlbuilder"
     8  )
     9  
    10  func TestConditions(t *testing.T) {
    11  	colA := TypedCol[int]("a")
    12  	colB := TypedCol[string]("b")
    13  	colC := TypedCol[int]("c")
    14  	colD := TypedCol[string]("d")
    15  
    16  	t.Run("Chain Condition", func(t *testing.T) {
    17  		testutil.ShouldBeExpr(t,
    18  			Xor(
    19  				Or(
    20  					And(
    21  						nil,
    22  						TypedCol[int]("a").V(Lt(1)),
    23  						TypedCol[string]("b").V(LeftLike[string]("text")),
    24  					),
    25  					TypedCol[int]("a").V(Eq(2)),
    26  				),
    27  				TypedCol[string]("b").V(RightLike[string]("g")),
    28  			),
    29  			"(((a < ?) AND (b LIKE ?)) OR (a = ?)) XOR (b LIKE ?)",
    30  			1, "%text", 2, "g%",
    31  		)
    32  	})
    33  	t.Run("Compose Condition", func(t *testing.T) {
    34  
    35  		testutil.ShouldBeExpr(t,
    36  			Xor(
    37  				Or(
    38  					And(
    39  						(*Condition)(nil),
    40  						(*Condition)(nil),
    41  						(*Condition)(nil),
    42  						(*Condition)(nil),
    43  						colC.V(In(1, 2)),
    44  						colC.V(In(3, 4)),
    45  						colA.V(Eq(1)),
    46  						colB.V(Like("text")),
    47  					),
    48  					colA.V(Eq(2)),
    49  				),
    50  				colB.V(Like("g")),
    51  			),
    52  
    53  			"(((c IN (?,?)) AND (c IN (?,?)) AND (a = ?) AND (b LIKE ?)) OR (a = ?)) XOR (b LIKE ?)",
    54  			1, 2, 3, 4, 1, "%text%", 2, "%g%",
    55  		)
    56  	})
    57  	t.Run("skip nil", func(t *testing.T) {
    58  		testutil.ShouldBeExpr(t,
    59  			Xor(
    60  				colA.V(In[int]()),
    61  				Or(
    62  					colA.V(NotIn[int]()),
    63  					And(
    64  						nil,
    65  						colA.V(Eq(1)),
    66  						colB.V(Like("text")),
    67  					),
    68  					colA.V(Eq(2)),
    69  				),
    70  				colB.V(Like("g")),
    71  			),
    72  			"(((a = ?) AND (b LIKE ?)) OR (a = ?)) XOR (b LIKE ?)",
    73  			1, "%text%", 2, "%g%",
    74  		)
    75  	})
    76  	t.Run("XOR and OR", func(t *testing.T) {
    77  		testutil.ShouldBeExpr(t,
    78  			Xor(
    79  				Or(
    80  					colA.V(NotIn[int]()),
    81  					And(
    82  						nil,
    83  						colA.V(Eq(1)),
    84  						colB.V(Like("text")),
    85  					),
    86  					colA.V(Eq(2)),
    87  				),
    88  				colB.V(Like("g")),
    89  			),
    90  			"(((a = ?) AND (b LIKE ?)) OR (a = ?)) XOR (b LIKE ?)",
    91  			1, "%text%", 2, "%g%",
    92  		)
    93  	})
    94  	t.Run("XOR", func(t *testing.T) {
    95  		testutil.ShouldBeExpr(t,
    96  			Xor(
    97  				colA.V(Eq(1)),
    98  				colB.V(Like("g")),
    99  			),
   100  			"(a = ?) XOR (b LIKE ?)",
   101  			1, "%g%",
   102  		)
   103  	})
   104  	t.Run("Like", func(t *testing.T) {
   105  		testutil.ShouldBeExpr(t,
   106  			colD.V(Like("e")),
   107  			"d LIKE ?",
   108  			"%e%",
   109  		)
   110  	})
   111  
   112  	t.Run("Not like", func(t *testing.T) {
   113  		testutil.ShouldBeExpr(t,
   114  			colD.V(NotLike("e")),
   115  			"d NOT LIKE ?",
   116  			"%e%",
   117  		)
   118  	})
   119  
   120  	t.Run("Equal", func(t *testing.T) {
   121  		testutil.ShouldBeExpr(t,
   122  			colD.V(Eq("e")),
   123  			"d = ?", "e",
   124  		)
   125  	})
   126  	t.Run("Not Equal", func(t *testing.T) {
   127  		testutil.ShouldBeExpr(t,
   128  			colD.V(Neq("e")),
   129  			"d <> ?",
   130  			"e",
   131  		)
   132  	})
   133  	t.Run("In", func(t *testing.T) {
   134  		testutil.ShouldBeExpr(t,
   135  			colD.V(In("e", "f")),
   136  			"d IN (?,?)", "e", "f",
   137  		)
   138  	})
   139  	t.Run("NotIn", func(t *testing.T) {
   140  		testutil.ShouldBeExpr(t,
   141  			colD.V(NotIn("e", "f")),
   142  			"d NOT IN (?,?)", "e", "f",
   143  		)
   144  	})
   145  	t.Run("Less than", func(t *testing.T) {
   146  		testutil.ShouldBeExpr(t,
   147  			colC.V(Lt(3)),
   148  			"c < ?", 3,
   149  		)
   150  	})
   151  	t.Run("Less or equal than", func(t *testing.T) {
   152  		testutil.ShouldBeExpr(t,
   153  			colC.V(Lte(3)),
   154  			"c <= ?", 3,
   155  		)
   156  	})
   157  	t.Run("Greater than", func(t *testing.T) {
   158  		testutil.ShouldBeExpr(t,
   159  			colC.V(Gt(3)),
   160  			"c > ?", 3,
   161  		)
   162  	})
   163  	t.Run("Greater or equal than", func(t *testing.T) {
   164  		testutil.ShouldBeExpr(t,
   165  			colC.V(Gte(3)),
   166  			"c >= ?", 3,
   167  		)
   168  	})
   169  	t.Run("Between", func(t *testing.T) {
   170  		testutil.ShouldBeExpr(t,
   171  			colC.V(Between(0, 2)),
   172  			"c BETWEEN ? AND ?", 0, 2,
   173  		)
   174  	})
   175  
   176  	t.Run("Not between", func(t *testing.T) {
   177  		testutil.ShouldBeExpr(t,
   178  			colC.V(NotBetween(0, 2)),
   179  			"c NOT BETWEEN ? AND ?", 0, 2,
   180  		)
   181  	})
   182  
   183  	t.Run("Is null", func(t *testing.T) {
   184  		testutil.ShouldBeExpr(t,
   185  			colD.V(IsNull[string]()),
   186  			"d IS NULL",
   187  		)
   188  	})
   189  
   190  	t.Run("Is not null", func(t *testing.T) {
   191  		testutil.ShouldBeExpr(t,
   192  			colD.V(IsNotNull[string]()),
   193  			"d IS NOT NULL",
   194  		)
   195  	})
   196  }