GEB

GEB , by Gerry Snyder, is an SQLite GUI in Tcl/Tk.

Reference

GEB -- An SQLite GUI in Tcl/Tk , tclforums.info, 2014-11-02
the release announcement

Description

Originally presented at the Fourteenth Annual Tcl/Tk Conference (2007).

Overview

An overview from the announcement thread :

GEB is a general-purpose SQLite manipulation tool. At least, it meets my needs. I have not used anything else for years. YMMV.

The project started after the second time I wrote a Tcl script to do some processing of an SQLite DB and could not find the script when I wanted to use it again. I realized that the only safe thing for me was to store the code in the DB file itself. Maintenance of the code then became inconvenient, because editors expect to work on files, not strings stored in a database.

GEB includes facilities for storing, examining, searching, editing, and executing text stored in the tclcode table of the main and any attached DB file.

It shows a spreadsheet-like display of the schema of the main and each attached DB file, one row per table.

When a row is clicked on, GEB brings up a spreadsheet-like display of that table. The data being shown can be sorted on one or two columns if desired, and restricted by using a WHERE clause. The data can be edited either in the cell or in an entry widget.

A similar display is available for each VIEW in the DB, in addition to facilities for adding, editing, and deleting views.The data cannot be edited.

It has a window for ALTER TABLE, using the copy to temp table, delete original table, copy back scheme -- so it does not automatically take care of foreign keys. It is a bit cumbersome to use (even for me), but hints for what to do next are always shown, and the generated code is shown (and can be edited) before execution.

It also allows for easy execution of single lines of SQL and Tcl code. These, in addition to search/replace snippets and the text used in views, are stored in a cmd_history table for review and possible re-use.

Getting Started

From the announcement :

GEB is available in two forms:

  1. a 200 kB Tcl script, intended for users who already have Tcl installed. SQLite3 and tktable are used.
  2. a 1.5 MB .kit file, for those who run tclkits--this includes binaries for SQLite (3.8.7.1 initially) and tktable for Windows and linux 32-bit and linux 64-bit, in addition to the script.

The two behave identically. Below I will describe two use scenarios.

Initial exploration

When asked if you want to use a disk file, chose "No." This will result in the creation of a :memory: database including the tclcode table which contains the guts of GEB. You can explore the code using the editruntcl window (recommended), but not much more at this point. You can see how I chose to break up the code into manageable hunks. It would have been to separate each proc by itself, but it seemed better to me to keep related stuff together.

To get a better idea of the file/table display capabilities, attach an existing SQLite table using the File menu. A spreadsheet-like display of the file's DB schema appears. One row for each table, one column for each column. It will probably be necessary to manually drag a column-dividing-border to be able to see a complete column definition.

If you click in any row, a window with a display of that table will be created. You can scroll through the data, sort it on one or two columns, and restrict the displayed data by using a WHERE clause (do not enter the actual word "WHERE"--that will be added if anything is typed in the entry widget). Entries can be edited, either in the cell or in an entry widget near the top of the window, the latter usually being more convenient. The default is that confirmation is required, but that can be changed using the radio buttons.

If you decide you want to use GEB regularly, you will probably want to do the next step:

Installing GEB in a disk file

If you say "Yes" when asked if you want to use a disk file, you are given a further choice--existing or new. If there is only one SQLite file you will be using, you might as well just install GEB there. I have maintained a few different databases over the years, so I prefer to keep GEB and my other general-purpose code in one file dedicated for that purpose; and attach other DB files, each with its own tclcode table containing code specific to that file, as needed. That way code always stays with the data it manages.

Whether GEB is installed in a new or in an existing DB file, a very small boot script file is also created. This boot file assumes that Tcl is installed in your system, and may require editing--it works for me. When booting from this file, the original script or kit file is no longer needed.