sqlp.go

// Copyright 2012 Kamil Kisiel. All rights reserved.
// Modified >2023 by Marius Schmalz
// Use of this source code is governed by the MIT
// license which can be found in the LICENSE file.

/*
Package sqlp provides some convenience functions for using structs with
the Go standard library's database/sql package.

The package matches struct field names to SQL query column names. A field can
also specify a matching column with "sql" tag, if it's different from field
name.  Unexported fields or fields marked with `sql:"-"` are ignored, just like
with "encoding/json" package.

For example:
ToDo (See Readme)
*/
package sqlp

import (
	"bytes"
	"database/sql"
	"fmt"
	. "github.com/ByteSizedMarius/sqlp/sqlpdb"
	"reflect"
	"strings"
)

var (
	// Global database handle to use for queries
	// Used for Insert, QueryBasic,
	db *sql.DB
)

// SetDatabase sets the global database handle to be used by the Query function.
func SetDatabase(sqldb *sql.DB) {
	db = sqldb
}

// ——————————————————————————————————————————————————————————————————————————————
// Queries
// ——————————————————————————————————————————————————————————————————————————————

func Query[T any](query string, args ...any) (results []T, err error) {
	return QueryDb[T](db, query, args...)
}

func QueryRow[T any](query string, args ...any) (result T, err error) {
	return QueryRowDb[T](db, query, args...)
}

func QueryBasic[T string | int | int64 | float32 | float64](query string, args ...any) (results []T, err error) {
	return QueryBasicDb[T](db, query, args...)
}

func QueryBasicRow[T string | int | int64 | float32 | float64](query string, args ...any) (result T, err error) {
	return QueryBasicRowDb[T](db, query, args...)

}

// ——————————————————————————————————————————————————————————————————————————————
// Repo Functions
// ——————————————————————————————————————————————————————————————————————————————

func GetR[T Repo]() ([]T, error) {
	query := "SELECT * FROM " + table[T]()
	return QueryDb[T](db, query)
}

func GetWhereR[T Repo](where string, args ...any) ([]T, error) {
	query, err := whereBuilder("SELECT * FROM "+table[T](), where)
	if err != nil {
		return nil, err
	}

	return QueryDb[T](db, query, args...)
}

func GetSingleWhereR[T Repo](where string, args ...any) (res T, err error) {
	query, err := whereBuilder("SELECT * FROM "+table[T](), where)
	if err != nil {
		return
	}

	return QueryRowDb[T](db, query, args...)
}

func InsertR[T Repo](obj T) (int, error) {
	return InsertRdb[T](db, obj)
}

func UpdateR[T Repo](obj T) error {
	return UpdateRdb[T](db, obj)
}

func DeleteR[T Repo](obj T) error {
	return DeleteRdb[T](db, obj)
}

// ——————————————————————————————————————————————————————————————————————————————
// Deprecated
// ——————————————————————————————————————————————————————————————————————————————

//func In(query string, args ...any) error {
//	return InDb(db, query, args...)
//}

// Insert inserts the given object into the table and returns the last inserted id.
// Autogenerated fields can be tagged with `sql-auto:""` (AutoGenTagName) in order for them to be ignored during insert.
// Deprecated
func Insert[T any](obj T, table string) (int, error) {
	return InsertDb[T](db, obj, table)
}

// Update
// Deprecated
func Update[T any](obj T, table string) error {
	return UpdateDb[T](db, obj, table)
}

// Delete
// Deprecated
func Delete[T any](pk any, table string) error {
	return DeleteDb[T](db, pk, table)
}

// ——————————————————————————————————————————————————————————————————————————————
// NameMapper
// ——————————————————————————————————————————————————————————————————————————————

// ToSnakeCase converts a string to snake case, words separated with underscores.
// It's intended to be used with NameMapper to map struct field names to snake case database fields.
func ToSnakeCase(src string) string {
	thisUpper := false
	prevUpper := false

	buf := bytes.NewBufferString("")
	for i, v := range src {
		if v >= 'A' && v <= 'Z' {
			thisUpper = true
		} else {
			thisUpper = false
		}
		if i > 0 && thisUpper && !prevUpper {
			buf.WriteRune('_')
		}
		prevUpper = thisUpper
		buf.WriteRune(v)
	}
	return strings.ToLower(buf.String())
}

// ——————————————————————————————————————————————————————————————————————————————
// Helper
// ——————————————————————————————————————————————————————————————————————————————

func table[T Repo]() string {
	var instance T
	instanceType := reflect.TypeOf(instance)
	instanceValue := reflect.New(instanceType).Elem().Interface().(T)
	return instanceValue.TableName()
}

func whereBuilder(query string, where string) (string, error) {
	if where == "" {
		return query, nil
	}
	if !strings.HasPrefix(where, "WHERE") && !strings.HasPrefix(where, "ORDER") {
		return "", fmt.Errorf("where clause must start with WHERE/ORDER " + query)
	}

	return query + " " + where, nil
}