/[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

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

Parent Directory Parent Directory | Revision Log Revision Log


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

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