Introduction

PostSqlForms (psf) is a simple, but powerfull tool for PostgreSQL and SQLite databases. It enables you:

• to design forms for adding, modifying or deleting records of data base tables;
• to design links (one to many relationships) from one form to another, for navigating in the data base;
• to design and to generate reports based on the data in a table or view;
• to edit and to execute SQL statements.

PostSqlForms is implemented in Tcl/Tk, but there is no need for you to program anything in Tcl/Tk. You only need SQL for creating tables and views and for designing forms and links.

PostSqlForms makes no attempt to hide the underlying SQL. On the contrary, in most cases, it shows both the SQL statements it generates and the results it gets back.

PostSqlForms has been designed and tested with:

• version 8.6 of Tcl/Tk;
• version 9.0 of PostgreSQL;
• version 3.7 of SQLite

For more information on Tcl/Tk look at http://www.tcl.tk.

For more information on PostgreSQL look at http://www.postgresql.org.

For more information on SQLite look at: http://www.sqlite.org

The PostSqlForms distribution includes a "kbskit", which provides a Tcl/Tk run time environment, for the following architectures:

• linux-x86
• windows-32

For these architectures there is no need for the user to install Tcl/Tk separately.

This kbskit also includes the Tcl "sqlite3" package which makes it possible to access an SQLite database from a Tcl program. For more information about kbskit, look at http://sourceforge.net/projects/kbskit/.

PostSqlForms includes pgintcl, which is a Tcl interface for interacting with the PostgreSQL database server. For more information on pgintcl, look at http://sourceforge.net/projects/pgintcl/.

To use PostSqlForms with PostgreSQL databases, you need to have access to a PostgreSQL database server, which either is installed locally on your computer or which you can access via TCP/IP. Additionally, you need a local installation of the the client program 'psql'.

To use PostSqlForms with SQLite databases you need a locally installed sqlite3 program. A copy of this program is included in the PostSqlForms package for the linux-x86 and windows-32 architectures.

Copyright

PostSqlForms (psf) is a frontend for PostgreSQL and SQLite databases.

Copyright © 2013 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'.

The home page of psf can be found at

http://sourceforge.net/projects/postsqlforms/

There you can report bugs, request new features and get support.

1. User interface

1.1. Main window

When you start the PostSqlForms (psf) application, the main window shows up. It displays a list of databases.

When you start psf for the first time, the list will be empty.

You can add databases to the list, by clicking the 'Add' button.

Then you can choose the type of database: a PostgreSQL or an SQLite database. The default database type is an option. See menu Tools → Options → General

1.1.1. Adding a PostgreSQL database

Adding a PostgreSQL database does not create a new database on the PostgreSQL database server. What you are really adding is just a set of connection parameters that will enable you to connect to a database that has already been created on the database server.

The default values for the connection parameters 'host', 'port' and 'user' are psf options. So, it might be a good idea to first set them using menu Tools → Options → PostgreSQL.

Just like 'Add', the 'Modify' and 'Remove' operations are purely local and have no effect on the database server.

To open a database, make the database you want to open highlighted on the main window and then either press 'Return' or click on the 'Open' button.

What happens next, depends on the value of the 'usePGPASSWORD' option (menu Tools → Options → PostgreSQL).

If 'usePGPASSWORD' is on, you will be prompted to enter your PostgreSQL password.

If 'usePGPASSWORD' is off, you will not have to enter a password, but that will only work if you have a properly configured PostgreSQL password file. You can use menu Tools → Edit password file to set up a PostgreSQL password file.

In fact, it is recommended to set up a password file because the use of the PGPASSWORD environment variable is a security risk. See PostgreSQL documentation section 'libpq - C library', subsection 'Environment variables'.

1.1.2. Adding an SQLite database

When adding an SQLite database you are prompted to specify a name and a fully qualified filename of the SQLite database file. If that file does not exist yet, it will be created.

Modifying or removing a database from the list will not modify or remove the corresponding database file.

To open a database, make the database you want to open highlighted on the main window and then either press 'Return' or click on the 'Open' button.

1.2. The Help file

You can browse the Help file 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 PostSqlForms for how to choose a web browser.

1.3. Context menu

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

1.4. Printing

Printing can be invoked from the 'Run SQL' window for printing the text in the output text area (menu Output → Print) or from any 'text window' (menu Text → Print).

