basegui - combobox - dgw - drawcanvas - hyperhelp - sbuttonbar - seditor - sfilebrowser - sfinddialog - sqlview - statusbar - tablelist - tlistbox - tvmixins
dgw::sqlview - database browser widget and standalone application
Usage as package:
package require Tk
package require snit
package require tdbc::sqlite3
package require dgw::seditor
package require dgw::hyperhelp
package require dgw::basegui
package require dgw::sqlview
dgw::sqlview pathName -database filename ?-funcfile sqlfunc.tcl?
dgw::sqlview tedit insert "sql statement"
dgw::sqlview tedit doHilights sql
Usage as command line application:
tclsh sqlview.tcl databasename
The dgw::sqlview package provides a SQL database browser widget as well as a standalone application. It main parts a treeview database structure viewer, a text editor with SQL syntax hilighting, and a tableview widget for viewing the results of the entered SQL statements. The editor widget provides shortcuts to execute either the current selection, the current line or the complete text entered in the text widget.
dgw::sqlview pathName -database fileName ?-option value ...?
creates a new sqlview widget using the given widget pathName and with the given -database fileName. Please note, that the filename must be currently a sqlite3 database. Support for other database types can be added on request.
The dgw::sqlview snit widget supports the following options:
-database filename
Configures the database used within the widget. Should be set already at widget initialization.
-funcfile filename(s)
Load SQL functions from the file given with the -funcfile option. Please note, that all SQL functions written in Tcl must be created within the sqlfunc namespace. If the filenames are a list, every file of this list will be loaded. See the following example for the beginning of such a
funcfile.tcl:namespace eval ::sqlfunc {} # replace all A's with B's proc ::sqlfunc::mmap args { return [string map {A B} [lindex $args 0]] } # support for regexp: rgx('^AB',colname) proc ::sqlfunc::rgx {args} { return [regexp [lindex $args 0] [lindex $args 1]] } # support for replacements: rsub('^AB',colname,'') proc ::sqlfunc::rsub {args} { return [regsub [lindex $args 0] [lindex $args 1] [lindex $args 2]] }
-log boolean
Should all executed statements written into the logfile. Default true.
-logfile filename
The file where all executed statements of the sqlview editor widget are written into. If not given, defaults to an the file sqlview.log in the users home directory.
-type sqlite3
Configures the database type, currently only the type sqlite3 is supported. Other types can be added on request. Should be set only at widget initialisation.
The dgw::sqlview widget provides the following methods:
pathName changeFontSize integer
Increase (positive integer values) or decrease widget (negative integer values) font size of all text and ttk::treeview widgets.
pathName closeSQLview
Destroys the sqlview widget. A message box will be shown to verify that the widget should be really destroyed. This as well disconnects the database cleanly.
pathName dbConnect
Connect to the actual database. Currently only SQLite 3 is supported.
pathName dbDisconnect
Disconnect the actual database. Currently only SQLite 3 is supported.
pathName dbSelect statement
Execute the given statement against the database and insert the result into the tableview widget at the bottom.
pathName executeSQL mode
Execute a SQL statement against the current database. If mode is 'all' (default) the complete text entered in the text editor widget will be used as statement. The mode can be as well 'line' where only the current line is executed, or 'selection' where only the currently selected text is send to the database.
pathName getDBVersion
Returns the SQLite 3 version of the current database implementation.
pathName openDatabase ?filename?
Open the database using the given filename is given. If not filename is given opens the open file dialog.
pathName refreshDatabase
Refresh the treeview widget which shows the database structure. Useful if the database was updated.
pathName showHistory toplevel
Displays the SQL statement history in the given toplevel path. Default:
.history
pathName tedit arguments
Expose the interface of the dgw::seditor text widget, so you can use all of its methods and the methods of the standard text editor widget. For example:
pathName tedit insert end "select * from students"
In addition to the standard bindings of the Tk text editor widget, the SQL editor widget, dgw::seditor, in the upper right, provides the following key and mouse bindings:
mouse right click : editor popup with cut, paste etc.Ctrl-x 2: split the window verticallyCtrl-x 3: split the window horizontallyCtrl-x 1 undo the splittingShift-Return: Send the widget text to the configured -toolcommandControl-Return: send the current line to configured -toolcommandControl-Shift-Return: send the current selection to the tool command
Please note, that the tool command accelerator keys can be changed to other keys
by using the options of the dgw::seditor widget using the tedit sub command of the dgw::sqlview widget.
The tableview widget at the bottom can be sorted by column if the user clicks on the column headers. It otherwise provides the standard bindings of the ttk::treeview widget to the developer and user.
The following shortcuts are only available if the users click on the notebook tab:
If you the notebook tab has not the focus you have the following bindings available:
In the example below we create a sample database and load the database and their information into the three sub widgets.
package require dgw::sqlview
set sqlv [::dgw::sqlview .sql -database test.sqlite3 -funcfile sqlfunc.tcl]
# create a test database
$sqlv dbSelect "drop table if exists students"
$sqlv dbSelect "create table students (id INTEGER, firstname TEXT, lastname TEXT, city TEXT)"
$sqlv dbSelect "insert into students (id,firstname, lastname, city) values (1234, 'Marc', 'Musterman', 'Mustercity')"
$sqlv dbSelect "insert into students (id,firstname, lastname, city) values (1235, 'Marcella', 'Musterwoman', 'Berlin')"
$sqlv refreshDatabase
$sqlv tedit doHilights sql
$sqlv tedit insert end " select * from students"
$sqlv executeSQL
pack $sqlv -side top -fill both -expand yes
You can install and use the dgw::sqlview package if you have a working install of:
If you have this, then download the latest dgw and dgtools package releases from: dgw package release page. For installation unzip the latest dgw and dgtools zip files and copy the complete dgw and dgtools folders into a path of your auto_path list of Tcl. Alternatively you can append the auto_path list with the parent directory of the dgw directory.
Example code for this package can be executed by running this file using the following command line:
$ wish sqlview.tcl --demo
The example code used for this demo can be seen in the terminal by using the following command line:
$ tclsh sqlview.tcl --code
The script contains embedded the documentation in Markdown format. To extract the documentation you should use the following command line:
$ tclsh sqlview.tcl --markdown
This will extract the embedded manual pages in standard Markdown format. You can as well use this markdown output directly to create html pages for the documentation by using the --html flag.
$ tclsh sqlview.tcl --html
If the tcllib Markdown package is installed, this will directly create a HTML page sqlview.html
which contains the formatted documentation.
Github-Markdown can be extracted by using the --man switch:
$ tclsh sqlview.tcl --man
The output of this command can be used to feed a Markdown processor for conversion into a html, docx or pdf document. If you have pandoc installed for instance, you could execute the following commands:
tclsh ../sqlview.tcl --man > sqlview.md
pandoc -i sqlview.md -s -o sqlview.html
pandoc -i sqlview.md -s -o sqlview.tex
pdflatex sqlview.tex
The dgw::sqlview widget was written by Detlef Groth, Schwielowsee, Germany.
Copyright (c) 2019-20 Dr. Detlef Groth, E-mail: detlef(at)dgroth(dot)de
dgw::sqlview package, version 0.6.
Copyright (c) 2019-2020 Dr. Detlef Groth, E-mail: detlef(at)dgroth(dot)de
This library is free software; you can use, modify, and redistribute it for any purpose, provided that existing copyright notices are retained in all copies and that this notice is included verbatim in any distributions.
This software is distributed WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.