The following three tables provide definitions for all custom fields in the Bugzilla installation. custom_fields ------------- project tinytext primary key, field_id integer not null, field_index integer not null This table defines which fields belong to which projects, and the order in which those fields should be presented to users. The global scope is described by the project "" (ie, the zero-length string). custom_field_defs ----------------- field_id integer primary key, field_name tinytext not null, field_type char not null, extended bool not null, selection_id integer The field_type column is 'I' for integer fields, 'C' for string fields, 'D' for date fields, and 'S' for selection fields. The extended column is always false for Integer fields. It has the following meanings for other fields: Field type | extended is "true" | extended is "false" -----------+---------------------+------------------------ String | long string (text) | short string (tinytext) Date | date/time | date only Selection | multiselection | single-selection The selection_id column is an index to the cf_selection_defs and cf_selection_strings tables for fields of type Selection, and is NULL for other types. cf_selection_defs ----------------- selection_id integer primary key, unset tinytext not null The unset column is the text which represents the "empty set" value of single-selection fields. cf_selection_strings -------------------- selection_id integer not null, string tinytext not null, index (selection_id) ***** The following five tables contain bug data. The first four, which hold scalar field data, are very similar. cf_integer ---------- bug_id integer not null, field_id integer not null, value integer, primary key (bug_id, field_id) cf_smallstring -------------- bug_id integer not null, field_id integer not null, value tinytext, primary key (bug_id, field_id) cf_longstring ------------- bug_id integer not null, field_id integer not null, value text, primary key (bug_id, field_id) cf_date ------- bug_id integer not null, field_id integer not null, value date, primary 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 not null, field_id integer not null, selection_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_strings 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_strings table. [Open question: Should this table be split into two: cf_selection_single and cf_selection_multi?] ***** The following six tables hold bug activity data. cf_transactions --------------- transaction_id integer primary key, bug_id integer not null, user_id tinytext not null, when date not null cf_integer_activity ------------------- transaction_id integer not null, field_id integer not null, old_value integer, new_value integer, index (transaction_id) cf_shortstring_activity ----------------------- transaction_id integer not null, field_id integer not null, old_value tinytext, new_value tinytext, index (transaction_id) cf_longstring_activity ---------------------- transaction_id integer not null, field_id integer not null, old_value text, new_value text, index (transaction_id) cf_date_activity ---------------- transaction_id integer not null, field_id integer not null, old_value date, new_value date, index (transaction_id) cf_selection_activity --------------------- transaction_id integer not null, field_id integer not null, 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.