goext/sq/builder.go

package sq

import (
	"fmt"
	"gogs.mikescher.com/BlackForestBytes/goext/exerr"
	"reflect"
	"strings"
)

func BuildUpdateStatement(q Queryable, tableName string, obj any, idColumn string) (string, PP, error) {
	rval := reflect.ValueOf(obj)
	rtyp := rval.Type()

	params := PP{}

	setClauses := make([]string, 0)

	matchClause := ""

	for i := 0; i < rtyp.NumField(); i++ {

		rsfield := rtyp.Field(i)
		rvfield := rval.Field(i)

		if !rsfield.IsExported() {
			continue
		}

		columnName := rsfield.Tag.Get("db")
		if columnName == "" || columnName == "-" {
			continue
		}

		if idColumn == columnName {
			idValue, err := convertValueToDB(q, rvfield.Interface())
			if err != nil {
				return "", nil, err
			}

			matchClause = fmt.Sprintf("(%s = :%s)", columnName, params.Add(idValue))

			continue
		}

		if rsfield.Type.Kind() == reflect.Ptr && rvfield.IsNil() {

			setClauses = append(setClauses, fmt.Sprintf("%s = NULL", columnName))

		} else {

			val, err := convertValueToDB(q, rvfield.Interface())
			if err != nil {
				return "", nil, err
			}

			setClauses = append(setClauses, fmt.Sprintf("%s = :%s", columnName, params.Add(val)))

		}
	}

	if len(setClauses) == 0 {
		return "", nil, exerr.New(exerr.TypeSQLBuild, "no updates clauses found in object").Build()
	}

	if matchClause == "" {
		return "", nil, exerr.New(exerr.TypeSQLBuild, "id column not found in object").Build()
	}

	//goland:noinspection SqlNoDataSourceInspection
	return fmt.Sprintf("UPDATE %s SET %s WHERE %s", tableName, strings.Join(setClauses, ", "), matchClause), params, nil
}

func BuildInsertStatement(q Queryable, tableName string, obj any) (string, PP, error) {
	rval := reflect.ValueOf(obj)
	rtyp := rval.Type()

	params := PP{}

	fields := make([]string, 0)
	values := make([]string, 0)

	for i := 0; i < rtyp.NumField(); i++ {

		rsfield := rtyp.Field(i)
		rvfield := rval.Field(i)

		if !rsfield.IsExported() {
			continue
		}

		columnName := rsfield.Tag.Get("db")
		if columnName == "" || columnName == "-" {
			continue
		}

		if rsfield.Type.Kind() == reflect.Ptr && rvfield.IsNil() {

			fields = append(fields, columnName)
			values = append(values, "NULL")

		} else {

			val, err := convertValueToDB(q, rvfield.Interface())
			if err != nil {
				return "", nil, err
			}

			fields = append(fields, columnName)
			values = append(values, ":"+params.Add(val))

		}
	}

	if len(fields) == 0 {
		return "", nil, exerr.New(exerr.TypeSQLBuild, "no fields found in object").Build()
	}

	//goland:noinspection SqlNoDataSourceInspection
	return fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)", tableName, strings.Join(fields, ", "), strings.Join(values, ", ")), params, nil
}