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