SCHEMA
======

custom_fields
-------------
field_id integer auto_increment primary key,
field_name tinytext not null,
display_name tinytext not null,
field_type char binary not null,
required bool not null,
selection_id integer references cf_selections(selection_id)

The field_type column is 'i' for integer fields, 'c' for short string
fields, 'C' for long string fields, 'd' for date-only fields, 'D' for
date-time fields, 's' for single-selection fields, and 'S' for
multiselection fields.

The selection_id column is an index to the cf_selections and
cf_selection_labels tables for fields of type Selection, and is NULL
for other types.

cf_groups
---------
group_id integer auto_increment primary key,
group_name tinytext

This table contains one row for every custom field group.  The name
may be NULL for reasons described below.

cf_product_members
------------------
product smallint references products(id),
group_id integer not null references cf_groups(group_id),
display_index integer not null,
index (product)

This table describes which groups belong to which products, and the
order in which the groups should be displayed to users.  The global
scope is described by rows with product = NULL.

For simplicity's sake, at the database level products are considered
to have only field groups as children, not individual custom fields.
A custom field that is not a member of a field group is represented as
belonging to an unnamed field group (ie, group_name = NULL) with
itself as the only member.

cf_group_members
----------------
group_id integer not null references cf_groups(group_id),
field_id integer not null references custom_fields(field_id),
display_index integer not null,
index (group_id)

This table describes which fields belong to which groups, and the
order in which the fields should be presented to users.

Given a product identified by $product_id, information about the
fields in that product can be retrieved in the proper order by a SQL
statement like the following:

SELECT custom_fields.*
FROM custom_fields, cf_product_members, cf_group_members
WHERE cf_product_members.product = $product_id
  AND cf_product_members.group_id = cf_group_members.group_id
  AND cf_group_members.field_id = custom_fields.field_id
ORDER BY cf_product_members.display_index, cf_group_members.display_index;

The cf_groups table can be added to the join as well, if the ID number
and/or name of the group to which each field belongs is desired.


cf_selections
-------------
selection_id integer auto_increment primary key,
unset_label tinytext not null

The unset_label column is the text which represents the "empty set"
value of single-selection fields.

cf_selection_labels
-------------------
label_id integer auto_increment primary key,
selection_id integer not null references cf_selections(selection_id),
label_index integer not null,
label tinytext not null,
index (selection_id)

*****

The following five tables contain bug data.  Field defaults are stored
in these tables, in rows with bug_id = NULL.

cf_integer
----------
bug_id integer references bugs(bug_id),
field_id integer not null references custom_fields(field_id),
value integer,
unique key (bug_id, field_id)

cf_smallstring
--------------
bug_id integer references bugs(bug_id),
field_id integer not null references custom_fields(field_id),
value tinytext,
unique key (bug_id, field_id)

cf_longstring
-------------
bug_id integer references bugs(bug_id),
field_id integer not null references custom_fields(field_id),
value text,
unique key (bug_id, field_id)

cf_date
-------
bug_id integer references bugs(bug_id),
field_id integer not null references custom_fields(field_id),
value date,
unique key (bug_id, field_id)

For date fields which do not include a time, the time component of the
date is ignored when it is read from the database.  When such a field
is stored, its time component is set to 12:00:00 AM.

cf_selection
------------
bug_id integer references bugs(bug_id),
field_id integer not null references custom_fields(field_id),
label_id integer,
index (bug_id, field_id)

This table holds bug data for single-selection and multiselection
fields.

For each single-selection field, there is one row in the table.  The
selection_id is NULL if the field is unset; otherwise it references a
row in the cf_selection_labels table.

For each multiselection field, there are as many rows in the table as
there are selected strings in the field's value.  Each selection_id
references a row in the cf_selection_labels table.

*****

The following six tables hold bug activity data.

cf_transactions
---------------
transaction_id integer auto_increment primary key,
bug_id integer not null references bugs(bug_id),
user_id mediumint not null references profiles(userid),
time date not null,
index (bug_id)

