pgxdml

package
v0.0.0-...-c5072ac Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 19, 2023 License: BSD-3-Clause Imports: 5 Imported by: 1

Documentation

Index

Examples

Constants

View Source 
const (
	Where = "WHERE "
	And   = " AND "
	Set   = "SET "
)
View Source 
const (
	// TimestampFn - timestamp SQL function
	TimestampFn = Function("now()")
)

Variables

This section is empty.

Functions

func ExpandSelect 

func ExpandSelect(template string, where []Attr) (string, error)

ExpandSelect - given a template, expand the template to build a WHERE clause if configured

Example 
t := "select * from access_log {where} order by start_time desc limit 5"
where := []Attr{{Key: "status_code", Val: "503"}}

sql, err := ExpandSelect("", nil)
fmt.Printf("test: ExpandSelect(nil,nil) -> [error:%v] [empty:%v]\n", err, sql == "")

sql, err = ExpandSelect(t, nil)
fmt.Printf("test: ExpandSelect(t,nil) -> [error:%v] %v\n", err, sql)

sql, err = ExpandSelect(t, where)
fmt.Printf("test: ExpandSelect(t,where) -> [error:%v] %v\n", err, sql)

Output:

test: ExpandSelect(nil,nil) -> [error:template is empty] [empty:true]
test: ExpandSelect(t,nil) -> [error:<nil>] select * from access_log order by start_time desc limit 5
test: ExpandSelect(t,where) -> [error:<nil>] select * from access_log
WHERE status_code = '503' order by start_time desc limit 5

func FmtAttr 

func FmtAttr(attr Attr) (string, error)

FmtAttr - format a name, value pair for a SQL statement

Example 
s, err := FmtAttr(Attr{})
fmt.Printf("Name  [\"\"]  : %v\n", NilEmpty(s))
fmt.Printf("Error       : %v\n", err)

s, err = FmtAttr(Attr{Key: "attr_name_1"})
fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
fmt.Printf("Error              : %v\n", err)

s, err = FmtAttr(Attr{Key: "attr_name_2", Val: 1234})
fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
fmt.Printf("Error              : %v\n", err)

s, err = FmtAttr(Attr{Key: "attr_name_3", Val: false})
fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
fmt.Printf("Error              : %v\n", err)

//s, err = FmtAttr(util.Attr{Name: "attr_name_4", Val: time.Now()})
//fmt.Println("default format:", time.Now())
//fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
//fmt.Printf("Error              : %v\n", err)

s, err = FmtAttr(Attr{Key: "attr_name_5", Val: "value string"})
fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
fmt.Printf("Error              : %v\n", err)

s, err = FmtAttr(Attr{Key: "attr_name_6", Val: Function("now()")})
fmt.Printf("Name  [attr_name]  : %v\n", NilEmpty(s))
fmt.Printf("Error              : %v\n", err)

Output:

Name  [""]  : <nil>
Error       : invalid attribute argument, attribute name is empty
Name  [attr_name]  : attr_name_1 = NULL
Error              : <nil>
Name  [attr_name]  : attr_name_2 = 1234
Error              : <nil>
Name  [attr_name]  : attr_name_3 = false
Error              : <nil>
Name  [attr_name]  : attr_name_5 = 'value string'
Error              : <nil>
Name  [attr_name]  : attr_name_6 = now()
Error              : <nil>

func FmtTimestamp 

func FmtTimestamp(t time.Time) string

FmtTimestamp - format a time.Time as a timestamp, YYYY-MM-DD HH:MM:SS MS similiarawith the following

func FmtValue 

func FmtValue(v any) (string, error)

FmtValue - format a value to be used in a SQL statment

func NewInsertValues 

func NewInsertValues(v []any) [][]any

NewInsertValues - create a 2-dimensional array of any out of a slice of any

Example 
values := NewInsertValues([]any{100, "customer 1", false, NextValFn("test_entry_Id"), TimestampFn})

fmt.Printf("test: NewInsertValues() -> %v\n", values)

Output:

test: NewInsertValues() -> [[100 customer 1 false nextval('test_entry_Id') now()]]

func SanitizeString 

func SanitizeString(s string) error

SanitizeString - verify that a string does not contain any text associated with a SQL injection

Example 
err := SanitizeString("")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("adfsdfe4fc&*4")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 1: /*")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 2: DROP   Table  ")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 3: DEL ETE FROM")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 4: - -")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 5: ;--")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 6: sa*/nitize 4 ;--")
fmt.Printf("Error  : %v\n", err)

err = SanitizeString("test 7: of select  * froM customers")
fmt.Printf("Error  : %v\n", err)

Output:

Error  : <nil>
Error  : <nil>
Error  : SQL injection embedded in string [test 1: /*] : /*
Error  : SQL injection embedded in string [test 2: drop table] : drop table
Error  : <nil>
Error  : <nil>
Error  : SQL injection embedded in string [test 5: ;--] : --
Error  : SQL injection embedded in string [test 6: sa*/nitize 4 ;--] : --
Error  : SQL injection embedded in string [test 7: of select * from customers] : select * from

func TrimDoubleSpace 

func TrimDoubleSpace(s string) string

TrimDoubleSpace - remove extra spaces

func WriteDelete 

func WriteDelete(sql string, where []Attr) (string, error)

WriteDelete - build a SQL delete statement with a WHERE clause

Example 
package main

import "fmt"

const (
	deleteTestEntryStmt = "DELETE test_entry"
)

func main() {
	where := []Attr{{Key: "customer_id", Val: "customer1"}, {Key: "created_ts", Val: "2022/11/30 15:48:54.049496"}} //time.Now()}}

	sql, err := WriteDelete(deleteTestEntryStmt, where)
	fmt.Printf("test: WriteDelete() -> [error:%v] [stmt:%v]\n", err, NilEmpty(sql))

}

Output:

test: WriteDelete() -> [error:<nil>] [stmt:DELETE test_entry
WHERE customer_id = 'customer1' AND created_ts = '2022/11/30 15:48:54.049496';]

func WriteInsert 

func WriteInsert(sql string, values [][]any) (string, error)

WriteInsert - build a SQL insert statement with a VALUES list

Example 
var values [][]any
values = append(values, []any{100, "customer 1", false, NextValFn("test_entry_Id"), TimestampFn})
values = append(values, []any{200, "customer 2", true, NextValFn("test_entry_Id"), TimestampFn})

stmt, err := WriteInsert(insertEntryStmt, values)
fmt.Printf("test: WriteInsert() -> [error:%v] [stmt:%v\n", err, stmt)

Output:

test: WriteInsert() -> [error:<nil>] [stmt:INSERT INTO test_entry (id,customer_id,ping_traffic,counter_value,changed_ts) VALUES
(100,'customer 1',false,nextval('test_entry_Id'),now()),
(200,'customer 2',true,nextval('test_entry_Id'),now());

func WriteInsertValues 

func WriteInsertValues(sb *strings.Builder, values []any) error

WriteInsertValues - build the values list of a SQL insert statement

Example 
sb := strings.Builder{}

err := WriteInsertValues(&sb, nil)
fmt.Printf("test: WriteInsertValues() -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

sb1 := strings.Builder{}
err = WriteInsertValues(&sb1, []any{100})
fmt.Printf("test: WriteInsertValues() -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb1.String()))

err = WriteInsertValues(&sb, []any{100, "test string", false, NextValFn("test_entry_Id"), TimestampFn})
fmt.Printf("test: WriteInsertValues() -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

Output:

test: WriteInsertValues() -> [error:invalid insert argument, values slice is empty] [stmt:<nil>]
test: WriteInsertValues() -> [error:<nil>] [stmt:(100)]
test: WriteInsertValues() -> [error:<nil>] [stmt:(100,'test string',false,nextval('test_entry_Id'),now())]

func WriteUpdate 

func WriteUpdate(sql string, attrs []Attr, where []Attr) (string, error)

WriteUpdate - build a SQL update statement, including SET and WHERE clauses

Example 
where := []Attr{{Key: "customer_id", Val: "customer1"}, {Key: "created_ts", Val: "2022/11/30 15:48:54.049496"}} //time.Now()}}
attrs := []Attr{{Key: "status_code", Val: "503"}, {Key: "minimum_code", Val: 99}, {Key: "created_ts", Val: Function("now()")}}

sql, err := WriteUpdate(UpdateTestEntryStmt, attrs, where)
fmt.Printf("test: WriteUpdate(stmt,attrs,where) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sql))

//fmt.Printf("Stmt       : %v\n", NilEmpty(sql))
//fmt.Printf("Error      : %v\n", err)

Output:

test: WriteUpdate(stmt,attrs,where) -> [error:<nil>] [stmt:UPDATE test_entry
SET status_code = '503',
minimum_code = 99,
created_ts = now()
WHERE customer_id = 'customer1' AND created_ts = '2022/11/30 15:48:54.049496';]

func WriteUpdateSet 

func WriteUpdateSet(sb *strings.Builder, attrs []Attr) error

WriteUpdateSet - build a SQL set clause

Example 
sb := strings.Builder{}

err := WriteUpdateSet(&sb, nil)
fmt.Printf("test: WriteUpdateSet(nil) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

sb.Reset()
err = WriteUpdateSet(&sb, []Attr{{Key: "status_code", Val: "503"}})
fmt.Printf("test: WriteUpdateSet(name value) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

sb.Reset()
err = WriteUpdateSet(&sb, []Attr{{Key: "status_code", Val: "503"}, {Key: "minimum_code", Val: 99}, {Key: "created_ts", Val: Function("now()")}})
fmt.Printf("test: WriteUpdateSet(name value) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

Output:

test: WriteUpdateSet(nil) -> [error:invalid update set argument, attrs slice is empty] [stmt:<nil>]
test: WriteUpdateSet(name value) -> [error:<nil>] [stmt:SET status_code = '503'
]
test: WriteUpdateSet(name value) -> [error:<nil>] [stmt:SET status_code = '503',
minimum_code = 99,
created_ts = now()
]

func WriteWhere 

func WriteWhere(sb *strings.Builder, terminate bool, attrs []Attr) error

WriteWhere - build a SQL WHERE clause utilizing the given []Attr

Example 
sb := strings.Builder{}

err := WriteWhere(&sb, false, nil)
fmt.Printf("test: WriteWhere(false,nil) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

err = WriteWhere(&sb, false, []Attr{{Key: "", Val: nil}})
fmt.Printf("test: WriteWhere(false,empty name) -> [error:%v] [stmt:%v]\n", err, NilEmpty(strings.Trim(sb.String(), " ")))

sb.Reset()
err = WriteWhere(&sb, true, []Attr{{Key: "status_code", Val: "503"}})
fmt.Printf("test: WriteWhere(true,name,val) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

sb.Reset()
err = WriteWhere(&sb, false, []Attr{{Key: "status_code", Val: "503"}, {Key: "minimum_code", Val: 99}, {Key: "created_ts", Val: Function("now()")}})
fmt.Printf("test: WriteWhere(false,name value) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

Output:

test: WriteWhere(false,nil) -> [error:invalid update where argument, attrs slice is empty] [stmt:<nil>]
test: WriteWhere(false,empty name) -> [error:<nil>] [stmt:WHERE]
test: WriteWhere(true,name,val) -> [error:<nil>] [stmt:WHERE status_code = '503';]
test: WriteWhere(false,name value) -> [error:<nil>] [stmt:WHERE status_code = '503' AND minimum_code = 99 AND created_ts = now()]

func WriteWhereAttributes 

func WriteWhereAttributes(sb *strings.Builder, attrs []Attr) error

WriteWhereAttributes - build a SQL statement only containing the []Attr conditionals

Example 
sb := strings.Builder{}

err := WriteWhereAttributes(&sb, nil)
fmt.Printf("test: WriteWhereAttributes(nil) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

err = WriteWhereAttributes(&sb, []Attr{{Key: "", Val: nil}})
fmt.Printf("test: WriteWhereAttributes(empty name) -> [error:%v] [stmt:%v]\n", err, NilEmpty(strings.Trim(sb.String(), " ")))

sb.Reset()
err = WriteWhereAttributes(&sb, []Attr{{Key: "status_code", Val: "503"}})
fmt.Printf("test: WriteWhereAttributes(name,val) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

sb.Reset()
err = WriteWhereAttributes(&sb, []Attr{{Key: "status_code", Val: "503"}, {Key: "minimum_code", Val: 99}, {Key: "created_ts", Val: Function("now()")}})
fmt.Printf("test: WriteWhereAttributes(name value) -> [error:%v] [stmt:%v]\n", err, NilEmpty(sb.String()))

Output:

test: WriteWhereAttributes(nil) -> [error:invalid update where argument, attrs slice is empty] [stmt:<nil>]
test: WriteWhereAttributes(empty name) -> [error:invalid attribute argument, attribute name is empty] [stmt:<nil>]
test: WriteWhereAttributes(name,val) -> [error:<nil>] [stmt:status_code = '503']
test: WriteWhereAttributes(name value) -> [error:<nil>] [stmt:status_code = '503' AND minimum_code = 99 AND created_ts = now()]

Types

type Attr 

type Attr struct {
	Key string
	Val any
}

func BuildWhere 

func BuildWhere(values map[string][]string) []Attr

BuildWhere - build the []Attr based on the URL query parameters

Example 
u, _ := url.Parse("http://www.google.com/search?loc=texas&zone=frisco")
where := BuildWhere(u.Query())
fmt.Printf("test: BuildWhere(u) -> %v\n", where)

Output:

test: BuildWhere(u) -> [{loc texas} {zone frisco}]

type Function 

type Function string

Function - type used to determine formatting of a functions

func NextValFn 

func NextValFn(sequence string) Function

NextValFn - build a postgresql SQL 'nextval()' function

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.