PgBrowse is a PostgreSQL client. It will allow the user to connect to a PostgreSQL
database and execute SQL commands (and much more).
Prerequistes:
Tcl/Tk (8.4.5 or better )
TkTable (2.8 or better )
Img (Needed to view images)
Tkhtml (Needed to view html documentation)
Pgtcl 1.5 or better (1.4 does not support the notice processor
http://gborg.postgresql.org/project/pgtclng ).
or
pgin.tcl (this is a pure tcl interface to postgresql 7.4 and greater
http://gborg.postgresql.org/project/pgintcl)
pgconnect.tcl ( a tcl program that logs into a postgresql database, in this package)
PostgreSQL ( Tested with 7.4.2 -8.1.1 note libpq needed to build Pgtcl))
Using the program.
When the program starts a login dialog is presented to the user. After a successful
login is made a new window will appear. This window is split into three areas.
The top area (editor) is used to enter SQL code. The code can be extended over multiple lines.
The user can enter multiple ( semicolon separated ) sql commands if desired.
The status (grey colored) panel is used to display result codes and notices from the
backend processor. Good news is written in blue, bad news in red and notices in magenta.
The third area is used for displaying the rows from the last select statement.
Starting with version 0.8 I have tried to make the code reasonably platform independent.
On unix system the modifier key is set to , on Windows platforms
and on Macintosh system
Executing the SQL code.
The user has several options available to execute the SQL code in the editor window.
(command=Apple key). Will send the contents of the entire window to
the postgresql backend one statement at a time. I have implemented a very crude
parser to separate the window into individual statements. A semicolon is deemed to
be a statement separator if it is preceeded by an even number of single quotes.
The result code of each of the statements will show in the status area. Only the
last select statement will be displayed in the table area.
will send any selected text to the back end. If no text is selected
then the line containing the insertion point will be sent to the back end.
is the same as
Future Directions:
This is a preliminary release, more to come :)
Version 0.6 Enhancements ( April 16, 2004)
There is a crude form of help available from the menubar. It looks
remarkably like the help in psql :)
SQL files can now be loaded into the editor window.
As long as the folder heirarchy remains intact, the program can be
executed from anywhere. ( Thanks for the tip "benny" ).
Version 0.7 Enhancements (April 17, 2004)
The table window now shows null values as "" so that the user
can distinguish between a null value and an emptry string.
The user can now save the contents of the last selection as either
a CSV style file with "|" as the seperator or as a formatted text file.
Version 0.8 Enhancements ( April 21, 2004)
This version is an attempt to be more cross platform independent. I
attempt to do the "right" thing with menus. Please advise on improvements.
I have also spliced in pgin.tcl. This is a "pure" tcl postgresql interface
it requires postgresql 7.4 or greater. pgin.tcl is available at
http://gborg.postgresql.org/project/pgintcl/ and was developed by
L Bayuk. He is also responsible for Pgtcl 1.5 I believe that pgin.tcl
requires a network connection and will not work for unix sockets.
Note that this version does not require libpg or any postgresql software
other than pgin.tcl. All you need is network access to a postgresql database.
Version 0.9 Enhancements ( April 26 2004)
I have redesigned the user interface to used the paned window widget. Note
this widget was introduced in Tk 8.4. This release adds a "Table Information..."
menu item to the File menu. When selected a listbox will appear that contains
the names of all of the files in the users catalog. After selecting a table name
the user can click the "Show Table" button to view the table or the "Describe Table"
button which will display information about the table columns and their data types.
Version 1.0 Enhancements (Nov 3 2004)
This version adds a number of enhancements.
o The ability to use psql (if present). If psql is found, psql meta-commands
starting with a "\" in the *first* character position are directly routed
to psql for execution. The results are added to the SQL window. In addition
if there is a selection in the SQL window, the menu item "Send Selection To
Psql" under the File menu will send the selection to psql for execution. The
results will be posted in the SQL window.
Note: Arbitrary (output only) shell commands can be executed from the
SQL window by the sequence "\! "
o The File menu has an item "Display null as ". When checked, null fields
will be displayed in the grid as otherwise null fields are displayed
as empty strings. The check mark toggles with menu item selection.
o The File menu has an item "Send Single Statements". When checked the program
will attempt to parse the string being sent to the backend into individual
statements and send each statement. If unchecked the entire string is
sent to the backend in one shot. Note the parsing algorithm is rather crude.
A semicolon is deemed to be a statement seperator if it not preceeded by
an odd number of single quotes ( don't use \' use '' ). Checkmark is toggled
by selection of the item.
o The File menu has an item "Show Long Fields". When checked the program will
attempt to load the entire field into the display grid. This can be
problematic when the database contains bytea columns containing a large
amount of data (think digital photos). If unchecked and the field length
is more than 200 characters then then string "LoNgFiElD:" will be
displayed. The will be replaced by the actual length of the field.
See below for a method that will, in many cases, allow the user to see
the actual contents of the field. The checkmark is toggled by selecting
the item.
o There is a contextual menu available for the Selection Results grid. The
menu is invoked by a click somewhere in the grid.
Selecting one of the menu items will control how the contents of the
selected field will be displayed ( the selected field is where the
mouse is when control-clicked). The field will be displayed in a separate
window. This window must be dismissed before another item can be viewed.
The menu items are:
o View As Text
The field is assumed to be plain text and the entire contents
of the field will be displayed.
o View As Bytea Text
The field is assumed to be plain text stored as a bytea object.
The entire contents will be "decoded" and displayed.
o View As Large Object Text
The field is assumed to be the "oid" of a large object containing
text. The contents will be fetched and displayed.
o View As Bytea Image
The field is assumed to be an "image" stored in a bytea field.
The image will displayed in a new window. Image means anything
supported by the Img package.
o View As Large Object Image
The field is assumed to be the "oid" of a large object containing
an image. The image will be fetched and displayed.
Version 1.1 Enhancements Oct 19 2005
o When the program starts it will look for a directory "~/SQLScripts". If not present
the program will attempt to create the directory. Any file in this directory
ending in ".sql" will appear in the "Scripts" menu. Selecting an item from
this menu will load the SQL code section with the contents of this file. The
code can recognize subdirectories of the script directory and ".sql" files
in the subdirectory will appear as menu items in the submenu named by the
subdirectory. There currently is not a refresh mechanism for the menu other
than restarting the program.
o The program remembers recently executed commands. and
will step back and forth through the history ( and
on the Macintosh). When the program is terminated by invoking the Quit command
in the File menu the history is saved as a tcl "set" command in the file
"~/SQLScripts/HiStOrY.tcl". As part of the startup process this file is
"sourced" to recover the previous command history. Quitting the program by
clicking on the close box does *not* save the command history.
o The Help menu has been updated to reflect version 8 of PostgreSQL.
Version 1.2 Enhancements Oct 22 2005
o Extended the contextual menu to include items
o Export Text Field...
The field is assumed to have a textual representation. The user will
be able to specify and store the contents in a file.
o Export Bytea Field...
The field is assumed to be a bytea field. The user will be able
to specify and store the contents in a file.
o Export Large Object Field...
The field is assumed to be the "oid" of a large object. The user
will be able to specify and store the contents in a file.
o The Script menu now displays a sorted list of menu items.
Version 1.3 Enhancements Nov 18 2005.
o The principle improvement in this release is the addtion of a simple
table editor.
o Accessing the Editor
The editor can be reached by choosing File->Table Info. This will
pop up a list box that displays all of the tables and views that
the user has access to. Clicking the button labeled "Edit Table"
or double clicking the table name will bring up the edit view
of the table. This view has a grid of the table and always has a "last"
row of fields that contain a single '*' character. Beneath the
grid is two rows of navigation tools. If the selected table
name is actually a view, then the editing buttons will be
disabled.
o Using the Editor ( The short version)
o To DELETE a record, navigate to a view that contains the
record and select the record by clicking anywhere on the
record. (The view always selects an entire row on receiving
a mouse click.) Click on the delete button and the record will
be deleted ( also any duplicates of the record!). You will be
given a chance to commit or rollback the transaction.
o To INSERT a record, fill all of the star'ed fields in the
last row with an appropriate value. Then click the
insert button. Again you will be given a chance to commit or rollback
the insertion.
o To UPDATE a record, navigate to a view containing the desired
record. Fill in the star'ed fields in the last row and then
select the record to be modified. Then click the update button.
A dialog will pop up showing how many records will be modified and
you will be given a chance to rollback or commit the transaction.
o To EXIT the editor click on the "close" button in the
navigation area.
o WARNING
If a cell contains LoNgFiElD the contents do not
reflect the true value of the table. Editing actions will
probably fail. This abreviated display can be surpressed
by ticking the "Show Long Fields" menu item in the File
menu of the main window.
o Navigation.
o The first row of the navigation bar has buttons "<",">" and a
field that can contain an integer. Adjacent to this group is
a menubutton labeled "Move" followed by a field that can hold
an integer. When the editor is opened a cursor is created and
these fields can control the placement of the cursor. When the
">" button is clicked the text field is checked its value
say "n" is used to fetch and display the the next "n" entries
of the table and the cursor is advanced appropriately. Clicking
the "<" fetchs and displays the previous n rows of the table.
Clicking the Move button will show a menu with two choices
"Move absolute" and "Move relative". Choosing one of these
item will move the cursor to an absolute position specified
by the textfield to the right of the menubutton, or to an
offset from the current cursor position using the contents
of the text field. No records will be fetched. Clicking on
the "<" or ">" will fetch from the new cursor position.
Cursors are frozen in the sense that as updates, inserts or
deletes are performed the changes will not be able to be seen.
To get around this problem after operations that change the
table the cursor is closed and then reopened.
It is my (limited) experience that it is difficult to find
information in the table by randomly scrolling through the
record set unless the table is quite small.
o The second row of the navigation area consists of a text
field and a button "Use Filter". Whatever is found in the
text field is appended to:
"select * from where "
When the "Use Filter" button is clicked the contents of
the resulting string is executed and the results displayed
in the grid.
Suppose we are editing a table named "bills" then if the
text field contained "category='Medical' and the Use Filter
button is clicked then the sql statement:
select * from bills where category='Medical'
would be executed and the rows would be displayed.
If the text field contained "true" (no quotes) then the
sql statment:
select * from bills where true
would be executed and ALL of the rows would be displayed.
This would not be a wise thing to do if the table had
millions of rows. However, we can limit the number of
rows displayed by placing:
true limit 1000
in the text field. The resulting statement would be
select * from bills where true limit 1000
This statement would retrieve 1000 rows from the
table.
o WARNING
The two navigation methods are totally
unconnected. If you display some records via the
filter mechanism and then click the ">" button
a new set of records will be displayed from the
cursors current position which is unaffected by
the Use Filter button.
o Miscellaneous
o Copy and Paste are useful. You can navigate to a page
containing the desired record to update. Click on the
desired row. This will select the entire row.
will copy the row. Click on the "Buffer" row and do
a paste . This will paste the entire row into
the buffer row where it can be modified.
o To manually enter data into the buffer row, click
on the first entry and start typing. The cells will
auto clear on the first keystroke. Tab or Right will
move to the next cell. Control-Tab or Left will back
to the left one cell.
o How does it work..
Suppose we have a table foo with columns c1 c2 c3
suppose further we have selected a row with contents
a1 a2 a3 and the buffer contains b1 b2 and b3. Then
the UPDATE command would look like
update foo set c1='b1',c2='b2',c3='b3'
where c1='a1' and c2='a2' and c3='a3'
An INSERT command looks like
insert into foo values('b1,'b2','b3')
The DELETE command would be
delete from foo where c1='a1' and c2='a2' and c3='a3'
Note that the contents of the cells are quoted by the
program. This needs a bit of tweeking to handle
the cases of the special values NULL and DEFAULT.
If the strings NULL or DEFAULT are found in the
buffer (case is important). The update and insert
commands will be tweeked. Suppose in the above
we have b3 containing NULL and b2 contains DEFAULT
and a1 contains NULL.
Then the above three commands will become
update foo set c1='b1', c2=DEFAULT, c3=NULL
where c1 is NULL and c2='a2' and c3='a3'
insert into foo values ('b1',DEFAULT,NULL)
delete from foo where c1 is NULL and c2='a2' and c3 ='a3'
Version 1.4 Enhancements and Fixes Dec 10, 2005.
o Fixed a problem where the global variable longFields was getting munged
when starting the editor. This prevented editing fields with more than
two hundred characters.
o I have added a "reserved word", BYTEA. Spelling and case are important.
If BYTEA appears in the buffer area (note no quotes) then when the update
or insert buttons are clicked a file open dialog will appear and the user
may choose a file to be used as the contents of the field for the operation.
The file will be (hopefully) properly escaped...Of course the column should
have the bytea attribute.
o I changed the initial size of the editor window to show just one record.
This came about when I tried editing a table with many multimedia
components and showing twenty records at once taxed my small system.
o Be sure to tick the "Show Long Fields" in File menu before invoking
the editor, updates will *not* work properly if a field shows
LoNgFiElD:mmm
in a table row that you want to update.
o Note that Tktable does not display excessively long fields
(think big Tiffs) properly. After you insert a graphics item
you can check the result by going back to the main window and
use the contextual menu to see the results. Note due to a bug
in the Img package, pdf items will hang the program when one
attempts to view the item via the contextual menu. ( You can
extract the item and use a PDF viewer to see the item.)
Version 1.5 released Dec 16, 2005
o Status window gives info on finding psql and the Img package at
program startup time.
o Fixed a problem where columns that had upper case letters in
their name were not recognized in the editor.
o Added a "reserved" word LOBJ. If LOBJ appears in the buffer
area of the editor then clicking the update/insert button will
open a file selector box and the user can select a file that
will be inserted into the database as a large object. The oid
of the created object will be placed into the users table
corresponding to the column where LOBJ appeared. The user
will be given a chance to rollback/commit the action.
One needs to take care in dealing with large objects. If
a row is deleted and that row contains the only reference
to the large object (its oid) then it can be a bit tricky to
recover the large object from the pg_largeobject table.
See the contributed vacuumlo application for a possible way
to clean up the large object table.
Version 1.6 released Dec 29, 2005
o Added "Postgresql Documentation" item to the Help menu. Selecting
this item will bring up a window that will display, in html, all
of the Postresql documentation using the package Tkhtml and a
modication of the hv.tcl demo program. This is probably most useful
to Windows users.
Version 1.7 released Jan 9, 2006
o Corrected error that prevented "Postgresql Documentation" from being
opened more than once per session.
o Added the ability to save the contents of the SQL window. By
default the saved file will have an extension of ".sql" and
will be written to the "~/SQLScripts" directory.
o Added the ability to refresh the scripts menu while the program
is running.
o Added MouseWheel support (each event scrolls 5 units).
Version 1.7a released Jan 17, 2006
o Added a "Open Scripts Folder" menu item to the Scripts menu. This
allows the user to open the Scripts folder on the Desktop.
o The "Save SQL Results As Formatted File..." menu item now will generate
reports that have numeric columns right justified.
o Renamed the "Table Info..." menu to "Table Editor & Information" to more
accurately reflect its use.
****************************************************************************
PgBrowser is a heavily reworked version of the Tcl PostgreSQL client that is
presented in the Douglas and Douglas book "PostgreSQL" ( Sams ). I also borrowed
from my Cocoa based browser BiggerSQL.
A special thanks for L J Bayuk for producing the Postgresql-Tcl interfaces this
program uses and for many helpful observations.
Enjoy the code.
Jerry LeVan
Homepage: http://homepage.mac.com/levanj
Cocoa : http://homepage.mac.com/levanj/Cocoa
Tcl/Tk : http://homepage.mac.com/levanj/TclTk
Perl : http://homepage.mac.com/levanj/Perl
Mail : Jerry.LeVan@eku.edu