cf_integer_activity
-------------------
transaction_id integer not null references cf_transactions(transaction_id),
field_id integer not null references custom_fields(field_id),
old_value integer,
new_value integer,
index (transaction_id)

cf_shortstring_activity
-----------------------
transaction_id integer not null references cf_transactions(transaction_id),
field_id integer not null references custom_fields(field_id),
old_value tinytext,
new_value tinytext,
index (transaction_id)

cf_longstring_activity
----------------------
transaction_id integer not null references cf_transactions(transaction_id),
field_id integer not null references custom_fields(field_id),
old_value text,
new_value text,
index (transaction_id)

cf_date_activity
----------------
transaction_id integer not null references cf_transactions(transaction_id),
field_id integer not null references custom_fields(field_id),
old_value date,
new_value date,
index (transaction_id)

cf_selection_activity
---------------------
transaction_id integer not null references cf_transactions(transaction_id),
field_id integer not null references custom_fields(field_id),
added integer,
removed integer,
index (transaction_id)

Let "Foo" be the name of a selection field whose value changes during
a particular transaction.  Let A be Foo's value prior to the
transaction, and B be Foo's value after the transaction.  (A and B are
both subsets of the set of all valid strings for the field.)  Then
there will be N rows in this table for the transaction, where N is the
greater of |A-B| and |B-A|.  For each element e of B-A (that is, those
elements which were added by the transaction), one of the N rows has e
in its "added" column; remaining rows (if any) have NULL in the
"added" column.  For each element e of A-B (that is, those elements
which were removed by the transaction), one of the N rows has e in its
"removed" column; remaining rows (if any) have NULL in the "removed"
column.

Examples:

Suppose multiselection field Foo has a field_id of 1, and its valid
strings are "One", "Two", "Three", "Four", and "Five", and that these
strings have selection_ids of 1, 2, 3, 4, and 5 respectively.  Then
the following changes to the field result in the corresponding rows
being added to the cf_selection_activity table.  (The transaction_id
may be different in practice, of course.)

{ }  ->  { "One", "Two" }

transaction_id | field_id | added | removed
---------------+----------+-------+--------
           100 |        1 |     1 |    NULL
           100 |        1 |     2 |    NULL

{ "One", "Two" }  ->  { "Three" }

transaction_id | field_id | added | removed
---------------+----------+-------+--------
           101 |        1 |     3 |       1
           101 |        1 |  NULL |       2

{ "Three" }  ->  { "One", "Two", "Four", "Five" }

transaction_id | field_id | added | removed
---------------+----------+-------+--------
           102 |        1 |     1 |       3
           102 |        1 |     2 |    NULL
           102 |        1 |     4 |    NULL
           102 |        1 |     5 |    NULL

{ "One", "Two", "Four", "Five" }  ->  { "One", "Five" }

transaction_id | field_id | added | removed
---------------+----------+-------+--------
           103 |        1 |  NULL |       2
           103 |        1 |  NULL |       4

Single-selection fields are handled identically, but naturally there
will be at most one row added per transaction.


FILE SYSTEM
===========

All custom-field-related templates go in the directory
$BUGZILLA_ROOT/template/en/default/custom.


TEMPLATE ISSUES
===============

Every template which needs to display information on custom fields
should expect to find such information via two new entries in the
global %::vars hash.

The "custom_fields" entry is a reference to an array of hashes, each
of which describes a single custom field.  The order of the fields in
this array reflects the field order established by the administrator.

The "custom_field_groups" entry is a reference to an array of groups.
Each group is a three-element hash; one element, "id", is the numeric
ID of the group; another element, "name", is the name of the group;
and the third element, "members", is an array of the same field hashes
that are in the "custom_fields" entry.  Custom fields which are not
members of a group are stored in groups by themselves, with an
undefined group name.  The order of the groups reflects the
established field order.

The contents of a field hash are as follows:

*  All fields have "id", "name", "display_name", "required", and
   "extended" .
*  All fields have "is_integer", "is_date", "is_string", and
   "is_selection" elements, exactly one of which is true; the rest are
   false.
*  Selection fields have a "domain" element, a reference to an array
   containing the field's value set.
*  Single-selection fields have an "unset" element, the string which
   labels the field's empty-set label.

Given a product identified by $product_id and a bug identified by
$bug_id, these elements are populated according to the following
pseudocode:

if not defined $product_id:  # (global scope)
    product_condition = "cf_product_members.product IS NULL";
else:
    product_condition = "cf_product_members.product = $product_id";

SELECT cf.field_id, cf.field_name, cf.display_name, cf.field_type,
       cf.required, cf.selection_id, cf_groups.group_id, cf_groups.group_name
FROM custom_fields cf, cf_groups, cf_product_members, cf_group_members
WHERE $product_condition
  AND cf_groups.group_id = cf_product_members.group_id
  AND cf_product_members.group_id = cf_group_members.group_id
  AND cf_group_members.field_id = custom_fields.field_id
ORDER BY cf_product_members.display_index, cf_group_members.display_index;

@bygroup = ();
$lastgroup = undef;

for row in result_set:
    $major_type = uc $field_type;
    $field = {
        id           => $field_id,
        name         => $field_name,
        display_name => $display_name
        required     => $required,
        extended     => $field_type eq $major_type,
        is_integer   => $major_type eq 'I',
        is_date      => $major_type eq 'D',
        is_string    => $major_type eq 'C',
        is_selection => $major_type eq 'S',
    };

    if $major_type eq 'C':
        $field->{default} =
            SELECT value
            FROM ($extended ? 'cf_longstring' : 'cf_shortstring')
            WHERE bug_id IS NULL AND field_id = $field_id;
    else if $major_type eq 'I':
        $field->{default} =
            SELECT value
            FROM cf_integer
            WHERE bug_id IS NULL AND field_id = $field_id;
    else if $major_type eq 'D':
        $format = '%Y-%m-%d';
        $format .= ' %H-%m-%s' if $extended;
        $field->{default} =
            SELECT DATE_FORMAT(value, $format)
            FROM cf_date
            WHERE bug_id IS NULL AND field_id = $field_id;
    else if $major_type eq 'S':
        $field->{default} = [
            SELECT cf_selection_labels.label
            FROM cf_selection, cf_selection_labels
            WHERE cf_selection.bug_id IS NULL
              AND cf_selection.field_id = $field_id
              AND cf_selection.label_id = cf_selection_labels.label_id
            ORDER BY cf_selection_labels.label_index
        ];
        $field->{default} = undef if not $extended
                                     and @{ $field->{default} } == 0;
        $field->{domain} = [
            SELECT label
            FROM cf_selection_labels
            WHERE selection_id = $selection_id
            ORDER BY label_index
        ];
        if not $extended, then:
            $field->{unset} =
                SELECT unset_label
                FROM cf_selections
                WHERE selection_id = $selection_id;

    push @custom_fields, $field;

    if not defined $last_name
           or not defined $group_name
           or $last_name ne $group_name, then:
        $current_group = [ $field ];
        push @custom_field_groups, {
            id => $group_id, name => $group_name, members => $current_group
        };
    else:
        push @$current_group, $field;
        
    $last_name = $group_name;

# End of loop.
    
$::vars{custom_fields}       = \@custom_fields;
$::vars{custom_field_groups} = \@custom_field_groups;

For efficiency reasons, the implementation should batch accesses to
the data tables.  For example, let @int_fields contain a list of the
integer field IDs of all integer fields in a given product.  Then the
SELECT FROM cf_integer statement above would become:

SELECT field_id, value
FROM cf_integer
WHERE bug_id IS NULL
  AND field_id IN (@int_fields);

Additional program logic would then distribute the result set into the
appropriate field hashes.

