The Problem
Suppose you have a table with a TEXT field that contains paragraphs full of text and you want to change a few characters out of a paragraph. How do you do that easily?
Using the UPDATE command you would have to say something like this:
UPDATE table
SET field='new value'
WHERE rowid=...;
This requires that the entire revised text be retyped. That is long and tedious and error-prone. It would be better if you could just edit the text that was in the field using a text editor.
A Solution: The edit() Function
If you have the ConsoleForTclTk available, you can do this using the "edit()" SQL function that the console builds in. Consider the example shown in the image on the top-right corner of this page. The database being edited is the CVSTrac database for SQLite. Suppose we want to change the check-in message for check-in [2732] . We could simply type:
UPDATE chng SET message = edit(message) WHERE cn=2732;
A toplevel window appears that contains the original text of column "message" in a Tk Text widget. You edit the text to your liking and press "OK", the edit() function returns the revised text, and the UPDATE statement inserts the revised text in place of the original.
All this is done with surprisingly little code. The first thing that has to happen is that the edit() function has to be registered with the SQLite handle. Like this:
db function edit ::sqlitecon::_edit
The statement above causes the Tcl ::sqlitecon::_edit proc to be invoked to handle any calls to the SQL edit() function. Next we have to define the ::sqlitecon::_edit proc.
proc sqlitecon::_edit {origtxt {title {}}} {
for {set i 0} {[winfo exists .ed$i]} {incr i} continue
set w .ed$i
toplevel $w
wm protocol $w WM_DELETE_WINDOW "$w.b.can invoke"
wm title $w {Inline SQL Editor}
frame $w.b
pack $w.b -side bottom -fill x
button $w.b.can -text Cancel -width 6 -command [list set ::$w 0]
button $w.b.ok -text OK -width 6 -command [list set ::$w 1]
button $w.b.cut -text Cut -width 6 -command [list ::sqlitecon::Cut $w.t]
button $w.b.copy -text Copy -width 6 -command [list ::sqlitecon::Copy $w.t]
button $w.b.paste -text Paste -width 6 -command [list ::sqlitecon::Paste $w.t]
set ::$w {}
pack $w.b.cut $w.b.copy $w.b.paste $w.b.can $w.b.ok\
-side left -padx 5 -pady 5 -expand 1
if {$title!=""} {
label $w.title -text $title
pack $w.title -side top -padx 5 -pady 5
}
text $w.t -bg white -fg black -yscrollcommand [list $w.sb set]
pack $w.t -side left -fill both -expand 1
scrollbar $w.sb -orient vertical -command [list $w.t yview]
pack $w.sb -side left -fill y
$w.t insert end $origtxt
vwait ::$w
if {[set ::$w]} {
set txt [string trimright [$w.t get 1.0 end]]
} else {
set txt $origtxt
}
destroy $w
return $txt
}
This procedure does everything that you need. Note that it takes an optional second parameter which is the text to place in the title bar of the pop-up text editor. So you could say something like this:
UPDATE chng SET message = edit(message, 'Check-in Message Editor')
WHERE cn=2732;
That would replace the default "Inline SQL Editor" title bar with "Check-in Message Editor".
The sqlitecon::_edit proc uses Cut, Copy, and Paste routines from elsewhere in the sqlite console code. But those routines are very short and can be omitted if desired. There are no other external dependencies so you can use this block of code even if you are not loading the sqlite console.
Tcl Advocacy
How many lines of code do you suppose it would take to do the same thing in Java?
Attachments:
- edit2.gif 32487 bytes added by drh on 2005-Sep-24 14:42:15 UTC.