basegui - combobox - dgw - drawcanvas - hyperhelp - sbuttonbar - seditor - sfilebrowser - sfinddialog - sqlview - statusbar - tablelist - tlistbox - tvmixins

dgw::sqlview 0.6

Dr. Detlef Groth, Schwielowsee, Germany

2020-04-14

NAME

dgw::sqlview - database browser widget and standalone application

TABLE OF CONTENTS

SYNOPSIS

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

DESCRIPTION

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.

COMMAND

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.

OPTIONS

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.

METHODS

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"

KEY BINDINGS

Editor widget

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:

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.

Tableview 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.

Notebook tabs

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:

EXAMPLE

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

INSTALLATION

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.

DEMO

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

DOCUMENTATION

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

SEE ALSO

CHANGES

TODO

AUTHORS

The dgw::sqlview widget was written by Detlef Groth, Schwielowsee, Germany.

Copyright

Copyright (c) 2019-20 Dr. Detlef Groth, E-mail: detlef(at)dgroth(dot)de

LICENSE

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.