lib/sql: refactoring the BindWhere

Changes the BindWhere parameters from two to four.
The first parameter is the OR/AND logic.
The second parameter is the column name.
The third parameter is the operator like "=", "!=".
The fourth parameter is the value.

3 files changed, 143 insertions, 115 deletions

diff --git a/lib/sql/meta.go b/lib/sql/meta.go
index f24a94aa..0bd6241d 100644
--- a/lib/sql/meta.go
+++ b/lib/sql/meta.go
@@ -28,13 +28,8 @@ type Meta struct {
 	// select.
 	ListValue []any
 
-	// ListWhereCond contains list of condition to be joined with
-	// ListHolder.
-	// The text is a free form, does not need to be a column name.
-	ListWhereCond []string
-
-	// ListWhereValue contains list of values for where condition.
-	ListWhereValue []any
+	// listOpWhere contains list of WHERE conditions.
+	listOpWhere []OpWhere
 
 	// Index collect all holder integer value, as in "1,2,3,...".
 	Index []any
@@ -42,8 +37,8 @@ type Meta struct {
 	nholder int
 }
 
-// NewMeta create new Meta using specific driver name.
-// The driver affect the ListHolder value.
+// NewMeta creates new Meta using specific driver name and DML operations.
+// The driver affect the [Meta.ListHolder] value.
 func NewMeta(driverName string, dmlKind DMLKind) (meta *Meta) {
 	meta = &Meta{
 		driver: driverName,
@@ -52,7 +47,7 @@ func NewMeta(driverName string, dmlKind DMLKind) (meta *Meta) {
 	return meta
 }
 
-// Bind column name and variable for DML INSERT, SELECT, or UPDATE.
+// Bind binds column name and variable for DML INSERT, SELECT, or UPDATE.
 // It is a no-op for DML DELETE.
 func (meta *Meta) Bind(colName string, val any) {
 	if meta.kind == DMLKindDelete {
@@ -84,42 +79,52 @@ func (meta *Meta) Bind(colName string, val any) {
 	}
 }
 
-// BindWhere bind value for where condition.
+// BindWhere binds the value val for DML WHERE condition.
+//
+// The logic parameter defines the logical operator like "OR"
+// and "AND" for joining multiple BindWhere.
 //
-// The cond string is optional, can be a column name with operator or any
-// text like "AND col=" or "OR col=".
+// The col parameter defines the column name to be compared with val.
 //
-// It return the length of [Meta.ListHolder].
+// The comp parameter defines the comparison for col and val, like "=" for
+// equality or "!=" for non-equality.
 //
 // It is a no-operation for DML INSERT.
-func (meta *Meta) BindWhere(cond string, val any) int {
+func (meta *Meta) BindWhere(logic, col, comp string, val any) (whereOp *OpWhere) {
 	if meta.kind == DMLKindInsert {
-		return 0
+		return nil
 	}
 
-	meta.ListWhereCond = append(meta.ListWhereCond, cond)
-	meta.ListWhereValue = append(meta.ListWhereValue, val)
+	whereOp = &OpWhere{
+		logic:  logic,
+		column: col,
+		comp:   comp,
+		val:    val,
+	}
 
 	meta.nholder++
 	meta.Index = append(meta.Index, meta.nholder)
 	if meta.driver == DriverNamePostgres {
-		meta.ListHolder = append(meta.ListHolder, fmt.Sprintf(`$%d`, meta.nholder))
+		whereOp.holder = fmt.Sprintf(`$%d`, meta.nholder)
 	} else {
-		meta.ListHolder = append(meta.ListHolder, DefaultPlaceHolder)
+		whereOp.holder = DefaultPlaceHolder
 	}
-	return meta.nholder
+
+	meta.listOpWhere = append(meta.listOpWhere, *whereOp)
+
+	return whereOp
 }
 
-// Holders generate string of holder, for example "$1, $2, ...", for DML
-// INSERT-VALUES.
+// Holders returns string of holders joined with comma ",";
+// for example "$1, $2, ...", for INSERT-VALUES.
 func (meta *Meta) Holders() string {
 	return strings.Join(meta.ListHolder, `,`)
 }
 
-// Names generate string of column names, for example "col1, col2, ...", for
+// Names returns string of column names, for example "col1, col2, ...", for
 // DML INSERT or SELECT.
 //
-// It will return an empty string if kind is DML UPDATE or DELETE.
+// It will return an empty string if kind is UPDATE or DELETE.
 func (meta *Meta) Names() string {
 	if meta.kind == DMLKindUpdate || meta.kind == DMLKindDelete {
 		return ``
@@ -127,7 +132,7 @@ func (meta *Meta) Names() string {
 	return strings.Join(meta.ListName, `,`)
 }
 
-// Sub return the child of Meta for building subquery.
+// Sub returns the child of Meta for building subquery.
 func (meta *Meta) Sub() (sub *Meta) {
 	sub = &Meta{
 		driver:  meta.driver,
@@ -137,10 +142,10 @@ func (meta *Meta) Sub() (sub *Meta) {
 	return sub
 }
 
-// UpdateFields generate string of "col1=<holder>, col2=<holder>, ..." for
+// UpdateFields returns string of "col1=<holder>, col2=<holder>, ..." for
 // DML UPDATE.
 //
-// It will return an empty string if kind is not UPDATE.
+// It will return an empty string if kind is not DML UPDATE.
 func (meta *Meta) UpdateFields() string {
 	if meta.kind != DMLKindUpdate {
 		return ``
@@ -162,7 +167,7 @@ func (meta *Meta) UpdateFields() string {
 	return sb.String()
 }
 
-// UpdateValues return the merged of ListValue and ListWhereValue for DML
+// UpdateValues returns the merged of ListValue and list WhereValues for DML
 // UPDATE.
 //
 // It will return nil if kind is not DML UPDATE.
@@ -171,38 +176,26 @@ func (meta *Meta) UpdateValues() (listVal []any) {
 		return nil
 	}
 	listVal = append(listVal, meta.ListValue...)
-	listVal = append(listVal, meta.ListWhereValue...)
+	listVal = append(listVal, meta.WhereValues()...)
 	return listVal
 }
 
-// WhereFields merge the ListWhereCond and ListHolder.
-//
+// WhereFields returns the DML for WHERE query, as in "col=$? AND y!=$?...".
 // It will return an empty string if kind is DML INSERT.
 func (meta *Meta) WhereFields() string {
 	if meta.kind == DMLKindInsert {
 		return ``
 	}
 
-	var (
-		off int
-		sb  strings.Builder
-		x   int
-	)
-
-	if meta.kind == DMLKindUpdate || meta.kind == DMLKindInsert {
-		off = len(meta.ListValue)
-	}
-	for ; x < len(meta.ListWhereCond); x++ {
-		if x > 0 {
-			sb.WriteByte(' ')
-		}
-		fmt.Fprintf(&sb, `%s%s`, meta.ListWhereCond[x], meta.ListHolder[off+x])
+	var sb strings.Builder
+	for _, op := range meta.listOpWhere {
+		sb.WriteString(op.String())
 	}
 	return sb.String()
 }
 
-// WhereHolders generate string of holder, for example "$1,$2, ...", based
-// on number of item added with [Meta.BindWhere].
+// WhereHolders returns string of holders joining by comma, for example
+// "$1,$2, ...", based on number of item added with [Meta.BindWhere].
 // Similar to method Holders but for where condition.
 //
 // It will return an empty string if kind is DML INSERT.
@@ -210,5 +203,21 @@ func (meta *Meta) WhereHolders() string {
 	if meta.kind == DMLKindInsert {
 		return ``
 	}
-	return strings.Join(meta.ListHolder, `,`)
+	var sb strings.Builder
+	for x, op := range meta.listOpWhere {
+		if x > 0 {
+			sb.WriteString(`,`)
+		}
+		sb.WriteString(op.holder)
+	}
+	return sb.String()
+}
+
+// WhereValues returns list of values in WHERE conditions.
+func (meta *Meta) WhereValues() (list []any) {
+	list = make([]any, 0, len(meta.listOpWhere))
+	for _, op := range meta.listOpWhere {
+		list = append(list, op.val)
+	}
+	return list
 }
diff --git a/lib/sql/meta_example_test.go b/lib/sql/meta_example_test.go
index 9d1840a2..4d668bee 100644
--- a/lib/sql/meta_example_test.go
+++ b/lib/sql/meta_example_test.go
@@ -43,20 +43,19 @@ func ExampleMeta_BindWhere() {
 			int(1000),
 			string(`JohnDoe`),
 		}
-		idx int
 	)
 
-	idx = meta.BindWhere(``, vals[0])
-	fmt.Printf("WHERE id=$%d\n", idx)
+	opWhere := meta.BindWhere(``, `id`, `=`, vals[0])
+	fmt.Printf("%s\n", opWhere.String())
 
-	idx = meta.BindWhere(``, vals[1])
-	fmt.Printf("AND name=$%d\n", idx)
+	opWhere = meta.BindWhere(`AND`, `name`, `=`, vals[1])
+	fmt.Printf("%s\n", opWhere.String())
 
-	fmt.Println(meta.ListWhereValue)
+	fmt.Println(meta.WhereValues())
 
 	// Output:
-	// WHERE id=$1
-	// AND name=$2
+	// id = $1
+	//  AND name = $2
 	// [1000 JohnDoe]
 }
 
@@ -131,27 +130,27 @@ func ExampleMeta_Sub() {
 
 	meta.Bind(`id`, &t.ID)
 	meta.Bind(`name`, &t.Name)
-	meta.BindWhere(`id`, qid)
+	meta.BindWhere(``, `id`, `=`, qid)
 
 	var (
 		metain = meta.Sub()
 		qnames = []string{`hello`, `world`}
 	)
 
-	metain.BindWhere(``, qnames[0])
-	metain.BindWhere(``, qnames[1])
+	metain.BindWhere(``, ``, ``, qnames[0])
+	metain.BindWhere(``, ``, ``, qnames[1])
 
 	var q = fmt.Sprintf(`SELECT %s FROM t WHERE id=$1 OR name IN (%s);`,
-		meta.Names(), metain.Holders())
+		meta.Names(), metain.WhereHolders())
 
-	var qparams = sql.JoinValues(meta.ListWhereValue, metain.ListWhereValue)
+	var qparams = sql.JoinValues(meta.WhereValues(), metain.WhereValues())
 
 	// db.QueryRow(q, qparams...).Scan(meta.ListValue...)
 
 	fmt.Println(q)
 	fmt.Println(`SELECT #n=`, len(meta.ListValue))
-	fmt.Println(`WHERE=`, meta.ListWhereValue)
-	fmt.Println(`WHERE IN=`, metain.ListWhereValue)
+	fmt.Println(`WHERE=`, meta.WhereValues())
+	fmt.Println(`WHERE IN=`, metain.WhereValues())
 	fmt.Println(`qparams=`, qparams)
 
 	// Output:
@@ -180,23 +179,20 @@ func ExampleMeta_UpdateFields() {
 
 	meta.Bind(`id`, t.ID)
 	meta.Bind(`name`, t.Name)
-	meta.BindWhere(`id=`, qid)
-	meta.BindWhere(`AND name=`, qname)
+	meta.BindWhere(``, `id`, `=`, qid)
+	meta.BindWhere(`AND`, `name`, `=`, qname)
 
-	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`, meta.UpdateFields(), meta.WhereFields())
+	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`,
+		meta.UpdateFields(), meta.WhereFields())
 
 	// db.Exec(q, meta.UpdateValues()...);
 
 	fmt.Println(q)
-	fmt.Println(`SET=`, meta.ListValue)
-	fmt.Println(`WHERE=`, meta.ListWhereValue)
-	fmt.Println(`Exec=`, meta.UpdateValues())
+	fmt.Println(`UpdateValues=`, meta.UpdateValues())
 
 	// Output:
-	// UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
-	// SET= [2 world]
-	// WHERE= [1 hello]
-	// Exec= [2 world 1 hello]
+	// UPDATE t SET id=$1,name=$2 WHERE  id = $3 AND name = $4;
+	// UpdateValues= [2 world 1 hello]
 }
 
 func ExampleMeta_UpdateValues() {
@@ -217,47 +213,48 @@ func ExampleMeta_UpdateValues() {
 
 	meta.Bind(`id`, t.ID)
 	meta.Bind(`name`, t.Name)
-	meta.BindWhere(`id`, qid)
-	meta.BindWhere(`name`, qname)
+	meta.BindWhere(``, `id`, `=`, qid)
+	meta.BindWhere(`AND`, `name`, `=`, qname)
 
-	var q = fmt.Sprintf(`UPDATE t SET id=$%d,name=$%d WHERE id=$%d AND name=$%d;`, meta.Index...)
+	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`,
+		meta.UpdateFields(), meta.WhereFields())
 
 	// db.Exec(q, meta.UpdateValues()...);
 
 	fmt.Println(q)
-	fmt.Println(`Index=`, meta.Index)
-	fmt.Println(`SET=`, meta.ListValue)
-	fmt.Println(`WHERE=`, meta.ListWhereValue)
-	fmt.Println(`Exec=`, meta.UpdateValues())
+	fmt.Println(`UpdateValues=`, meta.UpdateValues())
 
 	// Output:
-	// UPDATE t SET id=$1,name=$2 WHERE id=$3 AND name=$4;
-	// Index= [1 2 3 4]
-	// SET= [2 world]
-	// WHERE= [1 hello]
-	// Exec= [2 world 1 hello]
+	// UPDATE t SET id=$1,name=$2 WHERE  id = $3 AND name = $4;
+	// UpdateValues= [2 world 1 hello]
 }
 
 func ExampleMeta_WhereFields() {
 	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
 
-	meta.BindWhere(`id=`, 1000)
-	meta.BindWhere(`AND name=`, `share`)
+	meta.BindWhere(``, `id`, `=`, 1000)
+	meta.BindWhere(`AND`, `name`, `=`, `share`)
+
+	fmt.Printf("SELECT * FROM t WHERE %s;\n", meta.WhereFields())
+	fmt.Println(meta.WhereValues())
 
-	fmt.Printf(`SELECT * FROM t WHERE %s;`, meta.WhereFields())
 	// Output:
-	// SELECT * FROM t WHERE id=$1 AND name=$2;
+	// SELECT * FROM t WHERE  id = $1 AND name = $2;
+	// [1000 share]
 }
 
 func ExampleMeta_WhereHolders() {
 	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
 
-	meta.BindWhere(`id`, 1000)
-	meta.BindWhere(`name`, `share`)
+	meta.BindWhere(``, ``, ``, 1000)
+	meta.BindWhere(``, ``, ``, `share`)
+
+	fmt.Printf("SELECT * FROM t WHERE id IN (%s);\n", meta.WhereHolders())
+	fmt.Println(meta.WhereValues())
 
-	fmt.Printf(`SELECT * FROM t WHERE id IN (%s);`, meta.WhereHolders())
 	// Output:
 	// SELECT * FROM t WHERE id IN ($1,$2);
+	// [1000 share]
 }
 
 func ExampleMeta_deleteOnPostgresql() {
@@ -267,18 +264,18 @@ func ExampleMeta_deleteOnPostgresql() {
 		qname = `hello`
 	)
 
-	meta.BindWhere(`id=`, qid)
-	meta.BindWhere(`OR name=`, qname)
+	meta.BindWhere(``, `id`, `=`, qid)
+	meta.BindWhere(`OR`, `name`, `=`, qname)
 
 	var q = fmt.Sprintf(`DELETE FROM t WHERE %s;`, meta.WhereFields())
 
-	// db.Exec(q, meta.ListWhereValue...)
+	// db.Exec(q, meta.WhereValues()...)
 
 	fmt.Println(q)
-	fmt.Println(meta.ListWhereValue)
+	fmt.Println(meta.WhereValues())
 
 	// Output:
-	// DELETE FROM t WHERE id=$1 OR name=$2;
+	// DELETE FROM t WHERE  id = $1 OR name = $2;
 	// [1 hello]
 }
 
@@ -326,19 +323,19 @@ func ExampleMeta_selectOnPostgresql() {
 
 	meta.Bind(`id`, &t.ID)
 	meta.Bind(`name`, &t.Name)
-	meta.BindWhere(`id=`, qid)
-	meta.BindWhere(`OR name=`, qname)
+	meta.BindWhere(``, `id`, `=`, qid)
+	meta.BindWhere(`OR`, `name`, `=`, qname)
 
 	var q = fmt.Sprintf(`SELECT %s FROM t WHERE %s;`, meta.Names(), meta.WhereFields())
 
-	// db.QueryRow(q, meta.ListWhereValue...).Scan(meta.ListValue...)
+	// db.QueryRow(q, meta.WhereValues()...).Scan(meta.ListValue...)
 
 	fmt.Println(q)
-	fmt.Println(`WHERE=`, meta.ListWhereValue)
+	fmt.Println(`WHERE=`, meta.WhereValues())
 	fmt.Println(len(meta.ListValue))
 
 	// Output:
-	// SELECT id,name FROM t WHERE id=$1 OR name=$2;
+	// SELECT id,name FROM t WHERE  id = $1 OR name = $2;
 	// WHERE= [1 hello]
 	// 2
 }
@@ -357,7 +354,6 @@ func ExampleMeta_subquery() {
 		subid = 500
 		t     Table
 		qb    strings.Builder
-		idx   int
 	)
 
 	meta.Bind(`id`, &t.ID)
@@ -367,21 +363,22 @@ func ExampleMeta_subquery() {
 	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())
 
 	if id != 0 {
-		idx = meta.BindWhere(``, id)
-		fmt.Fprintf(&qb, ` AND id = $%d`, idx)
+		op := meta.BindWhere(`AND`, `id`, `=`, id)
+		qb.WriteString(op.String())
 	}
 	if subid != 0 {
-		idx = meta.BindWhere(``, subid)
-		fmt.Fprintf(&qb, ` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`, idx)
+		op := meta.BindWhere(``, `u.id`, `=`, subid)
+		fmt.Fprintf(&qb, ` AND sub_id = (SELECT id FROM u WHERE %s);`,
+			op.String())
 	}
 
-	// db.Exec(qb.String(),meta.ListWhereValue...).Scan(meta.ListValue...)
+	// db.Exec(qb.String(),meta.WhereValues()...).Scan(meta.ListValue...)
 
 	fmt.Println(qb.String())
-	fmt.Println(meta.ListWhereValue)
+	fmt.Println(meta.WhereValues())
 
 	// Output:
-	// SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
+	// SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE  u.id = $2);
 	// [1 500]
 }
 
@@ -408,20 +405,20 @@ func ExampleMeta_subqueryWithIndex() {
 	fmt.Fprintf(&qb, `SELECT %s FROM t WHERE 1=1`, meta.Names())
 	if id != 0 {
 		qb.WriteString(` AND id = $%d`)
-		meta.BindWhere(`id`, id)
+		meta.BindWhere(``, `id`, `=`, id)
 	}
 	if subid != 0 {
 		qb.WriteString(` AND sub_id = (SELECT id FROM u WHERE u.id = $%d);`)
-		meta.BindWhere(`sub_id`, subid)
+		meta.BindWhere(``, `u.id`, `=`, subid)
 	}
 
 	var q = fmt.Sprintf(qb.String(), meta.Index...)
 
-	// db.Exec(q, meta.ListWhereValue...).Scan(meta.ListValue...)
+	// db.Exec(q, meta.WhereValues()...).Scan(meta.ListValue...)
 
 	fmt.Println(q)
 	fmt.Println(meta.Index)
-	fmt.Println(meta.ListWhereValue)
+	fmt.Println(meta.WhereValues())
 
 	// Output:
 	// SELECT id,sub_id,name FROM t WHERE 1=1 AND id = $1 AND sub_id = (SELECT id FROM u WHERE u.id = $2);
diff --git a/lib/sql/op_where.go b/lib/sql/op_where.go
new file mode 100644
index 00000000..4330fb10
--- /dev/null
+++ b/lib/sql/op_where.go
@@ -0,0 +1,22 @@
+// SPDX-License-Identifier: BSD-3-Clause
+// SPDX-FileCopyrightText: 2024 Shulhan <ms@kilabit.info>
+
+package sql
+
+// OpWhere stores the operation for WHERE conditions from calling
+// [Meta.BindWhere].
+type OpWhere struct {
+	val    any
+	logic  string
+	column string
+	comp   string
+	holder string
+}
+
+func (op OpWhere) String() string {
+	// The space between column is required for "IS" like comparison.
+	if op.logic == `` {
+		return ` ` + op.column + ` ` + op.comp + ` ` + op.holder
+	}
+	return ` ` + op.logic + ` ` + op.column + ` ` + op.comp + ` ` + op.holder
+}