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 <meta>, on Windows platforms <control>
and on Macintosh system <command>

Executing the SQL code.

The user has several options available to execute the SQL code in the editor window.

   <modifier-r> (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.

   <shift-return> 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.

   <shift-enter> is the same as <shift-return>

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 "<NULL>" 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 "\! <shell command>"
		
o The File menu has an item "Display null as <NULL>". When checked, null fields
	will be displayed in the grid as <NULL> 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:<nnn>" will be
	displayed. The <nnn> 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 <control-Button-1> 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. <Control-Up> and <Control-Down>
    will step back and forth through the history ( <Command-Up> and <Command-Down>
    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<NNN> 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 <table> 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. <Cmd-c>
          will copy the row. Click on the "Buffer" row and do
          a paste <Cmd-v>. 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