How printing is handled completely depends on the value of the 'printcmd' option. See help for option 'printcmd': invoke menu 'Tools → Options' then press 'Help' button of option 'printcmd'.

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

The command may contain parameters for which you will be prompted to provide a value when the command is called.

1.5. Keyboard operation

It is possible to operate psf 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.

1. The menus on the menu bar of any window have one underlined character. Pressing "Alt-<that character>" invokes the menu.
2. The menu items can also have an underlined character. Pressing that character when the menu is displayed, selects the corresponding menu item.
3. The menu items can have a so called "accelerator" or "shortcut" displayed in the second column of the menu. Pressing that key causes the menu item to be invoked even when the menu is not displayed.
4. There can be several widgets like text entries, buttons and listboxes on a window that can receive keyboard input. At any time, there is only one widget that receives the keyboard input. That widget has the so called "input focus". The text entry widget that has input focus gets a in front of it.

   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.

5. Text entry widgets which have a button with a at the right, let you select a value from a listbox or form a file selection dialogue by pressing the down arrow key when the entry widget has input focus.
6. Buttons can have an underlined character. Pressing "Alt-<that character>" has the same effect as pressing that button.
7. If there are buttons with icons instead of text labels, there is usually a 'Legend' button or menu, which explains the meaning and the keyboard shortcuts of these buttons.
8. Pressing Return on a window usually has the same effect as clicking on the OK button.
9. The Escape key usually has the effect of closing the window which has input focus without doing anything else. The only exceptions are:
   • Pressing Escape when adding or updating a record, aborts the operation, but keeps the "Form" window.
   • Pressing Escape on a "form tab"
     • either closes that tab if it is the most right tab;
     • or "unlocks" that tab and deletes all tabs at its right.

1.6. Displaying the license

Selecting "License" in the "Help" menu on the main window causes the "GNU General Public License" to be displayed.

1.7. About psf

Selecting "About" in the "Help" menu on the main window displays some general information about PostSqlForms, such as: version, copyright, contact information, Tcl/Tk run-time environment, interface to PostgreSQL and SQLite, installation directory.

2. Working with data bases

2.1. The database window

After opening a database a database window pops up:

This window has 3 tabs:

• Forms: This tab displays the list of forms for normal use.
• Reports: This tab displays the list of reports that can be generated
• Design: This tab displays the list of forms for the pfm_* tables. They can be used to design forms, reports and links. See section "The pfm_* tables" for details.

2.2. Checking the pfm_* tables

When you open a database, psf checks for the presence of the pfm_* tables. These tables contain the definitions of forms and reports. The acronym "pfm" stems from "Postgress Forms", the predecessor of PostSqlForms. This acronym is kept in PostSqlForms for backward compatibilty with databases that were accessed with "Postgress Forms".

If psf does not find the pfm_* tables, you get a hint that you can install them using the 'SQL → Install pfm_* tables' or the 'SQL → Install example database ...' menus.

On a database without pfm_* tables, psf only allows you to execute SQL statements using the Execute SQL feature.

On a database with pfm_* tables, psf allows you to also design and use forms and reports.

When you open a database that was previously managed by an older version of psf, 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 psf.

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 psf, some features like defining and using forms and reports may not work properly, but you will be able to execute SQL statements using the Execute SQL feature of psf.

2.3. Close data base

You can close a database in any of the following ways:

• Menu Database → Close
• Pressing ESC
• Closing the database window

Whatever method you use, both the database and the database window are closed.

2.4. Install pfm_* tables

To install the pfm_* tables in the currently opened database, select 'install pfm_* tables' from the SQL menu on the database window.

2.5. Install example database

There are two example databases included in the psf package:

• the address book data base; and
• the customer data base.

Using 'SQL → 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.

See section 11. The example databases for more details on the structure of the example databases.

3. Running SQL

3.1. SQL terminal

When you invoke the menu SQL →Execute SQL on the database window the SQL 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' or 'sqlite3' tools that are included in PostgreSQL and SQLite repectively.

When you press the [Run] button, all the text present in the SQL statement text area is sent to psql or sqlite3.

Don't forget to end SQL statements with ";". If you forget this, the statement is not executed 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. This happens more often with PostgreSQL than with SQLite databases. 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 psf 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 SQL window to recover from this frustrating situation.

