Goldstein: 1 WIP: add /query page 5 files changed, 128 insertions(+), 0 deletions(-)
Copy & paste the following snippet into your terminal to import this patchset into git:
curl -s https://lists.sr.ht/~bouncepaw/betula/patches/47020/mbox | git am -3Learn more about email & git
This is a prototype patch adding `/query` page for custom SQL queries to Betula. It works, but the UI is sorely lacking: columns (and the table overall) is too thin. `SELECT * FROM Posts` type of queries would probably also benefit from displaying them as post feed instead of a table.
Yeah, this is true. In the body block of template, maybe move the <table> outside of <main>, so it can be as wide as the page is? Or make it horizontally scrollable? Displaying as posts would be great, but it would be fine to implement it later, in a different patch. Looking at column names, and deducing if the result can be shown as a post by that? Sounds doable. Some logging would also be nice. Also, running an empty query results in Betula hanging. The database gets locked, maybe? Have you considered making /query a GET endpoint? This way, the query itself would be written out in the URL, thus making it linkable and bookmarkable. In handlerSQLQuery you are doing SQL stuff. We don't do SQL stuff outside of the db package. Please, move it all there. I think queries_misc.go would be a good place for such a function.
--- db/db.go | 5 +++ web/handlers.go | 92 +++++++++++++++++++++++++++++++++++++++ web/templates.go | 1 + web/views/query.gohtml | 27 ++++++++++++ web/views/skeleton.gohtml | 3 ++ 5 files changed, 128 insertions(+) create mode 100644 web/views/query.gohtml diff --git a/db/db.go b/db/db.go index 5acd8d4..46eeee9 100644 --- a/db/db.go +++ b/db/db.go @@ -71,3 +71,8 @@ func querySingleValue[T any](query string, vals ...any) T { _ = rows.Close() return res } + +func RunArbitrary(query string) (*sql.Rows, error) { + res, err := db.Query(query) + return res, err +} diff --git a/web/handlers.go b/web/handlers.go index b5d953b..ca9bb47 100644 --- a/web/handlers.go +++ b/web/handlers.go @@ -9,6 +9,7 @@ import ( "git.sr.ht/~bouncepaw/betula/readpage" "git.sr.ht/~bouncepaw/betula/stricks" "html/template" + "database/sql" "io" "log" "net/http" @@ -72,6 +73,7 @@ func init() { mux.HandleFunc("/delete-tag/", adminOnly(handlerDeleteTag)) mux.HandleFunc("/day/", handlerDay) mux.HandleFunc("/search", handlerSearch) + mux.HandleFunc("/query", adminOnly(handlerSQLQuery)) mux.HandleFunc("/register", handlerRegister) mux.HandleFunc("/login", handlerLogin) mux.HandleFunc("/logout", handlerLogout) @@ -658,6 +660,96 @@ func handlerSettings(w http.ResponseWriter, rq *http.Request) { http.Redirect(w, rq, "/", http.StatusSeeOther) } +type queryResults struct { + Headers []string + Rows [][]string +} + +type dataQuery struct { + *dataCommon + SQL string + Err string + Res *queryResults +} + +func handlerSQLQuery(w http.ResponseWriter, rq *http.Request) { + if rq.Method == http.MethodGet { + templateExec(w, templateQuery, dataQuery{ + dataCommon: emptyCommon(), + SQL: "", + Err: "", + Res: nil, + }, rq) + return + } + + query := rq.FormValue("sql-query") + + handleError := func(err error) { + templateExec(w, templateQuery, dataQuery{ + dataCommon: emptyCommon(), + SQL: query, + Err: fmt.Sprintf("%s", err), + Res: nil, + }, rq) + } + + res, err := db.RunArbitrary(query) + if err != nil { + handleError(err) + return + } + defer (func() { + err := res.Close() + if err != nil { + log.Fatalf("Failed to close the cursor: %w", err) + } + })() + + headers, err := res.Columns() + if err != nil { + handleError(err) + return + } + + rows := [][]string{} + vals := make([]interface{}, len(headers)) + for i := range headers { + vals[i] = new(sql.RawBytes) + } + + for res.Next() { + err = res.Scan(vals...) + if err != nil { + res.Close() + handleError(err) + return + } + row := make([]string, 0, len(vals)) + for _, val := range vals { + col := val.(*sql.RawBytes) + if col != nil { + row = append(row, string(*col)) + } else { + row = append(row, "") + } + } + rows = append(rows, row) + } + + results := queryResults { + Headers: headers, + Rows: rows, + } + templateExec(w, templateQuery, dataQuery{ + dataCommon: emptyCommon(), + SQL: query, + Err: "", + Res: &results, + }, rq) + return +} + func handlerDeleteLink(w http.ResponseWriter, rq *http.Request) { if rq.Method == http.MethodGet { handlerNotFound(w, rq) diff --git a/web/templates.go b/web/templates.go index db1c3b1..00831cb 100644 --- a/web/templates.go +++ b/web/templates.go @@ -67,6 +67,7 @@ var templateRepostsFor = templateFrom(funcMapForTime, "reposts-of") var templateFeed = templateFrom(funcMapForPosts, "paginator-fragment", "post-fragment", "feed") var templateSubscriptions = templateFrom(funcMapForPosts, "paginator-fragment", "post-fragment", "subscriptions") var templateSearch = templateFrom(funcMapForPosts, "paginator-fragment", "post-fragment", "search") +var templateQuery = templateFrom(nil, "query") var templateTag = templateFrom(funcMapForPosts, "paginator-fragment", "post-fragment", "tag") var templateTags = templateFrom(nil, "tags") var templateDay = templateFrom(funcMapForPosts, "post-fragment", "day") diff --git a/web/views/query.gohtml b/web/views/query.gohtml new file mode 100644 index 0000000..96119b7 --- /dev/null +++ b/web/views/query.gohtml @@ -0,0 +1,27 @@ +{{define "title"}}Query{{end}} +{{define "body"}} + <main> + <article> + <h2>Query</h2> + <form method="post" action="/query"> + <div> + <textarea id="sql-query" name="sql-query" placeholder="select * from Posts">{{.SQL}}</textarea> + <p class="input-caption"> + You can run arbitrary SQL query against Betula’s database. + </div> + <input type="submit" class="btn" value="Run"> + </form> + </article> + {{if ne .Err ""}} + <!-- TODO styling --> + {{ .Err }} + {{end}} + {{if ne .Res nil}} + <!-- TODO styling --> + <table style="overflow-x: visible"> + <tr>{{range .Res.Headers}}<th>{{.}}</th>{{end}}</tr> + {{range .Res.Rows}}<tr>{{range .}}<td>{{.}}</td>{{end}}</tr>{{end}} + </table> + {{end}} + </main> +{{end}} diff --git a/web/views/skeleton.gohtml b/web/views/skeleton.gohtml index 824024d..6dfc87c 100644 --- a/web/views/skeleton.gohtml +++ b/web/views/skeleton.gohtml @@ -31,6 +31,9 @@ {{if .Authorized}}<a href="/settings">Settings</a> {{else}}<a href="/login">Log in</a>{{end}} </li> + {{if .Authorized}} + <li><a href="/query">Query</a></li> + {{end}} <li> <a href="/digest-rss">Site RSS</a> </li> -- 2.42.0
Cool feature!