pgm/pgm.go

// Patial Tech.
// Author, Ankit Patial

package pgm

import (
	"strings"
	"time"

	"github.com/jackc/pgx/v5/pgtype"
)

// Table in database
type Table struct {
	Name       string
	PK         []string
	FieldCount uint16
	debug      bool
}

// Debug when set true will print generated query string in stdout
func (t Table) Debug() Clause {
	t.debug = true
	return t
}

//
//  Field ==>
//

// Field related to a table
type Field string

func (f Field) Name() string {
	return strings.Split(string(f), ".")[1]
}

func (f Field) String() string {
	return string(f)
}

// Count fn wrapping of field
func (f Field) Count() Field {
	return Field("COUNT(" + f.String() + ")")
}

// StringEscape will return a empty string for null value
func (f Field) StringEscape() Field {
	return Field("COALESCE(" + f.String() + ", '')")
}

// NumberEscape will return a zero string for null value
func (f Field) NumberEscape() Field {
	return Field("COALESCE(" + f.String() + ", 0)")
}

// BooleanEscape will return a false for null value
func (f Field) BooleanEscape() Field {
	return Field("COALESCE(" + f.String() + ", FALSE)")
}

// Avg fn wrapping of field
func (f Field) Avg() Field {
	return Field("AVG(" + f.String() + ")")
}

func (f Field) Sum() Field {
	return Field("SUM(" + f.String() + ")")
}

func (f Field) Max() Field {
	return Field("MAX(" + f.String() + ")")
}

func (f Field) Min() Field {
	return Field("Min(" + f.String() + ")")
}

func (f Field) Lower() Field {
	return Field("LOWER(" + f.String() + ")")
}

func (f Field) Upper() Field {
	return Field("UPPER(" + f.String() + ")")
}

func (f Field) Trim() Field {
	return Field("TRIM(" + f.String() + ")")
}

func (f Field) IsNull() Conditioner {
	col := f.String()
	return &Cond{Field: col, op: " IS NULL", len: len(col) + 8}
}

func (f Field) IsNotNull() Conditioner {
	col := f.String()
	return &Cond{Field: col, op: " IS NOT NULL", len: len(col) + 12}
}

// EqualFold will use LOWER(column_name) = LOWER(val) for comparision
func (f Field) EqFold(val string) Conditioner {
	col := f.String()
	return &Cond{Field: "LOWER(" + col + ")", Val: val, op: " = LOWER($", action: CondActionNeedToClose, len: len(col) + 5}
}

// Eq is equal
func (f Field) Eq(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " = $", len: len(col) + 5}
}

func (f Field) NotEq(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " != $", len: len(col) + 5}
}

func (f Field) Gt(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " > $", len: len(col) + 5}
}

func (f Field) Lt(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " < $", len: len(col) + 5}
}

func (f Field) Gte(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " >= $", len: len(col) + 5}
}

func (f Field) Lte(val any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " <= $", len: len(col) + 5}
}

func (f Field) Like(val string) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " LIKE $", len: len(f.String()) + 5}
}

func (f Field) LikeFold(val string) Conditioner {
	col := f.String()
	return &Cond{Field: "LOWER(" + col + ")", Val: val, op: " LIKE LOWER($", action: CondActionNeedToClose, len: len(col) + 5}
}

// ILIKE is case-insensitive
func (f Field) ILike(val string) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " ILIKE $", len: len(col) + 5}
}

func (f Field) In(val ...any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " IN($", action: CondActionNeedToClose, len: len(col) + 5}
}

func (f Field) NotIn(val ...any) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: val, op: " NOT IN($", action: CondActionNeedToClose, len: len(col) + 5}
}

func (f Field) NotInSubQuery(qry WhereClause) Conditioner {
	col := f.String()
	return &Cond{Field: col, Val: qry, op: " NOT IN($)", action: CondActionSubQuery}
}

//
//  Helper func ==>
//

// PgTime as in UTC
func PgTime(t time.Time) pgtype.Timestamptz {
	return pgtype.Timestamptz{Time: t, Valid: true}
}

func PgTimeNow() pgtype.Timestamptz {
	return pgtype.Timestamptz{Time: time.Now(), Valid: true}
}

func ConcatWs(sep string, fields ...Field) Field {
	return Field("concat_ws('" + sep + "'," + joinFileds(fields) + ")")
}

func StringAgg(exp, sep string) Field {
	return Field("string_agg(" + exp + ",'" + sep + "')")
}

func StringAggCast(exp, sep string) Field {
	return Field("string_agg(cast(" + exp + " as varchar),'" + sep + "')")
}