Apart from SQL statements, you can also enter the psql "\" or the sqlite3 "." commands. E.g.

• \? : returns the psql on-line help on the "\" command;
• \h : returns the list of SQL statements for which psql provides on-line help;
• \h <SQL statement> : returns the psql help for the SQL statement.
• .help : returns the help on the sqlite3 "." commands
• .tables : returns the list of tables in an SQLite database
• .schema : returns the table definitions in an SQLite database

These functions can also be invoked from the Help and Sql menus 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.

There is no need to press the Run button if you end an SQL statement with ';<Return>'. Similarly, if the command you are typing begins with a '\' (for psql) or with a '.' (for sqlite3), the command is sent to psql (sqlite3) when you press '<Return>'.

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 invoking the menu Output → Clear.

3.2. Import SQL-script

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.

You are also asked whether to offer the file to psql (sqlite3) for import or to import the file into the SQL window. For large files it is recommended to offer the file to psql (sqlite3).

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.

4. Configuring PostSqlForms

PostSqlForms has a number of options that are stored in a text file. On UNIX platforms that file is

~/.postsqlforms

On Windows platforms that file is

APPDATA\postsqlforms\postsqlforms.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 help by clicking the button at the right.

If you start PostSqlForms for the first time, the options have their default values. On later occasions, the options are read from the options file.

5. Working with forms

5.1. Opening a form

When a form is opened, by pressing the [Open] button or the Return key on the database window, you will see a window such as this:

This window shows the SQL SELECT statement that will be executed to load the data into the form.

You can modify the 'WHERE' and 'ORDER BY' clauses.

The 'paste attribute Name' and 'past attribute Value' menus may help you to do so.

After pressing the 'Run' button, the SQL SELECT statement is executed, the form is displayed in a new tab of the current window and the data resulting from the SQL SELECT statement are loaded into the form's internal buffer.

5.2. The form window

The form window looks like this:

The upper text field shows some information on how the form was opened. This will become more interesting when you have followed one or more links.

The lower text field displays the SQL statements that were issued by psf. On this example, it shows the SQL statement that was issued to load the data in to the form's internal buffer.

The middle part of the window is the actual form. It shows:

• An 'n/m' indicator where n = index of current record in the internal buffer, m = index of last record in the internal buffer.

  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.

• Four buttons with arrow icons for moving through the form's internal buffer. From left to right the functions of these buttons are: go to first record, go to previous record, go to next record, go to last record. The same functions are also bound to the keyboard keys: Home, PageUp, PageDownn, End.
• A label in blue text with the name of the form.
• A label with the status of the currently displayed record. Immediately after opening a form, the status of all rercords is 'Not modified'. See 5.6. Status field for other possible status values.
• The names and values of the form attributes. Until you start an 'update' or 'add' operation the attribute values are read only. By clicking on an attribute's 'Expand' button, you open a text window that is more convenient to view larger, multi-line texts.
• The buttons 'Update', 'Add' and 'Delete' that start 'Update', 'Add' or 'Delete' operations.

The right side of the form window shows the link buttons which can be used to follow 'one-to-many' or 'many-to-one' relationships to other forms. See 5.7. Links to other tables for more details.

5.3. Update record

After you press the [Update] button, PostSqlForms 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.

The labels for the attributes that you can modify are shown in red colour.

If you press [OK]:

• A 'BEGIN TRANSACTION' is issued.
• A SELECT ... FOR UPDATE is done on the current record.
• It is checked that the record has not been modified in the mean time by another user: the values returned by the SELECT FOR UPDATE should match the values in the internal buffer.
• The new values for the record are stored in the data base by issuing an 'UPDATE' and a 'COMMIT'. This ends the transaction and releases the new record values to the data base.
• the record's status field becomes "Updated" and the modified record is reloaded from the database. The result is that the record's read-only data, such as calculated fields and attributes of other than the main table are refreshed, taking into account the modified data.
• The new values are also stored in the internal buffer. This means that even if the modifications are such that the record would no longer be selected by the original query, it stays in the internal buffer until the form is closed.
• Similarly, the record keeps its position in the internal buffer, even if that is no longer the position it would get according to the initial "ORDER BY" clause.
• the SQL UPDATE statement, the SQL COMMIT and their results, are displayed in the text field at the bottom of the form window.

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 :

