| 1 | package sheets |
| 2 | |
| 3 | import ( |
| 4 | "bytes" |
| 5 | "fmt" |
| 6 | "net/http" |
| 7 | "net/http/httptest" |
| 8 | "net/url" |
| 9 | "strings" |
| 10 | "testing" |
| 11 | ) |
| 12 | |
| 13 | // seedColumn writes one value per row into column 0. |
| 14 | func seedColumn(t *testing.T, app *App, cookies []*http.Cookie, cells string, values []string) { |
| 15 | t.Helper() |
| 16 | for i, v := range values { |
| 17 | rec := postCell(t, app, cookies, cells, fmt.Sprintf(`{"r":%d,"c":0,"raw":%q}`, i, v)) |
| 18 | if rec.Code != http.StatusOK { |
| 19 | t.Fatalf("seeding row %d = %d: %s", i, rec.Code, rec.Body.String()) |
| 20 | } |
| 21 | } |
| 22 | } |
| 23 | |
| 24 | func tablePath(s SheetSummary) string { return fmt.Sprintf("/sheets/%d/table", s.ID) } |
| 25 | |
| 26 | func saveViewForm(t *testing.T, app *App, s SheetSummary, cookies []*http.Cookie, form url.Values) { |
| 27 | t.Helper() |
| 28 | rec := do(t, app, as(postForm(fmt.Sprintf("/sheets/%d/view", s.ID), form), cookies)) |
| 29 | if rec.Code != http.StatusSeeOther { |
| 30 | t.Fatalf("saving the view = %d: %s", rec.Code, rec.Body.String()) |
| 31 | } |
| 32 | } |
| 33 | |
| 34 | // rowOrder reads the row numbers out of a rendered table, in order. |
| 35 | func rowOrder(body string) []string { |
| 36 | var out []string |
| 37 | for _, part := range strings.Split(body, `<th scope="row">`)[1:] { |
| 38 | if i := strings.Index(part, "<"); i > 0 { |
| 39 | out = append(out, part[:i]) |
| 40 | } |
| 41 | } |
| 42 | return out |
| 43 | } |
| 44 | |
| 45 | // The single most obvious way a spreadsheet sort can be wrong. |
| 46 | func TestSortIsNumericNotLexicographic(t *testing.T) { |
| 47 | if !less("9", "10") { |
| 48 | t.Fatal("9 sorts after 10; the sort is comparing numbers as strings") |
| 49 | } |
| 50 | if less("10", "9") { |
| 51 | t.Fatal("10 sorts before 9") |
| 52 | } |
| 53 | // Text still compares as text, case-insensitively. |
| 54 | if !less("apple", "Banana") { |
| 55 | t.Error("text does not compare case-insensitively") |
| 56 | } |
| 57 | // Numbers before text, and empties last, so a sorted column does |
| 58 | // not open with a screen of blanks. |
| 59 | if !less("1", "apple") { |
| 60 | t.Error("text sorts before numbers") |
| 61 | } |
| 62 | if less("", "apple") { |
| 63 | t.Error("an empty cell sorts before a value") |
| 64 | } |
| 65 | } |
| 66 | |
| 67 | func TestSortReordersWhatIsShown(t *testing.T) { |
| 68 | app, cookies := signIn(t) |
| 69 | s, cells := newSheet(t, app, cookies, "Budget") |
| 70 | seedColumn(t, app, cookies, cells, []string{"30", "10", "20"}) |
| 71 | |
| 72 | saveViewForm(t, app, s, cookies, url.Values{"sortCol": {"0"}}) |
| 73 | body := do(t, app, as(httptest.NewRequest(http.MethodGet, tablePath(s), nil), cookies)).Body.String() |
| 74 | |
| 75 | order := rowOrder(body) |
| 76 | if len(order) < 3 { |
| 77 | t.Fatalf("only %d rows rendered", len(order)) |
| 78 | } |
| 79 | // Rows 1, 2, 3 hold 30, 10, 20 — so ascending shows 2, 3, 1. |
| 80 | if got := strings.Join(order[:3], ","); got != "2,3,1" { |
| 81 | t.Fatalf("sorted row order = %s, want 2,3,1", got) |
| 82 | } |
| 83 | } |
| 84 | |
| 85 | func TestSortDescending(t *testing.T) { |
| 86 | app, cookies := signIn(t) |
| 87 | s, cells := newSheet(t, app, cookies, "Budget") |
| 88 | seedColumn(t, app, cookies, cells, []string{"30", "10", "20"}) |
| 89 | |
| 90 | saveViewForm(t, app, s, cookies, url.Values{"sortCol": {"0"}, "sortDesc": {"1"}}) |
| 91 | order := rowOrder(do(t, app, as(httptest.NewRequest(http.MethodGet, tablePath(s), nil), cookies)).Body.String()) |
| 92 | if got := strings.Join(order[:3], ","); got != "1,3,2" { |
| 93 | t.Fatalf("descending row order = %s, want 1,3,2", got) |
| 94 | } |
| 95 | } |
| 96 | |
| 97 | func TestFilterHidesRows(t *testing.T) { |
| 98 | app, cookies := signIn(t) |
| 99 | s, cells := newSheet(t, app, cookies, "Budget") |
| 100 | seedColumn(t, app, cookies, cells, []string{"alpha", "beta", "gamma"}) |
| 101 | |
| 102 | // "ma", not "a": every one of alpha, beta and gamma contains an |
| 103 | // "a", so filtering on it would pass whether the filter worked or |
| 104 | // not — a fixture that cannot fail. |
| 105 | saveViewForm(t, app, s, cookies, url.Values{ |
| 106 | "filterCol": {"0"}, "filterOp": {"contains"}, "filterVal": {"ma"}, |
| 107 | }) |
| 108 | body := do(t, app, as(httptest.NewRequest(http.MethodGet, tablePath(s), nil), cookies)).Body.String() |
| 109 | if !strings.Contains(body, "gamma") { |
| 110 | t.Error("the filter hid a row that matches") |
| 111 | } |
| 112 | for _, hidden := range []string{"alpha", "beta"} { |
| 113 | if strings.Contains(body, hidden) { |
| 114 | t.Errorf("the filter kept %q, which does not match", hidden) |
| 115 | } |
| 116 | } |
| 117 | } |
| 118 | |
| 119 | func TestNumericFilters(t *testing.T) { |
| 120 | app, cookies := signIn(t) |
| 121 | s, cells := newSheet(t, app, cookies, "Budget") |
| 122 | seedColumn(t, app, cookies, cells, []string{"5", "50", "500"}) |
| 123 | |
| 124 | saveViewForm(t, app, s, cookies, url.Values{ |
| 125 | "filterCol": {"0"}, "filterOp": {"gt"}, "filterVal": {"40"}, |
| 126 | }) |
| 127 | body := do(t, app, as(httptest.NewRequest(http.MethodGet, tablePath(s), nil), cookies)).Body.String() |
| 128 | if strings.Contains(body, ">5<") { |
| 129 | t.Error("gt 40 kept the row holding 5") |
| 130 | } |
| 131 | for _, want := range []string{"50", "500"} { |
| 132 | if !strings.Contains(body, want) { |
| 133 | t.Errorf("gt 40 hid the row holding %s", want) |
| 134 | } |
| 135 | } |
| 136 | } |
| 137 | |
| 138 | // The constraint that shaped the whole feature: reordering rows would |
| 139 | // change what every formula points at, because calc's keys are |
| 140 | // position-derived. So the sort must not move anything stored. |
| 141 | func TestSortingDoesNotMoveStoredRowsOrBreakFormulas(t *testing.T) { |
| 142 | app, cookies := signIn(t) |
| 143 | s, cells := newSheet(t, app, cookies, "Budget") |
| 144 | seedColumn(t, app, cookies, cells, []string{"30", "10", "20"}) |
| 145 | // B1 = A1 * 2 = 60. |
| 146 | postCell(t, app, cookies, cells, `{"r":0,"c":1,"raw":"=A1*2"}`) |
| 147 | |
| 148 | before := decodeGrid(t, do(t, app, as(httptest.NewRequest(http.MethodGet, cells, nil), cookies))) |
| 149 | saveViewForm(t, app, s, cookies, url.Values{"sortCol": {"0"}}) |
| 150 | after := decodeGrid(t, do(t, app, as(httptest.NewRequest(http.MethodGet, cells, nil), cookies))) |
| 151 | |
| 152 | find := func(g gridResponse, r, c int) string { |
| 153 | for _, cell := range g.Cells { |
| 154 | if cell.R == r && cell.C == c { |
| 155 | return cell.Display |
| 156 | } |
| 157 | } |
| 158 | return "" |
| 159 | } |
| 160 | // The grid — the stored order — is untouched by a view. |
| 161 | for _, rc := range [][2]int{{0, 0}, {1, 0}, {2, 0}, {0, 1}} { |
| 162 | if b, a := find(before, rc[0], rc[1]), find(after, rc[0], rc[1]); b != a { |
| 163 | t.Errorf("sorting moved the stored cell (%d,%d): %q became %q", rc[0], rc[1], b, a) |
| 164 | } |
| 165 | } |
| 166 | if got := find(after, 0, 1); got != "60" { |
| 167 | t.Fatalf("=A1*2 now reads %q, want 60 — the formula's reference moved", got) |
| 168 | } |
| 169 | } |
| 170 | |
| 171 | // A filter value IS a cell value (spec §2.1), so the criteria are |
| 172 | // sealed like anything else somebody typed. |
| 173 | func TestViewCriteriaAreSealedAtRest(t *testing.T) { |
| 174 | app, cookies := signIn(t) |
| 175 | s, _ := newSheet(t, app, cookies, "Budget") |
| 176 | |
| 177 | const secret = "ConfidentialThreshold" |
| 178 | saveViewForm(t, app, s, cookies, url.Values{ |
| 179 | "filterCol": {"0"}, "filterOp": {"contains"}, "filterVal": {secret}, |
| 180 | }) |
| 181 | |
| 182 | var raw []byte |
| 183 | if err := app.db.Raw("SELECT spec_sealed FROM tab_views LIMIT 1").Row().Scan(&raw); err != nil { |
| 184 | t.Fatalf("read back: %v", err) |
| 185 | } |
| 186 | if len(raw) == 0 { |
| 187 | t.Fatal("no sealed spec was stored") |
| 188 | } |
| 189 | if bytes.Contains(raw, []byte(secret)) { |
| 190 | t.Fatal("the filter value reached the database in plaintext") |
| 191 | } |
| 192 | } |
| 193 | |
| 194 | func TestTheViewPersistsAcrossRequests(t *testing.T) { |
| 195 | app, cookies := signIn(t) |
| 196 | s, cells := newSheet(t, app, cookies, "Budget") |
| 197 | seedColumn(t, app, cookies, cells, []string{"30", "10"}) |
| 198 | |
| 199 | saveViewForm(t, app, s, cookies, url.Values{"sortCol": {"0"}, "sortDesc": {"1"}}) |
| 200 | got := app.loadView(s.TabID) |
| 201 | if got.SortCol != 0 || !got.SortDsc { |
| 202 | t.Fatalf("stored view = %+v, want column 0 descending", got) |
| 203 | } |
| 204 | } |
| 205 | |
| 206 | // A viewer must not change what everybody else sees. |
| 207 | func TestAViewerCannotChangeTheView(t *testing.T) { |
| 208 | app, cookies := signIn(t) |
| 209 | s, _ := newSheet(t, app, cookies, "Budget") |
| 210 | guestOn(t, app, s.ID, "ada@elsewhere.example", SheetViewer) |
| 211 | adaCookies := signInAs(t, app, "ada@elsewhere.example") |
| 212 | |
| 213 | rec := do(t, app, as(postForm(fmt.Sprintf("/sheets/%d/view", s.ID), |
| 214 | url.Values{"sortCol": {"0"}}), adaCookies)) |
| 215 | if rec.Code != http.StatusNotFound { |
| 216 | t.Fatalf("a viewer changed the view: %d", rec.Code) |
| 217 | } |
| 218 | if app.loadView(s.TabID).Active() { |
| 219 | t.Fatal("a viewer's submission was stored anyway") |
| 220 | } |
| 221 | } |
| 222 | |
| 223 | func TestAnInvalidSpecLeavesTheStoredOneAlone(t *testing.T) { |
| 224 | app, cookies := signIn(t) |
| 225 | s, _ := newSheet(t, app, cookies, "Budget") |
| 226 | |
| 227 | saveViewForm(t, app, s, cookies, url.Values{"sortCol": {"1"}}) |
| 228 | // An unknown comparison. Losing somebody's view because they |
| 229 | // mistyped is a worse answer than ignoring the submission. |
| 230 | saveViewForm(t, app, s, cookies, url.Values{ |
| 231 | "filterCol": {"0"}, "filterOp": {"regex"}, "filterVal": {"x"}, |
| 232 | }) |
| 233 | if got := app.loadView(s.TabID); got.SortCol != 1 { |
| 234 | t.Fatalf("an invalid submission cleared the stored view: %+v", got) |
| 235 | } |
| 236 | } |
| 237 | |
| 238 | func TestViewValidationBounds(t *testing.T) { |
| 239 | for _, spec := range []ViewSpec{ |
| 240 | {SortCol: tableCols, FilterCol: -1}, |
| 241 | {SortCol: -2, FilterCol: -1}, |
| 242 | {SortCol: -1, FilterCol: tableCols}, |
| 243 | {SortCol: -1, FilterCol: 0, FilterOp: "regex"}, |
| 244 | {SortCol: -1, FilterCol: 0, FilterOp: "contains", FilterVal: strings.Repeat("x", maxRawBytes+1)}, |
| 245 | } { |
| 246 | if err := spec.Validate(); err == nil { |
| 247 | t.Errorf("Validate accepted %+v", spec) |
| 248 | } |
| 249 | } |
| 250 | if err := (ViewSpec{SortCol: 0, FilterCol: 1, FilterOp: "gt", FilterVal: "5"}).Validate(); err != nil { |
| 251 | t.Errorf("Validate refused a good spec: %v", err) |
| 252 | } |
| 253 | } |
| 254 | |
| 255 | func TestSortAndFilterAreScriptless(t *testing.T) { |
| 256 | app, cookies := signIn(t) |
| 257 | s, _ := newSheet(t, app, cookies, "Budget") |
| 258 | |
| 259 | body := withoutScript(do(t, app, |
| 260 | as(httptest.NewRequest(http.MethodGet, tablePath(s), nil), cookies)).Body.String()) |
| 261 | for _, want := range []string{ |
| 262 | fmt.Sprintf(`action="/sheets/%d/view"`, s.ID), |
| 263 | `name="sortCol"`, `name="filterCol"`, `name="filterOp"`, `name="filterVal"`, |
| 264 | } { |
| 265 | if !strings.Contains(body, want) { |
| 266 | t.Errorf("with scripts stripped, %q is gone", want) |
| 267 | } |
| 268 | } |
| 269 | } |
| 270 | |
amadan