/[gedafe]/trunk/doc/gedafe-sql.txt
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /trunk/doc/gedafe-sql.txt

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1 - (hide annotations)
Mon Feb 14 18:52:26 2005 UTC (19 years, 1 month ago) by dpavlin
File MIME type: text/plain
File size: 19500 byte(s)
import of Gedafe 1.2.2

1 dpavlin 1 GEDAFE-SQL(1) gedafe GEDAFE-SQL(1)
2    
3    
4    
5     NAME
6     gedafe-sql - Database Structure for Gedafe
7    
8     SYNOPSIS
9     Gedafe is a generic web front-end to PostgreSQL. Generic
10     means in this context that Gedafe does not know anything
11     about the structure or contents of the database it is pre-
12     senting.
13    
14     The idea is that Gedafe looks at a database and presents
15     it on the web. Because Gedafe is no AI it relies on cer-
16     tain rules for the database structure and naming rules.
17    
18     It is also important to note that Gedafe itself does not
19     impose any restrictions on to what a user can do with a
20     database. All consistency and permission rules must be
21     checked on the back-end side of the database. PostgreSQL
22     offers excellent support for this kind of checks through
23     referential integrity constraints, rules, triggers and
24     stored procedures. The advantage of this approach is that
25     data integrity does not depend on the usage of a certain
26     front-end, but is enforced for all possible interactions
27     with the database short of manipulation of the database
28     structure and rules themselves. The disadvantage of this
29     approach is that it imposes a large load on the database
30     server, especially on insert and update queries.
31    
32     NAMING RULES
33     Unique Field Names
34    
35     Each field in the user defined tables must have a unique
36     name. This simplifies writing queries because there is no
37     need for fully qualified field naming. The unique names
38     must be built by appending the field name to the table
39     name with an underscore in between.
40    
41     Example:
42    
43     CREATE TABLE customer (
44     customer_name TEXT,
45     customer_age INT,
46     customer_address TEXT);
47    
48     Gedafe currently assumes that you follow this rule. If you
49     don't things will not work as expected. We are planning to
50     relaxe this requirement in the future (nevertheless it
51     will remain "good practice" :-))
52    
53     Non Speaking Unique ID
54    
55     Each table defines a unique, non speaking, numeric
56     table_id field. It is best to use an auto-increment
57     counter for this field. The field table_id can be used
58     when a table is referenced from another table.
59    
60     If a unique ID for human consumption is required, it must
61     be called table_hid.
62    
63     Often the table_hid will be presented to the user of the
64     database, while internally the table_id is used as refer-
65     ence.
66    
67     Example:
68    
69     CREATE TABLE customer (
70     customer_id SERIAL NOT NULL PRIMARY KEY,
71     customer_hid TEXT UNIQUE,
72     customer_name TEXT,
73     customer_age INT,
74     customer_address TEXT);
75    
76     The reason we are requiering unique nonspeaking ids is
77     twofold. First gedafe need some way to uniquely identify
78     each database record (*_list, *_combo, ...) and second it
79     is good design practice to use nonspeaking keys. In any
80     event, if you need speaking keys, just use a *_hid on top
81     of the *_id, and you are all set.
82    
83     You will also note, that we are imposing some magic names,
84     like *_list, *_combo, *_id, *_hid, ... we chose todo this,
85     because we think it is simpler to name fields apropri-
86     ately, than to write up another table telling gedafe about
87     which field has what role in the database.
88    
89     Table and Field Naming
90    
91     For the benefit of a friendly presentation, each field and
92     table in the database can have a description. Descriptions
93     are added using the COMMENT command.
94    
95     Example:
96    
97     COMMENT ON TABLE customer IS 'Customers';
98     COMMENT ON COLUMN customer.customer_hid 'C Number';
99    
100     VIEWS FOR PRESENTATION
101     Presentation View
102    
103     When listing the contents of a table, the front-end uses
104     ""SELECT * FROM" table" unless there is a view called
105     table"_list" in which case the records are pulled from
106     this view. The reason for this is that tables containing
107     references to tuples from other tables might not be very
108     presentable. The users don't want to see *_id fields. They
109     want the corresponding *_HID field from the referenced
110     table or maybe even more. Another reason for creating a
111     *_list view may be that you do not want to present ALL
112     records or fields to a user.
113    
114     The first column of a *_list view must always be the *_id
115     field from original table. This column does not get dis-
116     played if there is a *_hid column as well. The other col-
117     umn names should match their original counter part unless
118     they contain calculated data (this is not enforced; how-
119     ever, no new comments for the fields of the view must be
120     given if this convention is followed as described below).
121    
122     Example:
123    
124     CREATE VIEW absence_list AS
125     SELECT absence_id, pers_hid, absty_hid, absence_start,
126     absence_end, absence_days, absence_desc
127     FROM absence,absty,pers
128     WHERE absence_pers=pers_id
129     AND absence_absty=absty_id
130     AND NOT pers_virt;
131    
132     A *_list view can contain rows which do not correspond to
133     rows in the original table. These rows must have the value
134     NULL in the *_id column. 'Editing' the data of such a row
135     from the front-end causes a row to be inserted into the
136     database. Defaults for the new row are taken from the
137     edited NULL-row.
138    
139     You may want to pre-sort the contents of a view. Unfortu-
140     nately, PostgreSQL does not allow to ORDER views. As a
141     work-around, you can create a column in your view called
142     meta_sort which the front-end will use to sort the con-
143     tents of the view as long as there are no other sorting
144     instructions present.
145    
146     You can set a COMMENT for each field of a *_list view,
147     this comment will then be taken as column head. If you do
148     not define a special comment, but the name of the list
149     field is identical to the field in its parent table, then
150     the COMMENT from the parent table field will be used. The
151     same is true if the list field name matches the name of
152     any other commented table field in the database.
153    
154     The Combo-box
155    
156     When editing fields that are a reference to a row in
157     another table it is useful to have some help in filling
158     out the field. Best is to provide a list of all possible
159     values. The widget used to present this information to the
160     user is called combo-box. In order to make this possible,
161     each table which gets referenced by another table must be
162     accompanied by a special view called table*_combo. This
163     view must have two columns one called id and one called
164     text. The id column contains the id of the referenced
165     record. You can select the view to be taken for a certain
166     field instead of the one chosen automatically be Gedafe by
167     explicitely using the 'idcombo', 'hidcombo' or 'combo'
168     widget attribute in meta_fields like explained in "EDIT
169     WIDGETS".
170    
171     If you want to order the contents of the combo-box, then
172     this can be done using an extra meta_sort column as
173     explained below. By default, combo-boxes get sorted by the
174     contents of their text column.
175    
176     Example:
177    
178     CREATE VIEW gender_combo AS
179     SELECT gender_hid AS id,
180     gender_name AS text
181     FROM gender;
182    
183     CREATE VIEW proj_combo AS
184     SELECT proj_id AS id,
185     proj_name || ', ' || pers_hid AS text,
186     proj_popularity(proj_id) || proj_name AS meta_sort
187     FROM proj, pers
188     WHERE proj_pers=pers_id and proj_active=true;
189    
190     The last example uses a custom function proj_popularity()
191     which calculates a project popularity value which is then
192     used as sort key in order to get the most popular projects
193     first in the combo box. Again, this is not computationally
194     cheap.
195    
196     META INFORMATION
197    
198    
199    
200     The meta_tables Table
201    
202     For some tables you may want to provide additional infor-
203     mation to optimize their presentation in the front-end.
204    
205     CREATE TABLE meta_tables (
206     -- Table Name
207     meta_tables_table NAME NOT NULL PRIMARY KEY,
208     -- Attribute
209     meta_tables_attribute TEXT NOT NULL,
210     -- Value
211     meta_tables_value TEXT
212     );
213    
214     This additional information is specified for a given table
215     in attribute/value pairs. The meta_tables table must be
216     created in your database, even if you don't use it. The
217     following attributes are currently defined:
218    
219     filterfirst
220     If a table tends to be big, it is possible to suggest a
221     field to filter on before a table is displayed. This is
222     done by specifying the field name as value for this
223     attribute. If you have a _list view for that long table,
224     the filterfirst must be set on the _list table and one
225     of its attributes. Because views will not contain any
226     information about which table a certain attribute poten-
227     tially references, this information must be specified in
228     the meta_fields table accordingly.
229    
230     Example:
231    
232     I have this view called task_list which I want to fil-
233     terfirst on the pack_id attribut. The combo I want to
234     see comes from the pack table.
235    
236     INSERT INTO meta_tables VALUES ('task_list', 'filterfirst','my_pack');
237     INSERT INTO meta_fields VALUES ('task_list', 'my_pack','reference','pack');
238    
239     Note, that if pack had a _hid attribute, gedafe would
240     figure it out and use this instead of the standard _id,
241     assuming you would prefer _hid over _id.
242    
243     hide
244     If the value of this attribute is set to '1', the table
245     won't be shown by the front-end.
246    
247     The meta_fields table
248    
249     For most fields is is possible to determine a presentation
250     mode from the data type of the field. For some fields this
251     is not possible and additional information must be pro-
252     vided. This is done through the meta_fields table. Every
253     database must have a meta_fields table. It may be empty
254     if no special information must be provided.
255    
256    
257    
258    
259    
260    
261    
262    
263    
264    
265     CREATE TABLE meta_fields (
266     -- Table Name
267     meta_fields_table NAME NOT NULL,
268     -- Field Name
269     meta_fields_field NAME NOT NULL,
270     -- Attribute
271     meta_fields_attribute TEXT NOT NULL,
272     -- Value
273     meta_fields_value TEXT
274     );
275    
276     As for meta_tables, the additional information is speci-
277     fied in attribute/value pairs for the field in question.
278     The following attributes are defined:
279    
280     align
281     In listings of table content, this field overrides the
282     alignment of the column. The contents of this field is
283     put directly in the alignment section of the <td> tag.
284     It is therefor good practice to put double quotes around
285     left,right and center. e.g.: "LEFT" "RIGHT" "CENTER".
286    
287     When not specified the alignment defaults to "LEFT"
288     unless the column type is in [numeric,date,time,times-
289     tamp,int4,int8,float8]. In that case, the alignment is
290     "RIGHT" NOWRAP.
291    
292     copy
293     If set to '1', while adding entries to a table, the data
294     for this field will be copied from one form to the next.
295    
296     hide_list
297     Do not show this field in the list (table) view.
298    
299     markup
300     If set to '1', the data in this field will be scanned
301     for tokens which resemble special web objects (currently
302     implemented: e-mail addresses and http URLs). If found,
303     an additional template variable MARKUP will be defined
304     in template element 'td' which contains appropriate HTML
305     markup of the web object.
306    
307     sortfunc
308     If you have a column which contains names like 'von
309     Burg' or 'di Alfredi' and you want the column sorted by
310     the second word, you can create a function which con-
311     verts the name into a string suitable for sorting. The
312     name of this function must be given as value for this
313     attribute. Note, however, that there is a heavy perfor-
314     mance penalty, as this function must be called for each
315     row of the table. A less costly approach during selects
316     is the use of a meta_sort field as described above (the
317     penalty there is again in inserts and updates).
318    
319     widget
320     Override Gedafe's automatically selected widget (element
321     in the edit form to enter the value of a field). A wid-
322     get definition is written like this:
323    
324     widgetname(param1=val1,param2=val2,...)
325    
326     If no parameters are specified, then the parens can be
327     omitted. See chapter "EDIT WIDGETS" for a description of
328     all available widgets and the available parameters.
329    
330     EDIT WIDGETS
331     readonly
332     read-only text
333    
334     text Text field. The optional argument size specifies
335     the width of the text field in characters
336     (default: 20).
337    
338     area Use a "TEXTAREA" widget for this field, such that
339     longer amounts of text can be entered.
340    
341     date The date widget on date columns generates a pull-
342     down list to enter dates. The from and to parame-
343     ters specify the the range for the year list.
344    
345     idcombo Text-entry, followed by a combo-box to select
346     (text-entry has higher priority). The mandatory
347     parameter combo specifies the name of the view to
348     use to fetch the contents of the combo-box. See
349     "The Combo-box" for a description of how to write
350     views for combo-boxes.
351    
352     hidcombo
353     Like "idcombo", but the text field references the
354     "hid" in the referenced table. Additionally to
355     combo, the mandatory parameter ref specifies the
356     name of the referenced table (so that the "hid"
357     can be converted to "id").
358    
359     combo Like "idcombo" but just the combo-box without text
360     entry.
361    
362     isearch isearch as a widget for a column that references
363     another table specifies a interactive record
364     selecting applet. The applet is designed to
365     replace comboboxes on tables with many records
366     where the combobox would become impracticaly
367     large.
368    
369     Because the isearch applet searches in all columns
370     of the referenced table it ignores the
371     table*_combo views, instead it use the original
372     table or ,if present, the table*_list view. Also
373     note that the values of bytea columns are trans-
374     lated to yield only the filename instead of the
375     raw content.
376    
377     The isearch applet will only download a search
378     resultset that is smaller than 20kb to make sure
379     that long waits are avoided. The progress of the
380     download is displayed with a blue bar progress
381     indicator. When the resultset is too large the
382     user has the option to narrow down the search cri-
383     teria by entering more characters to column search
384     values and then rescanning the resultspace by
385     pressing the the 'scan' button. Once a resultset
386     is loaded entering characters to column search
387     values immeadiately (interactivly) selects match-
388     ing records.
389    
390     Please also note that for this applet to work you
391     need to have a browser that supports java,
392     javascript and liveconnect. (Mozilla, Netscape
393     and IE will do fine)
394    
395     The isearch applet can be placed on a custom loca-
396     tion on your web-server. Placing it in the cgi-
397     bin directory of your server can lead to the web-
398     server trying to execute the java archive instead
399     of serving it. Make sure that the 'isearch' vari-
400     able in your cgi-script is set to point to the web
401     address of the isearch.jar java archive.
402    
403     REPORTS
404     Analog to the *_list views you can create as many *_rep
405     views as you want, the only condition being that their
406     names end in _rep. This can be used for simple reporting.
407     For more complex reports you may want to check out our
408     PearlReports module.
409    
410     Example:
411    
412     CREATE VIEW absence_rep AS
413     SELECT to_char(absence_start,'YYYY') as year,
414     pers_hid, absty_hid, sum(absence_days) as days
415     FROM absence, pers, absty
416     WHERE absence_pers=pers_id
417     AND absence_absty=absty_id AND NOT pers_virt
418     GROUP BY pers_hid, absty_hid, year;
419    
420     ACCESS RIGHTS
421     Gedafe reads the access rights of the user to determine if
422     a table can be accessed (SELECT must be granted), if a
423     record can be added (INSERT must be granted) and if a
424     record can be edited/deleted (UPDATE must be granted). Be
425     careful about access rights in the database. They deter-
426     mine what users can do. To manage access rights more eas-
427     ily, it is sensible to grant rights to groups and add
428     users to the groups according to the rights you want them
429     to have.
430    
431     BINARY LARGE OBJECTS
432     Columns of the BYTEA type get a special treatment in
433     Gedafe. These columns are used to store files in the
434     database. Users can upload and download the file with
435     their browser. To use these files in other software please
436     keep in mind that the perl BYTEA encoding is not standard.
437     The directory src/bytea provides information for using
438     this data with Perl and PHP.
439    
440     COPYRIGHT
441     Copyright (c) 2000-2003 ETH Zurich, All rights reserved.
442    
443     LICENSE
444     This library is free software; you can redistribute it
445     and/or modify it under the terms of the GNU Lesser General
446     Public License as published by the Free Software Founda-
447     tion; either version 2.1 of the License, or (at your
448     option) any later version.
449    
450     This library is distributed in the hope that it will be
451     useful, but WITHOUT ANY WARRANTY; without even the implied
452     warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
453     PURPOSE. See the GNU Lesser General Public License for
454     more details.
455    
456     You should have received a copy of the GNU Lesser General
457     Public License along with this library; if not, write to
458     the Free Software Foundation, Inc., 59 Temple Place, Suite
459     330, Boston, MA 02111-1307 USA
460    
461     AUTHOR
462     Tobias Oetiker <oetiker@ee.ethz.ch>,
463     David Schweikert <dws@ee.ethz.ch>,
464     Fritz Zaucker <zaucker@ee.ethz.ch>, Adi Fairbank <adi@adi-
465     raj.org>, Freek Zindel <freek@zindel.nl>
466    
467    
468    
469     1.2.2 2003-09-30 GEDAFE-SQL(1)

  ViewVC Help
Powered by ViewVC 1.1.26