Custom Fields again

[Bradley Baetz]

On Tue, Dec 10, 2002 at 11:28:32PM +0000, Gervase Markham wrote:
> Indeed. I assumed that adding extra columns to the bugs table was the 
> way we were going to implement it; I didn't even consider that there 
> might be another option.

It didn't occur to me that anyone would be trying to dynamically create
tables on the fly.... What if you called your custom field 'requests' or
something? (Yes, we can prefix with custfield_, I suppose)

> 
> As I see it, it would work as follows:
> - When you define a new field, you define an alphanumeric name/tag (e.g. 
> "buildid"), a description ("Build ID") and a type ("text").
> - For single-select or text fields, we would then add an extra column to 
> the bugs table of that type with that name.
> - For multi-select fields would instead have an extra table of that 
> name, which mapped bug IDs to values.
> - show_bug would display all fields it could find in a sane way; for 
> example, there'd be a vertical column of text fields, much like now, and 
> multi-selects would be three across the page.
> - query.cgi would provide suitable query UI depending on field type.
> - Of course, the layout of both the above would be customisable; you 
> would hard-code using a form name of the tag defined above, which fits 
> with current practice of using DB column names for form names.
> 
> Anyone else thinking along these lines?

Almost. What we do is define three new tables:

fields
---------------------------------
id    | auto_increment/serial/etc
name  | varchar(x)
type  | int - mapping to const in Bugzilla::Constants

field_vals
---------------------------------
id      | auto_increment/serial/etc
fieldid | references fields.id
value   | varchar(x)

field_entries
---------------------------------
fieldid | references fields.id
bug_id  | referencs bugs.bug_id
value   | varchar(x)

And, umm.... Thats it. This gives us:

- multiple values-per bug for multi-select fields
- constraints for popup types, via field_vals
- id mappings (value is actually an integer, except for teh freeform
text types)
- consistency + resuse of search code - just change the fieldid val in
the WHERE clause

See my coomments in the bug (as well as the existing patches) for more.

Bradley