OK, how does this editor thing work...

• 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" 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.



• Using the Editor.
  
  • Deleting a record
    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.
  • Inserting a record.
    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.
  • Updating a record.
    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.
  • Exiting the Editor.
    To EXIT the editor click on the "close" button in the navigation area.
  • 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.
• Navigation
  
  • Navigation via Cursor
    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.
  • Navigation by Selection.
    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'" (no double quotes in the actual entry) 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.
  • 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.
• Miscellaneous
  
  • Copy and Paste
    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.
  • Manual Data Entry.
    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.
  • 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 become

    delete from foo where "c1"='b1' and "c2"='b2' and "c3"='b3'

    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 None,DEFAULT,BYTEA and LOBJ. If the strings None, DEFAULT,BYTEA or LOBJ are found in the buffer (case is important). The update and insert commands will be tweeked. Suppose in the above we have b3 containing None and b2 contains DEFAULT and a1 contains None. ( Psycopg will transform None into 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'

    If a BYTEA or LOBJ is found in the Buffer then the user will be asked to pick a file that will become the contents of the field or in the case of LOBJ the contents will be the oid of the large object. This will make importing images into the database every easy to do.

    Note If you change a field containing a large object oid you will "lose" the previous contents and will need to clean up the pg_largeobject table.

  ---

  The above discussion is more of a conceptual description of the actual implementation. What the code does is construct a "prepared" statement and an argument list. The values in the table ( apart from the buffer row) are actually fetched from a "cursor" A parameter list is constructed and a sql command with "placeholders" is constructed. The actual code will look something like

  curs.execute("sqlstringwithplaceholders", arglist)

Last update July 17,2006