/
database.go
194 lines (163 loc) · 5.85 KB
/
database.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
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
// Package database manages storage
package database
import (
"fmt"
"io"
"os"
"strconv"
"github.com/jmoiron/sqlx"
"github.com/olekukonko/tablewriter"
// The database is sqlite3
_ "github.com/mattn/go-sqlite3"
)
// Database implements methods to perform operations on the database.
type Database struct {
File string
DB *sqlx.DB
}
// QueryOptions represents the options you can have for a query and how the result will be rendered
type QueryOptions struct {
// Writer is where the result is sent to
Writer io.Writer
// Query is executed on the database
Query string
// MergeCells enables the merge of cells with identical values
MergeCells bool
// RowLine enables a line on each row of the table
RowLine bool
// Caption sets a caption for the table
Caption string
// Render enables the rendering of the table output
Render bool
}
// QueryResponse represents the response from a query
type QueryResponse struct {
Rows [][]string
ColumnNames []string
Table *tablewriter.Table
}
// Contributions is a slice of contribution objects
type Contributions []Contribution
// Contribution is an activity or trigger created for Flogo
type Contribution struct {
Ref string `json:"ref"`
Name string `json:"name"`
ContributionType string `json:"type"`
SourceURL string
Author string `json:"author"`
UploadedOn string
ShowcaseEnabled bool
Description string `json:"description"`
Version string `json:"version"`
Title string `json:"title"`
Homepage string `json:"homepage"`
Legacy bool
}
// OpenSession creates a new reference to an SQLite database. If the file cannot be found an exception will be returned.
func OpenSession(file string) (*Database, error) {
// Validate the file exists
_, err := os.Stat(file)
if err != nil {
return nil, fmt.Errorf("error locating database file: %s", err.Error())
}
// Connect to the database
dbase, err := sqlx.Open("sqlite3", file)
if err != nil {
return nil, fmt.Errorf("error opening connection to database: %s", err.Error())
}
return &Database{File: file, DB: dbase}, nil
}
// MustOpenSession is like OpenSession but panics if the session cannot be created.
func MustOpenSession(file string) *Database {
db, err := OpenSession(file)
if err != nil {
panic(err)
}
return db
}
// Initialize creates the new database structure. This method must be called if you're starting with a brand new database.
// The sourceurl (github url) is the primary key as there can be only one activity in a location.
func (db *Database) Initialize() error {
return db.Exec(`create table contributions(
ref text,
name text,
contributiontype text,
sourceurl text not null primary key,
author text,
uploadedon text,
showcaseenabled text,
description text,
version text,
title text,
homepage text,
legacy text)
`)
}
// Close closes the database and prevents new queries from starting. Close then waits for all queries that have started processing on the server to finish.
func (db *Database) Close() error {
return db.DB.Close()
}
// Exec executes a query without returning any rows. An error is returned only when the database throws an error.
func (db *Database) Exec(query string) error {
_, err := db.DB.Exec(query)
return err
}
// UpdateContribution updates the data for activities and triggers in the database,
func (db *Database) UpdateContribution(c Contribution) error {
q := fmt.Sprintf("update contributions set ref=\"%s\", name=\"%s\", contributiontype=\"%s\", author=\"%s\", uploadedon=\"%s\", showcaseenabled=\"%s\", description=\"%s\", version=\"%s\", title=\"%s\", homepage=\"%s\", legacy=\"%s\" where sourceurl=\"%s\"", c.Ref, c.Name, c.ContributionType, c.Author, c.UploadedOn, strconv.FormatBool(c.ShowcaseEnabled), c.Description, c.Version, c.Title, c.Homepage, strconv.FormatBool(c.Legacy), c.SourceURL)
return db.Exec(q)
}
// InsertContribution inserts activities and triggers into the database,
func (db *Database) InsertContribution(c Contribution) error {
q := fmt.Sprintf("insert into contributions(ref, name, contributiontype, sourceurl, author, uploadedon, showcaseenabled, description, version, title, homepage, legacy) values(\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\")", c.Ref, c.Name, c.ContributionType, c.SourceURL, c.Author, c.UploadedOn, strconv.FormatBool(c.ShowcaseEnabled), c.Description, c.Version, c.Title, c.Homepage, strconv.FormatBool(c.Legacy))
return db.Exec(q)
}
// Query run a query on the database and prints the result in a table.
func (db *Database) Query(opts QueryOptions) (QueryResponse, error) {
queryResponse := QueryResponse{}
// Execute the query
rows, err := db.DB.Queryx(opts.Query)
if err != nil {
return queryResponse, fmt.Errorf("error while executing query: %s", err.Error())
}
defer rows.Close()
// Get the column names
colnames, _ := rows.Columns()
// Prepare the output table
table := tablewriter.NewWriter(opts.Writer)
table.SetHeader(colnames)
table.SetAutoMergeCells(opts.MergeCells)
table.SetRowLine(opts.RowLine)
if len(opts.Caption) > 0 {
table.SetCaption(true, opts.Caption)
}
// Prepare a result array
var resultArray [][]string
// Loop over the result
for rows.Next() {
cols, _ := rows.SliceScan()
tempStringArray := make([]string, len(cols))
for idx := range cols {
switch v := cols[idx].(type) {
case int64:
tempStringArray[idx] = strconv.Itoa(int(v))
case string:
tempStringArray[idx] = v
case nil:
tempStringArray[idx] = ""
default:
tempStringArray[idx] = string(v.([]uint8))
}
}
table.Append(tempStringArray)
resultArray = append(resultArray, tempStringArray)
}
// Print the table
if opts.Render {
table.Render()
}
queryResponse.ColumnNames = colnames
queryResponse.Rows = resultArray
queryResponse.Table = table
return queryResponse, nil
}