SCHEMA
======

The custom_fields table describes every custom field defined at this
Bugzilla installation.

CREATE TABLE custom_fields (
    field_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    field_name VARCHAR(100) NOT NULL,
    display_name TINYTEXT NOT NULL,
    field_type CHAR BINARY NOT NULL,
    selection_id INTEGER,
    UNIQUE INDEX (field_name)
);

field_id is the primary key.

field_name is an "internal" name which must be unique among all custom
fields.  Due to its uniqueness, it may be used to refer to a field in
CGI parameters, and it may be used as a hash key in internal code
without fear of collision

display_name is the human-readable name of the field which appears on
the Web interface.

field_type is a single-character description of the field's type.  It
must be one of the following:

'l':  long string
's':  short string
'i':  integer
'd':  date, no time
't':  date, with time
'e':  single selection
'm':  multiple selection

selection_id is non-NULL only for selection fields (either single or
multiple).  For such fields, the selection_id is a key into the
cf_selections table (see below).


The cf_membership table describes which fields belong to which
products, and in what order those fields are to be displayed on the
web interface.

CREATE TABLE cf_membership (
    product_id INTEGER NOT NULL REFERENCES products (id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    sortkey INTEGER NOT NULL,
    INDEX (product_id)
);

The meanings of all of the columns should be obvious.


The cf_selections table enumerates every selection field defined at
this Bugzilla installation, and provides an "unset label" for each.
An "unset label" is a short string which is displayed on the Web
interface when the value is a selection field is the empty set.

CREATE TABLE cf_selections (
    selection_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    unset_label TINYTEXT NOT NULL
);


The cf_selection_labels table stores the textual representation of
each selection field label.

CREATE TABLE cf_selection_labels (
    label_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    selection_id INTEGER NOT NULL REFERENCES cf_selections (selection_id),
    label TINYTEXT NOT NULL,
    inactive BOOL NOT NULL,
    sortkey INTEGER NOT NULL,
    INDEX (selection_id)
);

label_id is the primary key.

selection_id identifies the selection set to which this label belongs.

label is the label's textual representation.

inactive is true if the label is inactive, and false otherwise.  A
label may be made inactive when it should no longer be possible to
store new data with that label.  The label must remain in the
database, however, so that old data can refer to it.

sortkey is an integer used for ordering the labels within a selection
set.

[ What is a selection set anyway? ]


The cf_integer table holds data for integer custom fields.

CREATE TABLE cf_integer (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value INTEGER,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

The meanings of the columns should be obvious.  The index on the
field_id column is meant to speed up queries against integer custom
fields.


The cf_shortstring table holds data for short string custom fields.

CREATE TABLE cf_shortstring (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value TINYTEXT,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

The meanings of the columns should be obvious.  The index on the
field_id column is meant to speed up queries against short string
custom fields.


The cf_lonstring table holds data for long string custom fields.

CREATE TABLE cf_longstring (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value TEXT,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

The meanings of the columns should be obvious.  The index on the
field_id column is meant to speed up queries against long string
custom fields.


The cf_data table holds data for date and date/time custom fields.

CREATE TABLE cf_date (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    value DATETIME,
    PRIMARY KEY (bug_id, field_id),
    INDEX (field_id)
);

The meanings of the bug_id and field_id columns should be obvious.
The value column is of type DATETIME.  For custom fields of type date,
the time portion of this column is ignored--that is, it is not made
available to code which uses the standard interface.  The index on the
field_id column is meant to speed up queries against date custom
fields.


The cf_selection table holds data for single selection and
multiselection custom fields.

CREATE TABLE cf_selection (
    bug_id INTEGER NOT NULL REFERENCES bugs (bug_id),
    field_id INTEGER NOT NULL REFERENCES custom_fields (field_id),
    label_id INTEGER NOT NULL REFERENCES cf_selection_labels (label_id),
    INDEX (bug_id, field_id),
    INDEX (field_id)
);

Let B be the id of a bug which belongs to a product which contains a
selection field whose id is F.  Let V = { l1, l2, ..., lN } be the
value of that bug's field.  Then there will be N rows in this table to
record the value of that field:

(B, F, l1)
(B, F, l2)
...
(B, F, lN)

If V is the empty set, there will be no rows in this table with bug_id
B and field_id F.

Since there may be multiple rows in this table for a given bug_id and
field_id, a primary key cannot be used to index the data, as in the
tables for the scalar field types; instead, an index is used.  The
additional index on the field_id column alone is intended to speed up
queries against selection custom fields.


INTERFACE
=========

The programmatic interface to custom fields is provided by the module
Bugzilla::CustomFields.  It exports the following functions:

*  get_custom_fields
*  check_custom_fields
*  store_custom_fields
*  update_custom_fields


get_custom_fields
-----------------

This function returns information on zero or more custom fields.  Its
arguments should be in the form of zero or one name-value pairs, as
enumerated in the following prototype:

@custom_fields = get_custom_fields(
    [ product    => $product,    ]
    [ product_id => $product_id, ]
    [ bug_id     => $bug_id,     ]
    [ fields     => \@fields,    ]
    [ field_ids  => \@field_ids, ]
);

All parameters are mutually exclusive.

product => $product

If this parameter is present, data for all custom fields belonging to
the product whose name is $product is returned, in the same order that
the fields appear in in that product.  If no such product exists, an
exception is thrown.

product_id => $product_id

If this parameter is present, data for all custom fields belonging to
the product with id $product_id is returned, in the same order that
the fields appear in in that product.  If no such product exists, an
exception is thrown.

bug_id => $bug_id

If this parameter is present, data for all custom fields belonging to
the product which contains the bug whose id is $bug_id is returned, in
the same order that the fields appear in in that product.  In
addition, the data structure returned for each field is augmented with
an additional member, 'value', which contains the value of that field
for bug $bug_id.

fields => \@fields

If this parameter is present, data for the custom fields whose names
appear in @fields is returned, in the same order that their names
appear in in @fields.  If any of the elements of @fields is not the
name of an existing custom field, an exception is thrown.

field_ids => \@field_ids

If this parameter is present, data for the custom fields whose
numerical IDs appear in @field_ids is returned, in the same order that
their IDs appear in in @field_ids.  If any of the elements of
@field_ids is not the ID of an existing custom field, an exception is
thrown.

If no parameters are specified, then data for all existing custom
fields is returned, in no particular order.

get_custom_fields returns a list of hashes, one for each custom field
in the result set.  Each hash has the following members:

id
    The field's unique numeric ID, as taken from the field_id column
    of the CUSTOM_FIELDS table.

name
    The field's internal name, as taken from the field_name column of
    the CUSTOM_FIELDS table.

display_name
    The field's display name, as taken from the display_name column of
    the CUSTOM_FIELDS table.

type
    The field's type, as taken from the field_type column of the
    CUSTOM_FIELDS table.

selection_id
    The field's selection ID, as taken from the selection_id column of
    the CUSTOM_FIELDS table.  It will be undefined if the field is not
    a selection field.  [ Probably it should simply be absent in that
    case... ]

extended
    A boolean value, true if the field is of type long string,
    date/time, or multiselection, and false otherwise.

is_integer
    A boolean value, true if the field is of type integer, and false
    otherwise.

is_date
    A boolean value, true if the field is of type date or date/time,
    and false otherwise.

is_string
    A boolean value, true if the field is of type short string or long
    string, and false otherwise.

is_selection
    A boolean value, true if the field is of type single-selection or
    multiselection, and false otherwise.

as_string
    A reference to a subroutine which can convert the field's value to
    a simple string representation.  It will be undefined for
    selection fields.

[ The is_integer, is_date, is_string, is_selection, and extended
  elements are redundant with the type element, but they are more
  convenient to use from templates.  The as_string element is
  primarily useful from templates, as well. ]

For selection fields, the following additional elements are present:

unset
    The field's "unset label", taken from the unset_label column of
    the CF_SELECTIONS table.

domain
    A reference to an array containing the labels which make up the
    field's domain, sorted into the appropriate order.

lblid
    A hash reference.  The keys are the labels that make up the
    field's domain, and the values are the corresponding numerical
    label IDs.

label
    A hash reference.  The keys are numerical IDs of the labels which
    make up the field's domain, and the corresponding values are
    references to hashes which describe that label.  The elements of
    these second-level hashes are as follows:

    text
        The text of the label.

    order
        The relative order of the label within the field's domain, as
        taken from the sortkey column of the CF_SELECTION_LABELS
        table.

    inactive
        This element is present only if the label is inactive.  In
        that case, it has a true boolean value.

[ The domain element is redundant with either of the lblid or label
  elements, but is convenient to use from templates. ]


If the bug_id parameter is passed to get_custom_fields, each hash
returned by the function will have an additional element with the key
"value".  The corresponding value depends on the type of the custom
field.

*  For integer fields, the element is a simple integer.

*  For short string and long string fields, the element is a simple
   string.

*  For date custom fields, the element is a reference to a
   three-element array of this form:  [ $year, $month, $day ].

*  For date/time custom fields, the element is a reference to a
   six-element array of this form:  [ $year, $month, $day, $hour,
   $minute, $second ].  The hour is 0-23.

*  For single-selection and multiselection fields, the element is a
   reference to an array containing one textual label for every
   element of the field's domain which is a member of that field's
   value, sorted into the appropriate order.


EXAMPLE:

Suppose a product P contains three custom fields: a short string field
"Customer", a date/time field "Date Opened", and a multiselection
field "Departments" with domain { "Foo", "Bar", "Baz" }.  Suppose bug
1234 belongs to this product, and that its Customer field is
"Someone", its Date Opened field is 10 January 2002 3:30 PM, and its
Departments field is { "Foo", "Baz" }.  Then the result of calling
get_custom_fields(bug_id => 1234) will be a three-element list that
looks something like this (modulo precise internal field names,
numeric IDs, and boolean values):

({
    id => 10,
    name => 'customer',
    display_name => 'Customer',
    type => 's',
    selection_id => undef,
    extended => 0,
    is_integer => 0,
    is_date => 0,
    is_string => 1,
    is_selection => 0,
    as_string => \&some_subroutine,
    value => 'Someone'
}, {
    id => 22,
    name => 'date_opened',
    display_name => 'Date Opened',
    type => 't',
    selection_id => undef,
    extended => 1,
    is_integer => 0,
    is_date => 1,
    is_string => 0,
    is_selection => 0,
    as_string => \&some_subroutine,
    value => [ 2002, 1, 10, 15, 30, 0 ]
}, {
    id => 25,
    name => 'departments',
    display_name => 'Departments',
    type => 'm',
    selection_id => 5,
    extended => 1,
    is_integer => 0,
    is_date => 0,
    is_string => 0,
    is_selection => 1,
    as_string => undef,
    unset => '(None)',  # for example
    domain => [ 'Foo', 'Bar', 'Baz' ],
    lblid => { 'Bar' => 14, 'Baz' => 15, 'Foo' => 13 },
    label => { 13 => { text => 'Foo', order => 1 },
               15 => { text => 'Baz', order => 3 },
               14 => { text => 'Bar', order => 2 }
             },
    value => [ 'Foo', 'Baz' ]
})