The above logic describes how to populate the %::vars hash with custom
field metadata for a single scope (global or per-product).  When field
data for a specific bug is desired, the following processing is
necessary.

Let the bug in question be identified by $bug_id, and let the product
in which it resides be identified by $product_id.

First, obtain metadata for two scopes: the global scope (product IS
NULL) and the bug's parent product (product = $product_id).

Second, iterate over the $::vars{custom_fields} array and set the
"value" element of each.  This element is set in exactly the same
manner as the "default" element, as described above, except that in
all cases the condition "bug_id IS NULL" is replaced with "bug_id =
$bug_id".  For Selection fields, set an additional element "is_set",
which is a hash whose keys are the elements of the "value" array,
every corresponding value being the integer 1.


ADMINISTRATION
==============

A program called "editcustomfields.cgi", stored in Bugzilla's root
directory, will moderate all custom field administrative access.

Each page served by editcustomfields.cgi is templatized in the usual
Bugzilla fashion, and is bracketed by the usual Bugzilla header and
footer templates.

Each page contains a single form, which always contains a hidden field
called "context".


Initial page
------------
Context:  choose-scope
Template: template/en/default/custom/choose-scope.html.tmpl

The user may select a scope via either of the following three elements:

1.  A <select> box, seven lines in size, populated with a list of all
    available products:

    SELECT id, name
    FROM products
    ORDER BY name;

    Every <option> child represents one product; its "value" attribute
    is the product's numeric ID, and its content is the product's
    name.
    
2.  A <select> box, seven lines in size, populated with a list of all
    available field groups:

    SELECT group_id, group_name
    FROM cf_groups
    ORDER BY group_name;

    Evert <option> child represents one field group; its "value"
    attribute is the group's numeric ID, and its content is the
    group's name.

3.  A button <input> element, labelled "Global scope".


Main Menu
---------
Context:  main-menu
Template: template/en/default/custom/main-menu.html.tmpl

Given a $::vars{custom_fields} element populated as described
previously, here is a template that will generate the rows of the
informational table on this page.