• after you have opened the form and before you have pressed [Update];
• after you have pressed [Update] and before you have pressed [OK].

In the first case, this is noticed by PostSqlForms 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:

• after the you have opened the form and before you have pressed [Update];
• after you have pressed [Update] and before you have pressed [OK].

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, PostSqlForms notices that the record has been modified by another user when you press [OK]. Then, the update operation is cancelled, and you are notified.

5.4. Add record

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 the 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]:

• the SQL INSERT statement, and its result, are displayed in a the text field at the bottom of the form window;
• the record's status field becomes "Added";
• the record is reloaded so that the read-only data, such as calculated fields and attributes of other than the main table, are also displayed for the newly added record;
• the 'last record number' in the internal buffer is incremented with 1, which is shown on the 'current record number'/'last record number' display.
• if the insert fails, the values entered by you stay on the screen, the status field becomes "Not added" and the current record number becomes 'last record nr + 1'.

5.5. Delete record

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

5.6. Status field

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:

• Not modified : the record was not modified since the form was opened;
• Updated : the record has been updated since the form was opened;
• Added : the recorded was added since the form was opened;
• Deleted : the record has been deleted since the form was opened;
• After last : the current record pointer is 1 after the last record in the internal buffer.
• Not added : the last "Add" operation has failed and the current record pointer is 1 after the last record in the internal buffer.
• Updating : You have pressed the [Update] button. psf is waiting for input.
• Adding : You have pressed the [Add] button. psf is waiting for input.

5.7. Links to other tables

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' pane.

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.

Pressing a link button, brings you to another form in which the records, related to the originally displayed record, are loaded.

This form is opened in a new tab of the current window. By switching tabs you can move back to the form and record from which the link has originated.

Every time you follow a link, another tab is created. So, after having followed one or more links, you get a chain of records in which each record is represented by a tab on the window.

All tabs in the chain, except the last one, are 'locked'. That means that no operations are allowed on them.

You can 'unlock' a 'locked tab', by pressing the 'unlock tab' button or the 'Escape' key. That also deletes all tabs to the right of it.

Links are defined in the pfm_link table. See 6.5. pfm_link for details.

5.8. Filling out a form

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

• tgDirect: you directly type a value for the attribute;
• tgExpression: you can type an expression which is first evaluated before it is stored as a new value for the attribute (e.g. 1.0/7.75);
• tgList: you select a value by means of a list-box containing a predefined list of possible values;
• tgLink: you select a value by means of a list-box containing a list which is the result from a query on another table.
• tgReadOnly: the attribute cannot be modified.

5.9. Views

For a "view" it is possible to define a form in PostSqlForms 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.

5.10. Search record in internal buffer

The "Search" menu on the "Form" window contains a list of the form's attributes. After having selected an attribute, you get a search bar in the form 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.

5.11. Forms with a LIMIT-clause in their definition

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 psf 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, psf 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 psf 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.

• If, after having done some updates on records in the range 101 through 200, you move to a record outside this range and then come back to the records 101 through 200, you will notice that the status of the updated records is no longer "Updated". Every time the internal buffer is cleared to load another set of records, the status of the records is forgotten.
• If you add records, when records 101 through 200 are loaded in the internal buffer, they will get record numbers 201, 202, ... etc. Then when you move beyond the last added record, the internal buffer is first cleared and then reloaded with records 201 through 300. The added records will most likely not be at the positions 201, 202, ... etc. Instead, they will be at the position determined by the "ORDER BY"-clause with which the form was opened.
• If you delete e.g 4 records, when records 201 through 300 are loaded in the internal buffer and then move from record 300 to 301, the internal buffer is cleared and then reloaded with records 301 through 400. However, since 4 records have been deleted from the database in the mean time, the records that without the deletion would have been at positions 301, 302, 303 and 304 will now come at positions 297, 298, 299 and 300. So, although you think you have been stepping through all the records by doing "next record", "next record" ... you will have skipped 4 records.

5.12. Help for form

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

6. The pfm_* tables

Per data base, psf uses tables with a name starting with "pfm_" to store the definition of forms and reports. The acronym "pfm" stems from "Postgress Forms", the predecessor of PostSqlForms. This acronym is kept in PostSqlForms for backward compatibilty with databases that were accessed with "Postgress Forms".

6.1. pfm_form

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:

