Unauthorized
Provide your API key as a ?key= query parameter.
`
// knownTables is the ordered list of tables exposed in the admin UI.
var knownTables = []string{
"players",
"player_ips",
"game_sessions",
"device_infos",
"high_scores",
}
type columnInfo struct {
Name string
PK bool
}
func getColumns(db *sql.DB, table string) ([]columnInfo, error) {
rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", table))
if err != nil {
return nil, err
}
defer rows.Close()
var cols []columnInfo
for rows.Next() {
var cid int
var name, typ string
var notNull, pk int
var dflt sql.NullString
if err := rows.Scan(&cid, &name, &typ, ¬Null, &dflt, &pk); err != nil {
return nil, err
}
cols = append(cols, columnInfo{Name: name, PK: pk == 1})
}
return cols, rows.Err()
}
func getPKColumn(cols []columnInfo) string {
for _, c := range cols {
if c.PK {
return c.Name
}
}
return ""
}
func isKnownTable(name string) bool {
for _, t := range knownTables {
if t == name {
return true
}
}
return false
}
// AdminDashboard handles GET /admin/ — lists all tables with row counts.
func AdminDashboard(db *sql.DB) echo.HandlerFunc {
return func(c echo.Context) error {
type tableInfo struct {
Name string
Count int
}
var tables []tableInfo
for _, t := range knownTables {
var count int
row := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM %s", t))
if err := row.Scan(&count); err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
tables = append(tables, tableInfo{Name: t, Count: count})
}
data := map[string]interface{}{
"Tables": tables,
}
return renderTemplate(c, dashboardTmpl, data)
}
}
// AdminTable handles GET /admin/tables/:name/ — paginated table view.
func AdminTable(db *sql.DB) echo.HandlerFunc {
return func(c echo.Context) error {
table := c.Param("name")
if !isKnownTable(table) {
return c.JSON(http.StatusNotFound, echo.Map{"error": "table not found"})
}
cols, err := getColumns(db, table)
if err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
if len(cols) == 0 {
return c.JSON(http.StatusNotFound, echo.Map{"error": "table not found"})
}
// Pagination.
page, _ := strconv.Atoi(c.QueryParam("page"))
if page < 1 {
page = 1
}
perPage := 50
offset := (page - 1) * perPage
// Total count.
var total int
if err := db.QueryRow(fmt.Sprintf("SELECT COUNT(*) FROM %s", table)).Scan(&total); err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
// Sort.
sortCol := c.QueryParam("sort")
sortDir := c.QueryParam("dir")
orderClause := ""
validSort := false
for _, col := range cols {
if col.Name == sortCol {
validSort = true
break
}
}
if validSort {
dir := "ASC"
if strings.EqualFold(sortDir, "desc") {
dir = "DESC"
}
orderClause = fmt.Sprintf(" ORDER BY %s %s", sortCol, dir)
} else {
// Default: order by primary key descending.
pk := getPKColumn(cols)
if pk != "" {
orderClause = fmt.Sprintf(" ORDER BY %s DESC", pk)
sortCol = pk
sortDir = "desc"
}
}
// Search filter.
search := strings.TrimSpace(c.QueryParam("q"))
whereClause := ""
var args []interface{}
if search != "" {
var conditions []string
for _, col := range cols {
conditions = append(conditions, fmt.Sprintf("CAST(%s AS TEXT) LIKE ?", col.Name))
args = append(args, "%"+search+"%")
}
whereClause = " WHERE " + strings.Join(conditions, " OR ")
// Recount with filter.
countQuery := fmt.Sprintf("SELECT COUNT(*) FROM %s%s", table, whereClause)
if err := db.QueryRow(countQuery, args...).Scan(&total); err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
}
query := fmt.Sprintf("SELECT * FROM %s%s%s LIMIT ? OFFSET ?", table, whereClause, orderClause)
queryArgs := append(args, perPage, offset)
rows, err := db.Query(query, queryArgs...)
if err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
defer rows.Close()
var rowData [][]string
colNames, _ := rows.Columns()
for rows.Next() {
vals := make([]interface{}, len(colNames))
ptrs := make([]interface{}, len(colNames))
for i := range vals {
ptrs[i] = &vals[i]
}
if err := rows.Scan(ptrs...); err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "scan error"})
}
row := make([]string, len(colNames))
for i, v := range vals {
if v == nil {
row[i] = "NULL"
} else {
row[i] = fmt.Sprintf("%v", v)
}
}
rowData = append(rowData, row)
}
totalPages := (total + perPage - 1) / perPage
pk := getPKColumn(cols)
// Find PK column index for linking.
pkIdx := -1
for i, col := range cols {
if col.Name == pk {
pkIdx = i
break
}
}
data := map[string]interface{}{
"Table": table,
"Columns": colNames,
"Rows": rowData,
"Page": page,
"TotalPages": totalPages,
"Total": total,
"PerPage": perPage,
"Sort": sortCol,
"Dir": sortDir,
"Search": search,
"PKIndex": pkIdx,
"Tables": knownTables,
}
return renderTemplate(c, tableTmpl, data)
}
}
// AdminRow handles GET /admin/tables/:name/:pk/ — single row detail.
func AdminRow(db *sql.DB) echo.HandlerFunc {
return func(c echo.Context) error {
table := c.Param("name")
pkVal := c.Param("pk")
if !isKnownTable(table) {
return c.JSON(http.StatusNotFound, echo.Map{"error": "table not found"})
}
cols, err := getColumns(db, table)
if err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
pk := getPKColumn(cols)
if pk == "" {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "no primary key"})
}
query := fmt.Sprintf("SELECT * FROM %s WHERE %s = ?", table, pk)
rows, err := db.Query(query, pkVal)
if err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "database error"})
}
defer rows.Close()
colNames, _ := rows.Columns()
if !rows.Next() {
return c.JSON(http.StatusNotFound, echo.Map{"error": "row not found"})
}
vals := make([]interface{}, len(colNames))
ptrs := make([]interface{}, len(colNames))
for i := range vals {
ptrs[i] = &vals[i]
}
if err := rows.Scan(ptrs...); err != nil {
return c.JSON(http.StatusInternalServerError, echo.Map{"error": "scan error"})
}
type field struct {
Name string
Value string
}
var fields []field
for i, name := range colNames {
v := "NULL"
if vals[i] != nil {
v = fmt.Sprintf("%v", vals[i])
}
fields = append(fields, field{Name: name, Value: v})
}
data := map[string]interface{}{
"Table": table,
"PK": pkVal,
"Fields": fields,
"Tables": knownTables,
}
return renderTemplate(c, rowTmpl, data)
}
}
func renderTemplate(c echo.Context, tmpl *template.Template, data interface{}) error {
// Inject the API key so templates can propagate it in links.
if m, ok := data.(map[string]interface{}); ok {
if key, exists := c.Get("api_key").(string); exists {
m["Key"] = key
}
}
c.Response().Header().Set("Content-Type", "text/html; charset=utf-8")
c.Response().WriteHeader(http.StatusOK)
return tmpl.Execute(c.Response().Writer, data)
}
// --- HTML Templates ---
var baseFuncs = template.FuncMap{
"add": func(a, b int) int { return a + b },
"sub": func(a, b int) int { return a - b },
"toggleDir": func(dir string) string {
if strings.EqualFold(dir, "asc") {
return "desc"
}
return "asc"
},
"seq": func(start, end int) []int {
var s []int
for i := start; i <= end; i++ {
s = append(s, i)
}
return s
},
"min": func(a, b int) int {
if a < b {
return a
}
return b
},
"max": func(a, b int) int {
if a > b {
return a
}
return b
},
"eq": func(a, b interface{}) bool {
return fmt.Sprintf("%v", a) == fmt.Sprintf("%v", b)
},
"kp": func(key string) template.URL {
if key == "" {
return ""
}
return template.URL("key=" + key)
},
"kpAmp": func(key string) template.URL {
if key == "" {
return ""
}
return template.URL("&key=" + key)
},
}
const cssStyle = `
`
var dashboardTmpl = template.Must(template.New("dashboard").Funcs(baseFuncs).Parse(`