bradfitz/sqlplay.go

## sqlplay.go
package main

import (
	"database/sql"
	"flag"
	"fmt"
	"html"
	"io"
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"

	_ "github.com/lib/pq"
)

var db *sql.DB

var (
	listen = flag.String("listen", defaultListen(), "listen address")
)

func defaultListen() string {
	if os.Getuid() == 0 {
		return ":80"
	}
	return ":8219"
}

func main() {
	flag.Parse()

	log.Printf("starting")

	connStr, err := ioutil.ReadFile(filepath.Join(os.Getenv("HOME"), "keys", "sqlplay-ro"))
	if err != nil {
		log.Fatal(err)
	}
	db, err = sql.Open("postgres", strings.TrimSpace(string(connStr)))
	if err != nil {
		log.Fatal(err)
	}

	log.Printf("connected to postgres; listening on %v...", *listen)
	http.HandleFunc("/", serve)
	log.Fatal(http.ListenAndServe(*listen, nil))
}

func serve(w http.ResponseWriter, r *http.Request) {
	sql := r.FormValue("q")
	if sql == "" {
		r.FormValue("sql") // old way for compat
	}
	fmt.Fprintf(w, `<html>
<head><style>

tr:nth-child(even){background-color: #f2f2f2;}

td, th {
  border: 1px solid #ddd;
  padding: 8px;
}

table {
    border-collapse: collapse;
    width: 100%%;
}

tr:hover {background-color: #ddd;}

th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}

</style></head>
<body>
   <form method=GET>
   <textarea name=q rows=5 cols=80 style='width: 100%%'>%s</textarea>
   <p><input type=submit value="query">
          [<a href="https://github.com/tailscale/xxxx#L28">schema</a>]
          [<a href="https://docs.google.com/document/d/xxxxxx/edit#">sample queries</a>]
   </form>
</body>`, html.EscapeString(sql))

	if sql != "" {
		rows, err := db.Query(sql)
		if err != nil {
			http.Error(w, err.Error(), 500)
			return
		}
		io.WriteString(w, `<html><body><table><tr>`)
		cols, _ := rows.Columns()
		for _, c := range cols {
			fmt.Fprintf(w, "<th>%s</th>\n", html.EscapeString(c))
		}
		io.WriteString(w, `</tr>`)

		for rows.Next() {
			val := make([]interface{}, len(cols))
			valPtr := make([]interface{}, len(cols))
			for i := range cols {
				valPtr[i] = &val[i]
			}
			if err := rows.Scan(valPtr...); err != nil {
				http.Error(w, err.Error(), 500)
				return
			}
			io.WriteString(w, `<tr>`)

			for _, v := range val {
				fmt.Fprintf(w, "<td>%s</td>\n", colHTML(v))
			}
			io.WriteString(w, "</tr>\n")
		}
		io.WriteString(w, "</table>\n")
		return
	}

}

func colFmt(v interface{}) string {
	switch v := v.(type) {
	case []byte:
		return string(v)
	default:
		s := fmt.Sprint(v)
		s = strings.TrimSuffix(s, " 00:00:00 +0000 +0000") // so a time.Time of a single day formats nicely
		return s
	}
}

func colHTML(v interface{}) string {
	s := colFmt(v)
	h := html.EscapeString(s)
	if strings.HasPrefix(s, "cus_") {
		return fmt.Sprintf("<a href=\"https://dashboard.stripe.com/customers/%s\" rel=\"noopener noreferrer\">%s</a>", h, h)
	}
	return h
}
	package main

	import (
	"database/sql"
	"flag"
	"fmt"
	"html"
	"io"
	"io/ioutil"
	"log"
	"net/http"
	"os"
	"path/filepath"
	"strings"

	_ "github.com/lib/pq"
	)

	var db *sql.DB

	var (
	listen = flag.String("listen", defaultListen(), "listen address")
	)

	func defaultListen() string {
	if os.Getuid() == 0 {
	return ":80"
	}
	return ":8219"
	}

	func main() {
	flag.Parse()

	log.Printf("starting")

	connStr, err := ioutil.ReadFile(filepath.Join(os.Getenv("HOME"), "keys", "sqlplay-ro"))
	if err != nil {
	log.Fatal(err)
	}
	db, err = sql.Open("postgres", strings.TrimSpace(string(connStr)))
	if err != nil {
	log.Fatal(err)
	}

	log.Printf("connected to postgres; listening on %v...", *listen)
	http.HandleFunc("/", serve)
	log.Fatal(http.ListenAndServe(*listen, nil))
	}

	func serve(w http.ResponseWriter, r *http.Request) {
	sql := r.FormValue("q")
	if sql == "" {
	r.FormValue("sql") // old way for compat
	}
	fmt.Fprintf(w, `<html>
	<head><style>

	tr:nth-child(even){background-color: #f2f2f2;}

	td, th {
	border: 1px solid #ddd;
	padding: 8px;
	}

	table {
	border-collapse: collapse;
	width: 100%%;
	}

	tr:hover {background-color: #ddd;}

	th {
	padding-top: 12px;
	padding-bottom: 12px;
	text-align: left;
	background-color: #04AA6D;
	color: white;
	}

	</style></head>
	<body>
	<form method=GET>
	<textarea name=q rows=5 cols=80 style='width: 100%%'>%s</textarea>
	<p><input type=submit value="query">
	[<a href="https://github.com/tailscale/xxxx#L28">schema</a>]
	[<a href="https://docs.google.com/document/d/xxxxxx/edit#">sample queries</a>]
	</form>
	</body>`, html.EscapeString(sql))

	if sql != "" {
	rows, err := db.Query(sql)
	if err != nil {
	http.Error(w, err.Error(), 500)
	return
	}
	io.WriteString(w, `<html><body><table><tr>`)
	cols, _ := rows.Columns()
	for _, c := range cols {
	fmt.Fprintf(w, "<th>%s</th>\n", html.EscapeString(c))
	}
	io.WriteString(w, `</tr>`)

	for rows.Next() {
	val := make([]interface{}, len(cols))
	valPtr := make([]interface{}, len(cols))
	for i := range cols {
	valPtr[i] = &val[i]
	}
	if err := rows.Scan(valPtr...); err != nil {
	http.Error(w, err.Error(), 500)
	return
	}
	io.WriteString(w, `<tr>`)

	for _, v := range val {
	fmt.Fprintf(w, "<td>%s</td>\n", colHTML(v))
	}
	io.WriteString(w, "</tr>\n")
	}
	io.WriteString(w, "</table>\n")
	return
	}

	}

	func colFmt(v interface{}) string {
	switch v := v.(type) {
	case []byte:
	return string(v)
	default:
	s := fmt.Sprint(v)
	s = strings.TrimSuffix(s, " 00:00:00 +0000 +0000") // so a time.Time of a single day formats nicely
	return s
	}
	}

	func colHTML(v interface{}) string {
	s := colFmt(v)
	h := html.EscapeString(s)
	if strings.HasPrefix(s, "cus_") {
	return fmt.Sprintf("<a href=\"https://dashboard.stripe.com/customers/%s\" rel=\"noopener noreferrer\">%s</a>", h, h)
	}
	return h
	}