Custom Fields again

[Gervase Markham]

Bradley Baetz wrote:
> 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)

What's wrong with "dynamically" creating tables? And what's more 
"dynamic" about them than checksetup.pl's creation of tables? (Yes, we 
could prefix table names with cf_ to avoid clashes.)

> 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

...and a whacking great performance hit, surely. You have to join to one 
or more tables for every custom field you have. Also, numeric data is 
not numeric, it's varchar, and that's bad for search performance.

If we add rows to the bugs table for each (single-valued) field, this 
has several advantages. For a start, without too much work, we could 
make many of our current bugs tables fields into custom ones, allowing 
admins to remove those they don't like, because their existence will be 
the same as other custom fields. It also lets us use numbers for numeric 
data, and other appropriate SQL types - including short varchars for 
short data, and long varchars for long data. And I am certain search 
performance will be better without all those joins.

We can still have an external table with the names and types of the 
custom fields, if that makes things better. It would be nice if we could 
just scan all the column names from the bugs table, but maybe there are 
reasons why that wouldn't work.

Gerv