forked from knadh/listmonk
-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.go
132 lines (113 loc) · 5.47 KB
/
queries.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
package main
import (
"context"
"database/sql"
"fmt"
"github.com/jmoiron/sqlx"
"github.com/lib/pq"
)
// Queries contains all prepared SQL queries.
type Queries struct {
GetDashboardStats *sqlx.Stmt `query:"get-dashboard-stats"`
InsertSubscriber *sqlx.Stmt `query:"insert-subscriber"`
UpsertSubscriber *sqlx.Stmt `query:"upsert-subscriber"`
UpsertBlacklistSubscriber *sqlx.Stmt `query:"upsert-blacklist-subscriber"`
GetSubscriber *sqlx.Stmt `query:"get-subscriber"`
GetSubscribersByEmails *sqlx.Stmt `query:"get-subscribers-by-emails"`
GetSubscriberLists *sqlx.Stmt `query:"get-subscriber-lists"`
UpdateSubscriber *sqlx.Stmt `query:"update-subscriber"`
BlacklistSubscribers *sqlx.Stmt `query:"blacklist-subscribers"`
AddSubscribersToLists *sqlx.Stmt `query:"add-subscribers-to-lists"`
DeleteSubscriptions *sqlx.Stmt `query:"delete-subscriptions"`
UnsubscribeSubscribersFromLists *sqlx.Stmt `query:"unsubscribe-subscribers-from-lists"`
DeleteSubscribers *sqlx.Stmt `query:"delete-subscribers"`
Unsubscribe *sqlx.Stmt `query:"unsubscribe"`
ExportSubscriberData *sqlx.Stmt `query:"export-subscriber-data"`
// Non-prepared arbitrary subscriber queries.
QuerySubscribers string `query:"query-subscribers"`
QuerySubscribersTpl string `query:"query-subscribers-template"`
DeleteSubscribersByQuery string `query:"delete-subscribers-by-query"`
AddSubscribersToListsByQuery string `query:"add-subscribers-to-lists-by-query"`
BlacklistSubscribersByQuery string `query:"blacklist-subscribers-by-query"`
DeleteSubscriptionsByQuery string `query:"delete-subscriptions-by-query"`
UnsubscribeSubscribersFromListsByQuery string `query:"unsubscribe-subscribers-from-lists-by-query"`
CreateList *sqlx.Stmt `query:"create-list"`
GetLists *sqlx.Stmt `query:"get-lists"`
UpdateList *sqlx.Stmt `query:"update-list"`
UpdateListsDate *sqlx.Stmt `query:"update-lists-date"`
DeleteLists *sqlx.Stmt `query:"delete-lists"`
CreateCampaign *sqlx.Stmt `query:"create-campaign"`
QueryCampaigns *sqlx.Stmt `query:"query-campaigns"`
GetCampaign *sqlx.Stmt `query:"get-campaign"`
GetCampaignForPreview *sqlx.Stmt `query:"get-campaign-for-preview"`
GetCampaignStats *sqlx.Stmt `query:"get-campaign-stats"`
GetCampaignStatus *sqlx.Stmt `query:"get-campaign-status"`
NextCampaigns *sqlx.Stmt `query:"next-campaigns"`
NextCampaignSubscribers *sqlx.Stmt `query:"next-campaign-subscribers"`
GetOneCampaignSubscriber *sqlx.Stmt `query:"get-one-campaign-subscriber"`
UpdateCampaign *sqlx.Stmt `query:"update-campaign"`
UpdateCampaignStatus *sqlx.Stmt `query:"update-campaign-status"`
UpdateCampaignCounts *sqlx.Stmt `query:"update-campaign-counts"`
RegisterCampaignView *sqlx.Stmt `query:"register-campaign-view"`
DeleteCampaign *sqlx.Stmt `query:"delete-campaign"`
InsertMedia *sqlx.Stmt `query:"insert-media"`
GetMedia *sqlx.Stmt `query:"get-media"`
DeleteMedia *sqlx.Stmt `query:"delete-media"`
CreateTemplate *sqlx.Stmt `query:"create-template"`
GetTemplates *sqlx.Stmt `query:"get-templates"`
UpdateTemplate *sqlx.Stmt `query:"update-template"`
SetDefaultTemplate *sqlx.Stmt `query:"set-default-template"`
DeleteTemplate *sqlx.Stmt `query:"delete-template"`
CreateLink *sqlx.Stmt `query:"create-link"`
RegisterLinkClick *sqlx.Stmt `query:"register-link-click"`
// GetStats *sqlx.Stmt `query:"get-stats"`
}
// connectDB initializes a database connection.
func connectDB(host string, port int, user, pwd, dbName string, sslMode string) (*sqlx.DB, error) {
db, err := sqlx.Connect("postgres",
fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=%s", host, port, user, pwd, dbName, sslMode))
if err != nil {
return nil, err
}
return db, nil
}
// compileSubscriberQueryTpl takes a arbitrary WHERE expressions
// to filter subscribers from the subscribers table and prepares a query
// out of it using the raw `query-subscribers-template` query template.
// While doing this, a readonly transaction is created and the query is
// dry run on it to ensure that it is indeed readonly.
func (q *Queries) compileSubscriberQueryTpl(exp string, db *sqlx.DB) (string, error) {
tx, err := db.BeginTxx(context.Background(), &sql.TxOptions{ReadOnly: true})
if err != nil {
return "", err
}
// Perform the dry run.
if exp != "" {
exp = " AND " + exp
}
stmt := fmt.Sprintf(q.QuerySubscribersTpl, exp)
if _, err := tx.Exec(stmt, true, pq.Int64Array{}); err != nil {
tx.Rollback()
return "", err
}
return stmt, nil
}
// compileSubscriberQueryTpl takes a arbitrary WHERE expressions and a subscriber
// query template that depends on the filter (eg: delete by query, blacklist by query etc.)
// combines and executes them.
func (q *Queries) execSubscriberQueryTpl(exp, tpl string, listIDs []int64, db *sqlx.DB, args ...interface{}) error {
// Perform a dry run.
filterExp, err := q.compileSubscriberQueryTpl(exp, db)
if err != nil {
return err
}
if len(listIDs) == 0 {
listIDs = pq.Int64Array{}
}
// First argument is the boolean indicating if the query is a dry run.
a := append([]interface{}{false, pq.Int64Array(listIDs)}, args...)
if _, err := db.Exec(fmt.Sprintf(tpl, filterExp), a...); err != nil {
return err
}
return nil
}