-- convert_from_1.1.0.sql

-- The character encoding of this file is iso8859-1 (latin1)

-- This script converts the pfm_* tables of versions 1.1.*
-- to the format of version 1.2.0

-- From pfm version 1.3.0 on, we do not mark the pfm_tables
-- with the last version of pfm, but with the earliest compatible
-- version of pfm. In this way, earlier versions of pfm, from 1.2.0 on,
-- will be able to work with pfm_tables created by this version
-- of pfm. This will only change when there is a need to change
-- the structure of the pfm_tables.


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


-- Correction of error in pfm_link

UPDATE pfm_link
SET sqlwhere = 'name=''$(form)'''
WHERE (fromform = 'pfm_attribute') AND (linkname = 'Form');

-- Alter table definitions: new attributes for pfm_form and pfm_attribute

ALTER TABLE pfm_form
ADD COLUMN pkey text;

ALTER TABLE pfm_attribute
ADD COLUMN "default" text;

-- Add primary keys to pfm_* tables

ALTER TABLE pfm_attribute
ADD CONSTRAINT pfm_attribute_pkey
PRIMARY KEY (form, attribute);

ALTER TABLE pfm_link
ADD CONSTRAINT pfm_link_pkey
PRIMARY KEY (fromform, linkname);

ALTER TABLE pfm_section
ADD CONSTRAINT pfm_section_pkey
PRIMARY KEY (report, "level");

ALTER TABLE pfm_value
ADD CONSTRAINT pfm_value_pkey
PRIMARY KEY (valuelist, value);

-- Update pfm_tables

-- Set pkey for all forms

UPDATE pfm_form
SET pkey = 'oid'
WHERE NOT "view";

UPDATE pfm_form
SET pkey = 'name'
WHERE name = 'pfm_form';

UPDATE pfm_form
SET pkey = 'form attribute'
WHERE name = 'pfm_attribute';

UPDATE pfm_form
SET pkey = 'fromform linkname'
WHERE name = 'pfm_link';

UPDATE pfm_form
SET pkey = 'name'
WHERE name = 'pfm_report';

UPDATE pfm_form
SET pkey = 'report level'
WHERE name = 'pfm_section';

UPDATE pfm_form
SET pkey = 'name'
WHERE name = 'pfm_value_list';

UPDATE pfm_form
SET pkey = 'valuelist value'
WHERE name = 'pfm_value';

-- Add attribute pkey to form of pfm_form

UPDATE pfm_attribute
SET nr = nr + 1
WHERE (form = 'pfm_form') AND (nr >= 3);

INSERT INTO pfm_attribute
       (attribute, typeofattrib, typeofget, sqlselect, nr, form, valuelist, "default")
VALUES ('pkey', 'taQuoted', 'tgDirect', '', 3, 'pfm_form', 'none', '');

-- Add attribute 'default' to form of pfm_attribute

INSERT INTO pfm_attribute
       (attribute, typeofattrib, typeofget, sqlselect, nr, form, valuelist, "default")
VALUES ('default', 'taQuoted', 'tgDirect', '', 8, 'pfm_attribute', 'none', '');

-- Drop 'oid' from the sqlselect of all pfm_* forms

UPDATE pfm_form
SET sqlselect = 'attribute, typeofattrib, typeofget, sqlselect, nr, form, valuelist, "default"'
WHERE name = 'pfm_attribute';

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

UPDATE pfm_form
SET sqlselect = 'linkname, sqlwhere, orderby, displayattrib, fromform, toform'
WHERE name = 'pfm_link';

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

UPDATE pfm_form
SET sqlselect = 'report, "level", fieldlist, layout'
WHERE name = 'pfm_section';

UPDATE pfm_form
SET sqlselect = 'value, description, valuelist'
WHERE name = 'pfm_value';

UPDATE pfm_form
SET sqlselect = 'name'
WHERE name = 'pfm_value_list';

-- Update help text for pfm_form

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

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

-- Set default values for some attributes of pfm_* forms

UPDATE pfm_attribute
SET "default" = 'taQuoted'
WHERE (form = 'pfm_attribute') AND ("attribute" = 'typeofattrib');

UPDATE pfm_attribute
SET "default" = 'tgDirect'
WHERE (form = 'pfm_attribute') AND ("attribute" = 'typeofget');

UPDATE pfm_attribute
SET "default" = 'none'
WHERE (form = 'pfm_attribute') AND ("attribute" = 'valuelist');

UPDATE pfm_attribute
SET "default" = 't'
WHERE (form = 'pfm_form') AND ("attribute" = 'showform');

UPDATE pfm_attribute
SET "default" = 'f'
WHERE (form = 'pfm_form') AND ("attribute" = 'view');

UPDATE pfm_attribute
SET "default" = '1'
WHERE (form = 'pfm_section') AND ("attribute" = 'level');

UPDATE pfm_attribute
SET "default" = 'table'
WHERE (form = 'pfm_section') AND ("attribute" = 'layout');

-- Update help text for the form of 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;

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

-- Add summary to table pfm_section

ALTER TABLE pfm_section
ADD COLUMN summary text;

-- Add constraint on level of pfm_section

ALTER TABLE pfm_section
ADD CONSTRAINT level_min_1
CHECK("level" >= 1);

-- Add summary to form pfm_section

UPDATE pfm_form
SET sqlselect = 'report, "level", fieldlist, layout, summary'
WHERE name = 'pfm_section';

INSERT INTO pfm_attribute
       (form, attribute, typeofattrib, typeofget, sqlselect, valuelist, nr, "default")
VALUES ('pfm_section', 'summary', 'taQuoted', 'tgDirect', '', 'none', 5, '');


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

    - 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 must be formatted as follows:

{field_1 label_1 alignment_1} {field_2 label_2 alignment_2}...
                                     {field_N label_N alignment_N}

where :

    - field_i is the name of one of the columns returned by the
      report''s SQL SELECT statement;

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

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

{field_1 aggregate_1 format_1} {field_2 aggregate_2 format_2}...
                                      {field_N aggregate_N format_N}

where:

    - field_i is the name of a field defined in the fieldlist of
      either this section, or another, higher numbered section;

    - 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 standard deviation for the values of the
           specified field:

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

	   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:

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

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

          o e or E : Convert floating-point number to scientific
                notation in the form x.yyyezz, 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 Ezz 

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