DBEditor

Introduction

How many times did you have to build some custom interface for database backends to display and manage their content? Alhought the code might be quite similar there are always some adjustments for each different database. Doing this over and over again is tedious. Not anymore...

Challenge

All you have is a "Z MySQL Database Connection" object; nothing else. Build a database viewer/editor which pulls all needed information dynamically from this object. No hard-coded stuff is allowed to make it work.

Developer

Lorenz Textor, our developer, accepted the challenge and has built the dbeditor. Lorenz is well known in the Mac community because he is the developer of the famous 5 star CocoaMySQL application.

Product

Requirements

The dbeditor requires Zope, python-mysql and ZMySQLDA. Currently, Silva is used only for controlling the access to the dbeditor and its features (tested with Silva 1.1.x and 1.5.x).

Basic Features

• no hard-coded parameters necessary (but you can hard-code parameters to customize for specific databases; see below Advanced Features)
• nice user feedback of errors (Fig 1)
• view and sort tables (Fig 2)
• limit search display to 10, 20, 50, 100 or all rows
• select columns to be shown (Fig 3)
  
• search / advanced search (Fig 4, Fig 5)
  
• limit search to previous search results
  
• view, add, edit, duplicate and delete rows (Fig 2, Fig 6)
• date widget for easy entering of dates (Fig 7)
  
• export data as CSV and tab delimited files encoded in UTF-8, ISO Latin-1 or MacRoman format (Fig 8)
  
• printer friendly version
• field type aware: checks for the field types (e.g. char, integer, date, enum) and presents sensible options
• selection aware: All available options are stored in a temporary session. E.g. after a search only the search result is printed.
• optional action logging and undoing
• save database and table selection, sort, filter etc. to cookies and reload it later
  

Advanced Features

It might be convenient to parameterize the dbeditor to fit better to the databases. For this task there is a configuration panel accessible in the ZMI of the dbeditor (Fig 9).

Database Connection

• Choose the Z MySQL Database Connection object you want to use to connect to MySQL.

General Settings

• Show Table Selector: Show or hide the database and table drop down menus.
  
• NULL string: The NULL String is used to display MySQL NULL values in list and details view.
• Footer: The footer is displayed at the end of each page
• Preselect Table: Specify a table which is loaded when no database and table are selected.
  
• Log to Table: Specify a table here to enable logging. Users with "Add or remove data" privileges (see below) can view the log containing all performed database actions and they can also undo these actions. See README.txt for necessary log table structure.
  

Access Settings

• Access to features to a certain database is given by the privileges of the mysql account used in the database connection. If the connection allows it, additional fine grained access control can be set with the Access properties. E.g. you might want to hide certain colums from certain users, or you might give access to editing the data but don't want anybody to delete or add new data. The following table shows the default settings which can be changed in the configuration panel of the dbeditor.

Columns Settings (Fig 10)

• Use this settings to control the display of the tables in the list and details view. Order and mapping can be different for list and details view.
  
• Order: Use the arrows to change the order of the columns. The new column order is only saved if you click on "Save"!
• Mapping: Table column names can be mapped to a more user friendly name. E.g. IndepYear -> Independence Year,  GNP -> Gross National Product.
  
• Group Heading: The group heading is displayed before the column in detail and edit view; use it to group columns.
• Help: The help is displayed next to the column data in detail and edit view.
• Hidden: Hidden columns are not displayed in the list view by default, but can be enabled by the user.
• Secret: Secret columns can be viewed only by users with "view secure columns" privileges (see above). Not everybody should see everything;-)

Save Creator, Last Editor, Creation and Modification Time

If you want to automatically save the creator, last editor, creation time or modification time of records you have to create the following fields in your table:

To Do List

See separate page.

Download and Installation

• The DBEditor-1.3 can be downloaded here. (history)
• Unpack in Products folder of zope instance.
• Add a Z MySQL Database Connection in the same folder or above.
• Add DBEditor in ZMI.

Upgrade

• Delete old DBEditor from Products folder of zope instance.
• Unpack new DBEditor in Products folder of zope instance.
• Refresh the DBEditor in the ZMI (go to "Control_Panel/Products/DBEditor", click on the "Refresh tab" and then on "Refresh this product").
  

Feedback

• Please send any comments and feedback to the following address:
  dbeditor at mat.ethz.ch
• Please send us your changes and enhancement so we can include it in the next version.

README.txt, LICENSE.txt

• README.txt
• LICENSE.txt