-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql.go
136 lines (117 loc) · 3.75 KB
/
sql.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
// Content managed by Project Forge, see [projectforge.md] for details.
package database
import (
"fmt"
"strings"
)
const whereSpaces = " where "
func SQLSelect(columns string, tables string, where string, orderBy string, limit int, offset int) string {
return SQLSelectGrouped(columns, tables, where, "", orderBy, limit, offset)
}
func SQLSelectSimple(columns string, tables string, where ...string) string {
return SQLSelectGrouped(columns, tables, strings.Join(where, " and "), "", "", 0, 0)
}
func SQLSelectGrouped(columns string, tables string, where string, groupBy string, orderBy string, limit int, offset int) string {
if columns == "" {
columns = "*"
}
whereClause := ""
if len(where) > 0 {
whereClause = whereSpaces + where
}
groupByClause := ""
if len(groupBy) > 0 {
groupByClause = " group by " + groupBy
}
orderByClause := ""
if len(orderBy) > 0 {
orderByClause = " order by " + orderBy
}
limitClause := ""
if limit > 0 {
limitClause = fmt.Sprintf(" limit %d", limit)
}
offsetClause := ""
if offset > 0 {
offsetClause = fmt.Sprintf(" offset %d", offset)
}
return "select " + columns + " from " + tables + whereClause + groupByClause + orderByClause + limitClause + offsetClause
}
func SQLInsert(table string, columns []string, rows int, placeholder string) string {
if rows <= 0 {
rows = 1
}
colString := strings.Join(columns, ", ")
var placeholders []string
for i := 0; i < rows; i++ {
var ph []string
for idx := range columns {
if placeholder == "?" {
ph = append(ph, "?")
} else {
ph = append(ph, fmt.Sprintf("$%d", (i*len(columns))+idx+1))
}
}
placeholders = append(placeholders, "("+strings.Join(ph, ", ")+")")
}
return fmt.Sprintf("insert into %s (%s) values %s", table, colString, strings.Join(placeholders, ", "))
}
func SQLInsertReturning(table string, columns []string, rows int, returning []string, placeholder string) string {
q := SQLInsert(table, columns, rows, placeholder)
if len(returning) > 0 {
q += " returning " + strings.Join(returning, ", ")
}
return q
}
func SQLUpdate(table string, columns []string, where string, placeholder string) string {
whereClause := ""
if len(where) > 0 {
whereClause = whereSpaces + where
}
stmts := make([]string, 0, len(columns))
for i, col := range columns {
if placeholder == "?" {
stmts = append(stmts, fmt.Sprintf("%s = ?", col))
} else {
stmts = append(stmts, fmt.Sprintf("%s = $%d", col, i+1))
}
}
return fmt.Sprintf("update %s set %s%s", table, strings.Join(stmts, ", "), whereClause)
}
func SQLUpdateReturning(table string, columns []string, where string, returned []string, placeholder string) string {
q := SQLUpdate(table, columns, where, placeholder)
if len(returned) > 0 {
q += " returning " + strings.Join(returned, ", ")
}
return q
}
func SQLUpsert(table string, columns []string, rows int, conflicts []string, updates []string, placeholder string) string {
q := SQLInsert(table, columns, rows, placeholder)
q += " on conflict (" + strings.Join(conflicts, ", ") + ") do update set "
for idx, x := range updates {
if idx > 0 {
q += ", "
}
q += fmt.Sprintf("%s = excluded.%s", x, x)
}
return q
}
func SQLDelete(table string, where string) string {
if strings.TrimSpace(where) == "" {
return fmt.Sprintf("attempt to delete from [%s] with empty where clause", table)
}
return "delete from " + table + whereSpaces + where
}
func SQLInClause(column string, numParams int, offset int) string {
resBuilder := strings.Builder{}
for index := 0; index < numParams; index++ {
if index == 0 {
resBuilder.WriteString(column + " in ")
resBuilder.WriteString(fmt.Sprintf("($%d", offset+1))
} else {
resBuilder.WriteString(fmt.Sprintf(", $%d", index+offset+1))
}
}
resBuilder.WriteString(")")
return resBuilder.String()
}