Custom fields schema

[Sean McAfee]

Joel Peshkin <bugreport at peshkin.net> wrote:
>Sean McAfee wrote:
>>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)
>>);

>Let's merge this with fielddefs.  It would be a shame to make every bit 
>of code have to query both fielddefs and custom_fields.  At first glance 
>that may seem like more work, but it will not be by the time we are done 
>with buglist.cgi, Search.pm, and BugMail.pm.

Except for logging, I've never had much trouble with the two distinct
tables.  Besides, they describe two different kinds of data; FIELDDEFS
describes columns of the BUGS table, but CUSTOM_FIELDS describes data that
lives in the various CF_* tables.  Some FIELDDEFS columns are even computed,
such as "Days since bug changed", a concept which can't apply to custom
fields as currently defined.

A unification of the tables would be appropriate when what are currently
built-in fields are implemented as custom fields, or "fields" as I suppose
they would then be known.

>Also, do we need accesskeys for customfields? 

Er...what are they?

>>field_type obviously describes the type of the field.  Valid values are:
>>
>>'i' - An integer.
[snip]

>I think a single character may not be sufficient in the long run.  This 
>looks like a reasonable initial set, but I can see subsequent patches 
>adding 'u' for a userid.  Ideally, it would be nice to be able to 
>specify i(userid) or some more inspired syntax to indicate that the 
>field is an integer holding a userid, etc...

That would be nice, but I think 26 field types would be excessive, let alone
the 255 types (or even more, given the i18n-ness of MySQL) the CHAR BINARY
column supports.  

>>More recently, I've had a need to address custom fields and built-in
>>Bugzilla fields using the same range of integers, so I added an eighth field
>>type: 'b', for Bugzilla.

>Please explain

My current implementation logs changes to custom fields, attaching the
date/time and user IDs of the user making the change and the user of the new
assignee.  (I haven't described this part of the schema yet, since I was
starting with just the basics.)  I needed to be able to log fields
identified by numeric ID, using the same range of numbers for both built-in
and custom fields.  The 'b' custom field type was my solution.  Whenever I
needed to refer to a built-in field using the same range of integers as
custom fields, I would do something like this:

INSERT INTO custom_fields
SELECT NULL, name, description, 'b', fieldid
FROM fielddefs
WHERE field_name IN ('assigned_to', 'summary');

I suppose the built-in/custom distinction could also have been described in
the log table.

>We should also determine what expectations we have from the UI.  I still 
>believe that the right thing to do is to make things work if the 
>templates know nothing about the new fields, but give template authors 
>the ability to "take over" some fields while leaving others to the 
>default processing.

My current implementation is simple--too simple, probably.  All custom
fields appear in a two-column HTML table on the show_bug page, above the
"Attachments" section, names on the left, values on the right.  In previous
discussions, folks have expressed a desire to format fields arbitrarily,
anywhere on the page.  The only difficulty with this is that we need a
default format and location for custom fields which are not already
explicitly placed elsewhere; the cleanest-looking such format would line up
the names and values, probably by placing them as columns of an HTML table,
so that this format needs to assume some knowledge of the surrounding HTML
context.


--Sean