gedafe-user - the Generic Database Front-End (Gedafe) User-Manual
Gedafe (the Generic Database Front-End) is a web-based database front-end that is database-application independent. That means that the (perl) code doesn't contain any information about what tables are present in the database or how the data is organized.
This is only possible if a full-featured SQL dB server like PostgreSQL is used as backend. PostgreSQL permits to define not only the format of the various tables and fields, but also how tables are related to each other. It is even possible to write powerful functions inside the database which get executed as new data is accessed or modified. Together, these features allow the implementation of data integrity constraints inside the database itself.
The effect of this is, that the database server guarantees the integrity of the database, independently from the software used to access the database. A front-end can read all the integrity constraints directly off the database and enforce them itself in order to provide faster response to the user, but at the end of the day the database server will only accept data which follow the rules defined by the database programmer.
Overall, this approach makes the creation and maintenance of database applications much simpler. New databases can be created on the database server alone, using the language which is best suited for this task: SQL. The front-end then comes almost for free.
Gedafe has the following features:
- It is completely generic. Gedafe does not need to know anything about the structure or contents of the database it is working with.
- Authentication is done at the database-level.
- It is URL transparent. This means, you can directly access the URL of the page you want to look at. If necessary, the login screen will pop up and after a successful login you will be presented the page you initially requested.
- Almost no HTML is used in the front-end code. External template files define the look and feel of pages.
- POST is used only for data that modifies the database. Every page has a distinct URL, which makes them storable as bookmarks (Deep Linking).
- Double form protection. The front-end prevents accidental repeated submission of the same form. This is implemented using a unique serial number for each form.
- Using the PearlReports integration, it is possible to write custom multilevel report modules (Pearls) available from the Gedafe webinterface.
The structure of the Gedafe distribution is as follows:
|-- bin/ binaries (gedafed daemon)
|-- doc/ documentation
| `-- perl/
| |-- Gedafe/ gedafed main source
| |-- DBIx/ PearlReports (required for gedafe pearls)
| `-- Text/ CPPTemplates (required for gedafe screen layout)
|-- templates/ HTML templates
|-- mypearls/ Sample pearl report module
|-- demo1.cgi demo-application script
`-- demo1.sql PostgreSQL script to initialize the
In order to start using gedafe you must ensure that lib/perl of the Gedafe distribution is found by perl and start the gedafed daemon (you can use the script gedafed-ctl to start it with SYSV-init).
Gedafe is actually a library. The application itself just calls the 'Start' function of the library, providing the necessary configuration information as arguments. The application startup script should look as follows:
use lib "/usr/local/gedafe/lib/perl";
db_datasource => 'dbi:Pg:dbname=demo',
list_rows => 15,
templates => '/usr/local/gedafe/templates/demo',
documentation_url => 'http://mysite.com/demo-docs',
show_row_count => 1,
isearch => '/place/in/the/webtree/for/isearch.jar',
pearl_dir => '/usr/local/gedafe/example/mypearls',
oyster_dir => '/usr/local/gedafe/example/myoysters',
list_buttons => 'both',
edit_buttons_left => 1,
file2fs_dir => '/var/gedafe/upload_dir',
Gedafe gathers information about the database structure when it is started. This process can take a lot of time, it is therefor strongly suggested that you use a persistent perl instance, for example speedy. mod_perl works also great, but you have to be careful if you run multiple database applications, since if the same persistent perl is used, the cached data of the applications will go in the same global variables, which is certainly not what you want.
Of course, you must specify the correct path name to your perl interpreter in the first line of the script (unless you use a webserver perl module).
Very important in this script is the first 'use' statement. It should point to where you have stored lib/perl of the distribution. Start starts the application by specifying Gedafe configuration variables. The following configuration variables are defined:
- DBI data-source string specifying the database.
- Default number of rows to show.
- The directory where the html templates are stored (you can use a copy example/templates as a basis for you local modifications).
- URL passed to the html templates where the documentation of the application is stored.
- Options: [0,1] If set, show a count of total records returned by each select, along with extra navigation links to skip to first and last pages of result set. Since this produces slightly higher database overhead (an added SELECT
COUNT(*) for every SELECT), it is turned off by default.
- Web-servers don't like Java archives (jar) to be down-loaded from cgi-bin directory's. They will try to execute them instead. To resolve this, you have to place the 'incremental search widget' (isearch) Java archive in a place where it can be down-loaded like any other file. This item is used to point gedafe to the place where you have put the isearch.jar. Please make sure that it is on the same server and preferably a relative address. Java security restrictions require this.
- Options: ['top','bottom','both','none'] This option acts on the buttons that appear with table or view lists, the first,previous,add,next,last buttons. Top selects only buttons above the list. Bottom selects only buttons below the list. None removes all buttons, but doing so wouldn't make much sense. When omitted the default is 'both'.
- Name of a directory where gedafe should go looking for extra widgets. See gedafe-widget.pod for more information.
- Name of a directory where gedafe should go looking for pearls. Pearls are object oriented perl modules which first display a data input screen and then run a report off the database based on the entires given at the data entry screen. See gedafe-pearls.pod for more information.
- Name of a directory where gedafe should go looking for Oysters. Oysters are object oriented perl modules which allow developers to implement mini-applications while concentrating on the task at hand rather than the gotcha's of html-based software engineering. See gedafe-oysters.pod for more information.
- If you use the file2fs widet for uploading files you have to specify the root directory for the uploads otherwhise uploads will be disabled. See the file2fs widget for more information.
- See ``Schema Support''
- See ``Schema Support''
- When set to 1 enables the parsed way of searching even when Parse::RecDescent is present on the system. See also: gedafe-search.pod
- If set to 1, the buttons for edit, clone and delete are shown on the left, which is useful for very wide tables.
- edit_show_id = 'edit' makes the id column visible in EDIT screens. (edit_show_id = 'edit+add' makes the id column visible also in NEW screens. This has the side effect of incrementing serial id's even if you abort inserting a new value. Also the id jumps in steps of 2) The id is always ``read-only''. This can be used in two ways:
- The id column has a default, but you want to know the id value while editing (or inserting) a record.
- You want editable id values or you want to supply the id values by hand. This will not work. In this case we recommend to use a HID column.
Gedafe uses an external process called gedafed to manage session data. This daemon must be running to make Gedafe work. You can start it during the boot process of your server using the bin/gedafed-ctl script.
gedafe-sql.pod describes how the database should be setup to work with Gedafe.
Authentication is done with the help of gedafed. This daemon stores user/password pairs using a random-generated ``ticket'', which is stored in a cookie on the client side. To make these tickets more secure gedafed manages an expiration on these tickets. Every time that ticket is used, it's expiration is prolonged by a certain amount of seconds (configured in the script). If the database isn't accessed for a certain amount of time, the ticket is expired and a new login must be made.
The login screen is transparent to the page accessed: whenever a login is needed, the login screen is first presented, after which the requested page is shown.
Warning: Gedafe will not work with blank passwords. If you want to do anonymous logins, you may put the user in the url (as an additional parameter, user=xxx&...) and the password sent to the database will be 'anonymous'.
The navigation and general use of Gedafe should be straightforward. At the beginning, you are presented with the ``Entry'' page that contains links to every table to edit and to every available report.
For forms, the guiding principle while designing Gedafe was 'POST is evil, use it the least possible'. The reason for it is that if a generated page depends on POST data, that page can't be stored in a bookmark and the browsers have problems handling the reloading of pages obtained with a POST request. For that reason, POST was used only for database-modifying actions where large amounts of data must be transferred.
You can search on multiple fields, which are represented in the list view as multiple text fields on different lines. Each field must match. In each of the fields, you can type a search term using the following syntax:
- simple 'ILIKE' matching of the selected field
- OP text
- use OP instead of 'ILIKE'. Can be '<', '<=', '>', '>=', '=', '~'.
- NOT expr
- Negate expression
- is [not] null
Further, you can also join such expressions with 'OR' and 'AND. For example:
> 2004-01-01 and < 2004-12-31
Almost no HTML is used in the perl code. The HTML is generated with the help of Text::CPPTemplate, a very simple C-preprocessor-style templating system included in the Gedafe distribution. The templates are taken from a directory specified in the startup script with the 'templates' parameter.
The basic idea is that Gedafe places small ``elements'' of the page currently being generated such as the header or the cell of a table by only specifying variables (properties) of that element. Every element has always the following minimal variables specified:
- Name of the page (for example login, entry or list).
- Name of the element (for example header or td).
In addition, element-specific data such as DATA for the td element must be defined. Text::CPPTemplate will then search for an appropriate template to use and generate the HTML code. See the Text::CPPTemplate(3) manpage for a description of the syntax and how the templates are stored in files. See also gedafe-templates.txt for a description of what elements are used with what variables.
It is possible to use schema support if Postgres is Version 7.2 or newer. For database versions < 7.2 schema support is disabled. No test have be done yet if Postgres versions < 7.2 really work after introdution of schema support.
A schema is a group of tables in postgres. You can order your tables by moving them in several schemas without changing views and select statements.
If you add more then one schema to your gedafe application, the top of the Entry screen shows links into the other schemas instead of table links.
Schema support is activated with 2 optional config parameters for Start.pm. These values are:
- defines the default schema, i.e. the schema we want to start in. Gedafe only shows you tables in the ``current'' schema. The current schema is the default schema if defined else 'public'. You can override the default schema with the URL parameter schema=.
Syntax: A comma separated list of single quoted schema names like ``'public','test''. Is ignored, if schema is not set.
Defines the group of schemas we want to make accesible for gedafe. To be exact, all tables are acessible by prefixing their schema name (e.g. testschema.testtable is table testtable in schema testschema). You can use schema prefixes in sql commands, e.g. in views. But gedafe itself does not prefix table names. To reference tables from more then the 'public' schema, we have to put the schema names in a schema_search_path.
When setting a schema_search_path, you only will see links for tables in the ``actual'' schema but you can access all tables within the search path by their names as if they were in the current schema.
CAVEAT: Beware of name collisions.
Never put 2 tables with the same name in two schemas (inside your search path), as the results can be hard to predict. Also always put 'public' in your schema search path. Put the tables meta_fields and meta_tables in the 'public' schema. The effects of having several meta_fields and meta_tables will be hard to predict.
=head2 Hidden features
- In the URL:
list_rows=nn override the number-of-displayed-rows specified in the startup script.
- In the URL:
theme=xxx set the theme (templates will be loaded from that subdirectory of the templates directory)
- In the URL:
reload=1 reset all the cached data. This is useful, for example, if you changed a template file or the structure of the database.
yesterday can be specified as search value for a 'Date' field.
- Numbers can be entered as
hh:mm (for example
mm part will be multiplied by 100/60 and added to
- Some supporting perl modules are auto-detected and only used if they are installed on the system gedafe is running on. These are currently:
- Text::CSV_XS : for exporting data as comma-separated value (CSV) format; if not installed, only the default tab-delimited format will be available for exporting data.
A difficulty that we encountered while developing Gedafe was the caching of pages by the browser. We have to control precisely when a page can be cached and when not. The implementation is made with the
refresh URL parameter: when it is set, the expiration of the page is set to some positive value, meaning that the page can be cached. If
refresh is not available, the expiration is negative, meaning that the page should not be cached. The value of
refresh is a random number, that can be changed to force a reload of the page.
A side-effect of this technique is that pages with
refresh in the URL are not suitable to be stored as bookmarks, since you would then get always the same cached version. For that reason, bookmarks should be always saved without the
refresh parameter, such that a new version of the page is always requested from the server. There is a link on every page, that you can drag to store the currently viewed page.
When you get an empty form after selecting 'Edit' for a record, this could mean that you didn't put the record *_id into the first column of the presentation (*_list) view or the table (if there isn't a presentation view). Gedafe must know the id of the record to edit and it does so by using the first column as key. See the gedafe-sql.pod, section 'Presentation View'.
gedafe-sql.pod, gedafe-templates.txt, Text::CPPTemplate, gedafe-pearls.pod, DBIx::PearlReports
Copyright (c) 2000-2003 ETH Zurich, All rights reserved.
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
Tobias Oetiker, David Schweikert, Fritz Zaucker, Adi Fairbank, Freek Zindel