Mercurial > gemma

annotate pkg/xlsx/sql.go @ 5710:37c8feeecb4d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged branch sr-v2 into default.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 20 Feb 2024 21:28:56 +0100
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 }