Last Update:
2003-09-29 |
|
gedafe-sql - Database Structure for Gedafe
Gedafe is a generic web front-end to PostgreSQL. Generic means in this context that Gedafe does not know anything about the structure or contents of the database it is presenting.
The idea is that Gedafe looks at a database and presents it on the web. Because Gedafe is no AI it relies on certain rules for the database structure and naming rules.
It is also important to note that Gedafe itself does not impose any restrictions on to what a user can do with a database. All consistency and permission rules must be checked on the back-end side of the database. PostgreSQL offers excellent support for this kind of checks through referential integrity constraints, rules, triggers and stored procedures. The advantage of this approach is that data integrity does not depend on the usage of a certain front-end, but is enforced for all possible interactions with the database short of manipulation of the database structure and rules themselves. The disadvantage of this approach is that it imposes a large load on the database server, especially on insert and update queries.
Each field in the user defined tables must have a unique name. This simplifies writing queries because there is no need for fully qualified field naming. The unique names must be built by appending the field name to the table name with an underscore in between.
It is allowed to omit the table- for non ID fields, but you may not begin a column name with the reserved prefixes gedafe_ or meta_. Also the column names table_id and table_hid are reserved to ID fields (see below).
Example:
CREATE TABLE customer (
customer_name TEXT,
customer_age INT,
customer_address TEXT,
timestamp TIMESTAMP DEFAULT now(), -- lax naming ok here
meta_customer integer -- WRONG, "meta_*" is reserved
);
Using the table name + underscore prefix is nevertheless regarded ``good practice''.
Each table defines a unique, non speaking, numeric table_id field. It is best to use an auto-increment counter for this field. The field table_id can be used when a table is referenced from another table.
If a unique ID for human consumption is required, it must be called table_hid.
Often the table_hid will be presented to the user of the database, while internally the table_id is used as reference.
Example:
CREATE TABLE customer (
customer_id SERIAL NOT NULL PRIMARY KEY,
customer_hid TEXT UNIQUE,
customer_name TEXT,
customer_age INT,
customer_address TEXT,
timestamp TIMESTAMP DEFAULT now(), -- lax naming ok here
)
The reason we are requiering unique nonspeaking ids is twofold. First gedafe need some way to uniquely identify each database record (*_list, *_combo, ...) and second it is good design practice to use nonspeaking keys. In any event, if you need speaking keys, just use a *_hid on top of the *_id, and you are all set.
You will also note, that we are imposing some magic names, like *_list, *_combo, *_id, *_hid, ... we chose todo this, because we think it is simpler to name fields apropriately, than to write up another table telling gedafe about which field has what role in the database.
=head2 Table and Field Naming
For the benefit of a friendly presentation, each field and table in the database can have a description. Descriptions are added using the COMMENT command.
Example:
COMMENT ON TABLE customer IS 'Customers';
COMMENT ON COLUMN customer.customer_hid 'C Number';
When listing the contents of a table, the front-end uses ``SELECT * FROM table'' unless there is a view called table_list in which case the records are pulled from this view. The reason for this is that tables containing references to tuples from other tables might not be very presentable. The users don't want to see *_id fields. They want the corresponding *_HID field from the referenced table or maybe even more. Another reason for creating a *_list view may be that you do not want to present ALL records or fields to a user.
The first column of a *_list view must always be the *_id field from original table. This column does not get displayed if there is a *_hid column as well. The other column names should match their original counter part unless they contain calculated data (this is not enforced; however, no new comments for the fields of the view must be given if this convention is followed as described below).
Example:
CREATE VIEW absence_list AS
SELECT absence_id, pers_hid, absty_hid, absence_start,
absence_end, absence_days, absence_desc
FROM absence,absty,pers
WHERE absence_pers=pers_id
AND absence_absty=absty_id
AND NOT pers_virt;
A *_list view can contain rows which do not correspond to rows in the original table. These rows must have the value NULL in the *_id column. 'Editing' the data of such a row from the front-end causes a row to be inserted into the database. Defaults for the new row are taken from the edited NULL-row.
You may want to pre-sort the contents of a view. Unfortunately, PostgreSQL does not allow to ORDER views. As a work-around, you can create a column in your view called meta_sort which the front-end will use to sort the contents of the view as long as there are no other sorting instructions present.
You can set a COMMENT for each field of a *_list view, this comment will then be taken as column head. If you do not define a special comment, but the name of the list field is identical to the field in its parent table, then the COMMENT from the parent table field will be used. The same is true if the list field name matches the name of any other commented table field in the database.
IF
a column in some tableX references another tableY
AND
a column in tableX_list has the same name as the
referencing colum in tableX
THEN
Gedafe will make the text of the value of that
field in tableX_list a link to the edit form for
the referenced row in tableY.
E.G.: given this definition for the orders_list view:
SELECT orders_id, orders_date, customer_name AS orders_customer,
orders_qty, product_hid, product_description, orders_shipped,
((customer_name || ','::text) || (product_hid)::text)
AS meta_sort FROM orders, customer, product
WHERE ((customer_id = orders.orders_customer)
AND (product_id = orders_product));
[orders table]
ID Date Customer Quantity HID Description Shipped
1 2003-10-21 freek 2 tmp Temporary Product yes
In this case the customer "freek" will be a link to the edit form for
customer freek.
Note the customer_name as orders_customer and the customer_id = orders.orders_customer in the select.
All links are subject to ACL settings. No read and write: no edit link.
When editing fields that are a reference to a row in another table it is useful to have some help in filling out the field. Best is to provide a list of all possible values. The widget used to present this information to the user is called combo-box. In order to make this possible, each table which gets referenced by another table must be accompanied by a special view called table*_combo. This view must have two columns one called id and one called text. The id column contains the id of the referenced record. You can select the view to be taken for a certain field instead of the one chosen automatically be Gedafe by explicitely using the 'idcombo', 'hidcombo' or 'combo' widget attribute in meta_fields like explained in EDIT WIDGETS.
If you want to order the contents of the combo-box, then this can be done using an extra meta_sort column as explained below. By default, combo-boxes get sorted by the contents of their text column.
Example:
CREATE VIEW gender_combo AS
SELECT gender_hid AS id,
gender_name AS text
FROM gender;
CREATE VIEW proj_combo AS
SELECT proj_id AS id,
proj_name || ', ' || pers_hid AS text,
proj_popularity(proj_id) || proj_name AS meta_sort
FROM proj, pers
WHERE proj_pers=pers_id and proj_active=true;
The last example uses a custom function proj_popularity() which calculates a project popularity value which is then used as sort key in order to get the most popular projects first in the combo box. Again, this is not computationally cheap.
For some tables you may want to provide additional information to optimize their presentation in the front-end.
CREATE TABLE meta_tables (
-- Table Name
meta_tables_table NAME NOT NULL PRIMARY KEY,
-- Attribute
meta_tables_attribute TEXT NOT NULL,
-- Value
meta_tables_value TEXT
);
This additional information is specified for a given table in attribute/value pairs. The meta_tables table must be created in your database, even if you don't use it. The following attributes are currently defined:
- filterfirst
- If a table tends to be big, it is possible to suggest a field to filter on before a table is displayed. This is done by specifying the field name as value for this attribute. If you have a _list view for that long table, the filterfirst must be set on the _list table and one of its attributes. Because views will not contain any information about which table a certain attribute potentially references, this information must be specified in the meta_fields table accordingly.
-
Example:
-
I have this view called task_list which I want to filterfirst on the pack_id attribut. The combo I want to see comes from the pack table.
-
INSERT INTO meta_tables VALUES (DEFAULT,'task_list', 'filterfirst','my_pack');
INSERT INTO meta_fields VALUES (DEFAULT,'task_list', 'my_pack','reference','pack');
-
Note, that if pack had a _hid attribute, gedafe would figure it out and use this instead of the standard _id, assuming you would prefer _hid over _id.
- hide
- If the value of this attribute is set to '1', the table won't be shown by the front-end.
- showref
- Show references from another table to this table. The value is just the name of the referencing table (or a comma separated list of referencing tables. no spaces allowed).
-
if meta_tables contains a row that reads:
-
'table1','showref','some_other_table_that_has_foreign_keys_for_table1'
-
E.G.:
-
'products','showref','orders'
-
it will add a column to the listing of table1 (products) that tells you how many orders reference that product.
-
You would get something like this:
HID Description WWW-URL orders
tmp Temporary product http://tmp.org/ 2 items
-
The ``2 items'' value is also a link that will take you to the order table with searchfields set such that only the orders that reference this product will be shown. The creation of this table will of cource be subject to your privileges regarding reading from the other table. In case the reference is directed to the same table, or two columns of the second table reference the first table, you need to explicitly tell gedafe, which is the referencing column.
-
In the above example you would insert this line in meta_tables: 'products','showref','orders(orders_product)'
- twocols
- If this attribute is defined, it tells gedafe that wou want a 2 columns layout in your data add and edit screens for this table. The value should be 1 then. Gedafe then hands over an Attribute ``TWOCOL'' to the template editfield.html and toggles its value between 0 or 1 between 2 fields. So the template knows if a field is to be positioned left or right in a two column layout. The template has to evaluate this Attribute, look after the ``editfield.html'' example template.
- longcomment
- A long comment for a table. Will be printed in the Main menu screen, in the table header and pop up as tool tip when you place the mouse over the table links on top of each table. There is such a comment for the Customer table in the demo1.sql example database.
- editmask
- You can supply a template for the edit/add screen. If this value is defined, it is the filename of a template for the edit/add screen of this table and must be found in the templates directory. This template is a normal html snippet (without <html> and <body> tags!). It is placed verbatim between Edit/Add Header and Edit/Add Footer.
-
To make it work, for every field place ##XXXX_LABEL## at the places you want the field description and ##XXXX_INPUT## at the place you want the Input widget to be in your html layout. A very ugly example ``customer'' template is supplied for the ``customer'' table.
- quicklink
- A quicklink is shown at the botton of a list screen. You can offer links to different gedafe databases, external sources of information, gedafe pearls, gedafe onions, online help or whatever external web site.
-
A quicklink is defined as a URL, a optional icon and a description text.
-
As you can define 9 quicklink per Table, the attribute is quicklink(1) to quicklink(9) respectivly. The value is of the form:
-
foot(``http://example'',``/img/icon.gif'',``Example Quicklink'');
-
A empty icon is coded as a ``'' empty string.
For most fields is is possible to determine a presentation mode from the data type of the field. For some fields this is not possible and additional information must be provided. This is done through the meta_fields table. Every database must have a meta_fields table. It may be empty if no special information must be provided.
CREATE TABLE meta_fields (
-- Table Name
meta_fields_table NAME NOT NULL,
-- Field Name
meta_fields_field NAME NOT NULL,
-- Attribute
meta_fields_attribute TEXT NOT NULL,
-- Value
meta_fields_value TEXT
);
As for meta_tables, the additional information is specified in attribute/value pairs for the field in question. The following attributes are defined:
- align
- In listings of table content, this field overrides the alignment of the column. The contents of this field is put directly in the alignment section of the <td> tag. It is therefor good practice to put double quotes around left,right and center. e.g.: ``LEFT'' ``RIGHT'' ``CENTER''.
-
When not specified the alignment defaults to ``LEFT'' unless the column type is in [numeric,date,time,timestamp,int4,int8,float8]. In that case, the alignment is ``RIGHT'' NOWRAP.
-
=item copy
-
If set to '1', while adding entries to a table, the data for this field will be copied from one form to the next.
- hide_list
- Do not show this field in the list (table) view.
- markup
- If set to '1', the data in this field will be scanned for strings which resemble special web objects (currently implemented: e-mail addresses and http URLs). If found, these strings will be replaced with appropriate HTML markup (template variable DATA will be altered in element 'td').
- bgcolor_field
- Control the line background colour in list views. One column of the view is abused as html colour name like
#ffeeaa or red.
-
Select this abused column by setting the list view column attribute bgcolor_field to 1 for this colour column. This column is not displayed.
-
Instead you can just name this view column meta_bgcolour.
-
Nice for calenders (to show kind of weekday), to do lists (to show priority), bug list, etc.
-
If you want to give only one table column a different markup, you can produce the html that is needed for that with a SQL expression like this: '<div bgcolour=' || CASE WHEN urgent 'style=``color:red; font-weight:bold''' ELSE 'style=``color:green'''|| '>'|| date || '</div>' (be urgent a boolean column). Ugly, but it works.
- sortfunc
- If you have a column which contains names like 'von Burg' or 'di Alfredi' and you want the column sorted by the second word, you can create a function which converts the name into a string suitable for sorting. The name of this function must be given as value for this attribute. Note, however, that there is a heavy performance penalty, as this function must be called for each row of the table. A less costly approach during selects is the use of a meta_sort field as described above (the penalty there is again in inserts and updates).
- order
- Change the order of the fields in the edit screen. Gedafe reads the order of the fields from the table definition and assignes a ordering value to each field. The first field gets 1 the second 2 and so on. This value can be overriden with the 'order' attribute. Use floating values to move your fields around without having to change everything. Note that the *_id field is normally first and does not show up in edit view anyway, take this into account when counting.
- desc
- The labels in table headers and edit forms are normally pulled from whatever you set with the COMMENT command. You can override this with the 'desc' setting.
- widget
- Override Gedafe's automatically selected widget (element in the edit form to enter the value of a field). A widget definition is written like this:
-
widgetname(param1=val1,param2=val2,...)
-
If no parameters are specified, then the parens can be omitted. See chapter EDIT WIDGETS for a description of all available widgets and the available parameters.
- readonly
- read-only text
- text
- Text field. The optional argument size specifies the width of the text field in characters (default: 20).
- format_number, format_date, format_timestamp
- Formatted text input. The mandatory argument template specifies how the string should be converted from/to the number/date/timestamp. The corresponding PostgreSQL to_char, to_number, or to_timestamp function will be used with the given template.
-
Example: format_date(template=Mon DD YYYY)
- area
- Use a
TEXTAREA widget for this field, such that longer amounts of text can be entered. The optional rows and cols parameters (default 4x60) specify number of rows and columns for the textarea field.
- date
- The date widget on date columns generates a pulldown list to enter dates. The from and to parameters specify the the range for the year list.
- idcombo
- Text-entry, followed by a combo-box to select (text-entry has higher priority). The mandatory parameter combo specifies the name of the view to use to fetch the contents of the combo-box. See The Combo-box for a description of how to write views for combo-boxes.
- hidcombo
- Like
idcombo, but the text field references the hid in the referenced table. Additionally to combo, the mandatory parameter ref specifies the name of the referenced table (so that the hid can be converted to id).
- combo
- Like
idcombo but just the combo-box without text entry.
- radio
- Like
idcombo, but the values are presented as Radio Buttons. The optional parameter shownull adds a button for the NULL value, the optional parameter nulltext defines the label next to the INULL button.
-
Fixme: This widget should switch automatically to a combo box if there are more options then a configurable threshold.
- isearch
- isearch as a widget for a column that references another table specifies a interactive record selecting applet. The applet is designed to replace comboboxes on tables with many records where the combobox would become impracticaly large.
-
Because the isearch applet searches in all columns of the referenced table it ignores the table*_combo views, instead it use the original table or ,if present, the table*_list view. Also note that the values of bytea columns are translated to yield only the filename instead of the raw content.
-
The isearch applet will only download a search resultset that is smaller than 20kb to make sure that long waits are avoided. The progress of the download is displayed with a blue bar progress indicator. When the resultset is too large the user has the option to narrow down the search criteria by entering more characters to column search values and then rescanning the resultspace by pressing the the 'scan' button. Once a resultset is loaded entering characters to column search values immeadiately (interactivly) selects matching records.
-
Please also note that for this applet to work you need to have a browser that supports java, javascript and liveconnect. (Mozilla, Netscape and IE will do fine)
-
The isearch applet can be placed on a custom location on your web-server. Placing it in the cgi-bin directory of your server can lead to the web-server trying to execute the java archive instead of serving it. Make sure that the 'isearch' variable in your cgi-script is set to point to the web address of the isearch.jar java archive.
- file2fs
- If you want to handle big files with your gedafe frontend and you do not want to store them in the database but rather in external file, this widget is for you. It presents a file-upload box in the browser and stores the uploaded file on the machine where your gedafe frontend is running.
-
To make this work you have to Sepcify the root directory for your uploads in the gedafe.cgi with the file2fs_dir property.
-
The files2fs widget supports the optional uploadpath property which allows you to specify a subdirectory below file2fs_dir which the uploaded file will be stored.
-
file2fs(uploadpath=budgets)
-
Gedafe will make sure your upload will not overwrite any other file present in the directory. The stored filename will be relative to file2fs_dir so that files will still be found even when you modify the uploadpath.
-
If you upload a new file and there is already one noted in the database, the old file will be removed.
mncombo(mntable=publauthors[,combo=publauthors_combo])
- If you have two tables with an m:n relationship,
-
-------------- M N --------
| publications | ---------------------- | author |
-------------- --------
(publication_id) (author_id)
-
this is normally implemented in sql databases by creating a third table that holds the mn information, potentially with added meta information.
-
------------- 1 M ------------ N 1 --------
| publication | ---- | publauthor | ---- | author |
------------- ------------ --------
(publication_id) (publication,author) (author_id)
-
by default, gedafe would require you to edit the publauthor table seperately, entering all the valid publication/author combinations. This is not very user friendly. This is where mncombo comes in. Mncombo is a 'virtual widget' it can display the information of the mn-table in either the left or the right side table. This would enable you to select a list of author when entering a new publication. The mncombo widget would display two lists, one with all the authors and the other one with the authors you have selected for the particular publication.
-
The implementation of mncombo supports an optional sorting field so that the authors in our example can not only be asigned to a publication but also sorted.
-
The structure of the publauthor table must be:
-
publauthor_id SERIAL NOT NULL PRIMARY KEY,
publauthor_publication INT NOT NULL REFERENCES publication ON DELETE CASCADE,
publauthor_author INT NOT NULL REFERENCE author,
publauthor_order INT NOT NULL DEFAULT 0 -- optional,
UNIQUE(publauthor_publication,publauthor_author),
-
Note the ON DELETE CASCADE bit, it makes sure all the entries in the publauthor table refering to a publication that gets deleted are removed along with it. Without this setting, you would first have to remove all the authors from virtual combo before you could remove a publication. By NOT putting the same CASCADE on the _author field, we can ensure that no author gets removed from the database as long as he is listed as the author of a publication.
-
To tell gedafe about the virtual publication_authors field (represented by the mncombo widget) you would add the following to the meta_fields table:
-
INSERT INTO meta_fields VALUES(
'publication', 'publication_authors',
'widget', 'mncombo(mntable=publauthors)'
)
-
You may not want the publauthor table to show up directly in the UI:
-
INSERT INTO meta_tables VALUES(
'publauthor','hide','1'
)
-
Gedafe has to know how to present the list of authors in the interface so you have to create an author_combo view. If you want to use another view you can specify this via the optional combo argument of the mncombo widget.
-
To specify the position of the virtual publication_authors field in the publication form use:
-
INSERT INTO meta_fields VALUES (
'publication','publication_authors','order','3'
)
-
You may also want to specify a friendly label for the virtual field:
-
INSERT INTO meta_fields VALUES (
'publication','publication_authors','desc','Authors'
)
-
Note, that it is possible to use mncombo even in self-referencing setups. The problem is that in this setup an mn-table can encode even the direction of the relation ship. Person A can be owe person B as well as person B can owe person A and it is not the same thing. So while both id columns of the mn-table reference the same table, it is important to know which is which.
-
Normally it is enough for gedafe to look at the table a column references to figure out the setup. In the selfreferencing case the nameing of the columns will be taken into account too. The rule is to use the column whoes name ends in _${TABLE} as the left hand column.
-
The mncombo widget is implemented using the javascript in mncombo_javascript.html this gets loaded via the ##HEAD_SCRIPT## element in the header.html template.
Analog to the *_list views you can create as many *_rep views as you want, the only condition being that their names end in _rep. This can be used for simple reporting. For more complex reports you may want to check out our PearlReports module.
Example:
CREATE VIEW absence_rep AS
SELECT to_char(absence_start,'YYYY') as year,
pers_hid, absty_hid, sum(absence_days) as days
FROM absence, pers, absty
WHERE absence_pers=pers_id
AND absence_absty=absty_id AND NOT pers_virt
GROUP BY pers_hid, absty_hid, year;
Gedafe reads the access rights of the user to determine if a table can be accessed (SELECT must be granted), if a record can be added (INSERT must be granted) and if a record can be edited/deleted (UPDATE must be granted). Be careful about access rights in the database. They determine what users can do. To manage access rights more easily, it is sensible to grant rights to groups and add users to the groups according to the rights you want them to have.
Columns of the BYTEA type get a special treatment in Gedafe. These columns are used to store files in the database. Users can upload and download the file with their browser. To use these files in other software please keep in mind that the perl BYTEA encoding is not standard. The directory src/bytea provides information for using this data with Perl and PHP.
Copyright (c) 2000-2003 ETH Zurich, All rights reserved.
This library is free software; you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.
This library 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 Lesser General Public License for more details.
You should have received a copy of the GNU Lesser General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
Tobias Oetiker, David Schweikert, Fritz Zaucker, Adi Fairbank, Freek Zindel
|
|
