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

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

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

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