• name : the name of the form (usually equal to the name of the form's table);
• tablename : the name of the form's main table;
• pkey : the primary key of the form's main table, which may consist of more than one attribute. In that case pkey is a SPACE separated list of the attributes of the primary key;

  Note:

  If pkey is empty, or if pkey does not uniquely determine a record, all Update, Add and Delete operations will fail.

• sqlselect : the attribute list of the form's SQL SELECT statement, not including the word 'SELECT';
• sqlfrom : the FROM clause of the form's SQL SELECT statement, not including the word 'FROM';
• groupby : an optional 'GROUP BY' clause, not including the words 'GROUP BY';
• sqlorderby : an optional 'ORDER BY' clause, not including the words 'ORDER BY';
• sqllimit : an optional 'LIMIT' clause, only specifying the limit value as a positive integer;

  Notes:

  • This enables the designer of the form to avoid excessive memory usage by limiting the number of records loaded in the form's internal buffer. This may be useful for handling large tables.
  • If sqllimit is a positive integer, a

    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.

  • If sqllimit is an empty string, no LIMIT clause is appended to the form's SELECT.
  • Always specify an 'sqlorderby' if you specify an 'sqllimit'
• view : a boolean indicating whether or not the "tablename" is a view;
• showform : a boolean indicating whether the form is shown in "normal mode" (showform = 'true') or in "design mode" (showform = 'false'). Typically, showform is set 'true' for user defined forms and 'false' for the predefined pfm_* forms.
• help : a text which is displayed when the user selects the Help menu on the form.

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:

• the sqlselect, sqlfrom, groupby, sqlorderby and sqllimit attributes of pfm_form; and
• the optional WHERE and ORDER BY clauses provided by the user when opening the form.

The following rules should be observed when filling out sqlselect and sqlfrom:

1. The form's main table must appear in 'sqlfrom', and must not be aliased. Similarly, the main table's attributes appearing in 'sqlselect' must not be aliased. The other tables appearing in the 'sqlfrom' may be aliased.
2. The fields appearing in 'sqlselect' must have a unique, simple name without the need to precede them with a tablename. So, calculated fields must be given a name by aliasing and attributes of tables other than the main table may need to be aliased in order to have a unique, simple name.
3. The 'sqlfrom' is either just the name of the form's main table, or it is a JOIN clause in which 1 of the tables is the form's main table. Several join clauses can be nested in order to involve more than 2 tables. See examples below.

Example 1: the SQL SELECT for the person form of the addressbook database

tablename:
    person

pkey:
    id

sqlselect:
    id, christian_name, name, street, town, "ZIPcode",
    country, category, description

sqlfrom:
    person

groupby:
    -

Example 2: the SQL SELECT for the memberlist form of the addressbook database

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

6.2. pfm_attribute

The table "pfm_attribute" defines all the properties of form attributes.

It has the following attributes:

• form : the "name" of the form to which the attribute belongs;
• attribute : the name of the attribute; this must be equal to the name of the corresponding attribute of the form's SQL SELECT statement;
• typeofattrib : the type of attribute:
  • taQuoted: the value provided by the user is put between single quotes when it is transferred to SQL UPDATE or INSERT statements;
  • taNotQuoted: the value provided by the user is not quoted when it is transferred to SQL UPDATE or INSERT statements.

  Hint:

  In general, all attribute values must be quoted, exept the values or expressions for numeric attributes.

• typeofget: defines how the user provides a value for the attribute; possible values are:
  • tgDirect: the user types the value directly;
  • tgExpression: the user types an expression which is first evaluated before it is passed to SQL UPDATE or INSERT;

    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 or SQLite whereas with tgExpression, the expression is first evaluated by Tcl before the SQL statement is issued.

  • tgList: the user selects a value by means of a list box containing a list of values defined in table "pfm_value";
  • tgLink: the user selects a value by means of a list box containing a list of values which is the result from a query on another table.
  • tgReadOnly: this attribute cannot be modified by the user.

    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.

• sqlselect: the SQL SELECT statement which is used to fill the list box with possible values for the attribute (only meaningful if typeofget = tgLink).

  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.

• valuelist : the "name" of the value list defined in table "pfm_value_list" (only meaningful if typeofget = tgList);
• nr: a number which determines the order in which attributes are displayed on the form;
• default: a default value for this attribute which is used when adding a record. If the first character is an '=' sign, the following characters should be an SQL SELECT statement which returns just one value.

  Example:

  default: =SELECT nextval('seq_person_id')

  In this example the default value is the next value of the sequenece 'seq_person_id'.

6.3. pfm_value_list

The table "pfm_value_list" contains all the value lists of all the forms.

Its only attribute is

• name : a name uniquely identifying the value list.

6.4. pfm_value

The table "pfm_value" contains all the values of the lists defined in pfm_value_list.

It has the following attributes:

• valuelist : the name of the valuelist to which this value belongs
• value : a character string;
• description : a description of the value.

6.5. pfm_link

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:

• linkname : the name of the link, which is displayed on a link button on the "fromform";
• fromform : the name of the form from which the link originates;
• toform : the name of the form to which the link leads;
• sqlwhere : the "WHERE"-clause which is used to open the "toform" and in which the value of an attribute of the "fromform" may be represented by $(attrib-x), where 'attrib-x' is the name of the attribute;
• orderby : an 'order by' clause which determines the order of the records in the 'toform';
• displayattrib : a space separated list of attributes of the 'fromform', the value of which is displayed on the 'toform' to remind the user from which record the link originated. If one of the attributes contains spaces, delimit it with curly brackets { ... }.

6.6. pfm_report

The table pfm_report defines all the reports for the current data base.

pfm_report has the following attributes:

• name: the name of the report. This is the name that appears in the selection list of the "Run Report" function.
• description: free text describing the purpose of the report in more detail.
• sqlselect: an SQL SELECT statement that generates the data for the report.

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.

6.7. pfm_section

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

• by applying one of the aggregate funtions: COUNT, SUM, AVG, STDDEV, MIN, MAX;
• on the fields of the sections j (j >= i), between the last printed lower numbered section k (k < i), till the next (not yet printed) lower numbered section k (k < i).

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:

• report: the name of the report to which the section belongs
• level: a number 1, 2, 3, 4, ... . The first level must be '1'. The next levels must be numbered consecutively. In the most simple report, there is only a section with level 1.
• layout: can be "row", "column" or "table".
• fieldlist: a space separated list of field specifiers, one for each field to be printed in the sections of this level (see below for details).
• summary: a space separated list of summary field specifiers (see below for details).

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 :

• field_i is the name of one of the columns returned by the report's SQL SELECT statement; if it consists of more than 1 word, it must be delimited by curly brackets ({...});
• label_i is a string which has to be used as label for printing the i-th field of this section; if it consists of more than 1 word, it must be delimited by curly brackets ({...});
• alignment_i is optional; if present, it is either l or r, indicating whether this field should be left or right aligned.
• max_length_i is optional;if present, it is the maximum number of characters per line for printing the data of this field; lines longer than max_length_i will be wrapped by inserting one or more line breaks before printing.

  Notes :

  • The alignment is optional. If it is left out, left alignment is assumed by default.
  • The alignment only influences the table layout. Column and row layouts are unaffected by the alignment indicator.
  • Multi-line fields, i.e. fields containing more than one line of text are only formatted properly in a table or column layout.
  • For a table layout, psf automatically calculates the column width that is required to display all data. So, normally you don't have to worry about column widths. However, sometimes, the data of a few records, make the columns excessively wide. That is where you might consider using "max_length_i" in the field specifier. If the data do not exceed that maximum, it won't have any effect.
  • Although 'alignment' and 'max_length' are both optional, you have to specify 'alignment' if you want to specify max_length.

For every section, the layout can be defined as:

• row: the section's field labels and field values are printed in one row in a format: label_1 : value_1; label_2 : value_2; ... etc.
• column: the section's field labels are printed in a first column, the section's field values are printed in a second column.
• table: the section's values are printed in a table with a column per field and a row per record, the section's field labels are used as column headers for the table.

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:

• field_i is the name of a field defined in the fieldlist of either this section, or another, higher numbered section; if it consists of more than 1 word, it must be delimited by curly brackets ({...});
• aggregate_i is one of the aggregate functions: COUNT, SUM, AVG, STDDEV, MIN, MAX (see below for details); and
• format_i is an optional 'ANSI C sprintf' formatting string (see below for details). If it is left out, the number is printed with maximum precision.

Aggregate functions:

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:

• COUNT: Counts the number of rows. In this case, the field_i that is specified only determines which section is counted.
• SUM: Calculates the sum of all the values of the specified field.
• AVG: Calculates the average of the values of the specified field.
• STDDEV: Calculates the sample standard deviation for the values of the specified field:

  SQRT (SUM( (value_i - AVG(value))**2 ) / (N - 1) )

  where :

  • value_1, value_2, ... value_N are the values of the considered field;
  • AVG(value) is the average of the considered values;
  • N is the number of values.
• MIN: Calculates the minimum of the values of the specified field.
• MAX: Calculated the maximum of the values of the specified field.

'ANSI C sprintf' formatting string:

Here is a short overview of the 'ANSI C sprintf' formatting string. In general its form is:

%'MinWidth'.'Precision''Conversion'

where:

• 'MinWidth' is an integer defining the minimum width (as number of characters) for the number to be printed. If the number does not need so much space, spaces are inserted in front of the number, unless MinWidth is negative. In that case, spaces are appended at the end. If the number needs more space than MinWidth, more space is used.
• 'Precision' is an integer defining how many digits to print after the decimal point, or, in the case of g or G conversion, the total number of digits to appear, including those on both sides of the decimal point.
• 'Conversion' is one of:
  • d : convert integer to signed decimal string. In this case, there is no need to define a 'Precision'.

    Example: %1d

    prints an integer and uses as many characters as required.

  • f : convert floating point number to fixed point notation. In this case, 'Precision' defines the number of digits to print after the decimal point. If there are not enough digits available, trailing zeroes are appended.

    Example: %1.2f

    prints a floating point number wiht 2 digits after the decimal point and uses as many characters as required.

  • e or E : Convert floating-point number to scientific notation in the form x.yyye±zz, where the number of y's is determined by the 'Precision' (default: 6). If the precision is 0 then no decimal point is output. If the E form is used then E is printed instead of e.

    Example: %1.5E

    prints a floating point number in the form x.yyyyy E±zz

  • g or G : If the exponent is less than -4 or greater than or equal to the precision, then convert floating-point number as for %e or %E. Otherwise convert as for %f. Trailing zeroes and a trailing decimal point are omitted. In this case the 'Precision' specifies the total number of digits to appear, including those on both sides of the decimal point

    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

6.8. The structure of the pfm_* tables

The following drawing shows the structure the pfm_* tables:

The arrows represent "one-to-many" or "1 to n" relationships between the tables. The conditions shown on the arrows are the WHERE clauses that have to be applied when following the "one-to-many" relationships.

On the corresponding forms these one-to-many relationships are represented by "link buttons". They allow you to follow the "one-to-many" relationships.

7. Designing a form

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.

7.1. Modifying or adding a form

To modify or add a form:

• Select the 'Design' tab on the database window. The pfm_form form now becomes visible in the listbox on the database window;
• Select the pfm_form form and then open it;
• If you want to modify an existing form, select the form you want to modify either by specifying a"WHERE" clause, or by scrolling through the already defined forms until you reach the one you want to modify;
• If you want to add a new form, don't specify a "WHERE" clause, just press [Run] and then press the [Add] button;
• Now it is possible to enter/modify the form's definition, as described in section 6.1. pfm_form

7.2. Defining a form's attributes

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.

7.3. Defining links between forms

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, close the tab "pfm_link" 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, close the tab "pfm_link" 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.

7.4. Defining value lists

To modify an existing value list, select the form 'pfm_value_list' in the database 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. Here, you can modify, add, delete values.

To define a new value list, select the form 'pfm_value_list' in the database 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.

8. Reports

8.1. Running a report

To run a report, select the 'Reports' tab on the database window, select a report on the list-box and press the [Run] button.

The output appears in a new window.

For some reports you may be prompted to specify a value for some parameters.

8.2. Designing the data for the report

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 PostSqlForms to design this SQL SELECT statement.

Let us take an example from the addressbook sample database (see 11.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
 psf     | Persons involved in the psf project |  1 | Brouwers     | Adriaan        | De Coninckstraat 23  | 8750    | Zwevezele | Belgium
 psf     | Persons involved in the psf 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)

PostSqlForms has tools to easily layout a report for these data. That is explained in the next section.

8.3. Designing the report layout

Enter the design mode by selecting the 'Design' tab on the database 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:

• name: Groups and persons
• description : Lists all groups and their members
• 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
      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 (Tab 'Reports' on database window, 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: psf; description: Persons involved in the psf 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

9. Character encoding

PostSqlForms internally uses the 16 bit UNICODE character encoding from Tcl. When PostSqlForms reads text from a file or from a Tk widget, it converts the text to the Tcl 16 bit UNICODE encoding. By default, PostSqlForms assumes that files are encoded using the system's default encoding. However, when importing an SQL-file you have the possibility to change the encoding which is used to read the file.

Both PostgreSQL and SQLite have the possibility to choose a character encoding at the time the database is created.

The Tcl packages "pgintcl" and "sqlite3", make sure that the necessary encoding conversions are made when text is transferred to/from the databases PostgreSQL and SQLite.

10. Some general hints about SQL

Here are some hints about SQL. For full documentation see PostgreSQL documentation.

• The SQL language is not case sensitive, but it is customary to use upper case for SQL statements.
• PostgreSQL and SQLite always converts the names of tables and attributes to lower case, regardless of the case typed by the user, unless these names are enclosed in double quotes. Example:

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

• SQL keywords are not allowed as names of tables or attributes, unless they are enclosed in double quotes. Example:

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

• PostSqlForms automatically encloses the names of tables and attributes in double quotes for the SQL statements it generates itself. However, you still have to take care when entering SQL statements, or parts of SQL statements, in the pfm_* tables.

11. The example databases

Using menu 'SQL → Install example database' on the database window, you can install one of the example databases in the currently opened database. This database should be completely empty. It should not even contain the pfm_* tables.

11.1. The addressbook database

The first example database is the addressbook database. The following figure shows its structure:

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:

1. the one-to-many relationship from "person" to "memberlist"; and
2. the one-to-many relationship from "group" to "memberlist".

Both relationships are implemented as "links" in psf:

• on the form "person" there is a link button labelled "Groups" which opens the "memberlist" form in which all the memberlist records for the currently displayed "person" are loaded (one-to-many relationship "person → memberlist");
• on the form "memberlist" there is a link button, labelled "Person", which opens the "person" form in which the person, referenced by the currently displayed "memberlist" record, is loaded (many-to-one relationship "memberlist → person");
• on the form "group" there is a link button labelled "Members" which opens the "memberlist" form in which all memberlist records for the currently displayed "group" are loaded (one-to-many relationship "group → memberlist");
• on the form "memberlist" there is a link button, labelled "Group", which opens the "group" form in which the group, referenced by the currently displayed "memberlist" record, is loaded (many-to-one relationship "memberlist → group");

The referential integrity of the database is guaranteed by "foreign key constraints".

11.2. The customer database

The second example data base which is included in the distribution is somewhat more complicated. It is a database with customers, invoices, invoice details and products.

The following image gives an overview of the structure of the customerdb.

Here follow the table definitions.

CREATE TABLE customer (
    nr              integer PRIMARY KEY,
    "first name"    text,
    name            text,
    address         text,
    "E-mail"        text,
    telephone       text);

CREATE TABLE invoice (
    nr          integer PRIMARY KEY,
    "date"      date,
    customer    integer,
    "discount%" numeric,
    FOREIGN KEY (customer) REFERENCES customer(nr) ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE "invoice detail" (
    invoice         integer,
    product         integer,
    quantity        integer,
    "unit price"    numeric,
    PRIMARY KEY (invoice, product),
    FOREIGN KEY (invoice) REFERENCES invoice(nr) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (product) REFERENCES product(nr) ON UPDATE CASCADE ON DELETE RESTRICT);

CREATE TRIGGER unit_price
AFTER INSERT ON "invoice detail"
FOR EACH ROW
    BEGIN
        UPDATE "invoice detail"
        SET "unit price" = (SELECT price FROM product WHERE (product.nr = NEW.product))
        WHERE ("invoice detail".invoice = NEW.invoice)  AND ("invoice detail".product = NEW.product);
    END;

The foreign key constraints guarantee the referential integrity of the database.

The trigger "unit_price" copies the "price" from the product table to the "unit price" column of "invoice detail" table when a new "invoice detail" row is added, i.e. at the time of sale. When the "price" in the product table is changed after the time of sale, the "unit price" in the "invoice detail" table is not changed.