/
main.go
121 lines (104 loc) · 3.04 KB
/
main.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
package main
import (
"fmt"
"log"
"strings"
"github.com/jbsmith7741/go-tools/appenderr"
"github.com/jmoiron/sqlx"
_ "github.com/go-sql-driver/mysql"
_ "github.com/lib/pq"
tools "github.com/pcelvng/task-tools"
"github.com/pcelvng/task-tools/bootstrap"
"github.com/pcelvng/task-tools/file"
)
const (
taskType = "sql_readx"
desc = `extract data from a mysql or postgres table or execute a command
info params
- origin: (alternative to query) - path to a file containing a sql statement
- query: (instead of file) - statement to execute
- exec: execute statement instead of running as a query
- dest: (required for query) - file path to where the file should be written
- table: (required with field) - table (schema.table) to read from
- field: - map of columns of fields.
Query: list of columns to read from and the json field that should be used to write the values.
Exec: key to be replaced with value in statment. NOTE: key are wrapped with brackets {key} -> value
example
{"task":"sql_readx","info":"?dest=./data.json&table=report.impressions&field=id:my_id|date:date"}
{"task":"sql_readx","info":"./query.sql?dest=./data.json"}
{"task":"sql_readx","info":"./query.sql?exec&field=date:2020-01-01"}
`
)
type options struct {
DBOptions `toml:"sql"`
FOpts *file.Options `toml:"file"`
db *sqlx.DB
}
type DBOptions struct {
Type string `toml:"type" commented:"true"`
Username string `toml:"username" commented:"true"`
Password string `toml:"password" commented:"true"`
Host string `toml:"host" comment:"host can be 'host:port', 'host', 'host:' or ':port'"`
DBName string `toml:"dbname"`
}
func (o *options) Validate() error {
errs := appenderr.New()
if o.Host == "" {
errs.Addf("missing db host")
}
if o.DBName == "" {
errs.Addf("missing db name")
}
return errs.ErrOrNil()
}
// connectDB creates a connection to the database
func (o *options) connectDB() (err error) {
var driverName string
var dsn string
switch o.Type {
case "mysql":
dsn = fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=true", o.Username, o.Password, o.Host, o.DBName)
driverName = "mysql"
case "postgres":
driverName = "postgres"
host, port := o.Host, ""
if v := strings.Split(o.Host, ":"); len(v) > 1 {
host, port = v[0], v[1]
}
dsn = fmt.Sprintf("host=%s dbname=%s sslmode=disable", host, o.DBName)
if o.Username != "" {
dsn += " user=" + o.Username
}
if o.Password != "" {
dsn += " password=" + o.Password
}
if port != "" {
dsn += " port=" + port
}
default:
return fmt.Errorf("unknown db type %s", o.Type)
}
o.db, err = sqlx.Connect(driverName, dsn)
return err
}
func main() {
opts := &options{
FOpts: file.NewOptions(),
DBOptions: DBOptions{
Type: "mysql",
Username: "user",
Password: "pass",
Host: "127.0.0.1:3306",
DBName: "db",
},
}
app := bootstrap.NewWorkerApp(taskType, opts.NewWorker, opts).
Description(desc).
Version(tools.Version)
app.Initialize()
// setup database connection
if err := opts.connectDB(); err != nil {
log.Fatal("db connect: ", err)
}
app.Run()
}