BASIC TERMINOLOGY ================= A "custom field" is a typed piece of data which may be attached to a Bugzilla bug. Custom fields may be of any of the following types: integer A four-byte signed integer. [ Or whatever an INTEGER on the database happens to be. ] short string A string of no more than 255 characters. These fields will be displayed on Bugzilla's web interface as single-line text input fields, so they should not contain any vertical whitespace characters. long string A string of no more than 65535 characters. date A calendar date, including a year, a month, and a day. date/time A calendar date combined with a time-of-day. single-selection 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 * get_form_data * 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' ] }) get_form_data ------------- This function reads CGI form data associated with one or more custom fields and returns the data in a single data structure. The return value is a reference to a hash. There is one key-value pair in the hash for every custom field data structure (as returned by get_custom_fields) passed as an argument. Each key is the internal name of a field; the corresponding value is either the value of the CGI parameter with the same name (for nonselection fields), or a reference to an array containing all of the values in the multivalued CGI parameter with the same name (for selection fields). EXAMPLE: Suppose a user submits a form containing bug data for the product described in the Example for get_custom_fields. The Customer custom field has the value "Ford", the "Date Opened" field has the value "11-Mar-2003", and the "Departments" custom field has the values "Foo" and "Bar". Then the return value of the expression get_form_data(get_custom_fields(product => 'P')) ...would be this: { customer => 'Ford', date_opened => '11-Mar-2003', departments => [ 'Foo', 'Bar' ] } check_custom_fields ------------------- This function validates supplied data against one or more custom fields. It returns a list of errors. The function takes two parameters, which must be passed as two name-value pairs: check_custom_fields( form => $form, fields => \@fields ) Both parameters are mandatory. $form is a data structure as returned by get_form_data, above. @fields is an array of custom field data structures, as returned by get_custom_fields, above. For each field $field in @fields, let $display be the display name of that field. Then the following checks are performed: * If the field is a string field, and the supplied value for that field exceeds the maximum length of the field (255 characters for short string fields, 65535 characters for long string fields), an error "$display is too long" is generated. * If the field is an integer field, and the supplied value is not a valid integer, the error "$display is not a valid integer" is generated. * If the field is a date or date/time field, and the supplied value is not a valid date or date/time, the error "$display is not a valid date" is generated. * If the field is a single-selection field, and there are more than one supplied value, the error "$display may not be set to multiple values" is generated. * If the field is a single or multiselection field, and any of the supplied values is not equal to the field's unset label or any of the labels in the field's domain, then one of the following errors is generated: o $display may not be set to the value "..." (one invalid value) o $display may not be set to either of the values "..." or "..." (two invalid values) o $display may not be set to any of the values "...", "...", ..., or "..." (three or more invalid values) All of the errors that were generated in checking the parameters in $form are returned as a list of strings. If there were no errors, an empty list is returned. store_custom_fields ------------------- This function adds new custom field data to the database. It should be called only when a new bug is being created. The prototype is: store_custom_fields($bug_id, $data, @fields) $bug_id is the ID of a newly-created bug. $data is a data structure as returned by get_form_data, above. @fields is an array of custom field data structures as returned by get_custom_fields, above. For each field in @fields, rows are inserted into the custom field data tables (CF_INTEGER, CF_DATE, CF_SHORTSTRING, CF_LONGSTRING, CF_SELECTION) that correspond to the data in $data. This function does no error checking. check_custom_fields MUST be called, and return no error messages, before store_custom_fields is called. update_custom_fields -------------------- This function updates custom field data already present in the database. It should be called when a bug is being updated. The prototype is: update_custom_fields($bug_id, $data, @fields) $bug_id is the ID of the bug which is being updated. $data is a data structure as returned by get_form_data, above. @fields is an array of custom field data structures as returned by get_custom_fields, above. For each field in $field, the value of that field in $data is compared with the existing field value in the database. If they differ, then the appropriate rows are updated (for nonselection fields) or inserted and/or deleted (for selection fields). This function does no error checking. check_custom_fields MUST be called, and return no error messages, before update_custom_fields is called. QUERYING ======== An interface for obtaining lists of bugs which meet user-specified criteria is provided by the module Bugzilla::CustomFields::Query. [ This module would probably be better-named "Bugzilla::Query"; or, if it is ultimately accepted as a replacement for Bugzilla::Search, it could assume that module's name. ] The following routines are exported: * bug_iterator * find_bug bug_iterator ------------ This function is the main query interface function. Its arguments should be passed as name-value pairs: bug_iterator( [ product => $product, ] [ where => $where_clause, ] [ custom_fields => $custom_fields, ] [ notes => $notes, ] [ attachments => $attachments, ] [ debug => $debug, ] ) All parameters are optional. No pair of options is contradictory. The return value of the function is a subroutine reference (an "iterator"). Each time the referred-to subroutine is called, it returns a data structure which describes one of the bugs which satisfies the conditions described by the parameters originally passed to bug_iterator. The subroutine returns undef after it has returned every matching bug. The data structure returned by the iterators returned by this function is a reference to hash. This hash contains the following elements: * For every column in the BUGS table, there is an element in the hash whose name is the name of the column and whose value is the corresponding value from the BUGS table. Two exceptions are the "reporter" and "assigned_to" elements; the values of these elements are the login names of the users identified by the original column values, which are foreign keys into the PROFILES table. [ It might be better to include a more complete body of information from the PROFILES table than just the login name. ] * An element "product" whose value is the name of the product identified by the product_id column from the BUGS table. * An element "component" whose value is the name of the component identified by the component_id column from the BUGS table. * For every custom field which belongs to the product to which the bug belongs, there is an element whose name is the internal name of the field. The corresponding value is the value of that field: a simple scalar for non-multiselection fields, or a reference to an array of labels for multiselection fields. Note that although single-selection and multiselection fields are internally represented in the same way by much of the custom fields API--that is, as a reference to an array of labels--their representations differ here. For convenience, single-selection fields are represented as either a simple string (if the field is set) or undef (if it is unset). Note also that if a query returns bugs that reside in different products, the result hashes returned by the iterator may include different custom field elements. * If the "notes" parameter to bug_iterator is a true boolean value, then there will be an extra "notes" element present in every returned bug structure. This element's value is a reference to an array which will contain one hash reference for every note attached to the bug. The keys in these hashes are "who", "bug_when", "work_time", and "thetext". The corresponding values are taken directly from the LONGDESCS table. * If the "attachments" parameter to bug_iterator is a true boolean value, then there will be an extra "attachments" element present in every returned bug structure. If this value is anything other than the string "deferred", then the attachments element will be a reference to an array which contains one hash reference for every attachment attached to the bug. The keys in these hashes are "attach_id", "creation_ts", "description", "mimetype", "filename", "thedata", and "submitter_id". The corresponding values are taken directly from the ATTACHMENTS table. If the "attachments" parameter is true and is equal to the string "deferred", then the extra "attachments" element in the returned bug structures is instead an object, in the form of a hash-of-closures. To call method $method on this object, call $object->{$method}(@arguments). There are two methods available: o list() This method returns a list of the numeric IDs of all attachments attached to this bug. o fetch($attach_id) This method returns the data associated with the attachment identified by $attach_id in the same format described above (ie, if the attachments parameter to bug_iterator had been some other true value besides the string "deferred"). If there are many large attachments in the database, and only a few whose IDs are known in advance are required, then fetching them in deferred mode can save a lot of time. [ Perhaps more information than simply the attach_ids should be made available by the list() method. ] The "where" parameter --------------------- If neither the "product" nor "where" parameters are supplied to bug_iterator, the returned iterator will return every bug in the database. To select only a subset of bugs, one of these parameters must be present. The "product" parameter is available as a convenience, as described below. The "where" parameter must be a reference to an array, which describes a "where expression".