Postgres Forms (pfm) is a client application with a graphical user interface for the PostgreSQL data base server. It enables the user:
Postgres Forms is implemented in Tcl/Tk, but there is no need for the user to program anything in Tcl/Tk. The user has to use SQL for creating tables and views and for designing forms and links.
Postgres Forms makes no attempt to hide the underlying SQL. On the contrary, in most cases, it shows both the SQL statements it sends to the PostgreSQL server and the results it gets back.
Postgres Forms has been designed and tested with version 8.4 of Tcl/Tk and with version 7.4.2. of the PostgreSQL database server. It has also been tested with PostgreSQL 8.1. For more information on Tcl/Tk and PostgreSQL look at http://www.tcl.tk and http://www.postgresql.org respectively.
The latest version of pfm includes the 'tclkit' for Linux on PC. This means that it can also run on Linux PCs that do not have Tcl/Tk installed.
The latest version of pfm is also available for Windows XP and it includes the 'tclkit.exe' which means that it does not require a separate installation of Tcl/Tk. It may work on other versions of Windows, but it has only been tested on Windows XP.
Postgres Forms needs one of the Tcl interface packages pgtcl, pgtclng or pgintcl to communicate with PostgreSQL database server. All three can be obtained from http://pgfoundry.org/.
Two versions of pgintcl (1.5.0 and 3.0.1) are included in this version of pfm.
Postgres Forms (pfm) is a client application for PostgreSQL.
Copyright © 2004-2007 Willem Herremans
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
A copy of the GNU General Public License can be displayed with the menu item 'License' under 'Help'.
Please send bug reports and feature requests to the address that you can find in README.txt.
The home page of pfm can be found at
http://pgfoundry.org/projects/pfm
The following figure shows the main window of Postgres Forms (pfm):
Above the listbox, there are 2 radio buttons:
In "Normal mode" only the user defined forms are visible.
In "Design mode" only the forms for the pfm_* tables are visible. These are the forms which can be used to define forms, links, valuelists and reports.
Here is a short description of the pfm_* tables:
See section "6. The pfm_* tables" for details.
You can invoke the on-line help by selecting "Help file" in the "Help" menu on the main window.
Pressing F1 on the main window has the same effect.
The help file is displayed in the web browser of your choice. See 4. Configuring pfm for how to choose a web browser.
By clicking the right mouse button the "context menu" pops up.
It is a classic "edit menu" with menu items: Copy, Cut and Paste.
You can use it to copy text to/from the clipboard.
This menu is not restricted to the main window, but works on all the windows of pfm.
It is possible to operate pfm without using the mouse. Not all keyboard procedures are explicitly mentioned in this manual because they follow some general rules which make it very easy to know them.
Clicking with the mouse on a widget moves the input focus to that widget. The widgets on a window that can receive input focus are arranged in a circular list. Pressing the Tab key moves the input focus forward along the list. Pressing Shift-Tab moves the input focus backward along the list.
Selecting "License" in the "Help" menu on the main window causes the "GNU General Public License" to be displayed.
Selecting "About" in the "Help" menu on the main window displays some general information about postgres forms, such as: version, copyright and whether it is using Pgtcl or pgintcl to communicate with PostgreSQL.
After selecting 'Open...', from the Database menu on the main window, you are prompted to enter the parameters for opening a data base:
Notes:
~/.pgpass;
on Windows platforms the password file is
APPDATA\postgresql\pgpass.conf
where APPDATA is the user's application data directory, e.g.
C:\Documents and Settings\'username'\Application Data
Default values for the parameters host, port, user and dbname are read from the user's options file (see section 4. Configuring pfm).
All parameters can be typed directly on the window that is displayed. The 'dbname' can also be selected from a list 'dblist' defined in the user's options file.
After a database has been successfully opened, its name is stored in the 'dbname' option and added to the 'dblist' option if it is not in the list yet.
When you open a database, pfm checks for the presence of the pfm_* tables.
If pfm does not find the pfm_* tables, you get a hint that you can install them using the 'Tools -> Install pfm_* tables' or the 'Tools -> Install example database ...' menus.
On a database without pfm_* tables, pfm only allows you to execute SQL statements using the RunSQL feature.
On a database with pfm_* tables, pfm allows you to also design and use forms and reports.
When you open a database that was previously managed by an older version of pfm, you will be asked whether you want to convert the pfm_* tables to the new format.
If you choose "yes", the pfm_* tables are converted to the format compatible with the new version of pfm.
Note:
Beware that there is no way back: i.e. there is no automatic conversion from the new to the old format. So, to be sure, it is recommended to first make a backup of your database using PostgreSQL's "pg_dump" before answering "yes" to this question.
If you choose "no", the database is opened but not modified in any way. Since the format of the pfm_tables is not compatible with the new version of pfm, some features like defining and using forms and reports may not work properly, but you will be able to execute SQL statements using the RunSQL feature of pfm.
By selecting the Close menu item from the Database menu on the main window, you close the currently open data base. The list-box on the main window becomes empty.
To install the pfm_* tables in the currently opened database, select 'install pfm_* tables' from the Tools menu on the main window.
There are two example databases included in the pfm package:
Using 'Tools -> Install example database', you can install one of these example databases in the currently opened database. This database should be completely empty. It should not even contain the pfm_* tables.
The easiest way to obtain a completely empty database is to create a new one:
CREATE DATABASE exampledb ENCODING = 'LATIN1'; or
CREATE DATABASE exampledb ENCDOING = 'UNICODE'
This assumes that you are known to PostgreSQL as a user who has the right to create databases.
See section 10. The example databases for more details on the structure of the example databases.
The example databases only contain characters of the LATIN1 character set. So, LATIN1 encoding is sufficient. UNICODE is more than sufficient. See section 9.2. Character encoding for more details on character encoding.
When you select the menu SQL on the main window's menu bar, the following window is opened:
It has two text areas, one for the SQL statements, another for the output.
This tool is in fact nothing more than a GUI-frontend for the psql interactive terminal that is included in PostgreSQL.
When you press the [Run] button, all the text present in the SQL statement text area is sent to psql.
Don't forget to end SQL statements with ";". If you forget this, the statement is not executed by psql until you type ";" and press [Run].
Sometimes, the Run SQL feature appears to be dead, i.e. it is not responding to commands anymore. In many cases that is due to mismatched parentheses or quotes. E.g. if you enter
SELECT * FROM pfm_form WHERE (name='pfm_attribute';
psql does not respond until it gets a ')'. If you run psql in an xterm, it displays a prompt ending with '(>', indicating that there is still an unmatched '('. In pfm however, psql does not display this prompt, with the result that it does not seem to respond. If you cannot guess what is wrong, you can close and reopen the database to recover from this frustrating situation.
Apart from SQL statements, you can also enter the psql "\" commands. E.g.
These help functions can also be invoked from the Help menu on the "Run SQL" window.
Every time you press [Run] the commands in the SQL area are stored in the command history. You can scroll through the command history using the [Previous] end [Next] buttons.
Note:
Alt-p and Cntrl-Up are alternatives for the [Previous] button.
Alt-n and Cntrl-Down are alternatives for the [Next] button.
The output area is not cleared when another run is done, i.e. the output of a new run is appended to the text already present in the output area. You can clear the output area explicitly by pressing the [Clear] button.
You can also import SQL statements from a file by selecting "Import SQL from file" in the SQL menu on the "Run SQL" window.
After having selected the file to be imported, you are asked to select the character encoding. The default is the same as your system's default encoding. Usually, that is OK, but if you know that the file to be imported was made with another character encoding, you can select it here.
Next, you are asked whether to offer the file to psql via the '\i' command or to import the file into the SQL window. For large files it is recommended to offer the file to psql. pfm sometimes hangs for no obvious reason when large files are run from the SQL window.
Notes:
The text in the output area can be printed or saved to a file, by selecting respectively "Print" or "Save" in the "Output" menu on the "Run SQL" window.
The 'Print output' feature is described in more detail in section 3.4. Print output
Note:
The output area is shared between the "Run SQL" and "Run report" features. Since version 1.5.0, the output area is cleared when running a report. So, it is no longer possible to combine the outputs from both features.
When you select the Report menu on the main window's menu bar, the following window is opened:
The upper part of the window, shows the list of reports that have been defined in the database.
When you press the [Run] button or the 'Return' key, the highlighted report is executed and its output is appended to the text already present in the output area.
The text in the output area can be printed or saved to file, using respectively "Print" or "Save" in the "Output" menu on the "Run report" window.
The 'Print output' feature is described in more detail in section 3.4. Print output
Note:
The output area is shared between the "Run SQL" and "Run report" features. Since version 1.5.0, the output area is cleared when running a report. So, it is no longer possible to combine the outputs from both features.
How to define a report is explained in a section 8. Designing a report.
When you select "Print" in the "Output" menu on the "Run SQL" or "Run report" windows, a window is displayed which shows the print command as it is defined by the option 'printcmd' (see section 4. Configuring pfm).
The 'printcmd' calls an application that accepts plain text as input. It can be either a printer program that directly sends the text to a printer, or a text editor or word processor that accepts the text generated by pfm as input and that offers you the possibility to choose things such as font, font size, margins, page orientation etc. before sending it to a printer from there on.
'%s' in the command is a placeholder for the name of the temporary file in which pfm stores the text to be printed. If the command does not contain '%s', pfm does not use a temporary file and sends the text to the 'standard input' of the application being called (works only on UNIX with certain applications such as lpr, a2ps, etc.)
The command may also contain parameters for which the user is prompted to provide a value when the command is called. The syntax is as follows:
$(parameter_name=default_value)
where '=default_value' is optional.
Examples:
a2ps --output=- --$(portrait_or_landscape=portrait) --rows=$(nr-of-rows=1) --columns=$(nr-of-columns=1) --major=rows --chars-per-line=$(nr_of_chars_per_line=90) --center-title=$(title=Report) | gnome-gv -
This command accepts text on 'standard input' (no '%s' in command).
It uses 'a2ps', a versatile tool that converts plain (ascii) text to postscript and which can print several virtual pages on one physical page.
It sends the output to 'display', i.e. a print preview is shown in 'ghostview' from which it is possible to really print the output.
It has parameters and default values for:
The next figure shows the window that is displayed if the default print command is used with Print output:
{C:/Program Files/Windows NT/Bureau-accessoires/wordpad.exe} %s
Since the command contains '%s', pfm creates a temporary text file containing the text to be printed and then lets 'Wordpad' load that temporary file. In Wordpad you can then choose the font, margin, page orientation etc.
Remark the curly braces enclosing the full filename of wordpad.exe. They are necessary because of the spaces within the filename.
Notes:
Postgres Forms has a number of options that are stored in a text file. On UNIX platforms that file is
~/.pfmrc
On Windows platforms that file is
APPDATA\pfm\pfm.conf
where APPDATA is the user's application dirctory, e.g.
C:\Documents and Settings\'username'\Application Data
These options can be viewed and modified by selecting the Tools -> Options menu item:
For each option you can get dedicated on-line help by clicking the Help button at the right.
If you start Postgres Forms for the first time, the options have their default values. On later occasions, the options are read from the options file.
When a form is opened, by pressing the [Open] button or the Return key on the main window, a window is displayed with the following features:
The following figure shows the "Open form" window for table "memberlist" with attributes: "person" and "group". The form also displays "christian_name" and "name", which are derived from the table "person". These fields are read-only.
By pressing the "WHERE" and "ORDER BY" radio buttons, you can choose whether to edit the "WHERE" or the "ORDER BY" clause.
Initially, the status of the radio buttons is such that the "WHERE" clause can be edited. By pressing the "ORDER BY" radio button, you can switch to the "ORDER BY" clause. By pressing the "WHERE" radio button, you can return to the "WHERE" clause, etc.
Filling out the "WHERE" and/or "ORDER BY" clauses is optional.
After pressing the [Run] button, the SQL statement is executed, the selected records are stored in an internal buffer and the form window is displayed on the screen.
The form window displays the following items:
Note:
If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.
Note:
You can open several expanded attribute windows. When moving to another record, the expanded attribute windows are automatically updated with the values for the new current record.
Note:
The [Back] button is displayed only if the form that is being displayed is the result of pressing a "link" button.
When a form is opened, all the records' status fields initially have the value "Not Modified".
After you press the [Update] button, Postgres Forms reloads the current record.
If that is successful, the form's table attributes become modifiable, the buttons [OK] and [Cancel] are displayed and all the other buttons on the form are deactivated. The form's status becomes "Updating".
The calculated fields and the attributes of tables other than the form's main table are read-only and do not become modifiable.
If you press [OK]:
Note:
If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.
If you press [Cancel], the modifications are discarded.
While a record is displayed on your screen, it may be deleted by another user. This can happen :
In the first case, this is noticed by Postgres Forms when you press the [Update] button. In the second case it is noticed when you press the [OK] button. In both cases, the record is marked as deleted and the update operation is cancelled.
Similarly, while a record is displayed on your screen, it may be modified by another user. This can happen:
In the first case, the 'reload record', which is executed when you press [Update], refreshes the screen and the update proceeds as described above.
In the second case, Postgres Forms notices that the record has been modified by another user when you press [OK]. Then, the update operation is cancelled, and you are notified.
After you have pressed the [Add] button, the displayed record's attributes become modifiable in the same way as for "update record". The attributes for which a default value has been defined in pfm_attribute now get their default value on the screen. The buttons [OK] and [Cancel] are displayed and all the other buttons on the form are deactivated. The form's status becomes "Adding".
If you press [OK], the new record is stored, both in the data base and in the form's internal buffer. In the form's internal buffer, it becomes the last record. This means that even if the new record's values are such that the record would not be selected by original query, it stays in the internal buffer until the form is closed.
Similarly, the record gets the last position in the internal buffer, even if the that is not the position it would get according to the initial "ORDER BY" clause.
If you press [Cancel], the new record is discarded, i.e. it is neither stored in the data base, nor in the form's internal buffer.
After pressing [OK]:
Note:
If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.
If you press the [Delete] button, the current record is deleted from the data base. It is not deleted from the internal buffer, but its attributes are all put to an empty string and its status field is put to "Deleted".
Note:
If a LIMIT clause has been specified in the definition of the form, the behaviour is slightly different from the one described above. See 5.11. Forms with a LIMIT-clause in their definition for details.
The status field is a property of a record in the form's internal buffer. It is kept in the internal buffer until the form is closed and it is displayed on the form. The possible values are:
Whenever there is a one-to-many relationship between two database tables, you can define links between the corresponding forms.
When a form is displayed, all the links originating from that form, are displayed as buttons in the "links" frame.
Pressing a link button, brings you to another form in which the records, related to the originally displayed record, are loaded.
So, links are navigation tools that allow you to follow the one-to-many relationships in a database.
Note that you can define a link in the "one-to-many" direction and another one in the "many-to-one" direction of a relationship.
Links are defined in the pfm_link table. See 6.5. pfm_link for details.
Note:
Altough "many-to-many" relationships do exist in the real world, they are implemented as two "one-to-many" relationships, using an auxilliary table.
E.g. if you have a database containing tables for persons and groups of persons, you can have a many-to-many relationship between persons and groups: a person can be member of more than one group, and a group can contain more than one person.
To implement such a database, you would not only need tables "person" and "group", but also a table "membership". The "many-to-many" relationship between "person" and "group" is then implemented as a "one-to-many" relationship between "person" and "membership" and another "one-to-many" relationship between "group" and "membership".
So, to find all the members of a group, you first follow the one-to-many relationship to the membership table, and from there the many-to-one relationship to the person table.
After pressing [Update] or [Add], you can fill out a form, i.e. provide values for the table's attributes.
You can provide values for the attributes in one of the following ways, depending on the attribute's "type of get" (see section 6.2. pfm_attribute):
For a "view" it is possible to define a form in Postgres Forms in the same way as for a "table", but it is not possible to update a view.
The buttons [Update], [Add] and [Delete] are absent on a form for a view.
The "Search" menu on the "Form" window contains a list of the form's attributes. After having selected an attribute, you get a window in which you can specify a value for that attribute.
You can then search the internal buffer for the next record with the specified value for that attribute.
Since verion 1.5.0., the designer of the form can specify a LIMIT-clause to limit the number of records that is loaded into the form's internal buffer. This makes it possible to handle large tables without causing excessive memory usage. It is almost, but not entirely transparent for the user of the form.
As an example, we consider a form for which 'sqllimit = 100' has been specified in pfm_form and for which there are 523 records in the database.
When the form is opened, not all 523, but only the first 100 records are loaded into the internal buffer. Since pfm does not know how many records have not been loaded, it displays the the record number for records as '1/?', '2/?', ... '100/?'.
When you move beyond record 100, the internal buffer is first cleared and then reloaded with the next 100 records.
The same thing happens again when you move beyond records 200, 300 etc...
Finally, when moving beyond record 500, pfm only finds 23 instead of 100 records. Then it assumes that 523 is the last record and displays the record numbers as '501/523', '502/523', ... '523/523'.
When moving backwards through the records in the internal buffer, e.g. when moving from record 501 to record 500, the internal buffer is first cleared and then reloaded with the records 401 through 500.
Also searching the internal buffer with the Search menu, causes pfm to load another set of 100 records when required.
There are a few cases where this automatic clearing and reloading of the internal buffer has some confusing effects.
By invoking the "Help" menu on the "Form" window you get help information for filling out the currently displayed form.
This is the text that is stored in the "help" attribute of the table "pfm_form".
For the pfm_* forms, this help text is provided by pfm, but for the other forms that information has to be provided by the designer (probably you) of the form. See also section 6.1. pfm_form
Per data base, pfm uses tables with a name starting with "pfm_*" to store the definition of forms and reports.
A form allows the user to administer the data of just one table. This table is henceforth referred to as "the form's main table".
However, a form also has an SQL SELECT statement, which generates the data that are displayed on it.
In the simplest case the SQL SELECT statement is just:
SELECT <attributes of main table> FROM <main table>
In that case, the data which can be administered and the data which are displayed on the form are the same.
In more complex cases, the <main table> can be JOINED with other tables, which makes it possible to display data of other related tables as well. These data cannot be modified by means of the form.
The table "pfm_form" has the following attributes:
Note:
If pkey is empty, the form is read-only, since pfm is unable to uniquely identify a record. You can use the 'oid' as primary key, but according to the PostgreSQL documentation that is not recommended, unless you set a UNIQUE constraint on the 'oid'.
Notes:
LIMIT sqllimit OFFSET 0
is added to the form's SELECT when opening the form.
This means that only 'sqllimit' records are loaded into the form's internal buffer. When the user moves beyond the last record in the internal buffer, the internal buffer is first cleared and then reloaded with the next 'sqllimit' records by re-executing the form's SELECT but now with another OFFSET in the LIMIT clause.
The form's main table is defined by tablename. Only the data of that table can be administered by using the form.
All the data generated by the form's SQL SELECT statement can be displayed on the form. The SQL SELECT statement is defined by:
Note:
The WHERE clause provided by the user when opening the form, becomes a HAVING clause, if there is a GROUP BY clause.
The following rules should be observed when filling out sqlselect and sqlfrom:
tablename: person pkey: id sqlselect: id, christian_name, name, street, town, "ZIPcode", country, category, description sqlfrom: person groupby: -
tablename: memberlist pkey: group person sqlselect: memberlist."group", memberlist.person, p.christian_name, p.name sqlfrom: memberlist LEFT OUTER JOIN person p ON (p.id = memberlist.person) groupby: -
The table "pfm_attribute" defines all the properties of form attributes.
It has the following attributes:
Hint:
In general, all attribute values must be quoted, exept the values or expressions for numeric attributes.
Note:
Even with tgDirect it is possible to enter an expression as new value for an attribute, but then the expression is evaluated by PostgreSQL whereas with tgExpression, the expression is first evaluated by Tcl before the SQL statement is sent to PostgreSQL.
Note:
All calculated attributes and all attributes from tables other than the form's main table should be declared 'read-only'. If this rule is not observed, the Add and Update operations on this form will fail.
Note :
The sqlselect may return more than 1 attribute. If so, all the attributes are displayed in the list-box, but only the first one is used for updating the attribute.
Example:
default: =SELECT nextval('seq_person_id')
In this example the default value is the next value of the sequenece 'seq_person_id'.
The table "pfm_value_list" contains all the value lists of all the forms.
Its only attribute is
The table "pfm_value" contains all the values of the lists defined in pfm_value_list.
It has the following attributes:
A link is a navigation tool which allows you to follow a "one-to-many" or "many-to-one" relationship from one form to another.
Every link is stored as a record in the pfm_link table, which has the following attributes:
Note:
Postgres Forms does not provide any checks to safeguard the referential integrity of the data base in case of updates or deletions. However, PostgreSQL provides these functions as 'foreign key' table constraints (see PostgreSQL documentation).
The table pfm_report defines all the reports for the current data base.
pfm_report has the following attributes:
The sqlselect may contain one or more parameters for which a value is requested at "Run report" time. A parameter in the sqlwhere must be formatted as $(parameter_name).
Example:
sqlselect: SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name, p.street, p."ZIPcode", p.town, p.country FROM "group" g LEFT JOIN memberlist m ON g.name = m."group" LEFT JOIN person p ON m.person = p.id WHERE "group" = '$(group)' ORDER BY g.name, p.name, p.christian_name
When the report is run, the user is prompted to enter a value for the parameter "group". Then the report data are generated by executing the sqlselect statement in which $(group) is replaced with the value entered by the user.
The data returned by the report's SQL SELECT statement may be considered as a table with a column for each 'field' specified after the word 'SELECT' and with a row for each record.
By specifying an 'ORDER BY' clause in the report's SQL SELECT statement, it is possible to group rows with the same values for some fields together.
The report generator has an "economy" algorithm which avoids printing the same data repeatedly.
To control this you have to distribute the fields (columns) of the table over n sections such that section 1 contains the fields that are changing least frequently (when moving from one row to the next), section 2 contains the fields that are changing more frequently, and section n contains the fields that are changing at every row.
When the data of the first row of the table are printed, the data of section 1 are printed first. Then, on the following line, indented by one tab stop, the data of section 2 are printed. Then, on the following line, indented by 2 tab stops, data of section 2 are printed, etc.
[section 1] <--- row 1 [section 2] <--- row 1 [section 3] <--- row 1
Then, when the next rows are being printed, data of the lower numbered sections are only printed if they are different from the data of the last printed section of the same number:
[section 1] [section 2] [section 3] <--- row 1 [section 3] <--- row 2 [section 3] <--- row 3 [section 2] [section 3] <--- row 4 [section 3] <--- row 5 [section 1] [section 2] [section 3] <--- row 6 [section 3] <--- row 7
The report generator also enables you to print a summary at every point where a higher numbered section is about to be followed by a lower numbered section:
[section 1] [section 2] [section 3] <--- row 1 [section 3] <--- row 2 [section 3] <--- row 3 [summary 3] [section 2] [section 3] <--- row 4 [section 3] <--- row 5 [summary 3] [summary 2] [section 1] [section 2] [section 3] <--- row 6 [section 3] <--- row 7 [summary 3] [summary 2] [summary 1]
A summary i is printed just before a lower numbered section j (j < i). Its data can be calculated:
In particular, summary 1 is printed at the end of the report, is calculated from all the sections of the report and may be calculated from all the fields.
A record in pfm_section defines a section and a summary of a report.
The table pfm_section has the following attributes:
The fieldlist is a space separated list of field specifiers:
field_spec_1 field_spec_2 ... field_spec_N
where each field specifier is formatted as follows:
{field_i label_i alignment_i max_length_i}
where :
Notes :
For every section, the layout can be defined as:
The summary must be formatted as a space separated list of summary specifiers:
summary_spec_1 summary_sepc_2 .... summary_sepc_N
where each summary_spec is formatted as follows:
{field_i aggregate_i format_i}
where:
In general, the aggregate functions, use the same "economy" algorithm that is used for printing section data.
When all the fields of a section, which is not the highest numbered section of the report, have the same values for a number of consecutive rows, this section's data are only printed once for these rows.
Similarly, these rows are only counted once by the aggregate functions applied to a field of this section.
The aggregate functions that can be used in a summary are:
SQRT (SUM( (value_i - AVG(value))**2 ) / N)
where :
Here is a short overview of the 'ANSI C sprintf' formatting string. In general its form is:
%'MinWidth'.'Precision''Conversion'
where:
Example: %1d
prints an integer and uses as many characters as required.
Example: %1.2f
prints a floating point number wiht 2 digits after the decimal point and uses as many characters as required.
Example: %1.5E
prints a floating point number in the form x.yyyyy E±zz
Example: %1.4G
prints 2345.0 as 2345
prints 234567.0 as 2.346E+05
prints 0.003456 as 0.003456
prints 0.00003456 as 3.456E-05
The following drawing shows the links between the pfm_* tables:
The referential integrity is guaranteed by the following table constraints which are introduced when pfm installs the pfm_* tables:
ALTER TABLE pfm_attribute ADD CONSTRAINT ref_form FOREIGN KEY (form) REFERENCES pfm_form (name) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE pfm_link ADD CONSTRAINT ref_fromform FOREIGN KEY (fromform) REFERENCES pfm_form (name) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE pfm_link ADD CONSTRAINT ref_toform FOREIGN KEY (toform) REFERENCES pfm_form (name) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE pfm_value ADD CONSTRAINT ref_list FOREIGN KEY (valuelist) REFERENCES pfm_value_list (name) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE pfm_attribute ADD CONSTRAINT ref_value_list FOREIGN KEY (valuelist) REFERENCES pfm_value_list (name) ON DELETE RESTRICT ON UPDATE CASCADE;
Designing a form is in fact nothing more than filling out the data base tables pfm_forms, pfm_attribute, pfm_value_list, pfm_value and pfm_link defined in section 6. The pfm_* tables.
This is done using the forms that are predefined for these tables.
Postgres Forms does not offer the possibility to define data base tables or to modify their definition. That can be done using SQL statements CREATE TABLE ... or ALTER TABLE .... using the "Run SQL" function of Postgres Forms (see 3.1. Run SQL) or from 'psql', the interactive SQL terminal of PostgreSQL.
To modify or add a form:
First proceed as explained in the previous section to make the form you want to work on visible in the pfm_form form.
Then, follow the 'Attributes' link by pressing the [Attributes] button. This results in the form pfm_attribute being opened with all the attributes of the selected form loaded in the internal record buffer.
Here you can add, delete or modify the form's attribute definitions as appropriate. Note, that it is possible for the form to have less attributes than the table corresponding to the form, but all the attributes defined in the form must be returned by the SQL SELECT statement related to the form.
See section 6.2. pfm_attribute for a description of the attributes of pfm_attribute.
It may be necessary to add a value list for some attributes. See section 7.4. Defining value lists.
When finished, press [Back] to return to the form defining the selected form.
First proceed as explained in the section 7.1. Modifying or adding a form to make the form you want to work on visible in the pfm_form form.
To define links originating from the selected form, press the [Originating links] button. Then add, modify, delete links as appropriate. See section 6.5. pfm_link for a description of the attributes of pfm_link.
When finished, press [Back] to return to the form defining the selected form.
To define links terminating on the selected form, press the [Terminating links] button. Then add, modify, delete links as appropriate.
When finished, press [Back] to return to the form defining the selected form.
Links allow you to jump from one table/form to another while taking into account the relationships between the tables.
To modify an existing value list, select the form 'pfm_value_list' in the main window and press the [open] button.
Then, specify a "WHERE" clause to select that value list and press the [Run] button. This results in the selected value list to be displayed. Here you can modify it's name or delete it.
To add, modify or delete the list's values, press the [Values] button. This opens the pfm_value form with all the list's values in Postgres Form's internal buffer. Here, you can modify, add, delete values.
To define a new value list, select the form 'pfm_value_list' in the main window and press the [open] button.
Then, don't specify a "WHERE"-clause, press [Add] and specify the name of the new value list.
To insert values for the list, press the [Values] link-button.
The first thing you need to design a report is an SQL SELECT statement which generates the data you need for the report.
You can use the "Run SQL" function of Postgres Forms to design this SQL SELECT statement.
Let us take an example from the addressbook sample database (see 10.1. The addressbook sample database for more information).
Let us assume that we want to list all groups and the persons that are member of these groups. The following SELECT statement generates the "raw" data that we need for that report:
SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name, p.street, p."ZIPcode", p.town, p.country FROM "group" g LEFT JOIN memberlist m ON g.name = m."group" LEFT JOIN person p ON m.person = p.id ORDER BY g.name, p.name, p.christian_name
The result is:
group | description | id | name | christian_name | street | ZIPcode | town | country ---------+-------------------------------------+----+--------------+----------------+----------------------+---------+-----------+--------- cycling | Cycling companions | 10 | Van Horebeke | Norbert | Hoogstraat 3 | 9620 | Zottegem | Belgium family | Members of the family | 8 | Lemmens | Nancy | Copernicuslaan 198 | 9000 | Gent | Belgium family | Members of the family | 10 | Van Horebeke | Norbert | Hoogstraat 3 | 9620 | Zottegem | Belgium family | Members of the family | 6 | Van Riel | Hugo | Kerkstraat 56 | 2520 | Ranst | Belgium pfm | Persons involved in the pfm project | 1 | Brouwers | Adriaan | De Coninckstraat 23 | 8750 | Zwevezele | Belgium pfm | Persons involved in the pfm project | 2 | Van de Perre | Albert | Schanslaan 45 | 2600 | Berchem | Belgium tennis | Acquaintances from the tennis club | 10 | Van Horebeke | Norbert | Hoogstraat 3 | 9620 | Zottegem | Belgium tennis | Acquaintances from the tennis club | 6 | Van Riel | Hugo | Kerkstraat 56 | 2520 | Ranst | Belgium tennis | Acquaintances from the tennis club | 2 | Van de Perre | Albert | Schanslaan 45 | 2600 | Berchem | Belgium tennis | Acquaintances from the tennis club | 3 | Verdonck | Nelly | Azalealaan 33 | 9080 | Lochristi | Belgium work | Acquaintances from work | 1 | Brouwers | Adriaan | De Coninckstraat 23 | 8750 | Zwevezele | Belgium work | Acquaintances from work | 8 | Lemmens | Nancy | Copernicuslaan 198 | 9000 | Gent | Belgium work | Acquaintances from work | 12 | Van Geluwe | Adri | Jan Breydelstraat 21 | 8511 | Aalbeke | Belgium work | Acquaintances from work | 3 | Verdonck | Nelly | Azalealaan 33 | 9080 | Lochristi | Belgium (14 rows)
Postgres Forms has tools to easily layout a report for these data. That is explained in the next section.
Enter the design mode by pressing the [o Design mode] radio button on the main window.
Then select the form pfm_report and open it.
Don't specify a "WHERE" clause, press [Run] and then [Add].
Then fill out the form as follows:
SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name, p.street, p."ZIPcode", p.town, p.country FROM "group" g LEFT JOIN memberlist m ON g.name = m."group" LEFT JOIN person p ON m.person = p.id ORDER BY g.name, p.name, p.christian_name
Then press the [Sections] link button to define the sections and their layout.
A group having more than 1 member is listed more than once in the "raw" output of the report's SELECT statement.
Because of the order specified in the SELECT statement, all records related to a group are grouped together.
By defining a report with 2 sections (levels), we can avoid printing the data for a group more than once.
Section 1 will only print the fields related to group.
Section 2 will only print the fields related to a person.
When the report is printed, the records are printed in the order specified by the SELECT statement.
When the first record is printed, first section 1 data are printed, then section 2 data.
For every next record, the section 1 data are only printed if they differ from the data of the previously printed section 1 data. Then section 2 data are printed.
In this example, section 1 is a so called "group level" section.
In general, all sections, except the highest numbered section, are "group level" sections.
Possible section definitions for our example are shown below:
level : 1 layout : row fieldlist : {group group l} {description description l} summary : {group COUNT} level : 2 layout : table fieldlist : {id id r} {christian_name "Chr. name" l} {name name l} {street street l} {ZIPcode ZIP l} {town town l} {country country l} summary : {id COUNT}
See 6.7. pfm_section for more details on section definitions.
When this report is run (Menu Reports, Select "Persons and groups", press [Run]), this is the result:
Groups and persons ------------------ Description: Lists all groups and their members SQL : SELECT g.name AS "group", g.description, p.id, p.name, p.christian_name, p.street, p."ZIPcode", p.town, p.country FROM "group" g LEFT JOIN memberlist m ON g.name = m."group" LEFT JOIN person p ON m.person = p.id ORDER BY g.name, p.name, p.christian_name Date : 13-Dec-2004 group: cycling; description: Cycling companions; id | Chr. name | name | street | ZIP | town | country ----+-----------+--------------+----------------------+------+-----------+--------- 10 | Norbert | Van Horebeke | Hoogstraat 3 | 9620 | Zottegem | Belgium Summary: COUNT(id) = 1 group: family; description: Members of the family; id | Chr. name | name | street | ZIP | town | country ----+-----------+--------------+----------------------+------+-----------+--------- 8 | Nancy | Lemmens | Copernicuslaan 198 | 9000 | Gent | Belgium 10 | Norbert | Van Horebeke | Hoogstraat 3 | 9620 | Zottegem | Belgium 6 | Hugo | Van Riel | Kerkstraat 56 | 2520 | Ranst | Belgium Summary: COUNT(id) = 3 group: pfm; description: Persons involved in the pfm project; id | Chr. name | name | street | ZIP | town | country ----+-----------+--------------+----------------------+------+-----------+--------- 1 | Adriaan | Brouwers | De Coninckstraat 23 | 8750 | Zwevezele | Belgium 2 | Albert | Van de Perre | Schanslaan 45 | 2600 | Berchem | Belgium Summary: COUNT(id) = 2 group: tennis; description: Acquaintances from the tennis club; id | Chr. name | name | street | ZIP | town | country ----+-----------+--------------+----------------------+------+-----------+--------- 10 | Norbert | Van Horebeke | Hoogstraat 3 | 9620 | Zottegem | Belgium 6 | Hugo | Van Riel | Kerkstraat 56 | 2520 | Ranst | Belgium 2 | Albert | Van de Perre | Schanslaan 45 | 2600 | Berchem | Belgium 3 | Nelly | Verdonck | Azalealaan 33 | 9080 | Lochristi | Belgium Summary: COUNT(id) = 4 group: work; description: Acquaintances from work; id | Chr. name | name | street | ZIP | town | country ----+-----------+--------------+----------------------+------+-----------+--------- 1 | Adriaan | Brouwers | De Coninckstraat 23 | 8750 | Zwevezele | Belgium 8 | Nancy | Lemmens | Copernicuslaan 198 | 9000 | Gent | Belgium 12 | Adri | Van Geluwe | Jan Breydelstraat 21 | 8511 | Aalbeke | Belgium 3 | Nelly | Verdonck | Azalealaan 33 | 9080 | Lochristi | Belgium Summary: COUNT(id) = 4 Summary: COUNT(group) = 5
This is no replacement for the PostgreSQL documentation. It only gives some hints for installing and using PostgreSQL in combination with Postgres Forms.
After you have installed PostgreSQL, you have to initialise the so called 'database cluster'. This is in fact the directory that will contain all the databases.
To do so, you need to have the PostgreSQL user account. This is the UNIX user who owns all the data and who owns the server process. It is often the user with login id 'postgres', but that depends on how PostgreSQL is installed. This user is a kind of super-user who can access all databases, but it must not be the operating system's superuser.
initdb -D /var/lib/pgsql/data --encoding=LATIN1 --lc-collate=C
See section 9.2. for the 'encoding issue'.
When the Pgtcl package is loaded and initialised, it sets the environment variable PGCLIENTENCODING to UNICODE.
The pgintcl package does not do this.
In order to get the same behaviour of pfm with both Pgtcl and pgintcl, pfm always sets PGCLIENTENCODING to UNICODE.
Notes:
Because pfm uses clientencoding UNICODE, it can work with most database server encodings. PostgreSQL automatically performs the necessary conversions on the communications between client and server (see Chapter 20.3.1. Automatic Character Set Conversion Between Server and Client of the PostgreSQL 7.4.2 documentation).
The default character encoding used by the PostgreSQL database server is SQL-ASCII. This character encoding is not suitable for texts containing special characters like ë, é, è, etc.
The encoding LATIN1 (also referred to as iso8859-1) already contains some special characters, but it lacks some characters like the Euro symbol (€) and the French character œ.
The encoding LATIN9 (also referred to as iso8859-15) is almost identical to LATIN1, but it includes the € and œ. It still cannot encode characters from Cyrillic, Chinese or Japanese scripts.
The encoding UNICODE (which is in fact UTF-8 in the context of PostgreSQL) includes all these and much more. It is meant to be able to encode all possible characters of all known written languages.
When you create a database, you can specify an encoding for the database (e.g. CREATE DATABASE example ENCODING='LATIN1' or CREATE DATABASE example ENCODING='UNICODE', see PostgreSQL documentation for more details).
It is also worth considering to set your preferred encoding when running initdb. If you run e.g.
initdb -D /var/lib/pgsql/data --encoding=UNICODE --lc-collate=C
then all database that are subsequently created, are by default UNICODE.
First create the new database:
Then open newdbname and install the pfm_* tables by activating the menu 'Tools -> Install pfm_* tables'.
Here are some hints about SQL. For full documentation see PostgreSQL documentation.
CREATE TABLE Customer ( Id serial, Name text);
is implicitly and silently converted to:
CREATE TABLE customer ( id serial, name text);
If you really want Customer, Id and Name, you have to write:
CREATE TABLE "Customer" ( "Id" serial, "Name" text);
CREATE TABLE order ( ...
is not accepted because 'order' is an SQL keyword, but you can use
CREATE TABLE "order"
to create a table with the name 'order'.
You can install the example databases as explained in section 2.5. Install example database.
The tables of the addressbook database have been created with the following SQL statements:
CREATE TABLE person ( id serial primary key, christian_name text, name text, street text, town text, "ZIPcode" text, country text, category text, description text ); CREATE TABLE "ZIPcodes" ( town text, "ZIPcode" text ); CREATE TABLE "group" ( name text primary key, description text ); CREATE TABLE memberlist ( person integer, "group" text );
The following figure shows the links between the tables of the sample database:
This database shows a typical example of a many-to-many relationship: a person can belong to more than one group, a group can have more than 1 person as member. Such a relationship is implemented as a combination of 2 one-to-many relationships:
Both relationships are implemented as "links" in pfm:
The referential integrity of the these relationships is guaranteed by PostgreSQL table constraints, which have been created with the following SQL statements:
ALTER TABLE ONLY memberlist ADD CONSTRAINT ref_person FOREIGN KEY (person) REFERENCES person(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE ONLY memberlist ADD CONSTRAINT ref_group FOREIGN KEY ("group") REFERENCES "group"(name) ON UPDATE CASCADE ON DELETE CASCADE;
A person can belong to more than 1 group, and a group can have more than 1 member, but a person cannot be member of the same group twice.
The following constraint guarantees that a person can only be member of a group once:
ALTER TABLE ONLY memberlist ADD CONSTRAINT memberlist_pkey PRIMARY KEY (person, "group");
The second example data base which is included in the distribution is somewhat more complicated. It is a database with customers, invoices, products and orders.
The following image gives an overview of the structure of the customerdb.