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
195
196
197
|
package datamaps
import (
"database/sql"
"fmt"
"os"
"os/exec"
"strings"
"testing"
)
var singleTarget string = "./testdata/test_template.xlsm"
var opts = Options{
DBPath: "./testdata/test.db",
DMName: "First Datamap",
DMPath: "./testdata/datamap_matches_test_template.csv",
XLSXPath: "./testdata/",
}
func dbSetup() (*sql.DB, error) {
db, err := setupDB("./testdata/test.db")
if err != nil {
return nil, err
}
return db, nil
}
func dbTeardown(db *sql.DB) {
db.Close()
os.Remove("./testdata/test.db")
}
func TestOpenSQLiteFile(t *testing.T) {
db, err := dbSetup()
if err != nil {
t.Fatal(err)
}
defer dbTeardown(db)
stmt := `insert into datamap(id, name) values(1,'cock')`
_, err = db.Exec(stmt)
if err != nil {
t.Errorf("Cannot add record to db")
}
rows, err := db.Query("select name from datamap")
if err != nil {
t.Errorf("Cannot run select statement")
}
defer rows.Close()
for rows.Next() {
var name string
err = rows.Scan(&name)
if err != nil {
t.Errorf("Cannot scan resulting row")
}
}
}
func TestDatamapGoesIntoDB(t *testing.T) {
db, err := dbSetup()
if err != nil {
t.Fatal(err)
}
defer dbTeardown(db)
if err := DatamapToDB(&opts); err != nil {
t.Errorf("unable to write datamap to database file because %v", err)
}
}
func TestImportSimpleTemplate(t *testing.T) {
var tests = []struct {
sheet string
cellref string
value string
}{
{"Introduction", "A1", "10"},
{"Introduction", "C9", "Test Department"},
{"Introduction", "C22", "VUNT"},
{"Introduction", "J9", "Greedy Parrots"},
{"Summary", "B3", "This is a string"},
{"Summary", "B4", "2.2"},
{"Another Sheet", "N34", "23"},
{"Another Sheet", "DI15", "Rabbit Helga"},
}
db, err := dbSetup()
if err != nil {
t.Fatal(err)
}
defer dbTeardown(db)
// We need a datamap in there.
if err := DatamapToDB(&opts); err != nil {
t.Fatalf("cannot open %s", opts.DMPath)
}
if err := importXLSXtoDB(opts.DMName, "TEST RETURN", singleTarget, db); err != nil {
t.Fatalf("Something wrong: %v", err)
}
for _, test := range tests {
sql := fmt.Sprintf(`SELECT return_data.value FROM return_data, datamap_line
WHERE
(return_data.filename='test_template.xlsm'
AND datamap_line.cellref=%q
AND datamap_line.sheet=%q
AND return_data.dml_id=datamap_line.id);`, test.cellref, test.sheet)
got, err := exec.Command("sqlite3", opts.DBPath, sql).Output()
if err != nil {
t.Fatalf("something wrong %v", err)
}
got_s := strings.TrimSuffix(string(got), "\n")
if strings.Compare(got_s, test.value) != 0 {
t.Errorf("we wanted %s but got %s", test.value, got_s)
}
}
}
func TestImportToDB(t *testing.T) {
var tests = []struct {
sheet string
cellref string
value string
}{
{"Introduction", "A1", "10"},
{"Introduction", "C9", "Test Department"},
{"Introduction", "C22", "VUNT"},
{"Introduction", "J9", "Greedy Parrots"},
{"Summary", "B3", "This is a string"},
{"Summary", "B4", "2.2"},
{"Another Sheet", "N34", "23"},
{"Another Sheet", "DI15", "Rabbit Helga"},
}
db, err := dbSetup()
if err != nil {
t.Fatal(err)
}
// We need a datamap in there.
if err := DatamapToDB(&opts); err != nil {
t.Fatalf("cannot open %s", opts.DMPath)
}
defer dbTeardown(db)
if err := ImportToDB(&opts); err != nil {
t.Fatal(err)
}
for _, test := range tests {
sql := fmt.Sprintf(`SELECT return_data.value FROM return_data, datamap_line
WHERE
(return_data.filename='test_template.xlsm'
AND datamap_line.cellref=%q
AND datamap_line.sheet=%q
AND return_data.dml_id=datamap_line.id);`, test.cellref, test.sheet)
got, err := exec.Command("sqlite3", opts.DBPath, sql).Output()
if err != nil {
t.Fatalf("something wrong %v", err)
}
got_s := strings.TrimSuffix(string(got), "\n")
if strings.Compare(got_s, test.value) != 0 {
t.Errorf("we wanted %s but got %s", test.value, got_s)
}
}
}
// TODO:
// USING THE INDEX TO tests STRUCT WE COULD DO ALL THESE IN TEST ABOVE
// Returns useful error messages when querying for stuff not in datamap
// func TestImportSimpleQueryValueNotInDatamap(t *testing.T) {
// var tests = []struct {
// sheet string
// cellref string
// value string
// }{
// {"Summary", "B2", "20/10/19"}, // this is not referenced in datamap
// }
// }
// TODO:
// When a date is returned from the spreadsheet it is an integer and needs
// to be handled appropriately.
// func TestValuesReturnedAsDates(t *testing.T) {
// }
|
