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 not null 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. 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; 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. The first four, which hold scalar field data, are very similar. 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 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.