-- convert_from_1.0.4.sql
--
-- The character encoding of this file is iso8859-1 (latin1)

-- register pfm_version

CREATE TABLE pfm_version (
	seqnr serial primary key,
	version text,
	"date" date,
	comment text);

INSERT INTO pfm_version (version, "date", comment)
VALUES ('1.1.1', CURRENT_DATE, 'convert_from_1.0.4.sql');

-- Replace groupby_having with groupby in form definition

ALTER TABLE pfm_form
RENAME groupby_having TO groupby;

UPDATE pfm_attribute
SET attribute = 'groupby'
WHERE (form = 'pfm_form') AND (attribute = 'groupby_having');

UPDATE pfm_form
SET sqlselect = 'oid, name, tablename, sqlselect, sqlfrom, groupby, showform, "view", help'
WHERE name = 'pfm_form';

-- Convert old pfm_report, to new pfm_report

UPDATE pfm_report
	SET table_or_view = 'SELECT * FROM ' ||
	table_or_view ||
	CASE WHEN sqlwhere <> ''
		THEN ' WHERE ' || sqlwhere 
		ELSE ''
	END ||
	CASE WHEN orderby <> ''
		THEN ' ORDER BY ' || orderby
		ELSE ''
	END;

ALTER TABLE pfm_report
	RENAME table_or_view TO sqlselect;

ALTER TABLE pfm_report
	DROP sqlwhere CASCADE;

ALTER TABLE pfm_report
	DROP orderby CASCADE;

-- Modify the attributes of the form pfm_report

UPDATE pfm_attribute
	SET attribute = 'sqlselect',
	typeofget = 'tgDirect',
	sqlselect = ''
WHERE (form = 'pfm_report') AND (attribute = 'table_or_view');

DELETE FROM pfm_attribute
WHERE (form = 'pfm_report') AND ((attribute = 'sqlwhere') OR (attribute = 'orderby'));

-- Modify the data of pfm_report in pfm_form

UPDATE pfm_form
	SET sqlselect = 'oid, name, description, sqlselect'
WHERE name = 'pfm_report';


-- Modify the "tablename" attribute of the form for pfm_form

UPDATE pfm_attribute
	SET typeofget = 'tgDirect',
	sqlselect = ''
WHERE (form = 'pfm_form') AND (attribute = 'tablename');

-- Update the help texts in pfm_form

-- pfm_attribute

UPDATE pfm_form
	SET help =
'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:

        o taQuoted: the value provided by the user is put
             between single quotes when it is transferred to SQL
             UPDATE or INSERT statements;
            
        o 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:

          o tgDirect: the user types the value directly;

          o 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 whereas
                  with tgExpression, the expression is first evaluated
                  by Tcl before the SQL statement is sent to
                  postgresql.

          o tgList: the user selects a value by means of a list box
            containing a list of values defined in table "pfm_value";

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

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

         o 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;
'
WHERE name = 'pfm_attribute';

-- pfm_form

UPDATE pfm_form
	SET help =
'A form has a one-to-one relation with just 1 database table. Only the
data of that table can be administered by means of the form. This
table is henceforth referred to as "the form''s main table".

However, the form also has a one-to-one relationship with just one SQL
SELECT statement, which generates the data that are displayed on the
form.

In the simplest case the SQL SELECT statement is just:

    SELECT oid, <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;

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

    - 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 presses
      the [Help] key 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 and groupby attributes of pfm_form; and

    - the optional WHERE and ORDER BY clauses provided by the user
      when opening the form.

Note: The WHERE clause provided by the user when opening the form, is
      automatically converted to a HAVING clause, if there is a GROUP
      BY clause.

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. If the form''s main table is not a view, the ''sqlselect'' must
       contain the ''oid'' of the main table. If the form''s main table
       is a view, the ''sqlselect'' must not contain the ''oid'' of the
       main table.

       Note: The ''oid'' is the ''object identifier''. It uniquely
             identifies a row in a table. All tables (not views)
             implicitly have an oid in postgreSQL. Postgres Forms
             needs it for identifying a row when issuing an SQL UPDATE
             or DELETE FROM command. There is no need to display this
             attribute on the form, i.e. it is not necessary to define
             it as an attribute in pfm_attribute.

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

sqlselect:
    oid, 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

sqlselect:
    memberlist.oid, memberlist."group", memberlist.person,
    p.christian_name, p.name

sqlfrom:
    memberlist LEFT OUTER JOIN person p ON (p.id = memberlist.person)

groupby:
    -'
WHERE name = 'pfm_form';

-- pfm_link

UPDATE pfm_form
	SET help =
'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.

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).'
WHERE name = 'pfm_link';

-- pfm_report

UPDATE pfm_form
	SET help =
'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.'
WHERE name = 'pfm_report';


-- pfm_section

UPDATE pfm_form
	SET help =
'The table pfm_section contains all the sections of the reports defined
in pfm_report. A report must have one section at least.

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, ... which uniquely identifies a
      section within a report and which also defines an order of the
      sections.

    - layout: can be "row", "column" or "table".

    - fieldlist: a space separated list of field specifiers,
      one for each field to be printed in this section of the report
      (see below for details).

The fieldlist must be formatted as follows:

{attribute_1 label_1 alignment_1} {attribute_2 label_2 alignment_2}...
                                        {attribute_N label_N alignment_N}

where :

    - attribute_i is the name of the view attribute that has to be
      printed in the i-th field of this section;

    - 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 double quotes (" .... ");

    - alignment_i is optional; if present, it is either l or r,
      indicating whether this field should be left or right aligned.

      Notes :

          o The alignment is optional. If it is left out, left
            alignment is assumed by default.

          o The alignment only influences the table layout. Column and
            row layouts are unaffected by the alignment indicator.

          o Multi-line fields, i.e. fields containing more than one
            line of text are only formatted properly in a column layout.

All sections of a report, except the last one (i.e. the one with the
highest level), are "group level sections".

When the report is being generated, first the records resulting from
executing the report''s SQL SELECT statement are stored internally.

Then the data of the first record are printed, first the fields of the
level 1 section, then the fields of the level 2 section, etc. up to
the fields of the last section.

When the next records are being printed, the "group level sections"
are only printed when at least one of the fields belonging to that
section has a value different from the corresponding field of the
previously printed section of the same level.

Only the highest level section (which is not a "group level section")
is printed for all records.

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.
'
WHERE name = 'pfm_section';


-- pfm_value

UPDATE pfm_form
	SET help =
'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.
'
WHERE name = 'pfm_value';


-- pfm_value_list

UPDATE pfm_form
	SET help =
'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.
'
WHERE name = 'pfm_value_list';