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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
|
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)
}
}
// TestImportSimpleTemplate uses importXLSXtoDB() to import data from a
// populated template and then uses the sqlite3 executible to test the
// contents of the database. This does not test datamaps functionality
// in querying data in the database.
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 in test_template.xlsm but got %s", test.value, got_s)
}
}
}
// TestImportToDB uses ImportToDB() to import data from a
// directory of populated templates and then uses the sqlite3 executible to test the
// contents of the database. This does not test datamaps functionality
// in querying data in the database.
func TestImportToDB(t *testing.T) {
var tests = []struct {
filename string
sheet string
cellref string
value string
}{
{"test_template.xlsm", "Introduction", "A1", "10"},
{"test_template.xlsm", "Introduction", "C9", "Test Department"},
{"test_template.xlsm", "Introduction", "C22", "VUNT"},
{"test_template.xlsm", "Introduction", "J9", "Greedy Parrots"},
{"test_template.xlsm", "Summary", "B3", "This is a string"},
{"test_template.xlsm", "Summary", "B4", "2.2"},
{"test_template.xlsm", "Another Sheet", "N34", "23"},
{"test_template.xlsm", "Another Sheet", "DI15", "Rabbit Helga"},
{"test_template.xlsx", "Introduction", "A1", "10"},
{"test_template.xlsx", "Introduction", "C9", "Test Department"},
{"test_template.xlsx", "Introduction", "C22", "VUNT"},
{"test_template.xlsx", "Introduction", "J9", "Greedy Parrots"},
{"test_template.xlsx", "Summary", "B3", "This is a string"},
{"test_template.xlsx", "Summary", "B4", "2.2"},
{"test_template.xlsx", "Another Sheet", "N34", "23"},
{"test_template.xlsx", "Another Sheet", "DI15", "Rabbit Helga"},
{"test_template2.xlsx", "Introduction", "A1", "10"},
{"test_template2.xlsx", "Introduction", "C9", "Test Department"},
{"test_template2.xlsx", "Introduction", "C22", "VUNT"},
{"test_template2.xlsx", "Introduction", "J9", "Greedy Parrots"},
{"test_template2.xlsx", "Summary", "B3", "This is a string"},
{"test_template2.xlsx", "Summary", "B4", "2.2"},
{"test_template2.xlsx", "Another Sheet", "N34", "23"},
{"test_template2.xlsx", "Another Sheet", "DI15", "Rabbit Helga"},
{"test_template3.xlsx", "Introduction", "A1", "10"},
{"test_template3.xlsx", "Introduction", "C9", "Test Department"},
{"test_template3.xlsx", "Introduction", "C22", "VUNT"},
{"test_template3.xlsx", "Introduction", "J9", "Greedy Parrots"},
{"test_template3.xlsx", "Summary", "B3", "This is a string"},
{"test_template3.xlsx", "Summary", "B4", "2.2"},
{"test_template3.xlsx", "Another Sheet", "N34", "23"},
{"test_template3.xlsx", "Another Sheet", "DI15",
"Printers run amok in the land of carnivores when bacchus rings 1009.ff faiioif !!!]=-=-1290909"},
}
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=%q
AND datamap_line.cellref=%q
AND datamap_line.sheet=%q
AND return_data.dml_id=datamap_line.id);`, test.filename, 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 value %q in file %s sheet %s %s but got %s",
test.value, test.filename, test.sheet, test.cellref, 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) {
// }
|
