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