[% FOREACH group = custom_field_groups %]
  [% FOREACH field = group.members %]
    <tr>
    [% IF loop.index == 0 %]
      <td rowspan="[% group.members.size %]">
        [% IF group.name.defined %]
          [%# The exact parameters here may need some tweaking... #%]
          <a href="editcustomfields.cgi?context=main-menu&group=[%group.id%]">
            [% group.name FILTER html %]
          </a>
        [% END %]
      </td>
    [% END %]
    <td>[% PROCESS custom_name      %]</td>
    <td>[% PROCESS custom_type      %]</td>
    <td>[% PROCESS custom_required  %]</td>
    <td>[% PROCESS custom_default   %]</td>
    <td>[% PROCESS custom_value_set %]</td>
    </tr>
  [% END %]
[% END %]

[% BLOCK custom_name %]
  [% field.name FILTER html %]
[% END %]

[% BLOCK custom_type %]
  [% IF field.is_integer %]
    integer
  [% ELSIF field.is_date %]
    date [% IF field.extended; "(with time)"; END %]
  [% ELSIF field.is_string %]
    string [% IF field.extended; "(long)"; ELSE; "(short)"; END %]
  [% ELSIF field.is_selection %]
    selection [% IF field.extended; "(multiple)"; ELSE; "(single)"; END %]
  [% END %]
[% END %]

[% BLOCK custom_required %]
  [% IF field.required; "Yes"; ELSE; "No"; END %]
[% END %]

[% BLOCK custom_default %]
  [% IF not field.default.defined %]
    <i>None</i>
  [% ELSE %]
    [% IF not field.is_selection %]
      <pre>[% field.default FILTER html %]</pre>
    [% ELSE %]
      <select size="3">
      [% FOREACH value = field.default %]
        <option>[% value FILTER html %]</option>
      [% END %]
      </select>
    [% END %]
  [% END %]
[% END %]

[% BLOCK custom_value_set %]
  [% IF field.is_selection %]
    <select size="3">
      [% FOREACH value = field.domain %]
        <option>[% value FILTER html %]</option>
      [% END %]
    </select>
  [% END %]
[% END %]

Each of the five submit buttons below the table (Edit, Insert, Move,
Rename, and Remove) has the name "action".

The following template block will generate a named selection element
containing every ungrouped custom field in the current scope.

[% BLOCK cf_select_fields %]
<select name="[% name FILTER html %]">
  [% FOREACH group = custom_field_groups %]
    [% IF not group.name.defined %]
      <option value="[% group.members.0.id %]">
        [% group.members.0.name FILTER html %]
      </option>
    [% END %]
  [% END %]
</select>
[% END %]

The following block will generate a named selection element containing
every field group and ungrouped field in the current scope.  The value
attributes of the <option> children are the numeric IDs of the groups.

[% BLOCK cf_select_groups %]
<select name="[% name FILTER html %]">
  [% FOREACH group = custom_field_groups %]
    <option value="[% group.id %]">
      [% IF group.name.defined %]
        [% group.name FILTER html %]
      [% ELSE %]
        [% group.members.0.name FILTER html %]
      [% END %]
    </option>
  [% END %]
</select>
[% END %]

The following block will generate a named selection element with
"before" and "after" elements.

[% BLOCK cf_direction %]
<select name="[% name FILTER html %]">
  <option>before</option>
  <option>after</option>
</select>
[% END %]

And finally, the next block will generate a named selection element
containing the four custom fields types.

[% BLOCK cf_types %]
<select name="[% name FILTER html %]">
  <option>string</option>
  <option>selection</option>
  <option>date</option>
  <option>integer</option>
</select>
[% END %]

The following template will generate the control elements for the
page.  editcustomfields.cgi should set $::var{has_ungrouped_fields} to
a true value if the current scope has any ungrouped fields, and a
false value otherwise.

<form method="post" action="editcustomfields.cgi">

[% IF has_ungrouped_fields %]
  <p>
    <input type="submit" name="action" value="Edit">
    the custom field
    [% PROCESS cf_select_fields name="edit-target" %].
  </p>
[% END %]

<p>
  <input type="submit" name="action" value="Insert">
  a new
  [% PROCESS cf_types name="insert-type" %]
  custom field
  [% PROCESS cf_direction name="insert-dir" %]
  [% PROCESS cf_select_groups name="insert-base" %].
</p>

<p>
  <input type="submit" name="action" value="Move">
  [% PROCESS cf_select_groups name="move-target" %]
  so that it is
  [% PROCESS cf_direction name="move-dir" %]
  [% PROCESS cf_select_groups name="move-base" %].
</p>

[% IF has_ungrouped_fields %]
  <p>
    <input type="submit" name="action" value="Rename">
    the custom field
    [% PROCESS cf_select_fields name="rename-target" %].
  </p>
[% END %]

<p>
  <input type="submit" name="action" value="Remove">
  the field or group
  [% PROCESS cf_select_groups name="remove-target" %].
</p>

</form>

The page should display one of the following pages depending on the
value of the "action" parameter.


Edit a Field
------------
Context:  edit-field
Template: template/en/default/custom/edit-field.html.tmpl

The program should set $::vars{field} to the element of
$::vars{custom_fields} identified by the CGI parameter "edit-target".

[% IF field.is_integer
      or field.is_date
      or (field.is_string and not field.extended) %]
  <input type="text" name="new-default" value="[% field.value FILTER html %]">
[% ELSIF field.is_string %]
  <textarea name="new-default">[% field.value FILTER html %]</textarea>
[% ELSIF field.is_selection %]
  <select name="new-default" size="7">
    [% FOREACH value = field.domain %]
      <option value="[% value FILTER html %]"
              [% " selected" IF field.is_set.$value %]>
        [% value FILTER html %]
      </option>
    [% END %]
  </select>
[% END %]