Mercurial > gemma

annotate pkg/xlsx/sql.go @ 5591:0011f50cf216 surveysperbottleneckid

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed no longer used alternative api for surveys/ endpoint. As bottlenecks in the summary for SR imports are now identified by their id and no longer by the (not guarantied to be unique!) name, there is no longer the need to request survey data by the name+date tuple (which isn't reliable anyway). So the workaround was now reversed.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 06 Apr 2022 13:30:29 +0200
parents 979f139cde4d
children 6270951dda28
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
1 // This is Free Software under GNU Affero General Public License v >= 3.0
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
2 // without warranty, see README.md and license for details.
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
3 //
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
4 // SPDX-License-Identifier: AGPL-3.0-or-later
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
5 // License-Filename: LICENSES/AGPL-3.0.txt
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
6 //
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
7 // Copyright (C) 2021 by via donau
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
8 // – Österreichische Wasserstraßen-Gesellschaft mbH
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
9 // Software engineering by Intevation GmbH
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
10 //
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
11 // Author(s):
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
13
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
14 package xlsx
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
15
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
16 import (
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
17 "context"
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
18 "database/sql"
5347
1fc454da7ee8 XLSX templater: To ease debugging enrich error with SQL statement if it fails.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5333
diff changeset
19 "fmt"
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
20 "strconv"
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
21 "strings"
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
22 )
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
23
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
24 type sqlResult struct {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
25 columns []string
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
26 rows [][]interface{}
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
27 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
28
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
29 func (sr *sqlResult) find(column string) int {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
30 for i, name := range sr.columns {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
31 if name == column {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
32 return i
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
33 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
34 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
35 return -1
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
36 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
37
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
38 func replaceStmt(stmt string) (string, []string) {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
39
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
40 var names []string
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
41
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
42 add := func(name string) int {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
43 for i, n := range names {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
44 if n == name {
5352
979f139cde4d XLSX templater: Fixed indexing of already used vars.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5347
diff changeset
45 return i + 1
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
46 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
47 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
48 names = append(names, name)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
49 return len(names)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
50 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
51
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
52 replace := func(s string) string {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
53 n := add(strings.TrimSpace(s))
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
54 return "$" + strconv.Itoa(n)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
55 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
56
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
57 out := handlebars(stmt, replace)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
58 return out, names
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
59 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
60
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
61 func query(
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
62 ctx context.Context,
5333
6c0f40676984 Run xlsx templating in a read-only transaction.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5321
diff changeset
63 tx *sql.Tx,
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
64 stmt string,
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
65 eval func(string) (interface{}, error),
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
66 ) (*sqlResult, error) {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
67
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
68 nstmt, nargs := replaceStmt(stmt)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
69 args := make([]interface{}, len(nargs))
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
70 for i, n := range nargs {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
71 var err error
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
72 if args[i], err = eval(n); err != nil {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
73 return nil, err
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
74 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
75 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
76
5333
6c0f40676984 Run xlsx templating in a read-only transaction.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5321
diff changeset
77 rs, err := tx.QueryContext(ctx, nstmt, args...)
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
78 if err != nil {
5347
1fc454da7ee8 XLSX templater: To ease debugging enrich error with SQL statement if it fails.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5333
diff changeset
79 return nil, fmt.Errorf("SQL failed: '%s': %v", nstmt, err)
5321
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
80 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
81 defer rs.Close()
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
82
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
83 columns, err := rs.Columns()
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
84 if err != nil {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
85 return nil, err
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
86 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
87 var rows [][]interface{}
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
88
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
89 ptrs := make([]interface{}, len(columns))
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
90
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
91 for rs.Next() {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
92 row := make([]interface{}, len(columns))
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
93 for i := range row {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
94 ptrs[i] = &row[i]
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
95 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
96 if err := rs.Scan(ptrs...); err != nil {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
97 return nil, err
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
98 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
99 rows = append(rows, row)
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
100 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
101
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
102 if err := rs.Err(); err != nil {
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
103 return nil, err
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
104 }
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
105
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
106 return &sqlResult{
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
107 columns: columns,
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
108 rows: rows,
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
109 }, nil
0919946f624b Added a report controller.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
110 }