Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A demo Go application (a PostgreSQL database browser) highlighting the use of the rivo/tview package. See https://github.com/rivo/tview/wiki/Postgres
package main
import (
"database/sql"
"fmt"
"net/url"
"os"
"reflect"
"regexp"
"strconv"
"sync"
"time"
"github.com/gdamore/tcell"
_ "github.com/lib/pq"
"github.com/rivo/tview"
)
const (
batchSize = 80 // The number of rows loaded per batch.
finderPage = "*finder*" // The name of the Finder page.
)
var (
app *tview.Application // The tview application.
pages *tview.Pages // The application pages.
finderFocus tview.Primitive // The primitive in the Finder that last had focus.
)
// Main entry point.
func main() {
// Get connect string from the command line.
if len(os.Args) < 2 {
fmt.Println(`Please provide a PostgreSQL connect string.`)
fmt.Println("See https://godoc.org/github.com/lib/pq for details.")
return
}
// Start the application.
app = tview.NewApplication()
finder(os.Args[1])
if err := app.Run(); err != nil {
fmt.Printf("Error running application: %s\n", err)
}
}
// Sets up a "Finder" used to navigate the databases, tables, and columns.
func finder(connString string) {
// Create the basic objects.
databases := tview.NewList().ShowSecondaryText(false)
databases.SetBorder(true).SetTitle("Databases")
columns := tview.NewTable().SetBorders(true)
columns.SetBorder(true).SetTitle("Columns")
tables := tview.NewList()
tables.ShowSecondaryText(false).
SetDoneFunc(func() {
tables.Clear()
columns.Clear()
app.SetFocus(databases)
})
tables.SetBorder(true).SetTitle("Tables")
// Create the layout.
flex := tview.NewFlex().
AddItem(databases, 0, 1, true).
AddItem(tables, 0, 1, false).
AddItem(columns, 0, 3, false)
// We keep one connection pool per database.
dbMutex := sync.Mutex{}
dbs := make(map[string]*sql.DB)
getDB := func(database string) *sql.DB {
// Connect to a new database.
dbMutex.Lock()
defer dbMutex.Unlock()
if db, ok := dbs[database]; ok {
return db
}
var newConnString string
if u, err := url.Parse(connString); err == nil && u.Scheme == "postgres" {
u.Path = "/" + database
newConnString = u.String()
} else {
dbstring := regexp.MustCompile(`(dbname=)'?[^ ]*'?`)
matches := dbstring.FindStringSubmatch(connString)
if matches == nil {
newConnString = "dbname='" + database + "' " + connString
} else {
newConnString = dbstring.ReplaceAllString(connString, "$1'"+database+"'")
}
}
db, err := sql.Open("postgres", newConnString)
if err != nil {
panic(err)
}
dbs[database] = db
return db
}
// Get a list of all databases.
generalDB, err := sql.Open("postgres", connString)
if err != nil {
panic(err)
}
defer generalDB.Close() // We really close the DB because we only use it for this one query.
rows, err := generalDB.Query("select datname from pg_database where datistemplate = false")
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var dbName string
if err := rows.Scan(&dbName); err != nil {
panic(err)
}
databases.AddItem(dbName, "", 0, func() {
// A database was selected. Show all of its tables.
columns.Clear()
tables.Clear()
db := getDB(dbName)
t, err := db.Query("select table_name from information_schema.tables where table_schema = 'public'")
if err != nil {
panic(err)
}
defer t.Close()
for t.Next() {
var tableName string
if err := t.Scan(&tableName); err != nil {
panic(err)
}
tables.AddItem(tableName, "", 0, nil)
}
if err := t.Err(); err != nil {
panic(err)
}
app.SetFocus(tables)
// When the user navigates to a table, show its columns.
tables.SetChangedFunc(func(i int, tableName string, t string, s rune) {
// A table was selected. Show its columns.
columns.Clear()
c, err := db.Query(`
select
c.column_name,
c.is_nullable,
c.data_type,
c.character_maximum_length,
c.numeric_precision,
c.numeric_scale,
c.ordinal_position,
tc.constraint_type pkey
from
information_schema.columns c
left join
information_schema.constraint_column_usage as ccu
on
c.table_schema = ccu.table_schema
and c.table_name = ccu.table_name
and c.column_name = ccu.column_name
left join
information_schema.table_constraints as tc
on
ccu.constraint_schema = tc.constraint_schema
and ccu.constraint_name = tc.constraint_name
where
c.table_schema = 'public'
and c.table_name = $1
`, tableName)
if err != nil {
panic(err)
}
defer c.Close()
columns.SetCell(0, 0, &tview.TableCell{Text: "Name", Align: tview.AlignCenter, Color: tcell.ColorYellow}).
SetCell(0, 1, &tview.TableCell{Text: "Type", Align: tview.AlignCenter, Color: tcell.ColorYellow}).
SetCell(0, 2, &tview.TableCell{Text: "Size", Align: tview.AlignCenter, Color: tcell.ColorYellow}).
SetCell(0, 3, &tview.TableCell{Text: "Null", Align: tview.AlignCenter, Color: tcell.ColorYellow}).
SetCell(0, 4, &tview.TableCell{Text: "Constraint", Align: tview.AlignCenter, Color: tcell.ColorYellow})
for c.Next() {
var (
columnName, isNullable, dataType string
constraintType sql.NullString
size, numericPrecision, numericScale sql.NullInt64
ordinalPosition int
)
if err := c.Scan(&columnName,
&isNullable,
&dataType,
&size,
&numericPrecision,
&numericScale,
&ordinalPosition,
&constraintType,
); err != nil {
panic(err)
}
sizeText := ""
if size.Valid {
sizeText = strconv.Itoa(int(size.Int64))
} else if numericPrecision.Valid {
sizeText = strconv.Itoa(int(numericPrecision.Int64))
if numericScale.Valid {
sizeText += "," + strconv.Itoa(int(numericScale.Int64))
}
}
color := tcell.ColorWhite
if constraintType.Valid {
color = map[string]tcell.Color{
"CHECK": tcell.ColorGreen,
"FOREIGN KEY": tcell.ColorDarkMagenta,
"PRIMARY KEY": tcell.ColorRed,
"UNIQUE": tcell.ColorDarkCyan,
}[constraintType.String]
}
columns.SetCell(ordinalPosition, 0, &tview.TableCell{Text: columnName, Color: color}).
SetCell(ordinalPosition, 1, &tview.TableCell{Text: dataType, Color: color}).
SetCell(ordinalPosition, 2, &tview.TableCell{Text: sizeText, Align: tview.AlignRight, Color: color}).
SetCell(ordinalPosition, 3, &tview.TableCell{Text: isNullable, Align: tview.AlignRight, Color: color}).
SetCell(ordinalPosition, 4, &tview.TableCell{Text: constraintType.String, Align: tview.AlignLeft, Color: color})
}
if err := c.Err(); err != nil {
panic(err)
}
})
tables.SetCurrentItem(0) // Trigger the initial selection.
// When the user selects a table, show its content.
tables.SetSelectedFunc(func(i int, tableName string, t string, s rune) {
content(db, dbName, tableName)
})
})
}
if err := rows.Err(); err != nil {
panic(err)
}
// Set up the pages and show the Finder.
pages = tview.NewPages().
AddPage(finderPage, flex, true, true)
app.SetRoot(pages, true)
}
// Shows the contents of the given table.
func content(db *sql.DB, dbName, tableName string) {
finderFocus = app.GetFocus()
// If this page already exists, just show it.
if pages.HasPage(dbName + "." + tableName) {
pages.SwitchToPage(dbName + "." + tableName)
return
}
// We display the data in a table embedded in a frame.
table := tview.NewTable().
SetFixed(1, 0).
SetSeparator(tview.GraphicsVertBar).
SetBordersColor(tcell.ColorYellow)
frame := tview.NewFrame(table).
SetBorders(0, 0, 0, 0, 0, 0)
frame.SetBorder(true).
SetTitle(fmt.Sprintf(`Contents of table "%s"`, tableName))
// How many rows does this table have?
var rowCount int
err := db.QueryRow(fmt.Sprintf("select count(*) from %s", tableName)).Scan(&rowCount)
if err != nil {
panic(err)
}
// Load a batch of rows.
loadRows := func(offset int) {
rows, err := db.Query(fmt.Sprintf("select * from %s limit $1 offset $2", tableName), batchSize, offset)
if err != nil {
panic(err)
}
defer rows.Close()
// The first row in the table is the list of column names.
columnNames, err := rows.Columns()
if err != nil {
panic(err)
}
for index, name := range columnNames {
table.SetCell(0, index, &tview.TableCell{Text: name, Align: tview.AlignCenter, Color: tcell.ColorYellow})
}
// Read the rows.
columns := make([]interface{}, len(columnNames))
columnPointers := make([]interface{}, len(columns))
for index := range columnPointers {
columnPointers[index] = &columns[index]
}
for rows.Next() {
// Read the columns.
err := rows.Scan(columnPointers...)
if err != nil {
panic(err)
}
// Transfer them to the table.
row := table.GetRowCount()
for index, column := range columns {
switch value := column.(type) {
case int64:
table.SetCell(row, index, &tview.TableCell{Text: strconv.Itoa(int(value)), Align: tview.AlignRight, Color: tcell.ColorDarkCyan})
case float64:
table.SetCell(row, index, &tview.TableCell{Text: strconv.FormatFloat(value, 'f', 2, 64), Align: tview.AlignRight, Color: tcell.ColorDarkCyan})
case string:
table.SetCellSimple(row, index, value)
case time.Time:
t := value.Format("2006-01-02")
table.SetCell(row, index, &tview.TableCell{Text: t, Align: tview.AlignRight, Color: tcell.ColorDarkMagenta})
case []uint8:
str := make([]byte, len(value))
for index, num := range value {
str[index] = byte(num)
}
table.SetCell(row, index, &tview.TableCell{Text: string(str), Align: tview.AlignRight, Color: tcell.ColorGreen})
case nil:
table.SetCell(row, index, &tview.TableCell{Text: "NULL", Align: tview.AlignCenter, Color: tcell.ColorRed})
default:
// We've encountered a type that we don't know yet.
t := reflect.TypeOf(value)
str := "?nil?"
if t != nil {
str = "?" + t.String() + "?"
}
table.SetCellSimple(row, index, str)
}
}
}
if err := rows.Err(); err != nil {
panic(err)
}
// Show how much we've loaded.
frame.Clear()
loadMore := ""
if table.GetRowCount()-1 < rowCount {
loadMore = " - press Enter to load more"
}
loadMore = fmt.Sprintf("Loaded %d of %d rows%s", table.GetRowCount()-1, rowCount, loadMore)
frame.AddText(loadMore, false, tview.AlignCenter, tcell.ColorYellow)
}
// Load the first batch of rows.
loadRows(0)
// Handle key presses.
table.SetDoneFunc(func(key tcell.Key) {
switch key {
case tcell.KeyEscape:
// Go back to Finder.
pages.SwitchToPage(finderPage)
if finderFocus != nil {
app.SetFocus(finderFocus)
}
case tcell.KeyEnter:
// Load the next batch of rows.
loadRows(table.GetRowCount() - 1)
table.ScrollToEnd()
}
})
// Add a new page and show it.
pages.AddPage(dbName+"."+tableName, frame, true, true)
}
@stefansbv

This comment has been minimized.

Copy link

@stefansbv stefansbv commented Mar 10, 2019

There is an error:
./postgres.go:255:16: undefined: tview.GraphicsVertBar

But if I comment out that line, it works fine.

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment