/[gedafe]/trunk/doc/gedafe-sql.pod
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.pod

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 size: 16796 byte(s)
import of Gedafe 1.2.2

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

  ViewVC Help
Powered by ViewVC 1.1.26