-- convert_from_1.2.0.sql

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

-- This script converts the pfm_* tables of versions 1.2.*
-- to the format of version 1.5.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 case that is 1.5.0 and that will stay
-- until there is a need to change the format again.


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

-- Update the help text for form pfm_section

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

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

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

          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 or
            table layout.

          o For a table layout, pfm 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.

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

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

ALTER TABLE ONLY pfm_form ADD COLUMN sqlorderby text;

ALTER TABLE ONLY pfm_form ADD COLUMN sqllimit text;

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

UPDATE pfm_attribute
SET nr = nr + 2
WHERE (form = 'pfm_form') AND (nr >= 7);

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

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

UPDATE pfm_form
SET sqlorderby = 'form, nr'
WHERE name = 'pfm_attribute';

UPDATE pfm_form
SET sqlorderby = 'showform DESC, name'
WHERE name = 'pfm_form';

UPDATE pfm_form
SET sqlorderby = 'fromform, linkname'
WHERE name = 'pfm_link';

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

UPDATE pfm_form
SET sqlorderby = 'report, "level"'
WHERE name = 'pfm_section';

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

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

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

    - 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''.  See PostgreSQL documentation of LIMIT-clause
            in SELECT statement for more details.

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

    - view : a boolean indicating whether or not the
      "tablename" is a view;

    - 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, groupby, sqlorderby and sqllimit
      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, becomes
      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 the ''LEFT'' table 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';

UPDATE pfm_form
SET sqlselect = 'pfm_section.report, r.sqlselect, pfm_section."level", pfm_section.fieldlist, pfm_section.layout, pfm_section.summary',
    sqlfrom = 'pfm_section LEFT OUTER JOIN pfm_report r ON (pfm_section.report = r.name)'
WHERE name = 'pfm_section';

UPDATE pfm_attribute
set nr = nr + 1
WHERE (form = 'pfm_section') AND (nr >= 2);

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