aboutsummaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rw-r--r--lib/sql/meta.go107
-rw-r--r--lib/sql/meta_example_test.go129
-rw-r--r--lib/sql/op_where.go22
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
+}
generated by cgit v1.3 (git 2.53.0) at 2026-04-15 07:25:05 +0000