GEB - Tcl Developer Xchange

apatheticyogurtSoftware and s/w Development

Dec 13, 2013 (3 years and 5 months ago)

70 views

GEB

SQLite in Tcl in SQLite

A Tcl/Tk script for displaying and maintaining
an SQLite database, stored in an SQLite
database

GEB

Presentation Overview


How the project started


What its goals have been


Early version


Evolution: tkTable, storing script in SQLite, etc.


Bootup summary


Past and future


Potential users and licensing


Live demonstration

GEB: (evolving) Design Goals


Reduce dependence on proprietary s/w and data
formats (MS Access, dBase n)


Convenient scripting language, run on Linux and
Windows


Develop single package for all my needs


View table/column names


View and edit table contents


Store/edit/execute Tcl/SQL code in the database


Store the program itself in the database


Add functions as the need arises (ongoing)

GEB: Design Drivers


Develop incrementally


Keep data safe


Show generated SQL before COMMITting


Disallow many combinations of Alter Table
functions in a single pass


Accept slower screen updates


Easy to use


Minimize bootstrap

GEB: Pre
-
Tktable (top)


GEB: Pre
-
Tktable (bottom)


GEB: Alter Table


GEB: Pre
-
Tktable (edittable)


GEB: Change to Tktable


Driven by desire to improve looks


Each table is editable, in its own window


Solved potential problem of displaying large
databases


Each cell entry value from an individual SELECT


Safe but slower


Neither specific potential problem nor iron
-
clad
bulletproofing has been found

GEB: Main window


GEB: Table Display


GEB: Stand
-
alone Functions


Convert between v2 and v3 file formats


Import table from or export table to delimited file


First row can be column names or data


Imported data can have variable number of
columns


Import db element from or export db element to
text file (like ONECOLUMN method)


Execute a single line of SQL or Tcl

GEB: Run SQL


GEB: Decision to Put showtable in
Database File


Inspired by the “Database as Program” section
of DRH's “SQLite and Tcl” paper, but not an
implementation of it


I wanted to put a functional block (the chief proc
for a function and its supporting proc's) in one
table


Can't put header and body in separate columns


Outside function can't call support proc unless its
functional block is known to have been loaded

GEB: Impacts of Putting showtable in
Database File


ATTACH capability became mandatory


If only one file can be displayed/edited, that one slot
can't be taken up by the displaying program itself


Redo data arrays as 2D (attach name and original
index)


Had to implement editor


Searching routine needed to look at all Tcl tables


Find matching brackets, braces, and parentheses


Replace and Replace All, with and without
confirmation required


Check for infinite loop

GEB: editruntcl


GEB: search


GEB: Bootstrap


package require Tk; package require Tktable; console show


# Create namespace for all "globals" and procs


namespace eval ::GEB {}



load /sqlite/tclsqlite3.dll



load /sqlite/tclsqlite.dll


set dbfile showtable.sq3



sqlite3 sq $dbfile



set ::GEB::attachfilename(main) $dbfile


proc evalsqlitetcl table {



uplevel #0 [join [sq eval "select tcl from $table limit 1"]]


}


evalsqlitetcl main_attach


editruntcl


# attachit s2008 /irisdata/2008symp/2008symp.sq3

GEB: main_attach Overview


Read in core routines


Those needed before “unknown” is set up


Set up window and arrays for main file


Table names, columns in each table, etc.


Extend “unknown” proc handler to look in
database file


If a table with the name of the needed proc exists,
load it


If the loading defines the proc, execute it


Otherwise, exec original “unknown” handler

GEB: Summary of Development
Experience


Amazingly few gotchas considering my level of
knowledge of Tcl/Tk/SQLite when I started


It often took longer to decide what I wanted to
do and how I wanted it to work than to
implement it.


A very useful tool. For me. It fits my way of
thinking. But for others it may be too
idiosyncratic.

GEB: Future Plans


Allow wildcards in searches


Allow editing a development version while
executing stable version (row vs column)


Switch to Tile


Implement full text search support


Facilitate displays of views


....

GEB: Potential Users


One extreme: Tcl developer who does not care
about SQLite


Conceivable, but many more facilities available
elsewhere


Other extreme: SQLite user who does not care
about Tcl


Slightly more reasonable, but again, other tools
available


Happy medium: User with some data and some
scripts, who wants to keep and use them
together

GEB: Licensing


Intended to be released to Public Domain


(It would feel wrong to be more restrictive than
SQLite itself)


SQLite blessing or equivalent:


May you do good and not evil


May you find forgiveness for yourself and forgive others


May you share freely, never taking more than you give


Further development encouraged, especially if
kept public


Support available
--
inquire within


Gerry Snyder mesmerizerfan@gmail.com