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) |